| Bug #95416 | ZERO Date is both NULL and NOT NULL | ||
|---|---|---|---|
| Submitted: | 20 May 2019 0:33 | Modified: | 20 May 2019 7:00 |
| Reporter: | Morgan Tocker | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.7.25,8.0.15, 5.6.44, 5.7.26, 8.0.16 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 May 2019 0:36]
Morgan Tocker
See also this test-case:
mysql [localhost:5725] {msandbox} (test) > select a is null, a is not null from t1;
+-----------+---------------+
| a is null | a is not null |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
1 row in set (0.00 sec)
[20 May 2019 5:17]
MySQL Verification Team
Hello Morgan, Thank you for the report and test case. regards, Umesh
[20 May 2019 5:23]
MySQL Verification Team
- Found legacy issue Bug #940 zero date is both null and not null in where clause Leaving it for Dev's to take a call on this, if this is not going to be fixed then maybe document as Morgan suggested? Thank you.
[20 May 2019 6:57]
Tsubasa Tanaka
I found this in documentation. --- For DATE and DATETIME columns that are declared as NOT NULL, you can find the special date '0000-00-00' by using a statement like this: SELECT * FROM tbl_name WHERE date_column IS NULL This is needed to get some ODBC applications to work because ODBC does not support a '0000-00-00' date value. --- https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_is-null
[20 May 2019 7:00]
Morgan Tocker
Thanks Tanaka-san, I read this, but I didn't understand it on first pass :-) This explains that the feature is intended.
Description: I was not aware of this behavior previously, and it is not clearly documented as such: a ZERO date compares to both NULL and NOT NULL. How to repeat: drop table if exists t1; create table t1(a date not null); insert ignore into t1 values (0); select * from t1 where a is null; select * from t1 where a is not null; mysql [localhost:5725] {msandbox} (test) > create table t1(a date not null); Query OK, 0 rows affected (0.01 sec) mysql [localhost:5725] {msandbox} (test) > insert ignore into t1 values (0); Query OK, 1 row affected, 1 warning (0.00 sec) mysql [localhost:5725] {msandbox} (test) > select * from t1 where a is null; +------------+ | a | +------------+ | 0000-00-00 | +------------+ 1 row in set (0.00 sec) mysql [localhost:5725] {msandbox} (test) > select * from t1 where a is not null; +------------+ | a | +------------+ | 0000-00-00 | +------------+ 1 row in set (0.00 sec) Confirmed in MySQL 5.7.25 and 8.0.15. SQL mode was the default: mysql [localhost:5725] {msandbox} (test) > select @@sql_mode; +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Suggested fix: If this intentional, please recategorize as a documentation bug. But it seems like a very strange behavior. I am hoping it is a bug, and the zero date is one of NULL or NOT NULL.