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

Return Impossible WHERE instead of Full table scan on some admittedly false field=const expressions

    XMLWordPrintable

Details

    Description

      The majority of Field_xxx classes could detect "Impossible WHERE" by catching out-of-domain constants that appear in WHERE condition in the form field=const. It could avoid full table scan in such cases.

      Examples:

      a DATE column compared to a DATETIME constant

      {code}
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DATE);
      INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02');
      EXPLAIN SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:30';
      {code}
      returns:

      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+

      It's to do the full table scan, through the condition is known to return FALSE for all records in the table.

      INT compared to a constant that is out of range

      {code}
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a TINYINT);
      INSERT INTO t1 VALUES (10),(20),(30);
      EXPLAIN SELECT * FROM t1 WHERE a=300;
      {code}

      INT compared to a constant with fractional digits

      {code}
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (10),(20),(30);
      EXPLAIN SELECT * FROM t1 WHERE a=10.1
      {code}

      String column compared to a long constant

      {code}
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a VARCHAR(5));
      INSERT INTO t1 VALUES ('a'),('b'),('c');
      EXPLAIN SELECT * FROM t1 WHERE a=REPEAT('a',100);
      {code}

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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