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(10) 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(10) 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(10) DEFAULT '0' NOT NULL,
67
CRID int(10) DEFAULT '0' NOT NULL,
68
amount int(10) 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 mediumint(8) 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 mediumint(9) 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 mediumint(8) NOT NULL auto_increment,
105
contact_id mediumint(8) 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;
127
# Test of group by bug in bugzilla
131
bug_id mediumint(9) NOT NULL auto_increment,
132
groupset bigint(20) DEFAULT '0' NOT NULL,
133
assigned_to mediumint(9) DEFAULT '0' NOT NULL,
135
bug_severity enum('blocker','critical','major','normal','minor','trivial','enhancement') DEFAULT 'blocker' NOT NULL,
136
bug_status enum('','NEW','ASSIGNED','REOPENED','RESOLVED','VERIFIED','CLOSED') DEFAULT 'NEW' NOT NULL,
137
creation_ts datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
139
short_desc mediumtext,
140
long_desc mediumtext,
141
op_sys enum('All','Windows 3.1','Windows 95','Windows 98','Windows NT','Windows 2000','Linux','other') DEFAULT 'All' NOT NULL,
142
priority enum('P1','P2','P3','P4','P5') DEFAULT 'P1' NOT NULL,
143
product varchar(64) DEFAULT '' NOT NULL,
144
rep_platform enum('All','PC','VTD-8','Other'),
145
reporter mediumint(9) DEFAULT '0' NOT NULL,
146
version varchar(16) DEFAULT '' NOT NULL,
147
component varchar(50) DEFAULT '' NOT NULL,
148
resolution enum('','FIXED','INVALID','WONTFIX','LATER','REMIND','DUPLICATE','WORKSFORME') DEFAULT '' NOT NULL,
149
target_milestone varchar(20) DEFAULT '' NOT NULL,
150
qa_contact mediumint(9) DEFAULT '0' NOT NULL,
151
status_whiteboard mediumtext NOT NULL,
152
votes mediumint(9) DEFAULT '0' NOT NULL,
153
PRIMARY KEY (bug_id),
154
KEY assigned_to (assigned_to),
155
KEY creation_ts (creation_ts),
156
KEY delta_ts (delta_ts),
157
KEY bug_severity (bug_severity),
158
KEY bug_status (bug_status),
160
KEY priority (priority),
161
KEY product (product),
162
KEY reporter (reporter),
163
KEY version (version),
164
KEY component (component),
165
KEY resolution (resolution),
166
KEY target_milestone (target_milestone),
167
KEY qa_contact (qa_contact),
171
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);
172
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);
173
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);
174
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);
175
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);
176
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);
177
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);
178
INSERT INTO t1 VALUES (4,0,0,'','normal','','2000-03-08 18:32:14',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent2','','',0,'',0);
179
INSERT INTO t1 VALUES (3,0,0,'','normal','','2000-03-08 18:30:52',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent','','',0,'',0);
180
INSERT INTO t1 VALUES (2,0,0,'','enhancement','','2000-03-08 18:24:51',20000321114747,'','','All','P2','TestProduct','Other',4,'other','TestComponent2','','',0,'',0);
181
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);
182
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);
183
INSERT INTO t1 VALUES (13,0,0,'','normal','','2000-03-15 16:20:44',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent','','',0,'',0);
184
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);
185
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);
186
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);
187
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);
188
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);
189
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);
190
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);
191
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);
192
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);
193
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);
194
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);
195
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);
196
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);
197
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);
198
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);
199
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);
203
initialowner tinytext NOT NULL,
204
initialqacontact tinytext NOT NULL,
205
description mediumtext NOT NULL
208
INSERT INTO t2 VALUES ('TestComponent','TestProduct','id0001','','');
209
INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - conversion','AAAAA','id0001','','');
210
INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - generic','AAAAA','id0001','','');
211
INSERT INTO t2 VALUES ('TestComponent2','TestProduct','id0001','','');
212
INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - eeeeeeeee','AAAAA','id0001','','');
213
INSERT INTO t2 VALUES ('kkkkkkkkkkk lllllllllll','AAAAA','id0001','','');
214
INSERT INTO t2 VALUES ('Test Procedures','AAAAA','id0001','','');
215
INSERT INTO t2 VALUES ('Documentation','AAAAA','id0003','','');
216
INSERT INTO t2 VALUES ('DDDDDDDDD','CCC/CCCCCC','id0002','','');
217
INSERT INTO t2 VALUES ('Eeeeeeee Lite','CCC/CCCCCC','id0002','','');
218
INSERT INTO t2 VALUES ('Eeeeeeee Full','CCC/CCCCCC','id0002','','');
219
INSERT INTO t2 VALUES ('Administration','CCC/CCCCCC','id0002','','');
220
INSERT INTO t2 VALUES ('Distribution','CCC/CCCCCC','id0002','','');
221
INSERT INTO t2 VALUES ('Setup','CCC/CCCCCC','id0002','','');
222
INSERT INTO t2 VALUES ('Unspecified','CCC/CCCCCC','id0002','','');
223
INSERT INTO t2 VALUES ('Web Interface','AAAAAAAA-AAA','id0001','','');
224
INSERT INTO t2 VALUES ('Host communication','AAAAA','id0001','','');
225
select value,description,bug_id from t2 left join t1 on t2.program=t1.product and t2.value=t1.component where program="AAAAA";
226
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;
227
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);
232
# Problem with functions and group functions when no matching rows
235
create table t1 (foo int);
236
insert into t1 values (1);
237
select 1+1, "a",count(*) from t1 where foo in (2);
238
insert into t1 values (1);
239
select 1+1,"a",count(*) from t1 where foo in (2);
253
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);
254
explain select userid,count(*) from t1 group by userid desc;
255
explain select userid,count(*) from t1 group by userid desc order by null;
256
select userid,count(*) from t1 group by userid desc;
257
select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3);
258
select userid,count(*) from t1 group by userid desc having 3 IN (1,COUNT(*));
259
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
260
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid;
261
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null;
262
select spid,count(*) from t1 where spid between 1 and 2 group by spid;
263
select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
264
explain extended select sql_big_result spid,sum(userid) from t1 group by spid desc;
265
explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null;
266
select sql_big_result spid,sum(userid) from t1 group by spid desc;
267
explain select sql_big_result score,count(*) from t1 group by score desc;
268
explain select sql_big_result score,count(*) from t1 group by score desc order by null;
269
select sql_big_result score,count(*) from t1 group by score desc;
272
# not purely group_by bug, but group_by is involved...
274
create table t1 (a date default null, b date default null);
275
insert t1 values ('1999-10-01','2000-01-10'), ('1997-01-01','1998-10-01');
276
select a,min(b) c,count(distinct rand()) from t1 group by a having c<a + interval 1 day;
279
# Compare with hash keys
281
CREATE TABLE t1 (a char(1));
282
INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL);
283
SELECT a FROM t1 GROUP BY a;
284
SELECT a,count(*) FROM t1 GROUP BY a;
285
SELECT a FROM t1 GROUP BY binary a;
286
SELECT a,count(*) FROM t1 GROUP BY binary a;
287
SELECT binary a FROM t1 GROUP BY 1;
288
SELECT binary a,count(*) FROM t1 GROUP BY 1;
289
# Do the same tests with MyISAM temporary tables
290
SET SQL_BIG_TABLES=1;
291
SELECT a FROM t1 GROUP BY a;
292
SELECT a,count(*) FROM t1 GROUP BY a;
293
SELECT a FROM t1 GROUP BY binary a;
294
SELECT a,count(*) FROM t1 GROUP BY binary a;
295
SELECT binary a FROM t1 GROUP BY 1;
296
SELECT binary a,count(*) FROM t1 GROUP BY 1;
297
SET SQL_BIG_TABLES=0;
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(11) NOT NULL default '0',
351
c1id int(11) default NULL,
352
c2id int(11) default NULL,
353
value int(11) 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(11) 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(11) 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
set option sql_big_tables=1;
392
select a,count(*) from t1 group by a;
396
# Test of GROUP BY ... ORDER BY NULL optimization
399
create table t1 (a int not null, b int not null);
400
insert into t1 values (1,1),(1,2),(3,1),(3,2),(2,2),(2,1);
401
create table t2 (a int not null, b int not null, key(a));
402
insert into t2 values (1,3),(3,1),(2,2),(1,1);
403
select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
404
select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
405
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
406
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
410
# group function arguments in some functions
413
create table t1 (a int, b int);
414
insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
415
select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a;
416
select a, MAX(b), CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end from t1 group by a;
417
select a, MAX(b), FIELD(MAX(b), '43', '4', '5') from t1 group by a;
418
select a, MAX(b), CONCAT_WS(MAX(b), '43', '4', '5') from t1 group by a;
419
select a, MAX(b), ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f') from t1 group by a;
420
select a, MAX(b), MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') from t1 group by a;
424
# Problem with group by and alias
427
create table t1 (id int not null, qty int not null);
428
insert into t1 values (1,2),(1,3),(2,4),(2,5);
429
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and cqty>1;
430
select id, sum(qty) as sqty from t1 group by id having sqty>2 and count(qty)>1;
431
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sqty>2 and cqty>1;
432
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and count(qty)>1;
433
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;
434
select count(*), interval(qty,2,3,4,5,6,7,8) as category from t1 group by category;
437
# Tests for bug #1355: 'Using filesort' is missing in EXPLAIN when ORDER BY
445
INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3);
446
# Here we select unordered GROUP BY into a temporary talbe,
447
# and then sort it with filesort (GROUP BY in MySQL
448
# implies sorted order of results)
449
SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
450
EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
453
i int(11) default NULL,
454
j int(11) default NULL
456
INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5);
457
SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
458
explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
461
#Test for BUG#6976: Aggregate functions have incorrect NULL-ness
462
create table t1 (a int);
463
insert into t1 values(null);
464
select min(a) is null from t1;
465
select min(a) is null or null from t1;
466
select 1 and min(a) is null from t1;
469
# Test for BUG#5400: GROUP_CONCAT returns everything twice.
470
create table t1 ( col1 int, col2 int );
471
insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
472
select group_concat( distinct col1 ) as alias from t1
473
group by col2 having alias like '%';
478
# Test BUG#8216 when referring in HAVING to n alias which is rand() function
481
create table t1 (a integer, b integer, c integer);
482
insert into t1 (a,b) values (1,2),(1,3),(2,5);
483
select a, 0.1*0+1 r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2=1;
484
# rand(100)*10 will be < 2 only for the first row (of 6)
485
select a, round(rand(100)*10) r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2<=2;
486
select a,sum(b) from t1 where a=1 group by c;
487
select a*sum(b) from t1 where a=1 group by c;
488
select sum(a)*sum(b) from t1 where a=1 group by c;
489
select a,sum(b) from t1 where a=1 group by c having a=1;
490
select a as d,sum(b) from t1 where a=1 group by c having d=1;
491
select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;
494
# Test for BUG#9213 GROUP BY query on utf-8 key returns wrong results
495
create table t1(a int);
496
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9);
499
b varchar(200) NOT NULL,
500
c varchar(50) NOT NULL,
501
d varchar(100) NOT NULL,
502
primary key (a,b(132),c,d),
506
insert into t2 select
508
concat('val-', x3.a + 3*x4.a), -- 12
509
concat('val-', @a:=x3.a + 3*x4.a + 12*C.a), -- 120
510
concat('val-', @a + 120*D.a)
511
from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4;
513
delete from t2 where a = 2 and b = 'val-2' order by a,b,c,d limit 30;
515
explain select c from t2 where a = 2 and b = 'val-2' group by c;
516
select c from t2 where a = 2 and b = 'val-2' group by c;
519
# Test for BUG#9298 "Wrong handling of int4 columns in GROUP functions"
520
# (the actual problem was with protocol code, not GROUP BY)
521
create table t1 (b int4 not null);
522
insert into t1 values(3000000000);
524
select min(b) from t1;
528
# Test for bug #11088: GROUP BY a BLOB column with COUNT(DISTINCT column1)
531
CREATE TABLE t1 (id int PRIMARY KEY, user_id int, hostname longtext);
533
INSERT INTO t1 VALUES
534
(1, 7, 'cache-dtc-af05.proxy.aol.com'),
535
(2, 3, 'what.ever.com'),
536
(3, 7, 'cache-dtc-af05.proxy.aol.com'),
537
(4, 7, 'cache-dtc-af05.proxy.aol.com');
539
SELECT hostname, COUNT(DISTINCT user_id) as no FROM t1
540
WHERE hostname LIKE '%aol%'
546
# Test for bug #8614: GROUP BY 'const' with DISTINCT
549
CREATE TABLE t1 (a int, b int);
550
INSERT INTO t1 VALUES (1,2), (1,3);
551
SELECT a, b FROM t1 GROUP BY 'const';
552
SELECT DISTINCT a, b FROM t1 GROUP BY 'const';
557
# Test for bug #11385: GROUP BY for datetime converted to decimals
560
CREATE TABLE t1 (id INT, dt DATETIME);
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
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
565
SELECT dt DIV 1 AS f, id FROM t1 GROUP BY f;
570
# Test for bug #11295: GROUP BY a BLOB column with COUNT(DISTINCT column1)
571
# when the BLOB column takes NULL values
574
CREATE TABLE t1 (id varchar(20) NOT NULL);
575
INSERT INTO t1 VALUES ('trans1'), ('trans2');
576
CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL);
577
INSERT INTO t2 VALUES ('trans1', 'a problem');
578
SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment
579
FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment;
584
# Bug #12266 GROUP BY expression on DATE column produces result with
587
create table t1 (f1 date);
588
insert into t1 values('2005-06-06');
589
insert into t1 values('2005-06-06');
590
select date(left(f1+0,8)) from t1 group by 1;
594
# Test for bug #11414: crash on Windows for a simple GROUP BY query
597
CREATE TABLE t1 (n int);
598
INSERT INTO t1 VALUES (1);
599
SELECT n+1 AS n FROM t1 GROUP BY n;
603
# BUG#12695: Item_func_isnull::update_used_tables
604
# did not update const_item_cache
606
create table t1(f1 varchar(5) key);
607
insert into t1 values (1),(2);
608
select sql_buffer_result max(f1) is null from t1;
609
select sql_buffer_result max(f1)+1 from t1;
615
CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1),(2);
617
SELECT a FROM t1 GROUP BY 'a';
618
SELECT a FROM t1 GROUP BY "a";
619
SELECT a FROM t1 GROUP BY `a`;
621
SELECT a FROM t1 GROUP BY "a";
622
SELECT a FROM t1 GROUP BY 'a';
623
SELECT a FROM t1 GROUP BY `a`;
625
SELECT a FROM t1 HAVING 'a' > 1;
626
SELECT a FROM t1 HAVING "a" > 1;
627
SELECT a FROM t1 HAVING `a` > 1;
629
SELECT a FROM t1 ORDER BY 'a' DESC;
630
SELECT a FROM t1 ORDER BY "a" DESC;
631
SELECT a FROM t1 ORDER BY `a` DESC;
635
# Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself
639
f1 int(10) NOT NULL auto_increment primary key,
640
f2 varchar(100) NOT NULL default ''
643
f1 varchar(10) NOT NULL default '',
644
f2 char(3) NOT NULL default '',
649
INSERT INTO t1 values(NULL, '');
650
INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
651
SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
652
SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
659
# Bug#11211: Ambiguous column reference in GROUP BY.
662
create table t1 (c1 char(3), c2 char(3));
663
create table t2 (c3 char(3), c4 char(3));
664
insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
665
insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
667
# query with ambiguous column reference 'c2'
668
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
672
# this query has no ambiguity
673
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
680
# Bug#22781: SQL_BIG_RESULT fails to influence sort plan
682
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));
684
INSERT INTO t1 VALUES (1, 1);
685
INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1;
686
INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1;
687
INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1;
688
INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1;
689
INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1;
690
INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1;
691
INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1;
693
SELECT MIN(b), MAX(b) from t1;
695
EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;
696
EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
697
SELECT b, sum(1) FROM t1 GROUP BY b;
698
SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
702
# Bug #23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode
704
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
705
INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
707
SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b;
708
SELECT CEILING(MIN(a)) FROM t1 GROUP BY b;
709
SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1
711
SELECT a + 1 FROM t1 GROUP BY a;
712
SELECT a + b FROM t1 GROUP BY b;
713
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
715
SELECT 1 FROM t1 as t1_outer GROUP BY a
716
HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
717
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1)
718
FROM t1 AS t1_outer GROUP BY t1_outer.b;
719
--error ER_BAD_FIELD_ERROR
720
SELECT 1 FROM t1 as t1_outer GROUP BY a
721
HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
722
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
723
FROM t1 AS t1_outer GROUP BY t1_outer.b;
724
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
726
SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1)
727
FROM t1 AS t1_outer GROUP BY t1_outer.b;
729
SELECT 1 FROM t1 as t1_outer
730
WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1);
732
SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0;
734
SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12;
735
SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1);
737
--error ER_BAD_FIELD_ERROR
738
SELECT 1 FROM t1 GROUP BY b HAVING a = 2;
739
--error ER_INVALID_GROUP_FUNC_USE
740
SELECT 1 FROM t1 GROUP BY SUM(b);
741
SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN
742
(SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a
743
HAVING SUM(t1_inner.b)+t1_outer.b > 5);
746
# Bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode.
748
create table t1(f1 int, f2 int);
749
select * from t1 group by f1;
750
select * from t1 group by f2;
751
select * from t1 group by f1, f2;
752
select t1.f1,t.* from t1, t1 t group by 1;
756
# Bug #32202: ORDER BY not working with GROUP BY
760
id INT AUTO_INCREMENT PRIMARY KEY,
765
INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
767
# Show that the test cases from the bug report pass
768
SELECT * FROM t1 ORDER BY c1;
769
SELECT * FROM t1 GROUP BY id ORDER BY c1;
771
# Show that DESC is handled correctly
772
SELECT * FROM t1 GROUP BY id ORDER BY id DESC;
774
# Show that results are correctly ordered when ORDER BY fields
775
# are a subset of GROUP BY ones
776
SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;
777
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;
778
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;
780
# Show that results are correctly ordered when GROUP BY fields
781
# are a subset of ORDER BY ones
782
SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1;
783
SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1;
784
SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC;
790
--echo # Bug#27219: Aggregate functions in ORDER BY.
792
CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
793
INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
794
CREATE TABLE t2 SELECT * FROM t1;
796
SELECT 1 FROM t1 ORDER BY COUNT(*);
797
SELECT 1 FROM t1 ORDER BY COUNT(*) + 1;
798
SELECT 1 FROM t1 ORDER BY COUNT(*) + a;
799
SELECT 1 FROM t1 ORDER BY COUNT(*), 1;
800
SELECT 1 FROM t1 ORDER BY COUNT(*), a;
802
SELECT 1 FROM t1 ORDER BY SUM(a);
803
SELECT 1 FROM t1 ORDER BY SUM(a + 1);
804
SELECT 1 FROM t1 ORDER BY SUM(a) + 1;
805
SELECT 1 FROM t1 ORDER BY SUM(a), b;
807
SELECT a FROM t1 ORDER BY COUNT(b);
809
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2);
811
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a);
812
SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
815
WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
816
SELECT t1.a FROM t1 GROUP BY t1.a
817
HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
819
SELECT t1.a FROM t1 GROUP BY t1.a
820
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
821
SELECT t1.a FROM t1 GROUP BY t1.a
822
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
823
SELECT t1.a FROM t1 GROUP BY t1.a
824
HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
827
WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
829
SELECT 1 FROM t1 GROUP BY t1.a
830
HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
831
SELECT 1 FROM t1 GROUP BY t1.a
832
HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
833
SELECT 1 FROM t1 GROUP BY t1.a
834
HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
836
SELECT 1 FROM t1 GROUP BY t1.a
837
HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1);
838
SELECT 1 FROM t1 GROUP BY t1.a
839
HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
840
SELECT 1 FROM t1 GROUP BY t1.a
841
HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
843
# Both SUMs are aggregated in the subquery, no mixture:
845
WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a
846
ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1);
848
# SUM(t1.b) is aggregated in the subquery, no mixture:
849
SELECT t1.a, SUM(t1.b) FROM t1
850
WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a
851
ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1)
854
# 2nd SUM(t1.b) is aggregated in the subquery, no mixture:
855
SELECT t1.a, SUM(t1.b) FROM t1
856
WHERE t1.a = (SELECT SUM(t2.b) FROM t2
857
ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1)
860
# SUM(t2.b + t1.a) is aggregated in the subquery, no mixture:
861
SELECT t1.a, SUM(t1.b) FROM t1
862
WHERE t1.a = (SELECT SUM(t2.b) FROM t2
863
ORDER BY SUM(t2.b + t1.a) LIMIT 1)
866
SELECT t1.a FROM t1 GROUP BY t1.a
867
HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);
871
(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
872
from t1 as outr order by outr.a limit 1))
873
from t1 as most_outer;
877
(select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt
878
from t1 as outr order by count(outr.a) limit 1)) as tt
879
from t1 as most_outer;
881
select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a;
885
--echo End of 5.0 tests
886
# Bug #21174: Index degrades sort performance and
887
# optimizer does not honor IGNORE INDEX.
890
CREATE TABLE t1 (a INT, b INT,
893
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
894
INSERT INTO t1 SELECT a + 8,b FROM t1;
895
INSERT INTO t1 SELECT a + 16,b FROM t1;
896
INSERT INTO t1 SELECT a + 32,b FROM t1;
897
INSERT INTO t1 SELECT a + 64,b FROM t1;
898
INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16;
900
EXPLAIN SELECT a FROM t1 WHERE a < 2;
901
EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
902
EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
903
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
904
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
905
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
906
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
907
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
908
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
909
IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
910
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
911
EXPLAIN SELECT a FROM t1 FORCE INDEX (i2);
912
EXPLAIN SELECT a FROM t1 USE INDEX ();
913
EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);
914
--error ER_WRONG_USAGE
915
EXPLAIN SELECT a FROM t1
916
FORCE INDEX (PRIMARY)
917
IGNORE INDEX FOR GROUP BY (i2)
918
IGNORE INDEX FOR ORDER BY (i2)
920
EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
921
--error ER_PARSE_ERROR
922
EXPLAIN SELECT a FROM t1 FORCE INDEX ();
923
--error ER_PARSE_ERROR
924
EXPLAIN SELECT a FROM t1 IGNORE INDEX ();
925
# disable the columns irrelevant to this test here. On some systems
926
# without support for large files the rowid is shorter and its size affects
927
# the cost calculations. This causes the optimizer to choose loose index
928
# scan over normal index access.
929
--replace_column 4 # 7 # 9 # 10 #
930
EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2)
931
USE INDEX FOR GROUP BY (i2) GROUP BY a;
932
EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2)
933
FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
934
EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
935
EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX ();
937
EXPLAIN SELECT a FROM t1
938
USE INDEX FOR GROUP BY (i2)
939
USE INDEX FOR ORDER BY (i2)
940
USE INDEX FOR JOIN (i2);
942
EXPLAIN SELECT a FROM t1
943
USE INDEX FOR JOIN (i2)
944
USE INDEX FOR JOIN (i2)
945
USE INDEX FOR JOIN (i2,i2);
947
EXPLAIN SELECT 1 FROM t1 WHERE a IN
948
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
950
CREATE TABLE t2 (a INT, b INT, KEY(a));
951
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
952
EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
953
EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
955
EXPLAIN SELECT 1 FROM t2 WHERE a IN
956
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
958
SHOW VARIABLES LIKE 'old';
959
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
965
# Bug#30596: GROUP BY optimization gives wrong result order
975
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
983
INSERT INTO t2 VALUES (NULL, NULL), (NULL, NULL), (NULL, 1), (1, NULL), (1, 1), (1,2);
985
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
986
SELECT c,b,d FROM t1 GROUP BY c,b,d;
987
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
988
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
989
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
990
SELECT c,b,d FROM t1 ORDER BY c,b,d;
992
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
993
SELECT c,b,d FROM t1 GROUP BY c,b;
994
EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
995
SELECT c,b FROM t1 GROUP BY c,b;
997
EXPLAIN SELECT a,b from t2 ORDER BY a,b;
998
SELECT a,b from t2 ORDER BY a,b;
999
EXPLAIN SELECT a,b from t2 GROUP BY a,b;
1000
SELECT a,b from t2 GROUP BY a,b;
1001
EXPLAIN SELECT a from t2 GROUP BY a;
1002
SELECT a from t2 GROUP BY a;
1003
EXPLAIN SELECT b from t2 GROUP BY b;
1004
SELECT b from t2 GROUP BY b;
1009
# Bug #31797: error while parsing subqueries -- WHERE is parsed as HAVING
1011
CREATE TABLE t1 ( a INT, b INT );
1013
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1016
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1020
--error ER_ILLEGAL_REFERENCE
1021
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1025
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1028
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1032
--error ER_ILLEGAL_REFERENCE
1033
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1037
INSERT INTO t1 VALUES (1, 1);
1038
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1041
INSERT INTO t1 VALUES (2, 1);
1042
--error ER_SUBQUERY_NO_1_ROW
1043
SELECT b c, (SELECT a FROM t1 WHERE b = c)
added
removed
tests/t/group_by.test
