revision-id: c5c307abdd823d36be920e0937a96daed15af8c0 (mariadb-10.3.35-93-gc5c307a) parent(s): bd935a41060199a17019453d6e187e8edd7929ba author: Igor Babaev committer: Igor Babaev timestamp: 2022-08-01 20:52:10 -0700 message: MDEV-28617 Crash with INSERT...SELECT using derived table in GROUP BY clause This bug manifested itself for INSERT...SELECT and DELETE statements whose WHERE condition used an IN/ANY/ALL predicand or a EXISTS predicate with such grouping subquery that: - its GROUP BY clause could be eliminated, - the GROUP clause contained a subquery over a mergeable derived table referencing the updated table. The bug ultimately caused a server crash when the prepare phase of the statement processing was executed. This happened after removal redundant subqueries used in the eliminated GROUP BY clause from the statement tree. The function that excluded the subqueries from the did not do it properly. As a result the specification of any derived table contained in a removed subquery was not marked as excluded. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- mysql-test/main/insert_select.result | 89 ++++++++++++++++++++++++++++++++++++ mysql-test/main/insert_select.test | 82 +++++++++++++++++++++++++++++++++ sql/sql_lex.cc | 7 +-- 3 files changed, 175 insertions(+), 3 deletions(-) diff --git a/mysql-test/main/insert_select.result b/mysql-test/main/insert_select.result index ea77053..3f9ac8c 100644 --- a/mysql-test/main/insert_select.result +++ b/mysql-test/main/insert_select.result @@ -954,3 +954,92 @@ ERROR 23000: Duplicate entry '-128' for key 'a' DROP TABLE t1, t2; DROP PROCEDURE p1; # End of 10.2 test +# +# MDEV-28617: INSERT ... SELECT with redundant IN subquery in GROUP BY +# list that uses mergeable derived table containing +# reference to target table +# +create table t1 (a int); +create table t2 (b int); +create table t3 (c int); +insert into t1 values (3), (1); +insert into t2 values (3), (2); +insert into t3 values (4), (2); +insert into t1 +select b from t2 +where b in (select c from t3 +group by (select * from (select a from t1) dt where a = 1)); +select * from t1; +a +3 +1 +2 +delete from t1; +insert into t1 values (3), (1); +insert into t1 +select b from t2 +where b >= any (select c from t3 +group by (select * from (select a from t1) dt where a = 1)); +select * from t1; +a +3 +1 +3 +2 +delete from t1; +insert into t1 values (3), (1); +insert into t1 +select b from t2 +where b <= all (select c from t3 +group by (select * from (select a from t1) dt where a = 1)); +select * from t1; +a +3 +1 +2 +delete from t1; +insert into t1 values (3), (1); +insert into t1 +select b from t2 +where exists (select c from t3 +group by (select * from (select a from t1) dt where a = 1)); +select * from t1; +a +3 +1 +3 +2 +delete from t1; +insert into t1 values (3), (1); +prepare stmt from " +insert into t1 +select b from t2 + where b in (select c from t3 + group by (select * from (select a from t1) dt where a = 1)); +"; +execute stmt; +select * from t1; +a +3 +1 +2 +delete from t1; +insert into t1 values (3), (1); +execute stmt; +select * from t1; +a +3 +1 +2 +delete from t1; +insert into t1 values (3), (1); +delete from t1 +where exists (select b from t2 +where b in (select c from t3 +group by (select * from (select a from t1) dt +where a = 1))); +select * from t1; +a +deallocate prepare stmt; +drop table t1,t2,t3; +# End of 10.3 test diff --git a/mysql-test/main/insert_select.test b/mysql-test/main/insert_select.test index 1f672ac..6baa7e4 100644 --- a/mysql-test/main/insert_select.test +++ b/mysql-test/main/insert_select.test @@ -514,3 +514,85 @@ DROP TABLE t1, t2; DROP PROCEDURE p1; --echo # End of 10.2 test + +--echo # +--echo # MDEV-28617: INSERT ... SELECT with redundant IN subquery in GROUP BY +--echo # list that uses mergeable derived table containing +--echo # reference to target table +--echo # + +create table t1 (a int); +create table t2 (b int); +create table t3 (c int); + +insert into t1 values (3), (1); +insert into t2 values (3), (2); +insert into t3 values (4), (2); + +insert into t1 +select b from t2 + where b in (select c from t3 + group by (select * from (select a from t1) dt where a = 1)); +select * from t1; + +delete from t1; +insert into t1 values (3), (1); + +insert into t1 +select b from t2 + where b >= any (select c from t3 + group by (select * from (select a from t1) dt where a = 1)); +select * from t1; + +delete from t1; +insert into t1 values (3), (1); + +insert into t1 +select b from t2 + where b <= all (select c from t3 + group by (select * from (select a from t1) dt where a = 1)); +select * from t1; + +delete from t1; +insert into t1 values (3), (1); + +insert into t1 +select b from t2 + where exists (select c from t3 + group by (select * from (select a from t1) dt where a = 1)); +select * from t1; + +delete from t1; +insert into t1 values (3), (1); + +prepare stmt from " +insert into t1 +select b from t2 + where b in (select c from t3 + group by (select * from (select a from t1) dt where a = 1)); +"; + +execute stmt; +select * from t1; + +delete from t1; +insert into t1 values (3), (1); + +execute stmt; +select * from t1; + +delete from t1; +insert into t1 values (3), (1); + +delete from t1 + where exists (select b from t2 + where b in (select c from t3 + group by (select * from (select a from t1) dt + where a = 1))); +select * from t1; + +deallocate prepare stmt; + +drop table t1,t2,t3; + +--echo # End of 10.3 test diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index e4c5b7c..3400e31 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2530,6 +2530,7 @@ void st_select_lex_node::fast_exclude() for (; slave; slave= slave->next) slave->fast_exclude(); + prev= NULL; // to ensure correct behavior of st_select_lex_unit::is_excluded() } @@ -2604,9 +2605,7 @@ void st_select_lex_node::exclude_from_tree() */ void st_select_lex_node::exclude() { - /* exclude from global list */ - fast_exclude(); - /* exclude from other structures */ + /* exclude the node from the tree */ exclude_from_tree(); /* We do not need following statements, because prev pointer of first @@ -2614,6 +2613,8 @@ void st_select_lex_node::exclude() if (master->slave == this) master->slave= next; */ + /* exclude all nodes under this excluded node */ + fast_exclude(); }