Bug #107337 IN() does not work correctly for TIME column
Submitted: 19 May 6:08 Modified: 19 May 7:06
Reporter: Xie Tanner Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.27, 5.7, 8.0.29 OS:Linux
Assigned to: CPU Architecture:ARM

[19 May 6:08] Xie Tanner
Description:
The result shows nothing even if a value is matched in IN().

From the 8.0 documentation(https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_in):

> To comply with the SQL standard, IN() returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

How to repeat:
mysql> create table t (a time);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values ('08:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t where a in ('08:00:00', null);
Empty set (0.00 sec)

mysql> select * from t where a in ('08:00:00', '');
Empty set (0.00 sec)

mysql> select * from t where a in ('08:00:00', '07:59:59');
+----------+
| a        |
+----------+
| 08:00:00 |
+----------+
1 row in set (0.01 sec)

Suggested fix:
The expected result is

mysql> select * from t where a in ('08:00:00', null);
+----------+
| a        |
+----------+
| 08:00:00 |
+----------+

mysql> select * from t where a in ('08:00:00', '');
+----------+
| a        |
+----------+
| 08:00:00 |
+----------+

mysql> select * from t where a in ('08:00:00', '07:59:59');
+----------+
| a        |
+----------+
| 08:00:00 |
+----------+
1 row in set (0.01 sec)
[19 May 7:06] MySQL Verification Team
Hello Xie Tanner,

Thank you for the report and test case.
Verified as described with 8.0.29 build.

regards,
Umesh
[19 May 7:08] MySQL Verification Team
Bug #107338 marked as duplicate of this one
[23 May 8:40] Dag Wanvik
Posted by developer:
 
A workaround is to use a time literal instead of a string:

mysql> select * from t where a in (TIME'08:00:00', null);
+----------+
| a        |
+----------+
| 08:00:00 |
+----------+
[23 May 13:32] Dag Wanvik
Posted by developer:
 
Please disregard the patch. This issue will be fixed in WL#14109.