Bug #106567 DATA/DATATIME type cover other values type in Item_func_in
Submitted: 25 Feb 3:18 Modified: 3 Jun 20:53
Reporter: hao chen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8027, 8.0, 5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: date, datetime, IN

[25 Feb 3:18] hao chen
Description:
We discovery DATA/DATATIME will cover other value's type in value_list.

How to repeat:
CREATE TABLE t ( pk INTEGER PRIMARY KEY, col_date date DEFAULT NULL,col_datetime datetime DEFAULT NULL, col_varchar VARCHAR(1) DEFAULT NULL, KEY (col_varchar) );

INSERT INTO t VALUES(5, now(),now(), 'I');

 select * from t;
+----+------------+---------------------+-------------+
| pk | col_date   | col_datetime        | col_varchar |
+----+------------+---------------------+-------------+
|  5 | 2022-02-25 | 2022-02-25 11:01:14 | I           |
+----+------------+---------------------+-------------+
1 row in set (0.00 sec)

SELECT * FROM t wHERE pk = 5 AND (col_datetime, col_varchar) IN (('2022-02-25 11:01:14', 'Y'), (92, 'W'));
+----+------------+---------------------+-------------+
| pk | col_date   | col_datetime        | col_varchar |
+----+------------+---------------------+-------------+
|  5 | 2022-02-25 | 2022-02-25 11:01:14 | I           |
+----+------------+---------------------+-------------+
1 row in set, 4 warnings (0.00 sec)

show warnings;
+---------+------+-------------------------------------------------------------------+
| Level   | Code | Message                                                           |
+---------+------+-------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: 'Y' for column 'col_datetime' at row 1  |
| Warning | 1292 | Incorrect datetime value: '92' for column 'col_datetime' at row 1 |
| Warning | 1292 | Incorrect datetime value: 'W' for column 'col_datetime' at row 1  |
| Warning | 1292 | Incorrect datetime value: 'I' for column 'col_datetime' at row 1  |
+---------+------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)

SELECT * FROM t wHERE pk = 5 AND (col_date, col_varchar) IN (('2022-02-25', 'Y'), (92, 'W'));
+----+------------+---------------------+-------------+
| pk | col_date   | col_datetime        | col_varchar |
+----+------------+---------------------+-------------+
|  5 | 2022-02-25 | 2022-02-25 11:01:14 | I           |
+----+------------+---------------------+-------------+
1 row in set, 4 warnings (0.00 sec)

show warnings;
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Incorrect date value: 'Y' for column 'col_date' at row 1  |
| Warning | 1292 | Incorrect date value: '92' for column 'col_date' at row 1 |
| Warning | 1292 | Incorrect date value: 'W' for column 'col_date' at row 1  |
| Warning | 1292 | Incorrect date value: 'I' for column 'col_date' at row 1  |
+---------+------+-----------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM t wHERE pk = 5 AND (col_varchar,col_date) IN (('Y','2022-02-25'), ('W',92));
Empty set, 1 warning (0.00 sec)

mysql> SELECT * FROM t wHERE pk = 5 AND (col_varchar,col_date) IN (('I','2022-02-25'), ('W',92));
+----+------------+---------------------+-------------+
| pk | col_date   | col_datetime        | col_varchar |
+----+------------+---------------------+-------------+
|  5 | 2022-02-25 | 2022-02-25 11:01:14 | I           |
+----+------------+---------------------+-------------+
1 row in set, 1 warning (0.00 sec)

Suggested fix:
We find that the string `y` is judged as data type or datatime type,and the condition 'col_varchar' does not work in `in` function. So,these sqls return the wrong result.
However, if we change the order of the datetime/date column and varchar column, we can get the right result.

By reading the code, i find the flag of datetime_found in function Item_func_in::resolve_type covers other types if the cmp_type is ROW_RESULT. 
In this function all variables are handled uniformly with the same array type, can we process these variables one by one? 

Thank you for any advice!
[25 Feb 7:42] MySQL Verification Team
Hello hao chen,

Thank you for the report and test case.
Observed that issue is seen in 8.0/5.7 builds.

regards,
Umesh
[26 Feb 3:54] huahua xu
Hi hao chen,

I think you are right, and it may be a bug.

The local variable `datetime_found` should be reset to false at each iteration in the loop `for (uint col = 0; col < cols; col++) { ...}`
[3 Jun 20:53] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL 8.0.30 release, and here's the proposed changelog entry from the documentation team:

Comparator functions for queries like ( ) IN ((val1, val2), (val3, val4),
.…) could return the wrong results.

Thank you for the bug report.