Bug #121795 INTERSECT/EXCEPT: a NULL in an implicitly grouped right operand matches any NOT NULL value

Submitted: 17 Nov 0:30 Modified: 19 Nov 6:57
Reporter: Abhishek Soni Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:9.7.0, 9.7.4, 26.10.0 OS:Linux (Official Docker images mysql:9.7.0, mysql:9.7.4, mysql:26.10.0)
Assigned to: CPU Architecture:x86 (x86_64; also seen on arm64)

[17 Nov 0:30] Abhishek Soni
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;
[19 Nov 6:57] Chaithra Marsur Gopala Reddy
Hi Abhishek Soni,

Thank you for the test case. Verified as described.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.