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

    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

      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';

      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

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

      INT compared to a constant with fractional digits

      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

      String column compared to a long constant

      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);

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated: