Short circuit CONSTRAINTS not being true in WHERE clause
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
Example:
```mysql
set global tidb_enable_check_constraint = 1;
CREATE TABLE t (month INT CHECK (month>=1 AND month <=12));
INSERT INTO t values (1),(2),(11),(12);
EXPLAIN SELECT * FROM t WHERE month = 20;
```
This results in a full table scan, but it is obvious that no rows should match:
```
tidb> EXPLAIN SELECT * FROM t WHERE month = 20;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7 | 10.00 | root | | data:Selection_6 |
| └─Selection_6 | 10.00 | cop[tikv] | | eq(test.t.month, 20) |
| └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.000 sec)
```
Unfortunately I assume there are no way to know if there are non-checked values in the table, which would not be found by this optimization. So this sequence would break the optimization:
```mysql
drop table if exists t;
set global tidb_enable_check_constraint = 1;
CREATE TABLE t (month INT CONSTRAINT valid_month CHECK (month>=0 AND month <=12));
INSERT INTO t VALUES (1),(2),(11),(12);
EXPLAIN SELECT * FROM t WHERE month = 20;
INSERT INTO t VALUES (20); -- Expected to fail
ALTER TABLE t ALTER CONSTRAINT valid_month NOT ENFORCED;
INSERT INTO t VALUES (20); -- Expected to succeed
ALTER TABLE t ALTER CONSTRAINT valid_month ENFORCED; -- This does not validate all existing rows!
SELECT * FROM t WHERE month = 20; -- With optimization this would not work
```
Contributor guide
Assessment
This issue has not been assessed yet.