1SET @@session.default_storage_engine = 'MyISAM'; 2create table t1 (a int, 3b int as (-a), 4c int as (-a) persistent, 5index (c)); 6insert into t1 (a) values (2), (1), (1), (3), (NULL); 7create table t2 like t1; 8insert into t2 (a) values (1); 9create table t3 (a int primary key, 10b int as (-a), 11c int as (-a) persistent unique); 12insert into t3 (a) values (2),(1),(3),(5),(4),(7); 13# select_type=SIMPLE, type=system 14select * from t2; 15a b c 161 -1 -1 17explain select * from t2; 18id select_type table type possible_keys key key_len ref rows Extra 191 SIMPLE t2 system NULL NULL NULL NULL 1 20select * from t2 where c=-1; 21a b c 221 -1 -1 23explain select * from t2 where c=-1; 24id select_type table type possible_keys key key_len ref rows Extra 251 SIMPLE t2 system c NULL NULL NULL 1 26# select_type=SIMPLE, type=ALL 27select * from t1 where b=-1; 28a b c 291 -1 -1 301 -1 -1 31explain select * from t1 where b=-1; 32id select_type table type possible_keys key key_len ref rows Extra 331 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where 34# select_type=SIMPLE, type=const 35select * from t3 where a=1; 36a b c 371 -1 -1 38explain select * from t3 where a=1; 39id select_type table type possible_keys key key_len ref rows Extra 401 SIMPLE t3 const PRIMARY PRIMARY 4 const 1 41# select_type=SIMPLE, type=range 42select * from t3 where c>=-1; 43a b c 441 -1 -1 45explain select * from t3 where c>=-1; 46id select_type table type possible_keys key key_len ref rows Extra 471 SIMPLE t3 range c c 5 NULL 1 Using index condition 48# select_type=SIMPLE, type=ref 49select * from t1,t3 where t1.c=t3.c and t3.c=-1; 50a b c a b c 511 -1 -1 1 -1 -1 521 -1 -1 1 -1 -1 53explain select * from t1,t3 where t1.c=t3.c and t3.c=-1; 54id select_type table type possible_keys key key_len ref rows Extra 551 SIMPLE t3 const c c 5 const 1 561 SIMPLE t1 ref c c 5 const 2 57# select_type=PRIMARY, type=index,ALL 58select * from t1 where b in (select c from t3); 59a b c 602 -2 -2 611 -1 -1 621 -1 -1 633 -3 -3 64explain select * from t1 where b in (select c from t3); 65id select_type table type possible_keys key key_len ref rows Extra 661 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where 671 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index 68# select_type=PRIMARY, type=range,ref 69select * from t1 where c in (select c from t3 where c between -2 and -1); 70a b c 712 -2 -2 721 -1 -1 731 -1 -1 74explain select * from t1 where c in (select c from t3 where c between -2 and -1); 75id select_type table type possible_keys key key_len ref rows Extra 761 PRIMARY t3 range c c 5 NULL 2 Using where; Using index 771 PRIMARY t1 ref c c 5 test.t3.c 2 78# select_type=UNION, type=system 79# select_type=UNION RESULT, type=<union1,2> 80select * from t1 union select * from t2; 81a b c 822 -2 -2 831 -1 -1 843 -3 -3 85NULL NULL NULL 86explain select * from t1 union select * from t2; 87id select_type table type possible_keys key key_len ref rows Extra 881 PRIMARY t1 ALL NULL NULL NULL NULL 5 892 UNION t2 system NULL NULL NULL NULL 1 90NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 91# select_type=DERIVED, type=system 92set @tmp_optimizer_switch=@@optimizer_switch; 93set optimizer_switch='derived_merge=off,derived_with_keys=off'; 94select * from (select a,b,c from t1) as t11; 95a b c 962 -2 -2 971 -1 -1 981 -1 -1 993 -3 -3 100NULL NULL NULL 101explain select * from (select a,b,c from t1) as t11; 102id select_type table type possible_keys key key_len ref rows Extra 1031 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 1042 DERIVED t1 ALL NULL NULL NULL NULL 5 105set optimizer_switch=@tmp_optimizer_switch; 106### 107### Using aggregate functions with/without DISTINCT 108### 109# SELECT COUNT(*) FROM tbl_name 110select count(*) from t1; 111count(*) 1125 113explain select count(*) from t1; 114id select_type table type possible_keys key key_len ref rows Extra 1151 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 116# SELECT COUNT(DISTINCT <non-vcol>) FROM tbl_name 117select count(distinct a) from t1; 118count(distinct a) 1193 120explain select count(distinct a) from t1; 121id select_type table type possible_keys key key_len ref rows Extra 1221 SIMPLE t1 ALL NULL NULL NULL NULL 5 123# SELECT COUNT(DISTINCT <non-stored vcol>) FROM tbl_name 124select count(distinct b) from t1; 125count(distinct b) 1263 127explain select count(distinct b) from t1; 128id select_type table type possible_keys key key_len ref rows Extra 1291 SIMPLE t1 ALL NULL NULL NULL NULL 5 130# SELECT COUNT(DISTINCT <stored vcol>) FROM tbl_name 131select count(distinct c) from t1; 132count(distinct c) 1333 134explain select count(distinct c) from t1; 135id select_type table type possible_keys key key_len ref rows Extra 1361 SIMPLE t1 range NULL c 5 NULL 6 Using index for group-by 137### 138### filesort & range-based utils 139### 140# SELECT * FROM tbl_name WHERE <vcol expr> 141select * from t3 where c >= -2; 142a b c 1432 -2 -2 1441 -1 -1 145explain select * from t3 where c >= -2; 146id select_type table type possible_keys key key_len ref rows Extra 1471 SIMPLE t3 range c c 5 NULL 2 Using index condition 148# SELECT * FROM tbl_name WHERE <non-vcol expr> 149select * from t3 where a between 1 and 2; 150a b c 1511 -1 -1 1522 -2 -2 153explain select * from t3 where a between 1 and 2; 154id select_type table type possible_keys key key_len ref rows Extra 1551 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition 156# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> 157select * from t3 where b between -2 and -1; 158a b c 1592 -2 -2 1601 -1 -1 161explain select * from t3 where b between -2 and -1; 162id select_type table type possible_keys key key_len ref rows Extra 1631 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where 164# SELECT * FROM tbl_name WHERE <indexed vcol expr> 165select * from t3 where c between -2 and -1; 166a b c 1672 -2 -2 1681 -1 -1 169explain select * from t3 where c between -2 and -1; 170id select_type table type possible_keys key key_len ref rows Extra 1711 SIMPLE t3 range c c 5 NULL 2 Using index condition 172# SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol> 173select * from t3 where a between 1 and 2 order by c; 174a b c 1752 -2 -2 1761 -1 -1 177explain select * from t3 where a between 1 and 2 order by c; 178id select_type table type possible_keys key key_len ref rows Extra 1791 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> 181select * from t3 where b between -2 and -1 order by a; 182a b c 1831 -1 -1 1842 -2 -2 185explain select * from t3 where b between -2 and -1 order by a; 186id select_type table type possible_keys key key_len ref rows Extra 1871 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> 189select * from t3 where c between -2 and -1 order by a; 190a b c 1911 -1 -1 1922 -2 -2 193explain select * from t3 where c between -2 and -1 order by a; 194id select_type table type possible_keys key key_len ref rows Extra 1951 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> 197select * from t3 where b between -2 and -1 order by b; 198a b c 1992 -2 -2 2001 -1 -1 201explain select * from t3 where b between -2 and -1 order by b; 202id select_type table type possible_keys key key_len ref rows Extra 2031 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> 205select * from t3 where c between -2 and -1 order by b; 206a b c 2072 -2 -2 2081 -1 -1 209explain select * from t3 where c between -2 and -1 order by b; 210id select_type table type possible_keys key key_len ref rows Extra 2111 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> 213select * from t3 where b between -2 and -1 order by c; 214a b c 2152 -2 -2 2161 -1 -1 217explain select * from t3 where b between -2 and -1 order by c; 218id select_type table type possible_keys key key_len ref rows Extra 2191 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> 221select * from t3 where c between -2 and -1 order by c; 222a b c 2232 -2 -2 2241 -1 -1 225explain select * from t3 where c between -2 and -1 order by c; 226id select_type table type possible_keys key key_len ref rows Extra 2271 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> 229select sum(b) from t1 group by b; 230sum(b) 231NULL 232-3 233-2 234-2 235explain select sum(b) from t1 group by b; 236id select_type table type possible_keys key key_len ref rows Extra 2371 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort 238# SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <indexed vcol> 239select sum(c) from t1 group by c; 240sum(c) 241NULL 242-3 243-2 244-2 245explain select sum(c) from t1 group by c; 246id select_type table type possible_keys key key_len ref rows Extra 2471 SIMPLE t1 index NULL c 5 NULL 5 Using index 248# SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <indexed vcol> 249select sum(b) from t1 group by c; 250sum(b) 251NULL 252-3 253-2 254-2 255explain select sum(b) from t1 group by c; 256id select_type table type possible_keys key key_len ref rows Extra 2571 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> 259select sum(c) from t1 group by b; 260sum(c) 261NULL 262-3 263-2 264-2 265explain select sum(c) from t1 group by b; 266id select_type table type possible_keys key key_len ref rows Extra 2671 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort 268# 269# Bug #806057: join with USING over a virtual column 270# 271CREATE TABLE t1 (b int); 272INSERT INTO t1 VALUES (NULL),( 78), (185), (0), (154); 273CREATE TABLE t2 (a int, b int AS (a) VIRTUAL); 274INSERT IGNORE INTO t2 VALUES (187,187), (9,9), (187,187); 275Warnings: 276Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored 277Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored 278Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored 279EXPLAIN EXTENDED 280SELECT * FROM t1 JOIN t2 USING (b); 281id select_type table type possible_keys key key_len ref rows filtered Extra 2821 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 2831 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) 284Warnings: 285Note 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` 286SELECT * FROM t1 JOIN t2 USING (b); 287b a 288EXPLAIN EXTENDED 289SELECT * FROM t1 NATURAL JOIN t2; 290id select_type table type possible_keys key key_len ref rows filtered Extra 2911 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 2921 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) 293Warnings: 294Note 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` 295SELECT * FROM t1 NATURAL JOIN t2; 296b a 297DROP TABLE t1,t2; 298create table t1 ( 299pk integer auto_increment, 300bi integer not null, 301vi integer generated always as (bi) persistent, 302bc varchar(1) not null, 303vc varchar(2) generated always as (concat(bc, bc)) persistent, 304primary key (pk), 305key (vi, vc)); 306insert 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'); 307create table t2 ( 308pk integer auto_increment, 309bi integer not null, 310vi integer generated always as (bi) persistent, 311bc varchar(257) not null, 312vc varchar(2) generated always as (concat(bc, bc)) persistent, 313primary key (pk), 314key (vi, vc)); 315insert 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'); 316explain # should be using join buffer 317select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc)); 318id select_type table type possible_keys key key_len ref rows Extra 3191 SIMPLE t2 index NULL vi 10 NULL 19 Using index 3201 SIMPLE t1 ALL NULL NULL NULL NULL 19 Using where; Using join buffer (flat, BNL join) 3211 SIMPLE t3 index NULL PRIMARY 4 NULL 19 Using where; Using index; Using join buffer (incremental, BNL join) 322select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc)); 323vi 3241 3251 3261 3271 3281 3291 3301 3311 3321 3331 3341 3351 3361 3371 3381 3391 3401 3411 3421 3431 3441 3451 3461 3471 3481 3491 3501 3511 3521 3531 3541 3551 3561 3572 3584 3594 3604 3614 3624 3634 3644 3654 3664 3674 3684 3694 3704 3714 3724 3734 3744 3754 3764 3774 3784 3794 3804 3814 3824 3834 3844 3854 3864 3874 3884 3894 3904 3914 3924 3934 3946 3956 3966 3976 3987 3997 4008 4018 4028 4038 4049 4059 406drop table t2,t1; 407create table t1 ( 408pk integer auto_increment, 409bi integer not null, 410vi integer generated always as (bi) virtual, 411bc varchar(1) not null, 412vc varchar(2) generated always as (concat(bc, bc)), 413primary key (pk), 414key (vi, vc)); 415insert 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'); 416create table t2 ( 417pk integer auto_increment, 418bi integer not null, 419vi integer generated always as (bi) virtual, 420bc varchar(257) not null, 421vc varchar(2) generated always as (concat(bc, bc)), 422primary key (pk), 423key (vi, vc)); 424insert 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'); 425explain # should be using join buffer 426select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc)); 427id select_type table type possible_keys key key_len ref rows Extra 4281 SIMPLE t2 index NULL vi 10 NULL 19 Using index 4291 SIMPLE t1 ALL NULL NULL NULL NULL 19 Using where; Using join buffer (flat, BNL join) 4301 SIMPLE t3 index NULL PRIMARY 4 NULL 19 Using where; Using index; Using join buffer (incremental, BNL join) 431select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc)); 432vi 4331 4341 4351 4361 4371 4381 4391 4401 4411 4421 4431 4441 4451 4461 4471 4481 4491 4501 4511 4521 4531 4541 4551 4561 4571 4581 4591 4601 4611 4621 4631 4641 4651 4662 4674 4684 4694 4704 4714 4724 4734 4744 4754 4764 4774 4784 4794 4804 4814 4824 4834 4844 4854 4864 4874 4884 4894 4904 4914 4924 4934 4944 4954 4964 4974 4984 4994 5004 5014 5024 5036 5046 5056 5066 5077 5087 5098 5108 5118 5128 5139 5149 515drop table t2,t1; 516