Optimize impossible where conditions
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
Here a table with a unsigned tinyint that allows one to store values between 0 and 256 is queried for values that are outside of this range. Looks like it might be possible to optimize this to not have a TiDB-TiKV roundtrip.
Also the explain output could more explicitly state that it is excluding something due to it being impossible.
```
sql> create table t1(id tinyint unsigned primary key);
Query OK, 0 rows affected (0.1031 sec)
sql> explain select * from t1 where id<0;
+-------------+---------+------+---------------+---------------+
| id | estRows | task | access object | operator info |
+-------------+---------+------+---------------+---------------+
| TableDual_6 | 0.00 | root | | rows:0 |
+-------------+---------+------+---------------+---------------+
1 row in set (0.0011 sec)
sql> explain select * from t1 where id>10000;
+--------------------+---------+-----------+---------------+----------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------+---------+-----------+---------------+----------------------------------------------------+
| TableReader_6 | 3333.33 | root | | data:TableRangeScan_5 |
| └─TableRangeScan_5 | 3333.33 | cop[tikv] | table:t1 | range:(10000,+inf], keep order:false, stats:pseudo |
+--------------------+---------+-----------+---------------+----------------------------------------------------+
2 rows in set (0.0015 sec)
sql> insert into t1 values(-1);
ERROR: 1264 (22003): Out of range value for column 'id' at row 1
sql> insert into t1 values(1000);
ERROR: 1264 (22003): Out of range value for column 'id' at row 1
```
Some other cases:
```
sql> CREATE TABLE t2 (id bigint unsigned primary key auto_random, c1 VARCHAR(5));
Query OK, 0 rows affected, 1 warning (0.1124 sec)
Note (code 1105): Available implicit allocation times: 576460752303423487
sql> EXPLAIN SELECT * FROM t2 WHERE c1='xxxxxxxxxxxxxxxxxxxxxxxxxx';
+---------------------+----------+-----------+---------------+----------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------+----------+-----------+---------------+----------------------------------------------+
| TableReader_7 | 10.00 | root | | data:Selection_6 |
| └─Selection_6 | 10.00 | cop[tikv] | | eq(test.t2.c1, "xxxxxxxxxxxxxxxxxxxxxxxxxx") |
| └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
+---------------------+----------+-----------+---------------+----------------------------------------------+
3 rows in set, 1 warning (0.0014 sec)
Warning (code 1406): Data Too Long, field len 5, data len 26
```
```
sql> EXPLAIN SELECT * FROM t1 WHERE id>10 AND id IN (SELECT 9 UNION ALL SELECT 8);
+-------------------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------+
| IndexJoin_23 | 2.50 | root | | inner join, inner:TableReader_19, outer key:Column#4, inner key:test.t1.id, equal cond:eq(Column#4, test.t1.id) |
| ├─HashAgg_32(Build) | 2.00 | root | | group by:Column#4, funcs:firstrow(Column#4)->Column#4 |
| │ └─Union_36 | 2.00 | root | | |
| │ ├─Projection_38 | 1.00 | root | | 9->Column#4 |
| │ │ └─TableDual_39 | 1.00 | root | | rows:1 |
| │ └─Projection_40 | 1.00 | root | | 8->Column#4 |
| │ └─TableDual_41 | 1.00 | root | | rows:1 |
| └─TableReader_19(Probe) | 0.33 | root | | data:Selection_18 |
| └─Selection_18 | 0.33 | cop[tikv] | | gt(test.t1.id, 10) |
| └─TableRangeScan_17 | 1.00 | cop[tikv] | table:t1 | range: decided by [Column#4], keep order:false, stats:pseudo |
+-------------------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------+
10 rows in set (0.0157 sec)
```
Note that `SELECT * FROM t1 WHERE id>10 AND id<11` seems to do the right thing.
Contributor guide
Assessment
This issue has not been assessed yet.