Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-40550

ROLLUP marker NULL treated as data NULL in correlated subqueries

    XMLWordPrintable

Details

    • Unexpected results

    Description

      ### Description
       
      When a correlated subquery references a column from an outer query that uses `GROUP BY ... WITH ROLLUP`, the super-aggregate (ROLLUP) row's NULL value in the GROUP BY column is incorrectly treated as a data NULL rather than a structural marker NULL. This causes the correlated subquery to return incorrect results on the ROLLUP row.
       
      The SQL standard requires that `NULL = NULL` evaluates to UNKNOWN (i.e., no match), so a correlated subquery on the ROLLUP row (where the correlation column is NULL) should return 0 / NULL / FALSE. However, MariaDB incorrectly returns a non-zero count or non-NULL value, indicating that the marker NULL is being matched against actual data.
       
      ### Minimal Reproduction
       
      ```sql
      CREATE TABLE t0 (id INT PRIMARY KEY, c0 INT);
      CREATE TABLE t1 (id INT PRIMARY KEY, c0 INT);
      INSERT INTO t0 VALUES (1, 10), (2, 20), (3, 30);
      INSERT INTO t1 VALUES (1, 10), (2, 20), (3, 20), (4, 30);
       
      SELECT t0.c0, (SELECT COUNT(*) FROM t1 WHERE t1.c0 = t0.c0) AS cnt
      FROM t0 GROUP BY t0.c0 WITH ROLLUP;
      ```
       
      ### Expected Result
       
      The ROLLUP super-aggregate row (c0=NULL) should have `cnt=0`, because `t1.c0 = NULL` evaluates to UNKNOWN and matches no rows:
       
      ```
      c0    cnt
      10    1
      20    2
      30    1
      NULL  0    ← correct
      ```
       
      ### Actual Result
       
      MariaDB returns `cnt=1` on the ROLLUP row, indicating the marker NULL is incorrectly matched against data:
       
      ```
      c0    cnt
      10    1
      20    2
      30    1
      NULL  1    ← WRONG
      ```

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              Jacob Ding Jiahao Ding
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.