MySQL Bugs: #93513: Unexpected behaviour for date comparison with constant strings

Bug #93513	Unexpected behaviour for date comparison with constant strings
Submitted:	6 Dec 2018 21:44	Modified:	13 Feb 2019 20:15
Reporter:	Catalin Besleaga	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.15	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When comparing DATEs with constant strings, MySQL first tries to convert the string to date and do the comparison as DATETIME. In case the conversion fails, MySQL will still execute the comparison but as string which is unexpected as it's unlikely what the user intended(and can easily cover mistakes).

How to repeat:
CREATE TABLE t1(d DATE);
INSERT INTO t1 VALUES('2011-02-20');
SELECT * FROM t1 WHERE d <= '2013-02-32';
d
2011-02-20
Warnings:
Warning	1292	Incorrect date value: '2013-02-32' for column 'd' at row 1

Suggested fix:
Return error instead of doing the comparison:
ERROR HY000: Incorrect DATE value: '2013-02-32'

Documented fix as follows in the MySQL 8.0.16 changelog:

    When comparing DATE values with constant strings, MySQL first
    tries to convert the string to a DATE and then to perform the
    comparison. When the conversion failed, MySQL executed the
    comparison treating the DATE as a string. Now in such cases, if
    the conversion of the string to a DATE fails, the comparison
    fails with ER_WRONG_VALUE.

Closed.

See also BUG#95466.

Data type conversions is "warning" scope, not "error". In MySQL unconvertable values becomes 0 or NULL. Like "ABC" * 1 = 0

BUG#93513 solution was not required, AND it is inconsistent.
example with INT to DATE conversion still produces warnings, not errors.

mysql>  SELECT DATE(123456);
+--------------+
| DATE(123456) |
+--------------+
| NULL         |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------+
| Level   | Code | Message                            |
+---------+------+------------------------------------+
| Warning | 1292 | Incorrect datetime value: '123456' |
+---------+------+------------------------------------+
1 row in set (0.00 sec)

BUG#93513 proposed solution required for one user, and it is not configurable with sql_mode. This is not OK.

"Data type conversions is "warning" scope, not "error". In MySQL unconvertable values becomes 0 or NULL. Like "ABC" * 1 = 0"

This is not generally true. Range errors in arithmetic operations cause errors, according to the SQL standard, and the SQL mode cannot change this.

Automatic conversion from e.g, a numeric value to a date value is an extension over standard SQL. In general, we try to implement as if an implicit CAST operation is performed. The CAST operation would cause an error in standard SQL, but we currently allow this deviation.

"BUG#93513 solution was not required, AND it is inconsistent.
example with INT to DATE conversion still produces warnings, not errors.

mysql>  SELECT DATE(123456);
+--------------+
| DATE(123456) |
+--------------+
| NULL         |
+--------------+
1 row in set, 1 warning (0.00 sec)"

The behaviour of DATE is not according to the documentation, which says:

"Extracts the date part of the date or datetime expression expr."

123456 is not a valid date, so technically I think it would be a good idea to return an error in this case. In an SQL function, returning NULL is usually reserved for the cases where an argument is NULL, invalid input usually mandates an error.

The CASE function is one exception where you have greater control over when to return NULL values.

Roy Lyseng - what kind of "Range errors" do you have in mind?

On 8.0.27 checked few more:

mysql>  SELECT DATE(123456);
+--------------+
| DATE(123456) |
+--------------+
| NULL         |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> select CONVERT('160', TIME);
+----------------------+
| CONVERT('160', TIME) |
+----------------------+
| NULL                 |
+----------------------+
1 row in set, 1 warning (0.00 sec)

Tried true range check:

mysql> CREATE TABLE a (i INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO a VALUES (123456789123456879);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'i' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

MySQL in data conversion always was "warnings", please reconsider removing this strange DATE conversion error.
Or at least move it to configurable sql_mode.

It would be important to implement a backwards compatibility setting to deactivate this new behaviour in use cases where it is not desired.

I guess MySQL team should split this discussion in 2 topics. One for invalid date ranges (which is nice to throw an error). Other is about null/empty checking on SELECT...
Systems which support multiple databases are used to do a SELECT with (DATETIME_COLUMN  = '' or DATETIME_COLUMN IS NULL) in WHERE

Please consider providing backward compatibility to handle existing '' values in date/datetime column which were allowed to be inserted as empty string in older versions and produced warning when compared to empty string in prior versions.

We implement range error checks for all arithmetic expressions:

e.g: select 1000000000 * 10000000000;

gives error 1690: BIGINT value is out of range

Similar errors are not always given on temporal values, but should be.

Example: This statement causes a warning only, but should be an error:

select date'9999-01-01' + interval 1 year;

As for comparisons of temporal values against an empty string,
I cannot see that it makes much sense.
E.g Postgres will also report an error in this case.

You may cast the date value to a character string in the comparison, but
the result of the comparison will always be false.

The explanation here is not satisfactory. 
Focusing specifically on the case where date is compared against empty string:
Query
SELECT STR_TO_DATE('','%Y-%m-%d')
Output
0000-00-00

If the STR_TO_DATE function does NOT return NULL, then the system should not throw an error if we 'ALLOW_INVALID_DATES' and DON'T have [NO_ZERO_IN_DATE,NO_ZERO_DATE]. 

Clearly the conversion of '' (empty string) resulted in a valid '0000-00-00' date in system where the correct sql mode is set. There is no reason to break this backward compatibility.