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>)
18
spID int(10) unsigned,
19
userID int(10) unsigned,
20
score smallint(5) unsigned,
25
INSERT INTO t1 VALUES (1,1,1,'','0000-00-00');
26
INSERT INTO t1 VALUES (2,2,2,'','0000-00-00');
27
INSERT INTO t1 VALUES (2,1,1,'','0000-00-00');
28
INSERT INTO t1 VALUES (3,3,3,'','0000-00-00');
31
userID int(10) unsigned 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(10) unsigned NOT NULL auto_increment,
65
payDate date DEFAULT '0000-00-00' NOT NULL,
66
recDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
67
URID int(10) unsigned DEFAULT '0' NOT NULL,
68
CRID int(10) unsigned DEFAULT '0' NOT NULL,
69
amount int(10) unsigned DEFAULT '0' NOT NULL,
70
operator int(10) unsigned,
71
method enum('unknown','cash','dealer','check','card','lazy','delayed','test') DEFAULT 'unknown' NOT NULL,
72
DIID int(10) unsigned,
73
reason char(1) binary DEFAULT '' NOT NULL,
74
code_id int(10) unsigned,
75
qty mediumint(8) unsigned 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
96
cid mediumint(9) NOT NULL auto_increment,
97
firstname varchar(32) DEFAULT '' NOT NULL,
98
surname varchar(32) DEFAULT '' NOT NULL,
101
INSERT INTO t1 VALUES (1,'That','Guy');
102
INSERT INTO t1 VALUES (2,'Another','Gent');
105
call_id mediumint(8) NOT NULL auto_increment,
106
contact_id mediumint(8) DEFAULT '0' NOT NULL,
107
PRIMARY KEY (call_id),
108
KEY contact_id (contact_id)
111
lock tables t1 read,t2 write;
113
INSERT INTO t2 VALUES (10,2);
114
INSERT INTO t2 VALUES (18,2);
115
INSERT INTO t2 VALUES (62,2);
116
INSERT INTO t2 VALUES (91,2);
117
INSERT INTO t2 VALUES (92,2);
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;
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 NULL;
121
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;
128
# Test of group by bug in bugzilla
132
bug_id mediumint(9) NOT NULL auto_increment,
133
groupset bigint(20) DEFAULT '0' NOT NULL,
134
assigned_to mediumint(9) 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,
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 mediumint(9) 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 mediumint(9) DEFAULT '0' NOT NULL,
152
status_whiteboard mediumtext NOT NULL,
153
votes mediumint(9) 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),
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),
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);
204
initialowner tinytext NOT NULL,
205
initialqacontact tinytext NOT NULL,
206
description mediumtext 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);
247
spID int(10) unsigned,
248
userID int(10) unsigned,
249
score smallint(5) unsigned,
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 binary a;
287
SELECT a,count(*) FROM t1 GROUP BY binary a;
288
SELECT binary a FROM t1 GROUP BY 1;
289
SELECT binary a,count(*) FROM t1 GROUP BY 1;
290
# Do the same tests with MyISAM temporary tables
291
SET SQL_BIG_TABLES=1;
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;
298
SET SQL_BIG_TABLES=0;
302
# Test of key >= 256 bytes
306
`a` char(193) default NULL,
307
`b` char(63) default NULL
309
INSERT INTO t1 VALUES ('abc','def'),('hij','klm');
310
SELECT CONCAT(a, b) FROM t1 GROUP BY 1;
311
SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;
312
SELECT CONCAT(a, b),count(distinct a) FROM t1 GROUP BY 1;
313
SELECT 1 FROM t1 GROUP BY CONCAT(a, b);
314
INSERT INTO t1 values ('hij','klm');
315
SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;
319
# Test problem with ORDER BY on a SUM() column
322
create table t1 (One int unsigned, Two int unsigned, Three int unsigned, Four int unsigned);
323
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);
324
select One, Two, sum(Four) from t1 group by One,Two;
327
create table t1 (id integer primary key not null auto_increment, gender char(1));
328
insert into t1 values (NULL, 'M'), (NULL, 'F'),(NULL, 'F'),(NULL, 'F'),(NULL, 'M');
329
create table t2 (user_id integer not null, date date);
330
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');
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;
332
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;
336
# The GROUP BY returned rows in wrong order in 3.23.51
339
CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID
341
insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL);
342
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;
343
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;
347
# Problem with MAX and LEFT JOIN
351
pid int(11) unsigned NOT NULL default '0',
352
c1id int(11) unsigned default NULL,
353
c2id int(11) unsigned default NULL,
354
value int(11) unsigned NOT NULL default '0',
355
UNIQUE KEY pid2 (pid,c1id,c2id),
356
UNIQUE KEY pid (pid,value)
359
INSERT INTO t1 VALUES (1, 1, NULL, 1),(1, 2, NULL, 2),(1, NULL, 3, 3),(1, 4, NULL, 4),(1, 5, NULL, 5);
362
id int(11) unsigned NOT NULL default '0',
363
active enum('Yes','No') NOT NULL default 'Yes',
367
INSERT INTO t2 VALUES (1, 'Yes'),(2, 'No'),(4, 'Yes'),(5, 'No');
370
id int(11) unsigned NOT NULL default '0',
371
active enum('Yes','No') NOT NULL default 'Yes',
374
INSERT INTO t3 VALUES (3, 'Yes');
376
select * from t1 AS m LEFT JOIN t2 AS c1 ON m.c1id =
377
c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = c2.id AND
378
c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS NOT NULL);
379
select max(value) from t1 AS m LEFT JOIN t2 AS c1 ON
380
m.c1id = c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id =
381
c2.id AND c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS
386
# Test bug in GROUP BY on BLOB that is NULL or empty
389
create table t1 (a blob null);
390
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(""),(""),(""),("b");
391
select a,count(*) from t1 group by a;
392
set option sql_big_tables=1;
393
select a,count(*) from t1 group by a;
397
# Test of GROUP BY ... ORDER BY NULL optimization
400
create table t1 (a int not null, b int not null);
401
insert into t1 values (1,1),(1,2),(3,1),(3,2),(2,2),(2,1);
402
create table t2 (a int not null, b int not null, key(a));
403
insert into t2 values (1,3),(3,1),(2,2),(1,1);
404
select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
405
select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
406
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
407
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
411
# group function arguments in some functions
414
create table t1 (a int, b int);
415
insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
416
select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a;
417
select a, MAX(b), CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end from t1 group by a;
418
select a, MAX(b), FIELD(MAX(b), '43', '4', '5') from t1 group by a;
419
select a, MAX(b), CONCAT_WS(MAX(b), '43', '4', '5') from t1 group by a;
420
select a, MAX(b), ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f') from t1 group by a;
421
select a, MAX(b), MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') from t1 group by a;
425
# Problem with group by and alias
428
create table t1 (id int not null, qty int not null);
429
insert into t1 values (1,2),(1,3),(2,4),(2,5);
430
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and cqty>1;
431
select id, sum(qty) as sqty from t1 group by id having sqty>2 and count(qty)>1;
432
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sqty>2 and cqty>1;
433
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and count(qty)>1;
434
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;
435
select count(*), interval(qty,2,3,4,5,6,7,8) as category from t1 group by category;
438
# Tests for bug #1355: 'Using filesort' is missing in EXPLAIN when ORDER BY
442
userid int(10) unsigned,
443
score smallint(5) unsigned,
446
INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3);
447
# Here we select unordered GROUP BY into a temporary talbe,
448
# and then sort it with filesort (GROUP BY in MySQL
449
# implies sorted order of results)
450
SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
451
EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
454
i int(11) default NULL,
455
j int(11) default NULL
457
INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5);
458
SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
459
explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
462
#Test for BUG#6976: Aggregate functions have incorrect NULL-ness
463
create table t1 (a int);
464
insert into t1 values(null);
465
select min(a) is null from t1;
466
select min(a) is null or null from t1;
467
select 1 and min(a) is null from t1;
470
# Test for BUG#5400: GROUP_CONCAT returns everything twice.
471
create table t1 ( col1 int, col2 int );
472
insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
473
select group_concat( distinct col1 ) as alias from t1
474
group by col2 having alias like '%';
479
# Test BUG#8216 when referring in HAVING to n alias which is rand() function
482
create table t1 (a integer, b integer, c integer);
483
insert into t1 (a,b) values (1,2),(1,3),(2,5);
484
select a, 0.1*0+1 r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2=1;
485
# rand(100)*10 will be < 2 only for the first row (of 6)
486
select a, round(rand(100)*10) r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2<=2;
487
select a,sum(b) from t1 where a=1 group by c;
488
select a*sum(b) from t1 where a=1 group by c;
489
select sum(a)*sum(b) from t1 where a=1 group by c;
490
select a,sum(b) from t1 where a=1 group by c having a=1;
491
select a as d,sum(b) from t1 where a=1 group by c having d=1;
492
select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;
495
# Test for BUG#9213 GROUP BY query on utf-8 key returns wrong results
496
create table t1(a int);
497
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9);
500
b varchar(200) NOT NULL,
501
c varchar(50) NOT NULL,
502
d varchar(100) NOT NULL,
503
primary key (a,b(132),c,d),
507
insert into t2 select
509
concat('val-', x3.a + 3*x4.a), -- 12
510
concat('val-', @a:=x3.a + 3*x4.a + 12*C.a), -- 120
511
concat('val-', @a + 120*D.a)
512
from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4;
514
delete from t2 where a = 2 and b = 'val-2' order by a,b,c,d limit 30;
516
explain select c from t2 where a = 2 and b = 'val-2' group by c;
517
select c from t2 where a = 2 and b = 'val-2' group by c;
520
# Test for BUG#9298 "Wrong handling of int4 unsigned columns in GROUP functions"
521
# (the actual problem was with protocol code, not GROUP BY)
522
create table t1 (b int4 unsigned not null);
523
insert into t1 values(3000000000);
525
select min(b) from t1;
529
# Test for bug #11088: GROUP BY a BLOB column with COUNT(DISTINCT column1)
532
CREATE TABLE t1 (id int PRIMARY KEY, user_id int, hostname longtext);
534
INSERT INTO t1 VALUES
535
(1, 7, 'cache-dtc-af05.proxy.aol.com'),
536
(2, 3, 'what.ever.com'),
537
(3, 7, 'cache-dtc-af05.proxy.aol.com'),
538
(4, 7, 'cache-dtc-af05.proxy.aol.com');
540
SELECT hostname, COUNT(DISTINCT user_id) as no FROM t1
541
WHERE hostname LIKE '%aol%'
547
# Test for bug #8614: GROUP BY 'const' with DISTINCT
550
CREATE TABLE t1 (a int, b int);
551
INSERT INTO t1 VALUES (1,2), (1,3);
552
SELECT a, b FROM t1 GROUP BY 'const';
553
SELECT DISTINCT a, b FROM t1 GROUP BY 'const';
558
# Test for bug #11385: GROUP BY for datetime converted to decimals
561
CREATE TABLE t1 (id INT, dt DATETIME);
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
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
566
SELECT dt DIV 1 AS f, id FROM t1 GROUP BY f;
571
# Test for bug #11295: GROUP BY a BLOB column with COUNT(DISTINCT column1)
572
# when the BLOB column takes NULL values
575
CREATE TABLE t1 (id varchar(20) NOT NULL);
576
INSERT INTO t1 VALUES ('trans1'), ('trans2');
577
CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL);
578
INSERT INTO t2 VALUES ('trans1', 'a problem');
579
SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment
580
FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment;
585
# Bug #12266 GROUP BY expression on DATE column produces result with
588
create table t1 (f1 date);
589
insert into t1 values('2005-06-06');
590
insert into t1 values('2005-06-06');
591
select date(left(f1+0,8)) from t1 group by 1;
595
# Test for bug #11414: crash on Windows for a simple GROUP BY query
598
CREATE TABLE t1 (n int);
599
INSERT INTO t1 VALUES (1);
600
SELECT n+1 AS n FROM t1 GROUP BY n;
604
# BUG#12695: Item_func_isnull::update_used_tables
605
# did not update const_item_cache
607
create table t1(f1 varchar(5) key);
608
insert into t1 values (1),(2);
609
select sql_buffer_result max(f1) is null from t1;
610
select sql_buffer_result max(f1)+1 from t1;
616
CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1),(2);
618
SELECT a FROM t1 GROUP BY 'a';
619
SELECT a FROM t1 GROUP BY "a";
620
SELECT a FROM t1 GROUP BY `a`;
622
SELECT a FROM t1 GROUP BY "a";
623
SELECT a FROM t1 GROUP BY 'a';
624
SELECT a FROM t1 GROUP BY `a`;
626
SELECT a FROM t1 HAVING 'a' > 1;
627
SELECT a FROM t1 HAVING "a" > 1;
628
SELECT a FROM t1 HAVING `a` > 1;
630
SELECT a FROM t1 ORDER BY 'a' DESC;
631
SELECT a FROM t1 ORDER BY "a" DESC;
632
SELECT a FROM t1 ORDER BY `a` DESC;
636
# Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself
640
f1 int(10) unsigned NOT NULL auto_increment primary key,
641
f2 varchar(100) NOT NULL default ''
644
f1 varchar(10) NOT NULL default '',
645
f2 char(3) NOT NULL default '',
650
INSERT INTO t1 values(NULL, '');
651
INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
652
SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
653
SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
660
# Bug#11211: Ambiguous column reference in GROUP BY.
663
create table t1 (c1 char(3), c2 char(3));
664
create table t2 (c3 char(3), c4 char(3));
665
insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
666
insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
668
# query with ambiguous column reference 'c2'
669
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
673
# this query has no ambiguity
674
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
681
# Bug#22781: SQL_BIG_RESULT fails to influence sort plan
683
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));
685
INSERT INTO t1 VALUES (1, 1);
686
INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1;
687
INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1;
688
INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1;
689
INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1;
690
INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1;
691
INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1;
692
INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1;
694
SELECT MIN(b), MAX(b) from t1;
696
EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;
697
EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
698
SELECT b, sum(1) FROM t1 GROUP BY b;
699
SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
703
# Bug #23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode
705
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
706
INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
708
SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b;
709
SELECT CEILING(MIN(a)) FROM t1 GROUP BY b;
710
SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1
712
SELECT a + 1 FROM t1 GROUP BY a;
713
SELECT a + b FROM t1 GROUP BY b;
714
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
716
SELECT 1 FROM t1 as t1_outer GROUP BY a
717
HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
718
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1)
719
FROM t1 AS t1_outer GROUP BY t1_outer.b;
720
--error ER_BAD_FIELD_ERROR
721
SELECT 1 FROM t1 as t1_outer GROUP BY a
722
HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
723
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
724
FROM t1 AS t1_outer GROUP BY t1_outer.b;
725
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
727
SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1)
728
FROM t1 AS t1_outer GROUP BY t1_outer.b;
730
SELECT 1 FROM t1 as t1_outer
731
WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1);
733
SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0;
735
SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12;
736
SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1);
738
--error ER_BAD_FIELD_ERROR
739
SELECT 1 FROM t1 GROUP BY b HAVING a = 2;
740
--error ER_INVALID_GROUP_FUNC_USE
741
SELECT 1 FROM t1 GROUP BY SUM(b);
742
SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN
743
(SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a
744
HAVING SUM(t1_inner.b)+t1_outer.b > 5);
747
# Bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode.
749
create table t1(f1 int, f2 int);
750
select * from t1 group by f1;
751
select * from t1 group by f2;
752
select * from t1 group by f1, f2;
753
select t1.f1,t.* from t1, t1 t group by 1;
757
# Bug #32202: ORDER BY not working with GROUP BY
761
id INT AUTO_INCREMENT PRIMARY KEY,
766
INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
768
# Show that the test cases from the bug report pass
769
SELECT * FROM t1 ORDER BY c1;
770
SELECT * FROM t1 GROUP BY id ORDER BY c1;
772
# Show that DESC is handled correctly
773
SELECT * FROM t1 GROUP BY id ORDER BY id DESC;
775
# Show that results are correctly ordered when ORDER BY fields
776
# are a subset of GROUP BY ones
777
SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;
778
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;
779
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;
781
# Show that results are correctly ordered when GROUP BY fields
782
# are a subset of ORDER BY ones
783
SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1;
784
SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1;
785
SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC;
791
--echo # Bug#27219: Aggregate functions in ORDER BY.
793
CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
794
INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
795
CREATE TABLE t2 SELECT * FROM t1;
797
SELECT 1 FROM t1 ORDER BY COUNT(*);
798
SELECT 1 FROM t1 ORDER BY COUNT(*) + 1;
799
SELECT 1 FROM t1 ORDER BY COUNT(*) + a;
800
SELECT 1 FROM t1 ORDER BY COUNT(*), 1;
801
SELECT 1 FROM t1 ORDER BY COUNT(*), a;
803
SELECT 1 FROM t1 ORDER BY SUM(a);
804
SELECT 1 FROM t1 ORDER BY SUM(a + 1);
805
SELECT 1 FROM t1 ORDER BY SUM(a) + 1;
806
SELECT 1 FROM t1 ORDER BY SUM(a), b;
808
SELECT a FROM t1 ORDER BY COUNT(b);
810
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2);
812
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a);
813
SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
816
WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
817
SELECT t1.a FROM t1 GROUP BY t1.a
818
HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
820
SELECT t1.a FROM t1 GROUP BY t1.a
821
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
822
SELECT t1.a FROM t1 GROUP BY t1.a
823
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
824
SELECT t1.a FROM t1 GROUP BY t1.a
825
HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
828
WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
830
SELECT 1 FROM t1 GROUP BY t1.a
831
HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
832
SELECT 1 FROM t1 GROUP BY t1.a
833
HAVING (SELECT AVG(SUM(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(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
837
SELECT 1 FROM t1 GROUP BY t1.a
838
HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1);
839
SELECT 1 FROM t1 GROUP BY t1.a
840
HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
841
SELECT 1 FROM t1 GROUP BY t1.a
842
HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
844
# Both SUMs are aggregated in the subquery, no mixture:
846
WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a
847
ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1);
849
# SUM(t1.b) is aggregated in the subquery, no mixture:
850
SELECT t1.a, SUM(t1.b) FROM t1
851
WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a
852
ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1)
855
# 2nd SUM(t1.b) is aggregated in the subquery, no mixture:
856
SELECT t1.a, SUM(t1.b) FROM t1
857
WHERE t1.a = (SELECT SUM(t2.b) FROM t2
858
ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1)
861
# SUM(t2.b + t1.a) is aggregated in the subquery, no mixture:
862
SELECT t1.a, SUM(t1.b) FROM t1
863
WHERE t1.a = (SELECT SUM(t2.b) FROM t2
864
ORDER BY SUM(t2.b + t1.a) LIMIT 1)
867
SELECT t1.a FROM t1 GROUP BY t1.a
868
HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);
872
(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
873
from t1 as outr order by outr.a limit 1))
874
from t1 as most_outer;
878
(select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt
879
from t1 as outr order by count(outr.a) limit 1)) as tt
880
from t1 as most_outer;
882
select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a;
886
--echo End of 5.0 tests
887
# Bug #21174: Index degrades sort performance and
888
# optimizer does not honor IGNORE INDEX.
891
CREATE TABLE t1 (a INT, b INT,
894
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
895
INSERT INTO t1 SELECT a + 8,b FROM t1;
896
INSERT INTO t1 SELECT a + 16,b FROM t1;
897
INSERT INTO t1 SELECT a + 32,b FROM t1;
898
INSERT INTO t1 SELECT a + 64,b FROM t1;
899
INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16;
901
EXPLAIN SELECT a FROM t1 WHERE a < 2;
902
EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
903
EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
904
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
905
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
906
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
907
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
908
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
909
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
910
IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
911
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
912
EXPLAIN SELECT a FROM t1 FORCE INDEX (i2);
913
EXPLAIN SELECT a FROM t1 USE INDEX ();
914
EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);
915
--error ER_WRONG_USAGE
916
EXPLAIN SELECT a FROM t1
917
FORCE INDEX (PRIMARY)
918
IGNORE INDEX FOR GROUP BY (i2)
919
IGNORE INDEX FOR ORDER BY (i2)
921
EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
922
--error ER_PARSE_ERROR
923
EXPLAIN SELECT a FROM t1 FORCE INDEX ();
924
--error ER_PARSE_ERROR
925
EXPLAIN SELECT a FROM t1 IGNORE INDEX ();
926
# disable the columns irrelevant to this test here. On some systems
927
# without support for large files the rowid is shorter and its size affects
928
# the cost calculations. This causes the optimizer to choose loose index
929
# scan over normal index access.
930
--replace_column 4 # 7 # 9 # 10 #
931
EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2)
932
USE INDEX FOR GROUP BY (i2) GROUP BY a;
933
EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2)
934
FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
935
EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
936
EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX ();
938
EXPLAIN SELECT a FROM t1
939
USE INDEX FOR GROUP BY (i2)
940
USE INDEX FOR ORDER BY (i2)
941
USE INDEX FOR JOIN (i2);
943
EXPLAIN SELECT a FROM t1
944
USE INDEX FOR JOIN (i2)
945
USE INDEX FOR JOIN (i2)
946
USE INDEX FOR JOIN (i2,i2);
948
EXPLAIN SELECT 1 FROM t1 WHERE a IN
949
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
951
CREATE TABLE t2 (a INT, b INT, KEY(a));
952
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
953
EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
954
EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
956
EXPLAIN SELECT 1 FROM t2 WHERE a IN
957
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
959
SHOW VARIABLES LIKE 'old';
960
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
966
# Bug#30596: GROUP BY optimization gives wrong result order
976
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
984
INSERT INTO t2 VALUES (NULL, NULL), (NULL, NULL), (NULL, 1), (1, NULL), (1, 1), (1,2);
986
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
987
SELECT c,b,d FROM t1 GROUP BY c,b,d;
988
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
989
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
990
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
991
SELECT c,b,d FROM t1 ORDER BY c,b,d;
993
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
994
SELECT c,b,d FROM t1 GROUP BY c,b;
995
EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
996
SELECT c,b FROM t1 GROUP BY c,b;
998
EXPLAIN SELECT a,b from t2 ORDER BY a,b;
999
SELECT a,b from t2 ORDER BY a,b;
1000
EXPLAIN SELECT a,b from t2 GROUP BY a,b;
1001
SELECT a,b from t2 GROUP BY a,b;
1002
EXPLAIN SELECT a from t2 GROUP BY a;
1003
SELECT a from t2 GROUP BY a;
1004
EXPLAIN SELECT b from t2 GROUP BY b;
1005
SELECT b from t2 GROUP BY b;
1010
# Bug #31797: error while parsing subqueries -- WHERE is parsed as HAVING
1012
CREATE TABLE t1 ( a INT, b INT );
1014
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1017
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1021
--error ER_ILLEGAL_REFERENCE
1022
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1026
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1029
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1033
--error ER_ILLEGAL_REFERENCE
1034
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1038
INSERT INTO t1 VALUES (1, 1);
1039
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1042
INSERT INTO t1 VALUES (2, 1);
1043
--error ER_SUBQUERY_NO_1_ROW
1044
SELECT b c, (SELECT a FROM t1 WHERE b = c)
added
removed
mysql-test/t/group_by.test
