MySQL Bugs: #113508: Incorrect result for 'or where datetime is null'

Bug #113508	Incorrect result for 'or where datetime is null'
Submitted:	25 Dec 2023 8:10	Modified:	10 Jan 2024 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

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.

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

What is your sql_mode?

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.

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>

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.

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.

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.