zegab zegab - 1 year ago 67
SQL Question

mysql threats 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?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download