MDEV-25182 Complex query in Store procedure corrupts resultsbb-10.2-MDEV-25182

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, 77 insertions, 7 deletions

diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result
index 0e8b49d5714..069b0aa65c9 100644
--- a/mysql-test/r/derived_opt.result
+++ b/mysql-test/r/derived_opt.result
@@ -537,4 +537,31 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using temporary; Using filesort
 set join_cache_level=default;
 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/t/derived_opt.test b/mysql-test/t/derived_opt.test
index aab95f69f26..305cac120a0 100644
--- a/mysql-test/t/derived_opt.test
+++ b/mysql-test/t/derived_opt.test
@@ -401,5 +401,38 @@ set join_cache_level=default;
 
 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 55d135c660e..42272fe0148 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4678,6 +4678,9 @@ static bool mark_as_dependent(THD *thd, SELECT_LEX *last, SELECT_LEX *current,
                               bool suppress_warning_output)
 {
   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 5059e4f656e..bfe773b2c00 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2470,7 +2470,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);
@@ -2478,10 +2478,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
@@ -2503,7 +2507,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;
   this->master_unit()->item->is_correlated= TRUE;
   return FALSE;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index f733f783d0e..31751a16471 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1004,7 +1004,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 7bfbf719017..90c071803a1 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -12097,10 +12097,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. */
@@ -12108,7 +12110,7 @@ bool JOIN_TAB::preread_init()
        derived->is_recursive_with_table()) &&
       mysql_handle_single_derived(join->thd->lex,
                                     derived, DT_CREATE | DT_FILL))
-      return TRUE;
+      DBUG_RETURN(TRUE);
 
   preread_init_done= TRUE;
   if (select && select->quick)
@@ -12125,7 +12127,7 @@ bool JOIN_TAB::preread_init()
   if (table->fulltext_searched)
     init_ftfuncs(join->thd, join->select_lex, MY_TEST(join->order));
 
-  return FALSE;
+  DBUG_RETURN(FALSE);
 }