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

        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:

              Git Integration

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