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

        • Type: Task
        • Status: Open
        • Priority: Major
        • Resolution: Unresolved
        • Fix Version/s: 10.1
        • Component/s: Optimizer
        • Labels:
          None

          Description

          The majority of Field_xxx classes could detect "Impossible WHERE" by catching out-or-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);

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

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

                    Dates

                    • Created:
                      Updated: