← Back to branch summary

~stewart/drizzle/embedded-innodb-create-select-transaction-arrgh

« back to all changes in this revision

Viewing changes to mysql-test/r/group_by.result

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

Show diffs side-by-side

added added

removed removed

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