Description:
When the right operand of INTERSECT or EXCEPT is an implicitly grouped query (aggregate functions, no GROUP BY) with a NULL literal in its select list, that NULL matches any value of the corresponding NOT NULL column on the left. INTERSECT returns a row it should not return, and EXCEPT drops a row it should keep.
The plain-operand form of this problem (bug#117911) is fixed in 9.7.0: the right operand is planned as "Zero rows (Impossible WHERE)". With an implicitly grouped right operand the same rewrite still leaves one aggregated row, computed over zero input rows, and that row is matched (costs removed):
EXPLAIN FORMAT=TREE SELECT 7, NULL FROM t INTERSECT SELECT NULL, SUM(NULL) FROM t;
-> Table scan on <intersect temporary>
-> Intersect materialize with deduplication
-> Covering index scan on t using PRIMARY
-> Zero input rows (Impossible WHERE), aggregated into one output row
Because the operand is computed over zero rows, its aggregates change too: SELECT NULL, COUNT(*) FROM t returns (NULL, 3) on its own, but inside the INTERSECT it acts as (NULL, 0) (case 4 under "How to repeat").
Other observations on 26.10.0:
- Correct with the operands swapped, with a nullable left column, and when the NULL comes from an aggregate instead of a literal.
- Also wrong when the statement is wrapped in a derived table.
Versions (official Docker images, default configuration): wrong on 9.7.0, 9.7.4 and 26.10.0. On 9.4.0 to 9.6.0 the same statements are wrong as part of bug#117911. On 8.4.13 they are correct, but wrong when wrapped in a derived table (compare bug#120031). See also bug#121787 (nested right operand).
Found with automated metamorphic testing.
How to repeat:
-- Actual results below are from 26.10.0; 9.7.0 gives the same for cases 1 and 2.
-- 1. No tables needed.
-- Expected: empty set. Actual: 1 row (1, NULL).
SELECT 1, NULL INTERSECT SELECT NULL, SUM(NULL);
-- Expected: 1 row (1, NULL). Actual: empty set.
SELECT 1, NULL EXCEPT SELECT NULL, SUM(NULL);
-- 2. With a table.
CREATE TABLE t (c INT PRIMARY KEY);
INSERT INTO t VALUES (1), (2), (3);
-- Expected: empty set. Actual: (7, NULL).
SELECT 7, NULL FROM t INTERSECT SELECT NULL, SUM(NULL) FROM t;
-- Expected: (7, NULL). Actual: empty set.
SELECT 7, NULL FROM t EXCEPT SELECT NULL, SUM(NULL) FROM t;
-- Expected: empty set. Actual: (3, NULL).
SELECT c, NULL FROM t INTERSECT SELECT NULL, SUM(NULL) FROM t;
-- 3. Correct on the same server.
SELECT 7, NULL FROM t INTERSECT SELECT NULL, NULL FROM t; -- empty set
(SELECT NULL, SUM(NULL) FROM t) INTERSECT SELECT 7, NULL FROM t; -- empty set
-- 4. The operand is evaluated over zero rows.
SELECT NULL, COUNT(*) FROM t; -- (NULL, 3)
-- Expected: empty set. Actual: (7, 0).
SELECT 7, 0 FROM t INTERSECT SELECT NULL, COUNT(*) FROM t;