[MDEV-33127] EXCHANGE PARTITION bypasses check constraint on column/table Created: 2023-12-27  Updated: 2023-12-27

Status: Open
Project: MariaDB Server
Component/s: Partitioning
Affects Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Alexey Botchkov
Resolution: Unresolved Votes: 0
Labels: upstream


 Description    

--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;

10.4 87a5d16911bb94d383480fdd49e20876ed1400f2

 
select * from t1;
 
a
 
2000
 
check table t1 extended;
 
Table	Op	Msg_type	Msg_text
 
test.t1	check	status	OK

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



 Comments   
Comment by Elena Stepanova [ 2023-12-27 ]

Same is true for without overlaps constraint.

--source include/have_partition.inc
 
 
 
create table t1 (a int, s date, e date, period for app(s,e), primary key(a, app without overlaps) ) partition by range columns(s) (
 
  partition p0 values less than ('2010-01-01'),
 
  partition p1 values less than (maxvalue)
 
);
 
insert into t1 values (1,'2000-01-01','2021-01-01');
 
 
 
create table t (a int, s date, e date, period for app(s,e), primary key(a, app without overlaps));
 
insert into t values (1,'2012-01-01','2013-01-01');
 
 
 
alter table t1 exchange partition p1 with table t;
 
check table t1 extended;
 
select * from t1;
 
 
 
# Cleanup
 
drop table if exists t1, t;

10.5 f98d2ef5b4ab633d74fb245a8b6520fe86514de0

 
alter table t1 exchange partition p1 with table t;
 
check table t1 extended;
 
Table	Op	Msg_type	Msg_text
 
test.t1	check	status	OK
 
select * from t1;
 
a	s	e
 
1	2012-01-01	2013-01-01
 
1	2000-01-01	2021-01-01

Generated at Thu Feb 08 10:36:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.