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

LP:885799 - No "Impossible WHERE" for false constant conds when expensive const cond is present

    XMLWordPrintable

Details

    Description

      If we have a query which has

      • regular ("cheap") constant condition that evaluates to FALSE
      • an expensive constant condition (doesn't matter what it evaluates to)
        then "Impossible WHERE" will not be generated.

      Example:

      create table t1 (a int);
      insert into t1 values (1),(2),(3);
       
      create table t2 as select * from t1;
       
      create table t_pk1 (a int primary key);
      create table t_pk2 (a int primary key);
       
      insert into t_pk1 select a from t1;
      insert into t_pk2 select a from t1;
       
      alter table t_pk1 add b int;
      alter table t_pk2 add b int;

      ## Here t_pk1 is a constant table, and "t_pk1.b> 3" is a cheap constant condition that evaluates to FALSE:
       
      MariaDB [j10]> explain select * from t_pk1, t2 where t_pk1.a=2 and t_pk1.b> 3 ;
      +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
      | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
      +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
      |  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
      +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
      1 row in set (0.00 sec)

      # Now, let's add an expensive constant condition, and observe that we won't be getting "Impossible WHERE" anymore:
       
      MariaDB [j10]> explain select * from t_pk1, t2 where t_pk1.a=2 and t_pk1.b> 3 and 2 > (select max(a) from t1);
      +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
      | id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
      +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
      |  1 | PRIMARY     | t_pk1 | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
      |  1 | PRIMARY     | t2    | ALL   | NULL          | NULL    | NULL    | NULL  |    3 |       |
      |  2 | SUBQUERY    | t1    | ALL   | NULL          | NULL    | NULL    | NULL  |    3 |       |
      +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
      3 rows in set (0.00 sec)

      Attachments

        Activity

          People

            Unassigned Unassigned
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.