|
1
by brian
clean slate |
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 |
||
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
17 |
create TEMPORARY table t1 ( |
|
1
by brian
clean slate |
18 |
a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' |
|
723
by Brian Aker
Fix for group_min_max test (aka... use MyISAM... need to further test |
19 |
) ENGINE=MYISAM; |
|
1
by brian
clean slate |
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 |
||
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
67 |
create TEMPORARY table t2 ( |
|
1
by brian
clean slate |
68 |
a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(64) default ' ' |
|
723
by Brian Aker
Fix for group_min_max test (aka... use MyISAM... need to further test |
69 |
) ENGINE=MYISAM; |
|
1
by brian
clean slate |
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 |
||
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
104 |
create TEMPORARY table t3 ( |
|
1
by brian
clean slate |
105 |
a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' ' |
|
723
by Brian Aker
Fix for group_min_max test (aka... use MyISAM... need to further test |
106 |
) ENGINE=MYISAM; |
|
1
by brian
clean slate |
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; |
|
|
723
by Brian Aker
Fix for group_min_max test (aka... use MyISAM... need to further test |
372 |
#select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
|
|
1
by brian
clean slate |
373 |
select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b; |
|
723
by Brian Aker
Fix for group_min_max test (aka... use MyISAM... need to further test |
374 |
#select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
|
|
1
by brian
clean slate |
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; |
|
|
723
by Brian Aker
Fix for group_min_max test (aka... use MyISAM... need to further test |
380 |
#select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
|
|
1
by brian
clean slate |
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; |
|
|
723
by Brian Aker
Fix for group_min_max test (aka... use MyISAM... need to further test |
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;
|
|
|
1
by brian
clean slate |
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; |
|
|
1718.2.1
by Lee Bieber
For the feature request (https://blueprints.launchpad.net/drizzle/+spec/limit-maximum-sort-size) |
526 |
--sorted_result |
|
2141.4.2
by Andrew Hutchings
Implicit joins of the form "SELECT * FROM t1, t2" without WHERE or ON now error. |
527 |
select distinct t1.a1,t2.a1 from t1,t2 where t1.a1 >= ''; |
|
1
by brian
clean slate |
528 |
|
529 |
||
530 |
#
|
|
531 |
# DISTINCT queries with GROUP-BY
|
|
532 |
#
|
|
533 |
||
534 |
# plans
|
|
535 |
explain select distinct a1,a2,b from t1; |
|
536 |
explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; |
|
537 |
explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; |
|
538 |
explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; |
|
539 |
explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; |
|
540 |
||
541 |
--replace_column 9 # |
|
542 |
explain select distinct a1,a2,b from t2; |
|
543 |
--replace_column 9 # |
|
544 |
explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; |
|
545 |
--replace_column 9 # |
|
546 |
explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; |
|
547 |
--replace_column 9 # |
|
548 |
explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; |
|
549 |
--replace_column 9 # |
|
550 |
explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; |
|
551 |
||
552 |
# queries
|
|
553 |
select distinct a1,a2,b from t1; |
|
554 |
select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; |
|
555 |
select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; |
|
556 |
select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; |
|
557 |
select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; |
|
558 |
||
559 |
select distinct a1,a2,b from t2; |
|
560 |
select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; |
|
561 |
select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; |
|
562 |
select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; |
|
563 |
select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; |
|
564 |
||
565 |
||
566 |
#
|
|
567 |
# COUNT (DISTINCT cols) queries
|
|
568 |
#
|
|
569 |
||
570 |
explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); |
|
571 |
explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); |
|
572 |
explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); |
|
573 |
explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a'); |
|
574 |
explain extended select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); |
|
575 |
||
576 |
select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); |
|
577 |
select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); |
|
578 |
select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); |
|
579 |
select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a'); |
|
580 |
select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); |
|
581 |
||
582 |
#
|
|
583 |
# Queries with expressions in the select clause
|
|
584 |
#
|
|
585 |
||
586 |
explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b; |
|
587 |
explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b; |
|
588 |
explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b; |
|
589 |
explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; |
|
590 |
explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2; |
|
591 |
||
592 |
select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b; |
|
593 |
select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b; |
|
594 |
select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b; |
|
595 |
select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; |
|
596 |
select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2; |
|
597 |
||
598 |
||
599 |
#
|
|
600 |
# Negative examples: queries that should NOT be treated as optimizable by
|
|
601 |
# QUICK_GROUP_MIN_MAX_SELECT
|
|
602 |
#
|
|
603 |
||
604 |
# select a non-indexed attribute
|
|
605 |
explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b; |
|
606 |
||
607 |
explain select a1,a2,b,d from t1 group by a1,a2,b; |
|
608 |
||
609 |
# predicate that references an attribute that is after the MIN/MAX argument
|
|
610 |
# in the index
|
|
611 |
explain extended select a1,a2,min(b),max(b) from t1 |
|
612 |
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2; |
|
613 |
||
614 |
# predicate that references a non-indexed attribute
|
|
615 |
explain extended select a1,a2,b,min(c),max(c) from t1 |
|
616 |
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b; |
|
617 |
||
618 |
explain extended select a1,a2,b,c from t1 |
|
619 |
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c; |
|
620 |
||
621 |
# non-equality predicate for a non-group select attribute
|
|
622 |
explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1; |
|
623 |
explain extended select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b; |
|
624 |
||
625 |
# non-group field with an equality predicate that references a keypart after the
|
|
626 |
# MIN/MAX argument
|
|
627 |
explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; |
|
628 |
select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; |
|
629 |
||
630 |
# disjunction for a non-group select attribute
|
|
631 |
explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1; |
|
632 |
||
633 |
# non-range predicate for the MIN/MAX attribute
|
|
634 |
explain select a1,a2,b,min(c),max(c) from t2 |
|
635 |
where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b; |
|
636 |
||
637 |
# not all attributes are indexed by one index
|
|
638 |
explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c; |
|
639 |
||
640 |
# other aggregate functions than MIN/MAX
|
|
641 |
explain select a1,a2,count(a2) from t1 group by a1,a2,b; |
|
642 |
explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b; |
|
643 |
explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b; |
|
644 |
||
645 |
||
646 |
#
|
|
647 |
# Bug #16710: select distinct doesn't return all it should
|
|
648 |
#
|
|
649 |
||
650 |
explain select distinct(a1) from t1 where ord(a2) = 98; |
|
651 |
select distinct(a1) from t1 where ord(a2) = 98; |
|
652 |
||
653 |
#
|
|
654 |
# BUG#11044: DISTINCT or GROUP BY queries with equality predicates instead of MIN/MAX.
|
|
655 |
#
|
|
656 |
||
657 |
explain select a1 from t1 where a2 = 'b' group by a1; |
|
658 |
select a1 from t1 where a2 = 'b' group by a1; |
|
659 |
||
660 |
explain select distinct a1 from t1 where a2 = 'b'; |
|
661 |
select distinct a1 from t1 where a2 = 'b'; |
|
662 |
||
663 |
#
|
|
664 |
# Bug #12672: primary key implcitly included in every innodb index
|
|
665 |
#
|
|
666 |
# Test case moved to group_min_max_innodb
|
|
667 |
||
668 |
||
669 |
#
|
|
670 |
# Bug #6142: a problem with the empty innodb table
|
|
671 |
#
|
|
672 |
# Test case moved to group_min_max_innodb
|
|
673 |
||
674 |
||
675 |
#
|
|
676 |
# Bug #9798: group by with rollup
|
|
677 |
#
|
|
678 |
# Test case moved to group_min_max_innodb
|
|
679 |
||
680 |
||
681 |
#
|
|
682 |
# Bug #13293 Wrongly used index results in endless loop.
|
|
683 |
#
|
|
684 |
# Test case moved to group_min_max_innodb
|
|
685 |
||
686 |
||
687 |
drop table t1,t2,t3; |
|
688 |
||
689 |
#
|
|
690 |
# Bug #14920 Ordering aggregated result sets with composite primary keys
|
|
691 |
# corrupts resultset
|
|
692 |
#
|
|
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
693 |
create TEMPORARY table t1 (c1 int not null,c2 int not null, primary key(c1,c2)) ENGINE=MYISAM; |
|
1
by brian
clean slate |
694 |
insert into t1 (c1,c2) values |
695 |
(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9); |
|
696 |
select distinct c1, c2 from t1 order by c2; |
|
697 |
select c1,min(c2) as c2 from t1 group by c1 order by c2; |
|
698 |
select c1,c2 from t1 group by c1,c2 order by c2; |
|
699 |
drop table t1; |
|
700 |
||
701 |
#
|
|
702 |
# Bug #16203: Analysis for possible min/max optimization erroneously
|
|
703 |
# returns impossible range
|
|
704 |
#
|
|
705 |
||
|
723
by Brian Aker
Fix for group_min_max test (aka... use MyISAM... need to further test |
706 |
CREATE TABLE t1 (a varchar(5), b int, PRIMARY KEY (a,b)); |
|
1
by brian
clean slate |
707 |
INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4); |
|
1222.1.14
by Brian Aker
Remove OPTIMIZE, place in ALTER TABLE (which does the same thing). |
708 |
ALTER TABLE t1 ENGINE="DEFAULT"; |
|
1
by brian
clean slate |
709 |
|
710 |
SELECT a FROM t1 WHERE a='AA' GROUP BY a; |
|
711 |
SELECT a FROM t1 WHERE a='BB' GROUP BY a; |
|
712 |
||
713 |
EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a; |
|
714 |
EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a; |
|
715 |
||
716 |
SELECT DISTINCT a FROM t1 WHERE a='BB'; |
|
717 |
SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%'; |
|
718 |
SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a; |
|
719 |
||
720 |
DROP TABLE t1; |
|
721 |
||
722 |
#
|
|
723 |
# Bug #18068: SELECT DISTINCT
|
|
724 |
#
|
|
725 |
||
726 |
CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a)); |
|
727 |
||
728 |
INSERT INTO t1 (a) VALUES |
|
729 |
(''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'), |
|
730 |
('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'), |
|
731 |
('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN'); |
|
732 |
||
733 |
EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a; |
|
734 |
SELECT DISTINCT a,a FROM t1 ORDER BY a; |
|
735 |
||
736 |
DROP TABLE t1; |
|
737 |
||
738 |
#
|
|
739 |
# Bug #21007: NATURAL JOIN (any JOIN (2 x NATURAL JOIN)) crashes the server
|
|
740 |
#
|
|
741 |
||
742 |
CREATE TABLE t1 (id1 INT, id2 INT); |
|
743 |
CREATE TABLE t2 (id2 INT, id3 INT, id5 INT); |
|
744 |
CREATE TABLE t3 (id3 INT, id4 INT); |
|
745 |
CREATE TABLE t4 (id4 INT); |
|
746 |
CREATE TABLE t5 (id5 INT, id6 INT); |
|
747 |
CREATE TABLE t6 (id6 INT); |
|
748 |
||
749 |
INSERT INTO t1 VALUES(1,1); |
|
750 |
INSERT INTO t2 VALUES(1,1,1); |
|
751 |
INSERT INTO t3 VALUES(1,1); |
|
752 |
INSERT INTO t4 VALUES(1); |
|
753 |
INSERT INTO t5 VALUES(1,1); |
|
754 |
INSERT INTO t6 VALUES(1); |
|
755 |
||
756 |
# original bug query
|
|
757 |
SELECT * FROM |
|
758 |
t1
|
|
759 |
NATURAL JOIN |
|
760 |
(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6) |
|
761 |
ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)); |
|
762 |
||
763 |
# inner join swapped
|
|
764 |
SELECT * FROM |
|
765 |
t1
|
|
766 |
NATURAL JOIN |
|
767 |
(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2 |
|
768 |
ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)); |
|
769 |
||
770 |
# one join less, no ON cond
|
|
771 |
SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2); |
|
772 |
||
773 |
# wrong error message: 'id2' - ambiguous column
|
|
774 |
SELECT * FROM |
|
775 |
(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6) |
|
776 |
ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)) |
|
777 |
NATURAL JOIN |
|
778 |
t1; |
|
779 |
SELECT * FROM |
|
780 |
(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6))) |
|
781 |
NATURAL JOIN |
|
782 |
t1; |
|
783 |
||
784 |
DROP TABLE t1,t2,t3,t4,t5,t6; |
|
785 |
||
786 |
#
|
|
787 |
# Bug#22342: No results returned for query using max and group by
|
|
788 |
#
|
|
789 |
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)); |
|
790 |
INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3); |
|
791 |
||
792 |
explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; |
|
793 |
SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; |
|
794 |
SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a; |
|
795 |
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)); |
|
796 |
INSERT INTO t2 SELECT a,b,b FROM t1; |
|
797 |
explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; |
|
798 |
SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; |
|
799 |
||
800 |
DROP TABLE t1,t2; |
|
801 |
||
802 |
#
|
|
803 |
# Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar statements
|
|
804 |
#
|
|
805 |
||
806 |
CREATE TABLE t1 (a INT, b INT, INDEX (a,b)); |
|
807 |
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), |
|
808 |
(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); |
|
809 |
EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; |
|
810 |
FLUSH STATUS; |
|
811 |
SELECT max(b), a FROM t1 GROUP BY a; |
|
812 |
SHOW STATUS LIKE 'handler_read__e%'; |
|
813 |
EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; |
|
814 |
FLUSH STATUS; |
|
815 |
CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a; |
|
816 |
SHOW STATUS LIKE 'handler_read__e%'; |
|
817 |
FLUSH STATUS; |
|
818 |
SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b; |
|
819 |
SHOW STATUS LIKE 'handler_read__e%'; |
|
820 |
FLUSH STATUS; |
|
821 |
(SELECT max(b), a FROM t1 GROUP BY a) UNION |
|
822 |
(SELECT max(b), a FROM t1 GROUP BY a); |
|
823 |
SHOW STATUS LIKE 'handler_read__e%'; |
|
824 |
EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION |
|
825 |
(SELECT max(b), a FROM t1 GROUP BY a); |
|
826 |
||
827 |
EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x |
|
828 |
FROM t1 AS t1_outer; |
|
829 |
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS |
|
830 |
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); |
|
831 |
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE |
|
832 |
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; |
|
833 |
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE |
|
834 |
a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); |
|
835 |
EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING |
|
836 |
a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); |
|
837 |
EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 |
|
838 |
ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) |
|
839 |
AND t1_outer1.b = t1_outer2.b; |
|
840 |
EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x |
|
841 |
FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; |
|
842 |
||
843 |
CREATE TABLE t3 LIKE t1; |
|
844 |
FLUSH STATUS; |
|
845 |
INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a; |
|
846 |
SHOW STATUS LIKE 'handler_read__e%'; |
|
847 |
DELETE FROM t3; |
|
848 |
FLUSH STATUS; |
|
849 |
INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) |
|
850 |
FROM t1 LIMIT 1; |
|
851 |
SHOW STATUS LIKE 'handler_read__e%'; |
|
852 |
FLUSH STATUS; |
|
853 |
DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000; |
|
854 |
SHOW STATUS LIKE 'handler_read__e%'; |
|
855 |
FLUSH STATUS; |
|
856 |
--error ER_SUBQUERY_NO_1_ROW |
|
857 |
DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x |
|
858 |
FROM t1) > 10000; |
|
859 |
SHOW STATUS LIKE 'handler_read__e%'; |
|
860 |
||
861 |
DROP TABLE t1,t2,t3; |
|
862 |
||
863 |
#
|
|
864 |
# Bug#25602: queries with DISTINCT and SQL_BIG_RESULT hint
|
|
865 |
# for which loose scan optimization is applied
|
|
866 |
#
|
|
867 |
||
868 |
CREATE TABLE t1 (a int, INDEX idx(a)); |
|
869 |
INSERT INTO t1 VALUES |
|
870 |
(4), (2), (1), (2), (4), (2), (1), (4), |
|
871 |
(4), (2), (1), (2), (2), (4), (1), (4); |
|
872 |
||
873 |
EXPLAIN SELECT DISTINCT(a) FROM t1; |
|
874 |
SELECT DISTINCT(a) FROM t1; |
|
875 |
EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; |
|
876 |
SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; |
|
877 |
||
878 |
DROP TABLE t1; |
|
879 |
||
880 |
#
|
|
881 |
# Bug #32268: Indexed queries give bogus MIN and MAX results
|
|
882 |
#
|
|
883 |
||
884 |
CREATE TABLE t1 (a INT, b INT); |
|
885 |
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3); |
|
886 |
INSERT INTO t1 SELECT a + 1, b FROM t1; |
|
887 |
INSERT INTO t1 SELECT a + 2, b FROM t1; |
|
888 |
||
889 |
EXPLAIN
|
|
890 |
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; |
|
891 |
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; |
|
892 |
||
893 |
CREATE INDEX break_it ON t1 (a, b); |
|
894 |
||
895 |
EXPLAIN
|
|
896 |
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; |
|
897 |
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; |
|
898 |
||
899 |
EXPLAIN
|
|
900 |
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; |
|
901 |
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; |
|
902 |
||
903 |
EXPLAIN
|
|
904 |
SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; |
|
905 |
SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; |
|
906 |
||
907 |
DROP TABLE t1; |
|
|
1052.1.1
by Joe Daly
Add tests for #309547 |
908 |
|
909 |
#
|
|
910 |
# Bug #309547 key_infix_len can be overwritten causing some group by
|
|
911 |
# queries return no rows
|
|
912 |
||
913 |
CREATE TABLE t1 (a int, b int, c int, d int, |
|
914 |
KEY foo (c,d,a,b), KEY bar (c,a,b,d)); |
|
915 |
||
916 |
INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4); |
|
917 |
INSERT INTO t1 SELECT * FROM t1; |
|
918 |
INSERT INTO t1 SELECT * FROM t1; |
|
919 |
INSERT INTO t1 SELECT a,b,c+1,d FROM t1; |
|
920 |
||
921 |
#Should be non-empty
|
|
922 |
EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4; |
|
923 |
SELECT DISTINCT c FROM t1 WHERE d=4; |
|
924 |
||
925 |
DROP TABLE t1; |