TiDB is not treating '0000-00-00' date and '0000-00-00 00:00:00' as NULL

[ramanich1]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t1,t2;
CREATE TABLE t1 (a date  NOT NULL, b datetime NOT NULL);
INSERT IGNORE INTO t1 VALUES ('0000-00-00' ,'0000-00-00 00:00:00');
SELECT * FROM t1 WHERE a IS NULL and b IS NULL;

2. What did you expect to see? (Required)

mysql> SELECT * FROM t1 WHERE a IS NULL and b IS NULL;
+------------+---------------------+
| a          | b                   |
+------------+---------------------+
| 0000-00-00 | 0000-00-00 00:00:00 |
+------------+---------------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

mysql> SELECT * FROM t1 WHERE a IS NULL and b IS NULL;
Empty set (0.00 sec)

4. What is your TiDB version? (Required)

| Release Version: v5.4.0-alpha-264-g6efa36df6
Edition: Community
Git Commit Hash: 6efa36df6cff325106f67ecfe3d79816ba37ca6a
Git Branch: master
UTC Build Time: 2021-11-29 16:57:51
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[mjonss]

I would claim that this is rather a bug in MySQL that we should not be bug compatible with, since only NULL should match IS NULL

MySQL gives this weird result:

mysql> SELECT * FROM t1 WHERE a IS NOT NULL and b IS NOT NULL AND a IS NULL and
b IS NULL;
+------------+---------------------+
| a          | b                   |
+------------+---------------------+
| 0000-00-00 | 0000-00-00 00:00:00 |
+------------+---------------------+
1 row in set (0,00 sec)

But apparently it is intended for ODBC compatibility:
https://bugs.mysql.com/bug.php?id=95416
@morgo what is your opinion on this? Should we also compare ZERO DATE/DATETIME with both NULL and NOT NULL?

[morgo]

I would claim that this is rather a bug in MySQL that we should not be bug compatible with, since only NULL should match IS NULL

It's not a bug if it is documented and intentional :( Even if I have the same reaction as you 🤮

But apparently it is intended for ODBC compatibility: https://bugs.mysql.com/bug.php?id=95416 @morgo what is your opinion on this? Should we also compare ZERO DATE/DATETIME with both NULL and NOT NULL?

This is a duplicate of #10485

My suggestion is: we close this as a duplicate, but leave the other issue open as a feature request so that users have something to refer to and request it be added. If we identify an application as affected, we can look at adding support for it.