MySQL Bugs: #13500: sum() function returns different result based on order

Bug #13500	sum() function returns different result based on order
Submitted:	26 Sep 2005 20:33	Modified:	5 Oct 2005 5:44
Reporter:	Steve Meyers	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1.14/BK source	OS:	Linux (RHEL 3.0/Linux)
Assigned to:	Alexander Ivanov	CPU Architecture:	Any

Description:
I have the following table:

mysql> desc checksum_smiles;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| smileid  | int(10) unsigned |      | PRI | 0       |       |
| checksum | int(10) unsigned |      |     | 0       |       |
+----------+------------------+------+-----+---------+-------+

It returns different results for sum(checksum) based on how the results are ordered.  I first encountered it with two separate tables which had the same rows in different orders, but I did some tests using subqueries to get more insight into it.  Results below:

mysql> select count(*), sum(checksum) from checksum_smiles; select count(*), sum(checksum) from checksum_smiles2; 
+----------+------------------+
| count(*) | sum(checksum)    |
+----------+------------------+
|  4646874 | 9977027053429548 |
+----------+------------------+
1 row in set (1.05 sec)

+----------+------------------+
| count(*) | sum(checksum)    |
+----------+------------------+
|  4646874 | 9977027053429544 |
+----------+------------------+
1 row in set (1.06 sec)

mysql> select a.smileid from checksum_smiles a left join checksum_smiles2 b using (smileid) where b.smileid is null or a.checksum != b.checksum;
Empty set (46.37 sec)

mysql> select sum(checksum) from checksum_smiles;
+------------------+
| sum(checksum)    |
+------------------+
| 9977027053429548 |
+------------------+
1 row in set (1.79 sec)

mysql> select sum(checksum) from (select checksum from checksum_smiles order by smileid) a;
+------------------+
| sum(checksum)    |
+------------------+
| 9977027053430208 |
+------------------+
1 row in set (8.43 sec)

mysql> select sum(checksum) from (select checksum from checksum_smiles order by smileid desc) a;
+------------------+
| sum(checksum)    |
+------------------+
| 9977027053430172 |
+------------------+
1 row in set (7.97 sec)

mysql> select sum(checksum) from (select checksum from checksum_smiles order by rand()) a;
+------------------+
| sum(checksum)    |
+------------------+
| 9977027053430200 |
+------------------+
1 row in set (15.89 sec)

mysql> select sum(checksum) from (select checksum from checksum_smiles order by rand()) a;
+------------------+
| sum(checksum)    |
+------------------+
| 9977027053430822 |
+------------------+
1 row in set (15.97 sec)

mysql> select sum(checksum) from (select checksum & 0x7FFFFFFF as checksum from checksum_smiles order by rand()) a;
+------------------+
| sum(checksum)    |
+------------------+
| 4989305154905117 |
+------------------+
1 row in set (16.95 sec)

mysql> select sum(checksum) from (select checksum & 0x7FFFFFFF as checksum from checksum_smiles order by rand()) a;
+------------------+
| sum(checksum)    |
+------------------+
| 4989305154905117 |
+------------------+
1 row in set (16.50 sec)

As you can see, it's possible (but not certain) that removing the high bit fixes the problem.  That's the only lead I have on a possible cause.

I tested this on a dual 32-bit Xeon system as well as a dual 64-bit Opteron system, and got the same results.

How to repeat:
I will attach two sql files.  They both have the same data, just in different orders.

select count(*), sum(checksum) from checksum_smiles; select count(*), sum(checksum) from checksum_smiles2;

select a.smileid from checksum_smiles a left join checksum_smiles2 b using (smileid) where b.smileid is null or a.checksum != b.checksum;

select sum(checksum) from (select checksum from checksum_smiles order by smileid) a;

select sum(checksum) from (select checksum from checksum_smiles order by smileid desc) a;

select sum(checksum) from (select checksum from checksum_smiles order by rand()) a;

Repeat the last one as often as you'd like. :)

You can upload at:

ftp://ftp.mysql.com:/pub/mysql/upload

using a name like bug13500.zip

Thanks in advance.

Thank you for the bug report and feedback.

This bug not affects 5.0.

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.15-debug-log |
+------------------+
1 row in set (0.02 sec)

mysql> select sum(checksum) from (select checksum from checksum_smiles order by rand())
    -> a;
+------------------+
| sum(checksum)    |
+------------------+
| 9977027053428912 |
+------------------+
1 row in set (1 min 23.73 sec)

mysql> select sum(checksum) from (select checksum from checksum_smiles order by rand()) a;
+------------------+
| sum(checksum)    |
+------------------+
| 9977027053429912 |
+------------------+
1 row in set (1 min 23.38 sec)

mysql> select sum(checksum) from (select checksum from checksum_smiles order by rand()) a;
+------------------+
| sum(checksum)    |
+------------------+
| 9977027053430052 |
+------------------+
1 row in set (1 min 22.26 sec)

mysql> select sum(checksum) from (select checksum from checksum_smiles order by rand()) a;
+------------------+
| sum(checksum)    |
+------------------+
| 9977027053429668 |
+------------------+
1 row in set (1 min 22.94 sec)

mysql> alter table checksum_smiles engine=innodb;
Query OK, 4646874 rows affected (2 min 41.70 sec)
Records: 4646874  Duplicates: 0  Warnings: 0

mysql> alter table checksum_smiles2 engine=innodb;
Query OK, 4646874 rows affected (5 min 36.30 sec)
Records: 4646874  Duplicates: 0  Warnings: 0

mysql> select sum(checksum) from (select checksum from checksum_smiles order by rand()) a;
+------------------+
| sum(checksum)    |
+------------------+
| 9977027053430240 |
+------------------+
1 row in set (2 min 1.57 sec)

mysql> select sum(checksum) from (select checksum from checksum_smiles order by rand()) a;
+------------------+
| sum(checksum)    |
+------------------+
| 9977027053429888 |
+------------------+
1 row in set (1 min 59.57 sec)

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.0.14-rc-debug |
+-----------------+
1 row in set (0.00 sec)

mysql> select sum(checksum) from (select checksum from checksum_smiles order by rand()) a;
+------------------+
| sum(checksum)    |
+------------------+
| 9977027053429789 |
+------------------+
1 row in set (1 min 20.12 sec)

mysql> select sum(checksum) from (select checksum from checksum_smiles order by rand()) a;
+------------------+
| sum(checksum)    |
+------------------+
| 9977027053429789 |
+------------------+
1 row in set (1 min 18.90 sec)

mysql> select sum(checksum) from (select checksum from checksum_smiles order by rand()) a;
+------------------+
| sum(checksum)    |
+------------------+
| 9977027053429789 |
+------------------+
1 row in set (1 min 15.41 sec)

mysql> select sum(checksum) from (select checksum from checksum_smiles order by rand()) a;
+------------------+
| sum(checksum)    |
+------------------+
| 9977027053429789 |
+------------------+
1 row in set (1 min 15.12 sec)

We won't fix this problem in 4.1 since in 4.1 we use a value of the double type to calculate the checksum function.
In 5.0 this problem is resolved.