Null datetime is not compatible with MySQL

[qw4990]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

CREATE TABLE t1 (a datetime not null);
insert ignore into t1 values (0);
select * from t1 where a is null;

CREATE TABLE t2 (a date not null);
insert ignore into t2 values (0);
select * from t2 where a is null;

2. What did you expect to see?
   In MySQL

mysql> CREATE TABLE t1 (a datetime not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert ignore into t1 values (0);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t1 where a is null;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.01 sec)

3. What did you see instead?
   In TiDB

mysql> CREATE TABLE t1 (a datetime not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert ignore into t1 values (0);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1 where a is null;
Empty set (0.01 sec)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.0-beta-227-g06183393e-dirty
Git Commit Hash: 06183393ed536aa248248b72d4bf9f5010947f66
Git Branch: master
UTC Build Time: 2019-03-15 08:22:53
GoVersion: go version go1.12 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @qw4990

[zhuozl]

This problem should be solved at the "TIKV" layer ???

MySQL [test2]> explain select * from t1 where a is null;
+---------------------+-------+------+-------------------------------------------------------------+
| id                  | count | task | operator info                                               |
+---------------------+-------+------+-------------------------------------------------------------+
| TableReader_7       | 0.00  | root | data:Selection_6                                            |
| └─Selection_6       | 0.00  | cop  | isnull(test2.t1.a)                                          |
|   └─TableScan_5     | 1.00  | cop  | table:t1, range:[-inf,+inf], keep order:false, stats:pseudo |
+---------------------+-------+------+-------------------------------------------------------------+
3 rows in set (0.00 sec)

[qw4990]

After investigation, the reason is that TiDB's builtin function ScalarFuncSig_TimeIsNull is not compatible with MySQL.

As MySQL's documents describe:

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"

And TiDB doesn't check if these DATETIME columns are declared as NOT NULL.

[ghost]

Confirming this issue still exists in master:

DROP TABLE IF EXISTS t1, t2;

CREATE TABLE t1 (a datetime not null);
insert ignore into t1 values (0);
select * from t1 where a is null;

CREATE TABLE t2 (a date not null);
insert ignore into t2 values (0);
select * from t2 where a is null;

..


mysql> select * from t1 where a is null;
Empty set (0.01 sec)

mysql> 
mysql> CREATE TABLE t2 (a date not null);
Query OK, 0 rows affected (0.09 sec)

mysql> insert ignore into t2 values (0);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t2 where a is null;
Empty set (0.00 sec)

mysql> select tidb_version()
    -> \G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-750-g8a661044c
Edition: Community
Git Commit Hash: 8a661044cedf8daad1de4fbf79a390962b6f6c3b
Git Branch: master
UTC Build Time: 2020-07-10 10:52:37
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[ghost]

I am going to close this as a duplicate of #10485

There is a discussion in this issue which shows some of the nuances of MySQL's behavior. I don't think it all makes sense, so it is categorized as a feature request and not a bug. Feedback welcome of course if there are known applications that are impacted. Thanks :-)

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Duplicate of #10485