diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/delete_use_source.result | 184 |
1 files changed, 182 insertions, 2 deletions
diff --git a/mysql-test/main/delete_use_source.result b/mysql-test/main/delete_use_source.result index 0990a551..329203af 100644 --- a/mysql-test/main/delete_use_source.result +++ b/mysql-test/main/delete_use_source.result @@ -49,7 +49,7 @@ rollback; start transaction; 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 600 Using where +1 PRIMARY t1 range c1 c1 4 NULL 600 Using index condition; Using where 2 DEPENDENT SUBQUERY b ref c1 c1 4 test.t1.c1 167 Using index delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1; affected rows: 1 @@ -65,7 +65,7 @@ rollback; start transaction; 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 # Using where +1 PRIMARY t1 range c1 c1 4 NULL # Using index condition; Using where 2 DEPENDENT SUBQUERY b ref c1 c1 4 test.t1.c1 # Using index delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 ; affected rows: 500 @@ -154,3 +154,183 @@ 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 drop table t1; +# +# MDEV-17954: multi-table DELETE with the same source and target +# +create table t1 (c1 int, c2 int, c3 int); +insert into t1 values +(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8); +# +# Single-table DELETE with the same source and target +# handled as multi-table DELETE +# +explain delete from t1 +where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1 and a.c2 < 3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 8 Using where; FirstMatch(t1) +delete from t1 +where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1 and a.c2 < 3); +select * from t1; +c1 c2 c3 +1 3 3 +2 3 6 +2 4 7 +2 5 8 +delete from t1; +insert into t1 values +(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8); +prepare stmt from "delete from t1 +where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1 and a.c2 < 3)"; +execute stmt; +select * from t1; +c1 c2 c3 +1 3 3 +2 3 6 +2 4 7 +2 5 8 +delete from t1; +insert into t1 values +(2,2,5), (2,3,6), (2,4,7), (2,5,8); +execute stmt; +select * from t1; +c1 c2 c3 +2 3 6 +2 4 7 +2 5 8 +deallocate prepare stmt; +delete from t1; +insert into t1 values +(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8); +# +# Multi-table DELETE with the same source and target +# +create table t2 (c1 int, c2 int, c3 int); +insert into t2 values +(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,5,8); +explain delete from t1 using t1,t2 +where t1.c2 = t2.c2 and t1.c1 > 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 7 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +delete from t1 using t1,t2 +where t1.c2 = t2.c2 and t1.c1 > 1; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +2 4 7 +delete from t1; +insert into t1 values +(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8); +prepare stmt from "delete from t1 using t1,t2 +where t1.c2 = t2.c2 and t1.c1 > 1"; +execute stmt; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +2 4 7 +delete from t1; +insert into t1 values +(2,2,5), (2,3,6), (2,4,7), (2,5,8); +execute stmt; +select * from t1; +c1 c2 c3 +2 4 7 +deallocate prepare stmt; +explain delete from t1 using t1,t2 +where t1.c2 = t2.c2 and t1.c1 > 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +1 SIMPLE t2 ALL NULL NULL NULL NULL 7 Using where +delete from t1 using t1,t2 +where t1.c2 = t2.c2 and t1.c1 > 1; +select * from t1; +c1 c2 c3 +2 4 7 +delete from t1; +insert into t1 values +(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8); +prepare stmt from "delete from t1 using t1,t2 +where t1.c2 = t2.c2 and t1.c1 > 1"; +execute stmt; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +2 4 7 +delete from t1; +insert into t1 values +(2,2,5), (2,3,6), (2,4,7), (2,5,8); +execute stmt; +select * from t1; +c1 c2 c3 +2 4 7 +deallocate prepare stmt; +delete from t1; +insert into t1 values +(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8); +explain delete from t1,t2 using t1,t2 +where t1.c2 = t2.c2 and t1.c1 > 1 and t2.c1 > 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 7 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +delete from t1,t2 using t1,t2 +where t1.c2 = t2.c2 and t1.c1 > 1 and t2.c1 > 1; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +2 4 7 +select * from t2; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +delete from t1; +insert into t1 values +(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8); +delete from t2; +insert into t2 values +(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,5,8); +prepare stmt from "delete from t1,t2 using t1,t2 +where t1.c2 = t2.c2 and t1.c1 > 1 and t2.c1 > 1"; +execute stmt; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +2 4 7 +select * from t2; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +delete from t1; +insert into t1 values +(1,2,2), (1,3,3), (2,2,5), (2,3,6), (2,4,7), (2,5,8); +delete from t2; +insert into t2 values +(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5); +execute stmt; +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +2 3 6 +2 4 7 +2 5 8 +select * from t2; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +2 1 4 +deallocate prepare stmt; +drop table t1,t2; |