pingcap / pingcap/tidb

Short circuit CONSTRAINTS not being true in WHERE clause

Open
#66,309 1 comment 1 reaction 0 assignees View on GitHub
type/enhancement
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

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.