~drizzle-trunk/drizzle/development : contents of tests/r/group

~drizzle-trunk/drizzle/development : (revision 1192.3.27)

1 by brian clean slate	1	drop table if exists t1,t2,t3;
	2	SELECT 1 FROM (SELECT 1) as a GROUP BY SUM(1);
	3	ERROR HY000: Invalid use of group function
	4	CREATE TABLE t1 (
520.1.18 by Brian Aker A bunch more test fixes.	5	spID int,
	6	userID int,
	7	score int,
1 by brian clean slate	8	lsg char(40),
	9	date date
	10	);
873.1.1 by Jay Pipes Fixes the Field_date class to not allow any invalid input at	11	INSERT INTO t1 VALUES (1,1,1,'',NULL);
	12	INSERT INTO t1 VALUES (2,2,2,'',NULL);
	13	INSERT INTO t1 VALUES (2,1,1,'',NULL);
	14	INSERT INTO t1 VALUES (3,3,3,'',NULL);
1 by brian clean slate	15	CREATE TABLE t2 (
520.1.18 by Brian Aker A bunch more test fixes.	16	userID int NOT NULL auto_increment,
1 by brian clean slate	17	niName char(15),
	18	passwd char(8),
	19	mail char(50),
	20	isAukt enum('N','Y') DEFAULT 'N',
	21	vName char(30),
	22	nName char(40),
	23	adr char(60),
	24	plz char(5),
	25	ort char(35),
	26	land char(20),
	27	PRIMARY KEY (userID)
	28	);
	29	INSERT INTO t2 VALUES (1,'name','pass','mail','Y','v','n','adr','1','1','1');
	30	INSERT INTO t2 VALUES (2,'name','pass','mail','Y','v','n','adr','1','1','1');
	31	INSERT INTO t2 VALUES (3,'name','pass','mail','Y','v','n','adr','1','1','1');
	32	INSERT INTO t2 VALUES (4,'name','pass','mail','Y','v','n','adr','1','1','1');
	33	INSERT INTO t2 VALUES (5,'name','pass','mail','Y','v','n','adr','1','1','1');
	34	SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid;
	35	userid MIN(t1.score)
	36	1 1
	37	2 2
	38	3 3
	39	SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid ORDER BY NULL;
	40	userid MIN(t1.score)
	41	1 1
	42	2 2
	43	3 3
	44	SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid;
	45	userid MIN(t1.score)
	46	1 1
	47	2 2
	48	SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid;
	49	userid MIN(t1.score+0.0)
	50	1 1.0
	51	2 2.0
	52	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;
	53	userid MIN(t1.score+0.0)
	54	2 2.0
	55	1 1.0
	56	EXPLAIN SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid ORDER BY NULL;
	57	id select_type table type possible_keys key key_len ref rows Extra
	58	1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where; Using temporary
	59	1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.userID 1 Using index
	60	drop table t1,t2;
	61	CREATE TABLE t1 (
520.1.18 by Brian Aker A bunch more test fixes.	62	PID int NOT NULL auto_increment,
873.1.1 by Jay Pipes Fixes the Field_date class to not allow any invalid input at	63	payDate date,
873.1.2 by Jay Pipes Fixed Field_datetime to never accept any bad datetimes as a string. This broke	64	recDate datetime,
520.1.18 by Brian Aker A bunch more test fixes.	65	URID int DEFAULT '0' NOT NULL,
	66	CRID int DEFAULT '0' NOT NULL,
	67	amount int DEFAULT '0' NOT NULL,
	68	operator int,
1 by brian clean slate	69	method enum('unknown','cash','dealer','check','card','lazy','delayed','test') DEFAULT 'unknown' NOT NULL,
520.1.18 by Brian Aker A bunch more test fixes.	70	DIID int,
1217 by Brian Aker Removed bits of charset support from the parser.	71	reason char(1) DEFAULT '' NOT NULL,
520.1.18 by Brian Aker A bunch more test fixes.	72	code_id int,
520.1.18 by Brian Aker A bunch more test fixes.	73	qty int DEFAULT '0' NOT NULL,
1 by brian clean slate	74	PRIMARY KEY (PID),
	75	KEY URID (URID),
	76	KEY reason (reason),
	77	KEY method (method),
	78	KEY payDate (payDate)
	79	);
	80	INSERT INTO t1 VALUES (1,'1970-01-01','1997-10-17 00:00:00',2529,1,21000,11886,'check',0,'F',16200,6);
	81	SELECT COUNT(P.URID),SUM(P.amount),P.method, MIN(PP.recdate+0) > 19980501000000 AS IsNew FROM t1 AS P JOIN t1 as PP WHERE P.URID = PP.URID GROUP BY method,IsNew;
	82	ERROR 42000: Can't group on 'IsNew'
	83	drop table t1;
	84	CREATE TABLE t1 (
520.1.18 by Brian Aker A bunch more test fixes.	85	cid int NOT NULL auto_increment,
1 by brian clean slate	86	firstname varchar(32) DEFAULT '' NOT NULL,
	87	surname varchar(32) DEFAULT '' NOT NULL,
	88	PRIMARY KEY (cid)
	89	);
	90	INSERT INTO t1 VALUES (1,'That','Guy');
	91	INSERT INTO t1 VALUES (2,'Another','Gent');
	92	CREATE TABLE t2 (
520.1.18 by Brian Aker A bunch more test fixes.	93	call_id int NOT NULL auto_increment,
520.1.18 by Brian Aker A bunch more test fixes.	94	contact_id int DEFAULT '0' NOT NULL,
1 by brian clean slate	95	PRIMARY KEY (call_id),
	96	KEY contact_id (contact_id)
	97	);
	98	INSERT INTO t2 VALUES (10,2);
	99	INSERT INTO t2 VALUES (18,2);
	100	INSERT INTO t2 VALUES (62,2);
	101	INSERT INTO t2 VALUES (91,2);
	102	INSERT INTO t2 VALUES (92,2);
	103	SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid;
	104	cid CONCAT(firstname, ' ', surname) COUNT(call_id)
	105	SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY NULL;
	106	cid CONCAT(firstname, ' ', surname) COUNT(call_id)
1008.1.4 by Brian Aker Removed TL_READ_HIGH_PRIORITY	107	SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY surname, firstname;
1 by brian clean slate	108	cid CONCAT(firstname, ' ', surname) COUNT(call_id)
	109	drop table t2;
	110	drop table t1;
	111	create table t1 (foo int);
	112	insert into t1 values (1);
	113	select 1+1, "a",count(*) from t1 where foo in (2);
	114	1+1 a count(*)
	115	2 a 0
	116	insert into t1 values (1);
	117	select 1+1,"a",count(*) from t1 where foo in (2);
	118	1+1 a count(*)
	119	2 a 0
	120	drop table t1;
	121	CREATE TABLE t1 (
520.1.18 by Brian Aker A bunch more test fixes.	122	spID int,
	123	userID int,
	124	score int,
1 by brian clean slate	125	key (spid),
	126	key (score)
	127	);
	128	INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3),(6,3,3),(7,3,3);
	129	explain select userid,count(*) from t1 group by userid desc;
	130	id select_type table type possible_keys key key_len ref rows Extra
	131	1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort
	132	explain select userid,count(*) from t1 group by userid desc order by null;
	133	id select_type table type possible_keys key key_len ref rows Extra
	134	1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary
	135	select userid,count(*) from t1 group by userid desc;
	136	userid count(*)
	137	3 5
	138	2 1
	139	1 2
	140	select userid,count() from t1 group by userid desc having (count()+1) IN (4,3);
	141	userid count(*)
	142	1 2
	143	select userid,count() from t1 group by userid desc having 3 IN (1,COUNT());
	144	userid count(*)
	145	explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
	146	id select_type table type possible_keys key key_len ref rows Extra
	147	1 SIMPLE t1 range spID spID 5 NULL 3 Using where; Using index
	148	explain select spid,count(*) from t1 where spid between 1 and 2 group by spid;
	149	id select_type table type possible_keys key key_len ref rows Extra
	150	1 SIMPLE t1 range spID spID 5 NULL 3 Using where; Using index
	151	explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null;
	152	id select_type table type possible_keys key key_len ref rows Extra
	153	1 SIMPLE t1 range spID spID 5 NULL 3 Using where; Using index
	154	select spid,count(*) from t1 where spid between 1 and 2 group by spid;
	155	spid count(*)
	156	1 1
	157	2 2
	158	select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
	159	spid count(*)
	160	2 2
	161	1 1
	162	explain extended select sql_big_result spid,sum(userid) from t1 group by spid desc;
	163	id select_type table type possible_keys key key_len ref rows filtered Extra
	164	1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using filesort
	165	Warnings:
520.1.18 by Brian Aker A bunch more test fixes.	166	Note 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
1 by brian clean slate	167	explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null;
	168	id select_type table type possible_keys key key_len ref rows Extra
	169	1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort
	170	select sql_big_result spid,sum(userid) from t1 group by spid desc;
	171	spid sum(userid)
	172	7 3
	173	6 3
	174	5 3
	175	4 3
	176	3 3
	177	2 3
	178	1 1
	179	explain select sql_big_result score,count(*) from t1 group by score desc;
	180	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	181	1 SIMPLE t1 index NULL score 5 NULL 8 Using index; Using filesort
1 by brian clean slate	182	explain select sql_big_result score,count(*) from t1 group by score desc order by null;
1 by brian clean slate	183	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	184	1 SIMPLE t1 index NULL score 5 NULL 8 Using index; Using filesort
1 by brian clean slate	185	select sql_big_result score,count(*) from t1 group by score desc;
	186	score count(*)
	187	3 5
	188	2 1
	189	1 2
	190	drop table t1;
	191	create table t1 (a date default null, b date default null);
	192	insert t1 values ('1999-10-01','2000-01-10'), ('1997-01-01','1998-10-01');
	193	select a,min(b) c,count(distinct rand()) from t1 group by a having c<a + interval 1 day;
	194	a c count(distinct rand())
	195	drop table t1;
	196	CREATE TABLE t1 (a char(1));
	197	INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL);
	198	SELECT a FROM t1 GROUP BY a;
	199	a
	200	NULL
	201	A
	202	B
	203	SELECT a,count(*) FROM t1 GROUP BY a;
	204	a count(*)
	205	NULL 3
	206	A 5
	207	B 5
1217 by Brian Aker Removed bits of charset support from the parser.	208	SELECT a FROM t1 GROUP BY a;
	209	a
	210	NULL
	211	A
	212	B
	213	SELECT a,count(*) FROM t1 GROUP BY a;
	214	a count(*)
	215	NULL 3
	216	A 5
	217	B 5
	218	SELECT a FROM t1 GROUP BY 1;
	219	a
	220	NULL
	221	A
	222	B
	223	SELECT a,count(*) FROM t1 GROUP BY 1;
	224	a count(*)
	225	NULL 3
	226	A 5
	227	B 5
	228	SELECT a FROM t1 GROUP BY a;
	229	a
	230	NULL
	231	A
	232	B
	233	SELECT a,count(*) FROM t1 GROUP BY a;
	234	a count(*)
	235	NULL 3
	236	A 5
	237	B 5
	238	SELECT a FROM t1 GROUP BY a;
	239	a
	240	NULL
	241	A
	242	B
	243	SELECT a,count(*) FROM t1 GROUP BY a;
	244	a count(*)
	245	NULL 3
	246	A 5
	247	B 5
	248	SELECT a FROM t1 GROUP BY 1;
	249	a
	250	NULL
	251	A
	252	B
	253	SELECT a,count(*) FROM t1 GROUP BY 1;
	254	a count(*)
	255	NULL 3
	256	A 5
	257	B 5
1 by brian clean slate	258	drop table t1;
	259	CREATE TABLE t1 (
	260	`a` char(193) default NULL,
	261	`b` char(63) default NULL
	262	);
	263	INSERT INTO t1 VALUES ('abc','def'),('hij','klm');
	264	SELECT CONCAT(a, b) FROM t1 GROUP BY 1;
	265	CONCAT(a, b)
	266	abcdef
	267	hijklm
	268	SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;
	269	CONCAT(a, b) count(*)
	270	abcdef 1
	271	hijklm 1
	272	SELECT CONCAT(a, b),count(distinct a) FROM t1 GROUP BY 1;
	273	CONCAT(a, b) count(distinct a)
	274	abcdef 1
	275	hijklm 1
	276	SELECT 1 FROM t1 GROUP BY CONCAT(a, b);
	277	1
	278	1
	279	1
	280	INSERT INTO t1 values ('hij','klm');
	281	SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;
	282	CONCAT(a, b) count(*)
	283	abcdef 1
	284	hijklm 2
	285	DROP TABLE t1;
520.1.18 by Brian Aker A bunch more test fixes.	286	create table t1 (One int, Two int, Three int, Four int);
1 by brian clean slate	287	insert into t1 values (1,2,1,4),(1,2,2,4),(1,2,3,4),(1,2,4,4),(1,1,1,4),(1,1,2,4),(1,1,3,4),(1,1,4,4),(1,3,1,4),(1,3,2,4),(1,3,3,4),(1,3,4,4);
	288	select One, Two, sum(Four) from t1 group by One,Two;
	289	One Two sum(Four)
	290	1 1 16
	291	1 2 16
	292	1 3 16
	293	drop table t1;
	294	create table t1 (id integer primary key not null auto_increment, gender char(1));
	295	insert into t1 values (NULL, 'M'), (NULL, 'F'),(NULL, 'F'),(NULL, 'F'),(NULL, 'M');
	296	create table t2 (user_id integer not null, date date);
	297	insert into t2 values (1, '2002-06-09'),(2, '2002-06-09'),(1, '2002-06-09'),(3, '2002-06-09'),(4, '2002-06-09'),(4, '2002-06-09');
	298	select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender;
	299	gender dist_count percentage
	300	F 3 60.0000
	301	M 1 20.0000
	302	select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender order by percentage;
	303	gender dist_count percentage
	304	M 1 20.0000
	305	F 3 60.0000
	306	drop table t1,t2;
	307	CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID
	308	));
	309	insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL);
	310	select S.ID as xID, S.ID1 as xID1 from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2;
	311	xID xID1
	312	1 1
	313	2 2
	314	2 2
	315	3 134
	316	3 134
	317	3 134
	318	4 185
	319	4 185
	320	4 185
	321	4 185
	322	select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2 group by xID order by xID1;
	323	xID xID1 Level
	324	1 1 *
	325	2 2 **
	326	3 134 ***
	327	4 185 ****
	328	drop table t1;
1063.9.3 by Brian Aker Partial fix for tests for tmp	329	CREATE TEMPORARY TABLE t1 (
520.1.18 by Brian Aker A bunch more test fixes.	330	pid int NOT NULL default '0',
	331	c1id int default NULL,
	332	c2id int default NULL,
	333	value int NOT NULL default '0',
1 by brian clean slate	334	UNIQUE KEY pid2 (pid,c1id,c2id),
	335	UNIQUE KEY pid (pid,value)
	336	) ENGINE=MyISAM;
	337	INSERT INTO t1 VALUES (1, 1, NULL, 1),(1, 2, NULL, 2),(1, NULL, 3, 3),(1, 4, NULL, 4),(1, 5, NULL, 5);
1063.9.3 by Brian Aker Partial fix for tests for tmp	338	CREATE TEMPORARY TABLE t2 (
520.1.18 by Brian Aker A bunch more test fixes.	339	id int NOT NULL default '0',
1 by brian clean slate	340	active enum('Yes','No') NOT NULL default 'Yes',
	341	PRIMARY KEY (id)
	342	) ENGINE=MyISAM;
	343	INSERT INTO t2 VALUES (1, 'Yes'),(2, 'No'),(4, 'Yes'),(5, 'No');
	344	CREATE TABLE t3 (
520.1.18 by Brian Aker A bunch more test fixes.	345	id int NOT NULL default '0',
1 by brian clean slate	346	active enum('Yes','No') NOT NULL default 'Yes',
	347	PRIMARY KEY (id)
	348	);
	349	INSERT INTO t3 VALUES (3, 'Yes');
	350	select * from t1 AS m LEFT JOIN t2 AS c1 ON m.c1id =
	351	c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = c2.id AND
	352	c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS NOT NULL);
	353	pid c1id c2id value id active id active
	354	1 1 NULL 1 1 Yes NULL NULL
	355	1 NULL 3 3 NULL NULL 3 Yes
	356	1 4 NULL 4 4 Yes NULL NULL
	357	select max(value) from t1 AS m LEFT JOIN t2 AS c1 ON
	358	m.c1id = c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id =
	359	c2.id AND c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS
	360	NOT NULL);
	361	max(value)
	362	4
	363	drop table t1,t2,t3;
	364	create table t1 (a blob null);
	365	insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(""),(""),(""),("b");
	366	select a,count(*) from t1 group by a;
	367	a count(*)
	368	NULL 9
	369	3
	370	b 1
	371	select a,count(*) from t1 group by a;
	372	a count(*)
	373	NULL 9
	374	3
	375	b 1
	376	drop table t1;
	377	create table t1 (a int not null, b int not null);
	378	insert into t1 values (1,1),(1,2),(3,1),(3,2),(2,2),(2,1);
	379	create table t2 (a int not null, b int not null, key(a));
	380	insert into t2 values (1,3),(3,1),(2,2),(1,1);
	381	select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
	382	a b
	383	1 1
	384	1 3
	385	2 2
	386	3 1
	387	select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
	388	a b
	389	1 3
	390	3 1
	391	2 2
	392	1 1
	393	explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
	394	id select_type table type possible_keys key key_len ref rows Extra
	395	1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
	396	1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where; Using join buffer
	397	explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
	398	id select_type table type possible_keys key key_len ref rows Extra
	399	1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary
	400	1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where; Using join buffer
	401	drop table t1,t2;
	402	create table t1 (a int, b int);
	403	insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
	404	select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a;
	405	a MAX(b) INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000)
	406	1 4 2
	407	10 43 6
	408	select a, MAX(b), CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end from t1 group by a;
	409	a MAX(b) CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end
410	1 4 4
411	10 43 43
412	select a, MAX(b), FIELD(MAX(b), '43', '4', '5') from t1 group by a;
413	a MAX(b) FIELD(MAX(b), '43', '4', '5')
414	1 4 2
415	10 43 1
416	select a, MAX(b), CONCAT_WS(MAX(b), '43', '4', '5') from t1 group by a;
417	a MAX(b) CONCAT_WS(MAX(b), '43', '4', '5')
418	1 4 434445
419	10 43 43434435
420	select a, MAX(b), ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f') from t1 group by a;
421	a MAX(b) ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f')
422	1 4 d
423	10 43 NULL
424	select a, MAX(b), MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') from t1 group by a;
425	a MAX(b) MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h')
426	1 4 c
427	10 43 a,b,d,f
428	drop table t1;
429	create table t1 (id int not null, qty int not null);
430	insert into t1 values (1,2),(1,3),(2,4),(2,5);
431	select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and cqty>1;
432	id sqty cqty
433	1 5 2
434	2 9 2
435	select id, sum(qty) as sqty from t1 group by id having sqty>2 and count(qty)>1;
436	id sqty
437	1 5
438	2 9
439	select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sqty>2 and cqty>1;
440	id sqty cqty
441	1 5 2
442	2 9 2
443	select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and count(qty)>1;
444	id sqty cqty
445	1 5 2
446	2 9 2
447	select count(*), case interval(qty,2,3,4,5,6,7,8) when -1 then NULL when 0 then "zero" when 1 then "one" when 2 then "two" end as category from t1 group by category;
448	count(*) category
449	2 NULL
450	1 one
451	1 two
452	select count(*), interval(qty,2,3,4,5,6,7,8) as category from t1 group by category;
453	count(*) category
454	1 1
455	1 2
456	1 3
457	1 4
458	drop table t1;
459	CREATE TABLE t1 (
520.1.18 by Brian Aker A bunch more test fixes.	460	userid int,
520.1.18 by Brian Aker A bunch more test fixes.	461	score int,
1 by brian clean slate	462	key (score)
	463	);
	464	INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3);
	465	SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
	466	userid count(*)
	467	3 5
	468	2 1
	469	1 2
	470	EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
	471	id select_type table type possible_keys key key_len ref rows Extra
	472	1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort
	473	DROP TABLE t1;
	474	CREATE TABLE t1 (
520.1.18 by Brian Aker A bunch more test fixes.	475	i int default NULL,
520.1.18 by Brian Aker A bunch more test fixes.	476	j int default NULL
1 by brian clean slate	477	);
	478	INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5);
	479	SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
	480	i COUNT(DISTINCT(i))
	481	1 1
	482	2 1
	483	4 4
	484	explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
	485	id select_type table type possible_keys key key_len ref rows Extra
	486	1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
	487	DROP TABLE t1;
	488	create table t1 (a int);
	489	insert into t1 values(null);
	490	select min(a) is null from t1;
	491	min(a) is null
	492	1
	493	select min(a) is null or null from t1;
	494	min(a) is null or null
	495	1
	496	select 1 and min(a) is null from t1;
	497	1 and min(a) is null
	498	1
	499	drop table t1;
	500	create table t1 ( col1 int, col2 int );
	501	insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
	502	select group_concat( distinct col1 ) as alias from t1
	503	group by col2 having alias like '%';
	504	alias
	505	1,2
	506	1,2
	507	1
	508	drop table t1;
	509	create table t1 (a integer, b integer, c integer);
	510	insert into t1 (a,b) values (1,2),(1,3),(2,5);
	511	select a, 0.1*0+1 r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2=1;
	512	a r2 r1
	513	1 1.0 2
	514	select a, round(rand(100)*10) r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2<=2;
	515	a r2 r1
	516	1 2 2
	517	select a,sum(b) from t1 where a=1 group by c;
	518	a sum(b)
	519	1 5
	520	select a*sum(b) from t1 where a=1 group by c;
	521	a*sum(b)
	522	5
	523	select sum(a)*sum(b) from t1 where a=1 group by c;
	524	sum(a)*sum(b)
	525	10
	526	select a,sum(b) from t1 where a=1 group by c having a=1;
	527	a sum(b)
	528	1 5
	529	select a as d,sum(b) from t1 where a=1 group by c having d=1;
	530	d sum(b)
	531	1 5
	532	select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;
	533	d
	534	10
	535	drop table t1;
	536	create table t1(a int);
	537	insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9);
	538	create table t2 (
	539	a int,
	540	b varchar(200) NOT NULL,
541	c varchar(50) NOT NULL,
542	d varchar(100) NOT NULL,
543	primary key (a,b(132),c,d),
544	key a (a,b)
520.1.18 by Brian Aker A bunch more test fixes.	545	);
	546	Warnings:
	547	Warning 1071 Specified key was too long; max key length is 767 bytes
1 by brian clean slate	548	insert into t2 select
	549	x3.a, -- 3
	550	concat('val-', x3.a + 3*x4.a), -- 12
	551	concat('val-', @a:=x3.a + 3x4.a + 12C.a), -- 120
	552	concat('val-', @a + 120*D.a)
	553	from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4;
	554	delete from t2 where a = 2 and b = 'val-2' order by a,b,c,d limit 30;
	555	explain select c from t2 where a = 2 and b = 'val-2' group by c;
	556	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	557	1 SIMPLE t2 ref PRIMARY,a a 770 const,const 36 Using where; Using index
1 by brian clean slate	558	select c from t2 where a = 2 and b = 'val-2' group by c;
	559	c
	560	val-74
	561	val-98
	562	drop table t1,t2;
520.1.18 by Brian Aker A bunch more test fixes.	563	create table t1 (b int4 not null);
520.1.18 by Brian Aker A bunch more test fixes.	564	insert into t1 values(300000);
1 by brian clean slate	565	select * from t1;
1 by brian clean slate	566	b
520.1.18 by Brian Aker A bunch more test fixes.	567	300000
1 by brian clean slate	568	select min(b) from t1;
1 by brian clean slate	569	min(b)
520.1.18 by Brian Aker A bunch more test fixes.	570	300000
1 by brian clean slate	571	drop table t1;
	572	CREATE TABLE t1 (id int PRIMARY KEY, user_id int, hostname longtext);
	573	INSERT INTO t1 VALUES
	574	(1, 7, 'cache-dtc-af05.proxy.aol.com'),
	575	(2, 3, 'what.ever.com'),
	576	(3, 7, 'cache-dtc-af05.proxy.aol.com'),
	577	(4, 7, 'cache-dtc-af05.proxy.aol.com');
	578	SELECT hostname, COUNT(DISTINCT user_id) as no FROM t1
	579	WHERE hostname LIKE '%aol%'
	580	GROUP BY hostname;
	581	hostname no
	582	cache-dtc-af05.proxy.aol.com 1
	583	DROP TABLE t1;
	584	CREATE TABLE t1 (a int, b int);
	585	INSERT INTO t1 VALUES (1,2), (1,3);
	586	SELECT a, b FROM t1 GROUP BY 'const';
	587	a b
	588	1 2
	589	SELECT DISTINCT a, b FROM t1 GROUP BY 'const';
	590	a b
	591	1 2
	592	DROP TABLE t1;
	593	CREATE TABLE t1 (id INT, dt DATETIME);
	594	INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
	595	INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
	596	INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
	597	INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
	598	SELECT dt DIV 1 AS f, id FROM t1 GROUP BY f;
	599	f id
	600	20050501123000 1
	601	DROP TABLE t1;
	602	CREATE TABLE t1 (id varchar(20) NOT NULL);
	603	INSERT INTO t1 VALUES ('trans1'), ('trans2');
	604	CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL);
	605	INSERT INTO t2 VALUES ('trans1', 'a problem');
	606	SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment
	607	FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment;
	608	COUNT(DISTINCT(t1.id)) comment
	609	1 NULL
	610	1 a problem
	611	DROP TABLE t1, t2;
	612	create table t1 (f1 date);
	613	insert into t1 values('2005-06-06');
	614	insert into t1 values('2005-06-06');
	615	select date(left(f1+0,8)) from t1 group by 1;
	616	date(left(f1+0,8))
	617	2005-06-06
	618	drop table t1;
	619	CREATE TABLE t1 (n int);
	620	INSERT INTO t1 VALUES (1);
	621	SELECT n+1 AS n FROM t1 GROUP BY n;
	622	n
	623	2
	624	Warnings:
	625	Warning 1052 Column 'n' in group statement is ambiguous
	626	DROP TABLE t1;
	627	create table t1(f1 varchar(5) key);
	628	insert into t1 values (1),(2);
	629	select sql_buffer_result max(f1) is null from t1;
	630	max(f1) is null
	631	0
	632	select sql_buffer_result max(f1)+1 from t1;
	633	max(f1)+1
	634	3
635	drop table t1;
636	CREATE TABLE t1(a INT);
637	INSERT INTO t1 VALUES (1),(2);
638	SELECT a FROM t1 GROUP BY 'a';
639	a
640	1
641	SELECT a FROM t1 GROUP BY "a";
642	a
643	1
644	SELECT a FROM t1 GROUP BY `a`;
645	a
646	1
647	2
648	SELECT a FROM t1 GROUP BY "a";
649	a
650	1
651	SELECT a FROM t1 GROUP BY 'a';
652	a
653	1
654	SELECT a FROM t1 GROUP BY `a`;
655	a
656	1
657	2
658	SELECT a FROM t1 HAVING 'a' > 1;
659	a
660	Warnings:
661	Warning 1292 Truncated incorrect DOUBLE value: 'a'
662	SELECT a FROM t1 HAVING "a" > 1;
663	a
664	Warnings:
665	Warning 1292 Truncated incorrect DOUBLE value: 'a'
666	SELECT a FROM t1 HAVING `a` > 1;
667	a
668	2
669	SELECT a FROM t1 ORDER BY 'a' DESC;
670	a
671	1
672	2
673	SELECT a FROM t1 ORDER BY "a" DESC;
674	a
675	1
676	2
677	SELECT a FROM t1 ORDER BY `a` DESC;
678	a
679	2
680	1
681	DROP TABLE t1;
682	CREATE TABLE t1 (
520.1.18 by Brian Aker A bunch more test fixes.	683	f1 int NOT NULL auto_increment primary key,
1 by brian clean slate	684	f2 varchar(100) NOT NULL default ''
	685	);
	686	CREATE TABLE t2 (
	687	f1 varchar(10) NOT NULL default '',
	688	f2 char(3) NOT NULL default '',
	689	PRIMARY KEY (`f1`),
	690	KEY `k1` (`f2`,`f1`)
	691	);
	692	INSERT INTO t1 values(NULL, '');
	693	INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
	694	SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
	695	avg(t2.f1)
	696	SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
	697	avg(t2.f1)
	698	DROP TABLE t1, t2;
	699	create table t1 (c1 char(3), c2 char(3));
	700	create table t2 (c3 char(3), c4 char(3));
	701	insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
	702	insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
	703	select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
	704	group by c2;
	705	c2
	706	aaa
	707	aaa
	708	Warnings:
	709	Warning 1052 Column 'c2' in group statement is ambiguous
	710	show warnings;
	711	Level Code Message
	712	Warning 1052 Column 'c2' in group statement is ambiguous
	713	select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
	714	group by t1.c1;
	715	c2
	716	aaa
	717	show warnings;
	718	Level Code Message
	719	drop table t1, t2;
	720	CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));
	721	INSERT INTO t1 VALUES (1, 1);
	722	INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1;
	723	INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1;
	724	INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1;
	725	INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1;
	726	INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1;
	727	INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1;
	728	INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1;
	729	SELECT MIN(b), MAX(b) from t1;
	730	MIN(b) MAX(b)
	731	0 19
	732	EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;
	733	id select_type table type possible_keys key key_len ref rows Extra
	734	1 SIMPLE t1 index NULL b 5 NULL 128 Using index
	735	EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
	736	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	737	1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using filesort
1 by brian clean slate	738	SELECT b, sum(1) FROM t1 GROUP BY b;
	739	b sum(1)
	740	0 6
	741	1 7
	742	2 7
	743	3 7
	744	4 7
	745	5 7
	746	6 7
	747	7 7
	748	8 7
	749	9 6
	750	10 6
	751	11 6
	752	12 6
	753	13 6
	754	14 6
	755	15 6
	756	16 6
	757	17 6
	758	18 6
	759	19 6
	760	SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
	761	b sum(1)
	762	0 6
	763	1 7
	764	2 7
	765	3 7
	766	4 7
	767	5 7
	768	6 7
	769	7 7
	770	8 7
	771	9 6
	772	10 6
	773	11 6
	774	12 6
	775	13 6
	776	14 6
	777	15 6
	778	16 6
	779	17 6
	780	18 6
	781	19 6
	782	DROP TABLE t1;
	783	CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
	784	INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
	785	SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b;
	786	MAX(a)-MIN(a)
	787	1
	788	1
	789	1
	790	SELECT CEILING(MIN(a)) FROM t1 GROUP BY b;
	791	CEILING(MIN(a))
	792	1
	793	3
	794	5
	795	SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1
	796	GROUP BY b;
	797	CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END
	798	Positive
	799	Positive
	800	Positive
	801	SELECT a + 1 FROM t1 GROUP BY a;
802	a + 1
803	2
804	3
805	4
806	5
807	6
808	7
809	SELECT a + b FROM t1 GROUP BY b;
810	a + b
811	2
812	5
813	8
814	SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
815	FROM t1 AS t1_outer;
816	(SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
817	1
818	2
819	3
820	4
821	5
822	6
823	SELECT 1 FROM t1 as t1_outer GROUP BY a
824	HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
825	1
826	1
827	1
828	1
829	1
830	1
831	1
832	SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1)
833	FROM t1 AS t1_outer GROUP BY t1_outer.b;
834	(SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1)
835	1
836	3
837	5
838	SELECT 1 FROM t1 as t1_outer GROUP BY a
839	HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
840	ERROR 42S22: Unknown column 'test.t1_outer.b' in 'field list'
841	SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
842	FROM t1 AS t1_outer GROUP BY t1_outer.b;
843	(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
844	21
845	21
846	21
847	SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
848	FROM t1 AS t1_outer;
849	(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
850	3
851	3
852	3
853	3
854	3
855	3
856	SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1)
857	FROM t1 AS t1_outer GROUP BY t1_outer.b;
858	(SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1)
859	3
860	7
861	11
862	SELECT 1 FROM t1 as t1_outer
863	WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1);
864	1
865	1
866	1
867	1
868	1
869	1
870	1
871	SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0;
872	b
873	1
874	2
875	3
876	SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12;
877	1
878	1
879	1
880	SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1);
881	1
882	1
883	SELECT 1 FROM t1 GROUP BY b HAVING a = 2;
884	ERROR 42S22: Unknown column 'a' in 'having clause'
885	SELECT 1 FROM t1 GROUP BY SUM(b);
886	ERROR HY000: Invalid use of group function
887	SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN
888	(SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a
889	HAVING SUM(t1_inner.b)+t1_outer.b > 5);
890	b
891	3
892	DROP TABLE t1;
893	create table t1(f1 int, f2 int);
894	select * from t1 group by f1;
895	f1 f2
896	select * from t1 group by f2;
897	f1 f2
898	select * from t1 group by f1, f2;
899	f1 f2
900	select t1.f1,t.* from t1, t1 t group by 1;
901	f1 f1 f2
902	drop table t1;
903	CREATE TABLE t1(
904	id INT AUTO_INCREMENT PRIMARY KEY,
905	c1 INT NOT NULL,
906	c2 INT NOT NULL,
907	UNIQUE KEY (c2,c1));
908	INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
909	SELECT * FROM t1 ORDER BY c1;
910	id c1 c2
911	5 1 3
912	4 2 3
913	3 3 5
914	2 4 1
915	1 5 1
916	SELECT * FROM t1 GROUP BY id ORDER BY c1;
917	id c1 c2
918	5 1 3
919	4 2 3
920	3 3 5
921	2 4 1
922	1 5 1
923	SELECT * FROM t1 GROUP BY id ORDER BY id DESC;
924	id c1 c2
925	5 1 3
926	4 2 3
927	3 3 5
928	2 4 1
929	1 5 1
930	SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;
931	id c1 c2
932	2 4 1
933	1 5 1
934	5 1 3
935	4 2 3
936	3 3 5
937	SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;
938	id c1 c2
939	3 3 5
940	5 1 3
941	4 2 3
942	2 4 1
943	1 5 1
944	SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;
945	id c1 c2
946	3 3 5
947	4 2 3
948	5 1 3
949	1 5 1
950	2 4 1
951	SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1;
952	id c1 c2
520.1.18 by Brian Aker A bunch more test fixes.	953	2 4 1
520.1.18 by Brian Aker A bunch more test fixes.	954	5 1 3
1 by brian clean slate	955	3 3 5
	956	SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1;
	957	id c1 c2
	958	3 3 5
	959	4 2 3
	960	1 5 1
	961	SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC;
	962	id c1 c2
	963	3 3 5
	964	4 2 3
	965	1 5 1
	966	DROP TABLE t1;
	967	#
	968	# Bug#27219: Aggregate functions in ORDER BY.
	969	#
	970	CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
	971	INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
	972	CREATE TABLE t2 SELECT * FROM t1;
	973	SELECT 1 FROM t1 ORDER BY COUNT(*);
	974	1
	975	1
	976	SELECT 1 FROM t1 ORDER BY COUNT(*) + 1;
	977	1
	978	1
	979	SELECT 1 FROM t1 ORDER BY COUNT(*) + a;
	980	1
	981	1
	982	SELECT 1 FROM t1 ORDER BY COUNT(*), 1;
	983	1
	984	1
	985	SELECT 1 FROM t1 ORDER BY COUNT(*), a;
	986	1
	987	1
	988	SELECT 1 FROM t1 ORDER BY SUM(a);
	989	1
	990	1
	991	SELECT 1 FROM t1 ORDER BY SUM(a + 1);
	992	1
	993	1
	994	SELECT 1 FROM t1 ORDER BY SUM(a) + 1;
	995	1
	996	1
	997	SELECT 1 FROM t1 ORDER BY SUM(a), b;
	998	1
	999	1
	1000	SELECT a FROM t1 ORDER BY COUNT(b);
	1001	a
	1002	3
	1003	SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2);
	1004	a
	1005	3
	1006	2
	1007	3
	1008	2
	1009	3
	1010	4
	1011	SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a);
	1012	a
	1013	3
	1014	2
	1015	3
	1016	2
	1017	3
	1018	4
1019	SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
1020	a
1021	3
1022	2
1023	3
1024	2
1025	3
1026	4
1027	SELECT t1.a FROM t1
1028	WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
1029	a
1030	3
1031	3
1032	3
1033	SELECT t1.a FROM t1 GROUP BY t1.a
1034	HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1035	a
1036	3
1037	SELECT t1.a FROM t1 GROUP BY t1.a
1038	HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
1039	a
1040	2
1041	3
1042	4
1043	SELECT t1.a FROM t1 GROUP BY t1.a
1044	HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
1045	a
1046	3
1047	SELECT t1.a FROM t1 GROUP BY t1.a
1048	HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
1049	a
1050	4
1051	SELECT t1.a FROM t1
1052	WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
1053	a
1054	3
1055	3
1056	3
1057	SELECT 1 FROM t1 GROUP BY t1.a
1058	HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1059	1
1060	1
1061	1
1062	1
1063	SELECT 1 FROM t1 GROUP BY t1.a
1064	HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1065	1
1066	1
1067	1
1068	1
1069	SELECT 1 FROM t1 GROUP BY t1.a
1070	HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1071	1
1072	1
1073	1
1074	1
1075	SELECT 1 FROM t1 GROUP BY t1.a
1076	HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1);
1077	1
1078	1
1079	1
1080	1
1081	SELECT 1 FROM t1 GROUP BY t1.a
1082	HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
1083	1
1084	1
1085	1
1086	1
1087	SELECT 1 FROM t1 GROUP BY t1.a
1088	HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
1089	1
1090	1
1091	1
1092	1
1093	SELECT t1.a FROM t1
1094	WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a
1095	ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1);
1096	a
1097	4
1098	SELECT t1.a, SUM(t1.b) FROM t1
1099	WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a
1100	ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1)
1101	GROUP BY t1.a;
1102	a SUM(t1.b)
1103	4 4
1104	SELECT t1.a, SUM(t1.b) FROM t1
1105	WHERE t1.a = (SELECT SUM(t2.b) FROM t2
1106	ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1)
1107	GROUP BY t1.a;
1108	a SUM(t1.b)
1109	SELECT t1.a, SUM(t1.b) FROM t1
1110	WHERE t1.a = (SELECT SUM(t2.b) FROM t2
1111	ORDER BY SUM(t2.b + t1.a) LIMIT 1)
1112	GROUP BY t1.a;
1113	a SUM(t1.b)
1114	SELECT t1.a FROM t1 GROUP BY t1.a
1115	HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);
1116	a
1117	select avg (
1118	(select
1119	(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
1120	from t1 as outr order by outr.a limit 1))
1121	from t1 as most_outer;
1122	avg (
1123	(select
1124	(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
1125	from t1 as outr order by outr.a limit 1))
1126	29.0000
1127	select avg (
1128	(select (
1129	(select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt
1130	from t1 as outr order by count(outr.a) limit 1)) as tt
1131	from t1 as most_outer;
1132	tt
1133	35.0000
1134	select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a;
1135	tt
1136	29
1137	29
1138	35
1139	35
1140	35
1141	41
1142	DROP TABLE t1, t2;
1143	End of 5.0 tests
1144	CREATE TABLE t1 (a INT, b INT,
1145	PRIMARY KEY (a),
1146	KEY i2(a,b));
1147	INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
1148	INSERT INTO t1 SELECT a + 8,b FROM t1;
1149	INSERT INTO t1 SELECT a + 16,b FROM t1;
1150	INSERT INTO t1 SELECT a + 32,b FROM t1;
1151	INSERT INTO t1 SELECT a + 64,b FROM t1;
1152	INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16;
1153	ANALYZE TABLE t1;
1154	Table Op Msg_type Msg_text
1155	test.t1 analyze status OK
1156	EXPLAIN SELECT a FROM t1 WHERE a < 2;
1157	id select_type table type possible_keys key key_len ref rows Extra
1158	1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 1 Using where; Using index
1159	EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
1160	id select_type table type possible_keys key key_len ref rows Extra
1161	1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 1 Using where; Using index
1162	EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
1163	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	1164	1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 1 Using where; Using index for group-by
1 by brian clean slate	1165	EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
	1166	id select_type table type possible_keys key key_len ref rows Extra
	1167	1 SIMPLE t1 ALL NULL NULL NULL NULL 144
	1168	EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
	1169	id select_type table type possible_keys key key_len ref rows Extra
	1170	1 SIMPLE t1 ALL NULL NULL NULL NULL 144
	1171	EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
	1172	id select_type table type possible_keys key key_len ref rows Extra
	1173	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index
	1174	EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
	1175	id select_type table type possible_keys key key_len ref rows Extra
	1176	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index
	1177	SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
	1178	a
	1179	1
	1180	2
	1181	3
	1182	4
	1183	5
	1184	6
	1185	7
	1186	8
	1187	9
	1188	10
	1189	11
	1190	12
	1191	13
	1192	14
	1193	15
	1194	16
	1195	17
	1196	18
	1197	19
	1198	20
	1199	21
	1200	22
	1201	23
	1202	24
	1203	25
	1204	26
	1205	27
	1206	28
	1207	29
	1208	30
	1209	31
	1210	32
	1211	33
	1212	34
	1213	35
	1214	36
	1215	37
	1216	38
	1217	39
	1218	40
	1219	41
	1220	42
	1221	43
	1222	44
	1223	45
	1224	46
	1225	47
	1226	48
	1227	49
	1228	50
1229	51
1230	52
1231	53
1232	54
1233	55
1234	56
1235	57
1236	58
1237	59
1238	60
1239	61
1240	62
1241	63
1242	64
1243	65
1244	66
1245	67
1246	68
1247	69
1248	70
1249	71
1250	72
1251	73
1252	74
1253	75
1254	76
1255	77
1256	78
1257	79
1258	80
1259	81
1260	82
1261	83
1262	84
1263	85
1264	86
1265	87
1266	88
1267	89
1268	90
1269	91
1270	92
1271	93
1272	94
1273	95
1274	96
1275	97
1276	98
1277	99
1278	100
1279	101
1280	102
1281	103
1282	104
1283	105
1284	106
1285	107
1286	108
1287	109
1288	110
1289	111
1290	112
1291	113
1292	114
1293	115
1294	116
1295	117
1296	118
1297	119
1298	120
1299	121
1300	122
1301	123
1302	124
1303	125
1304	126
1305	127
1306	128
1307	129
1308	130
1309	131
1310	132
1311	133
1312	134
1313	135
1314	136
1315	137
1316	138
1317	139
1318	140
1319	141
1320	142
1321	143
1322	144
1323	EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
1324	IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
1325	id select_type table type possible_keys key key_len ref rows Extra
1326	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index
1327	EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
1328	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	1329	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144
1 by brian clean slate	1330	EXPLAIN SELECT a FROM t1 FORCE INDEX (i2);
1 by brian clean slate	1331	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	1332	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144
1 by brian clean slate	1333	EXPLAIN SELECT a FROM t1 USE INDEX ();
	1334	id select_type table type possible_keys key key_len ref rows Extra
	1335	1 SIMPLE t1 ALL NULL NULL NULL NULL 144
	1336	EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);
	1337	id select_type table type possible_keys key key_len ref rows Extra
	1338	1 SIMPLE t1 ALL NULL NULL NULL NULL 144
	1339	EXPLAIN SELECT a FROM t1
	1340	FORCE INDEX (PRIMARY)
	1341	IGNORE INDEX FOR GROUP BY (i2)
	1342	IGNORE INDEX FOR ORDER BY (i2)
	1343	USE INDEX (i2);
	1344	ERROR HY000: Incorrect usage of USE INDEX and FORCE INDEX
	1345	EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
	1346	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	1347	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144
1 by brian clean slate	1348	EXPLAIN SELECT a FROM t1 FORCE INDEX ();
629.2.6 by Monty Updated test output with new and improved error messages.	1349	ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ')' at line 1
1 by brian clean slate	1350	EXPLAIN SELECT a FROM t1 IGNORE INDEX ();
629.2.6 by Monty Updated test output with new and improved error messages.	1351	ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ')' at line 1
1 by brian clean slate	1352	EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2)
	1353	USE INDEX FOR GROUP BY (i2) GROUP BY a;
	1354	id select_type table type possible_keys key key_len ref rows Extra
	1355	1 SIMPLE t1 # NULL i2 # NULL # #
	1356	EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2)
	1357	FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
	1358	id select_type table type possible_keys key key_len ref rows Extra
	1359	1 SIMPLE t1 range NULL i2 4 NULL 145 Using index for group-by
	1360	EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
	1361	id select_type table type possible_keys key key_len ref rows Extra
	1362	1 SIMPLE t1 ALL NULL NULL NULL NULL 144
	1363	EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX ();
	1364	id select_type table type possible_keys key key_len ref rows Extra
	1365	1 SIMPLE t1 ALL NULL NULL NULL NULL 144
	1366	EXPLAIN SELECT a FROM t1
	1367	USE INDEX FOR GROUP BY (i2)
	1368	USE INDEX FOR ORDER BY (i2)
	1369	USE INDEX FOR JOIN (i2);
	1370	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	1371	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144
1 by brian clean slate	1372	EXPLAIN SELECT a FROM t1
	1373	USE INDEX FOR JOIN (i2)
	1374	USE INDEX FOR JOIN (i2)
	1375	USE INDEX FOR JOIN (i2,i2);
	1376	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	1377	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144
1 by brian clean slate	1378	EXPLAIN SELECT 1 FROM t1 WHERE a IN
	1379	(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
	1380	id select_type table type possible_keys key key_len ref rows Extra
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	1381	1 PRIMARY t1 index NULL PRIMARY 4 NULL 144 Using where; Using index
	1382	2 SUBQUERY t1 ALL NULL NULL NULL NULL 144
1 by brian clean slate	1383	CREATE TABLE t2 (a INT, b INT, KEY(a));
	1384	INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
	1385	EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
	1386	id select_type table type possible_keys key key_len ref rows Extra
	1387	1 SIMPLE t2 index NULL a 5 NULL 2
	1388	EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
	1389	id select_type table type possible_keys key key_len ref rows Extra
	1390	1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
	1391	EXPLAIN SELECT 1 FROM t2 WHERE a IN
	1392	(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
	1393	id select_type table type possible_keys key key_len ref rows Extra
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	1394	1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
	1395	2 SUBQUERY t1 ALL NULL NULL NULL NULL 144
1 by brian clean slate	1396	DROP TABLE t1, t2;
	1397	CREATE TABLE t1(
	1398	a INT,
	1399	b INT NOT NULL,
	1400	c INT NOT NULL,
	1401	d INT,
	1402	UNIQUE KEY (c,b)
	1403	);
	1404	INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
	1405	CREATE TABLE t2(
	1406	a INT,
	1407	b INT,
	1408	UNIQUE KEY(a,b)
	1409	);
	1410	INSERT INTO t2 VALUES (NULL, NULL), (NULL, NULL), (NULL, 1), (1, NULL), (1, 1), (1,2);
	1411	EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
	1412	id select_type table type possible_keys key key_len ref rows Extra
	1413	1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
	1414	SELECT c,b,d FROM t1 GROUP BY c,b,d;
	1415	c b d
	1416	1 1 50
	1417	3 1 4
	1418	3 2 40
	1419	EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
	1420	id select_type table type possible_keys key key_len ref rows Extra
	1421	1 SIMPLE t1 ALL NULL NULL NULL NULL 3
	1422	SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
	1423	c b d
	1424	1 1 50
520.1.18 by Brian Aker A bunch more test fixes.	1425	3 1 4
1 by brian clean slate	1426	3 2 40
	1427	EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
	1428	id select_type table type possible_keys key key_len ref rows Extra
	1429	1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
	1430	SELECT c,b,d FROM t1 ORDER BY c,b,d;
	1431	c b d
	1432	1 1 50
	1433	3 1 4
	1434	3 2 40
	1435	EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
	1436	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	1437	1 SIMPLE t1 index NULL c 8 NULL 3
1 by brian clean slate	1438	SELECT c,b,d FROM t1 GROUP BY c,b;
	1439	c b d
	1440	1 1 50
	1441	3 1 4
	1442	3 2 40
	1443	EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
	1444	id select_type table type possible_keys key key_len ref rows Extra
	1445	1 SIMPLE t1 index NULL c 8 NULL 3 Using index
	1446	SELECT c,b FROM t1 GROUP BY c,b;
	1447	c b
	1448	1 1
	1449	3 1
	1450	3 2
	1451	EXPLAIN SELECT a,b from t2 ORDER BY a,b;
	1452	id select_type table type possible_keys key key_len ref rows Extra
	1453	1 SIMPLE t2 index NULL a 10 NULL 6 Using index
	1454	SELECT a,b from t2 ORDER BY a,b;
	1455	a b
	1456	NULL NULL
	1457	NULL NULL
	1458	NULL 1
	1459	1 NULL
	1460	1 1
	1461	1 2
	1462	EXPLAIN SELECT a,b from t2 GROUP BY a,b;
	1463	id select_type table type possible_keys key key_len ref rows Extra
	1464	1 SIMPLE t2 index NULL a 10 NULL 6 Using index
	1465	SELECT a,b from t2 GROUP BY a,b;
	1466	a b
	1467	NULL NULL
	1468	NULL 1
	1469	1 NULL
	1470	1 1
	1471	1 2
	1472	EXPLAIN SELECT a from t2 GROUP BY a;
	1473	id select_type table type possible_keys key key_len ref rows Extra
	1474	1 SIMPLE t2 index NULL a 10 NULL 6 Using index
	1475	SELECT a from t2 GROUP BY a;
	1476	a
	1477	NULL
	1478	1
	1479	EXPLAIN SELECT b from t2 GROUP BY b;
	1480	id select_type table type possible_keys key key_len ref rows Extra
	1481	1 SIMPLE t2 index NULL a 10 NULL 6 Using index; Using temporary; Using filesort
	1482	SELECT b from t2 GROUP BY b;
	1483	b
	1484	NULL
	1485	1
	1486	2
1119.4.5 by Stewart Smith make group_by test not leave tables behind after running	1487	DROP TABLE t1,t2;
1 by brian clean slate	1488	CREATE TABLE t1 ( a INT, b INT );
	1489	SELECT b c, (SELECT a FROM t1 WHERE b = c)
	1490	FROM t1;
	1491	c (SELECT a FROM t1 WHERE b = c)
	1492	SELECT b c, (SELECT a FROM t1 WHERE b = c)
	1493	FROM t1
	1494	HAVING b = 10;
	1495	c (SELECT a FROM t1 WHERE b = c)
	1496	SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
	1497	FROM t1
	1498	HAVING b = 10;
	1499	ERROR 42S22: Reference 'c' not supported (reference to group function)
	1500	SELECT b c, (SELECT a FROM t1 WHERE b = c)
	1501	FROM t1;
	1502	c (SELECT a FROM t1 WHERE b = c)
	1503	SELECT b c, (SELECT a FROM t1 WHERE b = c)
	1504	FROM t1
	1505	HAVING b = 10;
	1506	c (SELECT a FROM t1 WHERE b = c)
	1507	SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
	1508	FROM t1
	1509	HAVING b = 10;
	1510	ERROR 42S22: Reference 'c' not supported (reference to group function)
	1511	INSERT INTO t1 VALUES (1, 1);
	1512	SELECT b c, (SELECT a FROM t1 WHERE b = c)
	1513	FROM t1;
	1514	c (SELECT a FROM t1 WHERE b = c)
	1515	1 1
	1516	INSERT INTO t1 VALUES (2, 1);
	1517	SELECT b c, (SELECT a FROM t1 WHERE b = c)
	1518	FROM t1;
	1519	ERROR 21000: Subquery returns more than 1 row
	1520	DROP TABLE t1;