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

Lost 'Impossible where' from query with inexpensive subquery

    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

            • Assignee:
              igor Igor Babaev
              Reporter:
              igor Igor Babaev
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: