MySQL Bugs: #108998: or expression containing is null for datetime column returns wrong results

Bug #108998	or expression containing is null for datetime column returns wrong results
Submitted:	4 Nov 2022 3:37	Modified:	25 Nov 2022 3:45
Reporter:	Allen Iverson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0.31 8.0.29 8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	datetime, null, wrong result, zero date

Description:
when querying a datetime column defined as not null which containing "zero" datetime 0000-00-00 00:00:00, "zero" datetime is returned when where condition is (datetime is null) , whereas "zero" datetime is not returned when where condition is  (datetime is null or datetime > SOMETIME)

refer to document, [“Zero” date or time values used through Connector/ODBC are converted automatically to NULL because ODBC cannot handle such values.](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html)

query with (datetime is null or datetime > SOMETIME) returns wrong results.

How to repeat:
mysql> show variables like '%sql_mode%';
+---------------+--------------------------------------------------------------+
| Variable_name | Value                                                        |
+---------------+--------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------------------------+

CREATE TABLE `test_null` (`id` int NOT NULL,`ftime` datetime NOT NULL) ENGINE=InnoDB;

insert into test_null values (1,'0000-00-00 00:00:00');

insert into test_null values (2, '2022-11-04 10:00:00');

1. "zero" datetime returned , querying with `ftime is null`.
mysql> select * from test_null where ftime is null;
+----+---------------------+
| id | ftime               |
+----+---------------------+
|  1 | 0000-00-00 00:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

2. normal datetime returned , querying with `ftime > '2022-11-04 00:00:00'`.
mysql> select * from test_null where ftime > '2022-11-04 00:00:00';
+----+---------------------+
| id | ftime               |
+----+---------------------+
|  2 | 2022-11-04 10:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

3. "zero" datetime NOT returned , querying with OR expression.
mysql> select * from test_null where ftime > '2022-11-04 00:00:00' or ftime is null;
+----+---------------------+
| id | ftime               |
+----+---------------------+
|  2 | 2022-11-04 10:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

Suggested fix:
`ftime > '2022-11-04 00:00:00' or ftime is null` should return values union of `ftime >'2022-11-04 00:00:00'` and `ftime is null`

Hi Mr. Iverson,

You are using some old MySQL version, since the new version behaves correctly !!!!

I'm using MySQL 8.0.29, has it been fixed in 8.0.30？

This issue still exists in mysql 8.0.31, please reopen the bug.

---

mysql [localhost:8031] {msandbox} (test) > select version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost:8031] {msandbox} (test) > show variables like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql [localhost:8031] {msandbox} (test) > CREATE TABLE `test_null` (`id` int NOT NULL,`ftime` datetime NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql [localhost:8031] {msandbox} (test) > insert into test_null values (1,'0000-00-00 00:00:00');
Query OK, 1 row affected (0.01 sec)

mysql [localhost:8031] {msandbox} (test) > insert into test_null values (2, '2022-11-04 10:00:00');
Query OK, 1 row affected (0.02 sec)

mysql [localhost:8031] {msandbox} (test) > select * from test_null where ftime is null;
+----+---------------------+
| id | ftime               |
+----+---------------------+
|  1 | 0000-00-00 00:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

mysql [localhost:8031] {msandbox} (test) > select * from test_null where ftime > '2022-11-04 00:00:00';
+----+---------------------+
| id | ftime               |
+----+---------------------+
|  2 | 2022-11-04 10:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

mysql [localhost:8031] {msandbox} (test) > select * from test_null where ftime > '2022-11-04 00:00:00' or ftime is null;
+----+---------------------+
| id | ftime               |
+----+---------------------+
|  2 | 2022-11-04 10:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

Hi Mr. Iverson,

We were not able to reproduce it with 8.0.29 nor with 8.0.31. 

That only means that you have misconfigured some settings in our server.

Hi,

For the starters, your sql_mode is wrong.

You should use the default settings.

Hi,

You should use the default sql_mode, which is :

 ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE,  ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION.

Only then you should get expected behaviour.

Hi Mr. Iverson,

We suggest that you read our Reference Manual.

According to SQL standards, there can be no DATETIME domain with all zeroes. According to the same standards,  all zeroes are then treated as NULL.

We are not discussing whether zero be treated as NULL, but why the or expression returns the wrong result in MySQL 8.0.

If all zeroes are then treated as NULL, `ftime > '2022-11-04 00:00:00' or ftime is null` should return both zero and time after '2022-11-04 00:00:00'.

As already explained, second condition is fully satisfied.