Tidb: Different behavior with MySQL when compare date with varchar or text
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
- What did you do?
If possible, provide a recipe for reproducing the error.
CREATE TABLE t(c0 text, c1 varchar(114), c2 timestamp);
INSERT INTO t(`c0`, `c1`, `c2`) VALUES("1919", "514", "0000-00-00 00:00:00"), ("810", "hello world", "2019-01-01 11:11:11");
SELECT * FROM t WHERE c1 = c2;
SELECT * FROM t WHERE c1 > c2;
SELECT * FROM t WHERE c1 < c2;
SELECT * FROM t WHERE c1 != c2;
SELECT * FROM t WHERE c0 = c2;
SELECT * FROM t WHERE c0 > c2;
SELECT * FROM t WHERE c0 < c2;
SELECT * FROM t WHERE c0 != c2;
BTW, when update statement use the same binary operation expression comparing date with varchar or text, the effect rows will be different.
- What did you expect to see?
- MySQL 8.0
mysql> CREATE TABLE t(c0 text, c1 varchar(114), c2 timestamp);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t(`c0`, `c1`, `c2`) VALUES("1919", "514", "0000-00-00 00:00:00"), ("810", "hello world", "201
9-01-01 11:11:11");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t WHERE c1 = c2;
+------+------+---------------------+
| c0 | c1 | c2 |
+------+------+---------------------+
| 1919 | 514 | 0000-00-00 00:00:00 |
+------+------+---------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c1 > c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c1 < c2;
+------+-------------+---------------------+
| c0 | c1 | c2 |
+------+-------------+---------------------+
| 810 | hello world | 2019-01-01 11:11:11 |
+------+-------------+---------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c1 != c2;
+------+-------------+---------------------+
| c0 | c1 | c2 |
+------+-------------+---------------------+
| 810 | hello world | 2019-01-01 11:11:11 |
+------+-------------+---------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c0 = c2;
+------+------+---------------------+
| c0 | c1 | c2 |
+------+------+---------------------+
| 1919 | 514 | 0000-00-00 00:00:00 |
+------+------+---------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c0 > c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c0 < c2;
+------+-------------+---------------------+
| c0 | c1 | c2 |
+------+-------------+---------------------+
| 810 | hello world | 2019-01-01 11:11:11 |
+------+-------------+---------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c0 != c2;
+------+-------------+---------------------+
| c0 | c1 | c2 |
+------+-------------+---------------------+
| 810 | hello world | 2019-01-01 11:11:11 |
+------+-------------+---------------------+
1 row in set, 2 warnings (0.00 sec)
- What did you see instead?
- TiDB
mysql> CREATE TABLE t(c0 text, c1 varchar(114), c2 timestamp);
Query OK, 0 rows affected (0.51 sec)
mysql> INSERT INTO t(`c0`, `c1`, `c2`) VALUES("1919", "514", "0000-00-00 00:00:00"), ("810", "hello world", "201
9-01-01 11:11:11");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t WHERE c1 = c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c1 > c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c1 < c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c1 != c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c0 = c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c0 > c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c0 < c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c0 != c2;
Empty set, 2 warnings (0.00 sec)
- What version of TiDB are you using (
tidb-server -Vor runselect tidb_version();on TiDB)?
Release Version: v4.0.0-alpha-857-gf94eac07d
Git Commit Hash: f94eac07df296ac3c1899c2b77b1ede8a71c7936
Git Branch: master
UTC Build Time: 2019-11-14 03:00:55
GoVersion: go version go1.13 linux/amd64
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
SIG slack channel
Score
300
Mentor
- @lzmhhh123
you06
All 3 comments
Confirming that I can still reproduce this against master. It requires an sql-mode change in order to insert into MySQL, which can be considered another behavior difference.
DROP TABLE IF EXISTS t;
CREATE TABLE t(c0 text, c1 varchar(114), c2 timestamp);
SET sql_mode='';
INSERT INTO t(`c0`, `c1`, `c2`) VALUES("1919", "514", "0000-00-00 00:00:00"), ("810", "hello world", "2019-01-01 11:11:11");
SELECT * FROM t WHERE c1 = c2;
SELECT * FROM t WHERE c1 > c2;
SELECT * FROM t WHERE c1 < c2;
SELECT * FROM t WHERE c1 != c2;
SELECT * FROM t WHERE c0 = c2;
SELECT * FROM t WHERE c0 > c2;
SELECT * FROM t WHERE c0 < c2;
SELECT * FROM t WHERE c0 != c2;
..
mysql> SELECT * FROM t WHERE c1 = c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c1 > c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c1 < c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c1 != c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c0 = c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c0 > c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c0 < c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT * FROM t WHERE c0 != c2;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-782-gb72e47e6d
Edition: Community
Git Commit Hash: b72e47e6db8863c08e47714391cf937b0f1c3269
Git Branch: master
UTC Build Time: 2020-07-15 01:26: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)
nullnotnil
on 17 Jul 2020
Here is an easy way to reproduce it.
In MySQL 8:
mysql> CREATE TABLE t(c0 text, c1 varchar(114), c2 timestamp);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t(`c0`, `c1`, `c2`) VALUES("1919", "514", "0000-00-00 00:00:00"), ("810", "hello world", "2019-01-01 11:11:11");
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
mysql> INSERT ignore INTO t(`c0`, `c1`, `c2`) VALUES("1919", "514", "0000-00-00 00:00:00"), ("810", "hello world", "2019-01-01 11:11:11");
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> select UNIX_TIMESTAMP(c1), c2 from t;
+--------------------+---------------------+
| UNIX_TIMESTAMP(c1) | c2 |
+--------------------+---------------------+
| 0.000000 | 0000-00-00 00:00:00 |
| 0.000000 | 2019-01-01 11:11:11 |
+--------------------+---------------------+
2 rows in set, 2 warnings (0.00 sec)
But in TiDB:
mysql> CREATE TABLE t(c0 text, c1 varchar(114), c2 timestamp);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t(`c0`, `c1`, `c2`) VALUES("1919", "514", "0000-00-00 00:00:00"), ("810", "hello world", "2019-01-01 11:11:11");
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
mysql> INSERT ignore INTO t(`c0`, `c1`, `c2`) VALUES("1919", "514", "0000-00-00 00:00:00"), ("810", "hello world", "2019-01-01 11:11:11");
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 1
tidb(127.0.0.1:4000) > select UNIX_TIMESTAMP(c1), c2 from t;
+--------------------+---------------------+
| UNIX_TIMESTAMP(c1) | c2 |
+--------------------+---------------------+
| NULL | 0000-00-00 00:00:00 |
| NULL | 2019-01-01 11:11:11 |
+--------------------+---------------------+
2 rows in set, 2 warnings (0.00 sec)
The warnings are consistent.
mysql> show warnings;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '514' |
| Warning | 1292 | Incorrect datetime value: 'hello world' |
+---------+------+-----------------------------------------+
2 rows in set (0.00 sec)
lzmhhh123
on 7 Dec 2020
I think it's a bug for mysql. Here is the evidence.
mysql> select cast(c0 as datetime), cast(c1 as datetime), c2 from t;
+----------------------+----------------------+---------------------+
| NULL | NULL | 0000-00-00 00:00:00 |
| NULL | NULL | 2019-01-01 11:11:11 |
+----------------------+----------------------+---------------------+
2 rows in set, 4 warnings (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '1919' |
| Warning | 1292 | Incorrect datetime value: '514' |
| Warning | 1292 | Incorrect datetime value: '810' |
| Warning | 1292 | Incorrect datetime value: 'hello world' |
+---------+------+-----------------------------------------+
4 rows in set (0.00 sec)
When call cast as datetime explicitly to convert the text type. It returns NULL.
But when implicitly convert it in where condition. It has been regarded as 0000-00-00 00:00:00.
mysql> select cast(c0 as datetime), c0, c2 from t where c0 = c2;
+----------------------+------+---------------------+
| cast(c0 as datetime) | c0 | c2 |
+----------------------+------+---------------------+
| NULL | 1919 | 0000-00-00 00:00:00 |
+----------------------+------+---------------------+
1 row in set, 3 warnings (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------+
| Warning | 1292 | Incorrect datetime value: '1919' |
| Warning | 1292 | Incorrect datetime value: '1919' |
| Warning | 1292 | Incorrect datetime value: '810' |
+---------+------+----------------------------------+
3 rows in set (0.00 sec)
👍2
Was this page helpful?
0 / 5 - 0 ratings
Related issues
wwar
路
3Comments
wjhuang2016
路
3Comments
zz-jason
路
3Comments
breeswish
路
3Comments
ekexium
路
3Comments
Most helpful comment
I think it's a bug for mysql. Here is the evidence.
When call
cast as datetimeexplicitly to convert the text type. It returns NULL.But when implicitly convert it in
wherecondition. It has been regarded as0000-00-00 00:00:00.