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	Engine-independent statistics collected
46test.t1	analyze	status	OK
47drop table if exists t2;
48create table t2 (
49a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(248) default ' '
50) engine=RocksDB;
51insert into t2 select * from t1;
52insert into t2 (a1, a2, b, c, d) values
53('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
54('a','a','a',NULL,'xyz'),
55('a','a','b',NULL,'xyz'),
56('a','b','a',NULL,'xyz'),
57('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
58('d','b','b',NULL,'xyz'),
59('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
60('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),
61('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
62('a','a','a',NULL,'xyz'),
63('a','a','b',NULL,'xyz'),
64('a','b','a',NULL,'xyz'),
65('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
66('d','b','b',NULL,'xyz'),
67('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
68('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz');
69create index idx_t2_0 on t2 (a1);
70create index idx_t2_1 on t2 (a1,a2,b,c);
71create index idx_t2_2 on t2 (a1,a2,b);
72analyze table t2;
73Table	Op	Msg_type	Msg_text
74test.t2	analyze	status	Engine-independent statistics collected
75test.t2	analyze	status	OK
76drop table if exists t3;
77create table t3 (
78a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' '
79) engine=RocksDB;
80insert into t3 (a1, a2, b, c, d) values
81('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
82('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
83('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
84('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
85('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
86('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
87('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
88('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
89('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
90('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
91('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
92('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
93insert into t3 (a1, a2, b, c, d) values
94('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
95('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
96('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
97('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
98('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
99('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
100('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
101('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
102('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
103('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
104('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
105('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
106insert into t3 (a1, a2, b, c, d) values
107('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
108('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
109('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
110('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
111('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
112('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
113('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
114('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
115('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
116('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
117('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
118('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
119insert into t3 (a1, a2, b, c, d) values
120('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
121('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
122('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
123('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
124('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
125('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
126('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
127('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
128('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
129('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
130('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
131('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
132create index idx_t3_0 on t3 (a1);
133create index idx_t3_1 on t3 (a1,a2,b,c);
134create index idx_t3_2 on t3 (a1,a2,b);
135analyze table t3;
136Table	Op	Msg_type	Msg_text
137test.t3	analyze	status	Engine-independent statistics collected
138test.t3	analyze	status	OK
139explain select a1, min(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	130	NULL	5	Using index for group-by
142explain select a1, 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	65	NULL	5	Using index for group-by
145explain select a1, min(a2), max(a2) from t1 group by a1;
146id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1471	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	5	Using index for group-by
148explain select a1, a2, b, min(c), max(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	17	Using index for group-by
151explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
152id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1531	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using index for group-by
154explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
155id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1561	SIMPLE	t2	range	NULL	idx_t2_1	#	NULL	#	Using index for group-by
157explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
158id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1591	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	5	Using index for group-by
160explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
161id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1621	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using index for group-by
163explain select min(a2) from t1 group by a1;
164id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1651	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	5	Using index for group-by
166explain select a2, min(c), max(c) from t1 group by a1,a2,b;
167id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1681	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using index for group-by
169select a1, min(a2) from t1 group by a1;
170a1	min(a2)
171a	a
172b	a
173c	a
174d	a
175select a1, max(a2) from t1 group by a1;
176a1	max(a2)
177a	b
178b	b
179c	b
180d	b
181select a1, min(a2), max(a2) from t1 group by a1;
182a1	min(a2)	max(a2)
183a	a	b
184b	a	b
185c	a	b
186d	a	b
187select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
188a1	a2	b	min(c)	max(c)
189a	a	a	a111	d111
190a	a	b	e112	h112
191a	b	a	i121	l121
192a	b	b	m122	p122
193b	a	a	a211	d211
194b	a	b	e212	h212
195b	b	a	i221	l221
196b	b	b	m222	p222
197c	a	a	a311	d311
198c	a	b	e312	h312
199c	b	a	i321	l321
200c	b	b	m322	p322
201d	a	a	a411	d411
202d	a	b	e412	h412
203d	b	a	i421	l421
204d	b	b	m422	p422
205select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
206a1	a2	b	max(c)	min(c)
207a	a	a	d111	a111
208a	a	b	h112	e112
209a	b	a	l121	i121
210a	b	b	p122	m122
211b	a	a	d211	a211
212b	a	b	h212	e212
213b	b	a	l221	i221
214b	b	b	p222	m222
215c	a	a	d311	a311
216c	a	b	h312	e312
217c	b	a	l321	i321
218c	b	b	p322	m322
219d	a	a	d411	a411
220d	a	b	h412	e412
221d	b	a	l421	i421
222d	b	b	p422	m422
223select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
224a1	a2	b	max(c)	min(c)
225a	a	NULL	a999	a777
226a	a	a	d111	a111
227a	a	b	h112	e112
228a	b	a	l121	i121
229a	b	b	p122	m122
230b	a	a	d211	a211
231b	a	b	h212	e212
232b	b	a	l221	i221
233b	b	b	p222	m222
234c	a	NULL	c999	c777
235c	a	a	d311	a311
236c	a	b	h312	e312
237c	b	a	l321	i321
238c	b	b	p322	m322
239d	a	a	d411	a411
240d	a	b	h412	e412
241d	b	a	l421	i421
242d	b	b	p422	m422
243e	a	a	NULL	NULL
244e	a	b	NULL	NULL
245select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
246min(a2)	a1	max(a2)	min(a2)	a1
247a	a	b	a	a
248a	b	b	a	b
249a	c	b	a	c
250a	d	b	a	d
251select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
252a1	b	min(c)	a1	max(c)	b	a2	max(c)	max(c)
253a	a	a111	a	d111	a	a	d111	d111
254a	b	e112	a	h112	b	a	h112	h112
255a	a	i121	a	l121	a	b	l121	l121
256a	b	m122	a	p122	b	b	p122	p122
257b	a	a211	b	d211	a	a	d211	d211
258b	b	e212	b	h212	b	a	h212	h212
259b	a	i221	b	l221	a	b	l221	l221
260b	b	m222	b	p222	b	b	p222	p222
261c	a	a311	c	d311	a	a	d311	d311
262c	b	e312	c	h312	b	a	h312	h312
263c	a	i321	c	l321	a	b	l321	l321
264c	b	m322	c	p322	b	b	p322	p322
265d	a	a411	d	d411	a	a	d411	d411
266d	b	e412	d	h412	b	a	h412	h412
267d	a	i421	d	l421	a	b	l421	l421
268d	b	m422	d	p422	b	b	p422	p422
269select min(a2) from t1 group by a1;
270min(a2)
271a
272a
273a
274a
275select a2, min(c), max(c) from t1 group by a1,a2,b;
276a2	min(c)	max(c)
277a	a111	d111
278a	e112	h112
279b	i121	l121
280b	m122	p122
281a	a211	d211
282a	e212	h212
283b	i221	l221
284b	m222	p222
285a	a311	d311
286a	e312	h312
287b	i321	l321
288b	m322	p322
289a	a411	d411
290a	e412	h412
291b	i421	l421
292b	m422	p422
293explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' 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	17	Using where; Using index for group-by
296explain select a1,a2,b,min(c),max(c) from t1 where 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	17	Using where; Using index for group-by
299explain select a1,a2,b,       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	17	Using where; Using index for group-by
302explain select a1, max(c)            from t1 where a1 >= 'c' or a1 < '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	17	Using where; Using index for group-by
305explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' 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	17	Using where; Using index for group-by
308explain select a1,a2,b,       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	17	Using where; Using index for group-by
311explain select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' 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	17	Using where; Using index for group-by
314explain 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;
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	17	Using where; Using index for group-by
317explain 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;
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	17	Using where; Using index for group-by
320explain select a1,min(c),max(c)      from t1 where a1 >= 'b' 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	17	Using where; Using index for group-by
323explain select a1,  max(c)           from t1 where a1 in ('a','b','d') group by a1,a2,b;
324id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3251	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	17	Using where; Using index for group-by
326explain select a1,a2,b,       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	146	NULL	#	Using where; Using index for group-by
329explain select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' 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,min(c),max(c) from t2 where 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	163	NULL	#	Using where; Using index for group-by
335explain select a1,a2,b,       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, max(c)            from t2 where a1 >= 'c' or a1 < '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	146	NULL	#	Using where; Using index for group-by
341explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' 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	163	NULL	#	Using where; Using index for group-by
344explain select a1,a2,b,       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	146	NULL	#	Using where; Using index for group-by
347explain select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' 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	163	NULL	#	Using where; Using index for group-by
350explain 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;
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	146	NULL	#	Using where; Using index for group-by
353explain 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;
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,min(c),max(c)      from t2 where a1 >= 'b' 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	163	NULL	#	Using where; Using index for group-by
359explain select a1,  max(c)           from t2 where a1 in ('a','b','d') group by a1,a2,b;
360id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3611	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
362select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
363a1	a2	b	min(c)	max(c)
364a	a	a	a111	d111
365a	a	b	e112	h112
366a	b	a	i121	l121
367a	b	b	m122	p122
368b	a	a	a211	d211
369b	a	b	e212	h212
370b	b	a	i221	l221
371b	b	b	m222	p222
372c	a	a	a311	d311
373c	a	b	e312	h312
374c	b	a	i321	l321
375c	b	b	m322	p322
376select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
377a1	a2	b	min(c)	max(c)
378b	a	a	a211	d211
379b	a	b	e212	h212
380b	b	a	i221	l221
381b	b	b	m222	p222
382c	a	a	a311	d311
383c	a	b	e312	h312
384c	b	a	i321	l321
385c	b	b	m322	p322
386d	a	a	a411	d411
387d	a	b	e412	h412
388d	b	a	i421	l421
389d	b	b	m422	p422
390select a1,a2,b,       max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
391a1	a2	b	max(c)
392a	a	a	d111
393a	a	b	h112
394a	b	a	l121
395a	b	b	p122
396c	a	a	d311
397c	a	b	h312
398c	b	a	l321
399c	b	b	p322
400d	a	a	d411
401d	a	b	h412
402d	b	a	l421
403d	b	b	p422
404select a1, max(c)            from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
405a1	max(c)
406a	d111
407a	h112
408a	l121
409a	p122
410c	d311
411c	h312
412c	l321
413c	p322
414d	d411
415d	h412
416d	l421
417d	p422
418select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
419a1	a2	b	min(c)	max(c)
420a	a	a	a111	d111
421a	a	b	e112	h112
422b	a	a	a211	d211
423b	a	b	e212	h212
424c	a	a	a311	d311
425c	a	b	e312	h312
426c	b	a	i321	l321
427c	b	b	m322	p322
428d	a	a	a411	d411
429d	a	b	e412	h412
430d	b	a	i421	l421
431d	b	b	m422	p422
432select a1,a2,b,       max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
433a1	a2	b	max(c)
434b	a	a	d211
435b	a	b	h212
436b	b	a	l221
437b	b	b	p222
438d	a	a	d411
439d	a	b	h412
440d	b	a	l421
441d	b	b	p422
442select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
443a1	a2	b	min(c)	max(c)
444b	a	a	a211	d211
445b	a	b	e212	h212
446b	b	a	i221	l221
447b	b	b	m222	p222
448d	a	a	a411	d411
449d	a	b	e412	h412
450d	b	a	i421	l421
451d	b	b	m422	p422
452select 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;
453a1	a2	b	max(c)
454a	b	a	l121
455a	b	b	p122
456b	b	a	l221
457b	b	b	p222
458c	b	a	l321
459c	b	b	p322
460d	b	a	l421
461d	b	b	p422
462select 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;
463a1	a2	b	min(c)	max(c)
464a	b	a	i121	l121
465a	b	b	m122	p122
466b	b	a	i221	l221
467b	b	b	m222	p222
468c	b	a	i321	l321
469c	b	b	m322	p322
470d	b	a	i421	l421
471d	b	b	m422	p422
472select a1,min(c),max(c)      from t1 where a1 >= 'b' group by a1,a2,b;
473a1	min(c)	max(c)
474b	a211	d211
475b	e212	h212
476b	i221	l221
477b	m222	p222
478c	a311	d311
479c	e312	h312
480c	i321	l321
481c	m322	p322
482d	a411	d411
483d	e412	h412
484d	i421	l421
485d	m422	p422
486select a1,  max(c)           from t1 where a1 in ('a','b','d') group by a1,a2,b;
487a1	max(c)
488a	d111
489a	h112
490a	l121
491a	p122
492b	d211
493b	h212
494b	l221
495b	p222
496d	d411
497d	h412
498d	l421
499d	p422
500select a1,a2,b,       max(c) from t2 where a1 < 'd' group by a1,a2,b;
501a1	a2	b	max(c)
502a	a	NULL	a999
503a	a	a	d111
504a	a	b	h112
505a	b	a	l121
506a	b	b	p122
507b	a	a	d211
508b	a	b	h212
509b	b	a	l221
510b	b	b	p222
511c	a	NULL	c999
512c	a	a	d311
513c	a	b	h312
514c	b	a	l321
515c	b	b	p322
516select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
517a1	a2	b	min(c)	max(c)
518a	a	NULL	a777	a999
519a	a	a	a111	d111
520a	a	b	e112	h112
521a	b	a	i121	l121
522a	b	b	m122	p122
523b	a	a	a211	d211
524b	a	b	e212	h212
525b	b	a	i221	l221
526b	b	b	m222	p222
527c	a	NULL	c777	c999
528c	a	a	a311	d311
529c	a	b	e312	h312
530c	b	a	i321	l321
531c	b	b	m322	p322
532select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
533a1	a2	b	min(c)	max(c)
534b	a	a	a211	d211
535b	a	b	e212	h212
536b	b	a	i221	l221
537b	b	b	m222	p222
538c	a	NULL	c777	c999
539c	a	a	a311	d311
540c	a	b	e312	h312
541c	b	a	i321	l321
542c	b	b	m322	p322
543d	a	a	a411	d411
544d	a	b	e412	h412
545d	b	a	i421	l421
546d	b	b	m422	p422
547e	a	a	NULL	NULL
548e	a	b	NULL	NULL
549select a1,a2,b,       max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
550a1	a2	b	max(c)
551a	a	NULL	a999
552a	a	a	d111
553a	a	b	h112
554a	b	a	l121
555a	b	b	p122
556c	a	NULL	c999
557c	a	a	d311
558c	a	b	h312
559c	b	a	l321
560c	b	b	p322
561d	a	a	d411
562d	a	b	h412
563d	b	a	l421
564d	b	b	p422
565e	a	a	NULL
566e	a	b	NULL
567select a1, max(c)            from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
568a1	max(c)
569a	a999
570a	d111
571a	h112
572a	l121
573a	p122
574c	c999
575c	d311
576c	h312
577c	l321
578c	p322
579d	d411
580d	h412
581d	l421
582d	p422
583e	NULL
584e	NULL
585select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
586a1	a2	b	min(c)	max(c)
587a	a	NULL	a777	a999
588a	a	a	a111	d111
589a	a	b	e112	h112
590b	a	a	a211	d211
591b	a	b	e212	h212
592c	a	NULL	c777	c999
593c	a	a	a311	d311
594c	a	b	e312	h312
595c	b	a	i321	l321
596c	b	b	m322	p322
597d	a	a	a411	d411
598d	a	b	e412	h412
599d	b	a	i421	l421
600d	b	b	m422	p422
601e	a	a	NULL	NULL
602e	a	b	NULL	NULL
603select a1,a2,b,       max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
604a1	a2	b	max(c)
605b	a	a	d211
606b	a	b	h212
607b	b	a	l221
608b	b	b	p222
609d	a	a	d411
610d	a	b	h412
611d	b	a	l421
612d	b	b	p422
613select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
614a1	a2	b	min(c)	max(c)
615b	a	a	a211	d211
616b	a	b	e212	h212
617b	b	a	i221	l221
618b	b	b	m222	p222
619d	a	a	a411	d411
620d	a	b	e412	h412
621d	b	a	i421	l421
622d	b	b	m422	p422
623select 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;
624a1	a2	b	max(c)
625a	b	a	l121
626a	b	b	p122
627b	b	a	l221
628b	b	b	p222
629c	b	a	l321
630c	b	b	p322
631d	b	a	l421
632d	b	b	p422
633select 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;
634a1	a2	b	min(c)	max(c)
635a	b	a	i121	l121
636a	b	b	m122	p122
637b	b	a	i221	l221
638b	b	b	m222	p222
639c	b	a	i321	l321
640c	b	b	m322	p322
641d	b	a	i421	l421
642d	b	b	m422	p422
643select a1,min(c),max(c)      from t2 where a1 >= 'b' group by a1,a2,b;
644a1	min(c)	max(c)
645b	a211	d211
646b	e212	h212
647b	i221	l221
648b	m222	p222
649c	c777	c999
650c	a311	d311
651c	e312	h312
652c	i321	l321
653c	m322	p322
654d	a411	d411
655d	e412	h412
656d	i421	l421
657d	m422	p422
658e	NULL	NULL
659e	NULL	NULL
660select a1,  max(c)           from t2 where a1 in ('a','b','d') group by a1,a2,b;
661a1	max(c)
662a	a999
663a	d111
664a	h112
665a	l121
666a	p122
667b	d211
668b	h212
669b	l221
670b	p222
671d	d411
672d	h412
673d	l421
674d	p422
675#
676# MariaDB: we dont have the following patch:
677#
678#  commit 60a92a79a3b7fde3c6efe91799e344b977c8e5c3
679#  Author: Manuel Ung <mung@fb.com>
680#  Date:   Thu Apr 19 23:06:27 2018 -0700
681#
682#   Enhance group-by loose index scan
683#
684#  So the following results are not very meaningful, but are still kept here
685explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
686id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
6871	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	5	Using where; Using index for group-by
688explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') 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	128	Using where; Using index
691explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') 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	128	Using where; Using index
694explain 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;
695id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
6961	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	Using where; Using index
697explain select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b') group by a1;
698id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
6991	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	5	Using where; Using index for group-by
700explain select a1,max(c),min(c)      from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') 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	128	Using where; Using index
703explain select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
704id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7051	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	Using where; Using index
706explain select a1,a2,b,       max(c) from t1 where (b = 'b') group by a1,a2;
707id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7081	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	9	Using where; Using index for group-by
709explain select a1,a2,b,       max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2;
710id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7111	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	Using where; Using index
712explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
713id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7141	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	9	Using where; Using index for group-by
715explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2;
716id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7171	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	Using where; Using index
718explain select a1,a2, max(c)         from t1 where (b = 'b') group by a1,a2;
719id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7201	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	9	Using where; Using index for group-by
721explain select a1,a2, max(c)         from t1 where (b = 'b' or b = 'a') group by a1,a2;
722id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7231	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	Using where; Using index
724explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
725id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7261	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	6	Using where; Using index for group-by
727explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') 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	164	Using where; Using index
730explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
731id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7321	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
733explain select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b') group by a1;
734id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7351	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	6	Using where; Using index for group-by
736explain select a1,max(c),min(c)      from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') 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	164	Using where; Using index
739explain select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
740id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7411	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
742explain select a1,a2,b,       max(c) from t2 where (b = 'b') group by a1,a2;
743id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7441	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	10	Using where; Using index for group-by
745explain select a1,a2,b,       max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2;
746id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7471	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
748explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
749id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7501	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	10	Using where; Using index for group-by
751explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2;
752id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7531	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
754explain select a1,a2, max(c)         from t2 where (b = 'b') group by a1,a2;
755id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7561	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	10	Using where; Using index for group-by
757explain select a1,a2, max(c)         from t2 where (b = 'b' or b = 'a') group by a1,a2;
758id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7591	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
760explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
761id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7621	SIMPLE	t3	range	NULL	idx_t3_1	6	NULL	4	Using where; Using index for group-by
763explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') 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	192	Using where; Using index
766explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
767id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7681	SIMPLE	t3	index	NULL	idx_t3_1	10	NULL	192	Using where; Using index
769explain select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b') group by a1;
770id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7711	SIMPLE	t3	range	NULL	idx_t3_1	6	NULL	4	Using where; Using index for group-by
772explain select a1,max(c),min(c)      from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') 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	192	Using where; Using index
775explain select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
776id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
7771	SIMPLE	t3	index	NULL	idx_t3_1	10	NULL	192	Using where; Using index
778select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
779a1	a2	b	max(c)	min(c)
780a	a	b	h112	e112
781b	a	b	h212	e212
782c	a	b	h312	e312
783d	a	b	h412	e412
784select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
785a1	a2	b	max(c)	min(c)
786a	a	b	p122	e112
787b	a	b	p222	e212
788c	a	b	p322	e312
789d	a	b	p422	e412
790select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
791a1	a2	b	max(c)	min(c)
792a	a	a	h112	a111
793b	a	a	h212	a211
794c	a	a	h312	a311
795d	a	a	h412	a411
796select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b' or b = 'a') group by a1;
797a1	a2	b	max(c)	min(c)
798a	a	a	p122	a111
799b	a	a	p222	a211
800c	a	a	p322	a311
801d	a	a	p422	a411
802select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b') group by a1;
803a1	max(c)	min(c)
804a	h112	e112
805b	h212	e212
806c	h312	e312
807d	h412	e412
808select a1,max(c),min(c)      from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
809a1	max(c)	min(c)
810a	p122	e112
811b	p222	e212
812c	p322	e312
813d	p422	e412
814select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
815a1	max(c)	min(c)
816a	h112	a111
817b	h212	a211
818c	h312	a311
819d	h412	a411
820select a1,a2,b,       max(c) from t1 where (b = 'b') group by a1,a2;
821a1	a2	b	max(c)
822a	a	b	h112
823a	b	b	p122
824b	a	b	h212
825b	b	b	p222
826c	a	b	h312
827c	b	b	p322
828d	a	b	h412
829d	b	b	p422
830select a1,a2,b,       max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2;
831a1	a2	b	max(c)
832a	a	a	h112
833a	b	a	p122
834b	a	a	h212
835b	b	a	p222
836c	a	a	h312
837c	b	a	p322
838d	a	a	h412
839d	b	a	p422
840select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
841a1	a2	b	min(c)	max(c)
842a	a	b	e112	h112
843a	b	b	m122	p122
844b	a	b	e212	h212
845b	b	b	m222	p222
846c	a	b	e312	h312
847c	b	b	m322	p322
848d	a	b	e412	h412
849d	b	b	m422	p422
850select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2;
851a1	a2	b	min(c)	max(c)
852a	a	a	a111	h112
853a	b	a	i121	p122
854b	a	a	a211	h212
855b	b	a	i221	p222
856c	a	a	a311	h312
857c	b	a	i321	p322
858d	a	a	a411	h412
859d	b	a	i421	p422
860select a1,a2, max(c)         from t1 where (b = 'b') group by a1,a2;
861a1	a2	max(c)
862a	a	h112
863a	b	p122
864b	a	h212
865b	b	p222
866c	a	h312
867c	b	p322
868d	a	h412
869d	b	p422
870select a1,a2, max(c)         from t1 where (b = 'b' or b = 'a') group by a1,a2;
871a1	a2	max(c)
872a	a	h112
873a	b	p122
874b	a	h212
875b	b	p222
876c	a	h312
877c	b	p322
878d	a	h412
879d	b	p422
880select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
881a1	a2	b	max(c)	min(c)
882a	a	b	h112	e112
883b	a	b	h212	e212
884c	a	b	h312	e312
885d	a	b	h412	e412
886e	a	b	NULL	NULL
887select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
888a1	a2	b	max(c)	min(c)
889a	a	b	p122	e112
890b	a	b	p222	e212
891c	a	b	p322	e312
892d	a	b	p422	e412
893e	a	b	NULL	NULL
894select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
895a1	a2	b	max(c)	min(c)
896a	a	a	h112	a111
897b	a	a	h212	a211
898c	a	a	h312	a311
899d	a	a	h412	a411
900e	a	a	NULL	NULL
901select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b') group by a1;
902a1	max(c)	min(c)
903a	h112	e112
904b	h212	e212
905c	h312	e312
906d	h412	e412
907e	NULL	NULL
908select a1,max(c),min(c)      from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
909a1	max(c)	min(c)
910a	p122	e112
911b	p222	e212
912c	p322	e312
913d	p422	e412
914e	NULL	NULL
915select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
916a1	max(c)	min(c)
917a	h112	a111
918b	h212	a211
919c	h312	a311
920d	h412	a411
921e	NULL	NULL
922select a1,a2,b,       max(c) from t2 where (b = 'b') group by a1,a2;
923a1	a2	b	max(c)
924a	a	b	h112
925a	b	b	p122
926b	a	b	h212
927b	b	b	p222
928c	a	b	h312
929c	b	b	p322
930d	a	b	h412
931d	b	b	p422
932e	a	b	NULL
933select a1,a2,b,       max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2;
934a1	a2	b	max(c)
935a	a	a	h112
936a	b	a	p122
937b	a	a	h212
938b	b	a	p222
939c	a	a	h312
940c	b	a	p322
941d	a	a	h412
942d	b	a	p422
943e	a	a	NULL
944select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
945a1	a2	b	min(c)	max(c)
946a	a	b	e112	h112
947a	b	b	m122	p122
948b	a	b	e212	h212
949b	b	b	m222	p222
950c	a	b	e312	h312
951c	b	b	m322	p322
952d	a	b	e412	h412
953d	b	b	m422	p422
954e	a	b	NULL	NULL
955select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2;
956a1	a2	b	min(c)	max(c)
957a	a	a	a111	h112
958a	b	a	i121	p122
959b	a	a	a211	h212
960b	b	a	i221	p222
961c	a	a	a311	h312
962c	b	a	i321	p322
963d	a	a	a411	h412
964d	b	a	i421	p422
965e	a	a	NULL	NULL
966select a1,a2, max(c)         from t2 where (b = 'b') group by a1,a2;
967a1	a2	max(c)
968a	a	h112
969a	b	p122
970b	a	h212
971b	b	p222
972c	a	h312
973c	b	p322
974d	a	h412
975d	b	p422
976e	a	NULL
977select a1,a2, max(c)         from t2 where (b = 'b' or b = 'a') group by a1,a2;
978a1	a2	max(c)
979a	a	h112
980a	b	p122
981b	a	h212
982b	b	p222
983c	a	h312
984c	b	p322
985d	a	h412
986d	b	p422
987e	a	NULL
988select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
989a1	a2	b	max(c)	min(c)
990a	a	b	h112	e112
991b	a	b	h212	e212
992c	a	b	h312	e312
993select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
994a1	a2	b	max(c)	min(c)
995a	a	b	p122	e112
996b	a	b	p222	e212
997c	a	b	p322	e312
998select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
999a1	a2	b	max(c)	min(c)
1000a	a	a	h112	a111
1001b	a	a	h212	a211
1002c	a	a	h312	a311
1003select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b') group by a1;
1004a1	max(c)	min(c)
1005a	h112	e112
1006b	h212	e212
1007c	h312	e312
1008select a1,max(c),min(c)      from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
1009a1	max(c)	min(c)
1010a	p122	e112
1011b	p222	e212
1012c	p322	e312
1013select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1;
1014a1	max(c)	min(c)
1015a	h112	a111
1016b	h212	a211
1017c	h312	a311
1018explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
1019id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10201	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	6	Using where; Using index for group-by
1021explain select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1;
1022id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10231	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
1024explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
1025id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10261	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	6	Using where; Using index for group-by
1027explain select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1;
1028id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10291	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
1030explain select a1,a2,b,min(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	163	NULL	10	Using where; Using index for group-by
1033explain select a1,a2,b,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	146	NULL	10	Using where; Using index for group-by
1036explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
1037id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
10381	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	10	Using where; Using index for group-by
1039select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
1040a1	a2	b	min(c)
1041a	a	NULL	a777
1042c	a	NULL	c777
1043select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1;
1044a1	a2	b	min(c)
1045a	a	NULL	a777
1046c	a	NULL	c777
1047select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
1048a1	a2	b	max(c)
1049a	a	NULL	a999
1050c	a	NULL	c999
1051select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1;
1052a1	a2	b	max(c)
1053a	a	NULL	a999
1054c	a	NULL	c999
1055select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
1056a1	a2	b	min(c)
1057a	a	NULL	a777
1058c	a	NULL	c777
1059select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
1060a1	a2	b	max(c)
1061a	a	NULL	a999
1062c	a	NULL	c999
1063select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
1064a1	a2	b	min(c)	max(c)
1065a	a	NULL	a777	a999
1066c	a	NULL	c777	c999
1067select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
1068a1	a2	b	min(c)	max(c)
1069a	a	NULL	a777	a999
1070c	a	NULL	c777	c999
1071explain select a1,a2,b,       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	147	NULL	#	Using where; Using index for group-by
1074explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') 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	163	NULL	17	Using where; Using index for group-by
1077explain select a1,a2,b,       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	147	NULL	17	Using where; Using index for group-by
1080explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') 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	17	Using where; Using index for group-by
1083explain select a1,a2,b,       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	17	Using where; Using index for group-by
1086explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') 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	17	Using where; Using index for group-by
1089explain select a1,a2,b,       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	17	Using where; Using index for group-by
1092explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') 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	17	Using where; Using index for group-by
1095explain select a1,a2,b,       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	17	Using where; Using index for group-by
1098explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') 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	163	NULL	17	Using where; Using index for group-by
1101explain select a1,a2,b,       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	17	Using where; Using index for group-by
1104explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') 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	147	NULL	17	Using where; Using index for group-by
1107explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') 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	17	Using where; Using index for group-by
1110explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') 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	17	Using where; Using index for group-by
1113explain 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;
1114id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11151	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
1116explain 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;
1117id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11181	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
1119explain select a1,a2,b,       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	146	NULL	#	Using where; Using index for group-by
1122explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') 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	163	NULL	#	Using where; Using index for group-by
1125explain select a1,a2,b,       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	146	NULL	#	Using where; Using index for group-by
1128explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') 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,       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,min(c),max(c) from t2 where (c < 'a0') 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,       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,min(c),max(c) from t2 where (c < 'k321') 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,       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,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') 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	163	NULL	#	Using where; Using index for group-by
1149explain select a1,a2,b,       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	146	NULL	#	Using where; Using index for group-by
1152explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') 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 > 'b111') and (c <= 'g112') 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 < 'c5') or (c = 'g412') or (c = 'k421') 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
1161explain 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;
1162id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
11631	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1164select a1,a2,b,       max(c) from t1 where (c > 'b1') group by a1,a2,b;
1165a1	a2	b	max(c)
1166a	a	a	d111
1167a	a	b	h112
1168a	b	a	l121
1169a	b	b	p122
1170b	a	a	d211
1171b	a	b	h212
1172b	b	a	l221
1173b	b	b	p222
1174c	a	a	d311
1175c	a	b	h312
1176c	b	a	l321
1177c	b	b	p322
1178d	a	a	d411
1179d	a	b	h412
1180d	b	a	l421
1181d	b	b	p422
1182select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
1183a1	a2	b	min(c)	max(c)
1184a	a	a	b111	d111
1185a	a	b	e112	h112
1186a	b	a	i121	l121
1187a	b	b	m122	p122
1188b	a	a	b211	d211
1189b	a	b	e212	h212
1190b	b	a	i221	l221
1191b	b	b	m222	p222
1192c	a	a	b311	d311
1193c	a	b	e312	h312
1194c	b	a	i321	l321
1195c	b	b	m322	p322
1196d	a	a	b411	d411
1197d	a	b	e412	h412
1198d	b	a	i421	l421
1199d	b	b	m422	p422
1200select a1,a2,b,       max(c) from t1 where (c > 'f123') group by a1,a2,b;
1201a1	a2	b	max(c)
1202a	a	b	h112
1203a	b	a	l121
1204a	b	b	p122
1205b	a	b	h212
1206b	b	a	l221
1207b	b	b	p222
1208c	a	b	h312
1209c	b	a	l321
1210c	b	b	p322
1211d	a	b	h412
1212d	b	a	l421
1213d	b	b	p422
1214select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
1215a1	a2	b	min(c)	max(c)
1216a	a	b	g112	h112
1217a	b	a	i121	l121
1218a	b	b	m122	p122
1219b	a	b	f212	h212
1220b	b	a	i221	l221
1221b	b	b	m222	p222
1222c	a	b	f312	h312
1223c	b	a	i321	l321
1224c	b	b	m322	p322
1225d	a	b	f412	h412
1226d	b	a	i421	l421
1227d	b	b	m422	p422
1228select a1,a2,b,       max(c) from t1 where (c < 'a0') group by a1,a2,b;
1229a1	a2	b	max(c)
1230select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
1231a1	a2	b	min(c)	max(c)
1232select a1,a2,b,       max(c) from t1 where (c < 'k321') group by a1,a2,b;
1233a1	a2	b	max(c)
1234a	a	a	d111
1235a	a	b	h112
1236a	b	a	k121
1237b	a	a	d211
1238b	a	b	h212
1239b	b	a	k221
1240c	a	a	d311
1241c	a	b	h312
1242c	b	a	j321
1243d	a	a	d411
1244d	a	b	h412
1245d	b	a	j421
1246select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
1247a1	a2	b	min(c)	max(c)
1248a	a	a	a111	d111
1249a	a	b	e112	h112
1250a	b	a	i121	k121
1251b	a	a	a211	d211
1252b	a	b	e212	h212
1253b	b	a	i221	k221
1254c	a	a	a311	d311
1255c	a	b	e312	h312
1256c	b	a	i321	j321
1257d	a	a	a411	d411
1258d	a	b	e412	h412
1259d	b	a	i421	j421
1260select a1,a2,b,       max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1261a1	a2	b	max(c)
1262a	a	a	d111
1263a	a	b	h112
1264a	b	a	l121
1265a	b	b	p122
1266b	a	a	d211
1267b	a	b	h212
1268b	b	a	l221
1269b	b	b	p222
1270c	a	a	d311
1271c	a	b	h312
1272c	b	a	l321
1273c	b	b	p322
1274d	a	a	d411
1275d	a	b	h412
1276d	b	a	l421
1277d	b	b	p422
1278select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1279a1	a2	b	min(c)	max(c)
1280a	a	a	b111	d111
1281a	a	b	e112	h112
1282a	b	a	i121	l121
1283a	b	b	m122	p122
1284b	a	a	b211	d211
1285b	a	b	e212	h212
1286b	b	a	i221	l221
1287b	b	b	m222	p222
1288c	a	a	b311	d311
1289c	a	b	e312	h312
1290c	b	a	i321	l321
1291c	b	b	m322	p322
1292d	a	a	b411	d411
1293d	a	b	e412	h412
1294d	b	a	i421	l421
1295d	b	b	m422	p422
1296select a1,a2,b,       max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1297a1	a2	b	max(c)
1298a	a	a	d111
1299a	a	b	h112
1300a	b	a	l121
1301a	b	b	p122
1302b	a	a	d211
1303b	a	b	h212
1304b	b	a	l221
1305b	b	b	p222
1306c	a	a	d311
1307c	a	b	h312
1308c	b	a	l321
1309c	b	b	p322
1310d	a	a	d411
1311d	a	b	h412
1312d	b	a	l421
1313d	b	b	p422
1314select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1315a1	a2	b	min(c)	max(c)
1316a	a	a	a111	d111
1317a	a	b	e112	h112
1318a	b	a	i121	l121
1319a	b	b	m122	p122
1320b	a	a	a211	d211
1321b	a	b	e212	h212
1322b	b	a	i221	l221
1323b	b	b	m222	p222
1324c	a	a	a311	d311
1325c	a	b	e312	h312
1326c	b	a	i321	l321
1327c	b	b	m322	p322
1328d	a	a	a411	d411
1329d	a	b	e412	h412
1330d	b	a	i421	l421
1331d	b	b	m422	p422
1332select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
1333a1	a2	b	min(c)	max(c)
1334a	a	a	c111	d111
1335a	a	b	e112	g112
1336b	a	a	b211	d211
1337b	a	b	e212	f212
1338c	a	a	b311	d311
1339c	a	b	e312	f312
1340d	a	a	b411	d411
1341d	a	b	e412	f412
1342select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
1343a1	a2	b	min(c)	max(c)
1344a	a	a	a111	c111
1345b	a	a	a211	c211
1346c	a	a	a311	c311
1347d	a	a	a411	c411
1348d	a	b	g412	g412
1349d	b	a	k421	k421
1350select 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;
1351a1	a2	b	min(c)	max(c)
1352a	a	a	c111	d111
1353a	a	b	e112	h112
1354b	a	a	b211	d211
1355b	a	b	e212	h212
1356c	a	a	b311	d311
1357c	a	b	e312	h312
1358d	a	a	b411	d411
1359d	a	b	e412	h412
1360select 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;
1361a1	a2	b	min(c)	max(c)
1362a	a	a	b111	d111
1363a	a	b	e112	h112
1364b	a	a	b211	d211
1365b	a	b	e212	h212
1366c	a	a	b311	d311
1367c	a	b	e312	h312
1368d	a	a	b411	d411
1369d	a	b	e412	h412
1370select a1,a2,b,       max(c) from t2 where (c > 'b1') group by a1,a2,b;
1371a1	a2	b	max(c)
1372a	a	a	d111
1373a	a	b	h112
1374a	b	a	l121
1375a	b	b	p122
1376b	a	a	d211
1377b	a	b	h212
1378b	b	a	l221
1379b	b	b	p222
1380c	a	NULL	c999
1381c	a	a	d311
1382c	a	b	h312
1383c	b	a	l321
1384c	b	b	p322
1385d	a	a	d411
1386d	a	b	h412
1387d	b	a	l421
1388d	b	b	p422
1389select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
1390a1	a2	b	min(c)	max(c)
1391a	a	a	b111	d111
1392a	a	b	e112	h112
1393a	b	a	i121	l121
1394a	b	b	m122	p122
1395b	a	a	b211	d211
1396b	a	b	e212	h212
1397b	b	a	i221	l221
1398b	b	b	m222	p222
1399c	a	NULL	c777	c999
1400c	a	a	b311	d311
1401c	a	b	e312	h312
1402c	b	a	i321	l321
1403c	b	b	m322	p322
1404d	a	a	b411	d411
1405d	a	b	e412	h412
1406d	b	a	i421	l421
1407d	b	b	m422	p422
1408select a1,a2,b,       max(c) from t2 where (c > 'f123') group by a1,a2,b;
1409a1	a2	b	max(c)
1410a	a	b	h112
1411a	b	a	l121
1412a	b	b	p122
1413b	a	b	h212
1414b	b	a	l221
1415b	b	b	p222
1416c	a	b	h312
1417c	b	a	l321
1418c	b	b	p322
1419d	a	b	h412
1420d	b	a	l421
1421d	b	b	p422
1422select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
1423a1	a2	b	min(c)	max(c)
1424a	a	b	g112	h112
1425a	b	a	i121	l121
1426a	b	b	m122	p122
1427b	a	b	f212	h212
1428b	b	a	i221	l221
1429b	b	b	m222	p222
1430c	a	b	f312	h312
1431c	b	a	i321	l321
1432c	b	b	m322	p322
1433d	a	b	f412	h412
1434d	b	a	i421	l421
1435d	b	b	m422	p422
1436select a1,a2,b,       max(c) from t2 where (c < 'a0') group by a1,a2,b;
1437a1	a2	b	max(c)
1438select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
1439a1	a2	b	min(c)	max(c)
1440select a1,a2,b,       max(c) from t2 where (c < 'k321') group by a1,a2,b;
1441a1	a2	b	max(c)
1442a	a	NULL	a999
1443a	a	a	d111
1444a	a	b	h112
1445a	b	a	k121
1446b	a	a	d211
1447b	a	b	h212
1448b	b	a	k221
1449c	a	NULL	c999
1450c	a	a	d311
1451c	a	b	h312
1452c	b	a	j321
1453d	a	a	d411
1454d	a	b	h412
1455d	b	a	j421
1456select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
1457a1	a2	b	min(c)	max(c)
1458a	a	NULL	a777	a999
1459a	a	a	a111	d111
1460a	a	b	e112	h112
1461a	b	a	i121	k121
1462b	a	a	a211	d211
1463b	a	b	e212	h212
1464b	b	a	i221	k221
1465c	a	NULL	c777	c999
1466c	a	a	a311	d311
1467c	a	b	e312	h312
1468c	b	a	i321	j321
1469d	a	a	a411	d411
1470d	a	b	e412	h412
1471d	b	a	i421	j421
1472select a1,a2,b,       max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1473a1	a2	b	max(c)
1474a	a	a	d111
1475a	a	b	h112
1476a	b	a	l121
1477a	b	b	p122
1478b	a	a	d211
1479b	a	b	h212
1480b	b	a	l221
1481b	b	b	p222
1482c	a	NULL	c999
1483c	a	a	d311
1484c	a	b	h312
1485c	b	a	l321
1486c	b	b	p322
1487d	a	a	d411
1488d	a	b	h412
1489d	b	a	l421
1490d	b	b	p422
1491select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1492a1	a2	b	min(c)	max(c)
1493a	a	a	b111	d111
1494a	a	b	e112	h112
1495a	b	a	i121	l121
1496a	b	b	m122	p122
1497b	a	a	b211	d211
1498b	a	b	e212	h212
1499b	b	a	i221	l221
1500b	b	b	m222	p222
1501c	a	NULL	c777	c999
1502c	a	a	b311	d311
1503c	a	b	e312	h312
1504c	b	a	i321	l321
1505c	b	b	m322	p322
1506d	a	a	b411	d411
1507d	a	b	e412	h412
1508d	b	a	i421	l421
1509d	b	b	m422	p422
1510select a1,a2,b,       max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1511a1	a2	b	max(c)
1512a	a	NULL	a999
1513a	a	a	d111
1514a	a	b	h112
1515a	b	a	l121
1516a	b	b	p122
1517b	a	a	d211
1518b	a	b	h212
1519b	b	a	l221
1520b	b	b	p222
1521c	a	NULL	c999
1522c	a	a	d311
1523c	a	b	h312
1524c	b	a	l321
1525c	b	b	p322
1526d	a	a	d411
1527d	a	b	h412
1528d	b	a	l421
1529d	b	b	p422
1530select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1531a1	a2	b	min(c)	max(c)
1532a	a	NULL	a777	a999
1533a	a	a	a111	d111
1534a	a	b	e112	h112
1535a	b	a	i121	l121
1536a	b	b	m122	p122
1537b	a	a	a211	d211
1538b	a	b	e212	h212
1539b	b	a	i221	l221
1540b	b	b	m222	p222
1541c	a	NULL	c777	c999
1542c	a	a	a311	d311
1543c	a	b	e312	h312
1544c	b	a	i321	l321
1545c	b	b	m322	p322
1546d	a	a	a411	d411
1547d	a	b	e412	h412
1548d	b	a	i421	l421
1549d	b	b	m422	p422
1550select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
1551a1	a2	b	min(c)	max(c)
1552a	a	a	c111	d111
1553a	a	b	e112	g112
1554b	a	a	b211	d211
1555b	a	b	e212	f212
1556c	a	NULL	c777	c999
1557c	a	a	b311	d311
1558c	a	b	e312	f312
1559d	a	a	b411	d411
1560d	a	b	e412	f412
1561select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
1562a1	a2	b	min(c)	max(c)
1563a	a	NULL	a777	a999
1564a	a	a	a111	c111
1565b	a	a	a211	c211
1566c	a	a	a311	c311
1567d	a	a	a411	c411
1568d	a	b	g412	g412
1569d	b	a	k421	k421
1570select 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;
1571a1	a2	b	min(c)	max(c)
1572a	a	a	c111	d111
1573a	a	b	e112	h112
1574b	a	a	b211	d211
1575b	a	b	e212	h212
1576c	a	NULL	c777	c999
1577c	a	a	b311	d311
1578c	a	b	e312	h312
1579d	a	a	b411	d411
1580d	a	b	e412	h412
1581explain select a1,a2,b,min(c),max(c) from t1
1582where exists ( select * from t2 where t2.c = t1.c )
1583group by a1,a2,b;
1584id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
15851	PRIMARY	t1	index	NULL	idx_t1_1	163	NULL	128	Using index
15861	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	16	func	1
15872	MATERIALIZED	t2	index	NULL	idx_t2_1	163	NULL	164	Using index
1588explain select a1,a2,b,min(c),max(c) from t1
1589where exists ( select * from t2 where t2.c > 'b1' )
1590group by a1,a2,b;
1591id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
15921	PRIMARY	t1	index	NULL	idx_t1_1	163	NULL	128	Using index
15932	SUBQUERY	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
1594explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') 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	147	NULL	17	Using where; Using index for group-by
1597explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') 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	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	163	NULL	17	Using where; Using index for group-by
1600explain select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
1601id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
16021	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	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 < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) 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	17	Using where; Using index for group-by
1606explain 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;
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	163	NULL	17	Using where; Using index for group-by
1609explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') 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	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	17	Using where; Using index for group-by
1612explain 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;
1613id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
16141	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	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 (b > 'a') 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 (a1 >= 'c' or a2 < 'b') 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	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1621explain select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
1622id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
16231	SIMPLE	t2	range	NULL	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 < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) 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') 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;
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
1630explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1631id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
16321	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1633select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1634a1	a2	b	min(c)	max(c)
1635a	a	b	e112	h112
1636b	a	b	e212	h212
1637c	a	b	e312	h312
1638c	b	b	m322	p322
1639d	a	b	e412	h412
1640d	b	b	m422	p422
1641select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
1642a1	a2	b	min(c)	max(c)
1643a	a	a	c111	d111
1644a	a	b	e112	h112
1645b	a	a	b211	d211
1646b	a	b	e212	h212
1647c	a	a	b311	d311
1648c	a	b	e312	h312
1649c	b	a	i321	l321
1650c	b	b	m322	p322
1651d	a	a	b411	d411
1652d	a	b	e412	h412
1653d	b	a	i421	l421
1654d	b	b	m422	p422
1655select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
1656a1	a2	b	min(c)	max(c)
1657a	b	a	i121	l121
1658b	b	a	i221	l221
1659c	b	a	i321	l321
1660d	b	a	i421	l421
1661select 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;
1662a1	a2	b	min(c)
1663b	b	a	k221
1664c	b	a	k321
1665d	b	a	k421
1666select 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;
1667a1	a2	b	min(c)
1668b	b	a	k221
1669c	b	a	k321
1670d	b	a	k421
1671select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1672a1	a2	b	min(c)
1673select 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;
1674a1	a2	b	min(c)
1675select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1676a1	a2	b	min(c)	max(c)
1677a	a	b	e112	h112
1678b	a	b	e212	h212
1679c	a	b	e312	h312
1680c	b	b	m322	p322
1681d	a	b	e412	h412
1682d	b	b	m422	p422
1683e	a	b	NULL	NULL
1684select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
1685a1	a2	b	min(c)	max(c)
1686a	a	a	c111	d111
1687a	a	b	e112	h112
1688b	a	a	b211	d211
1689b	a	b	e212	h212
1690c	a	NULL	c777	c999
1691c	a	a	b311	d311
1692c	a	b	e312	h312
1693c	b	a	i321	l321
1694c	b	b	m322	p322
1695d	a	a	b411	d411
1696d	a	b	e412	h412
1697d	b	a	i421	l421
1698d	b	b	m422	p422
1699select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
1700a1	a2	b	min(c)	max(c)
1701a	b	a	i121	l121
1702b	b	a	i221	l221
1703c	b	a	i321	l321
1704d	b	a	i421	l421
1705select 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;
1706a1	a2	b	min(c)
1707b	b	a	k221
1708c	b	a	k321
1709d	b	a	k421
1710select 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;
1711a1	a2	b	min(c)
1712b	b	a	k221
1713c	b	a	k321
1714d	b	a	k421
1715select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1716a1	a2	b	min(c)
1717explain select a1,a2,b from t1 where (a1 >= 'c' or 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	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	147	NULL	17	Using where; Using index for group-by
1720explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') 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_2	147	NULL	17	Using where; Using index for group-by
1723explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (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	17	Using where; Using index for group-by
1726explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b;
1727id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17281	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
1729explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1730id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17311	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	147	NULL	17	Using where; Using index for group-by
1732explain select a1,a2,b from t2 where (a1 >= 'c' or 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	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_2	146	NULL	#	Using where; Using index for group-by
1735explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') 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_2	146	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') 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,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b;
1742id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17431	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1744explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1745id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17461	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_2	146	NULL	#	Using where; Using index for group-by
1747select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1748a1	a2	b
1749a	a	b
1750b	a	b
1751c	a	b
1752c	b	b
1753d	a	b
1754d	b	b
1755select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1756a1	a2	b
1757a	b	a
1758b	b	a
1759c	b	a
1760d	b	a
1761select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1762a1	a2	b	c
1763a	b	a	i121
1764select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b;
1765a1	a2	b	c
1766a	b	a	i121
1767select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1768a1	a2	b
1769select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1770a1	a2	b
1771a	a	b
1772b	a	b
1773c	a	b
1774c	b	b
1775d	a	b
1776d	b	b
1777e	a	b
1778select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1779a1	a2	b
1780a	b	a
1781b	b	a
1782c	b	a
1783d	b	a
1784select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1785a1	a2	b	c
1786a	b	a	i121
1787select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b;
1788a1	a2	b	c
1789a	b	a	i121
1790select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1791a1	a2	b
1792explain select distinct a1,a2,b from t1;
1793id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17941	SIMPLE	t1	range	NULL	idx_t1_2	147	NULL	17	Using index for group-by
1795explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
1796id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
17971	SIMPLE	t1	range	NULL	idx_t1_2	147	NULL	17	Using where; Using index for group-by
1798explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1799id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
18001	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	65	100.00	Using where; Using index for group-by
1801Warnings:
1802Note	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'
1803explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1804id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18051	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	147	NULL	17	Using where; Using index for group-by
1806explain select distinct b from t1 where (a2 >= 'b') and (b = 'a');
1807id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18081	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	128	Using where; Using index
1809explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b';
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_2	130	NULL	5	Using where; Using index for group-by
1812explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e';
1813id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18141	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	130	NULL	5	Using where; Using index for group-by
1815explain select distinct a1,a2,b from t2;
1816id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18171	SIMPLE	t2	range	NULL	idx_t2_2	146	NULL	#	Using index for group-by
1818explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
1819id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18201	SIMPLE	t2	range	NULL	idx_t2_2	146	NULL	#	Using where; Using index for group-by
1821explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1822id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
18231	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	83	100.00	Using where; Using index for group-by
1824Warnings:
1825Note	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'
1826explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1827id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18281	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_2	146	NULL	#	Using where; Using index for group-by
1829explain select distinct b from t2 where (a2 >= 'b') and (b = 'a');
1830id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18311	SIMPLE	t2	index	NULL	idx_t2_2	146	NULL	164	Using where; Using index
1832explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b';
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_2	129	NULL	6	Using where; Using index for group-by
1835explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e';
1836id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
18371	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_2	129	NULL	6	Using where; Using index for group-by
1838select distinct a1,a2,b from t1;
1839a1	a2	b
1840a	a	a
1841a	a	b
1842a	b	a
1843a	b	b
1844b	a	a
1845b	a	b
1846b	b	a
1847b	b	b
1848c	a	a
1849c	a	b
1850c	b	a
1851c	b	b
1852d	a	a
1853d	a	b
1854d	b	a
1855d	b	b
1856select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
1857a1	a2	b
1858a	b	a
1859b	b	a
1860c	b	a
1861d	b	a
1862select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1863a1	a2	b	c
1864a	b	a	i121
1865select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1866a1	a2	b
1867select distinct b from t1 where (a2 >= 'b') and (b = 'a');
1868b
1869a
1870select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b';
1871a1
1872a
1873d
1874select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e';
1875a1
1876select distinct a1,a2,b from t2;
1877a1	a2	b
1878a	a	NULL
1879a	a	a
1880a	a	b
1881a	b	a
1882a	b	b
1883b	a	a
1884b	a	b
1885b	b	a
1886b	b	b
1887c	a	NULL
1888c	a	a
1889c	a	b
1890c	b	a
1891c	b	b
1892d	a	a
1893d	a	b
1894d	b	a
1895d	b	b
1896e	a	a
1897e	a	b
1898select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
1899a1	a2	b
1900a	b	a
1901b	b	a
1902c	b	a
1903d	b	a
1904select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1905a1	a2	b	c
1906a	b	a	i121
1907select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1908a1	a2	b
1909select distinct b from t2 where (a2 >= 'b') and (b = 'a');
1910b
1911a
1912select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b';
1913a1
1914a
1915d
1916select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e';
1917a1
1918select distinct t_00.a1
1919from t1 t_00
1920where exists ( select * from t2 where a1 = t_00.a1 );
1921a1
1922a
1923b
1924c
1925d
1926select distinct a1,a1 from t1;
1927a1	a1
1928a	a
1929b	b
1930c	c
1931d	d
1932select distinct a2,a1,a2,a1 from t1;
1933a2	a1	a2	a1
1934a	a	a	a
1935b	a	b	a
1936a	b	a	b
1937b	b	b	b
1938a	c	a	c
1939b	c	b	c
1940a	d	a	d
1941b	d	b	d
1942select distinct t1.a1,t2.a1 from t1,t2;
1943a1	a1
1944a	a
1945b	a
1946c	a
1947d	a
1948a	b
1949b	b
1950c	b
1951d	b
1952a	c
1953b	c
1954c	c
1955d	c
1956a	d
1957b	d
1958c	d
1959d	d
1960a	e
1961b	e
1962c	e
1963d	e
1964explain select distinct a1,a2,b from t1;
1965id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19661	SIMPLE	t1	range	NULL	idx_t1_2	147	NULL	17	Using index for group-by
1967explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') 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_2	147	NULL	17	Using where; Using index for group-by
1970explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1971id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19721	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	17	Using where; Using index for group-by
1973explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1974id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19751	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	147	NULL	17	Using where; Using index for group-by
1976explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1977id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19781	SIMPLE	t1	range	NULL	idx_t1_2	147	NULL	17	Using where; Using index for group-by; Using temporary; Using filesort
1979explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b' 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_2	130	NULL	5	Using where; Using index for group-by
1982explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e' group by a1;
1983id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19841	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	130	NULL	5	Using where; Using index for group-by
1985explain select distinct a1,a2,b from t2;
1986id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19871	SIMPLE	t2	range	NULL	idx_t2_2	146	NULL	#	Using index for group-by
1988explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') 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_2	146	NULL	#	Using where; Using index for group-by
1991explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1992id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19931	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
1994explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1995id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19961	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_2	146	NULL	#	Using where; Using index for group-by
1997explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1998id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19991	SIMPLE	t2	range	NULL	idx_t2_2	146	NULL	#	Using where; Using index for group-by; Using temporary; Using filesort
2000explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b' 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_2	129	NULL	#	Using where; Using index for group-by
2003explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e' group by a1;
2004id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
20051	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_2	129	NULL	#	Using where; Using index for group-by
2006select distinct a1,a2,b from t1;
2007a1	a2	b
2008a	a	a
2009a	a	b
2010a	b	a
2011a	b	b
2012b	a	a
2013b	a	b
2014b	b	a
2015b	b	b
2016c	a	a
2017c	a	b
2018c	b	a
2019c	b	b
2020d	a	a
2021d	a	b
2022d	b	a
2023d	b	b
2024select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
2025a1	a2	b
2026a	b	a
2027b	b	a
2028c	b	a
2029d	b	a
2030select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
2031a1	a2	b	c
2032a	b	a	i121
2033select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
2034a1	a2	b
2035select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
2036b
2037a
2038select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b' group by a1;
2039a1
2040a
2041d
2042select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e' group by a1;
2043a1
2044select distinct a1,a2,b from t2;
2045a1	a2	b
2046a	a	NULL
2047a	a	a
2048a	a	b
2049a	b	a
2050a	b	b
2051b	a	a
2052b	a	b
2053b	b	a
2054b	b	b
2055c	a	NULL
2056c	a	a
2057c	a	b
2058c	b	a
2059c	b	b
2060d	a	a
2061d	a	b
2062d	b	a
2063d	b	b
2064e	a	a
2065e	a	b
2066select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
2067a1	a2	b
2068a	b	a
2069b	b	a
2070c	b	a
2071d	b	a
2072select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
2073a1	a2	b	c
2074a	b	a	i121
2075select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
2076a1	a2	b
2077select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
2078b
2079a
2080select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b' group by a1;
2081a1
2082a
2083d
2084select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e' group by a1;
2085a1
2086explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
2087id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
20881	SIMPLE	t1	range	NULL	idx_t1_2	147	NULL	17	Using where; Using index for group-by
2089explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
2090id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
20911	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	65	Using where; Using index for group-by
2092explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
2093id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
20941	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	147	NULL	17	100.00	Using where; Using index for group-by
2095Warnings:
2096Note	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'
2097explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
2098id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
20991	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	128	Using where; Using index
2100explain extended select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
2101id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
21021	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	147	NULL	17	100.00	Using where; Using index for group-by
2103Warnings:
2104Note	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'
2105select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
2106count(distinct a1,a2,b)
21074
2108select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
2109count(distinct a1,a2,b,c)
21101
2111select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
2112count(distinct a1,a2,b)
21130
2114select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
2115count(distinct b)
21161
2117select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
211898 + count(distinct a1,a2,b)
2119104
2120explain select a1,a2,b, concat(min(c), max(c)) 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	17	Using where; Using index for group-by
2123explain select concat(a1,min(c)),b 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	17	Using where; Using index for group-by
2126explain select concat(a1,min(c)),b,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	17	Using where; Using index for group-by
2129explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
2130id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
21311	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	17	Using where; Using index for group-by
2132explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
2133id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
21341	SIMPLE	t1	range	NULL	idx_t1_2	147	NULL	9	Using index for group-by
2135select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
2136a1	a2	b	concat(min(c), max(c))
2137a	a	a	a111d111
2138a	a	b	e112h112
2139a	b	a	i121l121
2140a	b	b	m122p122
2141b	a	a	a211d211
2142b	a	b	e212h212
2143b	b	a	i221l221
2144b	b	b	m222p222
2145c	a	a	a311d311
2146c	a	b	e312h312
2147c	b	a	i321l321
2148c	b	b	m322p322
2149select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
2150concat(a1,min(c))	b
2151aa111	a
2152ae112	b
2153ai121	a
2154am122	b
2155ba211	a
2156be212	b
2157bi221	a
2158bm222	b
2159ca311	a
2160ce312	b
2161ci321	a
2162cm322	b
2163select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
2164concat(a1,min(c))	b	max(c)
2165aa111	a	d111
2166ae112	b	h112
2167ai121	a	l121
2168am122	b	p122
2169ba211	a	d211
2170be212	b	h212
2171bi221	a	l221
2172bm222	b	p222
2173ca311	a	d311
2174ce312	b	h312
2175ci321	a	l321
2176cm322	b	p322
2177select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
2178concat(a1,a2)	b	min(c)	max(c)
2179aa	a	a111	d111
2180aa	b	e112	h112
2181ab	a	i121	l121
2182ab	b	m122	p122
2183ba	a	a211	d211
2184ba	b	e212	h212
2185bb	a	i221	l221
2186bb	b	m222	p222
2187ca	a	a311	d311
2188ca	b	e312	h312
2189cb	a	i321	l321
2190cb	b	m322	p322
2191select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
2192concat(ord(min(b)),ord(max(b)))	min(b)	max(b)
21939798	a	b
21949798	a	b
21959798	a	b
21969798	a	b
21979798	a	b
21989798	a	b
21999798	a	b
22009798	a	b
2201explain select a1,a2,b,d,min(c),max(c) 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	128
2204explain select a1,a2,b,d from t1 group by a1,a2,b;
2205id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22061	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	128
2207explain extended select a1,a2,min(b),max(b) from t1
2208where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;
2209id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
22101	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	130	NULL	128	99.22	Using where; Using index
2211Warnings:
2212Note	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`
2213explain extended select a1,a2,b,min(c),max(c) from t1
2214where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
2215id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
22161	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	130	NULL	128	75.00	Using where
2217Warnings:
2218Note	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`
2219explain extended select a1,a2,b,c from t1
2220where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
2221id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
22221	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	130	NULL	128	75.00	Using where
2223Warnings:
2224Note	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`
2225explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (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	164	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	164	Using where; Using index
2231explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b') 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	164	Using where; Using index
2234explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b' and b >= 'a') group by a1;
2235id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22361	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
2237explain 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;
2238id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
22391	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	130	NULL	128	99.22	Using where; Using index
2240Warnings:
2241Note	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`
2242explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
2243id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22441	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
2245select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
2246a1	a2	min(b)	c
2247a	a	a	a111
2248explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1;
2249id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22501	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
2251explain select a1,a2,b,min(c),max(c) from t2
2252where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b;
2253id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22541	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
2255explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c;
2256id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22571	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128
2258explain select a1,a2,count(a2) from t1 group by a1,a2,b;
2259id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22601	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	128	Using index
2261explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
2262id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
22631	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	65	NULL	128	100.00	Using where; Using index
2264Warnings:
2265Note	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`
2266explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
2267id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
22681	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	65	NULL	128	100.00	Using where; Using index
2269Warnings:
2270Note	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`
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	164	Using where; Using index
2274explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1;
2275id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22761	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
2277explain select distinct(a1) from t1 where ord(a2) = 98;
2278id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22791	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	128	Using where; Using index
2280select distinct(a1) from t1 where ord(a2) = 98;
2281a1
2282a
2283b
2284c
2285d
2286explain select a1 from t1 where a2 = 'b' group by a1;
2287id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22881	SIMPLE	t1	range	NULL	idx_t1_2	130	NULL	5	Using where; Using index for group-by
2289select a1 from t1 where a2 = 'b' group by a1;
2290a1
2291a
2292b
2293c
2294d
2295explain select distinct a1 from t1 where a2 = 'b';
2296id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
22971	SIMPLE	t1	range	NULL	idx_t1_2	130	NULL	5	Using where; Using index for group-by
2298select distinct a1 from t1 where a2 = 'b';
2299a1
2300a
2301b
2302c
2303d
2304drop table t1,t2,t3;
2305create table t1 (c1 int not null,c2 int not null, primary key(c1,c2)) engine=RocksDB;
2306insert into t1 (c1,c2) values
2307(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9);
2308select distinct c1, c2 from t1 order by c2;
2309c1	c2
231010	1
231110	2
231210	3
231320	4
231420	5
231520	6
231630	7
231730	8
231830	9
2319select c1,min(c2) as c2 from t1 group by c1 order by c2;
2320c1	c2
232110	1
232220	4
232330	7
2324select c1,c2 from t1 group by c1,c2 order by c2;
2325c1	c2
232610	1
232710	2
232810	3
232920	4
233020	5
233120	6
233230	7
233330	8
233430	9
2335drop table t1;
2336CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b)) engine=RocksDB;
2337INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4);
2338OPTIMIZE TABLE t1;
2339Table	Op	Msg_type	Msg_text
2340test.t1	optimize	status	OK
2341SELECT a FROM t1 WHERE a='AA' GROUP BY a;
2342a
2343AA
2344SELECT a FROM t1 WHERE a='BB' GROUP BY a;
2345a
2346BB
2347EXPLAIN SELECT a FROM t1 WHERE a='AA' 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
2350EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a;
2351id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
23521	SIMPLE	t1	ref	PRIMARY	PRIMARY	7	const	1000	Using where; Using index
2353SELECT DISTINCT a FROM t1 WHERE a='BB';
2354a
2355BB
2356SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%';
2357a
2358BB
2359SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a;
2360a
2361BB
2362DROP TABLE t1;
2363CREATE TABLE t1 (
2364a int(11) NOT NULL DEFAULT '0',
2365b varchar(16) COLLATE latin1_general_ci NOT NULL DEFAULT '',
2366PRIMARY KEY  (a,b)
2367) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
2368CREATE PROCEDURE a(x INT)
2369BEGIN
2370DECLARE rnd INT;
2371DECLARE cnt INT;
2372WHILE x > 0 DO
2373SET rnd= x % 100;
2374SET cnt = (SELECT COUNT(*) FROM t1 WHERE a = rnd);
2375INSERT INTO t1(a,b) VALUES (rnd, CAST(cnt AS CHAR));
2376SET x= x - 1;
2377END WHILE;
2378END|
2379CALL a(1000);
2380SELECT a FROM t1 WHERE a=0;
2381a
23820
23830
23840
23850
23860
23870
23880
23890
23900
23910
2392SELECT DISTINCT a FROM t1 WHERE a=0;
2393a
23940
2395SELECT COUNT(DISTINCT a) FROM t1 WHERE a=0;
2396COUNT(DISTINCT a)
23971
2398DROP TABLE t1;
2399DROP PROCEDURE a;
2400CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a)) engine=RocksDB;
2401INSERT INTO t1 (a) VALUES
2402(''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'),
2403('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'),
2404('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN');
2405EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a;
2406id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
24071	SIMPLE	t1	index	NULL	PRIMARY	66	NULL	1000	Using index
2408SELECT DISTINCT a,a FROM t1 ORDER BY a;
2409a	a
2410
2411CENTRAL	CENTRAL
2412EASTERN	EASTERN
2413GREATER LONDON	GREATER LONDON
2414NORTH CENTRAL	NORTH CENTRAL
2415NORTH EAST	NORTH EAST
2416NORTH WEST	NORTH WEST
2417SCOTLAND	SCOTLAND
2418SOUTH EAST	SOUTH EAST
2419SOUTH WEST	SOUTH WEST
2420WESTERN	WESTERN
2421DROP TABLE t1;
2422CREATE TABLE t1 (id1 INT, id2 INT) engine=RocksDB;
2423CREATE TABLE t2 (id2 INT, id3 INT, id5 INT) engine=RocksDB;
2424CREATE TABLE t3 (id3 INT, id4 INT) engine=RocksDB;
2425CREATE TABLE t4 (id4 INT) engine=RocksDB;
2426CREATE TABLE t5 (id5 INT, id6 INT) engine=RocksDB;
2427CREATE TABLE t6 (id6 INT) engine=RocksDB;
2428INSERT INTO t1 VALUES(1,1);
2429INSERT INTO t2 VALUES(1,1,1);
2430INSERT INTO t3 VALUES(1,1);
2431INSERT INTO t4 VALUES(1);
2432INSERT INTO t5 VALUES(1,1);
2433INSERT INTO t6 VALUES(1);
2434SELECT * FROM
2435t1
2436NATURAL JOIN
2437(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
2438ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
2439id2	id1	id3	id5	id4	id3	id6	id5
24401	1	1	1	1	1	1	1
2441SELECT * FROM
2442t1
2443NATURAL JOIN
2444(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2
2445ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
2446id2	id1	id4	id3	id6	id5	id3	id5
24471	1	1	1	1	1	1	1
2448SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2);
2449id2	id1	id3	id4	id6	id5	id3	id5
24501	1	1	1	1	1	1	1
2451SELECT * FROM
2452(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
2453ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5))
2454NATURAL JOIN
2455t1;
2456id2	id3	id5	id4	id3	id6	id5	id1
24571	1	1	1	1	1	1	1
2458SELECT * FROM
2459(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6)))
2460NATURAL JOIN
2461t1;
2462id2	id3	id5	id4	id3	id6	id5	id1
24631	1	1	1	1	1	1	1
2464DROP TABLE t1,t2,t3,t4,t5,t6;
2465CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)) engine=RocksDB;
2466INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
2467ANALYZE TABLE t1;
2468Table	Op	Msg_type	Msg_text
2469test.t1	analyze	status	Engine-independent statistics collected
2470test.t1	analyze	status	OK
2471explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
2472id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
24731	SIMPLE	t1	range	PRIMARY,b	PRIMARY	8	NULL	2	Using where; Using index for group-by
2474SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
2475MAX(b)	a
24761	1
2477SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
2478MIN(b)	a
24792	1
2480CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)) engine=RocksDB;
2481INSERT INTO t2 SELECT a,b,b FROM t1;
2482ANALYZE TABLE t2;
2483Table	Op	Msg_type	Msg_text
2484test.t2	analyze	status	Engine-independent statistics collected
2485test.t2	analyze	status	OK
2486explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
2487id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
24881	SIMPLE	t2	range	PRIMARY	PRIMARY	12	NULL	2	Using where; Using index for group-by
2489SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
2490MIN(c)
24912
2492DROP TABLE t1,t2;
2493CREATE TABLE t1 (a INT, b INT, INDEX (a,b)) engine=RocksDB;
2494INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
2495(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
2496ANALYZE TABLE t1;
2497Table	Op	Msg_type	Msg_text
2498test.t1	analyze	status	Engine-independent statistics collected
2499test.t1	analyze	status	OK
2500EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
2501id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25021	SIMPLE	t1	range	NULL	a	5	NULL	6	Using index for group-by
2503FLUSH STATUS;
2504SELECT max(b), a FROM t1 GROUP BY a;
2505max(b)	a
25065	1
25073	2
25081	3
25096	4
2510SHOW STATUS LIKE 'handler_read__e%';
2511Variable_name	Value
2512Handler_read_key	8
2513Handler_read_next	0
2514Handler_read_retry	0
2515EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
2516id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25171	SIMPLE	t1	range	NULL	a	5	NULL	6	Using index for group-by
2518FLUSH STATUS;
2519CREATE TABLE t2 engine=RocksDB SELECT max(b), a FROM t1 GROUP BY a;
2520SHOW STATUS LIKE 'handler_read__e%';
2521Variable_name	Value
2522Handler_read_key	8
2523Handler_read_next	0
2524Handler_read_retry	0
2525FLUSH STATUS;
2526SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
2527max(b)	a
25285	1
25293	2
25301	3
25316	4
2532SHOW STATUS LIKE 'handler_read__e%';
2533Variable_name	Value
2534Handler_read_key	8
2535Handler_read_next	0
2536Handler_read_retry	0
2537FLUSH STATUS;
2538(SELECT max(b), a FROM t1 GROUP BY a) UNION
2539(SELECT max(b), a FROM t1 GROUP BY a);
2540max(b)	a
25415	1
25423	2
25431	3
25446	4
2545SHOW STATUS LIKE 'handler_read__e%';
2546Variable_name	Value
2547Handler_read_key	16
2548Handler_read_next	0
2549Handler_read_retry	0
2550EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION
2551(SELECT max(b), a FROM t1 GROUP BY a);
2552id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25531	PRIMARY	t1	range	NULL	a	5	NULL	6	Using index for group-by
25542	UNION	t1	range	NULL	a	5	NULL	6	Using index for group-by
2555NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL
2556EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
2557FROM t1 AS t1_outer;
2558id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25591	PRIMARY	t1_outer	index	NULL	a	10	NULL	15	Using index
25602	SUBQUERY	t1	range	a	a	5	NULL	6	Using where; Using index for group-by
2561EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS
2562(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
2563id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25641	PRIMARY	t1_outer	index	NULL	a	10	NULL	15	Using index
25652	SUBQUERY	t1	index	NULL	a	10	NULL	15	Using index
2566EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
2567(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
2568id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25691	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
25702	SUBQUERY	t1	range	a	a	5	NULL	6	Using where; Using index for group-by
2571EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
2572a IN (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	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	6
25751	PRIMARY	t1_outer	ref	a	a	5	<subquery2>.max(b)	3	Using index
25762	MATERIALIZED	t1	range	a	a	5	NULL	6	Using where; Using index for group-by
2577EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
2578a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
2579id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25801	PRIMARY	t1_outer	range	NULL	a	5	NULL	6	Using index for group-by
25812	SUBQUERY	t1	range	a	a	5	NULL	6	Using where; Using index for group-by
2582EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2
2583ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2)
2584AND t1_outer1.b = t1_outer2.b;
2585id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25861	PRIMARY	t1_outer2	index	NULL	a	10	NULL	15	Using where; Using index
25871	PRIMARY	t1_outer1	ref	a	a	10	const,test.t1_outer2.b	1	Using where; Using index
25882	SUBQUERY	t1	range	a	a	5	NULL	6	Using where; Using index for group-by
2589EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
2590FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
2591id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25921	PRIMARY	t1_outer2	index	NULL	a	10	NULL	15	Using index
25932	SUBQUERY	t1_outer	index	NULL	a	10	NULL	15	Using index
25943	SUBQUERY	t1	range	a	a	5	NULL	6	Using where; Using index for group-by
2595CREATE TABLE t3 LIKE t1;
2596FLUSH STATUS;
2597INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;
2598SHOW STATUS LIKE 'handler_read__e%';
2599Variable_name	Value
2600Handler_read_key	13
2601Handler_read_next	0
2602Handler_read_retry	0
2603DELETE FROM t3;
2604FLUSH STATUS;
2605INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2)
2606FROM t1 LIMIT 1;
2607SHOW STATUS LIKE 'handler_read__e%';
2608Variable_name	Value
2609Handler_read_key	8
2610Handler_read_next	0
2611Handler_read_retry	0
2612FLUSH STATUS;
2613DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
2614SHOW STATUS LIKE 'handler_read__e%';
2615Variable_name	Value
2616Handler_read_key	8
2617Handler_read_next	0
2618Handler_read_retry	0
2619FLUSH STATUS;
2620DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x
2621FROM t1) > 10000;
2622ERROR 21000: Subquery returns more than 1 row
2623SHOW STATUS LIKE 'handler_read__e%';
2624Variable_name	Value
2625Handler_read_key	8
2626Handler_read_next	1
2627Handler_read_retry	0
2628DROP TABLE t1,t2,t3;
2629CREATE TABLE t1 (a int, INDEX idx(a)) engine=RocksDB;
2630INSERT INTO t1 VALUES
2631(4), (2), (1), (2), (4), (2), (1), (4),
2632(4), (2), (1), (2), (2), (4), (1), (4);
2633ANALYZE TABLE t1;
2634Table	Op	Msg_type	Msg_text
2635test.t1	analyze	status	Engine-independent statistics collected
2636test.t1	analyze	status	OK
2637EXPLAIN SELECT DISTINCT(a) FROM t1;
2638id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
26391	SIMPLE	t1	range	NULL	idx	5	NULL	4	Using index for group-by
2640SELECT DISTINCT(a) FROM t1;
2641a
26421
26432
26444
2645EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
2646id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
26471	SIMPLE	t1	range	NULL	idx	5	NULL	4	Using index for group-by
2648SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
2649a
26501
26512
26524
2653DROP TABLE t1;
2654CREATE TABLE t1 (a INT, b INT) engine=RocksDB;
2655INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3);
2656INSERT INTO t1 SELECT a + 1, b FROM t1;
2657INSERT INTO t1 SELECT a + 2, b FROM t1;
2658ANALYZE TABLE t1;
2659Table	Op	Msg_type	Msg_text
2660test.t1	analyze	status	Engine-independent statistics collected
2661test.t1	analyze	status	OK
2662EXPLAIN
2663SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2664id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
26651	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	12	Using temporary; Using filesort
2666SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2667a	MIN(b)	MAX(b)
26684	1	3
26693	1	3
26702	1	3
26711	1	3
2672CREATE INDEX break_it ON t1 (a, b);
2673EXPLAIN
2674SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
2675id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
26761	SIMPLE	t1	range	NULL	break_it	10	NULL	4	Using index for group-by
2677SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
2678a	MIN(b)	MAX(b)
26791	1	3
26802	1	3
26813	1	3
26824	1	3
2683EXPLAIN
2684SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2685id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
26861	SIMPLE	t1	range	NULL	break_it	10	NULL	4	Using index for group-by; Using temporary; Using filesort
2687SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2688a	MIN(b)	MAX(b)
26894	1	3
26903	1	3
26912	1	3
26921	1	3
2693EXPLAIN
2694SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
2695id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
26961	SIMPLE	t1	index	NULL	break_it	10	NULL	12	Using index
2697SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
2698a	MIN(b)	MAX(b)	AVG(b)
26994	1	3	2.0000
27003	1	3	2.0000
27012	1	3	2.0000
27021	1	3	2.0000
2703DROP TABLE t1;
2704create table t1 (a int, b int, primary key (a,b), key `index` (a,b)) engine=MyISAM;
2705insert into  t1 (a,b) values
2706(0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6),
2707(0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13),
2708(1,0),(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),
2709(1,7),(1,8),(1,9),(1,10),(1,11),(1,12),(1,13),
2710(2,0),(2,1),(2,2),(2,3),(2,4),(2,5),(2,6),
2711(2,7),(2,8),(2,9),(2,10),(2,11),(2,12),(2,13),
2712(3,0),(3,1),(3,2),(3,3),(3,4),(3,5),(3,6),
2713(3,7),(3,8),(3,9),(3,10),(3,11),(3,12),(3,13);
2714insert into t1 (a,b) select a, max(b)+1 from t1 where a = 0 group by a;
2715ANALYZE TABLE t1;
2716Table	Op	Msg_type	Msg_text
2717test.t1	analyze	status	Engine-independent statistics collected
2718test.t1	analyze	status	OK
2719select * from t1;
2720a	b
27210	0
27220	1
27230	2
27240	3
27250	4
27260	5
27270	6
27280	7
27290	8
27300	9
27310	10
27320	11
27330	12
27340	13
27350	14
27361	0
27371	1
27381	2
27391	3
27401	4
27411	5
27421	6
27431	7
27441	8
27451	9
27461	10
27471	11
27481	12
27491	13
27502	0
27512	1
27522	2
27532	3
27542	4
27552	5
27562	6
27572	7
27582	8
27592	9
27602	10
27612	11
27622	12
27632	13
27643	0
27653	1
27663	2
27673	3
27683	4
27693	5
27703	6
27713	7
27723	8
27733	9
27743	10
27753	11
27763	12
27773	13
2778explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a;
2779id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
27801	SIMPLE	t1	ref	PRIMARY,index	PRIMARY	4	const	15	6.67	Using index; Using temporary
2781Warnings:
2782Note	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`
2783drop table t1;
2784CREATE TABLE t1 (a int, b int, c int, d int,
2785KEY foo (c,d,a,b), KEY bar (c,a,b,d)) engine=RocksDB;
2786INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4);
2787INSERT INTO t1 SELECT * FROM t1;
2788INSERT INTO t1 SELECT * FROM t1;
2789INSERT INTO t1 SELECT a,b,c+1,d FROM t1;
2790ANALYZE TABLE t1;
2791Table	Op	Msg_type	Msg_text
2792test.t1	analyze	status	Engine-independent statistics collected
2793test.t1	analyze	status	OK
2794EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4;
2795id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
27961	SIMPLE	t1	range	NULL	foo	10	NULL	3	Using where; Using index for group-by
2797SELECT DISTINCT c FROM t1 WHERE d=4;
2798c
27991
28002
2801DROP TABLE t1;
2802#
2803# Bug #45386: Wrong query result with MIN function in field list,
2804#  WHERE and GROUP BY clause
2805#
2806CREATE TABLE t (a INT, b INT, INDEX (a,b)) engine=RocksDB;
2807INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
2808INSERT INTO t SELECT * FROM t;
2809INSERT INTO t SELECT * FROM t;
2810ANALYZE TABLE t;
2811Table	Op	Msg_type	Msg_text
2812test.t	analyze	status	Engine-independent statistics collected
2813test.t	analyze	status	OK
2814# test MIN
2815#should use range with index for group by
2816EXPLAIN
2817SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
2818id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
28191	SIMPLE	t	range	NULL	a	10	NULL	2	Using where; Using index for group-by
2820#should return 1 row
2821SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
2822a	MIN(b)
28232	1
2824# test MAX
2825#should use range with index for group by
2826EXPLAIN
2827SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
2828id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
28291	SIMPLE	t	range	NULL	a	10	NULL	2	Using where; Using index for group-by
2830#should return 1 row
2831SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
2832a	MAX(b)
28332	0
2834# test 3 ranges and use the middle one
2835INSERT INTO t SELECT a, 2 FROM t;
2836#should use range with index for group by
2837EXPLAIN
2838SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
2839id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
28401	SIMPLE	t	range	NULL	a	10	NULL	2	Using where; Using index for group-by
2841#should return 1 row
2842SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
2843a	MAX(b)
28442	1
2845DROP TABLE t;
2846#
2847# Bug #48472: Loose index scan inappropriately chosen for some WHERE
2848#             conditions
2849#
2850CREATE TABLE t (a INT, b INT, INDEX (a,b)) engine=RocksDB;
2851INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
2852INSERT INTO t SELECT * FROM t;
2853ANALYZE TABLE t;
2854Table	Op	Msg_type	Msg_text
2855test.t	analyze	status	Engine-independent statistics collected
2856test.t	analyze	status	OK
2857SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a;
2858a	MAX(b)
28592	0
2860DROP TABLE t;
2861End of 5.0 tests
2862#
2863# Bug #46607: Assertion failed: (cond_type == Item::FUNC_ITEM) results in
2864#              server crash
2865#
2866CREATE TABLE t (a INT, b INT, INDEX (a,b)) engine=RocksDB;
2867INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
2868INSERT INTO t SELECT * FROM t;
2869SELECT a, MAX(b) FROM t WHERE b GROUP BY a;
2870a	MAX(b)
28712	1
2872DROP TABLE t;
2873CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, KEY (b)) engine=RocksDB;
2874INSERT INTO t1 VALUES(1,1),(2,1);
2875ANALYZE TABLE t1;
2876Table	Op	Msg_type	Msg_text
2877test.t1	analyze	status	Engine-independent statistics collected
2878test.t1	analyze	status	OK
2879SELECT 1 AS c, b FROM t1 WHERE b IN (1,2) GROUP BY c, b;
2880c	b
28811	1
2882SELECT a FROM t1 WHERE b=1;
2883a
28841
28852
2886DROP TABLE t1;
2887#
2888# Bug#47762: Incorrect result from MIN() when WHERE tests NOT NULL column
2889#            for NULL
2890#
2891## Test for NULLs allowed
2892CREATE TABLE t1 ( a INT, KEY (a) ) engine=RocksDB;
2893INSERT INTO t1 VALUES (1), (2), (3);
2894ANALYZE TABLE t1;
2895Table	Op	Msg_type	Msg_text
2896test.t1	analyze	status	Engine-independent statistics collected
2897test.t1	analyze	status	OK
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	Impossible WHERE noticed after reading const tables
2916SELECT MIN( a ) FROM t1 WHERE a > NULL;
2917MIN( a )
2918NULL
2919EXPLAIN
2920SELECT MIN( a ) FROM t1 WHERE a < NULL;
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 < NULL;
2924MIN( a )
2925NULL
2926EXPLAIN
2927SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
2928id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2929x	x	x	x	x	x	x	x	x	No matching min/max row
2930SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
2931MIN( a )
2932NULL
2933EXPLAIN
2934SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
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 NULL AND 10;
2938MIN( a )
2939NULL
2940EXPLAIN
2941SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
2942id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2943x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2944SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
2945MIN( a )
2946NULL
2947EXPLAIN
2948SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
2949id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2950x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2951SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
2952MIN( a )
2953NULL
2954EXPLAIN
2955SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
2956id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2957x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2958x	x	x	x	x	x	x	x	x	Using where; Using index
2959SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
2960MIN( a )
2961NULL
2962EXPLAIN
2963SELECT MIN( a ) FROM t1 WHERE a IS NULL;
2964id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2965x	x	x	x	x	x	x	x	x	No matching min/max row
2966SELECT MIN( a ) FROM t1 WHERE a IS NULL;
2967MIN( a )
2968NULL
2969INSERT INTO t1 VALUES (NULL), (NULL);
2970ANALYZE TABLE t1;
2971Table	Op	Msg_type	Msg_text
2972test.t1	analyze	status	Engine-independent statistics collected
2973test.t1	analyze	status	OK
2974EXPLAIN
2975SELECT MIN( a ) FROM t1 WHERE a = NULL;
2976id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2977x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2978SELECT MIN( a ) FROM t1 WHERE a = NULL;
2979MIN( a )
2980NULL
2981EXPLAIN
2982SELECT MIN( a ) FROM t1 WHERE a <> NULL;
2983id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2984x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2985SELECT MIN( a ) FROM t1 WHERE a <> NULL;
2986MIN( a )
2987NULL
2988EXPLAIN
2989SELECT MIN( a ) FROM t1 WHERE a > NULL;
2990id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2991x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2992SELECT MIN( a ) FROM t1 WHERE a > NULL;
2993MIN( a )
2994NULL
2995EXPLAIN
2996SELECT MIN( a ) FROM t1 WHERE a < NULL;
2997id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2998x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
2999SELECT MIN( a ) FROM t1 WHERE a < NULL;
3000MIN( a )
3001NULL
3002EXPLAIN
3003SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
3004id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3005x	x	x	x	x	x	x	x	x	Select tables optimized away
3006SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
3007MIN( a )
3008NULL
3009EXPLAIN
3010SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
3011id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3012x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3013SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
3014MIN( a )
3015NULL
3016EXPLAIN
3017SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
3018id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3019x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3020SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
3021MIN( a )
3022NULL
3023EXPLAIN
3024SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
3025id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3026x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3027SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
3028MIN( a )
3029NULL
3030EXPLAIN
3031SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
3032id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3033x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3034x	x	x	x	x	x	x	x	x	Using where; Using index
3035SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
3036MIN( a )
3037NULL
3038EXPLAIN
3039SELECT MIN( a ) FROM t1 WHERE a IS NULL;
3040id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3041x	x	x	x	x	x	x	x	x	Select tables optimized away
3042SELECT MIN( a ) FROM t1 WHERE a IS NULL;
3043MIN( a )
3044NULL
3045DROP TABLE t1;
3046## Test for NOT NULLs
3047CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY) engine=RocksDB;
3048INSERT INTO t1 VALUES (1), (2), (3);
3049ANALYZE TABLE t1;
3050Table	Op	Msg_type	Msg_text
3051test.t1	analyze	status	Engine-independent statistics collected
3052test.t1	analyze	status	OK
3053#
3054# NULL-safe operator test disabled for non-NULL indexed columns.
3055#
3056# See bugs
3057#
3058# - Bug#52173: Reading NULL value from non-NULL index gives
3059#   wrong result in embedded server
3060#
3061# - Bug#52174: Sometimes wrong plan when reading a MAX value from
3062#   non-NULL index
3063#
3064EXPLAIN
3065SELECT MIN( a ) FROM t1 WHERE a = NULL;
3066id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3067x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3068SELECT MIN( a ) FROM t1 WHERE a = NULL;
3069MIN( a )
3070NULL
3071EXPLAIN
3072SELECT MIN( a ) FROM t1 WHERE a <> NULL;
3073id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3074x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3075SELECT MIN( a ) FROM t1 WHERE a <> NULL;
3076MIN( a )
3077NULL
3078EXPLAIN
3079SELECT MIN( a ) FROM t1 WHERE a > NULL;
3080id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3081x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3082SELECT MIN( a ) FROM t1 WHERE a > NULL;
3083MIN( a )
3084NULL
3085EXPLAIN
3086SELECT MIN( a ) FROM t1 WHERE a < NULL;
3087id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3088x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3089SELECT MIN( a ) FROM t1 WHERE a < NULL;
3090MIN( a )
3091NULL
3092EXPLAIN
3093SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
3094id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3095x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3096SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
3097MIN( a )
3098NULL
3099EXPLAIN
3100SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
3101id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3102x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3103SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
3104MIN( a )
3105NULL
3106EXPLAIN
3107SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
3108id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3109x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3110SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
3111MIN( a )
3112NULL
3113EXPLAIN
3114SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
3115id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3116x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
3117x	x	x	x	x	x	x	x	x	Using where; Using index
3118SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
3119MIN( a )
3120NULL
3121EXPLAIN
3122SELECT MIN( a ) FROM t1 WHERE a IS NULL;
3123id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3124x	x	x	x	x	x	x	x	x	Impossible WHERE
3125SELECT MIN( a ) FROM t1 WHERE a IS NULL;
3126MIN( a )
3127NULL
3128DROP TABLE t1;
3129#
3130# Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&, Item*) at
3131# opt_sum.cc:305
3132#
3133CREATE TABLE t1 ( a INT, KEY (a) ) engine=RocksDB;
3134INSERT INTO t1 VALUES (1), (2), (3);
3135SELECT MIN( a ) AS min_a
3136FROM t1
3137WHERE a > 1 AND a IS NULL
3138ORDER BY min_a;
3139min_a
3140NULL
3141DROP TABLE t1;
3142End of 5.1 tests
3143#
3144# WL#3220 (Loose index scan for COUNT DISTINCT)
3145#
3146CREATE TABLE t1 (a INT, b INT, c INT, KEY (a,b)) engine=RocksDB;
3147INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1), (1,4,1);
3148INSERT INTO t1 SELECT a, b + 4, 1 FROM t1;
3149INSERT INTO t1 SELECT a + 1, b, 1 FROM t1;
3150ANALYZE TABLE t1;
3151Table	Op	Msg_type	Msg_text
3152test.t1	analyze	status	Engine-independent statistics collected
3153test.t1	analyze	status	OK
3154CREATE TABLE t2 (a INT, b INT, c INT, d INT, e INT, f INT, KEY (a,b,c)) engine=RocksDB;
3155INSERT INTO t2 VALUES (1,1,1,1,1,1), (1,2,1,1,1,1), (1,3,1,1,1,1),
3156(1,4,1,1,1,1);
3157INSERT INTO t2 SELECT a, b + 4, c,d,e,f FROM t2;
3158INSERT INTO t2 SELECT a + 1, b, c,d,e,f FROM t2;
3159ANALYZE TABLE t2;
3160Table	Op	Msg_type	Msg_text
3161test.t2	analyze	status	Engine-independent statistics collected
3162test.t2	analyze	status	OK
3163EXPLAIN SELECT COUNT(DISTINCT a) FROM t1;
3164id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
31651	SIMPLE	t1	range	NULL	a	5	NULL	3	Using index for group-by
3166SELECT COUNT(DISTINCT a) FROM t1;
3167COUNT(DISTINCT a)
31682
3169EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1;
3170id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
31711	SIMPLE	t1	range	NULL	a	10	NULL	17	Using index for group-by
3172SELECT COUNT(DISTINCT a,b) FROM t1;
3173COUNT(DISTINCT a,b)
317416
3175EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1;
3176id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
31771	SIMPLE	t1	range	NULL	a	10	NULL	17	Using index for group-by
3178SELECT COUNT(DISTINCT b,a) FROM t1;
3179COUNT(DISTINCT b,a)
318016
3181EXPLAIN SELECT COUNT(DISTINCT b) FROM t1;
3182id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
31831	SIMPLE	t1	index	NULL	a	10	NULL	16	Using index
3184SELECT COUNT(DISTINCT b) FROM t1;
3185COUNT(DISTINCT b)
31868
3187EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a;
3188id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
31891	SIMPLE	t1	range	NULL	a	5	NULL	3	Using index for group-by
3190SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a;
3191COUNT(DISTINCT a)
31921
31931
3194EXPLAIN SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
3195id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
31961	SIMPLE	t1	range	NULL	a	10	NULL	17	Using index for group-by
3197SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
3198COUNT(DISTINCT b)
31998
32008
3201EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;
3202id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32031	SIMPLE	t1	index	NULL	a	10	NULL	16	Using index; Using filesort
3204SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;
3205COUNT(DISTINCT a)
32062
32072
32082
32092
32102
32112
32122
32132
3214EXPLAIN SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
3215id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32161	SIMPLE	t1	index	NULL	a	10	NULL	16	Using index
3217SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
3218COUNT(DISTINCT a)
32192
3220EXPLAIN SELECT COUNT(DISTINCT a, b + 0) FROM t1;
3221id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32221	SIMPLE	t1	index	NULL	a	10	NULL	16	Using index
3223SELECT COUNT(DISTINCT a, b + 0) FROM t1;
3224COUNT(DISTINCT a, b + 0)
322516
3226EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10;
3227id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32281	SIMPLE	t1	index	NULL	a	10	NULL	16	Using index
3229SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10;
3230COUNT(DISTINCT a)
32312
3232EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10;
3233id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32341	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	16
3235SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10;
3236COUNT(DISTINCT a)
32372
3238EXPLAIN SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10;
3239id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32401	SIMPLE	t1	range	NULL	a	5	NULL	3	Using index for group-by
3241SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10;
32421
32431
3244EXPLAIN SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1;
3245id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32461	SIMPLE	t1	range	NULL	a	10	NULL	17	Using index for group-by
3247SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1;
32481
32491
32501
3251EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
3252id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32531	SIMPLE	t1_1	index	NULL	a	10	NULL	16	Using index; Using temporary; Using filesort
32541	SIMPLE	t1_2	index	NULL	a	10	NULL	16	Using index; Using join buffer (flat, BNL join)
3255SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
3256COUNT(DISTINCT t1_1.a)
32571
32581
3259EXPLAIN SELECT COUNT(DISTINCT a), 12 FROM t1;
3260id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32611	SIMPLE	t1	range	NULL	a	5	NULL	3	Using index for group-by
3262SELECT COUNT(DISTINCT a), 12 FROM t1;
3263COUNT(DISTINCT a)	12
32642	12
3265EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2;
3266id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32671	SIMPLE	t2	range	NULL	a	15	NULL	17	Using index for group-by
3268SELECT COUNT(DISTINCT a, b, c) FROM t2;
3269COUNT(DISTINCT a, b, c)
327016
3271EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2;
3272id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32731	SIMPLE	t2	range	NULL	a	5	NULL	3	Using index for group-by
3274SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2;
3275COUNT(DISTINCT a)	SUM(DISTINCT a)	AVG(DISTINCT a)
32762	3	1.5000
3277EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2;
3278id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32791	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16
3280SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2;
3281COUNT(DISTINCT a)	SUM(DISTINCT a)	AVG(DISTINCT f)
32822	3	1.0000
3283EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;
3284id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32851	SIMPLE	t2	range	NULL	a	10	NULL	17	Using index for group-by
3286SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;
3287COUNT(DISTINCT a, b)	COUNT(DISTINCT b, a)
328816	16
3289EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2;
3290id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32911	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16
3292SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2;
3293COUNT(DISTINCT a, b)	COUNT(DISTINCT b, f)
329416	8
3295EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2;
3296id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
32971	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16
3298SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2;
3299COUNT(DISTINCT a, b)	COUNT(DISTINCT b, d)
330016	8
3301EXPLAIN SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c;
3302id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33031	SIMPLE	t2	range	NULL	a	15	NULL	17	Using index for group-by
3304SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c;
3305a	c	COUNT(DISTINCT c, a, b)
33061	1	1
33071	1	1
33081	1	1
33091	1	1
33101	1	1
33111	1	1
33121	1	1
33131	1	1
33142	1	1
33152	1	1
33162	1	1
33172	1	1
33182	1	1
33192	1	1
33202	1	1
33212	1	1
3322EXPLAIN SELECT COUNT(DISTINCT c, a, b) FROM t2
3323WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;
3324id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33251	SIMPLE	t2	range	a	a	15	NULL	17	Using where; Using index for group-by
3326SELECT COUNT(DISTINCT c, a, b) FROM t2
3327WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;
3328COUNT(DISTINCT c, a, b)
3329EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5
3330GROUP BY b;
3331id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33321	SIMPLE	t2	ref	a	a	5	const	16	Using where; Using index
3333SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5
3334GROUP BY b;
3335COUNT(DISTINCT b)	SUM(DISTINCT b)
3336EXPLAIN SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
3337id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33381	SIMPLE	t2	range	NULL	a	10	NULL	17	Using index for group-by
3339SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
3340a	COUNT(DISTINCT b)	SUM(DISTINCT b)
33411	8	36
33422	8	36
3343EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
3344id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33451	SIMPLE	t2	range	NULL	a	10	NULL	17	Using index for group-by
3346SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
3347COUNT(DISTINCT b)	SUM(DISTINCT b)
33488	36
33498	36
3350EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42;
3351id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33521	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16	Using where
3353SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42;
3354COUNT(DISTINCT a, b)
33550
3356EXPLAIN SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2
3357WHERE b = 13 AND c = 42 GROUP BY a;
3358id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33591	SIMPLE	t2	range	NULL	a	15	NULL	3	Using where; Using index for group-by
3360SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2
3361WHERE b = 13 AND c = 42 GROUP BY a;
3362a	COUNT(DISTINCT a)	SUM(DISTINCT a)
3363# This query could have been resolved using loose index scan since
3364# the second part of count(..) is defined by a constant predicate
3365EXPLAIN SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42;
3366id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33671	SIMPLE	t2	index	NULL	a	15	NULL	16	Using where; Using index
3368SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42;
3369COUNT(DISTINCT a, b)	SUM(DISTINCT a)
33700	NULL
3371EXPLAIN SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a;
3372id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33731	SIMPLE	t2	index	NULL	a	15	NULL	16	Using index
3374SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a;
3375SUM(DISTINCT a)	MAX(b)
33761	8
33772	8
3378EXPLAIN SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c;
3379id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33801	SIMPLE	t2	range	NULL	a	15	NULL	17	Using index for group-by
3381SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c;
338242 * (a + c + COUNT(DISTINCT c, a, b))
3383126
3384126
3385126
3386126
3387126
3388126
3389126
3390126
3391168
3392168
3393168
3394168
3395168
3396168
3397168
3398168
3399EXPLAIN SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a;
3400id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34011	SIMPLE	t2	index	NULL	a	15	NULL	16	Using index
3402SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a;
3403(SUM(DISTINCT a) + MAX(b))
34049
340510
3406DROP TABLE t1,t2;
3407# end of WL#3220 tests
3408#
3409# Bug#50539: Wrong result when loose index scan is used for an aggregate
3410#            function with distinct
3411#
3412CREATE TABLE t1 (
3413f1 int(11) NOT NULL DEFAULT '0',
3414f2 char(1) NOT NULL DEFAULT '',
3415PRIMARY KEY (f1,f2)
3416)  engine=RocksDB;
3417insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'),
3418(3, 'A'), (3, 'B'), (3, 'C'), (3, 'D');
3419ANALYZE TABLE t1;
3420Table	Op	Msg_type	Msg_text
3421test.t1	analyze	status	Engine-independent statistics collected
3422test.t1	analyze	status	OK
3423SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
3424f1	COUNT(DISTINCT f2)
34251	3
34262	1
34273	4
3428explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
3429id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34301	SIMPLE	t1	index	NULL	PRIMARY	5	NULL	8	Using index
3431drop table t1;
3432# End of test#50539.
3433#
3434# Bug#17217128 -  BAD INTERACTION BETWEEN MIN/MAX AND
3435#                 "HAVING SUM(DISTINCT)": WRONG RESULTS.
3436#
3437CREATE TABLE t (a INT, b INT, KEY(a,b)) engine=RocksDB;
3438INSERT INTO t VALUES (1,1), (2,2), (3,3), (4,4), (1,0), (3,2), (4,5);
3439ANALYZE TABLE t;
3440Table	Op	Msg_type	Msg_text
3441test.t	analyze	status	Engine-independent statistics collected
3442test.t	analyze	status	OK
3443ANALYZE TABLE t;
3444Table	Op	Msg_type	Msg_text
3445test.t	analyze	status	Engine-independent statistics collected
3446test.t	analyze	status	OK
3447SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a;
3448a	SUM(DISTINCT a)	MIN(b)
34491	1	0
34502	2	2
34513	3	2
34524	4	4
3453EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a;
3454id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34551	SIMPLE	t	index	NULL	a	10	NULL	7	Using index
3456SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a;
3457a	SUM(DISTINCT a)	MAX(b)
34581	1	1
34592	2	2
34603	3	3
34614	4	5
3462EXPLAIN SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a;
3463id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34641	SIMPLE	t	index	NULL	a	10	NULL	7	Using index
3465SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a);
3466a	MAX(b)
34671	1
34682	2
34693	3
34704	5
3471EXPLAIN SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a);
3472id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34731	SIMPLE	t	index	NULL	a	10	NULL	7	Using index
3474SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t;
3475SUM(DISTINCT a)	MIN(b)	MAX(b)
347610	0	5
3477EXPLAIN SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t;
3478id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34791	SIMPLE	t	index	NULL	a	10	NULL	7	Using index
3480SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a;
3481a	SUM(DISTINCT a)	MIN(b)	MAX(b)
34821	1	0	1
34832	2	2	2
34843	3	2	3
34854	4	4	5
3486EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a;
3487id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34881	SIMPLE	t	index	NULL	a	10	NULL	7	Using index
3489DROP TABLE t;
3490#
3491# Bug#18109609: LOOSE INDEX SCAN IS NOT USED WHEN IT SHOULD
3492#
3493CREATE TABLE t1 (
3494id INT AUTO_INCREMENT PRIMARY KEY,
3495c1 INT,
3496c2 INT,
3497KEY(c1,c2)) engine=RocksDB;
3498INSERT INTO t1(c1,c2) VALUES
3499(1, 1), (1,2), (2,1), (2,2), (3,1), (3,2), (3,3), (4,1), (4,2), (4,3),
3500(4,4), (4,5), (4,6), (4,7), (4,8), (4,9), (4,10), (4,11), (4,12), (4,13),
3501(4,14), (4,15), (4,16), (4,17), (4,18), (4,19), (4,20),(5,5);
3502ANALYZE TABLE t1;
3503Table	Op	Msg_type	Msg_text
3504test.t1	analyze	status	Engine-independent statistics collected
3505test.t1	analyze	status	OK
3506EXPLAIN SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1;
3507id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
35081	SIMPLE	t1	ref	c1	c1	5	const	28	Using index
3509FLUSH STATUS;
3510SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1;
3511MAX(c2)	c1
351220	4
3513SHOW SESSION STATUS LIKE 'Handler_read%';
3514Variable_name	Value
3515Handler_read_first	0
3516Handler_read_key	1
3517Handler_read_last	0
3518Handler_read_next	20
3519Handler_read_prev	0
3520Handler_read_retry	0
3521Handler_read_rnd	0
3522Handler_read_rnd_deleted	0
3523Handler_read_rnd_next	0
3524DROP TABLE t1;
3525# End of test for Bug#18109609
3526set global debug_dbug=@debug_tmp;
3527