Debian Codegraph @

mysql-test/suite/vcol/r/vcol_sargable.result

raw
1 # Check various condition types
2 create table t1 (a int, vcol1 int as (a+1), index(vcol1));
3 insert into t1 (a) select seq from seq_1_to_100;
4 explain select * from t1 where a+1=2;
5 id select_type table type possible_keys key key_len ref rows Extra
6 1 SIMPLE t1 ref vcol1 vcol1 5 const 1
7 # Try renaming the table
8 explain select * from t1 as TBL where TBL.a+1=2;
9 id select_type table type possible_keys key key_len ref rows Extra
10 1 SIMPLE TBL ref vcol1 vcol1 5 const 1
11 explain select * from t1 where a+1<=2;
12 id select_type table type possible_keys key key_len ref rows Extra
13 1 SIMPLE t1 range vcol1 vcol1 5 NULL 1 Using index condition
14 explain select * from t1 where a+1<2;
15 id select_type table type possible_keys key key_len ref rows Extra
16 1 SIMPLE t1 range vcol1 vcol1 5 NULL 1 Using index condition
17 explain select * from t1 where a+1>100;
18 id select_type table type possible_keys key key_len ref rows Extra
19 1 SIMPLE t1 range vcol1 vcol1 5 NULL 6 Using index condition
20 explain select * from t1 where a+1>=100;
21 id select_type table type possible_keys key key_len ref rows Extra
22 1 SIMPLE t1 range vcol1 vcol1 5 NULL 13 Using index condition
23 explain select * from t1 where a+1 between 10 and 12;
24 id select_type table type possible_keys key key_len ref rows Extra
25 1 SIMPLE t1 range vcol1 vcol1 5 NULL 2 Using index condition
26 explain select * from t1 where (a+1) IS NULL;
27 id select_type table type possible_keys key key_len ref rows Extra
28 1 SIMPLE t1 ref vcol1 vcol1 5 const 1 Using index condition
29 explain select * from t1 force index(vcol1) where (a+1) IS NOT NULL;
30 id select_type table type possible_keys key key_len ref rows Extra
31 1 SIMPLE t1 range vcol1 vcol1 5 NULL 100 Using index condition
32 explain select * from t1 where (a+1) in (1,2,3,4);
33 id select_type table type possible_keys key key_len ref rows Extra
34 1 SIMPLE t1 range vcol1 vcol1 5 NULL 4 Using index condition
35 # Check UPDATE/DELETE:
36 explain delete from t1 where a+1=2;
37 id select_type table type possible_keys key key_len ref rows Extra
38 1 SIMPLE t1 range vcol1 vcol1 5 NULL 1 Using where
39 explain update t1 set a=a+1 where a+1=2;
40 id select_type table type possible_keys key key_len ref rows Extra
41 1 SIMPLE t1 range vcol1 vcol1 5 NULL 1 Using where; Using buffer
42 # Try merged VIEWs:
43 create view v1 as select * from t1;
44 explain select * from v1 where a+1=2;
45 id select_type table type possible_keys key key_len ref rows Extra
46 1 SIMPLE t1 ref vcol1 vcol1 5 const 1
47 create view v2 as select a as A_COL from t1;
48 explain select * from v2 where A_COL+1=2;
49 id select_type table type possible_keys key key_len ref rows Extra
50 1 SIMPLE t1 ref vcol1 vcol1 5 const 1
51 drop view v1;
52 drop view v2;
53 set names utf8mb4;
54 select @@collation_connection;
55 @@collation_connection
56 utf8mb4_uca1400_ai_ci
57 # Check VARCHAR
58 create table t2 (
59 a varchar(32),
60 vcol1 varchar(32) as (concat('hello-',a)),
61 index(vcol1)
62 );
63 insert into t2 (a) select seq from seq_1_to_100;
64 select collation('aaa'), collation(vcol1) from t2 limit 1;
65 collation('aaa') collation(vcol1)
66 utf8mb4_uca1400_ai_ci utf8mb4_uca1400_ai_ci
67 set @tmp_trace=@@optimizer_trace;
68 set optimizer_trace=1;
69 # This won't work:
70 explain select * from t2 where concat('bye-', a)='hello-5';
71 id select_type table type possible_keys key key_len ref rows Extra
72 1 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where
73 # This will work:
74 explain select * from t2 where concat('hello-', a)='hello-5';
75 id select_type table type possible_keys key key_len ref rows Extra
76 1 SIMPLE t2 ref vcol1 vcol1 131 const 1 Using index condition
77 select
78 json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
79 from
80 information_schema.optimizer_trace;
81 json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
82 [
83 {
84 "condition": "WHERE",
85 "resulting_condition": "t2.vcol1 = 'hello-5'"
86 }
87 ]
88 # Try also ON expressions
89 explain
90 select *
91 from t1 left join t2 on concat('hello-', t2.a)='hello-5'
92 where
93 t1.a+1=2;
94 id select_type table type possible_keys key key_len ref rows Extra
95 1 SIMPLE t1 ref vcol1 vcol1 5 const 1
96 1 SIMPLE t2 ref vcol1 vcol1 131 const 1 Using where
97 select
98 json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
99 from
100 information_schema.optimizer_trace;
101 json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
102 [
103 {
104 "condition": "WHERE",
105 "resulting_condition": "t1.vcol1 = 2"
106 },
107 {
108 "condition": "ON expression",
109 "resulting_condition": "t2.vcol1 = 'hello-5'"
110 }
111 ]
112 create table t3 (a int);
113 insert into t3 values (1),(2);
114 explain
115 select *
116 from
117 t3 left join
118 (t1 join t2 on concat('hello-', t2.a)='hello-5' and t1.a+1=2)
119 on t3.a<3;
120 id select_type table type possible_keys key key_len ref rows Extra
121 1 SIMPLE t3 ALL NULL NULL NULL NULL 2
122 1 SIMPLE t1 ref vcol1 vcol1 5 const 1 Using where
123 1 SIMPLE t2 ref vcol1 vcol1 131 const 1 Using where
124 select
125 json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
126 from
127 information_schema.optimizer_trace;
128 json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
129 [
130 {
131 "condition": "ON expression",
132 "resulting_condition": "t3.a < 3 and t2.vcol1 = 'hello-5' and t1.vcol1 = 2"
133 }
134 ]
135 drop table t1,t2,t3;
136 set optimizer_trace=@tmp_trace;
137 #
138 # Implicit type/charset conversions
139 #
140 create table t3 (
141 a varchar(32) collate utf8mb4_general_ci,
142 vcol1 int as (concat('100',a)),
143 vcol2 varchar(32) collate utf8mb4_unicode_ci as (concat('hello-',a)),
144 index(vcol1),
145 index(vcol2)
146 );
147 insert into t3 (a) select seq from seq_1_to_100;
148 # Type conversion
149 explain select * from t3 where concat('100', a)=10010;
150 id select_type table type possible_keys key key_len ref rows Extra
151 1 SIMPLE t3 ALL NULL NULL NULL NULL 100 Using where
152 Warnings:
153 Note 1105 Cannot substitute virtual column expression concat('100',`t3`.`a`) -> vcol1 due to type mismatch
154 # Character set change
155 explain select * from t3 where concat('hello-', a)='abcd';
156 id select_type table type possible_keys key key_len ref rows Extra
157 1 SIMPLE t3 ALL NULL NULL NULL NULL 100 Using where
158 Warnings:
159 Note 1105 Cannot substitute virtual column expression concat('hello-',`t3`.`a`) -> vcol2 due to collation mismatch
160 drop table t3;
161 # Try JSON_EXTRACT
162 create table t1 (a int, js1 blob);
163 insert into t1
164 select seq, concat('{"size":', seq, ', "color":"hue', seq ,'"}') from seq_1_to_100;
165 select * from t1 limit 3;
166 a js1
167 1 {"size":1, "color":"hue1"}
168 2 {"size":2, "color":"hue2"}
169 3 {"size":3, "color":"hue3"}
170 alter table t1 add size1 int as (cast(json_extract(js1, '$.size') as int));
171 alter table t1 add index(size1);
172 explain select * from t1 where cast(json_extract(js1,'$.size') as int)=5 ;
173 id select_type table type possible_keys key key_len ref rows Extra
174 1 SIMPLE t1 ref size1 size1 5 const 1
175 alter table t1 add
176 color varchar(100) COLLATE utf8mb4_bin
177 as (json_unquote(json_extract(js1, '$.color')));
178 alter table t1 add index(color);
179 select * from t1 limit 3;
180 a js1 size1 color
181 1 {"size":1, "color":"hue1"} 1 hue1
182 2 {"size":2, "color":"hue2"} 2 hue2
183 3 {"size":3, "color":"hue3"} 3 hue3
184 # Index is used:
185 explain select * from t1 where json_unquote(json_extract(js1, '$.color'))='hue5';
186 id select_type table type possible_keys key key_len ref rows Extra
187 1 SIMPLE t1 ref color color 403 const 1 Using index condition
188 explain select * from t1 where json_unquote(json_extract(js1, '$.color')) IS NULL;
189 id select_type table type possible_keys key key_len ref rows Extra
190 1 SIMPLE t1 ref color color 403 const 1 Using index condition
191 explain select * from t1 force index(color)
192 where json_unquote(json_extract(js1, '$.color')) IS NOT NULL;
193 id select_type table type possible_keys key key_len ref rows Extra
194 1 SIMPLE t1 range color color 403 NULL 100 Using index condition
195 alter table t1 drop column color;
196 alter table t1 add
197 color2 varchar(100)
198 as (json_unquote(json_extract(js1, '$.color')));
199 alter table t1 add index(color2);
200 # Index is not used due to collation mismatch:
201 explain select * from t1 where json_unquote(json_extract(js1, '$.color'))='hue5';
202 id select_type table type possible_keys key key_len ref rows Extra
203 1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
204 Warnings:
205 Note 1105 Cannot substitute virtual column expression json_unquote(json_extract(`t1`.`js1`,'$.color')) -> color2 due to collation mismatch
206 drop table t1;
207 #
208 # Tests with JSON_VALUE
209 #
210 create table t1 (a int, js1 json);
211 insert into t1
212 select seq, concat('{"size":', seq, ', "color":"hue', seq ,'"}') from seq_1_to_100;
213 select * from t1 limit 3;
214 a js1
215 1 {"size":1, "color":"hue1"}
216 2 {"size":2, "color":"hue2"}
217 3 {"size":3, "color":"hue3"}
218 alter table t1
219 add size1 int as (cast(json_value(js1, '$.size') as int)),
220 add index(size1);
221 explain
222 select * from t1 where size1=10;
223 id select_type table type possible_keys key key_len ref rows Extra
224 1 SIMPLE t1 ref size1 size1 5 const 1
225 # The "JSON" datatype uses binary collation.
226 # JSON_VALUE will produce output in binary collation, too:
227 select collation(json_value(js1, '$.color')) from t1 limit 1;
228 collation(json_value(js1, '$.color'))
229 utf8mb4_bin
230 # If one is fine with _bin comparisons, they can use index access:
231 alter table t1
232 add color1 varchar(100) collate utf8mb4_bin as (json_value(js1, '$.color')),
233 add index(color1);
234 explain select * from t1 where json_value(js1, '$.color')='hue10';
235 id select_type table type possible_keys key key_len ref rows Extra
236 1 SIMPLE t1 ref color1 color1 403 const 1 Using index condition
237 alter table t1 drop column color1;
238 # Using different collation in column substitution prevents
239 # the optimization from working:
240 alter table t1
241 add color2 varchar(100) collate utf8mb4_unicode_ci as (json_value(js1, '$.color')),
242 add index(color2);
243 explain select * from t1 where json_value(js1, '$.color')='hue10';
244 id select_type table type possible_keys key key_len ref rows Extra
245 1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
246 Warnings:
247 Note 1105 Cannot substitute virtual column expression json_value(`t1`.`js1`,'$.color') -> color2 due to collation mismatch
248 alter table t1 drop column color2;
249 # Explicitly specifying the collation helps:
250 alter table t1
251 add color3 varchar(100) collate utf8mb4_unicode_ci as
252 (json_value(js1, '$.color') collate utf8mb4_unicode_ci),
253 add index(color3);
254 explain select * from t1
255 where
256 json_value(js1, '$.color') collate utf8mb4_unicode_ci='hue10';
257 id select_type table type possible_keys key key_len ref rows Extra
258 1 SIMPLE t1 ref color3 color3 403 const 1 Using index condition
259 #
260 # Alternatively, one can store JSON in a column with the same
261 # collation as default and then casts are not needed:
262 #
263 create table t2(
264 js1 longtext COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL CHECK (json_valid(`js1`))
265 );
266 insert into t2 select js1 from t1;
267 alter table t2
268 add color3 varchar(100) as (json_value(js1, '$.color')),
269 add index(color3);
270 explain select * from t2
271 where
272 json_value(js1, '$.color')='hue10';
273 id select_type table type possible_keys key key_len ref rows Extra
274 1 SIMPLE t2 ref color3 color3 403 const 1 Using index condition
275 drop table t1,t2;
276 #
277 # Test interplay with sargable_casefold optimization:
278 #
279 create table t1 (
280 a varchar(100) collate utf8mb3_general_ci,
281 vcol varchar(100) collate utf8mb3_general_ci as (UPPER(a)),
282 index(a),
283 index(vcol)
284 );
285 insert into t1 (a) select seq from seq_1_to_100;
286 # Note that possible_keys doesn't include 'vcol'.
287 # Sargable_casefold is applied before vcol substitution:
288 explain select * from t1 where UPPER(a)='abc';
289 id select_type table type possible_keys key key_len ref rows Extra
290 1 SIMPLE t1 ref a a 303 const 1 Using index condition
291 explain select * from t1 ignore index(vcol) where UPPER(a)='abc';
292 id select_type table type possible_keys key key_len ref rows Extra
293 1 SIMPLE t1 ref a a 303 const 1 Using index condition
294 explain select * from t1 ignore index(a) where UPPER(a)='abc';
295 id select_type table type possible_keys key key_len ref rows Extra
296 1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
297 set optimizer_switch='sargable_casefold=off';
298 explain select * from t1 ignore index(a) where UPPER(a)='abc';
299 id select_type table type possible_keys key key_len ref rows Extra
300 1 SIMPLE t1 ref vcol vcol 303 const 1 Using index condition
301 set optimizer_switch=default;
302 drop table t1;
303 #
304 # Test interplay with Sargable YEAR/DATE optimization:
305 #
306 create table t1 (
307 a date,
308 vcol int as (year(a)),
309 index(a),
310 index(vcol)
311 );
312 insert into t1 (a) select date_add('2024-01-01', interval (seq*365) day) from seq_1_to_100;
313 # Note that possible_keys doesn't include 'vcol'.
314 # Sargable Year is applied before vcol substitution:
315 explain format=json select * from t1 where year(a)=2025;
316 EXPLAIN
317 {
318 "query_block": {
319 "select_id": 1,
320 "cost": 0.002574553,
321 "nested_loop": [
322 {
323 "table": {
324 "table_name": "t1",
325 "access_type": "range",
326 "possible_keys": ["a"],
327 "key": "a",
328 "key_length": "4",
329 "used_key_parts": ["a"],
330 "loops": 1,
331 "rows": 1,
332 "cost": 0.002574553,
333 "filtered": 100,
334 "index_condition": "t1.a between '2025-01-01' and '2025-12-31'"
335 }
336 }
337 ]
338 }
339 }
340 # Check that vcol would work if Sargable Year didn't disable it:
341 alter table t1
342 add vcol2 int as (year(a)+1),
343 add index(vcol2);
344 explain format=json select * from t1 where year(a)+1=2025;
345 EXPLAIN
346 {
347 "query_block": {
348 "select_id": 1,
349 "cost": 0.002024411,
350 "nested_loop": [
351 {
352 "table": {
353 "table_name": "t1",
354 "access_type": "ref",
355 "possible_keys": ["vcol2"],
356 "key": "vcol2",
357 "key_length": "5",
358 "used_key_parts": ["vcol2"],
359 "ref": ["const"],
360 "loops": 1,
361 "rows": 1,
362 "cost": 0.002024411,
363 "filtered": 100
364 }
365 }
366 ]
367 }
368 }
369 drop table t1;
370 #
371 # MDEV-35833: Assertion `marked_for_read()' failed for query with vcols
372 #
373 CREATE TABLE t1 (
374 id int,
375 v2 int AS (id),
376 v3 int AS (id+0),
377 a1 int AS (v2 + v3),
378 KEY a1 (a1)
379 );
380 insert t1(id) values (1),(2);
381 explain format=json DELETE FROM t1 WHERE v2+v3 > 'a';
382 EXPLAIN
383 {
384 "query_block": {
385 "select_id": 1,
386 "table": {
387 "delete": 1,
388 "table_name": "t1",
389 "access_type": "range",
390 "possible_keys": ["a1"],
391 "key": "a1",
392 "key_length": "5",
393 "used_key_parts": ["a1"],
394 "rows": 2,
395 "attached_condition": "t1.a1 > 'a'"
396 }
397 }
398 }
399 Warnings:
400 Warning 1292 Truncated incorrect DECIMAL value: 'a'
401 drop table t1;