1drop table if exists t1,t2,t3; 2SELECT 1 FROM (SELECT 1) as a GROUP BY SUM(1); 3ERROR HY000: Invalid use of group function 4CREATE TABLE t1 ( 5spID int(10) unsigned, 6userID int(10) unsigned, 7score smallint(5) unsigned, 8lsg char(40), 9date date 10); 11INSERT INTO t1 VALUES (1,1,1,'','0000-00-00'); 12INSERT INTO t1 VALUES (2,2,2,'','0000-00-00'); 13INSERT INTO t1 VALUES (2,1,1,'','0000-00-00'); 14INSERT INTO t1 VALUES (3,3,3,'','0000-00-00'); 15CREATE TABLE t2 ( 16userID int(10) unsigned NOT NULL auto_increment, 17niName char(15), 18passwd char(8), 19mail char(50), 20isAukt enum('N','Y') DEFAULT 'N', 21vName char(30), 22nName char(40), 23adr char(60), 24plz char(5), 25ort char(35), 26land char(20), 27PRIMARY KEY (userID) 28); 29INSERT INTO t2 VALUES (1,'name','pass','mail','Y','v','n','adr','1','1','1'); 30INSERT INTO t2 VALUES (2,'name','pass','mail','Y','v','n','adr','1','1','1'); 31INSERT INTO t2 VALUES (3,'name','pass','mail','Y','v','n','adr','1','1','1'); 32INSERT INTO t2 VALUES (4,'name','pass','mail','Y','v','n','adr','1','1','1'); 33INSERT INTO t2 VALUES (5,'name','pass','mail','Y','v','n','adr','1','1','1'); 34SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid; 35userid MIN(t1.score) 361 1 372 2 383 3 39SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid ORDER BY NULL; 40userid MIN(t1.score) 411 1 422 2 433 3 44SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid; 45userid MIN(t1.score) 461 1 472 2 48SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid; 49userid MIN(t1.score+0.0) 501 1.0 512 2.0 52SELECT 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; 53userid MIN(t1.score+0.0) 542 2.0 551 1.0 56EXPLAIN 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; 57id select_type table type possible_keys key key_len ref rows Extra 581 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where; Using temporary 591 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.userID 1 Using index 60drop table t1,t2; 61CREATE TABLE t1 ( 62PID int(10) unsigned NOT NULL auto_increment, 63payDate date DEFAULT '0000-00-00' NOT NULL, 64recDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, 65URID int(10) unsigned DEFAULT '0' NOT NULL, 66CRID int(10) unsigned DEFAULT '0' NOT NULL, 67amount int(10) unsigned DEFAULT '0' NOT NULL, 68operator int(10) unsigned, 69method enum('unknown','cash','dealer','check','card','lazy','delayed','test') DEFAULT 'unknown' NOT NULL, 70DIID int(10) unsigned, 71reason char(1) binary DEFAULT '' NOT NULL, 72code_id int(10) unsigned, 73qty mediumint(8) unsigned DEFAULT '0' NOT NULL, 74PRIMARY KEY (PID), 75KEY URID (URID), 76KEY reason (reason), 77KEY method (method), 78KEY payDate (payDate) 79); 80INSERT INTO t1 VALUES (1,'1970-01-01','1997-10-17 00:00:00',2529,1,21000,11886,'check',0,'F',16200,6); 81SELECT 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; 82ERROR 42000: Can't group on 'IsNew' 83drop table t1; 84CREATE TABLE t1 ( 85cid mediumint(9) NOT NULL auto_increment, 86firstname varchar(32) DEFAULT '' NOT NULL, 87surname varchar(32) DEFAULT '' NOT NULL, 88PRIMARY KEY (cid) 89); 90INSERT INTO t1 VALUES (1,'That','Guy'); 91INSERT INTO t1 VALUES (2,'Another','Gent'); 92CREATE TABLE t2 ( 93call_id mediumint(8) NOT NULL auto_increment, 94contact_id mediumint(8) DEFAULT '0' NOT NULL, 95PRIMARY KEY (call_id), 96KEY contact_id (contact_id) 97); 98lock tables t1 read,t2 write; 99INSERT INTO t2 VALUES (10,2); 100INSERT INTO t2 VALUES (18,2); 101INSERT INTO t2 VALUES (62,2); 102INSERT INTO t2 VALUES (91,2); 103INSERT INTO t2 VALUES (92,2); 104SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid; 105cid CONCAT(firstname, ' ', surname) COUNT(call_id) 106SELECT 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; 107cid CONCAT(firstname, ' ', surname) COUNT(call_id) 108SELECT 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; 109cid CONCAT(firstname, ' ', surname) COUNT(call_id) 110drop table t2; 111unlock tables; 112drop table t1; 113CREATE TABLE t1 ( 114bug_id mediumint(9) NOT NULL auto_increment, 115groupset bigint(20) DEFAULT '0' NOT NULL, 116assigned_to mediumint(9) DEFAULT '0' NOT NULL, 117bug_file_loc text, 118bug_severity enum('blocker','critical','major','normal','minor','trivial','enhancement') DEFAULT 'blocker' NOT NULL, 119bug_status enum('','NEW','ASSIGNED','REOPENED','RESOLVED','VERIFIED','CLOSED') DEFAULT 'NEW' NOT NULL, 120creation_ts datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, 121delta_ts timestamp, 122short_desc mediumtext, 123long_desc mediumtext, 124op_sys enum('All','Windows 3.1','Windows 95','Windows 98','Windows NT','Windows 2000','Linux','other') DEFAULT 'All' NOT NULL, 125priority enum('P1','P2','P3','P4','P5') DEFAULT 'P1' NOT NULL, 126product varchar(64) DEFAULT '' NOT NULL, 127rep_platform enum('All','PC','VTD-8','Other'), 128reporter mediumint(9) DEFAULT '0' NOT NULL, 129version varchar(16) DEFAULT '' NOT NULL, 130component varchar(50) DEFAULT '' NOT NULL, 131resolution enum('','FIXED','INVALID','WONTFIX','LATER','REMIND','DUPLICATE','WORKSFORME') DEFAULT '' NOT NULL, 132target_milestone varchar(20) DEFAULT '' NOT NULL, 133qa_contact mediumint(9) DEFAULT '0' NOT NULL, 134status_whiteboard mediumtext NOT NULL, 135votes mediumint(9) DEFAULT '0' NOT NULL, 136PRIMARY KEY (bug_id), 137KEY assigned_to (assigned_to), 138KEY creation_ts (creation_ts), 139KEY delta_ts (delta_ts), 140KEY bug_severity (bug_severity), 141KEY bug_status (bug_status), 142KEY op_sys (op_sys), 143KEY priority (priority), 144KEY product (product), 145KEY reporter (reporter), 146KEY version (version), 147KEY component (component), 148KEY resolution (resolution), 149KEY target_milestone (target_milestone), 150KEY qa_contact (qa_contact), 151KEY votes (votes) 152); 153INSERT INTO t1 VALUES (1,0,0,'','normal','','2000-02-10 09:25:12',20000321114747,'','','Linux','P1','TestProduct','PC',3,'other','TestComponent','','M1',0,'',0); 154INSERT 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); 155INSERT 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); 156INSERT 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); 157INSERT 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); 158INSERT 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); 159INSERT 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); 160INSERT INTO t1 VALUES (4,0,0,'','normal','','2000-03-08 18:32:14',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent2','','',0,'',0); 161INSERT INTO t1 VALUES (3,0,0,'','normal','','2000-03-08 18:30:52',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent','','',0,'',0); 162INSERT INTO t1 VALUES (2,0,0,'','enhancement','','2000-03-08 18:24:51',20000321114747,'','','All','P2','TestProduct','Other',4,'other','TestComponent2','','',0,'',0); 163INSERT 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); 164INSERT 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); 165INSERT INTO t1 VALUES (13,0,0,'','normal','','2000-03-15 16:20:44',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent','','',0,'',0); 166INSERT 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); 167INSERT 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); 168INSERT 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); 169INSERT 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); 170INSERT 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); 171INSERT 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); 172INSERT 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); 173INSERT 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); 174INSERT 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); 175INSERT 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); 176INSERT 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); 177INSERT 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); 178INSERT 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); 179INSERT 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); 180INSERT INTO t1 VALUES (28,0,0,'','normal','','2000-03-20 09:51:45',20000321114747,'','','Windows NT','P2','TestProduct','PC',8,'other','TestComponent','','',0,'',0); 181INSERT 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); 182CREATE TABLE t2 ( 183value tinytext, 184program varchar(64), 185initialowner tinytext NOT NULL, 186initialqacontact tinytext NOT NULL, 187description mediumtext NOT NULL 188); 189INSERT INTO t2 VALUES ('TestComponent','TestProduct','id0001','',''); 190INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - conversion','AAAAA','id0001','',''); 191INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - generic','AAAAA','id0001','',''); 192INSERT INTO t2 VALUES ('TestComponent2','TestProduct','id0001','',''); 193INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - eeeeeeeee','AAAAA','id0001','',''); 194INSERT INTO t2 VALUES ('kkkkkkkkkkk lllllllllll','AAAAA','id0001','',''); 195INSERT INTO t2 VALUES ('Test Procedures','AAAAA','id0001','',''); 196INSERT INTO t2 VALUES ('Documentation','AAAAA','id0003','',''); 197INSERT INTO t2 VALUES ('DDDDDDDDD','CCC/CCCCCC','id0002','',''); 198INSERT INTO t2 VALUES ('Eeeeeeee Lite','CCC/CCCCCC','id0002','',''); 199INSERT INTO t2 VALUES ('Eeeeeeee Full','CCC/CCCCCC','id0002','',''); 200INSERT INTO t2 VALUES ('Administration','CCC/CCCCCC','id0002','',''); 201INSERT INTO t2 VALUES ('Distribution','CCC/CCCCCC','id0002','',''); 202INSERT INTO t2 VALUES ('Setup','CCC/CCCCCC','id0002','',''); 203INSERT INTO t2 VALUES ('Unspecified','CCC/CCCCCC','id0002','',''); 204INSERT INTO t2 VALUES ('Web Interface','AAAAAAAA-AAA','id0001','',''); 205INSERT INTO t2 VALUES ('Host communication','AAAAA','id0001','',''); 206select value,description,bug_id from t2 left join t1 on t2.program=t1.product and t2.value=t1.component where program="AAAAA"; 207value description bug_id 208BBBBBBBBBBBBB - conversion 9 209BBBBBBBBBBBBB - conversion 10 210BBBBBBBBBBBBB - generic 7 211BBBBBBBBBBBBB - generic 14 212BBBBBBBBBBBBB - eeeeeeeee NULL 213kkkkkkkkkkk lllllllllll 6 214kkkkkkkkkkk lllllllllll 8 215kkkkkkkkkkk lllllllllll 12 216Test Procedures NULL 217Documentation NULL 218Host communication NULL 219select 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; 220value description COUNT(bug_id) 221BBBBBBBBBBBBB - conversion 2 222BBBBBBBBBBBBB - eeeeeeeee 0 223BBBBBBBBBBBBB - generic 2 224Documentation 0 225Host communication 0 226kkkkkkkkkkk lllllllllll 3 227Test Procedures 0 228select 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); 229value description COUNT(bug_id) 230BBBBBBBBBBBBB - conversion 2 231BBBBBBBBBBBBB - eeeeeeeee 0 232BBBBBBBBBBBBB - generic 2 233Documentation 0 234Host communication 0 235Test Procedures 0 236drop table t1,t2; 237create table t1 (foo int); 238insert into t1 values (1); 239select 1+1, "a",count(*) from t1 where foo in (2); 2401+1 a count(*) 2412 a 0 242insert into t1 values (1); 243select 1+1,"a",count(*) from t1 where foo in (2); 2441+1 a count(*) 2452 a 0 246drop table t1; 247CREATE TABLE t1 ( 248spID int(10) unsigned, 249userID int(10) unsigned, 250score smallint(5) unsigned, 251key (spid), 252key (score) 253); 254INSERT 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); 255explain select userid,count(*) from t1 group by userid desc; 256id select_type table type possible_keys key key_len ref rows Extra 2571 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort 258explain select userid,count(*) from t1 group by userid desc order by null; 259id select_type table type possible_keys key key_len ref rows Extra 2601 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary 261select userid,count(*) from t1 group by userid desc; 262userid count(*) 2633 5 2642 1 2651 2 266select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3); 267userid count(*) 2681 2 269select userid,count(*) from t1 group by userid desc having 3 IN (1,COUNT(*)); 270userid count(*) 271explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc; 272id select_type table type possible_keys key key_len ref rows Extra 2731 SIMPLE t1 range spID spID 5 NULL 3 Using where; Using index 274explain select spid,count(*) from t1 where spid between 1 and 2 group by spid; 275id select_type table type possible_keys key key_len ref rows Extra 2761 SIMPLE t1 range spID spID 5 NULL 3 Using where; Using index 277explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null; 278id select_type table type possible_keys key key_len ref rows Extra 2791 SIMPLE t1 range spID spID 5 NULL 3 Using where; Using index 280select spid,count(*) from t1 where spid between 1 and 2 group by spid; 281spid count(*) 2821 1 2832 2 284select spid,count(*) from t1 where spid between 1 and 2 group by spid desc; 285spid count(*) 2862 2 2871 1 288explain extended select sql_big_result spid,sum(userid) from t1 group by spid desc; 289id select_type table type possible_keys key key_len ref rows filtered Extra 2901 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using filesort 291Warnings: 292Note 1003 select sql_big_result `test`.`t1`.`spID` AS `spid`,sum(`test`.`t1`.`userID`) AS `sum(userid)` from `test`.`t1` group by `test`.`t1`.`spID` desc 293explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null; 294id select_type table type possible_keys key key_len ref rows Extra 2951 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort 296select sql_big_result spid,sum(userid) from t1 group by spid desc; 297spid sum(userid) 2987 3 2996 3 3005 3 3014 3 3023 3 3032 3 3041 1 305explain select sql_big_result score,count(*) from t1 group by score desc; 306id select_type table type possible_keys key key_len ref rows Extra 3071 SIMPLE t1 index NULL score 3 NULL 8 Using index; Using filesort 308explain select sql_big_result score,count(*) from t1 group by score desc order by null; 309id select_type table type possible_keys key key_len ref rows Extra 3101 SIMPLE t1 index NULL score 3 NULL 8 Using index; Using filesort 311select sql_big_result score,count(*) from t1 group by score desc; 312score count(*) 3133 5 3142 1 3151 2 316drop table t1; 317create table t1 (a date default null, b date default null); 318insert t1 values ('1999-10-01','2000-01-10'), ('1997-01-01','1998-10-01'); 319select a,min(b) c,count(distinct rand()) from t1 group by a having c<a + interval 1 day; 320a c count(distinct rand()) 321drop table t1; 322CREATE TABLE t1 (a char(1)); 323INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL); 324SELECT a FROM t1 GROUP BY a; 325a 326NULL 327A 328B 329SELECT a,count(*) FROM t1 GROUP BY a; 330a count(*) 331NULL 3 332A 5 333B 5 334SELECT a FROM t1 GROUP BY binary a; 335a 336NULL 337A 338B 339a 340b 341SELECT a,count(*) FROM t1 GROUP BY binary a; 342a count(*) 343NULL 3 344A 4 345B 4 346a 1 347b 1 348SELECT binary a FROM t1 GROUP BY 1; 349binary a 350NULL 351A 352B 353a 354b 355SELECT binary a,count(*) FROM t1 GROUP BY 1; 356binary a count(*) 357NULL 3 358A 4 359B 4 360a 1 361b 1 362SET BIG_TABLES=1; 363SELECT a FROM t1 GROUP BY a; 364a 365NULL 366A 367B 368SELECT a,count(*) FROM t1 GROUP BY a; 369a count(*) 370NULL 3 371A 5 372B 5 373SELECT a FROM t1 GROUP BY binary a; 374a 375NULL 376A 377B 378a 379b 380SELECT a,count(*) FROM t1 GROUP BY binary a; 381a count(*) 382NULL 3 383A 4 384B 4 385a 1 386b 1 387SELECT binary a FROM t1 GROUP BY 1; 388binary a 389NULL 390A 391B 392a 393b 394SELECT binary a,count(*) FROM t1 GROUP BY 1; 395binary a count(*) 396NULL 3 397A 4 398B 4 399a 1 400b 1 401SET BIG_TABLES=0; 402drop table t1; 403CREATE TABLE t1 ( 404`a` char(193) default NULL, 405`b` char(63) default NULL 406); 407INSERT INTO t1 VALUES ('abc','def'),('hij','klm'); 408SELECT CONCAT(a, b) FROM t1 GROUP BY 1; 409CONCAT(a, b) 410abcdef 411hijklm 412SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1; 413CONCAT(a, b) count(*) 414abcdef 1 415hijklm 1 416SELECT CONCAT(a, b),count(distinct a) FROM t1 GROUP BY 1; 417CONCAT(a, b) count(distinct a) 418abcdef 1 419hijklm 1 420SELECT 1 FROM t1 GROUP BY CONCAT(a, b); 4211 4221 4231 424INSERT INTO t1 values ('hij','klm'); 425SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1; 426CONCAT(a, b) count(*) 427abcdef 1 428hijklm 2 429DROP TABLE t1; 430create table t1 (One int unsigned, Two int unsigned, Three int unsigned, Four int unsigned); 431insert 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); 432select One, Two, sum(Four) from t1 group by One,Two; 433One Two sum(Four) 4341 1 16 4351 2 16 4361 3 16 437drop table t1; 438create table t1 (id integer primary key not null auto_increment, gender char(1)); 439insert into t1 values (NULL, 'M'), (NULL, 'F'),(NULL, 'F'),(NULL, 'F'),(NULL, 'M'); 440create table t2 (user_id integer not null, date date); 441insert 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'); 442select 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; 443gender dist_count percentage 444F 3 60.0000 445M 1 20.0000 446select 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; 447gender dist_count percentage 448M 1 20.0000 449F 3 60.0000 450drop table t1,t2; 451CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID 452)); 453insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL); 454SET @save_optimizer_switch=@@optimizer_switch; 455SET optimizer_switch='outer_join_with_cache=off'; 456select 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; 457xID xID1 4581 1 4592 2 4602 2 4613 134 4623 134 4633 134 4644 185 4654 185 4664 185 4674 185 468select 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; 469xID xID1 Level 4701 1 * 4712 2 ** 4723 134 *** 4734 185 **** 474SET optimizer_switch=@save_optimizer_switch; 475drop table t1; 476CREATE TABLE t1 ( 477pid int(11) unsigned NOT NULL default '0', 478c1id int(11) unsigned default NULL, 479c2id int(11) unsigned default NULL, 480value int(11) unsigned NOT NULL default '0', 481UNIQUE KEY pid2 (pid,c1id,c2id), 482UNIQUE KEY pid (pid,value) 483) ENGINE=MyISAM; 484INSERT INTO t1 VALUES (1, 1, NULL, 1),(1, 2, NULL, 2),(1, NULL, 3, 3),(1, 4, NULL, 4),(1, 5, NULL, 5); 485CREATE TABLE t2 ( 486id int(11) unsigned NOT NULL default '0', 487active enum('Yes','No') NOT NULL default 'Yes', 488PRIMARY KEY (id) 489) ENGINE=MyISAM; 490INSERT INTO t2 VALUES (1, 'Yes'),(2, 'No'),(4, 'Yes'),(5, 'No'); 491CREATE TABLE t3 ( 492id int(11) unsigned NOT NULL default '0', 493active enum('Yes','No') NOT NULL default 'Yes', 494PRIMARY KEY (id) 495); 496INSERT INTO t3 VALUES (3, 'Yes'); 497select * from t1 AS m LEFT JOIN t2 AS c1 ON m.c1id = 498c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = c2.id AND 499c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS NOT NULL); 500pid c1id c2id value id active id active 5011 1 NULL 1 1 Yes NULL NULL 5021 NULL 3 3 NULL NULL 3 Yes 5031 4 NULL 4 4 Yes NULL NULL 504select max(value) from t1 AS m LEFT JOIN t2 AS c1 ON 505m.c1id = c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = 506c2.id AND c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS 507NOT NULL); 508max(value) 5094 510drop table t1,t2,t3; 511create table t1 (a blob null); 512insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(""),(""),(""),("b"); 513select a,count(*) from t1 group by a; 514a count(*) 515NULL 9 516 3 517b 1 518set big_tables=1; 519select a,count(*) from t1 group by a; 520a count(*) 521NULL 9 522 3 523b 1 524drop table t1; 525set big_tables=0; 526SET @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity,@save_optimizer_switch=@@optimizer_switch; 527SET optimizer_switch='outer_join_with_cache=off',@@optimizer_use_condition_selectivity=4; 528create table t1 (a int not null, b int not null); 529insert into t1 values (1,1),(1,2),(3,1),(3,2),(2,2),(2,1); 530create table t2 (a int not null, b int not null, key(a)); 531insert into t2 values (1,3),(3,1),(2,2),(1,1); 532select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b; 533a b 5341 1 5351 3 5362 2 5373 1 538select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL; 539a b 5401 1 5411 3 5422 2 5433 1 544explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b; 545id select_type table type possible_keys key key_len ref rows Extra 5461 SIMPLE t2 ALL a NULL NULL NULL 4 Using temporary; Using filesort 5471 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 548explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL; 549id select_type table type possible_keys key key_len ref rows Extra 5501 SIMPLE t2 ALL a NULL NULL NULL 4 Using temporary 5511 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 552drop table t1,t2; 553SET @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity,@@optimizer_switch=@save_optimizer_switch; 554create table t1 (a int, b int); 555insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4); 556select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a; 557a MAX(b) INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) 5581 4 2 55910 43 6 560select a, MAX(b), CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end from t1 group by a; 561a MAX(b) CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end 5621 4 4 56310 43 43 564select a, MAX(b), FIELD(MAX(b), '43', '4', '5') from t1 group by a; 565a MAX(b) FIELD(MAX(b), '43', '4', '5') 5661 4 2 56710 43 1 568select a, MAX(b), CONCAT_WS(MAX(b), '43', '4', '5') from t1 group by a; 569a MAX(b) CONCAT_WS(MAX(b), '43', '4', '5') 5701 4 434445 57110 43 43434435 572select a, MAX(b), ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f') from t1 group by a; 573a MAX(b) ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f') 5741 4 d 57510 43 NULL 576select a, MAX(b), MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') from t1 group by a; 577a MAX(b) MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') 5781 4 c 57910 43 a,b,d,f 580drop table t1; 581create table t1 (id int not null, qty int not null); 582insert into t1 values (1,2),(1,3),(2,4),(2,5); 583select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and cqty>1; 584id sqty cqty 5851 5 2 5862 9 2 587select id, sum(qty) as sqty from t1 group by id having sqty>2 and count(qty)>1; 588id sqty 5891 5 5902 9 591select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sqty>2 and cqty>1; 592id sqty cqty 5931 5 2 5942 9 2 595select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and count(qty)>1; 596id sqty cqty 5971 5 2 5982 9 2 599select 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; 600count(*) category 6012 NULL 6021 one 6031 two 604select count(*), interval(qty,2,3,4,5,6,7,8) as category from t1 group by category; 605count(*) category 6061 1 6071 2 6081 3 6091 4 610drop table t1; 611CREATE TABLE t1 ( 612userid int(10) unsigned, 613score smallint(5) unsigned, 614key (score) 615); 616INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3); 617SELECT userid,count(*) FROM t1 GROUP BY userid DESC; 618userid count(*) 6193 5 6202 1 6211 2 622EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC; 623id select_type table type possible_keys key key_len ref rows Extra 6241 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort 625DROP TABLE t1; 626CREATE TABLE t1 ( 627i int(11) default NULL, 628j int(11) default NULL 629); 630INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5); 631SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL; 632i COUNT(DISTINCT(i)) 6331 1 6342 1 6354 4 636explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL; 637id select_type table type possible_keys key key_len ref rows Extra 6381 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort 639DROP TABLE t1; 640create table t1 (a int); 641insert into t1 values(null); 642select min(a) is null from t1; 643min(a) is null 6441 645select min(a) is null or null from t1; 646min(a) is null or null 6471 648select 1 and min(a) is null from t1; 6491 and min(a) is null 6501 651drop table t1; 652create table t1 ( col1 int, col2 int ); 653insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2); 654select group_concat( distinct col1 ) as alias from t1 655group by col2 having alias like '%'; 656alias 6571,2 6581,2 6591 660drop table t1; 661create table t1 (a integer, b integer, c integer); 662insert into t1 (a,b) values (1,2),(1,3),(2,5); 663select a, 0.1*0+1 r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2=1; 664a r2 r1 6651 1.0 2 666select a, round(rand(100)*10) r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2<=2; 667a r2 r1 6681 2 2 669select a, round(rand(100)*10) r2, sum(1) r1 from t1 group by a having r1>1 and r2<=2; 670a r2 r1 6711 2 2 672select a,sum(b) from t1 where a=1 group by c; 673a sum(b) 6741 5 675select a*sum(b) from t1 where a=1 group by c; 676a*sum(b) 6775 678select a*sum(b) as f1 from t1 where a=1 group by c having f1 <= 10; 679f1 6805 681select a,a*sum(b) as f1 from t1 where a=1 group by c having a*sum(b)+0 <= 10; 682a f1 6831 5 684select sum(a)*sum(b) from t1 where a=1 group by c; 685sum(a)*sum(b) 68610 687select a,sum(b) from t1 where a=1 group by c having a=1; 688a sum(b) 6891 5 690select a as d,sum(b) from t1 where a=1 group by c having d=1; 691d sum(b) 6921 5 693select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0; 694d 69510 696drop table t1; 697create table t1(a int); 698insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9); 699create table t2 ( 700a int, 701b varchar(200) NOT NULL, 702c varchar(50) NOT NULL, 703d varchar(100) NOT NULL, 704primary key (a,b(132),c,d), 705key a (a,b) 706) charset=utf8; 707insert into t2 select 708x3.a, -- 3 709concat('val-', x3.a + 3*x4.a), -- 12 710concat('val-', @a:=x3.a + 3*x4.a + 12*C.a), -- 120 711concat('val-', @a + 120*D.a) 712from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4; 713delete from t2 where a = 2 and b = 'val-2' order by a,b,c,d limit 30; 714explain select c from t2 where a = 2 and b = 'val-2' group by c; 715id select_type table type possible_keys key key_len ref rows Extra 7161 SIMPLE t2 ref PRIMARY,a PRIMARY 402 const,const 6 Using where 717select c from t2 where a = 2 and b = 'val-2' group by c; 718c 719val-74 720val-98 721drop table t1,t2; 722create table t1 (b int4 unsigned not null); 723insert into t1 values(3000000000); 724select * from t1; 725b 7263000000000 727select min(b) from t1; 728min(b) 7293000000000 730drop table t1; 731CREATE TABLE t1 (id int PRIMARY KEY, user_id int, hostname longtext); 732INSERT INTO t1 VALUES 733(1, 7, 'cache-dtc-af05.proxy.aol.com'), 734(2, 3, 'what.ever.com'), 735(3, 7, 'cache-dtc-af05.proxy.aol.com'), 736(4, 7, 'cache-dtc-af05.proxy.aol.com'); 737SELECT hostname, COUNT(DISTINCT user_id) as no FROM t1 738WHERE hostname LIKE '%aol%' 739 GROUP BY hostname; 740hostname no 741cache-dtc-af05.proxy.aol.com 1 742DROP TABLE t1; 743CREATE TABLE t1 (a int, b int); 744INSERT INTO t1 VALUES (1,2), (1,3); 745SELECT a, b FROM t1 GROUP BY 'const'; 746a b 7471 2 748SELECT DISTINCT a, b FROM t1 GROUP BY 'const'; 749a b 7501 2 751DROP TABLE t1; 752CREATE TABLE t1 (id INT, dt DATETIME); 753INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' ); 754INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' ); 755INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' ); 756INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' ); 757SELECT dt DIV 1 AS f, id FROM t1 GROUP BY f; 758f id 75920050501123000 1 760DROP TABLE t1; 761CREATE TABLE t1 (id varchar(20) NOT NULL); 762INSERT INTO t1 VALUES ('trans1'), ('trans2'); 763CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL); 764INSERT INTO t2 VALUES ('trans1', 'a problem'); 765SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment 766FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment; 767COUNT(DISTINCT(t1.id)) comment 7681 NULL 7691 a problem 770DROP TABLE t1, t2; 771create table t1 (f1 date); 772insert into t1 values('2005-06-06'); 773insert into t1 values('2005-06-06'); 774select date(left(f1+0,8)) from t1 group by 1; 775date(left(f1+0,8)) 7762005-06-06 777drop table t1; 778CREATE TABLE t1 (n int); 779INSERT INTO t1 VALUES (1); 780SELECT n+1 AS n FROM t1 GROUP BY n; 781n 7822 783Warnings: 784Warning 1052 Column 'n' in group statement is ambiguous 785DROP TABLE t1; 786create table t1(f1 varchar(5) key); 787insert into t1 values (1),(2); 788select sql_buffer_result max(f1) is null from t1; 789max(f1) is null 7900 791select sql_buffer_result max(f1)+1 from t1; 792max(f1)+1 7933 794drop table t1; 795CREATE TABLE t1(a INT); 796INSERT INTO t1 VALUES (1),(2); 797SELECT a FROM t1 GROUP BY 'a'; 798a 7991 800SELECT a FROM t1 GROUP BY "a"; 801a 8021 803SELECT a FROM t1 GROUP BY `a`; 804a 8051 8062 807set sql_mode=ANSI_QUOTES; 808SELECT a FROM t1 GROUP BY "a"; 809a 8101 8112 812SELECT a FROM t1 GROUP BY 'a'; 813a 8141 815SELECT a FROM t1 GROUP BY `a`; 816a 8171 8182 819set sql_mode=''; 820SELECT a FROM t1 HAVING 'a' > 1; 821a 822Warnings: 823Warning 1292 Truncated incorrect DOUBLE value: 'a' 824SELECT a FROM t1 HAVING "a" > 1; 825a 826Warnings: 827Warning 1292 Truncated incorrect DOUBLE value: 'a' 828SELECT a FROM t1 HAVING `a` > 1; 829a 8302 831SELECT a FROM t1 ORDER BY 'a' DESC; 832a 8331 8342 835SELECT a FROM t1 ORDER BY "a" DESC; 836a 8371 8382 839SELECT a FROM t1 ORDER BY `a` DESC; 840a 8412 8421 843DROP TABLE t1; 844CREATE TABLE t1 ( 845f1 int(10) unsigned NOT NULL auto_increment primary key, 846f2 varchar(100) NOT NULL default '' 847); 848CREATE TABLE t2 ( 849f1 varchar(10) NOT NULL default '', 850f2 char(3) NOT NULL default '', 851PRIMARY KEY (`f1`), 852KEY `k1` (`f2`,`f1`) 853); 854INSERT INTO t1 values(NULL, ''); 855INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT'); 856SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1; 857avg(t2.f1) 858SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1; 859avg(t2.f1) 860DROP TABLE t1, t2; 861create table t1 (c1 char(3), c2 char(3)); 862create table t2 (c3 char(3), c4 char(3)); 863insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2'); 864insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2'); 865select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4 866group by c2; 867c2 868aaa 869aaa 870Warnings: 871Warning 1052 Column 'c2' in group statement is ambiguous 872show warnings; 873Level Code Message 874Warning 1052 Column 'c2' in group statement is ambiguous 875select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4 876group by t1.c1; 877c2 878aaa 879show warnings; 880Level Code Message 881drop table t1, t2; 882CREATE TABLE t1 (a tinyint(3), b varchar(255), PRIMARY KEY (a)); 883INSERT INTO t1 VALUES (1,'-----'), (6,'Allemagne'), (17,'Autriche'), 884(25,'Belgique'), (54,'Danemark'), (62,'Espagne'), (68,'France'); 885CREATE TABLE t2 (a tinyint(3), b tinyint(3), PRIMARY KEY (a), KEY b (b)); 886INSERT INTO t2 VALUES (1,1), (2,1), (6,6), (18,17), (15,25), (16,25), 887(17,25), (10,54), (5,62),(3,68); 888CREATE VIEW v1 AS select t1.a, concat(t1.b,'') AS b, t1.b as real_b from t1; 889explain 890SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1 891where t2.b=v1.a GROUP BY t2.b; 892id select_type table type possible_keys key key_len ref rows Extra 8931 SIMPLE t2 index b b 2 NULL 10 Using where; Using index 8941 SIMPLE t1 eq_ref PRIMARY PRIMARY 1 test.t2.b 1 895SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1 896where t2.b=v1.a GROUP BY t2.b; 897a b real_b 8981 ----- ----- 8996 Allemagne Allemagne 90017 Autriche Autriche 90125 Belgique Belgique 90254 Danemark Danemark 90362 Espagne Espagne 90468 France France 905DROP VIEW v1; 906DROP TABLE t1,t2; 907CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b)); 908INSERT INTO t1 VALUES (1, 1); 909INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1; 910INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1; 911INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1; 912INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1; 913INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1; 914INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1; 915INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1; 916SELECT MIN(b), MAX(b) from t1; 917MIN(b) MAX(b) 9180 19 919EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b; 920id select_type table type possible_keys key key_len ref rows Extra 9211 SIMPLE t1 index NULL b 5 NULL 128 Using index 922EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b; 923id select_type table type possible_keys key key_len ref rows Extra 9241 SIMPLE t1 index NULL b 5 NULL 128 Using index; Using filesort 925SELECT b, sum(1) FROM t1 GROUP BY b; 926b sum(1) 9270 6 9281 7 9292 7 9303 7 9314 7 9325 7 9336 7 9347 7 9358 7 9369 6 93710 6 93811 6 93912 6 94013 6 94114 6 94215 6 94316 6 94417 6 94518 6 94619 6 947SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b; 948b sum(1) 9490 6 9501 7 9512 7 9523 7 9534 7 9545 7 9556 7 9567 7 9578 7 9589 6 95910 6 96011 6 96112 6 96213 6 96314 6 96415 6 96516 6 96617 6 96718 6 96819 6 969DROP TABLE t1; 970CREATE TABLE t1 (a INT PRIMARY KEY, b INT); 971INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3); 972SET SQL_MODE = 'ONLY_FULL_GROUP_BY'; 973SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b; 974MAX(a)-MIN(a) 9751 9761 9771 978SELECT CEILING(MIN(a)) FROM t1 GROUP BY b; 979CEILING(MIN(a)) 9801 9813 9825 983SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1 984GROUP BY b; 985CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END 986Positive 987Positive 988Positive 989SELECT a + 1 FROM t1 GROUP BY a; 990a + 1 9912 9923 9934 9945 9956 9967 997SELECT a + b FROM t1 GROUP BY b; 998ERROR 42000: 'test.t1.a' isn't in GROUP BY 999SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) 1000FROM t1 AS t1_outer; 1001(SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) 10021 10032 10043 10054 10065 10076 1008SELECT 1 FROM t1 as t1_outer GROUP BY a 1009HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1); 10101 10111 10121 10131 10141 10151 10161 1017SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1) 1018FROM t1 AS t1_outer GROUP BY t1_outer.b; 1019ERROR 42000: 'test.t1_outer.a' isn't in GROUP BY 1020SELECT 1 FROM t1 as t1_outer GROUP BY a 1021HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1); 1022ERROR 42S22: Unknown column 'test.t1_outer.b' in 'field list' 1023SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1) 1024FROM t1 AS t1_outer GROUP BY t1_outer.b; 1025(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1) 102621 102721 102821 1029SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1) 1030FROM t1 AS t1_outer; 1031(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1) 10323 10333 10343 10353 10363 10373 1038SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1) 1039FROM t1 AS t1_outer GROUP BY t1_outer.b; 1040ERROR 42000: 'test.t1_outer.a' isn't in GROUP BY 1041SELECT 1 FROM t1 as t1_outer 1042WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1); 10431 10441 10451 10461 10471 10481 10491 1050SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0; 1051b 10521 10532 10543 1055SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12; 10561 10571 10581 1059SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1); 10601 10611 1062SELECT 1 FROM t1 GROUP BY b HAVING a = 2; 1063ERROR 42S22: Unknown column 'a' in 'having clause' 1064SELECT 1 FROM t1 GROUP BY SUM(b); 1065ERROR HY000: Invalid use of group function 1066SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN 1067(SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a 1068HAVING SUM(t1_inner.b)+t1_outer.b > 5); 1069ERROR 42000: 'test.t1_outer.b' isn't in GROUP BY 1070DROP TABLE t1; 1071SET SQL_MODE = ''; 1072SET SQL_MODE = 'ONLY_FULL_GROUP_BY'; 1073create table t1(f1 int, f2 int); 1074select * from t1 group by f1; 1075ERROR 42000: 'test.t1.f2' isn't in GROUP BY 1076select * from t1 group by f2; 1077ERROR 42000: 'test.t1.f1' isn't in GROUP BY 1078select * from t1 group by f1, f2; 1079f1 f2 1080select t1.f1,t.* from t1, t1 t group by 1; 1081ERROR 42000: 'test.t.f1' isn't in GROUP BY 1082drop table t1; 1083SET SQL_MODE = ''; 1084CREATE TABLE t1( 1085id INT AUTO_INCREMENT PRIMARY KEY, 1086c1 INT NOT NULL, 1087c2 INT NOT NULL, 1088UNIQUE KEY (c2,c1)); 1089INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3); 1090SELECT * FROM t1 ORDER BY c1; 1091id c1 c2 10925 1 3 10934 2 3 10943 3 5 10952 4 1 10961 5 1 1097SELECT * FROM t1 GROUP BY id ORDER BY c1; 1098id c1 c2 10995 1 3 11004 2 3 11013 3 5 11022 4 1 11031 5 1 1104SELECT * FROM t1 GROUP BY id ORDER BY id DESC; 1105id c1 c2 11065 1 3 11074 2 3 11083 3 5 11092 4 1 11101 5 1 1111SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1; 1112id c1 c2 11132 4 1 11141 5 1 11155 1 3 11164 2 3 11173 3 5 1118SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1; 1119id c1 c2 11203 3 5 11215 1 3 11224 2 3 11232 4 1 11241 5 1 1125SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC; 1126id c1 c2 11273 3 5 11284 2 3 11295 1 3 11301 5 1 11312 4 1 1132SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1; 1133id c1 c2 11341 5 1 11354 2 3 11363 3 5 1137SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1; 1138id c1 c2 11393 3 5 11404 2 3 11411 5 1 1142SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC; 1143id c1 c2 11443 3 5 11454 2 3 11461 5 1 1147DROP TABLE t1; 1148# 1149# Bug#27219: Aggregate functions in ORDER BY. 1150# 1151SET @save_sql_mode=@@sql_mode; 1152SET @@sql_mode='ONLY_FULL_GROUP_BY'; 1153CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0); 1154INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4); 1155CREATE TABLE t2 SELECT * FROM t1; 1156SELECT 1 FROM t1 ORDER BY COUNT(*); 11571 11581 1159SELECT 1 FROM t1 ORDER BY COUNT(*) + 1; 11601 11611 1162SELECT 1 FROM t1 ORDER BY COUNT(*) + a; 1163ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1164SELECT 1 FROM t1 ORDER BY COUNT(*), 1; 11651 11661 1167SELECT 1 FROM t1 ORDER BY COUNT(*), a; 1168ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1169SELECT 1 FROM t1 ORDER BY SUM(a); 11701 11711 1172SELECT 1 FROM t1 ORDER BY SUM(a + 1); 11731 11741 1175SELECT 1 FROM t1 ORDER BY SUM(a) + 1; 11761 11771 1178SELECT 1 FROM t1 ORDER BY SUM(a), b; 1179ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1180SELECT a FROM t1 ORDER BY COUNT(b); 1181ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1182SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2); 1183a 11843 11852 11863 11872 11883 11894 1190SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a), t2.a FROM t2); 1191ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1192SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a); 1193ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1194SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1); 1195ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1196SELECT t1.a FROM t1 1197WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1); 1198ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1199SELECT t1.a FROM t1 GROUP BY t1.a 1200HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1); 1201ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1202SELECT t1.a FROM t1 GROUP BY t1.a 1203HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b)); 1204a 12052 12063 12074 1208SELECT t1.a FROM t1 GROUP BY t1.a 1209HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b)); 1210ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1211SELECT t1.a FROM t1 GROUP BY t1.a 1212HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b)); 1213ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1214SELECT t1.a FROM t1 1215WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b)); 1216ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1217SELECT 1 FROM t1 GROUP BY t1.a 1218HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1); 12191 12201 12211 12221 1223SELECT 1 FROM t1 GROUP BY t1.a 1224HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1); 12251 12261 12271 12281 1229SELECT 1 FROM t1 GROUP BY t1.a 1230HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1); 12311 12321 12331 12341 1235SELECT 1 FROM t1 GROUP BY t1.a 1236HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1); 1237ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1238SELECT 1 FROM t1 GROUP BY t1.a 1239HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1); 1240ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1241SELECT 1 FROM t1 GROUP BY t1.a 1242HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1); 1243ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1244SELECT t1.a FROM t1 1245WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a 1246ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1); 1247a 12484 1249SELECT t1.a, SUM(t1.b) FROM t1 1250WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a 1251ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1) 1252GROUP BY t1.a; 1253a SUM(t1.b) 12544 4 1255SELECT t1.a, SUM(t1.b) FROM t1 1256WHERE t1.a = (SELECT SUM(t2.b) FROM t2 1257ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1) 1258GROUP BY t1.a; 1259a SUM(t1.b) 1260SELECT t1.a, SUM(t1.b) FROM t1 1261WHERE t1.a = (SELECT SUM(t2.b) FROM t2 1262ORDER BY SUM(t2.b + t1.a) LIMIT 1) 1263GROUP BY t1.a; 1264a SUM(t1.b) 1265SELECT t1.a FROM t1 GROUP BY t1.a 1266HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1); 1267a 1268select avg ( 1269(select 1270(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt 1271from t1 as outr order by outr.a limit 1)) 1272from t1 as most_outer; 1273avg ( 1274(select 1275(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt 1276from t1 as outr order by outr.a limit 1)) 127729.0000 1278select avg ( 1279(select ( 1280(select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt 1281from t1 as outr order by count(outr.a) limit 1)) as tt 1282from t1 as most_outer; 1283ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 1284select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a; 1285tt 128629 128729 128835 128935 129035 129141 1292SET sql_mode=@save_sql_mode; 1293DROP TABLE t1, t2; 1294# 1295# BUG#38072: Wrong result: HAVING not observed in a query with aggregate 1296# 1297CREATE TABLE t1 ( 1298pk int(11) NOT NULL AUTO_INCREMENT, 1299int_nokey int(11) NOT NULL, 1300int_key int(11) NOT NULL, 1301varchar_key varchar(1) NOT NULL, 1302varchar_nokey varchar(1) NOT NULL, 1303PRIMARY KEY (pk), 1304KEY int_key (int_key), 1305KEY varchar_key (varchar_key) 1306); 1307INSERT INTO t1 VALUES 1308(1,5,5, 'h','h'), 1309(2,1,1, '{','{'), 1310(3,1,1, 'z','z'), 1311(4,8,8, 'x','x'), 1312(5,7,7, 'o','o'), 1313(6,3,3, 'p','p'), 1314(7,9,9, 'c','c'), 1315(8,0,0, 'k','k'), 1316(9,6,6, 't','t'), 1317(10,0,0,'c','c'); 1318explain SELECT COUNT(varchar_key) AS X FROM t1 WHERE pk = 8 having 'foo'='bar'; 1319id select_type table type possible_keys key key_len ref rows Extra 13201 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING 1321SELECT COUNT(varchar_key) AS X FROM t1 WHERE pk = 8 having 'foo'='bar'; 1322X 1323drop table t1; 1324End of 5.0 tests 1325CREATE TABLE t1 (a INT, b INT, 1326PRIMARY KEY (a), 1327KEY i2(a,b)); 1328INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8); 1329INSERT INTO t1 SELECT a + 8,b FROM t1; 1330INSERT INTO t1 SELECT a + 16,b FROM t1; 1331INSERT INTO t1 SELECT a + 32,b FROM t1; 1332INSERT INTO t1 SELECT a + 64,b FROM t1; 1333INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16; 1334ANALYZE TABLE t1; 1335Table Op Msg_type Msg_text 1336test.t1 analyze status Engine-independent statistics collected 1337test.t1 analyze status OK 1338EXPLAIN SELECT a FROM t1 WHERE a < 2; 1339id select_type table type possible_keys key key_len ref rows Extra 13401 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 1 Using where; Using index 1341EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a; 1342id select_type table type possible_keys key key_len ref rows Extra 13431 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 1 Using where; Using index 1344EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a; 1345id select_type table type possible_keys key key_len ref rows Extra 13461 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 1 Using where; Using index 1347EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2); 1348id select_type table type possible_keys key key_len ref rows Extra 13491 SIMPLE t1 ALL NULL NULL NULL NULL 144 1350EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2); 1351id select_type table type possible_keys key key_len ref rows Extra 13521 SIMPLE t1 ALL NULL NULL NULL NULL 144 1353# 1354# For this explain, the query plan is weird: if we are using 1355# the primary key for reasons other than doing grouping, can't 1356# GROUP BY code take advantage of this? Well, currently it doesnt: 1357EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a; 1358id select_type table type possible_keys key key_len ref rows Extra 13591 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index; Using filesort 1360# Here's a proof it is really doing sorting: 1361flush status; 1362SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a; 1363show status like 'Sort_%'; 1364Variable_name Value 1365Sort_merge_passes 0 1366Sort_priority_queue_sorts 0 1367Sort_range 0 1368Sort_rows 144 1369Sort_scan 1 1370# Proof ends. 1371# 1372# For this explain, the query plan is weird: if we are using 1373# the primary key for reasons other than doing sorting, can't 1374# ORDER BY code take advantage of this? Well, currently it doesnt: 1375EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a; 1376id select_type table type possible_keys key key_len ref rows Extra 13771 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index; Using filesort 1378# Here's a proof it is really doing sorting: 1379flush status; 1380SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a; 1381show status like 'Sort_%'; 1382Variable_name Value 1383Sort_merge_passes 0 1384Sort_priority_queue_sorts 0 1385Sort_range 0 1386Sort_rows 144 1387Sort_scan 1 1388# Proof ends. 1389# 1390SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a; 1391a 13921 13932 13943 13954 13965 13976 13987 13998 14009 140110 140211 140312 140413 140514 140615 140716 140817 140918 141019 141120 141221 141322 141423 141524 141625 141726 141827 141928 142029 142130 142231 142332 142433 142534 142635 142736 142837 142938 143039 143140 143241 143342 143443 143544 143645 143746 143847 143948 144049 144150 144251 144352 144453 144554 144655 144756 144857 144958 145059 145160 145261 145362 145463 145564 145665 145766 145867 145968 146069 146170 146271 146372 146473 146574 146675 146776 146877 146978 147079 147180 147281 147382 147483 147584 147685 147786 147887 147988 148089 148190 148291 148392 148493 148594 148695 148796 148897 148998 149099 1491100 1492101 1493102 1494103 1495104 1496105 1497106 1498107 1499108 1500109 1501110 1502111 1503112 1504113 1505114 1506115 1507116 1508117 1509118 1510119 1511120 1512121 1513122 1514123 1515124 1516125 1517126 1518127 1519128 1520129 1521130 1522131 1523132 1524133 1525134 1526135 1527136 1528137 1529138 1530139 1531140 1532141 1533142 1534143 1535144 1536EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) 1537IGNORE INDEX FOR GROUP BY (i2) GROUP BY a; 1538id select_type table type possible_keys key key_len ref rows Extra 15391 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index 1540EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2); 1541id select_type table type possible_keys key key_len ref rows Extra 15421 SIMPLE t1 index NULL i2 9 NULL 144 Using index 1543EXPLAIN SELECT a FROM t1 FORCE INDEX (i2); 1544id select_type table type possible_keys key key_len ref rows Extra 15451 SIMPLE t1 index NULL i2 9 NULL 144 Using index 1546EXPLAIN SELECT a FROM t1 USE INDEX (); 1547id select_type table type possible_keys key key_len ref rows Extra 15481 SIMPLE t1 ALL NULL NULL NULL NULL 144 1549EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2); 1550id select_type table type possible_keys key key_len ref rows Extra 15511 SIMPLE t1 ALL NULL NULL NULL NULL 144 1552EXPLAIN SELECT a FROM t1 1553FORCE INDEX (PRIMARY) 1554IGNORE INDEX FOR GROUP BY (i2) 1555IGNORE INDEX FOR ORDER BY (i2) 1556USE INDEX (i2); 1557ERROR HY000: Incorrect usage of USE INDEX and FORCE INDEX 1558EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX (); 1559id select_type table type possible_keys key key_len ref rows Extra 15601 SIMPLE t1 index NULL i2 9 NULL 144 Using index 1561EXPLAIN SELECT a FROM t1 FORCE INDEX (); 1562ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 1563EXPLAIN SELECT a FROM t1 IGNORE INDEX (); 1564ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 1565EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2) 1566USE INDEX FOR GROUP BY (i2) GROUP BY a; 1567id select_type table type possible_keys key key_len ref rows Extra 15681 SIMPLE t1 # NULL i2 # NULL # # 1569EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2) 1570FORCE INDEX FOR GROUP BY (i2) GROUP BY a; 1571id select_type table type possible_keys key key_len ref rows Extra 15721 SIMPLE t1 index NULL i2 9 NULL 144 Using index 1573EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2); 1574id select_type table type possible_keys key key_len ref rows Extra 15751 SIMPLE t1 ALL NULL NULL NULL NULL 144 1576EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX (); 1577id select_type table type possible_keys key key_len ref rows Extra 15781 SIMPLE t1 ALL NULL NULL NULL NULL 144 1579EXPLAIN SELECT a FROM t1 1580USE INDEX FOR GROUP BY (i2) 1581USE INDEX FOR ORDER BY (i2) 1582USE INDEX FOR JOIN (i2); 1583id select_type table type possible_keys key key_len ref rows Extra 15841 SIMPLE t1 index NULL i2 9 NULL 144 Using index 1585EXPLAIN SELECT a FROM t1 1586USE INDEX FOR JOIN (i2) 1587USE INDEX FOR JOIN (i2) 1588USE INDEX FOR JOIN (i2,i2); 1589id select_type table type possible_keys key key_len ref rows Extra 15901 SIMPLE t1 index NULL i2 9 NULL 144 Using index 1591SET @save_optimizer_switch=@@optimizer_switch; 1592SET optimizer_switch='semijoin_with_cache=off'; 1593EXPLAIN SELECT 1 FROM t1 WHERE a IN 1594(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); 1595id select_type table type possible_keys key key_len ref rows Extra 15961 PRIMARY t1 index PRIMARY,i2 PRIMARY 4 NULL 144 Using index 15971 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 15982 MATERIALIZED t1 ALL NULL NULL NULL NULL 144 1599SET optimizer_switch=@save_optimizer_switch; 1600CREATE TABLE t2 (a INT, b INT, KEY(a)); 1601INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); 1602EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; 1603id select_type table type possible_keys key key_len ref rows Extra 16041 SIMPLE t2 index NULL a 5 NULL 2 1605EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2; 1606id select_type table type possible_keys key key_len ref rows Extra 16071 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort 1608SET @save_optimizer_switch=@@optimizer_switch; 1609SET optimizer_switch='semijoin_with_cache=off'; 1610EXPLAIN SELECT 1 FROM t2 WHERE a IN 1611(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); 1612id select_type table type possible_keys key key_len ref rows Extra 16131 PRIMARY t2 index a a 5 NULL 4 Using index 16141 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 16152 MATERIALIZED t1 ALL NULL NULL NULL NULL 144 1616SET optimizer_switch=@save_optimizer_switch; 1617DROP TABLE t1, t2; 1618CREATE TABLE t1( 1619a INT, 1620b INT NOT NULL, 1621c INT NOT NULL, 1622d INT, 1623UNIQUE KEY (c,b) 1624); 1625INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4); 1626CREATE TABLE t2( 1627a INT, 1628b INT, 1629UNIQUE KEY(a,b) 1630); 1631INSERT INTO t2 VALUES (NULL, NULL), (NULL, NULL), (NULL, 1), (1, NULL), (1, 1), (1,2); 1632EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d; 1633id select_type table type possible_keys key key_len ref rows Extra 16341 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort 1635SELECT c,b,d FROM t1 GROUP BY c,b,d; 1636c b d 16371 1 50 16383 1 4 16393 2 40 1640EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; 1641id select_type table type possible_keys key key_len ref rows Extra 16421 SIMPLE t1 ALL NULL NULL NULL NULL 3 1643SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; 1644c b d 16451 1 50 16463 2 40 16473 1 4 1648EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d; 1649id select_type table type possible_keys key key_len ref rows Extra 16501 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort 1651SELECT c,b,d FROM t1 ORDER BY c,b,d; 1652c b d 16531 1 50 16543 1 4 16553 2 40 1656EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b; 1657id select_type table type possible_keys key key_len ref rows Extra 16581 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort 1659SELECT c,b,d FROM t1 GROUP BY c,b; 1660c b d 16611 1 50 16623 1 4 16633 2 40 1664EXPLAIN SELECT c,b FROM t1 GROUP BY c,b; 1665id select_type table type possible_keys key key_len ref rows Extra 16661 SIMPLE t1 index NULL c 8 NULL 3 Using index 1667SELECT c,b FROM t1 GROUP BY c,b; 1668c b 16691 1 16703 1 16713 2 1672EXPLAIN SELECT a,b from t2 ORDER BY a,b; 1673id select_type table type possible_keys key key_len ref rows Extra 16741 SIMPLE t2 index NULL a 10 NULL 6 Using index 1675SELECT a,b from t2 ORDER BY a,b; 1676a b 1677NULL NULL 1678NULL NULL 1679NULL 1 16801 NULL 16811 1 16821 2 1683EXPLAIN SELECT a,b from t2 GROUP BY a,b; 1684id select_type table type possible_keys key key_len ref rows Extra 16851 SIMPLE t2 index NULL a 10 NULL 6 Using index 1686SELECT a,b from t2 GROUP BY a,b; 1687a b 1688NULL NULL 1689NULL 1 16901 NULL 16911 1 16921 2 1693EXPLAIN SELECT a from t2 GROUP BY a; 1694id select_type table type possible_keys key key_len ref rows Extra 16951 SIMPLE t2 index NULL a 10 NULL 6 Using index 1696SELECT a from t2 GROUP BY a; 1697a 1698NULL 16991 1700EXPLAIN SELECT b from t2 GROUP BY b; 1701id select_type table type possible_keys key key_len ref rows Extra 17021 SIMPLE t2 index NULL a 10 NULL 6 Using index; Using temporary; Using filesort 1703SELECT b from t2 GROUP BY b; 1704b 1705NULL 17061 17072 1708DROP TABLE t1; 1709DROP TABLE t2; 1710CREATE TABLE t1 ( a INT, b INT ); 1711SELECT b c, (SELECT a FROM t1 WHERE b = c) 1712FROM t1; 1713c (SELECT a FROM t1 WHERE b = c) 1714SELECT b c, (SELECT a FROM t1 WHERE b = c) 1715FROM t1 1716HAVING b = 10; 1717c (SELECT a FROM t1 WHERE b = c) 1718SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c) 1719FROM t1 1720HAVING b = 10; 1721ERROR 42S22: Reference 'c' not supported (reference to group function) 1722SET @old_sql_mode = @@sql_mode; 1723SET @@sql_mode='ONLY_FULL_GROUP_BY'; 1724SELECT b c, (SELECT a FROM t1 WHERE b = c) 1725FROM t1; 1726c (SELECT a FROM t1 WHERE b = c) 1727SELECT b c, (SELECT a FROM t1 WHERE b = c) 1728FROM t1 1729HAVING b = 10; 1730ERROR 42000: Non-grouping field 'b' is used in HAVING clause 1731SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c) 1732FROM t1 1733HAVING b = 10; 1734ERROR 42S22: Reference 'c' not supported (reference to group function) 1735INSERT INTO t1 VALUES (1, 1); 1736SELECT b c, (SELECT a FROM t1 WHERE b = c) 1737FROM t1; 1738c (SELECT a FROM t1 WHERE b = c) 17391 1 1740INSERT INTO t1 VALUES (2, 1); 1741SELECT b c, (SELECT a FROM t1 WHERE b = c) 1742FROM t1; 1743ERROR 21000: Subquery returns more than 1 row 1744DROP TABLE t1; 1745SET @@sql_mode = @old_sql_mode; 1746SET @old_sql_mode = @@sql_mode; 1747SET @@sql_mode='ONLY_FULL_GROUP_BY'; 1748CREATE TABLE t1(i INT); 1749INSERT INTO t1 VALUES (1), (10); 1750SELECT COUNT(i) FROM t1; 1751COUNT(i) 17522 1753SELECT COUNT(i) FROM t1 WHERE i > 1; 1754COUNT(i) 17551 1756DROP TABLE t1; 1757SET @@sql_mode = @old_sql_mode; 1758# 1759# Bug #45640: optimizer bug produces wrong results 1760# 1761CREATE TABLE t1 (a INT, b INT); 1762INSERT INTO t1 VALUES (4, 40), (1, 10), (2, 20), (2, 20), (3, 30); 1763# should return 4 ordered records: 1764SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa; 1765aa COUNT(DISTINCT b) 17661 1 17672 1 17683 1 17694 1 1770SELECT (SELECT (SELECT t1.a)) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa; 1771aa COUNT(DISTINCT b) 17721 1 17732 1 17743 1 17754 1 1776SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa+0; 1777aa COUNT(DISTINCT b) 17781 1 17792 1 17803 1 17814 1 1782# should return the same result in a reverse order: 1783SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY -aa; 1784aa COUNT(DISTINCT b) 17854 1 17863 1 17872 1 17881 1 1789# execution plan should not use temporary table: 1790EXPLAIN EXTENDED 1791SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa+0; 1792id select_type table type possible_keys key key_len ref rows filtered Extra 17931 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using filesort 17942 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1795Warnings: 1796Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 1797Note 1003 /* select#1 */ select <expr_cache><`test`.`t1`.`a`>((/* select#2 */ select `test`.`t1`.`a`)) AS `aa`,count(distinct `test`.`t1`.`b`) AS `COUNT(DISTINCT b)` from `test`.`t1` group by <expr_cache><`test`.`t1`.`a`>((/* select#2 */ select `test`.`t1`.`a`)) + 0 1798EXPLAIN EXTENDED 1799SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY -aa; 1800id select_type table type possible_keys key key_len ref rows filtered Extra 18011 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using filesort 18022 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1803Warnings: 1804Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 1805Note 1003 /* select#1 */ select <expr_cache><`test`.`t1`.`a`>((/* select#2 */ select `test`.`t1`.`a`)) AS `aa`,count(distinct `test`.`t1`.`b`) AS `COUNT(DISTINCT b)` from `test`.`t1` group by -<expr_cache><`test`.`t1`.`a`>((/* select#2 */ select `test`.`t1`.`a`)) 1806# should return only one record 1807SELECT (SELECT tt.a FROM t1 tt LIMIT 1) aa, COUNT(DISTINCT b) FROM t1 1808GROUP BY aa; 1809aa COUNT(DISTINCT b) 18104 4 1811CREATE TABLE t2 SELECT DISTINCT a FROM t1; 1812# originally reported queries (1st two columns of next two query 1813# results should be same): 1814SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(DISTINCT b) 1815FROM t1 GROUP BY aa, b; 1816aa b COUNT(DISTINCT b) 18171 10 1 18182 20 1 18193 30 1 18204 40 1 1821SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT( b) 1822FROM t1 GROUP BY aa, b; 1823aa b COUNT( b) 18241 10 1 18252 20 2 18263 30 1 18274 40 1 1828# ORDER BY for sure: 1829SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(DISTINCT b) 1830FROM t1 GROUP BY aa, b ORDER BY -aa, -b; 1831aa b COUNT(DISTINCT b) 18324 40 1 18333 30 1 18342 20 1 18351 10 1 1836SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT( b) 1837FROM t1 GROUP BY aa, b ORDER BY -aa, -b; 1838aa b COUNT( b) 18394 40 1 18403 30 1 18412 20 2 18421 10 1 1843DROP TABLE t1, t2; 1844# 1845# Bug#52051: Aggregate functions incorrectly returns NULL from outer 1846# join query 1847# 1848CREATE TABLE t1 (a INT PRIMARY KEY); 1849CREATE TABLE t2 (a INT PRIMARY KEY); 1850INSERT INTO t2 VALUES (1), (2); 1851EXPLAIN SELECT MIN(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; 1852id select_type table type possible_keys key key_len ref rows Extra 18531 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 1854SELECT MIN(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; 1855MIN(t2.a) 18561 1857EXPLAIN SELECT MAX(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; 1858id select_type table type possible_keys key key_len ref rows Extra 18591 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 1860SELECT MAX(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; 1861MAX(t2.a) 18622 1863DROP TABLE t1, t2; 1864CREATE TABLE t1 (a int(11) NOT NULL); 1865INSERT INTO t1 VALUES (1),(2); 1866CREATE TABLE t2 ( 1867key_col int(11) NOT NULL, 1868KEY (key_col) 1869); 1870INSERT INTO t2 VALUES (1),(2); 1871select min(t2.key_col) from t1,t2 where t1.a=1; 1872min(t2.key_col) 18731 1874select min(t2.key_col) from t1,t2 where t1.a > 1000; 1875min(t2.key_col) 1876NULL 1877select min(t2.key_col)+1 from t1,t2 where t1.a> 1000; 1878min(t2.key_col)+1 1879NULL 1880drop table t1,t2; 1881# 1882# Bug#55188: GROUP BY, GROUP_CONCAT and TEXT - inconsistent results 1883# 1884CREATE TABLE t1 (a text, b varchar(10)); 1885INSERT INTO t1 VALUES (repeat('1', 1300),'one'), (repeat('1', 1300),'two'); 1886EXPLAIN 1887SELECT SUBSTRING(a,1,10), LENGTH(a), GROUP_CONCAT(b) FROM t1 GROUP BY a; 1888id 1 1889select_type SIMPLE 1890table t1 1891type ALL 1892possible_keys NULL 1893key NULL 1894key_len NULL 1895ref NULL 1896rows 2 1897Extra Using filesort 1898SELECT SUBSTRING(a,1,10), LENGTH(a), GROUP_CONCAT(b) FROM t1 GROUP BY a; 1899SUBSTRING(a,1,10) LENGTH(a) GROUP_CONCAT(b) 19001111111111 1300 one,two 1901EXPLAIN 1902SELECT SUBSTRING(a,1,10), LENGTH(a) FROM t1 GROUP BY a; 1903id 1 1904select_type SIMPLE 1905table t1 1906type ALL 1907possible_keys NULL 1908key NULL 1909key_len NULL 1910ref NULL 1911rows 2 1912Extra Using temporary; Using filesort 1913SELECT SUBSTRING(a,1,10), LENGTH(a) FROM t1 GROUP BY a; 1914SUBSTRING(a,1,10) LENGTH(a) 19151111111111 1300 1916DROP TABLE t1; 1917# 1918# Bug#57688 Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field 1919# 1920CREATE TABLE t1(f1 INT NOT NULL); 1921INSERT INTO t1 VALUES (16777214),(0); 1922SELECT COUNT(*) FROM t1 LEFT JOIN t1 t2 1923ON 1 WHERE t2.f1 > 1 GROUP BY t2.f1; 1924COUNT(*) 19252 1926DROP TABLE t1; 1927# 1928# Bug#59839: Aggregation followed by subquery yields wrong result 1929# 1930CREATE TABLE t1 ( 1931a INT, 1932b INT, 1933c INT, 1934KEY (a, b) 1935); 1936INSERT INTO t1 VALUES 1937( 1, 1, 1 ), 1938( 1, 2, 2 ), 1939( 1, 3, 3 ), 1940( 1, 4, 6 ), 1941( 1, 5, 5 ), 1942( 1, 9, 13 ), 1943( 2, 1, 6 ), 1944( 2, 2, 7 ), 1945( 2, 3, 8 ); 1946EXPLAIN 1947SELECT a, AVG(t1.b), 1948(SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c, 1949(SELECT t12.c FROM t1 t12 WHERE t12.a = t1.a AND t12.b = AVG(t1.b)) AS t12c 1950FROM t1 GROUP BY a; 1951id select_type table type possible_keys key key_len ref rows Extra 19521 PRIMARY t1 index NULL a 10 NULL 9 Using index 19533 DEPENDENT SUBQUERY t12 ref a a 10 func,func 2 Using index condition 19542 DEPENDENT SUBQUERY t11 ref a a 10 func,func 2 Using index condition 1955SELECT a, AVG(t1.b), 1956(SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c, 1957(SELECT t12.c FROM t1 t12 WHERE t12.a = t1.a AND t12.b = AVG(t1.b)) AS t12c 1958FROM t1 GROUP BY a; 1959a AVG(t1.b) t11c t12c 19601 4.0000 6 6 19612 2.0000 7 7 1962DROP TABLE t1; 1963# 1964# Bug#11765254 (58200): Assertion failed: param.sort_length when grouping 1965# by functions 1966# 1967SET BIG_TABLES=1; 1968CREATE TABLE t1(a INT); 1969INSERT INTO t1 VALUES (0),(0); 1970SELECT 1 FROM t1 GROUP BY IF(`a`,'',''); 19711 19721 1973SELECT 1 FROM t1 GROUP BY TRIM(LEADING RAND() FROM ''); 19741 19751 1976SELECT 1 FROM t1 GROUP BY SUBSTRING('',SLEEP(0),''); 19771 19781 1979Warnings: 1980Warning 1292 Truncated incorrect INTEGER value: '' 1981Warning 1292 Truncated incorrect INTEGER value: '' 1982Warning 1292 Truncated incorrect INTEGER value: '' 1983SELECT 1 FROM t1 GROUP BY SUBSTRING(SYSDATE() FROM 'K' FOR 'jxW<'); 19841 19851 1986Warnings: 1987Warning 1292 Truncated incorrect INTEGER value: 'K' 1988Warning 1292 Truncated incorrect INTEGER value: 'jxW<' 1989Warning 1292 Truncated incorrect INTEGER value: 'K' 1990Warning 1292 Truncated incorrect INTEGER value: 'jxW<' 1991Warning 1292 Truncated incorrect INTEGER value: 'K' 1992Warning 1292 Truncated incorrect INTEGER value: 'jxW<' 1993DROP TABLE t1; 1994SET BIG_TABLES=0; 1995# 1996# MDEV-641 LP:1002108 - Wrong result (or crash) from a query with duplicated field in the group list and a limit clause 1997# Bug#11761078: 53534: INCORRECT 'SELECT SQL_BIG_RESULT...' 1998# WITH GROUP BY ON DUPLICATED FIELDS 1999# 2000CREATE TABLE t1( 2001col1 int, 2002UNIQUE INDEX idx (col1)); 2003INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10), 2004(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); 2005EXPLAIN SELECT col1 AS field1, col1 AS field2 2006FROM t1 GROUP BY field1, field2;; 2007id select_type table type possible_keys key key_len ref rows Extra 20081 SIMPLE t1 index NULL idx 5 NULL 20 Using index 2009FLUSH STATUS; 2010SELECT col1 AS field1, col1 AS field2 2011FROM t1 GROUP BY field1, field2;; 2012field1 field2 20131 1 20142 2 20153 3 20164 4 20175 5 20186 6 20197 7 20208 8 20219 9 202210 10 202311 11 202412 12 202513 13 202614 14 202715 15 202816 16 202917 17 203018 18 203119 19 203220 20 2033SHOW SESSION STATUS LIKE 'Sort_scan%'; 2034Variable_name Value 2035Sort_scan 0 2036EXPLAIN SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2 2037FROM t1 GROUP BY field1, field2; 2038id select_type table type possible_keys key key_len ref rows Extra 20391 SIMPLE t1 index NULL idx 5 NULL 20 Using index; Using filesort 2040FLUSH STATUS; 2041SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2 2042FROM t1 GROUP BY field1, field2; 2043field1 field2 20441 1 20452 2 20463 3 20474 4 20485 5 20496 6 20507 7 20518 8 20529 9 205310 10 205411 11 205512 12 205613 13 205714 14 205815 15 205916 16 206017 17 206118 18 206219 19 206320 20 2064SHOW SESSION STATUS LIKE 'Sort_scan%'; 2065Variable_name Value 2066Sort_scan 1 2067CREATE VIEW v1 AS SELECT * FROM t1; 2068SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2 2069FROM v1 2070GROUP BY field1, field2; 2071field1 field2 20721 1 20732 2 20743 3 20754 4 20765 5 20776 6 20787 7 20798 8 20809 9 208110 10 208211 11 208312 12 208413 13 208514 14 208615 15 208716 16 208817 17 208918 18 209019 19 209120 20 2092SELECT SQL_BIG_RESULT tbl1.col1 AS field1, tbl2.col1 AS field2 2093FROM t1 as tbl1, t1 as tbl2 2094GROUP BY field1, field2 2095LIMIT 3; 2096field1 field2 20971 1 20981 2 20991 3 2100explain 2101select col1 f1, col1 f2 from t1 order by f2, f1; 2102id select_type table type possible_keys key key_len ref rows Extra 21031 SIMPLE t1 index NULL idx 5 NULL 20 Using index 2104explain 2105select col1 f1, col1 f2 from t1 order by f2, f1+0; 2106id select_type table type possible_keys key key_len ref rows Extra 21071 SIMPLE t1 index NULL idx 5 NULL 20 Using index; Using filesort 2108select col1 f1, col1 f2 from t1 order by f2, f1+0; 2109f1 f2 21101 1 21112 2 21123 3 21134 4 21145 5 21156 6 21167 7 21178 8 21189 9 211910 10 212011 11 212112 12 212213 13 212314 14 212415 15 212516 16 212617 17 212718 18 212819 19 212920 20 2130explain 2131select col1 f1, col1 f2 from t1 group by f1; 2132id select_type table type possible_keys key key_len ref rows Extra 21331 SIMPLE t1 index NULL idx 5 NULL 20 Using index 2134select col1 f1, col1 f2 from t1 group by f1; 2135f1 f2 21361 1 21372 2 21383 3 21394 4 21405 5 21416 6 21427 7 21438 8 21449 9 214510 10 214611 11 214712 12 214813 13 214914 14 215015 15 215116 16 215217 17 215318 18 215419 19 215520 20 2156explain 2157select col1 f1, col1 f2 from t1 group by f1, f2; 2158id select_type table type possible_keys key key_len ref rows Extra 21591 SIMPLE t1 index NULL idx 5 NULL 20 Using index 2160select col1 f1, col1 f2 from t1 group by f1, f2; 2161f1 f2 21621 1 21632 2 21643 3 21654 4 21665 5 21676 6 21687 7 21698 8 21709 9 217110 10 217211 11 217312 12 217413 13 217514 14 217615 15 217716 16 217817 17 217918 18 218019 19 218120 20 2182explain 2183select col1 f1, col1 f2 from t1 group by f2 order by f2, f1; 2184id select_type table type possible_keys key key_len ref rows Extra 21851 SIMPLE t1 index NULL idx 5 NULL 20 Using index 2186select col1 f1, col1 f2 from t1 group by f2 order by f2, f1; 2187f1 f2 21881 1 21892 2 21903 3 21914 4 21925 5 21936 6 21947 7 21958 8 21969 9 219710 10 219811 11 219912 12 220013 13 220114 14 220215 15 220316 16 220417 17 220518 18 220619 19 220720 20 2208explain 2209select col1 f1, col1 f2 from t1 group by f1, f2 order by f2, f1; 2210id select_type table type possible_keys key key_len ref rows Extra 22111 SIMPLE t1 index NULL idx 5 NULL 20 Using index 2212select col1 f1, col1 f2 from t1 group by f1, f2 order by f2, f1; 2213f1 f2 22141 1 22152 2 22163 3 22174 4 22185 5 22196 6 22207 7 22218 8 22229 9 222310 10 222411 11 222512 12 222613 13 222714 14 222815 15 222916 16 223017 17 223118 18 223219 19 223320 20 2234CREATE TABLE t2( 2235col1 int, 2236col2 int, 2237UNIQUE INDEX idx (col1, col2)); 2238INSERT INTO t2(col1, col2) VALUES 2239(1,20),(2,19),(3,18),(4,17),(5,16),(6,15),(7,14),(8,13),(9,12),(10,11), 2240(11,10),(12,9),(13,8),(14,7),(15,6),(16,5),(17,4),(18,3),(19,2),(20,1); 2241explain 2242select col1 f1, col2 f2, col1 f3 from t2 group by f1; 2243id select_type table type possible_keys key key_len ref rows Extra 22441 SIMPLE t2 range NULL idx 5 NULL 7 Using index for group-by 2245explain 2246select SQL_BIG_RESULT col1 f1, col2 f2, col1 f3 from t2 group by f1; 2247id select_type table type possible_keys key key_len ref rows Extra 22481 SIMPLE t2 range NULL idx 5 NULL 7 Using index for group-by 2249explain 2250select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2; 2251id select_type table type possible_keys key key_len ref rows Extra 22521 SIMPLE t2 index NULL idx 10 NULL 20 Using index 2253explain 2254select col1 f1, col1 f2 from t2 group by f1, 1+1; 2255id select_type table type possible_keys key key_len ref rows Extra 22561 SIMPLE t2 range NULL idx 5 NULL 7 Using index for group-by 2257explain 2258select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3+0; 2259id select_type table type possible_keys key key_len ref rows Extra 22601 SIMPLE t2 index NULL idx 10 NULL 20 Using index; Using temporary; Using filesort 2261select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3+0; 2262f1 f2 f3 22631 20 1 22642 19 2 22653 18 3 22664 17 4 22675 16 5 22686 15 6 22697 14 7 22708 13 8 22719 12 9 227210 11 10 227311 10 11 227412 9 12 227513 8 13 227614 7 14 227715 6 15 227816 5 16 227917 4 17 228018 3 18 228119 2 19 228220 1 20 2283explain 2284select col1 f1, col2 f2, col1 f3 from t2 order by f1,f2; 2285id select_type table type possible_keys key key_len ref rows Extra 22861 SIMPLE t2 index NULL idx 10 NULL 20 Using index 2287explain 2288select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3+0; 2289id select_type table type possible_keys key key_len ref rows Extra 22901 SIMPLE t2 index NULL idx 10 NULL 20 Using index; Using filesort 2291select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3+0; 2292f1 f2 f3 22931 20 1 22942 19 2 22953 18 3 22964 17 4 22975 16 5 22986 15 6 22997 14 7 23008 13 8 23019 12 9 230210 11 10 230311 10 11 230412 9 12 230513 8 13 230614 7 14 230715 6 15 230816 5 16 230917 4 17 231018 3 18 231119 2 19 231220 1 20 2313DROP VIEW v1; 2314DROP TABLE t1, t2; 2315# End of 5.1 tests 2316# 2317# LP bug#694450 Wrong result with non-standard GROUP BY + ORDER BY 2318# 2319SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY'; 2320CREATE TABLE t1 ( 2321f1 int(11), f2 int(11), f3 datetime, f4 varchar(1), PRIMARY KEY (f1)) ; 2322INSERT IGNORE INTO t1 VALUES ('1','9','2004-10-11 18:13','x'),('2','5','2004-03-07 14:02','g'),('3','1','2004-04-09 09:38','o'),('4','0','1900-01-01 00:00','g'),('5','1','2009-02-19 02:05','v'); 2323SELECT alias2.f3 AS field1 , alias2.f1 AS field2 FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f1 = alias1.f2 AND alias2.f1 != alias1.f4 GROUP BY field1 , field2 ORDER BY alias1.f2 , field2; 2324field1 field2 23252004-10-11 18:13:00 1 23262009-02-19 02:05:00 5 2327Warnings: 2328Warning 1292 Truncated incorrect DOUBLE value: 'g' 2329Warning 1292 Truncated incorrect DOUBLE value: 'o' 2330Warning 1292 Truncated incorrect DOUBLE value: 'v' 2331SELECT alias2.f3 AS field1 , alias2.f1 AS field2 FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f1 = alias1.f2 AND alias2.f1 != alias1.f4 GROUP BY field1 , field2 ; 2332field1 field2 23332004-10-11 18:13:00 1 23342009-02-19 02:05:00 5 2335Warnings: 2336Warning 1292 Truncated incorrect DOUBLE value: 'g' 2337Warning 1292 Truncated incorrect DOUBLE value: 'o' 2338Warning 1292 Truncated incorrect DOUBLE value: 'v' 2339SET SESSION SQL_MODE=default; 2340drop table t1; 2341# 2342# LP bug#967242 Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE 2343# 2344CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM; 2345INSERT INTO t1 VALUES ('x'); 2346CREATE TABLE t2 ( b INT, c VARCHAR(1), KEY (c, b) ) ENGINE=MyISAM; 2347INSERT INTO t2 VALUES 2348(4, 'd'),(8, 'g'),(3, 'x'),(3, 'f'), 2349(0, 'p'),(3, 'j'),(8, 'c'); 2350SELECT t2_1.b as zzz 2351FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2 2352ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c ) 2353WHERE 2354rand() + 1 > 0 OR 2355a = t2_1.c 2356GROUP BY zzz; 2357zzz 23580 23593 23604 23618 2362SELECT t2_1.b as zzz 2363FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2 2364ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c ) 2365WHERE 23661 > 0 OR 2367a = t2_1.c 2368GROUP BY zzz; 2369zzz 23700 23713 23724 23738 2374SELECT t2_1.b as zzz 2375FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2 2376ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c ) 2377WHERE 2378t2_1.b + 1 > 0 OR 2379a = t2_1.c 2380GROUP BY zzz; 2381zzz 23820 23833 23844 23858 2386SET @save_optimizer_switch967242=@@optimizer_switch; 2387SET optimizer_switch = 'in_to_exists=on'; 2388SELECT t2_1.b 2389FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2 2390ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c ) 2391WHERE 2392( SELECT COUNT(*) FROM t2 ) IS NOT NULL 2393OR a = t2_1.c 2394GROUP BY t2_1.b; 2395b 23960 23973 23984 23998 2400SET optimizer_switch=@save_optimizer_switch967242; 2401drop table t1, t2; 2402# 2403# Bug#12578908: SELECT SQL_BUFFER_RESULT OUTPUTS TOO MANY 2404# ROWS WHEN GROUP IS OPTIMIZED AWAY 2405# 2406CREATE TABLE t1 (col1 int, col2 int) ; 2407INSERT INTO t1 VALUES (10,1),(11,7); 2408CREATE TABLE t2 (col1 int, col2 int) ; 2409INSERT INTO t2 VALUES (10,8); 2410 2411EXPLAIN SELECT SQL_BUFFER_RESULT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2; 2412id select_type table type possible_keys key key_len ref rows Extra 24131 SIMPLE t2 system NULL NULL NULL NULL 1 Using temporary 24141 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 2415SELECT SQL_BUFFER_RESULT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2; 2416col2 24178 2418 2419EXPLAIN SELECT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2; 2420id select_type table type possible_keys key key_len ref rows Extra 24211 SIMPLE t2 system NULL NULL NULL NULL 1 24221 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 2423SELECT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2; 2424col2 24258 2426 2427DROP TABLE t1,t2; 2428# 2429# BUG#12640437: USING SQL_BUFFER_RESULT RESULTS IN A 2430# DIFFERENT QUERY OUTPUT 2431# 2432CREATE TABLE t1 ( 2433a int, 2434b varchar(1), 2435KEY (b,a) 2436); 2437INSERT INTO t1 VALUES 2438(1,NULL),(0,'a'),(1,NULL),(0,'a'), (1,'a'),(0,'a'),(2,'a'),(1,'a'); 2439ANALYZE TABLE t1; 2440Table Op Msg_type Msg_text 2441test.t1 analyze status Engine-independent statistics collected 2442test.t1 analyze status OK 2443 2444EXPLAIN SELECT SQL_BUFFER_RESULT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; 2445id select_type table type possible_keys key key_len ref rows Extra 24461 SIMPLE t1 range b b 9 NULL 2 Using where; Using index for group-by; Using temporary 2447 2448SELECT SQL_BUFFER_RESULT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; 2449MIN(a) b 24500 a 2451 2452EXPLAIN SELECT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; 2453id select_type table type possible_keys key key_len ref rows Extra 24541 SIMPLE t1 range b b 9 NULL 2 Using where; Using index for group-by 2455 2456SELECT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; 2457MIN(a) b 24580 a 2459 2460DROP TABLE t1; 2461create table t1 (a int, b int); 2462insert into t1 values (1,11), (1,12), (2,22),(2,23), (4,44),(4,45); 2463create table t2 (c int, d int); 2464insert into t2 values (1,11), (2,22), (4,44); 2465select distinct a,sum(b), (select d from t2 where c=a order by max(b) limit 1) from t1 group by a order by max(b); 2466a sum(b) (select d from t2 where c=a order by max(b) limit 1) 24671 23 11 24682 45 22 24694 89 44 2470drop table t1, t2; 2471# 2472# LP bug#993726 Wrong result from a query with ALL subquery predicate in WHERE 2473# 2474CREATE TABLE t1(a INT); 2475INSERT INTO t1 VALUES (0); 2476SELECT 1 FROM t1 WHERE 1 > ALL(SELECT 1 FROM t1 WHERE a!=0); 24771 24781 2479SELECT max(1) FROM t1 WHERE a!=0; 2480max(1) 2481NULL 2482drop table t1; 2483# End of 5.2 tests 2484# 2485# lp:872702: Crash in add_ref_to_table_cond() when grouping by a PK 2486# 2487CREATE TABLE t1 (a int, PRIMARY KEY (a)) ; 2488INSERT INTO t1 VALUES (14),(15),(16),(17),(18),(19),(20); 2489CREATE TABLE t2 (a int) ; 2490SELECT a 2491FROM t1 2492WHERE a = ( 2493SELECT t2.a 2494FROM t2 2495) OR t1.a = 73 2496GROUP BY 1; 2497a 2498DROP TABLE t1, t2; 2499FLUSH STATUS; 2500CREATE TABLE t1 (f1 INT, f2 decimal(20,1), f3 blob); 2501INSERT INTO t1 values(11,NULL,'blob'),(11,NULL,'blob'); 2502SELECT f3, MIN(f2) FROM t1 GROUP BY f1 LIMIT 1; 2503f3 MIN(f2) 2504blob NULL 2505DROP TABLE t1; 2506the value below *must* be 1 2507show status like 'Created_tmp_disk_tables'; 2508Variable_name Value 2509Created_tmp_disk_tables 1 2510# 2511# Bug #1002146: Unneeded filesort if usage of join buffer is not allowed 2512# (bug mdev-645) 2513# 2514CREATE TABLE t1 (pk int PRIMARY KEY, a int, INDEX idx(a)); 2515INSERT INTO t1 VALUES (3,2), (2,3), (5,3), (6,4); 2516CREATE TABLE t2 (pk int PRIMARY KEY, a int, INDEX idx(a)); 2517INSERT INTO t2 VALUES (9,0), (10,3), (6,4), (1,6), (3,100), (5,200); 2518set join_cache_level=0; 2519EXPLAIN 2520SELECT t2.a FROM t2 STRAIGHT_JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6 2521GROUP BY t2.a; 2522id select_type table type possible_keys key key_len ref rows Extra 25231 SIMPLE t2 range idx idx 5 NULL 5 Using where; Using index 25241 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index 2525SELECT t2.a FROM t2 STRAIGHT_JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6 2526GROUP BY t2.a; 2527a 25283 25294 2530100 2531200 2532set join_cache_level=default; 2533set @save_optimizer_switch=@@optimizer_switch; 2534set optimizer_switch='outer_join_with_cache=off'; 2535EXPLAIN 2536SELECT t2.a FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6 2537GROUP BY t2.a; 2538id select_type table type possible_keys key key_len ref rows Extra 25391 SIMPLE t2 range idx idx 5 NULL 5 Using where; Using index 25401 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using where; Using index 2541SELECT t2.a FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6 2542GROUP BY t2.a; 2543a 25440 25453 25464 2547100 2548200 2549set optimizer_switch=@save_optimizer_switch; 2550DROP TABLE t1,t2; 2551# 2552# MDEV-5104 crash in Item_field::used_tables with broken order by 2553# 2554(select 1 order by x(y)) order by 1; 2555ERROR 42S22: Unknown column 'y' in 'order clause' 2556# End of 5.3 tests 2557# 2558# Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00 2559# 2560CREATE TABLE t1 (f1 int, f2 DATE); 2561INSERT INTO t1 VALUES (1,'2004-04-19'), (1,'0000-00-00'), (1,'2004-04-18'), 2562(2,'2004-05-19'), (2,'0001-01-01'), (3,'2004-04-10'); 2563SELECT MIN(f2),MAX(f2) FROM t1; 2564MIN(f2) MAX(f2) 25650000-00-00 2004-05-19 2566SELECT f1,MIN(f2),MAX(f2) FROM t1 GROUP BY 1; 2567f1 MIN(f2) MAX(f2) 25681 0000-00-00 2004-04-19 25692 0001-01-01 2004-05-19 25703 2004-04-10 2004-04-10 2571DROP TABLE t1; 2572CREATE TABLE t1 ( f1 int, f2 time); 2573INSERT INTO t1 VALUES (1,'01:27:35'), (1,'06:11:01'), (2,'19:53:05'), 2574(2,'21:44:25'), (3,'10:55:12'), (3,'05:45:11'), (4,'00:25:00'); 2575SELECT MIN(f2),MAX(f2) FROM t1; 2576MIN(f2) MAX(f2) 257700:25:00 21:44:25 2578SELECT f1,MIN(f2),MAX(f2) FROM t1 GROUP BY 1; 2579f1 MIN(f2) MAX(f2) 25801 01:27:35 06:11:01 25812 19:53:05 21:44:25 25823 05:45:11 10:55:12 25834 00:25:00 00:25:00 2584DROP TABLE t1; 2585#End of test#49771 2586# 2587# Test of bug in GROUP_CONCAT with ROLLUP 2588# 2589CREATE TABLE t1 ( b VARCHAR(8) NOT NULL, a INT NOT NULL ) ENGINE=MyISAM; 2590INSERT INTO t1 (a,b) VALUES (1,'c'),(2,'v'); 2591CREATE TABLE t2 ( c VARCHAR(8), d INT, KEY (c, d) ) ENGINE=MyISAM; 2592INSERT INTO t2 VALUES ('v',6),('c',4),('v',3); 2593SELECT b, GROUP_CONCAT( a, b ORDER BY a, b ) 2594FROM t1 JOIN t2 ON c = b GROUP BY b; 2595b GROUP_CONCAT( a, b ORDER BY a, b ) 2596c 1c 2597v 2v,2v 2598SELECT b, GROUP_CONCAT( a, b ORDER BY a, b ) 2599FROM t1 JOIN t2 ON c = b GROUP BY b WITH ROLLUP; 2600b GROUP_CONCAT( a, b ORDER BY a, b ) 2601c 1c 2602v 2v,2v 2603NULL 1c,2v,2v 2604DROP TABLE t1,t2; 2605# 2606# MDEV-6129: Server crashes during UNION with ORDER BY field IS NULL 2607# 2608SET sql_mode='ONLY_FULL_GROUP_BY'; 2609SELECT 1 AS test UNION SELECT 2 AS test ORDER BY test IS NULL ASC; 2610test 26111 26122 2613SET sql_mode=''; 2614# 2615# MDEV-6484: Assertion `tab->ref.use_count' failed on query with joins, constant table, multi-part key 2616# 2617CREATE TABLE t1 (i1 INT, c1 VARCHAR(1)) ENGINE=MyISAM; 2618INSERT INTO t1 VALUES (6,'b'); 2619CREATE TABLE t2 (pk2 INT, i2 INT, c2 VARCHAR(1), PRIMARY KEY(pk2), KEY(pk2,i2)) ENGINE=MyISAM; 2620INSERT INTO t2 VALUES (1,2,'s'),(2,4,'r'),(3,8,'m'),(4,4,'b'),(5,4,'x'),(6,7,'g'),(7,4,'p'); 2621SELECT i2 FROM t1 AS t1a STRAIGHT_JOIN ( t2 INNER JOIN t1 AS t1b ON (t1b.c1 = c2) ) ON (t1b.i1 = pk2 ) 2622WHERE t1a.c1 = c2 GROUP BY i2; 2623i2 2624DROP TABLE t1,t2; 2625# 2626# MDEV-6855 2627# MIN(*) with subqueries with IS NOT NULL in WHERE clause crashed. 2628# 2629CREATE TABLE t1 (i INT, c VARCHAR(3), KEY(c,i)) ENGINE=MyISAM; 2630INSERT INTO t1 VALUES (7,'foo'),(0,'bar'); 2631CREATE TABLE t2 (j INT) ENGINE=MyISAM; 2632INSERT INTO t2 VALUES (0),(8),(1),(8),(9); 2633SELECT MAX(i), c FROM t1 2634WHERE c != 'qux' AND ( SELECT SUM(j) FROM t1, t2 ) IS NOT NULL GROUP BY c; 2635MAX(i) c 26360 bar 26377 foo 2638drop table t1,t2; 2639# 2640# ONLY_FULL_GROUP_BY references 2641# 2642set @save_sql_mode = @@sql_mode; 2643set sql_mode='ONLY_FULL_GROUP_BY'; 2644create table t1 (a int, b int); 2645select a+b as x from t1 group by x having x > 1; 2646x 2647select a as x from t1 group by x having x > 1; 2648x 2649select a from t1 group by a having a > 1; 2650a 2651drop table t1; 2652set sql_mode= @save_sql_mode; 2653create table t1 (f1 int); 2654insert into t1 values (5),(9); 2655create table t2 (f2 int); 2656insert into t2 values (0),(6); 2657create table t3 (f3 int); 2658insert into t3 values (6),(3); 2659create table t4 (f4 int); 2660insert into t4 values (1),(0); 2661select 2662(select min(f1) from t1 where f1 in (select min(f4) from t2)) as field7, 2663(select count(*) from t3 where f3 in (select max(f4) from t2 group by field7)) 2664from t4; 2665ERROR 42S22: Reference 'field7' not supported (reference to group function) 2666drop table t1, t2, t3, t4; 2667create table t1 (i1 int); 2668insert into t1 values (1); 2669create table t2 (i int); 2670insert into t2 values (2); 2671select 1 from t1 left join t2 b on b.i = (select max(b.i) from t2); 26721 26731 2674drop table t1, t2; 2675create table t1 (c1 int, c2 int); 2676create table t2 (c1 int, c2 int); 2677select t1.c1 as c1, t2.c2 as c1 from t1, t2 where t1.c1 < 20 and t2.c2 > 5 group by t1.c1, t2.c2 having t1.c1 < 3; 2678c1 c1 2679drop table t1, t2; 2680SET @old_sort_buff_size = @@sort_buffer_size; 2681SET @@sort_buffer_size=256*1024; 2682CREATE TABLE t1 (c INT) ENGINE=MyISAM; 2683INSERT INTO t1 VALUES 2684(2011),(1977),(1982),(2027),(2023),(NULL),(NULL),(2004),(1974),(2032), 2685(1993),(NULL),(1995),(2034),(NULL),(2009),(1900),(NULL),(2025),(1900), 2686(2033),(1900),(2012),(NULL),(2009),(1992),(1974),(1974),(2012),(2028), 2687(2007),(2012),(1900),(1983),(1900),(2010),(1987),(1994),(1981),(2032), 2688(2010),(1989),(2014),(1900),(1900),(1976),(1978),(2007),(2030),(NULL), 2689(2002),(1997),(1900),(NULL),(2000),(2027),(1975),(2026),(1975),(2026), 2690(2029),(1977),(1900),(1900),(2031),(1993),(1986),(2012),(1979),(2013), 2691(1994),(2014),(2025),(2006),(1971),(1974),(2021),(2011),(NULL),(1991), 2692(2001),(1977),(2023),(2012),(1900),(1978),(1998),(NULL),(1988),(1999), 2693(2017),(2008),(1976),(1900),(2005),(2030),(2023),(1900),(1978),(1990), 2694(1978),(1987),(2030),(1900),(2034),(2006),(2015),(2001),(2019),(2024), 2695(2030),(1989),(1997),(2007),(2023),(1994),(1971),(2011),(2011),(2015), 2696(1984),(1978),(1979),(1989),(2008),(2030); 2697SELECT ExtractValue('<a></a>','/a') AS f1, SPACE(c) AS f2 FROM t1 GROUP BY f1, f2 WITH ROLLUP; 2698f1 f2 2699 NULL 2700 2701 NULL 2702NULL NULL 2703SET @@sort_buffer_size = @old_sort_buff_size; 2704DROP TABLE t1; 2705# 2706# Bug #58782 2707# Missing rows with SELECT .. WHERE .. IN subquery 2708# with full GROUP BY and no aggr 2709# 2710CREATE TABLE t1 ( 2711pk INT NOT NULL, 2712col_int_nokey INT, 2713PRIMARY KEY (pk) 2714); 2715INSERT INTO t1 VALUES (10,7); 2716INSERT INTO t1 VALUES (11,1); 2717INSERT INTO t1 VALUES (12,5); 2718INSERT INTO t1 VALUES (13,3); 2719SELECT pk AS field1, col_int_nokey AS field2 2720FROM t1 2721WHERE col_int_nokey > 0 2722GROUP BY field1, field2; 2723field1 field2 272410 7 272511 1 272612 5 272713 3 2728CREATE TABLE where_subselect 2729SELECT pk AS field1, col_int_nokey AS field2 2730FROM t1 2731WHERE col_int_nokey > 0 2732GROUP BY field1, field2 2733; 2734SELECT * 2735FROM where_subselect 2736WHERE (field1, field2) IN ( 2737SELECT pk AS field1, col_int_nokey AS field2 2738FROM t1 2739WHERE col_int_nokey > 0 2740GROUP BY field1, field2 2741); 2742field1 field2 274310 7 274411 1 274512 5 274613 3 2747DROP TABLE t1; 2748DROP TABLE where_subselect; 2749# End of Bug #58782 2750# 2751# MDEV-8988: Apparently valid SQL query gives wrong result (nested WHERE) 2752# 2753create table t0(a int); 2754insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2755create table t1 (a int, b int, c int); 2756insert into t1 select A.a + 10*B.a, A.a, A.a + 10*B.a from t0 A, t0 B; 2757insert into t1 values (NULL, NULL, NULL); 2758create table t2 (c int, col1 int, key(c)); 2759insert into t2 select t1.a, 100000 from t1; 2760analyze table t2; 2761Table Op Msg_type Msg_text 2762test.t2 analyze status Engine-independent statistics collected 2763test.t2 analyze status Table is already up to date 2764explain 2765select 2766max(a)+ (select col1 from t2 where t2.c=t1.c) 2767from t1 2768group by t1.b; 2769id select_type table type possible_keys key key_len ref rows Extra 27701 PRIMARY t1 ALL NULL NULL NULL NULL 101 Using temporary; Using filesort 27712 DEPENDENT SUBQUERY t2 ref c c 5 func 1 2772select 2773max(a) + (select col1 from t2 where t2.c=t1.c) 2774from t1 2775group by t1.b; 2776max(a) + (select col1 from t2 where t2.c=t1.c) 2777NULL 2778100090 2779100091 2780100092 2781100093 2782100094 2783100095 2784100096 2785100097 2786100098 2787100099 2788drop table t0,t1,t2; 2789# 2790# MDEV-9602 crash in st_key::actual_rec_per_key when group by constant 2791# 2792create table t1 (a date not null,unique (a)) engine=innodb; 2793Warnings: 2794Warning 1286 Unknown storage engine 'innodb' 2795Warning 1266 Using storage engine MyISAM for table 't1' 2796select distinct a from t1 group by 'a'; 2797a 2798insert into t1 values("2001-02-02"),("2001-02-03"); 2799select distinct a from t1 group by 'a'; 2800a 28012001-02-02 2802drop table t1; 2803# 2804# MDEV-10324: Server crash in get_sel_arg_for_keypart or Assertion `n < size()' failed in Mem_root_array 2805# 2806CREATE TABLE t1 ( 2807job_id int(10) unsigned NOT NULL AUTO_INCREMENT, 2808job_cmd varbinary(60) NOT NULL DEFAULT '', 2809job_namespace int(11) NOT NULL, 2810job_title varbinary(255) NOT NULL, 2811job_params blob NOT NULL, 2812job_timestamp varbinary(14) DEFAULT NULL, 2813job_random int(10) unsigned NOT NULL DEFAULT '0', 2814job_token varbinary(32) NOT NULL DEFAULT '', 2815job_token_timestamp varbinary(14) DEFAULT NULL, 2816job_sha1 varbinary(32) NOT NULL DEFAULT '', 2817job_attempts int(10) unsigned NOT NULL DEFAULT '0', 2818PRIMARY KEY (job_id), 2819KEY job_cmd (job_cmd,job_namespace,job_title,job_params(128)), 2820KEY job_timestamp (job_timestamp), 2821KEY job_sha1 (job_sha1), 2822KEY job_cmd_token (job_cmd,job_token,job_random), 2823KEY job_cmd_token_id (job_cmd,job_token,job_id) 2824); 2825INSERT INTO t1 VALUES 2826(NULL, 'foo', 1, 'foo', 'foo', 'foo', 1, 'foo', 'foo', 'foo', 1), 2827(NULL, 'bar', 2, 'bar', 'bar', 'bar', 2, 'bar', 'bar', 'bar', 2); 2828SELECT DISTINCT job_cmd FROM t1 WHERE job_cmd IN ('foobar','null'); 2829job_cmd 2830drop table t1; 2831CREATE TABLE t1 (f1 INT NOT NULL, f2 VARCHAR(3) NOT NULL, KEY(f1), KEY(f2, f1)); 2832INSERT INTO t1 VALUES (0,'foo'),(1,'bar'); 2833SELECT 1 IN ( SELECT COUNT( DISTINCT f2 ) FROM t1 WHERE f1 <= 4 ); 28341 IN ( SELECT COUNT( DISTINCT f2 ) FROM t1 WHERE f1 <= 4 ) 28350 2836drop table t1; 2837# 2838# MDEV-20922: Adding an order by changes the query results 2839# 2840CREATE TABLE t1(a int, b int); 2841INSERT INTO t1 values (1, 100), (2, 200), (3, 100), (4, 200), (5, 200); 2842create view v1 as select a, b+1 as x from t1; 2843SELECT x, COUNT(DISTINCT a) AS y FROM v1 GROUP BY x ORDER BY y; 2844x y 2845101 2 2846201 3 2847SELECT b+1 AS x, COUNT(DISTINCT a) AS y FROM t1 GROUP BY x ORDER BY y; 2848x y 2849101 2 2850201 3 2851drop view v1; 2852drop table t1; 2853# 2854# MDEV-10694 - SIGFPE and/or huge memory allocation in maria_create with distinct/group by/ rollup 2855# 2856create table t1 (a int,b int) ; 2857insert into t1 values(-126,7),(1,1),(0,0),(-1,1),(351,65534); 2858select distinct 1 from t1 group by a,b with rollup limit 1; 28591 28601 2861drop table t1; 2862CREATE TABLE t1 ( pk int, i1 int, v1 varchar(1)); 2863explain 2864SELECT 1 FROM t1 2865GROUP BY REPEAT((BINARY pk), v1), AES_DECRYPT((@A := i1), 20852) WITH ROLLUP HAVING LOAD_FILE('a') ; 2866id select_type table type possible_keys key key_len ref rows Extra 28671 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING 2868SELECT 1 FROM t1 2869GROUP BY REPEAT((BINARY pk), v1), AES_DECRYPT((@A := i1), 20852) WITH ROLLUP HAVING LOAD_FILE('a') ; 28701 2871drop table t1; 2872# 2873# MDEV-22019: Sig 11 in next_breadth_first_tab | max_sort_length setting + double 2874# GROUP BY leads to crash 2875# 2876CALL mtr.add_suppression("Out of sort memory"); 2877CALL mtr.add_suppression("Sort aborted"); 2878SET @save_max_sort_length= @@max_sort_length; 2879SET max_sort_length=2000000; 2880SELECT * FROM information_schema.tables t JOIN information_schema.columns c 2881ON t.table_schema=c.table_schema 2882WHERE c.table_schema=(SELECT COUNT(*) FROM INFORMATION_SCHEMA.columns GROUP BY column_type) 2883GROUP BY t.table_name; 2884ERROR HY001: Out of sort memory, consider increasing server sort buffer size 2885SET max_sort_length= @save_max_sort_length; 2886# 2887# MDEV-23826: ORDER BY in view definition leads to wrong result with GROUP BY on query using view 2888# 2889CREATE TABLE t1 2890( 2891id INT PRIMARY KEY AUTO_INCREMENT, 2892dt datetime, 2893INDEX(dt), 2894foo int 2895); 2896INSERT INTO t1 VALUES (1,'2020-09-26 12:00:00',1); 2897INSERT INTO t1 VALUES (2,'2020-09-26 13:00:00',1); 2898INSERT INTO t1 VALUES (3,'2020-09-27 13:00:00',1); 2899INSERT INTO t1 VALUES (4,'2020-09-27 12:00:00',1); 2900INSERT INTO t1 VALUES (5,'2020-09-28 12:00:00',1); 2901INSERT INTO t1 VALUES (6,'2020-09-28 13:00:00',1); 2902INSERT INTO t1 VALUES (7,'2020-09-25 12:00:00',1); 2903INSERT INTO t1 VALUES (8,'2020-09-25 13:00:00',1); 2904INSERT INTO t1 VALUES (9,'2020-09-26 13:00:00',1); 2905CREATE VIEW v1 AS SELECT * FROM t1; 2906CREATE VIEW v2 AS SELECT * FROM t1 ORDER BY dt; 2907SELECT dt, sum(foo) AS foo FROM v1 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt; 2908dt foo 29092020-09-25 12:00:00 1 29102020-09-25 13:00:00 1 29112020-09-26 12:00:00 1 29122020-09-26 13:00:00 2 29132020-09-27 12:00:00 1 29142020-09-27 13:00:00 1 29152020-09-28 12:00:00 1 29162020-09-28 13:00:00 1 2917SELECT dt, sum(foo) AS foo FROM v2 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt; 2918dt foo 29192020-09-25 12:00:00 1 29202020-09-25 13:00:00 1 29212020-09-26 12:00:00 1 29222020-09-26 13:00:00 2 29232020-09-27 12:00:00 1 29242020-09-27 13:00:00 1 29252020-09-28 12:00:00 1 29262020-09-28 13:00:00 1 2927DROP TABLE t1; 2928DROP VIEW v1,v2; 2929# 2930# End of 10.2 tests 2931# 2932# 2933# MDEV-16170 2934# Server crashes in Item_null_result::type_handler on SELECT with ROLLUP 2935# 2936CREATE TABLE t1 (d DATE); 2937INSERT INTO t1 VALUES ('2032-10-08'); 2938SELECT d != '2023-03-04' AS f, COUNT(*) FROM t1 GROUP BY d WITH ROLLUP; 2939f COUNT(*) 29401 1 2941NULL 1 2942DROP TABLE t1; 2943# 2944# MDEV-24710 Uninitialized value upon CREATE .. SELECT ... VALUE 2945# 2946CREATE TABLE t1 (a VARCHAR(8) NOT NULL DEFAULT ''); 2947INSERT INTO t1 (a) VALUES ('foo'); 2948CREATE TABLE t2 AS SELECT MAX(a) AS f1, a AS f2 FROM t1 WHERE VALUE(a) IS NOT NULL; 2949SELECT * from t2; 2950f1 f2 2951NULL NULL 2952SELECT MAX(a) AS f1, a AS f2 FROM t1 WHERE VALUE(a) IS NOT NULL; 2953f1 f2 2954NULL NULL 2955SELECT MAX(a) AS f1, a AS f2 FROM t1 WHERE 1=0; 2956f1 f2 2957NULL NULL 2958drop table t1,t2; 2959# Extra test by to check the fix for MDEV-24710 2960create table t20 (pk int primary key, a int); 2961insert into t20 values (1,1); 2962create table t21 (pk int primary key, b int not null); 2963insert into t21 values (1,1); 2964create table t22 (a int); 2965insert into t22 values (1),(2); 2966select a, (select max(t21.b) from t20 left join t21 on t21.pk=t20.a+10 2967where t20.pk=1 and rand(123) < 0.5) as SUBQ from t22; 2968a SUBQ 29691 NULL 29702 NULL 2971drop table t20, t21, t22; 2972# 2973# End of 10.3 tests 2974# 2975