[MDEV-9456] Using SET multiple times on the same variable fails. Created: 2016-01-23  Updated: 2016-01-24

Status: Stalled
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.1.10
Fix Version/s: 10.1

Type: Bug Priority: Minor
Reporter: Michael Widenius Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Setting optimizer_switch twice only sets the later value:

set statement optimizer_switch="index_merge=off", optimizer_switch="index_merge_sort_union=off" FOR select @@optimizer_switch;

index_merge=on,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=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



 Comments   
Comment by Oleksandr Byelkin [ 2016-01-24 ]

It is not a SET STATEMENT problem, SET SESSION has exactly the same problem:

set session optimizer_switch=default;
set statement optimizer_switch="index_merge=off", optimizer_switch="index_merge_sort_union=off" FOR select @@optimizer_switch;
@@optimizer_switch
index_merge=on,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=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
set session optimizer_switch=default;
set session optimizer_switch="index_merge=off", optimizer_switch="index_merge_sort_union=off";
select @@optimizer_switch;
@@optimizer_switch
index_merge=on,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=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
set session optimizer_switch=default;

Comment by Oleksandr Byelkin [ 2016-01-24 ]

The problem is that sql_set_variables() work like this:

1) first call check() for all variables
2) then call update() for all variables

but Sys_var_flagset save old value during do_check() called from check() and so first set is just ignored.

Above makes of course operation more "atomic" first check then update and if check fails no update done. Also order of set and reset for STATEMENT case is same so restored value is correct (can be changed of revers order with no problem).

Will check if Sys_var_flagset the only class which saves value...

Comment by Oleksandr Byelkin [ 2016-01-24 ]

Other class do the same (saving result on check) but because we can't set it in more then 1 value it has no such side effect. Only (maybe) some variable with side effect (where they depend on which state they are switching to which other one) can have the same problems Sys_var_flagset have... on the top of my head it worse to check query cache status variables...

Comment by Oleksandr Byelkin [ 2016-01-24 ]

IMHO correct solution will be to make yet another phase of storing old value and do it for each variable just before updating it...

For SET STATEMENT it will also add whet we have to reset in revers order.

Generated at Thu Feb 08 07:34:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.