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