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

'Not exists' optimization isn't applied though listed in EXPLAIN plan

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.3
    • Fix Version/s: 10.3.36
    • Component/s: Optimizer
    • Labels:
      None

      Description

      The test case and diagnostic is based on https://bugs.mysql.com/bug.php?id=28188, but the difference is "t2" table of the MySQL bug has an index on column "b", and while having that index the optimization is applied correctly. Table "t2" in the following example doesn't have such an index.

      CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8));
      CREATE TABLE t2 (id int NOT NULL, b int NOT NULL);
       
      INSERT INTO t1 VALUES
        (1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc');
      INSERT INTO t2 VALUES
        (3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40);
       
      flush status;
       
      -- Run the following SQL which doesn't imply using the 'not exists' optimization
      -- just to get the statistics
      SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b <> 0;
       
      show status like 'Handler_read%';
       
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | Handler_read_first       | 0     |
      | Handler_read_key         | 0     |
      | Handler_read_last        | 0     |
      | Handler_read_next        | 0     |
      | Handler_read_prev        | 0     |
      | Handler_read_retry       | 0     |
      | Handler_read_rnd         | 0     |
      | Handler_read_rnd_deleted | 0     |
      | Handler_read_rnd_next    | 16    |
      +--------------------------+-------+
       
      -- Now flush the statistics and run the SQL that implies using the 'not exists' optimization:
      flush status;
       
      SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
       
      show status like 'Handler_read%';
       
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | Handler_read_first       | 0     |
      | Handler_read_key         | 0     |
      | Handler_read_last        | 0     |
      | Handler_read_next        | 0     |
      | Handler_read_prev        | 0     |
      | Handler_read_retry       | 0     |
      | Handler_read_rnd         | 0     |
      | Handler_read_rnd_deleted | 0     |
      | Handler_read_rnd_next    | 16    |
      +--------------------------+-------+
      

      As we can see, the value of 'Handler_read_rnd_next' is the same in both cases which means no optimization has been applied though it's listed in the explain plan:

      EXPLAIN SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra
                               |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 5    |
                               |
      |    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 9    | Using where; Not exists; Using join buffer (flat, BNL join) |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------+
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              oleg.smirnov Oleg Smirnov
              Reporter:
              oleg.smirnov Oleg Smirnov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.