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>

3 files changed, 79 insertions, 5 deletions

diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index 5cc5a2503e3..2504e55d77c 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 68dbc0cac19..c20a0dc1d39 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 702db8f96d9..b720eac2317 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: