MySQL Bugs: #3763: SUM and COUNT in a group by select return every time different result

Bug #3763	SUM and COUNT in a group by select return every time different result
Submitted:	14 May 2004 14:08	Modified:	17 May 2004 18:08
Reporter:	Finotello Andrea	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.18	OS:	Windows (W2K)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Making a query on a table and aggregating rows, the select returns every time it's called different result in the SUM and COUNT columns. This occours within or without the same session. In our case the query increment each time, by a constant value, the SUM and COUNT fields.

How to repeat:
#Create this table:

DROP TABLE IF EXISTS SUM_BUG;
CREATE TABLE SUM_BUG (
  FIELD1 int(11) NOT NULL default '0',
  FIELD2 int(11) NOT NULL default '0',
  FIELD3 int(11) NOT NULL default '0',
  FIELD4 decimal(11,2) NOT NULL default '0.00',
  FIELD5 decimal(11,2) NOT NULL default '0.00',
  FIELD6 decimal(11,2) NOT NULL default '0.00',
  PRIMARY KEY  (FIELD1,FIELD2,FIELD3)
) TYPE=MyISAM;

# Insert demo data
INSERT INTO SUM_BUG 
VALUES (ROUND( RAND()*10000),ROUND( RAND()*10000),ROUND( RAND()*10000),RAND()*10000,RAND()*10000,RAND()*10000);
INSERT INTO SUM_BUG 
VALUES (ROUND( RAND()*10000),ROUND( RAND()*10000),ROUND( RAND()*10000),RAND()*10000,RAND()*10000,RAND()*10000);
INSERT INTO SUM_BUG 
VALUES (ROUND( RAND()*10000),ROUND( RAND()*10000),ROUND( RAND()*10000),RAND()*10000,RAND()*10000,RAND()*10000);
INSERT INTO SUM_BUG 
VALUES (ROUND( RAND()*10000),ROUND( RAND()*10000),ROUND( RAND()*10000),RAND()*10000,RAND()*10000,RAND()*10000);
INSERT INTO SUM_BUG 
VALUES (ROUND( RAND()*10000),ROUND( RAND()*10000),ROUND( RAND()*10000),RAND()*10000,RAND()*10000,RAND()*10000);
INSERT INTO SUM_BUG 
VALUES (ROUND( RAND()*10000),ROUND( RAND()*10000),ROUND( RAND()*10000),RAND()*10000,RAND()*10000,RAND()*10000);
INSERT INTO SUM_BUG 
VALUES (ROUND( RAND()*10000),ROUND( RAND()*10000),ROUND( RAND()*10000),RAND()*10000,RAND()*10000,RAND()*10000);
INSERT INTO SUM_BUG 
VALUES (ROUND( RAND()*10000),ROUND( RAND()*10000),ROUND( RAND()*10000),RAND()*10000,RAND()*10000,RAND()*10000);
INSERT INTO SUM_BUG 
VALUES (ROUND( RAND()*10000),ROUND( RAND()*10000),ROUND( RAND()*10000),RAND()*10000,RAND()*10000,RAND()*10000);
INSERT INTO SUM_BUG 
VALUES (ROUND( RAND()*10000),ROUND( RAND()*10000),ROUND( RAND()*10000),RAND()*10000,RAND()*10000,RAND()*10000);

# call this query more times and compare the results

SELECT FIELD1, FIELD2, FIELD3, SUM( FIELD5 ) AS FIELD5,count(*) as TOT_FIELD
FROM SUM_BUG 
WHERE FIELD2 >100
GROUP BY FIELD1, FIELD2, FIELD3
HAVING FIELD3 >100
ORDER BY FIELD1 desc
limit 0,1;

I couldn't repeat it with current 4.0 from source tree
ChangeSet@1.1828, 2004-05-14 12:26:12+03:00, marko@hundin.mysql.fi

Result is always the same. To be sure I have created table with SELECT ... and them filled it up with hundreds of rows by INSERT ... SELECT ... within the same session, but also by reconnecting.

End result is:

mysql> SELECT COUNT(*) FROM bug3763;
+----------+
| COUNT(*) |
+----------+
|      511 |
+----------+
1 row in set (0.00 sec)
 
mysql> SELECT DISTINCT * FROM bug3763;
+--------+--------+--------+---------+-----------+
| FIELD1 | FIELD2 | FIELD3 | FIELD5  | TOT_FIELD |
+--------+--------+--------+---------+-----------+
|   9325 |   9447 |   9260 | 2021.69 |         1 |
+--------+--------+--------+---------+-----------+
1 row in set (0.01 sec)

Miguel,
Can you please test if you can repeat it under Windows?

Salle:
Also I can't repeat the bug reported, all the time I
got the same result. Tested on XP with 4.0.18 version.

I remake the test case and the bug still remains. 
this the contents 'mysqld' section of my 'my.ini':

[mysqld]
basedir=D:/mysql
datadir=D:/mysql/data
language=D:/mysql/share/italian
lower_case_table_names=2
max_allowed_packet=16M
key_buffer=256M
table_cache=512
record_buffer=8M
sort_buffer=16M
read_buffer_size=8M
thread_cache=8
thread_concurrency=4
myisam_sort_buffer_size=64M
tmp_table_size=256M
max_tmp_tables=256

innodb_data_file_path = ibdata1:256M:autoextend
innodb_flush_log_at_trx_commit=1
innodb_log_archive=0
innodb_buffer_pool_size=16M
innodb_additional_mem_pool_size=2M
innodb_file_io_threads=4
innodb_lock_wait_timeout=50

The OS is W2k Server

Please, you have to replicate the same SQL code that I sent, and execute this query more times.

SELECT FIELD1, FIELD2, FIELD3, SUM( FIELD5 ) AS FIELD5,count(*) as
TOT_FIELD
FROM SUM_BUG 
WHERE FIELD2 >100
GROUP BY FIELD1, FIELD2, FIELD3
HAVING FIELD3 >100
ORDER BY FIELD1 desc
limit 0,1;

Thanks.

Thank you for the bug report I was able to repeat with
version 4.0.18.

That behavior happens with the below key configured:

lower_case_table_names=2

I will test it with latest 4.0 BK tree.

Testing against 4.0.20 (our next release) I verified that
this bug is already fixed.

Again thank you for the bug report.