| Bug #113368 | IN() has a problem handling when the expression on the left is of type time | ||
|---|---|---|---|
| Submitted: | 8 Dec 2023 3:10 | Modified: | 8 Dec 2023 7:59 |
| Reporter: | M Ling | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.7.44-log, 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | in() | ||
[8 Dec 2023 7:59]
MySQL Verification Team
Hello M Ling, Thank you for the report and test case. regards, Umesh
Description: IN() returns NULL when the expression on the left is of type time and a match is found in the list but one of the expressions in the list is NULL, but it should actually return 1. How to repeat: 1、CREATE TABLE TEST_TMP(id INT, time_col TIME); 2、INSERT INTO TEST_TMP VALUES(1, '09:43:26'); 3、SELECT time_col IN('09:43:26', null), time_col IN('09:43:26') FROM TEST_TMP; 4、The expected result is (1,1), which is actually (NULL, 1) 5、Based on "https://dev.mysql.com/doc/refman/5.7/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. " , I think it's a bug