MDEV-25182 Complex query in Store procedure corrupts resultsbb-10.5-TODO-2875

At the second execution of the PS
1. mark_as_dependent() is called with the same parameters as at the first
   execution (select#4 and select#3)
2. as outer_select (select#3) has been already merged at the first
   execution of PS it cannot be reached using the outer_select() function
   anymore (and so can not stop iteration).
3. as a result all selects towards the top level select including the
   select for 'ca' are marked as uncacheable.
4. Marked uncacheable it executed incorrectly triggering filling its
   temporary table several times and using freed memory at the end.

To avoid the problem we use name resolution context to go "up".

NOTE: problem also exists in 10.2 but has no visible effect on execution.
That is why the problem is fixed in 10.2.

The patch also add debug logging of important procedures and
better specify parameters types of st_select_lex::mark_as_dependent.

6 files changed, 78 insertions, 8 deletions

diff --git a/mysql-test/main/derived_opt.result b/mysql-test/main/derived_opt.result
index 9da9e6e4413..cf0c1cb617f 100644
--- a/mysql-test/main/derived_opt.result
+++ b/mysql-test/main/derived_opt.result
@@ -540,4 +540,31 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 set join_cache_level=default;
 set optimizer_switch= @save_optimizer_switch;
 DROP TABLE t1,t2;
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch="derived_merge=on";
+CREATE TABLE t1 (id int, d2 datetime, id1 int) ;
+insert into t1 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',3);
+CREATE TABLE t2 (id int, d1 datetime, id1 int) ;
+insert into t2 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',2);
+prepare stmt from "
+SELECT * from
+  (SELECT min(d2) AS d2, min(d1) AS d1 FROM
+     (SELECT  t1.d2 AS d2, (SELECT t2.d1
+                              FROM t2 WHERE t1.id1 = t2.id1
+                              ORDER BY t2.id DESC LIMIT 1) AS d1
+        FROM t1
+     ) dt2
+  ) ca
+  ORDER BY ca.d2;";
+execute stmt;
+d2	d1
+2020-01-01 10:10:10	2020-01-01 10:10:10
+execute stmt;
+d2	d1
+2020-01-01 10:10:10	2020-01-01 10:10:10
+set optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1, t2;
+#
+# End of 10.3 tests
+#
 set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/main/derived_opt.test b/mysql-test/main/derived_opt.test
index eccf4c13020..dee424559ee 100644
--- a/mysql-test/main/derived_opt.test
+++ b/mysql-test/main/derived_opt.test
@@ -406,5 +406,38 @@ set optimizer_switch= @save_optimizer_switch;
 
 DROP TABLE t1,t2;
 
+#
+# MDEV-25182: Complex query in Store procedure corrupts results
+#
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch="derived_merge=on";
+
+CREATE TABLE t1 (id int, d2 datetime, id1 int) ;
+insert into t1 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',3);
+
+CREATE TABLE t2 (id int, d1 datetime, id1 int) ;
+insert into t2 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',2);
+
+prepare stmt from "
+SELECT * from
+  (SELECT min(d2) AS d2, min(d1) AS d1 FROM
+     (SELECT  t1.d2 AS d2, (SELECT t2.d1
+                              FROM t2 WHERE t1.id1 = t2.id1
+                              ORDER BY t2.id DESC LIMIT 1) AS d1
+        FROM t1
+     ) dt2
+  ) ca
+  ORDER BY ca.d2;";
+
+execute stmt;
+execute stmt;
+
+set optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1, t2;
+
+--echo #
+--echo # End of 10.3 tests
+--echo #
+
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;
diff --git a/sql/item.cc b/sql/item.cc
index a21bd490272..dffa0ea9929 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -5058,6 +5058,9 @@ static bool mark_as_dependent(THD *thd, SELECT_LEX *last, SELECT_LEX *current,
                               Item_ident *mark_item)
 {
   DBUG_ENTER("mark_as_dependent");
+  DBUG_PRINT("info", ("current select: %d (%p)  last: %d (%p)",
+                      current->select_number, current,
+                      (last ? last->select_number : 0), last));
 
   /* store pointer on SELECT_LEX from which item is dependent */
   if (mark_item && mark_item->can_be_depended)
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 0ebf3907b58..1025c0e8612 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -3319,7 +3319,7 @@ void st_select_lex_unit::exclude_tree()
 */
 
 bool st_select_lex::mark_as_dependent(THD *thd, st_select_lex *last,
-                                      Item *dependency)
+                                      Item_ident *dependency)
 {
 
   DBUG_ASSERT(this != last);
@@ -3327,10 +3327,14 @@ bool st_select_lex::mark_as_dependent(THD *thd, st_select_lex *last,
   /*
     Mark all selects from resolved to 1 before select where was
     found table as depended (of select where was found table)
+
+    We move by name resolution context, bacause during merge can some select
+    be excleded from SELECT tree
   */
-  SELECT_LEX *s= this;
+  Name_resolution_context *c= &this->context;
   do
   {
+    SELECT_LEX *s= c->select_lex;
     if (!(s->uncacheable & UNCACHEABLE_DEPENDENT_GENERATED))
     {
       // Select is dependent of outer select
@@ -3352,7 +3356,7 @@ bool st_select_lex::mark_as_dependent(THD *thd, st_select_lex *last,
     if (subquery_expr && subquery_expr->mark_as_dependent(thd, last, 
                                                           dependency))
       return TRUE;
-  } while ((s= s->outer_select()) != last && s != 0);
+  } while ((c= c->outer_context) != NULL && (c->select_lex != last));
   is_correlated= TRUE;
   master_unit()->item->is_correlated= TRUE;
   return FALSE;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 10b71781ce4..11d0b5ea89e 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1376,7 +1376,8 @@ public:
   }
   inline bool is_subquery_function() { return master_unit()->item != 0; }
 
-  bool mark_as_dependent(THD *thd, st_select_lex *last, Item *dependency);
+  bool mark_as_dependent(THD *thd, st_select_lex *last,
+                         Item_ident *dependency);
 
   void set_braces(bool value)
   {
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 6029b92e0d9..e3d61284222 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -13632,10 +13632,12 @@ ha_rows JOIN_TAB::get_examined_rows()
 bool JOIN_TAB::preread_init()
 {
   TABLE_LIST *derived= table->pos_in_table_list;
+  DBUG_ENTER("JOIN_TAB::preread_init");
+
   if (!derived || !derived->is_materialized_derived())
   {
     preread_init_done= TRUE;
-    return FALSE;
+    DBUG_RETURN(FALSE);
   }
 
   /* Materialize derived table/view. */
@@ -13643,7 +13645,7 @@ bool JOIN_TAB::preread_init()
        derived->is_recursive_with_table() ||
        derived->get_unit()->uncacheable) &&
       mysql_handle_single_derived(join->thd->lex, derived, DT_CREATE | DT_FILL))
-      return TRUE;
+      DBUG_RETURN(TRUE);
 
   if (!(derived->get_unit()->uncacheable & UNCACHEABLE_DEPENDENT) ||
       derived->is_nonrecursive_derived_with_rec_ref())
@@ -13661,9 +13663,9 @@ bool JOIN_TAB::preread_init()
   /* init ftfuns for just initialized derived table */
   if (table->fulltext_searched)
     if (init_ftfuncs(join->thd, join->select_lex, MY_TEST(join->order)))
-      return TRUE;
+      DBUG_RETURN(TRUE);
 
-  return FALSE;
+  DBUG_RETURN(FALSE);
 }