Description:
CREATE TABLE IF NOT EXISTS t0(c0 FLOAT);
CREATE TABLE IF NOT EXISTS t1 LIKE t0;
SET SESSION internal_tmp_mem_storage_engine = MEMORY;
DROP INDEX c0 ON t1;
INSERT INTO t1(c0) VALUES(NULL);
INSERT INTO t1(c0) VALUES(NULL);
INSERT INTO t1(c0) VALUES(1);
INSERT INTO t1(c0) VALUES(2);
UPDATE t1 SET c0=(IF(t1.c0, NULL, t1.c0)) IS FALSE;
UPDATE t1 SET c0=COALESCE((- (t1.c0)), (3) IS TRUE);
INSERT INTO t0(c0) VALUES(NULL);
INSERT INTO t0(c0) VALUES(NULL);
INSERT INTO t0(c0) VALUES("0");
INSERT INTO t0(c0) VALUES(4);
INSERT INTO t0(c0) VALUES(5);
INSERT INTO t0(c0) VALUES(6);
SELECT t0.c0 FROM t0 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0);
+------+
| c0 |
+------+
| NULL |
| NULL |
| 0 |
| 4 |
| 5 |
| 6 |
+------+
SELECT t0.c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0);
+------+
| c0 |
+------+
| 0 |
+------+
SELECT t0.c0 FROM t0;
+------+
| c0 |
+------+
| NULL |
| NULL |
| 0 |
| 4 |
| 5 |
| 6 |
+------+
When using the same join conditions, the output of semi-join and anti-join are contradictory. This indicates that semi join produce wrong results.
How to repeat:
docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:9.1.0
Then execute the above test case.
Description: CREATE TABLE IF NOT EXISTS t0(c0 FLOAT); CREATE TABLE IF NOT EXISTS t1 LIKE t0; SET SESSION internal_tmp_mem_storage_engine = MEMORY; DROP INDEX c0 ON t1; INSERT INTO t1(c0) VALUES(NULL); INSERT INTO t1(c0) VALUES(NULL); INSERT INTO t1(c0) VALUES(1); INSERT INTO t1(c0) VALUES(2); UPDATE t1 SET c0=(IF(t1.c0, NULL, t1.c0)) IS FALSE; UPDATE t1 SET c0=COALESCE((- (t1.c0)), (3) IS TRUE); INSERT INTO t0(c0) VALUES(NULL); INSERT INTO t0(c0) VALUES(NULL); INSERT INTO t0(c0) VALUES("0"); INSERT INTO t0(c0) VALUES(4); INSERT INTO t0(c0) VALUES(5); INSERT INTO t0(c0) VALUES(6); SELECT t0.c0 FROM t0 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0); +------+ | c0 | +------+ | NULL | | NULL | | 0 | | 4 | | 5 | | 6 | +------+ SELECT t0.c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0); +------+ | c0 | +------+ | 0 | +------+ SELECT t0.c0 FROM t0; +------+ | c0 | +------+ | NULL | | NULL | | 0 | | 4 | | 5 | | 6 | +------+ When using the same join conditions, the output of semi-join and anti-join are contradictory. This indicates that semi join produce wrong results. How to repeat: docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:9.1.0 Then execute the above test case.