Bug #106533 | The time type cannot be handled in the `in` statement | ||
---|---|---|---|
Submitted: | 22 Feb 2022 2:05 | Modified: | 22 Feb 2022 7:33 |
Reporter: | hao chen | Email Updates: | |
Status: | Verified | 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:05]
hao chen
[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.