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--source include/default_optimizer_switch.inc
7
8#
9# TODO:
10# Add queries with:
11# - C != const
12# - C IS NOT NULL
13# - HAVING clause
14
15--disable_warnings
16drop table if exists t1;
17--enable_warnings
18
19create table t1 (
20  a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' '
21);
22
23insert into t1 (a1, a2, b, c, d) values
24('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
25('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
26('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
27('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
28('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
29('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
30('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
31('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
32('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
33('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
34('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
35('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
36('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
37('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
38('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
39('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
40('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
41('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
42('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
43('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
44('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
45('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
46('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
47('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
48('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
49('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
50('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
51('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
52('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
53('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
54('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
55('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
56
57create index idx_t1_0 on t1 (a1);
58create index idx_t1_1 on t1 (a1,a2,b,c);
59create index idx_t1_2 on t1 (a1,a2,b);
60analyze table t1;
61
62# t2 is the same as t1, but with some NULLs in the MIN/MAX column, and
63# one more nullable attribute
64
65--disable_warnings
66drop table if exists t2;
67--enable_warnings
68
69create table t2 (
70  a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(248) default ' '
71);
72insert into t2 select * from t1;
73# add few rows with NULL's in the MIN/MAX column
74insert into t2 (a1, a2, b, c, d) values
75('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
76('a','a','a',NULL,'xyz'),
77('a','a','b',NULL,'xyz'),
78('a','b','a',NULL,'xyz'),
79('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
80('d','b','b',NULL,'xyz'),
81('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
82('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),
83('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
84('a','a','a',NULL,'xyz'),
85('a','a','b',NULL,'xyz'),
86('a','b','a',NULL,'xyz'),
87('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
88('d','b','b',NULL,'xyz'),
89('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
90('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz');
91
92create index idx_t2_0 on t2 (a1);
93create index idx_t2_1 on t2 (a1,a2,b,c);
94create index idx_t2_2 on t2 (a1,a2,b);
95analyze table t2;
96
97# Table t3 is the same as t1, but with smaller column lenghts.
98# This allows to test different branches of the cost computation procedure
99# when the number of keys per block are less than the number of keys in the
100# sub-groups formed by predicates over non-group attributes.
101
102--disable_warnings
103drop table if exists t3;
104--enable_warnings
105
106create table t3 (
107  a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' '
108);
109
110insert into t3 (a1, a2, b, c, d) values
111('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
112('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
113('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
114('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
115('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
116('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
117('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
118('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
119('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
120('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
121('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
122('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
123insert into t3 (a1, a2, b, c, d) values
124('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
125('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
126('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
127('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
128('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
129('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
130('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
131('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
132('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
133('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
134('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
135('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
136insert into t3 (a1, a2, b, c, d) values
137('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
138('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
139('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
140('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
141('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
142('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
143('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
144('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
145('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
146('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
147('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
148('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
149insert into t3 (a1, a2, b, c, d) values
150('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
151('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
152('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
153('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
154('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
155('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
156('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
157('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
158('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
159('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
160('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
161('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
162
163create index idx_t3_0 on t3 (a1);
164create index idx_t3_1 on t3 (a1,a2,b,c);
165create index idx_t3_2 on t3 (a1,a2,b);
166analyze table t3;
167
168
169#
170# Queries without a WHERE clause. These queries do not use ranges.
171#
172
173# plans
174explain select a1, min(a2) from t1 group by a1;
175explain select a1, max(a2) from t1 group by a1;
176explain select a1, min(a2), max(a2) from t1 group by a1;
177explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
178explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
179--replace_column 7 # 9 #
180explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
181# Select fields in different order
182explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
183explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
184explain select min(a2) from t1 group by a1;
185explain select a2, min(c), max(c) from t1 group by a1,a2,b;
186
187# queries
188select a1, min(a2) from t1 group by a1;
189select a1, max(a2) from t1 group by a1;
190select a1, min(a2), max(a2) from t1 group by a1;
191select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
192select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
193select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
194# Select fields in different order
195select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
196select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
197select min(a2) from t1 group by a1;
198select a2, min(c), max(c) from t1 group by a1,a2,b;
199
200#
201# Queries with a where clause
202#
203
204# A) Preds only over the group 'A' attributes
205# plans
206explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
207explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
208explain select a1,a2,b,       max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
209explain select a1, max(c)            from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
210explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
211explain select a1,a2,b,       max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
212explain select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
213explain 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;
214explain 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;
215explain select a1,min(c),max(c)      from t1 where a1 >= 'b' group by a1,a2,b;
216explain select a1,  max(c)           from t1 where a1 in ('a','b','d') group by a1,a2,b;
217
218--replace_column 9 #
219explain select a1,a2,b,       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 < 'd' group by a1,a2,b;
222--replace_column 9 #
223explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
224--replace_column 9 #
225explain select a1,a2,b,       max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
226--replace_column 9 #
227explain select a1, max(c)            from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
228--replace_column 9 #
229explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
230--replace_column 9 #
231explain select a1,a2,b,       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,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
234--replace_column 9 #
235explain 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;
236--replace_column 9 #
237explain 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;
238--replace_column 9 #
239explain select a1,min(c),max(c)      from t2 where a1 >= 'b' group by a1,a2,b;
240--replace_column 9 #
241explain select a1,  max(c)           from t2 where a1 in ('a','b','d') group by a1,a2,b;
242
243# queries
244select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
245select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
246select a1,a2,b,       max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
247select a1, max(c)            from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
248select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
249select a1,a2,b,       max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
250select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
251select 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;
252select 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;
253select a1,min(c),max(c)      from t1 where a1 >= 'b' group by a1,a2,b;
254select a1,  max(c)           from t1 where a1 in ('a','b','d') group by a1,a2,b;
255
256select a1,a2,b,       max(c) from t2 where a1 < 'd' group by a1,a2,b;
257select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
258select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
259select a1,a2,b,       max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
260select a1, max(c)            from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
261select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
262select a1,a2,b,       max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
263select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
264select 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;
265select 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;
266select a1,min(c),max(c)      from t2 where a1 >= 'b' group by a1,a2,b;
267select a1,  max(c)           from t2 where a1 in ('a','b','d') group by a1,a2,b;
268
269# B) Equalities only over the non-group 'B' attributes
270# plans
271explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
272explain select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b') group by a1;
273explain select a1,a2,b,       max(c) from t1 where (b = 'b') group by a1,a2;
274explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
275explain select a1,a2, max(c)         from t1 where (b = 'b') group by a1,a2;
276
277explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
278explain select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b') group by a1;
279explain select a1,a2,b,       max(c) from t2 where (b = 'b') group by a1,a2;
280explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
281explain select a1,a2, max(c)         from t2 where (b = 'b') group by a1,a2;
282
283# these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost()
284explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
285explain select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b') group by a1;
286
287# queries
288select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
289select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b') group by a1;
290select a1,a2,b,       max(c) from t1 where (b = 'b') group by a1,a2;
291select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
292select a1,a2, max(c)         from t1 where (b = 'b') group by a1,a2;
293
294select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
295select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b') group by a1;
296select a1,a2,b,       max(c) from t2 where (b = 'b') group by a1,a2;
297select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
298select a1,a2, max(c)         from t2 where (b = 'b') group by a1,a2;
299
300# these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost()
301select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
302select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b') group by a1;
303
304
305# IS NULL (makes sense for t2 only)
306# plans
307explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
308explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
309explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
310explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
311explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
312explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
313# queries
314select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
315select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
316select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
317select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
318select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
319select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
320
321# C) Range predicates for the MIN/MAX attribute
322# plans
323--replace_column 9 #
324explain select a1,a2,b,       max(c) from t1 where (c > 'b1') group by a1,a2,b;
325explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
326explain select a1,a2,b,       max(c) from t1 where (c > 'f123') group by a1,a2,b;
327explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
328explain select a1,a2,b,       max(c) from t1 where (c < 'a0') group by a1,a2,b;
329explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
330explain select a1,a2,b,       max(c) from t1 where (c < 'k321') group by a1,a2,b;
331explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
332explain select a1,a2,b,       max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
333explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
334explain select a1,a2,b,       max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
335explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
336explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
337explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
338explain 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;
339explain 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
341--replace_column 9 #
342explain select a1,a2,b,       max(c) from t2 where (c > 'b1') group by a1,a2,b;
343--replace_column 9 #
344explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
345--replace_column 9 #
346explain select a1,a2,b,       max(c) from t2 where (c > 'f123') group by a1,a2,b;
347--replace_column 9 #
348explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
349--replace_column 9 #
350explain select a1,a2,b,       max(c) from t2 where (c < 'a0') group by a1,a2,b;
351--replace_column 9 #
352explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
353--replace_column 9 #
354explain select a1,a2,b,       max(c) from t2 where (c < 'k321') group by a1,a2,b;
355--replace_column 9 #
356explain select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
357--replace_column 9 #
358explain select a1,a2,b,       max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
359--replace_column 9 #
360explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
361--replace_column 9 #
362explain select a1,a2,b,       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 > 'b1') or (c <= 'g1') group by a1,a2,b;
365--replace_column 9 #
366explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
367--replace_column 9 #
368explain select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
369--replace_column 9 #
370explain 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
372# queries
373select a1,a2,b,       max(c) from t1 where (c > 'b1') group by a1,a2,b;
374select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
375select a1,a2,b,       max(c) from t1 where (c > 'f123') group by a1,a2,b;
376select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
377select a1,a2,b,       max(c) from t1 where (c < 'a0') group by a1,a2,b;
378select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
379select a1,a2,b,       max(c) from t1 where (c < 'k321') group by a1,a2,b;
380select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
381select a1,a2,b,       max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
382select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
383select a1,a2,b,       max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
384select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
385select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
386select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
387select 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;
388select 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;
389
390select a1,a2,b,       max(c) from t2 where (c > 'b1') group by a1,a2,b;
391select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
392select a1,a2,b,       max(c) from t2 where (c > 'f123') group by a1,a2,b;
393select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
394select a1,a2,b,       max(c) from t2 where (c < 'a0') group by a1,a2,b;
395select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
396select a1,a2,b,       max(c) from t2 where (c < 'k321') group by a1,a2,b;
397select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
398select a1,a2,b,       max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
399select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
400select a1,a2,b,       max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
401select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
402select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
403select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
404select 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;
405
406# analyze the sub-select
407explain select a1,a2,b,min(c),max(c) from t1
408where exists ( select * from t2 where t2.c = t1.c )
409group by a1,a2,b;
410
411select a1,a2,b,min(c),max(c) from t1
412where exists ( select * from t2 where t2.c = t1.c )
413group by a1,a2,b;
414
415# the sub-select is unrelated to MIN/MAX
416explain select a1,a2,b,min(c),max(c) from t1
417where exists ( select * from t2 where t2.c > 'b1' )
418group by a1,a2,b;
419
420select a1,a2,b,min(c),max(c) from t1
421where exists ( select * from t2 where t2.c > 'b1' )
422group by a1,a2,b;
423
424# correlated subselect that doesn't reference the min/max argument
425explain select a1,a2,b,c,min(c), max(c) from t1
426where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' )
427group by a1,a2,b;
428
429select a1,a2,b,c,min(c), max(c) from t1
430where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' )
431group by a1,a2,b;
432
433SET @save_optimizer_switch=@@optimizer_switch;
434SET optimizer_switch='semijoin_with_cache=off';
435explain select a1,a2,b,c,min(c), max(c) from t1
436where exists ( select * from t2
437               where t2.c in (select c from t3 where t3.c > t1.b) and
438               t2.c > 'b1' )
439group by a1,a2,b;
440
441select a1,a2,b,c,min(c), max(c) from t1
442where exists ( select * from t2
443               where t2.c in (select c from t3 where t3.c > t1.b) and
444               t2.c > 'b1' )
445group by a1,a2,b;
446SET optimizer_switch=@save_optimizer_switch;
447
448# correlated subselect that references the min/max argument
449explain select a1,a2,b,c,min(c), max(c) from t1
450where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' )
451group by a1,a2,b;
452
453select a1,a2,b,c,min(c), max(c) from t1
454where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' )
455group by a1,a2,b;
456
457SET @save_optimizer_switch=@@optimizer_switch;
458SET optimizer_switch='semijoin_with_cache=off';
459explain select a1,a2,b,c,min(c), max(c) from t1
460where exists ( select * from t2
461               where t2.c in (select c from t3 where t3.c > t1.c) and
462               t2.c > 'b1' )
463group by a1,a2,b;
464
465select a1,a2,b,c,min(c), max(c) from t1
466where exists ( select * from t2
467               where t2.c in (select c from t3 where t3.c > t1.c) and
468               t2.c > 'b1' )
469group by a1,a2,b;
470SET optimizer_switch=@save_optimizer_switch;
471
472
473# A,B,C) Predicates referencing mixed classes of attributes
474# plans
475explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
476explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
477explain select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
478explain 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;
479explain 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;
480explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
481explain 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;
482
483--replace_column 9 #
484explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
485--replace_column 9 #
486explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
487--replace_column 9 #
488explain select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
489--replace_column 9 #
490explain 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;
491--replace_column 9 #
492explain 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;
493--replace_column 9 #
494explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
495
496# queries
497select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
498select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
499select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
500select 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;
501select 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;
502select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
503select 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;
504
505select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
506select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
507select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
508select 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;
509select 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;
510select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
511
512
513#
514# GROUP BY queries without MIN/MAX
515#
516
517# plans
518explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
519explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
520explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
521explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
522
523--replace_column 9 #
524explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
525--replace_column 9 #
526explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
527--replace_column 9 #
528explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
529--replace_column 9 #
530explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
531
532# queries
533select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
534select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
535select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
536select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
537
538select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
539select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
540select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
541select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
542
543#
544# DISTINCT queries
545#
546
547# plans
548explain select distinct a1,a2,b from t1;
549explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
550explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
551explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
552explain select distinct b from t1 where (a2 >= 'b') and (b = 'a');
553
554--replace_column 9 #
555explain select distinct a1,a2,b from t2;
556--replace_column 9 #
557explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
558explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
559--replace_column 9 #
560explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
561explain select distinct b from t2 where (a2 >= 'b') and (b = 'a');
562
563# queries
564select distinct a1,a2,b from t1;
565select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
566select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
567select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
568select distinct b from t1 where (a2 >= 'b') and (b = 'a');
569
570select distinct a1,a2,b from t2;
571select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
572select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
573select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
574select distinct b from t2 where (a2 >= 'b') and (b = 'a');
575
576# BUG #6303
577select distinct t_00.a1
578from t1 t_00
579where exists ( select * from t2 where a1 = t_00.a1 );
580
581# BUG #8532 - SELECT DISTINCT a, a causes server to crash
582select distinct a1,a1 from t1;
583select distinct a2,a1,a2,a1 from t1;
584select distinct t1.a1,t2.a1 from t1,t2;
585
586
587#
588# DISTINCT queries with GROUP-BY
589#
590
591# plans
592explain select distinct a1,a2,b from t1;
593explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
594explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
595explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
596explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
597
598--replace_column 9 #
599explain select distinct a1,a2,b from t2;
600--replace_column 9 #
601explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
602--replace_column 9 #
603explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
604--replace_column 9 #
605explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
606--replace_column 9 #
607explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
608
609# queries
610select distinct a1,a2,b from t1;
611select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
612select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
613select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
614select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
615
616select distinct a1,a2,b from t2;
617select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
618select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
619select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
620select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
621
622
623#
624# COUNT (DISTINCT cols) queries
625#
626
627explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
628explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
629explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
630explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
631explain extended select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
632
633select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
634select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
635select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
636select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
637select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
638
639#
640# Queries with expressions in the select clause
641#
642
643explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
644explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
645explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
646explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
647explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
648
649select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
650select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
651select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
652select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
653select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
654
655
656#
657# Negative examples: queries that should NOT be treated as optimizable by
658# QUICK_GROUP_MIN_MAX_SELECT
659#
660
661# select a non-indexed attribute
662explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b;
663
664explain select a1,a2,b,d from t1 group by a1,a2,b;
665
666# predicate that references an attribute that is after the MIN/MAX argument
667# in the index
668explain extended select a1,a2,min(b),max(b) from t1
669where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;
670
671# predicate that references a non-indexed attribute
672explain extended select a1,a2,b,min(c),max(c) from t1
673where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
674
675explain extended select a1,a2,b,c from t1
676where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
677
678# non-equality predicate for a non-group select attribute
679explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
680explain 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;
681
682# non-group field with an equality predicate that references a keypart after the
683# MIN/MAX argument
684explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
685select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
686
687# disjunction for a non-group select attribute
688explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1;
689
690# non-range predicate for the MIN/MAX attribute
691explain select a1,a2,b,min(c),max(c) from t2
692where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b;
693
694# not all attributes are indexed by one index
695explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c;
696
697# other aggregate functions than MIN/MAX
698explain select a1,a2,count(a2) from t1 group by a1,a2,b;
699explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
700explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
701
702#
703# MDEV-4120 UNIQUE indexes should not be considered for loose index scan
704#
705
706create table t4 as select distinct a1, a2, b, c from t1;
707alter table t4 add unique index idxt4 (a1, a2, b, c);
708
709--echo # This is "superceded" by MDEV-7118, and Loose Index Scan is again an option:
710explain
711select a1, a2, b, min(c) from t4 group by a1, a2, b;
712select a1, a2, b, min(c) from t4 group by a1, a2, b;
713
714drop table t4;
715
716#
717# Bug #16710: select distinct doesn't return all it should
718#
719
720explain select distinct(a1) from t1 where ord(a2) = 98;
721select distinct(a1) from t1 where ord(a2) = 98;
722
723#
724# BUG#11044: DISTINCT or GROUP BY queries with equality predicates instead of MIN/MAX.
725#
726
727explain select a1 from t1 where a2 = 'b' group by a1;
728select a1 from t1 where a2 = 'b' group by a1;
729
730explain select distinct a1 from t1 where a2 = 'b';
731select distinct a1 from t1 where a2 = 'b';
732
733#
734# Bug #12672: primary key implcitly included in every innodb index
735#
736# Test case moved to group_min_max_innodb
737
738
739#
740# Bug #6142: a problem with the empty innodb table
741#
742# Test case moved to group_min_max_innodb
743
744
745#
746# Bug #9798: group by with rollup
747#
748# Test case moved to group_min_max_innodb
749
750
751#
752# Bug #13293 Wrongly used index results in endless loop.
753#
754# Test case moved to group_min_max_innodb
755
756
757drop table t1,t2,t3;
758
759#
760# Bug #14920 Ordering aggregated result sets with composite primary keys
761# corrupts resultset
762#
763create table t1 (c1 int not null,c2 int not null, primary key(c1,c2));
764insert into t1 (c1,c2) values
765(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9);
766select distinct c1, c2 from t1 order by c2;
767select c1,min(c2) as c2 from t1 group by c1 order by c2;
768select c1,c2 from t1 group by c1,c2 order by c2;
769drop table t1;
770
771#
772# Bug #16203: Analysis for possible min/max optimization erroneously
773#             returns impossible range
774#
775
776CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b));
777INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4);
778OPTIMIZE TABLE t1;
779
780SELECT a FROM t1 WHERE a='AA' GROUP BY a;
781SELECT a FROM t1 WHERE a='BB' GROUP BY a;
782
783EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a;
784EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a;
785
786SELECT DISTINCT a FROM t1 WHERE a='BB';
787SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%';
788SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a;
789
790DROP TABLE t1;
791
792
793#
794# Bug #15102: select distinct returns empty result, select count
795#             distinct > 0 (correct)
796#
797
798CREATE TABLE t1 (
799   a int(11) NOT NULL DEFAULT '0',
800   b varchar(16) COLLATE latin1_general_ci NOT NULL DEFAULT '',
801   PRIMARY KEY  (a,b)
802 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
803
804delimiter |;
805
806CREATE PROCEDURE a(x INT)
807BEGIN
808  DECLARE rnd INT;
809  DECLARE cnt INT;
810
811  WHILE x > 0 DO
812    SET rnd= x % 100;
813    SET cnt = (SELECT COUNT(*) FROM t1 WHERE a = rnd);
814    INSERT INTO t1(a,b) VALUES (rnd, CAST(cnt AS CHAR));
815    SET x= x - 1;
816  END WHILE;
817END|
818
819DELIMITER ;|
820
821CALL a(1000);
822
823SELECT a FROM t1 WHERE a=0;
824SELECT DISTINCT a FROM t1 WHERE a=0;
825SELECT COUNT(DISTINCT a) FROM t1 WHERE a=0;
826
827DROP TABLE t1;
828DROP PROCEDURE a;
829
830#
831# Bug #18068: SELECT DISTINCT
832#
833
834CREATE TABLE t1 (a varchar(64) NOT NULL default '', KEY(a));
835
836INSERT INTO t1 (a) VALUES
837  (''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'),
838  ('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'),
839  ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN');
840INSERT INTO t1 SELECT * FROM t1;
841INSERT INTO t1 SELECT * FROM t1;
842INSERT INTO t1 SELECT * FROM t1;
843ANALYZE TABLE t1;
844
845EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a;
846SELECT DISTINCT a,a FROM t1 ORDER BY a;
847
848DROP TABLE t1;
849
850#
851# Bug #21007: NATURAL JOIN (any JOIN (2 x NATURAL JOIN)) crashes the server
852#
853
854CREATE TABLE t1 (id1 INT, id2 INT);
855CREATE TABLE t2 (id2 INT, id3 INT, id5 INT);
856CREATE TABLE t3 (id3 INT, id4 INT);
857CREATE TABLE t4 (id4 INT);
858CREATE TABLE t5 (id5 INT, id6 INT);
859CREATE TABLE t6 (id6 INT);
860
861INSERT INTO t1 VALUES(1,1);
862INSERT INTO t2 VALUES(1,1,1);
863INSERT INTO t3 VALUES(1,1);
864INSERT INTO t4 VALUES(1);
865INSERT INTO t5 VALUES(1,1);
866INSERT INTO t6 VALUES(1);
867
868# original bug query
869SELECT * FROM
870t1
871  NATURAL JOIN
872(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
873    ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
874
875# inner join swapped
876SELECT * FROM
877t1
878  NATURAL JOIN
879(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2
880    ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
881
882# one join less, no ON cond
883SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2);
884
885# wrong error message: 'id2' - ambiguous column
886SELECT * FROM
887(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
888    ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5))
889  NATURAL JOIN
890t1;
891SELECT * FROM
892(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6)))
893  NATURAL JOIN
894t1;
895
896DROP TABLE t1,t2,t3,t4,t5,t6;
897
898#
899# Bug#22342: No results returned for query using max and group by
900#
901CREATE TABLE t1 (a int, b int, KEY (a,b), KEY b (b));
902INSERT INTO t1 VALUES
903  (1,1),(1,2),(1,0),(1,3),
904  (1,-1),(1,-2),(1,-3),(1,-4);
905ANALYZE TABLE t1;
906
907explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
908SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
909SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
910CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
911INSERT INTO t2 SELECT a,b,b FROM t1;
912explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
913SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
914
915DROP TABLE t1,t2;
916
917#
918# Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar statements
919#
920
921CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
922INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
923       (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
924ANALYZE TABLE t1;
925EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
926FLUSH STATUS;
927SELECT max(b), a FROM t1 GROUP BY a;
928SHOW STATUS LIKE 'handler_read__e%';
929EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
930FLUSH STATUS;
931CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a;
932SHOW STATUS LIKE 'handler_read__e%';
933FLUSH STATUS;
934SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
935SHOW STATUS LIKE 'handler_read__e%';
936FLUSH STATUS;
937(SELECT max(b), a FROM t1 GROUP BY a) UNION
938 (SELECT max(b), a FROM t1 GROUP BY a);
939SHOW STATUS LIKE 'handler_read__e%';
940EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION
941 (SELECT max(b), a FROM t1 GROUP BY a);
942
943EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
944  FROM t1 AS t1_outer;
945EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS
946  (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
947EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
948  (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
949EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
950  a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
951EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
952  a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
953EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2
954   ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2)
955   AND t1_outer1.b = t1_outer2.b;
956EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
957  FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
958
959CREATE TABLE t3 LIKE t1;
960FLUSH STATUS;
961INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;
962SHOW STATUS LIKE 'handler_read__e%';
963DELETE FROM t3;
964FLUSH STATUS;
965INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2)
966  FROM t1 LIMIT 1;
967SHOW STATUS LIKE 'handler_read__e%';
968FLUSH STATUS;
969DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
970SHOW STATUS LIKE 'handler_read__e%';
971FLUSH STATUS;
972--error ER_SUBQUERY_NO_1_ROW
973DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x
974                      FROM t1) > 10000;
975SHOW STATUS LIKE 'handler_read__e%';
976
977DROP TABLE t1,t2,t3;
978
979#
980# Bug#25602: queries with DISTINCT and SQL_BIG_RESULT hint
981#            for which loose scan optimization is applied
982#
983
984CREATE TABLE t1 (a int, INDEX idx(a));
985INSERT INTO t1 VALUES
986  (4), (2), (1), (2), (4), (2), (1), (4),
987  (4), (2), (1), (2), (2), (4), (1), (4);
988ANALYZE TABLE t1;
989
990EXPLAIN SELECT DISTINCT(a) FROM t1;
991SELECT DISTINCT(a) FROM t1;
992EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
993SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
994
995DROP TABLE t1;
996
997#
998# Bug #32268: Indexed queries give bogus MIN and MAX results
999#
1000
1001CREATE TABLE t1 (a INT, b INT);
1002INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5);
1003INSERT INTO t1 SELECT a + 1, b FROM t1;
1004INSERT INTO t1 SELECT a + 2, b FROM t1;
1005ANALYZE TABLE t1;
1006
1007EXPLAIN
1008SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
1009SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
1010
1011CREATE INDEX break_it ON t1 (a, b);
1012
1013EXPLAIN
1014SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
1015SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
1016
1017EXPLAIN
1018SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
1019SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
1020
1021EXPLAIN
1022SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
1023SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
1024
1025DROP TABLE t1;
1026
1027#
1028# Bug#38195: Incorrect handling of aggregate functions when loose index scan is
1029#            used causes server crash.
1030#
1031create table t1 (a int, b int, key (a,b), key `index` (a,b)) engine=MyISAM;
1032insert into  t1 (a,b) values
1033(0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6),
1034  (0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13),
1035(1,0),(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),
1036  (1,7),(1,8),(1,9),(1,10),(1,11),(1,12),(1,13),
1037(2,0),(2,1),(2,2),(2,3),(2,4),(2,5),(2,6),
1038  (2,7),(2,8),(2,9),(2,10),(2,11),(2,12),(2,13),
1039(3,0),(3,1),(3,2),(3,3),(3,4),(3,5),(3,6),
1040  (3,7),(3,8),(3,9),(3,10),(3,11),(3,12),(3,13);
1041insert into t1 (a,b) select a, max(b)+1 from t1 where a = 0 group by a;
1042select * from t1;
1043explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a;
1044drop table t1;
1045
1046
1047#
1048# Bug #41610: key_infix_len can be overwritten causing some group by queries
1049# to return no rows
1050#
1051
1052CREATE TABLE t1 (a int, b int, c int, d int,
1053  KEY foo (c,d,a,b), KEY bar (c,a,b,d));
1054
1055INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4);
1056INSERT INTO t1 SELECT * FROM t1;
1057INSERT INTO t1 SELECT * FROM t1;
1058INSERT INTO t1 SELECT a,b,c+1,d FROM t1;
1059ANALYZE TABLE t1;
1060
1061#Should be non-empty
1062EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4;
1063SELECT DISTINCT c FROM t1 WHERE d=4;
1064
1065DROP TABLE t1;
1066
1067--echo #
1068--echo # Bug #45386: Wrong query result with MIN function in field list,
1069--echo #  WHERE and GROUP BY clause
1070--echo #
1071
1072CREATE TABLE t (a INT, b INT, INDEX (a,b));
1073INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
1074INSERT INTO t SELECT * FROM t;
1075INSERT INTO t SELECT * FROM t;
1076ANALYZE TABLE t;
1077
1078--echo # test MIN
1079--echo #should use range with index for group by
1080EXPLAIN
1081SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
1082--echo #should return 1 row
1083SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
1084
1085--echo # test MAX
1086--echo #should use range with index for group by
1087EXPLAIN
1088SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
1089--echo #should return 1 row
1090SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
1091
1092--echo # test 3 ranges and use the middle one
1093INSERT INTO t SELECT a, 2 FROM t;
1094
1095--echo #should use range with index for group by
1096EXPLAIN
1097SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
1098--echo #should return 1 row
1099SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
1100
1101DROP TABLE t;
1102
1103--echo #
1104--echo # Bug #48472: Loose index scan inappropriately chosen for some WHERE
1105--echo #             conditions
1106--echo #
1107
1108CREATE TABLE t (a INT, b INT, INDEX (a,b));
1109INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
1110INSERT INTO t SELECT * FROM t;
1111
1112SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a;
1113
1114DROP TABLE t;
1115
1116--echo End of 5.0 tests
1117
1118--echo #
1119--echo # Bug #46607: Assertion failed: (cond_type == Item::FUNC_ITEM) results in
1120--echo #              server crash
1121--echo #
1122
1123CREATE TABLE t (a INT, b INT, INDEX (a,b));
1124INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
1125INSERT INTO t SELECT * FROM t;
1126
1127SELECT a, MAX(b) FROM t WHERE b GROUP BY a;
1128
1129DROP TABLE t;
1130
1131#
1132# BUG#49902 - SELECT returns incorrect results
1133#
1134CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, KEY (b));
1135INSERT INTO t1 VALUES(1,1),(2,1);
1136ANALYZE TABLE t1;
1137SELECT 1 AS c, b FROM t1 WHERE b IN (1,2) GROUP BY c, b;
1138SELECT a FROM t1 WHERE b=1;
1139DROP TABLE t1;
1140
1141--echo #
1142--echo # Bug#47762: Incorrect result from MIN() when WHERE tests NOT NULL column
1143--echo #            for NULL
1144--echo #
1145
1146--echo ## Test for NULLs allowed
1147CREATE TABLE t1 ( a INT, KEY (a) );
1148INSERT INTO t1 VALUES (1), (2), (3);
1149--source include/min_null_cond.inc
1150INSERT INTO t1 VALUES (NULL), (NULL);
1151--source include/min_null_cond.inc
1152DROP TABLE t1;
1153
1154--echo ## Test for NOT NULLs
1155CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY);
1156INSERT INTO t1 VALUES (1), (2), (3);
1157--echo #
1158--echo # NULL-safe operator test disabled for non-NULL indexed columns.
1159--echo #
1160--echo # See bugs
1161--echo #
1162--echo # - Bug#52173: Reading NULL value from non-NULL index gives
1163--echo #   wrong result in embedded server
1164--echo #
1165--echo # - Bug#52174: Sometimes wrong plan when reading a MAX value from
1166--echo #   non-NULL index
1167--echo #
1168--let $skip_null_safe_test= 1
1169--source include/min_null_cond.inc
1170DROP TABLE t1;
1171
1172--echo #
1173--echo # Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&, Item*) at
1174--echo # opt_sum.cc:305
1175--echo #
1176CREATE TABLE t1 ( a INT, KEY (a) );
1177INSERT INTO t1 VALUES (1), (2), (3);
1178
1179SELECT MIN( a ) AS min_a
1180FROM t1
1181WHERE a > 1 AND a IS NULL
1182ORDER BY min_a;
1183
1184DROP TABLE t1;
1185
1186#
1187# MDEV-729 lp:998028 - Server crashes on normal shutdown in closefrm after executing a query from MyISAM table
1188#
1189create table t1 (a int, b varchar(1), key(b,a)) engine=myisam;
1190insert t1 values (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'g'),(8,'h'),(null,'i');
1191select min(a), b from t1 where a=7 or b='z' group by b;
1192flush tables;
1193drop table t1;
1194
1195--echo #
1196--echo # LP BUG#888456 Wrong result with DISTINCT , ANY , subquery_cache=off , NOT NULL
1197--echo #
1198
1199CREATE TABLE t1 ( a int NOT NULL) ;
1200INSERT INTO t1 VALUES (28),(29),(9);
1201
1202CREATE TABLE t2 ( a int, KEY (a)) ;
1203INSERT INTO t2 VALUES (1),(1),(1),(4),(4),(5),(5),(8),(8),(9);
1204
1205explain select (select t2.a from t2 where t2.a >= t1.a group by t2.a) from t1;
1206select (select t2.a from t2 where t2.a >= t1.a group by t2.a) from t1;
1207
1208drop table t1, t2;
1209
1210--echo #
1211--echo # LP BUG#900375 Wrong result with derived_merge=ON, DISTINCT or GROUP BY, EXISTS
1212--echo #
1213
1214CREATE TABLE t1 ( a INT, b INT, KEY (b) );
1215INSERT INTO t1 VALUES
1216(100,10),(101,11),(102,12),(103,13),(104,14),
1217(105,15),(106,16),(107,17),(108,18),(109,19);
1218
1219EXPLAIN
1220SELECT alias1.* FROM t1, (SELECT * FROM t1) AS alias1
1221WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
1222SELECT alias1.* FROM t1, (SELECT * FROM t1) AS alias1
1223WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
1224
1225EXPLAIN
1226SELECT alias1.* FROM t1, t1 AS alias1
1227WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
1228SELECT alias1.* FROM t1, t1 AS alias1
1229WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
1230
1231drop table t1;
1232
1233--echo End of 5.1 tests
1234
1235--echo #
1236--echo # MDEV-765 lp:825075 - Wrong result with GROUP BY + multipart key + MIN/MAX loose scan
1237--echo #
1238
1239CREATE TABLE t1 (a varchar(1), b varchar(1), KEY (b,a));
1240INSERT INTO t1 VALUES
1241('0',NULL),('9',NULL),('8','c'),('4','d'),('7','d'),(NULL,'f'),
1242('7','f'),('8','g'),(NULL,'j');
1243
1244explain
1245SELECT max(a) , b FROM t1 WHERE a IS NULL OR b = 'z' GROUP BY b;
1246SELECT max(a) , b FROM t1 WHERE a IS NULL OR b = 'z' GROUP BY b;
1247
1248explain
1249SELECT b, min(a) FROM t1 WHERE a = '7' OR b = 'z' GROUP BY b;
1250SELECT b, min(a) FROM t1 WHERE a = '7' OR b = 'z' GROUP BY b;
1251
1252explain
1253SELECT b, min(a) FROM t1 WHERE (a = b OR b = 'd' OR b is NULL) GROUP BY b;
1254SELECT b, min(a) FROM t1 WHERE (a = b OR b = 'd' OR b is NULL) GROUP BY b;
1255
1256explain
1257SELECT b, min(a) FROM t1 WHERE a > ('0' = b) AND b = 'z' GROUP BY b;
1258SELECT b, min(a) FROM t1 WHERE a > ('0' = b) AND b = 'z' GROUP BY b;
1259
1260explain
1261SELECT b, min(a) FROM t1 WHERE a > '0' AND (b < (a = '7')) GROUP BY b;
1262SELECT b, min(a) FROM t1 WHERE a > '0' AND (b < (a = '7')) GROUP BY b;
1263
1264explain
1265SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b;
1266SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b;
1267
1268drop table t1;
1269
1270--echo #
1271--echo # MDEV-4140 Wrong result with GROUP BY + multipart key + MIN/MAX loose scan and a subquery
1272--echo #
1273
1274CREATE TABLE t1 (a int, b int, KEY (b, a)) ;
1275INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
1276INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
1277CREATE TABLE t2 (c int) ;
1278INSERT INTO t2 VALUES (0),(1);
1279ANALYZE TABLE t1,t2;
1280
1281EXPLAIN
1282SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
1283SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
1284EXPLAIN
1285SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
1286SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
1287# this test is for 5.5 to ensure that the subquery is expensive
1288EXPLAIN
1289SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
1290SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
1291
1292drop table t1, t2;
1293
1294--echo End of 5.3 tests
1295
1296--echo #
1297--echo # WL#3220 (Loose index scan for COUNT DISTINCT)
1298--echo #
1299
1300CREATE TABLE t1 (a INT, b INT, c INT, KEY (a,b));
1301INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1), (1,4,1);
1302INSERT INTO t1 SELECT * FROM t1;
1303INSERT INTO t1 SELECT * FROM t1;
1304INSERT INTO t1 SELECT * FROM t1;
1305INSERT INTO t1 SELECT a, b + 4, 1 FROM t1;
1306INSERT INTO t1 SELECT a, b + 8, 1 FROM t1;
1307INSERT INTO t1 SELECT a + 1, b, 1 FROM t1;
1308CREATE TABLE t2 (a INT, b INT, c INT, d INT, e INT, f INT, KEY (a,b,c));
1309INSERT INTO t2 VALUES
1310  (1,1,1,1,1,1), (1,2,1,1,1,1), (1,3,1,1,1,1), (1,4,1,1,1,1);
1311INSERT INTO t2 SELECT * FROM t2;
1312INSERT INTO t2 SELECT * FROM t2;
1313INSERT INTO t2 SELECT * FROM t2;
1314INSERT INTO t2 SELECT * FROM t2;
1315INSERT INTO t2 SELECT a, b + 4, c,d,e,f FROM t2;
1316INSERT INTO t2 SELECT a + 1, b, c,d,e,f FROM t2;
1317ANALYZE TABLE t1,t2;
1318
1319EXPLAIN SELECT COUNT(DISTINCT a) FROM t1;
1320SELECT COUNT(DISTINCT a) FROM t1;
1321
1322EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1;
1323SELECT COUNT(DISTINCT a,b) FROM t1;
1324
1325EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1;
1326SELECT COUNT(DISTINCT b,a) FROM t1;
1327
1328EXPLAIN SELECT COUNT(DISTINCT b) FROM t1;
1329SELECT COUNT(DISTINCT b) FROM t1;
1330
1331EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a;
1332SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a;
1333
1334EXPLAIN SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
1335SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
1336
1337EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;
1338SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;
1339
1340EXPLAIN SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
1341SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
1342
1343EXPLAIN SELECT COUNT(DISTINCT a, b + 0) FROM t1;
1344SELECT COUNT(DISTINCT a, b + 0) FROM t1;
1345
1346EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10;
1347SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10;
1348
1349EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10;
1350SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10;
1351
1352EXPLAIN SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10;
1353SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10;
1354
1355EXPLAIN SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1;
1356SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1;
1357
1358EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
1359SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
1360
1361EXPLAIN SELECT COUNT(DISTINCT a), 12 FROM t1;
1362SELECT COUNT(DISTINCT a), 12 FROM t1;
1363
1364EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2;
1365SELECT COUNT(DISTINCT a, b, c) FROM t2;
1366
1367EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2;
1368SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2;
1369
1370EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2;
1371SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2;
1372
1373EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;
1374SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;
1375
1376EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2;
1377SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2;
1378
1379EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2;
1380SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2;
1381
1382EXPLAIN SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c;
1383SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c;
1384
1385EXPLAIN SELECT COUNT(DISTINCT c, a, b) FROM t2
1386  WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;
1387SELECT COUNT(DISTINCT c, a, b) FROM t2
1388  WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;
1389
1390EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5
1391  GROUP BY b;
1392SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5
1393  GROUP BY b;
1394
1395EXPLAIN SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
1396SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
1397
1398EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
1399SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
1400
1401EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42;
1402SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42;
1403
1404EXPLAIN SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2
1405  WHERE b = 13 AND c = 42 GROUP BY a;
1406SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2
1407  WHERE b = 13 AND c = 42 GROUP BY a;
1408
1409# This query could have been resolved using loose index scan since the second
1410# part of count(..) is defined by a constant predicate
1411EXPLAIN SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42;
1412SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42;
1413
1414EXPLAIN SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a;
1415SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a;
1416
1417EXPLAIN SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c;
1418SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c;
1419
1420EXPLAIN SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a;
1421SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a;
1422
1423DROP TABLE t1,t2;
1424
1425--echo # end of WL#3220 tests
1426
1427--echo #
1428--echo # Bug#50539: Wrong result when loose index scan is used for an aggregate
1429--echo #            function with distinct
1430--echo #
1431CREATE TABLE t1 (
1432  f1 int(11) NOT NULL DEFAULT '0',
1433  f2 char(1) NOT NULL DEFAULT '',
1434  KEY (f1,f2)
1435) ;
1436insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'),
1437(3, 'A'), (3, 'B'), (3, 'C'), (3, 'D');
1438
1439SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
1440explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
1441
1442drop table t1;
1443--echo # End of test#50539.
1444
1445--echo #
1446--echo # Bug#17217128 -  BAD INTERACTION BETWEEN MIN/MAX AND
1447--echo #                 "HAVING SUM(DISTINCT)": WRONG RESULTS.
1448--echo #
1449
1450CREATE TABLE t (a INT, b INT, KEY(a,b));
1451INSERT INTO t VALUES (1,1), (2,2), (3,3), (4,4), (1,0), (3,2), (4,5);
1452ANALYZE TABLE t;
1453
1454SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a;
1455EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a;
1456
1457SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a;
1458EXPLAIN SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a;
1459
1460SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a);
1461EXPLAIN SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a);
1462
1463SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t;
1464EXPLAIN SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t;
1465
1466SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a;
1467EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a;
1468DROP TABLE t;
1469
1470--echo #
1471--echo # MDEV-4219 A simple select query returns random data (upstream bug#68473)
1472--echo #
1473
1474--disable_warnings
1475drop table if exists faulty;
1476--enable_warnings
1477
1478# MySQL's test case
1479
1480CREATE TABLE faulty (
1481a int(11) unsigned NOT NULL AUTO_INCREMENT,
1482b int(11) unsigned NOT NULL,
1483c datetime NOT NULL,
1484PRIMARY KEY (a),
1485UNIQUE KEY b_and_c (b,c)
1486);
1487
1488INSERT INTO faulty (b, c) VALUES
1489(1801, '2013-02-15 09:00:00'),
1490(1802, '2013-02-28 09:00:00'),
1491(1802, '2013-03-01 09:00:00'),
1492(5,    '1990-02-15 09:00:00'),
1493(5,    '2013-02-15 09:00:00'),
1494(5,    '2009-02-15 17:00:00');
1495
1496EXPLAIN
1497SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
1498SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
1499
1500drop table faulty;
1501
1502# MariaDB test case
1503
1504CREATE TABLE t1 (a INT, b INT);
1505INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3);
1506INSERT INTO t1 SELECT * FROM t1;
1507INSERT INTO t1 SELECT a + 1, b FROM t1;
1508INSERT INTO t1 SELECT a + 2, b FROM t1;
1509ANALYZE TABLE t1;
1510
1511CREATE INDEX break_it ON t1 (a, b);
1512
1513EXPLAIN
1514SELECT distinct a, b FROM t1 where a = '3' ORDER BY b;
1515SELECT distinct a, b FROM t1 where a = '3' ORDER BY b;
1516
1517drop table t1;
1518
1519--echo #
1520--echo # Start of 10.0 tests
1521--echo #
1522
1523--echo #
1524--echo # MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases
1525--echo #
1526CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
1527INSERT INTO t1 VALUES (1,'2001-01-01');
1528INSERT INTO t1 VALUES (1,'2001-01-02');
1529INSERT INTO t1 VALUES (1,'2001-01-03');
1530INSERT INTO t1 VALUES (1,' 2001-01-04');
1531INSERT INTO t1 VALUES (2,'2001-01-01');
1532INSERT INTO t1 VALUES (2,'2001-01-02');
1533INSERT INTO t1 VALUES (2,'2001-01-03');
1534INSERT INTO t1 VALUES (2,' 2001-01-04');
1535INSERT INTO t1 VALUES (3,'2001-01-01');
1536INSERT INTO t1 VALUES (3,'2001-01-02');
1537INSERT INTO t1 VALUES (3,'2001-01-03');
1538INSERT INTO t1 VALUES (3,' 2001-01-04');
1539INSERT INTO t1 VALUES (4,'2001-01-01');
1540INSERT INTO t1 VALUES (4,'2001-01-02');
1541INSERT INTO t1 VALUES (4,'2001-01-03');
1542INSERT INTO t1 VALUES (4,' 2001-01-04');
1543SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
1544ALTER TABLE t1 ADD KEY(id,a);
1545SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
1546DROP TABLE t1;
1547
1548--echo #
1549--echo # MDEV-17039: Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4
1550--echo # and use_stat_tables= PREFERABLY
1551--echo #
1552
1553CREATE TABLE t1 (a INT, b INT,c INT DEFAULT 0, INDEX (a,b));
1554INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
1555(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
1556INSERT INTO t1 SELECT * FROM t1;
1557ANALYZE TABLE t1;
1558set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
1559set @save_use_stat_tables= @@use_stat_tables;
1560set @@optimizer_use_condition_selectivity=4;
1561set @@use_stat_tables=PREFERABLY;
1562explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1  GROUP BY a);
1563set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
1564set @@use_stat_tables=@save_use_stat_tables;
1565explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1   GROUP BY a);
1566drop table t1;
1567
1568--echo #
1569--echo # End of 10.0 tests
1570--echo #
1571
1572
1573--echo #
1574--echo # Start of 10.1 tests
1575--echo #
1576
1577--echo #
1578--echo # MDEV-6990 GROUP_MIN_MAX optimization is not applied in some cases when it could
1579--echo #
1580CREATE TABLE t1 (id INT NOT NULL, a DATE, KEY(id,a)) ENGINE=MyISAM;
1581INSERT INTO t1 VALUES (1,'2001-01-01');
1582INSERT INTO t1 VALUES (1,'2001-01-02');
1583INSERT INTO t1 VALUES (1,'2001-01-03');
1584INSERT INTO t1 VALUES (1,'2001-01-04');
1585INSERT INTO t1 VALUES (2,'2001-01-01');
1586INSERT INTO t1 VALUES (2,'2001-01-02');
1587INSERT INTO t1 VALUES (2,'2001-01-03');
1588INSERT INTO t1 VALUES (2,'2001-01-04');
1589INSERT INTO t1 VALUES (3,'2001-01-01');
1590INSERT INTO t1 VALUES (3,'2001-01-02');
1591INSERT INTO t1 VALUES (3,'2001-01-03');
1592INSERT INTO t1 VALUES (3,'2001-01-04');
1593INSERT INTO t1 VALUES (4,'2001-01-01');
1594INSERT INTO t1 VALUES (4,'2001-01-02');
1595INSERT INTO t1 VALUES (4,'2001-01-03');
1596INSERT INTO t1 VALUES (4,'2001-01-04');
1597ANALYZE TABLE t1;
1598EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='2001-01-04' GROUP BY id;
1599EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104.0 GROUP BY id;
1600EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id;
1601SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='2001-01-04' GROUP BY id;
1602SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104.0 GROUP BY id;
1603SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id;
1604DROP TABLE t1;
1605
1606--echo #
1607--echo # MDEV-8229 GROUP_MIN_MAX is erroneously applied for BETWEEN in some cases
1608--echo #
1609SET NAMES latin1;
1610CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
1611INSERT INTO t1 VALUES (1,'2001-01-01');
1612INSERT INTO t1 VALUES (1,'2001-01-02');
1613INSERT INTO t1 VALUES (1,'2001-01-03');
1614INSERT INTO t1 VALUES (1,' 2001-01-04');
1615INSERT INTO t1 VALUES (2,'2001-01-01');
1616INSERT INTO t1 VALUES (2,'2001-01-02');
1617INSERT INTO t1 VALUES (2,'2001-01-03');
1618INSERT INTO t1 VALUES (2,' 2001-01-04');
1619INSERT INTO t1 VALUES (3,'2001-01-01');
1620INSERT INTO t1 VALUES (3,'2001-01-02');
1621INSERT INTO t1 VALUES (3,'2001-01-03');
1622INSERT INTO t1 VALUES (3,' 2001-01-04');
1623INSERT INTO t1 VALUES (4,'2001-01-01');
1624INSERT INTO t1 VALUES (4,'2001-01-02');
1625INSERT INTO t1 VALUES (4,'2001-01-03');
1626INSERT INTO t1 VALUES (4,' 2001-01-04');
1627INSERT INTO t1 SELECT * FROM t1;
1628INSERT INTO t1 SELECT * FROM t1;
1629ANALYZE TABLE t1;
1630SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     ' 2001-01-04' AND    '2001-01-05' GROUP BY id;
1631SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     '2001-01-04' AND     '2001-01-05' GROUP BY id;
1632SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;
1633SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND     '2001-01-05' GROUP BY id;
1634SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     '2001-01-04' AND DATE'2001-01-05' GROUP BY id;
1635ALTER TABLE t1 ADD KEY(id,a);
1636SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     ' 2001-01-04' AND    '2001-01-05' GROUP BY id;
1637SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     '2001-01-04' AND     '2001-01-05' GROUP BY id;
1638SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;
1639SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND     '2001-01-05' GROUP BY id;
1640SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     '2001-01-04' AND DATE'2001-01-05' GROUP BY id;
1641EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     ' 2001-01-04' AND    '2001-01-05' GROUP BY id;
1642EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     '2001-01-04' AND     '2001-01-05' GROUP BY id;
1643EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;
1644EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND     '2001-01-05' GROUP BY id;
1645EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN     '2001-01-04' AND DATE'2001-01-05' GROUP BY id;
1646DROP TABLE t1;
1647
1648--echo #
1649--echo # MIN() optimization didn't work correctly with BETWEEN when using too
1650--echo # long strings.
1651--echo #
1652
1653create table t1 (a varchar(10), key (a)) engine=myisam;
1654insert into t1 values("bar"),("Cafe");
1655explain select min(a) from t1 where a between "a" and "Cafe2";
1656explain select min(a) from t1 where a between "a" and "Cafeeeeeeeeeeeeeeeeeeeeeeeeee";
1657explain select min(a) from t1 where a between "abbbbbbbbbbbbbbbbbbbb" and "Cafe2";
1658drop table t1;
1659
1660--echo #
1661--echo # MDEV-15433: Optimizer does not use group by optimization with distinct
1662--echo #
1663
1664CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a));
1665--disable_query_log
1666INSERT INTO t1(a) VALUES (1), (2), (3), (4);
1667INSERT INTO t1(a) SELECT a FROM t1;
1668INSERT INTO t1(a) SELECT a FROM t1;
1669INSERT INTO t1(a) SELECT a FROM t1;
1670INSERT INTO t1(a) SELECT a FROM t1;
1671INSERT INTO t1(a) SELECT a FROM t1;
1672INSERT INTO t1(a) SELECT a FROM t1;
1673INSERT INTO t1(a) SELECT a FROM t1;
1674INSERT INTO t1(a) SELECT a FROM t1;
1675INSERT INTO t1(a) SELECT a FROM t1;
1676INSERT INTO t1(a) SELECT a FROM t1;
1677INSERT INTO t1(a) SELECT a FROM t1;
1678INSERT INTO t1(a) SELECT a FROM t1;
1679INSERT INTO t1(a) SELECT a FROM t1;
1680INSERT INTO t1(a) SELECT a FROM t1;
1681--enable_query_log
1682OPTIMIZE TABLE t1;
1683EXPLAIN SELECT DISTINCT a FROM t1;
1684SELECT DISTINCT a FROM t1;
1685drop table t1;
1686
1687--echo #
1688--echo # End of 10.1 tests
1689--echo #
1690