4
drop table if exists t1,t2,t3;
8
# Simple test without tables
10
SELECT 1 FROM (SELECT 1) as a GROUP BY SUM(1);
13
# Test of group (Failed for Lars Hoss <lh@pbm.de>)
24
INSERT INTO t1 VALUES (1,1,1,'','0000-00-00');
25
INSERT INTO t1 VALUES (2,2,2,'','0000-00-00');
26
INSERT INTO t1 VALUES (2,1,1,'','0000-00-00');
27
INSERT INTO t1 VALUES (3,3,3,'','0000-00-00');
30
userID int NOT NULL auto_increment,
34
isAukt enum('N','Y') DEFAULT 'N',
44
INSERT INTO t2 VALUES (1,'name','pass','mail','Y','v','n','adr','1','1','1');
45
INSERT INTO t2 VALUES (2,'name','pass','mail','Y','v','n','adr','1','1','1');
46
INSERT INTO t2 VALUES (3,'name','pass','mail','Y','v','n','adr','1','1','1');
47
INSERT INTO t2 VALUES (4,'name','pass','mail','Y','v','n','adr','1','1','1');
48
INSERT INTO t2 VALUES (5,'name','pass','mail','Y','v','n','adr','1','1','1');
50
SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid;
51
SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid ORDER BY NULL;
52
SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid;
53
SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid;
54
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;
55
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;
59
# Bug in GROUP BY, by Nikki Chumakov <nikki@saddam.cityline.ru>
63
PID int NOT NULL auto_increment,
64
payDate date DEFAULT '0000-00-00' NOT NULL,
65
recDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
66
URID int DEFAULT '0' NOT NULL,
67
CRID int DEFAULT '0' NOT NULL,
68
amount int DEFAULT '0' NOT NULL,
70
method enum('unknown','cash','dealer','check','card','lazy','delayed','test') DEFAULT 'unknown' NOT NULL,
72
reason char(1) binary DEFAULT '' NOT NULL,
74
qty int DEFAULT '0' NOT NULL,
82
INSERT INTO t1 VALUES (1,'1970-01-01','1997-10-17 00:00:00',2529,1,21000,11886,'check',0,'F',16200,6);
85
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;
90
# Problem with GROUP BY + ORDER BY when no match
95
cid int NOT NULL auto_increment,
96
firstname varchar(32) DEFAULT '' NOT NULL,
97
surname varchar(32) DEFAULT '' NOT NULL,
100
INSERT INTO t1 VALUES (1,'That','Guy');
101
INSERT INTO t1 VALUES (2,'Another','Gent');
104
call_id int NOT NULL auto_increment,
105
contact_id int DEFAULT '0' NOT NULL,
106
PRIMARY KEY (call_id),
107
KEY contact_id (contact_id)
110
lock tables t1 read,t2 write;
112
INSERT INTO t2 VALUES (10,2);
113
INSERT INTO t2 VALUES (18,2);
114
INSERT INTO t2 VALUES (62,2);
115
INSERT INTO t2 VALUES (91,2);
116
INSERT INTO t2 VALUES (92,2);
118
SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid;
119
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;
120
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;
126
# Test needs to be rewritten
128
## Test of group by bug in bugzilla
132
# bug_id int NOT NULL auto_increment,
133
# groupset bigint DEFAULT '0' NOT NULL,
134
# assigned_to int DEFAULT '0' NOT NULL,
136
# bug_severity enum('blocker','critical','major','normal','minor','trivial','enhancement') DEFAULT 'blocker' NOT NULL,
137
# bug_status enum('','NEW','ASSIGNED','REOPENED','RESOLVED','VERIFIED','CLOSED') DEFAULT 'NEW' NOT NULL,
138
# creation_ts datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
139
# delta_ts timestamp,
140
# short_desc mediumtext,
141
# long_desc mediumtext,
142
# op_sys enum('All','Windows 3.1','Windows 95','Windows 98','Windows NT','Windows 2000','Linux','other') DEFAULT 'All' NOT NULL,
143
# priority enum('P1','P2','P3','P4','P5') DEFAULT 'P1' NOT NULL,
144
# product varchar(64) DEFAULT '' NOT NULL,
145
# rep_platform enum('All','PC','VTD-8','Other'),
146
# reporter int DEFAULT '0' NOT NULL,
147
# version varchar(16) DEFAULT '' NOT NULL,
148
# component varchar(50) DEFAULT '' NOT NULL,
149
# resolution enum('','FIXED','INVALID','WONTFIX','LATER','REMIND','DUPLICATE','WORKSFORME') DEFAULT '' NOT NULL,
150
# target_milestone varchar(20) DEFAULT '' NOT NULL,
151
# qa_contact int DEFAULT '0' NOT NULL,
152
# status_whiteboard mediumtext NOT NULL,
153
# votes int DEFAULT '0' NOT NULL,
154
# PRIMARY KEY (bug_id),
155
# KEY assigned_to (assigned_to),
156
# KEY creation_ts (creation_ts),
157
# KEY delta_ts (delta_ts),
158
# KEY bug_severity (bug_severity),
159
# KEY bug_status (bug_status),
160
# KEY op_sys (op_sys),
161
# KEY priority (priority),
162
# KEY product (product),
163
# KEY reporter (reporter),
164
# KEY version (version),
165
# KEY component (component),
166
# KEY resolution (resolution),
167
# KEY target_milestone (target_milestone),
168
# KEY qa_contact (qa_contact),
173
#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);
174
#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);
175
#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);
176
#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);
177
#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);
178
#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);
179
#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);
180
#INSERT INTO t1 VALUES (4,0,0,'','normal','','2000-03-08 18:32:14',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent2','','',0,'',0);
181
#INSERT INTO t1 VALUES (3,0,0,'','normal','','2000-03-08 18:30:52',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent','','',0,'',0);
182
#INSERT INTO t1 VALUES (2,0,0,'','enhancement','','2000-03-08 18:24:51',20000321114747,'','','All','P2','TestProduct','Other',4,'other','TestComponent2','','',0,'',0);
183
#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);
184
#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);
185
#INSERT INTO t1 VALUES (13,0,0,'','normal','','2000-03-15 16:20:44',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent','','',0,'',0);
186
#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);
187
#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);
188
#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);
189
#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);
190
#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);
191
#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);
192
#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);
193
#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);
194
#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);
195
#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);
196
#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);
197
#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);
198
#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);
199
#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);
200
#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);
201
#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);
204
# program varchar(64),
205
# initialowner text NOT NULL,
206
# initialqacontact text NOT NULL,
207
# description text NOT NULL
210
#INSERT INTO t2 VALUES ('TestComponent','TestProduct','id0001','','');
211
#INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - conversion','AAAAA','id0001','','');
212
#INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - generic','AAAAA','id0001','','');
213
#INSERT INTO t2 VALUES ('TestComponent2','TestProduct','id0001','','');
214
#INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - eeeeeeeee','AAAAA','id0001','','');
215
#INSERT INTO t2 VALUES ('kkkkkkkkkkk lllllllllll','AAAAA','id0001','','');
216
#INSERT INTO t2 VALUES ('Test Procedures','AAAAA','id0001','','');
217
#INSERT INTO t2 VALUES ('Documentation','AAAAA','id0003','','');
218
#INSERT INTO t2 VALUES ('DDDDDDDDD','CCC/CCCCCC','id0002','','');
219
#INSERT INTO t2 VALUES ('Eeeeeeee Lite','CCC/CCCCCC','id0002','','');
220
#INSERT INTO t2 VALUES ('Eeeeeeee Full','CCC/CCCCCC','id0002','','');
221
#INSERT INTO t2 VALUES ('Administration','CCC/CCCCCC','id0002','','');
222
#INSERT INTO t2 VALUES ('Distribution','CCC/CCCCCC','id0002','','');
223
#INSERT INTO t2 VALUES ('Setup','CCC/CCCCCC','id0002','','');
224
#INSERT INTO t2 VALUES ('Unspecified','CCC/CCCCCC','id0002','','');
225
#INSERT INTO t2 VALUES ('Web Interface','AAAAAAAA-AAA','id0001','','');
226
#INSERT INTO t2 VALUES ('Host communication','AAAAA','id0001','','');
227
#select value,description,bug_id from t2 left join t1 on t2.program=t1.product and t2.value=t1.component where program="AAAAA";
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;
229
#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);
234
# Problem with functions and group functions when no matching rows
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
insert into t1 values (1);
241
select 1+1,"a",count(*) from t1 where foo in (2);
255
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);
256
explain select userid,count(*) from t1 group by userid desc;
257
explain select userid,count(*) from t1 group by userid desc order by null;
258
select userid,count(*) from t1 group by userid desc;
259
select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3);
260
select userid,count(*) from t1 group by userid desc having 3 IN (1,COUNT(*));
261
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
262
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid;
263
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null;
264
select spid,count(*) from t1 where spid between 1 and 2 group by spid;
265
select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
266
explain extended select sql_big_result spid,sum(userid) from t1 group by spid desc;
267
explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null;
268
select sql_big_result spid,sum(userid) from t1 group by spid desc;
269
explain select sql_big_result score,count(*) from t1 group by score desc;
270
explain select sql_big_result score,count(*) from t1 group by score desc order by null;
271
select sql_big_result score,count(*) from t1 group by score desc;
274
# not purely group_by bug, but group_by is involved...
276
create table t1 (a date default null, b date default null);
277
insert t1 values ('1999-10-01','2000-01-10'), ('1997-01-01','1998-10-01');
278
select a,min(b) c,count(distinct rand()) from t1 group by a having c<a + interval 1 day;
281
# Compare with hash keys
283
CREATE TABLE t1 (a char(1));
284
INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL);
285
SELECT a FROM t1 GROUP BY a;
286
SELECT a,count(*) FROM t1 GROUP BY a;
287
SELECT a FROM t1 GROUP BY binary a;
288
SELECT a,count(*) FROM t1 GROUP BY binary a;
289
SELECT binary a FROM t1 GROUP BY 1;
290
SELECT binary a,count(*) FROM t1 GROUP BY 1;
291
# Do the same tests with MyISAM temporary tables
292
SELECT a FROM t1 GROUP BY a;
293
SELECT a,count(*) FROM t1 GROUP BY a;
294
SELECT a FROM t1 GROUP BY binary a;
295
SELECT a,count(*) FROM t1 GROUP BY binary a;
296
SELECT binary a FROM t1 GROUP BY 1;
297
SELECT binary a,count(*) FROM t1 GROUP BY 1;
301
# Test of key >= 256 bytes
305
`a` char(193) default NULL,
306
`b` char(63) default NULL
308
INSERT INTO t1 VALUES ('abc','def'),('hij','klm');
309
SELECT CONCAT(a, b) FROM t1 GROUP BY 1;
310
SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;
311
SELECT CONCAT(a, b),count(distinct a) FROM t1 GROUP BY 1;
312
SELECT 1 FROM t1 GROUP BY CONCAT(a, b);
313
INSERT INTO t1 values ('hij','klm');
314
SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;
318
# Test problem with ORDER BY on a SUM() column
321
create table t1 (One int, Two int, Three int, Four int);
322
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);
323
select One, Two, sum(Four) from t1 group by One,Two;
326
create table t1 (id integer primary key not null auto_increment, gender char(1));
327
insert into t1 values (NULL, 'M'), (NULL, 'F'),(NULL, 'F'),(NULL, 'F'),(NULL, 'M');
328
create table t2 (user_id integer not null, date date);
329
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');
330
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;
331
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;
335
# The GROUP BY returned rows in wrong order in 3.23.51
338
CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID
340
insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL);
341
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;
342
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;
346
# Problem with MAX and LEFT JOIN
350
pid int NOT NULL default '0',
351
c1id int default NULL,
352
c2id int default NULL,
353
value int NOT NULL default '0',
354
UNIQUE KEY pid2 (pid,c1id,c2id),
355
UNIQUE KEY pid (pid,value)
358
INSERT INTO t1 VALUES (1, 1, NULL, 1),(1, 2, NULL, 2),(1, NULL, 3, 3),(1, 4, NULL, 4),(1, 5, NULL, 5);
361
id int NOT NULL default '0',
362
active enum('Yes','No') NOT NULL default 'Yes',
366
INSERT INTO t2 VALUES (1, 'Yes'),(2, 'No'),(4, 'Yes'),(5, 'No');
369
id int NOT NULL default '0',
370
active enum('Yes','No') NOT NULL default 'Yes',
373
INSERT INTO t3 VALUES (3, 'Yes');
375
select * from t1 AS m LEFT JOIN t2 AS c1 ON m.c1id =
376
c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = c2.id AND
377
c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS NOT NULL);
378
select max(value) from t1 AS m LEFT JOIN t2 AS c1 ON
379
m.c1id = c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id =
380
c2.id AND c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS
385
# Test bug in GROUP BY on BLOB that is NULL or empty
388
create table t1 (a blob null);
389
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(""),(""),(""),("b");
390
select a,count(*) from t1 group by a;
391
select a,count(*) from t1 group by a;
395
# Test of GROUP BY ... ORDER BY NULL optimization
398
create table t1 (a int not null, b int not null);
399
insert into t1 values (1,1),(1,2),(3,1),(3,2),(2,2),(2,1);
400
create table t2 (a int not null, b int not null, key(a));
401
insert into t2 values (1,3),(3,1),(2,2),(1,1);
402
select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
403
select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
404
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
405
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
409
# group function arguments in some functions
412
create table t1 (a int, b int);
413
insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
414
select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a;
415
select a, MAX(b), CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end from t1 group by a;
416
select a, MAX(b), FIELD(MAX(b), '43', '4', '5') from t1 group by a;
417
select a, MAX(b), CONCAT_WS(MAX(b), '43', '4', '5') from t1 group by a;
418
select a, MAX(b), ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f') from t1 group by a;
419
select a, MAX(b), MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') from t1 group by a;
423
# Problem with group by and alias
426
create table t1 (id int not null, qty int not null);
427
insert into t1 values (1,2),(1,3),(2,4),(2,5);
428
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and cqty>1;
429
select id, sum(qty) as sqty from t1 group by id having sqty>2 and count(qty)>1;
430
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sqty>2 and cqty>1;
431
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and count(qty)>1;
432
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;
433
select count(*), interval(qty,2,3,4,5,6,7,8) as category from t1 group by category;
436
# Tests for bug #1355: 'Using filesort' is missing in EXPLAIN when ORDER BY
444
INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3);
445
# Here we select unordered GROUP BY into a temporary talbe,
446
# and then sort it with filesort (GROUP BY in MySQL
447
# implies sorted order of results)
448
SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
449
EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
455
INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5);
456
SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
457
explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
460
#Test for BUG#6976: Aggregate functions have incorrect NULL-ness
461
create table t1 (a int);
462
insert into t1 values(null);
463
select min(a) is null from t1;
464
select min(a) is null or null from t1;
465
select 1 and min(a) is null from t1;
468
# Test for BUG#5400: GROUP_CONCAT returns everything twice.
469
create table t1 ( col1 int, col2 int );
470
insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
471
select group_concat( distinct col1 ) as alias from t1
472
group by col2 having alias like '%';
477
# Test BUG#8216 when referring in HAVING to n alias which is rand() function
480
create table t1 (a integer, b integer, c integer);
481
insert into t1 (a,b) values (1,2),(1,3),(2,5);
482
select a, 0.1*0+1 r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2=1;
483
# rand(100)*10 will be < 2 only for the first row (of 6)
484
select a, round(rand(100)*10) r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2<=2;
485
select a,sum(b) from t1 where a=1 group by c;
486
select a*sum(b) from t1 where a=1 group by c;
487
select sum(a)*sum(b) from t1 where a=1 group by c;
488
select a,sum(b) from t1 where a=1 group by c having a=1;
489
select a as d,sum(b) from t1 where a=1 group by c having d=1;
490
select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;
493
# Test for BUG#9213 GROUP BY query on utf-8 key returns wrong results
494
create table t1(a int);
495
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9);
498
b varchar(200) NOT NULL,
499
c varchar(50) NOT NULL,
500
d varchar(100) NOT NULL,
501
primary key (a,b(132),c,d),
505
insert into t2 select
507
concat('val-', x3.a + 3*x4.a), -- 12
508
concat('val-', @a:=x3.a + 3*x4.a + 12*C.a), -- 120
509
concat('val-', @a + 120*D.a)
510
from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4;
512
delete from t2 where a = 2 and b = 'val-2' order by a,b,c,d limit 30;
514
explain select c from t2 where a = 2 and b = 'val-2' group by c;
515
select c from t2 where a = 2 and b = 'val-2' group by c;
518
# Test for BUG#9298 "Wrong handling of int4 columns in GROUP functions"
519
# (the actual problem was with protocol code, not GROUP BY)
520
create table t1 (b int4 not null);
521
insert into t1 values(300000);
523
select min(b) from t1;
527
# Test for bug #11088: GROUP BY a BLOB column with COUNT(DISTINCT column1)
530
CREATE TABLE t1 (id int PRIMARY KEY, user_id int, hostname longtext);
532
INSERT INTO t1 VALUES
533
(1, 7, 'cache-dtc-af05.proxy.aol.com'),
534
(2, 3, 'what.ever.com'),
535
(3, 7, 'cache-dtc-af05.proxy.aol.com'),
536
(4, 7, 'cache-dtc-af05.proxy.aol.com');
538
SELECT hostname, COUNT(DISTINCT user_id) as no FROM t1
539
WHERE hostname LIKE '%aol%'
545
# Test for bug #8614: GROUP BY 'const' with DISTINCT
548
CREATE TABLE t1 (a int, b int);
549
INSERT INTO t1 VALUES (1,2), (1,3);
550
SELECT a, b FROM t1 GROUP BY 'const';
551
SELECT DISTINCT a, b FROM t1 GROUP BY 'const';
556
# Test for bug #11385: GROUP BY for datetime converted to decimals
559
CREATE TABLE t1 (id INT, dt DATETIME);
560
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
561
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
562
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
563
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
564
SELECT dt DIV 1 AS f, id FROM t1 GROUP BY f;
569
# Test for bug #11295: GROUP BY a BLOB column with COUNT(DISTINCT column1)
570
# when the BLOB column takes NULL values
573
CREATE TABLE t1 (id varchar(20) NOT NULL);
574
INSERT INTO t1 VALUES ('trans1'), ('trans2');
575
CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL);
576
INSERT INTO t2 VALUES ('trans1', 'a problem');
577
SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment
578
FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment;
583
# Bug #12266 GROUP BY expression on DATE column produces result with
586
create table t1 (f1 date);
587
insert into t1 values('2005-06-06');
588
insert into t1 values('2005-06-06');
589
select date(left(f1+0,8)) from t1 group by 1;
593
# Test for bug #11414: crash on Windows for a simple GROUP BY query
596
CREATE TABLE t1 (n int);
597
INSERT INTO t1 VALUES (1);
598
SELECT n+1 AS n FROM t1 GROUP BY n;
602
# BUG#12695: Item_func_isnull::update_used_tables
603
# did not update const_item_cache
605
create table t1(f1 varchar(5) key);
606
insert into t1 values (1),(2);
607
select sql_buffer_result max(f1) is null from t1;
608
select sql_buffer_result max(f1)+1 from t1;
614
CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1),(2);
616
SELECT a FROM t1 GROUP BY 'a';
617
SELECT a FROM t1 GROUP BY "a";
618
SELECT a FROM t1 GROUP BY `a`;
620
SELECT a FROM t1 GROUP BY "a";
621
SELECT a FROM t1 GROUP BY 'a';
622
SELECT a FROM t1 GROUP BY `a`;
624
SELECT a FROM t1 HAVING 'a' > 1;
625
SELECT a FROM t1 HAVING "a" > 1;
626
SELECT a FROM t1 HAVING `a` > 1;
628
SELECT a FROM t1 ORDER BY 'a' DESC;
629
SELECT a FROM t1 ORDER BY "a" DESC;
630
SELECT a FROM t1 ORDER BY `a` DESC;
634
# Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself
638
f1 int NOT NULL auto_increment primary key,
639
f2 varchar(100) NOT NULL default ''
642
f1 varchar(10) NOT NULL default '',
643
f2 char(3) NOT NULL default '',
648
INSERT INTO t1 values(NULL, '');
649
INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
650
SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
651
SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
658
# Bug#11211: Ambiguous column reference in GROUP BY.
661
create table t1 (c1 char(3), c2 char(3));
662
create table t2 (c3 char(3), c4 char(3));
663
insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
664
insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
666
# query with ambiguous column reference 'c2'
667
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
671
# this query has no ambiguity
672
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
679
# Bug#22781: SQL_BIG_RESULT fails to influence sort plan
681
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));
683
INSERT INTO t1 VALUES (1, 1);
684
INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1;
685
INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1;
686
INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1;
687
INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1;
688
INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1;
689
INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1;
690
INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1;
692
SELECT MIN(b), MAX(b) from t1;
694
EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;
695
EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
696
SELECT b, sum(1) FROM t1 GROUP BY b;
697
SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
701
# Bug #23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode
703
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
704
INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
706
SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b;
707
SELECT CEILING(MIN(a)) FROM t1 GROUP BY b;
708
SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1
710
SELECT a + 1 FROM t1 GROUP BY a;
711
SELECT a + b FROM t1 GROUP BY b;
712
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
714
SELECT 1 FROM t1 as t1_outer GROUP BY a
715
HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
716
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1)
717
FROM t1 AS t1_outer GROUP BY t1_outer.b;
718
--error ER_BAD_FIELD_ERROR
719
SELECT 1 FROM t1 as t1_outer GROUP BY a
720
HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
721
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
722
FROM t1 AS t1_outer GROUP BY t1_outer.b;
723
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
725
SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1)
726
FROM t1 AS t1_outer GROUP BY t1_outer.b;
728
SELECT 1 FROM t1 as t1_outer
729
WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1);
731
SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0;
733
SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12;
734
SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1);
736
--error ER_BAD_FIELD_ERROR
737
SELECT 1 FROM t1 GROUP BY b HAVING a = 2;
738
--error ER_INVALID_GROUP_FUNC_USE
739
SELECT 1 FROM t1 GROUP BY SUM(b);
740
SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN
741
(SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a
742
HAVING SUM(t1_inner.b)+t1_outer.b > 5);
745
# Bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode.
747
create table t1(f1 int, f2 int);
748
select * from t1 group by f1;
749
select * from t1 group by f2;
750
select * from t1 group by f1, f2;
751
select t1.f1,t.* from t1, t1 t group by 1;
755
# Bug #32202: ORDER BY not working with GROUP BY
759
id INT AUTO_INCREMENT PRIMARY KEY,
764
INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
766
# Show that the test cases from the bug report pass
767
SELECT * FROM t1 ORDER BY c1;
768
SELECT * FROM t1 GROUP BY id ORDER BY c1;
770
# Show that DESC is handled correctly
771
SELECT * FROM t1 GROUP BY id ORDER BY id DESC;
773
# Show that results are correctly ordered when ORDER BY fields
774
# are a subset of GROUP BY ones
775
SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;
776
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;
777
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;
779
# Show that results are correctly ordered when GROUP BY fields
780
# are a subset of ORDER BY ones
781
SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1;
782
SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1;
783
SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC;
789
--echo # Bug#27219: Aggregate functions in ORDER BY.
791
CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
792
INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
793
CREATE TABLE t2 SELECT * FROM t1;
795
SELECT 1 FROM t1 ORDER BY COUNT(*);
796
SELECT 1 FROM t1 ORDER BY COUNT(*) + 1;
797
SELECT 1 FROM t1 ORDER BY COUNT(*) + a;
798
SELECT 1 FROM t1 ORDER BY COUNT(*), 1;
799
SELECT 1 FROM t1 ORDER BY COUNT(*), a;
801
SELECT 1 FROM t1 ORDER BY SUM(a);
802
SELECT 1 FROM t1 ORDER BY SUM(a + 1);
803
SELECT 1 FROM t1 ORDER BY SUM(a) + 1;
804
SELECT 1 FROM t1 ORDER BY SUM(a), b;
806
SELECT a FROM t1 ORDER BY COUNT(b);
808
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2);
810
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a);
811
SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
814
WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
815
SELECT t1.a FROM t1 GROUP BY t1.a
816
HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
818
SELECT t1.a FROM t1 GROUP BY t1.a
819
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
820
SELECT t1.a FROM t1 GROUP BY t1.a
821
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
822
SELECT t1.a FROM t1 GROUP BY t1.a
823
HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
826
WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
828
SELECT 1 FROM t1 GROUP BY t1.a
829
HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
830
SELECT 1 FROM t1 GROUP BY t1.a
831
HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
832
SELECT 1 FROM t1 GROUP BY t1.a
833
HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
835
SELECT 1 FROM t1 GROUP BY t1.a
836
HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1);
837
SELECT 1 FROM t1 GROUP BY t1.a
838
HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
839
SELECT 1 FROM t1 GROUP BY t1.a
840
HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
842
# Both SUMs are aggregated in the subquery, no mixture:
844
WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a
845
ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1);
847
# SUM(t1.b) is aggregated in the subquery, no mixture:
848
SELECT t1.a, SUM(t1.b) FROM t1
849
WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a
850
ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1)
853
# 2nd SUM(t1.b) is aggregated in the subquery, no mixture:
854
SELECT t1.a, SUM(t1.b) FROM t1
855
WHERE t1.a = (SELECT SUM(t2.b) FROM t2
856
ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1)
859
# SUM(t2.b + t1.a) is aggregated in the subquery, no mixture:
860
SELECT t1.a, SUM(t1.b) FROM t1
861
WHERE t1.a = (SELECT SUM(t2.b) FROM t2
862
ORDER BY SUM(t2.b + t1.a) LIMIT 1)
865
SELECT t1.a FROM t1 GROUP BY t1.a
866
HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);
870
(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
871
from t1 as outr order by outr.a limit 1))
872
from t1 as most_outer;
876
(select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt
877
from t1 as outr order by count(outr.a) limit 1)) as tt
878
from t1 as most_outer;
880
select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a;
884
--echo End of 5.0 tests
885
# Bug #21174: Index degrades sort performance and
886
# optimizer does not honor IGNORE INDEX.
889
CREATE TABLE t1 (a INT, b INT,
892
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
893
INSERT INTO t1 SELECT a + 8,b FROM t1;
894
INSERT INTO t1 SELECT a + 16,b FROM t1;
895
INSERT INTO t1 SELECT a + 32,b FROM t1;
896
INSERT INTO t1 SELECT a + 64,b FROM t1;
897
INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16;
899
EXPLAIN SELECT a FROM t1 WHERE a < 2;
900
EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
901
EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
902
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
903
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
904
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
905
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
906
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
907
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
908
IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
909
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
910
EXPLAIN SELECT a FROM t1 FORCE INDEX (i2);
911
EXPLAIN SELECT a FROM t1 USE INDEX ();
912
EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);
913
--error ER_WRONG_USAGE
914
EXPLAIN SELECT a FROM t1
915
FORCE INDEX (PRIMARY)
916
IGNORE INDEX FOR GROUP BY (i2)
917
IGNORE INDEX FOR ORDER BY (i2)
919
EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
920
--error ER_PARSE_ERROR
921
EXPLAIN SELECT a FROM t1 FORCE INDEX ();
922
--error ER_PARSE_ERROR
923
EXPLAIN SELECT a FROM t1 IGNORE INDEX ();
924
# disable the columns irrelevant to this test here. On some systems
925
# without support for large files the rowid is shorter and its size affects
926
# the cost calculations. This causes the optimizer to choose loose index
927
# scan over normal index access.
928
--replace_column 4 # 7 # 9 # 10 #
929
EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2)
930
USE INDEX FOR GROUP BY (i2) GROUP BY a;
931
EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2)
932
FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
933
EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
934
EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX ();
936
EXPLAIN SELECT a FROM t1
937
USE INDEX FOR GROUP BY (i2)
938
USE INDEX FOR ORDER BY (i2)
939
USE INDEX FOR JOIN (i2);
941
EXPLAIN SELECT a FROM t1
942
USE INDEX FOR JOIN (i2)
943
USE INDEX FOR JOIN (i2)
944
USE INDEX FOR JOIN (i2,i2);
946
EXPLAIN SELECT 1 FROM t1 WHERE a IN
947
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
949
CREATE TABLE t2 (a INT, b INT, KEY(a));
950
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
951
EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
952
EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
954
EXPLAIN SELECT 1 FROM t2 WHERE a IN
955
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
957
SHOW VARIABLES LIKE 'old';
958
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
964
# Bug#30596: GROUP BY optimization gives wrong result order
974
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
982
INSERT INTO t2 VALUES (NULL, NULL), (NULL, NULL), (NULL, 1), (1, NULL), (1, 1), (1,2);
984
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
985
SELECT c,b,d FROM t1 GROUP BY c,b,d;
986
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
987
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
988
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
989
SELECT c,b,d FROM t1 ORDER BY c,b,d;
991
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
992
SELECT c,b,d FROM t1 GROUP BY c,b;
993
EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
994
SELECT c,b FROM t1 GROUP BY c,b;
996
EXPLAIN SELECT a,b from t2 ORDER BY a,b;
997
SELECT a,b from t2 ORDER BY a,b;
998
EXPLAIN SELECT a,b from t2 GROUP BY a,b;
999
SELECT a,b from t2 GROUP BY a,b;
1000
EXPLAIN SELECT a from t2 GROUP BY a;
1001
SELECT a from t2 GROUP BY a;
1002
EXPLAIN SELECT b from t2 GROUP BY b;
1003
SELECT b from t2 GROUP BY b;
1008
# Bug #31797: error while parsing subqueries -- WHERE is parsed as HAVING
1010
CREATE TABLE t1 ( a INT, b INT );
1012
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1015
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1019
--error ER_ILLEGAL_REFERENCE
1020
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1024
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1027
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1031
--error ER_ILLEGAL_REFERENCE
1032
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1036
INSERT INTO t1 VALUES (1, 1);
1037
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1040
INSERT INTO t1 VALUES (2, 1);
1041
--error ER_SUBQUERY_NO_1_ROW
1042
SELECT b c, (SELECT a FROM t1 WHERE b = c)
added
removed
tests/t/group_by.test
