Description:
t0 has 17 rows. "NOT EXISTS (SELECT 1 FROM t0 z WHERE l.c0 > 0 OR TRUE)" is false for every outer row, and alone it correctly returns no rows. Combined with a second, unrelated NOT EXISTS whose WHERE refers only to the outer column ("l.c0 = 1"), the query returns the rows 2 and 3, i.e. the result of the first NOT EXISTS alone.
EXPLAIN FORMAT=TREE shows both subqueries materialized with deduplication on their select list "1". The first anti-join probes <subquery2> with (1 = l.c0). The second anti-join probes <subquery3> with (1 = `<subquery2>`.`1`), a column of the first subquery's materialized table, instead of a value of the outer row. For the outer rows that pass the first anti-join, `<subquery2>`.`1` is NULL, the lookup finds nothing, and the second NOT EXISTS is wrongly true.
Correct results: the second subquery alone, with WHERE TRUE (no reference to l), with the NO_SEMIJOIN hint on the first subquery, and with t0 of 3 rows (no materialization). MariaDB returns no rows. Reproduced on 26.7.1 and 9.7.2.
How to repeat:
CREATE TABLE t0 (c0 INT);
INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16);
CREATE TABLE t1 (c0 INT);
INSERT INTO t1 VALUES (1), (2), (3);
SELECT c0 FROM t1 l WHERE NOT EXISTS (SELECT 1 FROM t0 z WHERE l.c0 > 0 OR TRUE);
-- empty correct
SELECT c0 FROM t1 l WHERE NOT EXISTS (SELECT 1 FROM t0 r WHERE l.c0 = 1);
-- 2, 3 correct
SELECT c0 FROM t1 l WHERE NOT EXISTS (SELECT 1 FROM t0 r WHERE l.c0 = 1)
AND NOT EXISTS (SELECT 1 FROM t0 z WHERE l.c0 > 0 OR TRUE);
-- expected: empty
-- actual: 2, 3
SELECT c0 FROM t1 l WHERE NOT EXISTS (SELECT /*+ NO_SEMIJOIN() */ 1 FROM t0 r WHERE l.c0 = 1)
AND NOT EXISTS (SELECT 1 FROM t0 z WHERE l.c0 > 0 OR TRUE);
-- empty correct
EXPLAIN FORMAT=TREE
SELECT c0 FROM t1 l WHERE NOT EXISTS (SELECT 1 FROM t0 r WHERE l.c0 = 1)
AND NOT EXISTS (SELECT 1 FROM t0 z WHERE l.c0 > 0 OR TRUE);
-- -> Nested loop antijoin
-- -> Nested loop antijoin
-- -> Table scan on l
-- -> Filter: (l.c0 = 1)
-- -> Single-row index lookup on <subquery2> using <auto_distinct_key> (1 = l.c0)
-- -> Materialize with deduplication
-- -> Table scan on r
-- -> Single-row index lookup on <subquery3> using <auto_distinct_key> (1 = `<subquery2>`.`1`)
-- -> Materialize with deduplication
-- -> Table scan on z
Description: t0 has 17 rows. "NOT EXISTS (SELECT 1 FROM t0 z WHERE l.c0 > 0 OR TRUE)" is false for every outer row, and alone it correctly returns no rows. Combined with a second, unrelated NOT EXISTS whose WHERE refers only to the outer column ("l.c0 = 1"), the query returns the rows 2 and 3, i.e. the result of the first NOT EXISTS alone. EXPLAIN FORMAT=TREE shows both subqueries materialized with deduplication on their select list "1". The first anti-join probes <subquery2> with (1 = l.c0). The second anti-join probes <subquery3> with (1 = `<subquery2>`.`1`), a column of the first subquery's materialized table, instead of a value of the outer row. For the outer rows that pass the first anti-join, `<subquery2>`.`1` is NULL, the lookup finds nothing, and the second NOT EXISTS is wrongly true. Correct results: the second subquery alone, with WHERE TRUE (no reference to l), with the NO_SEMIJOIN hint on the first subquery, and with t0 of 3 rows (no materialization). MariaDB returns no rows. Reproduced on 26.7.1 and 9.7.2. How to repeat: CREATE TABLE t0 (c0 INT); INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16); CREATE TABLE t1 (c0 INT); INSERT INTO t1 VALUES (1), (2), (3); SELECT c0 FROM t1 l WHERE NOT EXISTS (SELECT 1 FROM t0 z WHERE l.c0 > 0 OR TRUE); -- empty correct SELECT c0 FROM t1 l WHERE NOT EXISTS (SELECT 1 FROM t0 r WHERE l.c0 = 1); -- 2, 3 correct SELECT c0 FROM t1 l WHERE NOT EXISTS (SELECT 1 FROM t0 r WHERE l.c0 = 1) AND NOT EXISTS (SELECT 1 FROM t0 z WHERE l.c0 > 0 OR TRUE); -- expected: empty -- actual: 2, 3 SELECT c0 FROM t1 l WHERE NOT EXISTS (SELECT /*+ NO_SEMIJOIN() */ 1 FROM t0 r WHERE l.c0 = 1) AND NOT EXISTS (SELECT 1 FROM t0 z WHERE l.c0 > 0 OR TRUE); -- empty correct EXPLAIN FORMAT=TREE SELECT c0 FROM t1 l WHERE NOT EXISTS (SELECT 1 FROM t0 r WHERE l.c0 = 1) AND NOT EXISTS (SELECT 1 FROM t0 z WHERE l.c0 > 0 OR TRUE); -- -> Nested loop antijoin -- -> Nested loop antijoin -- -> Table scan on l -- -> Filter: (l.c0 = 1) -- -> Single-row index lookup on <subquery2> using <auto_distinct_key> (1 = l.c0) -- -> Materialize with deduplication -- -> Table scan on r -- -> Single-row index lookup on <subquery3> using <auto_distinct_key> (1 = `<subquery2>`.`1`) -- -> Materialize with deduplication -- -> Table scan on z