revision-id: 0c3b1a545d0163d7641ebcc8317d85e633252d4a (mariadb-10.6.1-477-g0c3b1a5) parent(s): 2db18fdb3d68d906fbd188ec570a64502ba55849 author: Igor Babaev committer: Igor Babaev timestamp: 2022-07-11 16:39:23 -0700 message: MDEV-28965 Assertion failure when preparing UPDATE with derived table in WHERE This patch fixes not only the assertion failure in the function Field_iterator_table_ref::set_field_iterator() but also: - fixes the problem of forced materialization of derived tables used in subqueries contained in WHERE clauses of single-table and multi-table UPDATE and DELETE statements - fixes the problem of MDEV-17954 that prevented execution of multi-table DELETE statements if they use in their WHERE clauses references to the tables that are updated. The patch must be considered a complement to the patch for MDEV-28883. --- mysql-test/main/delete_use_source.result | 184 ++++++++++- mysql-test/main/delete_use_source.test | 120 ++++++++ mysql-test/main/derived.result | 336 +++++++++++++++++++++ mysql-test/main/derived.test | 210 +++++++++++++ mysql-test/main/derived_cond_pushdown.result | 27 +- mysql-test/main/derived_cond_pushdown.test | 2 +- mysql-test/main/multi_update.result | 2 - mysql-test/main/multi_update.test | 2 - mysql-test/main/subselect.result | 1 - mysql-test/main/subselect.test | 1 - mysql-test/main/subselect_no_exists_to_in.result | 1 - mysql-test/main/subselect_no_mat.result | 1 - mysql-test/main/subselect_no_opts.result | 1 - mysql-test/main/subselect_no_scache.result | 1 - mysql-test/main/subselect_no_semijoin.result | 1 - .../engines/iuds/r/update_delete_number.result | 22 +- .../suite/engines/iuds/t/update_delete_number.test | 20 +- sql/opt_subselect.cc | 56 +++- sql/sql_base.cc | 38 ++- sql/sql_delete.cc | 63 ++-- sql/sql_delete.h | 15 +- sql/sql_lex.cc | 28 +- sql/sql_lex.h | 1 + sql/sql_update.cc | 21 +- sql/sql_update.h | 16 +- sql/sql_yacc.yy | 15 + sql/table.cc | 10 +- 27 files changed, 1107 insertions(+), 88 deletions(-) diff --git a/mysql-test/main/delete_use_source.result b/mysql-test/main/delete_use_source.result index 0990a55..329203a 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; diff --git a/mysql-test/main/delete_use_source.test b/mysql-test/main/delete_use_source.test index 4aed00d..9625431 100644 --- a/mysql-test/main/delete_use_source.test +++ b/mysql-test/main/delete_use_source.test @@ -135,3 +135,123 @@ set session sort_buffer_size = 1024; delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10); drop table t1; + +--echo # +--echo # MDEV-17954: multi-table DELETE with the same source and target +--echo # + +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); + +--echo # +--echo # Single-table DELETE with the same source and target +--echo # handled as multi-table DELETE +--echo # + +let $q1= +delete from t1 + where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1 and a.c2 < 3); + +eval explain $q1; +eval $q1; +select * from t1; +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); +eval prepare stmt from "$q1"; +execute stmt; +select * from t1; +delete from t1; +insert into t1 values + (2,2,5), (2,3,6), (2,4,7), (2,5,8); +execute stmt; +select * from t1; +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); + +--echo # +--echo # Multi-table DELETE with the same source and target +--echo # + +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); + +let $q2= +delete from t1 using t1,t2 + where t1.c2 = t2.c2 and t1.c1 > 1; + +eval explain $q2; +eval $q2; +select * from t1; +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); +eval prepare stmt from "$q2"; +execute stmt; +select * from t1; +delete from t1; +insert into t1 values + (2,2,5), (2,3,6), (2,4,7), (2,5,8); +execute stmt; +select * from t1; +deallocate prepare stmt; + +let $q2= +delete from t1 using t1,t2 + where t1.c2 = t2.c2 and t1.c1 > 1; + +eval explain $q2; +eval $q2; +select * from t1; +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); +eval prepare stmt from "$q2"; +execute stmt; +select * from t1; +delete from t1; +insert into t1 values + (2,2,5), (2,3,6), (2,4,7), (2,5,8); +execute stmt; +select * from t1; +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); + +let $q3= +delete from t1,t2 using t1,t2 + where t1.c2 = t2.c2 and t1.c1 > 1 and t2.c1 > 1; + +eval explain $q3; +eval $q3; +select * from t1; +select * from t2; +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); +eval prepare stmt from "$q3"; +execute stmt; +select * from t1; +select * from t2; +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; +select * from t2; +deallocate prepare stmt; + +drop table t1,t2; diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result index b6310f1..c28e01d 100644 --- a/mysql-test/main/derived.result +++ b/mysql-test/main/derived.result @@ -1316,3 +1316,339 @@ a a 4 4 6 6 drop table t1,t2,t3; +# End of 10.3 tests +# +# MDEV-28883: single/multi-table UPDATE/DELETE whose WHERE condition +# contains subquery from mergeable derived table +# that uses the updated/deleted table +# +create table t1 (pk int, a int); +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +explain update t1 set a = 10 +where a = ( select * from (select a from t1) dt where dt.a > 7); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where +update t1 set a = 10 +where a = ( select * from (select a from t1) dt where dt.a > 7); +select * from t1; +pk a +1 3 +2 7 +3 1 +4 10 +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +prepare stmt from "update t1 set a = 10 +where a = ( select * from (select a from t1) dt where dt.a > 7)"; +execute stmt; +select * from t1; +pk a +1 3 +2 7 +3 1 +4 10 +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +select * from t1; +pk a +4 10 +3 1 +1 3 +deallocate prepare stmt; +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +explain update t1 set a = 10 +where a <> ( select * from (select a from t1) dt where dt.a > 7) +order by a limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using filesort +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +update t1 set a = 10 +where a <> ( select * from (select a from t1) dt where dt.a > 7) +order by a limit 2; +select * from t1; +pk a +4 9 +3 10 +1 10 +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +prepare stmt from "update t1 set a = 10 +where a <> ( select * from (select a from t1) dt where dt.a > 7) +order by a limit 2"; +execute stmt; +select * from t1; +pk a +4 9 +3 10 +1 10 +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +select * from t1; +pk a +4 9 +3 10 +1 10 +deallocate prepare stmt; +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +explain delete from t1 +where a = ( select * from (select a from t1) dt where dt.a > 7); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where +delete from t1 +where a = ( select * from (select a from t1) dt where dt.a > 7); +select * from t1; +pk a +1 3 +2 7 +3 1 +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +prepare stmt from "delete from t1 +where a = ( select * from (select a from t1) dt where dt.a > 7)"; +execute stmt; +select * from t1; +pk a +1 3 +2 7 +3 1 +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +select * from t1; +pk a +3 1 +1 3 +deallocate prepare stmt; +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +explain delete from t1 +where a = ( select * from (select a from t1) dt where dt.a > 5) +returning pk, a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 3 Using where +3 DERIVED t1 ALL NULL NULL NULL NULL 3 Using where +delete from t1 +where a = ( select * from (select a from t1) dt where dt.a > 5) +returning pk, a; +pk a +4 9 +select * from t1; +pk a +3 1 +1 3 +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +prepare stmt from "delete from t1 +where a = ( select * from (select a from t1) dt where dt.a > 5) +returning pk, a"; +execute stmt; +pk a +4 9 +select * from t1; +pk a +3 1 +1 3 +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +pk a +4 9 +select * from t1; +pk a +3 1 +1 3 +deallocate prepare stmt; +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +explain delete from t1 +where a <> ( select * from (select a from t1) dt where dt.a > 7) +order by a limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using filesort +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 4 Using where +3 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where +delete from t1 +where a <> ( select * from (select a from t1) dt where dt.a > 7) +order by a limit 2; +select * from t1; +pk a +2 7 +4 9 +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +prepare stmt from "delete from t1 +where a <> ( select * from (select a from t1) dt where dt.a > 7) +order by a limit 2"; +execute stmt; +select * from t1; +pk a +2 7 +4 9 +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +execute stmt; +select * from t1; +pk a +2 7 +4 9 +deallocate prepare stmt; +create table t2 (pk int, a int); +insert into t2 values (1,3), (2, 7), (3,1), (4,9); +create table t3 (a int); +insert into t3 VALUES (0),(1); +explain update t1,t3 set t1.a = 1 +where t1.a=t3.a and +t1.a = ( select * from (select a from t1) dt where dt.a > 7); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +update t1,t3 set t1.a = 1 +where t1.a=t3.a and +t1.a = ( select * from (select a from t1) dt where dt.a > 7); +select * from t1; +pk a +2 7 +4 9 +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +prepare stmt from "update t1,t3 set t1.a = 1 +where t1.a=t3.a and +t1.a = ( select * from (select a from t1) dt where dt.a > 7)"; +execute stmt; +select * from t1; +pk a +1 3 +2 7 +3 1 +4 9 +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +select * from t1; +pk a +4 9 +3 1 +1 3 +deallocate prepare stmt; +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +explain update t1,t3 set t1.a = 1 +where t1.a=t3.a and +t1.a = ( select * from (select a from t2) dt where dt.a > 7); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 Using where +update t1,t3 set t1.a = 1 +where t1.a=t3.a and +t1.a = ( select * from (select a from t2) dt where dt.a > 7); +select * from t1; +pk a +1 3 +2 7 +3 1 +4 9 +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +prepare stmt from "update t1,t3 set t1.a = 1 +where t1.a=t3.a and +t1.a = ( select * from (select a from t2) dt where dt.a > 7)"; +execute stmt; +select * from t1; +pk a +1 3 +2 7 +3 1 +4 9 +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +select * from t1; +pk a +4 9 +3 1 +1 3 +deallocate prepare stmt; +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +insert into t3 values (9), (10), (7); +explain delete from t1 using t1,t3 +where t1.a=t3.a and +t1.a = ( select * from (select a from t1) dt where dt.a > 7); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where +delete from t1 using t1,t3 +where t1.a=t3.a and +t1.a = ( select * from (select a from t1) dt where dt.a > 7); +select * from t1; +pk a +1 3 +2 7 +3 1 +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +prepare stmt from "delete from t1 using t1,t3 +where t1.a=t3.a and +t1.a = ( select * from (select a from t1) dt where dt.a > 7)"; +execute stmt; +select * from t1; +pk a +1 3 +2 7 +3 1 +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +select * from t1; +pk a +3 1 +1 3 +deallocate prepare stmt; +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +explain delete from t1 using t1,t3 +where t1.a=t3.a and +t1.a = ( select * from (select a from t2) dt where dt.a > 7); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 Using where +delete from t1 using t1,t3 +where t1.a=t3.a and +t1.a = ( select * from (select a from t2) dt where dt.a > 7); +select * from t1; +pk a +1 3 +2 7 +3 1 +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +prepare stmt from "delete from t1 using t1,t3 +where t1.a=t3.a and +t1.a = ( select * from (select a from t2) dt where dt.a > 7)"; +execute stmt; +select * from t1; +pk a +1 3 +2 7 +3 1 +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +select * from t1; +pk a +3 1 +1 3 +deallocate prepare stmt; +drop table t1,t2,t3; +# End of MariaDB 10.10 tests diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test index 904114e..d3b13ce 100644 --- a/mysql-test/main/derived.test +++ b/mysql-test/main/derived.test @@ -1126,3 +1126,213 @@ analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; drop table t1,t2,t3; + +--echo # End of 10.3 tests + +--echo # +--echo # MDEV-28883: single/multi-table UPDATE/DELETE whose WHERE condition +--echo # contains subquery from mergeable derived table +--echo # that uses the updated/deleted table +--echo # + +create table t1 (pk int, a int); +insert into t1 values (1,3), (2, 7), (3,1), (4,9); + +let $q1= +update t1 set a = 10 + where a = ( select * from (select a from t1) dt where dt.a > 7); + +eval explain $q1; +eval $q1; +select * from t1; +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +eval prepare stmt from "$q1"; +execute stmt; +select * from t1; +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +select * from t1; +deallocate prepare stmt; + +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); + +let $q2= +update t1 set a = 10 + where a <> ( select * from (select a from t1) dt where dt.a > 7) +order by a limit 2; +eval explain $q2; +eval $q2; +select * from t1; +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +eval prepare stmt from "$q2"; +execute stmt; +select * from t1; +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +select * from t1; +deallocate prepare stmt; + +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); + +let $q3= +delete from t1 + where a = ( select * from (select a from t1) dt where dt.a > 7); + +eval explain $q3; +eval $q3; +select * from t1; +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +eval prepare stmt from "$q3"; +execute stmt; +select * from t1; +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +select * from t1; +deallocate prepare stmt; + +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); + +let $q4= +delete from t1 + where a = ( select * from (select a from t1) dt where dt.a > 5) +returning pk, a; + +eval explain $q4; +eval $q4; +select * from t1; +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +eval prepare stmt from "$q4"; +execute stmt; +select * from t1; +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +select * from t1; +deallocate prepare stmt; + +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); + +let $q5= +delete from t1 + where a <> ( select * from (select a from t1) dt where dt.a > 7) +order by a limit 2; +eval explain $q5; +eval $q5; +select * from t1; +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +eval prepare stmt from "$q5"; +execute stmt; +select * from t1; +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +execute stmt; +select * from t1; +deallocate prepare stmt; + +create table t2 (pk int, a int); +insert into t2 values (1,3), (2, 7), (3,1), (4,9); +create table t3 (a int); +insert into t3 VALUES (0),(1); + +let $q6= +update t1,t3 set t1.a = 1 + where t1.a=t3.a and + t1.a = ( select * from (select a from t1) dt where dt.a > 7); + +eval explain $q6; +eval $q6; +select * from t1; +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +eval prepare stmt from "$q6"; +execute stmt; +select * from t1; +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +select * from t1; +deallocate prepare stmt; + +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); + +let $q7= +update t1,t3 set t1.a = 1 + where t1.a=t3.a and + t1.a = ( select * from (select a from t2) dt where dt.a > 7); + +eval explain $q7; +eval $q7; +select * from t1; +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +eval prepare stmt from "$q7"; +execute stmt; +select * from t1; +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +select * from t1; +deallocate prepare stmt; + +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +insert into t3 values (9), (10), (7); + +let $q8= +delete from t1 using t1,t3 + where t1.a=t3.a and + t1.a = ( select * from (select a from t1) dt where dt.a > 7); + +eval explain $q8; +eval $q8; +select * from t1; +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +eval prepare stmt from "$q8"; +execute stmt; +select * from t1; +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +select * from t1; +deallocate prepare stmt; + +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); + +let $q9= +delete from t1 using t1,t3 + where t1.a=t3.a and + t1.a = ( select * from (select a from t2) dt where dt.a > 7); + +eval explain $q9; +eval $q9; +select * from t1; +delete from t1; +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +eval prepare stmt from "$q9"; +execute stmt; +select * from t1; +delete from t1; +insert into t1 value (4,9), (3,1), (1,3); +execute stmt; +select * from t1; +deallocate prepare stmt; + + +drop table t1,t2,t3; + +--echo # End of MariaDB 10.10 tests diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 9bbd32a..a7d7e87 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -11908,7 +11908,7 @@ DROP TABLE t1; # CREATE TABLE t1 (f1 text, f2 int); INSERT INTO t1 VALUES ('x',1), ('y',2); -CREATE VIEW v1 AS SELECT f2 FROM ( SELECT f2 FROM t1 ) AS t; +CREATE VIEW v1 AS SELECT f2 FROM ( SELECT f2 FROM t1 GROUP BY f2) AS t; UPDATE v1, t1 SET t1.f1 = 'z' WHERE v1.f2 < 2 AND t1.f2 = v1.f2; EXPLAIN FORMAT=JSON UPDATE v1, t1 SET t1.f1 = 'z' WHERE v1.f2 < 2 AND t1.f2 = v1.f2; EXPLAIN @@ -11939,17 +11939,22 @@ EXPLAIN "materialized": { "query_block": { "select_id": 3, - "nested_loop": [ - { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 2, - "filtered": 100, - "attached_condition": "t1.f2 < 2" - } + "filesort": { + "sort_key": "t1.f2", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t1.f2 < 2" + } + } + ] } - ] + } } } } diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 39e8221..e88fae7 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -2089,7 +2089,7 @@ DROP TABLE t1; CREATE TABLE t1 (f1 text, f2 int); INSERT INTO t1 VALUES ('x',1), ('y',2); -CREATE VIEW v1 AS SELECT f2 FROM ( SELECT f2 FROM t1 ) AS t; +CREATE VIEW v1 AS SELECT f2 FROM ( SELECT f2 FROM t1 GROUP BY f2) AS t; let $q1 = UPDATE v1, t1 SET t1.f1 = 'z' WHERE v1.f2 < 2 AND t1.f2 = v1.f2; eval $q1; diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result index 674dc79..ae661fa 100644 --- a/mysql-test/main/multi_update.result +++ b/mysql-test/main/multi_update.result @@ -441,12 +441,10 @@ create table t1 (col1 int); create table t2 (col1 int); update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1; -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data drop table t1,t2; create table t1(a int); create table t2(a int); delete from t1,t2 using t1,t2 where t1.a=(select a from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data drop table t1, t2; create table t1 (a int, b int); insert into t1 values (1, 2), (2, 3), (3, 4); diff --git a/mysql-test/main/multi_update.test b/mysql-test/main/multi_update.test index 5f4b5fc..839cebf 100644 --- a/mysql-test/main/multi_update.test +++ b/mysql-test/main/multi_update.test @@ -390,7 +390,6 @@ drop table t1, t2, t3; create table t1 (col1 int); create table t2 (col1 int); update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; --- error ER_UPDATE_TABLE_USED delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1; drop table t1,t2; @@ -399,7 +398,6 @@ drop table t1,t2; # create table t1(a int); create table t2(a int); ---error ER_UPDATE_TABLE_USED delete from t1,t2 using t1,t2 where t1.a=(select a from t1); drop table t1, t2; # End of 4.1 tests diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 22a814c..5e8265c 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -634,7 +634,6 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); -ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index 19c30bd..7e29660 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -366,7 +366,6 @@ insert into t12 values (33, 10),(22, 11),(2, 12); insert into t2 values (1, 21),(2, 12),(3, 23); select * from t11; select * from t12; --- error ER_UPDATE_TABLE_USED delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); -- error ER_SUBQUERY_NO_1_ROW delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index def116c..3c18950 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -638,7 +638,6 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); -ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index 7eb3734..c1b0c67 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -641,7 +641,6 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); -ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index f2981c0..13af2f4 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -637,7 +637,6 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); -ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 17bec03..38d5665 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -640,7 +640,6 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); -ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index cb3620f..cab4e6f 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -637,7 +637,6 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); -ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); diff --git a/mysql-test/suite/engines/iuds/r/update_delete_number.result b/mysql-test/suite/engines/iuds/r/update_delete_number.result index 1cd2a62..1534f93 100644 --- a/mysql-test/suite/engines/iuds/r/update_delete_number.result +++ b/mysql-test/suite/engines/iuds/r/update_delete_number.result @@ -739,8 +739,21 @@ c1 c2 c3 c1 c2 c3 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; c1 c2 c3 c1 c2 c3 -DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(3,NULL,5); +INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-1,NULL,5); +DELETE FROM t1,t2 using t1,t2 +where t1.c1=(select c1 from t1 where c2 < 10) and t2.c2 < 10; +SELECT * FROM t1; +c1 c2 c3 +1 127 3 +254 127 1 +3 NULL 5 +SELECT * FROM t2; +c1 c2 c3 +-1 NULL 5 +127 255 1 CREATE TABLE t3(c1 INT UNSIGNED NOT NULL PRIMARY KEY, c2 INT SIGNED NULL, c3 INT); CREATE TABLE t4(c1 INT UNSIGNED, c2 INT); INSERT INTO t3 VALUES(200,126,1),(250,-127,2); @@ -981,7 +994,6 @@ create table mt1 (col1 int); create table mt2 (col1 int); update mt1,mt2 set mt1.col1 = (select max(col1) from mt1) where mt1.col1 = mt2.col1; delete mt1 from mt1,mt2 where mt1.col1 < (select max(col1) from mt1) and mt1.col1 = mt2.col1; -ERROR HY000: Table 'mt1' is specified twice, both as a target for 'DELETE' and as a separate source for data drop table mt1,mt2; CREATE TABLE IF NOT EXISTS `mt1` (`id` int(11) NOT NULL auto_increment, `tst` text, `tsmt1` text, PRIMARY KEY (`id`)); CREATE TABLE IF NOT EXISTS `mt2` (`ID` int(11) NOT NULL auto_increment, `ParId` int(11) default NULL, `tst` text, `tsmt1` text, PRIMARY KEY (`ID`), KEY `IX_ParId_mt2` (`ParId`), FOREIGN KEY (`ParId`) REFERENCES `mt1` (`id`)); @@ -1852,7 +1864,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; c1 c2 c3 c1 c2 c3 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data CREATE TABLE t3(c1 TINYINT UNSIGNED NOT NULL PRIMARY KEY, c2 TINYINT SIGNED NULL, c3 INT); CREATE TABLE t4(c1 TINYINT UNSIGNED, c2 INT); INSERT INTO t3 VALUES(200,126,1),(250,-127,2); @@ -2599,7 +2610,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; c1 c2 c3 c1 c2 c3 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data CREATE TABLE t3(c1 SMALLINT UNSIGNED NOT NULL PRIMARY KEY, c2 SMALLINT SIGNED NULL, c3 INT); CREATE TABLE t4(c1 SMALLINT UNSIGNED, c2 INT); INSERT INTO t3 VALUES(200,126,1),(250,-127,2); @@ -3346,7 +3356,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; c1 c2 c3 c1 c2 c3 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data CREATE TABLE t3(c1 MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, c2 MEDIUMINT SIGNED NULL, c3 INT); CREATE TABLE t4(c1 MEDIUMINT UNSIGNED, c2 INT); INSERT INTO t3 VALUES(200,126,1),(250,-127,2); @@ -4093,7 +4102,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; c1 c2 c3 c1 c2 c3 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data CREATE TABLE t3(c1 BIGINT UNSIGNED NOT NULL PRIMARY KEY, c2 BIGINT SIGNED NULL, c3 INT); CREATE TABLE t4(c1 BIGINT UNSIGNED, c2 INT); INSERT INTO t3 VALUES(200,126,1),(250,-127,2); diff --git a/mysql-test/suite/engines/iuds/t/update_delete_number.test b/mysql-test/suite/engines/iuds/t/update_delete_number.test index ce3f901..4347d06 100644 --- a/mysql-test/suite/engines/iuds/t/update_delete_number.test +++ b/mysql-test/suite/engines/iuds/t/update_delete_number.test @@ -285,8 +285,18 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; --sorted_result SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; ---error ER_UPDATE_TABLE_USED -DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(3,NULL,5); +INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-1,NULL,5); +# After the patch for MDEV-28883 this should not report +# ER_UPDATE_TABLE_USED anymore +DELETE FROM t1,t2 using t1,t2 + where t1.c1=(select c1 from t1 where c2 < 10) and t2.c2 < 10; +--sorted_result +SELECT * FROM t1; +--sorted_result +SELECT * FROM t2; # eq-ref join CREATE TABLE t3(c1 INT UNSIGNED NOT NULL PRIMARY KEY, c2 INT SIGNED NULL, c3 INT); @@ -496,7 +506,7 @@ drop table mt1, mt2, mt3; create table mt1 (col1 int); create table mt2 (col1 int); update mt1,mt2 set mt1.col1 = (select max(col1) from mt1) where mt1.col1 = mt2.col1; --- error ER_UPDATE_TABLE_USED +# -- error ER_UPDATE_TABLE_USED delete mt1 from mt1,mt2 where mt1.col1 < (select max(col1) from mt1) and mt1.col1 = mt2.col1; drop table mt1,mt2; @@ -865,7 +875,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; --sorted_result SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; ---error ER_UPDATE_TABLE_USED DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); # eq-ref join @@ -1166,7 +1175,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; --sorted_result SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; ---error ER_UPDATE_TABLE_USED DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); # eq-ref join @@ -1467,7 +1475,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; --sorted_result SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; ---error ER_UPDATE_TABLE_USED DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); # eq-ref join @@ -1768,7 +1775,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; --sorted_result SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; ---error ER_UPDATE_TABLE_USED DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); # eq-ref join diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index fa338f0..1e94f0a 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -30,6 +30,8 @@ #include "sql_base.h" #include "sql_const.h" #include "sql_select.h" +#include "sql_update.h" // class Sql_cmd_update +#include "sql_delete.h" // class Sql_cmd_delete #include "filesort.h" #include "opt_subselect.h" #include "sql_test.h" @@ -532,6 +534,48 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs, return FALSE; } +/** + @brief Check whether an IN subquery must be excluded from conversion to SJ + + @param thd global context the processed statement + @returns true if the IN subquery must be excluded from conversion to SJ + + @note + Currently a top level IN subquery of an delete statement is not converted + to SJ if the statement contains ORDER BY ... LIMIT or contains RETURNING. + + @todo + The disjunctive members + !((Sql_cmd_update *) cmd)->is_multitable() + !((Sql_cmd_delete *) cmd)->is_multitable() + will be removed when conversions of IN predicands to semi-joins are + fully supported for single-table UPDATE/DELETE statements. +*/ + +bool SELECT_LEX::is_sj_conversion_prohibited(THD *thd) +{ + DBUG_ASSERT(master_unit()->item->substype() == Item_subselect::IN_SUBS); + + SELECT_LEX *outer_sl= outer_select(); + if (outer_sl->outer_select()) + return false; + + Sql_cmd *cmd= thd->lex->m_sql_cmd; + + switch (thd->lex->sql_command) { + case SQLCOM_UPDATE: + return + !((Sql_cmd_update *) cmd)->is_multitable() || + ((Sql_cmd_update *) cmd)->processing_as_multitable_update_prohibited(thd); + case SQLCOM_DELETE: + return + !((Sql_cmd_delete *) cmd)->is_multitable() || + ((Sql_cmd_delete *) cmd)->processing_as_multitable_delete_prohibited(thd); + default: + return false; + } +} + /* Check if we need JOIN::prepare()-phase subquery rewrites and if yes, do them @@ -675,9 +719,8 @@ int check_and_do_in_subquery_rewrites(JOIN *join) 3. Subquery does not have GROUP BY or ORDER BY 4. Subquery does not use aggregate functions or HAVING 5. Subquery predicate is at the AND-top-level of ON/WHERE clause - 6. We are not in a subquery of a single table UPDATE/DELETE that - doesn't have a JOIN (TODO: We should handle this at some - point by switching to multi-table UPDATE/DELETE) + 6. We are not in a subquery of a single-table UPDATE/DELETE that + does not allow conversion to multi-table UPDATE/DELETE 7. We're not in a table-less subquery like "SELECT 1" 8. No execution method was already chosen (by a prepared statement) 9. Parent select is not a table-less select @@ -692,9 +735,7 @@ int check_and_do_in_subquery_rewrites(JOIN *join) !select_lex->group_list.elements && !join->order && // 3 !join->having && !select_lex->with_sum_func && // 4 in_subs->emb_on_expr_nest && // 5 - select_lex->outer_select()->join && // 6 - (!thd->lex->m_sql_cmd || - thd->lex->m_sql_cmd->sql_command_code() == SQLCOM_UPDATE_MULTI) && + !select_lex->is_sj_conversion_prohibited(thd) && // 6 parent_unit->first_select()->leaf_tables.elements && // 7 !in_subs->has_strategy() && // 8 select_lex->outer_select()->table_list.first && // 9 @@ -754,7 +795,8 @@ int check_and_do_in_subquery_rewrites(JOIN *join) */ if (in_subs && !in_subs->has_strategy()) { - if (is_materialization_applicable(thd, in_subs, select_lex)) + if (!select_lex->is_sj_conversion_prohibited(thd) && + is_materialization_applicable(thd, in_subs, select_lex)) { in_subs->add_strategy(SUBS_MATERIALIZATION); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 2b41b78..725a0ff 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -47,6 +47,8 @@ #include "sql_prepare.h" #include "sql_statistics.h" #include "sql_cte.h" +#include "sql_update.h" // class Sql_cmd_update +#include "sql_delete.h" // class Sql_cmd_delete #include <m_ctype.h> #include <my_dir.h> #include <hash.h> @@ -1175,16 +1177,42 @@ TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, We come here for queries of type: INSERT INTO t1 (SELECT tmp.a FROM (select * FROM t1) as tmp); - Try to fix by materializing the derived table + Try to fix by materializing the derived table if one can't do without it. */ TABLE_LIST *derived= res->belong_to_derived; if (derived->is_merged_derived() && !derived->derived->is_excluded()) { - DBUG_PRINT("info", + bool materialize= true; + if (thd->lex->sql_command == SQLCOM_UPDATE) + { + Sql_cmd_update *cmd= (Sql_cmd_update *) (thd->lex->m_sql_cmd); + if (cmd->is_multitable()) + materialize= false; + else if (!cmd->processing_as_multitable_update_prohibited(thd)) + { + cmd->set_as_multitable(); + materialize= false; + } + } + else if (thd->lex->sql_command == SQLCOM_DELETE) + { + Sql_cmd_delete *cmd= (Sql_cmd_delete *) (thd->lex->m_sql_cmd); + if (cmd->is_multitable()) + materialize= false; + if (!cmd->processing_as_multitable_delete_prohibited(thd)) + { + cmd->set_as_multitable(); + materialize= false; + } + } + if (materialize) + { + DBUG_PRINT("info", ("convert merged to materialization to resolve the conflict")); - derived->change_refs_to_fields(); - derived->set_materialized_derived(); - goto retry; + derived->change_refs_to_fields(); + derived->set_materialized_derived(); + goto retry; + } } } DBUG_RETURN(res); diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 2ff4331..2db662c 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -1109,6 +1109,8 @@ multi_delete::~multi_delete() table_being_deleted= table_being_deleted->next_local) { TABLE *table= table_being_deleted->table; + if (!table) + continue; table->no_keyread=0; table->no_cache= 0; } @@ -1440,6 +1442,34 @@ bool multi_delete::send_eof() } +/** + @brief Check whether processing to multi-table delete is prohibited + + @param thd global context the processed statement + @returns true if processing as multitable is prohibited, false otherwise + + @todo + Introduce handler level flag for storage engines that would prohibit + such conversion for any single-table delete. +*/ + +bool Sql_cmd_delete::processing_as_multitable_delete_prohibited(THD *thd) +{ + SELECT_LEX *const select_lex = thd->lex->first_select_lex(); + return + ((select_lex->order_list.elements && + select_lex->limit_params.select_limit) || + thd->lex->has_returning()); +} + + +/** + @brief Perform precheck of table privileges for delete statements + + @param thd global context the processed statement + @returns false on success, true on error +*/ + bool Sql_cmd_delete::precheck(THD *thd) { if (!multitable) @@ -1543,6 +1573,20 @@ bool Sql_cmd_delete::prepare_inner(THD *thd) DBUG_RETURN(true); } + if (!multitable) + { + TABLE_LIST *update_source_table= 0; + if (((update_source_table=unique_table(thd, table_list, + table_list->next_global, 0)) || + table_list->is_multitable())) + { + DBUG_ASSERT(update_source_table || table_list->view != 0); + if (!table_list->is_multitable() && + !processing_as_multitable_delete_prohibited(thd)) + multitable= true; + } + } + if (table_list->has_period()) { if (table_list->is_view_or_derived()) @@ -1627,25 +1671,6 @@ bool Sql_cmd_delete::prepare_inner(THD *thd) } } - for (target_tbl= (TABLE_LIST*) aux_tables; - target_tbl; - target_tbl= target_tbl->next_local) - { - /* - Check that table from which we delete is not used somewhere - inside subqueries/view. - */ - { - TABLE_LIST *duplicate; - if ((duplicate= unique_table(thd, target_tbl->correspondent_table, - lex->query_tables, 0))) - { - update_non_unique_table_error(target_tbl->correspondent_table, - "DELETE", duplicate); - DBUG_RETURN(TRUE); - } - } - } /* Reset the exclude flag to false so it doesn't interfare with further calls to unique_table diff --git a/sql/sql_delete.h b/sql/sql_delete.h index e1d5044..463cb19 100644 --- a/sql/sql_delete.h +++ b/sql/sql_delete.h @@ -44,11 +44,13 @@ class Sql_cmd_delete final : public Sql_cmd_dml { public: Sql_cmd_delete(bool multitable_arg) - : multitable(multitable_arg), save_protocol(NULL) {} + : orig_multitable(multitable_arg), multitable(multitable_arg), + save_protocol(NULL) + {} enum_sql_command sql_command_code() const override { - return multitable ? SQLCOM_DELETE_MULTI : SQLCOM_DELETE; + return orig_multitable ? SQLCOM_DELETE_MULTI : SQLCOM_DELETE; } DML_prelocking_strategy *get_dml_prelocking_strategy() @@ -56,6 +58,12 @@ class Sql_cmd_delete final : public Sql_cmd_dml return &dml_prelocking_strategy; } + bool processing_as_multitable_delete_prohibited(THD *thd); + + bool is_multitable() { return multitable; } + + void set_as_multitable() { multitable= true; } + protected: /** @brief Perform precheck of table privileges for delete statements @@ -78,6 +86,9 @@ class Sql_cmd_delete final : public Sql_cmd_dml */ bool delete_from_single_table(THD *thd); + /* Original value of the 'multitable' flag set by constructor */ + const bool orig_multitable; + /* True if the statement is a multitable delete or converted to such. For a single-table delete this flag is set to true if the statement diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 78c067a..c0f91a0 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -37,6 +37,8 @@ #include "sql_partition.h" #include "sql_partition_admin.h" // Sql_cmd_alter_table_*_part #include "event_parse_data.h" +#include "sql_update.h" // class Sql_cmd_update +#include "sql_delete.h" // class Sql_cmd_delete void LEX::parse_error(uint err_number) { @@ -4037,9 +4039,8 @@ bool LEX::can_use_merged() SYNOPSIS LEX::can_not_use_merged() - @param no_update_or_delete Set to 1 if we can't use merge with multiple-table - updates, like when used from - TALE_LIST::init_derived() + @param forced_no_merge_for_update_delete Set to 1 if we can't use merge with + multiple-table updates/deletes DESCRIPTION Temporary table algorithm will be used on all SELECT levels for queries @@ -4050,7 +4051,7 @@ bool LEX::can_use_merged() TRUE - VIEWs with MERGE algorithms can be used */ -bool LEX::can_not_use_merged(bool no_update_or_delete) +bool LEX::can_not_use_merged(bool forced_no_merge_for_update_delete) { switch (sql_command) { case SQLCOM_CREATE_VIEW: @@ -4064,18 +4065,29 @@ bool LEX::can_not_use_merged(bool no_update_or_delete) return TRUE; case SQLCOM_UPDATE_MULTI: - case SQLCOM_DELETE_MULTI: - if (no_update_or_delete) + if (forced_no_merge_for_update_delete) return TRUE; /* Fall through */ case SQLCOM_UPDATE: - if (no_update_or_delete && m_sql_cmd && - (m_sql_cmd->sql_command_code() == SQLCOM_UPDATE_MULTI || + if (forced_no_merge_for_update_delete && + (((Sql_cmd_update *) m_sql_cmd)->is_multitable() || query_tables->is_multitable())) return TRUE; + return FALSE; + + case SQLCOM_DELETE_MULTI: + if (forced_no_merge_for_update_delete) + return TRUE; /* Fall through */ + case SQLCOM_DELETE: + if (forced_no_merge_for_update_delete && + (((Sql_cmd_delete *) m_sql_cmd)->is_multitable() || + query_tables->is_multitable())) + return TRUE; + return FALSE; + default: return FALSE; } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index e733b4f..402e3bd 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1642,6 +1642,7 @@ class st_select_lex: public st_select_lex_node void lex_start(LEX *plex); bool is_unit_nest() { return (nest_flags & UNIT_NEST_FL); } void mark_as_unit_nest() { nest_flags= UNIT_NEST_FL; } + bool is_sj_conversion_prohibited(THD *thd); }; typedef class st_select_lex SELECT_LEX; diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 6b14c4f..7769777 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -2807,6 +2807,23 @@ bool multi_update::send_eof() /** + @brief Check whether conversion to multi-table update is prohibited + + @param thd global context the processed statement + @returns true if conversion is prohibited, false otherwise + + @todo + Introduce handler level flag for storage engines that would prohibit + such conversion for any single-table update. +*/ + +bool Sql_cmd_update::processing_as_multitable_update_prohibited(THD *thd) +{ + return false; +} + + +/** @brief Perform precheck of table privileges for update statements @param thd global context the processed statement @@ -2889,7 +2906,9 @@ bool Sql_cmd_update::prepare_inner(THD *thd) "updating and querying the same temporal periods table"); DBUG_RETURN(TRUE); } - multitable= true; + if (!table_list->is_multitable() && + !processing_as_multitable_update_prohibited(thd)) + multitable= true; } } diff --git a/sql/sql_update.h b/sql/sql_update.h index d0fc7cb..bd7d58c 100644 --- a/sql/sql_update.h +++ b/sql/sql_update.h @@ -46,12 +46,12 @@ class Sql_cmd_update final : public Sql_cmd_dml { public: Sql_cmd_update(bool multitable_arg) - : multitable(multitable_arg) - { } + : orig_multitable(multitable_arg), multitable(multitable_arg) + {} enum_sql_command sql_command_code() const override { - return multitable ? SQLCOM_UPDATE_MULTI : SQLCOM_UPDATE; + return orig_multitable ? SQLCOM_UPDATE_MULTI : SQLCOM_UPDATE; } DML_prelocking_strategy *get_dml_prelocking_strategy() @@ -59,6 +59,12 @@ class Sql_cmd_update final : public Sql_cmd_dml return &multiupdate_prelocking_strategy; } + bool processing_as_multitable_update_prohibited(THD *thd); + + bool is_multitable() { return multitable; } + + void set_as_multitable() { multitable= true; } + protected: /** @brief Perform precheck of table privileges for update statements @@ -82,6 +88,9 @@ class Sql_cmd_update final : public Sql_cmd_dml */ bool update_single_table(THD *thd); + /* Original value of the 'multitable' flag set by constructor */ + const bool orig_multitable; + /* True if the statement is a multi-table update or converted to such. For a single-table update this flag is set to true if the statement @@ -95,7 +104,6 @@ class Sql_cmd_update final : public Sql_cmd_dml public: /* The list of the updating expressions used in the set clause */ List<Item> *update_value_list; - }; #endif /* SQL_UPDATE_INCLUDED */ diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index a587a37..f0d9e69 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -13370,8 +13370,23 @@ delete_single_table: YYPS->m_lock_type, YYPS->m_mdl_type, NULL, + 0))) + MYSQL_YYABORT; + Select->table_list.save_and_clear(&Lex->auxiliary_table_list); + /* Save the number of auxiliary tables */ + Lex->table_count= 1; + + Lex->query_tables= 0; + Lex->query_tables_last= &Lex->query_tables; + if (unlikely(!Select-> + add_table_to_list(thd, $2, NULL, TL_OPTION_UPDATING, + YYPS->m_lock_type, + YYPS->m_mdl_type, + NULL, $3))) MYSQL_YYABORT; + Lex->auxiliary_table_list.first->correspondent_table= + Lex->query_tables; YYPS->m_lock_type= TL_READ_DEFAULT; YYPS->m_mdl_type= MDL_SHARED_READ; } diff --git a/sql/table.cc b/sql/table.cc index f0d5149..44eb9f6 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -7059,7 +7059,8 @@ void Field_iterator_table_ref::set_field_iterator() table_ref->alias.str)); } /* This is a merge view, so use field_translation. */ - else if (table_ref->field_translation) + else if (table_ref->field_translation && + !table_ref->is_materialized_derived()) { DBUG_ASSERT(table_ref->is_merged_derived()); field_it= &view_field_it; @@ -7069,7 +7070,7 @@ void Field_iterator_table_ref::set_field_iterator() /* This is a base table or stored view. */ else { - DBUG_ASSERT(table_ref->table || table_ref->view); + DBUG_ASSERT(table_ref->table || table_ref->is_materialized_derived()); field_it= &table_field_it; DBUG_PRINT("info", ("field_it for '%s' is Field_iterator_table", table_ref->alias.str)); @@ -9518,13 +9519,16 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view) !derived_table_optimization_done(this)) { /* A subquery might be forced to be materialized due to a side-effect. */ + bool forced_no_merge_for_update_delete= + belong_to_view ? belong_to_view->updating : + !unit->outer_select()->outer_select(); if (!is_materialized_derived() && first_select->is_mergeable() && (unit->outer_select() && !unit->outer_select()->with_rownum) && (!thd->lex->with_rownum || (!first_select->group_list.elements && !first_select->order_list.elements)) && optimizer_flag(thd, OPTIMIZER_SWITCH_DERIVED_MERGE) && - !thd->lex->can_not_use_merged(1) && + !thd->lex->can_not_use_merged(forced_no_merge_for_update_delete) && !is_recursive_with_table()) set_merged_derived(); else