| Bug #106533 | The time type cannot be handled in the `in` statement | ||
|---|---|---|---|
| Submitted: | 22 Feb 2022 2:05 | Modified: | 11 Dec 23:07 |
| Reporter: | hao chen | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.25, 5.7,8.0 | OS: | Any (rhel 7.4) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | IN, time | ||
[22 Feb 2022 2:40]
hao chen
mysql> select * from t1 where t2 in('19:20:20','19:19:10',NULL);
Empty set (1.93 sec)
mysql> select * from t1 where t2='19:20:20' or t2='19:19:10' or t2=NULL;
+----+---------------------+------------+----------+
| id | t | t1 | t2 |
+----+---------------------+------------+----------+
| 1 | 2022-02-21 19:19:10 | 2022-02-21 | 19:19:10 |
| 3 | NULL | NULL | 19:20:20 |
+----+---------------------+------------+----------+
2 rows in set (0.00 sec)
mysql> select * from t1 where t2 in('19:20:20','19:19:10');
+----+---------------------+------------+----------+
| id | t | t1 | t2 |
+----+---------------------+------------+----------+
| 1 | 2022-02-21 19:19:10 | 2022-02-21 | 19:19:10 |
| 3 | NULL | NULL | 19:20:20 |
+----+---------------------+------------+----------+
2 rows in set (1.93 sec)
If there is no null field, the result is correct.
[22 Feb 2022 7:33]
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
[25 Feb 2022 13:52]
huahua xu
The constant value `NULL` caused the adjustment of the comparison type to fail, which should be from STRING_RESULT to INT_RESULT. The convert_constant_item function replaces the PTI_text_literal_text_string with Item_time_with_ref, and it will not be rolled back when adjust STRING_RESULT to INT_RESULT failed. Item_time_with_ref will return numeric string when building string vetor by STRING_RESULT later.
[11 Dec 23:07]
Roy Lyseng
Closed as duplicate of 107337.
Description: We discovery time type can't return right result in `in` condition. How to repeat: create table t1(id int primary key, t datetime,t1 date,t2 time); Query OK, 0 rows affected (0.02 sec) insert into t1 values(1,now(),now(),now()); Query OK, 1 row affected, 1 warning (0.00 sec) insert into t1 values(2,now(),now(),now()); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t1; +----+---------------------+------------+----------+ | id | t | t1 | t2 | +----+---------------------+------------+----------+ | 1 | 2022-02-14 17:32:24 | 2022-02-14 | 17:32:24 | | 2 | 2022-02-14 17:32:27 | 2022-02-14 | 17:32:27 | +----+---------------------+------------+----------+ 2 rows in set (0.00 sec) mysql> select * from t1 where t in('2022-02-14 17:32:24',NULL); +----+---------------------+------------+----------+ | id | t | t1 | t2 | +----+---------------------+------------+----------+ | 1 | 2022-02-14 17:32:24 | 2022-02-14 | 17:32:24 | +----+---------------------+------------+----------+ 1 row in set (0.00 sec) mysql> select * from t1 where t1 in('2022-02-14',NULL); +----+---------------------+------------+----------+ | id | t | t1 | t2 | +----+---------------------+------------+----------+ | 1 | 2022-02-14 17:32:24 | 2022-02-14 | 17:32:24 | | 2 | 2022-02-14 17:32:27 | 2022-02-14 | 17:32:27 | +----+---------------------+------------+----------+ 2 rows in set (0.00 sec) mysql> select * from t1 where t2 in('17:32:24',NULL); Empty set (0.00 sec) Other function like "between ... and ..." can return right result: mysql> select * from t1 where t between '2022-02-21 19:19:09' and '2022-02-21 19:19:19'; +----+---------------------+------------+----------+ | id | t | t1 | t2 | +----+---------------------+------------+----------+ | 1 | 2022-02-21 19:19:10 | 2022-02-21 | 19:19:10 | +----+---------------------+------------+----------+ Suggested fix: Other function like "between ... and ..." can return right result: select * from t1 where t between '2022-02-21 19:19:09' and '2022-02-21 19:19:19'; +----+---------------------+------------+----------+ | id | t | t1 | t2 | +----+---------------------+------------+----------+ | 1 | 2022-02-21 19:19:10 | 2022-02-21 | 19:19:10 | +----+---------------------+------------+----------+ select * from t1 where t2 between '19:19:09' and '19:19:21'; +----+---------------------+------------+----------+ | id | t | t1 | t2 | +----+---------------------+------------+----------+ | 1 | 2022-02-21 19:19:10 | 2022-02-21 | 19:19:10 | | 2 | 2022-02-21 19:19:20 | 2022-02-21 | 19:19:20 | +----+---------------------+------------+----------+ The Item_func_in::resolve_type function only does special processing for the datetime and date type but ignores the time type. The '19:19:09' string be converted into int in tem_func_in::resolve_type function: /*IN must compare INT columns and constants as int values ......*/ ...... if (convert_constant_item(thd, field_item, &arg[0], &converted)) return true; The converted is true which seems unreasonable. Thanks for any advice!