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.
14 drop table if exists t1;
18 a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
21 insert 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');
55 create index idx_t1_0 on t1 (a1);
56 create index idx_t1_1 on t1 (a1,a2,b,c);
57 create index idx_t1_2 on t1 (a1,a2,b);
60 # t2 is the same as t1, but with some NULLs in the MIN/MAX column, and
61 # one more nullable attribute
64 drop table if exists t2;
68 a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(64) default ' '
70 insert into t2 select * from t1;
71 # add few rows with NULL's in the MIN/MAX column
72 insert 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');
90 create index idx_t2_0 on t2 (a1);
91 create index idx_t2_1 on t2 (a1,a2,b,c);
92 create index idx_t2_2 on t2 (a1,a2,b);
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.
101 drop table if exists t3;
105 a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' '
108 insert 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');
121 insert 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');
134 insert 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');
147 insert 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');
161 create index idx_t3_0 on t3 (a1);
162 create index idx_t3_1 on t3 (a1,a2,b,c);
163 create index idx_t3_2 on t3 (a1,a2,b);
168 # Queries without a WHERE clause. These queries do not use ranges.
172 explain select a1, min(a2) from t1 group by a1;
173 explain select a1, max(a2) from t1 group by a1;
174 explain select a1, min(a2), max(a2) from t1 group by a1;
175 explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
176 explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
177 --replace_column 7 # 9 #
178 explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
179 # Select fields in different order
180 explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
181 explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
182 explain select min(a2) from t1 group by a1;
183 explain select a2, min(c), max(c) from t1 group by a1,a2,b;
186 select a1, min(a2) from t1 group by a1;
187 select a1, max(a2) from t1 group by a1;
188 select a1, min(a2), max(a2) from t1 group by a1;
189 select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
190 select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
191 select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
192 # Select fields in different order
193 select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
194 select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
195 select min(a2) from t1 group by a1;
196 select a2, min(c), max(c) from t1 group by a1,a2,b;
199 # Queries with a where clause
202 # A) Preds only over the group 'A' attributes
204 explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
205 explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
206 explain select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
207 explain select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
208 explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
209 explain select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
210 explain select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
211 explain select a1,a2,b, max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
212 explain select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
213 explain select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
214 explain select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b;
217 explain select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b;
219 explain select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
221 explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
223 explain select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
225 explain select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
227 explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
229 explain select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
231 explain select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
233 explain select a1,a2,b, max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
235 explain select a1,a2,b,min(c),max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
237 explain select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
239 explain select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b;
242 select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
243 select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
244 select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
245 select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
246 select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
247 select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
248 select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
249 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;
250 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;
251 select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
252 select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b;
254 select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b;
255 select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
256 select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
257 select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
258 select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
259 select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
260 select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
261 select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
262 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;
263 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;
264 select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
265 select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b;
267 # B) Equalities only over the non-group 'B' attributes
269 explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
270 explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
271 explain select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2;
272 explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
273 explain select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2;
275 explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
276 explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
277 explain select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2;
278 explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
279 explain select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2;
281 # these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost()
282 explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
283 explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
286 select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
287 select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
288 select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2;
289 select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
290 select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2;
292 select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
293 select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
294 select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2;
295 select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
296 select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2;
298 # these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost()
299 select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
300 select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
303 # IS NULL (makes sense for t2 only)
305 explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
306 explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
307 explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
308 explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
309 explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
310 explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
312 select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
313 select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
314 select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
315 select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
316 select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
317 select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
319 # C) Range predicates for the MIN/MAX attribute
322 explain select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b;
323 explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
324 explain select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b;
325 explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
326 explain select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b;
327 explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
328 explain select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b;
329 explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
330 explain select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
331 explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
332 explain select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
333 explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
334 explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
335 explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
336 explain select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
337 explain select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b;
340 explain select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b;
342 explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
344 explain select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b;
346 explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
348 explain select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b;
350 explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
352 explain select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b;
354 explain select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
356 explain select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
358 explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
360 explain select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
362 explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
364 explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
366 explain select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
368 explain select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
371 select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b;
372 select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
373 select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b;
374 select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
375 select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b;
376 select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
377 select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b;
378 select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
379 select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
380 select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
381 select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
382 select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
383 select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
384 select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
385 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;
386 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;
388 select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b;
389 select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
390 select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b;
391 select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
392 select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b;
393 select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
394 select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b;
395 select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
396 select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
397 select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
398 select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
399 select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
400 select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
401 select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
402 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;
404 # analyze the sub-select
405 explain select a1,a2,b,min(c),max(c) from t1
406 where exists ( select * from t2 where t2.c = t1.c )
409 # the sub-select is unrelated to MIN/MAX
410 explain select a1,a2,b,min(c),max(c) from t1
411 where exists ( select * from t2 where t2.c > 'b1' )
415 # A,B,C) Predicates referencing mixed classes of attributes
417 explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
418 explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
419 explain select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
420 explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
421 explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
422 explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
423 explain select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b;
426 explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
428 explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
430 explain select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
432 explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
434 explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
436 explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
439 select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
440 select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
441 select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
442 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;
443 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;
444 select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
445 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;
447 select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
448 select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
449 select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
450 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;
451 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;
452 select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
456 # GROUP BY queries without MIN/MAX
460 explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
461 explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
462 explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
463 explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
466 explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
468 explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
470 explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
472 explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
475 select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
476 select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
477 select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
478 select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
480 select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
481 select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
482 select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
483 select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
490 explain select distinct a1,a2,b from t1;
491 explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
492 explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
493 explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
494 explain select distinct b from t1 where (a2 >= 'b') and (b = 'a');
497 explain select distinct a1,a2,b from t2;
499 explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
500 explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
502 explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
503 explain select distinct b from t2 where (a2 >= 'b') and (b = 'a');
506 select distinct a1,a2,b from t1;
507 select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
508 select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
509 select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
510 select distinct b from t1 where (a2 >= 'b') and (b = 'a');
512 select distinct a1,a2,b from t2;
513 select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
514 select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
515 select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
516 select distinct b from t2 where (a2 >= 'b') and (b = 'a');
519 select distinct t_00.a1
521 where exists ( select * from t2 where a1 = t_00.a1 );
523 # BUG #8532 - SELECT DISTINCT a, a causes server to crash
524 select distinct a1,a1 from t1;
525 select distinct a2,a1,a2,a1 from t1;
526 select distinct t1.a1,t2.a1 from t1,t2;
530 # DISTINCT queries with GROUP-BY
534 explain select distinct a1,a2,b from t1;
535 explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
536 explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
537 explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
538 explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
541 explain select distinct a1,a2,b from t2;
543 explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
545 explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
547 explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
549 explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
552 select distinct a1,a2,b from t1;
553 select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
554 select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
555 select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
556 select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
558 select distinct a1,a2,b from t2;
559 select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
560 select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
561 select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
562 select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
566 # COUNT (DISTINCT cols) queries
569 explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
570 explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
571 explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
572 explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
573 explain extended select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
575 select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
576 select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
577 select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
578 select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
579 select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
582 # Queries with expressions in the select clause
585 explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
586 explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
587 explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
588 explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
589 explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
591 select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
592 select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
593 select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
594 select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
595 select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
599 # Negative examples: queries that should NOT be treated as optimizable by
600 # QUICK_GROUP_MIN_MAX_SELECT
603 # select a non-indexed attribute
604 explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b;
606 explain select a1,a2,b,d from t1 group by a1,a2,b;
608 # predicate that references an attribute that is after the MIN/MAX argument
610 explain extended select a1,a2,min(b),max(b) from t1
611 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;
613 # predicate that references a non-indexed attribute
614 explain extended select a1,a2,b,min(c),max(c) from t1
615 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
617 explain extended select a1,a2,b,c from t1
618 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
620 # non-equality predicate for a non-group select attribute
621 explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
622 explain extended select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b;
624 # non-group field with an equality predicate that references a keypart after the
626 explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
627 select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
629 # disjunction for a non-group select attribute
630 explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1;
632 # non-range predicate for the MIN/MAX attribute
633 explain select a1,a2,b,min(c),max(c) from t2
634 where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b;
636 # not all attributes are indexed by one index
637 explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c;
639 # other aggregate functions than MIN/MAX
640 explain select a1,a2,count(a2) from t1 group by a1,a2,b;
641 explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
642 explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
646 # Bug #16710: select distinct doesn't return all it should
649 explain select distinct(a1) from t1 where ord(a2) = 98;
650 select distinct(a1) from t1 where ord(a2) = 98;
653 # BUG#11044: DISTINCT or GROUP BY queries with equality predicates instead of MIN/MAX.
656 explain select a1 from t1 where a2 = 'b' group by a1;
657 select a1 from t1 where a2 = 'b' group by a1;
659 explain select distinct a1 from t1 where a2 = 'b';
660 select distinct a1 from t1 where a2 = 'b';
663 # Bug #12672: primary key implcitly included in every innodb index
665 # Test case moved to group_min_max_innodb
669 # Bug #6142: a problem with the empty innodb table
671 # Test case moved to group_min_max_innodb
675 # Bug #9798: group by with rollup
677 # Test case moved to group_min_max_innodb
681 # Bug #13293 Wrongly used index results in endless loop.
683 # Test case moved to group_min_max_innodb
689 # Bug #14920 Ordering aggregated result sets with composite primary keys
692 create table t1 (c1 int not null,c2 int not null, primary key(c1,c2));
693 insert into t1 (c1,c2) values
694 (10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9);
695 select distinct c1, c2 from t1 order by c2;
696 select c1,min(c2) as c2 from t1 group by c1 order by c2;
697 select c1,c2 from t1 group by c1,c2 order by c2;
701 # Bug #16203: Analysis for possible min/max optimization erroneously
702 # returns impossible range
705 CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b));
706 INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4);
709 SELECT a FROM t1 WHERE a='AA' GROUP BY a;
710 SELECT a FROM t1 WHERE a='BB' GROUP BY a;
712 EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a;
713 EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a;
715 SELECT DISTINCT a FROM t1 WHERE a='BB';
716 SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%';
717 SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a;
723 # Bug #15102: select distinct returns empty result, select count
724 # distinct > 0 (correct)
728 a int(11) NOT NULL DEFAULT '0',
729 b varchar(16) COLLATE latin1_general_ci NOT NULL DEFAULT '',
731 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
735 CREATE PROCEDURE a(x INT)
742 SET cnt = (SELECT COUNT(*) FROM t1 WHERE a = rnd);
743 INSERT INTO t1(a,b) VALUES (rnd, CAST(cnt AS CHAR));
752 SELECT a FROM t1 WHERE a=0;
753 SELECT DISTINCT a FROM t1 WHERE a=0;
754 SELECT COUNT(DISTINCT a) FROM t1 WHERE a=0;
760 # Bug #18068: SELECT DISTINCT
763 CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a));
765 INSERT INTO t1 (a) VALUES
766 (''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'),
767 ('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'),
768 ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN');
770 EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a;
771 SELECT DISTINCT a,a FROM t1 ORDER BY a;
776 # Bug #21007: NATURAL JOIN (any JOIN (2 x NATURAL JOIN)) crashes the server
779 CREATE TABLE t1 (id1 INT, id2 INT);
780 CREATE TABLE t2 (id2 INT, id3 INT, id5 INT);
781 CREATE TABLE t3 (id3 INT, id4 INT);
782 CREATE TABLE t4 (id4 INT);
783 CREATE TABLE t5 (id5 INT, id6 INT);
784 CREATE TABLE t6 (id6 INT);
786 INSERT INTO t1 VALUES(1,1);
787 INSERT INTO t2 VALUES(1,1,1);
788 INSERT INTO t3 VALUES(1,1);
789 INSERT INTO t4 VALUES(1);
790 INSERT INTO t5 VALUES(1,1);
791 INSERT INTO t6 VALUES(1);
797 (t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
798 ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
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));
807 # one join less, no ON cond
808 SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2);
810 # wrong error message: 'id2' - ambiguous column
812 (t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
813 ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5))
817 (t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6)))
821 DROP TABLE t1,t2,t3,t4,t5,t6;
824 # Bug#22342: No results returned for query using max and group by
826 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
827 INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
829 explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
830 SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
831 SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
832 CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
833 INSERT INTO t2 SELECT a,b,b FROM t1;
834 explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
835 SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
840 # Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar statements
843 CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
844 INSERT 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);
846 EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
848 SELECT max(b), a FROM t1 GROUP BY a;
849 SHOW STATUS LIKE 'handler_read__e%';
850 EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
852 CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a;
853 SHOW STATUS LIKE 'handler_read__e%';
855 SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
856 SHOW STATUS LIKE 'handler_read__e%';
858 (SELECT max(b), a FROM t1 GROUP BY a) UNION
859 (SELECT max(b), a FROM t1 GROUP BY a);
860 SHOW STATUS LIKE 'handler_read__e%';
861 EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION
862 (SELECT max(b), a FROM t1 GROUP BY a);
864 EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
866 EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS
867 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
868 EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
869 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
870 EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
871 a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
872 EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
873 a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
874 EXPLAIN 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;
877 EXPLAIN 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;
880 CREATE TABLE t3 LIKE t1;
882 INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;
883 SHOW STATUS LIKE 'handler_read__e%';
886 INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2)
888 SHOW STATUS LIKE 'handler_read__e%';
890 DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
891 SHOW STATUS LIKE 'handler_read__e%';
893 --error ER_SUBQUERY_NO_1_ROW
894 DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x
896 SHOW STATUS LIKE 'handler_read__e%';
901 # Bug#25602: queries with DISTINCT and SQL_BIG_RESULT hint
902 # for which loose scan optimization is applied
905 CREATE TABLE t1 (a int, INDEX idx(a));
906 INSERT INTO t1 VALUES
907 (4), (2), (1), (2), (4), (2), (1), (4),
908 (4), (2), (1), (2), (2), (4), (1), (4);
910 EXPLAIN SELECT DISTINCT(a) FROM t1;
911 SELECT DISTINCT(a) FROM t1;
912 EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
913 SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
918 # Bug #32268: Indexed queries give bogus MIN and MAX results
921 CREATE TABLE t1 (a INT, b INT);
922 INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3);
923 INSERT INTO t1 SELECT a + 1, b FROM t1;
924 INSERT INTO t1 SELECT a + 2, b FROM t1;
927 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
928 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
930 CREATE INDEX break_it ON t1 (a, b);
933 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
934 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
937 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
938 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
941 SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
942 SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
947 # Bug#38195: Incorrect handling of aggregate functions when loose index scan is
948 # used causes server crash.
950 create table t1 (a int, b int, primary key (a,b), key `index` (a,b)) engine=MyISAM;
951 insert 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);
960 insert into t1 (a,b) select a, max(b)+1 from t1 where a = 0 group by a;
962 explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a;
967 # Bug #41610: key_infix_len can be overwritten causing some group by queries
971 CREATE TABLE t1 (a int, b int, c int, d int,
972 KEY foo (c,d,a,b), KEY bar (c,a,b,d));
974 INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4);
975 INSERT INTO t1 SELECT * FROM t1;
976 INSERT INTO t1 SELECT * FROM t1;
977 INSERT INTO t1 SELECT a,b,c+1,d FROM t1;
980 EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4;
981 SELECT DISTINCT c FROM t1 WHERE d=4;
986 --echo # Bug #45386: Wrong query result with MIN function in field list,
987 --echo # WHERE and GROUP BY clause
990 CREATE TABLE t (a INT, b INT, INDEX (a,b));
991 INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
992 INSERT INTO t SELECT * FROM t;
995 --echo #should use range with index for group by
997 SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
998 --echo #should return 1 row
999 SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
1002 --echo #should use range with index for group by
1004 SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
1005 --echo #should return 1 row
1006 SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
1008 --echo # test 3 ranges and use the middle one
1009 INSERT INTO t SELECT a, 2 FROM t;
1011 --echo #should use range with index for group by
1013 SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
1014 --echo #should return 1 row
1015 SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
1020 --echo # Bug #48472: Loose index scan inappropriately chosen for some WHERE
1024 CREATE TABLE t (a INT, b INT, INDEX (a,b));
1025 INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
1026 INSERT INTO t SELECT * FROM t;
1028 SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a;
1032 --echo End of 5.0 tests
1035 --echo # Bug #46607: Assertion failed: (cond_type == Item::FUNC_ITEM) results in
1036 --echo # server crash
1039 CREATE TABLE t (a INT, b INT, INDEX (a,b));
1040 INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
1041 INSERT INTO t SELECT * FROM t;
1043 SELECT a, MAX(b) FROM t WHERE b GROUP BY a;
1048 # BUG#49902 - SELECT returns incorrect results
1050 CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, KEY (b));
1051 INSERT INTO t1 VALUES(1,1),(2,1);
1053 SELECT 1 AS c, b FROM t1 WHERE b IN (1,2) GROUP BY c, b;
1054 SELECT a FROM t1 WHERE b=1;
1058 --echo # Bug#47762: Incorrect result from MIN() when WHERE tests NOT NULL column
1062 --echo ## Test for NULLs allowed
1063 CREATE TABLE t1 ( a INT, KEY (a) );
1064 INSERT INTO t1 VALUES (1), (2), (3);
1065 --source include/min_null_cond.inc
1066 INSERT INTO t1 VALUES (NULL), (NULL);
1067 --source include/min_null_cond.inc
1070 --echo ## Test for NOT NULLs
1071 CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY);
1072 INSERT INTO t1 VALUES (1), (2), (3);
1074 --echo # NULL-safe operator test disabled for non-NULL indexed columns.
1078 --echo # - Bug#52173: Reading NULL value from non-NULL index gives
1079 --echo # wrong result in embedded server
1081 --echo # - Bug#52174: Sometimes wrong plan when reading a MAX value from
1082 --echo # non-NULL index
1084 --let $skip_null_safe_test= 1
1085 --source include/min_null_cond.inc
1089 --echo # Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&, Item*) at
1090 --echo # opt_sum.cc:305
1092 CREATE TABLE t1 ( a INT, KEY (a) );
1093 INSERT INTO t1 VALUES (1), (2), (3);
1095 SELECT MIN( a ) AS min_a
1097 WHERE a > 1 AND a IS NULL
1103 --echo End of 5.1 tests