Bug #103337 DATE and DATETIME camparison with CHAR throws ERROR
Submitted: 15 Apr 2021 19:45 Modified: 16 Apr 2021 19:04
Reporter: Eimantas Jatkonis Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:8.0.21, 8.0.23 OS:Linux
Assigned to: CPU Architecture:Any

[15 Apr 2021 19:45] Eimantas Jatkonis
Description:
DATE or DATETIME (ex. NOW()) comparison with CHAR (ex. '44233') throws ERROR

In 5.5, 5.6, 5.7 this wasn't problem
Tested sql_mode posibilities, no solution found.

Even sql_mode = ALLOW_INVALID_DATES, does not solve.

Expected behaviour: warning.
This is how ir was on 5.X
mysql> SELECT DATE(NOW()) < '44233';
+-----------------------+
| DATE(NOW()) < '44233' |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT NOW() < '44233';
+-----------------+
| NOW() < '44233' |
+-----------------+
|               1 |
+-----------------+
1 row in set, 1 warning (0.00 sec)

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

How to repeat:
mysql> SELECT DATE(NOW()) < '44233';
ERROR 1525 (HY000): Incorrect DATE value: '44233'

mysql> SELECT NOW() < '44233';
ERROR 1525 (HY000): Incorrect DATETIME value: '44233'

mysql> show variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | ALLOW_INVALID_DATES |
+---------------+---------------------+
1 row in set (0.00 sec)
[16 Apr 2021 7:36] Roy Lyseng
This is not a bug.
It was a problem that was fixed in https://bugs.mysql.com/bug.php?id=93513
[16 Apr 2021 19:04] Eimantas Jatkonis
Your mentioned problem has other kind of problem, as there is no month with 32 days.
This is kind similar to mine, because CHAR to DATE conversion is required.

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.