Bug #120097 Incorrect result set when use INTERSECT ALL

• Submitted: 18 Mar 9:34
• Modified: 18 Mar 9:48
• Reporter: Guo Yuxiao
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any

[18 Mar 9:34] Guo Yuxiao

Description:
I found a logic bug in MySQL 9.6.0.

The queries Q2 and Q4 are equivalent, yet they yield different execution results. Below is the construction process of Q2 and Q4:

-- Q1, result:{''*16,'NULL'}
SELECT t2.c0 AS c0 FROM t0 AS t1 RIGHT JOIN t2 AS t2 ON TRUE LEFT JOIN t2 AS t3 ON (t2.c0 = t3.c0);

-- Q2 (Q1 + WHERE), result:{''*16}
SELECT t2.c0 AS c0 FROM t0 AS t1 RIGHT JOIN t2 AS t2 ON TRUE LEFT JOIN t2 AS t3 ON (t2.c0 = t3.c0) WHERE EXISTS(SELECT t1.c0 AS c0 FROM t2 AS t4 WHERE NOT t2.c0 IS NULL);

-- Q3 result:{''*16,'NULL'}
-- Q3 = Q1 INTERSECT ALL Q1
SELECT
  t_wrapper_0.c0
FROM (
  SELECT t2.c0 AS c0 FROM t0 AS t1 RIGHT JOIN t2 AS t2 ON TRUE LEFT JOIN t2 AS t3 ON (t2.c0 = t3.c0) 
  INTERSECT ALL
  SELECT t2.c0 AS c0 FROM t0 AS t1 RIGHT JOIN t2 AS t2 ON TRUE LEFT JOIN t2 AS t3 ON (t2.c0 = t3.c0) 
) AS t_wrapper_0;

-- Q4, expected result:{''*16}, actual result:{''}
-- Q4 = Q3 + WHERE
-- It looks different from the where condition in Q2, but it is actually equivalent
SELECT
  t_wrapper_0.c0
FROM (
  SELECT t2.c0 AS c0 FROM t0 AS t1 RIGHT JOIN t2 AS t2 ON TRUE LEFT JOIN t2 AS t3 ON (t2.c0 = t3.c0) 
  INTERSECT ALL
  SELECT t2.c0 AS c0 FROM t0 AS t1 RIGHT JOIN t2 AS t2 ON TRUE LEFT JOIN t2 AS t3 ON (t2.c0 = t3.c0) 
) AS t_wrapper_0
WHERE
  EXISTS(
    SELECT
      t_wrapper_0.c0 AS c0
    FROM t2 AS t4
    WHERE
      NOT t_wrapper_0.c0 IS NULL
  );

How to repeat:
-- create table
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 ( c0 longtext ) CHARSET=utf8mb4;
INSERT INTO t0 VALUES (NULL);
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 ( c0 longtext ) CHARSET=utf8mb4;
INSERT INTO t2 VALUES (''),(''),(''),(''),(NULL);

-- Q2, result:{''*16}
SELECT t2.c0 AS c0 FROM t0 AS t1 RIGHT JOIN t2 AS t2 ON TRUE LEFT JOIN t2 AS t3 ON (t2.c0 = t3.c0) WHERE EXISTS(SELECT t1.c0 AS c0 FROM t2 AS t4 WHERE NOT t2.c0 IS NULL);

-- Q4, expected result:{''*16}, actual result:{''}
SELECT
  t_wrapper_0.c0
FROM (
  SELECT t2.c0 AS c0 FROM t0 AS t1 RIGHT JOIN t2 AS t2 ON TRUE LEFT JOIN t2 AS t3 ON (t2.c0 = t3.c0) 
  INTERSECT ALL
  SELECT t2.c0 AS c0 FROM t0 AS t1 RIGHT JOIN t2 AS t2 ON TRUE LEFT JOIN t2 AS t3 ON (t2.c0 = t3.c0) 
) AS t_wrapper_0
WHERE
  EXISTS(
    SELECT
      t_wrapper_0.c0 AS c0
    FROM t2 AS t4
    WHERE
      NOT t_wrapper_0.c0 IS NULL
  );

[18 Mar 9:48] Roy Lyseng

Thank you for the bug report.
Verified as described.