1#
2# Test file for WL#1724 (Min/Max Optimization for Queries with Group By Clause).
3# The queries in this file test query execution via QUICK_GROUP_MIN_MAX_SELECT.
4#
5
6#
7# TODO:
8# Add queries with:
9# - C != const
10# - C IS NOT NULL
11# - HAVING clause
12
13--disable_warnings
14drop table if exists t1;
15--enable_warnings
16
17create table t1 (
18  a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' '
19);
20
21insert into t1 (a1, a2, b, c, d) values
22('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
23('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
24('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
25('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
26('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
27('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
28('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
29('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
30('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
31('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
32('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
33('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
34('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
35('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
36('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
37('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
38('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
39('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
40('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
41('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
42('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
43('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
44('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
45('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
46('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
47('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
48('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
49('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
50('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
51('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
52('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
53('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
54
55create index idx_t1_0 on t1 (a1);
56create index idx_t1_1 on t1 (a1,a2,b,c);
57create index idx_t1_2 on t1 (a1,a2,b);
58analyze table t1;
59
60# t2 is the same as t1, but with some NULLs in the MIN/MAX column, and
61# one more nullable attribute
62
63--disable_warnings
64drop table if exists t2;
65--enable_warnings
66
67create table t2 (
68  a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(248) default ' '
69);
70insert into t2 select * from t1;
71# add few rows with NULL's in the MIN/MAX column
72insert into t2 (a1, a2, b, c, d) values
73('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
74('a','a','a',NULL,'xyz'),
75('a','a','b',NULL,'xyz'),
76('a','b','a',NULL,'xyz'),
77('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
78('d','b','b',NULL,'xyz'),
79('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
80('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),
81('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
82('a','a','a',NULL,'xyz'),
83('a','a','b',NULL,'xyz'),
84('a','b','a',NULL,'xyz'),
85('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
86('d','b','b',NULL,'xyz'),
87('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
88('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz');
89
90create index idx_t2_0 on t2 (a1);
91create index idx_t2_1 on t2 (a1,a2,b,c);
92create index idx_t2_2 on t2 (a1,a2,b);
93analyze table t2;
94
95# Table t3 is the same as t1, but with smaller column lenghts.
96# This allows to test different branches of the cost computation procedure
97# when the number of keys per block are less than the number of keys in the
98# sub-groups formed by predicates over non-group attributes.
99
100--disable_warnings
101drop table if exists t3;
102--enable_warnings
103
104create table t3 (
105  a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' '
106);
107
108insert into t3 (a1, a2, b, c, d) values
109('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
110('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
111('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
112('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
113('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
114('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
115('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
116('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
117('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
118('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
119('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
120('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
121insert into t3 (a1, a2, b, c, d) values
122('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
123('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
124('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
125('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
126('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
127('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
128('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
129('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
130('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
131('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
132('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
133('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
134insert into t3 (a1, a2, b, c, d) values
135('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
136('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
137('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
138('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
139('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
140('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
141('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
142('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
143('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
144('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
145('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
146('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
147insert into t3 (a1, a2, b, c, d) values
148('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
149('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
150('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
151('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
152('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
153('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
154('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
155('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
156('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
157('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
158('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
159('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
160
161create index idx_t3_0 on t3 (a1);
162create index idx_t3_1 on t3 (a1,a2,b,c);
163create index idx_t3_2 on t3 (a1,a2,b);
164analyze table t3;
165
166
167#
168# Queries without a WHERE clause. These queries do not use ranges.
169#
170
171# plans
172explain select a1, min(a2) from t1 group by a1;
173explain select a1, max(a2) from t1 group by a1;
174explain select a1, min(a2), max(a2) from t1 group by a1;
175explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
176explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
177--replace_column 7 # 9 #
178explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
179# Select fields in different order
180explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
181explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
182explain select min(a2) from t1 group by a1;
183explain select a2, min(c), max(c) from t1 group by a1,a2,b;
184
185# queries
186select a1, min(a2) from t1 group by a1;
187select a1, max(a2) from t1 group by a1;
188select a1, min(a2), max(a2) from t1 group by a1;
189select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
190select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
191select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
192# Select fields in different order
193select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
194select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
195select min(a2) from t1 group by a1;
196select a2, min(c), max(c) from t1 group by a1,a2,b;
197
198#
199# Queries with a where clause
200#
201
202# A) Preds only over the group 'A' attributes
203# plans
204explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
205explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
206explain select a1,a2,b,       max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
207explain select a1, max(c)            from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
208explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
209explain select a1,a2,b,       max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
210explain select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
211explain 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;
212explain 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;
213explain select a1,min(c),max(c)      from t1 where a1 >= 'b' group by a1,a2,b;
214explain select a1,  max(c)           from t1 where a1 in ('a','b','d') group by a1,a2,b;
215
216--replace_column 9 #
217explain select a1,a2,b,       max(c) from t2 where a1 < 'd' group by a1,a2,b;
218--replace_column 9 #
219explain select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
220--replace_column 9 #
221explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
222--replace_column 9 #
223explain select a1,a2,b,       max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
224--replace_column 9 #
225explain select a1, max(c)            from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
226--replace_column 9 #
227explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
228--replace_column 9 #
229explain select a1,a2,b,       max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
230--replace_column 9 #
231explain select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
232--replace_column 9 #
233explain 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;
234--replace_column 9 #
235explain 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;
236--replace_column 9 #
237explain select a1,min(c),max(c)      from t2 where a1 >= 'b' group by a1,a2,b;
238--replace_column 9 #
239explain select a1,  max(c)           from t2 where a1 in ('a','b','d') group by a1,a2,b;
240
241# queries
242select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
243select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
244select a1,a2,b,       max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
245select a1, max(c)            from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
246select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
247select a1,a2,b,       max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
248select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
249select 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;
250select 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;
251select a1,min(c),max(c)      from t1 where a1 >= 'b' group by a1,a2,b;
252select a1,  max(c)           from t1 where a1 in ('a','b','d') group by a1,a2,b;
253
254select a1,a2,b,       max(c) from t2 where a1 < 'd' group by a1,a2,b;
255select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
256select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
257select a1,a2,b,       max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
258select a1, max(c)            from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
259select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
260select a1,a2,b,       max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
261select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
262select 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;
263select 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;
264select a1,min(c),max(c)      from t2 where a1 >= 'b' group by a1,a2,b;
265select a1,  max(c)           from t2 where a1 in ('a','b','d') group by a1,a2,b;
266
267# B) Equalities only over the non-group 'B' attributes
268# plans
269explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
270explain select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b') group by a1;
271explain select a1,a2,b,       max(c) from t1 where (b = 'b') group by a1,a2;
272explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
273explain select a1,a2, max(c)         from t1 where (b = 'b') group by a1,a2;
274
275explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
276explain select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b') group by a1;
277explain select a1,a2,b,       max(c) from t2 where (b = 'b') group by a1,a2;
278explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
279explain select a1,a2, max(c)         from t2 where (b = 'b') group by a1,a2;
280
281# these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost()
282explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
283explain select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b') group by a1;
284
285# queries
286select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
287select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b') group by a1;
288select a1,a2,b,       max(c) from t1 where (b = 'b') group by a1,a2;
289select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
290select a1,a2, max(c)         from t1 where (b = 'b') group by a1,a2;
291
292select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
293select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b') group by a1;
294select a1,a2,b,       max(c) from t2 where (b = 'b') group by a1,a2;
295select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
296select a1,a2, max(c)         from t2 where (b = 'b') group by a1,a2;
297
298# these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost()
299select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
300select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b') group by a1;
301
302
303# IS NULL (makes sense for t2 only)
304# plans
305explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
306explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
307explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
308explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
309explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
310explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
311# queries
312select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
313select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
314select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
315select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
316select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
317select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
318
319# C) Range predicates for the MIN/MAX attribute
320# plans
321--replace_column 9 #
322explain select a1,a2,b,       max(c) from t1 where (c > 'b1') group by a1,a2,b;
323explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
324explain select a1,a2,b,       max(c) from t1 where (c > 'f123') group by a1,a2,b;
325explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
326explain select a1,a2,b,       max(c) from t1 where (c < 'a0') group by a1,a2,b;
327explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
328explain select a1,a2,b,       max(c) from t1 where (c < 'k321') group by a1,a2,b;
329explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
330explain select a1,a2,b,       max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
331explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
332explain select a1,a2,b,       max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
333explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
334explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
335explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
336explain 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;
337explain 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;
338
339--replace_column 9 #
340explain select a1,a2,b,       max(c) from t2 where (c > 'b1') group by a1,a2,b;
341--replace_column 9 #
342explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
343--replace_column 9 #
344explain select a1,a2,b,       max(c) from t2 where (c > 'f123') group by a1,a2,b;
345--replace_column 9 #
346explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
347--replace_column 9 #
348explain select a1,a2,b,       max(c) from t2 where (c < 'a0') group by a1,a2,b;
349--replace_column 9 #
350explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
351--replace_column 9 #
352explain select a1,a2,b,       max(c) from t2 where (c < 'k321') group by a1,a2,b;
353--replace_column 9 #
354explain select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
355--replace_column 9 #
356explain select a1,a2,b,       max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
357--replace_column 9 #
358explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
359--replace_column 9 #
360explain select a1,a2,b,       max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
361--replace_column 9 #
362explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
363--replace_column 9 #
364explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
365--replace_column 9 #
366explain select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
367--replace_column 9 #
368explain 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;
369
370# queries
371select a1,a2,b,       max(c) from t1 where (c > 'b1') group by a1,a2,b;
372select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
373select a1,a2,b,       max(c) from t1 where (c > 'f123') group by a1,a2,b;
374select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
375select a1,a2,b,       max(c) from t1 where (c < 'a0') group by a1,a2,b;
376select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
377select a1,a2,b,       max(c) from t1 where (c < 'k321') group by a1,a2,b;
378select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
379select a1,a2,b,       max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
380select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
381select a1,a2,b,       max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
382select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
383select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
384select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
385select 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;
386select 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;
387
388select a1,a2,b,       max(c) from t2 where (c > 'b1') group by a1,a2,b;
389select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
390select a1,a2,b,       max(c) from t2 where (c > 'f123') group by a1,a2,b;
391select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
392select a1,a2,b,       max(c) from t2 where (c < 'a0') group by a1,a2,b;
393select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
394select a1,a2,b,       max(c) from t2 where (c < 'k321') group by a1,a2,b;
395select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
396select a1,a2,b,       max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
397select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
398select a1,a2,b,       max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
399select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
400select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
401select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
402select 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;
403
404# analyze the sub-select
405explain select a1,a2,b,min(c),max(c) from t1
406where exists ( select * from t2 where t2.c = t1.c )
407group by a1,a2,b;
408
409# the sub-select is unrelated to MIN/MAX
410explain select a1,a2,b,min(c),max(c) from t1
411where exists ( select * from t2 where t2.c > 'b1' )
412group by a1,a2,b;
413
414
415# A,B,C) Predicates referencing mixed classes of attributes
416# plans
417explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
418explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
419explain select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
420explain 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;
421explain 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;
422explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
423explain 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;
424
425--replace_column 9 #
426explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
427--replace_column 9 #
428explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
429--replace_column 9 #
430explain select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
431--replace_column 9 #
432explain 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;
433--replace_column 9 #
434explain 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;
435--replace_column 9 #
436explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
437
438# queries
439select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
440select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
441select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
442select 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;
443select 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;
444select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
445select 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;
446
447select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
448select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
449select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
450select 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;
451select 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;
452select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
453
454
455#
456# GROUP BY queries without MIN/MAX
457#
458
459# plans
460explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
461explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
462explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
463explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
464
465--replace_column 9 #
466explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
467--replace_column 9 #
468explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
469--replace_column 9 #
470explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
471--replace_column 9 #
472explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
473
474# queries
475select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
476select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
477select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
478select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
479
480select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
481select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
482select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
483select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
484
485#
486# DISTINCT queries
487#
488
489# plans
490explain select distinct a1,a2,b from t1;
491explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
492explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
493explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
494explain select distinct b from t1 where (a2 >= 'b') and (b = 'a');
495
496--replace_column 9 #
497explain select distinct a1,a2,b from t2;
498--replace_column 9 #
499explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
500explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
501--replace_column 9 #
502explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
503explain select distinct b from t2 where (a2 >= 'b') and (b = 'a');
504
505# queries
506select distinct a1,a2,b from t1;
507select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
508select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
509select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
510select distinct b from t1 where (a2 >= 'b') and (b = 'a');
511
512select distinct a1,a2,b from t2;
513select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
514select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
515select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
516select distinct b from t2 where (a2 >= 'b') and (b = 'a');
517
518# BUG #6303
519select distinct t_00.a1
520from t1 t_00
521where exists ( select * from t2 where a1 = t_00.a1 );
522
523# BUG #8532 - SELECT DISTINCT a, a causes server to crash
524select distinct a1,a1 from t1;
525select distinct a2,a1,a2,a1 from t1;
526select distinct t1.a1,t2.a1 from t1,t2;
527
528
529#
530# DISTINCT queries with GROUP-BY
531#
532
533# plans
534explain select distinct a1,a2,b from t1;
535explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
536explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
537explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
538explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
539
540--replace_column 9 #
541explain select distinct a1,a2,b from t2;
542--replace_column 9 #
543explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
544--replace_column 9 #
545explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
546--replace_column 9 #
547explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
548--replace_column 9 #
549explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
550
551# queries
552select distinct a1,a2,b from t1;
553select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
554select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
555select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
556select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
557
558select distinct a1,a2,b from t2;
559select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
560select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
561select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
562select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
563
564
565#
566# COUNT (DISTINCT cols) queries
567#
568
569explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
570explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
571explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
572explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
573explain extended select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
574
575select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
576select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
577select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
578select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
579select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
580
581#
582# Queries with expressions in the select clause
583#
584
585explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
586explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
587explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
588explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
589explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
590
591select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
592select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
593select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
594select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
595select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
596
597
598#
599# Negative examples: queries that should NOT be treated as optimizable by
600# QUICK_GROUP_MIN_MAX_SELECT
601#
602
603# select a non-indexed attribute
604explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b;
605
606explain select a1,a2,b,d from t1 group by a1,a2,b;
607
608# predicate that references an attribute that is after the MIN/MAX argument
609# in the index
610explain extended select a1,a2,min(b),max(b) from t1
611where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;
612
613# predicate that references a non-indexed attribute
614explain extended select a1,a2,b,min(c),max(c) from t1
615where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
616
617explain extended select a1,a2,b,c from t1
618where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
619
620# non-equality predicate for a non-group select attribute
621explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
622explain 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;
623
624# non-group field with an equality predicate that references a keypart after the
625# MIN/MAX argument
626explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
627select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
628
629# disjunction for a non-group select attribute
630explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1;
631
632# non-range predicate for the MIN/MAX attribute
633explain select a1,a2,b,min(c),max(c) from t2
634where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b;
635
636# not all attributes are indexed by one index
637explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c;
638
639# other aggregate functions than MIN/MAX
640explain select a1,a2,count(a2) from t1 group by a1,a2,b;
641explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
642explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
643
644
645#
646# Bug #16710: select distinct doesn't return all it should
647#
648
649explain select distinct(a1) from t1 where ord(a2) = 98;
650select distinct(a1) from t1 where ord(a2) = 98;
651
652#
653# BUG#11044: DISTINCT or GROUP BY queries with equality predicates instead of MIN/MAX.
654#
655
656explain select a1 from t1 where a2 = 'b' group by a1;
657select a1 from t1 where a2 = 'b' group by a1;
658
659explain select distinct a1 from t1 where a2 = 'b';
660select distinct a1 from t1 where a2 = 'b';
661
662#
663# Bug #12672: primary key implcitly included in every innodb index
664#
665# Test case moved to group_min_max_innodb
666
667
668#
669# Bug #6142: a problem with the empty innodb table
670#
671# Test case moved to group_min_max_innodb
672
673
674#
675# Bug #9798: group by with rollup
676#
677# Test case moved to group_min_max_innodb
678
679
680#
681# Bug #13293 Wrongly used index results in endless loop.
682#
683# Test case moved to group_min_max_innodb
684
685
686drop table t1,t2,t3;
687
688#
689# Bug #14920 Ordering aggregated result sets with composite primary keys
690# corrupts resultset
691#
692create table t1 (c1 int not null,c2 int not null, primary key(c1,c2));
693insert into t1 (c1,c2) values
694(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9);
695select distinct c1, c2 from t1 order by c2;
696select c1,min(c2) as c2 from t1 group by c1 order by c2;
697select c1,c2 from t1 group by c1,c2 order by c2;
698drop table t1;
699
700#
701# Bug #16203: Analysis for possible min/max optimization erroneously
702#             returns impossible range
703#
704
705CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b));
706INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4);
707OPTIMIZE TABLE t1;
708
709SELECT a FROM t1 WHERE a='AA' GROUP BY a;
710SELECT a FROM t1 WHERE a='BB' GROUP BY a;
711
712EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a;
713EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a;
714
715SELECT DISTINCT a FROM t1 WHERE a='BB';
716SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%';
717SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a;
718
719DROP TABLE t1;
720
721
722#
723# Bug #15102: select distinct returns empty result, select count
724#             distinct > 0 (correct)
725#
726
727CREATE TABLE t1 (
728   a int(11) NOT NULL DEFAULT '0',
729   b varchar(16) COLLATE latin1_general_ci NOT NULL DEFAULT '',
730   PRIMARY KEY  (a,b)
731 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
732
733delimiter |;
734
735CREATE PROCEDURE a(x INT)
736BEGIN
737  DECLARE rnd INT;
738  DECLARE cnt INT;
739
740  WHILE x > 0 DO
741    SET rnd= x % 100;
742    SET cnt = (SELECT COUNT(*) FROM t1 WHERE a = rnd);
743    INSERT INTO t1(a,b) VALUES (rnd, CAST(cnt AS CHAR));
744    SET x= x - 1;
745  END WHILE;
746END|
747
748DELIMITER ;|
749
750CALL a(1000);
751
752SELECT a FROM t1 WHERE a=0;
753SELECT DISTINCT a FROM t1 WHERE a=0;
754SELECT COUNT(DISTINCT a) FROM t1 WHERE a=0;
755
756DROP TABLE t1;
757DROP PROCEDURE a;
758
759#
760# Bug #18068: SELECT DISTINCT
761#
762
763CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a));
764
765INSERT INTO t1 (a) VALUES
766  (''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'),
767  ('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'),
768  ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN');
769
770EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a;
771SELECT DISTINCT a,a FROM t1 ORDER BY a;
772
773DROP TABLE t1;
774
775#
776# Bug #21007: NATURAL JOIN (any JOIN (2 x NATURAL JOIN)) crashes the server
777#
778
779CREATE TABLE t1 (id1 INT, id2 INT);
780CREATE TABLE t2 (id2 INT, id3 INT, id5 INT);
781CREATE TABLE t3 (id3 INT, id4 INT);
782CREATE TABLE t4 (id4 INT);
783CREATE TABLE t5 (id5 INT, id6 INT);
784CREATE TABLE t6 (id6 INT);
785
786INSERT INTO t1 VALUES(1,1);
787INSERT INTO t2 VALUES(1,1,1);
788INSERT INTO t3 VALUES(1,1);
789INSERT INTO t4 VALUES(1);
790INSERT INTO t5 VALUES(1,1);
791INSERT INTO t6 VALUES(1);
792
793# original bug query
794SELECT * FROM
795t1
796  NATURAL JOIN
797(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
798    ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
799
800# inner join swapped
801SELECT * FROM
802t1
803  NATURAL JOIN
804(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2
805    ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
806
807# one join less, no ON cond
808SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2);
809
810# wrong error message: 'id2' - ambiguous column
811SELECT * FROM
812(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
813    ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5))
814  NATURAL JOIN
815t1;
816SELECT * FROM
817(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6)))
818  NATURAL JOIN
819t1;
820
821DROP TABLE t1,t2,t3,t4,t5,t6;
822
823#
824# Bug#22342: No results returned for query using max and group by
825#
826CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
827INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
828
829explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
830SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
831SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
832CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
833INSERT INTO t2 SELECT a,b,b FROM t1;
834explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
835SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
836
837DROP TABLE t1,t2;
838
839#
840# Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar statements
841#
842
843CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
844INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
845       (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
846EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
847FLUSH STATUS;
848SELECT max(b), a FROM t1 GROUP BY a;
849SHOW STATUS LIKE 'handler_read__e%';
850EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
851FLUSH STATUS;
852CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a;
853SHOW STATUS LIKE 'handler_read__e%';
854FLUSH STATUS;
855SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
856SHOW STATUS LIKE 'handler_read__e%';
857FLUSH STATUS;
858(SELECT max(b), a FROM t1 GROUP BY a) UNION
859 (SELECT max(b), a FROM t1 GROUP BY a);
860SHOW STATUS LIKE 'handler_read__e%';
861EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION
862 (SELECT max(b), a FROM t1 GROUP BY a);
863
864EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
865  FROM t1 AS t1_outer;
866EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS
867  (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
868EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
869  (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
870EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
871  a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
872EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
873  a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
874EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2
875   ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2)
876   AND t1_outer1.b = t1_outer2.b;
877EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
878  FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
879
880CREATE TABLE t3 LIKE t1;
881FLUSH STATUS;
882INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;
883SHOW STATUS LIKE 'handler_read__e%';
884DELETE FROM t3;
885FLUSH STATUS;
886INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2)
887  FROM t1 LIMIT 1;
888SHOW STATUS LIKE 'handler_read__e%';
889FLUSH STATUS;
890DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
891SHOW STATUS LIKE 'handler_read__e%';
892FLUSH STATUS;
893--error ER_SUBQUERY_NO_1_ROW
894DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x
895                      FROM t1) > 10000;
896SHOW STATUS LIKE 'handler_read__e%';
897
898DROP TABLE t1,t2,t3;
899
900#
901# Bug#25602: queries with DISTINCT and SQL_BIG_RESULT hint
902#            for which loose scan optimization is applied
903#
904
905CREATE TABLE t1 (a int, INDEX idx(a));
906INSERT INTO t1 VALUES
907  (4), (2), (1), (2), (4), (2), (1), (4),
908  (4), (2), (1), (2), (2), (4), (1), (4);
909
910EXPLAIN SELECT DISTINCT(a) FROM t1;
911SELECT DISTINCT(a) FROM t1;
912EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
913SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
914
915DROP TABLE t1;
916
917#
918# Bug #32268: Indexed queries give bogus MIN and MAX results
919#
920
921CREATE TABLE t1 (a INT, b INT);
922INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3);
923INSERT INTO t1 SELECT a + 1, b FROM t1;
924INSERT INTO t1 SELECT a + 2, b FROM t1;
925
926EXPLAIN
927SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
928SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
929
930CREATE INDEX break_it ON t1 (a, b);
931
932EXPLAIN
933SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
934SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
935
936EXPLAIN
937SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
938SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
939
940EXPLAIN
941SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
942SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
943
944DROP TABLE t1;
945
946#
947# Bug#38195: Incorrect handling of aggregate functions when loose index scan is
948#            used causes server crash.
949#
950create table t1 (a int, b int, primary key (a,b), key `index` (a,b)) engine=MyISAM;
951insert into  t1 (a,b) values
952(0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6),
953  (0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13),
954(1,0),(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),
955  (1,7),(1,8),(1,9),(1,10),(1,11),(1,12),(1,13),
956(2,0),(2,1),(2,2),(2,3),(2,4),(2,5),(2,6),
957  (2,7),(2,8),(2,9),(2,10),(2,11),(2,12),(2,13),
958(3,0),(3,1),(3,2),(3,3),(3,4),(3,5),(3,6),
959  (3,7),(3,8),(3,9),(3,10),(3,11),(3,12),(3,13);
960insert into t1 (a,b) select a, max(b)+1 from t1 where a = 0 group by a;
961select * from t1;
962explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a;
963drop table t1;
964
965
966#
967# Bug #41610: key_infix_len can be overwritten causing some group by queries
968# to return no rows
969#
970
971CREATE TABLE t1 (a int, b int, c int, d int,
972  KEY foo (c,d,a,b), KEY bar (c,a,b,d));
973
974INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4);
975INSERT INTO t1 SELECT * FROM t1;
976INSERT INTO t1 SELECT * FROM t1;
977INSERT INTO t1 SELECT a,b,c+1,d FROM t1;
978
979#Should be non-empty
980EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4;
981SELECT DISTINCT c FROM t1 WHERE d=4;
982
983DROP TABLE t1;
984
985--echo #
986--echo # Bug #45386: Wrong query result with MIN function in field list,
987--echo #  WHERE and GROUP BY clause
988--echo #
989
990CREATE TABLE t (a INT, b INT, INDEX (a,b));
991INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
992INSERT INTO t SELECT * FROM t;
993INSERT INTO t SELECT * FROM t;
994
995--echo # test MIN
996--echo #should use range with index for group by
997EXPLAIN
998SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
999--echo #should return 1 row
1000SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
1001
1002--echo # test MAX
1003--echo #should use range with index for group by
1004EXPLAIN
1005SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
1006--echo #should return 1 row
1007SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
1008
1009--echo # test 3 ranges and use the middle one
1010INSERT INTO t SELECT a, 2 FROM t;
1011
1012--echo #should use range with index for group by
1013EXPLAIN
1014SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
1015--echo #should return 1 row
1016SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
1017
1018DROP TABLE t;
1019
1020--echo #
1021--echo # Bug #48472: Loose index scan inappropriately chosen for some WHERE
1022--echo #             conditions
1023--echo #
1024
1025CREATE TABLE t (a INT, b INT, INDEX (a,b));
1026INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
1027INSERT INTO t SELECT * FROM t;
1028
1029SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a;
1030
1031DROP TABLE t;
1032
1033--echo End of 5.0 tests
1034
1035--echo #
1036--echo # Bug #46607: Assertion failed: (cond_type == Item::FUNC_ITEM) results in
1037--echo #              server crash
1038--echo #
1039
1040CREATE TABLE t (a INT, b INT, INDEX (a,b));
1041INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
1042INSERT INTO t SELECT * FROM t;
1043
1044SELECT a, MAX(b) FROM t WHERE b GROUP BY a;
1045
1046DROP TABLE t;
1047
1048#
1049# BUG#49902 - SELECT returns incorrect results
1050#
1051CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, KEY (b));
1052INSERT INTO t1 VALUES(1,1),(2,1);
1053ANALYZE TABLE t1;
1054SELECT 1 AS c, b FROM t1 WHERE b IN (1,2) GROUP BY c, b;
1055SELECT a FROM t1 WHERE b=1;
1056DROP TABLE t1;
1057
1058--echo #
1059--echo # Bug#47762: Incorrect result from MIN() when WHERE tests NOT NULL column
1060--echo #            for NULL
1061--echo #
1062
1063--echo ## Test for NULLs allowed
1064CREATE TABLE t1 ( a INT, KEY (a) );
1065INSERT INTO t1 VALUES (1), (2), (3);
1066--source include/min_null_cond.inc
1067INSERT INTO t1 VALUES (NULL), (NULL);
1068--source include/min_null_cond.inc
1069DROP TABLE t1;
1070
1071--echo ## Test for NOT NULLs
1072CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY);
1073INSERT INTO t1 VALUES (1), (2), (3);
1074--echo #
1075--echo # NULL-safe operator test disabled for non-NULL indexed columns.
1076--echo #
1077--echo # See bugs
1078--echo #
1079--echo # - Bug#52173: Reading NULL value from non-NULL index gives
1080--echo #   wrong result in embedded server
1081--echo #
1082--echo # - Bug#52174: Sometimes wrong plan when reading a MAX value from
1083--echo #   non-NULL index
1084--echo #
1085--let $skip_null_safe_test= 1
1086--source include/min_null_cond.inc
1087DROP TABLE t1;
1088
1089--echo #
1090--echo # Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&, Item*) at
1091--echo # opt_sum.cc:305
1092--echo #
1093CREATE TABLE t1 ( a INT, KEY (a) );
1094INSERT INTO t1 VALUES (1), (2), (3);
1095
1096SELECT MIN( a ) AS min_a
1097FROM t1
1098WHERE a > 1 AND a IS NULL
1099ORDER BY min_a;
1100
1101DROP TABLE t1;
1102
1103
1104--echo End of 5.1 tests
1105
1106
1107--echo #
1108--echo # WL#3220 (Loose index scan for COUNT DISTINCT)
1109--echo #
1110
1111CREATE TABLE t1 (a INT, b INT, c INT, KEY (a,b));
1112INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1), (1,4,1);
1113INSERT INTO t1 SELECT a, b + 4, 1 FROM t1;
1114INSERT INTO t1 SELECT a + 1, b, 1 FROM t1;
1115CREATE TABLE t2 (a INT, b INT, c INT, d INT, e INT, f INT, KEY (a,b,c));
1116INSERT INTO t2 VALUES (1,1,1,1,1,1), (1,2,1,1,1,1), (1,3,1,1,1,1),
1117       (1,4,1,1,1,1);
1118INSERT INTO t2 SELECT a, b + 4, c,d,e,f FROM t2;
1119INSERT INTO t2 SELECT a + 1, b, c,d,e,f FROM t2;
1120
1121EXPLAIN SELECT COUNT(DISTINCT a) FROM t1;
1122SELECT COUNT(DISTINCT a) FROM t1;
1123
1124EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1;
1125SELECT COUNT(DISTINCT a,b) FROM t1;
1126
1127EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1;
1128SELECT COUNT(DISTINCT b,a) FROM t1;
1129
1130EXPLAIN SELECT COUNT(DISTINCT b) FROM t1;
1131SELECT COUNT(DISTINCT b) FROM t1;
1132
1133EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a;
1134SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a;
1135
1136EXPLAIN SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
1137SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
1138
1139EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;
1140SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;
1141
1142EXPLAIN SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
1143SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
1144
1145EXPLAIN SELECT COUNT(DISTINCT a, b + 0) FROM t1;
1146SELECT COUNT(DISTINCT a, b + 0) FROM t1;
1147
1148EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10;
1149SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10;
1150
1151EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10;
1152SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10;
1153
1154EXPLAIN SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10;
1155SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10;
1156
1157EXPLAIN SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1;
1158SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1;
1159
1160EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
1161SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
1162
1163EXPLAIN SELECT COUNT(DISTINCT a), 12 FROM t1;
1164SELECT COUNT(DISTINCT a), 12 FROM t1;
1165
1166EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2;
1167SELECT COUNT(DISTINCT a, b, c) FROM t2;
1168
1169EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2;
1170SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2;
1171
1172EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2;
1173SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2;
1174
1175EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;
1176SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;
1177
1178EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2;
1179SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2;
1180
1181EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2;
1182SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2;
1183
1184EXPLAIN SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c;
1185SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c;
1186
1187EXPLAIN SELECT COUNT(DISTINCT c, a, b) FROM t2
1188  WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;
1189SELECT COUNT(DISTINCT c, a, b) FROM t2
1190  WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;
1191
1192EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5
1193  GROUP BY b;
1194SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5
1195  GROUP BY b;
1196
1197EXPLAIN SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
1198SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
1199
1200EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
1201SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
1202
1203EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42;
1204SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42;
1205
1206EXPLAIN SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2
1207  WHERE b = 13 AND c = 42 GROUP BY a;
1208SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2
1209  WHERE b = 13 AND c = 42 GROUP BY a;
1210
1211--echo # This query could have been resolved using loose index scan since
1212--echo # the second part of count(..) is defined by a constant predicate
1213EXPLAIN SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42;
1214SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42;
1215
1216EXPLAIN SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a;
1217SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a;
1218
1219EXPLAIN SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c;
1220SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c;
1221
1222EXPLAIN SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a;
1223SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a;
1224
1225DROP TABLE t1,t2;
1226
1227--echo # end of WL#3220 tests
1228
1229--echo #
1230--echo # Bug#50539: Wrong result when loose index scan is used for an aggregate
1231--echo #            function with distinct
1232--echo #
1233CREATE TABLE t1 (
1234  f1 int(11) NOT NULL DEFAULT '0',
1235  f2 char(1) NOT NULL DEFAULT '',
1236  PRIMARY KEY (f1,f2)
1237) ;
1238insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'),
1239(3, 'A'), (3, 'B'), (3, 'C'), (3, 'D');
1240
1241SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
1242explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
1243
1244drop table t1;
1245--echo # End of test#50539.
1246
1247--echo #
1248--echo # Bug#17217128 -  BAD INTERACTION BETWEEN MIN/MAX AND
1249--echo #                 "HAVING SUM(DISTINCT)": WRONG RESULTS.
1250--echo #
1251
1252CREATE TABLE t (a INT, b INT, KEY(a,b));
1253INSERT INTO t VALUES (1,1), (2,2), (3,3), (4,4), (1,0), (3,2), (4,5);
1254let $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB
1255eval set optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE;
1256set @@session.optimizer_trace='enabled=on';
1257set end_markers_in_json=on;
1258
1259ANALYZE TABLE t;
1260
1261SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a;
1262EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a;
1263SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK
1264  FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
1265
1266SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a;
1267EXPLAIN SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a;
1268SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK
1269  FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
1270
1271SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a);
1272EXPLAIN SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a);
1273SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK
1274  FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
1275
1276SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t;
1277EXPLAIN SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t;
1278SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK
1279  FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
1280
1281SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a;
1282EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a;
1283SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK
1284  FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
1285
1286SET optimizer_trace_max_mem_size=DEFAULT;
1287SET optimizer_trace=DEFAULT;
1288SET end_markers_in_json=DEFAULT;
1289
1290DROP TABLE t;
1291
1292--echo #
1293--echo # Bug#18109609: LOOSE INDEX SCAN IS NOT USED WHEN IT SHOULD
1294--echo #
1295
1296CREATE TABLE t1 (
1297id INT AUTO_INCREMENT PRIMARY KEY,
1298c1 INT,
1299c2 INT,
1300KEY(c1,c2));
1301
1302INSERT INTO t1(c1,c2) VALUES
1303(1, 1), (1,2), (2,1), (2,2), (3,1), (3,2), (3,3), (4,1), (4,2), (4,3),
1304(4,4), (4,5), (4,6), (4,7), (4,8), (4,9), (4,10), (4,11), (4,12), (4,13),
1305(4,14), (4,15), (4,16), (4,17), (4,18), (4,19), (4,20),(5,5);
1306
1307EXPLAIN SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1;
1308FLUSH STATUS;
1309SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1;
1310SHOW SESSION STATUS LIKE 'Handler_read%';
1311
1312DROP TABLE t1;
1313
1314--echo # End of test for Bug#18109609
1315