inconsistent result with MySQL for comparing datetime with invalid value

[eurekaka]

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

[eurekaka]

Another interesting finding related to this problem:
in TiDB:

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

mysql> prepare stmt2 from "SELECT (col_datetime <= ?) AS res FROM t";
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt2 using @p0;
+-----+
| res |
+-----+
| NULL |
+-----+
1 row in set, 1 warning (0.00 sec)

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

while in MySQL:

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

mysql> prepare stmt2 from "SELECT (col_datetime <= ?) AS res FROM t";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt2 using @p0;
+------+
| res  |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

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

Looks like MySQL's behavior is inconsistent with itself when using user defined variables.

[ghost]

MySQL 8.0 has a different behavior here (and is at least consistent between both examples):

mysql [localhost:8021] {msandbox} (test) > execute stmt2 using @p0;
ERROR 1525 (HY000): Incorrect DATETIME value: 'EWJ77aX7t'
mysql [localhost:8021] {msandbox} (test) > SELECT (col_datetime <= @p0) AS res FROM t;
ERROR 1525 (HY000): Incorrect DATETIME value: 'EWJ77aX7t'

It might be easiest to follow MySQL 8.0 behavior here, since MySQL 5.7 is inconsistent?

[zhangysh1995]

Related to #17868. Actually I also tested the test case of the issue with DATETIME and TIMESTAMP, the results were incorrect at the time.

[blueseason]

/pick-up

[ti-challenge-bot]

Pick up success.

[blueseason]

test in mysql 8.0:

case1:
mysql> SELECT (col_datetime <= 'EWJ77aX7t') AS res FROM t;
ERROR 1525 (HY000): Incorrect DATETIME value: 'EWJ77aX7t'
case2:
mysql> SELECT (col_datetime <= @p0) AS res FROM t;
+------+
| res |
+------+
| 0 |
+-----+
row in set, 1 warning (0.00 sec)
case 3:
mysql> execute stmt2 using @p0;
+------+
| res |
+------+
| NULL |
+-----+
row in set, 1 warning (0.00 sec)

the related doc from mysql
1.Query Cast Injection in.8.0 Reference Manual feature

Query cast injection. In version 8.0.18 and later, MySQL injects cast operations into the query item tree inside expressions and conditions in which the data type of the argument and the expected data type do not match.
...
When comparing a string type to a DATETIME or TIMESTAMP value, the string is cast is to DATETIME; when comparing a string type with DATE, the string is cast to DATE.

2. and the 8.0 reference manual

When you compare a DATE, TIME, DATETIME, or TIMESTAMP to a constant string with the <, <=, =, >=, >, or BETWEEN operators, MySQL normally converts the string to an internal long integer for faster comparison (and also for a bit more “relaxed” string checking). However, this conversion is subject to the following exceptions:

• When you compare two columns
• When you compare a DATE, TIME, DATETIME, or TIMESTAMP column to an expression
• When you use any comparison method other than those just listed, such as IN or STRCMP().
  For those exceptions, the comparison is done by converting the objects to strings and performing a string comparison

some analysis:

• case 1: try to convert constant string to datetime, and return a error
• case 2: use the variable to store the string, and so compare datatime with an expression, do the string comparion.
• case 3: guessed that the constant string converted to null in the execute statement.

[ti-challenge-bot]

@blueseason You did not submit PR within 7 days, so give up automatically.

[wjhuang2016]

I think it's a compatibility problem. MySQL would return an error, while TiDB returns a warning.
According to the Query Cast Injection in.8.0. The result should be the same as the explicit cast. This is, return a warning instead of an error.
It's hard to be fully compatible with MySQL. I'm not sure if we need to be compatible with MySQL here.

[XuHuaiyu]

@wjhuang2016 Please discuss the conclusion with @jebter