revision-id: f4a080c50bab40b8c14f085a09b8448520925a28 (mariadb-10.11.1-31-gf4a080c) parent(s): 834e8d6c4616611b8b7ea91abbca761a1ebabff5 author: Igor Babaev committer: Igor Babaev timestamp: 2023-02-09 20:45:04 -0800 message: Fixes of MDEV-30538 and MDEV-30586 for 10.4 adjusted for 11.0. The commits for MDEV-30538 and MDEV-30586 could not be cherry-picked into 11.0 separately. --- mysql-test/main/delete.result | 39 +++++++++- mysql-test/main/delete.test | 41 ++++++++++- mysql-test/main/multi_update.result | 120 +++++++++++++++++++++++++++++++ mysql-test/main/multi_update.test | 70 ++++++++++++++++++ mysql-test/main/update_use_source.result | 18 +++-- sql/item_subselect.cc | 6 +- sql/sql_yacc.yy | 5 +- 7 files changed, 285 insertions(+), 14 deletions(-) diff --git a/mysql-test/main/delete.result b/mysql-test/main/delete.result index 51257d2..694c604 100644 --- a/mysql-test/main/delete.result +++ b/mysql-test/main/delete.result @@ -525,7 +525,38 @@ DELETE v2 FROM v2; ERROR HY000: Can not delete from join view 'test.v2' DROP VIEW v2, v1; DROP TABLE t1, t2; -End of 10.10 tests +End of 5.5 tests +# +# MDEV-30586: DELETE with WHERE containing nested subquery +# with set function aggregated in outer subquery +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create table t2 (b int); +insert into t2 values (2), (1), (4), (7); +create table t3 (a int, b int); +insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40); +select * from t1 +where t1.a in (select t3.a from t3 group by t3.a +having t3.a > any (select t2.b from t2 +where t2.b*10 < sum(t3.b))); +a +7 +delete from t1 +where t1.a in (select t3.a from t3 group by t3.a +having t3.a > any (select t2.b from t2 +where t2.b*10 < sum(t3.b))); +select * from t1 +where t1.a in (select t3.a from t3 group by t3.a +having t3.a > any (select t2.b from t2 +where t2.b*10 < sum(t3.b))); +a +update t1 set t1.a=t1.a+10 +where t1.a in (select t3.a from t3 group by t3.a +having t3.a > any (select t2.b from t2 +where t2.b*10 < sum(t3.b))); +drop table t1,t2,t3; +End of 10.4 tests # # MDEV-29428: DELETE with ORDER BY without LIMIT clause # @@ -545,7 +576,8 @@ explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and 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 -2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED a ALL NULL NULL NULL NULL 8 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; select *from t1; @@ -561,7 +593,8 @@ where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where -2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED a ALL NULL NULL NULL NULL 8 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; diff --git a/mysql-test/main/delete.test b/mysql-test/main/delete.test index 1e3848f..ff9da9b 100644 --- a/mysql-test/main/delete.test +++ b/mysql-test/main/delete.test @@ -583,7 +583,46 @@ DELETE v2 FROM v2; DROP VIEW v2, v1; DROP TABLE t1, t2; ---echo End of 10.10 tests +--echo End of 5.5 tests + +--echo # +--echo # MDEV-30586: DELETE with WHERE containing nested subquery +--echo # with set function aggregated in outer subquery +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create table t2 (b int); +insert into t2 values (2), (1), (4), (7); + +create table t3 (a int, b int); +insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40); + +let $c= + t1.a in (select t3.a from t3 group by t3.a + having t3.a > any (select t2.b from t2 + where t2.b*10 < sum(t3.b))); + +eval +select * from t1 + where $c; + +eval +delete from t1 + where $c; + +eval +select * from t1 + where $c; + +eval +update t1 set t1.a=t1.a+10 + where $c; + +drop table t1,t2,t3; + +--echo End of 10.4 tests --echo # --echo # MDEV-29428: DELETE with ORDER BY without LIMIT clause diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result index ae661fa..de36182 100644 --- a/mysql-test/main/multi_update.result +++ b/mysql-test/main/multi_update.result @@ -1267,3 +1267,123 @@ EXPLAIN } } DROP TABLES t1, t2; +# End of 10.3 tests +# +# MDEV-30538: multi-table UPDATE/DELETE with possible exists-to-in +# +create table t1 (c1 int, c2 int, c3 int, index idx(c2)); +insert into t1 values +(1,1,1),(3,2,2),(1,3,3), +(2,1,4),(2,2,5),(4,3,6), +(2,4,7),(2,5,8); +create table t2 (c1 int, c2 int, c3 int, index idx(c2)); +insert into t2 values +(1,7,1),(1,8,2),(1,3,3), +(2,1,4),(2,2,5),(2,3,6), +(2,4,7),(2,5,8); +create table t3 (c1 int, c2 int, c3 int, index idx(c2)); +insert into t3 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); +insert into t3 select c1+1, c2+2, c3 from t3; +insert into t3 select c1, c2+2, c3 from t3; +analyze table t1,t2,t3 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +explain select * from t1,t3 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 +2 MATERIALIZED t2 range idx idx 5 NULL 3 Using index condition; Using where +explain delete from t1 using t1,t3 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index +2 MATERIALIZED t2 range idx idx 5 NULL 3 Using where +explain update t1,t3 set t1.c1 = t1.c1+10 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index +2 MATERIALIZED t2 range idx idx 5 NULL 3 Using where +create table t as select * from t1; +select * from t1,t3 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +c1 c2 c3 c1 c2 c3 +2 1 4 1 1 1 +2 1 4 2 1 4 +2 2 5 1 2 2 +2 2 5 2 2 5 +2 4 7 2 4 7 +2 4 7 2 4 2 +2 4 7 3 4 5 +2 4 7 1 4 2 +2 4 7 2 4 5 +2 5 8 2 5 8 +2 5 8 2 5 3 +2 5 8 3 5 6 +2 5 8 1 5 3 +2 5 8 2 5 6 +2 5 8 2 5 1 +2 5 8 3 5 4 +select * from t1; +c1 c2 c3 +1 1 1 +3 2 2 +1 3 3 +2 1 4 +2 2 5 +4 3 6 +2 4 7 +2 5 8 +delete from t1 using t1,t3 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +select * from t1; +c1 c2 c3 +1 1 1 +3 2 2 +1 3 3 +4 3 6 +truncate table t1; +insert into t1 select * from t; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +update t1,t3 set t1.c1 = t1.c1+10 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +select * from t1; +c1 c2 c3 +1 1 1 +3 2 2 +1 3 3 +12 1 4 +12 2 5 +4 3 6 +12 4 7 +12 5 8 +drop table t1,t2,t3,t; +# End of 10.4 tests diff --git a/mysql-test/main/multi_update.test b/mysql-test/main/multi_update.test index 63648dc..39c9f41 100644 --- a/mysql-test/main/multi_update.test +++ b/mysql-test/main/multi_update.test @@ -1128,3 +1128,73 @@ EXPLAIN FORMAT=JSON UPDATE t2 JOIN t1 USING(a) SET t2.part=2 WHERE t2.part=1 AND EXPLAIN FORMAT=JSON UPDATE t2 JOIN t1 USING(a) SET t2.part=3 WHERE t2.part=2 AND t1.part=2; DROP TABLES t1, t2; + +--echo # End of 10.3 tests + +--echo # +--echo # MDEV-30538: multi-table UPDATE/DELETE with possible exists-to-in +--echo # + +create table t1 (c1 int, c2 int, c3 int, index idx(c2)); +insert into t1 values +(1,1,1),(3,2,2),(1,3,3), +(2,1,4),(2,2,5),(4,3,6), +(2,4,7),(2,5,8); + +create table t2 (c1 int, c2 int, c3 int, index idx(c2)); +insert into t2 values +(1,7,1),(1,8,2),(1,3,3), +(2,1,4),(2,2,5),(2,3,6), +(2,4,7),(2,5,8); + +create table t3 (c1 int, c2 int, c3 int, index idx(c2)); +insert into t3 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); +insert into t3 select c1+1, c2+2, c3 from t3; +insert into t3 select c1, c2+2, c3 from t3; + +analyze table t1,t2,t3 persistent for all; + +let $c= + t1.c2 = t3.c2 and + t1.c1 > 1 and + exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); + +let $q1= +select * from t1,t3 +where $c; + +eval explain $q1; + +let $q2= +delete from t1 using t1,t3 +where $c; + +eval explain $q2; + +let $q3= +update t1,t3 set t1.c1 = t1.c1+10 +where $c; + +eval explain $q3; + +create table t as select * from t1; + +eval $q1; +select * from t1; + +eval $q2; +select * from t1; + +truncate table t1; +insert into t1 select * from t; +analyze table t1 persistent for all; + +eval $q3; +select * from t1; + +drop table t1,t2,t3,t; + +--echo # End of 10.4 tests diff --git a/mysql-test/main/update_use_source.result b/mysql-test/main/update_use_source.result index 320f5b6..e4ba0e9 100644 --- a/mysql-test/main/update_use_source.result +++ b/mysql-test/main/update_use_source.result @@ -76,7 +76,8 @@ rollback; explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and 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 -2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED a ALL NULL NULL NULL NULL 8 start transaction; update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; affected rows: 4 @@ -316,8 +317,9 @@ rollback; # explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition; Using where -2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 4 Using index +1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED a range t1_c2 t1_c2 5 NULL 2 Using where; Using index start transaction; update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; affected rows: 4 @@ -557,8 +559,9 @@ rollback; # explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition; Using where -2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 1 Using index +1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED a range t1_c2 t1_c2 5 NULL 2 Using where; Using index start transaction; update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; affected rows: 4 @@ -799,8 +802,9 @@ rollback; # explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition; Using where -2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 1 Using index +1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED a range t1_c2 t1_c2 5 NULL 2 Using where; Using index start transaction; update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; affected rows: 4 diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 9b4e60b..b704de0 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2906,7 +2906,11 @@ bool Item_exists_subselect::select_prepare_to_be_in() bool trans_res= FALSE; DBUG_ENTER("Item_exists_subselect::select_prepare_to_be_in"); if (!optimizer && - thd->lex->sql_command == SQLCOM_SELECT && + (thd->lex->sql_command == SQLCOM_SELECT || + thd->lex->sql_command == SQLCOM_UPDATE_MULTI || + thd->lex->sql_command == SQLCOM_DELETE_MULTI || + thd->lex->sql_command == SQLCOM_UPDATE || + thd->lex->sql_command == SQLCOM_DELETE) && !unit->first_select()->is_part_of_union() && optimizer_flag(thd, OPTIMIZER_SWITCH_EXISTS_TO_IN) && (is_top_level_item() || diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index affd8f7..5d86041 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -13377,12 +13377,13 @@ delete_part2: { LEX *lex= Lex; lex->last_table()->vers_conditions= lex->vers_conditions; - lex->pop_select(); //main select lex->sql_command= SQLCOM_DELETE; if (!(lex->m_sql_cmd= new (thd->mem_root) Sql_cmd_delete(false))) MYSQL_YYABORT; } + stmt_end + {} ; delete_single_table: @@ -13433,12 +13434,12 @@ single_multi: LEX *lex= Lex; if ($3) Select->order_list= *($3); - lex->pop_select(); //main select lex->sql_command= SQLCOM_DELETE; if (!(lex->m_sql_cmd= new (thd->mem_root) Sql_cmd_delete(false))) MYSQL_YYABORT; } + stmt_end {} | table_alias_ref_list { LEX *lex= Lex;