1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
|
set
@tmp_switch_sarg_casefold=@@optimizer_switch,
optimizer_switch='sargable_casefold=on';
create table t1 (
col1 varchar(32),
col2 varchar(32),
col3 char(32),
col4 text,
key(col1),
key(col2),
key(col3),
key(col4(32))
) collate utf8mb3_general_ci;
insert into t1
select
concat('A-', seq),
concat('A-', seq),
concat('A-', seq),
concat('A-', seq)
from seq_1_to_100;
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze Warning Engine-independent statistics are not collected for column 'col4'
test.t1 analyze status Table is already up to date
# Basic examples. All should use ref(col1):
explain
select * from t1 where upper(col1)='A-3';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref col1 col1 99 const 1 Using index condition
select * from t1 where upper(col1)='A-3';
col1 col2 col3 col4
A-3 A-3 A-3 A-3
explain
select * from t1 where ucase(col1)='a-3';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref col1 col1 99 const 1 Using index condition
select * from t1 where ucase(col1)='a-3';
col1 col2 col3 col4
A-3 A-3 A-3 A-3
explain select * from t1 where 'abc'=upper(col1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref col1 col1 99 const 1 Using index condition
explain select * from t1 where 'xyz'=ucase(col1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref col1 col1 99 const 1 Using index condition
create view v1 as select * from t1;
explain select * from v1 where 'abc'=upper(col1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref col1 col1 99 const 1 Using index condition
drop view v1;
explain select * from t1 where upper(col3)='a-3';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref col3 col3 97 const 1 Using index condition
explain select * from t1 where upper(col4)='a-3';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref col4 col4 99 const 1 Using where
# must not be rewritten:
explain select * from t1 where ucase(col1 collate utf8mb3_bin)='a-3';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
# Will not do the rewrite due to collation mismatch:
explain select * from t1 where ucase(col1)=_utf8mb3'abc' COLLATE utf8mb3_bin;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
drop table t1;
create table t1 (
col1 varchar(32),
col2 varchar(32),
col3 char(32),
col4 text,
key(col1),
key(col2),
key(col3),
key(col4(32))
) collate utf8mb4_general_ci;
insert into t1
select
concat('A-', seq),
concat('A-', seq),
concat('A-', seq),
concat('A-', seq)
from seq_1_to_100;
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze Warning Engine-independent statistics are not collected for column 'col4'
test.t1 analyze status Table is already up to date
# Basic examples. All should use ref(col1):
explain
select * from t1 where upper(col1)='A-3';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref col1 col1 131 const 1 Using index condition
select * from t1 where upper(col1)='A-3';
col1 col2 col3 col4
A-3 A-3 A-3 A-3
explain
select * from t1 where ucase(col1)='a-3';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref col1 col1 131 const 1 Using index condition
select * from t1 where ucase(col1)='a-3';
col1 col2 col3 col4
A-3 A-3 A-3 A-3
explain select * from t1 where 'abc'=upper(col1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref col1 col1 131 const 1 Using index condition
explain select * from t1 where 'xyz'=ucase(col1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref col1 col1 131 const 1 Using index condition
create view v1 as select * from t1;
explain select * from v1 where 'abc'=upper(col1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref col1 col1 131 const 1 Using index condition
drop view v1;
explain select * from t1 where upper(col3)='a-3';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref col3 col3 129 const 1 Using index condition
explain select * from t1 where upper(col4)='a-3';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref col4 col4 131 const 1 Using where
# must not be rewritten:
explain select * from t1 where ucase(col1 collate utf8mb4_bin)='a-3';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
# Will not do the rewrite due to collation mismatch:
explain select * from t1 where ucase(col1)=_utf8mb4'abc' COLLATE utf8mb4_bin;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
#
# Check if optimizer_switch turns the rewrite off:
#
set
@save_os=@@optimizer_switch,
optimizer_switch='sargable_casefold=off';
explain select * from t1 where upper(col1)='A-3';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
explain select * from t1 where ucase(col1)='a-3';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
set optimizer_switch=@save_os;
# The following will not do the rewrite because the comparison
# is done as DOUBLEs. Come to think of it, it won't harm to do
# the rewrite but it is outside of the scope of this patch:
explain select * from t1 where ucase(col1)=123.456;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
select
coercibility(upper(col1))
from t1 limit 1;
coercibility(upper(col1))
2
select coercibility(_utf8mb3'abc' COLLATE utf8mb3_bin);
coercibility(_utf8mb3'abc' COLLATE utf8mb3_bin)
0
# This is transformed too even if it doesn't create any new
# [potential] access paths:
explain format=json select * from t1 where upper(col1)=upper(col2);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": 0.0256761,
"nested_loop": [
{
"table": {
"table_name": "t1",
"access_type": "ALL",
"loops": 1,
"rows": 100,
"cost": 0.0256761,
"filtered": 100,
"attached_condition": "t1.col2 = t1.col1"
}
}
]
}
}
#
# Check if ref access works
#
create table t2 (
a varchar(32),
non_key varchar(32),
key(a)
) collate utf8mb4_general_ci;
insert into t2
select
concat('A-', seq),
concat('A-', seq)
from seq_1_to_10;
# Must use ref access for t1:
explain select * from t1, t2 where upper(t1.col1)= t2.non_key;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t1 ref col1 col1 131 test.t2.non_key 1
create table t3 (
a varchar(32),
b varchar(32),
key(a),
key(b)
) collate utf8mb3_general_ci;
insert into t3 values ('abc','ABC'), ('xyz','XYZ');
explain extended
select a from t3 ignore index(a) where a=b and upper(b)='ABC';
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t3 ref b b 99 const 1 100.00 Using index condition; Using where
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` IGNORE INDEX (`a`) where `test`.`t3`.`a` = `test`.`t3`.`b` and `test`.`t3`.`b` = 'ABC'
#
# Check that rewrite isn't applied for non-applicable collations
#
create table t4 (
col1 varchar(32) collate utf8mb3_bin,
col2 varchar(32) collate utf8mb3_czech_ci,
col3 varchar(32) collate latin1_bin,
key(col1),
key(col2),
key(col3)
);
insert into t4
select
concat('A-', seq),
concat('A-', seq),
concat('A-', seq)
from seq_1_to_100;
analyze table t4 persistent for all;
Table Op Msg_type Msg_text
test.t4 analyze status Engine-independent statistics collected
test.t4 analyze status Table is already up to date
# None should use ref access:
explain select * from t4 where upper(col1)='A-3';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ALL NULL NULL NULL NULL 100 Using where
explain select * from t4 where upper(col2)='a-3';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ALL NULL NULL NULL NULL 100 Using where
explain select * from t4 where upper(col3)='a-3';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ALL NULL NULL NULL NULL 100 Using where
#
# Check that rewrite works for UPPER(col) IN (const-list)
#
set
@tmp_ot= @@optimizer_trace,
optimizer_trace=1;
# must use range:
explain
select * from t1 where upper(col1) IN ('A-3','A-4','a-5');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range col1 col1 131 NULL 3 Using index condition
select * from t1 where upper(col1) IN ('A-3','A-4','a-5');
col1 col2 col3 col4
A-3 A-3 A-3 A-3
A-4 A-4 A-4 A-4
A-5 A-5 A-5 A-5
# Will not use the rewrite:
explain
select * from t1 where upper(col1) IN ('A-3','A-4',col2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
#
# MDEV-31946: Optimizer handle UCASE(varchar_col)=... does not work for UPDATE/DELETE
#
explain delete from t1 where upper(col1)='A';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range col1 col1 131 NULL 1 Using where
explain delete from t1 where upper(col1) IN ('A','B');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range col1 col1 131 NULL 2 Using where
explain update t1 set col2='ABC' where upper(col1)='A';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range col1 col1 131 NULL 1 Using where
explain update t1 set col2='ABC' where upper(col1) IN ('A','B');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range col1 col1 131 NULL 2 Using where
drop table t1,t2,t3,t4;
set optimizer_switch=@tmp_switch_sarg_casefold;
|
