Bug #112454 Incorrect result
Submitted: 25 Sep 2023 7:50 Modified: 25 Sep 2023 11:26
Reporter: Doris Li Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.28 OS:Any
Assigned to: CPU Architecture:Any

[25 Sep 2023 7:50] Doris Li
Description:
These two sql queries are the same, but they return different results.

SET optimizer_switch='semijoin=on';
SELECT apple.time FROM apple WHERE apple.time IN (SELECT apple.time FROM apple WHERE (apple.time NOT IN (SELECT apple.time FROM apple WHERE apple.time )) = (apple.time) );
+------+
| time |
+------+
| 1964 |
| 1899 |
+------+
2 rows in set (0.00 sec)

SET optimizer_switch='semijoin=off';
SELECT apple.time FROM apple WHERE apple.time IN (SELECT apple.time FROM apple WHERE (apple.time NOT IN (SELECT apple.time FROM apple WHERE apple.time )) = (apple.time) );
Empty set (0.00 sec)

How to repeat:
CREATE TABLE IF NOT EXISTS apple(time float);
INSERT HIGH_PRIORITY INTO apple(time) VALUES(NULL), (1980-10-06), (NULL);
INSERT INTO apple(time) VALUES(NULL);
INSERT DELAYED INTO apple(time) VALUES(1910-10-01);

SET optimizer_switch='semijoin=on';
SELECT apple.time FROM apple WHERE apple.time IN (SELECT apple.time FROM apple WHERE (apple.time NOT IN (SELECT apple.time FROM apple WHERE apple.time )) = (apple.time) );
+------+
| time |
+------+
| 1964 |
| 1899 |
+------+
2 rows in set (0.00 sec)

SET optimizer_switch='semijoin=off';
SELECT apple.time FROM apple WHERE apple.time IN (SELECT apple.time FROM apple WHERE (apple.time NOT IN (SELECT apple.time FROM apple WHERE apple.time )) = (apple.time) );
Empty set (0.00 sec)

I run my sql test on containers from docker. I pull the docker images from docker hub, and run the sql above on the docker container.
[25 Sep 2023 11:26] MySQL Verification Team
Hi Mrs. Li,

Thank you very much for your bug report.

However, we were not able to repeat it with 8.0.34 and latest 8.1.

We get empty results with both settings for the semi-join.

Can't repeat.