Description:
With optimizer_switch='hash_set_operations=off', INTERSECT ALL of a 300,000-row table with itself returns 105,439 rows instead of 300,000. The table has 1,000 distinct values with 300 copies each; INTERSECT ALL of a table with itself must return every row.
The result is correct when the temporary table stays in memory: with tmp_table_size = 256M, or with internal_tmp_mem_storage_engine = MEMORY. With the default tmp_table_size (16M) it is correct up to about 160,000 rows and wrong from about 200,000 rows on, whatever the number of distinct values (one value repeated 200,000 times gives 3,441 rows). EXCEPT ALL with the same input is correct.
So the counts seem to be lost when the in-memory TempTable is converted to an on-disk table during the set operation. This is not Bug #121292: that one is in the hash algorithm, and its report states that hash_set_operations=off is correct.
Reproduced on 26.7.1 and 9.7.2 with default settings except hash_set_operations.
How to repeat:
SET SESSION cte_max_recursion_depth = 1000000;
CREATE TABLE t (a INT);
INSERT INTO t WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM r WHERE n < 300000)
SELECT n MOD 1000 FROM r;
SET SESSION optimizer_switch = 'hash_set_operations=off';
SELECT COUNT(*) FROM (SELECT a FROM t INTERSECT ALL SELECT a FROM t) d;
-- expected: 300000
-- actual: 105439
SELECT COUNT(*) FROM (SELECT a FROM t EXCEPT ALL SELECT a FROM t WHERE FALSE) d;
-- 300000 correct
SET SESSION tmp_table_size = 256*1024*1024;
SELECT COUNT(*) FROM (SELECT a FROM t INTERSECT ALL SELECT a FROM t) d;
-- 300000 correct (temporary table stays in memory)
SET SESSION tmp_table_size = DEFAULT;
SET SESSION internal_tmp_mem_storage_engine = MEMORY;
SELECT COUNT(*) FROM (SELECT a FROM t INTERSECT ALL SELECT a FROM t) d;
-- 300000 correct
