SET @@session.default_storage_engine = 'MyISAM';
create table t1 (a int,
b int as (-a),
c int as (-a) persistent,
index (c));
insert into t1 (a) values (2), (1), (1), (3), (NULL);
create table t2 like t1;
insert into t2 (a) values (1);
create table t3 (a int primary key, 
b int as (-a),
c int as (-a) persistent unique);
insert into t3 (a) values (2),(1),(3),(5),(4),(7);
# select_type=SIMPLE, type=system
select * from t2;
a	b	c
1	-1	-1
explain select * from t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	
select * from t2 where c=-1;
a	b	c
1	-1	-1
explain select * from t2 where c=-1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	system	c	NULL	NULL	NULL	1	
# select_type=SIMPLE, type=ALL
select * from t1 where b=-1;
a	b	c
1	-1	-1
1	-1	-1
explain select * from t1 where b=-1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
# select_type=SIMPLE, type=const
select * from t3 where a=1;
a	b	c
1	-1	-1
explain select * from t3 where a=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	const	PRIMARY	PRIMARY	4	const	1	
# select_type=SIMPLE, type=range
select * from t3 where c>=-1;
a	b	c
1	-1	-1
explain select * from t3 where c>=-1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	range	c	c	5	NULL	1	Using index condition
# select_type=SIMPLE, type=ref
select * from t1,t3 where t1.c=t3.c and t3.c=-1;
a	b	c	a	b	c
1	-1	-1	1	-1	-1
1	-1	-1	1	-1	-1
explain select * from t1,t3 where t1.c=t3.c and t3.c=-1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	const	c	c	5	const	1	
1	SIMPLE	t1	ref	c	c	5	const	2	
# select_type=PRIMARY, type=index,ALL
select * from t1 where b in (select c from t3);
a	b	c
2	-2	-2
1	-1	-1
1	-1	-1
3	-3	-3
explain select * from t1 where b in (select c from t3);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
1	PRIMARY	t3	eq_ref	c	c	5	test.t1.b	1	Using index
# select_type=PRIMARY, type=range,ref
select * from t1 where c in (select c from t3 where c between -2 and -1);
a	b	c
2	-2	-2
1	-1	-1
1	-1	-1
explain select * from t1 where c in (select c from t3 where c between -2 and -1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	range	c	c	5	NULL	2	Using where; Using index
1	PRIMARY	t1	ref	c	c	5	test.t3.c	1	
# select_type=UNION, type=system
# select_type=UNION RESULT, type=<union1,2>
select * from t1 union select * from t2;
a	b	c
2	-2	-2
1	-1	-1
3	-3	-3
NULL	NULL	NULL
explain select * from t1 union select * from t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
2	UNION	t2	system	NULL	NULL	NULL	NULL	1	
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
# select_type=DERIVED, type=system
set @tmp_optimizer_switch=@@optimizer_switch;
set optimizer_switch='derived_merge=off,derived_with_keys=off';
select * from (select a,b,c from t1) as t11;
a	b	c
2	-2	-2
1	-1	-1
1	-1	-1
3	-3	-3
NULL	NULL	NULL
explain select * from (select a,b,c from t1) as t11;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	5	
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	5	
set optimizer_switch=@tmp_optimizer_switch;
###
### Using aggregate functions with/without DISTINCT
###
# SELECT COUNT(*) FROM tbl_name
select count(*) from t1;
count(*)
5
explain select count(*) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
# SELECT COUNT(DISTINCT <non-vcol>) FROM tbl_name
select count(distinct a) from t1;
count(distinct a)
3
explain select count(distinct a) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
# SELECT COUNT(DISTINCT <non-stored vcol>) FROM tbl_name
select count(distinct b) from t1;
count(distinct b)
3
explain select count(distinct b) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
# SELECT COUNT(DISTINCT <stored vcol>) FROM tbl_name
select count(distinct c) from t1;
count(distinct c)
3
explain select count(distinct c) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	c	5	NULL	5	Using index for group-by
###
### filesort & range-based utils
###
# SELECT * FROM tbl_name WHERE <vcol expr>
select * from t3 where c >= -2;
a	b	c
2	-2	-2
1	-1	-1
explain select * from t3 where c >= -2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	range	c	c	5	NULL	2	Using index condition
# SELECT * FROM tbl_name WHERE <non-vcol expr>
select * from t3 where a between 1 and 2;
a	b	c
1	-1	-1
2	-2	-2
explain select * from t3 where a between 1 and 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition
# SELECT * FROM tbl_name WHERE <non-indexed vcol expr>
select * from t3 where b between -2 and -1;
a	b	c
2	-2	-2
1	-1	-1
explain select * from t3 where b between -2 and -1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	6	Using where
# SELECT * FROM tbl_name WHERE <indexed vcol expr>
select * from t3 where c between -2 and -1;
a	b	c
2	-2	-2
1	-1	-1
explain select * from t3 where c between -2 and -1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	range	c	c	5	NULL	2	Using index condition
# SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol>
select * from t3 where a between 1 and 2 order by c;
a	b	c
2	-2	-2
1	-1	-1
explain select * from t3 where a between 1 and 2 order by c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using filesort
# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol>
select * from t3 where b between -2 and -1 order by a;
a	b	c
1	-1	-1
2	-2	-2
explain select * from t3 where b between -2 and -1 order by a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	6	Using where; Using filesort
# SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-vcol>
select * from t3 where c between -2 and -1 order by a;
a	b	c
1	-1	-1
2	-2	-2
explain select * from t3 where c between -2 and -1 order by a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	range	c	c	5	NULL	2	Using index condition; Using filesort
# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-indexed vcol>
select * from t3 where b between -2 and -1 order by b;
a	b	c
2	-2	-2
1	-1	-1
explain select * from t3 where b between -2 and -1 order by b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	6	Using where; Using filesort
# SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-indexed vcol>
select * from t3 where c between -2 and -1 order by b;
a	b	c
2	-2	-2
1	-1	-1
explain select * from t3 where c between -2 and -1 order by b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	range	c	c	5	NULL	2	Using index condition; Using filesort
# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <indexed vcol>
select * from t3 where b between -2 and -1 order by c;
a	b	c
2	-2	-2
1	-1	-1
explain select * from t3 where b between -2 and -1 order by c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	6	Using where; Using filesort
# SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <indexed vcol>
select * from t3 where c between -2 and -1 order by c;
a	b	c
2	-2	-2
1	-1	-1
explain select * from t3 where c between -2 and -1 order by c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	range	c	c	5	NULL	2	Using index condition
# SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol>
select sum(b) from t1 group by b;
sum(b)
NULL
-3
-2
-2
explain select sum(b) from t1 group by b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
# SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <indexed vcol>
select sum(c) from t1 group by c;
sum(c)
NULL
-3
-2
-2
explain select sum(c) from t1 group by c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	c	5	NULL	5	Using index
# SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <indexed vcol>
select sum(b) from t1 group by c;
sum(b)
NULL
-3
-2
-2
explain select sum(b) from t1 group by c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
# SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol>
select sum(c) from t1 group by b;
sum(c)
NULL
-3
-2
-2
explain select sum(c) from t1 group by b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
#
# Bug #806057: join with USING over a virtual column 
#
CREATE TABLE t1 (b int);
INSERT INTO t1 VALUES (NULL),( 78), (185), (0), (154);
CREATE TABLE t2 (a int, b int AS (a) VIRTUAL);
INSERT IGNORE INTO t2 VALUES (187,187), (9,9), (187,187);
Warnings:
Warning	1906	The value specified for generated column 'b' in table 't2' has been ignored
Warning	1906	The value specified for generated column 'b' in table 't2' has been ignored
Warning	1906	The value specified for generated column 'b' in table 't2' has been ignored
EXPLAIN EXTENDED
SELECT * FROM t1 JOIN t2 USING (b);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
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`
SELECT * FROM t1 JOIN t2 USING (b);
b	a
EXPLAIN EXTENDED
SELECT * FROM t1 NATURAL JOIN t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
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`
SELECT * FROM t1 NATURAL JOIN t2;
b	a
DROP TABLE t1,t2;
create table t1 (
pk integer auto_increment,
bi integer not null,
vi integer generated always as (bi) persistent,
bc varchar(1) not null,
vc varchar(2) generated always as (concat(bc, bc)) persistent,
primary key (pk),
key (vi, vc));
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');
create table t2 (
pk integer auto_increment,
bi integer not null,
vi integer generated always as (bi) persistent,
bc varchar(257) not null,
vc varchar(2) generated always as (concat(bc, bc)) persistent,
primary key (pk),
key (vi, vc));
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');
explain # should be using join buffer
select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	vi	16	NULL	19	Using index
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	19	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	index	NULL	PRIMARY	4	NULL	19	Using where; Using index; Using join buffer (incremental, BNL join)
select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc));
vi
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
2
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
6
6
6
6
7
7
8
8
8
8
9
9
drop table t2,t1;
create table t1 (
pk integer auto_increment,
bi integer not null,
vi integer generated always as (bi) virtual,
bc varchar(1) not null,
vc varchar(2) generated always as (concat(bc, bc)),
primary key (pk),
key (vi, vc));
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');
create table t2 (
pk integer auto_increment,
bi integer not null,
vi integer generated always as (bi) virtual,
bc varchar(257) not null,
vc varchar(2) generated always as (concat(bc, bc)),
primary key (pk),
key (vi, vc));
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');
explain # should be using join buffer
select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	vi	16	NULL	19	Using index
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	19	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	index	NULL	PRIMARY	4	NULL	19	Using where; Using index; Using join buffer (incremental, BNL join)
select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc));
vi
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
2
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
6
6
6
6
7
7
8
8
8
8
9
9
drop table t2,t1;