Bug #102873 implicit cast WHERE datetime_column = 0 not producing Incorrect DATETIME value

• Submitted: 9 Mar 2021 7:23
• Modified: 9 Mar 2021 8:52
• Reporter: Nikolai Ikhalainen
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.23
• OS: Any
• CPU Architecture: Any

[9 Mar 2021 7:23] Nikolai Ikhalainen

Description:
If NO_ZERO_IN_DATE,NO_ZERO_DATE are enabled zero date in DML where condition works inconsistently for 0 and '0000-00-00 00:00:00'

How to repeat:
set session sql_mode='';
create table t(id int auto_Increment primary key, d datetime);
insert into t (d) values('0000-00-00 00:00:00');

In new connection:
mysql> select @@version,@@sql_mode\G
*************************** 1. row ***************************
 @@version: 8.0.22-13
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set d=NOW() where d= 0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.28 sec)

mysql> update t set d=NOW() where d= '0000-00-00 00:00:00';
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'd' at row 1

Same for select queries:
mysql> select d = 0 from t;
+-------+
| d = 0 |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

mysql> select * from t where d = 0;
+----+---------------------+
| id | d                   |
+----+---------------------+
|  1 | 0000-00-00 00:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

mysql> select * from t where d = '0000-00-00 00:00:00';
ERROR 1525 (HY000): Incorrect DATETIME value: '0000-00-00 00:00:00'

Suggested fix:
Document implicit cast case or produce same error for datetime_column

[9 Mar 2021 8:52] MySQL Verification Team

Hello Nikolai,

Thank you for the report and feedback.

Thanks,
Umesh