diff options
author | Rucha Deodhar <rucha.deodhar@mariadb.com> | 2020-07-02 19:03:39 +0530 |
---|---|---|
committer | Rucha Deodhar <rucha.deodhar@mariadb.com> | 2020-07-12 20:03:32 +0530 |
commit | 0994af43e582fccf55f7893b16228b57e92330d0 (patch) | |
tree | 7d977bcd4d8a7565cc62267ee2862690f17e1063 | |
parent | f81ff93287349daeb62c46e699ac54996a09860b (diff) | |
download | mariadb-git-0994af43e582fccf55f7893b16228b57e92330d0.tar.gz |
MDEV-22058: Assertion `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' failed in Diagnostics_area::set_ok_status
Error state is not stored in check_and_do_in_subquery_rewrites() when there is
illegal combination of optimizer switches. So all the functions eventually
return false. Thus the assetion failure.
-rw-r--r-- | mysql-test/r/mysqltest_tracking_info.result | 4 | ||||
-rw-r--r-- | mysql-test/r/sp-error.result | 13 | ||||
-rw-r--r-- | mysql-test/r/subselect_exists2in.result | 8 | ||||
-rw-r--r-- | mysql-test/suite/sys_vars/r/optimizer_switch_basic.result | 22 | ||||
-rw-r--r-- | mysql-test/suite/sys_vars/t/optimizer_switch_basic.test | 4 | ||||
-rw-r--r-- | mysql-test/t/mysqltest_tracking_info.test | 2 | ||||
-rw-r--r-- | mysql-test/t/sp-error.test | 19 | ||||
-rw-r--r-- | mysql-test/t/subselect_exists2in.test | 8 | ||||
-rw-r--r-- | sql/opt_subselect.cc | 5 | ||||
-rw-r--r-- | sql/sys_vars.cc | 13 |
10 files changed, 70 insertions, 28 deletions
diff --git a/mysql-test/r/mysqltest_tracking_info.result b/mysql-test/r/mysqltest_tracking_info.result index 16d737dc4e6..142f5237876 100644 --- a/mysql-test/r/mysqltest_tracking_info.result +++ b/mysql-test/r/mysqltest_tracking_info.result @@ -35,10 +35,10 @@ SET @@session.session_track_system_variables= @save_session_track_system_variabl # set @save_optimizer_switch=@@optimizer_switch; SET @@session.session_track_system_variables='optimizer_switch'; -set optimizer_switch='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off'; +set optimizer_switch='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off'; -- Tracker : SESSION_TRACK_SYSTEM_VARIABLES -- optimizer_switch --- index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off +-- index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off Warnings: Warning 1681 'engine_condition_pushdown=on' is deprecated and will be removed in a future release diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index b9291973ab3..877c8bb1666 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -2872,3 +2872,16 @@ SELECT @msg; DROP FUNCTION f1; DROP FUNCTION f2; DROP TABLE t1; +# +# 10.2 Test +# +# MDEV-MDEV-22058: Assertion `!is_set() || (m_status == DA_OK_BULK && +# is_bulk_op())' failed in Diagnostics_area::set_ok_status +# +SET @old_optimizer_switch = @@SESSION.OPTIMIZER_SWITCH; +SET @cmd:="SET @@SESSION.SQL_MODE=(SELECT 'a')"; +SET @@SESSION.OPTIMIZER_SWITCH="in_to_exists=OFF,materialization=OFF"; +ERROR HY000: At least one of the 'in_to_exists' or 'materialization' optimizer_switch flags must be 'on' +PREPARE stmt FROM @cmd; +set @@SESSION.OPTIMIZER_SWITCH=@old_optimizer_switch; +# End of 10.2 Test diff --git a/mysql-test/r/subselect_exists2in.result b/mysql-test/r/subselect_exists2in.result index de01a4e7304..957f7b9bbff 100644 --- a/mysql-test/r/subselect_exists2in.result +++ b/mysql-test/r/subselect_exists2in.result @@ -437,8 +437,8 @@ drop table t1,t3; # MDEV-159 Assertion about not marked for read failed in # String* Field_varstring::val_str(String*, String*) # -SET optimizer_switch = REPLACE( @@optimizer_switch, '=on', '=off' ); -SET optimizer_switch='in_to_exists=on,exists_to_in=on'; +SET optimizer_switch = REPLACE(REPLACE(@@optimizer_switch, '=on', '=off'), 'in_to_exists=off', 'in_to_exists=on'); +SET optimizer_switch='exists_to_in=on'; CREATE TABLE t1 ( a VARCHAR(1) ); INSERT INTO t1 VALUES ('k'),('m'); CREATE TABLE t2 ( b INT, @@ -593,8 +593,8 @@ Warnings: Note 1276 Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`alias`.`a` AS `a`,`test`.`alias`.`b` AS `b` from `test`.`t1` `alias` semi join (`test`.`t1`) where `test`.`t1`.`a` = `test`.`alias`.`b` and `test`.`alias`.`b` > `test`.`alias`.`a` -SET optimizer_switch = REPLACE( @@optimizer_switch, '=on', '=off' ); -SET optimizer_switch = 'exists_to_in=on,materialization=on,semijoin=off'; +SET optimizer_switch = REPLACE(REPLACE(@@optimizer_switch, '=on', '=off'), 'materialization=off', 'materialization=on'); +SET optimizer_switch = 'exists_to_in=on,semijoin=off'; SELECT * FROM t1 AS alias WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); a b diff --git a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result index c37c6785b9b..d813f515f83 100644 --- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result @@ -17,38 +17,38 @@ OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,i select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on -set global optimizer_switch=10; -set session optimizer_switch=5; +set global optimizer_switch=4101; +set session optimizer_switch=2058; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off +index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off +index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off set global optimizer_switch="index_merge_sort_union=on"; set session optimizer_switch="index_merge=off"; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off +index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off +index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off show global variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off +optimizer_switch index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off show session variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off +optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off select * from information_schema.global_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off +OPTIMIZER_SWITCH index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off +OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off set session optimizer_switch="default"; select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off +index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off set optimizer_switch = replace(@@optimizer_switch, '=off', '=on'); Warnings: Warning 1681 'engine_condition_pushdown=on' is deprecated and will be removed in a future release diff --git a/mysql-test/suite/sys_vars/t/optimizer_switch_basic.test b/mysql-test/suite/sys_vars/t/optimizer_switch_basic.test index ef9d8f17832..ada22845f3e 100644 --- a/mysql-test/suite/sys_vars/t/optimizer_switch_basic.test +++ b/mysql-test/suite/sys_vars/t/optimizer_switch_basic.test @@ -19,8 +19,8 @@ select * from information_schema.session_variables where variable_name='optimize # # show that it's writable # -set global optimizer_switch=10; -set session optimizer_switch=5; +set global optimizer_switch=4101; +set session optimizer_switch=2058; select @@global.optimizer_switch; select @@session.optimizer_switch; set global optimizer_switch="index_merge_sort_union=on"; diff --git a/mysql-test/t/mysqltest_tracking_info.test b/mysql-test/t/mysqltest_tracking_info.test index da7732b5011..dc51167137a 100644 --- a/mysql-test/t/mysqltest_tracking_info.test +++ b/mysql-test/t/mysqltest_tracking_info.test @@ -36,7 +36,7 @@ set @save_optimizer_switch=@@optimizer_switch; SET @@session.session_track_system_variables='optimizer_switch'; --enable_session_track_info -set optimizer_switch='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off'; +set optimizer_switch='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off'; --disable_session_track_info set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 4dd3c51e4b0..b0c957ad164 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -3851,3 +3851,22 @@ SELECT @msg; DROP FUNCTION f1; DROP FUNCTION f2; DROP TABLE t1; + +--echo # +--echo # 10.2 Test +--echo # +--echo # MDEV-MDEV-22058: Assertion `!is_set() || (m_status == DA_OK_BULK && +--echo # is_bulk_op())' failed in Diagnostics_area::set_ok_status +--echo # + +SET @old_optimizer_switch = @@SESSION.OPTIMIZER_SWITCH; + +SET @cmd:="SET @@SESSION.SQL_MODE=(SELECT 'a')"; + +--error ER_ILLEGAL_SUBQUERY_OPTIMIZER_SWITCHES +SET @@SESSION.OPTIMIZER_SWITCH="in_to_exists=OFF,materialization=OFF"; +PREPARE stmt FROM @cmd; + +set @@SESSION.OPTIMIZER_SWITCH=@old_optimizer_switch; + +--echo # End of 10.2 Test diff --git a/mysql-test/t/subselect_exists2in.test b/mysql-test/t/subselect_exists2in.test index 1354953e972..2a9947123d4 100644 --- a/mysql-test/t/subselect_exists2in.test +++ b/mysql-test/t/subselect_exists2in.test @@ -330,8 +330,8 @@ drop table t1,t3; --echo # String* Field_varstring::val_str(String*, String*) --echo # -SET optimizer_switch = REPLACE( @@optimizer_switch, '=on', '=off' ); -SET optimizer_switch='in_to_exists=on,exists_to_in=on'; +SET optimizer_switch = REPLACE(REPLACE(@@optimizer_switch, '=on', '=off'), 'in_to_exists=off', 'in_to_exists=on'); +SET optimizer_switch='exists_to_in=on'; CREATE TABLE t1 ( a VARCHAR(1) ); INSERT INTO t1 VALUES ('k'),('m'); @@ -466,8 +466,8 @@ explain extended SELECT * FROM t1 AS alias WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); -SET optimizer_switch = REPLACE( @@optimizer_switch, '=on', '=off' ); -SET optimizer_switch = 'exists_to_in=on,materialization=on,semijoin=off'; +SET optimizer_switch = REPLACE(REPLACE(@@optimizer_switch, '=on', '=off'), 'materialization=off', 'materialization=on'); +SET optimizer_switch = 'exists_to_in=on,semijoin=off'; SELECT * FROM t1 AS alias WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); explain extended diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 2fb5b88687f..45aa625389d 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -701,9 +701,8 @@ int check_and_do_in_subquery_rewrites(JOIN *join) { DBUG_PRINT("info", ("Subquery can't be converted to merged semi-join")); /* Test if the user has set a legal combination of optimizer switches. */ - if (!optimizer_flag(thd, OPTIMIZER_SWITCH_IN_TO_EXISTS) && - !optimizer_flag(thd, OPTIMIZER_SWITCH_MATERIALIZATION)) - my_error(ER_ILLEGAL_SUBQUERY_OPTIMIZER_SWITCHES, MYF(0)); + DBUG_ASSERT(optimizer_flag(thd, OPTIMIZER_SWITCH_IN_TO_EXISTS | + OPTIMIZER_SWITCH_MATERIALIZATION)); /* Transform each subquery predicate according to its overloaded transformer. diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 2d40943827f..af261299496 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -2471,12 +2471,23 @@ static bool fix_optimizer_switch(sys_var *self, THD *thd, "engine_condition_pushdown=on"); return false; } +static bool check_legal_optimizer_switch(sys_var *self, THD *thd, + set_var *var) +{ + if (var->save_result.ulonglong_value & (OPTIMIZER_SWITCH_MATERIALIZATION | + OPTIMIZER_SWITCH_IN_TO_EXISTS)) + { + return false; + } + my_error(ER_ILLEGAL_SUBQUERY_OPTIMIZER_SWITCHES, MYF(0)); + return true; +} static Sys_var_flagset Sys_optimizer_switch( "optimizer_switch", "Fine-tune the optimizer behavior", SESSION_VAR(optimizer_switch), CMD_LINE(REQUIRED_ARG), optimizer_switch_names, DEFAULT(OPTIMIZER_SWITCH_DEFAULT), - NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(NULL), + NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_legal_optimizer_switch), ON_UPDATE(fix_optimizer_switch)); static Sys_var_charptr Sys_pid_file( |