zero date `IS NULL` is different from MySQL #10485

imtbkcat · 2019-05-15T08:54:12Z

Bug Report

Please answer these questions before submitting your issue. Thanks!

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

drop table if exists t1;
create table t1 (a datetime not null);
set sql_mode='';
insert into t1 values ('0000-00-00 00:00:00');
select a FROM t1 WHERE a is null;
select a FROM t1 WHERE a is not null;

SELECT CAST("0000-00-00" as date) IS NULL, CAST("0000-00-00" as date) IS NOT NULL;

What did you expect to see?
In MySQL:

mysql [localhost:8022] {msandbox} (test) > select a FROM t1 WHERE a is null;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql [localhost:8022] {msandbox} (test) > select a FROM t1 WHERE a is not null;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql [localhost:8022] {msandbox} (test) > 
mysql [localhost:8022] {msandbox} (test) > SELECT CAST("0000-00-00" as date) IS NULL, CAST("0000-00-00" as date) IS NOT NULL;
+------------------------------------+----------------------------------------+
| CAST("0000-00-00" as date) IS NULL | CAST("0000-00-00" as date) IS NOT NULL |
+------------------------------------+----------------------------------------+
|                                  0 |                                      1 |
+------------------------------------+----------------------------------------+
1 row in set (0.00 sec)

What did you see instead?
in TiDB:

mysql> select a FROM t1 WHERE a is null;
Empty set (0.00 sec)

mysql> select a FROM t1 WHERE a is not null;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)


mysql> SELECT CAST("0000-00-00" as date) IS NULL, CAST("0000-00-00" as date) IS NOT NULL;
+------------------------------------+----------------------------------------+
| CAST("0000-00-00" as date) IS NULL | CAST("0000-00-00" as date) IS NOT NULL |
+------------------------------------+----------------------------------------+
|                                  0 |                                      1 |
+------------------------------------+----------------------------------------+
1 row in set (0.00 sec)

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

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-1585-ga426a0e5f
Edition: Community
Git Commit Hash: a426a0e5ff45300bed9b7e569d2004c3401ec401
Git Branch: master
UTC Build Time: 2020-11-17 10:37:06
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)

The text was updated successfully, but these errors were encountered:

zz-jason · 2019-05-15T08:56:49Z

@imtbkcat Could you post the sql modes used in mysql and tidb respectively?

zz-jason · 2019-05-15T08:58:03Z

guess there is a sql mode that regards zero time values as null..

imtbkcat · 2019-05-15T09:02:38Z

@zz-jason

TiDB SQL mode:

mysql> 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)

MySQL sql mode:

mysql> 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_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

spongedu · 2019-05-19T07:03:50Z

This issue is nothing to do with SQL_MODE, I think. According to MySQL Manual, INSERT IGNORE just undone the effect of struct mode:

If this mode and strict mode are enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

I think the difference lays on whether date 0000-00-00 is treated as NULL. In MySQL:

mysql> set @@sql_mode="";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1(a date not null);                                                                                                                                                                    Query OK, 0 rows affected (0.03 sec)

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

mysql> select * from t1;
+------------+
| a          |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

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

mysql> select * from t1 where a = '0000-00-00';
+------------+
| a          |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

In TIDB:

mysql> set @@sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(a date 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;
+------------+
| a          |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.01 sec)

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

mysql> select * from t1 where a = '0000-00-00';
+------------+
| a          |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

MySQL seems treat date 0000-00-00 as null, while TiDB not.

@zz-jason @imtbkcat

spongedu · 2019-05-19T08:47:15Z

MySQL's behavior is interesting......
in MySQL:

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

mysql>
mysql> select a, a is null, a is not null from t1;
+------------+-----------+---------------+
| a          | a is null | a is not null |
+------------+-----------+---------------+
| 0000-00-00 |         0 |             1 |
+------------+-----------+---------------+
1 row in set (0.00 sec)

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

mysql> select * from t1 where a = '0000-00-00';
+------------+
| a          |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

mysql> select * from t1 where a != '0000-00-00';
Empty set (0.00 sec)

I tend to treat MySQL's behavior as a bug. I think a value can't be NULL and NOT NULL at the same time, unless there's a specific explain in MySQL document. @zz-jason @imtbkcat

zz-jason · 2019-05-19T08:58:29Z

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

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

conclusion 1: 0000-00-00 is null and not null

mysql> select a, a is null, a is not null from t1;
+------------+-----------+---------------+
| a          | a is null | a is not null |
+------------+-----------+---------------+
| 0000-00-00 |         0 |             1 |
+------------+-----------+---------------+
1 row in set (0.00 sec)

conclusion 2: 0000-00-00 is not null, it violates conclusion 1 😂

@morgo How do you think? What's the reasonable behavior?

spongedu · 2019-05-19T09:25:59Z

😂

morgo · 2019-05-19T17:55:10Z

@morgo How do you think? What's the reasonable behavior?

I am as surprised as you. I tend to agree with @spongedu that we should call this behavior a bug. I might do some research between MySQL versions and file a bug report, and then we can see how it is handled.

morgo · 2019-05-20T00:34:52Z

Reported as https://bugs.mysql.com/bug.php?id=95416 - let's wait a few days to see if there is an insightful answer :-)

morgo · 2019-05-20T07:19:37Z

Update: This is confirmed as intentional (and documented) behavior since some ODBC drivers do not allow to select for NULL(?)

I think because it is intended, we may need to support it as well.

spongedu · 2019-05-20T07:31:17Z

@morgo yeah... I just read the issue details... its tricky indeed.. since it's a feature other than a bug, maybe we need to support it ....

lysu · 2019-05-20T07:35:43Z

I meet some issue in update test too 🤣

#10538

mysql add a test to fix this bug https://bugs.mysql.com/bug.php?id=14186

if test case is create table t1(a date) will make different storys.

ghost · 2020-08-03T20:56:38Z

Confirming this is still an issue:

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

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

mysql> delete from t1 where a is null;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-870-g2a8b96845
Edition: Community
Git Commit Hash: 2a8b968453520e4fcf9d6ff46c9f23b4ad23feee
Git Branch: master
UTC Build Time: 2020-07-31 08:45:35
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 · 2020-11-19T02:23:37Z

I have updated the description to make the testcase clearer. There are a couple of different issues here:

The zero date compares to both IS NULL and IS NOT NULL in a WHERE clause on a table, but not without a table. MySQL says this behavior is intentional :-)
The zero date in TiDB compares as NOT NULL. In MySQL it compares as IS NULL.

How to fix these issues is not clear to me, since the MySQL behavior does not really make sense. It is also not really easy to compare to other database impliementations, since the "zero date" is a MySQL-ism.

imtbkcat added the type/compatibility label May 15, 2019

ghost added the priority/P4 Minor issue, awaiting more evidence before prioritizing label Aug 12, 2020

SunRunAway added the type/bug The issue is confirmed as a bug. label Oct 19, 2020

SunRunAway added the severity/major label Nov 11, 2020

jebter added the sig/execution SIG execution label Nov 16, 2020

ichn-hu mentioned this issue Nov 16, 2020

Welcome to contribute #20804

Closed

ghost changed the title ~~IS NULL is different from MySQL~~ zero date IS NULL is different from MySQL Nov 19, 2020

seiya-annie added type/feature-request Categorizes issue or PR as related to a new feature. severity/moderate and removed type/bug The issue is confirmed as a bug. severity/major labels Nov 19, 2020

ghost mentioned this issue Nov 19, 2020

Null datetime is not compatible with MySQL #9763

Closed

ghost mentioned this issue Nov 20, 2020

Different behave between MySQL and TiDB in query #21167

Open

XuHuaiyu removed the severity/moderate label Feb 19, 2021

morgo mentioned this issue Dec 2, 2021

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

Open

gengliqi mentioned this issue Mar 3, 2023

cast real as datetime is not compatible with tidb/mysql pingcap/tiflash#3832

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

zero date `IS NULL` is different from MySQL #10485

zero date `IS NULL` is different from MySQL #10485

imtbkcat commented May 15, 2019 •

edited by ghost

Loading

zz-jason commented May 15, 2019

zz-jason commented May 15, 2019

imtbkcat commented May 15, 2019

spongedu commented May 19, 2019

spongedu commented May 19, 2019 •

edited

Loading

zz-jason commented May 19, 2019

spongedu commented May 19, 2019

morgo commented May 19, 2019

morgo commented May 20, 2019

morgo commented May 20, 2019

spongedu commented May 20, 2019 •

edited

Loading

lysu commented May 20, 2019 •

edited

Loading

ghost commented Aug 3, 2020

ghost commented Nov 19, 2020 •

edited by ghost

Loading

zero date IS NULL is different from MySQL #10485

zero date IS NULL is different from MySQL #10485

Comments

imtbkcat commented May 15, 2019 • edited by ghost Loading

Bug Report

zz-jason commented May 15, 2019

zz-jason commented May 15, 2019

imtbkcat commented May 15, 2019

spongedu commented May 19, 2019

spongedu commented May 19, 2019 • edited Loading

zz-jason commented May 19, 2019

spongedu commented May 19, 2019

morgo commented May 19, 2019

morgo commented May 20, 2019

morgo commented May 20, 2019

spongedu commented May 20, 2019 • edited Loading

lysu commented May 20, 2019 • edited Loading

ghost commented Aug 3, 2020

ghost commented Nov 19, 2020 • edited by ghost Loading

zero date `IS NULL` is different from MySQL #10485

zero date `IS NULL` is different from MySQL #10485

imtbkcat commented May 15, 2019 •

edited by ghost

Loading

spongedu commented May 19, 2019 •

edited

Loading

spongedu commented May 20, 2019 •

edited

Loading

lysu commented May 20, 2019 •

edited

Loading

ghost commented Nov 19, 2020 •

edited by ghost

Loading