Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
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
- includes
-
MDEV-28531 update on out of range criteria should be Impossible Where
- Open
- relates to
-
MDEV-12432 Range optimizer for ENUM and SET does not return "Impossible WHERE" in some case
- Closed
-
MDEV-15758 Split Item_bool_func::get_mm_leaf() into virtual methods in Field and Type_handler
- Closed
-
MDEV-15759 Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant
- Closed
-
MDEV-16784 Range search does not work well for decimal_10_2_column<10.999
- Open
-
MDEV-18898 SELECT using wrong index when using operator IN with mixed types
- Closed