engine_condition_pushdown_basic.result 26.8 KB
Newer Older
1 2 3
SET @session_start_value = @@session.engine_condition_pushdown;
SELECT @session_start_value;
@session_start_value
Sergei Golubchik's avatar
Sergei Golubchik committed
4
0
5 6 7
SET @global_start_value = @@global.engine_condition_pushdown;
SELECT @global_start_value;
@global_start_value
Sergei Golubchik's avatar
Sergei Golubchik committed
8
0
9 10 11
select @old_session_opt_switch:=@@session.optimizer_switch,
@old_global_opt_switch:=@@global.optimizer_switch;
@old_session_opt_switch:=@@session.optimizer_switch	@old_global_opt_switch:=@@global.optimizer_switch
Sergei Golubchik's avatar
Sergei Golubchik committed
12
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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
13 14
'#--------------------FN_DYNVARS_028_01------------------------#'
SET @@session.engine_condition_pushdown = 0;
15 16
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
17
SET @@session.engine_condition_pushdown = DEFAULT;
18 19
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
20 21
SELECT @@session.engine_condition_pushdown;
@@session.engine_condition_pushdown
Sergei Golubchik's avatar
Sergei Golubchik committed
22
0
23
SET @@global.engine_condition_pushdown = 0;
24 25
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
26
SET @@global.engine_condition_pushdown = DEFAULT;
27 28
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
29 30 31 32 33
SELECT @@global.engine_condition_pushdown;
@@global.engine_condition_pushdown
1
'#---------------------FN_DYNVARS_028_02-------------------------#'
SET engine_condition_pushdown = 1;
34 35
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
36 37 38 39 40 41 42 43 44 45
SELECT @@engine_condition_pushdown;
@@engine_condition_pushdown
1
SELECT session.engine_condition_pushdown;
ERROR 42S02: Unknown table 'session' in field list
SELECT local.engine_condition_pushdown;
ERROR 42S02: Unknown table 'local' in field list
SELECT global.engine_condition_pushdown;
ERROR 42S02: Unknown table 'global' in field list
SET session engine_condition_pushdown = 0;
46 47
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
48 49 50 51
SELECT @@session.engine_condition_pushdown;
@@session.engine_condition_pushdown
0
SET global engine_condition_pushdown = 0;
52 53
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
54 55 56 57 58
SELECT @@global.engine_condition_pushdown;
@@global.engine_condition_pushdown
0
'#--------------------FN_DYNVARS_028_03------------------------#'
SET @@session.engine_condition_pushdown = 0;
59 60
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
61 62 63 64
SELECT @@session.engine_condition_pushdown;
@@session.engine_condition_pushdown
0
SET @@session.engine_condition_pushdown = 1;
65 66
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
67 68 69 70
SELECT @@session.engine_condition_pushdown;
@@session.engine_condition_pushdown
1
SET @@global.engine_condition_pushdown = 0;
71 72
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
73 74 75 76
SELECT @@global.engine_condition_pushdown;
@@global.engine_condition_pushdown
0
SET @@global.engine_condition_pushdown = 1;
77 78
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
79 80 81 82 83 84 85
SELECT @@global.engine_condition_pushdown;
@@global.engine_condition_pushdown
1
'#--------------------FN_DYNVARS_028_04-------------------------#'
SET @@session.engine_condition_pushdown = -1;
ERROR 42000: Variable 'engine_condition_pushdown' can't be set to the value of '-1'
SET @@session.engine_condition_pushdown = 1.6;
86
ERROR 42000: Incorrect argument type to variable 'engine_condition_pushdown'
87 88 89 90 91
SET @@session.engine_condition_pushdown = "T";
ERROR 42000: Variable 'engine_condition_pushdown' can't be set to the value of 'T'
SET @@session.engine_condition_pushdown = "Y";
ERROR 42000: Variable 'engine_condition_pushdown' can't be set to the value of 'Y'
SET @@session.engine_condition_pushdown = TRÜE;
92
ERROR 42000: Variable 'engine_condition_pushdown' can't be set to the value of 'TRÜE'
93
SET @@session.engine_condition_pushdown = ÕN;
94
ERROR 42000: Variable 'engine_condition_pushdown' can't be set to the value of 'ÕN'
95
SET @@session.engine_condition_pushdown = OF;
96
ERROR 42000: Variable 'engine_condition_pushdown' can't be set to the value of 'OF'
97
SET @@session.engine_condition_pushdown = ÓFF;
98
ERROR 42000: Variable 'engine_condition_pushdown' can't be set to the value of 'ÓFF'
99 100 101 102 103 104 105 106 107
SET @@global.engine_condition_pushdown = -1;
ERROR 42000: Variable 'engine_condition_pushdown' can't be set to the value of '-1'
SET @@global.engine_condition_pushdown = 2;
ERROR 42000: Variable 'engine_condition_pushdown' can't be set to the value of '2'
SET @@global.engine_condition_pushdown = "T";
ERROR 42000: Variable 'engine_condition_pushdown' can't be set to the value of 'T'
SET @@global.engine_condition_pushdown = "Y";
ERROR 42000: Variable 'engine_condition_pushdown' can't be set to the value of 'Y'
SET @@global.engine_condition_pushdown = TRÜE;
108
ERROR 42000: Variable 'engine_condition_pushdown' can't be set to the value of 'TRÜE'
109
SET @@global.engine_condition_pushdown = ÕN;
110
ERROR 42000: Variable 'engine_condition_pushdown' can't be set to the value of 'ÕN'
111
SET @@global.engine_condition_pushdown = OF;
112
ERROR 42000: Variable 'engine_condition_pushdown' can't be set to the value of 'OF'
113
SET @@global.engine_condition_pushdown = ÓFF;
114
ERROR 42000: Variable 'engine_condition_pushdown' can't be set to the value of 'ÓFF'
115 116
'#-------------------FN_DYNVARS_028_05----------------------------#'
SET @@global.engine_condition_pushdown = 0;
117 118
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
119
SET @@session.engine_condition_pushdown = 1;
120 121
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
122 123 124 125
SELECT @@global.engine_condition_pushdown AS res_is_0;
res_is_0
0
SET @@global.engine_condition_pushdown = 0;
126 127
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
128 129 130 131
SELECT @@session.engine_condition_pushdown AS res_is_1;
res_is_1
1
'#----------------------FN_DYNVARS_028_06------------------------#'
132
SELECT IF(@@global.engine_condition_pushdown, "ON", "OFF") = VARIABLE_VALUE 
133 134
FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 
WHERE VARIABLE_NAME='engine_condition_pushdown';
135
IF(@@global.engine_condition_pushdown, "ON", "OFF") = VARIABLE_VALUE
136 137 138 139 140 141 142 143 144 145
1
SELECT @@global.engine_condition_pushdown;
@@global.engine_condition_pushdown
0
SELECT VARIABLE_VALUE 
FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 
WHERE VARIABLE_NAME='engine_condition_pushdown';
VARIABLE_VALUE
OFF
'#----------------------FN_DYNVARS_028_07------------------------#'
146
SELECT IF(@@session.engine_condition_pushdown, "ON", "OFF") = VARIABLE_VALUE 
147 148
FROM INFORMATION_SCHEMA.SESSION_VARIABLES
WHERE VARIABLE_NAME='engine_condition_pushdown';
149 150
IF(@@session.engine_condition_pushdown, "ON", "OFF") = VARIABLE_VALUE
1
151 152 153 154 155 156 157 158 159 160
SELECT @@session.engine_condition_pushdown;
@@session.engine_condition_pushdown
1
SELECT VARIABLE_VALUE 
FROM INFORMATION_SCHEMA.SESSION_VARIABLES 
WHERE VARIABLE_NAME='engine_condition_pushdown';
VARIABLE_VALUE
ON
'#---------------------FN_DYNVARS_028_08-------------------------#'
SET @@session.engine_condition_pushdown = OFF;
161 162
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
163 164 165 166
SELECT @@session.engine_condition_pushdown;
@@session.engine_condition_pushdown
0
SET @@session.engine_condition_pushdown = ON;
167 168
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
169 170 171 172
SELECT @@session.engine_condition_pushdown;
@@session.engine_condition_pushdown
1
SET @@global.engine_condition_pushdown = OFF;
173 174
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
175 176 177 178
SELECT @@global.engine_condition_pushdown;
@@global.engine_condition_pushdown
0
SET @@global.engine_condition_pushdown = ON;
179 180
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
181 182 183 184 185
SELECT @@global.engine_condition_pushdown;
@@global.engine_condition_pushdown
1
'#---------------------FN_DYNVARS_028_09----------------------#'
SET @@session.engine_condition_pushdown = TRUE;
186 187
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
188 189 190 191
SELECT @@session.engine_condition_pushdown;
@@session.engine_condition_pushdown
1
SET @@session.engine_condition_pushdown = FALSE;
192 193
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
194 195 196 197
SELECT @@session.engine_condition_pushdown;
@@session.engine_condition_pushdown
0
SET @@global.engine_condition_pushdown = TRUE;
198 199
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
200 201 202 203
SELECT @@global.engine_condition_pushdown;
@@global.engine_condition_pushdown
1
SET @@global.engine_condition_pushdown = FALSE;
204 205
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
206 207 208
SELECT @@global.engine_condition_pushdown;
@@global.engine_condition_pushdown
0
209 210 211 212 213 214
Check that @@engine_condition_pushdown influences
@@optimizer_switch and vice-versa
select @@session.engine_condition_pushdown,
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown	@@global.engine_condition_pushdown	@@session.optimizer_switch	@@global.optimizer_switch
Sergei Golubchik's avatar
Sergei Golubchik committed
215
0	0	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
216 217 218 219 220 221 222
set @@session.engine_condition_pushdown = TRUE;
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
select @@session.engine_condition_pushdown,
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown	@@global.engine_condition_pushdown	@@session.optimizer_switch	@@global.optimizer_switch
Sergei Golubchik's avatar
Sergei Golubchik committed
223
1	0	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
224 225 226 227 228 229 230
set @@session.engine_condition_pushdown = FALSE;
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
select @@session.engine_condition_pushdown,
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown	@@global.engine_condition_pushdown	@@session.optimizer_switch	@@global.optimizer_switch
Sergei Golubchik's avatar
Sergei Golubchik committed
231
0	0	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
232 233 234 235 236 237 238
set @@global.engine_condition_pushdown = TRUE;
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
select @@session.engine_condition_pushdown,
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown	@@global.engine_condition_pushdown	@@session.optimizer_switch	@@global.optimizer_switch
Sergei Golubchik's avatar
Sergei Golubchik committed
239
0	1	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
240 241 242 243 244 245 246
set @@global.engine_condition_pushdown = FALSE;
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
select @@session.engine_condition_pushdown,
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown	@@global.engine_condition_pushdown	@@session.optimizer_switch	@@global.optimizer_switch
Sergei Golubchik's avatar
Sergei Golubchik committed
247
0	0	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
248 249 250 251 252
set @@session.optimizer_switch = "engine_condition_pushdown=on";
select @@session.engine_condition_pushdown,
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown	@@global.engine_condition_pushdown	@@session.optimizer_switch	@@global.optimizer_switch
Sergei Golubchik's avatar
Sergei Golubchik committed
253
1	0	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
254 255 256 257 258
set @@session.optimizer_switch = "engine_condition_pushdown=off";
select @@session.engine_condition_pushdown,
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown	@@global.engine_condition_pushdown	@@session.optimizer_switch	@@global.optimizer_switch
Sergei Golubchik's avatar
Sergei Golubchik committed
259
0	0	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
260 261 262 263 264
set @@global.optimizer_switch = "engine_condition_pushdown=on";
select @@session.engine_condition_pushdown,
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown	@@global.engine_condition_pushdown	@@session.optimizer_switch	@@global.optimizer_switch
Sergei Golubchik's avatar
Sergei Golubchik committed
265
0	1	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
266 267 268 269 270
set @@global.optimizer_switch = "engine_condition_pushdown=off";
select @@session.engine_condition_pushdown,
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown	@@global.engine_condition_pushdown	@@session.optimizer_switch	@@global.optimizer_switch
Sergei Golubchik's avatar
Sergei Golubchik committed
271
0	0	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
272
SET @@session.engine_condition_pushdown = @session_start_value;
273 274
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
275 276
SELECT @@session.engine_condition_pushdown;
@@session.engine_condition_pushdown
Sergei Golubchik's avatar
Sergei Golubchik committed
277
0
278
SET @@global.engine_condition_pushdown = @global_start_value;
279 280
Warnings:
Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead
281 282
SELECT @@global.engine_condition_pushdown;
@@global.engine_condition_pushdown
Sergei Golubchik's avatar
Sergei Golubchik committed
283
0
284 285 286 287 288 289
set @session.optimizer_switch=@old_session_opt_switch,
@@global.optimizer_switch=@old_global_opt_switch;
select @@session.engine_condition_pushdown,
@@global.engine_condition_pushdown,
@@session.optimizer_switch, @@global.optimizer_switch;
@@session.engine_condition_pushdown	@@global.engine_condition_pushdown	@@session.optimizer_switch	@@global.optimizer_switch
Sergei Golubchik's avatar
Sergei Golubchik committed
290
0	0	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on	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=off,derived_with_keys=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on