Bug #112453 Incorrect result

• Submitted: 25 Sep 2023 7:42
• Modified: 25 Sep 2023 11:28
• Reporter: Doris Li
• Status: Can't repeat
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.28
• OS: Any
• CPU Architecture: Any

[25 Sep 2023 7:42] Doris Li

Description:
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)

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.01 sec)

How to repeat:
CREATE TABLE IF NOT EXISTS apple(time DECIMAL);
INSERT INTO apple (time)VALUES(NULL), (1980-10-06), (NULL),(NULL),(1910-10-01);
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)

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.01 sec)

Suggested fix:
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:28] 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.