1set @debug_tmp= @@debug_dbug;
2set global debug_dbug="+d,force_group_by";
3drop table if exists t1;
4create table t1 (
5a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' '
6) engine=RocksDB;
7insert into t1 (a1, a2, b, c, d) values
8('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
9('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
10('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
11('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
12('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
13('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
14('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
15('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
16('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
17('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
18('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
19('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
20('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
21('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
22('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
23('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
24('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
25('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
26('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
27('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
28('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
29('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
30('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
31('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
32('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
33('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
34('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
35('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
36('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
37('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
38('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
39('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
40create index idx_t1_0 on t1 (a1);
41create index idx_t1_1 on t1 (a1,a2,b,c);
42create index idx_t1_2 on t1 (a1,a2,b);
43analyze table t1;
44Table	Op	Msg_type	Msg_text
45test.t1	analyze	status	OK
46drop table if exists t2;
47create table t2 (
48a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(248) default ' '
49) engine=RocksDB;
50insert into t2 select * from t1;
51insert into t2 (a1, a2, b, c, d) values
52('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
53('a','a','a',NULL,'xyz'),
54('a','a','b',NULL,'xyz'),
55('a','b','a',NULL,'xyz'),
56('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
57('d','b','b',NULL,'xyz'),
58('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
59('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),
60('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
61('a','a','a',NULL,'xyz'),
62('a','a','b',NULL,'xyz'),
63('a','b','a',NULL,'xyz'),
64('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
65('d','b','b',NULL,'xyz'),
66('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
67('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz');
68create index idx_t2_0 on t2 (a1);
69create index idx_t2_1 on t2 (a1,a2,b,c);
70create index idx_t2_2 on t2 (a1,a2,b);
71analyze table t2;
72Table	Op	Msg_type	Msg_text
73test.t2	analyze	status	OK
74drop table if exists t3;
75create table t3 (
76a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' '
77) engine=RocksDB;
78insert into t3 (a1, a2, b, c, d) values
79('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
80('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
81('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
82('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
83('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
84('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
85('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
86('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
87('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
88('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
89('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
90('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
91insert into t3 (a1, a2, b, c, d) values
92('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
93('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
94('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
95('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
96('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
97('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
98('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
99('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
100('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
101('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
102('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
103('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
104insert into t3 (a1, a2, b, c, d) values
105('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
106('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
107('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
108('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
109('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
110('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
111('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
112('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
113('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
114('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
115('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
116('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
117insert into t3 (a1, a2, b, c, d) values
118('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
119('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
120('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
121('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
122('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
123('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
124('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
125('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
126('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
127('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
128('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
129('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
130create index idx_t3_0 on t3 (a1);
131create index idx_t3_1 on t3 (a1,a2,b,c);
132create index idx_t3_2 on t3 (a1,a2,b);
133analyze table t3;
134Table	Op	Msg_type	Msg_text
135test.t3	analyze	status	OK
136explain select a1, min(a2) from t1 group by a1;
137id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1381	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	63	Using index for group-by
139explain select a1, max(a2) from t1 group by a1;
140id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1411	SIMPLE	t1	range	NULL	idx_t1_1	65	NULL	63	Using index for group-by
142explain select a1, min(a2), max(a2) from t1 group by a1;
143id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1441	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	63	Using index for group-by
145explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
146id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1471	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	251	Using index for group-by
148explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
149id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1501	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	251	Using index for group-by
151explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
152id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1531	SIMPLE	t2	range	NULL	idx_t2_1	#	NULL	#	Using index for group-by
154explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
155id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1561	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	63	Using index for group-by
157explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
158id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1591	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	251	Using index for group-by
160explain select min(a2) from t1 group by a1;
161id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1621	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	63	Using index for group-by
163explain select a2, min(c), max(c) from t1 group by a1,a2,b;
164id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1651	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	251	Using index for group-by
166select a1, min(a2) from t1 group by a1;
167a1	min(a2)
168a	a
169b	a
170c	a
171d	a
172select a1, max(a2) from t1 group by a1;
173a1	max(a2)
174a	b
175b	b
176c	b
177d	b
178select a1, min(a2), max(a2) from t1 group by a1;
179a1	min(a2)	max(a2)
180a	a	b
181b	a	b
182c	a	b
183d	a	b
184select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
185a1	a2	b	min(c)	max(c)
186a	a	a	a111	d111
187a	a	b	e112	h112
188a	b	a	i121	l121
189a	b	b	m122	p122
190b	a	a	a211	d211
191b	a	b	e212	h212
192b	b	a	i221	l221
193b	b	b	m222	p222
194c	a	a	a311	d311
195c	a	b	e312	h312
196c	b	a	i321	l321
197c	b	b	m322	p322
198d	a	a	a411	d411
199d	a	b	e412	h412
200d	b	a	i421	l421
201d	b	b	m422	p422
202select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
203a1	a2	b	max(c)	min(c)
204a	a	a	d111	a111
205a	a	b	h112	e112
206a	b	a	l121	i121
207a	b	b	p122	m122
208b	a	a	d211	a211
209b	a	b	h212	e212
210b	b	a	l221	i221
211b	b	b	p222	m222
212c	a	a	d311	a311
213c	a	b	h312	e312
214c	b	a	l321	i321
215c	b	b	p322	m322
216d	a	a	d411	a411
217d	a	b	h412	e412
218d	b	a	l421	i421
219d	b	b	p422	m422
220select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
221a1	a2	b	max(c)	min(c)
222a	a	NULL	a999	a777
223a	a	a	d111	a111
224a	a	b	h112	e112
225a	b	a	l121	i121
226a	b	b	p122	m122
227b	a	a	d211	a211
228b	a	b	h212	e212
229b	b	a	l221	i221
230b	b	b	p222	m222
231c	a	NULL	c999	c777
232c	a	a	d311	a311
233c	a	b	h312	e312
234c	b	a	l321	i321
235c	b	b	p322	m322
236d	a	a	d411	a411
237d	a	b	h412	e412
238d	b	a	l421	i421
239d	b	b	p422	m422
240e	a	a	NULL	NULL
241e	a	b	NULL	NULL
242select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
243min(a2)	a1	max(a2)	min(a2)	a1
244a	a	b	a	a
245a	b	b	a	b
246a	c	b	a	c
247a	d	b	a	d
248select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
249a1	b	min(c)	a1	max(c)	b	a2	max(c)	max(c)
250a	a	a111	a	d111	a	a	d111	d111
251a	b	e112	a	h112	b	a	h112	h112
252a	a	i121	a	l121	a	b	l121	l121
253a	b	m122	a	p122	b	b	p122	p122
254b	a	a211	b	d211	a	a	d211	d211
255b	b	e212	b	h212	b	a	h212	h212
256b	a	i221	b	l221	a	b	l221	l221
257b	b	m222	b	p222	b	b	p222	p222
258c	a	a311	c	d311	a	a	d311	d311
259c	b	e312	c	h312	b	a	h312	h312
260c	a	i321	c	l321	a	b	l321	l321
261c	b	m322	c	p322	b	b	p322	p322
262d	a	a411	d	d411	a	a	d411	d411
263d	b	e412	d	h412	b	a	h412	h412
264d	a	i421	d	l421	a	b	l421	l421
265d	b	m422	d	p422	b	b	p422	p422
266select min(a2) from t1 group by a1;
267min(a2)
268a
269a
270a
271a
272select a2, min(c), max(c) from t1 group by a1,a2,b;
273a2	min(c)	max(c)
274a	a111	d111
275a	e112	h112
276b	i121	l121
277b	m122	p122
278a	a211	d211
279a	e212	h212
280b	i221	l221
281b	m222	p222
282a	a311	d311
283a	e312	h312
284b	i321	l321
285b	m322	p322
286a	a411	d411
287a	e412	h412
288b	i421	l421
289b	m422	p422
290explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
291id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2921	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
293explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
294id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2951	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
296explain select a1,a2,b,       max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
297id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2981	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
299explain select a1, max(c)            from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
300id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3011	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
302explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
303id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3041	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
305explain select a1,a2,b,       max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
306id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3071	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
308explain select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
309id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3101	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
311explain 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;
312id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3131	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
314explain 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;
315id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3161	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
317explain select a1,min(c),max(c)      from t1 where a1 >= 'b' group by a1,a2,b;
318id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3191	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
320explain select a1,  max(c)           from t1 where a1 in ('a','b','d') group by a1,a2,b;
321id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3221	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
323explain select a1,a2,b,       max(c) from t2 where a1 < 'd' group by a1,a2,b;
324id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3251	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
326explain select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
327id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3281	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
329explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
330id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3311	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
332explain select a1,a2,b,       max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
333id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3341	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
335explain select a1, max(c)            from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
336id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3371	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
338explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
339id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3401	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
341explain select a1,a2,b,       max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
342id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3431	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
344explain select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
345id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3461	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
347explain 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;
348id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3491	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
350explain 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;
351id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3521	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
353explain select a1,min(c),max(c)      from t2 where a1 >= 'b' group by a1,a2,b;
354id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3551	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
356explain select a1,  max(c)           from t2 where a1 in ('a','b','d') group by a1,a2,b;
357id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3581	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
359select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
360a1	a2	b	min(c)	max(c)
361a	a	a	a111	d111
362a	a	b	e112	h112
363a	b	a	i121	l121
364a	b	b	m122	p122
365b	a	a	a211	d211
366b	a	b	e212	h212
367b	b	a	i221	l221
368b	b	b	m222	p222
369c	a	a	a311	d311
370c	a	b	e312	h312
371c	b	a	i321	l321
372c	b	b	m322	p322
373select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
374a1	a2	b	min(c)	max(c)
375b	a	a	a211	d211
376b	a	b	e212	h212
377b	b	a	i221	l221
378b	b	b	m222	p222
379c	a	a	a311	d311
380c	a	b	e312	h312
381c	b	a	i321	l321
382c	b	b	m322	p322
383d	a	a	a411	d411
384d	a	b	e412	h412
385d	b	a	i421	l421
386d	b	b	m422	p422
387select a1,a2,b,       max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
388a1	a2	b	max(c)
389a	a	a	d111
390a	a	b	h112
391a	b	a	l121
392a	b	b	p122
393c	a	a	d311
394c	a	b	h312
395c	b	a	l321
396c	b	b	p322
397d	a	a	d411
398d	a	b	h412
399d	b	a	l421
400d	b	b	p422
401select a1, max(c)            from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
402a1	max(c)
403a	d111
404a	h112
405a	l121
406a	p122
407c	d311
408c	h312
409c	l321
410c	p322
411d	d411
412d	h412
413d	l421
414d	p422
415select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
416a1	a2	b	min(c)	max(c)
417a	a	a	a111	d111
418a	a	b	e112	h112
419b	a	a	a211	d211
420b	a	b	e212	h212
421c	a	a	a311	d311
422c	a	b	e312	h312
423c	b	a	i321	l321
424c	b	b	m322	p322
425d	a	a	a411	d411
426d	a	b	e412	h412
427d	b	a	i421	l421
428d	b	b	m422	p422
429select a1,a2,b,       max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
430a1	a2	b	max(c)
431b	a	a	d211
432b	a	b	h212
433b	b	a	l221
434b	b	b	p222
435d	a	a	d411
436d	a	b	h412
437d	b	a	l421
438d	b	b	p422
439select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
440a1	a2	b	min(c)	max(c)
441b	a	a	a211	d211
442b	a	b	e212	h212
443b	b	a	i221	l221
444b	b	b	m222	p222
445d	a	a	a411	d411
446d	a	b	e412	h412
447d	b	a	i421	l421
448d	b	b	m422	p422
449select 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;
450a1	a2	b	max(c)
451a	b	a	l121
452a	b	b	p122
453b	b	a	l221
454b	b	b	p222
455c	b	a	l321
456c	b	b	p322
457d	b	a	l421
458d	b	b	p422
459select 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;
460a1	a2	b	min(c)	max(c)
461a	b	a	i121	l121
462a	b	b	m122	p122
463b	b	a	i221	l221
464b	b	b	m222	p222
465c	b	a	i321	l321
466c	b	b	m322	p322
467d	b	a	i421	l421
468d	b	b	m422	p422
469select a1,min(c),max(c)      from t1 where a1 >= 'b' group by a1,a2,b;
470a1	min(c)	max(c)
471b	a211	d211
472b	e212	h212
473b	i221	l221
474b	m222	p222
475c	a311	d311
476c	e312	h312
477c	i321	l321
478c	m322	p322
479d	a411	d411
480d	e412	h412
481d	i421	l421
482d	m422	p422
483select a1,  max(c)           from t1 where a1 in ('a','b','d') group by a1,a2,b;
484a1	max(c)
485a	d111
486a	h112
487a	l121
488a	p122
489b	d211
490b	h212
491b	l221
492b	p222
493d	d411
494d	h412
495d	l421
496d	p422
497select a1,a2,b,       max(c) from t2 where a1 < 'd' group by a1,a2,b;
498a1	a2	b	max(c)
499a	a	NULL	a999
500a	a	a	d111
501a	a	b	h112
502a	b	a	l121
503a	b	b	p122
504b	a	a	d211
505b	a	b	h212
506b	b	a	l221
507b	b	b	p222
508c	a	NULL	c999
509c	a	a	d311
510c	a	b	h312
511c	b	a	l321
512c	b	b	p322
513select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
514a1	a2	b	min(c)	max(c)
515a	a	NULL	a777	a999
516a	a	a	a111	d111
517a	a	b	e112	h112
518a	b	a	i121	l121
519a	b	b	m122	p122
520b	a	a	a211	d211
521b	a	b	e212	h212
522b	b	a	i221	l221
523b	b	b	m222	p222
524c	a	NULL	c777	c999
525c	a	a	a311	d311
526c	a	b	e312	h312
527c	b	a	i321	l321
528c	b	b	m322	p322
529select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
530a1	a2	b	min(c)	max(c)
531b	a	a	a211	d211
532b	a	b	e212	h212
533b	b	a	i221	l221
534b	b	b	m222	p222
535c	a	NULL	c777	c999
536c	a	a	a311	d311
537c	a	b	e312	h312
538c	b	a	i321	l321
539c	b	b	m322	p322
540d	a	a	a411	d411
541d	a	b	e412	h412
542d	b	a	i421	l421
543d	b	b	m422	p422
544e	a	a	NULL	NULL
545e	a	b	NULL	NULL
546select a1,a2,b,       max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
547a1	a2	b	max(c)
548a	a	NULL	a999
549a	a	a	d111
550a	a	b	h112
551a	b	a	l121
552a	b	b	p122
553c	a	NULL	c999
554c	a	a	d311
555c	a	b	h312
556c	b	a	l321
557c	b	b	p322
558d	a	a	d411
559d	a	b	h412
560d	b	a	l421
561d	b	b	p422
562e	a	a	NULL
563e	a	b	NULL
564select a1, max(c)            from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
565a1	max(c)
566a	a999
567a	d111
568a	h112
569a	l121
570a	p122
571c	c999
572c	d311
573c	h312
574c	l321
575c	p322
576d	d411
577d	h412
578d	l421
579d	p422
580e	NULL
581e	NULL
582select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
583a1	a2	b	min(c)	max(c)
584a	a	NULL	a777	a999
585a	a	a	a111	d111
586a	a	b	e112	h112
587b	a	a	a211	d211
588b	a	b	e212	h212
589c	a	NULL	c777	c999
590c	a	a	a311	d311
591c	a	b	e312	h312
592c	b	a	i321	l321
593c	b	b	m322	p322
594d	a	a	a411	d411
595d	a	b	e412	h412
596d	b	a	i421	l421
597d	b	b	m422	p422
598e	a	a	NULL	NULL
599e	a	b	NULL	NULL
600select a1,a2,b,       max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
601a1	a2	b	max(c)
602b	a	a	d211
603b	a	b	h212
604b	b	a	l221
605b	b	b	p222
606d	a	a	d411
607d	a	b	h412
608d	b	a	l421
609d	b	b	p422
610select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
611a1	a2	b	min(c)	max(c)
612b	a	a	a211	d211
613b	a	b	e212	h212
614b	b	a	i221	l221
615b	b	b	m222	p222
616d	a	a	a411	d411
617d	a	b	e412	h412
618d	b	a	i421	l421
619d	b	b	m422	p422
620select 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;
621a1	a2	b	max(c)
622a	b	a	l121
623a	b	b	p122
624b	b	a	l221
625b	b	b	p222
626c	b	a	l321
627c	b	b	p322
628d	b	a	l421
629d	b	b	p422
630select 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;
631a1	a2	b	min(c)	max(c)
632a	b	a	i121	l121
633a	b	b	m122	p122
634b	b	a	i221	l221
635b	b	b	m222	p222
636c	b	a	i321	l321
637c	b	b	m322	p322
638d	b	a	i421	l421
639d	b	b	m422	p422
640select a1,min(c),max(c)      from t2 where a1 >= 'b' group by a1,a2,b;
641a1	min(c)	max(c)
642b	a211	d211
643b	e212	h212
644b	i221	l221
645b	m222	p222
646c	c777	c999
647c	a311	d311
648c	e312	h312
649c	i321	l321
650c	m322	p322
651d	a411	d411
652d	e412	h412
653d	i421	l421
654d	m422	p422
655e	NULL	NULL
656e	NULL	NULL
657select a1,  max(c)           from t2 where a1 in ('a','b','d') group by a1,a2,b;
658a1	max(c)
659a	a999
660a	d111
661a	h112
662a	l121
663a	p122
664b	d211
665b	h212
666b	l221
667b	p222
668d	d411
669d	h412
670d	l421
671d	p422
672#
673# MariaDB: we dont have the following patch:
674#
675#  commit 60a92a79a3b7fde3c6efe91799e344b977c8e5c3
676#  Author: Manuel Ung <mung@fb.com>
677#  Date:   Thu Apr 19 23:06:27 2018 -0700
678#
679#   Enhance group-by loose index scan
680#
681#  So the following results are not very meaningful, but are still kept here
682explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
683id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
6841	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	63	Using where; Using index for group-by
685explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
686id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
6871	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	1000	Using where; Using index
688explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
689id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
6901	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	1000	Using where; Using index
691explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b' or b = 'a') group by a1;
692id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
6931	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	1000	Using where; Using index
694explain select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b') group by a1;
695id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
6961	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	63	Using where; Using index for group-by
697explain select a1,max(c),min(c)      from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
698id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
6991	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	1000	Using where; Using index
700explain select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
701id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7021	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	1000	Using where; Using index
703explain select a1,a2,b,       max(c) from t1 where (b = 'b') group by a1,a2;
704id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7051	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	126	Using where; Using index for group-by
706explain select a1,a2,b,       max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2;
707id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7081	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	1000	Using where; Using index
709explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
710id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7111	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	126	Using where; Using index for group-by
712explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2;
713id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7141	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	1000	Using where; Using index
715explain select a1,a2, max(c)         from t1 where (b = 'b') group by a1,a2;
716id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7171	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	126	Using where; Using index for group-by
718explain select a1,a2, max(c)         from t1 where (b = 'b' or b = 'a') group by a1,a2;
719id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7201	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	1000	Using where; Using index
721explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
722id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7231	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	63	Using where; Using index for group-by
724explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
725id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7261	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
727explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
728id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7291	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
730explain select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b') group by a1;
731id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7321	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	63	Using where; Using index for group-by
733explain select a1,max(c),min(c)      from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
734id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7351	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
736explain select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
737id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7381	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
739explain select a1,a2,b,       max(c) from t2 where (b = 'b') group by a1,a2;
740id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7411	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	126	Using where; Using index for group-by
742explain select a1,a2,b,       max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2;
743id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7441	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
745explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
746id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7471	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	126	Using where; Using index for group-by
748explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2;
749id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7501	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
751explain select a1,a2, max(c)         from t2 where (b = 'b') group by a1,a2;
752id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7531	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	126	Using where; Using index for group-by
754explain select a1,a2, max(c)         from t2 where (b = 'b' or b = 'a') group by a1,a2;
755id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7561	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
757explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
758id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7591	SIMPLE	t3	range	NULL	idx_t3_1	6	NULL	63	Using where; Using index for group-by
760explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
761id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7621	SIMPLE	t3	index	NULL	idx_t3_1	10	NULL	1000	Using where; Using index
763explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
764id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7651	SIMPLE	t3	index	NULL	idx_t3_1	10	NULL	1000	Using where; Using index
766explain select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b') group by a1;
767id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7681	SIMPLE	t3	range	NULL	idx_t3_1	6	NULL	63	Using where; Using index for group-by
769explain select a1,max(c),min(c)      from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
770id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7711	SIMPLE	t3	index	NULL	idx_t3_1	10	NULL	1000	Using where; Using index
772explain select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
773id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7741	SIMPLE	t3	index	NULL	idx_t3_1	10	NULL	1000	Using where; Using index
775select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
776a1	a2	b	max(c)	min(c)
777a	a	b	h112	e112
778b	a	b	h212	e212
779c	a	b	h312	e312
780d	a	b	h412	e412
781select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
782a1	a2	b	max(c)	min(c)
783a	a	b	p122	e112
784b	a	b	p222	e212
785c	a	b	p322	e312
786d	a	b	p422	e412
787select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
788a1	a2	b	max(c)	min(c)
789a	a	a	h112	a111
790b	a	a	h212	a211
791c	a	a	h312	a311
792d	a	a	h412	a411
793select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b' or b = 'a') group by a1;
794a1	a2	b	max(c)	min(c)
795a	a	a	p122	a111
796b	a	a	p222	a211
797c	a	a	p322	a311
798d	a	a	p422	a411
799select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b') group by a1;
800a1	max(c)	min(c)
801a	h112	e112
802b	h212	e212
803c	h312	e312
804d	h412	e412
805select a1,max(c),min(c)      from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
806a1	max(c)	min(c)
807a	p122	e112
808b	p222	e212
809c	p322	e312
810d	p422	e412
811select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
812a1	max(c)	min(c)
813a	h112	a111
814b	h212	a211
815c	h312	a311
816d	h412	a411
817select a1,a2,b,       max(c) from t1 where (b = 'b') group by a1,a2;
818a1	a2	b	max(c)
819a	a	b	h112
820a	b	b	p122
821b	a	b	h212
822b	b	b	p222
823c	a	b	h312
824c	b	b	p322
825d	a	b	h412
826d	b	b	p422
827select a1,a2,b,       max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2;
828a1	a2	b	max(c)
829a	a	a	h112
830a	b	a	p122
831b	a	a	h212
832b	b	a	p222
833c	a	a	h312
834c	b	a	p322
835d	a	a	h412
836d	b	a	p422
837select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
838a1	a2	b	min(c)	max(c)
839a	a	b	e112	h112
840a	b	b	m122	p122
841b	a	b	e212	h212
842b	b	b	m222	p222
843c	a	b	e312	h312
844c	b	b	m322	p322
845d	a	b	e412	h412
846d	b	b	m422	p422
847select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2;
848a1	a2	b	min(c)	max(c)
849a	a	a	a111	h112
850a	b	a	i121	p122
851b	a	a	a211	h212
852b	b	a	i221	p222
853c	a	a	a311	h312
854c	b	a	i321	p322
855d	a	a	a411	h412
856d	b	a	i421	p422
857select a1,a2, max(c)         from t1 where (b = 'b') group by a1,a2;
858a1	a2	max(c)
859a	a	h112
860a	b	p122
861b	a	h212
862b	b	p222
863c	a	h312
864c	b	p322
865d	a	h412
866d	b	p422
867select a1,a2, max(c)         from t1 where (b = 'b' or b = 'a') group by a1,a2;
868a1	a2	max(c)
869a	a	h112
870a	b	p122
871b	a	h212
872b	b	p222
873c	a	h312
874c	b	p322
875d	a	h412
876d	b	p422
877select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
878a1	a2	b	max(c)	min(c)
879a	a	b	h112	e112
880b	a	b	h212	e212
881c	a	b	h312	e312
882d	a	b	h412	e412
883e	a	b	NULL	NULL
884select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
885a1	a2	b	max(c)	min(c)
886a	a	b	p122	e112
887b	a	b	p222	e212
888c	a	b	p322	e312
889d	a	b	p422	e412
890e	a	b	NULL	NULL
891select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
892a1	a2	b	max(c)	min(c)
893a	a	a	h112	a111
894b	a	a	h212	a211
895c	a	a	h312	a311
896d	a	a	h412	a411
897e	a	a	NULL	NULL
898select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b') group by a1;
899a1	max(c)	min(c)
900a	h112	e112
901b	h212	e212
902c	h312	e312
903d	h412	e412
904e	NULL	NULL
905select a1,max(c),min(c)      from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
906a1	max(c)	min(c)
907a	p122	e112
908b	p222	e212
909c	p322	e312
910d	p422	e412
911e	NULL	NULL
912select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
913a1	max(c)	min(c)
914a	h112	a111
915b	h212	a211
916c	h312	a311
917d	h412	a411
918e	NULL	NULL
919select a1,a2,b,       max(c) from t2 where (b = 'b') group by a1,a2;
920a1	a2	b	max(c)
921a	a	b	h112
922a	b	b	p122
923b	a	b	h212
924b	b	b	p222
925c	a	b	h312
926c	b	b	p322
927d	a	b	h412
928d	b	b	p422
929e	a	b	NULL
930select a1,a2,b,       max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2;
931a1	a2	b	max(c)
932a	a	a	h112
933a	b	a	p122
934b	a	a	h212
935b	b	a	p222
936c	a	a	h312
937c	b	a	p322
938d	a	a	h412
939d	b	a	p422
940e	a	a	NULL
941select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
942a1	a2	b	min(c)	max(c)
943a	a	b	e112	h112
944a	b	b	m122	p122
945b	a	b	e212	h212
946b	b	b	m222	p222
947c	a	b	e312	h312
948c	b	b	m322	p322
949d	a	b	e412	h412
950d	b	b	m422	p422
951e	a	b	NULL	NULL
952select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2;
953a1	a2	b	min(c)	max(c)
954a	a	a	a111	h112
955a	b	a	i121	p122
956b	a	a	a211	h212
957b	b	a	i221	p222
958c	a	a	a311	h312
959c	b	a	i321	p322
960d	a	a	a411	h412
961d	b	a	i421	p422
962e	a	a	NULL	NULL
963select a1,a2, max(c)         from t2 where (b = 'b') group by a1,a2;
964a1	a2	max(c)
965a	a	h112
966a	b	p122
967b	a	h212
968b	b	p222
969c	a	h312
970c	b	p322
971d	a	h412
972d	b	p422
973e	a	NULL
974select a1,a2, max(c)         from t2 where (b = 'b' or b = 'a') group by a1,a2;
975a1	a2	max(c)
976a	a	h112
977a	b	p122
978b	a	h212
979b	b	p222
980c	a	h312
981c	b	p322
982d	a	h412
983d	b	p422
984e	a	NULL
985select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
986a1	a2	b	max(c)	min(c)
987a	a	b	h112	e112
988b	a	b	h212	e212
989c	a	b	h312	e312
990select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
991a1	a2	b	max(c)	min(c)
992a	a	b	p122	e112
993b	a	b	p222	e212
994c	a	b	p322	e312
995select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
996a1	a2	b	max(c)	min(c)
997a	a	a	h112	a111
998b	a	a	h212	a211
999c	a	a	h312	a311
1000select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b') group by a1;
1001a1	max(c)	min(c)
1002a	h112	e112
1003b	h212	e212
1004c	h312	e312
1005select a1,max(c),min(c)      from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
1006a1	max(c)	min(c)
1007a	p122	e112
1008b	p222	e212
1009c	p322	e312
1010select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
1011a1	max(c)	min(c)
1012a	h112	a111
1013b	h212	a211
1014c	h312	a311
1015explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
1016id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10171	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	63	Using where; Using index for group-by
1018explain select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1;
1019id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10201	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
1021explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
1022id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10231	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	63	Using where; Using index for group-by
1024explain select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1;
1025id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10261	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
1027explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
1028id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10291	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	126	Using where; Using index for group-by
1030explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
1031id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10321	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	126	Using where; Using index for group-by
1033explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
1034id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10351	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	126	Using where; Using index for group-by
1036select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
1037a1	a2	b	min(c)
1038a	a	NULL	a777
1039c	a	NULL	c777
1040select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1;
1041a1	a2	b	min(c)
1042a	a	NULL	a777
1043c	a	NULL	c777
1044select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
1045a1	a2	b	max(c)
1046a	a	NULL	a999
1047c	a	NULL	c999
1048select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1;
1049a1	a2	b	max(c)
1050a	a	NULL	a999
1051c	a	NULL	c999
1052select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
1053a1	a2	b	min(c)
1054a	a	NULL	a777
1055c	a	NULL	c777
1056select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
1057a1	a2	b	max(c)
1058a	a	NULL	a999
1059c	a	NULL	c999
1060select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
1061a1	a2	b	min(c)	max(c)
1062a	a	NULL	a777	a999
1063c	a	NULL	c777	c999
1064select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
1065a1	a2	b	min(c)	max(c)
1066a	a	NULL	a777	a999
1067c	a	NULL	c777	c999
1068explain select a1,a2,b,       max(c) from t1 where (c > 'b1') group by a1,a2,b;
1069id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10701	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	#	Using where; Using index for group-by
1071explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
1072id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10731	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1074explain select a1,a2,b,       max(c) from t1 where (c > 'f123') group by a1,a2,b;
1075id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10761	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	251	Using where; Using index for group-by
1077explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
1078id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10791	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1080explain select a1,a2,b,       max(c) from t1 where (c < 'a0') group by a1,a2,b;
1081id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10821	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1083explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
1084id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10851	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1086explain select a1,a2,b,       max(c) from t1 where (c < 'k321') group by a1,a2,b;
1087id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10881	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1089explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
1090id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10911	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1092explain select a1,a2,b,       max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1093id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10941	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1095explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1096id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10971	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1098explain select a1,a2,b,       max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1099id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11001	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	251	Using where; Using index for group-by
1101explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1102id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11031	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	251	Using where; Using index for group-by
1104explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
1105id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11061	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1107explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
1108id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11091	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1110explain 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;
1111id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11121	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1113explain 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;
1114id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11151	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1116explain select a1,a2,b,       max(c) from t2 where (c > 'b1') group by a1,a2,b;
1117id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11181	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
1119explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
1120id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11211	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1122explain select a1,a2,b,       max(c) from t2 where (c > 'f123') group by a1,a2,b;
1123id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11241	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
1125explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
1126id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11271	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1128explain select a1,a2,b,       max(c) from t2 where (c < 'a0') group by a1,a2,b;
1129id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11301	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1131explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
1132id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11331	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1134explain select a1,a2,b,       max(c) from t2 where (c < 'k321') group by a1,a2,b;
1135id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11361	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1137explain select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
1138id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11391	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1140explain select a1,a2,b,       max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1141id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11421	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1143explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1144id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11451	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1146explain select a1,a2,b,       max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1147id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11481	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
1149explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1150id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11511	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1152explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
1153id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11541	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1155explain select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
1156id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11571	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1158explain 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;
1159id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11601	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1161select a1,a2,b,       max(c) from t1 where (c > 'b1') group by a1,a2,b;
1162a1	a2	b	max(c)
1163a	a	a	d111
1164a	a	b	h112
1165a	b	a	l121
1166a	b	b	p122
1167b	a	a	d211
1168b	a	b	h212
1169b	b	a	l221
1170b	b	b	p222
1171c	a	a	d311
1172c	a	b	h312
1173c	b	a	l321
1174c	b	b	p322
1175d	a	a	d411
1176d	a	b	h412
1177d	b	a	l421
1178d	b	b	p422
1179select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
1180a1	a2	b	min(c)	max(c)
1181a	a	a	b111	d111
1182a	a	b	e112	h112
1183a	b	a	i121	l121
1184a	b	b	m122	p122
1185b	a	a	b211	d211
1186b	a	b	e212	h212
1187b	b	a	i221	l221
1188b	b	b	m222	p222
1189c	a	a	b311	d311
1190c	a	b	e312	h312
1191c	b	a	i321	l321
1192c	b	b	m322	p322
1193d	a	a	b411	d411
1194d	a	b	e412	h412
1195d	b	a	i421	l421
1196d	b	b	m422	p422
1197select a1,a2,b,       max(c) from t1 where (c > 'f123') group by a1,a2,b;
1198a1	a2	b	max(c)
1199a	a	b	h112
1200a	b	a	l121
1201a	b	b	p122
1202b	a	b	h212
1203b	b	a	l221
1204b	b	b	p222
1205c	a	b	h312
1206c	b	a	l321
1207c	b	b	p322
1208d	a	b	h412
1209d	b	a	l421
1210d	b	b	p422
1211select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
1212a1	a2	b	min(c)	max(c)
1213a	a	b	g112	h112
1214a	b	a	i121	l121
1215a	b	b	m122	p122
1216b	a	b	f212	h212
1217b	b	a	i221	l221
1218b	b	b	m222	p222
1219c	a	b	f312	h312
1220c	b	a	i321	l321
1221c	b	b	m322	p322
1222d	a	b	f412	h412
1223d	b	a	i421	l421
1224d	b	b	m422	p422
1225select a1,a2,b,       max(c) from t1 where (c < 'a0') group by a1,a2,b;
1226a1	a2	b	max(c)
1227select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
1228a1	a2	b	min(c)	max(c)
1229select a1,a2,b,       max(c) from t1 where (c < 'k321') group by a1,a2,b;
1230a1	a2	b	max(c)
1231a	a	a	d111
1232a	a	b	h112
1233a	b	a	k121
1234b	a	a	d211
1235b	a	b	h212
1236b	b	a	k221
1237c	a	a	d311
1238c	a	b	h312
1239c	b	a	j321
1240d	a	a	d411
1241d	a	b	h412
1242d	b	a	j421
1243select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
1244a1	a2	b	min(c)	max(c)
1245a	a	a	a111	d111
1246a	a	b	e112	h112
1247a	b	a	i121	k121
1248b	a	a	a211	d211
1249b	a	b	e212	h212
1250b	b	a	i221	k221
1251c	a	a	a311	d311
1252c	a	b	e312	h312
1253c	b	a	i321	j321
1254d	a	a	a411	d411
1255d	a	b	e412	h412
1256d	b	a	i421	j421
1257select a1,a2,b,       max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1258a1	a2	b	max(c)
1259a	a	a	d111
1260a	a	b	h112
1261a	b	a	l121
1262a	b	b	p122
1263b	a	a	d211
1264b	a	b	h212
1265b	b	a	l221
1266b	b	b	p222
1267c	a	a	d311
1268c	a	b	h312
1269c	b	a	l321
1270c	b	b	p322
1271d	a	a	d411
1272d	a	b	h412
1273d	b	a	l421
1274d	b	b	p422
1275select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1276a1	a2	b	min(c)	max(c)
1277a	a	a	b111	d111
1278a	a	b	e112	h112
1279a	b	a	i121	l121
1280a	b	b	m122	p122
1281b	a	a	b211	d211
1282b	a	b	e212	h212
1283b	b	a	i221	l221
1284b	b	b	m222	p222
1285c	a	a	b311	d311
1286c	a	b	e312	h312
1287c	b	a	i321	l321
1288c	b	b	m322	p322
1289d	a	a	b411	d411
1290d	a	b	e412	h412
1291d	b	a	i421	l421
1292d	b	b	m422	p422
1293select a1,a2,b,       max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1294a1	a2	b	max(c)
1295a	a	a	d111
1296a	a	b	h112
1297a	b	a	l121
1298a	b	b	p122
1299b	a	a	d211
1300b	a	b	h212
1301b	b	a	l221
1302b	b	b	p222
1303c	a	a	d311
1304c	a	b	h312
1305c	b	a	l321
1306c	b	b	p322
1307d	a	a	d411
1308d	a	b	h412
1309d	b	a	l421
1310d	b	b	p422
1311select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1312a1	a2	b	min(c)	max(c)
1313a	a	a	a111	d111
1314a	a	b	e112	h112
1315a	b	a	i121	l121
1316a	b	b	m122	p122
1317b	a	a	a211	d211
1318b	a	b	e212	h212
1319b	b	a	i221	l221
1320b	b	b	m222	p222
1321c	a	a	a311	d311
1322c	a	b	e312	h312
1323c	b	a	i321	l321
1324c	b	b	m322	p322
1325d	a	a	a411	d411
1326d	a	b	e412	h412
1327d	b	a	i421	l421
1328d	b	b	m422	p422
1329select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
1330a1	a2	b	min(c)	max(c)
1331a	a	a	c111	d111
1332a	a	b	e112	g112
1333b	a	a	b211	d211
1334b	a	b	e212	f212
1335c	a	a	b311	d311
1336c	a	b	e312	f312
1337d	a	a	b411	d411
1338d	a	b	e412	f412
1339select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
1340a1	a2	b	min(c)	max(c)
1341a	a	a	a111	c111
1342b	a	a	a211	c211
1343c	a	a	a311	c311
1344d	a	a	a411	c411
1345d	a	b	g412	g412
1346d	b	a	k421	k421
1347select 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;
1348a1	a2	b	min(c)	max(c)
1349a	a	a	c111	d111
1350a	a	b	e112	h112
1351b	a	a	b211	d211
1352b	a	b	e212	h212
1353c	a	a	b311	d311
1354c	a	b	e312	h312
1355d	a	a	b411	d411
1356d	a	b	e412	h412
1357select 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;
1358a1	a2	b	min(c)	max(c)
1359a	a	a	b111	d111
1360a	a	b	e112	h112
1361b	a	a	b211	d211
1362b	a	b	e212	h212
1363c	a	a	b311	d311
1364c	a	b	e312	h312
1365d	a	a	b411	d411
1366d	a	b	e412	h412
1367select a1,a2,b,       max(c) from t2 where (c > 'b1') group by a1,a2,b;
1368a1	a2	b	max(c)
1369a	a	a	d111
1370a	a	b	h112
1371a	b	a	l121
1372a	b	b	p122
1373b	a	a	d211
1374b	a	b	h212
1375b	b	a	l221
1376b	b	b	p222
1377c	a	NULL	c999
1378c	a	a	d311
1379c	a	b	h312
1380c	b	a	l321
1381c	b	b	p322
1382d	a	a	d411
1383d	a	b	h412
1384d	b	a	l421
1385d	b	b	p422
1386select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
1387a1	a2	b	min(c)	max(c)
1388a	a	a	b111	d111
1389a	a	b	e112	h112
1390a	b	a	i121	l121
1391a	b	b	m122	p122
1392b	a	a	b211	d211
1393b	a	b	e212	h212
1394b	b	a	i221	l221
1395b	b	b	m222	p222
1396c	a	NULL	c777	c999
1397c	a	a	b311	d311
1398c	a	b	e312	h312
1399c	b	a	i321	l321
1400c	b	b	m322	p322
1401d	a	a	b411	d411
1402d	a	b	e412	h412
1403d	b	a	i421	l421
1404d	b	b	m422	p422
1405select a1,a2,b,       max(c) from t2 where (c > 'f123') group by a1,a2,b;
1406a1	a2	b	max(c)
1407a	a	b	h112
1408a	b	a	l121
1409a	b	b	p122
1410b	a	b	h212
1411b	b	a	l221
1412b	b	b	p222
1413c	a	b	h312
1414c	b	a	l321
1415c	b	b	p322
1416d	a	b	h412
1417d	b	a	l421
1418d	b	b	p422
1419select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
1420a1	a2	b	min(c)	max(c)
1421a	a	b	g112	h112
1422a	b	a	i121	l121
1423a	b	b	m122	p122
1424b	a	b	f212	h212
1425b	b	a	i221	l221
1426b	b	b	m222	p222
1427c	a	b	f312	h312
1428c	b	a	i321	l321
1429c	b	b	m322	p322
1430d	a	b	f412	h412
1431d	b	a	i421	l421
1432d	b	b	m422	p422
1433select a1,a2,b,       max(c) from t2 where (c < 'a0') group by a1,a2,b;
1434a1	a2	b	max(c)
1435select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
1436a1	a2	b	min(c)	max(c)
1437select a1,a2,b,       max(c) from t2 where (c < 'k321') group by a1,a2,b;
1438a1	a2	b	max(c)
1439a	a	NULL	a999
1440a	a	a	d111
1441a	a	b	h112
1442a	b	a	k121
1443b	a	a	d211
1444b	a	b	h212
1445b	b	a	k221
1446c	a	NULL	c999
1447c	a	a	d311
1448c	a	b	h312
1449c	b	a	j321
1450d	a	a	d411
1451d	a	b	h412
1452d	b	a	j421
1453select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
1454a1	a2	b	min(c)	max(c)
1455a	a	NULL	a777	a999
1456a	a	a	a111	d111
1457a	a	b	e112	h112
1458a	b	a	i121	k121
1459b	a	a	a211	d211
1460b	a	b	e212	h212
1461b	b	a	i221	k221
1462c	a	NULL	c777	c999
1463c	a	a	a311	d311
1464c	a	b	e312	h312
1465c	b	a	i321	j321
1466d	a	a	a411	d411
1467d	a	b	e412	h412
1468d	b	a	i421	j421
1469select a1,a2,b,       max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1470a1	a2	b	max(c)
1471a	a	a	d111
1472a	a	b	h112
1473a	b	a	l121
1474a	b	b	p122
1475b	a	a	d211
1476b	a	b	h212
1477b	b	a	l221
1478b	b	b	p222
1479c	a	NULL	c999
1480c	a	a	d311
1481c	a	b	h312
1482c	b	a	l321
1483c	b	b	p322
1484d	a	a	d411
1485d	a	b	h412
1486d	b	a	l421
1487d	b	b	p422
1488select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1489a1	a2	b	min(c)	max(c)
1490a	a	a	b111	d111
1491a	a	b	e112	h112
1492a	b	a	i121	l121
1493a	b	b	m122	p122
1494b	a	a	b211	d211
1495b	a	b	e212	h212
1496b	b	a	i221	l221
1497b	b	b	m222	p222
1498c	a	NULL	c777	c999
1499c	a	a	b311	d311
1500c	a	b	e312	h312
1501c	b	a	i321	l321
1502c	b	b	m322	p322
1503d	a	a	b411	d411
1504d	a	b	e412	h412
1505d	b	a	i421	l421
1506d	b	b	m422	p422
1507select a1,a2,b,       max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1508a1	a2	b	max(c)
1509a	a	NULL	a999
1510a	a	a	d111
1511a	a	b	h112
1512a	b	a	l121
1513a	b	b	p122
1514b	a	a	d211
1515b	a	b	h212
1516b	b	a	l221
1517b	b	b	p222
1518c	a	NULL	c999
1519c	a	a	d311
1520c	a	b	h312
1521c	b	a	l321
1522c	b	b	p322
1523d	a	a	d411
1524d	a	b	h412
1525d	b	a	l421
1526d	b	b	p422
1527select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1528a1	a2	b	min(c)	max(c)
1529a	a	NULL	a777	a999
1530a	a	a	a111	d111
1531a	a	b	e112	h112
1532a	b	a	i121	l121
1533a	b	b	m122	p122
1534b	a	a	a211	d211
1535b	a	b	e212	h212
1536b	b	a	i221	l221
1537b	b	b	m222	p222
1538c	a	NULL	c777	c999
1539c	a	a	a311	d311
1540c	a	b	e312	h312
1541c	b	a	i321	l321
1542c	b	b	m322	p322
1543d	a	a	a411	d411
1544d	a	b	e412	h412
1545d	b	a	i421	l421
1546d	b	b	m422	p422
1547select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
1548a1	a2	b	min(c)	max(c)
1549a	a	a	c111	d111
1550a	a	b	e112	g112
1551b	a	a	b211	d211
1552b	a	b	e212	f212
1553c	a	NULL	c777	c999
1554c	a	a	b311	d311
1555c	a	b	e312	f312
1556d	a	a	b411	d411
1557d	a	b	e412	f412
1558select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
1559a1	a2	b	min(c)	max(c)
1560a	a	NULL	a777	a999
1561a	a	a	a111	c111
1562b	a	a	a211	c211
1563c	a	a	a311	c311
1564d	a	a	a411	c411
1565d	a	b	g412	g412
1566d	b	a	k421	k421
1567select 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;
1568a1	a2	b	min(c)	max(c)
1569a	a	a	c111	d111
1570a	a	b	e112	h112
1571b	a	a	b211	d211
1572b	a	b	e212	h212
1573c	a	NULL	c777	c999
1574c	a	a	b311	d311
1575c	a	b	e312	h312
1576d	a	a	b411	d411
1577d	a	b	e412	h412
1578explain select a1,a2,b,min(c),max(c) from t1
1579where exists ( select * from t2 where t2.c = t1.c )
1580group by a1,a2,b;
1581id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
15821	PRIMARY	t1	index	NULL	idx_t1_1	163	NULL	1000	Using index
15831	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	16	func	1
15842	MATERIALIZED	t2	index	NULL	idx_t2_1	163	NULL	1000	Using index
1585explain select a1,a2,b,min(c),max(c) from t1
1586where exists ( select * from t2 where t2.c > 'b1' )
1587group by a1,a2,b;
1588id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
15891	PRIMARY	t1	index	NULL	idx_t1_1	163	NULL	1000	Using index
15902	SUBQUERY	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
1591explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1592id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
15931	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
1594explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
1595id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
15961	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1597explain select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
1598id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
15991	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1600explain 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;
1601id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
16021	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1603explain 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;
1604id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
16051	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1606explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1607id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
16081	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
1609explain 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;
1610id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
16111	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	251	Using where; Using index for group-by
1612explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1613id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
16141	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1615explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
1616id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
16171	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1618explain select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
1619id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
16201	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1621explain 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;
1622id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
16231	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1624explain 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;
1625id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
16261	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1627explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1628id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
16291	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1630select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1631a1	a2	b	min(c)	max(c)
1632a	a	b	e112	h112
1633b	a	b	e212	h212
1634c	a	b	e312	h312
1635c	b	b	m322	p322
1636d	a	b	e412	h412
1637d	b	b	m422	p422
1638select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
1639a1	a2	b	min(c)	max(c)
1640a	a	a	c111	d111
1641a	a	b	e112	h112
1642b	a	a	b211	d211
1643b	a	b	e212	h212
1644c	a	a	b311	d311
1645c	a	b	e312	h312
1646c	b	a	i321	l321
1647c	b	b	m322	p322
1648d	a	a	b411	d411
1649d	a	b	e412	h412
1650d	b	a	i421	l421
1651d	b	b	m422	p422
1652select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
1653a1	a2	b	min(c)	max(c)
1654a	b	a	i121	l121
1655b	b	a	i221	l221
1656c	b	a	i321	l321
1657d	b	a	i421	l421
1658select 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;
1659a1	a2	b	min(c)
1660b	b	a	k221
1661c	b	a	k321
1662d	b	a	k421
1663select 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;
1664a1	a2	b	min(c)
1665b	b	a	k221
1666c	b	a	k321
1667d	b	a	k421
1668select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1669a1	a2	b	min(c)
1670select 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;
1671a1	a2	b	min(c)
1672select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1673a1	a2	b	min(c)	max(c)
1674a	a	b	e112	h112
1675b	a	b	e212	h212
1676c	a	b	e312	h312
1677c	b	b	m322	p322
1678d	a	b	e412	h412
1679d	b	b	m422	p422
1680e	a	b	NULL	NULL
1681select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
1682a1	a2	b	min(c)	max(c)
1683a	a	a	c111	d111
1684a	a	b	e112	h112
1685b	a	a	b211	d211
1686b	a	b	e212	h212
1687c	a	NULL	c777	c999
1688c	a	a	b311	d311
1689c	a	b	e312	h312
1690c	b	a	i321	l321
1691c	b	b	m322	p322
1692d	a	a	b411	d411
1693d	a	b	e412	h412
1694d	b	a	i421	l421
1695d	b	b	m422	p422
1696select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
1697a1	a2	b	min(c)	max(c)
1698a	b	a	i121	l121
1699b	b	a	i221	l221
1700c	b	a	i321	l321
1701d	b	a	i421	l421
1702select 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;
1703a1	a2	b	min(c)
1704b	b	a	k221
1705c	b	a	k321
1706d	b	a	k421
1707select 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;
1708a1	a2	b	min(c)
1709b	b	a	k221
1710c	b	a	k321
1711d	b	a	k421
1712select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1713a1	a2	b	min(c)
1714explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1715id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17161	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
1717explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1718id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17191	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	251	Using where; Using index for group-by
1720explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1721id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17221	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1723explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b;
1724id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17251	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1726explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1727id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17281	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
1729explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1730id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17311	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
1732explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1733id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17341	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
1735explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1736id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17371	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1738explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b;
1739id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17401	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1741explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1742id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17431	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
1744select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1745a1	a2	b
1746a	a	b
1747b	a	b
1748c	a	b
1749c	b	b
1750d	a	b
1751d	b	b
1752select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1753a1	a2	b
1754a	b	a
1755b	b	a
1756c	b	a
1757d	b	a
1758select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1759a1	a2	b	c
1760a	b	a	i121
1761select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b;
1762a1	a2	b	c
1763a	b	a	i121
1764select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1765a1	a2	b
1766select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1767a1	a2	b
1768a	a	b
1769b	a	b
1770c	a	b
1771c	b	b
1772d	a	b
1773d	b	b
1774e	a	b
1775select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1776a1	a2	b
1777a	b	a
1778b	b	a
1779c	b	a
1780d	b	a
1781select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1782a1	a2	b	c
1783a	b	a	i121
1784select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b;
1785a1	a2	b	c
1786a	b	a	i121
1787select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1788a1	a2	b
1789explain select distinct a1,a2,b from t1;
1790id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17911	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	251	Using index for group-by
1792explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
1793id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17941	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	251	Using where; Using index for group-by
1795explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1796id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
17971	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	501	100.00	Using where; Using index for group-by
1798Warnings:
1799Note	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'
1800explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1801id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18021	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
1803explain select distinct b from t1 where (a2 >= 'b') and (b = 'a');
1804id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18051	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	1000	Using where; Using index
1806explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b';
1807id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18081	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	130	NULL	63	Using where; Using index for group-by
1809explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e';
1810id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18111	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	130	NULL	63	Using where; Using index for group-by
1812explain select distinct a1,a2,b from t2;
1813id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18141	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using index for group-by
1815explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
1816id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18171	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
1818explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1819id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
18201	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	501	100.00	Using where; Using index for group-by
1821Warnings:
1822Note	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'
1823explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1824id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18251	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
1826explain select distinct b from t2 where (a2 >= 'b') and (b = 'a');
1827id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18281	SIMPLE	t2	index	NULL	idx_t2_2	146	NULL	1000	Using where; Using index
1829explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b';
1830id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18311	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	129	NULL	63	Using where; Using index for group-by
1832explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e';
1833id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18341	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	129	NULL	63	Using where; Using index for group-by
1835select distinct a1,a2,b from t1;
1836a1	a2	b
1837a	a	a
1838a	a	b
1839a	b	a
1840a	b	b
1841b	a	a
1842b	a	b
1843b	b	a
1844b	b	b
1845c	a	a
1846c	a	b
1847c	b	a
1848c	b	b
1849d	a	a
1850d	a	b
1851d	b	a
1852d	b	b
1853select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
1854a1	a2	b
1855a	b	a
1856b	b	a
1857c	b	a
1858d	b	a
1859select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1860a1	a2	b	c
1861a	b	a	i121
1862select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1863a1	a2	b
1864select distinct b from t1 where (a2 >= 'b') and (b = 'a');
1865b
1866a
1867select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b';
1868a1
1869a
1870d
1871select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e';
1872a1
1873select distinct a1,a2,b from t2;
1874a1	a2	b
1875a	a	NULL
1876a	a	a
1877a	a	b
1878a	b	a
1879a	b	b
1880b	a	a
1881b	a	b
1882b	b	a
1883b	b	b
1884c	a	NULL
1885c	a	a
1886c	a	b
1887c	b	a
1888c	b	b
1889d	a	a
1890d	a	b
1891d	b	a
1892d	b	b
1893e	a	a
1894e	a	b
1895select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
1896a1	a2	b
1897a	b	a
1898b	b	a
1899c	b	a
1900d	b	a
1901select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1902a1	a2	b	c
1903a	b	a	i121
1904select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1905a1	a2	b
1906select distinct b from t2 where (a2 >= 'b') and (b = 'a');
1907b
1908a
1909select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b';
1910a1
1911a
1912d
1913select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e';
1914a1
1915select distinct t_00.a1
1916from t1 t_00
1917where exists ( select * from t2 where a1 = t_00.a1 );
1918a1
1919a
1920b
1921c
1922d
1923select distinct a1,a1 from t1;
1924a1	a1
1925a	a
1926b	b
1927c	c
1928d	d
1929select distinct a2,a1,a2,a1 from t1;
1930a2	a1	a2	a1
1931a	a	a	a
1932b	a	b	a
1933a	b	a	b
1934b	b	b	b
1935a	c	a	c
1936b	c	b	c
1937a	d	a	d
1938b	d	b	d
1939select distinct t1.a1,t2.a1 from t1,t2;
1940a1	a1
1941a	a
1942b	a
1943c	a
1944d	a
1945a	b
1946b	b
1947c	b
1948d	b
1949a	c
1950b	c
1951c	c
1952d	c
1953a	d
1954b	d
1955c	d
1956d	d
1957a	e
1958b	e
1959c	e
1960d	e
1961explain select distinct a1,a2,b from t1;
1962id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19631	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	251	Using index for group-by
1964explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1965id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19661	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	251	Using where; Using index for group-by
1967explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1968id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19691	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	251	Using where; Using index for group-by
1970explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1971id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19721	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
1973explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1974id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19751	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	251	Using where; Using index for group-by; Using temporary; Using filesort
1976explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b' group by a1;
1977id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19781	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	130	NULL	63	Using where; Using index for group-by
1979explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e' group by a1;
1980id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19811	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	130	NULL	63	Using where; Using index for group-by
1982explain select distinct a1,a2,b from t2;
1983id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19841	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using index for group-by
1985explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1986id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19871	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
1988explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1989id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19901	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1991explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1992id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19931	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
1994explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1995id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19961	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by; Using temporary; Using filesort
1997explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b' group by a1;
1998id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19991	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	129	NULL	#	Using where; Using index for group-by
2000explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e' group by a1;
2001id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
20021	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	129	NULL	#	Using where; Using index for group-by
2003select distinct a1,a2,b from t1;
2004a1	a2	b
2005a	a	a
2006a	a	b
2007a	b	a
2008a	b	b
2009b	a	a
2010b	a	b
2011b	b	a
2012b	b	b
2013c	a	a
2014c	a	b
2015c	b	a
2016c	b	b
2017d	a	a
2018d	a	b
2019d	b	a
2020d	b	b
2021select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
2022a1	a2	b
2023a	b	a
2024b	b	a
2025c	b	a
2026d	b	a
2027select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
2028a1	a2	b	c
2029a	b	a	i121
2030select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
2031a1	a2	b
2032select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
2033b
2034a
2035select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b' group by a1;
2036a1
2037a
2038d
2039select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e' group by a1;
2040a1
2041select distinct a1,a2,b from t2;
2042a1	a2	b
2043a	a	NULL
2044a	a	a
2045a	a	b
2046a	b	a
2047a	b	b
2048b	a	a
2049b	a	b
2050b	b	a
2051b	b	b
2052c	a	NULL
2053c	a	a
2054c	a	b
2055c	b	a
2056c	b	b
2057d	a	a
2058d	a	b
2059d	b	a
2060d	b	b
2061e	a	a
2062e	a	b
2063select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
2064a1	a2	b
2065a	b	a
2066b	b	a
2067c	b	a
2068d	b	a
2069select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
2070a1	a2	b	c
2071a	b	a	i121
2072select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
2073a1	a2	b
2074select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
2075b
2076a
2077select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b' group by a1;
2078a1
2079a
2080d
2081select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e' group by a1;
2082a1
2083explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
2084id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
20851	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	251	Using where; Using index for group-by
2086explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
2087id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
20881	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	501	Using where; Using index for group-by (scanning)
2089explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
2090id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
20911	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	100.00	Using where; Using index for group-by
2092Warnings:
2093Note	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'
2094explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
2095id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
20961	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	1000	Using where; Using index
2097explain extended select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
2098id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
20991	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	100.00	Using where; Using index for group-by
2100Warnings:
2101Note	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'
2102select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
2103count(distinct a1,a2,b)
21044
2105select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
2106count(distinct a1,a2,b,c)
21071
2108select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
2109count(distinct a1,a2,b)
21100
2111select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
2112count(distinct b)
21131
2114select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
211598 + count(distinct a1,a2,b)
2116104
2117explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
2118id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
21191	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
2120explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
2121id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
21221	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
2123explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
2124id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
21251	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
2126explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
2127id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
21281	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	251	Using where; Using index for group-by
2129explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
2130id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
21311	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	126	Using index for group-by
2132select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
2133a1	a2	b	concat(min(c), max(c))
2134a	a	a	a111d111
2135a	a	b	e112h112
2136a	b	a	i121l121
2137a	b	b	m122p122
2138b	a	a	a211d211
2139b	a	b	e212h212
2140b	b	a	i221l221
2141b	b	b	m222p222
2142c	a	a	a311d311
2143c	a	b	e312h312
2144c	b	a	i321l321
2145c	b	b	m322p322
2146select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
2147concat(a1,min(c))	b
2148aa111	a
2149ae112	b
2150ai121	a
2151am122	b
2152ba211	a
2153be212	b
2154bi221	a
2155bm222	b
2156ca311	a
2157ce312	b
2158ci321	a
2159cm322	b
2160select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
2161concat(a1,min(c))	b	max(c)
2162aa111	a	d111
2163ae112	b	h112
2164ai121	a	l121
2165am122	b	p122
2166ba211	a	d211
2167be212	b	h212
2168bi221	a	l221
2169bm222	b	p222
2170ca311	a	d311
2171ce312	b	h312
2172ci321	a	l321
2173cm322	b	p322
2174select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
2175concat(a1,a2)	b	min(c)	max(c)
2176aa	a	a111	d111
2177aa	b	e112	h112
2178ab	a	i121	l121
2179ab	b	m122	p122
2180ba	a	a211	d211
2181ba	b	e212	h212
2182bb	a	i221	l221
2183bb	b	m222	p222
2184ca	a	a311	d311
2185ca	b	e312	h312
2186cb	a	i321	l321
2187cb	b	m322	p322
2188select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
2189concat(ord(min(b)),ord(max(b)))	min(b)	max(b)
21909798	a	b
21919798	a	b
21929798	a	b
21939798	a	b
21949798	a	b
21959798	a	b
21969798	a	b
21979798	a	b
2198explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b;
2199id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22001	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	1000
2201explain select a1,a2,b,d from t1 group by a1,a2,b;
2202id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22031	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	1000
2204explain extended select a1,a2,min(b),max(b) from t1
2205where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;
2206id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
22071	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	130	NULL	1000	100.00	Using where; Using index
2208Warnings:
2209Note	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`
2210explain extended select a1,a2,b,min(c),max(c) from t1
2211where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
2212id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
22131	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	130	NULL	1000	100.00	Using where
2214Warnings:
2215Note	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`
2216explain extended select a1,a2,b,c from t1
2217where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
2218id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
22191	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	130	NULL	1000	100.00	Using where
2220Warnings:
2221Note	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`
2222explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
2223id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22241	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
2225explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b < 'b') group by a1;
2226id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22271	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
2228explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b') group by a1;
2229id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22301	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
2231explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b' and b >= 'a') group by a1;
2232id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22331	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
2234explain 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;
2235id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
22361	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	130	NULL	1000	100.00	Using where; Using index
2237Warnings:
2238Note	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`
2239explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
2240id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22411	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
2242select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
2243a1	a2	min(b)	c
2244a	a	a	a111
2245explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1;
2246id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22471	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
2248explain select a1,a2,b,min(c),max(c) from t2
2249where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b;
2250id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22511	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
2252explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c;
2253id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22541	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	1000
2255explain select a1,a2,count(a2) from t1 group by a1,a2,b;
2256id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22571	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	1000	Using index
2258explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
2259id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
22601	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	65	NULL	1000	100.00	Using where; Using index
2261Warnings:
2262Note	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`
2263explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
2264id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
22651	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	65	NULL	1000	100.00	Using where; Using index
2266Warnings:
2267Note	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`
2268explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1;
2269id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22701	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
2271explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1;
2272id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22731	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	1000	Using where; Using index
2274explain select distinct(a1) from t1 where ord(a2) = 98;
2275id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22761	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	1000	Using where; Using index
2277select distinct(a1) from t1 where ord(a2) = 98;
2278a1
2279a
2280b
2281c
2282d
2283explain select a1 from t1 where a2 = 'b' group by a1;
2284id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22851	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	63	Using where; Using index for group-by
2286select a1 from t1 where a2 = 'b' group by a1;
2287a1
2288a
2289b
2290c
2291d
2292explain select distinct a1 from t1 where a2 = 'b';
2293id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22941	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	63	Using where; Using index for group-by
2295select distinct a1 from t1 where a2 = 'b';
2296a1
2297a
2298b
2299c
2300d
2301drop table t1,t2,t3;
2302create table t1 (c1 int not null,c2 int not null, primary key(c1,c2)) engine=RocksDB;
2303insert into t1 (c1,c2) values
2304(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9);
2305select distinct c1, c2 from t1 order by c2;
2306c1	c2
230710	1
230810	2
230910	3
231020	4
231120	5
231220	6
231330	7
231430	8
231530	9
2316select c1,min(c2) as c2 from t1 group by c1 order by c2;
2317c1	c2
231810	1
231920	4
232030	7
2321select c1,c2 from t1 group by c1,c2 order by c2;
2322c1	c2
232310	1
232410	2
232510	3
232620	4
232720	5
232820	6
232930	7
233030	8
233130	9
2332drop table t1;
2333CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b)) engine=RocksDB;
2334INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4);
2335OPTIMIZE TABLE t1;
2336Table	Op	Msg_type	Msg_text
2337test.t1	optimize	status	OK
2338SELECT a FROM t1 WHERE a='AA' GROUP BY a;
2339a
2340AA
2341SELECT a FROM t1 WHERE a='BB' GROUP BY a;
2342a
2343BB
2344EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a;
2345id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
23461	SIMPLE	t1	ref	PRIMARY	PRIMARY	7	const	1000	Using where; Using index
2347EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a;
2348id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
23491	SIMPLE	t1	ref	PRIMARY	PRIMARY	7	const	1000	Using where; Using index
2350SELECT DISTINCT a FROM t1 WHERE a='BB';
2351a
2352BB
2353SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%';
2354a
2355BB
2356SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a;
2357a
2358BB
2359DROP TABLE t1;
2360CREATE TABLE t1 (
2361a int(11) NOT NULL DEFAULT '0',
2362b varchar(16) COLLATE latin1_general_ci NOT NULL DEFAULT '',
2363PRIMARY KEY  (a,b)
2364) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
2365CREATE PROCEDURE a(x INT)
2366BEGIN
2367DECLARE rnd INT;
2368DECLARE cnt INT;
2369WHILE x > 0 DO
2370SET rnd= x % 100;
2371SET cnt = (SELECT COUNT(*) FROM t1 WHERE a = rnd);
2372INSERT INTO t1(a,b) VALUES (rnd, CAST(cnt AS CHAR));
2373SET x= x - 1;
2374END WHILE;
2375END|
2376CALL a(1000);
2377SELECT a FROM t1 WHERE a=0;
2378a
23790
23800
23810
23820
23830
23840
23850
23860
23870
23880
2389SELECT DISTINCT a FROM t1 WHERE a=0;
2390a
23910
2392SELECT COUNT(DISTINCT a) FROM t1 WHERE a=0;
2393COUNT(DISTINCT a)
23941
2395DROP TABLE t1;
2396DROP PROCEDURE a;
2397CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a)) engine=RocksDB;
2398INSERT INTO t1 (a) VALUES
2399(''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'),
2400('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'),
2401('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN');
2402EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a;
2403id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
24041	SIMPLE	t1	index	NULL	PRIMARY	66	NULL	1000	Using index
2405SELECT DISTINCT a,a FROM t1 ORDER BY a;
2406a	a
2407
2408CENTRAL	CENTRAL
2409EASTERN	EASTERN
2410GREATER LONDON	GREATER LONDON
2411NORTH CENTRAL	NORTH CENTRAL
2412NORTH EAST	NORTH EAST
2413NORTH WEST	NORTH WEST
2414SCOTLAND	SCOTLAND
2415SOUTH EAST	SOUTH EAST
2416SOUTH WEST	SOUTH WEST
2417WESTERN	WESTERN
2418DROP TABLE t1;
2419CREATE TABLE t1 (id1 INT, id2 INT) engine=RocksDB;
2420CREATE TABLE t2 (id2 INT, id3 INT, id5 INT) engine=RocksDB;
2421CREATE TABLE t3 (id3 INT, id4 INT) engine=RocksDB;
2422CREATE TABLE t4 (id4 INT) engine=RocksDB;
2423CREATE TABLE t5 (id5 INT, id6 INT) engine=RocksDB;
2424CREATE TABLE t6 (id6 INT) engine=RocksDB;
2425INSERT INTO t1 VALUES(1,1);
2426INSERT INTO t2 VALUES(1,1,1);
2427INSERT INTO t3 VALUES(1,1);
2428INSERT INTO t4 VALUES(1);
2429INSERT INTO t5 VALUES(1,1);
2430INSERT INTO t6 VALUES(1);
2431SELECT * FROM
2432t1
2433NATURAL JOIN
2434(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
2435ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
2436id2	id1	id3	id5	id4	id3	id6	id5
24371	1	1	1	1	1	1	1
2438SELECT * FROM
2439t1
2440NATURAL JOIN
2441(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2
2442ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
2443id2	id1	id4	id3	id6	id5	id3	id5
24441	1	1	1	1	1	1	1
2445SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2);
2446id2	id1	id3	id4	id6	id5	id3	id5
24471	1	1	1	1	1	1	1
2448SELECT * FROM
2449(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
2450ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5))
2451NATURAL JOIN
2452t1;
2453id2	id3	id5	id4	id3	id6	id5	id1
24541	1	1	1	1	1	1	1
2455SELECT * FROM
2456(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6)))
2457NATURAL JOIN
2458t1;
2459id2	id3	id5	id4	id3	id6	id5	id1
24601	1	1	1	1	1	1	1
2461DROP TABLE t1,t2,t3,t4,t5,t6;
2462CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)) engine=RocksDB;
2463INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
2464ANALYZE TABLE t1;
2465Table	Op	Msg_type	Msg_text
2466test.t1	analyze	status	OK
2467explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
2468id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
24691	SIMPLE	t1	range	PRIMARY,b	PRIMARY	8	NULL	501	Using where; Using index for group-by
2470SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
2471MAX(b)	a
24721	1
2473SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
2474MIN(b)	a
24752	1
2476CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)) engine=RocksDB;
2477INSERT INTO t2 SELECT a,b,b FROM t1;
2478ANALYZE TABLE t2;
2479Table	Op	Msg_type	Msg_text
2480test.t2	analyze	status	OK
2481explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
2482id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
24831	SIMPLE	t2	range	PRIMARY	PRIMARY	12	NULL	251	Using where; Using index for group-by
2484SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
2485MIN(c)
24862
2487DROP TABLE t1,t2;
2488CREATE TABLE t1 (a INT, b INT, INDEX (a,b)) engine=RocksDB;
2489INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
2490(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
2491ANALYZE TABLE t1;
2492Table	Op	Msg_type	Msg_text
2493test.t1	analyze	status	OK
2494EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
2495id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
24961	SIMPLE	t1	range	NULL	a	5	NULL	251	Using index for group-by
2497FLUSH STATUS;
2498SELECT max(b), a FROM t1 GROUP BY a;
2499max(b)	a
25005	1
25013	2
25021	3
25036	4
2504SHOW STATUS LIKE 'handler_read__e%';
2505Variable_name	Value
2506Handler_read_key	8
2507Handler_read_next	0
2508Handler_read_retry	0
2509EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
2510id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25111	SIMPLE	t1	range	NULL	a	5	NULL	251	Using index for group-by
2512FLUSH STATUS;
2513CREATE TABLE t2 engine=RocksDB SELECT max(b), a FROM t1 GROUP BY a;
2514SHOW STATUS LIKE 'handler_read__e%';
2515Variable_name	Value
2516Handler_read_key	8
2517Handler_read_next	0
2518Handler_read_retry	0
2519FLUSH STATUS;
2520SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
2521max(b)	a
25225	1
25233	2
25241	3
25256	4
2526SHOW STATUS LIKE 'handler_read__e%';
2527Variable_name	Value
2528Handler_read_key	8
2529Handler_read_next	0
2530Handler_read_retry	0
2531FLUSH STATUS;
2532(SELECT max(b), a FROM t1 GROUP BY a) UNION
2533(SELECT max(b), a FROM t1 GROUP BY a);
2534max(b)	a
25355	1
25363	2
25371	3
25386	4
2539SHOW STATUS LIKE 'handler_read__e%';
2540Variable_name	Value
2541Handler_read_key	16
2542Handler_read_next	0
2543Handler_read_retry	0
2544EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION
2545(SELECT max(b), a FROM t1 GROUP BY a);
2546id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25471	PRIMARY	t1	range	NULL	a	5	NULL	251	Using index for group-by
25482	UNION	t1	range	NULL	a	5	NULL	251	Using index for group-by
2549NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL
2550EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
2551FROM t1 AS t1_outer;
2552id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25531	PRIMARY	t1_outer	index	NULL	a	10	NULL	1000	Using index
25542	SUBQUERY	t1	range	NULL	a	5	NULL	251	Using index for group-by
2555EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS
2556(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
2557id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25581	PRIMARY	t1_outer	index	NULL	a	10	NULL	1000	Using index
25592	SUBQUERY	t1	index	NULL	a	10	NULL	1000	Using index
2560EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
2561(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
2562id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25631	PRIMARY	t1_outer	index	NULL	a	10	NULL	1000	Using index
25642	SUBQUERY	t1	range	NULL	a	5	NULL	251	Using index for group-by
2565EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
2566a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
2567id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25681	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	251
25691	PRIMARY	t1_outer	ref	a	a	5	<subquery2>.max(b)	4	Using index
25702	MATERIALIZED	t1	range	NULL	a	5	NULL	251	Using index for group-by
2571EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
2572a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
2573id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25741	PRIMARY	t1_outer	range	NULL	a	5	NULL	251	Using index for group-by
25752	SUBQUERY	t1	range	NULL	a	5	NULL	251	Using index for group-by
2576EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2
2577ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2)
2578AND t1_outer1.b = t1_outer2.b;
2579id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25801	PRIMARY	t1_outer1	ref	a	a	5	const	4	Using where; Using index
25811	PRIMARY	t1_outer2	index	NULL	a	10	NULL	1000	Using where; Using index; Using join buffer (flat, BNL join)
25822	SUBQUERY	t1	range	NULL	a	5	NULL	251	Using index for group-by
2583EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
2584FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
2585id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25861	PRIMARY	t1_outer2	index	NULL	a	10	NULL	1000	Using index
25872	SUBQUERY	t1_outer	index	NULL	a	10	NULL	1000	Using index
25883	SUBQUERY	t1	range	NULL	a	5	NULL	251	Using index for group-by
2589CREATE TABLE t3 LIKE t1;
2590FLUSH STATUS;
2591INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;
2592SHOW STATUS LIKE 'handler_read__e%';
2593Variable_name	Value
2594Handler_read_key	8
2595Handler_read_next	0
2596Handler_read_retry	0
2597DELETE FROM t3;
2598FLUSH STATUS;
2599INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2)
2600FROM t1 LIMIT 1;
2601SHOW STATUS LIKE 'handler_read__e%';
2602Variable_name	Value
2603Handler_read_key	8
2604Handler_read_next	0
2605Handler_read_retry	0
2606FLUSH STATUS;
2607DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
2608SHOW STATUS LIKE 'handler_read__e%';
2609Variable_name	Value
2610Handler_read_key	8
2611Handler_read_next	0
2612Handler_read_retry	0
2613FLUSH STATUS;
2614DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x
2615FROM t1) > 10000;
2616ERROR 21000: Subquery returns more than 1 row
2617SHOW STATUS LIKE 'handler_read__e%';
2618Variable_name	Value
2619Handler_read_key	8
2620Handler_read_next	1
2621Handler_read_retry	0
2622DROP TABLE t1,t2,t3;
2623CREATE TABLE t1 (a int, INDEX idx(a)) engine=RocksDB;
2624INSERT INTO t1 VALUES
2625(4), (2), (1), (2), (4), (2), (1), (4),
2626(4), (2), (1), (2), (2), (4), (1), (4);
2627ANALYZE TABLE t1;
2628Table	Op	Msg_type	Msg_text
2629test.t1	analyze	status	OK
2630EXPLAIN SELECT DISTINCT(a) FROM t1;
2631id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
26321	SIMPLE	t1	range	NULL	idx	5	NULL	501	Using index for group-by
2633SELECT DISTINCT(a) FROM t1;
2634a
26351
26362
26374
2638EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
2639id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
26401	SIMPLE	t1	range	NULL	idx	5	NULL	501	Using index for group-by
2641SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
2642a
26431
26442
26454
2646DROP TABLE t1;
2647CREATE TABLE t1 (a INT, b INT) engine=RocksDB;
2648INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3);
2649INSERT INTO t1 SELECT a + 1, b FROM t1;
2650INSERT INTO t1 SELECT a + 2, b FROM t1;
2651ANALYZE TABLE t1;
2652Table	Op	Msg_type	Msg_text
2653test.t1	analyze	status	OK
2654EXPLAIN
2655SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2656id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
26571	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using temporary; Using filesort
2658SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2659a	MIN(b)	MAX(b)
26604	1	3
26613	1	3
26622	1	3
26631	1	3
2664CREATE INDEX break_it ON t1 (a, b);
2665EXPLAIN
2666SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
2667id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
26681	SIMPLE	t1	range	NULL	break_it	10	NULL	251	Using index for group-by
2669SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
2670a	MIN(b)	MAX(b)
26711	1	3
26722	1	3
26733	1	3
26744	1	3
2675EXPLAIN
2676SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2677id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
26781	SIMPLE	t1	range	NULL	break_it	10	NULL	251	Using index for group-by; Using temporary; Using filesort
2679SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2680a	MIN(b)	MAX(b)
26814	1	3
26823	1	3
26832	1	3
26841	1	3
2685EXPLAIN
2686SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
2687id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
26881	SIMPLE	t1	index	NULL	break_it	10	NULL	1000	Using index
2689SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
2690a	MIN(b)	MAX(b)	AVG(b)
26914	1	3	2.0000
26923	1	3	2.0000
26932	1	3	2.0000
26941	1	3	2.0000
2695DROP TABLE t1;
2696create table t1 (a int, b int, primary key (a,b), key `index` (a,b)) engine=MyISAM;
2697insert into  t1 (a,b) values
2698(0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6),
2699(0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13),
2700(1,0),(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),
2701(1,7),(1,8),(1,9),(1,10),(1,11),(1,12),(1,13),
2702(2,0),(2,1),(2,2),(2,3),(2,4),(2,5),(2,6),
2703(2,7),(2,8),(2,9),(2,10),(2,11),(2,12),(2,13),
2704(3,0),(3,1),(3,2),(3,3),(3,4),(3,5),(3,6),
2705(3,7),(3,8),(3,9),(3,10),(3,11),(3,12),(3,13);
2706insert into t1 (a,b) select a, max(b)+1 from t1 where a = 0 group by a;
2707ANALYZE TABLE t1;
2708Table	Op	Msg_type	Msg_text
2709test.t1	analyze	status	OK
2710select * from t1;
2711a	b
27120	0
27130	1
27140	2
27150	3
27160	4
27170	5
27180	6
27190	7
27200	8
27210	9
27220	10
27230	11
27240	12
27250	13
27260	14
27271	0
27281	1
27291	2
27301	3
27311	4
27321	5
27331	6
27341	7
27351	8
27361	9
27371	10
27381	11
27391	12
27401	13
27412	0
27422	1
27432	2
27442	3
27452	4
27462	5
27472	6
27482	7
27492	8
27502	9
27512	10
27522	11
27532	12
27542	13
27553	0
27563	1
27573	2
27583	3
27593	4
27603	5
27613	6
27623	7
27633	8
27643	9
27653	10
27663	11
27673	12
27683	13
2769explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a;
2770id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
27711	SIMPLE	t1	ref	PRIMARY,index	PRIMARY	4	const	15	100.00	Using index; Using temporary
2772Warnings:
2773Note	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`
2774drop table t1;
2775CREATE TABLE t1 (a int, b int, c int, d int,
2776KEY foo (c,d,a,b), KEY bar (c,a,b,d)) engine=RocksDB;
2777INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4);
2778INSERT INTO t1 SELECT * FROM t1;
2779INSERT INTO t1 SELECT * FROM t1;
2780INSERT INTO t1 SELECT a,b,c+1,d FROM t1;
2781ANALYZE TABLE t1;
2782Table	Op	Msg_type	Msg_text
2783test.t1	analyze	status	OK
2784EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4;
2785id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
27861	SIMPLE	t1	range	NULL	foo	10	NULL	63	Using where; Using index for group-by
2787SELECT DISTINCT c FROM t1 WHERE d=4;
2788c
27891
27902
2791DROP TABLE t1;
2792#
2793# Bug #45386: Wrong query result with MIN function in field list,
2794#  WHERE and GROUP BY clause
2795#
2796CREATE TABLE t (a INT, b INT, INDEX (a,b)) engine=RocksDB;
2797INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
2798INSERT INTO t SELECT * FROM t;
2799INSERT INTO t SELECT * FROM t;
2800ANALYZE TABLE t;
2801Table	Op	Msg_type	Msg_text
2802test.t	analyze	status	OK
2803# test MIN
2804#should use range with index for group by
2805EXPLAIN
2806SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
2807id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
28081	SIMPLE	t	range	NULL	a	10	NULL	251	Using where; Using index for group-by
2809#should return 1 row
2810SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
2811a	MIN(b)
28122	1
2813# test MAX
2814#should use range with index for group by
2815EXPLAIN
2816SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
2817id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
28181	SIMPLE	t	range	NULL	a	10	NULL	251	Using where; Using index for group-by
2819#should return 1 row
2820SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
2821a	MAX(b)
28222	0
2823# test 3 ranges and use the middle one
2824INSERT INTO t SELECT a, 2 FROM t;
2825#should use range with index for group by
2826EXPLAIN
2827SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
2828id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
28291	SIMPLE	t	range	NULL	a	10	NULL	251	Using where; Using index for group-by
2830#should return 1 row
2831SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
2832a	MAX(b)
28332	1
2834DROP TABLE t;
2835#
2836# Bug #48472: Loose index scan inappropriately chosen for some WHERE
2837#             conditions
2838#
2839CREATE TABLE t (a INT, b INT, INDEX (a,b)) engine=RocksDB;
2840INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
2841INSERT INTO t SELECT * FROM t;
2842ANALYZE TABLE t;
2843Table	Op	Msg_type	Msg_text
2844test.t	analyze	status	OK
2845SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a;
2846a	MAX(b)
28472	0
2848DROP TABLE t;
2849End of 5.0 tests
2850#
2851# Bug #46607: Assertion failed: (cond_type == Item::FUNC_ITEM) results in
2852#              server crash
2853#
2854CREATE TABLE t (a INT, b INT, INDEX (a,b)) engine=RocksDB;
2855INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
2856INSERT INTO t SELECT * FROM t;
2857SELECT a, MAX(b) FROM t WHERE b GROUP BY a;
2858a	MAX(b)
28592	1
2860DROP TABLE t;
2861CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, KEY (b)) engine=RocksDB;
2862INSERT INTO t1 VALUES(1,1),(2,1);
2863ANALYZE TABLE t1;
2864Table	Op	Msg_type	Msg_text
2865test.t1	analyze	status	OK
2866SELECT 1 AS c, b FROM t1 WHERE b IN (1,2) GROUP BY c, b;
2867c	b
28681	1
2869SELECT a FROM t1 WHERE b=1;
2870a
28711
28722
2873DROP TABLE t1;
2874#
2875# Bug#47762: Incorrect result from MIN() when WHERE tests NOT NULL column
2876#            for NULL
2877#
2878## Test for NULLs allowed
2879CREATE TABLE t1 ( a INT, KEY (a) ) engine=RocksDB;
2880INSERT INTO t1 VALUES (1), (2), (3);
2881ANALYZE TABLE t1;
2882Table	Op	Msg_type	Msg_text
2883test.t1	analyze	status	OK
2884EXPLAIN
2885SELECT MIN( a ) FROM t1 WHERE a = NULL;
2886id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2887x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2888SELECT MIN( a ) FROM t1 WHERE a = NULL;
2889MIN( a )
2890NULL
2891EXPLAIN
2892SELECT MIN( a ) FROM t1 WHERE a <> NULL;
2893id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2894x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2895SELECT MIN( a ) FROM t1 WHERE a <> NULL;
2896MIN( a )
2897NULL
2898EXPLAIN
2899SELECT MIN( a ) FROM t1 WHERE a > NULL;
2900id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2901x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2902SELECT MIN( a ) FROM t1 WHERE a > NULL;
2903MIN( a )
2904NULL
2905EXPLAIN
2906SELECT MIN( a ) FROM t1 WHERE a < NULL;
2907id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2908x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2909SELECT MIN( a ) FROM t1 WHERE a < NULL;
2910MIN( a )
2911NULL
2912EXPLAIN
2913SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
2914id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2915x	x	x	x	x	x	x	x	x	No matching min/max row
2916SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
2917MIN( a )
2918NULL
2919EXPLAIN
2920SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
2921id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2922x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2923SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
2924MIN( a )
2925NULL
2926EXPLAIN
2927SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
2928id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2929x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2930SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
2931MIN( a )
2932NULL
2933EXPLAIN
2934SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
2935id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2936x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2937SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
2938MIN( a )
2939NULL
2940EXPLAIN
2941SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
2942id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2943x	x	x	x	x	x	x	x	x	Using where; Using index
2944x	x	x	x	x	x	x	x	x	Using where; Using index
2945SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
2946MIN( a )
2947NULL
2948EXPLAIN
2949SELECT MIN( a ) FROM t1 WHERE a IS NULL;
2950id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2951x	x	x	x	x	x	x	x	x	No matching min/max row
2952SELECT MIN( a ) FROM t1 WHERE a IS NULL;
2953MIN( a )
2954NULL
2955INSERT INTO t1 VALUES (NULL), (NULL);
2956ANALYZE TABLE t1;
2957Table	Op	Msg_type	Msg_text
2958test.t1	analyze	status	OK
2959EXPLAIN
2960SELECT MIN( a ) FROM t1 WHERE a = NULL;
2961id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2962x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2963SELECT MIN( a ) FROM t1 WHERE a = NULL;
2964MIN( a )
2965NULL
2966EXPLAIN
2967SELECT MIN( a ) FROM t1 WHERE a <> NULL;
2968id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2969x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2970SELECT MIN( a ) FROM t1 WHERE a <> NULL;
2971MIN( a )
2972NULL
2973EXPLAIN
2974SELECT MIN( a ) FROM t1 WHERE a > NULL;
2975id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2976x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2977SELECT MIN( a ) FROM t1 WHERE a > NULL;
2978MIN( a )
2979NULL
2980EXPLAIN
2981SELECT MIN( a ) FROM t1 WHERE a < NULL;
2982id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2983x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2984SELECT MIN( a ) FROM t1 WHERE a < NULL;
2985MIN( a )
2986NULL
2987EXPLAIN
2988SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
2989id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2990x	x	x	x	x	x	x	x	x	Select tables optimized away
2991SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
2992MIN( a )
2993NULL
2994EXPLAIN
2995SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
2996id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2997x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2998SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
2999MIN( a )
3000NULL
3001EXPLAIN
3002SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
3003id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3004x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3005SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
3006MIN( a )
3007NULL
3008EXPLAIN
3009SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
3010id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3011x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3012SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
3013MIN( a )
3014NULL
3015EXPLAIN
3016SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
3017id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3018x	x	x	x	x	x	x	x	x	Using where; Using index
3019x	x	x	x	x	x	x	x	x	Using where; Using index
3020SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
3021MIN( a )
3022NULL
3023EXPLAIN
3024SELECT MIN( a ) FROM t1 WHERE a IS NULL;
3025id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3026x	x	x	x	x	x	x	x	x	Select tables optimized away
3027SELECT MIN( a ) FROM t1 WHERE a IS NULL;
3028MIN( a )
3029NULL
3030DROP TABLE t1;
3031## Test for NOT NULLs
3032CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY) engine=RocksDB;
3033INSERT INTO t1 VALUES (1), (2), (3);
3034ANALYZE TABLE t1;
3035Table	Op	Msg_type	Msg_text
3036test.t1	analyze	status	OK
3037#
3038# NULL-safe operator test disabled for non-NULL indexed columns.
3039#
3040# See bugs
3041#
3042# - Bug#52173: Reading NULL value from non-NULL index gives
3043#   wrong result in embedded server
3044#
3045# - Bug#52174: Sometimes wrong plan when reading a MAX value from
3046#   non-NULL index
3047#
3048EXPLAIN
3049SELECT MIN( a ) FROM t1 WHERE a = NULL;
3050id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3051x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3052SELECT MIN( a ) FROM t1 WHERE a = NULL;
3053MIN( a )
3054NULL
3055EXPLAIN
3056SELECT MIN( a ) FROM t1 WHERE a <> NULL;
3057id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3058x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3059SELECT MIN( a ) FROM t1 WHERE a <> NULL;
3060MIN( a )
3061NULL
3062EXPLAIN
3063SELECT MIN( a ) FROM t1 WHERE a > NULL;
3064id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3065x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3066SELECT MIN( a ) FROM t1 WHERE a > NULL;
3067MIN( a )
3068NULL
3069EXPLAIN
3070SELECT MIN( a ) FROM t1 WHERE a < NULL;
3071id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3072x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3073SELECT MIN( a ) FROM t1 WHERE a < NULL;
3074MIN( a )
3075NULL
3076EXPLAIN
3077SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
3078id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3079x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3080SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
3081MIN( a )
3082NULL
3083EXPLAIN
3084SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
3085id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3086x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3087SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
3088MIN( a )
3089NULL
3090EXPLAIN
3091SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
3092id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3093x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3094SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
3095MIN( a )
3096NULL
3097EXPLAIN
3098SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
3099id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3100x	x	x	x	x	x	x	x	x	Using where; Using index
3101x	x	x	x	x	x	x	x	x	Using where; Using index
3102SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
3103MIN( a )
3104NULL
3105EXPLAIN
3106SELECT MIN( a ) FROM t1 WHERE a IS NULL;
3107id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3108x	x	x	x	x	x	x	x	x	Impossible WHERE
3109SELECT MIN( a ) FROM t1 WHERE a IS NULL;
3110MIN( a )
3111NULL
3112DROP TABLE t1;
3113#
3114# Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&, Item*) at
3115# opt_sum.cc:305
3116#
3117CREATE TABLE t1 ( a INT, KEY (a) ) engine=RocksDB;
3118INSERT INTO t1 VALUES (1), (2), (3);
3119SELECT MIN( a ) AS min_a
3120FROM t1
3121WHERE a > 1 AND a IS NULL
3122ORDER BY min_a;
3123min_a
3124NULL
3125DROP TABLE t1;
3126End of 5.1 tests
3127#
3128# WL#3220 (Loose index scan for COUNT DISTINCT)
3129#
3130CREATE TABLE t1 (a INT, b INT, c INT, KEY (a,b)) engine=RocksDB;
3131INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1), (1,4,1);
3132INSERT INTO t1 SELECT a, b + 4, 1 FROM t1;
3133INSERT INTO t1 SELECT a + 1, b, 1 FROM t1;
3134ANALYZE TABLE t1;
3135Table	Op	Msg_type	Msg_text
3136test.t1	analyze	status	OK
3137CREATE TABLE t2 (a INT, b INT, c INT, d INT, e INT, f INT, KEY (a,b,c)) engine=RocksDB;
3138INSERT INTO t2 VALUES (1,1,1,1,1,1), (1,2,1,1,1,1), (1,3,1,1,1,1),
3139(1,4,1,1,1,1);
3140INSERT INTO t2 SELECT a, b + 4, c,d,e,f FROM t2;
3141INSERT INTO t2 SELECT a + 1, b, c,d,e,f FROM t2;
3142ANALYZE TABLE t2;
3143Table	Op	Msg_type	Msg_text
3144test.t2	analyze	status	OK
3145EXPLAIN SELECT COUNT(DISTINCT a) FROM t1;
3146id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
31471	SIMPLE	t1	range	NULL	a	5	NULL	251	Using index for group-by
3148SELECT COUNT(DISTINCT a) FROM t1;
3149COUNT(DISTINCT a)
31502
3151EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1;
3152id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
31531	SIMPLE	t1	range	NULL	a	10	NULL	501	Using index for group-by
3154SELECT COUNT(DISTINCT a,b) FROM t1;
3155COUNT(DISTINCT a,b)
315616
3157EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1;
3158id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
31591	SIMPLE	t1	range	NULL	a	10	NULL	501	Using index for group-by
3160SELECT COUNT(DISTINCT b,a) FROM t1;
3161COUNT(DISTINCT b,a)
316216
3163EXPLAIN SELECT COUNT(DISTINCT b) FROM t1;
3164id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
31651	SIMPLE	t1	index	NULL	a	10	NULL	1000	Using index
3166SELECT COUNT(DISTINCT b) FROM t1;
3167COUNT(DISTINCT b)
31688
3169EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a;
3170id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
31711	SIMPLE	t1	range	NULL	a	5	NULL	251	Using index for group-by
3172SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a;
3173COUNT(DISTINCT a)
31741
31751
3176EXPLAIN SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
3177id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
31781	SIMPLE	t1	range	NULL	a	10	NULL	501	Using index for group-by
3179SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
3180COUNT(DISTINCT b)
31818
31828
3183EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;
3184id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
31851	SIMPLE	t1	index	NULL	a	10	NULL	1000	Using index; Using filesort
3186SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;
3187COUNT(DISTINCT a)
31882
31892
31902
31912
31922
31932
31942
31952
3196EXPLAIN SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
3197id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
31981	SIMPLE	t1	index	NULL	a	10	NULL	1000	Using index
3199SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
3200COUNT(DISTINCT a)
32012
3202EXPLAIN SELECT COUNT(DISTINCT a, b + 0) FROM t1;
3203id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32041	SIMPLE	t1	index	NULL	a	10	NULL	1000	Using index
3205SELECT COUNT(DISTINCT a, b + 0) FROM t1;
3206COUNT(DISTINCT a, b + 0)
320716
3208EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10;
3209id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32101	SIMPLE	t1	index	NULL	a	10	NULL	1000	Using index
3211SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10;
3212COUNT(DISTINCT a)
32132
3214EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10;
3215id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32161	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000
3217SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10;
3218COUNT(DISTINCT a)
32192
3220EXPLAIN SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10;
3221id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32221	SIMPLE	t1	range	NULL	a	5	NULL	251	Using index for group-by
3223SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10;
32241
32251
3226EXPLAIN SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1;
3227id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32281	SIMPLE	t1	range	NULL	a	10	NULL	501	Using index for group-by
3229SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1;
32301
32311
32321
3233EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
3234id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32351	SIMPLE	t1_1	index	NULL	a	10	NULL	1000	Using index; Using temporary; Using filesort
32361	SIMPLE	t1_2	index	NULL	a	10	NULL	1000	Using index; Using join buffer (flat, BNL join)
3237SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
3238COUNT(DISTINCT t1_1.a)
32391
32401
3241EXPLAIN SELECT COUNT(DISTINCT a), 12 FROM t1;
3242id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32431	SIMPLE	t1	range	NULL	a	5	NULL	251	Using index for group-by
3244SELECT COUNT(DISTINCT a), 12 FROM t1;
3245COUNT(DISTINCT a)	12
32462	12
3247EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2;
3248id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32491	SIMPLE	t2	range	NULL	a	15	NULL	501	Using index for group-by
3250SELECT COUNT(DISTINCT a, b, c) FROM t2;
3251COUNT(DISTINCT a, b, c)
325216
3253EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2;
3254id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32551	SIMPLE	t2	range	NULL	a	5	NULL	126	Using index for group-by
3256SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2;
3257COUNT(DISTINCT a)	SUM(DISTINCT a)	AVG(DISTINCT a)
32582	3	1.5000
3259EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2;
3260id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32611	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1000
3262SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2;
3263COUNT(DISTINCT a)	SUM(DISTINCT a)	AVG(DISTINCT f)
32642	3	1.0000
3265EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;
3266id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32671	SIMPLE	t2	range	NULL	a	10	NULL	251	Using index for group-by
3268SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;
3269COUNT(DISTINCT a, b)	COUNT(DISTINCT b, a)
327016	16
3271EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2;
3272id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32731	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1000
3274SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2;
3275COUNT(DISTINCT a, b)	COUNT(DISTINCT b, f)
327616	8
3277EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2;
3278id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32791	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1000
3280SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2;
3281COUNT(DISTINCT a, b)	COUNT(DISTINCT b, d)
328216	8
3283EXPLAIN SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c;
3284id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32851	SIMPLE	t2	range	NULL	a	15	NULL	501	Using index for group-by
3286SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c;
3287a	c	COUNT(DISTINCT c, a, b)
32881	1	1
32891	1	1
32901	1	1
32911	1	1
32921	1	1
32931	1	1
32941	1	1
32951	1	1
32962	1	1
32972	1	1
32982	1	1
32992	1	1
33002	1	1
33012	1	1
33022	1	1
33032	1	1
3304EXPLAIN SELECT COUNT(DISTINCT c, a, b) FROM t2
3305WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;
3306id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33071	SIMPLE	t2	range	a	a	15	NULL	501	Using where; Using index for group-by
3308SELECT COUNT(DISTINCT c, a, b) FROM t2
3309WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;
3310COUNT(DISTINCT c, a, b)
3311EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5
3312GROUP BY b;
3313id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33141	SIMPLE	t2	ref	a	a	5	const	1000	Using where; Using index
3315SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5
3316GROUP BY b;
3317COUNT(DISTINCT b)	SUM(DISTINCT b)
3318EXPLAIN SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
3319id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33201	SIMPLE	t2	range	NULL	a	10	NULL	251	Using index for group-by
3321SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
3322a	COUNT(DISTINCT b)	SUM(DISTINCT b)
33231	8	36
33242	8	36
3325EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
3326id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33271	SIMPLE	t2	range	NULL	a	10	NULL	251	Using index for group-by
3328SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
3329COUNT(DISTINCT b)	SUM(DISTINCT b)
33308	36
33318	36
3332EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42;
3333id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33341	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1000	Using where
3335SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42;
3336COUNT(DISTINCT a, b)
33370
3338EXPLAIN SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2
3339WHERE b = 13 AND c = 42 GROUP BY a;
3340id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33411	SIMPLE	t2	range	NULL	a	15	NULL	126	Using where; Using index for group-by
3342SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2
3343WHERE b = 13 AND c = 42 GROUP BY a;
3344a	COUNT(DISTINCT a)	SUM(DISTINCT a)
3345# This query could have been resolved using loose index scan since
3346# the second part of count(..) is defined by a constant predicate
3347EXPLAIN SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42;
3348id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33491	SIMPLE	t2	index	NULL	a	15	NULL	1000	Using where; Using index
3350SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42;
3351COUNT(DISTINCT a, b)	SUM(DISTINCT a)
33520	NULL
3353EXPLAIN SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a;
3354id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33551	SIMPLE	t2	index	NULL	a	15	NULL	1000	Using index
3356SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a;
3357SUM(DISTINCT a)	MAX(b)
33581	8
33592	8
3360EXPLAIN SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c;
3361id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33621	SIMPLE	t2	range	NULL	a	15	NULL	501	Using index for group-by
3363SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c;
336442 * (a + c + COUNT(DISTINCT c, a, b))
3365126
3366126
3367126
3368126
3369126
3370126
3371126
3372126
3373168
3374168
3375168
3376168
3377168
3378168
3379168
3380168
3381EXPLAIN SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a;
3382id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33831	SIMPLE	t2	index	NULL	a	15	NULL	1000	Using index
3384SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a;
3385(SUM(DISTINCT a) + MAX(b))
33869
338710
3388DROP TABLE t1,t2;
3389# end of WL#3220 tests
3390#
3391# Bug#50539: Wrong result when loose index scan is used for an aggregate
3392#            function with distinct
3393#
3394CREATE TABLE t1 (
3395f1 int(11) NOT NULL DEFAULT '0',
3396f2 char(1) NOT NULL DEFAULT '',
3397PRIMARY KEY (f1,f2)
3398)  engine=RocksDB;
3399insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'),
3400(3, 'A'), (3, 'B'), (3, 'C'), (3, 'D');
3401ANALYZE TABLE t1;
3402Table	Op	Msg_type	Msg_text
3403test.t1	analyze	status	OK
3404SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
3405f1	COUNT(DISTINCT f2)
34061	3
34072	1
34083	4
3409explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
3410id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34111	SIMPLE	t1	index	NULL	PRIMARY	5	NULL	1000	Using index
3412drop table t1;
3413# End of test#50539.
3414#
3415# Bug#17217128 -  BAD INTERACTION BETWEEN MIN/MAX AND
3416#                 "HAVING SUM(DISTINCT)": WRONG RESULTS.
3417#
3418CREATE TABLE t (a INT, b INT, KEY(a,b)) engine=RocksDB;
3419INSERT INTO t VALUES (1,1), (2,2), (3,3), (4,4), (1,0), (3,2), (4,5);
3420ANALYZE TABLE t;
3421Table	Op	Msg_type	Msg_text
3422test.t	analyze	status	OK
3423ANALYZE TABLE t;
3424Table	Op	Msg_type	Msg_text
3425test.t	analyze	status	OK
3426SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a;
3427a	SUM(DISTINCT a)	MIN(b)
34281	1	0
34292	2	2
34303	3	2
34314	4	4
3432EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a;
3433id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34341	SIMPLE	t	index	NULL	a	10	NULL	1000	Using index
3435SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a;
3436a	SUM(DISTINCT a)	MAX(b)
34371	1	1
34382	2	2
34393	3	3
34404	4	5
3441EXPLAIN SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a;
3442id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34431	SIMPLE	t	index	NULL	a	10	NULL	1000	Using index
3444SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a);
3445a	MAX(b)
34461	1
34472	2
34483	3
34494	5
3450EXPLAIN SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a);
3451id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34521	SIMPLE	t	index	NULL	a	10	NULL	1000	Using index
3453SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t;
3454SUM(DISTINCT a)	MIN(b)	MAX(b)
345510	0	5
3456EXPLAIN SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t;
3457id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34581	SIMPLE	t	index	NULL	a	10	NULL	1000	Using index
3459SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a;
3460a	SUM(DISTINCT a)	MIN(b)	MAX(b)
34611	1	0	1
34622	2	2	2
34633	3	2	3
34644	4	4	5
3465EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a;
3466id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34671	SIMPLE	t	index	NULL	a	10	NULL	1000	Using index
3468DROP TABLE t;
3469#
3470# Bug#18109609: LOOSE INDEX SCAN IS NOT USED WHEN IT SHOULD
3471#
3472CREATE TABLE t1 (
3473id INT AUTO_INCREMENT PRIMARY KEY,
3474c1 INT,
3475c2 INT,
3476KEY(c1,c2)) engine=RocksDB;
3477INSERT INTO t1(c1,c2) VALUES
3478(1, 1), (1,2), (2,1), (2,2), (3,1), (3,2), (3,3), (4,1), (4,2), (4,3),
3479(4,4), (4,5), (4,6), (4,7), (4,8), (4,9), (4,10), (4,11), (4,12), (4,13),
3480(4,14), (4,15), (4,16), (4,17), (4,18), (4,19), (4,20),(5,5);
3481ANALYZE TABLE t1;
3482Table	Op	Msg_type	Msg_text
3483test.t1	analyze	status	OK
3484EXPLAIN SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1;
3485id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34861	SIMPLE	t1	ref	c1	c1	5	const	1000	Using index
3487FLUSH STATUS;
3488SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1;
3489MAX(c2)	c1
349020	4
3491SHOW SESSION STATUS LIKE 'Handler_read%';
3492Variable_name	Value
3493Handler_read_first	0
3494Handler_read_key	1
3495Handler_read_last	0
3496Handler_read_next	20
3497Handler_read_prev	0
3498Handler_read_retry	0
3499Handler_read_rnd	0
3500Handler_read_rnd_deleted	0
3501Handler_read_rnd_next	0
3502DROP TABLE t1;
3503# End of test for Bug#18109609
3504set global debug_dbug=@debug_tmp;
3505