diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2023-01-13 10:18:30 +0200 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2023-01-13 10:18:30 +0200 |
commit | 73ecab3d26c055928673a2629f4513aa7008dbf0 (patch) | |
tree | b04760b3369cd1d8fc03c7dec204f5a371335b29 /mysql-test/suite/versioning | |
parent | 7d1df207c4ded0ac8aa61f0d35bcae7eda974c54 (diff) | |
parent | 71e8e4934db06c02db1b51716e9d4b3992505161 (diff) | |
download | mariadb-git-73ecab3d26c055928673a2629f4513aa7008dbf0.tar.gz |
Merge 10.4 into 10.5
Diffstat (limited to 'mysql-test/suite/versioning')
-rw-r--r-- | mysql-test/suite/versioning/r/foreign.result | 44 | ||||
-rw-r--r-- | mysql-test/suite/versioning/t/foreign.test | 52 |
2 files changed, 90 insertions, 6 deletions
diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result index 8a779be760a..b17deba1c1e 100644 --- a/mysql-test/suite/versioning/r/foreign.result +++ b/mysql-test/suite/versioning/r/foreign.result @@ -496,11 +496,48 @@ delete history from t1; delete from t1; drop table t1; # +# MDEV-30378 Versioned REPLACE succeeds with ON DELETE RESTRICT +# constraint +# +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); +delete from t0; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE) +replace t0 values (1); +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE) +select * into outfile 'load_t0' from t0 ; +load data infile 'load_t0' replace into table t0; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE) +delete t0, t2 from t0 join t2; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE) +select pk from t0; +pk +1 +# Cleanup +drop table t1, t0, t2; +# 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; +a b +1 3 +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; +a b +1 3 +drop table t; +# # MDEV-20729 Fix REFERENCES constraint in column definition # -create or replace table t1( -id int -); +create table t1(id int); # system fields can't be foreign keys: create or replace table t2( x int, @@ -533,3 +570,4 @@ foreign key (sys_end) references t1(id) ) engine innodb with system versioning; ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") drop table t1; +# End of 10.5 tests diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test index a354489521c..f266c668105 100644 --- a/mysql-test/suite/versioning/t/foreign.test +++ b/mysql-test/suite/versioning/t/foreign.test @@ -528,11 +528,55 @@ delete from t1; 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); + +select * into outfile 'load_t0' from t0 ; +--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 or replace table t1( - id int -); +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 @@ -570,4 +614,6 @@ eval create or replace table t2( ) engine innodb with system versioning; drop table t1; +--echo # End of 10.5 tests + --source suite/versioning/common_finish.inc |