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!
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!