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

ROLLUP marker NULL treated as data NULL in correlated subqueries

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 12.3.2
    • None
    • Server
    • None
    • Unexpected results
    • Hide
      ### 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
      ```
      Show
      ### 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

            Unassigned Unassigned
            Jacob Ding Jiahao Ding
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.