← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to mysql-test/t/group_min_max.test

  • Committer: brian
  • Date: 2008-06-25 05:29:13 UTC
  • Revision ID: brian@localhost.localdomain-20080625052913-6upwo0jsrl4lnapl
clean slate

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/t/group_min_max.test
 
1
#
 
2
# Test file for WL#1724 (Min/Max Optimization for Queries with Group By Clause).
 
3
# The queries in this file test query execution via QUICK_GROUP_MIN_MAX_SELECT.
 
4
#
 
5
 
 
6
#
 
7
# TODO:
 
8
# Add queries with:
 
9
# - C != const
 
10
# - C IS NOT NULL
 
11
# - HAVING clause
 
12
 
 
13
--disable_warnings
 
14
drop table if exists t1;
 
15
--enable_warnings
 
16
 
 
17
create table t1 (
 
18
  a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
 
19
);
 
20
 
 
21
insert into t1 (a1, a2, b, c, d) values
 
22
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
 
23
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
 
24
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
 
25
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
 
26
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
 
27
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
 
28
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
 
29
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
 
30
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
 
31
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
 
32
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
 
33
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
 
34
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
 
35
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
 
36
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
 
37
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
 
38
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
 
39
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
 
40
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
 
41
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
 
42
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
 
43
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
 
44
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
 
45
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
 
46
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
 
47
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
 
48
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
 
49
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
 
50
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
 
51
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
 
52
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
 
53
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
 
54
 
 
55
create index idx_t1_0 on t1 (a1);
 
56
create index idx_t1_1 on t1 (a1,a2,b,c);
 
57
create index idx_t1_2 on t1 (a1,a2,b);
 
58
analyze table t1;
 
59
 
 
60
# t2 is the same as t1, but with some NULLs in the MIN/MAX column, and
 
61
# one more nullable attribute
 
62
 
 
63
--disable_warnings
 
64
drop table if exists t2;
 
65
--enable_warnings
 
66
 
 
67
create table t2 (
 
68
  a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(64) default ' '
 
69
);
 
70
insert into t2 select * from t1;
 
71
# add few rows with NULL's in the MIN/MAX column
 
72
insert into t2 (a1, a2, b, c, d) values
 
73
('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
 
74
('a','a','a',NULL,'xyz'),
 
75
('a','a','b',NULL,'xyz'),
 
76
('a','b','a',NULL,'xyz'),
 
77
('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
 
78
('d','b','b',NULL,'xyz'),
 
79
('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
 
80
('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),
 
81
('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
 
82
('a','a','a',NULL,'xyz'),
 
83
('a','a','b',NULL,'xyz'),
 
84
('a','b','a',NULL,'xyz'),
 
85
('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
 
86
('d','b','b',NULL,'xyz'),
 
87
('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
 
88
('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz');
 
89
 
 
90
create index idx_t2_0 on t2 (a1);
 
91
create index idx_t2_1 on t2 (a1,a2,b,c);
 
92
create index idx_t2_2 on t2 (a1,a2,b);
 
93
analyze table t2;
 
94
 
 
95
# Table t3 is the same as t1, but with smaller column lenghts.
 
96
# This allows to test different branches of the cost computation procedure
 
97
# when the number of keys per block are less than the number of keys in the
 
98
# sub-groups formed by predicates over non-group attributes. 
 
99
 
 
100
--disable_warnings
 
101
drop table if exists t3;
 
102
--enable_warnings
 
103
 
 
104
create table t3 (
 
105
  a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' '
 
106
);
 
107
 
 
108
insert into t3 (a1, a2, b, c, d) values
 
109
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
 
110
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
 
111
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
 
112
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
 
113
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
 
114
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
 
115
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
 
116
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
 
117
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
 
118
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
 
119
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
 
120
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
 
121
insert into t3 (a1, a2, b, c, d) values
 
122
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
 
123
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
 
124
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
 
125
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
 
126
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
 
127
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
 
128
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
 
129
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
 
130
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
 
131
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
 
132
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
 
133
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
 
134
insert into t3 (a1, a2, b, c, d) values
 
135
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
 
136
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
 
137
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
 
138
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
 
139
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
 
140
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
 
141
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
 
142
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
 
143
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
 
144
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
 
145
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
 
146
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
 
147
insert into t3 (a1, a2, b, c, d) values
 
148
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
 
149
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
 
150
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
 
151
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
 
152
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
 
153
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
 
154
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
 
155
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
 
156
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
 
157
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
 
158
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
 
159
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
 
160
 
 
161
create index idx_t3_0 on t3 (a1);
 
162
create index idx_t3_1 on t3 (a1,a2,b,c);
 
163
create index idx_t3_2 on t3 (a1,a2,b);
 
164
analyze table t3;
 
165
 
 
166
 
 
167
#
 
168
# Queries without a WHERE clause. These queries do not use ranges.
 
169
#
 
170
 
 
171
# plans
 
172
explain select a1, min(a2) from t1 group by a1;
 
173
explain select a1, max(a2) from t1 group by a1;
 
174
explain select a1, min(a2), max(a2) from t1 group by a1;
 
175
explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
 
176
explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
 
177
--replace_column 7 # 9 #
 
178
explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
 
179
# Select fields in different order
 
180
explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
 
181
explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
 
182
explain select min(a2) from t1 group by a1;
 
183
explain select a2, min(c), max(c) from t1 group by a1,a2,b;
 
184
 
 
185
# queries
 
186
select a1, min(a2) from t1 group by a1;
 
187
select a1, max(a2) from t1 group by a1;
 
188
select a1, min(a2), max(a2) from t1 group by a1;
 
189
select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
 
190
select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
 
191
select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
 
192
# Select fields in different order
 
193
select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
 
194
select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
 
195
select min(a2) from t1 group by a1;
 
196
select a2, min(c), max(c) from t1 group by a1,a2,b;
 
197
 
 
198
#
 
199
# Queries with a where clause
 
200
#
 
201
 
 
202
# A) Preds only over the group 'A' attributes
 
203
# plans
 
204
explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
 
205
explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
 
206
explain select a1,a2,b,       max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
 
207
explain select a1, max(c)            from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
 
208
explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
 
209
explain select a1,a2,b,       max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
 
210
explain select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
 
211
explain select a1,a2,b,       max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
 
212
explain select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
 
213
explain select a1,min(c),max(c)      from t1 where a1 >= 'b' group by a1,a2,b;
 
214
explain select a1,  max(c)           from t1 where a1 in ('a','b','d') group by a1,a2,b;
 
215
 
 
216
--replace_column 9 #
 
217
explain select a1,a2,b,       max(c) from t2 where a1 < 'd' group by a1,a2,b;
 
218
--replace_column 9 #
 
219
explain select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
 
220
--replace_column 9 #
 
221
explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
 
222
--replace_column 9 #
 
223
explain select a1,a2,b,       max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
 
224
--replace_column 9 #
 
225
explain select a1, max(c)            from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
 
226
--replace_column 9 #
 
227
explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
 
228
--replace_column 9 #
 
229
explain select a1,a2,b,       max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
 
230
--replace_column 9 #
 
231
explain select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
 
232
--replace_column 9 #
 
233
explain select a1,a2,b,       max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
 
234
--replace_column 9 #
 
235
explain select a1,a2,b,min(c),max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
 
236
--replace_column 9 #
 
237
explain select a1,min(c),max(c)      from t2 where a1 >= 'b' group by a1,a2,b;
 
238
--replace_column 9 #
 
239
explain select a1,  max(c)           from t2 where a1 in ('a','b','d') group by a1,a2,b;
 
240
 
 
241
# queries
 
242
select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
 
243
select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
 
244
select a1,a2,b,       max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
 
245
select a1, max(c)            from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
 
246
select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
 
247
select a1,a2,b,       max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
 
248
select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
 
249
select a1,a2,b,       max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
 
250
select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
 
251
select a1,min(c),max(c)      from t1 where a1 >= 'b' group by a1,a2,b;
 
252
select a1,  max(c)           from t1 where a1 in ('a','b','d') group by a1,a2,b;
 
253
 
 
254
select a1,a2,b,       max(c) from t2 where a1 < 'd' group by a1,a2,b;
 
255
select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
 
256
select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
 
257
select a1,a2,b,       max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
 
258
select a1, max(c)            from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
 
259
select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
 
260
select a1,a2,b,       max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
 
261
select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
 
262
select a1,a2,b,       max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
 
263
select a1,a2,b,min(c),max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
 
264
select a1,min(c),max(c)      from t2 where a1 >= 'b' group by a1,a2,b;
 
265
select a1,  max(c)           from t2 where a1 in ('a','b','d') group by a1,a2,b;
 
266
 
 
267
# B) Equalities only over the non-group 'B' attributes
 
268
# plans
 
269
explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
 
270
explain select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b') group by a1;
 
271
explain select a1,a2,b,       max(c) from t1 where (b = 'b') group by a1,a2;
 
272
explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
 
273
explain select a1,a2, max(c)         from t1 where (b = 'b') group by a1,a2;
 
274
 
 
275
explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
 
276
explain select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b') group by a1;
 
277
explain select a1,a2,b,       max(c) from t2 where (b = 'b') group by a1,a2;
 
278
explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
 
279
explain select a1,a2, max(c)         from t2 where (b = 'b') group by a1,a2;
 
280
 
 
281
# these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost()
 
282
explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
 
283
explain select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b') group by a1;
 
284
 
 
285
# queries
 
286
select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
 
287
select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b') group by a1;
 
288
select a1,a2,b,       max(c) from t1 where (b = 'b') group by a1,a2;
 
289
select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
 
290
select a1,a2, max(c)         from t1 where (b = 'b') group by a1,a2;
 
291
 
 
292
select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
 
293
select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b') group by a1;
 
294
select a1,a2,b,       max(c) from t2 where (b = 'b') group by a1,a2;
 
295
select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
 
296
select a1,a2, max(c)         from t2 where (b = 'b') group by a1,a2;
 
297
 
 
298
# these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost()
 
299
select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
 
300
select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b') group by a1;
 
301
 
 
302
 
 
303
# IS NULL (makes sense for t2 only)
 
304
# plans
 
305
explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
 
306
explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
 
307
explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
 
308
explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
 
309
explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
 
310
explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
 
311
# queries
 
312
select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
 
313
select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
 
314
select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
 
315
select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
 
316
select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
 
317
select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
 
318
 
 
319
# C) Range predicates for the MIN/MAX attribute
 
320
# plans
 
321
--replace_column 9 #
 
322
explain select a1,a2,b,       max(c) from t1 where (c > 'b1') group by a1,a2,b;
 
323
explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
 
324
explain select a1,a2,b,       max(c) from t1 where (c > 'f123') group by a1,a2,b;
 
325
explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
 
326
explain select a1,a2,b,       max(c) from t1 where (c < 'a0') group by a1,a2,b;
 
327
explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
 
328
explain select a1,a2,b,       max(c) from t1 where (c < 'k321') group by a1,a2,b;
 
329
explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
 
330
explain select a1,a2,b,       max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
 
331
explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
 
332
explain select a1,a2,b,       max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
 
333
explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
 
334
explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
 
335
explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
 
336
explain select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
 
337
explain select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b;
 
338
 
 
339
--replace_column 9 #
 
340
explain select a1,a2,b,       max(c) from t2 where (c > 'b1') group by a1,a2,b;
 
341
--replace_column 9 #
 
342
explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
 
343
--replace_column 9 #
 
344
explain select a1,a2,b,       max(c) from t2 where (c > 'f123') group by a1,a2,b;
 
345
--replace_column 9 #
 
346
explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
 
347
--replace_column 9 #
 
348
explain select a1,a2,b,       max(c) from t2 where (c < 'a0') group by a1,a2,b;
 
349
--replace_column 9 #
 
350
explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
 
351
--replace_column 9 #
 
352
explain select a1,a2,b,       max(c) from t2 where (c < 'k321') group by a1,a2,b;
 
353
--replace_column 9 #
 
354
explain select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
 
355
--replace_column 9 #
 
356
explain select a1,a2,b,       max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
 
357
--replace_column 9 #
 
358
explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
 
359
--replace_column 9 #
 
360
explain select a1,a2,b,       max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
 
361
--replace_column 9 #
 
362
explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
 
363
--replace_column 9 #
 
364
explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
 
365
--replace_column 9 #
 
366
explain select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
 
367
--replace_column 9 #
 
368
explain select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
 
369
 
 
370
# queries
 
371
select a1,a2,b,       max(c) from t1 where (c > 'b1') group by a1,a2,b;
 
372
select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
 
373
select a1,a2,b,       max(c) from t1 where (c > 'f123') group by a1,a2,b;
 
374
select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
 
375
select a1,a2,b,       max(c) from t1 where (c < 'a0') group by a1,a2,b;
 
376
select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
 
377
select a1,a2,b,       max(c) from t1 where (c < 'k321') group by a1,a2,b;
 
378
select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
 
379
select a1,a2,b,       max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
 
380
select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
 
381
select a1,a2,b,       max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
 
382
select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
 
383
select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
 
384
select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
 
385
select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
 
386
select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b;
 
387
 
 
388
select a1,a2,b,       max(c) from t2 where (c > 'b1') group by a1,a2,b;
 
389
select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
 
390
select a1,a2,b,       max(c) from t2 where (c > 'f123') group by a1,a2,b;
 
391
select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
 
392
select a1,a2,b,       max(c) from t2 where (c < 'a0') group by a1,a2,b;
 
393
select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
 
394
select a1,a2,b,       max(c) from t2 where (c < 'k321') group by a1,a2,b;
 
395
select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
 
396
select a1,a2,b,       max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
 
397
select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
 
398
select a1,a2,b,       max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
 
399
select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
 
400
select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
 
401
select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
 
402
select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
 
403
 
 
404
# analyze the sub-select
 
405
explain select a1,a2,b,min(c),max(c) from t1
 
406
where exists ( select * from t2 where t2.c = t1.c )
 
407
group by a1,a2,b;
 
408
 
 
409
# the sub-select is unrelated to MIN/MAX
 
410
explain select a1,a2,b,min(c),max(c) from t1
 
411
where exists ( select * from t2 where t2.c > 'b1' )
 
412
group by a1,a2,b;
 
413
 
 
414
 
 
415
# A,B,C) Predicates referencing mixed classes of attributes
 
416
# plans
 
417
explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
 
418
explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
 
419
explain select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
 
420
explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
 
421
explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
 
422
explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
 
423
explain select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b;
 
424
 
 
425
--replace_column 9 #
 
426
explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
 
427
--replace_column 9 #
 
428
explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
 
429
--replace_column 9 #
 
430
explain select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
 
431
--replace_column 9 #
 
432
explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
 
433
--replace_column 9 #
 
434
explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
 
435
--replace_column 9 #
 
436
explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
 
437
 
 
438
# queries
 
439
select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
 
440
select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
 
441
select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
 
442
select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
 
443
select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
 
444
select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
 
445
select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b;
 
446
 
 
447
select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
 
448
select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
 
449
select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
 
450
select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
 
451
select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
 
452
select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
 
453
 
 
454
 
 
455
#
 
456
# GROUP BY queries without MIN/MAX
 
457
#
 
458
 
 
459
# plans
 
460
explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
 
461
explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 
462
explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
 
463
explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
 
464
 
 
465
--replace_column 9 #
 
466
explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
 
467
--replace_column 9 #
 
468
explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 
469
--replace_column 9 #
 
470
explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
 
471
--replace_column 9 #
 
472
explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
 
473
 
 
474
# queries
 
475
select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
 
476
select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 
477
select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
 
478
select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
 
479
 
 
480
select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
 
481
select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 
482
select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
 
483
select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
 
484
 
 
485
#
 
486
# DISTINCT queries
 
487
#
 
488
 
 
489
# plans
 
490
explain select distinct a1,a2,b from t1;
 
491
explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
 
492
explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
 
493
explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
 
494
explain select distinct b from t1 where (a2 >= 'b') and (b = 'a');
 
495
 
 
496
--replace_column 9 #
 
497
explain select distinct a1,a2,b from t2;
 
498
--replace_column 9 #
 
499
explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
 
500
explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
 
501
--replace_column 9 #
 
502
explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
 
503
explain select distinct b from t2 where (a2 >= 'b') and (b = 'a');
 
504
 
 
505
# queries
 
506
select distinct a1,a2,b from t1;
 
507
select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
 
508
select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
 
509
select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
 
510
select distinct b from t1 where (a2 >= 'b') and (b = 'a');
 
511
 
 
512
select distinct a1,a2,b from t2;
 
513
select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
 
514
select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
 
515
select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
 
516
select distinct b from t2 where (a2 >= 'b') and (b = 'a');
 
517
 
 
518
# BUG #6303
 
519
select distinct t_00.a1
 
520
from t1 t_00
 
521
where exists ( select * from t2 where a1 = t_00.a1 );
 
522
 
 
523
# BUG #8532 - SELECT DISTINCT a, a causes server to crash
 
524
select distinct a1,a1 from t1;
 
525
select distinct a2,a1,a2,a1 from t1;
 
526
select distinct t1.a1,t2.a1 from t1,t2;
 
527
 
 
528
 
 
529
#
 
530
# DISTINCT queries with GROUP-BY
 
531
#
 
532
 
 
533
# plans
 
534
explain select distinct a1,a2,b from t1;
 
535
explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 
536
explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
 
537
explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
 
538
explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 
539
 
 
540
--replace_column 9 #
 
541
explain select distinct a1,a2,b from t2;
 
542
--replace_column 9 #
 
543
explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 
544
--replace_column 9 #
 
545
explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
 
546
--replace_column 9 #
 
547
explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
 
548
--replace_column 9 #
 
549
explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 
550
 
 
551
# queries
 
552
select distinct a1,a2,b from t1;
 
553
select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 
554
select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
 
555
select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
 
556
select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 
557
 
 
558
select distinct a1,a2,b from t2;
 
559
select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 
560
select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
 
561
select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
 
562
select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 
563
 
 
564
 
 
565
#
 
566
# COUNT (DISTINCT cols) queries
 
567
#
 
568
 
 
569
explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
 
570
explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
 
571
explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
 
572
explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
 
573
explain extended select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
 
574
 
 
575
select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
 
576
select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
 
577
select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
 
578
select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
 
579
select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
 
580
 
 
581
#
 
582
# Queries with expressions in the select clause
 
583
#
 
584
 
 
585
explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
 
586
explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
 
587
explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
 
588
explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
 
589
explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
 
590
 
 
591
select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
 
592
select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
 
593
select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
 
594
select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
 
595
select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
 
596
 
 
597
 
 
598
#
 
599
# Negative examples: queries that should NOT be treated as optimizable by
 
600
# QUICK_GROUP_MIN_MAX_SELECT
 
601
#
 
602
 
 
603
# select a non-indexed attribute
 
604
explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b;
 
605
 
 
606
explain select a1,a2,b,d from t1 group by a1,a2,b;
 
607
 
 
608
# predicate that references an attribute that is after the MIN/MAX argument
 
609
# in the index
 
610
explain extended select a1,a2,min(b),max(b) from t1
 
611
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;
 
612
 
 
613
# predicate that references a non-indexed attribute
 
614
explain extended select a1,a2,b,min(c),max(c) from t1
 
615
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
 
616
 
 
617
explain extended select a1,a2,b,c from t1
 
618
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
 
619
 
 
620
# non-equality predicate for a non-group select attribute
 
621
explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
 
622
explain extended select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b;
 
623
 
 
624
# non-group field with an equality predicate that references a keypart after the
 
625
# MIN/MAX argument
 
626
explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
 
627
select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
 
628
 
 
629
# disjunction for a non-group select attribute
 
630
explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1;
 
631
 
 
632
# non-range predicate for the MIN/MAX attribute
 
633
explain select a1,a2,b,min(c),max(c) from t2
 
634
where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b;
 
635
 
 
636
# not all attributes are indexed by one index
 
637
explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c;
 
638
 
 
639
# other aggregate functions than MIN/MAX
 
640
explain select a1,a2,count(a2) from t1 group by a1,a2,b;
 
641
explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
 
642
explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
 
643
 
 
644
 
 
645
#
 
646
# Bug #16710: select distinct doesn't return all it should
 
647
#
 
648
 
 
649
explain select distinct(a1) from t1 where ord(a2) = 98;
 
650
select distinct(a1) from t1 where ord(a2) = 98;
 
651
 
 
652
#
 
653
# BUG#11044: DISTINCT or GROUP BY queries with equality predicates instead of MIN/MAX.
 
654
#
 
655
 
 
656
explain select a1 from t1 where a2 = 'b' group by a1;
 
657
select a1 from t1 where a2 = 'b' group by a1;
 
658
 
 
659
explain select distinct a1 from t1 where a2 = 'b';
 
660
select distinct a1 from t1 where a2 = 'b';
 
661
 
 
662
#
 
663
# Bug #12672: primary key implcitly included in every innodb index
 
664
#
 
665
# Test case moved to group_min_max_innodb
 
666
 
 
667
 
 
668
#
 
669
# Bug #6142: a problem with the empty innodb table
 
670
#
 
671
# Test case moved to group_min_max_innodb
 
672
 
 
673
 
 
674
#
 
675
# Bug #9798: group by with rollup
 
676
#
 
677
# Test case moved to group_min_max_innodb
 
678
 
 
679
 
 
680
#
 
681
# Bug #13293 Wrongly used index results in endless loop.
 
682
#
 
683
# Test case moved to group_min_max_innodb
 
684
 
 
685
 
 
686
drop table t1,t2,t3;
 
687
 
 
688
#
 
689
# Bug #14920 Ordering aggregated result sets with composite primary keys
 
690
# corrupts resultset
 
691
#
 
692
create table t1 (c1 int not null,c2 int not null, primary key(c1,c2));
 
693
insert into t1 (c1,c2) values
 
694
(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9);
 
695
select distinct c1, c2 from t1 order by c2;
 
696
select c1,min(c2) as c2 from t1 group by c1 order by c2;
 
697
select c1,c2 from t1 group by c1,c2 order by c2;
 
698
drop table t1;
 
699
 
 
700
#
 
701
# Bug #16203: Analysis for possible min/max optimization erroneously
 
702
#             returns impossible range
 
703
#
 
704
 
 
705
CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b));
 
706
INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4);
 
707
OPTIMIZE TABLE t1;
 
708
 
 
709
SELECT a FROM t1 WHERE a='AA' GROUP BY a;
 
710
SELECT a FROM t1 WHERE a='BB' GROUP BY a;
 
711
 
 
712
EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a;
 
713
EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a;
 
714
 
 
715
SELECT DISTINCT a FROM t1 WHERE a='BB';
 
716
SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%';
 
717
SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a;
 
718
 
 
719
DROP TABLE t1;
 
720
 
 
721
#
 
722
# Bug #18068: SELECT DISTINCT
 
723
#
 
724
 
 
725
CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a));
 
726
 
 
727
INSERT INTO t1 (a) VALUES 
 
728
  (''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'),
 
729
  ('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'),
 
730
  ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN');
 
731
 
 
732
EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a;  
 
733
SELECT DISTINCT a,a FROM t1 ORDER BY a;  
 
734
 
 
735
DROP TABLE t1;
 
736
 
 
737
#
 
738
# Bug #21007: NATURAL JOIN (any JOIN (2 x NATURAL JOIN)) crashes the server
 
739
#
 
740
 
 
741
CREATE TABLE t1 (id1 INT, id2 INT);
 
742
CREATE TABLE t2 (id2 INT, id3 INT, id5 INT);
 
743
CREATE TABLE t3 (id3 INT, id4 INT);
 
744
CREATE TABLE t4 (id4 INT);
 
745
CREATE TABLE t5 (id5 INT, id6 INT);
 
746
CREATE TABLE t6 (id6 INT);
 
747
 
 
748
INSERT INTO t1 VALUES(1,1);
 
749
INSERT INTO t2 VALUES(1,1,1);
 
750
INSERT INTO t3 VALUES(1,1);
 
751
INSERT INTO t4 VALUES(1);
 
752
INSERT INTO t5 VALUES(1,1);
 
753
INSERT INTO t6 VALUES(1);
 
754
 
 
755
# original bug query
 
756
SELECT * FROM
 
757
t1
 
758
  NATURAL JOIN
 
759
(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
 
760
    ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
 
761
 
 
762
# inner join swapped
 
763
SELECT * FROM
 
764
t1
 
765
  NATURAL JOIN
 
766
(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2
 
767
    ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
 
768
 
 
769
# one join less, no ON cond
 
770
SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2);
 
771
 
 
772
# wrong error message: 'id2' - ambiguous column
 
773
SELECT * FROM
 
774
(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
 
775
    ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5))
 
776
  NATURAL JOIN
 
777
t1;
 
778
SELECT * FROM
 
779
(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6)))
 
780
  NATURAL JOIN
 
781
t1;
 
782
 
 
783
DROP TABLE t1,t2,t3,t4,t5,t6;
 
784
 
 
785
#
 
786
# Bug#22342: No results returned for query using max and group by
 
787
#
 
788
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
 
789
INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
 
790
 
 
791
explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
 
792
SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
 
793
SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
 
794
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
 
795
INSERT INTO t2 SELECT a,b,b FROM t1;
 
796
explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
 
797
SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
 
798
 
 
799
DROP TABLE t1,t2;
 
800
 
 
801
#
 
802
# Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar statements
 
803
#
 
804
 
 
805
CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
 
806
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
 
807
       (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
 
808
EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
 
809
FLUSH STATUS;
 
810
SELECT max(b), a FROM t1 GROUP BY a;
 
811
SHOW STATUS LIKE 'handler_read__e%';
 
812
EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
 
813
FLUSH STATUS;
 
814
CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a;
 
815
SHOW STATUS LIKE 'handler_read__e%';
 
816
FLUSH STATUS;
 
817
SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
 
818
SHOW STATUS LIKE 'handler_read__e%';
 
819
FLUSH STATUS;
 
820
(SELECT max(b), a FROM t1 GROUP BY a) UNION 
 
821
 (SELECT max(b), a FROM t1 GROUP BY a);
 
822
SHOW STATUS LIKE 'handler_read__e%';
 
823
EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION 
 
824
 (SELECT max(b), a FROM t1 GROUP BY a);
 
825
 
 
826
EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
 
827
  FROM t1 AS t1_outer;
 
828
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS 
 
829
  (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
 
830
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
 
831
  (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
 
832
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
 
833
  a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
 
834
EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING 
 
835
  a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
 
836
EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 
 
837
   ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) 
 
838
   AND t1_outer1.b = t1_outer2.b;
 
839
EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
 
840
  FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
 
841
 
 
842
CREATE TABLE t3 LIKE t1;
 
843
FLUSH STATUS;
 
844
INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;
 
845
SHOW STATUS LIKE 'handler_read__e%';
 
846
DELETE FROM t3;
 
847
FLUSH STATUS;
 
848
INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) 
 
849
  FROM t1 LIMIT 1;
 
850
SHOW STATUS LIKE 'handler_read__e%';
 
851
FLUSH STATUS;
 
852
DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
 
853
SHOW STATUS LIKE 'handler_read__e%';
 
854
FLUSH STATUS;
 
855
--error ER_SUBQUERY_NO_1_ROW
 
856
DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x 
 
857
                      FROM t1) > 10000;
 
858
SHOW STATUS LIKE 'handler_read__e%';
 
859
 
 
860
DROP TABLE t1,t2,t3;
 
861
 
 
862
#
 
863
# Bug#25602: queries with DISTINCT and SQL_BIG_RESULT hint 
 
864
#            for which loose scan optimization is applied
 
865
#
 
866
 
 
867
CREATE TABLE t1 (a int, INDEX idx(a));
 
868
INSERT INTO t1 VALUES
 
869
  (4), (2), (1), (2), (4), (2), (1), (4),
 
870
  (4), (2), (1), (2), (2), (4), (1), (4);
 
871
 
 
872
EXPLAIN SELECT DISTINCT(a) FROM t1;
 
873
SELECT DISTINCT(a) FROM t1;
 
874
EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
 
875
SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
 
876
 
 
877
DROP TABLE t1;
 
878
 
 
879
#
 
880
# Bug #32268: Indexed queries give bogus MIN and MAX results
 
881
#
 
882
 
 
883
CREATE TABLE t1 (a INT, b INT);
 
884
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3);
 
885
INSERT INTO t1 SELECT a + 1, b FROM t1;
 
886
INSERT INTO t1 SELECT a + 2, b FROM t1;
 
887
 
 
888
EXPLAIN
 
889
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
 
890
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
 
891
 
 
892
CREATE INDEX break_it ON t1 (a, b);
 
893
 
 
894
EXPLAIN
 
895
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
 
896
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
 
897
 
 
898
EXPLAIN
 
899
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
 
900
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
 
901
 
 
902
EXPLAIN
 
903
SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
 
904
SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
 
905
 
 
906
DROP TABLE t1;