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

• Submitted: 19 May 2022 6:08
• Modified: 19 May 2022 7:06
• Reporter: Xie Tanner
• Status: Verified
• 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.