ddl: ADD CHECK constraint validates existing rows with an unparenthesized `where not <expr>`, so a top-level OR rejects valid rows and a top-level AND accepts violating rows
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
`ALTER TABLE ... ADD CONSTRAINT ... CHECK (expr)` validates existing rows with an internal query built as `where not %s` without parentheses around the expression (`pkg/ddl/constraint.go`, ``sql := fmt.Sprintf("select 1 from `%s`.`%s` where not %s limit 1", ...)`` — line 379 at v8.5.3 and on `release-8.5`, line 378 on master `e26d86f9fb58c1829e75ac7f8bf6caa91bd4693e`). Because `NOT` binds tighter than `AND`/`OR`, a check expression whose top-level operator is `OR` or `AND` is validated against the wrong predicate:
- top-level `OR`: `NOT a OR b` is evaluated, so rows that **satisfy** the constraint are reported as violating (error 3819), and the DDL fails.
- top-level `AND`: `NOT a AND b` is evaluated, so rows that **violate** the constraint can pass validation. The constraint is created while violating rows remain, and a later unrelated `UPDATE` of those rows fails with 3819.
The same validation is used by `ALTER TABLE ... ALTER CONSTRAINT ... ENFORCED`. INSERT/UPDATE enforcement itself is correct. Tables with no rows are not affected.
### 1. Minimal reproduce step (Required)
```sql
SET GLOBAL tidb_enable_check_constraint = ON;
-- reconnect
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, b INT NOT NULL, c INT NOT NULL);
INSERT INTO t (a, b, c) VALUES (0, 1, 0); -- satisfies (a = 1 OR b = 1)
SELECT COUNT(*) FROM t WHERE NOT (a = 1 OR b = 1); -- 0
ALTER TABLE t ADD CONSTRAINT ck_or CHECK (a = 1 OR b = 1);
CREATE TABLE u (id INT AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, b INT NOT NULL, c INT NOT NULL);
INSERT INTO u (a, b, c) VALUES (1, 0, 0); -- violates (a = 1 AND b = 1)
SELECT COUNT(*) FROM u WHERE NOT (a = 1 AND b = 1); -- 1
ALTER TABLE u ADD CONSTRAINT ck_and CHECK (a = 1 AND b = 1);
UPDATE u SET c = c + 1;
CREATE TABLE w (id INT AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, b INT NOT NULL);
ALTER TABLE w ADD CONSTRAINT ck_w CHECK (a = 1 OR b = 1) NOT ENFORCED;
INSERT INTO w (a, b) VALUES (0, 1); -- satisfies the constraint
ALTER TABLE w ALTER CONSTRAINT ck_w ENFORCED;
```
### 2. What did you expect to see? (Required)
The same result as MySQL 8.0.46 (measured with the statements above):
- `ALTER TABLE t ADD CONSTRAINT ck_or ...` succeeds
- `ALTER TABLE u ADD CONSTRAINT ck_and ...` fails with `ERROR 3819 (HY000): Check constraint 'ck_and' is violated.`
- `UPDATE u SET c = c + 1` succeeds (the constraint was not added)
- `ALTER TABLE w ALTER CONSTRAINT ck_w ENFORCED` succeeds
### 3. What did you see instead (Required)
- `ALTER TABLE t ADD CONSTRAINT ck_or ...` → `ERROR 3819 (HY000): Check constraint 'ck_or' is violated.`, although `SELECT COUNT(*) FROM t WHERE NOT (a = 1 OR b = 1)` returns 0. `SELECT COUNT(*) FROM t WHERE NOT a = 1 OR b = 1` (the unparenthesized form) returns 1.
- `ALTER TABLE u ADD CONSTRAINT ck_and ...` succeeds, and `SELECT COUNT(*) FROM u WHERE NOT (a = 1 AND b = 1)` still returns 1 afterwards.
- `UPDATE u SET c = c + 1` → `ERROR 3819 (HY000): Check constraint 'ck_and' is violated.`
- `ALTER TABLE w ALTER CONSTRAINT ck_w ENFORCED` → `ERROR 3819 (HY000): Check constraint 'ck_w' is violated.`
Adding one extra pair of parentheses to the expression gives the MySQL results on TiDB: `CHECK ((a = 1 OR b = 1))` is accepted for the row `(0, 1)`, and `CHECK ((a = 1 AND b = 1))` is rejected with 3819 for the row `(1, 0)`.
Possible fix: wrap the restored expression in parentheses in the validation query, e.g. `where not (%s)`.
### 4. What is your TiDB version? (Required)
```
Release Version: v8.5.3
Edition: Community
Git Commit Hash: dc2548aac79a712265e831cff2a3a896bc0a5a38
Git Branch: HEAD
UTC Build Time: 2025-07-31 13:54:59
GoVersion: go1.23.6
Race Enabled: false
Check Table Before Drop: false
Store: unistore
```
Reproduced on `tiup tidb:v8.5.3 --store=unistore`. The same unparenthesized format string is present on master (by source inspection; not run on master).
Contributor guide
Research direction
Start in pkg/ddl/constraint.go at the validation query around line 379, then trace how ADD CONSTRAINT and ALTER CONSTRAINT ... ENFORCED use it. Reproduce the OR and AND cases from the issue with TiDB, and verify that valid existing rows are accepted, violating rows reject constraint creation, and NOT ENFORCED constraints can be enabled correctly.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 82/100