CODEWITHSUNDEEP
mysqlmariadb
zegab
zegab

Reputation: 23

mysql treats the string 'now()' as datetime?

I have a query that incorrectly constructed to include the function 'now()' as a string:

 select Listing.id,Listing.reactivated FROM `listings` AS Listing  WHERE Listing.reactivated < 'now()';

but actually this query is able to return correct results with mysql 5.5:

|  26662 | 2007-06-04 21:42:51 |
|  26663 | 2007-06-04 21:46:34 |

actually, several date functions, even without parentheses work; like 'now' or 'curdate', but a simple, "select 'now()';", will return a string.

Actually, I have noticed this when we upgraded Mysql to MariaDB 10.1, where it stopped working, the result of the above query is a null set, and a warning:

| Warning | 1292 | Incorrect datetime value: 'NOW()' |

I understand we should fix the queries :), but I would like to ask if anybody knows the reason behind this, when this was changed, and also, if this behavior can be configured?

Upvotes: 2

Views: 469

Answers (2)

Rick James
Rick James

Reputation: 142306

Don't use quotes: 'NOW()'; simply say NOW().

The former is a string, which is not a valid datetime. (Ditto for 'NOW' and 'XYZ'.)

Upvotes: 1

Barmar
Barmar

Reputation: 781255

The documentation of Type Conversion in Expression Evaluation claims:

If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed.

But that doesn't seem to be what's happening in this case, so it may be a bug. It appears that it's converting the DATETIME to a string, and comparing that with the constant. So if the current datetime is 2016-07-19 15:13:06, you get the result of:

SELECT '2016-07-19 15:13:06' < 'now()';

Since digits are lower than letters in the collating sequence, this returns 1.

I came to this conclusion by trying other strings:

SELECT NOW() < '300';

This also returns 1. But if I change it to:

SELECT NOW() < '100', NOW() < '!';

the results are 0.

Upvotes: 0

Related Questions