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

Wrong query result due to Table Elimination hitting unique_col IS NULL condition

    XMLWordPrintable

Details

    • Unexpected results
    • Added more conditions to not do table elimination when UNIQUE indexes over nullable columns may have duplicate NULL values.
    • Q3/2025 Maintenance

    Description

      These two equivalent queries produce contradictory results:

      SELECT t1.c1 FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);
      SELECT * FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);
       
      sudo docker run -it -p 3307:3306 -e MARIADB_ROOT_PASSWORD=root mariadb:latest
       
      CREATE TABLE t0(c0 FLOAT);
      CREATE TABLE t1(c1 FLOAT);
      INSERT INTO t0 VALUES (NULL), (NULL);
      INSERT INTO t1 VALUES (1.0);
      CREATE UNIQUE INDEX i0 USING BTREE ON t0(c0);
       
      SELECT t1.c1 FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);
      +------+
      | c1   |
      +------+
      |    1 |
      +------+
      1 row in set (0.001 sec)
       
      SELECT * FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);
      +------+------+
      | c1   | c0   |
      +------+------+
      |    1 | NULL |
      |    1 | NULL |
      +------+------+
      2 rows in set (0.001 sec)
      

      Attachments

        Issue Links

          Activity

            People

              bsrikanth Srikanth Bondalapati
              jinhui lai jinhui lai
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - 3d
                  3d
                  Remaining:
                  Remaining Estimate - 0d
                  0d
                  Logged:
                  Time Spent - 2.5d Time Not Required
                  2.5d

                  Git Integration

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