Bug #107337 IN() does not work correctly for TIME column

• Submitted: 19 May 2022 6:08
• Modified: 29 Sep 16:24
• Reporter: Xie Tanner
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.27, 5.7, 8.0.29, 8.0.32
• OS: Linux
• CPU Architecture: ARM

[19 May 2022 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 2022 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 2022 7:08] MySQL Verification Team

Bug #107338 marked as duplicate of this one

[23 May 2022 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 2022 13:32] Dag Wanvik

Posted by developer:
 
Please disregard the patch. This issue will be fixed in WL#14109.

[29 Sep 16:24] Edward Gilmore

Posted by developer:
 
Added the following note to the MySQL Server 9.5.0 release notes:
	
Using the IN operator with TIME columns could return an empty set when a value 
is matched if the list contains NULL or empty strings.