MySQL Bugs: #37905: SELECT COUNT with GROUP BY bug (see #7128)

Bug #37905	SELECT COUNT with GROUP BY bug (see #7128)
Submitted:	6 Jul 2008 16:19	Modified:	7 Jul 2008 18:02
Reporter:	Axel Krysztofiak	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.51a	OS:	MacOS (part of XAMPP 0.7.2)
Assigned to:		CPU Architecture:	Any
Tags:	count, GROUP BY

Description:
As I'm not able to re-open bug #7128, I'm creating a new one, reporting the same issue on MySQL 5.0.51a.

My problem was a table with about 300 entries. After I made a simple SELECT statement containing a COUNT field combined with a GROUP BY statement, MySQL found several groups (which were right) with a COUNT value of many millions, billions, trillions whatever... 

Then I tried to reconstruct the example giving in bug #7128.
I didn't see that the example worked on InnoDB tables, so I've done this on MyISAM tables. But even with MyISAM I had exactly the same result using MySQL 5.0.51a (provided in the latest XAMPP package for Mac OS X). 
I think there’ll be no need for another example.

To minimize research time here a copy of bug #7128 containing the example I talked about.

{copied text from bug #7128}
[9 Dec 2004 12:20] George Sogrwig

Description:
My table data:

(vote_id , vote_day)
-------------------- 
(16, 9)
(17, 16)
(32, 2) 
(33, 16) 
(35, 9) 
(37, 11) 
(38, 16) 

running the code:

SELECT vote_day, count(*) as mycount
FROM `vote`
GROUP BY vote_day

RETURNS:
-----------
(vote_day , mycount)
2 , 1 
9 , 2 
11 , 1 
16 , 3 

...on My WinXP PRO - version: 4.0.20a-max.

....while on a MAC OS X - version 4.0.14:

vote_day , mycount 
2 , 72057594037927936 
9 , 144115188075855872 
11 , 72057594037927936 
16 , 216172782113783808 
      

My table is: 
---------------------------------
CREATE TABLE `vote` (
`vote_id` int(11) NOT NULL auto_increment,
`vote_day` int(11) default NULL,
PRIMARY KEY (`vote_id`)
) TYPE=InnoDB ROW_FORMAT=FIXED COMMENT='InnoDB free'

How to repeat:
CREATE TABLE `vote` (
`vote_id` int(11) NOT NULL auto_increment,
`vote_day` int(11) default NULL,
PRIMARY KEY (`vote_id`)
) TYPE=InnoDB ROW_FORMAT=FIXED COMMENT='InnoDB free'

on a MAC OS X - Mysql vesions: 4.0.14

insert some data.... and run the query:

SELECT vote_day, count(*) as mycount
FROM `vote`
GROUP BY vote_day

{end of copy}

How to repeat:
Use any SELECT statement with a COUNT field and a GROUP BY statement

Thank you for the report.

I get correct results with data from earlier bug report:

CREATE TABLE `vote` (
`vote_id` int(11) NOT NULL auto_increment,
`vote_day` int(11) default NULL,
PRIMARY KEY (`vote_id`)
) TYPE=InnoDB ROW_FORMAT=FIXED COMMENT='InnoDB free';
Warnings:
Warning 1287    'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
insert into vote values(16,9);
insert into vote values(17,16);
insert into vote values(32,2);
insert into vote values(33,16);
insert into vote values(35,9);
insert into vote values(37,11);
insert into vote values(38,16);
SELECT vote_day, count(*) as mycount
FROM `vote`
GROUP BY vote_day;
vote_day        mycount
2       1
9       2
11      1
16      3

Please provide dump of your table.

Also please try with our binaries accessible from http://dev.mysql.com/downloads to check if this is MySQL or xampp bug.

can it be that this is a problem using older (non-intel) macs?

Thank you for the feedback.

> can it be that this is a problem using older (non-intel) macs?

I can not say until I repeat it. Please in addtition to information requested in previous comment indicate which Mac do you have. And in any case I'd check with MySQL binaries first.

I downloaded the version 5.0.51b from mysql.com, and with this version I get the right values. It really seems to be a XAMPP problem. (?)
Did they add a wrong version to their package? Hm... Whatever...

Ok, then I'll use the XAPP package with an extra MySQL, because it seems to be the best way to let it work.