MDEV-26149: The test main.fetch_first fails in case it is run in PS modebb-10.6-MDEV-26149

The root cause of test failure is that on optimization
of the statement clause 'order by (select 1)' in outer select
is done incorrect in case the statement run in PS mode.

3 files changed, 36 insertions, 16 deletions

diff --git a/mysql-test/main/fetch_first.test b/mysql-test/main/fetch_first.test
index 8a5cb2c8e73..7c41b922978 100644
--- a/mysql-test/main/fetch_first.test
+++ b/mysql-test/main/fetch_first.test
@@ -1,7 +1,3 @@
-if (`SELECT $PS_PROTOCOL != 0`)
-{
-   --skip Test temporarily disabled for ps-protocol
-}
 --echo #
 --echo # The following entries are meant for testing the parser, ensuring
 --echo # the right values are passed down to the executor, for all possible
diff --git a/sql/item.h b/sql/item.h
index 8ab4c8a3bd1..66e2a583b98 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1818,6 +1818,14 @@ public:
     their method implementations typically have DBUG_ASSERT(0).
   */
   virtual bool is_evaluable_expression() const { return true; }
+
+  /**
+   * Check whether the item is a parameter  ('?') of stored routine.
+   * Default implementation returns false. Method is overridden in the class
+   * Item_param where it returns true.
+   */
+  virtual bool is_stored_routine_parameter() const { return false; }
+
   bool check_is_evaluable_expression_or_error()
   {
     if (is_evaluable_expression())
@@ -4281,6 +4289,7 @@ public:
     return state == SHORT_DATA_VALUE &&
            value.type_handler()->cmp_type() == INT_RESULT;
   }
+  bool is_stored_routine_parameter() const override { return true; }
   /*
     This method is used to make a copy of a basic constant item when
     propagating constants in the optimizer. The reason to create a new
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 381f015ca96..3e2ea561dce 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1273,22 +1273,37 @@ Item_singlerow_subselect::select_transformer(JOIN *join)
   Query_arena *arena, backup;
   arena= thd->activate_stmt_arena_if_needed(&backup);
 
+  auto need_to_pull_out_item = [](enum_parsing_place context_analysis_place,
+                                  Item *item) {
+    return
+      !item->with_sum_func() &&
+      /*
+        We can't change name of Item_field or Item_ref, because it will
+        prevent its correct resolving, but we should save name of
+        removed item => we do not make optimization if top item of
+        list is field or reference.
+        TODO: solve above problem
+      */
+      item->type() != FIELD_ITEM && item->type() != REF_ITEM &&
+      /*
+        The item can be pulled out to upper level in case it doesn't represent
+        the constant in the clause 'ORDER/GROUP BY (constant)'.
+      */
+      !((item->is_order_clause_position() ||
+         item->is_stored_routine_parameter()) &&
+        (context_analysis_place == IN_ORDER_BY ||
+         context_analysis_place == IN_GROUP_BY)
+       );
+  };
+
   if (!select_lex->master_unit()->is_unit_op() &&
       !select_lex->table_list.elements &&
       select_lex->item_list.elements == 1 &&
-      !select_lex->item_list.head()->with_sum_func() &&
-      /*
-	We can't change name of Item_field or Item_ref, because it will
-	prevent its correct resolving, but we should save name of
-	removed item => we do not make optimization if top item of
-	list is field or reference.
-	TODO: solve above problem
-      */
-      !(select_lex->item_list.head()->type() == FIELD_ITEM ||
-	select_lex->item_list.head()->type() == REF_ITEM) &&
       !join->conds && !join->having &&
-      thd->stmt_arena->state != Query_arena::STMT_INITIALIZED_FOR_SP
-      )
+      need_to_pull_out_item(
+        join->select_lex->outer_select()->context_analysis_place,
+        select_lex->item_list.head()) &&
+      thd->stmt_arena->state != Query_arena::STMT_INITIALIZED_FOR_SP)
   {
     have_to_be_excluded= 1;
     if (thd->lex->describe)