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

Problem was that on second execution marked more selects as needed:
  merged derived removed from SELECT tree and limit (last) in
  st_select_lex::mark_as_dependent is skipped.

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

NOTE: problem also exists in 10.2 but has no vosoble effect on execution.

The patch also add debug logging of important procedures and
 bettr specify parametrs 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 48ac7e62653..06e727f95b7 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 a46d516ff29..c93cbed4326 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -5283,6 +5283,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 c2bc8386404..fe4d086bd22 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2723,7 +2723,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);
@@ -2731,10 +2731,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
@@ -2756,7 +2760,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 474f3174ac9..b5b39feca91 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1211,7 +1211,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 1eb23781da2..275f6b0699d 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -12840,10 +12840,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. */
@@ -12852,7 +12854,7 @@ bool JOIN_TAB::preread_init()
        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())
@@ -12870,9 +12872,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);
 }