Debian Codegraph @

mysql-test/suite/vcol/t/vcol_sargable.test

raw
1 --source include/have_sequence.inc
2 --source include/not_embedded.inc
3
4 --echo # Check various condition types
5 create table t1 (a int, vcol1 int as (a+1), index(vcol1));
6 insert into t1 (a) select seq from seq_1_to_100;
7 explain select * from t1 where a+1=2;
8 --echo # Try renaming the table
9 explain select * from t1 as TBL where TBL.a+1=2;
10 explain select * from t1 where a+1<=2;
11 explain select * from t1 where a+1<2;
12 explain select * from t1 where a+1>100;
13 explain select * from t1 where a+1>=100;
14 explain select * from t1 where a+1 between 10 and 12;
15 explain select * from t1 where (a+1) IS NULL;
16 explain select * from t1 force index(vcol1) where (a+1) IS NOT NULL;
17 explain select * from t1 where (a+1) in (1,2,3,4);
18
19 --echo # Check UPDATE/DELETE:
20 explain delete from t1 where a+1=2;
21 explain update t1 set a=a+1 where a+1=2;
22
23 --echo # Try merged VIEWs:
24 create view v1 as select * from t1;
25 explain select * from v1 where a+1=2;
26 create view v2 as select a as A_COL from t1;
27 explain select * from v2 where A_COL+1=2;
28 drop view v1;
29 drop view v2;
30
31 set names utf8mb4;
32 select @@collation_connection;
33 --echo # Check VARCHAR
34 create table t2 (
35 a varchar(32),
36 vcol1 varchar(32) as (concat('hello-',a)),
37 index(vcol1)
38 );
39 insert into t2 (a) select seq from seq_1_to_100;
40 select collation('aaa'), collation(vcol1) from t2 limit 1;
41 # Also check optimizer trace coverage
42 set @tmp_trace=@@optimizer_trace;
43 set optimizer_trace=1;
44 --echo # This won't work:
45 explain select * from t2 where concat('bye-', a)='hello-5';
46 --echo # This will work:
47 explain select * from t2 where concat('hello-', a)='hello-5';
48 --disable_view_protocol
49 select
50 json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
51 from
52 information_schema.optimizer_trace;
53 --enable_view_protocol
54
55 --echo # Try also ON expressions
56 explain
57 select *
58 from t1 left join t2 on concat('hello-', t2.a)='hello-5'
59 where
60 t1.a+1=2;
61 --disable_view_protocol
62 select
63 json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
64 from
65 information_schema.optimizer_trace;
66 --enable_view_protocol
67
68 create table t3 (a int);
69 insert into t3 values (1),(2);
70 explain
71 select *
72 from
73 t3 left join
74 (t1 join t2 on concat('hello-', t2.a)='hello-5' and t1.a+1=2)
75 on t3.a<3;
76 --disable_view_protocol
77 select
78 json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
79 from
80 information_schema.optimizer_trace;
81 --enable_view_protocol
82
83 drop table t1,t2,t3;
84
85
86 set optimizer_trace=@tmp_trace;
87
88 --echo #
89 --echo # Implicit type/charset conversions
90 --echo #
91 create table t3 (
92 a varchar(32) collate utf8mb4_general_ci,
93 vcol1 int as (concat('100',a)),
94 vcol2 varchar(32) collate utf8mb4_unicode_ci as (concat('hello-',a)),
95 index(vcol1),
96 index(vcol2)
97 );
98 insert into t3 (a) select seq from seq_1_to_100;
99
100 --echo # Type conversion
101 explain select * from t3 where concat('100', a)=10010;
102
103 --echo # Character set change
104 explain select * from t3 where concat('hello-', a)='abcd';
105 drop table t3;
106
107 --echo # Try JSON_EXTRACT
108 create table t1 (a int, js1 blob);
109 insert into t1
110 select seq, concat('{"size":', seq, ', "color":"hue', seq ,'"}') from seq_1_to_100;
111 select * from t1 limit 3;
112
113 alter table t1 add size1 int as (cast(json_extract(js1, '$.size') as int));
114 alter table t1 add index(size1);
115 explain select * from t1 where cast(json_extract(js1,'$.size') as int)=5 ;
116
117 #
118 # JSON_UNQUOTE() returns utf8mb4_bin
119 #
120 # Without COLLATE clause, the default is utf8mb4_uca1400_ai_ci.
121 #
122 alter table t1 add
123 color varchar(100) COLLATE utf8mb4_bin
124 as (json_unquote(json_extract(js1, '$.color')));
125 alter table t1 add index(color);
126
127 select * from t1 limit 3;
128
129 --echo # Index is used:
130 explain select * from t1 where json_unquote(json_extract(js1, '$.color'))='hue5';
131 explain select * from t1 where json_unquote(json_extract(js1, '$.color')) IS NULL;
132 explain select * from t1 force index(color)
133 where json_unquote(json_extract(js1, '$.color')) IS NOT NULL;
134
135 alter table t1 drop column color;
136 alter table t1 add
137 color2 varchar(100)
138 as (json_unquote(json_extract(js1, '$.color')));
139 alter table t1 add index(color2);
140 --echo # Index is not used due to collation mismatch:
141 explain select * from t1 where json_unquote(json_extract(js1, '$.color'))='hue5';
142
143 drop table t1;
144
145 --echo #
146 --echo # Tests with JSON_VALUE
147 --echo #
148 create table t1 (a int, js1 json);
149 insert into t1
150 select seq, concat('{"size":', seq, ', "color":"hue', seq ,'"}') from seq_1_to_100;
151 select * from t1 limit 3;
152
153 alter table t1
154 add size1 int as (cast(json_value(js1, '$.size') as int)),
155 add index(size1);
156
157 explain
158 select * from t1 where size1=10;
159
160 --echo # The "JSON" datatype uses binary collation.
161 --echo # JSON_VALUE will produce output in binary collation, too:
162 select collation(json_value(js1, '$.color')) from t1 limit 1;
163
164 --echo # If one is fine with _bin comparisons, they can use index access:
165 alter table t1
166 add color1 varchar(100) collate utf8mb4_bin as (json_value(js1, '$.color')),
167 add index(color1);
168 explain select * from t1 where json_value(js1, '$.color')='hue10';
169 alter table t1 drop column color1;
170
171 --echo # Using different collation in column substitution prevents
172 --echo # the optimization from working:
173 alter table t1
174 add color2 varchar(100) collate utf8mb4_unicode_ci as (json_value(js1, '$.color')),
175 add index(color2);
176 explain select * from t1 where json_value(js1, '$.color')='hue10';
177 alter table t1 drop column color2;
178
179
180 --echo # Explicitly specifying the collation helps:
181 alter table t1
182 add color3 varchar(100) collate utf8mb4_unicode_ci as
183 (json_value(js1, '$.color') collate utf8mb4_unicode_ci),
184 add index(color3);
185 explain select * from t1
186 where
187 json_value(js1, '$.color') collate utf8mb4_unicode_ci='hue10';
188
189 --echo #
190 --echo # Alternatively, one can store JSON in a column with the same
191 --echo # collation as default and then casts are not needed:
192 --echo #
193 create table t2(
194 js1 longtext COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL CHECK (json_valid(`js1`))
195 );
196 insert into t2 select js1 from t1;
197
198 alter table t2
199 add color3 varchar(100) as (json_value(js1, '$.color')),
200 add index(color3);
201 explain select * from t2
202 where
203 json_value(js1, '$.color')='hue10';
204
205 drop table t1,t2;
206
207 --echo #
208 --echo # Test interplay with sargable_casefold optimization:
209 --echo #
210 create table t1 (
211 a varchar(100) collate utf8mb3_general_ci,
212 vcol varchar(100) collate utf8mb3_general_ci as (UPPER(a)),
213 index(a),
214 index(vcol)
215 );
216 insert into t1 (a) select seq from seq_1_to_100;
217 --echo # Note that possible_keys doesn't include 'vcol'.
218 --echo # Sargable_casefold is applied before vcol substitution:
219 explain select * from t1 where UPPER(a)='abc';
220 explain select * from t1 ignore index(vcol) where UPPER(a)='abc';
221 explain select * from t1 ignore index(a) where UPPER(a)='abc';
222 set optimizer_switch='sargable_casefold=off';
223 explain select * from t1 ignore index(a) where UPPER(a)='abc';
224 set optimizer_switch=default;
225 drop table t1;
226
227 --echo #
228 --echo # Test interplay with Sargable YEAR/DATE optimization:
229 --echo #
230 create table t1 (
231 a date,
232 vcol int as (year(a)),
233 index(a),
234 index(vcol)
235 );
236 insert into t1 (a) select date_add('2024-01-01', interval (seq*365) day) from seq_1_to_100;
237 --echo # Note that possible_keys doesn't include 'vcol'.
238 --echo # Sargable Year is applied before vcol substitution:
239 explain format=json select * from t1 where year(a)=2025;
240
241 --echo # Check that vcol would work if Sargable Year didn't disable it:
242 alter table t1
243 add vcol2 int as (year(a)+1),
244 add index(vcol2);
245
246 explain format=json select * from t1 where year(a)+1=2025;
247
248 drop table t1;
249
250 --echo #
251 --echo # MDEV-35833: Assertion `marked_for_read()' failed for query with vcols
252 --echo #
253 CREATE TABLE t1 (
254 id int,
255 v2 int AS (id),
256 v3 int AS (id+0),
257 a1 int AS (v2 + v3),
258 KEY a1 (a1)
259 );
260 insert t1(id) values (1),(2);
261
262 explain format=json DELETE FROM t1 WHERE v2+v3 > 'a';
263
264 drop table t1;
265