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 - mysql-test/t/delete_use_source.test | 172 +++++++++++++++++++++++++ mysql-test/t/lowercase_view.test | 4 +- mysql-test/t/merge.test | 61 ++++++--- mysql-test/t/subselect.test | 8 +- mysql-test/t/view.test | 7 +- 15 files changed, 451 insertions(+), 57 deletions(-) create mode 100644 mysql-test/r/delete_use_source.result create mode 100644 mysql-test/t/delete_use_source.test (limited to 'mysql-test') 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; diff --git a/mysql-test/t/delete_use_source.test b/mysql-test/t/delete_use_source.test new file mode 100644 index 00000000000..ddd7858ee77 --- /dev/null +++ b/mysql-test/t/delete_use_source.test @@ -0,0 +1,172 @@ +-- source include/have_innodb.inc +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); +delimiter /; +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; +/ +delimiter ;/ +set @count=500; +call gendata(0,@count); +call gendata(1,50); +call gendata(2,20); +call gendata(3,20); +commit; + +--echo # +--echo # Delete with limit (quick select - range acces) +--echo # + +start transaction; +--enable_info +delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1; +delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1; +--disable_info +select count(*) from view_delete where c1=0; +rollback; + +--echo # +--echo # Delete +--echo # + +start transaction; +--enable_info +delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 ; +--disable_info +rollback; + +--echo # +--echo # Delete with exists +--echo # + +start transaction; +select count(*) from view_delete where c1=2; +--enable_info +delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10); +delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10); +--disable_info +select count(*) from view_delete where c1=2; +rollback; + +--echo # +--echo # Delete throw a view with limit (range access) +--echo # + +start transaction; +--echo # Acces by range (quick_select), initied = INDEX +--echo # +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+ +--echo # | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +--echo # +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+ +--echo # | 1 | PRIMARY | tab_delete | range | tab_delete_c1 | tab_delete_c1 | 4 | NULL | 550 | Using where | +--echo # | 2 | DEPENDENT SUBQUERY | b | ref | tab_delete_c1 | tab_delete_c1 | 4 | test.tab_delete.c1 | 73 | Using index | +--echo # +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+ +# explain delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1; +--enable_info +delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1; +delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1; +--disable_info +select count(*) from view_delete where c1=0; +rollback; + +--echo # +--echo # Delete throw a view (ALL access) +--echo # + +start transaction; +--echo # Acces by pointer, initied = RND +--echo # +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+ +--echo # | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +--echo # +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+ +--echo # | 1 | PRIMARY | tab_delete | ALL | tab_delete_c1 | NULL | NULL | NULL | 589 | Using where | +--echo # | 2 | DEPENDENT SUBQUERY | b | ref | tab_delete_c1 | tab_delete_c1 | 4 | test.tab_delete.c1 | 295 | Using index | +--echo # +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+ +# explain delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500; +--enable_info +delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 ; +--disable_info +select count(*) from view_delete where c1=0; +rollback; + + +--echo # +--echo # Delete failed due to trigger +--echo # + +start transaction; +--enable_info +--error ER_SIGNAL_EXCEPTION +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; +--disable_info +rollback; +start transaction; +--enable_info +--error ER_SIGNAL_EXCEPTION +delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) > 0 order by c1 desc limit 100; +--disable_info +select c1,count(*) from tab_delete group by c1; +rollback; + +--echo # +--echo # Delete throw a view with returning +--echo # + +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; +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; +rollback; + + +--echo # +--echo # Delete from table with more than 150000 rows +--echo # +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; + +--echo with high memory for sort_buffer_size +SET SESSION sort_buffer_size = 1024000; +start transaction; +--enable_info +delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10); +--disable_info +rollback; + +--echo with few memory for sort_buffer_size +SET SESSION sort_buffer_size = 1024; +start transaction; +--enable_info +delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10); +--disable_info +rollback; + +drop procedure if exists gendata; +drop view if exists view_delete; +drop table if exists tab_delete; diff --git a/mysql-test/t/lowercase_view.test b/mysql-test/t/lowercase_view.test index 52be911cde0..6d53e6d9130 100644 --- a/mysql-test/t/lowercase_view.test +++ b/mysql-test/t/lowercase_view.test @@ -73,11 +73,9 @@ update v3aA set v3Aa.col1 = (select max(col1) from t1aA); update v3aA set v3Aa.col1 = (select max(col1) from v2aA); -- error 1093 update v3aA set v3Aa.col1 = (select max(col1) from v3aA); --- error 1443 +# Works since MDEV-12137 (no more error 1093) delete from v2Aa where col1 = (select max(col1) from v1Aa); --- error 1443 delete from v2aA where col1 = (select max(col1) from t1Aa); --- error 1093 delete from v2Aa where col1 = (select max(col1) from v2aA); -- error 1443 delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1; 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; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 282013222de..59694635c70 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -340,12 +340,16 @@ insert into t1 values (0, 10),(1, 11),(2, 12); insert into t2 values (1, 21),(2, 12),(3, 23); select * from t1; select * from t1 where b = (select b from t2 where t1.a = t2.a); --- error ER_UPDATE_TABLE_USED +# Works since MDEV-12137 +# previously : ER_UPDATE_TABLE_USED +--enable_info delete from t1 where b in (select b from t1); +--disable_info +insert into t1 values (0, 10),(1, 11),(2, 12); -- error ER_SUBQUERY_NO_1_ROW delete from t1 where b = (select b from t2); delete from t1 where b = (select b from t2 where t1.a = t2.a); -select * from t1; +select * from t1 order by b; drop table t1, t2; #multi-delete with subselects diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index fa130afc84b..9ed37a2bf65 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -914,11 +914,12 @@ update v3 set v3.col1 = (select max(col1) from t1); update v3 set v3.col1 = (select max(col1) from v2); -- error ER_UPDATE_TABLE_USED update v3 set v3.col1 = (select max(col1) from v3); --- error ER_VIEW_PREVENT_UPDATE +# Works since MDEV-12137 +# Previously error ER_VIEW_PREVENT_UPDATE delete from v2 where col1 = (select max(col1) from v1); --- error ER_VIEW_PREVENT_UPDATE +# Previously error ER_VIEW_PREVENT_UPDATE delete from v2 where col1 = (select max(col1) from t1); --- error ER_UPDATE_TABLE_USED +# Previously error ER_UPDATE_TABLE_USED delete from v2 where col1 = (select max(col1) from v2); -- error ER_VIEW_PREVENT_UPDATE delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.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 - mysql-test/t/delete_use_source.test | 168 +++++++++++++--------------------- mysql-test/t/lowercase_view.test | 4 - mysql-test/t/merge.test | 60 ------------ mysql-test/t/view.test | 7 -- 8 files changed, 123 insertions(+), 309 deletions(-) (limited to 'mysql-test') 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; diff --git a/mysql-test/t/delete_use_source.test b/mysql-test/t/delete_use_source.test index ddd7858ee77..a54d12c3d65 100644 --- a/mysql-test/t/delete_use_source.test +++ b/mysql-test/t/delete_use_source.test @@ -1,37 +1,12 @@ --- source include/have_innodb.inc -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); -delimiter /; -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; -/ -delimiter ;/ -set @count=500; -call gendata(0,@count); -call gendata(1,50); -call gendata(2,20); -call gendata(3,20); -commit; +--source include/have_sequence.inc +--source include/have_innodb.inc +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; --echo # --echo # Delete with limit (quick select - range acces) @@ -39,10 +14,10 @@ commit; start transaction; --enable_info -delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 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; +delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 limit 1; --disable_info -select count(*) from view_delete where c1=0; +select count(*) from v1 where c1=0; rollback; --echo # @@ -50,9 +25,8 @@ rollback; --echo # start transaction; ---enable_info -delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 ; ---disable_info +--enable_info ONCE +delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 ; rollback; --echo # @@ -60,12 +34,12 @@ rollback; --echo # start transaction; -select count(*) from view_delete where c1=2; +select count(*) from v1 where c1=2; --enable_info -delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10); -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); +delete from t1 where c1=2 and exists(select 'x' from t1 b where b.c2<10); --disable_info -select count(*) from view_delete where c1=2; +select count(*) from v1 where c1=2; rollback; --echo # @@ -73,19 +47,12 @@ rollback; --echo # start transaction; ---echo # Acces by range (quick_select), initied = INDEX ---echo # +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+ ---echo # | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---echo # +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+ ---echo # | 1 | PRIMARY | tab_delete | range | tab_delete_c1 | tab_delete_c1 | 4 | NULL | 550 | Using where | ---echo # | 2 | DEPENDENT SUBQUERY | b | ref | tab_delete_c1 | tab_delete_c1 | 4 | test.tab_delete.c1 | 73 | Using index | ---echo # +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+ -# explain 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; --enable_info -delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 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; +delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1; --disable_info -select count(*) from view_delete where c1=0; +select count(*) from v1 where c1=0; rollback; --echo # @@ -93,80 +60,73 @@ rollback; --echo # start transaction; ---echo # Acces by pointer, initied = RND ---echo # +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+ ---echo # | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---echo # +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+ ---echo # | 1 | PRIMARY | tab_delete | ALL | tab_delete_c1 | NULL | NULL | NULL | 589 | Using where | ---echo # | 2 | DEPENDENT SUBQUERY | b | ref | tab_delete_c1 | tab_delete_c1 | 4 | test.tab_delete.c1 | 295 | Using index | ---echo # +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+ -# explain delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500; ---enable_info -delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 ; ---disable_info -select count(*) from view_delete where c1=0; +explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500; +--enable_info ONCE +delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 ; +select count(*) from v1 where c1=0; rollback; - --echo # --echo # Delete failed due to trigger --echo # +delimiter /; +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; +/ +delimiter ;/ + start transaction; ---enable_info --error ER_SIGNAL_EXCEPTION -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; ---disable_info +delete from t1 where c1=1 and (select count(*) from t1 b where b.c1=t1.c1) > 0 order by c2 asc limit 10; rollback; start transaction; ---enable_info --error ER_SIGNAL_EXCEPTION -delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) > 0 order by c1 desc limit 100; ---disable_info -select c1,count(*) from tab_delete group by c1; +delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) > 0 order by c1 desc limit 100; +select c1,count(*) from t1 group by c1; rollback; +drop trigger trg; + --echo # --echo # Delete throw a view with returning --echo # 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; 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; rollback; +drop view v1; +drop table t1; --echo # --echo # Delete from table with more than 150000 rows --echo # -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; - ---echo with high memory for sort_buffer_size -SET SESSION sort_buffer_size = 1024000; -start transaction; ---enable_info -delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10); ---disable_info -rollback; +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; ---echo with few memory for sort_buffer_size -SET SESSION sort_buffer_size = 1024; -start transaction; ---enable_info -delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10); ---disable_info -rollback; +--echo # with a lot of memory for sort_buffer_size +set session sort_buffer_size = 1024000; +--enable_info ONCE +delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10); + +--echo # with little memory for sort_buffer_size +insert t1 select 0,seq from seq_1_to_128000; +set session sort_buffer_size = 1024; +--enable_info ONCE +delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10); -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/t/lowercase_view.test b/mysql-test/t/lowercase_view.test index 6d53e6d9130..4c91383db60 100644 --- a/mysql-test/t/lowercase_view.test +++ b/mysql-test/t/lowercase_view.test @@ -73,10 +73,6 @@ update v3aA set v3Aa.col1 = (select max(col1) from t1aA); update v3aA set v3Aa.col1 = (select max(col1) from v2aA); -- error 1093 update v3aA set v3Aa.col1 = (select max(col1) from v3aA); -# Works since MDEV-12137 (no more error 1093) -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); -- error 1443 delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1; -- error 1443 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; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 9ed37a2bf65..00a040ccfdc 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -914,13 +914,6 @@ update v3 set v3.col1 = (select max(col1) from t1); update v3 set v3.col1 = (select max(col1) from v2); -- error ER_UPDATE_TABLE_USED update v3 set v3.col1 = (select max(col1) from v3); -# Works since MDEV-12137 -# Previously error ER_VIEW_PREVENT_UPDATE -delete from v2 where col1 = (select max(col1) from v1); -# Previously error ER_VIEW_PREVENT_UPDATE -delete from v2 where col1 = (select max(col1) from t1); -# Previously error ER_UPDATE_TABLE_USED -delete from v2 where col1 = (select max(col1) from v2); -- error ER_VIEW_PREVENT_UPDATE delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1; -- error ER_VIEW_PREVENT_UPDATE -- cgit v1.2.1