EXCHANGE PARTITION does not follow CHECK Constraints

[mjonss]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

set @@global.tidb_enable_check_constraint = 1;
create table t (a int check (a > 50) ENFORCED);
create table tp (a int check (a < 50) ENFORCED) partition by range (a) (partition p1 values less than (100));
insert into t values (75);
insert into tp values (25);
alter table tp exchange partition p1 with table t;
select * from t; -- shows 25, which breaks the CHECK constraint
insert into t values (25); -- fails as it should
select * from tp; -- shows 75, which breaks the CHECK constraint
insert into tp values (75); -- fail as it should

2. What did you expect to see? (Required)

That the alter would keep the CHECK Constraint enforced

3. What did you see instead (Required)

The CHECK constraint is not enforced.

4. What is your TiDB version? (Required)

tidb_version(): Release Version: v7.4.0-alpha-43-gcdeac4c66f
Edition: Community
Git Commit Hash: cdeac4c66f728ad60a5166ef7700efa52cbd1384
Git Branch: master
UTC Build Time: 2023-08-08 14:27:32
GoVersion: go1.20.7
Race Enabled: false
Check Table Before Drop: false
Store: unistore

[jiyfhust]

/assign

[jiyfhust]

Can we check the Constraints by sql like this:

select 1 from tb where (not (expr1 and expr2)) limit 1;

@mjonss

[mjonss]

@jiyfhust Yes, that should work, but we also need to add the constraints from the non-partitioned table to the partition, and from the partitioned table to the non-partitioned table before the 'write only' state (see #45877 where I added that state...)

Since if we don't add the contraints before validation, someone may still sneak in a non checked value.

[mjonss]

CHECK CONSTRAINT was introduced in 7.2, so this does not affect any LTS version yet.