Description:
This bug demonstrates a logical inconsistency in MySQL's INTERSECT operation, where (LEFT JOIN) INTERSECT (RIGHT JOIN) returns a row despite there being no actual intersection between the two result sets, due to improper NULL comparison semantics. Specifically, while the LEFT JOIN returns (0, NULL, NULL, NULL) and the RIGHT JOIN returns (NULL, NULL, NULL, NULL), MySQL incorrectly considers these NULL-filled rows as matching in the INTERSECT operation because NULL comparisons in the ON clause (t0.c1 > t1.c1 where both are NULL) yield NULL/UNKNOWN, causing both outer joins to preserve their rows with all-NULL right/left sides respectively, and MySQL's INTERSECT implementation appears to treat NULLs as equal for intersection purposes despite SQL's three-valued logic where NULL ≠ NULL.
How to repeat:
CREATE TABLE t0(c0 FLOAT PRIMARY KEY, c1 FLOAT);
CREATE TABLE t1(c0 FLOAT, c1 FLOAT);
INSERT INTO t0(c0) VALUES(0);
INSERT INTO t1(c0) VALUES(NULL);
SELECT * FROM t0 LEFT JOIN t1 ON t0.c1 > t1.c1 INTERSECT SELECT * FROM t0 RIGHT JOIN t1 ON t0.c1 > t1.c1; -- expect: empty set
+----+------+------+------+
| c0 | c1 | c0 | c1 |
+----+------+------+------+
| 0 | NULL | NULL | NULL |
+----+------+------+------+
1 row in set (0.000 sec)
SELECT * FROM t0 LEFT JOIN t1 ON t0.c1 > t1.c1;
+----+------+------+------+
| c0 | c1 | c0 | c1 |
+----+------+------+------+
| 0 | NULL | NULL | NULL |
+----+------+------+------+
1 row in set (0.000 sec)
SELECT * FROM t0 RIGHT JOIN t1 ON t0.c1 > t1.c1;
+------+------+------+------+
| c0 | c1 | c0 | c1 |
+------+------+------+------+
| NULL | NULL | NULL | NULL |
+------+------+------+------+
1 row in set (0.000 sec)
