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

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

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	);
1 by brian clean slate	546	insert into t2 select
	547	x3.a, -- 3
	548	concat('val-', x3.a + 3*x4.a), -- 12
	549	concat('val-', @a:=x3.a + 3x4.a + 12C.a), -- 120
	550	concat('val-', @a + 120*D.a)
	551	from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4;
	552	delete from t2 where a = 2 and b = 'val-2' order by a,b,c,d limit 30;
	553	explain select c from t2 where a = 2 and b = 'val-2' group by c;
	554	id select_type table type possible_keys key key_len ref rows Extra
1718.1.2 by Brian Aker Merge in fix for test result.	555	1 SIMPLE t2 # PRIMARY,a a # const,const # #
1 by brian clean slate	556	select c from t2 where a = 2 and b = 'val-2' group by c;
	557	c
	558	val-74
	559	val-98
	560	drop table t1,t2;
520.1.18 by Brian Aker A bunch more test fixes.	561	create table t1 (b int4 not null);
520.1.18 by Brian Aker A bunch more test fixes.	562	insert into t1 values(300000);
1 by brian clean slate	563	select * from t1;
1 by brian clean slate	564	b
520.1.18 by Brian Aker A bunch more test fixes.	565	300000
1 by brian clean slate	566	select min(b) from t1;
1 by brian clean slate	567	min(b)
520.1.18 by Brian Aker A bunch more test fixes.	568	300000
1 by brian clean slate	569	drop table t1;
	570	CREATE TABLE t1 (id int PRIMARY KEY, user_id int, hostname longtext);
	571	INSERT INTO t1 VALUES
	572	(1, 7, 'cache-dtc-af05.proxy.aol.com'),
	573	(2, 3, 'what.ever.com'),
	574	(3, 7, 'cache-dtc-af05.proxy.aol.com'),
	575	(4, 7, 'cache-dtc-af05.proxy.aol.com');
	576	SELECT hostname, COUNT(DISTINCT user_id) as no FROM t1
	577	WHERE hostname LIKE '%aol%'
	578	GROUP BY hostname;
	579	hostname no
	580	cache-dtc-af05.proxy.aol.com 1
	581	DROP TABLE t1;
	582	CREATE TABLE t1 (a int, b int);
	583	INSERT INTO t1 VALUES (1,2), (1,3);
	584	SELECT a, b FROM t1 GROUP BY 'const';
	585	a b
	586	1 2
	587	SELECT DISTINCT a, b FROM t1 GROUP BY 'const';
	588	a b
	589	1 2
	590	DROP TABLE t1;
	591	CREATE TABLE t1 (id INT, dt DATETIME);
	592	INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
	593	INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
	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	SELECT dt DIV 1 AS f, id FROM t1 GROUP BY f;
	597	f id
	598	20050501123000 1
	599	DROP TABLE t1;
	600	CREATE TABLE t1 (id varchar(20) NOT NULL);
	601	INSERT INTO t1 VALUES ('trans1'), ('trans2');
	602	CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL);
	603	INSERT INTO t2 VALUES ('trans1', 'a problem');
	604	SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment
	605	FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment;
	606	COUNT(DISTINCT(t1.id)) comment
	607	1 NULL
	608	1 a problem
	609	DROP TABLE t1, t2;
	610	create table t1 (f1 date);
	611	insert into t1 values('2005-06-06');
	612	insert into t1 values('2005-06-06');
	613	select date(left(f1+0,8)) from t1 group by 1;
	614	date(left(f1+0,8))
	615	2005-06-06
	616	drop table t1;
	617	CREATE TABLE t1 (n int);
	618	INSERT INTO t1 VALUES (1);
	619	SELECT n+1 AS n FROM t1 GROUP BY n;
	620	n
	621	2
	622	Warnings:
	623	Warning 1052 Column 'n' in group statement is ambiguous
	624	DROP TABLE t1;
	625	create table t1(f1 varchar(5) key);
	626	insert into t1 values (1),(2);
	627	select sql_buffer_result max(f1) is null from t1;
	628	max(f1) is null
	629	0
	630	select sql_buffer_result max(f1)+1 from t1;
	631	max(f1)+1
	632	3
633	drop table t1;
634	CREATE TABLE t1(a INT);
635	INSERT INTO t1 VALUES (1),(2);
636	SELECT a FROM t1 GROUP BY 'a';
637	a
638	1
639	SELECT a FROM t1 GROUP BY "a";
640	a
641	1
642	SELECT a FROM t1 GROUP BY `a`;
643	a
644	1
645	2
646	SELECT a FROM t1 GROUP BY "a";
647	a
648	1
649	SELECT a FROM t1 GROUP BY 'a';
650	a
651	1
652	SELECT a FROM t1 GROUP BY `a`;
653	a
654	1
655	2
656	SELECT a FROM t1 HAVING 'a' > 1;
657	a
658	Warnings:
659	Warning 1292 Truncated incorrect DOUBLE value: 'a'
660	SELECT a FROM t1 HAVING "a" > 1;
661	a
662	Warnings:
663	Warning 1292 Truncated incorrect DOUBLE value: 'a'
664	SELECT a FROM t1 HAVING `a` > 1;
665	a
666	2
667	SELECT a FROM t1 ORDER BY 'a' DESC;
668	a
669	1
670	2
671	SELECT a FROM t1 ORDER BY "a" DESC;
672	a
673	1
674	2
675	SELECT a FROM t1 ORDER BY `a` DESC;
676	a
677	2
678	1
679	DROP TABLE t1;
680	CREATE TABLE t1 (
520.1.18 by Brian Aker A bunch more test fixes.	681	f1 int NOT NULL auto_increment primary key,
1 by brian clean slate	682	f2 varchar(100) NOT NULL default ''
	683	);
	684	CREATE TABLE t2 (
	685	f1 varchar(10) NOT NULL default '',
	686	f2 char(3) NOT NULL default '',
	687	PRIMARY KEY (`f1`),
	688	KEY `k1` (`f2`,`f1`)
	689	);
	690	INSERT INTO t1 values(NULL, '');
	691	INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
	692	SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
	693	avg(t2.f1)
	694	SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
	695	avg(t2.f1)
	696	DROP TABLE t1, t2;
	697	create table t1 (c1 char(3), c2 char(3));
	698	create table t2 (c3 char(3), c4 char(3));
	699	insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
	700	insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
	701	select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
	702	group by c2;
	703	c2
	704	aaa
	705	aaa
	706	Warnings:
	707	Warning 1052 Column 'c2' in group statement is ambiguous
	708	show warnings;
	709	Level Code Message
	710	Warning 1052 Column 'c2' in group statement is ambiguous
	711	select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
	712	group by t1.c1;
	713	c2
	714	aaa
	715	show warnings;
	716	Level Code Message
	717	drop table t1, t2;
	718	CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));
	719	INSERT INTO t1 VALUES (1, 1);
	720	INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1;
	721	INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1;
	722	INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1;
	723	INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1;
	724	INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1;
	725	INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1;
	726	INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1;
	727	SELECT MIN(b), MAX(b) from t1;
	728	MIN(b) MAX(b)
	729	0 19
	730	EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;
	731	id select_type table type possible_keys key key_len ref rows Extra
	732	1 SIMPLE t1 index NULL b 5 NULL 128 Using index
	733	EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
	734	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	735	1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using filesort
1 by brian clean slate	736	SELECT b, sum(1) FROM t1 GROUP BY b;
	737	b sum(1)
	738	0 6
	739	1 7
	740	2 7
	741	3 7
	742	4 7
	743	5 7
	744	6 7
	745	7 7
	746	8 7
	747	9 6
	748	10 6
	749	11 6
	750	12 6
	751	13 6
	752	14 6
	753	15 6
	754	16 6
	755	17 6
	756	18 6
	757	19 6
	758	SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
	759	b sum(1)
	760	0 6
	761	1 7
	762	2 7
	763	3 7
	764	4 7
	765	5 7
	766	6 7
	767	7 7
	768	8 7
	769	9 6
	770	10 6
	771	11 6
	772	12 6
	773	13 6
	774	14 6
	775	15 6
	776	16 6
	777	17 6
	778	18 6
	779	19 6
	780	DROP TABLE t1;
	781	CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
	782	INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
	783	SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b;
	784	MAX(a)-MIN(a)
	785	1
	786	1
	787	1
	788	SELECT CEILING(MIN(a)) FROM t1 GROUP BY b;
	789	CEILING(MIN(a))
	790	1
	791	3
	792	5
	793	SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1
	794	GROUP BY b;
	795	CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END
	796	Positive
	797	Positive
	798	Positive
	799	SELECT a + 1 FROM t1 GROUP BY a;
800	a + 1
801	2
802	3
803	4
804	5
805	6
806	7
807	SELECT a + b FROM t1 GROUP BY b;
808	a + b
809	2
810	5
811	8
812	SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
813	FROM t1 AS t1_outer;
814	(SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
815	1
816	2
817	3
818	4
819	5
820	6
821	SELECT 1 FROM t1 as t1_outer GROUP BY a
822	HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
823	1
824	1
825	1
826	1
827	1
828	1
829	1
830	SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1)
831	FROM t1 AS t1_outer GROUP BY t1_outer.b;
832	(SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1)
833	1
834	3
835	5
836	SELECT 1 FROM t1 as t1_outer GROUP BY a
837	HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
838	ERROR 42S22: Unknown column 'test.t1_outer.b' in 'field list'
839	SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
840	FROM t1 AS t1_outer GROUP BY t1_outer.b;
841	(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
842	21
843	21
844	21
845	SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
846	FROM t1 AS t1_outer;
847	(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
848	3
849	3
850	3
851	3
852	3
853	3
854	SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1)
855	FROM t1 AS t1_outer GROUP BY t1_outer.b;
856	(SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1)
857	3
858	7
859	11
860	SELECT 1 FROM t1 as t1_outer
861	WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1);
862	1
863	1
864	1
865	1
866	1
867	1
868	1
869	SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0;
870	b
871	1
872	2
873	3
874	SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12;
875	1
876	1
877	1
878	SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1);
879	1
880	1
881	SELECT 1 FROM t1 GROUP BY b HAVING a = 2;
882	ERROR 42S22: Unknown column 'a' in 'having clause'
883	SELECT 1 FROM t1 GROUP BY SUM(b);
884	ERROR HY000: Invalid use of group function
885	SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN
886	(SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a
887	HAVING SUM(t1_inner.b)+t1_outer.b > 5);
888	b
889	3
890	DROP TABLE t1;
891	create table t1(f1 int, f2 int);
892	select * from t1 group by f1;
893	f1 f2
894	select * from t1 group by f2;
895	f1 f2
896	select * from t1 group by f1, f2;
897	f1 f2
898	select t1.f1,t.* from t1, t1 t group by 1;
899	f1 f1 f2
900	drop table t1;
901	CREATE TABLE t1(
902	id INT AUTO_INCREMENT PRIMARY KEY,
903	c1 INT NOT NULL,
904	c2 INT NOT NULL,
905	UNIQUE KEY (c2,c1));
906	INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
907	SELECT * FROM t1 ORDER BY c1;
908	id c1 c2
909	5 1 3
910	4 2 3
911	3 3 5
912	2 4 1
913	1 5 1
914	SELECT * FROM t1 GROUP BY id ORDER BY c1;
915	id c1 c2
916	5 1 3
917	4 2 3
918	3 3 5
919	2 4 1
920	1 5 1
921	SELECT * FROM t1 GROUP BY id ORDER BY id DESC;
922	id c1 c2
923	5 1 3
924	4 2 3
925	3 3 5
926	2 4 1
927	1 5 1
928	SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;
929	id c1 c2
930	2 4 1
931	1 5 1
932	5 1 3
933	4 2 3
934	3 3 5
935	SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;
936	id c1 c2
937	3 3 5
938	5 1 3
939	4 2 3
940	2 4 1
941	1 5 1
942	SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;
943	id c1 c2
944	3 3 5
945	4 2 3
946	5 1 3
947	1 5 1
948	2 4 1
949	SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1;
950	id c1 c2
520.1.18 by Brian Aker A bunch more test fixes.	951	2 4 1
520.1.18 by Brian Aker A bunch more test fixes.	952	5 1 3
1 by brian clean slate	953	3 3 5
	954	SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1;
	955	id c1 c2
	956	3 3 5
	957	4 2 3
	958	1 5 1
	959	SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC;
	960	id c1 c2
	961	3 3 5
	962	4 2 3
	963	1 5 1
	964	DROP TABLE t1;
	965	#
	966	# Bug#27219: Aggregate functions in ORDER BY.
	967	#
	968	CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
	969	INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
	970	CREATE TABLE t2 SELECT * FROM t1;
	971	SELECT 1 FROM t1 ORDER BY COUNT(*);
	972	1
	973	1
	974	SELECT 1 FROM t1 ORDER BY COUNT(*) + 1;
	975	1
	976	1
	977	SELECT 1 FROM t1 ORDER BY COUNT(*) + a;
	978	1
	979	1
	980	SELECT 1 FROM t1 ORDER BY COUNT(*), 1;
	981	1
	982	1
	983	SELECT 1 FROM t1 ORDER BY COUNT(*), a;
	984	1
	985	1
	986	SELECT 1 FROM t1 ORDER BY SUM(a);
	987	1
	988	1
	989	SELECT 1 FROM t1 ORDER BY SUM(a + 1);
	990	1
	991	1
	992	SELECT 1 FROM t1 ORDER BY SUM(a) + 1;
	993	1
	994	1
	995	SELECT 1 FROM t1 ORDER BY SUM(a), b;
	996	1
	997	1
	998	SELECT a FROM t1 ORDER BY COUNT(b);
	999	a
	1000	3
	1001	SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2);
	1002	a
	1003	3
	1004	2
	1005	3
	1006	2
	1007	3
	1008	4
	1009	SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a);
	1010	a
	1011	3
	1012	2
	1013	3
	1014	2
	1015	3
	1016	4
1017	SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
1018	a
1019	3
1020	2
1021	3
1022	2
1023	3
1024	4
1025	SELECT t1.a FROM t1
1026	WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
1027	a
1028	3
1029	3
1030	3
1031	SELECT t1.a FROM t1 GROUP BY t1.a
1032	HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1033	a
1034	3
1035	SELECT t1.a FROM t1 GROUP BY t1.a
1036	HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
1037	a
1038	2
1039	3
1040	4
1041	SELECT t1.a FROM t1 GROUP BY t1.a
1042	HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
1043	a
1044	3
1045	SELECT t1.a FROM t1 GROUP BY t1.a
1046	HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
1047	a
1048	4
1049	SELECT t1.a FROM t1
1050	WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
1051	a
1052	3
1053	3
1054	3
1055	SELECT 1 FROM t1 GROUP BY t1.a
1056	HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1057	1
1058	1
1059	1
1060	1
1061	SELECT 1 FROM t1 GROUP BY t1.a
1062	HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1063	1
1064	1
1065	1
1066	1
1067	SELECT 1 FROM t1 GROUP BY t1.a
1068	HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1069	1
1070	1
1071	1
1072	1
1073	SELECT 1 FROM t1 GROUP BY t1.a
1074	HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1);
1075	1
1076	1
1077	1
1078	1
1079	SELECT 1 FROM t1 GROUP BY t1.a
1080	HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
1081	1
1082	1
1083	1
1084	1
1085	SELECT 1 FROM t1 GROUP BY t1.a
1086	HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
1087	1
1088	1
1089	1
1090	1
1091	SELECT t1.a FROM t1
1092	WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a
1093	ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1);
1094	a
1095	4
1096	SELECT t1.a, SUM(t1.b) FROM t1
1097	WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a
1098	ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1)
1099	GROUP BY t1.a;
1100	a SUM(t1.b)
1101	4 4
1102	SELECT t1.a, SUM(t1.b) FROM t1
1103	WHERE t1.a = (SELECT SUM(t2.b) FROM t2
1104	ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1)
1105	GROUP BY t1.a;
1106	a SUM(t1.b)
1107	SELECT t1.a, SUM(t1.b) FROM t1
1108	WHERE t1.a = (SELECT SUM(t2.b) FROM t2
1109	ORDER BY SUM(t2.b + t1.a) LIMIT 1)
1110	GROUP BY t1.a;
1111	a SUM(t1.b)
1112	SELECT t1.a FROM t1 GROUP BY t1.a
1113	HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);
1114	a
1115	select avg (
1116	(select
1117	(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
1118	from t1 as outr order by outr.a limit 1))
1119	from t1 as most_outer;
1120	avg (
1121	(select
1122	(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
1123	from t1 as outr order by outr.a limit 1))
1124	29.0000
1125	select avg (
1126	(select (
1127	(select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt
1128	from t1 as outr order by count(outr.a) limit 1)) as tt
1129	from t1 as most_outer;
1130	tt
1131	35.0000
1132	select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a;
1133	tt
1134	29
1135	29
1136	35
1137	35
1138	35
1139	41
1140	DROP TABLE t1, t2;
1141	End of 5.0 tests
1142	CREATE TABLE t1 (a INT, b INT,
1143	PRIMARY KEY (a),
1144	KEY i2(a,b));
1145	INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
1146	INSERT INTO t1 SELECT a + 8,b FROM t1;
1147	INSERT INTO t1 SELECT a + 16,b FROM t1;
1148	INSERT INTO t1 SELECT a + 32,b FROM t1;
1149	INSERT INTO t1 SELECT a + 64,b FROM t1;
1150	INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16;
1151	ANALYZE TABLE t1;
1152	Table Op Msg_type Msg_text
1153	test.t1 analyze status OK
1154	EXPLAIN SELECT a FROM t1 WHERE a < 2;
1155	id select_type table type possible_keys key key_len ref rows Extra
1156	1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 1 Using where; Using index
1157	EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
1158	id select_type table type possible_keys key key_len ref rows Extra
1159	1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 1 Using where; Using index
1160	EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
1161	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	1162	1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 1 Using where; Using index for group-by
1 by brian clean slate	1163	EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
	1164	id select_type table type possible_keys key key_len ref rows Extra
	1165	1 SIMPLE t1 ALL NULL NULL NULL NULL 144
	1166	EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
	1167	id select_type table type possible_keys key key_len ref rows Extra
	1168	1 SIMPLE t1 ALL NULL NULL NULL NULL 144
	1169	EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
	1170	id select_type table type possible_keys key key_len ref rows Extra
	1171	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index
	1172	EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
	1173	id select_type table type possible_keys key key_len ref rows Extra
	1174	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index
	1175	SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
	1176	a
	1177	1
	1178	2
	1179	3
	1180	4
	1181	5
	1182	6
	1183	7
	1184	8
	1185	9
	1186	10
	1187	11
	1188	12
	1189	13
	1190	14
	1191	15
	1192	16
	1193	17
	1194	18
	1195	19
	1196	20
	1197	21
	1198	22
	1199	23
	1200	24
	1201	25
	1202	26
	1203	27
	1204	28
	1205	29
	1206	30
	1207	31
	1208	32
	1209	33
	1210	34
	1211	35
	1212	36
	1213	37
	1214	38
	1215	39
	1216	40
	1217	41
	1218	42
	1219	43
	1220	44
	1221	45
	1222	46
	1223	47
	1224	48
	1225	49
	1226	50
1227	51
1228	52
1229	53
1230	54
1231	55
1232	56
1233	57
1234	58
1235	59
1236	60
1237	61
1238	62
1239	63
1240	64
1241	65
1242	66
1243	67
1244	68
1245	69
1246	70
1247	71
1248	72
1249	73
1250	74
1251	75
1252	76
1253	77
1254	78
1255	79
1256	80
1257	81
1258	82
1259	83
1260	84
1261	85
1262	86
1263	87
1264	88
1265	89
1266	90
1267	91
1268	92
1269	93
1270	94
1271	95
1272	96
1273	97
1274	98
1275	99
1276	100
1277	101
1278	102
1279	103
1280	104
1281	105
1282	106
1283	107
1284	108
1285	109
1286	110
1287	111
1288	112
1289	113
1290	114
1291	115
1292	116
1293	117
1294	118
1295	119
1296	120
1297	121
1298	122
1299	123
1300	124
1301	125
1302	126
1303	127
1304	128
1305	129
1306	130
1307	131
1308	132
1309	133
1310	134
1311	135
1312	136
1313	137
1314	138
1315	139
1316	140
1317	141
1318	142
1319	143
1320	144
1321	EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
1322	IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
1323	id select_type table type possible_keys key key_len ref rows Extra
1324	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index
1325	EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
1326	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	1327	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144
1 by brian clean slate	1328	EXPLAIN SELECT a FROM t1 FORCE INDEX (i2);
1 by brian clean slate	1329	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	1330	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144
1 by brian clean slate	1331	EXPLAIN SELECT a FROM t1 USE INDEX ();
	1332	id select_type table type possible_keys key key_len ref rows Extra
	1333	1 SIMPLE t1 ALL NULL NULL NULL NULL 144
	1334	EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);
	1335	id select_type table type possible_keys key key_len ref rows Extra
	1336	1 SIMPLE t1 ALL NULL NULL NULL NULL 144
	1337	EXPLAIN SELECT a FROM t1
	1338	FORCE INDEX (PRIMARY)
	1339	IGNORE INDEX FOR GROUP BY (i2)
	1340	IGNORE INDEX FOR ORDER BY (i2)
	1341	USE INDEX (i2);
	1342	ERROR HY000: Incorrect usage of USE INDEX and FORCE INDEX
	1343	EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
	1344	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	1345	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144
1 by brian clean slate	1346	EXPLAIN SELECT a FROM t1 FORCE INDEX ();
629.2.6 by Monty Updated test output with new and improved error messages.	1347	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	1348	EXPLAIN SELECT a FROM t1 IGNORE 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 USE INDEX FOR JOIN (i2)
	1351	USE INDEX FOR GROUP BY (i2) GROUP BY a;
	1352	id select_type table type possible_keys key key_len ref rows Extra
	1353	1 SIMPLE t1 # NULL i2 # NULL # #
	1354	EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2)
	1355	FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
	1356	id select_type table type possible_keys key key_len ref rows Extra
	1357	1 SIMPLE t1 range NULL i2 4 NULL 145 Using index for group-by
	1358	EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
	1359	id select_type table type possible_keys key key_len ref rows Extra
	1360	1 SIMPLE t1 ALL NULL NULL NULL NULL 144
	1361	EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX ();
	1362	id select_type table type possible_keys key key_len ref rows Extra
	1363	1 SIMPLE t1 ALL NULL NULL NULL NULL 144
	1364	EXPLAIN SELECT a FROM t1
	1365	USE INDEX FOR GROUP BY (i2)
	1366	USE INDEX FOR ORDER BY (i2)
	1367	USE INDEX FOR JOIN (i2);
	1368	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	1369	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144
1 by brian clean slate	1370	EXPLAIN SELECT a FROM t1
	1371	USE INDEX FOR JOIN (i2)
	1372	USE INDEX FOR JOIN (i2)
	1373	USE INDEX FOR JOIN (i2,i2);
	1374	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	1375	1 SIMPLE t1 index NULL PRIMARY 4 NULL 144
1 by brian clean slate	1376	EXPLAIN SELECT 1 FROM t1 WHERE a IN
	1377	(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
	1378	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.	1379	1 PRIMARY t1 index NULL PRIMARY 4 NULL 144 Using where; Using index
	1380	2 SUBQUERY t1 ALL NULL NULL NULL NULL 144
1 by brian clean slate	1381	CREATE TABLE t2 (a INT, b INT, KEY(a));
	1382	INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
	1383	EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
	1384	id select_type table type possible_keys key key_len ref rows Extra
	1385	1 SIMPLE t2 index NULL a 5 NULL 2
	1386	EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
	1387	id select_type table type possible_keys key key_len ref rows Extra
	1388	1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
	1389	EXPLAIN SELECT 1 FROM t2 WHERE a IN
	1390	(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
	1391	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.	1392	1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
	1393	2 SUBQUERY t1 ALL NULL NULL NULL NULL 144
1 by brian clean slate	1394	DROP TABLE t1, t2;
	1395	CREATE TABLE t1(
	1396	a INT,
	1397	b INT NOT NULL,
	1398	c INT NOT NULL,
	1399	d INT,
	1400	UNIQUE KEY (c,b)
	1401	);
	1402	INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
	1403	CREATE TABLE t2(
	1404	a INT,
	1405	b INT,
	1406	UNIQUE KEY(a,b)
	1407	);
	1408	INSERT INTO t2 VALUES (NULL, NULL), (NULL, NULL), (NULL, 1), (1, NULL), (1, 1), (1,2);
	1409	EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
	1410	id select_type table type possible_keys key key_len ref rows Extra
	1411	1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
	1412	SELECT c,b,d FROM t1 GROUP BY c,b,d;
	1413	c b d
	1414	1 1 50
	1415	3 1 4
	1416	3 2 40
	1417	EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
	1418	id select_type table type possible_keys key key_len ref rows Extra
	1419	1 SIMPLE t1 ALL NULL NULL NULL NULL 3
	1420	SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
	1421	c b d
	1422	1 1 50
520.1.18 by Brian Aker A bunch more test fixes.	1423	3 1 4
1 by brian clean slate	1424	3 2 40
	1425	EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
	1426	id select_type table type possible_keys key key_len ref rows Extra
	1427	1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
	1428	SELECT c,b,d FROM t1 ORDER BY c,b,d;
	1429	c b d
	1430	1 1 50
	1431	3 1 4
	1432	3 2 40
	1433	EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
	1434	id select_type table type possible_keys key key_len ref rows Extra
520.1.18 by Brian Aker A bunch more test fixes.	1435	1 SIMPLE t1 index NULL c 8 NULL 3
1 by brian clean slate	1436	SELECT c,b,d FROM t1 GROUP BY c,b;
	1437	c b d
	1438	1 1 50
	1439	3 1 4
	1440	3 2 40
	1441	EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
	1442	id select_type table type possible_keys key key_len ref rows Extra
	1443	1 SIMPLE t1 index NULL c 8 NULL 3 Using index
	1444	SELECT c,b FROM t1 GROUP BY c,b;
	1445	c b
	1446	1 1
	1447	3 1
	1448	3 2
	1449	EXPLAIN SELECT a,b from t2 ORDER BY a,b;
	1450	id select_type table type possible_keys key key_len ref rows Extra
	1451	1 SIMPLE t2 index NULL a 10 NULL 6 Using index
	1452	SELECT a,b from t2 ORDER BY a,b;
	1453	a b
	1454	NULL NULL
	1455	NULL NULL
	1456	NULL 1
	1457	1 NULL
	1458	1 1
	1459	1 2
	1460	EXPLAIN SELECT a,b from t2 GROUP BY a,b;
	1461	id select_type table type possible_keys key key_len ref rows Extra
	1462	1 SIMPLE t2 index NULL a 10 NULL 6 Using index
	1463	SELECT a,b from t2 GROUP BY a,b;
	1464	a b
	1465	NULL NULL
	1466	NULL 1
	1467	1 NULL
	1468	1 1
	1469	1 2
	1470	EXPLAIN SELECT a from t2 GROUP BY a;
	1471	id select_type table type possible_keys key key_len ref rows Extra
	1472	1 SIMPLE t2 index NULL a 10 NULL 6 Using index
	1473	SELECT a from t2 GROUP BY a;
	1474	a
	1475	NULL
	1476	1
	1477	EXPLAIN SELECT b from t2 GROUP BY b;
	1478	id select_type table type possible_keys key key_len ref rows Extra
	1479	1 SIMPLE t2 index NULL a 10 NULL 6 Using index; Using temporary; Using filesort
	1480	SELECT b from t2 GROUP BY b;
	1481	b
	1482	NULL
	1483	1
	1484	2
1119.4.5 by Stewart Smith make group_by test not leave tables behind after running	1485	DROP TABLE t1,t2;
1 by brian clean slate	1486	CREATE TABLE t1 ( a INT, b INT );
	1487	SELECT b c, (SELECT a FROM t1 WHERE b = c)
	1488	FROM t1;
	1489	c (SELECT a FROM t1 WHERE b = c)
	1490	SELECT b c, (SELECT a FROM t1 WHERE b = c)
	1491	FROM t1
	1492	HAVING b = 10;
	1493	c (SELECT a FROM t1 WHERE b = c)
	1494	SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
	1495	FROM t1
	1496	HAVING b = 10;
	1497	ERROR 42S22: Reference 'c' not supported (reference to group function)
	1498	SELECT b c, (SELECT a FROM t1 WHERE b = c)
	1499	FROM t1;
	1500	c (SELECT a FROM t1 WHERE b = c)
	1501	SELECT b c, (SELECT a FROM t1 WHERE b = c)
	1502	FROM t1
	1503	HAVING b = 10;
	1504	c (SELECT a FROM t1 WHERE b = c)
	1505	SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
	1506	FROM t1
	1507	HAVING b = 10;
	1508	ERROR 42S22: Reference 'c' not supported (reference to group function)
	1509	INSERT INTO t1 VALUES (1, 1);
	1510	SELECT b c, (SELECT a FROM t1 WHERE b = c)
	1511	FROM t1;
	1512	c (SELECT a FROM t1 WHERE b = c)
	1513	1 1
	1514	INSERT INTO t1 VALUES (2, 1);
	1515	SELECT b c, (SELECT a FROM t1 WHERE b = c)
	1516	FROM t1;
	1517	ERROR 21000: Subquery returns more than 1 row
	1518	DROP TABLE t1;