MDEV-25973: The test main.opt_trace fails in case it is run in PS modebb-10.6-MDEV-26150

The test main.opt_trace failed since querying from the table
INFORMATION_SCHEMA.OPTIMIZER_TRACE produced an output that differed from
the expected one in the following way:
  @@ -2829,14 +2829,6 @@
                     }
                   },
                   {
  -                  "transformation": {
  -                    "select_id": 2,
  -                    "from": "IN (SELECT)",
  -                    "to": "semijoin",
  -                    "chosen": true
  -                  }
  -                },
  -                {
                     "expanded_query": "/* select#2 */ select t10.pk from t10"
                   }

in case the test is run with the option --ps-protocol.

The table INFORMATION_SCHEMA.OPTIMIZER_TRACE is filled when optimizer_trace is on.

The reason of missing above mentioned pieces in query result set is that
the C++ macros
  OPT_TRACE_TRANSFORM(thd, trace_wrapper, trace_transform,
                      select_lex->select_number,
                      "IN (SELECT)", "semijoin");
located in the standalone function check_and_do_in_subquery_rewrites()
is executed twice in case the statement
  explain extended select * from t1 where a in (select pk from t10);
is run in PS mode. The first time it is executed on PREPARE phase and
the second time on EXECUTE phase. The output produced by this macros on
EXECUTE phase rewrites the output produced on PREPARE phase.
In result test failed in case it was run in PS mode.

To make test output uniform regardless the test is run in PS or normal
mode the operator '--source include/protocol.inc' has been added to
the file opt_trace.test and extra opt_trace,ps.rdiff file has been added.

Additionally, added operators
  --enable_prepared_warnings/--disable_prepared_warnings
in order to store warnings in result file that received on PREPARE phase
during running the statemement 'SELECT INTO'.

2 files changed, 95 insertions, 4 deletions

diff --git a/mysql-test/main/opt_trace,ps.rdiff b/mysql-test/main/opt_trace,ps.rdiff
new file mode 100644
index 00000000000..f79d3f3d9ea
--- /dev/null
+++ b/mysql-test/main/opt_trace,ps.rdiff
@@ -0,0 +1,92 @@
+--- opt_trace.result
++++ opt_trace,ps.result
+@@ -2829,14 +2829,6 @@
+                   }
+                 },
+                 {
+-                  "transformation": {
+-                    "select_id": 2,
+-                    "from": "IN (SELECT)",
+-                    "to": "semijoin",
+-                    "chosen": true
+-                  }
+-                },
+-                {
+                   "expanded_query": "/* select#2 */ select t10.pk from t10"
+                 }
+               ]
+@@ -4402,14 +4394,6 @@
+                   }
+                 },
+                 {
+-                  "transformation": {
+-                    "select_id": 2,
+-                    "from": "IN (SELECT)",
+-                    "to": "semijoin",
+-                    "chosen": true
+-                  }
+-                },
+-                {
+                   "expanded_query": "/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2"
+                 }
+               ]
+@@ -4852,14 +4836,6 @@
+                   }
+                 },
+                 {
+-                  "transformation": {
+-                    "select_id": 2,
+-                    "from": "IN (SELECT)",
+-                    "to": "semijoin",
+-                    "chosen": true
+-                  }
+-                },
+-                {
+                   "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1"
+                 }
+               ]
+@@ -4879,14 +4855,6 @@
+                   }
+                 },
+                 {
+-                  "transformation": {
+-                    "select_id": 3,
+-                    "from": "IN (SELECT)",
+-                    "to": "semijoin",
+-                    "chosen": true
+-                  }
+-                },
+-                {
+                   "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4"
+                 }
+               ]
+@@ -6432,14 +6400,6 @@
+                   }
+                 },
+                 {
+-                  "transformation": {
+-                    "select_id": 2,
+-                    "from": "IN (SELECT)",
+-                    "to": "semijoin",
+-                    "chosen": true
+-                  }
+-                },
+-                {
+                   "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1"
+                 }
+               ]
+@@ -6459,14 +6419,6 @@
+                   }
+                 },
+                 {
+-                  "transformation": {
+-                    "select_id": 3,
+-                    "from": "IN (SELECT)",
+-                    "to": "semijoin",
+-                    "chosen": true
+-                  }
+-                },
+-                {
+                   "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4"
+                 }
+               ]
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index 35a1f99236f..e0c65723012 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -1,9 +1,6 @@
-if (`SELECT $PS_PROTOCOL != 0`)
-{
-   --skip Test temporarily disabled for ps-protocol
-}
 --source include/not_embedded.inc
 --source include/have_sequence.inc
+--source include/protocol.inc
 SELECT table_name, column_name FROM information_schema.columns where table_name="OPTIMIZER_TRACE";
 show variables like 'optimizer_trace';
 set optimizer_trace="enabled=on";
@@ -267,12 +264,14 @@ begin
   return a;
 end|
 
+--enable_prepare_warnings
 create function f2(a int) returns int
 begin
   declare a int default 0;
   select count(*) from t2 into a;
   return a;
 end|
+--disable_prepare_warnings
 
 delimiter ;|
 set optimizer_trace='enabled=on';