pingcap / pingcap/tidb

Optimize impossible where conditions

Open
#36,015 1 comment 0 reactions 0 assignees View on GitHub
type/enhancement
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.