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:
None 
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
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!
[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.