optimizer_switch stored procedures in sys schema.bb-11.0-sysschema-optimizer_switch

Split optimizer_switch into individual option names and values, and return
it as result set.

5 files changed, 115 insertions, 0 deletions

diff --git a/mysql-test/suite/sysschema/r/all_sys_objects_exist.result b/mysql-test/suite/sysschema/r/all_sys_objects_exist.result
index 6dddd8a186c..4c1bf311450 100644
--- a/mysql-test/suite/sysschema/r/all_sys_objects_exist.result
+++ b/mysql-test/suite/sysschema/r/all_sys_objects_exist.result
@@ -129,6 +129,9 @@ version_patch	FUNCTION
 create_synonym_db	PROCEDURE
 diagnostics	PROCEDURE
 execute_prepared_stmt	PROCEDURE
+optimizer_switch_choice	PROCEDURE
+optimizer_switch_off	PROCEDURE
+optimizer_switch_on	PROCEDURE
 ps_setup_disable_background_threads	PROCEDURE
 ps_setup_disable_consumer	PROCEDURE
 ps_setup_disable_instrument	PROCEDURE
diff --git a/mysql-test/suite/sysschema/r/optimizer_switch.result b/mysql-test/suite/sysschema/r/optimizer_switch.result
new file mode 100644
index 00000000000..017276fc4b8
--- /dev/null
+++ b/mysql-test/suite/sysschema/r/optimizer_switch.result
@@ -0,0 +1,40 @@
+call sys.optimizer_switch_on();
+option	opt
+condition_pushdown_for_derived	on
+condition_pushdown_for_subquery	on
+condition_pushdown_from_having	on
+derived_merge	on
+derived_with_keys	on
+exists_to_in	on
+extended_keys	on
+firstmatch	on
+index_condition_pushdown	on
+index_merge	on
+index_merge_intersection	on
+index_merge_sort_union	on
+index_merge_union	on
+in_to_exists	on
+join_cache_bka	on
+join_cache_hashed	on
+join_cache_incremental	on
+loosescan	on
+materialization	on
+optimize_join_buffer_size	on
+orderby_uses_equalities	on
+outer_join_with_cache	on
+partial_match_rowid_merge	on
+partial_match_table_scan	on
+rowid_filter	on
+semijoin	on
+semijoin_with_cache	on
+split_materialized	on
+subquery_cache	on
+table_elimination	on
+call sys.optimizer_switch_off();
+option	opt
+engine_condition_pushdown	off
+index_merge_sort_intersection	off
+mrr	off
+mrr_cost_based	off
+mrr_sort_keys	off
+not_null_range_scan	off
diff --git a/mysql-test/suite/sysschema/t/optimizer_switch.test b/mysql-test/suite/sysschema/t/optimizer_switch.test
new file mode 100644
index 00000000000..b4d527e5519
--- /dev/null
+++ b/mysql-test/suite/sysschema/t/optimizer_switch.test
@@ -0,0 +1,2 @@
+call sys.optimizer_switch_on();
+call sys.optimizer_switch_off();
diff --git a/scripts/sys_schema/CMakeLists.txt b/scripts/sys_schema/CMakeLists.txt
index ccb268cc4fd..dc023174fc7 100644
--- a/scripts/sys_schema/CMakeLists.txt
+++ b/scripts/sys_schema/CMakeLists.txt
@@ -130,6 +130,7 @@ ${CMAKE_CURRENT_SOURCE_DIR}/views/p_s/session_ssl_status.sql
 ${CMAKE_CURRENT_SOURCE_DIR}/procedures/create_synonym_db.sql
 ${CMAKE_CURRENT_SOURCE_DIR}/procedures/execute_prepared_stmt.sql
 ${CMAKE_CURRENT_SOURCE_DIR}/procedures/diagnostics.sql
+${CMAKE_CURRENT_SOURCE_DIR}/procedures/optimizer_switch.sql
 ${CMAKE_CURRENT_SOURCE_DIR}/procedures/ps_statement_avg_latency_histogram.sql
 ${CMAKE_CURRENT_SOURCE_DIR}/procedures/ps_trace_statement_digest.sql
 ${CMAKE_CURRENT_SOURCE_DIR}/procedures/ps_trace_thread.sql
diff --git a/scripts/sys_schema/procedures/optimizer_switch.sql b/scripts/sys_schema/procedures/optimizer_switch.sql
new file mode 100644
index 00000000000..febeabc1208
--- /dev/null
+++ b/scripts/sys_schema/procedures/optimizer_switch.sql
@@ -0,0 +1,69 @@
+--  Copyright (C) 2023, MariaDB
+--
+--  This program is free software; you can redistribute it and/or modify
+--  it under the terms of the GNU General Public License as published by
+--  the Free Software Foundation; version 2 of the License.
+--
+--  This program is distributed in the hope that it will be useful,
+--  but WITHOUT ANY WARRANTY; without even the implied warranty of
+--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+--  GNU General Public License for more details.
+--
+--  You should have received a copy of the GNU General Public License
+--  along with this program; if not, write to the Free Software
+--  Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+
+DROP PROCEDURE IF EXISTS optimizer_switch_choice;
+DROP PROCEDURE IF EXISTS optimizer_switch_on;
+DROP PROCEDURE IF EXISTS optimizer_switch_off;
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE optimizer_switch_choice(IN on_off VARCHAR(3))
+COMMENT 'return @@optimizer_switch options as a result set for easier readability'
+SQL SECURITY INVOKER
+NOT DETERMINISTIC
+CONTAINS SQL
+BEGIN
+  DECLARE tmp VARCHAR(1024);
+  DECLARE opt VARCHAR(1024);
+  DECLARE start INT;
+  DECLARE end INT;
+  DECLARE pos INT;
+  set tmp=concat(@@optimizer_switch,",");
+  CREATE OR REPLACE TEMPORARY TABLE tmp_opt_switch (a varchar(64), opt CHAR(3)) character set latin1 engine=heap;
+  set start=1;
+  FIND_OPTIONS:
+  LOOP
+    set pos= INSTR(SUBSTR(tmp, start), ",");
+    if (pos = 0) THEN
+       LEAVE FIND_OPTIONS;
+    END IF;
+    set opt= MID(tmp, start, pos-1);
+    set end= INSTR(opt, "=");
+    insert into tmp_opt_switch values(LEFT(opt,end-1),SUBSTR(opt,end+1));
+    set start=start + pos;
+  END LOOP;
+  SELECT  t.a as "option",t.opt from tmp_opt_switch as t where t.opt = on_off order by a;
+  DROP TEMPORARY TABLE tmp_opt_switch;
+END$$
+
+CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE optimizer_switch_on()
+COMMENT 'return @@optimizer_switch options that are on'
+SQL SECURITY INVOKER
+NOT DETERMINISTIC
+CONTAINS SQL
+BEGIN
+  call optimizer_switch_choice("on");
+END$$
+
+CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE optimizer_switch_off()
+COMMENT 'return @@optimizer_switch options that are off'
+SQL SECURITY INVOKER
+NOT DETERMINISTIC
+CONTAINS SQL
+BEGIN
+  call optimizer_switch_choice("off");
+END$$
+
+DELIMITER ;
+