From 3f619478f796eddbba6e39502fe941b285dd97b1 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 20:00:34 +0200 Subject: Adding upstream version 1:10.11.6. Signed-off-by: Daniel Baumann --- mysql-test/suite/versioning/t/foreign.test | 629 +++++++++++++++++++++++++++++ 1 file changed, 629 insertions(+) create mode 100644 mysql-test/suite/versioning/t/foreign.test (limited to 'mysql-test/suite/versioning/t/foreign.test') diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test new file mode 100644 index 00000000..5f228747 --- /dev/null +++ b/mysql-test/suite/versioning/t/foreign.test @@ -0,0 +1,629 @@ +--source suite/versioning/key_type.inc +--source suite/versioning/common.inc + +--echo ################# +--echo # Test RESTRICT # +--echo ################# + +--replace_result "$KEY_TYPE" KEY_TYPE +eval create table parent( + id int, + $KEY_TYPE (id) +) engine innodb; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create table child( + parent_id int, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end), + foreign key(parent_id) references parent(id) + on delete restrict + on update restrict +) engine innodb with system versioning; + +insert into parent values(1); +insert into child values(1); + +-- error ER_ROW_IS_REFERENCED_2 +delete from parent where id = 1; +delete from child where parent_id = 1; +delete from parent where id = 1; + +insert into parent values(1); +insert into child values(1); +-- error ER_ROW_IS_REFERENCED_2 +update parent set id=id+1; +delete from child; +update parent set id=id+1; +select * from child for system_time all; + +drop table child; +drop table parent; + +--echo ############################################## +--echo # Test when clustered index is a foreign key # +--echo ############################################## + +--replace_result "$KEY_TYPE" KEY_TYPE +eval create table parent( + id int(10) unsigned, + $KEY_TYPE (id) +) engine innodb; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create table child( + parent_id int(10) unsigned primary key, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end), + foreign key(parent_id) references parent(id) +) engine innodb with system versioning; + +insert into parent values(1); +insert into child values(1); + +-- error ER_ROW_IS_REFERENCED_2 +delete from parent where id = 1; + +drop table child; +drop table parent; + +--echo ################ +--echo # Test CASCADE # +--echo ################ + +--replace_result "$KEY_TYPE" KEY_TYPE +eval create table parent( + id int, + $KEY_TYPE (id) +) engine innodb; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create table child( + parent_id int, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end), + foreign key(parent_id) references parent(id) + on delete cascade + on update cascade +) engine innodb with system versioning; + +insert into parent values(1); +insert into child values(1); + +delete from parent where id = 1; +select * from child; +select * from child for system_time all; + +insert into parent values(1); +insert into child values(1); +update parent set id = id + 1; +select * from child; +select * from child for system_time all; + +drop table child; +drop table parent; + +--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE +eval create or replace table parent ( + id int, + $KEY_TYPE(id), + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end) +) with system versioning +engine innodb; + +create or replace table child ( + x int, + parent_id int not null, + constraint `parent-fk` + foreign key (parent_id) references parent (id) + on delete cascade + on update restrict +) +engine innodb; + +insert into parent (id) values (2); +insert into child (x, parent_id) values (2, 2); +delete from parent; +select * from child; + +drop table child; +drop table parent; + +--replace_result "$KEY_TYPE" KEY_TYPE +eval create or replace table parent ( + id int, + $KEY_TYPE(id) +) +engine innodb; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table child ( + id int primary key, + parent_id int not null, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time(row_start, row_end), + constraint `parent-fk` + foreign key (parent_id) references parent (id) + on delete cascade + on update restrict +) with system versioning +engine innodb; + +insert into parent (id) values (3); +insert into child (id, parent_id) values (3, 3); +delete from parent; +select * from child; +select *, check_row(row_start, row_end) from child for system_time all; + +drop table child; +drop table parent; + +--echo ################# +--echo # Test SET NULL # +--echo ################# + +--replace_result "$KEY_TYPE" KEY_TYPE +eval create table parent( + id int, + $KEY_TYPE (id) +) engine innodb; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table child( + parent_id int, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end), + foreign key(parent_id) references parent(id) + on delete set null + on update set null +) engine innodb with system versioning; + +insert into parent values(1); +insert into child values(1); +delete from child; +insert into child values(1); + +delete from parent where id = 1; +select * from child; +select *, current_row(sys_end) as current_row from child for system_time all order by sys_end; +delete from child; + +insert into parent values(1); +insert into child values(1); +update parent set id= id + 1; +select * from child; +select *, current_row(sys_end) as current_row from child for system_time all order by sys_end; + +drop table child; +drop table parent; + +--echo ########################### +--echo # Parent table is foreign # +--echo ########################### + +--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE +eval create or replace table parent( + id int, + $KEY_TYPE (id), + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end) +) engine innodb with system versioning; + +create or replace table child( + parent_id int, + foreign key(parent_id) references parent(id) +) engine innodb; + +insert into parent values(1); +insert into child values(1); +-- error ER_ROW_IS_REFERENCED_2 +delete from parent; +-- error ER_ROW_IS_REFERENCED_2 +update parent set id=2; + +delete from child; +delete from parent; + +-- error ER_NO_REFERENCED_ROW_2 +insert into child values(1); + +insert into parent values(1); +insert into child values(1); +-- error ER_ROW_IS_REFERENCED_2 +delete from parent; +-- error ER_ROW_IS_REFERENCED_2 +update parent set id=2; + +drop table child; +drop table parent; + +--echo ################### +--echo # crash on DELETE # +--echo ################### + +--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE +eval create or replace table a ( + cola int(10), + $KEY_TYPE (cola), + v_cola int(10) as (cola mod 10) virtual, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end) +) engine=innodb with system versioning; + +create index v_cola on a (v_cola); + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table b( + cola int(10), + v_cola int(10), + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end) +) engine=innodb with system versioning; + +alter table b add constraint `v_cola_fk` +foreign key (v_cola) references a (v_cola); + +insert into a(cola) values (12); +insert into b(cola, v_cola) values (10,2); +--error ER_ROW_IS_REFERENCED_2 +delete from a; + +drop table b, a; + +--echo ############################################### +--echo # CASCADE UPDATE foreign not system versioned # +--echo ############################################### +create or replace table parent ( + id smallint unsigned not null auto_increment, + value int unsigned not null, + primary key (id, value) +) engine = innodb; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table child ( + id mediumint unsigned not null auto_increment primary key, + parent_id smallint unsigned not null, + parent_value int unsigned not null, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end), + constraint `fk_child_parent` + foreign key (parent_id, parent_value) references parent (id, value) + on delete cascade + on update cascade +) engine = innodb with system versioning; + +create or replace table subchild ( + id int not null auto_increment primary key, + parent_id smallint unsigned not null, + parent_value int unsigned not null, + constraint `fk_subchild_child_parent` + foreign key (parent_id, parent_value) references child (parent_id, parent_value) + on delete cascade + on update cascade +) engine=innodb; + +insert into parent (value) values (23); +--enable_prepare_warnings +select id, value from parent into @id, @value; +--disable_prepare_warnings +insert into child values (default, @id, @value); +insert into subchild values (default, @id, @value); + +select parent_id from subchild; +update parent set id = 11, value = value + 1; +select parent_id from subchild; +select * from child; + +delete from parent; +select count(*) from child; +select * from child for system_time all; +select count(*) from subchild; + +drop table subchild, child, parent; + +--echo # +--echo # MDEV-18057 Assertion `(node->state == 5) || (node->state == 6)' failed in row_upd_sec_step upon DELETE after UPDATE failed due to FK violation +--echo # +create or replace table t1 (f1 int, key(f1)) engine=innodb; +create or replace table t2 (f2 int, foreign key (f2) references t1 (f1)) engine=innodb with system versioning; + +set foreign_key_checks= off; +insert ignore into t2 values (1); + +set foreign_key_checks= on; +--error ER_NO_REFERENCED_ROW_2 +update t2 set f2= 2; +delete from t2; + +drop table t2, t1; + +--echo # +--echo # MDEV-18879 Corrupted record inserted by FOREIGN KEY operation +--echo # +SET timestamp = 1; +SET time_zone='+02:00'; +SELECT now(); +CREATE TABLE t1 ( + pk INT UNSIGNED PRIMARY KEY, + f1 varchar(255) CHARACTER SET ucs2, + f2 longtext CHARACTER SET ucs2, + f3 varchar(255), + f4 char(255), + f5 longtext CHARACTER SET ucs2, + f6 INT UNSIGNED, + f7 INT UNSIGNED, + f8 INT UNSIGNED, + f9 INT UNSIGNED, + f10 INT UNSIGNED, + f11 INT UNSIGNED, + f12 varchar(255) CHARACTER SET ucs2, + f13 char(255) CHARACTER SET ucs2, + f14 char(255) CHARACTER SET ucs2, + f15 varchar(255), + f16 longtext, + f17 char(255) +) ENGINE=InnoDB WITH SYSTEM VERSIONING; + +INSERT INTO t1 VALUES +(1, 'a', 'e', 'f', 'a', 'generate', 1, 2, 3, 4, 5, 6, 'main', 'against', 'b', 'u', 'explode', 'tomorrow'), +(2, REPEAT('a',127), 'f', 'k', 'game', 'g', 2, 3, 4, 5, 6, 7, REPEAT('o',222), 'oven', 'flower', REPEAT('r',120), 'l', 'g'), +(3, 'weekly', 'x', 'v', 'r', 'c', 3, 4, 5, 6, 7, 8, 'validity', 'y', 'h', 'oxygen', 'venture', 'uncertainty'), +(4, 'r', 't', REPEAT('b',153), 'modern', 'h', 4, 5, 6, 7, 8, 9, REPEAT('g',128), 'a', 'w', 'f', 'b', 'b'), +(5, 'h', 'y', REPEAT('v',107), 'knife', 'profession', 5, 6, 7, 8, 9, 0, 'infection', 'u', 'likelihood', REPEAT('n',149), 'folk', 'd'), +(6, 'g', 'violent', REPEAT('o',28), 'capital', 'p', 6, 7, 8, 9, 0, 1, 'w', 'patron', 'd', 'y', 'originally', 'k'), +(7, 'k', 'uncomfortable', REPEAT('v',248), 'y', 'link', 7, 8, 9, 0, 1, 2, REPEAT('j',204), 'j', 'statute', 'emphasis', 'u', 'water'), +(8, 'preparation', 'water', 'suck', 'silver', 'a', 8, 9, 0, 1, 2, 3, 'h', 'q', 'o', 't', 'k', 'y'), +(9, 'y', 'f', 'e', 'a', 'dawn', 9, 0, 1, 2, 3, 4, 'peak', 'parking', 'b', 't', 'timber', 'c'), +(10, REPEAT('h',78), 'apologize', 'direct', 'u', 'frankly', 0, 1, 2, 3, 4, 5, 'h', 'exhibit', 'f', 'd', 'effective', 'c'), +(11, 'i', 'h', 'a', 'y', 'u', 1, 2, 3, 4, 5, 6, 'l', 'b', 'm', 'respond', 'ideological', 'credibility'); + +CREATE TABLE t2 ( + pk int primary key, + f char(255) CHARACTER SET ucs2, + key(f) +) ENGINE=InnoDB; + +INSERT INTO t2 VALUES (1,'against'),(2,'q'); + +SET SQL_MODE= ''; +SET timestamp = 2; +--disable_ps2_protocol +SELECT * INTO OUTFILE 't1.data' FROM t1; +--enable_ps2_protocol +SET timestamp = 3; +UPDATE t1 SET f13 = 'q'; +SET timestamp = 4; +LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1; +--disable_ps2_protocol +SELECT * INTO OUTFILE 't1.data.2' FROM t1; +--enable_ps2_protocol +SET timestamp = 5; +LOAD DATA INFILE 't1.data.2' REPLACE INTO TABLE t1; +--disable_ps2_protocol +SELECT * INTO OUTFILE 't2.data' FROM t2; +--enable_ps2_protocol +SET timestamp = 6; +LOAD DATA INFILE 't2.data' REPLACE INTO TABLE t2; +SET FOREIGN_KEY_CHECKS = OFF; +ALTER TABLE t1 ADD FOREIGN KEY (f13) REFERENCES t2 (f) ON DELETE SET NULL; +SET timestamp = 7; +LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1; +SET FOREIGN_KEY_CHECKS = ON; + +SET SESSION SQL_MODE= 'NO_BACKSLASH_ESCAPES'; +SET timestamp = 8; +LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1; +SET timestamp = 9; +REPLACE INTO t2 SELECT * FROM t2; + +# Cleanup +DROP TABLE t1, t2; +set timestamp= default; +set time_zone='+00:00'; +--let $datadir= `select @@datadir` +--remove_file $datadir/test/t1.data +--remove_file $datadir/test/t1.data.2 +--remove_file $datadir/test/t2.data + +--echo # +--echo # MDEV-16210 FK constraints on versioned tables use historical rows, which may cause constraint violation +--echo # +create or replace table t1 (a int, key(a)) engine innodb with system versioning; +create or replace table t2 (b int, foreign key (b) references t1(a)) engine innodb; +insert into t1 values (1),(2); +insert into t2 values (1); +--echo # DELETE from referenced table is not allowed +--error ER_ROW_IS_REFERENCED_2 +delete from t1 where a = 1; +drop tables t2, t1; + +--echo # +--echo # MDEV-20812 Unexpected ER_ROW_IS_REFERENCED_2 or server crash in row_ins_foreign_report_err upon DELETE from versioned table with FK +--echo # +create or replace table t1 (x int primary key) engine innodb; +create or replace table t2 (x int, foreign key (x) references t1(x)) engine innodb with system versioning; +set foreign_key_checks= off; +insert into t2 values (1), (1); +set foreign_key_checks= on; +--echo # DELETE from foreign table is allowed +delete from t2; +drop tables t2, t1; + +create or replace table t1 (a int, key(a)) engine innodb; +insert into t1 values (1); +create or replace table t2 (b int, foreign key (b) references t1(a)) engine innodb with system versioning; +insert into t2 values (1), (1); +--echo # DELETE from foreign table is allowed +delete from t2; +drop tables t2, t1; + +--echo # +--echo # MDEV-23644 Assertion on evaluating foreign referential action for self-reference in system versioned table +--echo # +create table t1 (pk int primary key, f1 int,f2 int, f3 text, + key(f1), fulltext(f3), key(f3(10)), + foreign key (f2) references t1 (f1) on delete set null +) engine=innodb with system versioning; + +insert into t1 values (1, 8, 8, 'SHORT'), (2, 8, 8, repeat('LONG', 8071)); + +delete from t1; +select pk, f1, f2, left(f3, 4), check_row_ts(row_start, row_end) from t1 for system_time all order by pk; + +# cleanup +drop table t1; + +--echo # Shorter case for clustered index (MDEV-25004) +create table t1 ( + y int primary key, r int, f int, key (r), + foreign key (f) references t1 (r) on delete set null) +with system versioning engine innodb; + +insert into t1 values (1, 6, 6), (2, 6, 6); +delete from t1; +select *, check_row_ts(row_start, row_end) from t1 for system_time all; +drop tables t1; + +--echo # Secondary unique index +create table t1 ( + y int unique null, r int, f int, key (r), + foreign key (f) references t1 (r) on delete set null) +with system versioning engine innodb; + +insert into t1 values (1, 6, 6), (2, 6, 6); +delete from t1; +select *, check_row_ts(row_start, row_end) from t1 for system_time all; +drop tables t1; + +--echo # Non-unique index cannot be fixed because it does not trigger duplicate error +create table t1 ( + y int, r int, f int, key (y), key (r), + foreign key (f) references t1 (r) on delete set null) +with system versioning engine innodb; + +insert into t1 values (1, 6, 6), (2, 6, 6); +delete from t1; +select *, check_row_ts(row_start, row_end) from t1 for system_time all; +drop tables t1; + +--echo # +--echo # MDEV-21555 Assertion secondary index is out of sync on delete from versioned table +--echo # +create table t1 (a int, b int as (a + 1) virtual, key(a)) engine=innodb with system versioning; + +set foreign_key_checks= off; +insert into t1 (a) values (1), (2); +alter table t1 add foreign key (b) references t1 (a), algorithm=copy; +update t1 set a= null where a = 1; +delete from t1 where a is null; +set foreign_key_checks= on; + +delete history from t1; +delete from t1; + +# cleanup +drop table t1; + +--echo # +--echo # MDEV-30378 Versioned REPLACE succeeds with ON DELETE RESTRICT +--echo # constraint +--echo # +create table t0 (pk integer primary key) with system versioning engine=innodb; +create table t1 (pk integer primary key, + foreign key(pk) references t0(pk) + on delete restrict on update cascade) engine=innodb; +create table t2 (pk integer); + +insert into t0 (pk) values (1); +insert into t1 (pk) values (1); +insert into t2 (pk) values (1); + +--error ER_ROW_IS_REFERENCED_2 +delete from t0; + +--error ER_ROW_IS_REFERENCED_2 +replace t0 values (1); + +--disable_ps2_protocol +select * into outfile 'load_t0' from t0 ; +--enable_ps2_protocol +--error ER_ROW_IS_REFERENCED_2 +load data infile 'load_t0' replace into table t0; + +--error ER_ROW_IS_REFERENCED_2 +delete t0, t2 from t0 join t2; + +select pk from t0; + +--echo # Cleanup +drop table t1, t0, t2; +--let $datadir= `select @@datadir` +--remove_file $datadir/test/load_t0 + + +--echo # create_select for a temporary table didn't set up pos_in_locked_tables. +create table t (a int unique) engine=innodb + replace select 1 as a, 2 as b union select 1 as a, 3 as c; +select * from t; +drop table t; + +create temporary table t (a int unique) engine=innodb + replace select 1 as a, 2 as b union select 1 as a, 3 as c; +select * from t; +drop table t; + +--echo # +--echo # MDEV-20729 Fix REFERENCES constraint in column definition +--echo # +create table t1(id int); +--echo # system fields can't be foreign keys: +--replace_result $sys_datatype_expl SYS_DATATYPE +--error ER_PARSE_ERROR,ER_PARSE_ERROR +eval create or replace table t2( + x int, + sys_start $sys_datatype_expl as row start references t1(id), + sys_end $sys_datatype_expl as row end, + period for system_time(sys_start, sys_end) +) engine innodb with system versioning; +--replace_result $sys_datatype_expl SYS_DATATYPE +--error ER_PARSE_ERROR,ER_PARSE_ERROR +eval create or replace table t2( + x int, + sys_start $sys_datatype_expl as row start, + sys_end $sys_datatype_expl as row end references t1(id), + period for system_time(sys_start, sys_end) +) engine innodb with system versioning; +--replace_result $sys_datatype_expl SYS_DATATYPE +--error ER_CANT_CREATE_TABLE +eval create or replace table t2( + x int, + sys_start $sys_datatype_expl as row start, + sys_end $sys_datatype_expl as row end, + period for system_time(sys_start, sys_end), + foreign key (sys_start) references t1(id) +) engine innodb with system versioning; +--replace_result $sys_datatype_expl SYS_DATATYPE +--error ER_CANT_CREATE_TABLE +eval create or replace table t2( + x int, + sys_start $sys_datatype_expl as row start, + sys_end $sys_datatype_expl as row end, + period for system_time(sys_start, sys_end), + foreign key (sys_end) references t1(id) +) engine innodb with system versioning; +drop table t1; + +--echo # End of 10.5 tests + +--source suite/versioning/common_finish.inc -- cgit v1.2.3