revision-id: 0eaeb28c4d1914b6fa648214c18d111fe86ef773 (mariadb-10.2.31-1062-g0eaeb28) parent(s): f053349797a1dca5206a3b8d5ff33353f45430d8 author: Igor Babaev committer: Igor Babaev timestamp: 2021-07-20 00:07:31 -0700 message: MDEV-26025 Server crashes while executing query with CTE in PS/SP This bug appeared after the patch for bug MDEV-23886. Due to this bug execution of queries with CTEs used the same CTE at least twice via prepared statements or with stored procedures caused crashes of the server. It happened because the select created for any of not the first usage of a CTE erroneously was not included into all_selects_list. This patch corrects the patch applied to fix the bug MDEV-26108. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- mysql-test/r/cte_nonrecursive.result | 42 ++++++++++++++++++++++++++++++++++++ mysql-test/t/cte_nonrecursive.test | 27 +++++++++++++++++++++++ sql/sql_cte.cc | 15 ++++++++----- 3 files changed, 79 insertions(+), 5 deletions(-) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index 5cc5a25..2504e55 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -2044,4 +2044,46 @@ select a from t1 union select a+1 as a from cte_r r where a < 10 ) select * from cte_e; ERROR 42S02: Table 'test.cte_r' doesn't exist drop table t1; +# +# MDEV-26025: query with two usage of a CTE executing via PS /SP +# +create table t1 (a int, b int); +insert into t1 value (1,3), (3,2), (1,3), (4,1); +prepare stmt from "with +cte1 as ( select a,b from t1 where a = 1 AND b = 3 ), +cte2 as ( select a,b from cte1 ), +cte3 as ( select a,b from cte2 ) +select * from cte3, cte2"; +execute stmt; +a b a b +1 3 1 3 +1 3 1 3 +1 3 1 3 +1 3 1 3 +execute stmt; +a b a b +1 3 1 3 +1 3 1 3 +1 3 1 3 +1 3 1 3 +deallocate prepare stmt; +create procedure sp() with +cte1 as ( select a,b from t1 where a = 1 AND b = 3 ), +cte2 as ( select a,b from cte1 ), +cte3 as ( select a,b from cte2 ) +select * from cte3, cte2; +call sp(); +a b a b +1 3 1 3 +1 3 1 3 +1 3 1 3 +1 3 1 3 +call sp(); +a b a b +1 3 1 3 +1 3 1 3 +1 3 1 3 +1 3 1 3 +drop procedure sp; +drop table t1; # End of 10.2 tests diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index 68dbc0c..c20a0dc 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -1515,4 +1515,31 @@ with cte_e as ( drop table t1; +--echo # +--echo # MDEV-26025: query with two usage of a CTE executing via PS /SP +--echo # + +create table t1 (a int, b int); +insert into t1 value (1,3), (3,2), (1,3), (4,1); + +let $q= +with + cte1 as ( select a,b from t1 where a = 1 AND b = 3 ), + cte2 as ( select a,b from cte1 ), + cte3 as ( select a,b from cte2 ) +select * from cte3, cte2; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +eval create procedure sp() $q; + +call sp(); +call sp(); + +drop procedure sp; +drop table t1; + --echo # End of 10.2 tests diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index 702db8f..b720eac 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1012,6 +1012,7 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, bool parse_status= false; st_select_lex *with_select; + st_select_lex *last_clone_select; char save_end= unparsed_spec.str[unparsed_spec.length]; unparsed_spec.str[unparsed_spec.length]= '\0'; @@ -1099,11 +1100,6 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, lex->unit.include_down(with_table->select_lex); lex->unit.set_slave(with_select); lex->unit.cloned_from= spec; - old_lex->all_selects_list= - (st_select_lex*) (lex->all_selects_list-> - insert_chain_before( - (st_select_lex_node **) &(old_lex->all_selects_list), - with_select)); /* Now all references to the CTE defined outside of the cloned specification @@ -1119,6 +1115,15 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, goto err; } + last_clone_select= lex->all_selects_list; + while (last_clone_select->next_select_in_list()) + last_clone_select= last_clone_select->next_select_in_list(); + old_lex->all_selects_list= + (st_select_lex*) (lex->all_selects_list-> + insert_chain_before( + (st_select_lex_node **) &(old_lex->all_selects_list), + last_clone_select)); + lex->sphead= NULL; // in order not to delete lex->sphead lex_end(lex); err: