Description:
This is either a bug or an omission in the documentation. If a date expressed as a numeric unquoted literal is used in an expression without first assigning it to a datetime column or casting it to datetime, the expression does not evaluate correctly.
It appears in this case the literal is converted to some other intermediate type (int? decimal?) and the expression is somehow evaluated by comparing a datetime with that intermediate type; yet from what I can find, comparison of datetime and non-datetime types is not documented.
The docs explain how unquoted literals can be used for date/datetime values:
http://dev.mysql.com/doc/refman/5.5/en/datetime.html:
Values specified as numbers should be 6, 8, 12, or 14 digits long. If a number is 8 or 14 digits long, it is assumed to be in YYYYMMDD or YYYYMMDDHHMMSS format and that the year is given by the first 4 digits. If the number is 6 or 12 digits long, it is assumed to be in YYMMDD or YYMMDDHHMMSS format and that the year is given by the first 2 digits. Numbers that are not one of these lengths are interpreted as though padded with leading zeros to the closest length.
Values specified as nondelimited strings are interpreted using their length as given. If the string is 8 or 14 characters long, the year is assumed to be given by the first 4 characters. Otherwise, the year is assumed to be given by the first 2 characters. The string is interpreted from left to right to find year, month, day, hour, minute, and second values, for as many parts as are present in the string. This means you should not use strings that have fewer than 6 characters. For example, if you specify '9903', thinking that represents March, 1999, MySQL inserts a “zero” date value into your table. This occurs because the year and month values are 99 and 03, but the day part is completely missing, so the value is not a legal date. However, you can explicitly specify a value of zero to represent missing month or day parts. For example, you can use '990300' to insert the value '1999-03-00'.
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html:
MySQL gives warnings or errors if you try to insert an illegal date.
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-values.html
Date and time values can be represented as quoted strings or as numbers, depending on the exact type of the value and other factors.
How to repeat:
mysql> select NOW() > 20120101;
+------------------+
| NOW() > 20120101 |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> select NOW() > '20120101';
+--------------------+
| NOW() > '20120101' |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
mysql> select now() > CAST(20120101 AS DATETIME);
+------------------------------------+
| now() > CAST(20120101 AS DATETIME) |
+------------------------------------+
| 0 |
+------------------------------------+
1 row in set (0.00 sec)
Suggested fix:
If a datetime is tested for comparison or equality with an unquoted numeric literal, an attempt to implicitly convert the literal to a datetime should be attempted in the manner that the documentation explains, or an error thrown if the value is not convertible.
Furthermore, attempts to compare types that are not implicitly convertible should throw an error:
mysql> select 'dog' > 2;
+-----------+
| 'dog' > 2 |
+-----------+
| 0 |
+-----------+
1 row in set, 1 warning (0.00 sec)
mysql> select '2011-05-02 23:12:02' > 'cat';
+-------------------------------+
| '2011-05-02 23:12:02' > 'cat' |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (0.00 sec)
Alternately I would recommend a language extension for date literals, like:
SELECT NOW() > #20120101#;
