Error code 1582 incorrect parameter count in the call to native function datediff

Error 1582 incorrect parameter count in the call to native function datediff

MySQL 5.1 Manual
DATEDIFF(expr1,expr2)

DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

Вы не обратили внимание, там написано (Transact-SQL)
Знаете, что это означает? Что данный код допустим в Transact-SQL.
А Transact-SQL — это диалект SQL, используемый в Microsoft SQL Server.

я вообще не понимаю, зачем Вы смотрите в msdn описание команд MS SQL server, если Вы им не пользуетесь.
MS SQL это совсем не тоже самое, что MySQL!

И, надеюсь, для Вас уже не секрет, что в разный СУБД (MS SQL, Oracle, MySQL, PostrgeSQL, Firebird) используются РАЗНЫЕ диалекты SQL.
Особенно это касается работы с полями DATE, TIME, обработкой строк и т.д. и т.п.
Поэтому SQL код для Oracle может не работать на MySQL, или, код от MS SQL может не работать на MySQL (как в вашем случае).

Источник

Error occurs when saving a point — «Incorrect parameter count in the call to native function ‘ST_GeomFromText’» #138

Comments

Art-Mas commented Apr 25, 2020

Hi! I decided to try your package in PHPUnit:

an error occurs when running the test:

SQLSTATE[42000]: Syntax error or access violation: 1582 Incorrect parameter count in the call to native function ‘ST_GeomFromText’ (SQL: insert into points ( point ) values (ST_GeomFromText(POINT(-73.9878441 40.7484404), 0, ‘axis-order=long-lat’))

but if you delete the line «, ‘axis-order=long-lat’ « in the file SpatialExpression.php then saving will work fine, see screenshot:

I also tried running the raw query leaving the string «, ‘axis-order=long-lat’ « and it worked without an error:

The text was updated successfully, but these errors were encountered:

grimzy commented Apr 25, 2020

What version of MySQL are you using? axis-order was added in MySQL 8.

Also, note that the query reported in the error is different than the one you tested (notice the quotes inside ST_GeomFromText() )

Art-Mas commented Apr 26, 2020 •

@grimzy
Thanks for the answer!

What version of MySQL are you using?

The problem was using MariaDb (delivered by default in XAMPP)
Changing to MYSQL 8 solved the problem

It would be good to add mariadb compatibility 🙂

dacianb commented Apr 26, 2020

grimzy commented Apr 26, 2020

@slave2anubis , also same version of MariaDB?

I’d love to add better MariaDB compatibility. Did you have any issues with the migrations?

For now, if this is blocking you, I believe that «upgrading» to your database engine to MySQL 8 or downgrading the package to the v2.x branch could fix your issue.

dacianb commented Apr 26, 2020

Sorry but i removed Mariadb and added Mysql server. The version was 10.4.xx i am running on a linux machine with default mariadb packages.

mwargan commented May 2, 2020

I’m on MySQL 5.7 — what is the max supported version of this package for that version of MySQL?

blindpenguin commented May 2, 2020

I’m running MariaDB 10.3.12 (via WAMP 3.1.7) and i have the same issue. Migration runs just fine for me. The previous version of your package works just fine.

grimzy commented May 2, 2020

@mwargan , for MySQL 5.7, please use v2.x.x of grimzy/laravel-mysql-spatial .

grimzy commented May 2, 2020

For now I recommend MariaDB users to also stick to v2.x.x of this package.

RomainMazB commented Aug 28, 2020

For now I recommend MariaDB users to also stick to v2.x.x of this package.

It solved my issue (can’t install MySQL on my shared hosting. )

andrewminion-luminfire commented Sep 21, 2020

I’m using v4.0 for Laravel 8 compatibility, and override just the one file to fix MariaDB compatibility:

And the src/Eloquent/SpatialExpression.php file:

However, it would be great to have separate MySQL vs. MariaDB files, or conditionally return the spatial query based on the actual database engine.

grimzy commented Sep 21, 2020

However, it would be great to have separate MySQL vs. MariaDB files, or conditionally return the spatial query based on the actual database engine.

Yes! It’s also quite difficult to maintain at the moment. Any suggestions on how to implement this?
I was thinking maybe we could have Traits for each database engine (mysql 5.6, 5.7, 8, Maria DB. ). What do you think?

andrewminion-luminfire commented Sep 28, 2020

I was thinking maybe we could have Traits for each database engine (mysql 5.6, 5.7, 8, Maria DB. ). What do you think?

That makes sense…I think. Are you talking about a trait for the EloquentSpatialExpression class to use? or the ElquentSpatialTrait for end users to place on their models?

As a user of the package, it would be great for the package to auto-detect the database engine and use the appropriate class internally, so I don’t have to choose a MySQL vs. MariaDB model trait.

If auto-detecting the engine is too troublesome, maybe a database engine config key/value would work?

Sonicelo commented Oct 20, 2020

I’m using v4.0 for Laravel 8 compatibility, and override just the one file to fix MariaDB compatibility:

And the src/Eloquent/SpatialExpression.php file:

However, it would be great to have separate MySQL vs. MariaDB files, or conditionally return the spatial query based on the actual database engine.

In my case, I had to override the SpatialTrait.php as well.

nazmulpcc commented Apr 16, 2021

Had the same problem with Mariadb 10.3 and Laravel 8 . The solution from @andrewminion-luminfire fixes the problem. But what I did is made another trait AppHelpersSpatialSpatialTrait and override performInsert method that uses my own SpatialExpression instead of the one from this package. This way, I have more control over everything and I can override anything that I might need to in the future.

jasonmm commented Apr 19, 2021

Version 5.0.0 of this library ( composer require grimzy/laravel-mysql-spatial:^5.0 ) works for me using Laravel 8 and MariaDB 10.5.

tonsoflaz2 commented May 23, 2021

@jasonmm worked for me as well, thank you! (and i’m just extremely glad i didn’t have to go down some of these other rabbit holes)

AmineDevF commented Jun 10, 2021

I Had the same problem with HedeiSql and Laravel 8 ??
«errors»: «SQLSTATE[42000]: Syntax error or access violation: 1582 Incorrect parameter count in the call to native function ‘ST_GeomFromText’ «

lanz1 commented Jul 19, 2021

Version 5.0.0 of this library ( composer require grimzy/laravel-mysql-spatial:^5.0 ) works for me using Laravel 8 and MariaDB 10.5.

That solved the issue for me as well, Laravel 8, on 10.3.29-MariaDB and PHP 7.4.20

asdrubalp9 commented Apr 16, 2022

I’m using v4.0 for Laravel 8 compatibility, and override just the one file to fix MariaDB compatibility:

And the src/Eloquent/SpatialExpression.php file:

However, it would be great to have separate MySQL vs. MariaDB files, or conditionally return the spatial query based on the actual database engine.

Источник

Используйте функцию DATEDIFF с mySQL

Просто попробуйте использовать встроенную функцию DATEDIFF в SQL с моим следующим запросом:

Сообщение об ошибке:
#1582 — Incorrect parameter count in the call to native function ‘DATEDIFF’ .

Что мне не хватает?

Отметьте свою версию MySQL.

Ошибка не требует пояснений

Похоже, вы знакомы с функцией DATEDIFF в SQL Server, которая принимает три аргумента. MySQL DATEDIFF принимает только два аргумента. Избавьтесь от ‘DAY’, , и все будет хорошо. Удачи.

Bob Jarvis — Reinstate Monica

Из документов MySQL:

РАЗНДАТ () возвращает expr1 — expr2, выраженное как значение в дней от одной даты до другой. expr1 и expr2 — это выражения даты или даты и времени. В расчетах используются только части значений даты.

• РАЗНДАТ (выражение1; выражение2)

У меня вот эта ошибка: Subquery returns more than 1 row . См. sqlfiddle.com/#!9/6d3c4c/1.

@rony dateiff должен вернуть одну запись. sqlfiddle.com/#!9/6d3c4c/12

Какую функцию мне следует использовать, если мне нужно просуммировать все возвращенные даты?

Я имею в виду, вы не можете использовать скалярный подзапрос, который возвращает более одной строки, в противном случае вы должны присоединиться к нему

Я не могу использовать что-то подобное SUM(DISTINCT DATEDIFF(. )) ?

Источник

Error 1582 incorrect parameter count in the call to native function datediff

public function search(Request $request) < n

$rules = [n ‘name’ => ‘max:191|min:4’,n ‘city’ => »,n ‘lat’ => »,n ‘lang’ => »,n ‘price_from’=> ‘numeric’,n ‘price_to’ => ‘numeric’,n ‘area_from’ => ‘numeric’,n ‘area_to’ => ‘numeric’,n ‘num_of_person_from’=> ‘numeric’,n ‘num_of_person_to’ => ‘numeric’,n n ];nn $validator = Validator::make($request->all(), $rules);nn if ($validator->fails())n return ApiController::respondWithError(validateRules($validator->errors(), $rules));n $gr_circle_radius = 6371;n $max_distance = 2;n $lat = $request->lat;n $lng = $request->lang;n n $data = Hole::select(DB::raw(‘*, ( 6367 * acos( cos( radians(‘.$lat.’) ) * cos( radians( lat ) ) * cos( radians( lang ) — radians(‘.$lng.’) ) + sin( radians(‘.$lat.’) )* sin( radians( lat ) ) ) ) AS distance’))n ->having(‘distance’, ‘ orderBy(‘distance’)->with(‘photos’)n ->orwhere(‘city’, $request->city)n ->orwhere(‘name’, $request->name)n ->orWhereBetween(‘price’, [$request->price_from, $request->price_to])n ->orWhereBetween(‘area’, [$request->area_from, $request->area_to])n ->orWhereBetween(‘person_count’, [$request->num_of_person_from, $request->num_of_person_to])n ->get();n n n >);nn return ApiController::respondWithSuccess($data);nn>n n

Are you sure your form input name is «lang» and not «long» for longitude? If it’s wrong, then $lng is null. n

Several places you’re using lat instead of $lat? n

Источник

Error 1582 incorrect parameter count in the call to native function datediff

MySQL 5.1 Manual
DATEDIFF(expr1,expr2)

DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

Вы не обратили внимание, там написано (Transact-SQL)
Знаете, что это означает? Что данный код допустим в Transact-SQL.
А Transact-SQL — это диалект SQL, используемый в Microsoft SQL Server.

я вообще не понимаю, зачем Вы смотрите в msdn описание команд MS SQL server, если Вы им не пользуетесь.
MS SQL это совсем не тоже самое, что MySQL!

И, надеюсь, для Вас уже не секрет, что в разный СУБД (MS SQL, Oracle, MySQL, PostrgeSQL, Firebird) используются РАЗНЫЕ диалекты SQL.
Особенно это касается работы с полями DATE, TIME, обработкой строк и т.д. и т.п.
Поэтому SQL код для Oracle может не работать на MySQL, или, код от MS SQL может не работать на MySQL (как в вашем случае).

Источник

In MariaDB, DATEDIFF() is a built-in date and time function that returns the difference, in days, between two dates.

It accepts two arguments, both of which are date or datetime expressions. It then subtracts the second date from the first.

Syntax

The syntax goes like this:

DATEDIFF(expr1,expr2)

It then returns expr1 - expr2.

Example

Here’s an example:

SELECT DATEDIFF('2030-01-25', '2030-01-20');

Result:

+--------------------------------------+
| DATEDIFF('2030-01-25', '2030-01-20') |
+--------------------------------------+
|                                    5 |
+--------------------------------------+

Here it is again, but with the dates swapped around:

SELECT DATEDIFF('2030-01-20', '2030-01-25');

Result:

+--------------------------------------+
| DATEDIFF('2030-01-20', '2030-01-25') |
+--------------------------------------+
|                                   -5 |
+--------------------------------------+

Datetime Values

Here’s an example that uses datetime values:

SELECT DATEDIFF('2030-01-25 00:00:00', '2030-01-20 23:59:59');

Result:

+--------------------------------------------------------+
| DATEDIFF('2030-01-25 00:00:00', '2030-01-20 23:59:59') |
+--------------------------------------------------------+
|                                                      5 |
+--------------------------------------------------------+

Here’s another example:

SELECT DATEDIFF('2030-01-25 00:00:00', '2030-01-25 23:59:59');

Result:

+--------------------------------------------------------+
| DATEDIFF('2030-01-25 00:00:00', '2030-01-25 23:59:59') |
+--------------------------------------------------------+
|                                                      0 |
+--------------------------------------------------------+

In this case, even though it was almost a whole day, it wasn’t quite, and so the result is 0.

Current Date

Here, we pass NOW() as the second argument:

SELECT 
    NOW(),
    DATEDIFF('2030-01-20', NOW());

Result:

+---------------------+-------------------------------+
| NOW()               | DATEDIFF('2030-01-20', NOW()) |
+---------------------+-------------------------------+
| 2021-05-10 10:03:08 |                          3177 |
+---------------------+-------------------------------+

In this case there are 3,177 days between now and the second date.

Invalid Arguments

When passed any invalid arguments, DATEDIFF() returns null:

SELECT DATEDIFF('Homer', 'Simpson');

Result:

+------------------------------+
| DATEDIFF('Homer', 'Simpson') |
+------------------------------+
|                         NULL |
+------------------------------+

Missing Argument

Calling DATEDIFF() the wrong number of arguments, or without passing any arguments, results in an error:

SELECT DATEDIFF();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DATEDIFF'

And:

SELECT DATEDIFF('2030-05-21');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DATEDIFF'

B

Do you have notifications associated with 2 or more roles? That would explain the duplicate results.
From the schema we can infer that a notification could be associated with many statues and also with many roles. Therefore if a notification is associated with many — say 3 — roles, then every one of its statuses will appear many (3) times. The notifications that are associated with 1 role do not show any duplication.
We also notice that the SELECT list does not include any columns from the role table. That helps for a solution: convert the JOIN to table role to an EXISTS subquery. The query becomes:
SELECT
n.*, ns.notification_id AS is_read
FROM
notifications n
LEFT OUTER JOIN notification_status ns
ON n.id = ns.notification_id
WHERE EXISTS
( SELECT 1
FROM notification_user_role nur
WHERE n.id = nur.notification_id
AND nur.user_role_id = :consultant_role
)
AND n.esb_consultant_id IN (:consultant_id, :role_all)
AND n.creation_date = NOW()
ORDER BY
n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC
LIMIT
$offset, $limit

The other reason might be that you have many statuses for a notification. In that case, the solution depends on what you want to do about it. (i.e. what is means for a notification to have 2 or more statuses, should it appear once or twice in the results, etc).
(but that’s not the problem in this case)

The third reason that you may get «duplicates» is when you don’t have a definite (full) ORDER BY clause. That is, when the ORDER BY cannot resolve all ties. In this case, when there are ties (say in positions 8 to 28), the LIMIT 10 OFFSET 0 means «get me 10 rows, order by that». But the «that» is only good enough to specify the first 7 rows. The last 8th, 9th and 10th are tied (with 18 more!) so MySQL decides and picks 3 of them arbitrarily. (Still no duplicates)
But in the next query, when you ask for rows 11th to 20 with OFFSET 10 LIMIT 10, MySQL resolves the ties in a different way and may give you the 3 rows that you had seen before again (now appearing as 11th, 14th and 15th!).
Why does it do that? Because it can and you haven’t told her a definitive and full way to do the ORDER BY.
By the way, I wouldn’t call these «duplicates» as they appear in different result sets / calls. The slightly misleading terminology was probably the reason the cause couldn’t easily be identified.
The solution is easy however. Just add one more column (that is unique) in the ORDER BY clause:
ORDER BY
n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC,
n.id — to resolve ties

(or, as the OP clarified in the discussion in comments and the chat room, a different order by was needed, still with the n.id in the end):
ORDER BY
(is_read IS NULL) DESC, n.priority ASC, n.creation_date DESC,
n.id DESC