Bug #119100 WHERE alias IS NULL Fails for Derived Column Using subquery

• Submitted: 1 Oct 1:00
• Modified: 6 Oct 4:17
• Reporter: Runyuan He
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.4.0
• OS: Linux
• CPU Architecture: x86

[1 Oct 1:00] Runyuan He

Description:
A WHERE alias IS NULL clause on a derived table column produces an incorrect empty set. This occurs when the column is defined with a (NULL) OR (column) expression and another correlated subquery is also present in the SELECT list of the derived table.

How to repeat:
CREATE  TABLE  t1(c1 VARCHAR(500));
INSERT INTO t1 VALUES ('a');

SELECT sub.p0 FROM (
  SELECT
    (NULL) OR (t1.c1) AS p0,
    (SELECT x FROM (SELECT t1.c1 AS x) AS u) AS p1
  FROM t1
) AS sub;
-- NULL, Correct

SELECT sub.p0 FROM (
  SELECT
    (NULL) OR (t1.c1) AS p0,
    (SELECT x FROM (SELECT t1.c1 AS x) AS u) AS p1
  FROM t1
) AS sub 
WHERE sub.p0 IS NULL;
-- Empty set, Wrong

[6 Oct 4:17] Chaithra Marsur Gopala Reddy

Hi Runyuan He,

Thank you for the bug report. Verified as described.