inconsistent result with MySQL for comparing datetime with invalid value

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.

In TiDB:

mysql> create table t(col_datetime datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values('1994-02-05 05:41:38');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT (col_datetime <= 'EWJ77aX7t') AS res FROM t;
+-----+
| res |
+-----+
| NULL |
+-----+
1 row in set, 1 warning (0.00 sec)

while in MySQL:

mysql> create table t(col_datetime datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values('1994-02-05 05:41:38');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT (col_datetime <= 'EWJ77aX7t') AS res FROM t;
+------+
| res  |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

2. What did you expect to see?

Same result.

3. What did you see instead?

Different result.

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

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0-alpha-702-gf12403e
Git Commit Hash: f12403ef08625ad5c58a406b53ea11005cbebc58
Git Branch: master
UTC Build Time: 2019-10-24 07:46:37
GoVersion: go version go1.13.1 linux/amd64
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @XuHuaiyu