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

Wrong result from ref_or_null access when two key parts have IS NULL alternatives

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.4
    • 11.4
    • Optimizer

    Description

      A correlated subquery misses a row when it reads t1 by ref_or_null on a two-part key where each key part has an IS NULL alternative.

        CREATE TABLE t1 (a INT, b INT, KEY(a,b)) ENGINE=InnoDB;
        INSERT INTO t1 VALUES (NULL,2);
        CREATE TABLE t2 (x INT) ENGINE=InnoDB;
        INSERT INTO t2 VALUES (2);
       
        SELECT (SELECT COUNT(*) FROM t2 AS i, t1
                WHERE (t1.a = 1 OR t1.a IS NULL) AND
                      (t1.b = o.x OR t1.b IS NULL) AND
                      t1.b = i.x) AS c
        FROM t2 AS o;
       
        SELECT (SELECT COUNT(*) FROM t2 AS i, t1 IGNORE INDEX(a)
                WHERE (t1.a = 1 OR t1.a IS NULL) AND
                      (t1.b = o.x OR t1.b IS NULL) AND
                      t1.b = i.x) AS c
        FROM t2 AS o;
       
        DROP TABLE t1, t2;
        

      The first SELECT returns 0 while the second returns 1. The correct result is 1, because the only row of t1, (NULL,2), satisfies all three predicates.

      On MySQL 9.6.0, the scenario returns 1 for both SELECTs.

      Attachments

        Activity

          People

            Unassigned Unassigned
            Gosselin Dave Gosselin
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0d
                0d
                Logged:
                Time Spent - 20m
                20m

                Git Integration

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