1# Initialise 2--disable_warnings 3drop table if exists t1,t2,t3; 4--enable_warnings 5 6# 7# Simple test without tables 8 9-- error 1111 10SELECT 1 FROM (SELECT 1) as a GROUP BY SUM(1); 11 12# 13# Test of group (Failed for Lars Hoss <lh@pbm.de>) 14# 15 16CREATE TABLE t1 ( 17 spID int(10) unsigned, 18 userID int(10) unsigned, 19 score smallint(5) unsigned, 20 lsg char(40), 21 date date 22); 23 24INSERT INTO t1 VALUES (1,1,1,'','0000-00-00'); 25INSERT INTO t1 VALUES (2,2,2,'','0000-00-00'); 26INSERT INTO t1 VALUES (2,1,1,'','0000-00-00'); 27INSERT INTO t1 VALUES (3,3,3,'','0000-00-00'); 28 29CREATE TABLE t2 ( 30 userID int(10) unsigned NOT NULL auto_increment, 31 niName char(15), 32 passwd char(8), 33 mail char(50), 34 isAukt enum('N','Y') DEFAULT 'N', 35 vName char(30), 36 nName char(40), 37 adr char(60), 38 plz char(5), 39 ort char(35), 40 land char(20), 41 PRIMARY KEY (userID) 42); 43 44INSERT INTO t2 VALUES (1,'name','pass','mail','Y','v','n','adr','1','1','1'); 45INSERT INTO t2 VALUES (2,'name','pass','mail','Y','v','n','adr','1','1','1'); 46INSERT INTO t2 VALUES (3,'name','pass','mail','Y','v','n','adr','1','1','1'); 47INSERT INTO t2 VALUES (4,'name','pass','mail','Y','v','n','adr','1','1','1'); 48INSERT INTO t2 VALUES (5,'name','pass','mail','Y','v','n','adr','1','1','1'); 49 50SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid; 51SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid ORDER BY NULL; 52SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid; 53SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid; 54SELECT 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; 55EXPLAIN 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; 56drop table t1,t2; 57 58# 59# Bug in GROUP BY, by Nikki Chumakov <nikki@saddam.cityline.ru> 60# 61 62CREATE TABLE t1 ( 63 PID int(10) unsigned NOT NULL auto_increment, 64 payDate date DEFAULT '0000-00-00' NOT NULL, 65 recDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, 66 URID int(10) unsigned DEFAULT '0' NOT NULL, 67 CRID int(10) unsigned DEFAULT '0' NOT NULL, 68 amount int(10) unsigned DEFAULT '0' NOT NULL, 69 operator int(10) unsigned, 70 method enum('unknown','cash','dealer','check','card','lazy','delayed','test') DEFAULT 'unknown' NOT NULL, 71 DIID int(10) unsigned, 72 reason char(1) binary DEFAULT '' NOT NULL, 73 code_id int(10) unsigned, 74 qty mediumint(8) unsigned DEFAULT '0' NOT NULL, 75 PRIMARY KEY (PID), 76 KEY URID (URID), 77 KEY reason (reason), 78 KEY method (method), 79 KEY payDate (payDate) 80); 81 82INSERT INTO t1 VALUES (1,'1970-01-01','1997-10-17 00:00:00',2529,1,21000,11886,'check',0,'F',16200,6); 83 84--error 1056 85SELECT 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; 86 87drop table t1; 88 89# 90# Problem with GROUP BY + ORDER BY when no match 91# Tested with locking 92# 93 94CREATE TABLE t1 ( 95 cid mediumint(9) NOT NULL auto_increment, 96 firstname varchar(32) DEFAULT '' NOT NULL, 97 surname varchar(32) DEFAULT '' NOT NULL, 98 PRIMARY KEY (cid) 99); 100INSERT INTO t1 VALUES (1,'That','Guy'); 101INSERT INTO t1 VALUES (2,'Another','Gent'); 102 103CREATE TABLE t2 ( 104 call_id mediumint(8) NOT NULL auto_increment, 105 contact_id mediumint(8) DEFAULT '0' NOT NULL, 106 PRIMARY KEY (call_id), 107 KEY contact_id (contact_id) 108); 109 110lock tables t1 read,t2 write; 111 112INSERT INTO t2 VALUES (10,2); 113INSERT INTO t2 VALUES (18,2); 114INSERT INTO t2 VALUES (62,2); 115INSERT INTO t2 VALUES (91,2); 116INSERT INTO t2 VALUES (92,2); 117 118SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid; 119SELECT 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; 120SELECT 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; 121 122drop table t2; 123unlock tables; 124drop table t1; 125 126# 127# Test of group by bug in bugzilla 128# 129 130CREATE TABLE t1 ( 131 bug_id mediumint(9) NOT NULL auto_increment, 132 groupset bigint(20) DEFAULT '0' NOT NULL, 133 assigned_to mediumint(9) DEFAULT '0' NOT NULL, 134 bug_file_loc text, 135 bug_severity enum('blocker','critical','major','normal','minor','trivial','enhancement') DEFAULT 'blocker' NOT NULL, 136 bug_status enum('','NEW','ASSIGNED','REOPENED','RESOLVED','VERIFIED','CLOSED') DEFAULT 'NEW' NOT NULL, 137 creation_ts datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, 138 delta_ts timestamp, 139 short_desc mediumtext, 140 long_desc mediumtext, 141 op_sys enum('All','Windows 3.1','Windows 95','Windows 98','Windows NT','Windows 2000','Linux','other') DEFAULT 'All' NOT NULL, 142 priority enum('P1','P2','P3','P4','P5') DEFAULT 'P1' NOT NULL, 143 product varchar(64) DEFAULT '' NOT NULL, 144 rep_platform enum('All','PC','VTD-8','Other'), 145 reporter mediumint(9) DEFAULT '0' NOT NULL, 146 version varchar(16) DEFAULT '' NOT NULL, 147 component varchar(50) DEFAULT '' NOT NULL, 148 resolution enum('','FIXED','INVALID','WONTFIX','LATER','REMIND','DUPLICATE','WORKSFORME') DEFAULT '' NOT NULL, 149 target_milestone varchar(20) DEFAULT '' NOT NULL, 150 qa_contact mediumint(9) DEFAULT '0' NOT NULL, 151 status_whiteboard mediumtext NOT NULL, 152 votes mediumint(9) DEFAULT '0' NOT NULL, 153 PRIMARY KEY (bug_id), 154 KEY assigned_to (assigned_to), 155 KEY creation_ts (creation_ts), 156 KEY delta_ts (delta_ts), 157 KEY bug_severity (bug_severity), 158 KEY bug_status (bug_status), 159 KEY op_sys (op_sys), 160 KEY priority (priority), 161 KEY product (product), 162 KEY reporter (reporter), 163 KEY version (version), 164 KEY component (component), 165 KEY resolution (resolution), 166 KEY target_milestone (target_milestone), 167 KEY qa_contact (qa_contact), 168 KEY votes (votes) 169); 170 171INSERT INTO t1 VALUES (1,0,0,'','normal','','2000-02-10 09:25:12',20000321114747,'','','Linux','P1','TestProduct','PC',3,'other','TestComponent','','M1',0,'',0); 172INSERT 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); 173INSERT 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); 174INSERT 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); 175INSERT 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); 176INSERT 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); 177INSERT 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); 178INSERT INTO t1 VALUES (4,0,0,'','normal','','2000-03-08 18:32:14',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent2','','',0,'',0); 179INSERT INTO t1 VALUES (3,0,0,'','normal','','2000-03-08 18:30:52',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent','','',0,'',0); 180INSERT INTO t1 VALUES (2,0,0,'','enhancement','','2000-03-08 18:24:51',20000321114747,'','','All','P2','TestProduct','Other',4,'other','TestComponent2','','',0,'',0); 181INSERT 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); 182INSERT 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); 183INSERT INTO t1 VALUES (13,0,0,'','normal','','2000-03-15 16:20:44',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent','','',0,'',0); 184INSERT 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); 185INSERT 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); 186INSERT 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); 187INSERT 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); 188INSERT 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); 189INSERT 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); 190INSERT 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); 191INSERT 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); 192INSERT 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); 193INSERT 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); 194INSERT 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); 195INSERT 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); 196INSERT 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); 197INSERT 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); 198INSERT INTO t1 VALUES (28,0,0,'','normal','','2000-03-20 09:51:45',20000321114747,'','','Windows NT','P2','TestProduct','PC',8,'other','TestComponent','','',0,'',0); 199INSERT 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); 200CREATE TABLE t2 ( 201 value tinytext, 202 program varchar(64), 203 initialowner tinytext NOT NULL, 204 initialqacontact tinytext NOT NULL, 205 description mediumtext NOT NULL 206); 207 208INSERT INTO t2 VALUES ('TestComponent','TestProduct','id0001','',''); 209INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - conversion','AAAAA','id0001','',''); 210INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - generic','AAAAA','id0001','',''); 211INSERT INTO t2 VALUES ('TestComponent2','TestProduct','id0001','',''); 212INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - eeeeeeeee','AAAAA','id0001','',''); 213INSERT INTO t2 VALUES ('kkkkkkkkkkk lllllllllll','AAAAA','id0001','',''); 214INSERT INTO t2 VALUES ('Test Procedures','AAAAA','id0001','',''); 215INSERT INTO t2 VALUES ('Documentation','AAAAA','id0003','',''); 216INSERT INTO t2 VALUES ('DDDDDDDDD','CCC/CCCCCC','id0002','',''); 217INSERT INTO t2 VALUES ('Eeeeeeee Lite','CCC/CCCCCC','id0002','',''); 218INSERT INTO t2 VALUES ('Eeeeeeee Full','CCC/CCCCCC','id0002','',''); 219INSERT INTO t2 VALUES ('Administration','CCC/CCCCCC','id0002','',''); 220INSERT INTO t2 VALUES ('Distribution','CCC/CCCCCC','id0002','',''); 221INSERT INTO t2 VALUES ('Setup','CCC/CCCCCC','id0002','',''); 222INSERT INTO t2 VALUES ('Unspecified','CCC/CCCCCC','id0002','',''); 223INSERT INTO t2 VALUES ('Web Interface','AAAAAAAA-AAA','id0001','',''); 224INSERT INTO t2 VALUES ('Host communication','AAAAA','id0001','',''); 225select value,description,bug_id from t2 left join t1 on t2.program=t1.product and t2.value=t1.component where program="AAAAA"; 226select 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; 227select 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); 228 229drop table t1,t2; 230 231# 232# Problem with functions and group functions when no matching rows 233# 234 235create table t1 (foo int); 236insert into t1 values (1); 237select 1+1, "a",count(*) from t1 where foo in (2); 238insert into t1 values (1); 239select 1+1,"a",count(*) from t1 where foo in (2); 240drop table t1; 241 242# 243# Test GROUP BY DESC 244 245CREATE TABLE t1 ( 246 spID int(10) unsigned, 247 userID int(10) unsigned, 248 score smallint(5) unsigned, 249 key (spid), 250 key (score) 251); 252 253INSERT 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); 254explain select userid,count(*) from t1 group by userid desc; 255explain select userid,count(*) from t1 group by userid desc order by null; 256select userid,count(*) from t1 group by userid desc; 257select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3); 258select userid,count(*) from t1 group by userid desc having 3 IN (1,COUNT(*)); 259explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc; 260explain select spid,count(*) from t1 where spid between 1 and 2 group by spid; 261explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null; 262select spid,count(*) from t1 where spid between 1 and 2 group by spid; 263select spid,count(*) from t1 where spid between 1 and 2 group by spid desc; 264explain extended select sql_big_result spid,sum(userid) from t1 group by spid desc; 265explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null; 266select sql_big_result spid,sum(userid) from t1 group by spid desc; 267explain select sql_big_result score,count(*) from t1 group by score desc; 268explain select sql_big_result score,count(*) from t1 group by score desc order by null; 269select sql_big_result score,count(*) from t1 group by score desc; 270drop table t1; 271 272# not purely group_by bug, but group_by is involved... 273 274create table t1 (a date default null, b date default null); 275insert t1 values ('1999-10-01','2000-01-10'), ('1997-01-01','1998-10-01'); 276select a,min(b) c,count(distinct rand()) from t1 group by a having c<a + interval 1 day; 277drop table t1; 278 279# Compare with hash keys 280 281CREATE TABLE t1 (a char(1)); 282INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL); 283flush status; 284SELECT a FROM t1 GROUP BY a; 285SELECT a,count(*) FROM t1 GROUP BY a; 286SELECT a FROM t1 GROUP BY binary a; 287SELECT a,count(*) FROM t1 GROUP BY binary a; 288SELECT binary a FROM t1 GROUP BY 1; 289SELECT binary a,count(*) FROM t1 GROUP BY 1; 290--disable_ps_protocol 291show status like 'Created%tables'; 292--enable_ps_protocol 293# Do the same tests with on-disk temporary tables 294set tmp_memory_table_size=0; 295SELECT a FROM t1 GROUP BY a; 296SELECT a,count(*) FROM t1 GROUP BY a; 297SELECT a FROM t1 GROUP BY binary a; 298SELECT a,count(*) FROM t1 GROUP BY binary a; 299SELECT binary a FROM t1 GROUP BY 1; 300SELECT binary a,count(*) FROM t1 GROUP BY 1; 301--disable_ps_protocol 302show status like 'Created%tables'; 303--enable_ps_protocol 304set tmp_memory_table_size=default; 305drop table t1; 306 307# 308# Test of key >= 256 bytes 309# 310 311CREATE TABLE t1 ( 312 `a` char(193) default NULL, 313 `b` char(63) default NULL 314); 315INSERT INTO t1 VALUES ('abc','def'),('hij','klm'); 316SELECT CONCAT(a, b) FROM t1 GROUP BY 1; 317SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1; 318SELECT CONCAT(a, b),count(distinct a) FROM t1 GROUP BY 1; 319SELECT 1 FROM t1 GROUP BY CONCAT(a, b); 320INSERT INTO t1 values ('hij','klm'); 321SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1; 322DROP TABLE t1; 323 324# 325# Test problem with ORDER BY on a SUM() column 326# 327 328create table t1 (One int unsigned, Two int unsigned, Three int unsigned, Four int unsigned); 329insert 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); 330select One, Two, sum(Four) from t1 group by One,Two; 331drop table t1; 332 333create table t1 (id integer primary key not null auto_increment, gender char(1)); 334insert into t1 values (NULL, 'M'), (NULL, 'F'),(NULL, 'F'),(NULL, 'F'),(NULL, 'M'); 335create table t2 (user_id integer not null, date date); 336insert 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'); 337select 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; 338select 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; 339drop table t1,t2; 340 341# 342# The GROUP BY returned rows in wrong order in 3.23.51 343# 344 345CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID 346)); 347insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL); 348SET @save_optimizer_switch=@@optimizer_switch; 349SET optimizer_switch='outer_join_with_cache=off'; 350select 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; 351select 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; 352SET optimizer_switch=@save_optimizer_switch; 353drop table t1; 354 355# 356# Problem with MAX and LEFT JOIN 357# 358 359CREATE TABLE t1 ( 360 pid int(11) unsigned NOT NULL default '0', 361 c1id int(11) unsigned default NULL, 362 c2id int(11) unsigned default NULL, 363 value int(11) unsigned NOT NULL default '0', 364 UNIQUE KEY pid2 (pid,c1id,c2id), 365 UNIQUE KEY pid (pid,value) 366) ENGINE=MyISAM; 367 368INSERT INTO t1 VALUES (1, 1, NULL, 1),(1, 2, NULL, 2),(1, NULL, 3, 3),(1, 4, NULL, 4),(1, 5, NULL, 5); 369 370CREATE TABLE t2 ( 371 id int(11) unsigned NOT NULL default '0', 372 active enum('Yes','No') NOT NULL default 'Yes', 373 PRIMARY KEY (id) 374) ENGINE=MyISAM; 375 376INSERT INTO t2 VALUES (1, 'Yes'),(2, 'No'),(4, 'Yes'),(5, 'No'); 377 378CREATE TABLE t3 ( 379 id int(11) unsigned NOT NULL default '0', 380 active enum('Yes','No') NOT NULL default 'Yes', 381 PRIMARY KEY (id) 382); 383INSERT INTO t3 VALUES (3, 'Yes'); 384 385select * from t1 AS m LEFT JOIN t2 AS c1 ON m.c1id = 386c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = c2.id AND 387c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS NOT NULL); 388select max(value) from t1 AS m LEFT JOIN t2 AS c1 ON 389m.c1id = c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = 390c2.id AND c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS 391NOT NULL); 392drop table t1,t2,t3; 393 394# 395# Test bug in GROUP BY on BLOB that is NULL or empty 396# 397 398create table t1 (a blob null); 399insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(""),(""),(""),("b"); 400select a,count(*) from t1 group by a; 401set tmp_memory_table_size=0; 402select a,count(*) from t1 group by a; 403drop table t1; 404set tmp_memory_table_size=default; 405 406# 407# Test of GROUP BY ... ORDER BY NULL optimization 408# 409 410SET @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity,@save_optimizer_switch=@@optimizer_switch; 411SET optimizer_switch='outer_join_with_cache=off',@@optimizer_use_condition_selectivity=4; 412create table t1 (a int not null, b int not null); 413insert into t1 values (1,1),(1,2),(3,1),(3,2),(2,2),(2,1); 414create table t2 (a int not null, b int not null, key(a)); 415insert into t2 values (1,3),(3,1),(2,2),(1,1); 416select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b; 417--sorted_result 418select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL; 419explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b; 420explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL; 421drop table t1,t2; 422SET @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity,@@optimizer_switch=@save_optimizer_switch; 423 424 425# 426# group function arguments in some functions 427# 428 429create table t1 (a int, b int); 430insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4); 431select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a; 432select a, MAX(b), CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end from t1 group by a; 433select a, MAX(b), FIELD(MAX(b), '43', '4', '5') from t1 group by a; 434select a, MAX(b), CONCAT_WS(MAX(b), '43', '4', '5') from t1 group by a; 435select a, MAX(b), ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f') from t1 group by a; 436select a, MAX(b), MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') from t1 group by a; 437drop table t1; 438 439# 440# Problem with group by and alias 441# 442 443create table t1 (id int not null, qty int not null); 444insert into t1 values (1,2),(1,3),(2,4),(2,5); 445select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and cqty>1; 446select id, sum(qty) as sqty from t1 group by id having sqty>2 and count(qty)>1; 447select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sqty>2 and cqty>1; 448select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and count(qty)>1; 449select 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; 450select count(*), interval(qty,2,3,4,5,6,7,8) as category from t1 group by category; 451drop table t1; 452# 453# Tests for bug #1355: 'Using filesort' is missing in EXPLAIN when ORDER BY 454# NULL is used. 455# 456CREATE TABLE t1 ( 457 userid int(10) unsigned, 458 score smallint(5) unsigned, 459 key (score) 460); 461INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3); 462# Here we select unordered GROUP BY into a temporary talbe, 463# and then sort it with filesort (GROUP BY in MySQL 464# implies sorted order of results) 465SELECT userid,count(*) FROM t1 GROUP BY userid DESC; 466EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC; 467DROP TABLE t1; 468CREATE TABLE t1 ( 469 i int(11) default NULL, 470 j int(11) default NULL 471); 472INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5); 473SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL; 474explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL; 475DROP TABLE t1; 476 477#Test for BUG#6976: Aggregate functions have incorrect NULL-ness 478create table t1 (a int); 479insert into t1 values(null); 480select min(a) is null from t1; 481select min(a) is null or null from t1; 482select 1 and min(a) is null from t1; 483drop table t1; 484 485# Test for BUG#5400: GROUP_CONCAT returns everything twice. 486create table t1 ( col1 int, col2 int ); 487insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2); 488select group_concat( distinct col1 ) as alias from t1 489 group by col2 having alias like '%'; 490 491drop table t1; 492 493# 494# Test BUG#8216 when referring in HAVING to n alias which is rand() function 495# 496 497create table t1 (a integer, b integer, c integer); 498insert into t1 (a,b) values (1,2),(1,3),(2,5); 499select a, 0.1*0+1 r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2=1; 500# rand(100)*10 will be < 2 only for the first row (of 6) 501select a, round(rand(100)*10) r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2<=2; 502select a, round(rand(100)*10) r2, sum(1) r1 from t1 group by a having r1>1 and r2<=2; 503select a,sum(b) from t1 where a=1 group by c; 504select a*sum(b) from t1 where a=1 group by c; 505select a*sum(b) as f1 from t1 where a=1 group by c having f1 <= 10; 506select a,a*sum(b) as f1 from t1 where a=1 group by c having a*sum(b)+0 <= 10; 507select sum(a)*sum(b) from t1 where a=1 group by c; 508select a,sum(b) from t1 where a=1 group by c having a=1; 509select a as d,sum(b) from t1 where a=1 group by c having d=1; 510select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0; 511drop table t1; 512 513# Test for BUG#9213 GROUP BY query on utf-8 key returns wrong results 514create table t1(a int); 515insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9); 516create table t2 ( 517 a int, 518 b varchar(200) NOT NULL, 519 c varchar(50) NOT NULL, 520 d varchar(100) NOT NULL, 521 primary key (a,b(132),c,d), 522 key a (a,b) 523) charset=utf8; 524 525insert into t2 select 526 x3.a, -- 3 527 concat('val-', x3.a + 3*x4.a), -- 12 528 concat('val-', @a:=x3.a + 3*x4.a + 12*C.a), -- 120 529 concat('val-', @a + 120*D.a) 530from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4; 531 532delete from t2 where a = 2 and b = 'val-2' order by a,b,c,d limit 30; 533 534explain select c from t2 where a = 2 and b = 'val-2' group by c; 535select c from t2 where a = 2 and b = 'val-2' group by c; 536drop table t1,t2; 537 538# Test for BUG#9298 "Wrong handling of int4 unsigned columns in GROUP functions" 539# (the actual problem was with protocol code, not GROUP BY) 540create table t1 (b int4 unsigned not null); 541insert into t1 values(3000000000); 542select * from t1; 543select min(b) from t1; 544drop table t1; 545 546# 547# Test for bug #11088: GROUP BY a BLOB column with COUNT(DISTINCT column1) 548# 549 550CREATE TABLE t1 (id int PRIMARY KEY, user_id int, hostname longtext); 551 552INSERT INTO t1 VALUES 553 (1, 7, 'cache-dtc-af05.proxy.aol.com'), 554 (2, 3, 'what.ever.com'), 555 (3, 7, 'cache-dtc-af05.proxy.aol.com'), 556 (4, 7, 'cache-dtc-af05.proxy.aol.com'); 557 558SELECT hostname, COUNT(DISTINCT user_id) as no FROM t1 559 WHERE hostname LIKE '%aol%' 560 GROUP BY hostname; 561 562DROP TABLE t1; 563 564# 565# Test for bug #8614: GROUP BY 'const' with DISTINCT 566# 567 568CREATE TABLE t1 (a int, b int); 569INSERT INTO t1 VALUES (1,2), (1,3); 570SELECT a, b FROM t1 GROUP BY 'const'; 571SELECT DISTINCT a, b FROM t1 GROUP BY 'const'; 572 573DROP TABLE t1; 574 575# 576# Test for bug #11385: GROUP BY for datetime converted to decimals 577# 578 579CREATE TABLE t1 (id INT, dt DATETIME); 580INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' ); 581INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' ); 582INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' ); 583INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' ); 584SELECT dt DIV 1 AS f, id FROM t1 GROUP BY f; 585 586DROP TABLE t1; 587 588# 589# Test for bug #11295: GROUP BY a BLOB column with COUNT(DISTINCT column1) 590# when the BLOB column takes NULL values 591# 592 593CREATE TABLE t1 (id varchar(20) NOT NULL); 594INSERT INTO t1 VALUES ('trans1'), ('trans2'); 595CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL); 596INSERT INTO t2 VALUES ('trans1', 'a problem'); 597SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment 598 FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment; 599 600DROP TABLE t1, t2; 601 602# 603# Bug #12266 GROUP BY expression on DATE column produces result with 604# reduced length 605# 606create table t1 (f1 date); 607insert into t1 values('2005-06-06'); 608insert into t1 values('2005-06-06'); 609select date(left(f1+0,8)) from t1 group by 1; 610drop table t1; 611 612# 613# Test for bug #11414: crash on Windows for a simple GROUP BY query 614# 615 616CREATE TABLE t1 (n int); 617INSERT INTO t1 VALUES (1); 618SELECT n+1 AS n FROM t1 GROUP BY n; 619DROP TABLE t1; 620 621# 622# BUG#12695: Item_func_isnull::update_used_tables 623# did not update const_item_cache 624# 625create table t1(f1 varchar(5) key); 626insert into t1 values (1),(2); 627select sql_buffer_result max(f1) is null from t1; 628select sql_buffer_result max(f1)+1 from t1; 629drop table t1; 630 631# 632# BUG#14019-4.1-opt 633# 634CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1),(2); 635 636SELECT a FROM t1 GROUP BY 'a'; 637SELECT a FROM t1 GROUP BY "a"; 638SELECT a FROM t1 GROUP BY `a`; 639 640set sql_mode=ANSI_QUOTES; 641SELECT a FROM t1 GROUP BY "a"; 642SELECT a FROM t1 GROUP BY 'a'; 643SELECT a FROM t1 GROUP BY `a`; 644set sql_mode=''; 645 646SELECT a FROM t1 HAVING 'a' > 1; 647SELECT a FROM t1 HAVING "a" > 1; 648SELECT a FROM t1 HAVING `a` > 1; 649 650SELECT a FROM t1 ORDER BY 'a' DESC; 651SELECT a FROM t1 ORDER BY "a" DESC; 652SELECT a FROM t1 ORDER BY `a` DESC; 653DROP TABLE t1; 654 655# 656# Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself 657# returns empty 658# 659CREATE TABLE t1 ( 660 f1 int(10) unsigned NOT NULL auto_increment primary key, 661 f2 varchar(100) NOT NULL default '' 662); 663CREATE TABLE t2 ( 664 f1 varchar(10) NOT NULL default '', 665 f2 char(3) NOT NULL default '', 666 PRIMARY KEY (`f1`), 667 KEY `k1` (`f2`,`f1`) 668); 669 670INSERT INTO t1 values(NULL, ''); 671INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT'); 672SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1; 673SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1; 674DROP TABLE t1, t2; 675 676 677# End of 4.1 tests 678 679# 680# Bug#11211: Ambiguous column reference in GROUP BY. 681# 682 683create table t1 (c1 char(3), c2 char(3)); 684create table t2 (c3 char(3), c4 char(3)); 685insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2'); 686insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2'); 687 688# query with ambiguous column reference 'c2' 689select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4 690group by c2; 691show warnings; 692 693# this query has no ambiguity 694select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4 695group by t1.c1; 696 697show warnings; 698drop table t1, t2; 699 700# 701# Bug #20466: a view is mixing data when there's a trigger on the table 702# 703CREATE TABLE t1 (a tinyint(3), b varchar(255), PRIMARY KEY (a)); 704 705INSERT INTO t1 VALUES (1,'-----'), (6,'Allemagne'), (17,'Autriche'), 706 (25,'Belgique'), (54,'Danemark'), (62,'Espagne'), (68,'France'); 707 708CREATE TABLE t2 (a tinyint(3), b tinyint(3), PRIMARY KEY (a), KEY b (b)); 709 710INSERT INTO t2 VALUES (1,1), (2,1), (6,6), (18,17), (15,25), (16,25), 711 (17,25), (10,54), (5,62),(3,68); 712 713CREATE VIEW v1 AS select t1.a, concat(t1.b,'') AS b, t1.b as real_b from t1; 714 715explain 716SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1 717where t2.b=v1.a GROUP BY t2.b; 718SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1 719where t2.b=v1.a GROUP BY t2.b; 720 721DROP VIEW v1; 722DROP TABLE t1,t2; 723 724# 725# Bug#22781: SQL_BIG_RESULT fails to influence sort plan 726# 727CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b)); 728 729INSERT INTO t1 VALUES (1, 1); 730INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1; 731INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1; 732INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1; 733INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1; 734INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1; 735INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1; 736INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1; 737 738SELECT MIN(b), MAX(b) from t1; 739 740EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b; 741EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b; 742SELECT b, sum(1) FROM t1 GROUP BY b; 743SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b; 744DROP TABLE t1; 745 746# 747# Bug #23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode 748# 749CREATE TABLE t1 (a INT PRIMARY KEY, b INT); 750INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3); 751 752SET SQL_MODE = 'ONLY_FULL_GROUP_BY'; 753SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b; 754SELECT CEILING(MIN(a)) FROM t1 GROUP BY b; 755SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1 756 GROUP BY b; 757SELECT a + 1 FROM t1 GROUP BY a; 758--error ER_WRONG_FIELD_WITH_GROUP 759SELECT a + b FROM t1 GROUP BY b; 760SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) 761 FROM t1 AS t1_outer; 762SELECT 1 FROM t1 as t1_outer GROUP BY a 763 HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1); 764--error ER_WRONG_FIELD_WITH_GROUP 765SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1) 766 FROM t1 AS t1_outer GROUP BY t1_outer.b; 767--error ER_BAD_FIELD_ERROR 768SELECT 1 FROM t1 as t1_outer GROUP BY a 769 HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1); 770SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1) 771 FROM t1 AS t1_outer GROUP BY t1_outer.b; 772SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1) 773 FROM t1 AS t1_outer; 774--error ER_WRONG_FIELD_WITH_GROUP 775SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1) 776 FROM t1 AS t1_outer GROUP BY t1_outer.b; 777 778SELECT 1 FROM t1 as t1_outer 779 WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1); 780 781SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0; 782 783SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12; 784SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1); 785 786--error ER_BAD_FIELD_ERROR 787SELECT 1 FROM t1 GROUP BY b HAVING a = 2; 788--error ER_INVALID_GROUP_FUNC_USE 789SELECT 1 FROM t1 GROUP BY SUM(b); 790--error ER_WRONG_FIELD_WITH_GROUP 791SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN 792 (SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a 793 HAVING SUM(t1_inner.b)+t1_outer.b > 5); 794DROP TABLE t1; 795SET SQL_MODE = ''; 796# 797# Bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode. 798# 799SET SQL_MODE = 'ONLY_FULL_GROUP_BY'; 800create table t1(f1 int, f2 int); 801--error 1055 802select * from t1 group by f1; 803--error 1055 804select * from t1 group by f2; 805select * from t1 group by f1, f2; 806--error 1055 807select t1.f1,t.* from t1, t1 t group by 1; 808drop table t1; 809SET SQL_MODE = ''; 810 811# 812# Bug #32202: ORDER BY not working with GROUP BY 813# 814 815CREATE TABLE t1( 816 id INT AUTO_INCREMENT PRIMARY KEY, 817 c1 INT NOT NULL, 818 c2 INT NOT NULL, 819 UNIQUE KEY (c2,c1)); 820 821INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3); 822 823# Show that the test cases from the bug report pass 824SELECT * FROM t1 ORDER BY c1; 825SELECT * FROM t1 GROUP BY id ORDER BY c1; 826 827# Show that DESC is handled correctly 828SELECT * FROM t1 GROUP BY id ORDER BY id DESC; 829 830# Show that results are correctly ordered when ORDER BY fields 831# are a subset of GROUP BY ones 832SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1; 833SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1; 834SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC; 835 836# Show that results are correctly ordered when GROUP BY fields 837# are a subset of ORDER BY ones 838SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1; 839SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1; 840SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC; 841 842DROP TABLE t1; 843 844 845--echo # 846--echo # Bug#27219: Aggregate functions in ORDER BY. 847--echo # 848SET @save_sql_mode=@@sql_mode; 849SET @@sql_mode='ONLY_FULL_GROUP_BY'; 850 851CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0); 852INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4); 853CREATE TABLE t2 SELECT * FROM t1; 854 855SELECT 1 FROM t1 ORDER BY COUNT(*); 856SELECT 1 FROM t1 ORDER BY COUNT(*) + 1; 857--error 1140 858SELECT 1 FROM t1 ORDER BY COUNT(*) + a; 859SELECT 1 FROM t1 ORDER BY COUNT(*), 1; 860--error 1140 861SELECT 1 FROM t1 ORDER BY COUNT(*), a; 862 863SELECT 1 FROM t1 ORDER BY SUM(a); 864SELECT 1 FROM t1 ORDER BY SUM(a + 1); 865SELECT 1 FROM t1 ORDER BY SUM(a) + 1; 866--error 1140 867SELECT 1 FROM t1 ORDER BY SUM(a), b; 868 869--error 1140 870SELECT a FROM t1 ORDER BY COUNT(b); 871 872SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2); 873 874--error 1140 875SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a), t2.a FROM t2); 876--error 1140 877SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a); 878--error 1140 879SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1); 880 881--error 1140 882SELECT t1.a FROM t1 883 WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1); 884--error 1140 885SELECT t1.a FROM t1 GROUP BY t1.a 886 HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1); 887 888SELECT t1.a FROM t1 GROUP BY t1.a 889 HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b)); 890--error 1140 891SELECT t1.a FROM t1 GROUP BY t1.a 892 HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b)); 893--error 1140 894SELECT t1.a FROM t1 GROUP BY t1.a 895 HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b)); 896 897--error 1140 898SELECT t1.a FROM t1 899 WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b)); 900 901SELECT 1 FROM t1 GROUP BY t1.a 902 HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1); 903SELECT 1 FROM t1 GROUP BY t1.a 904 HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1); 905SELECT 1 FROM t1 GROUP BY t1.a 906 HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1); 907 908--error 1140 909SELECT 1 FROM t1 GROUP BY t1.a 910 HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1); 911--error 1140 912SELECT 1 FROM t1 GROUP BY t1.a 913 HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1); 914--error 1140 915SELECT 1 FROM t1 GROUP BY t1.a 916 HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1); 917 918# Both SUMs are aggregated in the subquery, no mixture: 919SELECT t1.a FROM t1 920 WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a 921 ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1); 922 923# SUM(t1.b) is aggregated in the subquery, no mixture: 924SELECT t1.a, SUM(t1.b) FROM t1 925 WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a 926 ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1) 927 GROUP BY t1.a; 928 929# 2nd SUM(t1.b) is aggregated in the subquery, no mixture: 930SELECT t1.a, SUM(t1.b) FROM t1 931 WHERE t1.a = (SELECT SUM(t2.b) FROM t2 932 ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1) 933 GROUP BY t1.a; 934 935# SUM(t2.b + t1.a) is aggregated in the subquery, no mixture: 936SELECT t1.a, SUM(t1.b) FROM t1 937 WHERE t1.a = (SELECT SUM(t2.b) FROM t2 938 ORDER BY SUM(t2.b + t1.a) LIMIT 1) 939 GROUP BY t1.a; 940 941SELECT t1.a FROM t1 GROUP BY t1.a 942 HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1); 943 944select avg ( 945 (select 946 (select sum(outr.a + innr.a) from t1 as innr limit 1) as tt 947 from t1 as outr order by outr.a limit 1)) 948from t1 as most_outer; 949 950--error 1140 951select avg ( 952 (select ( 953 (select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt 954 from t1 as outr order by count(outr.a) limit 1)) as tt 955from t1 as most_outer; 956 957select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a; 958 959SET sql_mode=@save_sql_mode; 960DROP TABLE t1, t2; 961 962--echo # 963--echo # BUG#38072: Wrong result: HAVING not observed in a query with aggregate 964--echo # 965CREATE TABLE t1 ( 966 pk int(11) NOT NULL AUTO_INCREMENT, 967 int_nokey int(11) NOT NULL, 968 int_key int(11) NOT NULL, 969 varchar_key varchar(1) NOT NULL, 970 varchar_nokey varchar(1) NOT NULL, 971 PRIMARY KEY (pk), 972 KEY int_key (int_key), 973 KEY varchar_key (varchar_key) 974); 975INSERT INTO t1 VALUES 976(1,5,5, 'h','h'), 977(2,1,1, '{','{'), 978(3,1,1, 'z','z'), 979(4,8,8, 'x','x'), 980(5,7,7, 'o','o'), 981(6,3,3, 'p','p'), 982(7,9,9, 'c','c'), 983(8,0,0, 'k','k'), 984(9,6,6, 't','t'), 985(10,0,0,'c','c'); 986 987explain SELECT COUNT(varchar_key) AS X FROM t1 WHERE pk = 8 having 'foo'='bar'; 988SELECT COUNT(varchar_key) AS X FROM t1 WHERE pk = 8 having 'foo'='bar'; 989drop table t1; 990 991--echo End of 5.0 tests 992# Bug #21174: Index degrades sort performance and 993# optimizer does not honor IGNORE INDEX. 994# a.k.a WL3527. 995# 996CREATE TABLE t1 (a INT, b INT, 997 PRIMARY KEY (a), 998 KEY i2(a,b)); 999INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8); 1000INSERT INTO t1 SELECT a + 8,b FROM t1; 1001INSERT INTO t1 SELECT a + 16,b FROM t1; 1002INSERT INTO t1 SELECT a + 32,b FROM t1; 1003INSERT INTO t1 SELECT a + 64,b FROM t1; 1004INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16; 1005ANALYZE TABLE t1; 1006EXPLAIN SELECT a FROM t1 WHERE a < 2; 1007EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a; 1008EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a; 1009EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2); 1010EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2); 1011 1012--echo # 1013--echo # For this explain, the query plan is weird: if we are using 1014--echo # the primary key for reasons other than doing grouping, can't 1015--echo # GROUP BY code take advantage of this? Well, currently it doesnt: 1016EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a; 1017--echo # Here's a proof it is really doing sorting: 1018flush status; 1019--disable_result_log 1020SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a; 1021--enable_result_log 1022show status like 'Sort_%'; 1023--echo # Proof ends. 1024--echo # 1025 1026--echo # For this explain, the query plan is weird: if we are using 1027--echo # the primary key for reasons other than doing sorting, can't 1028--echo # ORDER BY code take advantage of this? Well, currently it doesnt: 1029EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a; 1030--echo # Here's a proof it is really doing sorting: 1031flush status; 1032--disable_result_log 1033SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a; 1034--enable_result_log 1035show status like 'Sort_%'; 1036--echo # Proof ends. 1037--echo # 1038SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a; 1039EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) 1040 IGNORE INDEX FOR GROUP BY (i2) GROUP BY a; 1041EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2); 1042EXPLAIN SELECT a FROM t1 FORCE INDEX (i2); 1043EXPLAIN SELECT a FROM t1 USE INDEX (); 1044EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2); 1045--error ER_WRONG_USAGE 1046EXPLAIN SELECT a FROM t1 1047 FORCE INDEX (PRIMARY) 1048 IGNORE INDEX FOR GROUP BY (i2) 1049 IGNORE INDEX FOR ORDER BY (i2) 1050 USE INDEX (i2); 1051EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX (); 1052--error ER_PARSE_ERROR 1053EXPLAIN SELECT a FROM t1 FORCE INDEX (); 1054--error ER_PARSE_ERROR 1055EXPLAIN SELECT a FROM t1 IGNORE INDEX (); 1056# disable the columns irrelevant to this test here. On some systems 1057# without support for large files the rowid is shorter and its size affects 1058# the cost calculations. This causes the optimizer to choose loose index 1059# scan over normal index access. 1060--replace_column 4 # 7 # 9 # 10 # 1061EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2) 1062 USE INDEX FOR GROUP BY (i2) GROUP BY a; 1063EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2) 1064 FORCE INDEX FOR GROUP BY (i2) GROUP BY a; 1065EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2); 1066EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX (); 1067 1068EXPLAIN SELECT a FROM t1 1069 USE INDEX FOR GROUP BY (i2) 1070 USE INDEX FOR ORDER BY (i2) 1071 USE INDEX FOR JOIN (i2); 1072 1073EXPLAIN SELECT a FROM t1 1074 USE INDEX FOR JOIN (i2) 1075 USE INDEX FOR JOIN (i2) 1076 USE INDEX FOR JOIN (i2,i2); 1077 1078SET @save_optimizer_switch=@@optimizer_switch; 1079SET optimizer_switch='semijoin_with_cache=off'; 1080EXPLAIN SELECT 1 FROM t1 WHERE a IN 1081 (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); 1082SET optimizer_switch=@save_optimizer_switch; 1083 1084CREATE TABLE t2 (a INT, b INT, KEY(a)); 1085INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); 1086EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; 1087EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2; 1088 1089SET @save_optimizer_switch=@@optimizer_switch; 1090SET optimizer_switch='semijoin_with_cache=off'; 1091EXPLAIN SELECT 1 FROM t2 WHERE a IN 1092 (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); 1093SET optimizer_switch=@save_optimizer_switch; 1094 1095DROP TABLE t1, t2; 1096 1097# 1098# Bug#30596: GROUP BY optimization gives wrong result order 1099# 1100CREATE TABLE t1( 1101 a INT, 1102 b INT NOT NULL, 1103 c INT NOT NULL, 1104 d INT, 1105 UNIQUE KEY (c,b) 1106); 1107 1108INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4); 1109 1110CREATE TABLE t2( 1111 a INT, 1112 b INT, 1113 UNIQUE KEY(a,b) 1114); 1115 1116INSERT INTO t2 VALUES (NULL, NULL), (NULL, NULL), (NULL, 1), (1, NULL), (1, 1), (1,2); 1117 1118EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d; 1119SELECT c,b,d FROM t1 GROUP BY c,b,d; 1120EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; 1121SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; 1122EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d; 1123SELECT c,b,d FROM t1 ORDER BY c,b,d; 1124 1125EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b; 1126SELECT c,b,d FROM t1 GROUP BY c,b; 1127EXPLAIN SELECT c,b FROM t1 GROUP BY c,b; 1128SELECT c,b FROM t1 GROUP BY c,b; 1129 1130EXPLAIN SELECT a,b from t2 ORDER BY a,b; 1131SELECT a,b from t2 ORDER BY a,b; 1132EXPLAIN SELECT a,b from t2 GROUP BY a,b; 1133SELECT a,b from t2 GROUP BY a,b; 1134EXPLAIN SELECT a from t2 GROUP BY a; 1135SELECT a from t2 GROUP BY a; 1136EXPLAIN SELECT b from t2 GROUP BY b; 1137SELECT b from t2 GROUP BY b; 1138 1139DROP TABLE t1; 1140DROP TABLE t2; 1141 1142# 1143# Bug #31797: error while parsing subqueries -- WHERE is parsed as HAVING 1144# 1145CREATE TABLE t1 ( a INT, b INT ); 1146 1147SELECT b c, (SELECT a FROM t1 WHERE b = c) 1148FROM t1; 1149 1150SELECT b c, (SELECT a FROM t1 WHERE b = c) 1151FROM t1 1152HAVING b = 10; 1153 1154--error ER_ILLEGAL_REFERENCE 1155SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c) 1156FROM t1 1157HAVING b = 10; 1158 1159SET @old_sql_mode = @@sql_mode; 1160SET @@sql_mode='ONLY_FULL_GROUP_BY'; 1161 1162SELECT b c, (SELECT a FROM t1 WHERE b = c) 1163FROM t1; 1164 1165--error ER_NON_GROUPING_FIELD_USED 1166SELECT b c, (SELECT a FROM t1 WHERE b = c) 1167FROM t1 1168HAVING b = 10; 1169 1170--error ER_ILLEGAL_REFERENCE 1171SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c) 1172FROM t1 1173HAVING b = 10; 1174 1175INSERT INTO t1 VALUES (1, 1); 1176SELECT b c, (SELECT a FROM t1 WHERE b = c) 1177FROM t1; 1178 1179INSERT INTO t1 VALUES (2, 1); 1180--error ER_SUBQUERY_NO_1_ROW 1181SELECT b c, (SELECT a FROM t1 WHERE b = c) 1182FROM t1; 1183 1184DROP TABLE t1; 1185SET @@sql_mode = @old_sql_mode; 1186 1187 1188# 1189# Bug#42567 Invalid GROUP BY error 1190# 1191 1192# Setup of the subtest 1193SET @old_sql_mode = @@sql_mode; 1194SET @@sql_mode='ONLY_FULL_GROUP_BY'; 1195 1196CREATE TABLE t1(i INT); 1197INSERT INTO t1 VALUES (1), (10); 1198 1199# The actual test 1200SELECT COUNT(i) FROM t1; 1201SELECT COUNT(i) FROM t1 WHERE i > 1; 1202 1203# Cleanup of subtest 1204DROP TABLE t1; 1205SET @@sql_mode = @old_sql_mode; 1206 1207--echo # 1208--echo # Bug #45640: optimizer bug produces wrong results 1209--echo # 1210 1211CREATE TABLE t1 (a INT, b INT); 1212INSERT INTO t1 VALUES (4, 40), (1, 10), (2, 20), (2, 20), (3, 30); 1213 1214--echo # should return 4 ordered records: 1215SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa; 1216 1217SELECT (SELECT (SELECT t1.a)) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa; 1218 1219SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa+0; 1220 1221--echo # should return the same result in a reverse order: 1222SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY -aa; 1223 1224--echo # execution plan should not use temporary table: 1225EXPLAIN EXTENDED 1226SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa+0; 1227 1228EXPLAIN EXTENDED 1229SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY -aa; 1230 1231--echo # should return only one record 1232SELECT (SELECT tt.a FROM t1 tt LIMIT 1) aa, COUNT(DISTINCT b) FROM t1 1233 GROUP BY aa; 1234 1235CREATE TABLE t2 SELECT DISTINCT a FROM t1; 1236 1237--echo # originally reported queries (1st two columns of next two query 1238--echo # results should be same): 1239 1240SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(DISTINCT b) 1241 FROM t1 GROUP BY aa, b; 1242SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT( b) 1243 FROM t1 GROUP BY aa, b; 1244 1245--echo # ORDER BY for sure: 1246 1247SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(DISTINCT b) 1248 FROM t1 GROUP BY aa, b ORDER BY -aa, -b; 1249SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT( b) 1250 FROM t1 GROUP BY aa, b ORDER BY -aa, -b; 1251 1252DROP TABLE t1, t2; 1253 1254 1255--echo # 1256--echo # Bug#52051: Aggregate functions incorrectly returns NULL from outer 1257--echo # join query 1258--echo # 1259CREATE TABLE t1 (a INT PRIMARY KEY); 1260CREATE TABLE t2 (a INT PRIMARY KEY); 1261INSERT INTO t2 VALUES (1), (2); 1262EXPLAIN SELECT MIN(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; 1263SELECT MIN(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; 1264EXPLAIN SELECT MAX(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; 1265SELECT MAX(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; 1266DROP TABLE t1, t2; 1267 1268# 1269# min() returns wrong value when used in expression when there is no matching 1270# rows 1271# 1272 1273CREATE TABLE t1 (a int(11) NOT NULL); 1274INSERT INTO t1 VALUES (1),(2); 1275CREATE TABLE t2 ( 1276 key_col int(11) NOT NULL, 1277 KEY (key_col) 1278); 1279INSERT INTO t2 VALUES (1),(2); 1280 1281select min(t2.key_col) from t1,t2 where t1.a=1; 1282select min(t2.key_col) from t1,t2 where t1.a > 1000; 1283select min(t2.key_col)+1 from t1,t2 where t1.a> 1000; 1284drop table t1,t2; 1285 1286--echo # 1287--echo # Bug#55188: GROUP BY, GROUP_CONCAT and TEXT - inconsistent results 1288--echo # 1289 1290CREATE TABLE t1 (a text, b varchar(10)); 1291INSERT INTO t1 VALUES (repeat('1', 1300),'one'), (repeat('1', 1300),'two'); 1292 1293query_vertical EXPLAIN 1294SELECT SUBSTRING(a,1,10), LENGTH(a), GROUP_CONCAT(b) FROM t1 GROUP BY a; 1295SELECT SUBSTRING(a,1,10), LENGTH(a), GROUP_CONCAT(b) FROM t1 GROUP BY a; 1296query_vertical EXPLAIN 1297SELECT SUBSTRING(a,1,10), LENGTH(a) FROM t1 GROUP BY a; 1298SELECT SUBSTRING(a,1,10), LENGTH(a) FROM t1 GROUP BY a; 1299DROP TABLE t1; 1300 1301--echo # 1302--echo # Bug#57688 Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field 1303--echo # 1304 1305CREATE TABLE t1(f1 INT NOT NULL); 1306INSERT INTO t1 VALUES (16777214),(0); 1307 1308SELECT COUNT(*) FROM t1 LEFT JOIN t1 t2 1309ON 1 WHERE t2.f1 > 1 GROUP BY t2.f1; 1310 1311DROP TABLE t1; 1312 1313--echo # 1314--echo # Bug#59839: Aggregation followed by subquery yields wrong result 1315--echo # 1316 1317CREATE TABLE t1 ( 1318 a INT, 1319 b INT, 1320 c INT, 1321 KEY (a, b) 1322); 1323 1324INSERT INTO t1 VALUES 1325 ( 1, 1, 1 ), 1326 ( 1, 2, 2 ), 1327 ( 1, 3, 3 ), 1328 ( 1, 4, 6 ), 1329 ( 1, 5, 5 ), 1330 ( 1, 9, 13 ), 1331 1332 ( 2, 1, 6 ), 1333 ( 2, 2, 7 ), 1334 ( 2, 3, 8 ); 1335 1336EXPLAIN 1337SELECT a, AVG(t1.b), 1338(SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c, 1339(SELECT t12.c FROM t1 t12 WHERE t12.a = t1.a AND t12.b = AVG(t1.b)) AS t12c 1340FROM t1 GROUP BY a; 1341 1342SELECT a, AVG(t1.b), 1343(SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c, 1344(SELECT t12.c FROM t1 t12 WHERE t12.a = t1.a AND t12.b = AVG(t1.b)) AS t12c 1345FROM t1 GROUP BY a; 1346 1347DROP TABLE t1; 1348 1349--echo # 1350--echo # Bug#11765254 (58200): Assertion failed: param.sort_length when grouping 1351--echo # by functions 1352--echo # 1353 1354set tmp_memory_table_size=0; 1355CREATE TABLE t1(a INT); 1356INSERT INTO t1 VALUES (0),(0); 1357SELECT 1 FROM t1 GROUP BY IF(`a`,'',''); 1358SELECT 1 FROM t1 GROUP BY TRIM(LEADING RAND() FROM ''); 1359SELECT 1 FROM t1 GROUP BY SUBSTRING('',SLEEP(0),''); 1360SELECT 1 FROM t1 GROUP BY SUBSTRING(SYSDATE() FROM 'K' FOR 'jxW<'); 1361DROP TABLE t1; 1362set tmp_memory_table_size=default; 1363 1364--echo # 1365--echo # MDEV-641 LP:1002108 - Wrong result (or crash) from a query with duplicated field in the group list and a limit clause 1366--echo # Bug#11761078: 53534: INCORRECT 'SELECT SQL_BIG_RESULT...' 1367--echo # WITH GROUP BY ON DUPLICATED FIELDS 1368--echo # 1369 1370CREATE TABLE t1( 1371 col1 int, 1372 UNIQUE INDEX idx (col1)); 1373 1374INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10), 1375 (11),(12),(13),(14),(15),(16),(17),(18),(19),(20); 1376 1377let $query0=SELECT col1 AS field1, col1 AS field2 1378 FROM t1 GROUP BY field1, field2; 1379 1380# Needs to be range to exercise bug 1381--eval EXPLAIN $query0; 1382FLUSH STATUS; 1383--eval $query0; 1384SHOW SESSION STATUS LIKE 'Sort_scan%'; 1385 1386let $query=SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2 1387 FROM t1 GROUP BY field1, field2; 1388 1389# Needs to be range to exercise bug 1390--eval EXPLAIN $query 1391FLUSH STATUS; 1392--eval $query 1393SHOW SESSION STATUS LIKE 'Sort_scan%'; 1394 1395CREATE VIEW v1 AS SELECT * FROM t1; 1396 1397SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2 1398FROM v1 1399GROUP BY field1, field2; 1400 1401SELECT SQL_BIG_RESULT tbl1.col1 AS field1, tbl2.col1 AS field2 1402FROM t1 as tbl1, t1 as tbl2 1403GROUP BY field1, field2 1404LIMIT 3; 1405 1406explain 1407select col1 f1, col1 f2 from t1 order by f2, f1; 1408explain 1409select col1 f1, col1 f2 from t1 order by f2, f1+0; 1410select col1 f1, col1 f2 from t1 order by f2, f1+0; 1411 1412explain 1413select col1 f1, col1 f2 from t1 group by f1; 1414select col1 f1, col1 f2 from t1 group by f1; 1415 1416explain 1417select col1 f1, col1 f2 from t1 group by f1, f2; 1418select col1 f1, col1 f2 from t1 group by f1, f2; 1419 1420explain 1421select col1 f1, col1 f2 from t1 group by f2 order by f2, f1; 1422select col1 f1, col1 f2 from t1 group by f2 order by f2, f1; 1423 1424explain 1425select col1 f1, col1 f2 from t1 group by f1, f2 order by f2, f1; 1426select col1 f1, col1 f2 from t1 group by f1, f2 order by f2, f1; 1427 1428 1429CREATE TABLE t2( 1430 col1 int, 1431 col2 int, 1432 UNIQUE INDEX idx (col1, col2)); 1433 1434INSERT INTO t2(col1, col2) VALUES 1435 (1,20),(2,19),(3,18),(4,17),(5,16),(6,15),(7,14),(8,13),(9,12),(10,11), 1436 (11,10),(12,9),(13,8),(14,7),(15,6),(16,5),(17,4),(18,3),(19,2),(20,1); 1437 1438explain 1439select col1 f1, col2 f2, col1 f3 from t2 group by f1; 1440explain 1441select SQL_BIG_RESULT col1 f1, col2 f2, col1 f3 from t2 group by f1; 1442explain 1443select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2; 1444explain 1445select col1 f1, col1 f2 from t2 group by f1, 1+1; 1446 1447explain 1448select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3+0; 1449select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3+0; 1450 1451explain 1452select col1 f1, col2 f2, col1 f3 from t2 order by f1,f2; 1453explain 1454select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3+0; 1455select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3+0; 1456 1457DROP VIEW v1; 1458DROP TABLE t1, t2; 1459 1460--echo # End of 5.1 tests 1461 1462--echo # 1463--echo # LP bug#694450 Wrong result with non-standard GROUP BY + ORDER BY 1464--echo # 1465SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY'; 1466CREATE TABLE t1 ( 1467f1 int(11), f2 int(11), f3 datetime, f4 varchar(1), PRIMARY KEY (f1)) ; 1468INSERT 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'); 1469 1470# This must return an error, but instead returns 1 row 1471SELECT 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; 1472 1473# This returns several rows 1474SELECT 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 ; 1475SET SESSION SQL_MODE=default; 1476drop table t1; 1477 1478--echo # 1479--echo # LP bug#967242 Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE 1480--echo # 1481 1482CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM; 1483INSERT INTO t1 VALUES ('x'); 1484CREATE TABLE t2 ( b INT, c VARCHAR(1), KEY (c, b) ) ENGINE=MyISAM; 1485INSERT INTO t2 VALUES 1486(4, 'd'),(8, 'g'),(3, 'x'),(3, 'f'), 1487(0, 'p'),(3, 'j'),(8, 'c'); 1488 1489SELECT t2_1.b as zzz 1490FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2 1491ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c ) 1492WHERE 1493rand() + 1 > 0 OR 1494a = t2_1.c 1495GROUP BY zzz; 1496 1497SELECT t2_1.b as zzz 1498FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2 1499ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c ) 1500WHERE 15011 > 0 OR 1502a = t2_1.c 1503GROUP BY zzz; 1504 1505SELECT t2_1.b as zzz 1506FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2 1507ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c ) 1508WHERE 1509t2_1.b + 1 > 0 OR 1510a = t2_1.c 1511GROUP BY zzz; 1512 1513SET @save_optimizer_switch967242=@@optimizer_switch; 1514SET optimizer_switch = 'in_to_exists=on'; 1515 1516SELECT t2_1.b 1517FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2 1518 ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c ) 1519WHERE 1520 ( SELECT COUNT(*) FROM t2 ) IS NOT NULL 1521 OR a = t2_1.c 1522GROUP BY t2_1.b; 1523SET optimizer_switch=@save_optimizer_switch967242; 1524 1525drop table t1, t2; 1526 1527--echo # 1528--echo # Bug#12578908: SELECT SQL_BUFFER_RESULT OUTPUTS TOO MANY 1529--echo # ROWS WHEN GROUP IS OPTIMIZED AWAY 1530--echo # 1531 1532CREATE TABLE t1 (col1 int, col2 int) ; 1533INSERT INTO t1 VALUES (10,1),(11,7); 1534 1535CREATE TABLE t2 (col1 int, col2 int) ; 1536INSERT INTO t2 VALUES (10,8); 1537 1538let $q_body=t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2; 1539 1540--echo 1541--eval EXPLAIN SELECT SQL_BUFFER_RESULT $q_body 1542--eval SELECT SQL_BUFFER_RESULT $q_body 1543--echo 1544--eval EXPLAIN SELECT $q_body 1545--eval SELECT $q_body 1546 1547--echo 1548DROP TABLE t1,t2; 1549 1550--echo # 1551--echo # BUG#12640437: USING SQL_BUFFER_RESULT RESULTS IN A 1552--echo # DIFFERENT QUERY OUTPUT 1553--echo # 1554 1555CREATE TABLE t1 ( 1556 a int, 1557 b varchar(1), 1558 KEY (b,a) 1559); 1560INSERT INTO t1 VALUES 1561 (1,NULL),(0,'a'),(1,NULL),(0,'a'), (1,'a'),(0,'a'),(2,'a'),(1,'a'); 1562ANALYZE TABLE t1; 1563 1564let $query= 1565 SELECT SQL_BUFFER_RESULT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; 1566 1567--echo 1568--eval EXPLAIN $query 1569--echo 1570--eval $query 1571 1572let $query= SELECT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; 1573--echo 1574--eval EXPLAIN $query 1575--echo 1576--eval $query 1577 1578--echo 1579DROP TABLE t1; 1580 1581 1582# 1583# Bug#19880368 : GROUP_CONCAT CRASHES AFTER DUMP_LEAF_KEY 1584# 1585create table t1 (a int, b int); 1586insert into t1 values (1,11), (1,12), (2,22),(2,23), (4,44),(4,45); 1587create table t2 (c int, d int); 1588insert into t2 values (1,11), (2,22), (4,44); 1589select 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); 1590drop table t1, t2; 1591 1592--echo # 1593--echo # LP bug#993726 Wrong result from a query with ALL subquery predicate in WHERE 1594--echo # 1595CREATE TABLE t1(a INT); 1596INSERT INTO t1 VALUES (0); 1597SELECT 1 FROM t1 WHERE 1 > ALL(SELECT 1 FROM t1 WHERE a!=0); 1598SELECT max(1) FROM t1 WHERE a!=0; 1599drop table t1; 1600 1601--echo # End of 5.2 tests 1602 1603--echo # 1604--echo # lp:872702: Crash in add_ref_to_table_cond() when grouping by a PK 1605--echo # 1606CREATE TABLE t1 (a int, PRIMARY KEY (a)) ; 1607INSERT INTO t1 VALUES (14),(15),(16),(17),(18),(19),(20); 1608 1609CREATE TABLE t2 (a int) ; 1610 1611SELECT a 1612FROM t1 1613WHERE a = ( 1614 SELECT t2.a 1615 FROM t2 1616) OR t1.a = 73 1617GROUP BY 1; 1618DROP TABLE t1, t2; 1619 1620# 1621# MDEV-736 LP:1004615 - Unexpected warnings "Encountered illegal value '' when converting to DECIMAL" on a query with aggregate functions and GROUP BY 1622# 1623 1624FLUSH STATUS; # this test case *must* use Aria temp tables 1625 1626CREATE TABLE t1 (f1 INT, f2 decimal(20,1), f3 blob); 1627INSERT INTO t1 values(11,NULL,'blob'),(11,NULL,'blob'); 1628SELECT f3, MIN(f2) FROM t1 GROUP BY f1 LIMIT 1; 1629DROP TABLE t1; 1630 1631--echo the value below *must* be 1 1632show status like 'Created_tmp_disk_tables'; 1633 1634--echo # 1635--echo # Bug #1002146: Unneeded filesort if usage of join buffer is not allowed 1636--echo # (bug mdev-645) 1637--echo # 1638 1639CREATE TABLE t1 (pk int PRIMARY KEY, a int, INDEX idx(a)); 1640INSERT INTO t1 VALUES (3,2), (2,3), (5,3), (6,4); 1641 1642CREATE TABLE t2 (pk int PRIMARY KEY, a int, INDEX idx(a)); 1643INSERT INTO t2 VALUES (9,0), (10,3), (6,4), (1,6), (3,100), (5,200); 1644 1645set join_cache_level=0; 1646 1647EXPLAIN 1648SELECT t2.a FROM t2 STRAIGHT_JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6 1649 GROUP BY t2.a; 1650SELECT t2.a FROM t2 STRAIGHT_JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6 1651 GROUP BY t2.a; 1652 1653set join_cache_level=default; 1654 1655set @save_optimizer_switch=@@optimizer_switch; 1656set optimizer_switch='outer_join_with_cache=off'; 1657 1658EXPLAIN 1659SELECT t2.a FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6 1660 GROUP BY t2.a; 1661SELECT t2.a FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6 1662 GROUP BY t2.a; 1663 1664set optimizer_switch=@save_optimizer_switch; 1665 1666 1667DROP TABLE t1,t2; 1668 1669--echo # 1670--echo # MDEV-5104 crash in Item_field::used_tables with broken order by 1671--echo # 1672 1673--error ER_BAD_FIELD_ERROR 1674(select 1 order by x(y)) order by 1; 1675 1676--echo # End of 5.3 tests 1677 1678--echo # 1679--echo # Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00 1680--echo # 1681CREATE TABLE t1 (f1 int, f2 DATE); 1682 1683INSERT INTO t1 VALUES (1,'2004-04-19'), (1,'0000-00-00'), (1,'2004-04-18'), 1684(2,'2004-05-19'), (2,'0001-01-01'), (3,'2004-04-10'); 1685 1686SELECT MIN(f2),MAX(f2) FROM t1; 1687SELECT f1,MIN(f2),MAX(f2) FROM t1 GROUP BY 1; 1688 1689DROP TABLE t1; 1690 1691CREATE TABLE t1 ( f1 int, f2 time); 1692INSERT INTO t1 VALUES (1,'01:27:35'), (1,'06:11:01'), (2,'19:53:05'), 1693(2,'21:44:25'), (3,'10:55:12'), (3,'05:45:11'), (4,'00:25:00'); 1694 1695SELECT MIN(f2),MAX(f2) FROM t1; 1696SELECT f1,MIN(f2),MAX(f2) FROM t1 GROUP BY 1; 1697 1698DROP TABLE t1; 1699--echo #End of test#49771 1700 1701--echo # 1702--echo # Test of bug in GROUP_CONCAT with ROLLUP 1703--echo # 1704 1705CREATE TABLE t1 ( b VARCHAR(8) NOT NULL, a INT NOT NULL ) ENGINE=MyISAM; 1706INSERT INTO t1 (a,b) VALUES (1,'c'),(2,'v'); 1707 1708CREATE TABLE t2 ( c VARCHAR(8), d INT, KEY (c, d) ) ENGINE=MyISAM; 1709INSERT INTO t2 VALUES ('v',6),('c',4),('v',3); 1710 1711SELECT b, GROUP_CONCAT( a, b ORDER BY a, b ) 1712FROM t1 JOIN t2 ON c = b GROUP BY b; 1713 1714SELECT b, GROUP_CONCAT( a, b ORDER BY a, b ) 1715FROM t1 JOIN t2 ON c = b GROUP BY b WITH ROLLUP; 1716 1717DROP TABLE t1,t2; 1718 1719--echo # 1720--echo # MDEV-24855 ER_CRASHED_ON_USAGE or Assertion `length <= column->length' 1721--echo # failed in ma_blockrec.c 1722--echo # 1723 1724CREATE TABLE t1 (a BIT(5), c BINARY(179)); 1725INSERT INTO t1 VALUES (b'1100','foo'),(b'0','bar'); 1726--disable_result_log 1727SELECT c, GROUP_CONCAT(CASE NULL WHEN 0 THEN a END, CASE 'foo' WHEN c THEN 1 END) AS f FROM t1 GROUP BY ExtractValue('<a></a>', '/a'), UUID(); 1728--enable_result_log 1729DROP TABLE t1; 1730 1731--echo # 1732--echo # MDEV-6129: Server crashes during UNION with ORDER BY field IS NULL 1733--echo # 1734 1735SET sql_mode='ONLY_FULL_GROUP_BY'; 1736SELECT 1 AS test UNION SELECT 2 AS test ORDER BY test IS NULL ASC; 1737SET sql_mode=''; 1738 1739--echo # 1740--echo # MDEV-6484: Assertion `tab->ref.use_count' failed on query with joins, constant table, multi-part key 1741--echo # 1742CREATE TABLE t1 (i1 INT, c1 VARCHAR(1)) ENGINE=MyISAM; 1743INSERT INTO t1 VALUES (6,'b'); 1744 1745CREATE TABLE t2 (pk2 INT, i2 INT, c2 VARCHAR(1), PRIMARY KEY(pk2), KEY(pk2,i2)) ENGINE=MyISAM; 1746INSERT INTO t2 VALUES (1,2,'s'),(2,4,'r'),(3,8,'m'),(4,4,'b'),(5,4,'x'),(6,7,'g'),(7,4,'p'); 1747 1748SELECT i2 FROM t1 AS t1a STRAIGHT_JOIN ( t2 INNER JOIN t1 AS t1b ON (t1b.c1 = c2) ) ON (t1b.i1 = pk2 ) 1749WHERE t1a.c1 = c2 GROUP BY i2; 1750 1751DROP TABLE t1,t2; 1752 1753--echo # 1754--echo # MDEV-6855 1755--echo # MIN(*) with subqueries with IS NOT NULL in WHERE clause crashed. 1756--echo # 1757 1758CREATE TABLE t1 (i INT, c VARCHAR(3), KEY(c,i)) ENGINE=MyISAM; 1759INSERT INTO t1 VALUES (7,'foo'),(0,'bar'); 1760 1761CREATE TABLE t2 (j INT) ENGINE=MyISAM; 1762INSERT INTO t2 VALUES (0),(8),(1),(8),(9); 1763 1764SELECT MAX(i), c FROM t1 1765WHERE c != 'qux' AND ( SELECT SUM(j) FROM t1, t2 ) IS NOT NULL GROUP BY c; 1766drop table t1,t2; 1767 1768--echo # 1769--echo # ONLY_FULL_GROUP_BY references 1770--echo # 1771 1772set @save_sql_mode = @@sql_mode; 1773set sql_mode='ONLY_FULL_GROUP_BY'; 1774create table t1 (a int, b int); 1775select a+b as x from t1 group by x having x > 1; 1776select a as x from t1 group by x having x > 1; 1777select a from t1 group by a having a > 1; 1778drop table t1; 1779set sql_mode= @save_sql_mode; 1780 1781# 1782# MDEV-7826 Server crashes in Item_subselect::enumerate_field_refs_processor 1783# 1784create table t1 (f1 int); 1785insert into t1 values (5),(9); 1786create table t2 (f2 int); 1787insert into t2 values (0),(6); 1788create table t3 (f3 int); 1789insert into t3 values (6),(3); 1790create table t4 (f4 int); 1791insert into t4 values (1),(0); 1792--error ER_ILLEGAL_REFERENCE 1793select 1794(select min(f1) from t1 where f1 in (select min(f4) from t2)) as field7, 1795(select count(*) from t3 where f3 in (select max(f4) from t2 group by field7)) 1796from t4; 1797drop table t1, t2, t3, t4; 1798 1799# 1800# MDEV-13180 Unused left join causes server crash 1801# 1802create table t1 (i1 int); 1803insert into t1 values (1); 1804create table t2 (i int); 1805insert into t2 values (2); 1806select 1 from t1 left join t2 b on b.i = (select max(b.i) from t2); 1807drop table t1, t2; 1808 1809 1810# 1811# MDEV-12489 The select stmt may fail due to "having clause is ambiguous" unexpected 1812# 1813create table t1 (c1 int, c2 int); 1814create table t2 (c1 int, c2 int); 1815select 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; 1816drop table t1, t2; 1817 1818# 1819# MDEV-12350: Heap corruption, overrun buffer, ASAN errors, server crash in my_fill_8bit / filesort 1820# 1821 1822SET @old_sort_buff_size = @@sort_buffer_size; 1823SET @@sort_buffer_size=256*1024; 1824CREATE TABLE t1 (c INT) ENGINE=MyISAM; 1825INSERT INTO t1 VALUES 1826 (2011),(1977),(1982),(2027),(2023),(NULL),(NULL),(2004),(1974),(2032), 1827 (1993),(NULL),(1995),(2034),(NULL),(2009),(1900),(NULL),(2025),(1900), 1828 (2033),(1900),(2012),(NULL),(2009),(1992),(1974),(1974),(2012),(2028), 1829 (2007),(2012),(1900),(1983),(1900),(2010),(1987),(1994),(1981),(2032), 1830 (2010),(1989),(2014),(1900),(1900),(1976),(1978),(2007),(2030),(NULL), 1831 (2002),(1997),(1900),(NULL),(2000),(2027),(1975),(2026),(1975),(2026), 1832 (2029),(1977),(1900),(1900),(2031),(1993),(1986),(2012),(1979),(2013), 1833 (1994),(2014),(2025),(2006),(1971),(1974),(2021),(2011),(NULL),(1991), 1834 (2001),(1977),(2023),(2012),(1900),(1978),(1998),(NULL),(1988),(1999), 1835 (2017),(2008),(1976),(1900),(2005),(2030),(2023),(1900),(1978),(1990), 1836 (1978),(1987),(2030),(1900),(2034),(2006),(2015),(2001),(2019),(2024), 1837 (2030),(1989),(1997),(2007),(2023),(1994),(1971),(2011),(2011),(2015), 1838 (1984),(1978),(1979),(1989),(2008),(2030); 1839 1840SELECT ExtractValue('<a></a>','/a') AS f1, SPACE(c) AS f2 FROM t1 GROUP BY f1, f2 WITH ROLLUP; 1841SET @@sort_buffer_size = @old_sort_buff_size; 1842DROP TABLE t1; 1843 1844# 1845# End of MariaDB 5.5 tests 1846# 1847 1848--echo # 1849--echo # Bug #58782 1850--echo # Missing rows with SELECT .. WHERE .. IN subquery 1851--echo # with full GROUP BY and no aggr 1852--echo # 1853 1854CREATE TABLE t1 ( 1855 pk INT NOT NULL, 1856 col_int_nokey INT, 1857 PRIMARY KEY (pk) 1858); 1859 1860INSERT INTO t1 VALUES (10,7); 1861INSERT INTO t1 VALUES (11,1); 1862INSERT INTO t1 VALUES (12,5); 1863INSERT INTO t1 VALUES (13,3); 1864 1865## original query: 1866 1867SELECT pk AS field1, col_int_nokey AS field2 1868FROM t1 1869WHERE col_int_nokey > 0 1870GROUP BY field1, field2; 1871 1872## store query results in a new table: 1873 1874CREATE TABLE where_subselect 1875 SELECT pk AS field1, col_int_nokey AS field2 1876 FROM t1 1877 WHERE col_int_nokey > 0 1878 GROUP BY field1, field2 1879; 1880 1881## query the new table and compare to original using WHERE ... IN(): 1882 1883SELECT * 1884FROM where_subselect 1885WHERE (field1, field2) IN ( 1886 SELECT pk AS field1, col_int_nokey AS field2 1887 FROM t1 1888 WHERE col_int_nokey > 0 1889 GROUP BY field1, field2 1890); 1891 1892DROP TABLE t1; 1893DROP TABLE where_subselect; 1894 1895--echo # End of Bug #58782 1896 1897--echo # 1898--echo # MDEV-8988: Apparently valid SQL query gives wrong result (nested WHERE) 1899--echo # 1900create table t0(a int); 1901insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1902 1903create table t1 (a int, b int, c int); 1904insert into t1 select A.a + 10*B.a, A.a, A.a + 10*B.a from t0 A, t0 B; 1905insert into t1 values (NULL, NULL, NULL); 1906 1907create table t2 (c int, col1 int, key(c)); 1908insert into t2 select t1.a, 100000 from t1; 1909analyze table t2; 1910 1911explain 1912select 1913 max(a)+ (select col1 from t2 where t2.c=t1.c) 1914from t1 1915group by t1.b; 1916 1917select 1918 max(a) + (select col1 from t2 where t2.c=t1.c) 1919from t1 1920group by t1.b; 1921 1922drop table t0,t1,t2; 1923 1924--echo # 1925--echo # MDEV-9602 crash in st_key::actual_rec_per_key when group by constant 1926--echo # 1927 1928create table t1 (a date not null,unique (a)) engine=innodb; 1929select distinct a from t1 group by 'a'; 1930insert into t1 values("2001-02-02"),("2001-02-03"); 1931select distinct a from t1 group by 'a'; 1932drop table t1; 1933 1934--echo # 1935--echo # MDEV-10324: Server crash in get_sel_arg_for_keypart or Assertion `n < size()' failed in Mem_root_array 1936--echo # 1937CREATE TABLE t1 ( 1938 job_id int(10) unsigned NOT NULL AUTO_INCREMENT, 1939 job_cmd varbinary(60) NOT NULL DEFAULT '', 1940 job_namespace int(11) NOT NULL, 1941 job_title varbinary(255) NOT NULL, 1942 job_params blob NOT NULL, 1943 job_timestamp varbinary(14) DEFAULT NULL, 1944 job_random int(10) unsigned NOT NULL DEFAULT '0', 1945 job_token varbinary(32) NOT NULL DEFAULT '', 1946 job_token_timestamp varbinary(14) DEFAULT NULL, 1947 job_sha1 varbinary(32) NOT NULL DEFAULT '', 1948 job_attempts int(10) unsigned NOT NULL DEFAULT '0', 1949 PRIMARY KEY (job_id), 1950 KEY job_cmd (job_cmd,job_namespace,job_title,job_params(128)), 1951 KEY job_timestamp (job_timestamp), 1952 KEY job_sha1 (job_sha1), 1953 KEY job_cmd_token (job_cmd,job_token,job_random), 1954 KEY job_cmd_token_id (job_cmd,job_token,job_id) 1955); 1956 1957INSERT INTO t1 VALUES 1958 (NULL, 'foo', 1, 'foo', 'foo', 'foo', 1, 'foo', 'foo', 'foo', 1), 1959 (NULL, 'bar', 2, 'bar', 'bar', 'bar', 2, 'bar', 'bar', 'bar', 2); 1960 1961SELECT DISTINCT job_cmd FROM t1 WHERE job_cmd IN ('foobar','null'); 1962drop table t1; 1963 1964CREATE TABLE t1 (f1 INT NOT NULL, f2 VARCHAR(3) NOT NULL, KEY(f1), KEY(f2, f1)); 1965INSERT INTO t1 VALUES (0,'foo'),(1,'bar'); 1966SELECT 1 IN ( SELECT COUNT( DISTINCT f2 ) FROM t1 WHERE f1 <= 4 ); 1967drop table t1; 1968 1969--echo # 1970--echo # MDEV-20922: Adding an order by changes the query results 1971--echo # 1972 1973CREATE TABLE t1(a int, b int); 1974INSERT INTO t1 values (1, 100), (2, 200), (3, 100), (4, 200), (5, 200); 1975 1976create view v1 as select a, b+1 as x from t1; 1977 1978SELECT x, COUNT(DISTINCT a) AS y FROM v1 GROUP BY x ORDER BY y; 1979SELECT b+1 AS x, COUNT(DISTINCT a) AS y FROM t1 GROUP BY x ORDER BY y; 1980 1981drop view v1; 1982drop table t1; 1983--echo # 1984--echo # MDEV-10694 - SIGFPE and/or huge memory allocation in maria_create with distinct/group by/ rollup 1985--echo # 1986create table t1 (a int,b int) ; 1987insert into t1 values(-126,7),(1,1),(0,0),(-1,1),(351,65534); 1988select distinct 1 from t1 group by a,b with rollup limit 1; 1989drop table t1; 1990 1991# 1992# MDEV-12575: Server crash in AGGR_OP::put_record or in JOIN_CACHE::free 1993# or Invalid write in JOIN::make_aggr_tables_info 1994# 1995 1996CREATE TABLE t1 ( pk int, i1 int, v1 varchar(1)); 1997explain 1998SELECT 1 FROM t1 1999GROUP BY REPEAT((BINARY pk), v1), AES_DECRYPT((@A := i1), 20852) WITH ROLLUP HAVING LOAD_FILE('a') ; 2000SELECT 1 FROM t1 2001GROUP BY REPEAT((BINARY pk), v1), AES_DECRYPT((@A := i1), 20852) WITH ROLLUP HAVING LOAD_FILE('a') ; 2002drop table t1; 2003 2004--echo # 2005--echo # MDEV-22019: Sig 11 in next_breadth_first_tab | max_sort_length setting + double 2006--echo # GROUP BY leads to crash 2007--echo # 2008 2009CALL mtr.add_suppression("Out of sort memory"); 2010CALL mtr.add_suppression("Sort aborted"); 2011SET @save_max_sort_length= @@max_sort_length; 2012SET max_sort_length=2000000; 2013--error ER_OUT_OF_SORTMEMORY 2014SELECT * FROM information_schema.tables t JOIN information_schema.columns c 2015ON t.table_schema=c.table_schema 2016WHERE c.table_schema=(SELECT COUNT(*) FROM INFORMATION_SCHEMA.columns GROUP BY column_type) 2017GROUP BY t.table_name; 2018SET max_sort_length= @save_max_sort_length; 2019 2020--echo # 2021--echo # MDEV-23826: ORDER BY in view definition leads to wrong result with GROUP BY on query using view 2022--echo # 2023 2024CREATE TABLE t1 2025( 2026 id INT PRIMARY KEY AUTO_INCREMENT, 2027 dt datetime, 2028 INDEX(dt), 2029 foo int 2030); 2031 2032INSERT INTO t1 VALUES (1,'2020-09-26 12:00:00',1); 2033INSERT INTO t1 VALUES (2,'2020-09-26 13:00:00',1); 2034INSERT INTO t1 VALUES (3,'2020-09-27 13:00:00',1); 2035INSERT INTO t1 VALUES (4,'2020-09-27 12:00:00',1); 2036INSERT INTO t1 VALUES (5,'2020-09-28 12:00:00',1); 2037INSERT INTO t1 VALUES (6,'2020-09-28 13:00:00',1); 2038INSERT INTO t1 VALUES (7,'2020-09-25 12:00:00',1); 2039INSERT INTO t1 VALUES (8,'2020-09-25 13:00:00',1); 2040INSERT INTO t1 VALUES (9,'2020-09-26 13:00:00',1); 2041 2042CREATE VIEW v1 AS SELECT * FROM t1; 2043CREATE VIEW v2 AS SELECT * FROM t1 ORDER BY dt; 2044SELECT dt, sum(foo) AS foo FROM v1 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt; 2045SELECT dt, sum(foo) AS foo FROM v2 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt; 2046 2047DROP TABLE t1; 2048DROP VIEW v1,v2; 2049 2050--echo # 2051--echo # End of 10.2 tests 2052--echo # 2053 2054--echo # 2055--echo # MDEV-16170 2056--echo # Server crashes in Item_null_result::type_handler on SELECT with ROLLUP 2057--echo # 2058 2059CREATE TABLE t1 (d DATE); 2060INSERT INTO t1 VALUES ('2032-10-08'); 2061SELECT d != '2023-03-04' AS f, COUNT(*) FROM t1 GROUP BY d WITH ROLLUP; 2062DROP TABLE t1; 2063 2064--echo # 2065--echo # MDEV-24710 Uninitialized value upon CREATE .. SELECT ... VALUE 2066--echo # 2067 2068CREATE TABLE t1 (a VARCHAR(8) NOT NULL DEFAULT ''); 2069INSERT INTO t1 (a) VALUES ('foo'); 2070CREATE TABLE t2 AS SELECT MAX(a) AS f1, a AS f2 FROM t1 WHERE VALUE(a) IS NOT NULL; 2071SELECT * from t2; 2072SELECT MAX(a) AS f1, a AS f2 FROM t1 WHERE VALUE(a) IS NOT NULL; 2073SELECT MAX(a) AS f1, a AS f2 FROM t1 WHERE 1=0; 2074drop table t1,t2; 2075 2076--echo # Extra test by to check the fix for MDEV-24710 2077 2078create table t20 (pk int primary key, a int); 2079insert into t20 values (1,1);create table t21 (pk int primary key, b int not null); 2080insert into t21 values (1,1); 2081create table t22 (a int); 2082insert into t22 values (1),(2); 2083select a, (select max(t21.b) from t20 left join t21 on t21.pk=t20.a+10 2084 where t20.pk=1 and rand(123) < 0.5) as SUBQ from t22; 2085drop table t20, t21, t22; 2086 2087--echo # 2088--echo # End of 10.3 tests 2089--echo # 2090