Bug #121505 Wrong result: EXISTS (semijoin duplicate weedout) over a derived table with INTERSECT ALL / EXCEPT ALL removes the dupli

• Submitted: 14 Oct 13:02
• Reporter: Ann Calla
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 26.7.1
• OS: Ubuntu (24.04)
• CPU Architecture: x86 (x86_64)

[14 Oct 13:02] Ann Calla

Description:
A derived table built with INTERSECT ALL or EXCEPT ALL contains duplicate rows. When the outer query filters it with an EXISTS subquery and the semijoin is executed with duplicate weedout, the result has only one row per distinct value of the derived table. The EXISTS condition is true for every row, so all rows should be returned.

In the script the derived table has four rows with the value 1. Without the EXISTS the count is 4. With the EXISTS it is 1. The same happens for EXCEPT ALL.

The result is correct (4) with the NO_SEMIJOIN hint, with UNION ALL instead of INTERSECT ALL, and when EXISTS is replaced by an inner join. It is still wrong with optimizer_switch='hash_set_operations=off' and with internal_tmp_mem_storage_engine=MEMORY.

EXPLAIN FORMAT=TREE shows "Remove duplicate s rows using temporary table (weedout)" above "Intersect all materialize". The weedout seems to treat the copies of a row that the set operation produces as the same row.

Reproduced on 26.7.1 and 9.7.2 with default settings.

How to repeat:
CREATE TABLE a(c0 INT);
CREATE TABLE z(c0 INT);
INSERT INTO a VALUES (1),(1),(1),(1);
INSERT INTO z VALUES (0);

SELECT COUNT(*) FROM (SELECT c0 FROM a INTERSECT ALL SELECT c0 FROM a) s;
-- 4 (correct)

SELECT COUNT(*) FROM (SELECT c0 FROM a INTERSECT ALL SELECT c0 FROM a) s
WHERE EXISTS (SELECT 1 FROM z WHERE s.c0 > z.c0);
-- expected: 4
-- actual:   1

SELECT COUNT(*) FROM (SELECT c0 FROM a EXCEPT ALL SELECT c0 FROM z) s
WHERE EXISTS (SELECT 1 FROM z WHERE s.c0 > z.c0);
-- expected: 4
-- actual:   1

SELECT COUNT(*) FROM (SELECT c0 FROM a INTERSECT ALL SELECT c0 FROM a) s
WHERE EXISTS (SELECT /*+ NO_SEMIJOIN() */ 1 FROM z WHERE s.c0 > z.c0);
-- 4 (correct)

SELECT COUNT(*) FROM (SELECT c0 FROM a UNION ALL SELECT c0 FROM a WHERE FALSE) s
WHERE EXISTS (SELECT 1 FROM z WHERE s.c0 > z.c0);
-- 4 (correct)

SELECT COUNT(*) FROM (SELECT c0 FROM a INTERSECT ALL SELECT c0 FROM a) s JOIN z ON s.c0 > z.c0;
-- 4 (correct)

EXPLAIN FORMAT=TREE SELECT COUNT(*) FROM (SELECT c0 FROM a INTERSECT ALL SELECT c0 FROM a) s
WHERE EXISTS (SELECT 1 FROM z WHERE s.c0 > z.c0);
-- -> Aggregate: count(0)
--     -> Remove duplicate s rows using temporary table (weedout)
--         -> Filter: (s.c0 > z.c0)
--             -> Inner hash join (no condition)
--                 -> Table scan on s
--                     -> Intersect all materialize
--                         -> Table scan on a
--                         -> Table scan on a
--                 -> Hash
--                     -> Table scan on z