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/r/delete_use_source.result | 178 ++++++++++++++++++++++++++ mysql-test/r/lowercase_view.result | 3 - mysql-test/r/merge.result | 42 ++++-- mysql-test/r/subselect.result | 5 +- mysql-test/r/subselect_no_exists_to_in.result | 5 +- mysql-test/r/subselect_no_mat.result | 5 +- mysql-test/r/subselect_no_opts.result | 5 +- mysql-test/r/subselect_no_scache.result | 5 +- mysql-test/r/subselect_no_semijoin.result | 5 +- mysql-test/r/view.result | 3 - 10 files changed, 224 insertions(+), 32 deletions(-) create mode 100644 mysql-test/r/delete_use_source.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/delete_use_source.result b/mysql-test/r/delete_use_source.result new file mode 100644 index 00000000000..c7ca43083cf --- /dev/null +++ b/mysql-test/r/delete_use_source.result @@ -0,0 +1,178 @@ +set sql_mode=oracle; +use test; +create or replace table tab_delete(c1 integer not null,c2 integer not null) engine=InnoDb; +create index tab_delete_c1 on tab_delete(c1); +create or replace view view_delete as select * from tab_delete where c1 in (0,1); +CREATE or replace PROCEDURE gendata(a int, count int ) AS +i INT:=0; +BEGIN +FOR i IN 1 .. count +LOOP +insert into tab_delete values (a,i); +END LOOP; +END; +/ +create or replace trigger trg after delete on tab_delete for each row +begin +declare c int; +begin +if old.c1 = 1 then +select count(*) into c from tab_delete where c1!=old.c1; +SIGNAL SQLSTATE '45000' set table_name=c; +end if; +end; +end; +/ +set @count=500; +call gendata(0,@count); +call gendata(1,50); +call gendata(2,20); +call gendata(3,20); +commit; +# +# Delete with limit (quick select - range acces) +# +start transaction; +delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1; +affected rows: 1 +delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1; +affected rows: 0 +select count(*) from view_delete where c1=0; +count(*) +499 +rollback; +# +# Delete +# +start transaction; +delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 ; +affected rows: 500 +rollback; +# +# Delete with exists +# +start transaction; +select count(*) from view_delete where c1=2; +count(*) +0 +delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10); +affected rows: 20 +delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10); +affected rows: 0 +select count(*) from view_delete where c1=2; +count(*) +0 +rollback; +# +# Delete throw a view with limit (range access) +# +start transaction; +# Acces by range (quick_select), initied = INDEX +# +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+ +# | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +# +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+ +# | 1 | PRIMARY | tab_delete | range | tab_delete_c1 | tab_delete_c1 | 4 | NULL | 550 | Using where | +# | 2 | DEPENDENT SUBQUERY | b | ref | tab_delete_c1 | tab_delete_c1 | 4 | test.tab_delete.c1 | 73 | Using index | +# +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+ +delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1; +affected rows: 1 +delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1; +affected rows: 0 +select count(*) from view_delete where c1=0; +count(*) +499 +rollback; +# +# Delete throw a view (ALL access) +# +start transaction; +# Acces by pointer, initied = RND +# +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+ +# | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +# +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+ +# | 1 | PRIMARY | tab_delete | ALL | tab_delete_c1 | NULL | NULL | NULL | 589 | Using where | +# | 2 | DEPENDENT SUBQUERY | b | ref | tab_delete_c1 | tab_delete_c1 | 4 | test.tab_delete.c1 | 295 | Using index | +# +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+ +delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 ; +affected rows: 500 +select count(*) from view_delete where c1=0; +count(*) +0 +rollback; +# +# Delete failed due to trigger +# +start transaction; +delete from tab_delete where c1=1 and (select count(*) from tab_delete b where b.c1=tab_delete.c1) > 0 order by c2 asc limit 10; +ERROR 45000: Unhandled user-defined exception condition +rollback; +start transaction; +delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) > 0 order by c1 desc limit 100; +ERROR 45000: Unhandled user-defined exception condition +select c1,count(*) from tab_delete group by c1; +c1 count(*) +0 500 +1 50 +2 20 +3 20 +rollback; +# +# Delete throw a view with returning +# +start transaction; +delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 order by c2 asc limit 10 returning c1,c2; +c1 c2 +0 1 +0 2 +0 3 +0 4 +0 5 +0 6 +0 7 +0 8 +0 9 +0 10 +rollback; +start transaction; +delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 order by c2 desc limit 10 returning c1,c2; +c1 c2 +0 491 +0 492 +0 493 +0 494 +0 495 +0 496 +0 497 +0 498 +0 499 +0 500 +rollback; +# +# Delete from table with more than 150000 rows +# +insert into tab_delete select * from tab_delete; +insert into tab_delete select * from tab_delete; +insert into tab_delete select * from tab_delete; +insert into tab_delete select * from tab_delete; +insert into tab_delete select * from tab_delete; +insert into tab_delete select * from tab_delete; +insert into tab_delete select * from tab_delete; +insert into tab_delete select * from tab_delete; +select count(*) from tab_delete; +count(*) +151040 +with high memory for sort_buffer_size +SET SESSION sort_buffer_size = 1024000; +start transaction; +delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10); +affected rows: 128000 +rollback; +with few memory for sort_buffer_size +SET SESSION sort_buffer_size = 1024; +start transaction; +delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10); +affected rows: 128000 +rollback; +drop procedure if exists gendata; +drop view if exists view_delete; +drop table if exists tab_delete; diff --git a/mysql-test/r/lowercase_view.result b/mysql-test/r/lowercase_view.result index df303807407..3b6aeb9548b 100644 --- a/mysql-test/r/lowercase_view.result +++ b/mysql-test/r/lowercase_view.result @@ -66,11 +66,8 @@ ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table ' update v3aA set v3Aa.col1 = (select max(col1) from v3aA); ERROR HY000: Table 'v3aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data delete from v2Aa where col1 = (select max(col1) from v1Aa); -ERROR HY000: The definition of table 'v1Aa' prevents operation DELETE on table 'v2Aa' delete from v2aA where col1 = (select max(col1) from t1Aa); -ERROR HY000: The definition of table 'v2aA' prevents operation DELETE on table 'v2aA' delete from v2Aa where col1 = (select max(col1) from v2aA); -ERROR HY000: Table 'v2Aa' is specified twice, both as a target for 'DELETE' and as a separate source for data delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1; ERROR HY000: The definition of table 'v1aA' prevents operation DELETE on table 'v2aA' delete t1aA from t1Aa,t2Aa where (select max(col1) from v1Aa) > 0 and t1aA.col1 = t2aA.col1; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index f468f47c4c9..6da7eb38655 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -3743,33 +3743,47 @@ ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1 update m1 set a = ((select max(a) from tmp, v1)); ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1' delete from m1 where a = (select max(a) from m1); -ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data +insert into t1 (a) values (1); +insert into t2 (a) values (1); delete from m1 where a = (select max(a) from m2); -ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data +insert into t1 (a) values (1); +insert into t2 (a) values (1); delete from m1 where a = (select max(a) from t1); -ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data +insert into t1 (a) values (1); +insert into t2 (a) values (1); delete from m1 where a = (select max(a) from t2); -ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data +insert into t1 (a) values (1); +insert into t2 (a) values (1); delete from m1 where a = (select max(a) from t3, m1); -ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data +insert into t1 (a) values (1); +insert into t2 (a) values (1); delete from m1 where a = (select max(a) from t3, m2); -ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data +insert into t1 (a) values (1); +insert into t2 (a) values (1); delete from m1 where a = (select max(a) from t3, t1); -ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data +insert into t1 (a) values (1); +insert into t2 (a) values (1); delete from m1 where a = (select max(a) from t3, t2); -ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data +insert into t1 (a) values (1); +insert into t2 (a) values (1); delete from m1 where a = (select max(a) from tmp, m1); -ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data +insert into t1 (a) values (1); +insert into t2 (a) values (1); delete from m1 where a = (select max(a) from tmp, m2); -ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data +insert into t1 (a) values (1); +insert into t2 (a) values (1); delete from m1 where a = (select max(a) from tmp, t1); -ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data +insert into t1 (a) values (1); +insert into t2 (a) values (1); delete from m1 where a = (select max(a) from tmp, t2); -ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data +insert into t1 (a) values (1); +insert into t2 (a) values (1); delete from m1 where a = (select max(a) from v1); -ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'm1' +insert into t1 (a) values (1); +insert into t2 (a) values (1); delete from m1 where a = (select max(a) from tmp, v1); -ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'm1' +insert into t1 (a) values (1); +insert into t2 (a) values (1); drop view v1; drop temporary table tmp; drop table t1, t2, t3, m1, m2; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 95a872f1498..98a279c28e9 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -607,11 +607,12 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a); a b 2 12 delete from t1 where b in (select b from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data +affected rows: 3 +insert into t1 values (0, 10),(1, 11),(2, 12); delete from t1 where b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete from t1 where b = (select b from t2 where t1.a = t2.a); -select * from t1; +select * from t1 order by b; a b 0 10 1 11 diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index 7f07b974bb2..5cee4076ed8 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -611,11 +611,12 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a); a b 2 12 delete from t1 where b in (select b from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data +affected rows: 3 +insert into t1 values (0, 10),(1, 11),(2, 12); delete from t1 where b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete from t1 where b = (select b from t2 where t1.a = t2.a); -select * from t1; +select * from t1 order by b; a b 0 10 1 11 diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 57c7a979d61..e7409b0b09c 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -614,11 +614,12 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a); a b 2 12 delete from t1 where b in (select b from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data +affected rows: 3 +insert into t1 values (0, 10),(1, 11),(2, 12); delete from t1 where b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete from t1 where b = (select b from t2 where t1.a = t2.a); -select * from t1; +select * from t1 order by b; a b 0 10 1 11 diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 6e8be4a02a7..3bdc91686d7 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -610,11 +610,12 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a); a b 2 12 delete from t1 where b in (select b from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data +affected rows: 3 +insert into t1 values (0, 10),(1, 11),(2, 12); delete from t1 where b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete from t1 where b = (select b from t2 where t1.a = t2.a); -select * from t1; +select * from t1 order by b; a b 0 10 1 11 diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 1b437f6919d..3beba7c338d 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -613,11 +613,12 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a); a b 2 12 delete from t1 where b in (select b from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data +affected rows: 3 +insert into t1 values (0, 10),(1, 11),(2, 12); delete from t1 where b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete from t1 where b = (select b from t2 where t1.a = t2.a); -select * from t1; +select * from t1 order by b; a b 0 10 1 11 diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 6094c7e029d..f2d97078772 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -610,11 +610,12 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a); a b 2 12 delete from t1 where b in (select b from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data +affected rows: 3 +insert into t1 values (0, 10),(1, 11),(2, 12); delete from t1 where b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete from t1 where b = (select b from t2 where t1.a = t2.a); -select * from t1; +select * from t1 order by b; a b 0 10 1 11 diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 77794ac1c82..50a48ade3bd 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -994,11 +994,8 @@ ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v3 update v3 set v3.col1 = (select max(col1) from v3); ERROR HY000: Table 'v3' is specified twice, both as a target for 'UPDATE' and as a separate source for data delete from v2 where col1 = (select max(col1) from v1); -ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v2' delete from v2 where col1 = (select max(col1) from t1); -ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 'v2' delete from v2 where col1 = (select max(col1) from v2); -ERROR HY000: Table 'v2' is specified twice, both as a target for 'DELETE' and as a separate source for data delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v2' delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1; -- 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/r/delete_use_source.result | 145 ++++++++++++++-------------------- mysql-test/r/lowercase_view.result | 3 - mysql-test/r/merge.result | 42 ---------- mysql-test/r/view.result | 3 - 4 files changed, 59 insertions(+), 134 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/delete_use_source.result b/mysql-test/r/delete_use_source.result index c7ca43083cf..7886caf6ba4 100644 --- a/mysql-test/r/delete_use_source.result +++ b/mysql-test/r/delete_use_source.result @@ -1,43 +1,18 @@ -set sql_mode=oracle; -use test; -create or replace table tab_delete(c1 integer not null,c2 integer not null) engine=InnoDb; -create index tab_delete_c1 on tab_delete(c1); -create or replace view view_delete as select * from tab_delete where c1 in (0,1); -CREATE or replace PROCEDURE gendata(a int, count int ) AS -i INT:=0; -BEGIN -FOR i IN 1 .. count -LOOP -insert into tab_delete values (a,i); -END LOOP; -END; -/ -create or replace trigger trg after delete on tab_delete for each row -begin -declare c int; -begin -if old.c1 = 1 then -select count(*) into c from tab_delete where c1!=old.c1; -SIGNAL SQLSTATE '45000' set table_name=c; -end if; -end; -end; -/ -set @count=500; -call gendata(0,@count); -call gendata(1,50); -call gendata(2,20); -call gendata(3,20); -commit; +create table t1(c1 integer not null,c2 integer not null, key (c1)) engine=InnoDb; +create view v1 as select * from t1 where c1 in (0,1); +insert t1 select 0,seq from seq_1_to_500; +insert t1 select 1,seq from seq_1_to_50; +insert t1 select 2,seq from seq_1_to_20; +insert t1 select 3,seq from seq_1_to_20; # # Delete with limit (quick select - range acces) # start transaction; -delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1; +delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 limit 1; affected rows: 1 -delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1; +delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 limit 1; affected rows: 0 -select count(*) from view_delete where c1=0; +select count(*) from v1 where c1=0; count(*) 499 rollback; @@ -45,21 +20,21 @@ rollback; # Delete # start transaction; -delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 ; +delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 ; affected rows: 500 rollback; # # Delete with exists # start transaction; -select count(*) from view_delete where c1=2; +select count(*) from v1 where c1=2; count(*) 0 -delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10); +delete from t1 where c1=2 and exists(select 'x' from t1 b where b.c2<10); affected rows: 20 -delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10); +delete from t1 where c1=2 and exists(select 'x' from t1 b where b.c2<10); affected rows: 0 -select count(*) from view_delete where c1=2; +select count(*) from v1 where c1=2; count(*) 0 rollback; @@ -67,18 +42,15 @@ rollback; # Delete throw a view with limit (range access) # start transaction; -# Acces by range (quick_select), initied = INDEX -# +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+ -# | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | -# +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+ -# | 1 | PRIMARY | tab_delete | range | tab_delete_c1 | tab_delete_c1 | 4 | NULL | 550 | Using where | -# | 2 | DEPENDENT SUBQUERY | b | ref | tab_delete_c1 | tab_delete_c1 | 4 | test.tab_delete.c1 | 73 | Using index | -# +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+ -delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1; +explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range c1 c1 4 NULL 550 Using where +2 DEPENDENT SUBQUERY b ref c1 c1 4 test.t1.c1 82 Using index +delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1; affected rows: 1 -delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1; +delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1; affected rows: 0 -select count(*) from view_delete where c1=0; +select count(*) from v1 where c1=0; count(*) 499 rollback; @@ -86,41 +58,50 @@ rollback; # Delete throw a view (ALL access) # start transaction; -# Acces by pointer, initied = RND -# +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+ -# | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | -# +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+ -# | 1 | PRIMARY | tab_delete | ALL | tab_delete_c1 | NULL | NULL | NULL | 589 | Using where | -# | 2 | DEPENDENT SUBQUERY | b | ref | tab_delete_c1 | tab_delete_c1 | 4 | test.tab_delete.c1 | 295 | Using index | -# +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+ -delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 ; +explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL c1 NULL NULL NULL 717 Using where +2 DEPENDENT SUBQUERY b ref c1 c1 4 test.t1.c1 82 Using index +delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 ; affected rows: 500 -select count(*) from view_delete where c1=0; +select count(*) from v1 where c1=0; count(*) 0 rollback; # # Delete failed due to trigger # +create trigger trg after delete on t1 for each row +begin +declare c int; +begin +if old.c1 = 1 then +select count(*) into c from t1 where c1!=old.c1; +SIGNAL SQLSTATE '45000' set table_name=c; +end if; +end; +end; +/ start transaction; -delete from tab_delete where c1=1 and (select count(*) from tab_delete b where b.c1=tab_delete.c1) > 0 order by c2 asc limit 10; +delete from t1 where c1=1 and (select count(*) from t1 b where b.c1=t1.c1) > 0 order by c2 asc limit 10; ERROR 45000: Unhandled user-defined exception condition rollback; start transaction; -delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) > 0 order by c1 desc limit 100; +delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) > 0 order by c1 desc limit 100; ERROR 45000: Unhandled user-defined exception condition -select c1,count(*) from tab_delete group by c1; +select c1,count(*) from t1 group by c1; c1 count(*) 0 500 1 50 2 20 3 20 rollback; +drop trigger trg; # # Delete throw a view with returning # start transaction; -delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 order by c2 asc limit 10 returning c1,c2; +delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 order by c2 asc limit 10 returning c1,c2; c1 c2 0 1 0 2 @@ -134,7 +115,7 @@ c1 c2 0 10 rollback; start transaction; -delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 order by c2 desc limit 10 returning c1,c2; +delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 order by c2 desc limit 10 returning c1,c2; c1 c2 0 491 0 492 @@ -147,32 +128,24 @@ c1 c2 0 499 0 500 rollback; +drop view v1; +drop table t1; # # Delete from table with more than 150000 rows # -insert into tab_delete select * from tab_delete; -insert into tab_delete select * from tab_delete; -insert into tab_delete select * from tab_delete; -insert into tab_delete select * from tab_delete; -insert into tab_delete select * from tab_delete; -insert into tab_delete select * from tab_delete; -insert into tab_delete select * from tab_delete; -insert into tab_delete select * from tab_delete; -select count(*) from tab_delete; +create table t1(c1 integer not null,c2 integer not null, key (c1)); +insert t1 select 0,seq from seq_1_to_128000; +insert t1 select 1,seq from seq_1_to_25600; +select count(*) from t1; count(*) -151040 -with high memory for sort_buffer_size -SET SESSION sort_buffer_size = 1024000; -start transaction; -delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10); +153600 +# with a lot of memory for sort_buffer_size +set session sort_buffer_size = 1024000; +delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10); affected rows: 128000 -rollback; -with few memory for sort_buffer_size -SET SESSION sort_buffer_size = 1024; -start transaction; -delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10); +# with little memory for sort_buffer_size +insert t1 select 0,seq from seq_1_to_128000; +set session sort_buffer_size = 1024; +delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10); affected rows: 128000 -rollback; -drop procedure if exists gendata; -drop view if exists view_delete; -drop table if exists tab_delete; +drop table t1; diff --git a/mysql-test/r/lowercase_view.result b/mysql-test/r/lowercase_view.result index 3b6aeb9548b..6ccfe29b2cd 100644 --- a/mysql-test/r/lowercase_view.result +++ b/mysql-test/r/lowercase_view.result @@ -65,9 +65,6 @@ update v3aA set v3Aa.col1 = (select max(col1) from v2aA); ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 'v3aA' update v3aA set v3Aa.col1 = (select max(col1) from v3aA); ERROR HY000: Table 'v3aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data -delete from v2Aa where col1 = (select max(col1) from v1Aa); -delete from v2aA where col1 = (select max(col1) from t1Aa); -delete from v2Aa where col1 = (select max(col1) from v2aA); delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1; ERROR HY000: The definition of table 'v1aA' prevents operation DELETE on table 'v2aA' delete t1aA from t1Aa,t2Aa where (select max(col1) from v1Aa) > 0 and t1aA.col1 = t2aA.col1; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 6da7eb38655..f55251ac199 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -3742,48 +3742,6 @@ update m1 set a = ((select max(a) from v1)); ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1' update m1 set a = ((select max(a) from tmp, v1)); ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1' -delete from m1 where a = (select max(a) from m1); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -delete from m1 where a = (select max(a) from m2); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -delete from m1 where a = (select max(a) from t1); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -delete from m1 where a = (select max(a) from t2); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -delete from m1 where a = (select max(a) from t3, m1); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -delete from m1 where a = (select max(a) from t3, m2); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -delete from m1 where a = (select max(a) from t3, t1); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -delete from m1 where a = (select max(a) from t3, t2); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -delete from m1 where a = (select max(a) from tmp, m1); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -delete from m1 where a = (select max(a) from tmp, m2); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -delete from m1 where a = (select max(a) from tmp, t1); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -delete from m1 where a = (select max(a) from tmp, t2); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -delete from m1 where a = (select max(a) from v1); -insert into t1 (a) values (1); -insert into t2 (a) values (1); -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; drop table t1, t2, t3, m1, m2; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 50a48ade3bd..ef2e0935592 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -993,9 +993,6 @@ update v3 set v3.col1 = (select max(col1) from v2); ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v3' update v3 set v3.col1 = (select max(col1) from v3); ERROR HY000: Table 'v3' is specified twice, both as a target for 'UPDATE' and as a separate source for data -delete from v2 where col1 = (select max(col1) from v1); -delete from v2 where col1 = (select max(col1) from t1); -delete from v2 where col1 = (select max(col1) from v2); delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v2' delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1; -- cgit v1.2.1