Bug #111409 Incorrect result when select time col use "WHERE IN (NULL,'xx:xx:xx.xxxxxx')"

• Submitted: 14 Jun 2023 3:40
• Modified: 29 Sep 16:33
• Reporter: Will Chen
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[14 Jun 2023 3:40] Will Chen

Description:
When time col use "WHERE IN (NULL,'xx:xx:xx.xxxxxx') return empty set, but the result of "WHERE IN ('xx:xx:xx.xxxxxx') is not empty, I think ('xx:xx:xx.xxxxxx') is a subset of (NULL,'xx:xx:xx.xxxxxx'), so the result of less element should be a subset of more element.

mysql> select * from time_bug;
+----+-----------------+
| id | time            |
+----+-----------------+
|  1 | NULL            |
|  2 | NULL            |
|  3 | 00:00:00.000000 |
|  4 | 00:00:00.000000 |
+----+-----------------+
4 rows in set (0.00 sec)

mysql> SELECT count(*) from time_bug where time in ('00:00:00.000000', NULL) ORDER BY 1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) from time_bug where time in ('00:00:00.000000') ORDER BY 1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) from time_bug where time in ('00:00:00.000000', NULL);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) from time_bug where time in ('00:00:00.000000');
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

I try varchar type, the result is as expected, it will filter null value, and keep not null value, but time type just filter all.

mysql> show create table time_bug;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                     |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| time_bug | CREATE TABLE `time_bug` (
  `id` int NOT NULL AUTO_INCREMENT,
  `time_char` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from time_bug;
+----+-----------------+
| id | time_char       |
+----+-----------------+
|  1 | NULL            |
|  2 | NULL            |
|  3 | 00:00:00.000000 |
|  4 | 00:00:00.000000 |
+----+-----------------+
4 rows in set (0.00 sec)

mysql> SELECT count(*) from time_bug where time_char in ('00:00:00.000000', NULL);
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT count(*) from time_bug where time_char in ('00:00:00.000000');
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

How to repeat:
CREATE TABLE `time_bug` (
  `id` int NOT NULL AUTO_INCREMENT,
  `time` time(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO time_bug (id) VALUES (1);
INSERT INTO time_bug (id) VALUES (2);
INSERT INTO time_bug (time) VALUES ('00:00:00.000000');
INSERT INTO time_bug (time) VALUES ('00:00:00.000000');

SELECT count(*) from time_bug where time in ('00:00:00.000000', NULL) ORDER BY 1;
SELECT count(*) from time_bug where time in ('00:00:00.000000') ORDER BY 1;
SELECT count(*) from time_bug where time in ('00:00:00.000000', NULL);
SELECT count(*) from time_bug where time in ('00:00:00.000000');

Suggested fix:
myabe time type should react same as varchar type?

[14 Jun 2023 12:42] MySQL Verification Team

Hi Mr. Chen,

Thank you for your bug report.

We have repeated it with 8.0.33:

+----------+
| count(*) |
+----------+
|        0 |
+----------+
+----------+
| count(*) |
+----------+
|        2 |
+----------+
+----------+
| count(*) |
+----------+
|        0 |
+----------+
+----------+
| count(*) |
+----------+
|        2 |
+----------+

We agree with your conclusions.

Verified as reported.

[29 Sep 16:33] Edward Gilmore

Posted by developer:
 
Added the following note to the MySQL Server 8.0.44, 8.4.7, and 9.5.0 release notes:
	
Using WHERE IN (NULL,'xx:xx:xx.xxxxxx') on a TIME column returned an empty
set, whereas using WHERE IN ('xx:xx:xx.xxxxxx') returned the expected results.