« back to all changes in this revision
Viewing changes to tests/r/pbxt/group_by.result
- browse files at revision 1455.3.1
- compare with another revision
- compare with revision 2161
- download diff
- download tarball
- view history from revision 1455.3.1
- Committer: Vladimir Kolesnikov
- Date: 2010-04-08 16:11:29 UTC
- mto: (1457.1.1 bad-staging) (1455.3.13 drizzle-pbxt-6)
- mto: This revision was merged to the branch mainline in revision 1462.
- Revision ID: vladimir@primebase.org-20100408161129-x85uvbu9qavjck66
lp:drizzle + pbxt 1.1 + test results
- files added:
- plugin/pbxt
- plugin/pbxt/bin
- plugin/pbxt/src
- tests/r/pbxt
- files modified:
- drizzled/sql_table.cc
added
removed
tests/r/pbxt/group_by.result
1
drop table if exists t1,t2,t3;
2
SELECT 1 FROM (SELECT 1) as a GROUP BY SUM(1);
3
ERROR HY000: Invalid use of group function
4
CREATE TABLE t1 (
5
spID int,
6
userID int,
7
score int,
8
lsg char(40),
9
date date
10
);
11
INSERT INTO t1 VALUES (1,1,1,'',NULL);
12
INSERT INTO t1 VALUES (2,2,2,'',NULL);
13
INSERT INTO t1 VALUES (2,1,1,'',NULL);
14
INSERT INTO t1 VALUES (3,3,3,'',NULL);
15
CREATE TABLE t2 (
16
userID int NOT NULL auto_increment,
17
niName char(15),
18
passwd char(8),
19
mail char(50),
20
isAukt enum('N','Y') DEFAULT 'N',
21
vName char(30),
22
nName char(40),
23
adr char(60),
24
plz char(5),
25
ort char(35),
26
land char(20),
27
PRIMARY KEY (userID)
28
);
29
INSERT INTO t2 VALUES (1,'name','pass','mail','Y','v','n','adr','1','1','1');
30
INSERT INTO t2 VALUES (2,'name','pass','mail','Y','v','n','adr','1','1','1');
31
INSERT INTO t2 VALUES (3,'name','pass','mail','Y','v','n','adr','1','1','1');
32
INSERT INTO t2 VALUES (4,'name','pass','mail','Y','v','n','adr','1','1','1');
33
INSERT INTO t2 VALUES (5,'name','pass','mail','Y','v','n','adr','1','1','1');
34
SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid;
35
userid MIN(t1.score)
36
1 1
37
2 2
38
3 3
39
SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid ORDER BY NULL;
40
userid MIN(t1.score)
41
1 1
42
2 2
43
3 3
44
SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid;
45
userid MIN(t1.score)
46
1 1
47
2 2
48
SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid;
49
userid MIN(t1.score+0.0)
50
1 1.0
51
2 2.0
52
SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid ORDER BY NULL;
53
userid MIN(t1.score+0.0)
54
2 2.0
55
1 1.0
56
EXPLAIN SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid ORDER BY NULL;
57
id select_type table type possible_keys key key_len ref rows Extra
58
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where; Using temporary
59
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.userID 1 Using index
60
drop table t1,t2;
61
CREATE TABLE t1 (
62
PID int NOT NULL auto_increment,
63
payDate date,
64
recDate datetime,
65
URID int DEFAULT '0' NOT NULL,
66
CRID int DEFAULT '0' NOT NULL,
67
amount int DEFAULT '0' NOT NULL,
68
operator int,
69
method enum('unknown','cash','dealer','check','card','lazy','delayed','test') DEFAULT 'unknown' NOT NULL,
70
DIID int,
71
reason char(1) binary DEFAULT '' NOT NULL,
72
code_id int,
73
qty int DEFAULT '0' NOT NULL,
74
PRIMARY KEY (PID),
75
KEY URID (URID),
76
KEY reason (reason),
77
KEY method (method),
78
KEY payDate (payDate)
79
);
80
INSERT INTO t1 VALUES (1,'1970-01-01','1997-10-17 00:00:00',2529,1,21000,11886,'check',0,'F',16200,6);
81
SELECT COUNT(P.URID),SUM(P.amount),P.method, MIN(PP.recdate+0) > 19980501000000 AS IsNew FROM t1 AS P JOIN t1 as PP WHERE P.URID = PP.URID GROUP BY method,IsNew;
82
ERROR 42000: Can't group on 'IsNew'
83
drop table t1;
84
CREATE TABLE t1 (
85
cid int NOT NULL auto_increment,
86
firstname varchar(32) DEFAULT '' NOT NULL,
87
surname varchar(32) DEFAULT '' NOT NULL,
88
PRIMARY KEY (cid)
89
);
90
INSERT INTO t1 VALUES (1,'That','Guy');
91
INSERT INTO t1 VALUES (2,'Another','Gent');
92
CREATE TABLE t2 (
93
call_id int NOT NULL auto_increment,
94
contact_id int DEFAULT '0' NOT NULL,
95
PRIMARY KEY (call_id),
96
KEY contact_id (contact_id)
97
);
98
INSERT INTO t2 VALUES (10,2);
99
INSERT INTO t2 VALUES (18,2);
100
INSERT INTO t2 VALUES (62,2);
101
INSERT INTO t2 VALUES (91,2);
102
INSERT INTO t2 VALUES (92,2);
103
SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid;
104
cid CONCAT(firstname, ' ', surname) COUNT(call_id)
105
SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY NULL;
106
cid CONCAT(firstname, ' ', surname) COUNT(call_id)
107
SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY surname, firstname;
108
cid CONCAT(firstname, ' ', surname) COUNT(call_id)
109
drop table t2;
110
drop table t1;
111
create table t1 (foo int);
112
insert into t1 values (1);
113
select 1+1, "a",count(*) from t1 where foo in (2);
114
1+1 a count(*)
115
2 a 0
116
insert into t1 values (1);
117
select 1+1,"a",count(*) from t1 where foo in (2);
118
1+1 a count(*)
119
2 a 0
120
drop table t1;
121
CREATE TABLE t1 (
122
spID int,
123
userID int,
124
score int,
125
key (spid),
126
key (score)
127
);
128
INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3),(6,3,3),(7,3,3);
129
explain select userid,count(*) from t1 group by userid desc;
130
id select_type table type possible_keys key key_len ref rows Extra
131
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort
132
explain select userid,count(*) from t1 group by userid desc order by null;
133
id select_type table type possible_keys key key_len ref rows Extra
134
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary
135
select userid,count(*) from t1 group by userid desc;
136
userid count(*)
137
3 5
138
2 1
139
1 2
140
select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3);
141
userid count(*)
142
1 2
143
select userid,count(*) from t1 group by userid desc having 3 IN (1,COUNT(*));
144
userid count(*)
145
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
146
id select_type table type possible_keys key key_len ref rows Extra
147
1 SIMPLE t1 range spID spID 5 NULL 1 Using where; Using index
148
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid;
149
id select_type table type possible_keys key key_len ref rows Extra
150
1 SIMPLE t1 range spID spID 5 NULL 1 Using where; Using index
151
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null;
152
id select_type table type possible_keys key key_len ref rows Extra
153
1 SIMPLE t1 range spID spID 5 NULL 1 Using where; Using index
154
select spid,count(*) from t1 where spid between 1 and 2 group by spid;
155
spid count(*)
156
1 1
157
2 2
158
select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
159
spid count(*)
160
2 2
161
1 1
162
explain extended select sql_big_result spid,sum(userid) from t1 group by spid desc;
163
id select_type table type possible_keys key key_len ref rows filtered Extra
164
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using filesort
165
Warnings:
166
Note 1003 select sql_big_result `test`.`t1`.`spID` AS `spid`,sum(`test`.`t1`.`userID`) AS `sum(userid)` from `test`.`t1` group by `test`.`t1`.`spID` desc
167
explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null;
168
id select_type table type possible_keys key key_len ref rows Extra
169
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort
170
select sql_big_result spid,sum(userid) from t1 group by spid desc;
171
spid sum(userid)
172
7 3
173
6 3
174
5 3
175
4 3
176
3 3
177
2 3
178
1 1
179
explain select sql_big_result score,count(*) from t1 group by score desc;
180
id select_type table type possible_keys key key_len ref rows Extra
181
1 SIMPLE t1 index NULL score 5 NULL 8 Using index; Using filesort
182
explain select sql_big_result score,count(*) from t1 group by score desc order by null;
183
id select_type table type possible_keys key key_len ref rows Extra
184
1 SIMPLE t1 index NULL score 5 NULL 8 Using index; Using filesort
185
select sql_big_result score,count(*) from t1 group by score desc;
186
score count(*)
187
3 5
188
2 1
189
1 2
190
drop table t1;
191
create table t1 (a date default null, b date default null);
192
insert t1 values ('1999-10-01','2000-01-10'), ('1997-01-01','1998-10-01');
193
select a,min(b) c,count(distinct rand()) from t1 group by a having c<a + interval 1 day;
194
a c count(distinct rand())
195
drop table t1;
196
CREATE TABLE t1 (a char(1));
197
INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL);
198
SELECT a FROM t1 GROUP BY a;
199
a
200
NULL
201
A
202
B
203
SELECT a,count(*) FROM t1 GROUP BY a;
204
a count(*)
205
NULL 3
206
A 5
207
B 5
208
SELECT a FROM t1 GROUP BY binary a;
209
a
210
NULL
211
A
212
B
213
a
214
b
215
SELECT a,count(*) FROM t1 GROUP BY binary a;
216
a count(*)
217
NULL 3
218
A 4
219
B 4
220
a 1
221
b 1
222
SELECT binary a FROM t1 GROUP BY 1;
223
binary a
224
NULL
225
A
226
B
227
a
228
b
229
SELECT binary a,count(*) FROM t1 GROUP BY 1;
230
binary a count(*)
231
NULL 3
232
A 4
233
B 4
234
a 1
235
b 1
236
SELECT a FROM t1 GROUP BY a;
237
a
238
NULL
239
A
240
B
241
SELECT a,count(*) FROM t1 GROUP BY a;
242
a count(*)
243
NULL 3
244
A 5
245
B 5
246
SELECT a FROM t1 GROUP BY binary a;
247
a
248
NULL
249
A
250
B
251
a
252
b
253
SELECT a,count(*) FROM t1 GROUP BY binary a;
254
a count(*)
255
NULL 3
256
A 4
257
B 4
258
a 1
259
b 1
260
SELECT binary a FROM t1 GROUP BY 1;
261
binary a
262
NULL
263
A
264
B
265
a
266
b
267
SELECT binary a,count(*) FROM t1 GROUP BY 1;
268
binary a count(*)
269
NULL 3
270
A 4
271
B 4
272
a 1
273
b 1
274
drop table t1;
275
CREATE TABLE t1 (
276
`a` char(193) default NULL,
277
`b` char(63) default NULL
278
);
279
INSERT INTO t1 VALUES ('abc','def'),('hij','klm');
280
SELECT CONCAT(a, b) FROM t1 GROUP BY 1;
281
CONCAT(a, b)
282
abcdef
283
hijklm
284
SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;
285
CONCAT(a, b) count(*)
286
abcdef 1
287
hijklm 1
288
SELECT CONCAT(a, b),count(distinct a) FROM t1 GROUP BY 1;
289
CONCAT(a, b) count(distinct a)
290
abcdef 1
291
hijklm 1
292
SELECT 1 FROM t1 GROUP BY CONCAT(a, b);
293
1
294
1
295
1
296
INSERT INTO t1 values ('hij','klm');
297
SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;
298
CONCAT(a, b) count(*)
299
abcdef 1
300
hijklm 2
301
DROP TABLE t1;
302
create table t1 (One int, Two int, Three int, Four int);
303
insert into t1 values (1,2,1,4),(1,2,2,4),(1,2,3,4),(1,2,4,4),(1,1,1,4),(1,1,2,4),(1,1,3,4),(1,1,4,4),(1,3,1,4),(1,3,2,4),(1,3,3,4),(1,3,4,4);
304
select One, Two, sum(Four) from t1 group by One,Two;
305
One Two sum(Four)
306
1 1 16
307
1 2 16
308
1 3 16
309
drop table t1;
310
create table t1 (id integer primary key not null auto_increment, gender char(1));
311
insert into t1 values (NULL, 'M'), (NULL, 'F'),(NULL, 'F'),(NULL, 'F'),(NULL, 'M');
312
create table t2 (user_id integer not null, date date);
313
insert into t2 values (1, '2002-06-09'),(2, '2002-06-09'),(1, '2002-06-09'),(3, '2002-06-09'),(4, '2002-06-09'),(4, '2002-06-09');
314
select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender;
315
gender dist_count percentage
316
F 3 60.0000
317
M 1 20.0000
318
select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender order by percentage;
319
gender dist_count percentage
320
M 1 20.0000
321
F 3 60.0000
322
drop table t1,t2;
323
CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID
324
));
325
insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL);
326
select S.ID as xID, S.ID1 as xID1 from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2;
327
xID xID1
328
1 1
329
2 2
330
2 2
331
3 134
332
3 134
333
3 134
334
4 185
335
4 185
336
4 185
337
4 185
338
select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2 group by xID order by xID1;
339
xID xID1 Level
340
1 1 *
341
2 2 **
342
3 134 ***
343
4 185 ****
344
drop table t1;
345
CREATE TEMPORARY TABLE t1 (
346
pid int NOT NULL default '0',
347
c1id int default NULL,
348
c2id int default NULL,
349
value int NOT NULL default '0',
350
UNIQUE KEY pid2 (pid,c1id,c2id),
351
UNIQUE KEY pid (pid,value)
352
) ENGINE=MyISAM;
353
INSERT INTO t1 VALUES (1, 1, NULL, 1),(1, 2, NULL, 2),(1, NULL, 3, 3),(1, 4, NULL, 4),(1, 5, NULL, 5);
354
CREATE TEMPORARY TABLE t2 (
355
id int NOT NULL default '0',
356
active enum('Yes','No') NOT NULL default 'Yes',
357
PRIMARY KEY (id)
358
) ENGINE=MyISAM;
359
INSERT INTO t2 VALUES (1, 'Yes'),(2, 'No'),(4, 'Yes'),(5, 'No');
360
CREATE TABLE t3 (
361
id int NOT NULL default '0',
362
active enum('Yes','No') NOT NULL default 'Yes',
363
PRIMARY KEY (id)
364
);
365
INSERT INTO t3 VALUES (3, 'Yes');
366
select * from t1 AS m LEFT JOIN t2 AS c1 ON m.c1id =
367
c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = c2.id AND
368
c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS NOT NULL);
369
pid c1id c2id value id active id active
370
1 1 NULL 1 1 Yes NULL NULL
371
1 NULL 3 3 NULL NULL 3 Yes
372
1 4 NULL 4 4 Yes NULL NULL
373
select max(value) from t1 AS m LEFT JOIN t2 AS c1 ON
374
m.c1id = c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id =
375
c2.id AND c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS
376
NOT NULL);
377
max(value)
378
4
379
drop table t1,t2,t3;
380
create table t1 (a blob null);
381
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(""),(""),(""),("b");
382
select a,count(*) from t1 group by a;
383
a count(*)
384
NULL 9
385
3
386
b 1
387
select a,count(*) from t1 group by a;
388
a count(*)
389
NULL 9
390
3
391
b 1
392
drop table t1;
393
create table t1 (a int not null, b int not null);
394
insert into t1 values (1,1),(1,2),(3,1),(3,2),(2,2),(2,1);
395
create table t2 (a int not null, b int not null, key(a));
396
insert into t2 values (1,3),(3,1),(2,2),(1,1);
397
select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
398
a b
399
1 1
400
1 3
401
2 2
402
3 1
403
select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
404
a b
405
1 3
406
3 1
407
2 2
408
1 1
409
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
410
id select_type table type possible_keys key key_len ref rows Extra
411
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
412
1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where; Using join buffer
413
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
414
id select_type table type possible_keys key key_len ref rows Extra
415
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary
416
1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where; Using join buffer
417
drop table t1,t2;
418
create table t1 (a int, b int);
419
insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
420
select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a;
421
a MAX(b) INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000)
422
1 4 2
423
10 43 6
424
select a, MAX(b), CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end from t1 group by a;
425
a MAX(b) CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end
426
1 4 4
427
10 43 43
428
select a, MAX(b), FIELD(MAX(b), '43', '4', '5') from t1 group by a;
429
a MAX(b) FIELD(MAX(b), '43', '4', '5')
430
1 4 2
431
10 43 1
432
select a, MAX(b), CONCAT_WS(MAX(b), '43', '4', '5') from t1 group by a;
433
a MAX(b) CONCAT_WS(MAX(b), '43', '4', '5')
434
1 4 434445
435
10 43 43434435
436
select a, MAX(b), ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f') from t1 group by a;
437
a MAX(b) ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f')
438
1 4 d
439
10 43 NULL
440
select a, MAX(b), MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') from t1 group by a;
441
a MAX(b) MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h')
442
1 4 c
443
10 43 a,b,d,f
444
drop table t1;
445
create table t1 (id int not null, qty int not null);
446
insert into t1 values (1,2),(1,3),(2,4),(2,5);
447
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and cqty>1;
448
id sqty cqty
449
1 5 2
450
2 9 2
451
select id, sum(qty) as sqty from t1 group by id having sqty>2 and count(qty)>1;
452
id sqty
453
1 5
454
2 9
455
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sqty>2 and cqty>1;
456
id sqty cqty
457
1 5 2
458
2 9 2
459
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and count(qty)>1;
460
id sqty cqty
461
1 5 2
462
2 9 2
463
select count(*), case interval(qty,2,3,4,5,6,7,8) when -1 then NULL when 0 then "zero" when 1 then "one" when 2 then "two" end as category from t1 group by category;
464
count(*) category
465
2 NULL
466
1 one
467
1 two
468
select count(*), interval(qty,2,3,4,5,6,7,8) as category from t1 group by category;
469
count(*) category
470
1 1
471
1 2
472
1 3
473
1 4
474
drop table t1;
475
CREATE TABLE t1 (
476
userid int,
477
score int,
478
key (score)
479
);
480
INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3);
481
SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
482
userid count(*)
483
3 5
484
2 1
485
1 2
486
EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
487
id select_type table type possible_keys key key_len ref rows Extra
488
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort
489
DROP TABLE t1;
490
CREATE TABLE t1 (
491
i int default NULL,
492
j int default NULL
493
);
494
INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5);
495
SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
496
i COUNT(DISTINCT(i))
497
1 1
498
2 1
499
4 4
500
explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
501
id select_type table type possible_keys key key_len ref rows Extra
502
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
503
DROP TABLE t1;
504
create table t1 (a int);
505
insert into t1 values(null);
506
select min(a) is null from t1;
507
min(a) is null
508
1
509
select min(a) is null or null from t1;
510
min(a) is null or null
511
1
512
select 1 and min(a) is null from t1;
513
1 and min(a) is null
514
1
515
drop table t1;
516
create table t1 ( col1 int, col2 int );
517
insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
518
select group_concat( distinct col1 ) as alias from t1
519
group by col2 having alias like '%';
520
alias
521
1,2
522
1,2
523
1
524
drop table t1;
525
create table t1 (a integer, b integer, c integer);
526
insert into t1 (a,b) values (1,2),(1,3),(2,5);
527
select a, 0.1*0+1 r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2=1;
528
a r2 r1
529
1 1.0 2
530
select a, round(rand(100)*10) r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2<=2;
531
a r2 r1
532
1 2 2
533
select a,sum(b) from t1 where a=1 group by c;
534
a sum(b)
535
1 5
536
select a*sum(b) from t1 where a=1 group by c;
537
a*sum(b)
538
5
539
select sum(a)*sum(b) from t1 where a=1 group by c;
540
sum(a)*sum(b)
541
10
542
select a,sum(b) from t1 where a=1 group by c having a=1;
543
a sum(b)
544
1 5
545
select a as d,sum(b) from t1 where a=1 group by c having d=1;
546
d sum(b)
547
1 5
548
select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;
549
d
550
10
551
drop table t1;
552
create table t1(a int);
553
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9);
554
create table t2 (
555
a int,
556
b varchar(200) NOT NULL,
557
c varchar(50) NOT NULL,
558
d varchar(100) NOT NULL,
559
primary key (a,b(132),c,d),
560
key a (a,b)
561
);
562
insert into t2 select
563
x3.a, -- 3
564
concat('val-', x3.a + 3*x4.a), -- 12
565
concat('val-', @a:=x3.a + 3*x4.a + 12*C.a), -- 120
566
concat('val-', @a + 120*D.a)
567
from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4;
568
delete from t2 where a = 2 and b = 'val-2' order by a,b,c,d limit 30;
569
explain select c from t2 where a = 2 and b = 'val-2' group by c;
570
id select_type table type possible_keys key key_len ref rows Extra
571
1 SIMPLE t2 ref PRIMARY,a PRIMARY 534 const,const 1 Using where
572
select c from t2 where a = 2 and b = 'val-2' group by c;
573
c
574
val-74
575
val-98
576
drop table t1,t2;
577
create table t1 (b int4 not null);
578
insert into t1 values(300000);
579
select * from t1;
580
b
581
300000
582
select min(b) from t1;
583
min(b)
584
300000
585
drop table t1;
586
CREATE TABLE t1 (id int PRIMARY KEY, user_id int, hostname longtext);
587
INSERT INTO t1 VALUES
588
(1, 7, 'cache-dtc-af05.proxy.aol.com'),
589
(2, 3, 'what.ever.com'),
590
(3, 7, 'cache-dtc-af05.proxy.aol.com'),
591
(4, 7, 'cache-dtc-af05.proxy.aol.com');
592
SELECT hostname, COUNT(DISTINCT user_id) as no FROM t1
593
WHERE hostname LIKE '%aol%'
594
GROUP BY hostname;
595
hostname no
596
cache-dtc-af05.proxy.aol.com 1
597
DROP TABLE t1;
598
CREATE TABLE t1 (a int, b int);
599
INSERT INTO t1 VALUES (1,2), (1,3);
600
SELECT a, b FROM t1 GROUP BY 'const';
601
a b
602
1 2
603
SELECT DISTINCT a, b FROM t1 GROUP BY 'const';
604
a b
605
1 2
606
DROP TABLE t1;
607
CREATE TABLE t1 (id INT, dt DATETIME);
608
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
609
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
610
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
611
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
612
SELECT dt DIV 1 AS f, id FROM t1 GROUP BY f;
613
f id
614
20050501123000 1
615
DROP TABLE t1;
616
CREATE TABLE t1 (id varchar(20) NOT NULL);
617
INSERT INTO t1 VALUES ('trans1'), ('trans2');
618
CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL);
619
INSERT INTO t2 VALUES ('trans1', 'a problem');
620
SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment
621
FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment;
622
COUNT(DISTINCT(t1.id)) comment
623
1 NULL
624
1 a problem
625
DROP TABLE t1, t2;
626
create table t1 (f1 date);
627
insert into t1 values('2005-06-06');
628
insert into t1 values('2005-06-06');
629
select date(left(f1+0,8)) from t1 group by 1;
630
date(left(f1+0,8))
631
2005-06-06
632
drop table t1;
633
CREATE TABLE t1 (n int);
634
INSERT INTO t1 VALUES (1);
635
SELECT n+1 AS n FROM t1 GROUP BY n;
636
n
637
2
638
Warnings:
639
Warning 1052 Column 'n' in group statement is ambiguous
640
DROP TABLE t1;
641
create table t1(f1 varchar(5) key);
642
insert into t1 values (1),(2);
643
select sql_buffer_result max(f1) is null from t1;
644
max(f1) is null
645
0
646
select sql_buffer_result max(f1)+1 from t1;
647
max(f1)+1
648
3
649
drop table t1;
650
CREATE TABLE t1(a INT);
651
INSERT INTO t1 VALUES (1),(2);
652
SELECT a FROM t1 GROUP BY 'a';
653
a
654
1
655
SELECT a FROM t1 GROUP BY "a";
656
a
657
1
658
SELECT a FROM t1 GROUP BY `a`;
659
a
660
1
661
2
662
SELECT a FROM t1 GROUP BY "a";
663
a
664
1
665
SELECT a FROM t1 GROUP BY 'a';
666
a
667
1
668
SELECT a FROM t1 GROUP BY `a`;
669
a
670
1
671
2
672
SELECT a FROM t1 HAVING 'a' > 1;
673
a
674
Warnings:
675
Warning 1292 Truncated incorrect DOUBLE value: 'a'
676
SELECT a FROM t1 HAVING "a" > 1;
677
a
678
Warnings:
679
Warning 1292 Truncated incorrect DOUBLE value: 'a'
680
SELECT a FROM t1 HAVING `a` > 1;
681
a
682
2
683
SELECT a FROM t1 ORDER BY 'a' DESC;
684
a
685
1
686
2
687
SELECT a FROM t1 ORDER BY "a" DESC;
688
a
689
1
690
2
691
SELECT a FROM t1 ORDER BY `a` DESC;
692
a
693
2
694
1
695
DROP TABLE t1;
696
CREATE TABLE t1 (
697
f1 int NOT NULL auto_increment primary key,
698
f2 varchar(100) NOT NULL default ''
699
);
700
CREATE TABLE t2 (
701
f1 varchar(10) NOT NULL default '',
702
f2 char(3) NOT NULL default '',
703
PRIMARY KEY (`f1`),
704
KEY `k1` (`f2`,`f1`)
705
);
706
INSERT INTO t1 values(NULL, '');
707
INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
708
SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
709
avg(t2.f1)
710
SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
711
avg(t2.f1)
712
DROP TABLE t1, t2;
713
create table t1 (c1 char(3), c2 char(3));
714
create table t2 (c3 char(3), c4 char(3));
715
insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
716
insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
717
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
718
group by c2;
719
c2
720
aaa
721
aaa
722
Warnings:
723
Warning 1052 Column 'c2' in group statement is ambiguous
724
show warnings;
725
Level Code Message
726
Warning 1052 Column 'c2' in group statement is ambiguous
727
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
728
group by t1.c1;
729
c2
730
aaa
731
show warnings;
732
Level Code Message
733
drop table t1, t2;
734
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));
735
INSERT INTO t1 VALUES (1, 1);
736
INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1;
737
INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1;
738
INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1;
739
INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1;
740
INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1;
741
INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1;
742
INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1;
743
SELECT MIN(b), MAX(b) from t1;
744
MIN(b) MAX(b)
745
0 19
746
EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;
747
id select_type table type possible_keys key key_len ref rows Extra
748
1 SIMPLE t1 index NULL b 5 NULL 128 Using index
749
EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
750
id select_type table type possible_keys key key_len ref rows Extra
751
1 SIMPLE t1 index NULL b 5 NULL 128 Using index; Using filesort
752
SELECT b, sum(1) FROM t1 GROUP BY b;
753
b sum(1)
754
0 6
755
1 7
756
2 7
757
3 7
758
4 7
759
5 7
760
6 7
761
7 7
762
8 7
763
9 6
764
10 6
765
11 6
766
12 6
767
13 6
768
14 6
769
15 6
770
16 6
771
17 6
772
18 6
773
19 6
774
SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
775
b sum(1)
776
0 6
777
1 7
778
2 7
779
3 7
780
4 7
781
5 7
782
6 7
783
7 7
784
8 7
785
9 6
786
10 6
787
11 6
788
12 6
789
13 6
790
14 6
791
15 6
792
16 6
793
17 6
794
18 6
795
19 6
796
DROP TABLE t1;
797
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
798
INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
799
SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b;
800
MAX(a)-MIN(a)
801
1
802
1
803
1
804
SELECT CEILING(MIN(a)) FROM t1 GROUP BY b;
805
CEILING(MIN(a))
806
1
807
3
808
5
809
SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1
810
GROUP BY b;
811
CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END
812
Positive
813
Positive
814
Positive
815
SELECT a + 1 FROM t1 GROUP BY a;
816
a + 1
817
2
818
3
819
4
820
5
821
6
822
7
823
SELECT a + b FROM t1 GROUP BY b;
824
a + b
825
2
826
5
827
8
828
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
829
FROM t1 AS t1_outer;
830
(SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
831
1
832
2
833
3
834
4
835
5
836
6
837
SELECT 1 FROM t1 as t1_outer GROUP BY a
838
HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
839
1
840
1
841
1
842
1
843
1
844
1
845
1
846
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1)
847
FROM t1 AS t1_outer GROUP BY t1_outer.b;
848
(SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1)
849
1
850
3
851
5
852
SELECT 1 FROM t1 as t1_outer GROUP BY a
853
HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
854
ERROR 42S22: Unknown column 'test.t1_outer.b' in 'field list'
855
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
856
FROM t1 AS t1_outer GROUP BY t1_outer.b;
857
(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
858
21
859
21
860
21
861
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
862
FROM t1 AS t1_outer;
863
(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
864
3
865
3
866
3
867
3
868
3
869
3
870
SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1)
871
FROM t1 AS t1_outer GROUP BY t1_outer.b;
872
(SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1)
873
3
874
7
875
11
876
SELECT 1 FROM t1 as t1_outer
877
WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1);
878
1
879
1
880
1
881
1
882
1
883
1
884
1
885
SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0;
886
b
887
1
888
2
889
3
890
SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12;
891
1
892
1
893
1
894
SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1);
895
1
896
1
897
SELECT 1 FROM t1 GROUP BY b HAVING a = 2;
898
ERROR 42S22: Unknown column 'a' in 'having clause'
899
SELECT 1 FROM t1 GROUP BY SUM(b);
900
ERROR HY000: Invalid use of group function
901
SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN
902
(SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a
903
HAVING SUM(t1_inner.b)+t1_outer.b > 5);
904
b
905
3
906
DROP TABLE t1;
907
create table t1(f1 int, f2 int);
908
select * from t1 group by f1;
909
f1 f2
910
select * from t1 group by f2;
911
f1 f2
912
select * from t1 group by f1, f2;
913
f1 f2
914
select t1.f1,t.* from t1, t1 t group by 1;
915
f1 f1 f2
916
drop table t1;
917
CREATE TABLE t1(
918
id INT AUTO_INCREMENT PRIMARY KEY,
919
c1 INT NOT NULL,
920
c2 INT NOT NULL,
921
UNIQUE KEY (c2,c1));
922
INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
923
SELECT * FROM t1 ORDER BY c1;
924
id c1 c2
925
5 1 3
926
4 2 3
927
3 3 5
928
2 4 1
929
1 5 1
930
SELECT * FROM t1 GROUP BY id ORDER BY c1;
931
id c1 c2
932
5 1 3
933
4 2 3
934
3 3 5
935
2 4 1
936
1 5 1
937
SELECT * FROM t1 GROUP BY id ORDER BY id DESC;
938
id c1 c2
939
5 1 3
940
4 2 3
941
3 3 5
942
2 4 1
943
1 5 1
944
SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;
945
id c1 c2
946
2 4 1
947
1 5 1
948
5 1 3
949
4 2 3
950
3 3 5
951
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;
952
id c1 c2
953
3 3 5
954
5 1 3
955
4 2 3
956
2 4 1
957
1 5 1
958
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;
959
id c1 c2
960
3 3 5
961
4 2 3
962
5 1 3
963
1 5 1
964
2 4 1
965
SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1;
966
id c1 c2
967
1 5 1
968
4 2 3
969
3 3 5
970
SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1;
971
id c1 c2
972
3 3 5
973
4 2 3
974
1 5 1
975
SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC;
976
id c1 c2
977
3 3 5
978
4 2 3
979
1 5 1
980
DROP TABLE t1;
981
#
982
# Bug#27219: Aggregate functions in ORDER BY.
983
#
984
CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
985
INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
986
CREATE TABLE t2 SELECT * FROM t1;
987
SELECT 1 FROM t1 ORDER BY COUNT(*);
988
1
989
1
990
SELECT 1 FROM t1 ORDER BY COUNT(*) + 1;
991
1
992
1
993
SELECT 1 FROM t1 ORDER BY COUNT(*) + a;
994
1
995
1
996
SELECT 1 FROM t1 ORDER BY COUNT(*), 1;
997
1
998
1
999
SELECT 1 FROM t1 ORDER BY COUNT(*), a;
1000
1
1001
1
1002
SELECT 1 FROM t1 ORDER BY SUM(a);
1003
1
1004
1
1005
SELECT 1 FROM t1 ORDER BY SUM(a + 1);
1006
1
1007
1
1008
SELECT 1 FROM t1 ORDER BY SUM(a) + 1;
1009
1
1010
1
1011
SELECT 1 FROM t1 ORDER BY SUM(a), b;
1012
1
1013
1
1014
SELECT a FROM t1 ORDER BY COUNT(b);
1015
a
1016
3
1017
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2);
1018
a
1019
3
1020
2
1021
3
1022
2
1023
3
1024
4
1025
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a);
1026
a
1027
3
1028
2
1029
3
1030
2
1031
3
1032
4
1033
SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
1034
a
1035
3
1036
2
1037
3
1038
2
1039
3
1040
4
1041
SELECT t1.a FROM t1
1042
WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
1043
a
1044
3
1045
3
1046
3
1047
SELECT t1.a FROM t1 GROUP BY t1.a
1048
HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1049
a
1050
3
1051
SELECT t1.a FROM t1 GROUP BY t1.a
1052
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
1053
a
1054
2
1055
3
1056
4
1057
SELECT t1.a FROM t1 GROUP BY t1.a
1058
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
1059
a
1060
3
1061
SELECT t1.a FROM t1 GROUP BY t1.a
1062
HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
1063
a
1064
4
1065
SELECT t1.a FROM t1
1066
WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
1067
a
1068
3
1069
3
1070
3
1071
SELECT 1 FROM t1 GROUP BY t1.a
1072
HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1073
1
1074
1
1075
1
1076
1
1077
SELECT 1 FROM t1 GROUP BY t1.a
1078
HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1079
1
1080
1
1081
1
1082
1
1083
SELECT 1 FROM t1 GROUP BY t1.a
1084
HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1085
1
1086
1
1087
1
1088
1
1089
SELECT 1 FROM t1 GROUP BY t1.a
1090
HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1);
1091
1
1092
1
1093
1
1094
1
1095
SELECT 1 FROM t1 GROUP BY t1.a
1096
HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
1097
1
1098
1
1099
1
1100
1
1101
SELECT 1 FROM t1 GROUP BY t1.a
1102
HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
1103
1
1104
1
1105
1
1106
1
1107
SELECT t1.a FROM t1
1108
WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a
1109
ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1);
1110
a
1111
4
1112
SELECT t1.a, SUM(t1.b) FROM t1
1113
WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a
1114
ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1)
1115
GROUP BY t1.a;
1116
a SUM(t1.b)
1117
4 4
1118
SELECT t1.a, SUM(t1.b) FROM t1
1119
WHERE t1.a = (SELECT SUM(t2.b) FROM t2
1120
ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1)
1121
GROUP BY t1.a;
1122
a SUM(t1.b)
1123
SELECT t1.a, SUM(t1.b) FROM t1
1124
WHERE t1.a = (SELECT SUM(t2.b) FROM t2
1125
ORDER BY SUM(t2.b + t1.a) LIMIT 1)
1126
GROUP BY t1.a;
1127
a SUM(t1.b)
1128
SELECT t1.a FROM t1 GROUP BY t1.a
1129
HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);
1130
a
1131
select avg (
1132
(select
1133
(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
1134
from t1 as outr order by outr.a limit 1))
1135
from t1 as most_outer;
1136
avg (
1137
(select
1138
(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
1139
from t1 as outr order by outr.a limit 1))
1140
29.0000
1141
select avg (
1142
(select (
1143
(select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt
1144
from t1 as outr order by count(outr.a) limit 1)) as tt
1145
from t1 as most_outer;
1146
tt
1147
35.0000
1148
select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a;
1149
tt
1150
29
1151
29
1152
35
1153
35
1154
35
1155
41
1156
DROP TABLE t1, t2;
1157
End of 5.0 tests
1158
CREATE TABLE t1 (a INT, b INT,
1159
PRIMARY KEY (a),
1160
KEY i2(a,b));
1161
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
1162
INSERT INTO t1 SELECT a + 8,b FROM t1;
1163
INSERT INTO t1 SELECT a + 16,b FROM t1;
1164
INSERT INTO t1 SELECT a + 32,b FROM t1;
1165
INSERT INTO t1 SELECT a + 64,b FROM t1;
1166
INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16;
1167
ANALYZE TABLE t1;
1168
Table Op Msg_type Msg_text
1169
test.t1 analyze status OK
1170
EXPLAIN SELECT a FROM t1 WHERE a < 2;
1171
id select_type table type possible_keys key key_len ref rows Extra
1172
1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 1 Using where; Using index
1173
EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
1174
id select_type table type possible_keys key key_len ref rows Extra
1175
1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 1 Using where; Using index
1176
EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
1177
id select_type table type possible_keys key key_len ref rows Extra
1178
1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 1 Using where; Using index for group-by
1179
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
1180
id select_type table type possible_keys key key_len ref rows Extra
1181
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1182
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
1183
id select_type table type possible_keys key key_len ref rows Extra
1184
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1185
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
1186
id select_type table type possible_keys key key_len ref rows Extra
1187
1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index
1188
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
1189
id select_type table type possible_keys key key_len ref rows Extra
1190
1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index
1191
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
1192
a
1193
1
1194
2
1195
3
1196
4
1197
5
1198
6
1199
7
1200
8
1201
9
1202
10
1203
11
1204
12
1205
13
1206
14
1207
15
1208
16
1209
17
1210
18
1211
19
1212
20
1213
21
1214
22
1215
23
1216
24
1217
25
1218
26
1219
27
1220
28
1221
29
1222
30
1223
31
1224
32
1225
33
1226
34
1227
35
1228
36
1229
37
1230
38
1231
39
1232
40
1233
41
1234
42
1235
43
1236
44
1237
45
1238
46
1239
47
1240
48
1241
49
1242
50
1243
51
1244
52
1245
53
1246
54
1247
55
1248
56
1249
57
1250
58
1251
59
1252
60
1253
61
1254
62
1255
63
1256
64
1257
65
1258
66
1259
67
1260
68
1261
69
1262
70
1263
71
1264
72
1265
73
1266
74
1267
75
1268
76
1269
77
1270
78
1271
79
1272
80
1273
81
1274
82
1275
83
1276
84
1277
85
1278
86
1279
87
1280
88
1281
89
1282
90
1283
91
1284
92
1285
93
1286
94
1287
95
1288
96
1289
97
1290
98
1291
99
1292
100
1293
101
1294
102
1295
103
1296
104
1297
105
1298
106
1299
107
1300
108
1301
109
1302
110
1303
111
1304
112
1305
113
1306
114
1307
115
1308
116
1309
117
1310
118
1311
119
1312
120
1313
121
1314
122
1315
123
1316
124
1317
125
1318
126
1319
127
1320
128
1321
129
1322
130
1323
131
1324
132
1325
133
1326
134
1327
135
1328
136
1329
137
1330
138
1331
139
1332
140
1333
141
1334
142
1335
143
1336
144
1337
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
1338
IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
1339
id select_type table type possible_keys key key_len ref rows Extra
1340
1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index
1341
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
1342
id select_type table type possible_keys key key_len ref rows Extra
1343
1 SIMPLE t1 index NULL i2 9 NULL 144 Using index
1344
EXPLAIN SELECT a FROM t1 FORCE INDEX (i2);
1345
id select_type table type possible_keys key key_len ref rows Extra
1346
1 SIMPLE t1 index NULL i2 9 NULL 144 Using index
1347
EXPLAIN SELECT a FROM t1 USE INDEX ();
1348
id select_type table type possible_keys key key_len ref rows Extra
1349
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1350
EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);
1351
id select_type table type possible_keys key key_len ref rows Extra
1352
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1353
EXPLAIN SELECT a FROM t1
1354
FORCE INDEX (PRIMARY)
1355
IGNORE INDEX FOR GROUP BY (i2)
1356
IGNORE INDEX FOR ORDER BY (i2)
1357
USE INDEX (i2);
1358
ERROR HY000: Incorrect usage of USE INDEX and FORCE INDEX
1359
EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
1360
id select_type table type possible_keys key key_len ref rows Extra
1361
1 SIMPLE t1 index NULL i2 9 NULL 144 Using index
1362
EXPLAIN SELECT a FROM t1 FORCE INDEX ();
1363
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ')' at line 1
1364
EXPLAIN SELECT a FROM t1 IGNORE INDEX ();
1365
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ')' at line 1
1366
EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2)
1367
USE INDEX FOR GROUP BY (i2) GROUP BY a;
1368
id select_type table type possible_keys key key_len ref rows Extra
1369
1 SIMPLE t1 # NULL i2 # NULL # #
1370
EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2)
1371
FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
1372
id select_type table type possible_keys key key_len ref rows Extra
1373
1 SIMPLE t1 range NULL i2 4 NULL 145 Using index for group-by
1374
EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
1375
id select_type table type possible_keys key key_len ref rows Extra
1376
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1377
EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX ();
1378
id select_type table type possible_keys key key_len ref rows Extra
1379
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1380
EXPLAIN SELECT a FROM t1
1381
USE INDEX FOR GROUP BY (i2)
1382
USE INDEX FOR ORDER BY (i2)
1383
USE INDEX FOR JOIN (i2);
1384
id select_type table type possible_keys key key_len ref rows Extra
1385
1 SIMPLE t1 index NULL i2 9 NULL 144 Using index
1386
EXPLAIN SELECT a FROM t1
1387
USE INDEX FOR JOIN (i2)
1388
USE INDEX FOR JOIN (i2)
1389
USE INDEX FOR JOIN (i2,i2);
1390
id select_type table type possible_keys key key_len ref rows Extra
1391
1 SIMPLE t1 index NULL i2 9 NULL 144 Using index
1392
EXPLAIN SELECT 1 FROM t1 WHERE a IN
1393
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
1394
id select_type table type possible_keys key key_len ref rows Extra
1395
1 PRIMARY t1 index NULL PRIMARY 4 NULL 144 Using where; Using index
1396
2 SUBQUERY t1 ALL NULL NULL NULL NULL 144
1397
CREATE TABLE t2 (a INT, b INT, KEY(a));
1398
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
1399
EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
1400
id select_type table type possible_keys key key_len ref rows Extra
1401
1 SIMPLE t2 index NULL a 5 NULL 2
1402
EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
1403
id select_type table type possible_keys key key_len ref rows Extra
1404
1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
1405
EXPLAIN SELECT 1 FROM t2 WHERE a IN
1406
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
1407
id select_type table type possible_keys key key_len ref rows Extra
1408
1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
1409
2 SUBQUERY t1 ALL NULL NULL NULL NULL 144
1410
DROP TABLE t1, t2;
1411
CREATE TABLE t1(
1412
a INT,
1413
b INT NOT NULL,
1414
c INT NOT NULL,
1415
d INT,
1416
UNIQUE KEY (c,b)
1417
);
1418
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
1419
CREATE TABLE t2(
1420
a INT,
1421
b INT,
1422
UNIQUE KEY(a,b)
1423
);
1424
INSERT INTO t2 VALUES (NULL, NULL), (NULL, NULL), (NULL, 1), (1, NULL), (1, 1), (1,2);
1425
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
1426
id select_type table type possible_keys key key_len ref rows Extra
1427
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1428
SELECT c,b,d FROM t1 GROUP BY c,b,d;
1429
c b d
1430
1 1 50
1431
3 1 4
1432
3 2 40
1433
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
1434
id select_type table type possible_keys key key_len ref rows Extra
1435
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
1436
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
1437
c b d
1438
1 1 50
1439
3 2 40
1440
3 1 4
1441
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
1442
id select_type table type possible_keys key key_len ref rows Extra
1443
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1444
SELECT c,b,d FROM t1 ORDER BY c,b,d;
1445
c b d
1446
1 1 50
1447
3 1 4
1448
3 2 40
1449
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
1450
id select_type table type possible_keys key key_len ref rows Extra
1451
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1452
SELECT c,b,d FROM t1 GROUP BY c,b;
1453
c b d
1454
1 1 50
1455
3 1 4
1456
3 2 40
1457
EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
1458
id select_type table type possible_keys key key_len ref rows Extra
1459
1 SIMPLE t1 index NULL c 8 NULL 3 Using index
1460
SELECT c,b FROM t1 GROUP BY c,b;
1461
c b
1462
1 1
1463
3 1
1464
3 2
1465
EXPLAIN SELECT a,b from t2 ORDER BY a,b;
1466
id select_type table type possible_keys key key_len ref rows Extra
1467
1 SIMPLE t2 index NULL a 10 NULL 6 Using index
1468
SELECT a,b from t2 ORDER BY a,b;
1469
a b
1470
NULL NULL
1471
NULL NULL
1472
NULL 1
1473
1 NULL
1474
1 1
1475
1 2
1476
EXPLAIN SELECT a,b from t2 GROUP BY a,b;
1477
id select_type table type possible_keys key key_len ref rows Extra
1478
1 SIMPLE t2 index NULL a 10 NULL 6 Using index
1479
SELECT a,b from t2 GROUP BY a,b;
1480
a b
1481
NULL NULL
1482
NULL 1
1483
1 NULL
1484
1 1
1485
1 2
1486
EXPLAIN SELECT a from t2 GROUP BY a;
1487
id select_type table type possible_keys key key_len ref rows Extra
1488
1 SIMPLE t2 index NULL a 10 NULL 6 Using index
1489
SELECT a from t2 GROUP BY a;
1490
a
1491
NULL
1492
1
1493
EXPLAIN SELECT b from t2 GROUP BY b;
1494
id select_type table type possible_keys key key_len ref rows Extra
1495
1 SIMPLE t2 index NULL a 10 NULL 6 Using index; Using temporary; Using filesort
1496
SELECT b from t2 GROUP BY b;
1497
b
1498
NULL
1499
1
1500
2
1501
DROP TABLE t1,t2;
1502
CREATE TABLE t1 ( a INT, b INT );
1503
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1504
FROM t1;
1505
c (SELECT a FROM t1 WHERE b = c)
1506
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1507
FROM t1
1508
HAVING b = 10;
1509
c (SELECT a FROM t1 WHERE b = c)
1510
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1511
FROM t1
1512
HAVING b = 10;
1513
ERROR 42S22: Reference 'c' not supported (reference to group function)
1514
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1515
FROM t1;
1516
c (SELECT a FROM t1 WHERE b = c)
1517
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1518
FROM t1
1519
HAVING b = 10;
1520
c (SELECT a FROM t1 WHERE b = c)
1521
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1522
FROM t1
1523
HAVING b = 10;
1524
ERROR 42S22: Reference 'c' not supported (reference to group function)
1525
INSERT INTO t1 VALUES (1, 1);
1526
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1527
FROM t1;
1528
c (SELECT a FROM t1 WHERE b = c)
1529
1 1
1530
INSERT INTO t1 VALUES (2, 1);
1531
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1532
FROM t1;
1533
ERROR 21000: Subquery returns more than 1 row
1534
DROP TABLE t1;
Loggerhead is a web-based interface for Breezy
Version: 2.0.1