Bug #113508 Incorrect result for 'or where datetime is null'
Submitted: 25 Dec 2023 8:10 Modified: 10 Jan 3:38
Reporter: Shota Suzuki Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0 OS:Linux
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: incorrect result

[25 Dec 2023 8:10] Shota Suzuki
Description:
Hello, I found a test case which causes mysql produce incorrect result. The behavior is as follows

How to repeat:
mysql> create table t0 (c0 int, c1 datetime not null);
mysql> insert into t0 values (1, '0000-00-00 00:00:00');
imysql> nsert into t0 values (2, '0000-00-00 00:00:00');

c1 does not allow nulls in the datetime type, so special dates can be referenced with is null.

mysql> select * from t0 where c1 is null;
+------+---------------------+
| c0   | c1                  |
+------+---------------------+
|    1 | 0000-00-00 00:00:00 |
|    2 | 0000-00-00 00:00:00 |
+------+---------------------+

However, if you do that with an or condition, you won't be able to reference it.
select * from t0 where c0 = 1 or c1 is null;
+------+---------------------+
| c0   | c1                  |
+------+---------------------+
|    1 | 0000-00-00 00:00:00 |
+------+---------------------+

When I run similar sql in mysql5 the result is below, with different results.
+------+---------------------+
| c0   | c1                  |
+------+---------------------+
|    1 | 0000-00-00 00:00:00 |
|    2 | 0000-00-00 00:00:00 |
+------+---------------------+

I think the execution results with mysql5 are correct and the execution results with mysql8 are incorrect.
[27 Dec 2023 2:07] huahua xu
For DATE and DATETIME columns defined as NOT NULL, "date_notnull IS NULL" has to be modified to "date_notnull == 0", but it also causes problems as it's not SQL-compliant. Thus, the optimizer has added some constraints to disable the above behaviour in the commit: https://github.com/mysql/mysql-server/commit/dc80b26d9097bea487e58efa90a1a859ef99f2fc
[27 Dec 2023 15:45] MySQL Verification Team
What is your sql_mode?
[28 Dec 2023 2:02] Shota Suzuki
Hi huahua xu.
What you presented seems different from my problem.
Thanks.

Hi MySQL Verification Team.
The sql mode is below.

mysql> show variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+

Thanks.
[28 Dec 2023 2:40] MySQL Verification Team
Hi,

Thank you for the report. This is behavior voiding SQL standard (sql_mode should prevent invalid dates for start) so I'm not sure if for 8.x we would consider this a bug but I'm verifying it so that sustaining team can give final decision on this.

Thanks

mysql> set sql_mode="";
Query OK, 0 rows affected (0.00 sec)

mysql> set session sql_mode="";
Query OK, 0 rows affected (0.00 sec)

mysql> create table t0 (c0 int, c1 datetime not null);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t0 values (1, '0000-00-00 00:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t0 values (2, '0000-00-00 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t0;
+------+---------------------+
| c0   | c1                  |
+------+---------------------+
|    1 | 0000-00-00 00:00:00 |
|    2 | 0000-00-00 00:00:00 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> select * from t0 where c1 is null;
+------+---------------------+
| c0   | c1                  |
+------+---------------------+
|    1 | 0000-00-00 00:00:00 |
|    2 | 0000-00-00 00:00:00 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> select * from t0 where c0 = 1 or c1 is null;
+------+---------------------+
| c0   | c1                  |
+------+---------------------+
|    1 | 0000-00-00 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.2.0     |
+-----------+
1 row in set (0.00 sec)

mysql>
[2 Jan 12:31] Roy Lyseng
This is not a bug.

This is a special handling that is triggered only for the where condition

  "where c1 is null".

It is not triggered if the IS NULL predicate is combined with other predicates in the condition.

This is indeed a change from 5.7, but it was made to ensure that clients use this exact syntax when dealing with the non-standard zero date values.
[10 Jan 3:38] Shota Suzuki
Hi Roy Lyseng.

I see.
Is it possible to make this behave the same as sql mode mysql5.7?
Please let me know if you know.
Thank you so much.
[11 Jan 7:55] Roy Lyseng
This special syntax was introduced for the purpose of dealing with temporal "zero" values from connectors, and should not be exploited by regular code.
The change was made to clean up MySQL semantics.

We recommend that the special temporal "zero" value be used explicitly, and only when it is absolutely needed, when writing regular SQL code.