Description:
INTERSECT queries return different results depending on:
1. Whether `block_nested_loop` optimizer switch is enabled
2. Whether a redundant WHERE clause is present
How to repeat:
CREATE TABLE t62 (
c1 MEDIUMBLOB NULL,
c2 MEDIUMTEXT CHARACTER SET ASCII,
PRIMARY KEY (c2(3))
);
CREATE TABLE t63 (
c1 DECIMAL(20,0) UNIQUE
);
INSERT INTO t63 (c1) VALUES (-56850516), (-423045439110148), (243963924330621);
INSERT INTO t62 (c1,c2) VALUES
('gZRwb3Gff017Aty','Y9QdrTnb1qc9HWtgp4wBDq9'),
('r6m8ihHPU9xlDaBEdOeF0kPVxR','7DCLHupgThhGY'),
('MAfQNO1M','REF2');
### Test 1: No WHERE clause
(SELECT ta1.ca2 AS ca1
FROM t63
JOIN (SELECT c2 AS ca2 FROM t62 GROUP BY c2) AS ta1)
INTERSECT
(SELECT t63.c1 AS ca3
FROM (SELECT c2 AS ca4 FROM t62) AS ta2
LEFT JOIN t63 ON FALSE);
+-------------------------+
| ca1 |
+-------------------------+
| Y9QdrTnb1qc9HWtgp4wBDq9 |
+-------------------------+
1 row in set (0.002 sec)
(SELECT ta1.ca2 AS ca1
FROM t63
JOIN (SELECT c2 AS ca2 FROM t62 WHERE !(c1 IS NULL) GROUP BY c2) AS ta1)
INTERSECT
(SELECT t63.c1 AS ca3
FROM (SELECT c2 AS ca4 FROM t62) AS ta2
LEFT JOIN t63 ON FALSE);
+---------------+
| ca1 |
+---------------+
| 7DCLHupgThhGY |
+---------------+
1 row in set, 1 warning (0.003 sec)
### Test 2: BNL enabled/disabled
SET SESSION optimizer_switch='block_nested_loop=off';
(SELECT ta1.ca2 AS ca1
FROM t63
JOIN (SELECT c2 AS ca2 FROM t62 WHERE !(c1 IS NULL) GROUP BY c2) AS ta1)
INTERSECT
(SELECT t63.c1 AS ca3
FROM (SELECT c2 AS ca4 FROM t62) AS ta2
LEFT JOIN t63 ON FALSE);
+-------------------------+
| ca1 |
+-------------------------+
| Y9QdrTnb1qc9HWtgp4wBDq9 |
+-------------------------+
1 row in set, 1 warning (0.004 sec)
SET SESSION optimizer_switch='block_nested_loop=on';
(SELECT ta1.ca2 AS ca1
FROM t63
JOIN (SELECT c2 AS ca2 FROM t62 WHERE !(c1 IS NULL) GROUP BY c2) AS ta1)
INTERSECT
(SELECT t63.c1 AS ca3
FROM (SELECT c2 AS ca4 FROM t62) AS ta2
LEFT JOIN t63 ON FALSE);
+---------------+
| ca1 |
+---------------+
| 7DCLHupgThhGY |
+---------------+
1 row in set, 1 warning (0.002 sec)
Description: INTERSECT queries return different results depending on: 1. Whether `block_nested_loop` optimizer switch is enabled 2. Whether a redundant WHERE clause is present How to repeat: CREATE TABLE t62 ( c1 MEDIUMBLOB NULL, c2 MEDIUMTEXT CHARACTER SET ASCII, PRIMARY KEY (c2(3)) ); CREATE TABLE t63 ( c1 DECIMAL(20,0) UNIQUE ); INSERT INTO t63 (c1) VALUES (-56850516), (-423045439110148), (243963924330621); INSERT INTO t62 (c1,c2) VALUES ('gZRwb3Gff017Aty','Y9QdrTnb1qc9HWtgp4wBDq9'), ('r6m8ihHPU9xlDaBEdOeF0kPVxR','7DCLHupgThhGY'), ('MAfQNO1M','REF2'); ### Test 1: No WHERE clause (SELECT ta1.ca2 AS ca1 FROM t63 JOIN (SELECT c2 AS ca2 FROM t62 GROUP BY c2) AS ta1) INTERSECT (SELECT t63.c1 AS ca3 FROM (SELECT c2 AS ca4 FROM t62) AS ta2 LEFT JOIN t63 ON FALSE); +-------------------------+ | ca1 | +-------------------------+ | Y9QdrTnb1qc9HWtgp4wBDq9 | +-------------------------+ 1 row in set (0.002 sec) (SELECT ta1.ca2 AS ca1 FROM t63 JOIN (SELECT c2 AS ca2 FROM t62 WHERE !(c1 IS NULL) GROUP BY c2) AS ta1) INTERSECT (SELECT t63.c1 AS ca3 FROM (SELECT c2 AS ca4 FROM t62) AS ta2 LEFT JOIN t63 ON FALSE); +---------------+ | ca1 | +---------------+ | 7DCLHupgThhGY | +---------------+ 1 row in set, 1 warning (0.003 sec) ### Test 2: BNL enabled/disabled SET SESSION optimizer_switch='block_nested_loop=off'; (SELECT ta1.ca2 AS ca1 FROM t63 JOIN (SELECT c2 AS ca2 FROM t62 WHERE !(c1 IS NULL) GROUP BY c2) AS ta1) INTERSECT (SELECT t63.c1 AS ca3 FROM (SELECT c2 AS ca4 FROM t62) AS ta2 LEFT JOIN t63 ON FALSE); +-------------------------+ | ca1 | +-------------------------+ | Y9QdrTnb1qc9HWtgp4wBDq9 | +-------------------------+ 1 row in set, 1 warning (0.004 sec) SET SESSION optimizer_switch='block_nested_loop=on'; (SELECT ta1.ca2 AS ca1 FROM t63 JOIN (SELECT c2 AS ca2 FROM t62 WHERE !(c1 IS NULL) GROUP BY c2) AS ta1) INTERSECT (SELECT t63.c1 AS ca3 FROM (SELECT c2 AS ca4 FROM t62) AS ta2 LEFT JOIN t63 ON FALSE); +---------------+ | ca1 | +---------------+ | 7DCLHupgThhGY | +---------------+ 1 row in set, 1 warning (0.002 sec)