path: root/mysql-test/main/group_min_max.result

drop table if exists t1;
create table t1 (
a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' '
);
insert into t1 (a1, a2, b, c, d) values
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
insert into t1 select * from t1;
insert into t1 select * from t1;
create index idx_t1_0 on t1 (a1);
create index idx_t1_1 on t1 (a1,a2,b,c);
create index idx_t1_2 on t1 (a1,a2,b);
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	Table is already up to date
drop table if exists t2;
create table t2 (
a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(248) default ' '
);
insert into t2 select * from t1;
insert into t2 (a1, a2, b, c, d) values
('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
('a','a','a',NULL,'xyz'),
('a','a','b',NULL,'xyz'),
('a','b','a',NULL,'xyz'),
('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
('d','b','b',NULL,'xyz'),
('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),
('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
('a','a','a',NULL,'xyz'),
('a','a','b',NULL,'xyz'),
('a','b','a',NULL,'xyz'),
('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
('d','b','b',NULL,'xyz'),
('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz');
create index idx_t2_0 on t2 (a1);
create index idx_t2_1 on t2 (a1,a2,b,c);
create index idx_t2_2 on t2 (a1,a2,b);
analyze table t2;
Table	Op	Msg_type	Msg_text
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	Table is already up to date
drop table if exists t3;
create table t3 (
a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' '
);
insert into t3 (a1, a2, b, c, d) values
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
insert into t3 (a1, a2, b, c, d) values
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
insert into t3 (a1, a2, b, c, d) values
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
insert into t3 (a1, a2, b, c, d) values
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
create index idx_t3_0 on t3 (a1);
create index idx_t3_1 on t3 (a1,a2,b,c);
create index idx_t3_2 on t3 (a1,a2,b);
analyze table t3;
Table	Op	Msg_type	Msg_text
test.t3	analyze	status	Engine-independent statistics collected
test.t3	analyze	status	Table is already up to date
explain select a1, min(a2) from t1 group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	5	Using index for group-by
explain select a1, max(a2) from t1 group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	65	NULL	5	Using index for group-by
explain select a1, min(a2), max(a2) from t1 group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	5	Using index for group-by
explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using index for group-by
explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using index for group-by
explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	#	NULL	#	Using index for group-by
explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	5	Using index for group-by
explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using index for group-by
explain select min(a2) from t1 group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	5	Using index for group-by
explain select a2, min(c), max(c) from t1 group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using index for group-by
select a1, min(a2) from t1 group by a1;
a1	min(a2)
a	a
b	a
c	a
d	a
select a1, max(a2) from t1 group by a1;
a1	max(a2)
a	b
b	b
c	b
d	b
select a1, min(a2), max(a2) from t1 group by a1;
a1	min(a2)	max(a2)
a	a	b
b	a	b
c	a	b
d	a	b
select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	a111	d111
a	a	b	e112	h112
a	b	a	i121	l121
a	b	b	m122	p122
b	a	a	a211	d211
b	a	b	e212	h212
b	b	a	i221	l221
b	b	b	m222	p222
c	a	a	a311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	a	a411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
a1	a2	b	max(c)	min(c)
a	a	a	d111	a111
a	a	b	h112	e112
a	b	a	l121	i121
a	b	b	p122	m122
b	a	a	d211	a211
b	a	b	h212	e212
b	b	a	l221	i221
b	b	b	p222	m222
c	a	a	d311	a311
c	a	b	h312	e312
c	b	a	l321	i321
c	b	b	p322	m322
d	a	a	d411	a411
d	a	b	h412	e412
d	b	a	l421	i421
d	b	b	p422	m422
select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
a1	a2	b	max(c)	min(c)
a	a	NULL	a999	a777
a	a	a	d111	a111
a	a	b	h112	e112
a	b	a	l121	i121
a	b	b	p122	m122
b	a	a	d211	a211
b	a	b	h212	e212
b	b	a	l221	i221
b	b	b	p222	m222
c	a	NULL	c999	c777
c	a	a	d311	a311
c	a	b	h312	e312
c	b	a	l321	i321
c	b	b	p322	m322
d	a	a	d411	a411
d	a	b	h412	e412
d	b	a	l421	i421
d	b	b	p422	m422
e	a	a	NULL	NULL
e	a	b	NULL	NULL
select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
min(a2)	a1	max(a2)	min(a2)	a1
a	a	b	a	a
a	b	b	a	b
a	c	b	a	c
a	d	b	a	d
select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
a1	b	min(c)	a1	max(c)	b	a2	max(c)	max(c)
a	a	a111	a	d111	a	a	d111	d111
a	b	e112	a	h112	b	a	h112	h112
a	a	i121	a	l121	a	b	l121	l121
a	b	m122	a	p122	b	b	p122	p122
b	a	a211	b	d211	a	a	d211	d211
b	b	e212	b	h212	b	a	h212	h212
b	a	i221	b	l221	a	b	l221	l221
b	b	m222	b	p222	b	b	p222	p222
c	a	a311	c	d311	a	a	d311	d311
c	b	e312	c	h312	b	a	h312	h312
c	a	i321	c	l321	a	b	l321	l321
c	b	m322	c	p322	b	b	p322	p322
d	a	a411	d	d411	a	a	d411	d411
d	b	e412	d	h412	b	a	h412	h412
d	a	i421	d	l421	a	b	l421	l421
d	b	m422	d	p422	b	b	p422	p422
select min(a2) from t1 group by a1;
min(a2)
a
a
a
a
select a2, min(c), max(c) from t1 group by a1,a2,b;
a2	min(c)	max(c)
a	a111	d111
a	e112	h112
b	i121	l121
b	m122	p122
a	a211	d211
a	e212	h212
b	i221	l221
b	m222	p222
a	a311	d311
a	e312	h312
b	i321	l321
b	m322	p322
a	a411	d411
a	e412	h412
b	i421	l421
b	m422	p422
explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	12	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	13	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	13	Using where; Using index for group-by
explain select a1, max(c)            from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	13	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	9	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	9	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	9	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	9	Using where; Using index for group-by
explain select a1,min(c),max(c)      from t1 where a1 >= 'b' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	13	Using where; Using index for group-by
explain select a1,  max(c)           from t1 where a1 in ('a','b','d') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	13	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t2 where a1 < 'd' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
explain select a1, max(c)            from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,min(c),max(c)      from t2 where a1 >= 'b' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,  max(c)           from t2 where a1 in ('a','b','d') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	a111	d111
a	a	b	e112	h112
a	b	a	i121	l121
a	b	b	m122	p122
b	a	a	a211	d211
b	a	b	e212	h212
b	b	a	i221	l221
b	b	b	m222	p222
c	a	a	a311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
a1	a2	b	min(c)	max(c)
b	a	a	a211	d211
b	a	b	e212	h212
b	b	a	i221	l221
b	b	b	m222	p222
c	a	a	a311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	a	a411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
select a1,a2,b,       max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
a1	a2	b	max(c)
a	a	a	d111
a	a	b	h112
a	b	a	l121
a	b	b	p122
c	a	a	d311
c	a	b	h312
c	b	a	l321
c	b	b	p322
d	a	a	d411
d	a	b	h412
d	b	a	l421
d	b	b	p422
select a1, max(c)            from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
a1	max(c)
a	d111
a	h112
a	l121
a	p122
c	d311
c	h312
c	l321
c	p322
d	d411
d	h412
d	l421
d	p422
select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	a111	d111
a	a	b	e112	h112
b	a	a	a211	d211
b	a	b	e212	h212
c	a	a	a311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	a	a411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
select a1,a2,b,       max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
a1	a2	b	max(c)
b	a	a	d211
b	a	b	h212
b	b	a	l221
b	b	b	p222
d	a	a	d411
d	a	b	h412
d	b	a	l421
d	b	b	p422
select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
a1	a2	b	min(c)	max(c)
b	a	a	a211	d211
b	a	b	e212	h212
b	b	a	i221	l221
b	b	b	m222	p222
d	a	a	a411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
select a1,a2,b,       max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
a1	a2	b	max(c)
a	b	a	l121
a	b	b	p122
b	b	a	l221
b	b	b	p222
c	b	a	l321
c	b	b	p322
d	b	a	l421
d	b	b	p422
select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	b	a	i121	l121
a	b	b	m122	p122
b	b	a	i221	l221
b	b	b	m222	p222
c	b	a	i321	l321
c	b	b	m322	p322
d	b	a	i421	l421
d	b	b	m422	p422
select a1,min(c),max(c)      from t1 where a1 >= 'b' group by a1,a2,b;
a1	min(c)	max(c)
b	a211	d211
b	e212	h212
b	i221	l221
b	m222	p222
c	a311	d311
c	e312	h312
c	i321	l321
c	m322	p322
d	a411	d411
d	e412	h412
d	i421	l421
d	m422	p422
select a1,  max(c)           from t1 where a1 in ('a','b','d') group by a1,a2,b;
a1	max(c)
a	d111
a	h112
a	l121
a	p122
b	d211
b	h212
b	l221
b	p222
d	d411
d	h412
d	l421
d	p422
select a1,a2,b,       max(c) from t2 where a1 < 'd' group by a1,a2,b;
a1	a2	b	max(c)
a	a	NULL	a999
a	a	a	d111
a	a	b	h112
a	b	a	l121
a	b	b	p122
b	a	a	d211
b	a	b	h212
b	b	a	l221
b	b	b	p222
c	a	NULL	c999
c	a	a	d311
c	a	b	h312
c	b	a	l321
c	b	b	p322
select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	NULL	a777	a999
a	a	a	a111	d111
a	a	b	e112	h112
a	b	a	i121	l121
a	b	b	m122	p122
b	a	a	a211	d211
b	a	b	e212	h212
b	b	a	i221	l221
b	b	b	m222	p222
c	a	NULL	c777	c999
c	a	a	a311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
a1	a2	b	min(c)	max(c)
b	a	a	a211	d211
b	a	b	e212	h212
b	b	a	i221	l221
b	b	b	m222	p222
c	a	NULL	c777	c999
c	a	a	a311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	a	a411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
e	a	a	NULL	NULL
e	a	b	NULL	NULL
select a1,a2,b,       max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
a1	a2	b	max(c)
a	a	NULL	a999
a	a	a	d111
a	a	b	h112
a	b	a	l121
a	b	b	p122
c	a	NULL	c999
c	a	a	d311
c	a	b	h312
c	b	a	l321
c	b	b	p322
d	a	a	d411
d	a	b	h412
d	b	a	l421
d	b	b	p422
e	a	a	NULL
e	a	b	NULL
select a1, max(c)            from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
a1	max(c)
a	a999
a	d111
a	h112
a	l121
a	p122
c	c999
c	d311
c	h312
c	l321
c	p322
d	d411
d	h412
d	l421
d	p422
e	NULL
e	NULL
select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	NULL	a777	a999
a	a	a	a111	d111
a	a	b	e112	h112
b	a	a	a211	d211
b	a	b	e212	h212
c	a	NULL	c777	c999
c	a	a	a311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	a	a411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
e	a	a	NULL	NULL
e	a	b	NULL	NULL
select a1,a2,b,       max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
a1	a2	b	max(c)
b	a	a	d211
b	a	b	h212
b	b	a	l221
b	b	b	p222
d	a	a	d411
d	a	b	h412
d	b	a	l421
d	b	b	p422
select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
a1	a2	b	min(c)	max(c)
b	a	a	a211	d211
b	a	b	e212	h212
b	b	a	i221	l221
b	b	b	m222	p222
d	a	a	a411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
select a1,a2,b,       max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
a1	a2	b	max(c)
a	b	a	l121
a	b	b	p122
b	b	a	l221
b	b	b	p222
c	b	a	l321
c	b	b	p322
d	b	a	l421
d	b	b	p422
select a1,a2,b,min(c),max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	b	a	i121	l121
a	b	b	m122	p122
b	b	a	i221	l221
b	b	b	m222	p222
c	b	a	i321	l321
c	b	b	m322	p322
d	b	a	i421	l421
d	b	b	m422	p422
select a1,min(c),max(c)      from t2 where a1 >= 'b' group by a1,a2,b;
a1	min(c)	max(c)
b	a211	d211
b	e212	h212
b	i221	l221
b	m222	p222
c	c777	c999
c	a311	d311
c	e312	h312
c	i321	l321
c	m322	p322
d	a411	d411
d	e412	h412
d	i421	l421
d	m422	p422
e	NULL	NULL
e	NULL	NULL
select a1,  max(c)           from t2 where a1 in ('a','b','d') group by a1,a2,b;
a1	max(c)
a	a999
a	d111
a	h112
a	l121
a	p122
b	d211
b	h212
b	l221
b	p222
d	d411
d	h412
d	l421
d	p422
explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	5	Using where; Using index for group-by
explain select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b') group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	5	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t1 where (b = 'b') group by a1,a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	9	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	9	Using where; Using index for group-by
explain select a1,a2, max(c)         from t1 where (b = 'b') group by a1,a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	9	Using where; Using index for group-by
explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	6	Using where; Using index for group-by
explain select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b') group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	6	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t2 where (b = 'b') group by a1,a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	10	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	10	Using where; Using index for group-by
explain select a1,a2, max(c)         from t2 where (b = 'b') group by a1,a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	10	Using where; Using index for group-by
explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	range	NULL	idx_t3_1	6	NULL	4	Using where; Using index for group-by
explain select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b') group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	range	NULL	idx_t3_1	6	NULL	4	Using where; Using index for group-by
select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
a1	a2	b	max(c)	min(c)
a	a	b	h112	e112
b	a	b	h212	e212
c	a	b	h312	e312
d	a	b	h412	e412
select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b') group by a1;
a1	max(c)	min(c)
a	h112	e112
b	h212	e212
c	h312	e312
d	h412	e412
select a1,a2,b,       max(c) from t1 where (b = 'b') group by a1,a2;
a1	a2	b	max(c)
a	a	b	h112
a	b	b	p122
b	a	b	h212
b	b	b	p222
c	a	b	h312
c	b	b	p322
d	a	b	h412
d	b	b	p422
select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
a1	a2	b	min(c)	max(c)
a	a	b	e112	h112
a	b	b	m122	p122
b	a	b	e212	h212
b	b	b	m222	p222
c	a	b	e312	h312
c	b	b	m322	p322
d	a	b	e412	h412
d	b	b	m422	p422
select a1,a2, max(c)         from t1 where (b = 'b') group by a1,a2;
a1	a2	max(c)
a	a	h112
a	b	p122
b	a	h212
b	b	p222
c	a	h312
c	b	p322
d	a	h412
d	b	p422
select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
a1	a2	b	max(c)	min(c)
a	a	b	h112	e112
b	a	b	h212	e212
c	a	b	h312	e312
d	a	b	h412	e412
e	a	b	NULL	NULL
select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b') group by a1;
a1	max(c)	min(c)
a	h112	e112
b	h212	e212
c	h312	e312
d	h412	e412
e	NULL	NULL
select a1,a2,b,       max(c) from t2 where (b = 'b') group by a1,a2;
a1	a2	b	max(c)
a	a	b	h112
a	b	b	p122
b	a	b	h212
b	b	b	p222
c	a	b	h312
c	b	b	p322
d	a	b	h412
d	b	b	p422
e	a	b	NULL
select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
a1	a2	b	min(c)	max(c)
a	a	b	e112	h112
a	b	b	m122	p122
b	a	b	e212	h212
b	b	b	m222	p222
c	a	b	e312	h312
c	b	b	m322	p322
d	a	b	e412	h412
d	b	b	m422	p422
e	a	b	NULL	NULL
select a1,a2, max(c)         from t2 where (b = 'b') group by a1,a2;
a1	a2	max(c)
a	a	h112
a	b	p122
b	a	h212
b	b	p222
c	a	h312
c	b	p322
d	a	h412
d	b	p422
e	a	NULL
select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
a1	a2	b	max(c)	min(c)
a	a	b	h112	e112
b	a	b	h212	e212
c	a	b	h312	e312
select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b') group by a1;
a1	max(c)	min(c)
a	h112	e112
b	h212	e212
c	h312	e312
explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	6	Using where; Using index for group-by
explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	6	Using where; Using index for group-by
explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	10	Using where; Using index for group-by
explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	10	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	10	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	10	Using where; Using index for group-by
select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
a1	a2	b	min(c)
a	a	NULL	a777
c	a	NULL	c777
select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
a1	a2	b	max(c)
a	a	NULL	a999
c	a	NULL	c999
select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
a1	a2	b	min(c)
a	a	NULL	a777
c	a	NULL	c777
select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
a1	a2	b	max(c)
a	a	NULL	a999
c	a	NULL	c999
select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
a1	a2	b	min(c)	max(c)
a	a	NULL	a777	a999
c	a	NULL	c777	c999
select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
a1	a2	b	min(c)	max(c)
a	a	NULL	a777	a999
c	a	NULL	c777	c999
explain select a1,a2,b,       max(c) from t1 where (c > 'b1') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t1 where (c > 'f123') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t1 where (c < 'a0') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t1 where (c < 'k321') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t2 where (c > 'b1') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t2 where (c > 'f123') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t2 where (c < 'a0') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t2 where (c < 'k321') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,       max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
select a1,a2,b,       max(c) from t1 where (c > 'b1') group by a1,a2,b;
a1	a2	b	max(c)
a	a	a	d111
a	a	b	h112
a	b	a	l121
a	b	b	p122
b	a	a	d211
b	a	b	h212
b	b	a	l221
b	b	b	p222
c	a	a	d311
c	a	b	h312
c	b	a	l321
c	b	b	p322
d	a	a	d411
d	a	b	h412
d	b	a	l421
d	b	b	p422
select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	b111	d111
a	a	b	e112	h112
a	b	a	i121	l121
a	b	b	m122	p122
b	a	a	b211	d211
b	a	b	e212	h212
b	b	a	i221	l221
b	b	b	m222	p222
c	a	a	b311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	a	b411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
select a1,a2,b,       max(c) from t1 where (c > 'f123') group by a1,a2,b;
a1	a2	b	max(c)
a	a	b	h112
a	b	a	l121
a	b	b	p122
b	a	b	h212
b	b	a	l221
b	b	b	p222
c	a	b	h312
c	b	a	l321
c	b	b	p322
d	a	b	h412
d	b	a	l421
d	b	b	p422
select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	b	g112	h112
a	b	a	i121	l121
a	b	b	m122	p122
b	a	b	f212	h212
b	b	a	i221	l221
b	b	b	m222	p222
c	a	b	f312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	b	f412	h412
d	b	a	i421	l421
d	b	b	m422	p422
select a1,a2,b,       max(c) from t1 where (c < 'a0') group by a1,a2,b;
a1	a2	b	max(c)
select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
select a1,a2,b,       max(c) from t1 where (c < 'k321') group by a1,a2,b;
a1	a2	b	max(c)
a	a	a	d111
a	a	b	h112
a	b	a	k121
b	a	a	d211
b	a	b	h212
b	b	a	k221
c	a	a	d311
c	a	b	h312
c	b	a	j321
d	a	a	d411
d	a	b	h412
d	b	a	j421
select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	a111	d111
a	a	b	e112	h112
a	b	a	i121	k121
b	a	a	a211	d211
b	a	b	e212	h212
b	b	a	i221	k221
c	a	a	a311	d311
c	a	b	e312	h312
c	b	a	i321	j321
d	a	a	a411	d411
d	a	b	e412	h412
d	b	a	i421	j421
select a1,a2,b,       max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
a1	a2	b	max(c)
a	a	a	d111
a	a	b	h112
a	b	a	l121
a	b	b	p122
b	a	a	d211
b	a	b	h212
b	b	a	l221
b	b	b	p222
c	a	a	d311
c	a	b	h312
c	b	a	l321
c	b	b	p322
d	a	a	d411
d	a	b	h412
d	b	a	l421
d	b	b	p422
select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	b111	d111
a	a	b	e112	h112
a	b	a	i121	l121
a	b	b	m122	p122
b	a	a	b211	d211
b	a	b	e212	h212
b	b	a	i221	l221
b	b	b	m222	p222
c	a	a	b311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	a	b411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
select a1,a2,b,       max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
a1	a2	b	max(c)
a	a	a	d111
a	a	b	h112
a	b	a	l121
a	b	b	p122
b	a	a	d211
b	a	b	h212
b	b	a	l221
b	b	b	p222
c	a	a	d311
c	a	b	h312
c	b	a	l321
c	b	b	p322
d	a	a	d411
d	a	b	h412
d	b	a	l421
d	b	b	p422
select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	a111	d111
a	a	b	e112	h112
a	b	a	i121	l121
a	b	b	m122	p122
b	a	a	a211	d211
b	a	b	e212	h212
b	b	a	i221	l221
b	b	b	m222	p222
c	a	a	a311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	a	a411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	c111	d111
a	a	b	e112	g112
b	a	a	b211	d211
b	a	b	e212	f212
c	a	a	b311	d311
c	a	b	e312	f312
d	a	a	b411	d411
d	a	b	e412	f412
select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	a111	c111
b	a	a	a211	c211
c	a	a	a311	c311
d	a	a	a411	c411
d	a	b	g412	g412
d	b	a	k421	k421
select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	c111	d111
a	a	b	e112	h112
b	a	a	b211	d211
b	a	b	e212	h212
c	a	a	b311	d311
c	a	b	e312	h312
d	a	a	b411	d411
d	a	b	e412	h412
select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	b111	d111
a	a	b	e112	h112
b	a	a	b211	d211
b	a	b	e212	h212
c	a	a	b311	d311
c	a	b	e312	h312
d	a	a	b411	d411
d	a	b	e412	h412
select a1,a2,b,       max(c) from t2 where (c > 'b1') group by a1,a2,b;
a1	a2	b	max(c)
a	a	a	d111
a	a	b	h112
a	b	a	l121
a	b	b	p122
b	a	a	d211
b	a	b	h212
b	b	a	l221
b	b	b	p222
c	a	NULL	c999
c	a	a	d311
c	a	b	h312
c	b	a	l321
c	b	b	p322
d	a	a	d411
d	a	b	h412
d	b	a	l421
d	b	b	p422
select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	b111	d111
a	a	b	e112	h112
a	b	a	i121	l121
a	b	b	m122	p122
b	a	a	b211	d211
b	a	b	e212	h212
b	b	a	i221	l221
b	b	b	m222	p222
c	a	NULL	c777	c999
c	a	a	b311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	a	b411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
select a1,a2,b,       max(c) from t2 where (c > 'f123') group by a1,a2,b;
a1	a2	b	max(c)
a	a	b	h112
a	b	a	l121
a	b	b	p122
b	a	b	h212
b	b	a	l221
b	b	b	p222
c	a	b	h312
c	b	a	l321
c	b	b	p322
d	a	b	h412
d	b	a	l421
d	b	b	p422
select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	b	g112	h112
a	b	a	i121	l121
a	b	b	m122	p122
b	a	b	f212	h212
b	b	a	i221	l221
b	b	b	m222	p222
c	a	b	f312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	b	f412	h412
d	b	a	i421	l421
d	b	b	m422	p422
select a1,a2,b,       max(c) from t2 where (c < 'a0') group by a1,a2,b;
a1	a2	b	max(c)
select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
select a1,a2,b,       max(c) from t2 where (c < 'k321') group by a1,a2,b;
a1	a2	b	max(c)
a	a	NULL	a999
a	a	a	d111
a	a	b	h112
a	b	a	k121
b	a	a	d211
b	a	b	h212
b	b	a	k221
c	a	NULL	c999
c	a	a	d311
c	a	b	h312
c	b	a	j321
d	a	a	d411
d	a	b	h412
d	b	a	j421
select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	NULL	a777	a999
a	a	a	a111	d111
a	a	b	e112	h112
a	b	a	i121	k121
b	a	a	a211	d211
b	a	b	e212	h212
b	b	a	i221	k221
c	a	NULL	c777	c999
c	a	a	a311	d311
c	a	b	e312	h312
c	b	a	i321	j321
d	a	a	a411	d411
d	a	b	e412	h412
d	b	a	i421	j421
select a1,a2,b,       max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
a1	a2	b	max(c)
a	a	a	d111
a	a	b	h112
a	b	a	l121
a	b	b	p122
b	a	a	d211
b	a	b	h212
b	b	a	l221
b	b	b	p222
c	a	NULL	c999
c	a	a	d311
c	a	b	h312
c	b	a	l321
c	b	b	p322
d	a	a	d411
d	a	b	h412
d	b	a	l421
d	b	b	p422
select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	b111	d111
a	a	b	e112	h112
a	b	a	i121	l121
a	b	b	m122	p122
b	a	a	b211	d211
b	a	b	e212	h212
b	b	a	i221	l221
b	b	b	m222	p222
c	a	NULL	c777	c999
c	a	a	b311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	a	b411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
select a1,a2,b,       max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
a1	a2	b	max(c)
a	a	NULL	a999
a	a	a	d111
a	a	b	h112
a	b	a	l121
a	b	b	p122
b	a	a	d211
b	a	b	h212
b	b	a	l221
b	b	b	p222
c	a	NULL	c999
c	a	a	d311
c	a	b	h312
c	b	a	l321
c	b	b	p322
d	a	a	d411
d	a	b	h412
d	b	a	l421
d	b	b	p422
select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	NULL	a777	a999
a	a	a	a111	d111
a	a	b	e112	h112
a	b	a	i121	l121
a	b	b	m122	p122
b	a	a	a211	d211
b	a	b	e212	h212
b	b	a	i221	l221
b	b	b	m222	p222
c	a	NULL	c777	c999
c	a	a	a311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	a	a411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	c111	d111
a	a	b	e112	g112
b	a	a	b211	d211
b	a	b	e212	f212
c	a	NULL	c777	c999
c	a	a	b311	d311
c	a	b	e312	f312
d	a	a	b411	d411
d	a	b	e412	f412
select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	NULL	a777	a999
a	a	a	a111	c111
b	a	a	a211	c211
c	a	a	a311	c311
d	a	a	a411	c411
d	a	b	g412	g412
d	b	a	k421	k421
select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	c111	d111
a	a	b	e112	h112
b	a	a	b211	d211
b	a	b	e212	h212
c	a	NULL	c777	c999
c	a	a	b311	d311
c	a	b	e312	h312
d	a	a	b411	d411
d	a	b	e412	h412
explain select a1,a2,b,min(c),max(c) from t1
where exists ( select * from t2 where t2.c = t1.c )
group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	idx_t1_1	163	NULL	512	Using index
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	16	func	1	
2	MATERIALIZED	t2	index	NULL	idx_t2_1	163	NULL	548	Using index
select a1,a2,b,min(c),max(c) from t1
where exists ( select * from t2 where t2.c = t1.c )
group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	a111	d111
a	a	b	e112	h112
a	b	a	i121	l121
a	b	b	m122	p122
b	a	a	a211	d211
b	a	b	e212	h212
b	b	a	i221	l221
b	b	b	m222	p222
c	a	a	a311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	a	a411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
explain select a1,a2,b,min(c),max(c) from t1
where exists ( select * from t2 where t2.c > 'b1' )
group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	idx_t1_1	163	NULL	512	Using index
2	SUBQUERY	t2	index	NULL	idx_t2_1	163	NULL	548	Using where; Using index
select a1,a2,b,min(c),max(c) from t1
where exists ( select * from t2 where t2.c > 'b1' )
group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	a111	d111
a	a	b	e112	h112
a	b	a	i121	l121
a	b	b	m122	p122
b	a	a	a211	d211
b	a	b	e212	h212
b	b	a	i221	l221
b	b	b	m222	p222
c	a	a	a311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	a	a411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
explain select a1,a2,b,c,min(c), max(c) from t1
where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' )
group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	idx_t1_1	163	NULL	512	Using where; Using index
2	DEPENDENT SUBQUERY	t2	index	NULL	idx_t2_1	163	NULL	548	Using where; Using index
select a1,a2,b,c,min(c), max(c) from t1
where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' )
group by a1,a2,b;
a1	a2	b	c	min(c)	max(c)
a	a	b	e112	e112	h112
a	b	b	m122	m122	p122
b	a	b	e212	e212	h212
b	b	b	m222	m222	p222
c	a	b	e312	e312	h312
c	b	b	m322	m322	p322
d	a	b	e412	e412	h412
d	b	b	m422	m422	p422
SET @save_optimizer_switch=@@optimizer_switch;
SET optimizer_switch='semijoin_with_cache=off';
explain select a1,a2,b,c,min(c), max(c) from t1
where exists ( select * from t2
where t2.c in (select c from t3 where t3.c > t1.b) and
t2.c > 'b1' )
group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	idx_t1_1	163	NULL	512	Using where; Using index
2	DEPENDENT SUBQUERY	t2	index	NULL	idx_t2_1	163	NULL	548	Using where; Using index
2	DEPENDENT SUBQUERY	t3	index	NULL	idx_t3_1	10	NULL	192	Using where; Using index; FirstMatch(t2)
select a1,a2,b,c,min(c), max(c) from t1
where exists ( select * from t2
where t2.c in (select c from t3 where t3.c > t1.b) and
t2.c > 'b1' )
group by a1,a2,b;
a1	a2	b	c	min(c)	max(c)
a	a	a	a111	a111	d111
a	a	b	e112	e112	h112
a	b	a	i121	i121	l121
a	b	b	m122	m122	p122
b	a	a	a211	a211	d211
b	a	b	e212	e212	h212
b	b	a	i221	i221	l221
b	b	b	m222	m222	p222
c	a	a	a311	a311	d311
c	a	b	e312	e312	h312
c	b	a	i321	i321	l321
c	b	b	m322	m322	p322
d	a	a	a411	a411	d411
d	a	b	e412	e412	h412
d	b	a	i421	i421	l421
d	b	b	m422	m422	p422
SET optimizer_switch=@save_optimizer_switch;
explain select a1,a2,b,c,min(c), max(c) from t1
where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' )
group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	idx_t1_1	163	NULL	512	Using where; Using index
2	DEPENDENT SUBQUERY	t2	index	NULL	idx_t2_1	163	NULL	548	Using where; Using index
select a1,a2,b,c,min(c), max(c) from t1
where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' )
group by a1,a2,b;
a1	a2	b	c	min(c)	max(c)
a	a	a	a111	a111	d111
a	a	b	e112	e112	h112
a	b	a	i121	i121	l121
a	b	b	m122	m122	p122
b	a	a	a211	a211	d211
b	a	b	e212	e212	h212
b	b	a	i221	i221	l221
b	b	b	m222	m222	p222
c	a	a	a311	a311	d311
c	a	b	e312	e312	h312
c	b	a	i321	i321	l321
c	b	b	m322	m322	p322
d	a	a	a411	a411	d411
d	a	b	e412	e412	h412
d	b	a	i421	i421	l421
d	b	b	m422	m422	p422
SET @save_optimizer_switch=@@optimizer_switch;
SET optimizer_switch='semijoin_with_cache=off';
explain select a1,a2,b,c,min(c), max(c) from t1
where exists ( select * from t2
where t2.c in (select c from t3 where t3.c > t1.c) and
t2.c > 'b1' )
group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	idx_t1_1	163	NULL	512	Using where; Using index
2	DEPENDENT SUBQUERY	t2	index	NULL	idx_t2_1	163	NULL	548	Using where; Using index
2	DEPENDENT SUBQUERY	t3	index	NULL	idx_t3_1	10	NULL	192	Using where; Using index; FirstMatch(t2)
select a1,a2,b,c,min(c), max(c) from t1
where exists ( select * from t2
where t2.c in (select c from t3 where t3.c > t1.c) and
t2.c > 'b1' )
group by a1,a2,b;
a1	a2	b	c	min(c)	max(c)
a	a	a	a111	a111	d111
a	a	b	e112	e112	h112
a	b	a	i121	i121	l121
a	b	b	m122	m122	p122
b	a	a	a211	a211	d211
b	a	b	e212	e212	h212
b	b	a	i221	i221	l221
b	b	b	m222	m222	p222
c	a	a	a311	a311	d311
c	a	b	e312	e312	h312
c	b	a	i321	i321	l321
c	b	b	m322	m322	o322
d	a	a	a411	a411	d411
d	a	b	e412	e412	h412
d	b	a	i421	i421	l421
d	b	b	m422	m422	o422
SET optimizer_switch=@save_optimizer_switch;
explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	163	NULL	13	Using where; Using index for group-by
explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	163	NULL	13	Using where; Using index for group-by
explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	13	Using where; Using index for group-by
explain select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	b	e112	h112
b	a	b	e212	h212
c	a	b	e312	h312
c	b	b	m322	p322
d	a	b	e412	h412
d	b	b	m422	p422
select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	c111	d111
a	a	b	e112	h112
b	a	a	b211	d211
b	a	b	e212	h212
c	a	a	b311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	a	b411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	b	a	i121	l121
b	b	a	i221	l221
c	b	a	i321	l321
d	b	a	i421	l421
select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
a1	a2	b	min(c)
b	b	a	k221
c	b	a	k321
d	b	a	k421
select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
a1	a2	b	min(c)
b	b	a	k221
c	b	a	k321
d	b	a	k421
select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
a1	a2	b	min(c)
select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b;
a1	a2	b	min(c)
select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	b	e112	h112
b	a	b	e212	h212
c	a	b	e312	h312
c	b	b	m322	p322
d	a	b	e412	h412
d	b	b	m422	p422
e	a	b	NULL	NULL
select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	a	a	c111	d111
a	a	b	e112	h112
b	a	a	b211	d211
b	a	b	e212	h212
c	a	NULL	c777	c999
c	a	a	b311	d311
c	a	b	e312	h312
c	b	a	i321	l321
c	b	b	m322	p322
d	a	a	b411	d411
d	a	b	e412	h412
d	b	a	i421	l421
d	b	b	m422	p422
select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
a1	a2	b	min(c)	max(c)
a	b	a	i121	l121
b	b	a	i221	l221
c	b	a	i321	l321
d	b	a	i421	l421
select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
a1	a2	b	min(c)
b	b	a	k221
c	b	a	k321
d	b	a	k421
select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
a1	a2	b	min(c)
b	b	a	k221
c	b	a	k321
d	b	a	k421
select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
a1	a2	b	min(c)
explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	17	Using where; Using index for group-by
explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	13	Using where; Using index for group-by
explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
a1	a2	b
a	a	b
b	a	b
c	a	b
c	b	b
d	a	b
d	b	b
select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
a1	a2	b
a	b	a
b	b	a
c	b	a
d	b	a
select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
a1	a2	b	c
a	b	a	i121
select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
a1	a2	b
select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
a1	a2	b
a	a	b
b	a	b
c	a	b
c	b	b
d	a	b
d	b	b
e	a	b
select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
a1	a2	b
a	b	a
b	b	a
c	b	a
d	b	a
select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
a1	a2	b	c
a	b	a	i121
select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
a1	a2	b
explain select distinct a1,a2,b from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using index for group-by
explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	65	100.00	Using where; Using index for group-by
Warnings:
Note	1003	select distinct `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`b` = 'a' and `test`.`t1`.`c` = 'i121' and `test`.`t1`.`a2` >= 'b'
explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	13	Using where; Using index for group-by
explain select distinct b from t1 where (a2 >= 'b') and (b = 'a');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	512	Using where; Using index
explain select distinct a1,a2,b from t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using index for group-by
explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	69	100.00	Using where; Using index for group-by
Warnings:
Note	1003	select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where `test`.`t2`.`b` = 'a' and `test`.`t2`.`c` = 'i121' and `test`.`t2`.`a2` >= 'b'
explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
explain select distinct b from t2 where (a2 >= 'b') and (b = 'a');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	idx_t2_2	146	NULL	548	Using where; Using index
select distinct a1,a2,b from t1;
a1	a2	b
a	a	a
a	a	b
a	b	a
a	b	b
b	a	a
b	a	b
b	b	a
b	b	b
c	a	a
c	a	b
c	b	a
c	b	b
d	a	a
d	a	b
d	b	a
d	b	b
select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
a1	a2	b
a	b	a
b	b	a
c	b	a
d	b	a
select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
a1	a2	b	c
a	b	a	i121
select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
a1	a2	b
select distinct b from t1 where (a2 >= 'b') and (b = 'a');
b
a
select distinct a1,a2,b from t2;
a1	a2	b
a	a	NULL
a	a	a
a	a	b
a	b	a
a	b	b
b	a	a
b	a	b
b	b	a
b	b	b
c	a	NULL
c	a	a
c	a	b
c	b	a
c	b	b
d	a	a
d	a	b
d	b	a
d	b	b
e	a	a
e	a	b
select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
a1	a2	b
a	b	a
b	b	a
c	b	a
d	b	a
select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
a1	a2	b	c
a	b	a	i121
select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
a1	a2	b
select distinct b from t2 where (a2 >= 'b') and (b = 'a');
b
a
select distinct t_00.a1
from t1 t_00
where exists ( select * from t2 where a1 = t_00.a1 );
a1
a
b
c
d
select distinct a1,a1 from t1;
a1	a1
a	a
b	b
c	c
d	d
select distinct a2,a1,a2,a1 from t1;
a2	a1	a2	a1
a	a	a	a
b	a	b	a
a	b	a	b
b	b	b	b
a	c	a	c
b	c	b	c
a	d	a	d
b	d	b	d
select distinct t1.a1,t2.a1 from t1,t2;
a1	a1
a	a
b	a
c	a
d	a
a	b
b	b
c	b
d	b
a	c
b	c
c	c
d	c
a	d
b	d
c	d
d	d
a	e
b	e
c	e
d	e
explain select distinct a1,a2,b from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using index for group-by
explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	13	Using where; Using index for group-by
explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by; Using temporary; Using filesort
explain select distinct a1,a2,b from t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using index for group-by
explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by; Using temporary; Using filesort
select distinct a1,a2,b from t1;
a1	a2	b
a	a	a
a	a	b
a	b	a
a	b	b
b	a	a
b	a	b
b	b	a
b	b	b
c	a	a
c	a	b
c	b	a
c	b	b
d	a	a
d	a	b
d	b	a
d	b	b
select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
a1	a2	b
a	b	a
b	b	a
c	b	a
d	b	a
select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
a1	a2	b	c
a	b	a	i121
select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
a1	a2	b
select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
b
a
select distinct a1,a2,b from t2;
a1	a2	b
a	a	NULL
a	a	a
a	a	b
a	b	a
a	b	b
b	a	a
b	a	b
b	b	a
b	b	b
c	a	NULL
c	a	a
c	a	b
c	b	a
c	b	b
d	a	a
d	a	b
d	b	a
d	b	b
e	a	a
e	a	b
select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
a1	a2	b
a	b	a
b	b	a
c	b	a
d	b	a
select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
a1	a2	b	c
a	b	a	i121
select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
a1	a2	b
select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
b
a
explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	65	Using where; Using index for group-by
explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	13	100.00	Using where; Using index for group-by
Warnings:
Note	1003	select count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`) AS `count(distinct a1,a2,b)` from `test`.`t1` where `test`.`t1`.`b` = 'c' and `test`.`t1`.`a1` > 'a' and `test`.`t1`.`a2` > 'a'
explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	512	Using where; Using index
explain extended select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	13	100.00	Using where; Using index for group-by
Warnings:
Note	1003	select 98 + count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`) AS `98 + count(distinct a1,a2,b)` from `test`.`t1` where `test`.`t1`.`a1` > 'a' and `test`.`t1`.`a2` > 'a'
select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
count(distinct a1,a2,b)
4
select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
count(distinct a1,a2,b,c)
1
select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
count(distinct a1,a2,b)
0
select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
count(distinct b)
1
select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
98 + count(distinct a1,a2,b)
104
explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	12	Using where; Using index for group-by
explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	12	Using where; Using index for group-by
explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	12	Using where; Using index for group-by
explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	12	Using where; Using index for group-by
explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	9	Using index for group-by
select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
a1	a2	b	concat(min(c), max(c))
a	a	a	a111d111
a	a	b	e112h112
a	b	a	i121l121
a	b	b	m122p122
b	a	a	a211d211
b	a	b	e212h212
b	b	a	i221l221
b	b	b	m222p222
c	a	a	a311d311
c	a	b	e312h312
c	b	a	i321l321
c	b	b	m322p322
select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
concat(a1,min(c))	b
aa111	a
ae112	b
ai121	a
am122	b
ba211	a
be212	b
bi221	a
bm222	b
ca311	a
ce312	b
ci321	a
cm322	b
select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
concat(a1,min(c))	b	max(c)
aa111	a	d111
ae112	b	h112
ai121	a	l121
am122	b	p122
ba211	a	d211
be212	b	h212
bi221	a	l221
bm222	b	p222
ca311	a	d311
ce312	b	h312
ci321	a	l321
cm322	b	p322
select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
concat(a1,a2)	b	min(c)	max(c)
aa	a	a111	d111
aa	b	e112	h112
ab	a	i121	l121
ab	b	m122	p122
ba	a	a211	d211
ba	b	e212	h212
bb	a	i221	l221
bb	b	m222	p222
ca	a	a311	d311
ca	b	e312	h312
cb	a	i321	l321
cb	b	m322	p322
select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
concat(ord(min(b)),ord(max(b)))	min(b)	max(b)
9798	a	b
9798	a	b
9798	a	b
9798	a	b
9798	a	b
9798	a	b
9798	a	b
9798	a	b
explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	512	Using temporary; Using filesort
explain select a1,a2,b,d from t1 group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	512	Using temporary; Using filesort
explain extended select a1,a2,min(b),max(b) from t1
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	130	NULL	276	96.30	Using where; Using index
Warnings:
Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,min(`test`.`t1`.`b`) AS `min(b)`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`c` > 'a111' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`
explain extended select a1,a2,b,min(c),max(c) from t1
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	idx_t1_0,idx_t1_1,idx_t1_2	NULL	NULL	NULL	512	26.37	Using where; Using temporary; Using filesort
Warnings:
Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,min(`test`.`t1`.`c`) AS `min(c)`,max(`test`.`t1`.`c`) AS `max(c)` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`d` > 'xy2' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
explain extended select a1,a2,b,c from t1
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	idx_t1_0,idx_t1_1,idx_t1_2	NULL	NULL	NULL	512	26.37	Using where; Using temporary; Using filesort
Warnings:
Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`d` > 'xy2' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`,`test`.`t1`.`c`
explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	548	Using where; Using index
explain extended select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	130	NULL	276	96.30	Using where; Using index
Warnings:
Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`c` > 'a111' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	548	Using where; Using index
select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
a1	a2	min(b)	c
a	a	a	a111
explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	548	Using where; Using index
explain select a1,a2,b,min(c),max(c) from t2
where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	548	Using where; Using index
explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	512	Using temporary; Using filesort
explain select a1,a2,count(a2) from t1 group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	512	Using index
explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	65	NULL	392	100.00	Using where; Using index
Warnings:
Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where `test`.`t1`.`a1` > 'a' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	65	NULL	392	100.00	Using where; Using index
Warnings:
Note	1003	select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where `test`.`t1`.`a1` > 'a' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
create table t4 as select distinct a1, a2, b, c from t1;
alter table t4 add unique index idxt4 (a1, a2, b, c);
# This is "superceded" by MDEV-7118, and Loose Index Scan is again an option:
explain
select a1, a2, b, min(c) from t4 group by a1, a2, b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t4	range	NULL	idxt4	147	NULL	10	Using index for group-by
select a1, a2, b, min(c) from t4 group by a1, a2, b;
a1	a2	b	min(c)
a	a	a	a111
a	a	b	e112
a	b	a	i121
a	b	b	m122
b	a	a	a211
b	a	b	e212
b	b	a	i221
b	b	b	m222
c	a	a	a311
c	a	b	e312
c	b	a	i321
c	b	b	m322
d	a	a	a411
d	a	b	e412
d	b	a	i421
d	b	b	m422
drop table t4;
explain select distinct(a1) from t1 where ord(a2) = 98;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	512	Using where; Using index
select distinct(a1) from t1 where ord(a2) = 98;
a1
a
b
c
d
explain select a1 from t1 where a2 = 'b' group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	5	Using where; Using index for group-by
select a1 from t1 where a2 = 'b' group by a1;
a1
a
b
c
d
explain select distinct a1 from t1 where a2 = 'b';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	5	Using where; Using index for group-by
select distinct a1 from t1 where a2 = 'b';
a1
a
b
c
d
drop table t1,t2,t3;
create table t1 (c1 int not null,c2 int not null, primary key(c1,c2));
insert into t1 (c1,c2) values
(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9);
select distinct c1, c2 from t1 order by c2;
c1	c2
10	1
10	2
10	3
20	4
20	5
20	6
30	7
30	8
30	9
select c1,min(c2) as c2 from t1 group by c1 order by c2;
c1	c2
10	1
20	4
30	7
select c1,c2 from t1 group by c1,c2 order by c2;
c1	c2
10	1
10	2
10	3
20	4
20	5
20	6
30	7
30	8
30	9
drop table t1;
CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b));
INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4);
OPTIMIZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	OK
SELECT a FROM t1 WHERE a='AA' GROUP BY a;
a
AA
SELECT a FROM t1 WHERE a='BB' GROUP BY a;
a
BB
EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	7	NULL	1	Using where; Using index for group-by
EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	7	NULL	1	Using where; Using index for group-by
SELECT DISTINCT a FROM t1 WHERE a='BB';
a
BB
SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%';
a
BB
SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a;
a
BB
DROP TABLE t1;
CREATE TABLE t1 (
a int(11) NOT NULL DEFAULT '0',
b varchar(16) COLLATE latin1_general_ci NOT NULL DEFAULT '',
PRIMARY KEY  (a,b)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
CREATE PROCEDURE a(x INT)
BEGIN
DECLARE rnd INT;
DECLARE cnt INT;
WHILE x > 0 DO
SET rnd= x % 100;
SET cnt = (SELECT COUNT(*) FROM t1 WHERE a = rnd);
INSERT INTO t1(a,b) VALUES (rnd, CAST(cnt AS CHAR));
SET x= x - 1;
END WHILE;
END|
CALL a(1000);
SELECT a FROM t1 WHERE a=0;
a
0
0
0
0
0
0
0
0
0
0
SELECT DISTINCT a FROM t1 WHERE a=0;
a
0
SELECT COUNT(DISTINCT a) FROM t1 WHERE a=0;
COUNT(DISTINCT a)
1
DROP TABLE t1;
DROP PROCEDURE a;
CREATE TABLE t1 (a varchar(64) NOT NULL default '', KEY(a));
INSERT INTO t1 (a) VALUES 
(''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'),
('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'),
('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN');
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	a	66	NULL	12	Using index for group-by
SELECT DISTINCT a,a FROM t1 ORDER BY a;
a	a
	
CENTRAL	CENTRAL
EASTERN	EASTERN
GREATER LONDON	GREATER LONDON
NORTH CENTRAL	NORTH CENTRAL
NORTH EAST	NORTH EAST
NORTH WEST	NORTH WEST
SCOTLAND	SCOTLAND
SOUTH EAST	SOUTH EAST
SOUTH WEST	SOUTH WEST
WESTERN	WESTERN
DROP TABLE t1;
CREATE TABLE t1 (id1 INT, id2 INT);
CREATE TABLE t2 (id2 INT, id3 INT, id5 INT);
CREATE TABLE t3 (id3 INT, id4 INT);
CREATE TABLE t4 (id4 INT);
CREATE TABLE t5 (id5 INT, id6 INT);
CREATE TABLE t6 (id6 INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1,1);
INSERT INTO t3 VALUES(1,1);
INSERT INTO t4 VALUES(1);
INSERT INTO t5 VALUES(1,1);
INSERT INTO t6 VALUES(1);
SELECT * FROM
t1
NATURAL JOIN
(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
id2	id1	id3	id5	id4	id3	id6	id5
1	1	1	1	1	1	1	1
SELECT * FROM
t1
NATURAL JOIN
(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2
ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
id2	id1	id4	id3	id6	id5	id3	id5
1	1	1	1	1	1	1	1
SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2);
id2	id1	id3	id4	id6	id5	id3	id5
1	1	1	1	1	1	1	1
SELECT * FROM
(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5))
NATURAL JOIN
t1;
id2	id3	id5	id4	id3	id6	id5	id1
1	1	1	1	1	1	1	1
SELECT * FROM
(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6)))
NATURAL JOIN
t1;
id2	id3	id5	id4	id3	id6	id5	id1
1	1	1	1	1	1	1	1
DROP TABLE t1,t2,t3,t4,t5,t6;
#
# Bug#22342: No results returned for query using max and group by
#
CREATE TABLE t1 (a int, b int, KEY (a,b), KEY b (b));
INSERT INTO t1 VALUES
(1,1),(1,2),(1,0),(1,3),
(1,-1),(1,-2),(1,-3),(1,-4);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
INSERT INTO t2 SELECT a,b,b FROM t1;
explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a,b	a	10	NULL	1	Using where; Using index for group-by
insert into t1 select 1,seq from seq_1_to_100;
explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a,b	a	10	NULL	1	Using where; Using index for group-by
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a,b	a	10	NULL	1	Using where; Using index for group-by
SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
MAX(b)	a
1	1
SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
MIN(b)	a
2	1
explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	PRIMARY	PRIMARY	12	NULL	1	Using where; Using index for group-by
SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
MIN(c)
2
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	a	5	NULL	6	Using index for group-by
FLUSH STATUS;
SELECT max(b), a FROM t1 GROUP BY a;
max(b)	a
5	1
3	2
1	3
6	4
SHOW STATUS LIKE 'handler_read__e%';
Variable_name	Value
Handler_read_key	8
Handler_read_next	0
Handler_read_retry	0
EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	a	5	NULL	6	Using index for group-by
FLUSH STATUS;
CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a;
SHOW STATUS LIKE 'handler_read__e%';
Variable_name	Value
Handler_read_key	8
Handler_read_next	0
Handler_read_retry	0
FLUSH STATUS;
SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
max(b)	a
5	1
3	2
1	3
6	4
SHOW STATUS LIKE 'handler_read__e%';
Variable_name	Value
Handler_read_key	8
Handler_read_next	0
Handler_read_retry	0
FLUSH STATUS;
(SELECT max(b), a FROM t1 GROUP BY a) UNION 
(SELECT max(b), a FROM t1 GROUP BY a);
max(b)	a
5	1
3	2
1	3
6	4
SHOW STATUS LIKE 'handler_read__e%';
Variable_name	Value
Handler_read_key	16
Handler_read_next	0
Handler_read_retry	0
EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION 
(SELECT max(b), a FROM t1 GROUP BY a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	range	NULL	a	5	NULL	6	Using index for group-by
2	UNION	t1	range	NULL	a	5	NULL	6	Using index for group-by
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
FROM t1 AS t1_outer;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1_outer	index	NULL	a	10	NULL	15	Using index
2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index for group-by
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS 
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1_outer	index	NULL	a	10	NULL	15	Using index
2	SUBQUERY	t1	index	NULL	a	10	NULL	15	Using index
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index for group-by
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
1	PRIMARY	t1_outer	ref	a	a	5	<subquery2>.max(b)	3	Using index
2	MATERIALIZED	t1	range	a	a	5	NULL	2	Using where; Using index for group-by
EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING 
a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1_outer	range	NULL	a	5	NULL	6	Using index for group-by
2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index for group-by
EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 
ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) 
AND t1_outer1.b = t1_outer2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1_outer1	ref	a	a	5	const	1	Using where; Using index
1	PRIMARY	t1_outer2	index	NULL	a	10	NULL	15	Using where; Using index; Using join buffer (flat, BNL join)
2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index for group-by
EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1_outer2	index	NULL	a	10	NULL	15	Using index
2	SUBQUERY	t1_outer	index	NULL	a	10	NULL	15	Using index
3	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index for group-by
CREATE TABLE t3 LIKE t1;
FLUSH STATUS;
INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;
SHOW STATUS LIKE 'handler_read__e%';
Variable_name	Value
Handler_read_key	13
Handler_read_next	0
Handler_read_retry	0
DELETE FROM t3;
FLUSH STATUS;
INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) 
FROM t1 LIMIT 1;
SHOW STATUS LIKE 'handler_read__e%';
Variable_name	Value
Handler_read_key	8
Handler_read_next	0
Handler_read_retry	0
FLUSH STATUS;
DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
SHOW STATUS LIKE 'handler_read__e%';
Variable_name	Value
Handler_read_key	8
Handler_read_next	0
Handler_read_retry	0
FLUSH STATUS;
DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x 
FROM t1) > 10000;
ERROR 21000: Subquery returns more than 1 row
SHOW STATUS LIKE 'handler_read__e%';
Variable_name	Value
Handler_read_key	8
Handler_read_next	1
Handler_read_retry	0
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (a int, INDEX idx(a));
INSERT INTO t1 VALUES
(4), (2), (1), (2), (4), (2), (1), (4),
(4), (2), (1), (2), (2), (4), (1), (4);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
EXPLAIN SELECT DISTINCT(a) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx	5	NULL	4	Using index for group-by
SELECT DISTINCT(a) FROM t1;
a
1
2
4
EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	idx	5	NULL	4	Using index for group-by
SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
a
1
2
4
DROP TABLE t1;
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5);
INSERT INTO t1 SELECT a + 1, b FROM t1;
INSERT INTO t1 SELECT a + 2, b FROM t1;
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
EXPLAIN
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	20	Using temporary; Using filesort
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
a	MIN(b)	MAX(b)
4	1	5
3	1	5
2	1	5
1	1	5
CREATE INDEX break_it ON t1 (a, b);
EXPLAIN
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	break_it	10	NULL	7	Using index for group-by
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
a	MIN(b)	MAX(b)
1	1	5
2	1	5
3	1	5
4	1	5
EXPLAIN
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	break_it	10	NULL	7	Using index for group-by; Using temporary; Using filesort
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
a	MIN(b)	MAX(b)
4	1	5
3	1	5
2	1	5
1	1	5
EXPLAIN
SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	break_it	10	NULL	20	Using index
SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
a	MIN(b)	MAX(b)	AVG(b)
4	1	5	3.0000
3	1	5	3.0000
2	1	5	3.0000
1	1	5	3.0000
DROP TABLE t1;
create table t1 (a int, b int, key (a,b), key `index` (a,b)) engine=MyISAM;
Warnings:
Note	1831	Duplicate index `index`. This is deprecated and will be disallowed in a future release
insert into  t1 (a,b) values 
(0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6),
(0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13),
(1,0),(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),
(1,7),(1,8),(1,9),(1,10),(1,11),(1,12),(1,13),
(2,0),(2,1),(2,2),(2,3),(2,4),(2,5),(2,6),
(2,7),(2,8),(2,9),(2,10),(2,11),(2,12),(2,13),
(3,0),(3,1),(3,2),(3,3),(3,4),(3,5),(3,6),
(3,7),(3,8),(3,9),(3,10),(3,11),(3,12),(3,13);
insert into t1 (a,b) select a, max(b)+1 from t1 where a = 0 group by a;
select * from t1;
a	b
0	0
0	1
0	2
0	3
0	4
0	5
0	6
0	7
0	8
0	9
0	10
0	11
0	12
0	13
0	14
1	0
1	1
1	2
1	3
1	4
1	5
1	6
1	7
1	8
1	9
1	10
1	11
1	12
1	13
2	0
2	1
2	2
2	3
2	4
2	5
2	6
2	7
2	8
2	9
2	10
2	11
2	12
2	13
3	0
3	1
3	2
3	3
3	4
3	5
3	6
3	7
3	8
3	9
3	10
3	11
3	12
3	13
explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	a,index	a	5	NULL	1	100.00	Using where; Using index for group-by; Using temporary
Warnings:
Note	1003	select sql_buffer_result `test`.`t1`.`a` AS `a`,max(`test`.`t1`.`b`) + 1 AS `max(b)+1` from `test`.`t1` where `test`.`t1`.`a` = 0 group by `test`.`t1`.`a`
drop table t1;
CREATE TABLE t1 (a int, b int, c int, d int,
KEY foo (c,d,a,b), KEY bar (c,a,b,d));
INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4);
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT a,b,c+1,d FROM t1;
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	foo	10	NULL	3	Using where; Using index for group-by
SELECT DISTINCT c FROM t1 WHERE d=4;
c
1
2
DROP TABLE t1;
#
# Bug #45386: Wrong query result with MIN function in field list, 
#  WHERE and GROUP BY clause
#
CREATE TABLE t (a INT, b INT, INDEX (a,b));
INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
ANALYZE TABLE t;
Table	Op	Msg_type	Msg_text
test.t	analyze	status	Engine-independent statistics collected
test.t	analyze	status	OK
# test MIN
#should use range with index for group by
EXPLAIN
SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	range	NULL	a	10	NULL	2	Using where; Using index for group-by
#should return 1 row
SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
a	MIN(b)
2	1
# test MAX
#should use range with index for group by
EXPLAIN
SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	range	NULL	a	10	NULL	2	Using where; Using index for group-by
#should return 1 row
SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
a	MAX(b)
2	0
# test 3 ranges and use the middle one
INSERT INTO t SELECT a, 2 FROM t;
#should use range with index for group by
EXPLAIN
SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	range	NULL	a	10	NULL	2	Using where; Using index for group-by
#should return 1 row
SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
a	MAX(b)
2	1
DROP TABLE t;
#
# Bug #48472: Loose index scan inappropriately chosen for some WHERE
#             conditions
# 
CREATE TABLE t (a INT, b INT, INDEX (a,b));
INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
INSERT INTO t SELECT * FROM t;
SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a;
a	MAX(b)
2	0
DROP TABLE t;
End of 5.0 tests
#
# Bug #46607: Assertion failed: (cond_type == Item::FUNC_ITEM) results in
#              server crash
#
CREATE TABLE t (a INT, b INT, INDEX (a,b));
INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
INSERT INTO t SELECT * FROM t;
SELECT a, MAX(b) FROM t WHERE b GROUP BY a;
a	MAX(b)
2	1
DROP TABLE t;
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, KEY (b));
INSERT INTO t1 VALUES(1,1),(2,1);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
SELECT 1 AS c, b FROM t1 WHERE b IN (1,2) GROUP BY c, b;
c	b
1	1
SELECT a FROM t1 WHERE b=1;
a
1
2
DROP TABLE t1;
# 
# Bug#47762: Incorrect result from MIN() when WHERE tests NOT NULL column
#            for NULL
#
## Test for NULLs allowed
CREATE TABLE t1 ( a INT, KEY (a) );
INSERT INTO t1 VALUES (1), (2), (3);
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a = NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a = NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a <> NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a <> NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a > NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a > NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a < NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a < NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	No matching min/max row
SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
x	x	x	x	x	x	x	x	x	Using where; Using index
SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	No matching min/max row
SELECT MIN( a ) FROM t1 WHERE a IS NULL;
MIN( a )
NULL
INSERT INTO t1 VALUES (NULL), (NULL);
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a = NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a = NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a <> NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a <> NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a > NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a > NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a < NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a < NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Select tables optimized away
SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
x	x	x	x	x	x	x	x	x	Using where; Using index
SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Select tables optimized away
SELECT MIN( a ) FROM t1 WHERE a IS NULL;
MIN( a )
NULL
DROP TABLE t1;
## Test for NOT NULLs
CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY);
INSERT INTO t1 VALUES (1), (2), (3);
#
# NULL-safe operator test disabled for non-NULL indexed columns.
#
# See bugs
#
# - Bug#52173: Reading NULL value from non-NULL index gives
#   wrong result in embedded server 
#
# - Bug#52174: Sometimes wrong plan when reading a MAX value from 
#   non-NULL index
#
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a = NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a = NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a <> NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Using where; Using index
SELECT MIN( a ) FROM t1 WHERE a <> NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a > NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a > NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a < NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a < NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
x	x	x	x	x	x	x	x	x	Using where; Using index
SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
MIN( a )
NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Impossible WHERE
SELECT MIN( a ) FROM t1 WHERE a IS NULL;
MIN( a )
NULL
DROP TABLE t1;
#
# Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&, Item*) at
# opt_sum.cc:305
#
CREATE TABLE t1 ( a INT, KEY (a) );
INSERT INTO t1 VALUES (1), (2), (3);
SELECT MIN( a ) AS min_a
FROM t1
WHERE a > 1 AND a IS NULL
ORDER BY min_a;
min_a
NULL
DROP TABLE t1;
create table t1 (a int, b varchar(1), key(b,a)) engine=myisam;
insert t1 values (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'g'),(8,'h'),(null,'i');
select min(a), b from t1 where a=7 or b='z' group by b;
min(a)	b
7	g
flush tables;
drop table t1;
#
# LP BUG#888456 Wrong result with DISTINCT , ANY , subquery_cache=off , NOT NULL
#
CREATE TABLE t1 ( a int NOT NULL) ;
INSERT INTO t1 VALUES (28),(29),(9);
CREATE TABLE t2 ( a int, KEY (a)) ;
INSERT INTO t2 VALUES (1),(1),(1),(4),(4),(5),(5),(8),(8),(9);
explain select (select t2.a from t2 where t2.a >= t1.a group by t2.a) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
2	DEPENDENT SUBQUERY	t2	index	a	a	5	NULL	10	Using where; Using index
select (select t2.a from t2 where t2.a >= t1.a group by t2.a) from t1;
(select t2.a from t2 where t2.a >= t1.a group by t2.a)
NULL
NULL
9
drop table t1, t2;
#
# LP BUG#900375 Wrong result with derived_merge=ON, DISTINCT or GROUP BY, EXISTS
#
CREATE TABLE t1 ( a INT, b INT, KEY (b) );
INSERT INTO t1 VALUES
(100,10),(101,11),(102,12),(103,13),(104,14),
(105,15),(106,16),(107,17),(108,18),(109,19);
EXPLAIN
SELECT alias1.* FROM t1, (SELECT * FROM t1) AS alias1
WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	b	5	NULL	10	Using index
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
3	DEPENDENT SUBQUERY	t1	index	b	b	5	NULL	10	Using where; Using index
SELECT alias1.* FROM t1, (SELECT * FROM t1) AS alias1
WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
a	b
100	10
100	10
100	10
100	10
100	10
100	10
100	10
100	10
100	10
100	10
101	11
101	11
101	11
101	11
101	11
101	11
101	11
101	11
101	11
101	11
102	12
102	12
102	12
102	12
102	12
102	12
102	12
102	12
102	12
102	12
103	13
103	13
103	13
103	13
103	13
103	13
103	13
103	13
103	13
103	13
104	14
104	14
104	14
104	14
104	14
104	14
104	14
104	14
104	14
104	14
105	15
105	15
105	15
105	15
105	15
105	15
105	15
105	15
105	15
105	15
106	16
106	16
106	16
106	16
106	16
106	16
106	16
106	16
106	16
106	16
107	17
107	17
107	17
107	17
107	17
107	17
107	17
107	17
107	17
107	17
108	18
108	18
108	18
108	18
108	18
108	18
108	18
108	18
108	18
108	18
109	19
109	19
109	19
109	19
109	19
109	19
109	19
109	19
109	19
109	19
EXPLAIN
SELECT alias1.* FROM t1, t1 AS alias1
WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	b	5	NULL	10	Using index
1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
2	DEPENDENT SUBQUERY	t1	index	b	b	5	NULL	10	Using where; Using index
SELECT alias1.* FROM t1, t1 AS alias1
WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
a	b
100	10
100	10
100	10
100	10
100	10
100	10
100	10
100	10
100	10
100	10
101	11
101	11
101	11
101	11
101	11
101	11
101	11
101	11
101	11
101	11
102	12
102	12
102	12
102	12
102	12
102	12
102	12
102	12
102	12
102	12
103	13
103	13
103	13
103	13
103	13
103	13
103	13
103	13
103	13
103	13
104	14
104	14
104	14
104	14
104	14
104	14
104	14
104	14
104	14
104	14
105	15
105	15
105	15
105	15
105	15
105	15
105	15
105	15
105	15
105	15
106	16
106	16
106	16
106	16
106	16
106	16
106	16
106	16
106	16
106	16
107	17
107	17
107	17
107	17
107	17
107	17
107	17
107	17
107	17
107	17
108	18
108	18
108	18
108	18
108	18
108	18
108	18
108	18
108	18
108	18
109	19
109	19
109	19
109	19
109	19
109	19
109	19
109	19
109	19
109	19
drop table t1;
End of 5.1 tests
#
# MDEV-765 lp:825075 - Wrong result with GROUP BY + multipart key + MIN/MAX loose scan
#
CREATE TABLE t1 (a varchar(1), b varchar(1), KEY (b,a));
INSERT INTO t1 VALUES
('0',NULL),('9',NULL),('8','c'),('4','d'),('7','d'),(NULL,'f'),
('7','f'),('8','g'),(NULL,'j');
explain
SELECT max(a) , b FROM t1 WHERE a IS NULL OR b = 'z' GROUP BY b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	b	b	8	NULL	9	Using where; Using index
SELECT max(a) , b FROM t1 WHERE a IS NULL OR b = 'z' GROUP BY b;
max(a)	b
NULL	f
NULL	j
explain
SELECT b, min(a) FROM t1 WHERE a = '7' OR b = 'z' GROUP BY b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	b	b	8	NULL	9	Using where; Using index
SELECT b, min(a) FROM t1 WHERE a = '7' OR b = 'z' GROUP BY b;
b	min(a)
d	7
f	7
explain
SELECT b, min(a) FROM t1 WHERE (a = b OR b = 'd' OR b is NULL) GROUP BY b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	b	b	8	NULL	9	Using where; Using index
SELECT b, min(a) FROM t1 WHERE (a = b OR b = 'd' OR b is NULL) GROUP BY b;
b	min(a)
NULL	0
d	4
explain
SELECT b, min(a) FROM t1 WHERE a > ('0' = b) AND b = 'z' GROUP BY b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	b	b	4	const	1	Using where; Using index
SELECT b, min(a) FROM t1 WHERE a > ('0' = b) AND b = 'z' GROUP BY b;
b	min(a)
explain
SELECT b, min(a) FROM t1 WHERE a > '0' AND (b < (a = '7')) GROUP BY b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	b	8	NULL	9	Using where; Using index
SELECT b, min(a) FROM t1 WHERE a > '0' AND (b < (a = '7')) GROUP BY b;
b	min(a)
d	7
f	7
Warnings:
Warning	1292	Truncated incorrect DECIMAL value: 'c'
Warning	1292	Truncated incorrect DECIMAL value: 'd'
Warning	1292	Truncated incorrect DECIMAL value: 'd'
Warning	1292	Truncated incorrect DECIMAL value: 'f'
Warning	1292	Truncated incorrect DECIMAL value: 'g'
explain
SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	b	b	8	NULL	9	Using where; Using index
SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b;
b	min(a)
NULL	9
c	8
d	4
f	7
g	8
drop table t1;
#
# MDEV-4140 Wrong result with GROUP BY + multipart key + MIN/MAX loose scan and a subquery
#
CREATE TABLE t1 (a int, b int, KEY (b, a)) ;
INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
CREATE TABLE t2 (c int) ;
INSERT INTO t2 VALUES (0),(1);
ANALYZE TABLE t1,t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	OK
EXPLAIN
SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	b	10	NULL	3	Using where; Using index for group-by
SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
MIN(a)	b
1	0
9	99
EXPLAIN
SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	range	NULL	b	10	NULL	3	Using where; Using index for group-by
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
MIN(a)	b
1	0
9	99
EXPLAIN
SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	b	10	NULL	18	Using where; Using index
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
2	SUBQUERY	t1a	index	NULL	b	10	NULL	18	Using index; Using join buffer (flat, BNL join)
2	SUBQUERY	t1b	index	NULL	b	10	NULL	18	Using index; Using join buffer (incremental, BNL join)
SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
MIN(a)	b
1	0
9	99
drop table t1, t2;
End of 5.3 tests
#
# WL#3220 (Loose index scan for COUNT DISTINCT)
#
CREATE TABLE t1 (a INT, b INT, c INT, KEY (a,b));
INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1), (1,4,1);
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT a, b + 4, 1 FROM t1;
INSERT INTO t1 SELECT a, b + 8, 1 FROM t1;
INSERT INTO t1 SELECT a + 1, b, 1 FROM t1;
CREATE TABLE t2 (a INT, b INT, c INT, d INT, e INT, f INT, KEY (a,b,c));
INSERT INTO t2 VALUES
(1,1,1,1,1,1), (1,2,1,1,1,1), (1,3,1,1,1,1), (1,4,1,1,1,1);
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT a, b + 4, c,d,e,f FROM t2;
INSERT INTO t2 SELECT a + 1, b, c,d,e,f FROM t2;
ANALYZE TABLE t1,t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	OK
EXPLAIN SELECT COUNT(DISTINCT a) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	a	5	NULL	3	Using index for group-by
SELECT COUNT(DISTINCT a) FROM t1;
COUNT(DISTINCT a)
2
EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	a	10	NULL	33	Using index for group-by
SELECT COUNT(DISTINCT a,b) FROM t1;
COUNT(DISTINCT a,b)
32
EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	a	10	NULL	33	Using index for group-by
SELECT COUNT(DISTINCT b,a) FROM t1;
COUNT(DISTINCT b,a)
32
EXPLAIN SELECT COUNT(DISTINCT b) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	10	NULL	256	Using index
SELECT COUNT(DISTINCT b) FROM t1;
COUNT(DISTINCT b)
16
EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	a	5	NULL	3	Using index for group-by
SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a;
COUNT(DISTINCT a)
1
1
EXPLAIN SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	a	10	NULL	33	Using index for group-by
SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
COUNT(DISTINCT b)
16
16
EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	10	NULL	256	Using index; Using filesort
SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;
COUNT(DISTINCT a)
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
EXPLAIN SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	10	NULL	256	Using index
SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
COUNT(DISTINCT a)
2
EXPLAIN SELECT COUNT(DISTINCT a, b + 0) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	10	NULL	256	Using index
SELECT COUNT(DISTINCT a, b + 0) FROM t1;
COUNT(DISTINCT a, b + 0)
32
EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	10	NULL	256	Using index
SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10;
COUNT(DISTINCT a)
EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	256	
SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10;
COUNT(DISTINCT a)
2
EXPLAIN SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	a	5	NULL	3	Using index for group-by
SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10;
1
1
EXPLAIN SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	a	10	NULL	33	Using index for group-by
SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1;
1
1
1
EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1_1	index	NULL	a	10	NULL	256	Using index; Using temporary; Using filesort
1	SIMPLE	t1_2	index	NULL	a	10	NULL	256	Using index; Using join buffer (flat, BNL join)
SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
COUNT(DISTINCT t1_1.a)
1
1
EXPLAIN SELECT COUNT(DISTINCT a), 12 FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	a	5	NULL	3	Using index for group-by
SELECT COUNT(DISTINCT a), 12 FROM t1;
COUNT(DISTINCT a)	12
2	12
EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	a	15	NULL	17	Using index for group-by
SELECT COUNT(DISTINCT a, b, c) FROM t2;
COUNT(DISTINCT a, b, c)
16
EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	a	5	NULL	3	Using index for group-by
SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2;
COUNT(DISTINCT a)	SUM(DISTINCT a)	AVG(DISTINCT a)
2	3	1.5000
EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	256	
SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2;
COUNT(DISTINCT a)	SUM(DISTINCT a)	AVG(DISTINCT f)
2	3	1.0000
EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	a	10	NULL	17	Using index for group-by
SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;
COUNT(DISTINCT a, b)	COUNT(DISTINCT b, a)
16	16
EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	256	
SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2;
COUNT(DISTINCT a, b)	COUNT(DISTINCT b, f)
16	8
EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	256	
SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2;
COUNT(DISTINCT a, b)	COUNT(DISTINCT b, d)
16	8
EXPLAIN SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	a	15	NULL	17	Using index for group-by
SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c;
a	c	COUNT(DISTINCT c, a, b)
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	1	1
2	1	1
2	1	1
2	1	1
2	1	1
2	1	1
2	1	1
2	1	1
EXPLAIN SELECT COUNT(DISTINCT c, a, b) FROM t2
WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	a	a	15	NULL	1	Using where; Using index for group-by
SELECT COUNT(DISTINCT c, a, b) FROM t2
WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;
COUNT(DISTINCT c, a, b)
EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5
GROUP BY b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ref	a	a	5	const	1	Using where; Using index
SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5
GROUP BY b;
COUNT(DISTINCT b)	SUM(DISTINCT b)
EXPLAIN SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	a	10	NULL	17	Using index for group-by
SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
a	COUNT(DISTINCT b)	SUM(DISTINCT b)
1	8	36
2	8	36
EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	a	10	NULL	17	Using index for group-by
SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
COUNT(DISTINCT b)	SUM(DISTINCT b)
8	36
8	36
EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	256	Using where
SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42;
COUNT(DISTINCT a, b)
0
EXPLAIN SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2
WHERE b = 13 AND c = 42 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	a	15	NULL	3	Using where; Using index for group-by
SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2
WHERE b = 13 AND c = 42 GROUP BY a;
a	COUNT(DISTINCT a)	SUM(DISTINCT a)
EXPLAIN SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	a	15	NULL	256	Using where; Using index
SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42;
COUNT(DISTINCT a, b)	SUM(DISTINCT a)
0	NULL
EXPLAIN SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	a	15	NULL	256	Using index
SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a;
SUM(DISTINCT a)	MAX(b)
1	8
2	8
EXPLAIN SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	NULL	a	15	NULL	17	Using index for group-by
SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c;
42 * (a + c + COUNT(DISTINCT c, a, b))
126
126
126
126
126
126
126
126
168
168
168
168
168
168
168
168
EXPLAIN SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	a	15	NULL	256	Using index
SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a;
(SUM(DISTINCT a) + MAX(b))
9
10
DROP TABLE t1,t2;
# end of WL#3220 tests
#
# Bug#50539: Wrong result when loose index scan is used for an aggregate
#            function with distinct
#
CREATE TABLE t1 (
f1 int(11) NOT NULL DEFAULT '0',
f2 char(1) NOT NULL DEFAULT '',
KEY (f1,f2)
) ;
insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'), 
(3, 'A'), (3, 'B'), (3, 'C'), (3, 'D');
explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	f1	5	NULL	8	Using index for group-by
SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
f1	COUNT(DISTINCT f2)
1	3
2	1
3	4
insert into t1 select seq/10,char(64+mod(seq,4)) from seq_1_to_100;
explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	f1	5	NULL	10	Using index for group-by
SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
f1	COUNT(DISTINCT f2)
0	4
1	4
2	4
3	5
4	4
5	4
6	4
7	4
8	4
9	4
10	4
explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	f1	5	NULL	10	Using index for group-by
drop table t1;
# End of test#50539.
#
# Bug#17217128 -  BAD INTERACTION BETWEEN MIN/MAX AND
#                 "HAVING SUM(DISTINCT)": WRONG RESULTS.
#
CREATE TABLE t (a INT, b INT, KEY(a,b));
INSERT INTO t VALUES (1,1), (2,2), (3,3), (4,4), (1,0), (3,2), (4,5);
ANALYZE TABLE t;
Table	Op	Msg_type	Msg_text
test.t	analyze	status	Engine-independent statistics collected
test.t	analyze	status	OK
SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a;
a	SUM(DISTINCT a)	MIN(b)
1	1	0
2	2	2
3	3	2
4	4	4
EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	index	NULL	a	10	NULL	7	Using index
SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a;
a	SUM(DISTINCT a)	MAX(b)
1	1	1
2	2	2
3	3	3
4	4	5
EXPLAIN SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	index	NULL	a	10	NULL	7	Using index
SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a);
a	MAX(b)
1	1
2	2
3	3
4	5
EXPLAIN SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	index	NULL	a	10	NULL	7	Using index
SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t;
SUM(DISTINCT a)	MIN(b)	MAX(b)
10	0	5
EXPLAIN SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	index	NULL	a	10	NULL	7	Using index
SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a;
a	SUM(DISTINCT a)	MIN(b)	MAX(b)
1	1	0	1
2	2	2	2
3	3	2	3
4	4	4	5
EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	index	NULL	a	10	NULL	7	Using index
DROP TABLE t;
#
# MDEV-4219 A simple select query returns random data (upstream bug#68473)
#
drop table if exists faulty;
CREATE TABLE faulty (
a int(11) unsigned NOT NULL AUTO_INCREMENT,
b int(11) unsigned NOT NULL,
c datetime NOT NULL,
PRIMARY KEY (a),
UNIQUE KEY b_and_c (b,c)
);
INSERT INTO faulty (b, c) VALUES
(1801, '2013-02-15 09:00:00'),
(1802, '2013-02-28 09:00:00'),
(1802, '2013-03-01 09:00:00'),
(5,    '1990-02-15 09:00:00'),
(5,    '2013-02-15 09:00:00'),
(5,    '2009-02-15 17:00:00');
EXPLAIN
SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	faulty	ref	b_and_c	b_and_c	4	const	2	Using where; Using index
SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
b	c
1802	2013-02-28 09:00:00
1802	2013-03-01 09:00:00
drop table faulty;
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3);
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT a + 1, b FROM t1;
INSERT INTO t1 SELECT a + 2, b FROM t1;
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
CREATE INDEX break_it ON t1 (a, b);
EXPLAIN
SELECT distinct a, b FROM t1 where a = '3' ORDER BY b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	break_it	break_it	10	NULL	2	Using where; Using index for group-by; Using filesort
SELECT distinct a, b FROM t1 where a = '3' ORDER BY b;
a	b
3	1
3	2
3	3
drop table t1;
#
# Start of 10.0 tests
#
#
# MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases
#
CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,'2001-01-01');
INSERT INTO t1 VALUES (1,'2001-01-02');
INSERT INTO t1 VALUES (1,'2001-01-03');
INSERT INTO t1 VALUES (1,' 2001-01-04');
INSERT INTO t1 VALUES (2,'2001-01-01');
INSERT INTO t1 VALUES (2,'2001-01-02');
INSERT INTO t1 VALUES (2,'2001-01-03');
INSERT INTO t1 VALUES (2,' 2001-01-04');
INSERT INTO t1 VALUES (3,'2001-01-01');
INSERT INTO t1 VALUES (3,'2001-01-02');
INSERT INTO t1 VALUES (3,'2001-01-03');
INSERT INTO t1 VALUES (3,' 2001-01-04');
INSERT INTO t1 VALUES (4,'2001-01-01');
INSERT INTO t1 VALUES (4,'2001-01-02');
INSERT INTO t1 VALUES (4,'2001-01-03');
INSERT INTO t1 VALUES (4,' 2001-01-04');
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
id	MIN(a)	MAX(a)
1	 2001-01-04	 2001-01-04
2	 2001-01-04	 2001-01-04
3	 2001-01-04	 2001-01-04
4	 2001-01-04	 2001-01-04
ALTER TABLE t1 ADD KEY(id,a);
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
id	MIN(a)	MAX(a)
1	 2001-01-04	 2001-01-04
2	 2001-01-04	 2001-01-04
3	 2001-01-04	 2001-01-04
4	 2001-01-04	 2001-01-04
DROP TABLE t1;
#
# MDEV-17039: Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4
# and use_stat_tables= PREFERABLY
#
CREATE TABLE t1 (a INT, b INT,c INT DEFAULT 0, INDEX (a,b));
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
INSERT INTO t1 SELECT * FROM t1;
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
set @save_use_stat_tables= @@use_stat_tables;
set @@optimizer_use_condition_selectivity=4;
set @@use_stat_tables=PREFERABLY;
explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1  GROUP BY a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	100.00	
1	PRIMARY	t1_outer	ref	a	a	5	<subquery2>.max(b)	7	100.00	Using index
2	MATERIALIZED	t1	range	NULL	a	5	NULL	5	100.00	Using index for group-by
Warnings:
Note	1003	/* select#1 */ select `test`.`t1_outer`.`a` AS `a` from  <materialize> (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where `test`.`t1_outer`.`a` = `<subquery2>`.`max(b)`
set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
set @@use_stat_tables=@save_use_stat_tables;
explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1   GROUP BY a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	100.00	
1	PRIMARY	t1_outer	ref	a	a	5	<subquery2>.max(b)	7	100.00	Using index
2	MATERIALIZED	t1	range	NULL	a	5	NULL	5	100.00	Using index for group-by
Warnings:
Note	1003	/* select#1 */ select `test`.`t1_outer`.`a` AS `a` from  <materialize> (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where `test`.`t1_outer`.`a` = `<subquery2>`.`max(b)`
drop table t1;
#
# End of 10.0 tests
#
#
# Start of 10.1 tests
#
#
# MDEV-6990 GROUP_MIN_MAX optimization is not applied in some cases when it could
#
CREATE TABLE t1 (id INT NOT NULL, a DATE, KEY(id,a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,'2001-01-01');
INSERT INTO t1 VALUES (1,'2001-01-02');
INSERT INTO t1 VALUES (1,'2001-01-03');
INSERT INTO t1 VALUES (1,'2001-01-04');
INSERT INTO t1 VALUES (2,'2001-01-01');
INSERT INTO t1 VALUES (2,'2001-01-02');
INSERT INTO t1 VALUES (2,'2001-01-03');
INSERT INTO t1 VALUES (2,'2001-01-04');
INSERT INTO t1 VALUES (3,'2001-01-01');
INSERT INTO t1 VALUES (3,'2001-01-02');
INSERT INTO t1 VALUES (3,'2001-01-03');
INSERT INTO t1 VALUES (3,'2001-01-04');
INSERT INTO t1 VALUES (4,'2001-01-01');
INSERT INTO t1 VALUES (4,'2001-01-02');
INSERT INTO t1 VALUES (4,'2001-01-03');
INSERT INTO t1 VALUES (4,'2001-01-04');
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='2001-01-04' GROUP BY id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	id	8	NULL	5	Using where; Using index for group-by
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104.0 GROUP BY id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	id	8	NULL	5	Using where; Using index for group-by
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	id	8	NULL	5	Using where; Using index for group-by
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='2001-01-04' GROUP BY id;
id	MIN(a)	MAX(a)
1	2001-01-04	2001-01-04
2	2001-01-04	2001-01-04
3	2001-01-04	2001-01-04
4	2001-01-04	2001-01-04
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104.0 GROUP BY id;
id	MIN(a)	MAX(a)
1	2001-01-04	2001-01-04
2	2001-01-04	2001-01-04
3	2001-01-04	2001-01-04
4	2001-01-04	2001-01-04
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id;
id	MIN(a)	MAX(a)
1	2001-01-04	2001-01-04
2	2001-01-04	2001-01-04
3	2001-01-04	2001-01-04
4	2001-01-04	2001-01-04
DROP TABLE t1;
#
# MDEV-8229 GROUP_MIN_MAX is erroneously applied for BETWEEN in some cases
#
SET NAMES latin1;
CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,'2001-01-01');
INSERT INTO t1 VALUES (1,'2001-01-02');
INSERT INTO t1 VALUES (1,'2001-01-03');
INSERT INTO t1 VALUES (1,' 2001-01-04');
INSERT INTO t1 VALUES (2,'2001-01-01');
INSERT INTO t1 VALUES (2,'2001-01-02');
INSERT INTO t1 VALUES (2,'2001-01-03');
INSERT INTO t1 VALUES (2,' 2001-01-04');
INSERT INTO t1 VALUES (3,'2001-01-01');
INSERT INTO t1 VALUES (3,'2001-01-02');
INSERT INTO t1 VALUES (3,'2001-01-03');
INSERT INTO t1 VALUES (3,' 2001-01-04');
INSERT INTO t1 VALUES (4,'2001-01-01');
INSERT INTO t1 VALUES (4,'2001-01-02');
INSERT INTO t1 VALUES (4,'2001-01-03');
INSERT INTO t1 VALUES (4,' 2001-01-04');
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     ' 2001-01-04' AND    '2001-01-05' GROUP BY id;
id	MIN(a)	MAX(a)
1	 2001-01-04	2001-01-03
2	 2001-01-04	2001-01-03
3	 2001-01-04	2001-01-03
4	 2001-01-04	2001-01-03
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     '2001-01-04' AND     '2001-01-05' GROUP BY id;
id	MIN(a)	MAX(a)
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;
id	MIN(a)	MAX(a)
1	 2001-01-04	 2001-01-04
2	 2001-01-04	 2001-01-04
3	 2001-01-04	 2001-01-04
4	 2001-01-04	 2001-01-04
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND     '2001-01-05' GROUP BY id;
id	MIN(a)	MAX(a)
1	 2001-01-04	 2001-01-04
2	 2001-01-04	 2001-01-04
3	 2001-01-04	 2001-01-04
4	 2001-01-04	 2001-01-04
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     '2001-01-04' AND DATE'2001-01-05' GROUP BY id;
id	MIN(a)	MAX(a)
1	 2001-01-04	 2001-01-04
2	 2001-01-04	 2001-01-04
3	 2001-01-04	 2001-01-04
4	 2001-01-04	 2001-01-04
ALTER TABLE t1 ADD KEY(id,a);
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     ' 2001-01-04' AND    '2001-01-05' GROUP BY id;
id	MIN(a)	MAX(a)
1	 2001-01-04	2001-01-03
2	 2001-01-04	2001-01-03
3	 2001-01-04	2001-01-03
4	 2001-01-04	2001-01-03
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     '2001-01-04' AND     '2001-01-05' GROUP BY id;
id	MIN(a)	MAX(a)
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;
id	MIN(a)	MAX(a)
1	 2001-01-04	 2001-01-04
2	 2001-01-04	 2001-01-04
3	 2001-01-04	 2001-01-04
4	 2001-01-04	 2001-01-04
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND     '2001-01-05' GROUP BY id;
id	MIN(a)	MAX(a)
1	 2001-01-04	 2001-01-04
2	 2001-01-04	 2001-01-04
3	 2001-01-04	 2001-01-04
4	 2001-01-04	 2001-01-04
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     '2001-01-04' AND DATE'2001-01-05' GROUP BY id;
id	MIN(a)	MAX(a)
1	 2001-01-04	 2001-01-04
2	 2001-01-04	 2001-01-04
3	 2001-01-04	 2001-01-04
4	 2001-01-04	 2001-01-04
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     ' 2001-01-04' AND    '2001-01-05' GROUP BY id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	id	27	NULL	10	Using where; Using index for group-by
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     '2001-01-04' AND     '2001-01-05' GROUP BY id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	id	27	NULL	10	Using where; Using index for group-by
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	id	27	NULL	64	Using where; Using index
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND     '2001-01-05' GROUP BY id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	id	27	NULL	64	Using where; Using index
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     '2001-01-04' AND DATE'2001-01-05' GROUP BY id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	id	27	NULL	64	Using where; Using index
DROP TABLE t1;
#
# MIN() optimization didn't work correctly with BETWEEN when using too
# long strings.
#
create table t1 (a varchar(10), key (a)) engine=myisam;
insert into t1 values("bar"),("Cafe");
explain select min(a) from t1 where a between "a" and "Cafe2";
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
explain select min(a) from t1 where a between "a" and "Cafeeeeeeeeeeeeeeeeeeeeeeeeee";
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	13	NULL	2	Using where; Using index
explain select min(a) from t1 where a between "abbbbbbbbbbbbbbbbbbbb" and "Cafe2";
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	13	NULL	2	Using where; Using index
drop table t1;
#
# MDEV-15433: Optimizer does not use group by optimization with distinct
#
CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a));
OPTIMIZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	OK
EXPLAIN SELECT DISTINCT a FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	a	4	NULL	5	Using index for group-by
SELECT DISTINCT a FROM t1;
a
1
2
3
4
drop table t1;
#
# End of 10.1 tests
#
#
# MDEV-27442 Wrong result upon query with DISTINCT and EXISTS subquery
#
CREATE TABLE t1 (a int, b int, KEY b (b,a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (0,100),(2,100),(2,101),(3,102);
# Must not use Using index for group-by
explain SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 );
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	NULL	b	10	NULL	4	Using where; Using index
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 );
b
100
101
102
DROP TABLE t1;
#
# MDEV-26585 Wrong query results when `using index for group-by`
#
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`owner_id` int(11) DEFAULT NULL,
`foo` tinyint(1) DEFAULT 0,
`whatever` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_t1_on_owner_id_and_foo` (`owner_id`,`foo`)
) engine=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO t1 (owner_id, foo, whatever)
VALUES (1, TRUE, "yello"), (1, FALSE, "yello"), (2, TRUE, "yello"),
(2, TRUE, "yello"), (2, FALSE, "yello");
EXPLAIN 
SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	index_t1_on_owner_id_and_foo	7	NULL	#	Using where; Using index
SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
owner_id
1
DROP TABLE t1;
#
# MDEV-30240 Wrong result upon aggregate function with SQL_BUFFER_RESULT
#
drop table if exists t1,t2;
Warnings:
Note	1051	Unknown table 'test.t1,test.t2'
CREATE TABLE t1 (pk INT PRIMARY KEY);
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (a INT);
INSERT INTO t2 VALUES (1),(2);
SELECT SQL_BUFFER_RESULT MIN(pk) FROM t1, t2;
MIN(pk)
1
SELECT MIN(pk) FROM t1, t2;
MIN(pk)
1
DROP TABLE t1, t2;
#
# End of 10.5 tests
#
#
# MDEV-24353: Adding GROUP BY slows down a query
#
CREATE TABLE t1 (p int NOT NULL, a int NOT NULL, PRIMARY KEY (p,a));
insert into t1 select 2,seq from seq_0_to_1000;
EXPLAIN select MIN(a) from t1 where p = 2 group by p;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using where; Using index for group-by
SELECT MIN(a) from t1 where p = 2 group by p;
MIN(a)
0
EXPLAIN select MIN(a) from t1 group by p;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	PRIMARY	4	NULL	10	Using index for group-by
SELECT MIN(a) from t1 where p = 2;
MIN(a)
0
drop table t1;
#
# End of 10.6 tests
#