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
|
#
# MDEV-31496 Make optimizer handle UCASE(varchar_col)=...
#
--source include/have_sequence.inc
set
@tmp_switch_sarg_casefold=@@optimizer_switch,
optimizer_switch='sargable_casefold=on';
let $collation=utf8mb3_general_ci;
source include/sargable_casefold.inc;
--echo # must not be rewritten:
explain select * from t1 where ucase(col1 collate utf8mb3_bin)='a-3';
--echo # Will not do the rewrite due to collation mismatch:
explain select * from t1 where ucase(col1)=_utf8mb3'abc' COLLATE utf8mb3_bin;
drop table t1;
let $collation=utf8mb4_general_ci;
source include/sargable_casefold.inc;
--echo # must not be rewritten:
explain select * from t1 where ucase(col1 collate utf8mb4_bin)='a-3';
--echo # Will not do the rewrite due to collation mismatch:
explain select * from t1 where ucase(col1)=_utf8mb4'abc' COLLATE utf8mb4_bin;
--echo #
--echo # Check if optimizer_switch turns the rewrite off:
--echo #
set
@save_os=@@optimizer_switch,
optimizer_switch='sargable_casefold=off';
explain select * from t1 where upper(col1)='A-3';
explain select * from t1 where ucase(col1)='a-3';
set optimizer_switch=@save_os;
--echo # The following will not do the rewrite because the comparison
--echo # is done as DOUBLEs. Come to think of it, it won't harm to do
--echo # the rewrite but it is outside of the scope of this patch:
explain select * from t1 where ucase(col1)=123.456;
select
coercibility(upper(col1))
from t1 limit 1;
select coercibility(_utf8mb3'abc' COLLATE utf8mb3_bin);
--echo # This is transformed too even if it doesn't create any new
--echo # [potential] access paths:
explain format=json select * from t1 where upper(col1)=upper(col2);
--echo #
--echo # Check if ref access works
--echo #
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;
--echo # Must use ref access for t1:
explain select * from t1, t2 where upper(t1.col1)= t2.non_key;
# Check the interplay with equality propagation
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';
--echo #
--echo # Check that rewrite isn't applied for non-applicable collations
--echo #
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;
--echo # None should use ref access:
explain select * from t4 where upper(col1)='A-3';
explain select * from t4 where upper(col2)='a-3';
explain select * from t4 where upper(col3)='a-3';
--echo #
--echo # Check that rewrite works for UPPER(col) IN (const-list)
--echo #
set
@tmp_ot= @@optimizer_trace,
optimizer_trace=1;
--echo # must use range:
explain
select * from t1 where upper(col1) IN ('A-3','A-4','a-5');
select * from t1 where upper(col1) IN ('A-3','A-4','a-5');
--echo # Will not use the rewrite:
explain
select * from t1 where upper(col1) IN ('A-3','A-4',col2);
--echo #
--echo # MDEV-31946: Optimizer handle UCASE(varchar_col)=... does not work for UPDATE/DELETE
--echo #
explain delete from t1 where upper(col1)='A';
explain delete from t1 where upper(col1) IN ('A','B');
explain update t1 set col2='ABC' where upper(col1)='A';
explain update t1 set col2='ABC' where upper(col1) IN ('A','B');
drop table t1,t2,t3,t4;
set optimizer_switch=@tmp_switch_sarg_casefold;
|
