mdev32034-alt.diff

diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 3b7961c2349..b41de0cc859 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -9041,5 +9041,342 @@ JS
 drop table t1,t2,t3,t10,t11;
 set optimizer_trace=DEFAULT;
 #
+# MDEV-32034: Second SELECT from view created from
+#             INFORMATION_SCHEMA.OPTIMIZER_TRACE gives NULL
+#
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 SELECT seq, seq from seq_1_to_100;
+SET optimizer_trace=1;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+EXPLAIN EXTENDED SELECT * from t1 WHERE a BETWEEN 1 AND 5 AND b <= 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	0.22	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` between 1 and 5 and `test`.`t1`.`b` <= 5
+# Standard way of accessing the optimizer trace:
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS
+FROM information_schema.optimizer_trace;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["1 <= a <= 5"],
+            "selectivity_from_histogram": 0.0469
+        },
+        {
+            "column_name": "b",
+            "ranges": 
+            ["NULL < b <= 5"],
+            "selectivity_from_histogram": 0.0469
+        }
+    ]
+]
+CREATE VIEW v1 AS
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS
+FROM information_schema.optimizer_trace;
+# Check that the trace accessed via a view is the same as before:
+SELECT * FROM v1;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["1 <= a <= 5"],
+            "selectivity_from_histogram": 0.0469
+        },
+        {
+            "column_name": "b",
+            "ranges": 
+            ["NULL < b <= 5"],
+            "selectivity_from_histogram": 0.0469
+        }
+    ]
+]
+# Make sure trace is not overwritten with the previous SELECT:
+SELECT * FROM v1;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["1 <= a <= 5"],
+            "selectivity_from_histogram": 0.0469
+        },
+        {
+            "column_name": "b",
+            "ranges": 
+            ["NULL < b <= 5"],
+            "selectivity_from_histogram": 0.0469
+        }
+    ]
+]
+# A view containing I_S.OPTIMIZER_TRACE involved, no trace overwrite:
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 10 AND 50 AND b > 10
+AND EXISTS (SELECT * FROM v1 WHERE JS IS NOT NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	optimizer_trace	ALL	NULL	NULL	NULL	NULL	NULL	Using where
+# Check the trace is not overwritten:
+SELECT * FROM v1;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["1 <= a <= 5"],
+            "selectivity_from_histogram": 0.0469
+        },
+        {
+            "column_name": "b",
+            "ranges": 
+            ["NULL < b <= 5"],
+            "selectivity_from_histogram": 0.0469
+        }
+    ]
+]
+# Overwrite the trace:
+EXPLAIN EXTENDED SELECT * from t1 WHERE a between 10 and 50 and b > 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	38.72	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` between 10 and 50 and `test`.`t1`.`b` > 5
+SELECT * FROM v1;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["10 <= a <= 50"],
+            "selectivity_from_histogram": 0.4062
+        },
+        {
+            "column_name": "b",
+            "ranges": 
+            ["5 < b"],
+            "selectivity_from_histogram": 0.9531
+        }
+    ]
+]
+# Prepared statement
+PREPARE stmt FROM
+"SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS
+  FROM information_schema.optimizer_trace";
+EXECUTE stmt;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["10 <= a <= 50"],
+            "selectivity_from_histogram": 0.4062
+        },
+        {
+            "column_name": "b",
+            "ranges": 
+            ["5 < b"],
+            "selectivity_from_histogram": 0.9531
+        }
+    ]
+]
+EXECUTE stmt;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["10 <= a <= 50"],
+            "selectivity_from_histogram": 0.4062
+        },
+        {
+            "column_name": "b",
+            "ranges": 
+            ["5 < b"],
+            "selectivity_from_histogram": 0.9531
+        }
+    ]
+]
+DEALLOCATE PREPARE stmt;
+# Prepared statement with view
+PREPARE stmt FROM "SELECT * FROM v1";
+EXECUTE stmt;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["10 <= a <= 50"],
+            "selectivity_from_histogram": 0.4062
+        },
+        {
+            "column_name": "b",
+            "ranges": 
+            ["5 < b"],
+            "selectivity_from_histogram": 0.9531
+        }
+    ]
+]
+EXECUTE stmt;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["10 <= a <= 50"],
+            "selectivity_from_histogram": 0.4062
+        },
+        {
+            "column_name": "b",
+            "ranges": 
+            ["5 < b"],
+            "selectivity_from_histogram": 0.9531
+        }
+    ]
+]
+# Overwrite the trace:
+EXPLAIN EXTENDED SELECT * from t1 WHERE a between 1 and 5 and b <= 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	0.22	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` between 1 and 5 and `test`.`t1`.`b` <= 5
+EXECUTE stmt;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["1 <= a <= 5"],
+            "selectivity_from_histogram": 0.0469
+        },
+        {
+            "column_name": "b",
+            "ranges": 
+            ["NULL < b <= 5"],
+            "selectivity_from_histogram": 0.0469
+        }
+    ]
+]
+DEALLOCATE PREPARE stmt;
+# Stored procedure
+CREATE PROCEDURE sp1()
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS
+FROM information_schema.optimizer_trace;
+CALL sp1;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["1 <= a <= 5"],
+            "selectivity_from_histogram": 0.0469
+        },
+        {
+            "column_name": "b",
+            "ranges": 
+            ["NULL < b <= 5"],
+            "selectivity_from_histogram": 0.0469
+        }
+    ]
+]
+CALL sp1;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["1 <= a <= 5"],
+            "selectivity_from_histogram": 0.0469
+        },
+        {
+            "column_name": "b",
+            "ranges": 
+            ["NULL < b <= 5"],
+            "selectivity_from_histogram": 0.0469
+        }
+    ]
+]
+# Stored procedure with view
+CREATE PROCEDURE sp2() SELECT * FROM v1;
+CALL sp2;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["1 <= a <= 5"],
+            "selectivity_from_histogram": 0.0469
+        },
+        {
+            "column_name": "b",
+            "ranges": 
+            ["NULL < b <= 5"],
+            "selectivity_from_histogram": 0.0469
+        }
+    ]
+]
+CALL sp2;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["1 <= a <= 5"],
+            "selectivity_from_histogram": 0.0469
+        },
+        {
+            "column_name": "b",
+            "ranges": 
+            ["NULL < b <= 5"],
+            "selectivity_from_histogram": 0.0469
+        }
+    ]
+]
+# Overwrite the trace:
+EXPLAIN EXTENDED SELECT * from t1 WHERE a BETWEEN 5 AND 15 AND b > 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	9.91	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` between 5 and 15 and `test`.`t1`.`b` > 10
+CALL sp2;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["5 <= a <= 15"],
+            "selectivity_from_histogram": 0.1094
+        },
+        {
+            "column_name": "b",
+            "ranges": 
+            ["10 < b"],
+            "selectivity_from_histogram": 0.9062
+        }
+    ]
+]
+DROP VIEW v1;
+DROP TABLE t1;
+DROP PROCEDURE sp1;
+DROP PROCEDURE sp2;
+set optimizer_trace=DEFAULT;
+#
 # End of 10.4 tests
 #
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index 5ae00c8236f..8e32a59a258 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -765,15 +765,6 @@ select * from
                from t10 left join t11 on t11.col1=t10.col1
                group by grp_id) T on T.grp_id=t1.b;
 
-# Enable after fix MDEV-31408
-# On the first creation of the view from information_schema.optimizer_trace
-# everything is fine, but on the second creation of the view is
-# from information_schema.optimizer_trace the result of select
-# from this view is already returned NULL.
-# That's why view-protocol is disabled here
-
---disable_view_protocol
-
 select json_detailed(json_extract(trace, '$**.check_split_materialized')) as JS
 from information_schema.optimizer_trace;
 
@@ -786,10 +777,81 @@ select
   ) as JS
 from information_schema.optimizer_trace;
 
---enable_view_protocol
 drop table t1,t2,t3,t10,t11;
 set optimizer_trace=DEFAULT;
 
+--echo #
+--echo # MDEV-32034: Second SELECT from view created from
+--echo #             INFORMATION_SCHEMA.OPTIMIZER_TRACE gives NULL
+--echo #
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 SELECT seq, seq from seq_1_to_100;
+SET optimizer_trace=1;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+EXPLAIN EXTENDED SELECT * from t1 WHERE a BETWEEN 1 AND 5 AND b <= 5;
+--echo # Standard way of accessing the optimizer trace:
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS
+  FROM information_schema.optimizer_trace;
+
+CREATE VIEW v1 AS
+  SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS
+  FROM information_schema.optimizer_trace;
+
+--echo # Check that the trace accessed via a view is the same as before:
+SELECT * FROM v1;
+--echo # Make sure trace is not overwritten with the previous SELECT:
+SELECT * FROM v1;
+--echo # A view containing I_S.OPTIMIZER_TRACE involved, no trace overwrite:
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 10 AND 50 AND b > 10
+                         AND EXISTS (SELECT * FROM v1 WHERE JS IS NOT NULL);
+--echo # Check the trace is not overwritten:
+SELECT * FROM v1;
+
+--echo # Overwrite the trace:
+EXPLAIN EXTENDED SELECT * from t1 WHERE a between 10 and 50 and b > 5;
+
+SELECT * FROM v1;
+
+--echo # Prepared statement
+PREPARE stmt FROM
+  "SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS
+  FROM information_schema.optimizer_trace";
+EXECUTE stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+--echo # Prepared statement with view
+PREPARE stmt FROM "SELECT * FROM v1";
+EXECUTE stmt;
+EXECUTE stmt;
+--echo # Overwrite the trace:
+EXPLAIN EXTENDED SELECT * from t1 WHERE a between 1 and 5 and b <= 5;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+--echo # Stored procedure
+CREATE PROCEDURE sp1()
+  SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS
+  FROM information_schema.optimizer_trace;
+CALL sp1;
+CALL sp1;
+
+--echo # Stored procedure with view
+CREATE PROCEDURE sp2() SELECT * FROM v1;
+set @a=sin(0);
+CALL sp2;
+CALL sp2;
+--echo # Overwrite the trace:
+EXPLAIN EXTENDED SELECT * from t1 WHERE a BETWEEN 5 AND 15 AND b > 10;
+CALL sp2;
+
+DROP VIEW v1;
+DROP TABLE t1;
+DROP PROCEDURE sp1;
+DROP PROCEDURE sp2;
+set optimizer_trace=DEFAULT;
+
 --echo #
 --echo # End of 10.4 tests
 --echo #
diff --git a/mysql-test/main/opt_trace_index_merge.test b/mysql-test/main/opt_trace_index_merge.test
index 1181aaa3db1..30c29802c97 100644
--- a/mysql-test/main/opt_trace_index_merge.test
+++ b/mysql-test/main/opt_trace_index_merge.test
@@ -119,8 +119,6 @@ insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1
 insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
 set optimizer_trace='enabled=on';
 
-# Enable after fix MDEV-31408
---disable_view_protocol
 --echo #  3-way ROR-intersection
 explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
@@ -130,7 +128,6 @@ select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) AS
 explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
---enable_view_protocol
 
 drop table t0,t1;
 set optimizer_trace="enabled=off";
diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc
index 9957e524e90..e4bfc69bb7e 100644
--- a/sql/opt_trace.cc
+++ b/sql/opt_trace.cc
@@ -28,26 +28,6 @@
 
 const char I_S_table_name[]= "OPTIMIZER_TRACE";
 
-/**
-   Whether a list of tables contains information_schema.OPTIMIZER_TRACE.
-   @param  tbl  list of tables
-
-   Can we do better than this here??
-   @note this does not catch that a stored routine or view accesses
-   the OPTIMIZER_TRACE table. So using a stored routine or view to read
-   OPTIMIZER_TRACE will overwrite OPTIMIZER_TRACE as it runs and provide
-   uninteresting info.
-*/
-bool list_has_optimizer_trace_table(const TABLE_LIST *tbl)
-{
-  for (; tbl; tbl= tbl->next_global)
-  {
-    if (tbl->schema_table &&
-        0 == strcmp(tbl->schema_table->table_name, I_S_table_name))
-      return true;
-  }
-  return false;
-}
 
 /*
   Returns if a query has a set command with optimizer_trace being switched on/off.
@@ -242,6 +222,9 @@ void opt_trace_disable_if_no_tables_access(THD *thd, TABLE_LIST *tbl)
   for (TABLE_LIST *t= tbl; t != NULL && t != first_not_own_table;
        t= t->next_global)
   {
+    if (t->schema_table && !strcmp(t->schema_table->table_name, 
+                                     I_S_table_name))
+      trace->set_uses_opt_trace_table();
     /*
       Anonymous derived tables (as in
       "SELECT ... FROM (SELECT ...)") don't have their grant.privilege set.
@@ -448,12 +431,22 @@ void Opt_trace_context::start(THD *thd, TABLE_LIST *tbl,
   current_trace= new Opt_trace_stmt(this);
   max_mem_size= max_mem_size_arg;
   set_allowed_mem_size(remaining_mem_size());
+  query_uses_is_opt_trace= false;
 }
 
 void Opt_trace_context::end()
 {
-  if (current_trace)
+  /*
+    The second call to list_has_optimizer_trace_table() here yiels more accurate
+    results compared to the first one in the Opt_trace_start constructor.
+    At this point, references resolved, enabling us to determine whether
+    the traced query includes a view created from `I_S.OPTIMIZER_TRACE`.
+    If so, the current trace is discarded and the previous one remains.
+  */
+  if (current_trace && !query_uses_is_opt_trace)
     traces.push(current_trace);
+  else
+    delete current_trace;
 
   if (!traces.elements())
     return;
@@ -481,7 +474,6 @@ Opt_trace_start::Opt_trace_start(THD *thd, TABLE_LIST *tbl,
   traceable= FALSE;
   if (unlikely(var & Opt_trace_context::FLAG_ENABLED) &&
       sql_command_can_be_traced(sql_command) &&
-      !list_has_optimizer_trace_table(tbl) &&
       !sets_var_optimizer_trace(sql_command, set_vars) &&
       !thd->system_thread &&
       !ctx->disable_tracing_if_required())
diff --git a/sql/opt_trace_context.h b/sql/opt_trace_context.h
index f578a0c67ec..0e90d41f93c 100644
--- a/sql/opt_trace_context.h
+++ b/sql/opt_trace_context.h
@@ -71,6 +71,7 @@ class Opt_trace_context
   {
     return *(traces.front());
   }
+  //const TABLE_LIST *tables_list;
 
 public:
 
@@ -122,13 +123,15 @@ class Opt_trace_context
     FLAG_DEFAULT = 0,
     FLAG_ENABLED = 1 << 0
   };
-
+ 
+  void set_uses_opt_trace_table() { query_uses_is_opt_trace= true; }
 private:
+  bool query_uses_is_opt_trace= false;
   /*
     List of traces (currently it stores only 1 trace)
   */
   Dynamic_array<Opt_trace_stmt*> traces;
-  Opt_trace_stmt *current_trace;
+  Opt_trace_stmt *current_trace; // psergey: trace we're currently writing, if any.
   size_t max_mem_size;
 };