4
drop table if exists t1,t2,t3;
8
# Simple test without tables
11
SELECT 1 FROM (SELECT 1) as a GROUP BY SUM(1);
14
# Test of group (Failed for Lars Hoss <lh@pbm.de>)
25
INSERT INTO t1 VALUES (1,1,1,'',NULL);
26
INSERT INTO t1 VALUES (2,2,2,'',NULL);
27
INSERT INTO t1 VALUES (2,1,1,'',NULL);
28
INSERT INTO t1 VALUES (3,3,3,'',NULL);
31
userID int NOT NULL auto_increment,
35
isAukt enum('N','Y') DEFAULT 'N',
45
INSERT INTO t2 VALUES (1,'name','pass','mail','Y','v','n','adr','1','1','1');
46
INSERT INTO t2 VALUES (2,'name','pass','mail','Y','v','n','adr','1','1','1');
47
INSERT INTO t2 VALUES (3,'name','pass','mail','Y','v','n','adr','1','1','1');
48
INSERT INTO t2 VALUES (4,'name','pass','mail','Y','v','n','adr','1','1','1');
49
INSERT INTO t2 VALUES (5,'name','pass','mail','Y','v','n','adr','1','1','1');
51
SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid;
52
SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid ORDER BY NULL;
53
SELECT t2.userid, MIN(t1.score) 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;
55
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;
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;
60
# Bug in GROUP BY, by Nikki Chumakov <nikki@saddam.cityline.ru>
64
PID int NOT NULL auto_increment,
67
URID int DEFAULT '0' NOT NULL,
68
CRID int DEFAULT '0' NOT NULL,
69
amount int DEFAULT '0' NOT NULL,
71
method enum('unknown','cash','dealer','check','card','lazy','delayed','test') DEFAULT 'unknown' NOT NULL,
73
reason char(1) DEFAULT '' NOT NULL,
75
qty int DEFAULT '0' NOT NULL,
83
INSERT INTO t1 VALUES (1,'1970-01-01','1997-10-17 00:00:00',2529,1,21000,11886,'check',0,'F',16200,6);
86
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;
91
# Problem with GROUP BY + ORDER BY when no match
93
# NOTE: LOCK TABLE was removed, so now just testing normal syntax.
97
cid int NOT NULL auto_increment,
98
firstname varchar(32) DEFAULT '' NOT NULL,
99
surname varchar(32) DEFAULT '' NOT NULL,
102
INSERT INTO t1 VALUES (1,'That','Guy');
103
INSERT INTO t1 VALUES (2,'Another','Gent');
106
call_id int NOT NULL auto_increment,
107
contact_id int DEFAULT '0' NOT NULL,
108
PRIMARY KEY (call_id),
109
KEY contact_id (contact_id)
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 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;
125
# Test needs to be rewritten
127
## Test of group by bug in bugzilla
131
# bug_id int NOT NULL auto_increment,
132
# groupset bigint DEFAULT '0' NOT NULL,
133
# assigned_to int 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,
138
# delta_ts timestamp,
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 int 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 int DEFAULT '0' NOT NULL,
151
# status_whiteboard mediumtext NOT NULL,
152
# votes int 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),
159
# KEY op_sys (op_sys),
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),
172
#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);
173
#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);
174
#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);
175
#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);
176
#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);
177
#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);
178
#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);
179
#INSERT INTO t1 VALUES (4,0,0,'','normal','','2000-03-08 18:32:14',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent2','','',0,'',0);
180
#INSERT INTO t1 VALUES (3,0,0,'','normal','','2000-03-08 18:30:52',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent','','',0,'',0);
181
#INSERT INTO t1 VALUES (2,0,0,'','enhancement','','2000-03-08 18:24:51',20000321114747,'','','All','P2','TestProduct','Other',4,'other','TestComponent2','','',0,'',0);
182
#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);
183
#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);
184
#INSERT INTO t1 VALUES (13,0,0,'','normal','','2000-03-15 16:20:44',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent','','',0,'',0);
185
#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);
186
#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);
187
#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);
188
#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);
189
#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);
190
#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);
191
#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);
192
#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);
193
#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);
194
#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);
195
#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);
196
#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);
197
#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);
198
#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);
199
#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);
200
#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
# program varchar(64),
204
# initialowner text NOT NULL,
205
# initialqacontact text NOT NULL,
206
# description text NOT NULL
209
#INSERT INTO t2 VALUES ('TestComponent','TestProduct','id0001','','');
210
#INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - conversion','AAAAA','id0001','','');
211
#INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - generic','AAAAA','id0001','','');
212
#INSERT INTO t2 VALUES ('TestComponent2','TestProduct','id0001','','');
213
#INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - eeeeeeeee','AAAAA','id0001','','');
214
#INSERT INTO t2 VALUES ('kkkkkkkkkkk lllllllllll','AAAAA','id0001','','');
215
#INSERT INTO t2 VALUES ('Test Procedures','AAAAA','id0001','','');
216
#INSERT INTO t2 VALUES ('Documentation','AAAAA','id0003','','');
217
#INSERT INTO t2 VALUES ('DDDDDDDDD','CCC/CCCCCC','id0002','','');
218
#INSERT INTO t2 VALUES ('Eeeeeeee Lite','CCC/CCCCCC','id0002','','');
219
#INSERT INTO t2 VALUES ('Eeeeeeee Full','CCC/CCCCCC','id0002','','');
220
#INSERT INTO t2 VALUES ('Administration','CCC/CCCCCC','id0002','','');
221
#INSERT INTO t2 VALUES ('Distribution','CCC/CCCCCC','id0002','','');
222
#INSERT INTO t2 VALUES ('Setup','CCC/CCCCCC','id0002','','');
223
#INSERT INTO t2 VALUES ('Unspecified','CCC/CCCCCC','id0002','','');
224
#INSERT INTO t2 VALUES ('Web Interface','AAAAAAAA-AAA','id0001','','');
225
#INSERT INTO t2 VALUES ('Host communication','AAAAA','id0001','','');
226
#select value,description,bug_id from t2 left join t1 on t2.program=t1.product and t2.value=t1.component where program="AAAAA";
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;
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);
233
# Problem with functions and group functions when no matching rows
236
create table t1 (foo int);
237
insert into t1 values (1);
238
select 1+1, "a",count(*) from t1 where foo in (2);
239
insert into t1 values (1);
240
select 1+1,"a",count(*) from t1 where foo in (2);
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
explain select userid,count(*) from t1 group by userid desc order by null;
257
select userid,count(*) from t1 group by userid desc;
258
select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3);
259
select userid,count(*) from t1 group by userid desc having 3 IN (1,COUNT(*));
260
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
261
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid;
262
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null;
263
select spid,count(*) from t1 where spid between 1 and 2 group by spid;
264
select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
265
explain extended select sql_big_result spid,sum(userid) from t1 group by spid desc;
266
explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null;
267
select sql_big_result spid,sum(userid) from t1 group by spid desc;
268
explain select sql_big_result score,count(*) from t1 group by score desc;
269
explain select sql_big_result score,count(*) from t1 group by score desc order by null;
270
select sql_big_result score,count(*) from t1 group by score desc;
273
# not purely group_by bug, but group_by is involved...
275
create table t1 (a date default null, b date default null);
276
insert t1 values ('1999-10-01','2000-01-10'), ('1997-01-01','1998-10-01');
277
select a,min(b) c,count(distinct rand()) from t1 group by a having c<a + interval 1 day;
280
# Compare with hash keys
282
CREATE TABLE t1 (a char(1));
283
INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL);
284
SELECT a FROM t1 GROUP BY a;
285
SELECT a,count(*) FROM t1 GROUP BY a;
286
SELECT a FROM t1 GROUP BY a;
287
SELECT a,count(*) FROM t1 GROUP BY a;
288
SELECT a FROM t1 GROUP BY 1;
289
SELECT a,count(*) FROM t1 GROUP BY 1;
290
# Do the same tests with MyISAM temporary tables
291
SELECT a FROM t1 GROUP BY a;
292
SELECT a,count(*) FROM t1 GROUP BY a;
293
SELECT a FROM t1 GROUP BY a;
294
SELECT a,count(*) FROM t1 GROUP BY a;
295
SELECT a FROM t1 GROUP BY 1;
296
SELECT a,count(*) FROM t1 GROUP BY 1;
300
# Test of key >= 256 bytes
304
`a` char(193) default NULL,
305
`b` char(63) default NULL
307
INSERT INTO t1 VALUES ('abc','def'),('hij','klm');
308
SELECT CONCAT(a, b) FROM t1 GROUP BY 1;
309
SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;
310
SELECT CONCAT(a, b),count(distinct a) FROM t1 GROUP BY 1;
311
SELECT 1 FROM t1 GROUP BY CONCAT(a, b);
312
INSERT INTO t1 values ('hij','klm');
313
SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;
317
# Test problem with ORDER BY on a SUM() column
320
create table t1 (One int, Two int, Three int, Four int);
321
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);
322
select One, Two, sum(Four) from t1 group by One,Two;
325
create table t1 (id integer primary key not null auto_increment, gender char(1));
326
insert into t1 values (NULL, 'M'), (NULL, 'F'),(NULL, 'F'),(NULL, 'F'),(NULL, 'M');
327
create table t2 (user_id integer not null, date date);
328
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');
329
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;
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 order by percentage;
334
# The GROUP BY returned rows in wrong order in 3.23.51
337
CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID
339
insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL);
340
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;
341
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;
345
# Problem with MAX and LEFT JOIN
348
CREATE TEMPORARY TABLE t1 (
349
pid int NOT NULL default '0',
350
c1id int default NULL,
351
c2id int default NULL,
352
value int NOT NULL default '0',
353
UNIQUE KEY pid2 (pid,c1id,c2id),
354
UNIQUE KEY pid (pid,value)
357
INSERT INTO t1 VALUES (1, 1, NULL, 1),(1, 2, NULL, 2),(1, NULL, 3, 3),(1, 4, NULL, 4),(1, 5, NULL, 5);
359
CREATE TEMPORARY TABLE t2 (
360
id int NOT NULL default '0',
361
active enum('Yes','No') NOT NULL default 'Yes',
365
INSERT INTO t2 VALUES (1, 'Yes'),(2, 'No'),(4, 'Yes'),(5, 'No');
368
id int NOT NULL default '0',
369
active enum('Yes','No') NOT NULL default 'Yes',
372
INSERT INTO t3 VALUES (3, 'Yes');
374
select * from t1 AS m LEFT JOIN t2 AS c1 ON m.c1id =
375
c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = c2.id AND
376
c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS NOT NULL);
377
select max(value) from t1 AS m LEFT JOIN t2 AS c1 ON
378
m.c1id = c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id =
379
c2.id AND c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS
384
# Test bug in GROUP BY on BLOB that is NULL or empty
387
create table t1 (a blob null);
388
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(""),(""),(""),("b");
389
select a,count(*) from t1 group by a;
390
select a,count(*) from t1 group by a;
394
# Test of GROUP BY ... ORDER BY NULL optimization
397
create table t1 (a int not null, b int not null);
398
insert into t1 values (1,1),(1,2),(3,1),(3,2),(2,2),(2,1);
399
create table t2 (a int not null, b int not null, key(a));
400
insert into t2 values (1,3),(3,1),(2,2),(1,1);
401
select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
402
select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
403
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
404
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
408
# group function arguments in some functions
411
create table t1 (a int, b int);
412
insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
413
select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a;
414
select a, MAX(b), CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end from t1 group by a;
415
select a, MAX(b), FIELD(MAX(b), '43', '4', '5') from t1 group by a;
416
select a, MAX(b), CONCAT_WS(MAX(b), '43', '4', '5') from t1 group by a;
417
select a, MAX(b), ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f') from t1 group by a;
418
select a, MAX(b), MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') from t1 group by a;
422
# Problem with group by and alias
425
create table t1 (id int not null, qty int not null);
426
insert into t1 values (1,2),(1,3),(2,4),(2,5);
427
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and cqty>1;
428
select id, sum(qty) as sqty from t1 group by id having sqty>2 and count(qty)>1;
429
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sqty>2 and cqty>1;
430
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and count(qty)>1;
431
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;
432
select count(*), interval(qty,2,3,4,5,6,7,8) as category from t1 group by category;
435
# Tests for bug #1355: 'Using filesort' is missing in EXPLAIN when ORDER BY
443
INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3);
444
# Here we select unordered GROUP BY into a temporary talbe,
445
# and then sort it with filesort (GROUP BY in MySQL
446
# implies sorted order of results)
447
SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
448
EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
454
INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5);
455
SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
456
explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
459
#Test for BUG#6976: Aggregate functions have incorrect NULL-ness
460
create table t1 (a int);
461
insert into t1 values(null);
462
select min(a) is null from t1;
463
select min(a) is null or null from t1;
464
select 1 and min(a) is null from t1;
467
# Test for BUG#5400: GROUP_CONCAT returns everything twice.
468
create table t1 ( col1 int, col2 int );
469
insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
470
select group_concat( distinct col1 ) as alias from t1
471
group by col2 having alias like '%';
476
# Test BUG#8216 when referring in HAVING to n alias which is rand() function
479
create table t1 (a integer, b integer, c integer);
480
insert into t1 (a,b) values (1,2),(1,3),(2,5);
481
select a, 0.1*0+1 r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2=1;
482
# rand(100)*10 will be < 2 only for the first row (of 6)
483
select a, round(rand(100)*10) r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2<=2;
484
select a,sum(b) from t1 where a=1 group by c;
485
select a*sum(b) from t1 where a=1 group by c;
486
select sum(a)*sum(b) from t1 where a=1 group by c;
487
select a,sum(b) from t1 where a=1 group by c having a=1;
488
select a as d,sum(b) from t1 where a=1 group by c having d=1;
489
select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;
492
# Test for BUG#9213 GROUP BY query on utf-8 key returns wrong results
493
create table t1(a int);
494
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9);
497
b varchar(200) NOT NULL,
498
c varchar(50) NOT NULL,
499
d varchar(100) NOT NULL,
500
primary key (a,b(132),c,d),
504
insert into t2 select
506
concat('val-', x3.a + 3*x4.a), -- 12
507
concat('val-', @a:=x3.a + 3*x4.a + 12*C.a), -- 120
508
concat('val-', @a + 120*D.a)
509
from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4;
511
delete from t2 where a = 2 and b = 'val-2' order by a,b,c,d limit 30;
513
explain select c from t2 where a = 2 and b = 'val-2' group by c;
514
select c from t2 where a = 2 and b = 'val-2' group by c;
517
# Test for BUG#9298 "Wrong handling of int4 columns in GROUP functions"
518
# (the actual problem was with protocol code, not GROUP BY)
519
create table t1 (b int4 not null);
520
insert into t1 values(300000);
522
select min(b) from t1;
526
# Test for bug #11088: GROUP BY a BLOB column with COUNT(DISTINCT column1)
529
CREATE TABLE t1 (id int PRIMARY KEY, user_id int, hostname longtext);
531
INSERT INTO t1 VALUES
532
(1, 7, 'cache-dtc-af05.proxy.aol.com'),
533
(2, 3, 'what.ever.com'),
534
(3, 7, 'cache-dtc-af05.proxy.aol.com'),
535
(4, 7, 'cache-dtc-af05.proxy.aol.com');
537
SELECT hostname, COUNT(DISTINCT user_id) as no FROM t1
538
WHERE hostname LIKE '%aol%'
544
# Test for bug #8614: GROUP BY 'const' with DISTINCT
547
CREATE TABLE t1 (a int, b int);
548
INSERT INTO t1 VALUES (1,2), (1,3);
549
SELECT a, b FROM t1 GROUP BY 'const';
550
SELECT DISTINCT a, b FROM t1 GROUP BY 'const';
555
# Test for bug #11385: GROUP BY for datetime converted to decimals
558
CREATE TABLE t1 (id INT, dt DATETIME);
559
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
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
SELECT dt DIV 1 AS f, id FROM t1 GROUP BY f;
568
# Test for bug #11295: GROUP BY a BLOB column with COUNT(DISTINCT column1)
569
# when the BLOB column takes NULL values
572
CREATE TABLE t1 (id varchar(20) NOT NULL);
573
INSERT INTO t1 VALUES ('trans1'), ('trans2');
574
CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL);
575
INSERT INTO t2 VALUES ('trans1', 'a problem');
576
SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment
577
FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment;
582
# Bug #12266 GROUP BY expression on DATE column produces result with
585
create table t1 (f1 date);
586
insert into t1 values('2005-06-06');
587
insert into t1 values('2005-06-06');
588
select date(left(f1+0,8)) from t1 group by 1;
592
# Test for bug #11414: crash on Windows for a simple GROUP BY query
595
CREATE TABLE t1 (n int);
596
INSERT INTO t1 VALUES (1);
597
SELECT n+1 AS n FROM t1 GROUP BY n;
601
# BUG#12695: Item_func_isnull::update_used_tables
602
# did not update const_item_cache
604
create table t1(f1 varchar(5) key);
605
insert into t1 values (1),(2);
606
select sql_buffer_result max(f1) is null from t1;
607
select sql_buffer_result max(f1)+1 from t1;
613
CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1),(2);
615
SELECT a FROM t1 GROUP BY 'a';
616
SELECT a FROM t1 GROUP BY "a";
617
SELECT a FROM t1 GROUP BY `a`;
619
SELECT a FROM t1 GROUP BY "a";
620
SELECT a FROM t1 GROUP BY 'a';
621
SELECT a FROM t1 GROUP BY `a`;
623
SELECT a FROM t1 HAVING 'a' > 1;
624
SELECT a FROM t1 HAVING "a" > 1;
625
SELECT a FROM t1 HAVING `a` > 1;
627
SELECT a FROM t1 ORDER BY 'a' DESC;
628
SELECT a FROM t1 ORDER BY "a" DESC;
629
SELECT a FROM t1 ORDER BY `a` DESC;
633
# Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself
637
f1 int NOT NULL auto_increment primary key,
638
f2 varchar(100) NOT NULL default ''
641
f1 varchar(10) NOT NULL default '',
642
f2 char(3) NOT NULL default '',
647
INSERT INTO t1 values(NULL, '');
648
INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
649
SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
650
SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
657
# Bug#11211: Ambiguous column reference in GROUP BY.
660
create table t1 (c1 char(3), c2 char(3));
661
create table t2 (c3 char(3), c4 char(3));
662
insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
663
insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
665
# query with ambiguous column reference 'c2'
666
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
670
# this query has no ambiguity
671
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
678
# Bug#22781: SQL_BIG_RESULT fails to influence sort plan
680
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));
682
INSERT INTO t1 VALUES (1, 1);
683
INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1;
684
INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1;
685
INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1;
686
INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1;
687
INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1;
688
INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1;
689
INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1;
691
SELECT MIN(b), MAX(b) from t1;
693
EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;
694
EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
695
SELECT b, sum(1) FROM t1 GROUP BY b;
696
SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
700
# Bug #23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode
702
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
703
INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
705
SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b;
706
SELECT CEILING(MIN(a)) FROM t1 GROUP BY b;
707
SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1
709
SELECT a + 1 FROM t1 GROUP BY a;
710
SELECT a + b FROM t1 GROUP BY b;
711
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
713
SELECT 1 FROM t1 as t1_outer GROUP BY a
714
HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
715
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1)
716
FROM t1 AS t1_outer GROUP BY t1_outer.b;
717
--error ER_BAD_FIELD_ERROR
718
SELECT 1 FROM t1 as t1_outer GROUP BY a
719
HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
720
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
721
FROM t1 AS t1_outer GROUP BY t1_outer.b;
722
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
724
SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1)
725
FROM t1 AS t1_outer GROUP BY t1_outer.b;
727
SELECT 1 FROM t1 as t1_outer
728
WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1);
730
SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0;
732
SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12;
733
SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1);
735
--error ER_BAD_FIELD_ERROR
736
SELECT 1 FROM t1 GROUP BY b HAVING a = 2;
737
--error ER_INVALID_GROUP_FUNC_USE
738
SELECT 1 FROM t1 GROUP BY SUM(b);
739
SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN
740
(SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a
741
HAVING SUM(t1_inner.b)+t1_outer.b > 5);
744
# Bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode.
746
create table t1(f1 int, f2 int);
747
select * from t1 group by f1;
748
select * from t1 group by f2;
749
select * from t1 group by f1, f2;
750
select t1.f1,t.* from t1, t1 t group by 1;
754
# Bug #32202: ORDER BY not working with GROUP BY
758
id INT AUTO_INCREMENT PRIMARY KEY,
763
INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
765
# Show that the test cases from the bug report pass
766
SELECT * FROM t1 ORDER BY c1;
767
SELECT * FROM t1 GROUP BY id ORDER BY c1;
769
# Show that DESC is handled correctly
770
SELECT * FROM t1 GROUP BY id ORDER BY id DESC;
772
# Show that results are correctly ordered when ORDER BY fields
773
# are a subset of GROUP BY ones
774
SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;
775
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;
776
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;
778
# Show that results are correctly ordered when GROUP BY fields
779
# are a subset of ORDER BY ones
780
SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1;
781
SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1;
782
SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC;
788
--echo # Bug#27219: Aggregate functions in ORDER BY.
790
CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
791
INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
792
CREATE TABLE t2 SELECT * FROM t1;
794
SELECT 1 FROM t1 ORDER BY COUNT(*);
795
SELECT 1 FROM t1 ORDER BY COUNT(*) + 1;
796
SELECT 1 FROM t1 ORDER BY COUNT(*) + a;
797
SELECT 1 FROM t1 ORDER BY COUNT(*), 1;
798
SELECT 1 FROM t1 ORDER BY COUNT(*), a;
800
SELECT 1 FROM t1 ORDER BY SUM(a);
801
SELECT 1 FROM t1 ORDER BY SUM(a + 1);
802
SELECT 1 FROM t1 ORDER BY SUM(a) + 1;
803
SELECT 1 FROM t1 ORDER BY SUM(a), b;
805
SELECT a FROM t1 ORDER BY COUNT(b);
807
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2);
809
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a);
810
SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
813
WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
814
SELECT t1.a FROM t1 GROUP BY t1.a
815
HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
817
SELECT t1.a FROM t1 GROUP BY t1.a
818
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
819
SELECT t1.a FROM t1 GROUP BY t1.a
820
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
821
SELECT t1.a FROM t1 GROUP BY t1.a
822
HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
825
WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
827
SELECT 1 FROM t1 GROUP BY t1.a
828
HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
829
SELECT 1 FROM t1 GROUP BY t1.a
830
HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
831
SELECT 1 FROM t1 GROUP BY t1.a
832
HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
834
SELECT 1 FROM t1 GROUP BY t1.a
835
HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1);
836
SELECT 1 FROM t1 GROUP BY t1.a
837
HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
838
SELECT 1 FROM t1 GROUP BY t1.a
839
HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
841
# Both SUMs are aggregated in the subquery, no mixture:
843
WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a
844
ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1);
846
# SUM(t1.b) is aggregated in the subquery, no mixture:
847
SELECT t1.a, SUM(t1.b) FROM t1
848
WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a
849
ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1)
852
# 2nd SUM(t1.b) is aggregated in the subquery, no mixture:
853
SELECT t1.a, SUM(t1.b) FROM t1
854
WHERE t1.a = (SELECT SUM(t2.b) FROM t2
855
ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1)
858
# SUM(t2.b + t1.a) is aggregated in the subquery, no mixture:
859
SELECT t1.a, SUM(t1.b) FROM t1
860
WHERE t1.a = (SELECT SUM(t2.b) FROM t2
861
ORDER BY SUM(t2.b + t1.a) LIMIT 1)
864
SELECT t1.a FROM t1 GROUP BY t1.a
865
HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);
869
(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
870
from t1 as outr order by outr.a limit 1))
871
from t1 as most_outer;
875
(select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt
876
from t1 as outr order by count(outr.a) limit 1)) as tt
877
from t1 as most_outer;
879
select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a;
883
--echo End of 5.0 tests
884
# Bug #21174: Index degrades sort performance and
885
# optimizer does not honor IGNORE INDEX.
888
CREATE TABLE t1 (a INT, b INT,
891
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
892
INSERT INTO t1 SELECT a + 8,b FROM t1;
893
INSERT INTO t1 SELECT a + 16,b FROM t1;
894
INSERT INTO t1 SELECT a + 32,b FROM t1;
895
INSERT INTO t1 SELECT a + 64,b FROM t1;
896
INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16;
898
EXPLAIN SELECT a FROM t1 WHERE a < 2;
899
EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
900
EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
901
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
902
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
903
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
904
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
905
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
906
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
907
IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
908
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
909
EXPLAIN SELECT a FROM t1 FORCE INDEX (i2);
910
EXPLAIN SELECT a FROM t1 USE INDEX ();
911
EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);
912
--error ER_WRONG_USAGE
913
EXPLAIN SELECT a FROM t1
914
FORCE INDEX (PRIMARY)
915
IGNORE INDEX FOR GROUP BY (i2)
916
IGNORE INDEX FOR ORDER BY (i2)
918
EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
919
--error ER_PARSE_ERROR
920
EXPLAIN SELECT a FROM t1 FORCE INDEX ();
921
--error ER_PARSE_ERROR
922
EXPLAIN SELECT a FROM t1 IGNORE INDEX ();
923
# disable the columns irrelevant to this test here. On some systems
924
# without support for large files the rowid is shorter and its size affects
925
# the cost calculations. This causes the optimizer to choose loose index
926
# scan over normal index access.
927
--replace_column 4 # 7 # 9 # 10 #
928
EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2)
929
USE INDEX FOR GROUP BY (i2) GROUP BY a;
930
EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2)
931
FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
932
EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
933
EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX ();
935
EXPLAIN SELECT a FROM t1
936
USE INDEX FOR GROUP BY (i2)
937
USE INDEX FOR ORDER BY (i2)
938
USE INDEX FOR JOIN (i2);
940
EXPLAIN SELECT a FROM t1
941
USE INDEX FOR JOIN (i2)
942
USE INDEX FOR JOIN (i2)
943
USE INDEX FOR JOIN (i2,i2);
945
EXPLAIN SELECT 1 FROM t1 WHERE a IN
946
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
948
CREATE TABLE t2 (a INT, b INT, KEY(a));
949
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
950
EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
951
EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
953
EXPLAIN SELECT 1 FROM t2 WHERE a IN
954
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
959
# Bug#30596: GROUP BY optimization gives wrong result order
969
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
977
INSERT INTO t2 VALUES (NULL, NULL), (NULL, NULL), (NULL, 1), (1, NULL), (1, 1), (1,2);
979
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
980
SELECT c,b,d FROM t1 GROUP BY c,b,d;
981
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
982
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
983
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
984
SELECT c,b,d FROM t1 ORDER BY c,b,d;
986
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
987
SELECT c,b,d FROM t1 GROUP BY c,b;
988
EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
989
SELECT c,b FROM t1 GROUP BY c,b;
991
EXPLAIN SELECT a,b from t2 ORDER BY a,b;
992
SELECT a,b from t2 ORDER BY a,b;
993
EXPLAIN SELECT a,b from t2 GROUP BY a,b;
994
SELECT a,b from t2 GROUP BY a,b;
995
EXPLAIN SELECT a from t2 GROUP BY a;
996
SELECT a from t2 GROUP BY a;
997
EXPLAIN SELECT b from t2 GROUP BY b;
998
SELECT b from t2 GROUP BY b;
1003
# Bug #31797: error while parsing subqueries -- WHERE is parsed as HAVING
1005
CREATE TABLE t1 ( a INT, b INT );
1007
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1010
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1014
--error ER_ILLEGAL_REFERENCE
1015
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1019
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1022
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1026
--error ER_ILLEGAL_REFERENCE
1027
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1031
INSERT INTO t1 VALUES (1, 1);
1032
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1035
INSERT INTO t1 VALUES (2, 1);
1036
--error ER_SUBQUERY_NO_1_ROW
1037
SELECT b c, (SELECT a FROM t1 WHERE b = c)
added
removed
tests/t/group_by.test
