Description:
When both c0 and c1 are defined as PRIMARY KEYs, the following SQL query produces incorrect results for the INTERSECT operation:
SELECT * FROM t1 LEFT JOIN t0 ON t0.c0 IS NULL INTERSECT SELECT * FROM t1 RIGHT JOIN t0 ON t0.c0 IS NULL;
How to repeat:
-- case 1. When both c0 and c1 are PRIMARY KEYs, INTERSECT gets wrong result
DROP TABLE IF EXISTS t0,t1;
CREATE TABLE t0(c0 FLOAT PRIMARY KEY);
CREATE TABLE t1(c1 INT PRIMARY KEY );
INSERT IGNORE INTO t0(c0) VALUES(0);
INSERT INTO t1(c1) VALUES(1);
SELECT * FROM t1 INNER JOIN t0 ON t0.c0 IS NULL;
Empty set;
SELECT * FROM t1 LEFT JOIN t0 ON t0.c0 IS NULL INTERSECT SELECT * FROM t1 RIGHT JOIN t0 ON t0.c0 IS NULL; --incorrect result
+----+----+
| c1 | c0 |
+----+----+
| 1 | 0 |
+----+----+
-- case 2. When c0 or c1 is not PRIMARY KEY, INTERSECT gets right result
DROP TABLE IF EXISTS t0,t1;
CREATE TABLE t0(c0 FLOAT); -- c0 is not PRIMARY KEY
CREATE TABLE t1(c1 INT PRIMARY KEY);
INSERT IGNORE INTO t0(c0) VALUES(0);
INSERT INTO t1(c1) VALUES(1);
SELECT * FROM t1 INNER JOIN t0 ON t0.c0 IS NULL;
Empty set;
SELECT * FROM t1 LEFT JOIN t0 ON t0.c0 IS NULL INTERSECT SELECT * FROM t1 RIGHT JOIN t0 ON t0.c0 IS NULL; --correct result
Empty set;
