MDEV-24383 SIGSEGV in heap_info from make_join_statistics on 2nd SP exec.bb-10.2-hf

Since some optimizer switches lead to the changes in the
parser-generated structures, we have to reload cached SP-s after
the change.

5 files changed, 86 insertions, 4 deletions

diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 2106ba504a9..018aa0acabf 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -1204,5 +1204,46 @@ REPLACE INTO v2 ( SELECT * FROM v4 ) UNION ( SELECT f FROM v2 );
 drop view v1,v2,v3,v4;
 drop table t1,t2,t3;
 #
+# MDEV-42383 SIGSEGV in heap_info from make_join_statistics on 2nd SP exec.
+#
+CREATE TABLE t1 (name varchar(100));
+insert into t1 values ("one"), ("two"), ("threee");
+CREATE PROCEDURE p() SELECT * FROM (SELECT 1 FROM t1) AS a;
+SET SESSION optimizer_switch="derived_merge=OFF";
+CALL p();
+1
+1
+1
+1
+SET SESSION optimizer_switch="derived_merge=ON";
+CALL p();
+1
+1
+1
+1
+drop procedure p;
+SET GLOBAL optimizer_switch="derived_merge=OFF";
+connect  con1,localhost,root,,;
+connection con1;
+CREATE PROCEDURE p() SELECT * FROM (SELECT 1 FROM t1) AS a;
+CALL p();
+1
+1
+1
+1
+connection default;
+SET GLOBAL optimizer_switch="derived_merge=ON";
+connection con1;
+CALL p();
+1
+1
+1
+1
+drop procedure p;
+connection default;
+disconnect con1;
+drop table t1;
+set optimizer_switch=@save_derived_optimizer_switch;
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index 6d9d5e23cf9..a5fc3e0f93f 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -1037,5 +1037,34 @@ drop view v1,v2,v3,v4;
 drop table t1,t2,t3;
 
 --echo #
+--echo # MDEV-42383 SIGSEGV in heap_info from make_join_statistics on 2nd SP exec.
+--echo #
+CREATE TABLE t1 (name varchar(100));
+insert into t1 values ("one"), ("two"), ("threee");
+CREATE PROCEDURE p() SELECT * FROM (SELECT 1 FROM t1) AS a;
+SET SESSION optimizer_switch="derived_merge=OFF";
+CALL p();
+SET SESSION optimizer_switch="derived_merge=ON";
+CALL p();
+drop procedure p;
+
+SET GLOBAL optimizer_switch="derived_merge=OFF";
+connect (con1,localhost,root,,);
+connection con1;
+CREATE PROCEDURE p() SELECT * FROM (SELECT 1 FROM t1) AS a;
+CALL p();
+
+connection default;
+SET GLOBAL optimizer_switch="derived_merge=ON";
+connection con1;
+CALL p();
+drop procedure p;
+connection default;
+disconnect con1;
+
+drop table t1;
+set optimizer_switch=@save_derived_optimizer_switch;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 5ada018e540..dcfdf40c964 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -1353,8 +1353,7 @@ void THD::change_user(void)
   my_hash_init(&user_vars, system_charset_info, USER_VARS_HASH_SIZE, 0, 0,
                (my_hash_get_key) get_var_key,
                (my_hash_free_key) free_user_var, 0);
-  sp_cache_clear(&sp_proc_cache);
-  sp_cache_clear(&sp_func_cache);
+  clear_sp_caches();
 }
 
 
@@ -1410,8 +1409,7 @@ void THD::cleanup(void)
 #endif /* defined(ENABLED_DEBUG_SYNC) */
 
   my_hash_free(&user_vars);
-  sp_cache_clear(&sp_proc_cache);
-  sp_cache_clear(&sp_func_cache);
+  clear_sp_caches();
   auto_inc_intervals_forced.empty();
   auto_inc_intervals_in_cur_stmt_for_binlog.empty();
 
@@ -2123,6 +2121,13 @@ void THD::cleanup_after_query()
 }
 
 
+void THD::clear_sp_caches()
+{
+  sp_cache_clear(&sp_proc_cache);
+  sp_cache_clear(&sp_func_cache);
+}
+
+
 /*
   Convert a string to another character set
 
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 5f871f9caf6..2cf0f3f4530 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -3209,6 +3209,7 @@ public:
   void change_user(void);
   void cleanup(void);
   void cleanup_after_query();
+  void clear_sp_caches();
   void free_connection();
   void reset_for_reuse();
   bool store_globals();
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index e4de3d8d0aa..585806fdce5 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -53,6 +53,7 @@
 #include "log_slow.h"
 #include "debug_sync.h"                         // DEBUG_SYNC
 #include "sql_show.h"
+#include "sp_cache.h"                           // sp_cache_invalidate()
 
 #include "log_event.h"
 #ifdef WITH_PERFSCHEMA_STORAGE_ENGINE
@@ -2469,6 +2470,11 @@ static bool fix_optimizer_switch(sys_var *self, THD *thd,
                         ER_WARN_DEPRECATED_SYNTAX_NO_REPLACEMENT,
                         ER_THD(thd, ER_WARN_DEPRECATED_SYNTAX_NO_REPLACEMENT),
                         "engine_condition_pushdown=on");
+  if (type == OPT_GLOBAL)
+    sp_cache_invalidate();
+  else
+    thd->clear_sp_caches();
+
   return false;
 }
 static bool check_legal_optimizer_switch(sys_var *self, THD *thd,