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

Lost 'Impossible where' from query with inexpensive subquery

    XMLWordPrintable

Details

    Description

      In 5.5.32 for the test case

      create table t1 (a int) engine=myisam;
      insert into t1 values (3), (1), (7);
       
      create table t2 (b int, index idx(b));
      insert into t2 values (2), (5), (3), (2);
       
      explain select * from t1 where (select max(b) from t2) = 10;
      explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3;
      

      we had

      MariaDB [test]> explain select * from t1 where (select max(b) from t2) = 10;
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      |    1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE             |
      |    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
       
      MariaDB [test]> explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3;
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      |    1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE             |
      |    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      

      Yet after mdev-4817 (commit 04684b7709f55a5a9de9226e834bcfbed05ee5c0) we have

      MariaDB [test]> explain select * from t1 where (select max(b) from t2) = 10;
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      |    1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE             |
      |    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3;
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      |    1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where                  |
      |    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      

      So we observe a performance regression.

      Attachments

        Activity

          People

            igor Igor Babaev
            igor Igor Babaev
            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.