Debian Codegraph @

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

raw
1 SET @@session.default_storage_engine = 'MyISAM';
2 create table t1 (a int,
3 b int as (-a),
4 c int as (-a) persistent,
5 index (c));
6 insert into t1 (a) values (2), (1), (1), (3), (NULL);
7 create table t2 like t1;
8 insert into t2 (a) values (1);
9 create table t3 (a int primary key,
10 b int as (-a),
11 c int as (-a) persistent unique);
12 insert into t3 (a) values (2),(1),(3),(5),(4),(7);
13 # select_type=SIMPLE, type=system
14 select * from t2;
15 a b c
16 1 -1 -1
17 explain select * from t2;
18 id select_type table type possible_keys key key_len ref rows Extra
19 1 SIMPLE t2 system NULL NULL NULL NULL 1
20 select * from t2 where c=-1;
21 a b c
22 1 -1 -1
23 explain select * from t2 where c=-1;
24 id select_type table type possible_keys key key_len ref rows Extra
25 1 SIMPLE t2 system c NULL NULL NULL 1
26 # select_type=SIMPLE, type=ALL
27 select * from t1 where b=-1;
28 a b c
29 1 -1 -1
30 1 -1 -1
31 explain select * from t1 where b=-1;
32 id select_type table type possible_keys key key_len ref rows Extra
33 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
34 # select_type=SIMPLE, type=const
35 select * from t3 where a=1;
36 a b c
37 1 -1 -1
38 explain select * from t3 where a=1;
39 id select_type table type possible_keys key key_len ref rows Extra
40 1 SIMPLE t3 const PRIMARY PRIMARY 4 const 1
41 # select_type=SIMPLE, type=range
42 select * from t3 where c>=-1;
43 a b c
44 1 -1 -1
45 explain select * from t3 where c>=-1;
46 id select_type table type possible_keys key key_len ref rows Extra
47 1 SIMPLE t3 range c c 5 NULL 1 Using index condition
48 # select_type=SIMPLE, type=ref
49 select * from t1,t3 where t1.c=t3.c and t3.c=-1;
50 a b c a b c
51 1 -1 -1 1 -1 -1
52 1 -1 -1 1 -1 -1
53 explain select * from t1,t3 where t1.c=t3.c and t3.c=-1;
54 id select_type table type possible_keys key key_len ref rows Extra
55 1 SIMPLE t3 const c c 5 const 1
56 1 SIMPLE t1 ref c c 5 const 2
57 # select_type=PRIMARY, type=index,ALL
58 select * from t1 where b in (select c from t3);
59 a b c
60 2 -2 -2
61 1 -1 -1
62 1 -1 -1
63 3 -3 -3
64 explain select * from t1 where b in (select c from t3);
65 id select_type table type possible_keys key key_len ref rows Extra
66 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where
67 1 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index
68 # select_type=PRIMARY, type=range,ref
69 select * from t1 where c in (select c from t3 where c between -2 and -1);
70 a b c
71 2 -2 -2
72 1 -1 -1
73 1 -1 -1
74 explain select * from t1 where c in (select c from t3 where c between -2 and -1);
75 id select_type table type possible_keys key key_len ref rows Extra
76 1 PRIMARY t3 range c c 5 NULL 2 Using where; Using index
77 1 PRIMARY t1 ref c c 5 test.t3.c 1
78 # select_type=UNION, type=system
79 # select_type=UNION RESULT, type=<union1,2>
80 select * from t1 union select * from t2;
81 a b c
82 2 -2 -2
83 1 -1 -1
84 3 -3 -3
85 NULL NULL NULL
86 explain select * from t1 union select * from t2;
87 id select_type table type possible_keys key key_len ref rows Extra
88 1 PRIMARY t1 ALL NULL NULL NULL NULL 5
89 2 UNION t2 system NULL NULL NULL NULL 1
90 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
91 # select_type=DERIVED, type=system
92 set @tmp_optimizer_switch=@@optimizer_switch;
93 set optimizer_switch='derived_merge=off,derived_with_keys=off';
94 select * from (select a,b,c from t1) as t11;
95 a b c
96 2 -2 -2
97 1 -1 -1
98 1 -1 -1
99 3 -3 -3
100 NULL NULL NULL
101 explain select * from (select a,b,c from t1) as t11;
102 id select_type table type possible_keys key key_len ref rows Extra
103 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5
104 2 DERIVED t1 ALL NULL NULL NULL NULL 5
105 set optimizer_switch=@tmp_optimizer_switch;
106 ###
107 ### Using aggregate functions with/without DISTINCT
108 ###
109 # SELECT COUNT(*) FROM tbl_name
110 select count(*) from t1;
111 count(*)
112 5
113 explain select count(*) from t1;
114 id select_type table type possible_keys key key_len ref rows Extra
115 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
116 # SELECT COUNT(DISTINCT <non-vcol>) FROM tbl_name
117 select count(distinct a) from t1;
118 count(distinct a)
119 3
120 explain select count(distinct a) from t1;
121 id select_type table type possible_keys key key_len ref rows Extra
122 1 SIMPLE t1 ALL NULL NULL NULL NULL 5
123 # SELECT COUNT(DISTINCT <non-stored vcol>) FROM tbl_name
124 select count(distinct b) from t1;
125 count(distinct b)
126 3
127 explain select count(distinct b) from t1;
128 id select_type table type possible_keys key key_len ref rows Extra
129 1 SIMPLE t1 ALL NULL NULL NULL NULL 5
130 # SELECT COUNT(DISTINCT <stored vcol>) FROM tbl_name
131 select count(distinct c) from t1;
132 count(distinct c)
133 3
134 explain select count(distinct c) from t1;
135 id select_type table type possible_keys key key_len ref rows Extra
136 1 SIMPLE t1 range NULL c 5 NULL 5 Using index for group-by
137 ###
138 ### filesort & range-based utils
139 ###
140 # SELECT * FROM tbl_name WHERE <vcol expr>
141 select * from t3 where c >= -2;
142 a b c
143 2 -2 -2
144 1 -1 -1
145 explain select * from t3 where c >= -2;
146 id select_type table type possible_keys key key_len ref rows Extra
147 1 SIMPLE t3 range c c 5 NULL 2 Using index condition
148 # SELECT * FROM tbl_name WHERE <non-vcol expr>
149 select * from t3 where a between 1 and 2;
150 a b c
151 1 -1 -1
152 2 -2 -2
153 explain select * from t3 where a between 1 and 2;
154 id select_type table type possible_keys key key_len ref rows Extra
155 1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition
156 # SELECT * FROM tbl_name WHERE <non-indexed vcol expr>
157 select * from t3 where b between -2 and -1;
158 a b c
159 2 -2 -2
160 1 -1 -1
161 explain select * from t3 where b between -2 and -1;
162 id select_type table type possible_keys key key_len ref rows Extra
163 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where
164 # SELECT * FROM tbl_name WHERE <indexed vcol expr>
165 select * from t3 where c between -2 and -1;
166 a b c
167 2 -2 -2
168 1 -1 -1
169 explain select * from t3 where c between -2 and -1;
170 id select_type table type possible_keys key key_len ref rows Extra
171 1 SIMPLE t3 range c c 5 NULL 2 Using index condition
172 # SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol>
173 select * from t3 where a between 1 and 2 order by c;
174 a b c
175 2 -2 -2
176 1 -1 -1
177 explain select * from t3 where a between 1 and 2 order by c;
178 id select_type table type possible_keys key key_len ref rows Extra
179 1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using filesort
180 # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol>
181 select * from t3 where b between -2 and -1 order by a;
182 a b c
183 1 -1 -1
184 2 -2 -2
185 explain select * from t3 where b between -2 and -1 order by a;
186 id select_type table type possible_keys key key_len ref rows Extra
187 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort
188 # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-vcol>
189 select * from t3 where c between -2 and -1 order by a;
190 a b c
191 1 -1 -1
192 2 -2 -2
193 explain select * from t3 where c between -2 and -1 order by a;
194 id select_type table type possible_keys key key_len ref rows Extra
195 1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort
196 # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-indexed vcol>
197 select * from t3 where b between -2 and -1 order by b;
198 a b c
199 2 -2 -2
200 1 -1 -1
201 explain select * from t3 where b between -2 and -1 order by b;
202 id select_type table type possible_keys key key_len ref rows Extra
203 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort
204 # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-indexed vcol>
205 select * from t3 where c between -2 and -1 order by b;
206 a b c
207 2 -2 -2
208 1 -1 -1
209 explain select * from t3 where c between -2 and -1 order by b;
210 id select_type table type possible_keys key key_len ref rows Extra
211 1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort
212 # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <indexed vcol>
213 select * from t3 where b between -2 and -1 order by c;
214 a b c
215 2 -2 -2
216 1 -1 -1
217 explain select * from t3 where b between -2 and -1 order by c;
218 id select_type table type possible_keys key key_len ref rows Extra
219 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort
220 # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <indexed vcol>
221 select * from t3 where c between -2 and -1 order by c;
222 a b c
223 2 -2 -2
224 1 -1 -1
225 explain select * from t3 where c between -2 and -1 order by c;
226 id select_type table type possible_keys key key_len ref rows Extra
227 1 SIMPLE t3 range c c 5 NULL 2 Using index condition
228 # SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol>
229 select sum(b) from t1 group by b;
230 sum(b)
231 NULL
232 -3
233 -2
234 -2
235 explain select sum(b) from t1 group by b;
236 id select_type table type possible_keys key key_len ref rows Extra
237 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort
238 # SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <indexed vcol>
239 select sum(c) from t1 group by c;
240 sum(c)
241 NULL
242 -3
243 -2
244 -2
245 explain select sum(c) from t1 group by c;
246 id select_type table type possible_keys key key_len ref rows Extra
247 1 SIMPLE t1 index NULL c 5 NULL 5 Using index
248 # SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <indexed vcol>
249 select sum(b) from t1 group by c;
250 sum(b)
251 NULL
252 -3
253 -2
254 -2
255 explain select sum(b) from t1 group by c;
256 id select_type table type possible_keys key key_len ref rows Extra
257 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort
258 # SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol>
259 select sum(c) from t1 group by b;
260 sum(c)
261 NULL
262 -3
263 -2
264 -2
265 explain select sum(c) from t1 group by b;
266 id select_type table type possible_keys key key_len ref rows Extra
267 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort
268 #
269 # Bug #806057: join with USING over a virtual column
270 #
271 CREATE TABLE t1 (b int);
272 INSERT INTO t1 VALUES (NULL),( 78), (185), (0), (154);
273 CREATE TABLE t2 (a int, b int AS (a) VIRTUAL);
274 INSERT IGNORE INTO t2 VALUES (187,187), (9,9), (187,187);
275 Warnings:
276 Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored
277 Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored
278 Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored
279 EXPLAIN EXTENDED
280 SELECT * FROM t1 JOIN t2 USING (b);
281 id select_type table type possible_keys key key_len ref rows filtered Extra
282 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00
283 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
284 Warnings:
285 Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`b` = `test`.`t2`.`b`
286 SELECT * FROM t1 JOIN t2 USING (b);
287 b a
288 EXPLAIN EXTENDED
289 SELECT * FROM t1 NATURAL JOIN t2;
290 id select_type table type possible_keys key key_len ref rows filtered Extra
291 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00
292 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
293 Warnings:
294 Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`b` = `test`.`t2`.`b`
295 SELECT * FROM t1 NATURAL JOIN t2;
296 b a
297 DROP TABLE t1,t2;
298 create table t1 (
299 pk integer auto_increment,
300 bi integer not null,
301 vi integer generated always as (bi) persistent,
302 bc varchar(1) not null,
303 vc varchar(2) generated always as (concat(bc, bc)) persistent,
304 primary key (pk),
305 key (vi, vc));
306 insert t1 (bi, bc) values (0, 'x'), (0, 'n'), (1, 'w'), (7, 's'), (0, 'a'), (4, 'd'), (1, 'w'), (1, 'j'), (1, 'm'), (4, 'k'), (7, 't'), (4, 'k'), (2, 'e'), (0, 'i'), (1, 't'), (6, 'z'), (3, 'c'), (6, 'i'), (8, 'v');
307 create table t2 (
308 pk integer auto_increment,
309 bi integer not null,
310 vi integer generated always as (bi) persistent,
311 bc varchar(257) not null,
312 vc varchar(2) generated always as (concat(bc, bc)) persistent,
313 primary key (pk),
314 key (vi, vc));
315 insert t2 (bi, bc) values (1, 'c'), (8, 'm'), (9, 'd'), (6, 'y'), (1, 't'), (6, 'd'), (2, 's'), (4, 'r'), (8, 'm'), (4, 'b'), (4, 'x'), (7, 'g'), (4, 'p'), (1, 'q'), (9, 'w'), (4, 'd'), (8, 'e'), (4, 'b'), (8, 'y');
316 explain # should be using join buffer
317 select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc));
318 id select_type table type possible_keys key key_len ref rows Extra
319 1 SIMPLE t2 index NULL vi 16 NULL 19 Using index
320 1 SIMPLE t1 ALL NULL NULL NULL NULL 19 Using where; Using join buffer (flat, BNL join)
321 1 SIMPLE t3 index NULL PRIMARY 4 NULL 19 Using where; Using index; Using join buffer (incremental, BNL join)
322 select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc));
323 vi
324 1
325 1
326 1
327 1
328 1
329 1
330 1
331 1
332 1
333 1
334 1
335 1
336 1
337 1
338 1
339 1
340 1
341 1
342 1
343 1
344 1
345 1
346 1
347 1
348 1
349 1
350 1
351 1
352 1
353 1
354 1
355 1
356 1
357 2
358 4
359 4
360 4
361 4
362 4
363 4
364 4
365 4
366 4
367 4
368 4
369 4
370 4
371 4
372 4
373 4
374 4
375 4
376 4
377 4
378 4
379 4
380 4
381 4
382 4
383 4
384 4
385 4
386 4
387 4
388 4
389 4
390 4
391 4
392 4
393 4
394 6
395 6
396 6
397 6
398 7
399 7
400 8
401 8
402 8
403 8
404 9
405 9
406 drop table t2,t1;
407 create table t1 (
408 pk integer auto_increment,
409 bi integer not null,
410 vi integer generated always as (bi) virtual,
411 bc varchar(1) not null,
412 vc varchar(2) generated always as (concat(bc, bc)),
413 primary key (pk),
414 key (vi, vc));
415 insert t1 (bi, bc) values (0, 'x'), (0, 'n'), (1, 'w'), (7, 's'), (0, 'a'), (4, 'd'), (1, 'w'), (1, 'j'), (1, 'm'), (4, 'k'), (7, 't'), (4, 'k'), (2, 'e'), (0, 'i'), (1, 't'), (6, 'z'), (3, 'c'), (6, 'i'), (8, 'v');
416 create table t2 (
417 pk integer auto_increment,
418 bi integer not null,
419 vi integer generated always as (bi) virtual,
420 bc varchar(257) not null,
421 vc varchar(2) generated always as (concat(bc, bc)),
422 primary key (pk),
423 key (vi, vc));
424 insert t2 (bi, bc) values (1, 'c'), (8, 'm'), (9, 'd'), (6, 'y'), (1, 't'), (6, 'd'), (2, 's'), (4, 'r'), (8, 'm'), (4, 'b'), (4, 'x'), (7, 'g'), (4, 'p'), (1, 'q'), (9, 'w'), (4, 'd'), (8, 'e'), (4, 'b'), (8, 'y');
425 explain # should be using join buffer
426 select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc));
427 id select_type table type possible_keys key key_len ref rows Extra
428 1 SIMPLE t2 index NULL vi 16 NULL 19 Using index
429 1 SIMPLE t1 ALL NULL NULL NULL NULL 19 Using where; Using join buffer (flat, BNL join)
430 1 SIMPLE t3 index NULL PRIMARY 4 NULL 19 Using where; Using index; Using join buffer (incremental, BNL join)
431 select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc));
432 vi
433 1
434 1
435 1
436 1
437 1
438 1
439 1
440 1
441 1
442 1
443 1
444 1
445 1
446 1
447 1
448 1
449 1
450 1
451 1
452 1
453 1
454 1
455 1
456 1
457 1
458 1
459 1
460 1
461 1
462 1
463 1
464 1
465 1
466 2
467 4
468 4
469 4
470 4
471 4
472 4
473 4
474 4
475 4
476 4
477 4
478 4
479 4
480 4
481 4
482 4
483 4
484 4
485 4
486 4
487 4
488 4
489 4
490 4
491 4
492 4
493 4
494 4
495 4
496 4
497 4
498 4
499 4
500 4
501 4
502 4
503 6
504 6
505 6
506 6
507 7
508 7
509 8
510 8
511 8
512 8
513 9
514 9
515 drop table t2,t1;