Bug #121024 Wrong result for IS NULL / IS NOT NULL on column from nested LEFT JOIN over empty derived table

• Submitted: 28 Jul 15:49
• Modified: 31 Jul 11:14
• Reporter: mu mu
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.46, 8.4.10, 9.7.1
• OS: Ubuntu (22.04)
• CPU Architecture: Any

[28 Jul 15:49] mu mu

Description:
A column produced through a nested LEFT JOIN can appear as NULL in the SELECT list, but outer predicates IS NULL and IS NOT NULL on that column return inverted counts. EXPLAIN FORMAT=TREE shows that IS NOT NULL is removed from the plan, as if the column were non-nullable.

Reproduced on MySQL 9.7.1 (Community). The same SQL on MariaDB 12.3.0 returns the expected counts (is_null=2, is_not_null=0).

How to repeat:
DROP DATABASE IF EXISTS bug_isnull_demo;
CREATE DATABASE bug_isnull_demo;
USE bug_isnull_demo;

SELECT 'base' AS which, v.x, ss.q
FROM (SELECT 1 AS x UNION ALL SELECT 2) v
LEFT JOIN (
  SELECT q
  FROM (SELECT 7 AS q FROM (SELECT 1 AS a FROM DUAL WHERE FALSE) e) s
  LEFT JOIN (SELECT 8 AS z) z ON TRUE
) ss ON TRUE
ORDER BY v.x;

SELECT 'is_null' AS which, COUNT(*) AS cnt
FROM (
  SELECT v.x, ss.q
  FROM (SELECT 1 AS x UNION ALL SELECT 2) v
  LEFT JOIN (
    SELECT q
    FROM (SELECT 7 AS q FROM (SELECT 1 AS a FROM DUAL WHERE FALSE) e) s
    LEFT JOIN (SELECT 8 AS z) z ON TRUE
  ) ss ON TRUE
) t
WHERE t.q IS NULL;

SELECT 'is_not_null' AS which, COUNT(*) AS cnt
FROM (
  SELECT v.x, ss.q
  FROM (SELECT 1 AS x UNION ALL SELECT 2) v
  LEFT JOIN (
    SELECT q
    FROM (SELECT 7 AS q FROM (SELECT 1 AS a FROM DUAL WHERE FALSE) e) s
    LEFT JOIN (SELECT 8 AS z) z ON TRUE
  ) ss ON TRUE
) t
WHERE t.q IS NOT NULL;

EXPLAIN FORMAT=TREE
SELECT COUNT(*)
FROM (
  SELECT v.x, ss.q
  FROM (SELECT 1 AS x UNION ALL SELECT 2) v
  LEFT JOIN (
    SELECT q
    FROM (SELECT 7 AS q FROM (SELECT 1 AS a FROM DUAL WHERE FALSE) e) s
    LEFT JOIN (SELECT 8 AS z) z ON TRUE
  ) ss ON TRUE
) t
WHERE t.q IS NOT NULL;

Actual result on MySQL 9.7.1:

base:
  x=1, q=NULL
  x=2, q=NULL

is_null:
  cnt=0

is_not_null:
  cnt=2

EXPLAIN FORMAT=TREE for the IS NOT NULL query has no IS NOT NULL / Filter node; the predicate is optimized away.

Expected result:

base: unchanged (q=NULL)

is_null:
  cnt=2

is_not_null:
  cnt=0

[31 Jul 10:44] Chaithra Marsur Gopala Reddy

Hi mu mu,

Thank you for the test case. Verified as described.

[31 Jul 11:14] Chaithra Marsur Gopala Reddy

This is fixed as part of Bug#119499 which is part of Mysql-9.7.2 release.