From abf95afa2a1c23f3c7aa42f44fc665450ebeeea1 Mon Sep 17 00:00:00 2001 From: halfspawn Date: Fri, 7 Jul 2017 17:50:09 +0200 Subject: MDEV-12137 DELETE statement with the same source and target single-table deletes only --- mysql-test/t/merge.test | 61 +++++++++++++++++++++++++++++++++++-------------- 1 file changed, 44 insertions(+), 17 deletions(-) (limited to 'mysql-test/t/merge.test') diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 09f313616f1..c35dd39170b 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -2763,39 +2763,66 @@ update m1 set a = ((select max(a) from tmp, t2)); update m1 set a = ((select max(a) from v1)); --error ER_VIEW_PREVENT_UPDATE update m1 set a = ((select max(a) from tmp, v1)); - - ---error ER_UPDATE_TABLE_USED +# Works since MDEV-12137 +# previously :ER_UPDATE_TABLE_USED delete from m1 where a = (select max(a) from m1); ---error ER_UPDATE_TABLE_USED +insert into t1 (a) values (1); +insert into t2 (a) values (1); +# previously :ER_UPDATE_TABLE_USED delete from m1 where a = (select max(a) from m2); ---error ER_UPDATE_TABLE_USED +insert into t1 (a) values (1); +insert into t2 (a) values (1); +# previously :ER_UPDATE_TABLE_USED delete from m1 where a = (select max(a) from t1); ---error ER_UPDATE_TABLE_USED +insert into t1 (a) values (1); +insert into t2 (a) values (1); +# previously :ER_UPDATE_TABLE_USED delete from m1 where a = (select max(a) from t2); +insert into t1 (a) values (1); +insert into t2 (a) values (1); ---error ER_UPDATE_TABLE_USED +# previously :ER_UPDATE_TABLE_USED delete from m1 where a = (select max(a) from t3, m1); ---error ER_UPDATE_TABLE_USED +insert into t1 (a) values (1); +insert into t2 (a) values (1); +# previously :ER_UPDATE_TABLE_USED delete from m1 where a = (select max(a) from t3, m2); ---error ER_UPDATE_TABLE_USED +insert into t1 (a) values (1); +insert into t2 (a) values (1); +# previously :ER_UPDATE_TABLE_USED delete from m1 where a = (select max(a) from t3, t1); ---error ER_UPDATE_TABLE_USED +insert into t1 (a) values (1); +insert into t2 (a) values (1); +# previously :ER_UPDATE_TABLE_USED delete from m1 where a = (select max(a) from t3, t2); +insert into t1 (a) values (1); +insert into t2 (a) values (1); ---error ER_UPDATE_TABLE_USED +# previously :ER_UPDATE_TABLE_USED delete from m1 where a = (select max(a) from tmp, m1); ---error ER_UPDATE_TABLE_USED +insert into t1 (a) values (1); +insert into t2 (a) values (1); +# previously :ER_UPDATE_TABLE_USED delete from m1 where a = (select max(a) from tmp, m2); ---error ER_UPDATE_TABLE_USED +insert into t1 (a) values (1); +insert into t2 (a) values (1); +# previously :ER_UPDATE_TABLE_USED delete from m1 where a = (select max(a) from tmp, t1); ---error ER_UPDATE_TABLE_USED +insert into t1 (a) values (1); +insert into t2 (a) values (1); +# previously :ER_UPDATE_TABLE_USED delete from m1 where a = (select max(a) from tmp, t2); - ---error ER_VIEW_PREVENT_UPDATE +insert into t1 (a) values (1); +insert into t2 (a) values (1); + +# previously : ER_VIEW_PREVENT_UPDATE delete from m1 where a = (select max(a) from v1); ---error ER_VIEW_PREVENT_UPDATE +insert into t1 (a) values (1); +insert into t2 (a) values (1); +# previously : ER_VIEW_PREVENT_UPDATE delete from m1 where a = (select max(a) from tmp, v1); +insert into t1 (a) values (1); +insert into t2 (a) values (1); drop view v1; drop temporary table tmp; -- cgit v1.2.1 From c65cce3698edde272b8452c1fecec3d9522cea6e Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 7 Jul 2017 19:55:31 +0200 Subject: MDEV-12137 DELETE statement with the same source and target * various cleanups (mostly cosmetic) * remove useless tests (that were tesing the error condition) * optimize delete_use_source test (from 6 mins to 50 seconds, mainly by removing two huge rollbacks at the end). --- mysql-test/t/merge.test | 60 ------------------------------------------------- 1 file changed, 60 deletions(-) (limited to 'mysql-test/t/merge.test') diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index c35dd39170b..95c78caf034 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -2763,66 +2763,6 @@ update m1 set a = ((select max(a) from tmp, t2)); update m1 set a = ((select max(a) from v1)); --error ER_VIEW_PREVENT_UPDATE update m1 set a = ((select max(a) from tmp, v1)); -# Works since MDEV-12137 -# previously :ER_UPDATE_TABLE_USED -delete from m1 where a = (select max(a) from m1); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -# previously :ER_UPDATE_TABLE_USED -delete from m1 where a = (select max(a) from m2); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -# previously :ER_UPDATE_TABLE_USED -delete from m1 where a = (select max(a) from t1); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -# previously :ER_UPDATE_TABLE_USED -delete from m1 where a = (select max(a) from t2); -insert into t1 (a) values (1); -insert into t2 (a) values (1); - -# previously :ER_UPDATE_TABLE_USED -delete from m1 where a = (select max(a) from t3, m1); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -# previously :ER_UPDATE_TABLE_USED -delete from m1 where a = (select max(a) from t3, m2); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -# previously :ER_UPDATE_TABLE_USED -delete from m1 where a = (select max(a) from t3, t1); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -# previously :ER_UPDATE_TABLE_USED -delete from m1 where a = (select max(a) from t3, t2); -insert into t1 (a) values (1); -insert into t2 (a) values (1); - -# previously :ER_UPDATE_TABLE_USED -delete from m1 where a = (select max(a) from tmp, m1); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -# previously :ER_UPDATE_TABLE_USED -delete from m1 where a = (select max(a) from tmp, m2); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -# previously :ER_UPDATE_TABLE_USED -delete from m1 where a = (select max(a) from tmp, t1); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -# previously :ER_UPDATE_TABLE_USED -delete from m1 where a = (select max(a) from tmp, t2); -insert into t1 (a) values (1); -insert into t2 (a) values (1); - -# previously : ER_VIEW_PREVENT_UPDATE -delete from m1 where a = (select max(a) from v1); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -# previously : ER_VIEW_PREVENT_UPDATE -delete from m1 where a = (select max(a) from tmp, v1); -insert into t1 (a) values (1); -insert into t2 (a) values (1); drop view v1; drop temporary table tmp; -- cgit v1.2.1