Bug #13831 GROUP BY and AGGREGATE FUNCTION bug
Submitted: 7 Oct 2005 8:39 Modified: 7 Oct 2005 9:00
Reporter: Adrian Tan Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.12a OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[7 Oct 2005 8:39] Adrian Tan
Description:
Server crashes when processing a group by query with an aggregate function for large rows.

How to repeat:
drop table foo;

CREATE TABLE foo (
  gene	varchar(20),	
	chrom	varchar(10),		
	start	int(11) unsigned default '0',
	end	int(11) unsigned default '0',
	strand	char(1),		
	exonStarts	longblob,				
	exonEnds	longblob,				
	prod	longblob
) TYPE=MyISAM;

insert into foo (gene, chrom, start, end, strand, exonStarts, exonEnds, prod) values ('cdc2001000000','chr1',10000000,100000000,'+','10000000,50000000,10000000,50000000,10000000,50000000,','30000000,80000000,10000000,50000000,10000000,50000000,30000000,80000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,','chicken');
insert into foo (gene, chrom, start, end, strand, exonStarts, exonEnds, prod) values ('cdc2000020000','chr1',10000000,100000000,'+','10000000,50000000,10000000,50000000,10000000,50000000,','30000000,80000000,10000000,50000000,10000000,50000000,30000000,80000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,','duck');
insert into foo (gene, chrom, start, end, strand, exonStarts, exonEnds, prod) values ('cdc2100000200','chr1',110000000,400000000,'+','110000000,110000000,110000000,110000000,','350000000,110000000,110000000,110000000,110000000,110000000,110000000,','chocolate');
insert into foo (gene, chrom, start, end, strand, exonStarts, exonEnds, prod) values ('cdc2000001000','chr1',10000000,100000000,'+','10000000,50000000,10000000,50000000,10000000,50000000,','30000000,80000000,10000000,50000000,10000000,50000000,30000000,80000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,','horse');
insert into foo (gene, chrom, start, end, strand, exonStarts, exonEnds, prod) values ('cdc2000000100','chr1',10000000,100000000,'+','10000000,50000000,10000000,50000000,10000000,50000000,','30000000,80000000,10000000,50000000,10000000,50000000,30000000,80000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,','duck');
insert into foo (gene, chrom, start, end, strand, exonStarts, exonEnds, prod) values ('cdc2100000000','chr1',110000000,400000000,'+','110000000,110000000,110000000,110000000,','350000000,110000000,110000000,110000000,110000000,110000000,110000000,','chocolate');
insert into foo (gene, chrom, start, end, strand, exonStarts, exonEnds, prod) values ('cdc2000000000','chr1',10000000,100000000,'+','10000000,50000000,10000000,50000000,10000000,50000000,','30000000,80000000,10000000,50000000,10000000,50000000,30000000,80000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,','chicken');
insert into foo (gene, chrom, start, end, strand, exonStarts, exonEnds, prod) values ('cdc2000000000','chr1',10000000,100000000,'+','10000000,50000000,10000000,50000000,10000000,50000000,','30000000,80000000,10000000,50000000,10000000,50000000,30000000,80000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,','lamb');
insert into foo (gene, chrom, start, end, strand, exonStarts, exonEnds, prod) values ('cdc2100000000','chr1',110000000,400000000,'+','110000000,110000000,110000000,110000000,','350000000,110000000,110000000,110000000,110000000,110000000,110000000,','ice cream');
insert into foo (gene, chrom, start, end, strand, exonStarts, exonEnds, prod) values ('cdc2000000000','chr1',10000000,100000000,'+','10000000,50000000,10000000,50000000,10000000,50000000,','30000000,80000000,10000000,50000000,10000000,50000000,30000000,80000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,','chicken');
insert into foo (gene, chrom, start, end, strand, exonStarts, exonEnds, prod) values ('cdc2000000000','chr1',10000000,100000000,'+','10000000,50000000,10000000,50000000,10000000,50000000,','30000000,80000000,10000000,50000000,10000000,50000000,30000000,80000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,10000000,50000000,','duck');
insert into foo (gene, chrom, start, end, strand, exonStarts, exonEnds, prod) values ('cdc2100000000','chr1',110000000,400000000,'+','110000000,110000000,110000000,110000000,','350000000,110000000,110000000,110000000,110000000,110000000,110000000,','chocolate');

select gene, start, end, exonstarts, exonends, group_concat(prod) from foo group by gene, start, end, exonstarts, exonends;

Suggested fix:
Can't fix. ><
[7 Oct 2005 9:00] Vasily Kishkin
Thanks for bug report. I was not able to reproduce the bug on 4.1.15. Probably the bug was fixed.