| blob | 6fef66b9d9376d4858d40cbac92afef3c610c1a5 |
1 drop table if exists t1;
2 create table t1 (
3 a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
4 );
5 insert into t1 (a1, a2, b, c, d) values
6 ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
7 ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
8 ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
9 ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
10 ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
11 ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
12 ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
13 ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
14 ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
15 ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
16 ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
17 ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
18 ('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
19 ('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
20 ('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
21 ('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
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 create index idx_t1_0 on t1 (a1);
39 create index idx_t1_1 on t1 (a1,a2,b,c);
40 create index idx_t1_2 on t1 (a1,a2,b);
41 analyze table t1;
42 Table Op Msg_type Msg_text
43 test.t1 analyze status Table is already up to date
44 drop table if exists t2;
45 create table t2 (
46 a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(64) default ' '
47 );
48 insert into t2 select * from t1;
49 insert into t2 (a1, a2, b, c, d) values
50 ('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
51 ('a','a','a',NULL,'xyz'),
52 ('a','a','b',NULL,'xyz'),
53 ('a','b','a',NULL,'xyz'),
54 ('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
55 ('d','b','b',NULL,'xyz'),
56 ('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
57 ('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),
58 ('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
59 ('a','a','a',NULL,'xyz'),
60 ('a','a','b',NULL,'xyz'),
61 ('a','b','a',NULL,'xyz'),
62 ('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
63 ('d','b','b',NULL,'xyz'),
64 ('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
65 ('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz');
66 create index idx_t2_0 on t2 (a1);
67 create index idx_t2_1 on t2 (a1,a2,b,c);
68 create index idx_t2_2 on t2 (a1,a2,b);
69 analyze table t2;
70 Table Op Msg_type Msg_text
71 test.t2 analyze status Table is already up to date
72 drop table if exists t3;
73 create table t3 (
74 a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' '
75 );
76 insert into t3 (a1, a2, b, c, d) values
77 ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
78 ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
79 ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
80 ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
81 ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
82 ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
83 ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
84 ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
85 ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
86 ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
87 ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
88 ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
89 insert into t3 (a1, a2, b, c, d) values
90 ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
91 ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
92 ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
93 ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
94 ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
95 ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
96 ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
97 ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
98 ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
99 ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
100 ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
101 ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
102 insert into t3 (a1, a2, b, c, d) values
103 ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
104 ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
105 ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
106 ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
107 ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
108 ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
109 ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
110 ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
111 ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
112 ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
113 ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
114 ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
115 insert into t3 (a1, a2, b, c, d) values
116 ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
117 ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
118 ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
119 ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
120 ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
121 ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
122 ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
123 ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
124 ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
125 ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
126 ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
127 ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
128 create index idx_t3_0 on t3 (a1);
129 create index idx_t3_1 on t3 (a1,a2,b,c);
130 create index idx_t3_2 on t3 (a1,a2,b);
131 analyze table t3;
132 Table Op Msg_type Msg_text
133 test.t3 analyze status Table is already up to date
134 explain select a1, min(a2) from t1 group by a1;
135 id select_type table type possible_keys key key_len ref rows Extra
136 1 SIMPLE t1 range NULL idx_t1_1 130 NULL 5 Using index for group-by
137 explain select a1, max(a2) from t1 group by a1;
138 id select_type table type possible_keys key key_len ref rows Extra
139 1 SIMPLE t1 range NULL idx_t1_1 65 NULL 5 Using index for group-by
140 explain select a1, min(a2), max(a2) from t1 group by a1;
141 id select_type table type possible_keys key key_len ref rows Extra
142 1 SIMPLE t1 range NULL idx_t1_1 130 NULL 5 Using index for group-by
143 explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
144 id select_type table type possible_keys key key_len ref rows Extra
145 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by
146 explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
147 id select_type table type possible_keys key key_len ref rows Extra
148 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by
149 explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
150 id select_type table type possible_keys key key_len ref rows Extra
151 1 SIMPLE t2 range NULL idx_t2_1 # NULL # Using index for group-by
152 explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
153 id select_type table type possible_keys key key_len ref rows Extra
154 1 SIMPLE t1 range NULL idx_t1_1 130 NULL 5 Using index for group-by
155 explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
156 id select_type table type possible_keys key key_len ref rows Extra
157 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by
158 explain select min(a2) from t1 group by a1;
159 id select_type table type possible_keys key key_len ref rows Extra
160 1 SIMPLE t1 range NULL idx_t1_1 130 NULL 5 Using index for group-by
161 explain select a2, min(c), max(c) from t1 group by a1,a2,b;
162 id select_type table type possible_keys key key_len ref rows Extra
163 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by
164 select a1, min(a2) from t1 group by a1;
165 a1 min(a2)
166 a a
167 b a
168 c a
169 d a
170 select a1, max(a2) from t1 group by a1;
171 a1 max(a2)
172 a b
173 b b
174 c b
175 d b
176 select a1, min(a2), max(a2) from t1 group by a1;
177 a1 min(a2) max(a2)
178 a a b
179 b a b
180 c a b
181 d a b
182 select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
183 a1 a2 b min(c) max(c)
184 a a a a111 d111
185 a a b e112 h112
186 a b a i121 l121
187 a b b m122 p122
188 b a a a211 d211
189 b a b e212 h212
190 b b a i221 l221
191 b b b m222 p222
192 c a a a311 d311
193 c a b e312 h312
194 c b a i321 l321
195 c b b m322 p322
196 d a a a411 d411
197 d a b e412 h412
198 d b a i421 l421
199 d b b m422 p422
200 select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
201 a1 a2 b max(c) min(c)
202 a a a d111 a111
203 a a b h112 e112
204 a b a l121 i121
205 a b b p122 m122
206 b a a d211 a211
207 b a b h212 e212
208 b b a l221 i221
209 b b b p222 m222
210 c a a d311 a311
211 c a b h312 e312
212 c b a l321 i321
213 c b b p322 m322
214 d a a d411 a411
215 d a b h412 e412
216 d b a l421 i421
217 d b b p422 m422
218 select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
219 a1 a2 b max(c) min(c)
220 a a NULL a999 a777
221 a a a d111 a111
222 a a b h112 e112
223 a b a l121 i121
224 a b b p122 m122
225 b a a d211 a211
226 b a b h212 e212
227 b b a l221 i221
228 b b b p222 m222
229 c a NULL c999 c777
230 c a a d311 a311
231 c a b h312 e312
232 c b a l321 i321
233 c b b p322 m322
234 d a a d411 a411
235 d a b h412 e412
236 d b a l421 i421
237 d b b p422 m422
238 e a a NULL NULL
239 e a b NULL NULL
240 select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
241 min(a2) a1 max(a2) min(a2) a1
242 a a b a a
243 a b b a b
244 a c b a c
245 a d b a d
246 select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
247 a1 b min(c) a1 max(c) b a2 max(c) max(c)
248 a a a111 a d111 a a d111 d111
249 a b e112 a h112 b a h112 h112
250 a a i121 a l121 a b l121 l121
251 a b m122 a p122 b b p122 p122
252 b a a211 b d211 a a d211 d211
253 b b e212 b h212 b a h212 h212
254 b a i221 b l221 a b l221 l221
255 b b m222 b p222 b b p222 p222
256 c a a311 c d311 a a d311 d311
257 c b e312 c h312 b a h312 h312
258 c a i321 c l321 a b l321 l321
259 c b m322 c p322 b b p322 p322
260 d a a411 d d411 a a d411 d411
261 d b e412 d h412 b a h412 h412
262 d a i421 d l421 a b l421 l421
263 d b m422 d p422 b b p422 p422
264 select min(a2) from t1 group by a1;
265 min(a2)
266 a
267 a
268 a
269 a
270 select a2, min(c), max(c) from t1 group by a1,a2,b;
271 a2 min(c) max(c)
272 a a111 d111
273 a e112 h112
274 b i121 l121
275 b m122 p122
276 a a211 d211
277 a e212 h212
278 b i221 l221
279 b m222 p222
280 a a311 d311
281 a e312 h312
282 b i321 l321
283 b m322 p322
284 a a411 d411
285 a e412 h412
286 b i421 l421
287 b m422 p422
288 explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
289 id select_type table type possible_keys key key_len ref rows Extra
290 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
291 explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
292 id select_type table type possible_keys key key_len ref rows Extra
293 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
294 explain select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
295 id select_type table type possible_keys key key_len ref rows Extra
296 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
297 explain select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
298 id select_type table type possible_keys key key_len ref rows Extra
299 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
300 explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
301 id select_type table type possible_keys key key_len ref rows Extra
302 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by
303 explain select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
304 id select_type table type possible_keys key key_len ref rows Extra
305 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
306 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;
307 id select_type table type possible_keys key key_len ref rows Extra
308 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
309 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;
310 id select_type table type possible_keys key key_len ref rows Extra
311 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
312 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;
313 id select_type table type possible_keys key key_len ref rows Extra
314 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
315 explain select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
316 id select_type table type possible_keys key key_len ref rows Extra
317 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
318 explain select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b;
319 id select_type table type possible_keys key key_len ref rows Extra
320 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
321 explain select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b;
322 id select_type table type possible_keys key key_len ref rows Extra
323 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by
324 explain select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
325 id select_type table type possible_keys key key_len ref rows Extra
326 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
327 explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
328 id select_type table type possible_keys key key_len ref rows Extra
329 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
330 explain select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
331 id select_type table type possible_keys key key_len ref rows Extra
332 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by
333 explain select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
334 id select_type table type possible_keys key key_len ref rows Extra
335 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by
336 explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
337 id select_type table type possible_keys key key_len ref rows Extra
338 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
339 explain select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
340 id select_type table type possible_keys key key_len ref rows Extra
341 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by
342 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;
343 id select_type table type possible_keys key key_len ref rows Extra
344 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
345 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;
346 id select_type table type possible_keys key key_len ref rows Extra
347 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by
348 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;
349 id select_type table type possible_keys key key_len ref rows Extra
350 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
351 explain select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
352 id select_type table type possible_keys key key_len ref rows Extra
353 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
354 explain select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b;
355 id select_type table type possible_keys key key_len ref rows Extra
356 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by
357 select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
358 a1 a2 b min(c) max(c)
359 a a a a111 d111
360 a a b e112 h112
361 a b a i121 l121
362 a b b m122 p122
363 b a a a211 d211
364 b a b e212 h212
365 b b a i221 l221
366 b b b m222 p222
367 c a a a311 d311
368 c a b e312 h312
369 c b a i321 l321
370 c b b m322 p322
371 select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
372 a1 a2 b min(c) max(c)
373 b a a a211 d211
374 b a b e212 h212
375 b b a i221 l221
376 b b b m222 p222
377 c a a a311 d311
378 c a b e312 h312
379 c b a i321 l321
380 c b b m322 p322
381 d a a a411 d411
382 d a b e412 h412
383 d b a i421 l421
384 d b b m422 p422
385 select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
386 a1 a2 b max(c)
387 a a a d111
388 a a b h112
389 a b a l121
390 a b b p122
391 c a a d311
392 c a b h312
393 c b a l321
394 c b b p322
395 d a a d411
396 d a b h412
397 d b a l421
398 d b b p422
399 select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
400 a1 max(c)
401 a d111
402 a h112
403 a l121
404 a p122
405 c d311
406 c h312
407 c l321
408 c p322
409 d d411
410 d h412
411 d l421
412 d p422
413 select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
414 a1 a2 b min(c) max(c)
415 a a a a111 d111
416 a a b e112 h112
417 b a a a211 d211
418 b a b e212 h212
419 c a a a311 d311
420 c a b e312 h312
421 c b a i321 l321
422 c b b m322 p322
423 d a a a411 d411
424 d a b e412 h412
425 d b a i421 l421
426 d b b m422 p422
427 select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
428 a1 a2 b max(c)
429 b a a d211
430 b a b h212
431 b b a l221
432 b b b p222
433 d a a d411
434 d a b h412
435 d b a l421
436 d b b p422
437 select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
438 a1 a2 b min(c) max(c)
439 b a a a211 d211
440 b a b e212 h212
441 b b a i221 l221
442 b b b m222 p222
443 d a a a411 d411
444 d a b e412 h412
445 d b a i421 l421
446 d b b m422 p422
447 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;
448 a1 a2 b max(c)
449 a b a l121
450 a b b p122
451 b b a l221
452 b b b p222
453 c b a l321
454 c b b p322
455 d b a l421
456 d b b p422
457 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;
458 a1 a2 b min(c) max(c)
459 a b a i121 l121
460 a b b m122 p122
461 b b a i221 l221
462 b b b m222 p222
463 c b a i321 l321
464 c b b m322 p322
465 d b a i421 l421
466 d b b m422 p422
467 select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
468 a1 min(c) max(c)
469 b a211 d211
470 b e212 h212
471 b i221 l221
472 b m222 p222
473 c a311 d311
474 c e312 h312
475 c i321 l321
476 c m322 p322
477 d a411 d411
478 d e412 h412
479 d i421 l421
480 d m422 p422
481 select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b;
482 a1 max(c)
483 a d111
484 a h112
485 a l121
486 a p122
487 b d211
488 b h212
489 b l221
490 b p222
491 d d411
492 d h412
493 d l421
494 d p422
495 select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b;
496 a1 a2 b max(c)
497 a a NULL a999
498 a a a d111
499 a a b h112
500 a b a l121
501 a b b p122
502 b a a d211
503 b a b h212
504 b b a l221
505 b b b p222
506 c a NULL c999
507 c a a d311
508 c a b h312
509 c b a l321
510 c b b p322
511 select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
512 a1 a2 b min(c) max(c)
513 a a NULL a777 a999
514 a a a a111 d111
515 a a b e112 h112
516 a b a i121 l121
517 a b b m122 p122
518 b a a a211 d211
519 b a b e212 h212
520 b b a i221 l221
521 b b b m222 p222
522 c a NULL c777 c999
523 c a a a311 d311
524 c a b e312 h312
525 c b a i321 l321
526 c b b m322 p322
527 select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
528 a1 a2 b min(c) max(c)
529 b a a a211 d211
530 b a b e212 h212
531 b b a i221 l221
532 b b b m222 p222
533 c a NULL c777 c999
534 c a a a311 d311
535 c a b e312 h312
536 c b a i321 l321
537 c b b m322 p322
538 d a a a411 d411
539 d a b e412 h412
540 d b a i421 l421
541 d b b m422 p422
542 e a a NULL NULL
543 e a b NULL NULL
544 select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
545 a1 a2 b max(c)
546 a a NULL a999
547 a a a d111
548 a a b h112
549 a b a l121
550 a b b p122
551 c a NULL c999
552 c a a d311
553 c a b h312
554 c b a l321
555 c b b p322
556 d a a d411
557 d a b h412
558 d b a l421
559 d b b p422
560 e a a NULL
561 e a b NULL
562 select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
563 a1 max(c)
564 a a999
565 a d111
566 a h112
567 a l121
568 a p122
569 c c999
570 c d311
571 c h312
572 c l321
573 c p322
574 d d411
575 d h412
576 d l421
577 d p422
578 e NULL
579 e NULL
580 select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
581 a1 a2 b min(c) max(c)
582 a a NULL a777 a999
583 a a a a111 d111
584 a a b e112 h112
585 b a a a211 d211
586 b a b e212 h212
587 c a NULL c777 c999
588 c a a a311 d311
589 c a b e312 h312
590 c b a i321 l321
591 c b b m322 p322
592 d a a a411 d411
593 d a b e412 h412
594 d b a i421 l421
595 d b b m422 p422
596 e a a NULL NULL
597 e a b NULL NULL
598 select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
599 a1 a2 b max(c)
600 b a a d211
601 b a b h212
602 b b a l221
603 b b b p222
604 d a a d411
605 d a b h412
606 d b a l421
607 d b b p422
608 select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
609 a1 a2 b min(c) max(c)
610 b a a a211 d211
611 b a b e212 h212
612 b b a i221 l221
613 b b b m222 p222
614 d a a a411 d411
615 d a b e412 h412
616 d b a i421 l421
617 d b b m422 p422
618 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;
619 a1 a2 b max(c)
620 a b a l121
621 a b b p122
622 b b a l221
623 b b b p222
624 c b a l321
625 c b b p322
626 d b a l421
627 d b b p422
628 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;
629 a1 a2 b min(c) max(c)
630 a b a i121 l121
631 a b b m122 p122
632 b b a i221 l221
633 b b b m222 p222
634 c b a i321 l321
635 c b b m322 p322
636 d b a i421 l421
637 d b b m422 p422
638 select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
639 a1 min(c) max(c)
640 b a211 d211
641 b e212 h212
642 b i221 l221
643 b m222 p222
644 c c777 c999
645 c a311 d311
646 c e312 h312
647 c i321 l321
648 c m322 p322
649 d a411 d411
650 d e412 h412
651 d i421 l421
652 d m422 p422
653 e NULL NULL
654 e NULL NULL
655 select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b;
656 a1 max(c)
657 a a999
658 a d111
659 a h112
660 a l121
661 a p122
662 b d211
663 b h212
664 b l221
665 b p222
666 d d411
667 d h412
668 d l421
669 d p422
670 explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
671 id select_type table type possible_keys key key_len ref rows Extra
672 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 5 Using where; Using index for group-by
673 explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
674 id select_type table type possible_keys key key_len ref rows Extra
675 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 5 Using where; Using index for group-by
676 explain select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2;
677 id select_type table type possible_keys key key_len ref rows Extra
678 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 9 Using where; Using index for group-by
679 explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
680 id select_type table type possible_keys key key_len ref rows Extra
681 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 9 Using where; Using index for group-by
682 explain select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2;
683 id select_type table type possible_keys key key_len ref rows Extra
684 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 9 Using where; Using index for group-by
685 explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
686 id select_type table type possible_keys key key_len ref rows Extra
687 1 SIMPLE t2 range NULL idx_t2_1 163 NULL 5 Using where; Using index for group-by
688 explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
689 id select_type table type possible_keys key key_len ref rows Extra
690 1 SIMPLE t2 range NULL idx_t2_1 163 NULL 5 Using where; Using index for group-by
691 explain select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2;
692 id select_type table type possible_keys key key_len ref rows Extra
693 1 SIMPLE t2 range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by
694 explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
695 id select_type table type possible_keys key key_len ref rows Extra
696 1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by
697 explain select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2;
698 id select_type table type possible_keys key key_len ref rows Extra
699 1 SIMPLE t2 range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by
700 explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
701 id select_type table type possible_keys key key_len ref rows Extra
702 1 SIMPLE t3 range NULL idx_t3_1 6 NULL 4 Using where; Using index for group-by
703 explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
704 id select_type table type possible_keys key key_len ref rows Extra
705 1 SIMPLE t3 range NULL idx_t3_1 6 NULL 4 Using where; Using index for group-by
706 select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
707 a1 a2 b max(c) min(c)
708 a a b h112 e112
709 b a b h212 e212
710 c a b h312 e312
711 d a b h412 e412
712 select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
713 a1 max(c) min(c)
714 a h112 e112
715 b h212 e212
716 c h312 e312
717 d h412 e412
718 select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2;
719 a1 a2 b max(c)
720 a a b h112
721 a b b p122
722 b a b h212
723 b b b p222
724 c a b h312
725 c b b p322
726 d a b h412
727 d b b p422
728 select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
729 a1 a2 b min(c) max(c)
730 a a b e112 h112
731 a b b m122 p122
732 b a b e212 h212
733 b b b m222 p222
734 c a b e312 h312
735 c b b m322 p322
736 d a b e412 h412
737 d b b m422 p422
738 select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2;
739 a1 a2 max(c)
740 a a h112
741 a b p122
742 b a h212
743 b b p222
744 c a h312
745 c b p322
746 d a h412
747 d b p422
748 select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
749 a1 a2 b max(c) min(c)
750 a a b h112 e112
751 b a b h212 e212
752 c a b h312 e312
753 d a b h412 e412
754 e a b NULL NULL
755 select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
756 a1 max(c) min(c)
757 a h112 e112
758 b h212 e212
759 c h312 e312
760 d h412 e412
761 e NULL NULL
762 select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2;
763 a1 a2 b max(c)
764 a a b h112
765 a b b p122
766 b a b h212
767 b b b p222
768 c a b h312
769 c b b p322
770 d a b h412
771 d b b p422
772 e a b NULL
773 select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
774 a1 a2 b min(c) max(c)
775 a a b e112 h112
776 a b b m122 p122
777 b a b e212 h212
778 b b b m222 p222
779 c a b e312 h312
780 c b b m322 p322
781 d a b e412 h412
782 d b b m422 p422
783 e a b NULL NULL
784 select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2;
785 a1 a2 max(c)
786 a a h112
787 a b p122
788 b a h212
789 b b p222
790 c a h312
791 c b p322
792 d a h412
793 d b p422
794 e a NULL
795 select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
796 a1 a2 b max(c) min(c)
797 a a b h112 e112
798 b a b h212 e212
799 c a b h312 e312
800 select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
801 a1 max(c) min(c)
802 a h112 e112
803 b h212 e212
804 c h312 e312
805 explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
806 id select_type table type possible_keys key key_len ref rows Extra
807 1 SIMPLE t2 range NULL idx_t2_1 163 NULL 5 Using where; Using index for group-by
808 explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
809 id select_type table type possible_keys key key_len ref rows Extra
810 1 SIMPLE t2 range NULL idx_t2_1 146 NULL 5 Using where; Using index for group-by
811 explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
812 id select_type table type possible_keys key key_len ref rows Extra
813 1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by
814 explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
815 id select_type table type possible_keys key key_len ref rows Extra
816 1 SIMPLE t2 range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by
817 explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
818 id select_type table type possible_keys key key_len ref rows Extra
819 1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by
820 explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
821 id select_type table type possible_keys key key_len ref rows Extra
822 1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by
823 select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
824 a1 a2 b min(c)
825 a a NULL a777
826 c a NULL c777
827 select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
828 a1 a2 b max(c)
829 a a NULL a999
830 c a NULL c999
831 select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
832 a1 a2 b min(c)
833 a a NULL a777
834 c a NULL c777
835 select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
836 a1 a2 b max(c)
837 a a NULL a999
838 c a NULL c999
839 select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
840 a1 a2 b min(c) max(c)
841 a a NULL a777 a999
842 c a NULL c777 c999
843 select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
844 a1 a2 b min(c) max(c)
845 a a NULL a777 a999
846 c a NULL c777 c999
847 explain select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b;
848 id select_type table type possible_keys key key_len ref rows Extra
849 1 SIMPLE t1 range NULL idx_t1_1 147 NULL # Using where; Using index for group-by
850 explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
851 id select_type table type possible_keys key key_len ref rows Extra
852 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
853 explain select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b;
854 id select_type table type possible_keys key key_len ref rows Extra
855 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by
856 explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
857 id select_type table type possible_keys key key_len ref rows Extra
858 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
859 explain select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b;
860 id select_type table type possible_keys key key_len ref rows Extra
861 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
862 explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
863 id select_type table type possible_keys key key_len ref rows Extra
864 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
865 explain select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b;
866 id select_type table type possible_keys key key_len ref rows Extra
867 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
868 explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
869 id select_type table type possible_keys key key_len ref rows Extra
870 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
871 explain select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
872 id select_type table type possible_keys key key_len ref rows Extra
873 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
874 explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
875 id select_type table type possible_keys key key_len ref rows Extra
876 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
877 explain select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
878 id select_type table type possible_keys key key_len ref rows Extra
879 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
880 explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
881 id select_type table type possible_keys key key_len ref rows Extra
882 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
883 explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
884 id select_type table type possible_keys key key_len ref rows Extra
885 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
886 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;
887 id select_type table type possible_keys key key_len ref rows Extra
888 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
889 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;
890 id select_type table type possible_keys key key_len ref rows Extra
891 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
892 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;
893 id select_type table type possible_keys key key_len ref rows Extra
894 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
895 explain select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b;
896 id select_type table type possible_keys key key_len ref rows Extra
897 1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by
898 explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
899 id select_type table type possible_keys key key_len ref rows Extra
900 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
901 explain select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b;
902 id select_type table type possible_keys key key_len ref rows Extra
903 1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by
904 explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
905 id select_type table type possible_keys key key_len ref rows Extra
906 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
907 explain select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b;
908 id select_type table type possible_keys key key_len ref rows Extra
909 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
910 explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
911 id select_type table type possible_keys key key_len ref rows Extra
912 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
913 explain select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b;
914 id select_type table type possible_keys key key_len ref rows Extra
915 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
916 explain select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
917 id select_type table type possible_keys key key_len ref rows Extra
918 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
919 explain select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
920 id select_type table type possible_keys key key_len ref rows Extra
921 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
922 explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
923 id select_type table type possible_keys key key_len ref rows Extra
924 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
925 explain select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
926 id select_type table type possible_keys key key_len ref rows Extra
927 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
928 explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
929 id select_type table type possible_keys key key_len ref rows Extra
930 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
931 explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
932 id select_type table type possible_keys key key_len ref rows Extra
933 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
934 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;
935 id select_type table type possible_keys key key_len ref rows Extra
936 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
937 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;
938 id select_type table type possible_keys key key_len ref rows Extra
939 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
940 select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b;
941 a1 a2 b max(c)
942 a a a d111
943 a a b h112
944 a b a l121
945 a b b p122
946 b a a d211
947 b a b h212
948 b b a l221
949 b b b p222
950 c a a d311
951 c a b h312
952 c b a l321
953 c b b p322
954 d a a d411
955 d a b h412
956 d b a l421
957 d b b p422
958 select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
959 a1 a2 b min(c) max(c)
960 a a a b111 d111
961 a a b e112 h112
962 a b a i121 l121
963 a b b m122 p122
964 b a a b211 d211
965 b a b e212 h212
966 b b a i221 l221
967 b b b m222 p222
968 c a a b311 d311
969 c a b e312 h312
970 c b a i321 l321
971 c b b m322 p322
972 d a a b411 d411
973 d a b e412 h412
974 d b a i421 l421
975 d b b m422 p422
976 select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b;
977 a1 a2 b max(c)
978 a a b h112
979 a b a l121
980 a b b p122
981 b a b h212
982 b b a l221
983 b b b p222
984 c a b h312
985 c b a l321
986 c b b p322
987 d a b h412
988 d b a l421
989 d b b p422
990 select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
991 a1 a2 b min(c) max(c)
992 a a b g112 h112
993 a b a i121 l121
994 a b b m122 p122
995 b a b f212 h212
996 b b a i221 l221
997 b b b m222 p222
998 c a b f312 h312
999 c b a i321 l321
1000 c b b m322 p322
1001 d a b f412 h412
1002 d b a i421 l421
1003 d b b m422 p422
1004 select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b;
1005 a1 a2 b max(c)
1006 select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
1007 a1 a2 b min(c) max(c)
1008 select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b;
1009 a1 a2 b max(c)
1010 a a a d111
1011 a a b h112
1012 a b a k121
1013 b a a d211
1014 b a b h212
1015 b b a k221
1016 c a a d311
1017 c a b h312
1018 c b a j321
1019 d a a d411
1020 d a b h412
1021 d b a j421
1022 select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
1023 a1 a2 b min(c) max(c)
1024 a a a a111 d111
1025 a a b e112 h112
1026 a b a i121 k121
1027 b a a a211 d211
1028 b a b e212 h212
1029 b b a i221 k221
1030 c a a a311 d311
1031 c a b e312 h312
1032 c b a i321 j321
1033 d a a a411 d411
1034 d a b e412 h412
1035 d b a i421 j421
1036 select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1037 a1 a2 b max(c)
1038 a a a d111
1039 a a b h112
1040 a b a l121
1041 a b b p122
1042 b a a d211
1043 b a b h212
1044 b b a l221
1045 b b b p222
1046 c a a d311
1047 c a b h312
1048 c b a l321
1049 c b b p322
1050 d a a d411
1051 d a b h412
1052 d b a l421
1053 d b b p422
1054 select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1055 a1 a2 b min(c) max(c)
1056 a a a b111 d111
1057 a a b e112 h112
1058 a b a i121 l121
1059 a b b m122 p122
1060 b a a b211 d211
1061 b a b e212 h212
1062 b b a i221 l221
1063 b b b m222 p222
1064 c a a b311 d311
1065 c a b e312 h312
1066 c b a i321 l321
1067 c b b m322 p322
1068 d a a b411 d411
1069 d a b e412 h412
1070 d b a i421 l421
1071 d b b m422 p422
1072 select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1073 a1 a2 b max(c)
1074 a a a d111
1075 a a b h112
1076 a b a l121
1077 a b b p122
1078 b a a d211
1079 b a b h212
1080 b b a l221
1081 b b b p222
1082 c a a d311
1083 c a b h312
1084 c b a l321
1085 c b b p322
1086 d a a d411
1087 d a b h412
1088 d b a l421
1089 d b b p422
1090 select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1091 a1 a2 b min(c) max(c)
1092 a a a a111 d111
1093 a a b e112 h112
1094 a b a i121 l121
1095 a b b m122 p122
1096 b a a a211 d211
1097 b a b e212 h212
1098 b b a i221 l221
1099 b b b m222 p222
1100 c a a a311 d311
1101 c a b e312 h312
1102 c b a i321 l321
1103 c b b m322 p322
1104 d a a a411 d411
1105 d a b e412 h412
1106 d b a i421 l421
1107 d b b m422 p422
1108 select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
1109 a1 a2 b min(c) max(c)
1110 a a a c111 d111
1111 a a b e112 g112
1112 b a a b211 d211
1113 b a b e212 f212
1114 c a a b311 d311
1115 c a b e312 f312
1116 d a a b411 d411
1117 d a b e412 f412
1118 select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
1119 a1 a2 b min(c) max(c)
1120 a a a a111 c111
1121 b a a a211 c211
1122 c a a a311 c311
1123 d a a a411 c411
1124 d a b g412 g412
1125 d b a k421 k421
1126 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;
1127 a1 a2 b min(c) max(c)
1128 a a a c111 d111
1129 a a b e112 h112
1130 b a a b211 d211
1131 b a b e212 h212
1132 c a a b311 d311
1133 c a b e312 h312
1134 d a a b411 d411
1135 d a b e412 h412
1136 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;
1137 a1 a2 b min(c) max(c)
1138 a a a b111 d111
1139 a a b e112 h112
1140 b a a b211 d211
1141 b a b e212 h212
1142 c a a b311 d311
1143 c a b e312 h312
1144 d a a b411 d411
1145 d a b e412 h412
1146 select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b;
1147 a1 a2 b max(c)
1148 a a a d111
1149 a a b h112
1150 a b a l121
1151 a b b p122
1152 b a a d211
1153 b a b h212
1154 b b a l221
1155 b b b p222
1156 c a NULL c999
1157 c a a d311
1158 c a b h312
1159 c b a l321
1160 c b b p322
1161 d a a d411
1162 d a b h412
1163 d b a l421
1164 d b b p422
1165 select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
1166 a1 a2 b min(c) max(c)
1167 a a a b111 d111
1168 a a b e112 h112
1169 a b a i121 l121
1170 a b b m122 p122
1171 b a a b211 d211
1172 b a b e212 h212
1173 b b a i221 l221
1174 b b b m222 p222
1175 c a NULL c777 c999
1176 c a a b311 d311
1177 c a b e312 h312
1178 c b a i321 l321
1179 c b b m322 p322
1180 d a a b411 d411
1181 d a b e412 h412
1182 d b a i421 l421
1183 d b b m422 p422
1184 select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b;
1185 a1 a2 b max(c)
1186 a a b h112
1187 a b a l121
1188 a b b p122
1189 b a b h212
1190 b b a l221
1191 b b b p222
1192 c a b h312
1193 c b a l321
1194 c b b p322
1195 d a b h412
1196 d b a l421
1197 d b b p422
1198 select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
1199 a1 a2 b min(c) max(c)
1200 a a b g112 h112
1201 a b a i121 l121
1202 a b b m122 p122
1203 b a b f212 h212
1204 b b a i221 l221
1205 b b b m222 p222
1206 c a b f312 h312
1207 c b a i321 l321
1208 c b b m322 p322
1209 d a b f412 h412
1210 d b a i421 l421
1211 d b b m422 p422
1212 select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b;
1213 a1 a2 b max(c)
1214 select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
1215 a1 a2 b min(c) max(c)
1216 select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b;
1217 a1 a2 b max(c)
1218 a a NULL a999
1219 a a a d111
1220 a a b h112
1221 a b a k121
1222 b a a d211
1223 b a b h212
1224 b b a k221
1225 c a NULL c999
1226 c a a d311
1227 c a b h312
1228 c b a j321
1229 d a a d411
1230 d a b h412
1231 d b a j421
1232 select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
1233 a1 a2 b min(c) max(c)
1234 a a NULL a777 a999
1235 a a a a111 d111
1236 a a b e112 h112
1237 a b a i121 k121
1238 b a a a211 d211
1239 b a b e212 h212
1240 b b a i221 k221
1241 c a NULL c777 c999
1242 c a a a311 d311
1243 c a b e312 h312
1244 c b a i321 j321
1245 d a a a411 d411
1246 d a b e412 h412
1247 d b a i421 j421
1248 select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1249 a1 a2 b max(c)
1250 a a a d111
1251 a a b h112
1252 a b a l121
1253 a b b p122
1254 b a a d211
1255 b a b h212
1256 b b a l221
1257 b b b p222
1258 c a NULL c999
1259 c a a d311
1260 c a b h312
1261 c b a l321
1262 c b b p322
1263 d a a d411
1264 d a b h412
1265 d b a l421
1266 d b b p422
1267 select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
1268 a1 a2 b min(c) max(c)
1269 a a a b111 d111
1270 a a b e112 h112
1271 a b a i121 l121
1272 a b b m122 p122
1273 b a a b211 d211
1274 b a b e212 h212
1275 b b a i221 l221
1276 b b b m222 p222
1277 c a NULL c777 c999
1278 c a a b311 d311
1279 c a b e312 h312
1280 c b a i321 l321
1281 c b b m322 p322
1282 d a a b411 d411
1283 d a b e412 h412
1284 d b a i421 l421
1285 d b b m422 p422
1286 select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1287 a1 a2 b max(c)
1288 a a NULL a999
1289 a a a d111
1290 a a b h112
1291 a b a l121
1292 a b b p122
1293 b a a d211
1294 b a b h212
1295 b b a l221
1296 b b b p222
1297 c a NULL c999
1298 c a a d311
1299 c a b h312
1300 c b a l321
1301 c b b p322
1302 d a a d411
1303 d a b h412
1304 d b a l421
1305 d b b p422
1306 select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
1307 a1 a2 b min(c) max(c)
1308 a a NULL a777 a999
1309 a a a a111 d111
1310 a a b e112 h112
1311 a b a i121 l121
1312 a b b m122 p122
1313 b a a a211 d211
1314 b a b e212 h212
1315 b b a i221 l221
1316 b b b m222 p222
1317 c a NULL c777 c999
1318 c a a a311 d311
1319 c a b e312 h312
1320 c b a i321 l321
1321 c b b m322 p322
1322 d a a a411 d411
1323 d a b e412 h412
1324 d b a i421 l421
1325 d b b m422 p422
1326 select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
1327 a1 a2 b min(c) max(c)
1328 a a a c111 d111
1329 a a b e112 g112
1330 b a a b211 d211
1331 b a b e212 f212
1332 c a NULL c777 c999
1333 c a a b311 d311
1334 c a b e312 f312
1335 d a a b411 d411
1336 d a b e412 f412
1337 select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
1338 a1 a2 b min(c) max(c)
1339 a a NULL a777 a999
1340 a a a a111 c111
1341 b a a a211 c211
1342 c a a a311 c311
1343 d a a a411 c411
1344 d a b g412 g412
1345 d b a k421 k421
1346 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;
1347 a1 a2 b min(c) max(c)
1348 a a a c111 d111
1349 a a b e112 h112
1350 b a a b211 d211
1351 b a b e212 h212
1352 c a NULL c777 c999
1353 c a a b311 d311
1354 c a b e312 h312
1355 d a a b411 d411
1356 d a b e412 h412
1357 explain select a1,a2,b,min(c),max(c) from t1
1358 where exists ( select * from t2 where t2.c = t1.c )
1359 group by a1,a2,b;
1360 id select_type table type possible_keys key key_len ref rows Extra
1361 1 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index
1362 2 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
1363 explain select a1,a2,b,min(c),max(c) from t1
1364 where exists ( select * from t2 where t2.c > 'b1' )
1365 group by a1,a2,b;
1366 id select_type table type possible_keys key key_len ref rows Extra
1367 1 PRIMARY t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by
1368 2 SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
1369 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;
1370 id select_type table type possible_keys key key_len ref rows Extra
1371 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by
1372 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;
1373 id select_type table type possible_keys key key_len ref rows Extra
1374 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 17 Using where; Using index for group-by
1375 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;
1376 id select_type table type possible_keys key key_len ref rows Extra
1377 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
1378 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;
1379 id select_type table type possible_keys key key_len ref rows Extra
1380 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 14 Using where; Using index for group-by
1381 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;
1382 id select_type table type possible_keys key key_len ref rows Extra
1383 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 14 Using where; Using index for group-by
1384 explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1385 id select_type table type possible_keys key key_len ref rows Extra
1386 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
1387 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;
1388 id select_type table type possible_keys key key_len ref rows Extra
1389 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by
1390 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;
1391 id select_type table type possible_keys key key_len ref rows Extra
1392 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
1393 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;
1394 id select_type table type possible_keys key key_len ref rows Extra
1395 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
1396 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;
1397 id select_type table type possible_keys key key_len ref rows Extra
1398 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
1399 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;
1400 id select_type table type possible_keys key key_len ref rows Extra
1401 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
1402 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;
1403 id select_type table type possible_keys key key_len ref rows Extra
1404 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
1405 explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1406 id select_type table type possible_keys key key_len ref rows Extra
1407 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by
1408 select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1409 a1 a2 b min(c) max(c)
1410 a a b e112 h112
1411 b a b e212 h212
1412 c a b e312 h312
1413 c b b m322 p322
1414 d a b e412 h412
1415 d b b m422 p422
1416 select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
1417 a1 a2 b min(c) max(c)
1418 a a a c111 d111
1419 a a b e112 h112
1420 b a a b211 d211
1421 b a b e212 h212
1422 c a a b311 d311
1423 c a b e312 h312
1424 c b a i321 l321
1425 c b b m322 p322
1426 d a a b411 d411
1427 d a b e412 h412
1428 d b a i421 l421
1429 d b b m422 p422
1430 select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
1431 a1 a2 b min(c) max(c)
1432 a b a i121 l121
1433 b b a i221 l221
1434 c b a i321 l321
1435 d b a i421 l421
1436 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;
1437 a1 a2 b min(c)
1438 b b a k221
1439 c b a k321
1440 d b a k421
1441 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;
1442 a1 a2 b min(c)
1443 b b a k221
1444 c b a k321
1445 d b a k421
1446 select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1447 a1 a2 b min(c)
1448 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;
1449 a1 a2 b min(c)
1450 select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1451 a1 a2 b min(c) max(c)
1452 a a b e112 h112
1453 b a b e212 h212
1454 c a b e312 h312
1455 c b b m322 p322
1456 d a b e412 h412
1457 d b b m422 p422
1458 e a b NULL NULL
1459 select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
1460 a1 a2 b min(c) max(c)
1461 a a a c111 d111
1462 a a b e112 h112
1463 b a a b211 d211
1464 b a b e212 h212
1465 c a NULL c777 c999
1466 c a a b311 d311
1467 c a b e312 h312
1468 c b a i321 l321
1469 c b b m322 p322
1470 d a a b411 d411
1471 d a b e412 h412
1472 d b a i421 l421
1473 d b b m422 p422
1474 select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
1475 a1 a2 b min(c) max(c)
1476 a b a i121 l121
1477 b b a i221 l221
1478 c b a i321 l321
1479 d b a i421 l421
1480 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;
1481 a1 a2 b min(c)
1482 b b a k221
1483 c b a k321
1484 d b a k421
1485 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;
1486 a1 a2 b min(c)
1487 b b a k221
1488 c b a k321
1489 d b a k421
1490 select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1491 a1 a2 b min(c)
1492 explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1493 id select_type table type possible_keys key key_len ref rows Extra
1494 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by
1495 explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1496 id select_type table type possible_keys key key_len ref rows Extra
1497 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by
1498 explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1499 id select_type table type possible_keys key key_len ref rows Extra
1500 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
1501 explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1502 id select_type table type possible_keys key key_len ref rows Extra
1503 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
1504 explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1505 id select_type table type possible_keys key key_len ref rows Extra
1506 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by
1507 explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1508 id select_type table type possible_keys key key_len ref rows Extra
1509 1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by
1510 explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1511 id select_type table type possible_keys key key_len ref rows Extra
1512 1 SIMPLE t2 index NULL idx_t2_1 163 NULL # Using where; Using index
1513 explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1514 id select_type table type possible_keys key key_len ref rows Extra
1515 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by
1516 select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1517 a1 a2 b
1518 a a b
1519 b a b
1520 c a b
1521 c b b
1522 d a b
1523 d b b
1524 select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1525 a1 a2 b
1526 a b a
1527 b b a
1528 c b a
1529 d b a
1530 select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1531 a1 a2 b c
1532 a b a i121
1533 select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1534 a1 a2 b
1535 select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
1536 a1 a2 b
1537 a a b
1538 b a b
1539 c a b
1540 c b b
1541 d a b
1542 d b b
1543 e a b
1544 select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1545 a1 a2 b
1546 a b a
1547 b b a
1548 c b a
1549 d b a
1550 select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1551 a1 a2 b c
1552 a b a i121
1553 select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1554 a1 a2 b
1555 explain select distinct a1,a2,b from t1;
1556 id select_type table type possible_keys key key_len ref rows Extra
1557 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by
1558 explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
1559 id select_type table type possible_keys key key_len ref rows Extra
1560 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by
1561 explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1562 id select_type table type possible_keys key key_len ref rows filtered Extra
1563 1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 50.78 Using where; Using index
1564 Warnings:
1565 Note 1003 select distinct `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`c` = 'i121') and (`test`.`t1`.`b` = 'a') and (`test`.`t1`.`a2` >= 'b'))
1566 explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1567 id select_type table type possible_keys key key_len ref rows Extra
1568 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
1569 explain select distinct b from t1 where (a2 >= 'b') and (b = 'a');
1570 id select_type table type possible_keys key key_len ref rows Extra
1571 1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index
1572 explain select distinct a1,a2,b from t2;
1573 id select_type table type possible_keys key key_len ref rows Extra
1574 1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using index for group-by
1575 explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
1576 id select_type table type possible_keys key key_len ref rows Extra
1577 1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by
1578 explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1579 id select_type table type possible_keys key key_len ref rows filtered Extra
1580 1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 50.61 Using where; Using index
1581 Warnings:
1582 Note 1003 select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = 'i121') and (`test`.`t2`.`b` = 'a') and (`test`.`t2`.`a2` >= 'b'))
1583 explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1584 id select_type table type possible_keys key key_len ref rows Extra
1585 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by
1586 explain select distinct b from t2 where (a2 >= 'b') and (b = 'a');
1587 id select_type table type possible_keys key key_len ref rows Extra
1588 1 SIMPLE t2 index NULL idx_t2_2 146 NULL 164 Using where; Using index
1589 select distinct a1,a2,b from t1;
1590 a1 a2 b
1591 a a a
1592 a a b
1593 a b a
1594 a b b
1595 b a a
1596 b a b
1597 b b a
1598 b b b
1599 c a a
1600 c a b
1601 c b a
1602 c b b
1603 d a a
1604 d a b
1605 d b a
1606 d b b
1607 select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
1608 a1 a2 b
1609 a b a
1610 b b a
1611 c b a
1612 d b a
1613 select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1614 a1 a2 b c
1615 a b a i121
1616 select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1617 a1 a2 b
1618 select distinct b from t1 where (a2 >= 'b') and (b = 'a');
1619 b
1620 a
1621 select distinct a1,a2,b from t2;
1622 a1 a2 b
1623 a a NULL
1624 a a a
1625 a a b
1626 a b a
1627 a b b
1628 b a a
1629 b a b
1630 b b a
1631 b b b
1632 c a NULL
1633 c a a
1634 c a b
1635 c b a
1636 c b b
1637 d a a
1638 d a b
1639 d b a
1640 d b b
1641 e a a
1642 e a b
1643 select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
1644 a1 a2 b
1645 a b a
1646 b b a
1647 c b a
1648 d b a
1649 select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1650 a1 a2 b c
1651 a b a i121
1652 select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1653 a1 a2 b
1654 select distinct b from t2 where (a2 >= 'b') and (b = 'a');
1655 b
1656 a
1657 select distinct t_00.a1
1658 from t1 t_00
1659 where exists ( select * from t2 where a1 = t_00.a1 );
1660 a1
1661 a
1662 b
1663 c
1664 d
1665 select distinct a1,a1 from t1;
1666 a1 a1
1667 a a
1668 b b
1669 c c
1670 d d
1671 select distinct a2,a1,a2,a1 from t1;
1672 a2 a1 a2 a1
1673 a a a a
1674 b a b a
1675 a b a b
1676 b b b b
1677 a c a c
1678 b c b c
1679 a d a d
1680 b d b d
1681 select distinct t1.a1,t2.a1 from t1,t2;
1682 a1 a1
1683 a a
1684 b a
1685 c a
1686 d a
1687 a b
1688 b b
1689 c b
1690 d b
1691 a c
1692 b c
1693 c c
1694 d c
1695 a d
1696 b d
1697 c d
1698 d d
1699 a e
1700 b e
1701 c e
1702 d e
1703 explain select distinct a1,a2,b from t1;
1704 id select_type table type possible_keys key key_len ref rows Extra
1705 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by
1706 explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1707 id select_type table type possible_keys key key_len ref rows Extra
1708 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by
1709 explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1710 id select_type table type possible_keys key key_len ref rows Extra
1711 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
1712 explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1713 id select_type table type possible_keys key key_len ref rows Extra
1714 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
1715 explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1716 id select_type table type possible_keys key key_len ref rows Extra
1717 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by; Using temporary; Using filesort
1718 explain select distinct a1,a2,b from t2;
1719 id select_type table type possible_keys key key_len ref rows Extra
1720 1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using index for group-by
1721 explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1722 id select_type table type possible_keys key key_len ref rows Extra
1723 1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by
1724 explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1725 id select_type table type possible_keys key key_len ref rows Extra
1726 1 SIMPLE t2 index NULL idx_t2_1 163 NULL # Using where; Using index
1727 explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1728 id select_type table type possible_keys key key_len ref rows Extra
1729 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by
1730 explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1731 id select_type table type possible_keys key key_len ref rows Extra
1732 1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by; Using temporary; Using filesort
1733 select distinct a1,a2,b from t1;
1734 a1 a2 b
1735 a a a
1736 a a b
1737 a b a
1738 a b b
1739 b a a
1740 b a b
1741 b b a
1742 b b b
1743 c a a
1744 c a b
1745 c b a
1746 c b b
1747 d a a
1748 d a b
1749 d b a
1750 d b b
1751 select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1752 a1 a2 b
1753 a b a
1754 b b a
1755 c b a
1756 d b a
1757 select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1758 a1 a2 b c
1759 a b a i121
1760 select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1761 a1 a2 b
1762 select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1763 b
1764 a
1765 select distinct a1,a2,b from t2;
1766 a1 a2 b
1767 a a NULL
1768 a a a
1769 a a b
1770 a b a
1771 a b b
1772 b a a
1773 b a b
1774 b b a
1775 b b b
1776 c a NULL
1777 c a a
1778 c a b
1779 c b a
1780 c b b
1781 d a a
1782 d a b
1783 d b a
1784 d b b
1785 e a a
1786 e a b
1787 select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1788 a1 a2 b
1789 a b a
1790 b b a
1791 c b a
1792 d b a
1793 select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
1794 a1 a2 b c
1795 a b a i121
1796 select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
1797 a1 a2 b
1798 select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
1799 b
1800 a
1801 explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
1802 id select_type table type possible_keys key key_len ref rows Extra
1803 1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index
1804 explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1805 id select_type table type possible_keys key key_len ref rows Extra
1806 1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index
1807 explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1808 id select_type table type possible_keys key key_len ref rows filtered Extra
1809 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index
1810 Warnings:
1811 Note 1003 select count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`) AS `count(distinct a1,a2,b)` from `test`.`t1` where ((`test`.`t1`.`b` = 'c') and (`test`.`t1`.`a1` > 'a') and (`test`.`t1`.`a2` > 'a'))
1812 explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
1813 id select_type table type possible_keys key key_len ref rows Extra
1814 1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index
1815 explain extended select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
1816 id select_type table type possible_keys key key_len ref rows filtered Extra
1817 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index
1818 Warnings:
1819 Note 1003 select (ord(`test`.`t1`.`a1`) + count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`)) AS `ord(a1) + count(distinct a1,a2,b)` from `test`.`t1` where ((`test`.`t1`.`a1` > 'a') and (`test`.`t1`.`a2` > 'a'))
1820 select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
1821 count(distinct a1,a2,b)
1822 4
1823 select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
1824 count(distinct a1,a2,b,c)
1825 1
1826 select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
1827 count(distinct a1,a2,b)
1828 0
1829 select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
1830 count(distinct b)
1831 1
1832 select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
1833 ord(a1) + count(distinct a1,a2,b)
1834 104
1835 explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
1836 id select_type table type possible_keys key key_len ref rows Extra
1837 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
1838 explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
1839 id select_type table type possible_keys key key_len ref rows Extra
1840 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
1841 explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
1842 id select_type table type possible_keys key key_len ref rows Extra
1843 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
1844 explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
1845 id select_type table type possible_keys key key_len ref rows Extra
1846 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
1847 explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
1848 id select_type table type possible_keys key key_len ref rows Extra
1849 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 9 Using index for group-by
1850 select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
1851 a1 a2 b concat(min(c), max(c))
1852 a a a a111d111
1853 a a b e112h112
1854 a b a i121l121
1855 a b b m122p122
1856 b a a a211d211
1857 b a b e212h212
1858 b b a i221l221
1859 b b b m222p222
1860 c a a a311d311
1861 c a b e312h312
1862 c b a i321l321
1863 c b b m322p322
1864 select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
1865 concat(a1,min(c)) b
1866 aa111 a
1867 ae112 b
1868 ai121 a
1869 am122 b
1870 ba211 a
1871 be212 b
1872 bi221 a
1873 bm222 b
1874 ca311 a
1875 ce312 b
1876 ci321 a
1877 cm322 b
1878 select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
1879 concat(a1,min(c)) b max(c)
1880 aa111 a d111
1881 ae112 b h112
1882 ai121 a l121
1883 am122 b p122
1884 ba211 a d211
1885 be212 b h212
1886 bi221 a l221
1887 bm222 b p222
1888 ca311 a d311
1889 ce312 b h312
1890 ci321 a l321
1891 cm322 b p322
1892 select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
1893 concat(a1,a2) b min(c) max(c)
1894 aa a a111 d111
1895 aa b e112 h112
1896 ab a i121 l121
1897 ab b m122 p122
1898 ba a a211 d211
1899 ba b e212 h212
1900 bb a i221 l221
1901 bb b m222 p222
1902 ca a a311 d311
1903 ca b e312 h312
1904 cb a i321 l321
1905 cb b m322 p322
1906 select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
1907 concat(ord(min(b)),ord(max(b))) min(b) max(b)
1908 9798 a b
1909 9798 a b
1910 9798 a b
1911 9798 a b
1912 9798 a b
1913 9798 a b
1914 9798 a b
1915 9798 a b
1916 explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b;
1917 id select_type table type possible_keys key key_len ref rows Extra
1918 1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort
1919 explain select a1,a2,b,d from t1 group by a1,a2,b;
1920 id select_type table type possible_keys key key_len ref rows Extra
1921 1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort
1922 explain extended select a1,a2,min(b),max(b) from t1
1923 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;
1924 id select_type table type possible_keys key key_len ref rows filtered Extra
1925 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 76 85.53 Using where; Using index
1926 Warnings:
1927 Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,min(`test`.`t1`.`b`) AS `min(b)`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`c` > 'a111')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`
1928 explain extended select a1,a2,b,min(c),max(c) from t1
1929 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
1930 id select_type table type possible_keys key key_len ref rows filtered Extra
1931 1 SIMPLE t1 ALL idx_t1_0,idx_t1_1,idx_t1_2 NULL NULL NULL 128 50.78 Using where; Using temporary; Using filesort
1932 Warnings:
1933 Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,min(`test`.`t1`.`c`) AS `min(c)`,max(`test`.`t1`.`c`) AS `max(c)` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`d` > 'xy2')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
1934 explain extended select a1,a2,b,c from t1
1935 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
1936 id select_type table type possible_keys key key_len ref rows filtered Extra
1937 1 SIMPLE t1 ALL idx_t1_0,idx_t1_1,idx_t1_2 NULL NULL NULL 128 50.78 Using where; Using temporary; Using filesort
1938 Warnings:
1939 Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`d` > 'xy2')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`,`test`.`t1`.`c`
1940 explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
1941 id select_type table type possible_keys key key_len ref rows Extra
1942 1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
1943 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;
1944 id select_type table type possible_keys key key_len ref rows filtered Extra
1945 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 76 85.53 Using where; Using index
1946 Warnings:
1947 Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`c` > 'a111')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
1948 explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
1949 id select_type table type possible_keys key key_len ref rows Extra
1950 1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
1951 select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
1952 a1 a2 min(b) c
1953 a a a a111
1954 explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1;
1955 id select_type table type possible_keys key key_len ref rows Extra
1956 1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
1957 explain select a1,a2,b,min(c),max(c) from t2
1958 where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b;
1959 id select_type table type possible_keys key key_len ref rows Extra
1960 1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
1961 explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c;
1962 id select_type table type possible_keys key key_len ref rows Extra
1963 1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort
1964 explain select a1,a2,count(a2) from t1 group by a1,a2,b;
1965 id select_type table type possible_keys key key_len ref rows Extra
1966 1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using index
1967 explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
1968 id select_type table type possible_keys key key_len ref rows filtered Extra
1969 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index
1970 Warnings:
1971 Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where (`test`.`t1`.`a1` > 'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
1972 explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
1973 id select_type table type possible_keys key key_len ref rows filtered Extra
1974 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index
1975 Warnings:
1976 Note 1003 select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where (`test`.`t1`.`a1` > 'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
1977 explain select distinct(a1) from t1 where ord(a2) = 98;
1978 id select_type table type possible_keys key key_len ref rows Extra
1979 1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index
1980 select distinct(a1) from t1 where ord(a2) = 98;
1981 a1
1982 a
1983 b
1984 c
1985 d
1986 explain select a1 from t1 where a2 = 'b' group by a1;
1987 id select_type table type possible_keys key key_len ref rows Extra
1988 1 SIMPLE t1 range NULL idx_t1_1 130 NULL 5 Using where; Using index for group-by
1989 select a1 from t1 where a2 = 'b' group by a1;
1990 a1
1991 a
1992 b
1993 c
1994 d
1995 explain select distinct a1 from t1 where a2 = 'b';
1996 id select_type table type possible_keys key key_len ref rows Extra
1997 1 SIMPLE t1 range NULL idx_t1_1 130 NULL 5 Using where; Using index for group-by
1998 select distinct a1 from t1 where a2 = 'b';
1999 a1
2000 a
2001 b
2002 c
2003 d
2004 drop table t1,t2,t3;
2005 create table t1 (c1 int not null,c2 int not null, primary key(c1,c2));
2006 insert into t1 (c1,c2) values
2007 (10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9);
2008 select distinct c1, c2 from t1 order by c2;
2009 c1 c2
2010 10 1
2011 10 2
2012 10 3
2013 20 4
2014 20 5
2015 20 6
2016 30 7
2017 30 8
2018 30 9
2019 select c1,min(c2) as c2 from t1 group by c1 order by c2;
2020 c1 c2
2021 10 1
2022 20 4
2023 30 7
2024 select c1,c2 from t1 group by c1,c2 order by c2;
2025 c1 c2
2026 10 1
2027 10 2
2028 10 3
2029 20 4
2030 20 5
2031 20 6
2032 30 7
2033 30 8
2034 30 9
2035 drop table t1;
2036 CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b));
2037 INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4);
2038 OPTIMIZE TABLE t1;
2039 Table Op Msg_type Msg_text
2040 test.t1 optimize status OK
2041 SELECT a FROM t1 WHERE a='AA' GROUP BY a;
2042 a
2043 AA
2044 SELECT a FROM t1 WHERE a='BB' GROUP BY a;
2045 a
2046 BB
2047 EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a;
2048 id select_type table type possible_keys key key_len ref rows Extra
2049 1 SIMPLE t1 ref PRIMARY PRIMARY 7 const 3 Using where; Using index
2050 EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a;
2051 id select_type table type possible_keys key key_len ref rows Extra
2052 1 SIMPLE t1 ref PRIMARY PRIMARY 7 const 1 Using where; Using index
2053 SELECT DISTINCT a FROM t1 WHERE a='BB';
2054 a
2055 BB
2056 SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%';
2057 a
2058 BB
2059 SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a;
2060 a
2061 BB
2062 DROP TABLE t1;
2063 CREATE TABLE t1 (
2064 a int(11) NOT NULL DEFAULT '0',
2065 b varchar(16) COLLATE latin1_general_ci NOT NULL DEFAULT '',
2066 PRIMARY KEY (a,b)
2067 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
2068 CREATE PROCEDURE a(x INT)
2069 BEGIN
2070 DECLARE rnd INT;
2071 DECLARE cnt INT;
2072 WHILE x > 0 DO
2073 SET rnd= x % 100;
2074 SET cnt = (SELECT COUNT(*) FROM t1 WHERE a = rnd);
2075 INSERT INTO t1(a,b) VALUES (rnd, CAST(cnt AS CHAR));
2076 SET x= x - 1;
2077 END WHILE;
2078 END|
2079 CALL a(1000);
2080 SELECT a FROM t1 WHERE a=0;
2081 a
2082 0
2083 0
2084 0
2085 0
2086 0
2087 0
2088 0
2089 0
2090 0
2091 0
2092 SELECT DISTINCT a FROM t1 WHERE a=0;
2093 a
2094 0
2095 SELECT COUNT(DISTINCT a) FROM t1 WHERE a=0;
2096 COUNT(DISTINCT a)
2097 1
2098 DROP TABLE t1;
2099 DROP PROCEDURE a;
2100 CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a));
2101 INSERT INTO t1 (a) VALUES
2102 (''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'),
2103 ('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'),
2104 ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN');
2105 EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a;
2106 id select_type table type possible_keys key key_len ref rows Extra
2107 1 SIMPLE t1 range NULL PRIMARY 66 NULL 12 Using index for group-by
2108 SELECT DISTINCT a,a FROM t1 ORDER BY a;
2109 a a
2111 CENTRAL CENTRAL
2112 EASTERN EASTERN
2113 GREATER LONDON GREATER LONDON
2114 NORTH CENTRAL NORTH CENTRAL
2115 NORTH EAST NORTH EAST
2116 NORTH WEST NORTH WEST
2117 SCOTLAND SCOTLAND
2118 SOUTH EAST SOUTH EAST
2119 SOUTH WEST SOUTH WEST
2120 WESTERN WESTERN
2121 DROP TABLE t1;
2122 CREATE TABLE t1 (id1 INT, id2 INT);
2123 CREATE TABLE t2 (id2 INT, id3 INT, id5 INT);
2124 CREATE TABLE t3 (id3 INT, id4 INT);
2125 CREATE TABLE t4 (id4 INT);
2126 CREATE TABLE t5 (id5 INT, id6 INT);
2127 CREATE TABLE t6 (id6 INT);
2128 INSERT INTO t1 VALUES(1,1);
2129 INSERT INTO t2 VALUES(1,1,1);
2130 INSERT INTO t3 VALUES(1,1);
2131 INSERT INTO t4 VALUES(1);
2132 INSERT INTO t5 VALUES(1,1);
2133 INSERT INTO t6 VALUES(1);
2134 SELECT * FROM
2135 t1
2136 NATURAL JOIN
2137 (t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
2138 ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
2139 id2 id1 id3 id5 id4 id3 id6 id5
2140 1 1 1 1 1 1 1 1
2141 SELECT * FROM
2142 t1
2143 NATURAL JOIN
2144 (((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2
2145 ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
2146 id2 id1 id4 id3 id6 id5 id3 id5
2147 1 1 1 1 1 1 1 1
2148 SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2);
2149 id2 id1 id3 id4 id6 id5 id3 id5
2150 1 1 1 1 1 1 1 1
2151 SELECT * FROM
2152 (t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
2153 ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5))
2154 NATURAL JOIN
2155 t1;
2156 id2 id3 id5 id4 id3 id6 id5 id1
2157 1 1 1 1 1 1 1 1
2158 SELECT * FROM
2159 (t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6)))
2160 NATURAL JOIN
2161 t1;
2162 id2 id3 id5 id4 id3 id6 id5 id1
2163 1 1 1 1 1 1 1 1
2164 DROP TABLE t1,t2,t3,t4,t5,t6;
2165 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
2166 INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
2167 explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
2168 id select_type table type possible_keys key key_len ref rows Extra
2169 1 SIMPLE t1 range PRIMARY,b PRIMARY 8 NULL 1 Using where; Using index for group-by
2170 SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
2171 MAX(b) a
2172 1 1
2173 SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
2174 MIN(b) a
2175 2 1
2176 CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
2177 INSERT INTO t2 SELECT a,b,b FROM t1;
2178 explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
2179 id select_type table type possible_keys key key_len ref rows Extra
2180 1 SIMPLE t2 range PRIMARY PRIMARY 12 NULL 1 Using where; Using index for group-by
2181 SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
2182 MIN(c)
2183 2
2184 DROP TABLE t1,t2;
2185 CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
2186 INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
2187 (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
2188 EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
2189 id select_type table type possible_keys key key_len ref rows Extra
2190 1 SIMPLE t1 range NULL a 5 NULL 8 Using index for group-by
2191 FLUSH STATUS;
2192 SELECT max(b), a FROM t1 GROUP BY a;
2193 max(b) a
2194 5 1
2195 3 2
2196 1 3
2197 6 4
2198 SHOW STATUS LIKE 'handler_read__e%';
2199 Variable_name Value
2200 Handler_read_key 8
2201 Handler_read_next 0
2202 EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
2203 id select_type table type possible_keys key key_len ref rows Extra
2204 1 SIMPLE t1 range NULL a 5 NULL 8 Using index for group-by
2205 FLUSH STATUS;
2206 CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a;
2207 SHOW STATUS LIKE 'handler_read__e%';
2208 Variable_name Value
2209 Handler_read_key 8
2210 Handler_read_next 0
2211 FLUSH STATUS;
2212 SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
2213 max(b) a
2214 5 1
2215 3 2
2216 1 3
2217 6 4
2218 SHOW STATUS LIKE 'handler_read__e%';
2219 Variable_name Value
2220 Handler_read_key 8
2221 Handler_read_next 0
2222 FLUSH STATUS;
2223 (SELECT max(b), a FROM t1 GROUP BY a) UNION
2224 (SELECT max(b), a FROM t1 GROUP BY a);
2225 max(b) a
2226 5 1
2227 3 2
2228 1 3
2229 6 4
2230 SHOW STATUS LIKE 'handler_read__e%';
2231 Variable_name Value
2232 Handler_read_key 16
2233 Handler_read_next 0
2234 EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION
2235 (SELECT max(b), a FROM t1 GROUP BY a);
2236 id select_type table type possible_keys key key_len ref rows Extra
2237 1 PRIMARY t1 range NULL a 5 NULL 8 Using index for group-by
2238 2 UNION t1 range NULL a 5 NULL 8 Using index for group-by
2239 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
2240 EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
2241 FROM t1 AS t1_outer;
2242 id select_type table type possible_keys key key_len ref rows Extra
2243 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index
2244 2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
2245 EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS
2246 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
2247 id select_type table type possible_keys key key_len ref rows Extra
2248 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index
2249 2 SUBQUERY t1 index NULL a 10 NULL 15 Using index
2250 EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
2251 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
2252 id select_type table type possible_keys key key_len ref rows Extra
2253 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2254 2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
2255 EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
2256 a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
2257 id select_type table type possible_keys key key_len ref rows Extra
2258 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index
2259 2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 1 Using index
2260 EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
2261 a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
2262 id select_type table type possible_keys key key_len ref rows Extra
2263 1 PRIMARY t1_outer range NULL a 5 NULL 8 Using index for group-by
2264 2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
2265 EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2
2266 ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2)
2267 AND t1_outer1.b = t1_outer2.b;
2268 id select_type table type possible_keys key key_len ref rows Extra
2269 1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index
2270 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index; Using join buffer
2271 2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
2272 EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
2273 FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
2274 id select_type table type possible_keys key key_len ref rows Extra
2275 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using index
2276 2 SUBQUERY t1_outer index NULL a 10 NULL 15 Using index
2277 3 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
2278 CREATE TABLE t3 LIKE t1;
2279 FLUSH STATUS;
2280 INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;
2281 SHOW STATUS LIKE 'handler_read__e%';
2282 Variable_name Value
2283 Handler_read_key 8
2284 Handler_read_next 0
2285 DELETE FROM t3;
2286 FLUSH STATUS;
2287 INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2)
2288 FROM t1 LIMIT 1;
2289 SHOW STATUS LIKE 'handler_read__e%';
2290 Variable_name Value
2291 Handler_read_key 8
2292 Handler_read_next 0
2293 FLUSH STATUS;
2294 DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
2295 SHOW STATUS LIKE 'handler_read__e%';
2296 Variable_name Value
2297 Handler_read_key 8
2298 Handler_read_next 0
2299 FLUSH STATUS;
2300 DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x
2301 FROM t1) > 10000;
2302 ERROR 21000: Subquery returns more than 1 row
2303 SHOW STATUS LIKE 'handler_read__e%';
2304 Variable_name Value
2305 Handler_read_key 8
2306 Handler_read_next 1
2307 DROP TABLE t1,t2,t3;
2308 CREATE TABLE t1 (a int, INDEX idx(a));
2309 INSERT INTO t1 VALUES
2310 (4), (2), (1), (2), (4), (2), (1), (4),
2311 (4), (2), (1), (2), (2), (4), (1), (4);
2312 EXPLAIN SELECT DISTINCT(a) FROM t1;
2313 id select_type table type possible_keys key key_len ref rows Extra
2314 1 SIMPLE t1 range NULL idx 5 NULL 9 Using index for group-by
2315 SELECT DISTINCT(a) FROM t1;
2316 a
2317 1
2318 2
2319 4
2320 EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
2321 id select_type table type possible_keys key key_len ref rows Extra
2322 1 SIMPLE t1 range NULL idx 5 NULL 9 Using index for group-by
2323 SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
2324 a
2325 1
2326 2
2327 4
2328 DROP TABLE t1;
2329 CREATE TABLE t1 (a INT, b INT);
2330 INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3);
2331 INSERT INTO t1 SELECT a + 1, b FROM t1;
2332 INSERT INTO t1 SELECT a + 2, b FROM t1;
2333 EXPLAIN
2334 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2335 id select_type table type possible_keys key key_len ref rows Extra
2336 1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort
2337 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2338 a MIN(b) MAX(b)
2339 4 1 3
2340 3 1 3
2341 2 1 3
2342 1 1 3
2343 CREATE INDEX break_it ON t1 (a, b);
2344 EXPLAIN
2345 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
2346 id select_type table type possible_keys key key_len ref rows Extra
2347 1 SIMPLE t1 range NULL break_it 10 NULL 7 Using index for group-by
2348 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
2349 a MIN(b) MAX(b)
2350 1 1 3
2351 2 1 3
2352 3 1 3
2353 4 1 3
2354 EXPLAIN
2355 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2356 id select_type table type possible_keys key key_len ref rows Extra
2357 1 SIMPLE t1 range NULL break_it 10 NULL 7 Using index for group-by; Using temporary; Using filesort
2358 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
2359 a MIN(b) MAX(b)
2360 4 1 3
2361 3 1 3
2362 2 1 3
2363 1 1 3
2364 EXPLAIN
2365 SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
2366 id select_type table type possible_keys key key_len ref rows Extra
2367 1 SIMPLE t1 index NULL break_it 10 NULL 12 Using index
2368 SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
2369 a MIN(b) MAX(b) AVG(b)
2370 4 1 3 2.0000
2371 3 1 3 2.0000
2372 2 1 3 2.0000
2373 1 1 3 2.0000
2374 DROP TABLE t1;
2375 create table t1 (a int, b int, primary key (a,b), key `index` (a,b)) engine=MyISAM;
2376 insert into t1 (a,b) values
2377 (0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6),
2378 (0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13),
2379 (1,0),(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),
2380 (1,7),(1,8),(1,9),(1,10),(1,11),(1,12),(1,13),
2381 (2,0),(2,1),(2,2),(2,3),(2,4),(2,5),(2,6),
2382 (2,7),(2,8),(2,9),(2,10),(2,11),(2,12),(2,13),
2383 (3,0),(3,1),(3,2),(3,3),(3,4),(3,5),(3,6),
2384 (3,7),(3,8),(3,9),(3,10),(3,11),(3,12),(3,13);
2385 insert into t1 (a,b) select a, max(b)+1 from t1 where a = 0 group by a;
2386 select * from t1;
2387 a b
2388 0 0
2389 0 1
2390 0 2
2391 0 3
2392 0 4
2393 0 5
2394 0 6
2395 0 7
2396 0 8
2397 0 9
2398 0 10
2399 0 11
2400 0 12
2401 0 13
2402 0 14
2403 1 0
2404 1 1
2405 1 2
2406 1 3
2407 1 4
2408 1 5
2409 1 6
2410 1 7
2411 1 8
2412 1 9
2413 1 10
2414 1 11
2415 1 12
2416 1 13
2417 2 0
2418 2 1
2419 2 2
2420 2 3
2421 2 4
2422 2 5
2423 2 6
2424 2 7
2425 2 8
2426 2 9
2427 2 10
2428 2 11
2429 2 12
2430 2 13
2431 3 0
2432 3 1
2433 3 2
2434 3 3
2435 3 4
2436 3 5
2437 3 6
2438 3 7
2439 3 8
2440 3 9
2441 3 10
2442 3 11
2443 3 12
2444 3 13
2445 explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a;
2446 id select_type table type possible_keys key key_len ref rows filtered Extra
2447 1 SIMPLE t1 range PRIMARY,index PRIMARY 4 NULL 3 100.00 Using where; Using index for group-by; Using temporary
2448 Warnings:
2449 Note 1003 select sql_buffer_result `test`.`t1`.`a` AS `a`,(max(`test`.`t1`.`b`) + 1) AS `max(b)+1` from `test`.`t1` where (`test`.`t1`.`a` = 0) group by `test`.`t1`.`a`
2450 drop table t1;
2451 CREATE TABLE t1 (a int, b int, c int, d int,
2452 KEY foo (c,d,a,b), KEY bar (c,a,b,d));
2453 INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4);
2454 INSERT INTO t1 SELECT * FROM t1;
2455 INSERT INTO t1 SELECT * FROM t1;
2456 INSERT INTO t1 SELECT a,b,c+1,d FROM t1;
2457 EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4;
2458 id select_type table type possible_keys key key_len ref rows Extra
2459 1 SIMPLE t1 range NULL foo 10 NULL 9 Using where; Using index for group-by
2460 SELECT DISTINCT c FROM t1 WHERE d=4;
2461 c
2462 1
2463 2
2464 DROP TABLE t1;
2465 #
2466 # Bug #45386: Wrong query result with MIN function in field list,
2467 # WHERE and GROUP BY clause
2468 #
2469 CREATE TABLE t (a INT, b INT, INDEX (a,b));
2470 INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
2471 INSERT INTO t SELECT * FROM t;
2472 # test MIN
2473 #should use range with index for group by
2474 EXPLAIN
2475 SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
2476 id select_type table type possible_keys key key_len ref rows Extra
2477 1 SIMPLE t range NULL a 10 NULL 9 Using where; Using index for group-by
2478 #should return 1 row
2479 SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
2480 a MIN(b)
2481 2 1
2482 # test MAX
2483 #should use range with index for group by
2484 EXPLAIN
2485 SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
2486 id select_type table type possible_keys key key_len ref rows Extra
2487 1 SIMPLE t range NULL a 10 NULL 9 Using where; Using index for group-by
2488 #should return 1 row
2489 SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
2490 a MAX(b)
2491 2 0
2492 # test 3 ranges and use the middle one
2493 INSERT INTO t SELECT a, 2 FROM t;
2494 #should use range with index for group by
2495 EXPLAIN
2496 SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
2497 id select_type table type possible_keys key key_len ref rows Extra
2498 1 SIMPLE t range NULL a 10 NULL 9 Using where; Using index for group-by
2499 #should return 1 row
2500 SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
2501 a MAX(b)
2502 2 1
2503 DROP TABLE t;
2504 #
2505 # Bug #48472: Loose index scan inappropriately chosen for some WHERE
2506 # conditions
2507 #
2508 CREATE TABLE t (a INT, b INT, INDEX (a,b));
2509 INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
2510 INSERT INTO t SELECT * FROM t;
2511 SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a;
2512 a MAX(b)
2513 2 0
2514 DROP TABLE t;
2515 End of 5.0 tests
2516 #
2517 # Bug #46607: Assertion failed: (cond_type == Item::FUNC_ITEM) results in
2518 # server crash
2519 #
2520 CREATE TABLE t (a INT, b INT, INDEX (a,b));
2521 INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
2522 INSERT INTO t SELECT * FROM t;
2523 SELECT a, MAX(b) FROM t WHERE b GROUP BY a;
2524 a MAX(b)
2525 2 1
2526 DROP TABLE t;
2527 CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, KEY (b));
2528 INSERT INTO t1 VALUES(1,1),(2,1);
2529 ANALYZE TABLE t1;
2530 Table Op Msg_type Msg_text
2531 test.t1 analyze status OK
2532 SELECT 1 AS c, b FROM t1 WHERE b IN (1,2) GROUP BY c, b;
2533 c b
2534 1 1
2535 SELECT a FROM t1 WHERE b=1;
2536 a
2537 1
2538 2
2539 DROP TABLE t1;
2540 #
2541 # Bug#47762: Incorrect result from MIN() when WHERE tests NOT NULL column
2542 # for NULL
2543 #
2544 ## Test for NULLs allowed
2545 CREATE TABLE t1 ( a INT, KEY (a) );
2546 INSERT INTO t1 VALUES (1), (2), (3);
2547 EXPLAIN
2548 SELECT MIN( a ) FROM t1 WHERE a = NULL;
2549 id select_type table type possible_keys key key_len ref rows Extra
2550 x x x x x x x x x Impossible WHERE noticed after reading const tables
2551 SELECT MIN( a ) FROM t1 WHERE a = NULL;
2552 MIN( a )
2553 NULL
2554 EXPLAIN
2555 SELECT MIN( a ) FROM t1 WHERE a <> NULL;
2556 id select_type table type possible_keys key key_len ref rows Extra
2557 x x x x x x x x x Impossible WHERE noticed after reading const tables
2558 SELECT MIN( a ) FROM t1 WHERE a <> NULL;
2559 MIN( a )
2560 NULL
2561 EXPLAIN
2562 SELECT MIN( a ) FROM t1 WHERE a > NULL;
2563 id select_type table type possible_keys key key_len ref rows Extra
2564 x x x x x x x x x Impossible WHERE noticed after reading const tables
2565 SELECT MIN( a ) FROM t1 WHERE a > NULL;
2566 MIN( a )
2567 NULL
2568 EXPLAIN
2569 SELECT MIN( a ) FROM t1 WHERE a < NULL;
2570 id select_type table type possible_keys key key_len ref rows Extra
2571 x x x x x x x x x Impossible WHERE noticed after reading const tables
2572 SELECT MIN( a ) FROM t1 WHERE a < NULL;
2573 MIN( a )
2574 NULL
2575 EXPLAIN
2576 SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
2577 id select_type table type possible_keys key key_len ref rows Extra
2578 x x x x x x x x x No matching min/max row
2579 SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
2580 MIN( a )
2581 NULL
2582 EXPLAIN
2583 SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
2584 id select_type table type possible_keys key key_len ref rows Extra
2585 x x x x x x x x x Impossible WHERE noticed after reading const tables
2586 SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
2587 MIN( a )
2588 NULL
2589 EXPLAIN
2590 SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
2591 id select_type table type possible_keys key key_len ref rows Extra
2592 x x x x x x x x x Impossible WHERE noticed after reading const tables
2593 SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
2594 MIN( a )
2595 NULL
2596 EXPLAIN
2597 SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
2598 id select_type table type possible_keys key key_len ref rows Extra
2599 x x x x x x x x x Impossible WHERE noticed after reading const tables
2600 SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
2601 MIN( a )
2602 NULL
2603 EXPLAIN
2604 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
2605 id select_type table type possible_keys key key_len ref rows Extra
2606 x x x x x x x x x Impossible WHERE noticed after reading const tables
2607 x x x x x x x x x Using where; Using index
2608 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
2609 MIN( a )
2610 NULL
2611 EXPLAIN
2612 SELECT MIN( a ) FROM t1 WHERE a IS NULL;
2613 id select_type table type possible_keys key key_len ref rows Extra
2614 x x x x x x x x x No matching min/max row
2615 SELECT MIN( a ) FROM t1 WHERE a IS NULL;
2616 MIN( a )
2617 NULL
2618 INSERT INTO t1 VALUES (NULL), (NULL);
2619 EXPLAIN
2620 SELECT MIN( a ) FROM t1 WHERE a = NULL;
2621 id select_type table type possible_keys key key_len ref rows Extra
2622 x x x x x x x x x Impossible WHERE noticed after reading const tables
2623 SELECT MIN( a ) FROM t1 WHERE a = NULL;
2624 MIN( a )
2625 NULL
2626 EXPLAIN
2627 SELECT MIN( a ) FROM t1 WHERE a <> NULL;
2628 id select_type table type possible_keys key key_len ref rows Extra
2629 x x x x x x x x x Impossible WHERE noticed after reading const tables
2630 SELECT MIN( a ) FROM t1 WHERE a <> NULL;
2631 MIN( a )
2632 NULL
2633 EXPLAIN
2634 SELECT MIN( a ) FROM t1 WHERE a > NULL;
2635 id select_type table type possible_keys key key_len ref rows Extra
2636 x x x x x x x x x Impossible WHERE noticed after reading const tables
2637 SELECT MIN( a ) FROM t1 WHERE a > NULL;
2638 MIN( a )
2639 NULL
2640 EXPLAIN
2641 SELECT MIN( a ) FROM t1 WHERE a < NULL;
2642 id select_type table type possible_keys key key_len ref rows Extra
2643 x x x x x x x x x Impossible WHERE noticed after reading const tables
2644 SELECT MIN( a ) FROM t1 WHERE a < NULL;
2645 MIN( a )
2646 NULL
2647 EXPLAIN
2648 SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
2649 id select_type table type possible_keys key key_len ref rows Extra
2650 x x x x x x x x x Select tables optimized away
2651 SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
2652 MIN( a )
2653 NULL
2654 EXPLAIN
2655 SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
2656 id select_type table type possible_keys key key_len ref rows Extra
2657 x x x x x x x x x Impossible WHERE noticed after reading const tables
2658 SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
2659 MIN( a )
2660 NULL
2661 EXPLAIN
2662 SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
2663 id select_type table type possible_keys key key_len ref rows Extra
2664 x x x x x x x x x Impossible WHERE noticed after reading const tables
2665 SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
2666 MIN( a )
2667 NULL
2668 EXPLAIN
2669 SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
2670 id select_type table type possible_keys key key_len ref rows Extra
2671 x x x x x x x x x Impossible WHERE noticed after reading const tables
2672 SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
2673 MIN( a )
2674 NULL
2675 EXPLAIN
2676 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
2677 id select_type table type possible_keys key key_len ref rows Extra
2678 x x x x x x x x x Impossible WHERE noticed after reading const tables
2679 x x x x x x x x x Using where; Using index
2680 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
2681 MIN( a )
2682 NULL
2683 EXPLAIN
2684 SELECT MIN( a ) FROM t1 WHERE a IS NULL;
2685 id select_type table type possible_keys key key_len ref rows Extra
2686 x x x x x x x x x Select tables optimized away
2687 SELECT MIN( a ) FROM t1 WHERE a IS NULL;
2688 MIN( a )
2689 NULL
2690 DROP TABLE t1;
2691 ## Test for NOT NULLs
2692 CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY);
2693 INSERT INTO t1 VALUES (1), (2), (3);
2694 #
2695 # NULL-safe operator test disabled for non-NULL indexed columns.
2696 #
2697 # See bugs
2698 #
2699 # - Bug#52173: Reading NULL value from non-NULL index gives
2700 # wrong result in embedded server
2701 #
2702 # - Bug#52174: Sometimes wrong plan when reading a MAX value from
2703 # non-NULL index
2704 #
2705 EXPLAIN
2706 SELECT MIN( a ) FROM t1 WHERE a = NULL;
2707 id select_type table type possible_keys key key_len ref rows Extra
2708 x x x x x x x x x Impossible WHERE noticed after reading const tables
2709 SELECT MIN( a ) FROM t1 WHERE a = NULL;
2710 MIN( a )
2711 NULL
2712 EXPLAIN
2713 SELECT MIN( a ) FROM t1 WHERE a <> NULL;
2714 id select_type table type possible_keys key key_len ref rows Extra
2715 x x x x x x x x x Impossible WHERE noticed after reading const tables
2716 SELECT MIN( a ) FROM t1 WHERE a <> NULL;
2717 MIN( a )
2718 NULL
2719 EXPLAIN
2720 SELECT MIN( a ) FROM t1 WHERE a > NULL;
2721 id select_type table type possible_keys key key_len ref rows Extra
2722 x x x x x x x x x Impossible WHERE noticed after reading const tables
2723 SELECT MIN( a ) FROM t1 WHERE a > NULL;
2724 MIN( a )
2725 NULL
2726 EXPLAIN
2727 SELECT MIN( a ) FROM t1 WHERE a < NULL;
2728 id select_type table type possible_keys key key_len ref rows Extra
2729 x x x x x x x x x Impossible WHERE noticed after reading const tables
2730 SELECT MIN( a ) FROM t1 WHERE a < NULL;
2731 MIN( a )
2732 NULL
2733 EXPLAIN
2734 SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
2735 id select_type table type possible_keys key key_len ref rows Extra
2736 x x x x x x x x x Impossible WHERE noticed after reading const tables
2737 SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
2738 MIN( a )
2739 NULL
2740 EXPLAIN
2741 SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
2742 id select_type table type possible_keys key key_len ref rows Extra
2743 x x x x x x x x x Impossible WHERE noticed after reading const tables
2744 SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
2745 MIN( a )
2746 NULL
2747 EXPLAIN
2748 SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
2749 id select_type table type possible_keys key key_len ref rows Extra
2750 x x x x x x x x x Impossible WHERE noticed after reading const tables
2751 SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
2752 MIN( a )
2753 NULL
2754 EXPLAIN
2755 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
2756 id select_type table type possible_keys key key_len ref rows Extra
2757 x x x x x x x x x Impossible WHERE noticed after reading const tables
2758 x x x x x x x x x Using where; Using index
2759 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
2760 MIN( a )
2761 NULL
2762 EXPLAIN
2763 SELECT MIN( a ) FROM t1 WHERE a IS NULL;
2764 id select_type table type possible_keys key key_len ref rows Extra
2765 x x x x x x x x x Impossible WHERE
2766 SELECT MIN( a ) FROM t1 WHERE a IS NULL;
2767 MIN( a )
2768 NULL
2769 DROP TABLE t1;
2770 #
2771 # Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&, Item*) at
2772 # opt_sum.cc:305
2773 #
2774 CREATE TABLE t1 ( a INT, KEY (a) );
2775 INSERT INTO t1 VALUES (1), (2), (3);
2776 SELECT MIN( a ) AS min_a
2777 FROM t1
2778 WHERE a > 1 AND a IS NULL
2779 ORDER BY min_a;
2780 min_a
2781 NULL
2782 DROP TABLE t1;
2783 End of 5.1 tests