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.
