Bug #95416 ZERO Date is both NULL and NOT NULL

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.

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)

Hello Morgan,

Thank you for the report and test case.

regards,
Umesh

- 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.

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

Thanks Tanaka-san,

I read this, but I didn't understand it on first pass :-) This explains that the feature is intended.