[MDEV-33127] EXCHANGE PARTITION bypasses check constraint on column/table - Jira

XML

Word

Printable

--source include/have_partition.inc

create table t1 (a int check (a < 1000))

partition by range(a) (partition p0 values less than (100), partition p1 values less than (maxvalue));

create table t (a int);

insert into t values (2000);

alter table t1 exchange partition p1 with table t;

select * from t1;

check table t1 extended;

# Cleanup

drop table if exists t1, t;

So, even although t1 has a constraint "a < 1000", the greater value still ends up in the table without any visible sign of corruption.

Reproducible on all existing versions of MariaDB and on MySQL (at least 8.2.0).

Same is true for CONVERT TABLE .. TO PARTITION on versions where it is applicable (MariaDB 10.7+):

--source include/have_partition.inc

create table t1 (a int check (a < 1000))

partition by range(a) (partition p0 values less than (100));

create table t (a int);

insert into t values (2000);

alter table t1 convert table t to partition p1 values less than (maxvalue);

select * from t1;

check table t1 extended;

# Cleanup

drop table if exists t1, t;

10.11 9a7deb1c36f9ed08a1ce48e7fd635b45c721dcd6
alter table t1 convert table t to partition p1 values less than (maxvalue);
select * from t1;
a
2000
check table t1 extended;
Table Op Msg_type Msg_text
test.t1 check status OK

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.