Bug #19690 ORDER BY eliminates rows from the result
Submitted: 10 May 2006 16:13 Modified: 21 Feb 2007 3:17
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.19/4.1BK OS:Linux (Gentoo Linux)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[10 May 2006 16:13] Baron Schwartz 
Description:
First and foremost, this bug is not about imprecise DECIMAL math.  Imprecise DECIMAL math causes the bug to be visible, but this isn't about precision math.

When I select rows with a HAVING clause based on the SUM of a DECIMAL column, I may get spurious rows in the result because of imprecise math.  So far so good.  But when I ORDER BY that computed column, the spurious rows go away.

ORDER BY should never change the result set.

It appears the ORDER BY is being applied before the HAVING clause, casting the computed column back to DECIMAL from FLOAT.

Applying the ORDER BY before the HAVING clause is mathematically equivalent to applying it after, but has bad worst-case performance.  And in this case, of course it's changing the results.

The bug seems to depend on the storage engine being InnoDB and having a primary key in one of the tables.

How to repeat:
I will attach a detailed test case to this bug.  Run that script to populate the database, then run the following queries:

mysql> select t.id, sum(amount) as balance from transaction as t inner join split as s on t.id = s.transaction group by t.id having sum(amount) <> 0;
+---------------------------------+---------+
| id                              | balance |
+---------------------------------+---------+
| 182fedf6bf740de209da658362307d6 |    0.00 |
| 1e6f698e737158fcfb04eed030ca038 |   26.65 |
| 3c6cb1aa8df451e0d2a234bea919edd |    0.00 |
| 40509d65d986eaad08b06ef59afce31 |   -0.00 |
| 74bc51a3fba5c0961fa3aac5afb7dc3 |   -0.00 |
| 81487469bdbc9e862ddaf068086aabe |   -0.00 |
| 93e8e62b5da5b4366de03e6e62ade25 |    0.00 |
| 96779b5478b7b4cda07e639729ac4ff |   -0.00 |
| 98c3a0a07af42bb0b96539e8ca6d31d |    0.00 |
| 9c6ab1d5a822205fbba82d5e93613dd |   -0.00 |
| a482d3416841b6870e22aeb7bc1e65b |   -0.00 |
| d7976b8667435bdb6939d5354a38102 |    0.00 |
| d8f6bf34a82b65005af11e53b69e9d0 |   -0.00 |
| de59667992e5045ff98e8f0b798bb06 |    0.00 |
| df6705732c57ce0979873bd566dd9ae |    0.00 |
| e8e1fcd15fc82ae2cdc057341efe4af |    0.00 |
+---------------------------------+---------+
16 rows in set (0.00 sec)

mysql> select t.id, sum(amount) as balance from transaction as t inner join split as s on t.id = s.transaction group by t.id having sum(amount) <> 0 order by balance;
+---------------------------------+---------+
| id                              | balance |
+---------------------------------+---------+
| 1e6f698e737158fcfb04eed030ca038 |   26.65 |
+---------------------------------+---------+
1 row in set (0.00 sec)

The second result set should have 16 rows, not one.  The only difference is the ORDER BY clause.

To make the bug disappear, drop the primary key on the transaction table:

mysql> alter table transaction drop primary key;
Query OK, 16 rows affected (0.09 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> select t.id, sum(amount) as balance from transaction as t inner join split as s on t.id = s.transaction group by t.id having sum(amount) <> 0 order by balance;
+---------------------------------+---------+
| id                              | balance |
+---------------------------------+---------+
| 74bc51a3fba5c0961fa3aac5afb7dc3 |   -0.00 |
| 81487469bdbc9e862ddaf068086aabe |   -0.00 |
| d8f6bf34a82b65005af11e53b69e9d0 |   -0.00 |
| 40509d65d986eaad08b06ef59afce31 |   -0.00 |
| a482d3416841b6870e22aeb7bc1e65b |   -0.00 |
| 96779b5478b7b4cda07e639729ac4ff |   -0.00 |
| 9c6ab1d5a822205fbba82d5e93613dd |   -0.00 |
| de59667992e5045ff98e8f0b798bb06 |    0.00 |
| 182fedf6bf740de209da658362307d6 |    0.00 |
| e8e1fcd15fc82ae2cdc057341efe4af |    0.00 |
| 98c3a0a07af42bb0b96539e8ca6d31d |    0.00 |
| 93e8e62b5da5b4366de03e6e62ade25 |    0.00 |
| d7976b8667435bdb6939d5354a38102 |    0.00 |
| 3c6cb1aa8df451e0d2a234bea919edd |    0.00 |
| df6705732c57ce0979873bd566dd9ae |    0.00 |
| 1e6f698e737158fcfb04eed030ca038 |   26.65 |
+---------------------------------+---------+
16 rows in set (0.00 sec)

Now that the primary key is gone, the query returns 16 rows ordered by the computed column, which is what it should do.

Suggested fix:
Always apply the ORDER BY after the HAVING clause.  This should be done for efficiency anyway.
[10 May 2006 16:15] Baron Schwartz 
Run this file to populate the database with a set of test data that will trigger the bug.

Attachment: setup.sql (text/x-sql), 6.20 KiB.
[10 May 2006 16:18] Baron Schwartz 
When I said "I will attach a detailed test case to this bug" I meant "setup script" not "test case."  Sorry for any confusion.
[10 May 2006 16:25] MySQL Verification Team 
Thank you for the bug report. The server's version you are reporting
is pretty older, could you please test with the most recent 4.1.XX
version released?

Thanks in advance.
[10 May 2006 18:35] Baron Schwartz 
I am upgrading now.
[10 May 2006 18:41] MySQL Verification Team 
Thank you for the feedback. Open this bug report when you verified it
with the new server. Thanks in advance.
[10 May 2006 19:24] Baron Schwartz 
I get the same results on 4.1.19.
[10 May 2006 19:44] MySQL Verification Team 
Thank you for the feedback. I will test now your db and offended
query. Sorry I didn't noticed you had provide the dump file.
[10 May 2006 19:59] Baron Schwartz 
Here is another behavior that seems related.  Run the setup script, in version 5.0.19-log.  You need version 5.x so you can do a CAST as DECIMAL(8,2), which is not supported in earlier versions.  Now run the following statements to create a copy of the data with the amounts as FLOAT instead of DECIMAL:

alter table split change amount amount float not null default 0;

select t.id, sum(amount) as balance, cast(sum(amount) as decimal(8,2)) as balance2 from transaction as t inner join split as s on t.id = s.transaction group by t.id having sum(amount) <> 0 order by cast(sum(amount) as decimal(8,2));

+---------------------------------+-----------------------+----------+
| id                              | balance               | balance2 |
+---------------------------------+-----------------------+----------+
| 3c6cb1aa8df451e0d2a234bea919edd | -0.000392764806747437 | 0.00     |
| 182fedf6bf740de209da658362307d6 |  2.05039978027344e-05 | 0.00     |
| e8e1fcd15fc82ae2cdc057341efe4af |     -3.0517578125e-05 | 0.00     |
| de59667992e5045ff98e8f0b798bb06 |    3.814697265625e-06 | 0.00     |
| d7976b8667435bdb6939d5354a38102 | -5.96046447753906e-05 | 0.00     |
| 9c6ab1d5a822205fbba82d5e93613dd |   5.7220458984375e-06 | 0.00     |
| 96779b5478b7b4cda07e639729ac4ff | -2.92975455522537e-05 | 0.00     |
| 81487469bdbc9e862ddaf068086aabe |  -0.00031280517578125 | 0.00     |
| 40509d65d986eaad08b06ef59afce31 |   -8.392333984375e-05 | 0.00     |
| 1e6f698e737158fcfb04eed030ca038 |      26.6499996185303 | 26.65    |
| df6705732c57ce0979873bd566dd9ae |  -0.00029754638671875 | 0.00     |
| d8f6bf34a82b65005af11e53b69e9d0 |    6.866455078125e-05 | 0.00     |
| a482d3416841b6870e22aeb7bc1e65b |  3.05473804473877e-07 | 0.00     |
| 98c3a0a07af42bb0b96539e8ca6d31d |     7.62939453125e-05 | 0.00     |
| 74bc51a3fba5c0961fa3aac5afb7dc3 | -0.000702857971191406 | 0.00     |
+---------------------------------+-----------------------+----------+

The results are not ordered correctly.  Again it looks like the ORDER BY may be done at the wrong time.
[10 May 2006 20:03] Baron Schwartz 
Here is another strange behavior.  Run the setup script as before on version 4.1.19.  Now remove the key on the 'split' table and run the query against just that table, instead of joining the 'split' and 'transaction' tables:

alter table split drop key FK_split_transaction;

select transaction, sum(amount) as balance from split group by transaction having sum(amount) <> 0;

+---------------------------------+---------+
| transaction                     | balance |
+---------------------------------+---------+
| 182fedf6bf740de209da658362307d6 |    0.00 |
| 1e6f698e737158fcfb04eed030ca038 |   26.65 |
| 3c6cb1aa8df451e0d2a234bea919edd |    0.00 |
| 40509d65d986eaad08b06ef59afce31 |   -0.00 |
| 74bc51a3fba5c0961fa3aac5afb7dc3 |   -0.00 |
| 81487469bdbc9e862ddaf068086aabe |   -0.00 |
| 93e8e62b5da5b4366de03e6e62ade25 |    0.00 |
| 96779b5478b7b4cda07e639729ac4ff |   -0.00 |
| 98c3a0a07af42bb0b96539e8ca6d31d |    0.00 |
| 9c6ab1d5a822205fbba82d5e93613dd |   -0.00 |
| a482d3416841b6870e22aeb7bc1e65b |   -0.00 |
| d7976b8667435bdb6939d5354a38102 |    0.00 |
| d8f6bf34a82b65005af11e53b69e9d0 |   -0.00 |
| de59667992e5045ff98e8f0b798bb06 |    0.00 |
| df6705732c57ce0979873bd566dd9ae |    0.00 |
| e8e1fcd15fc82ae2cdc057341efe4af |    0.00 |
+---------------------------------+---------+

select transaction, sum(amount) as balance from split group by transaction having sum(amount) <> 0 order by balance;

+---------------------------------+---------+
| transaction                     | balance |
+---------------------------------+---------+
| 74bc51a3fba5c0961fa3aac5afb7dc3 |   -0.00 |
| 81487469bdbc9e862ddaf068086aabe |   -0.00 |
| d8f6bf34a82b65005af11e53b69e9d0 |   -0.00 |
| 40509d65d986eaad08b06ef59afce31 |   -0.00 |
| a482d3416841b6870e22aeb7bc1e65b |   -0.00 |
| 96779b5478b7b4cda07e639729ac4ff |   -0.00 |
| 9c6ab1d5a822205fbba82d5e93613dd |   -0.00 |
| de59667992e5045ff98e8f0b798bb06 |    0.00 |
| 182fedf6bf740de209da658362307d6 |    0.00 |
| e8e1fcd15fc82ae2cdc057341efe4af |    0.00 |
| 98c3a0a07af42bb0b96539e8ca6d31d |    0.00 |
| 93e8e62b5da5b4366de03e6e62ade25 |    0.00 |
| d7976b8667435bdb6939d5354a38102 |    0.00 |
| 3c6cb1aa8df451e0d2a234bea919edd |    0.00 |
| df6705732c57ce0979873bd566dd9ae |    0.00 |
| 1e6f698e737158fcfb04eed030ca038 |   26.65 |
+---------------------------------+---------+

The ORDER BY clause no longer changes the results without that key.
[1 Jun 2006 0:54] MySQL Verification Team 
Thank you for the bug report. Notice that the wrong result is when the
server returns 16 rows. You can see below that 5.0 server behaves
correctly with both queries returning 1 row:

miguel@hegel:~/dbs/4.1> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.21-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test                
Database changed
mysql> source /home/miguel/a/setup.sql
Query OK, 0 rows affected (0.01 sec)

<CUT>
mysql> select t.id, sum(amount) as balance from transaction as t inner join
    -> split as s on t.id = s.transaction group by t.id having sum(amount) <> 0;
+---------------------------------+---------+
| id                              | balance |
+---------------------------------+---------+
| 182fedf6bf740de209da658362307d6 |    0.00 |
| 1e6f698e737158fcfb04eed030ca038 |   26.65 |
| 3c6cb1aa8df451e0d2a234bea919edd |    0.00 |
| 40509d65d986eaad08b06ef59afce31 |   -0.00 |
| 74bc51a3fba5c0961fa3aac5afb7dc3 |   -0.00 |
| 81487469bdbc9e862ddaf068086aabe |   -0.00 |
| 93e8e62b5da5b4366de03e6e62ade25 |    0.00 |
| 96779b5478b7b4cda07e639729ac4ff |   -0.00 |
| 98c3a0a07af42bb0b96539e8ca6d31d |    0.00 |
| 9c6ab1d5a822205fbba82d5e93613dd |   -0.00 |
| a482d3416841b6870e22aeb7bc1e65b |   -0.00 |
| d7976b8667435bdb6939d5354a38102 |    0.00 |
| d8f6bf34a82b65005af11e53b69e9d0 |   -0.00 |
| de59667992e5045ff98e8f0b798bb06 |    0.00 |
| df6705732c57ce0979873bd566dd9ae |    0.00 |
| e8e1fcd15fc82ae2cdc057341efe4af |    0.00 |
+---------------------------------+---------+
16 rows in set (0.01 sec)

mysql> select t.id, sum(amount) as balance from transaction as t inner join
    -> split as s on t.id = s.transaction group by t.id having sum(amount) <> 0 order
    -> by balance;
+---------------------------------+---------+
| id                              | balance |
+---------------------------------+---------+
| 1e6f698e737158fcfb04eed030ca038 |   26.65 |
+---------------------------------+---------+
1 row in set (0.01 sec)

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

mysql> alter table transaction engine=MyISAM;
Query OK, 16 rows affected (0.02 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> alter table split engine=MyISAM;
Query OK, 84 rows affected (0.02 sec)
Records: 84  Duplicates: 0  Warnings: 0

mysql> select t.id, sum(amount) as balance from transaction as t inner join
    -> split as s on t.id = s.transaction group by t.id having sum(amount) <> 0;
+---------------------------------+---------+
| id                              | balance |
+---------------------------------+---------+
| 182fedf6bf740de209da658362307d6 |    0.00 |
| 1e6f698e737158fcfb04eed030ca038 |   26.65 |
| 3c6cb1aa8df451e0d2a234bea919edd |    0.00 |
| 40509d65d986eaad08b06ef59afce31 |   -0.00 |
| 74bc51a3fba5c0961fa3aac5afb7dc3 |   -0.00 |
| 81487469bdbc9e862ddaf068086aabe |   -0.00 |
| 93e8e62b5da5b4366de03e6e62ade25 |    0.00 |
| 96779b5478b7b4cda07e639729ac4ff |   -0.00 |
| 98c3a0a07af42bb0b96539e8ca6d31d |    0.00 |
| 9c6ab1d5a822205fbba82d5e93613dd |   -0.00 |
| a482d3416841b6870e22aeb7bc1e65b |   -0.00 |
| d7976b8667435bdb6939d5354a38102 |    0.00 |
| d8f6bf34a82b65005af11e53b69e9d0 |   -0.00 |
| de59667992e5045ff98e8f0b798bb06 |    0.00 |
| df6705732c57ce0979873bd566dd9ae |    0.00 |
| e8e1fcd15fc82ae2cdc057341efe4af |    0.00 |
+---------------------------------+---------+
16 rows in set (0.01 sec)

mysql> select t.id, sum(amount) as balance from transaction as t inner join
    -> split as s on t.id = s.transaction group by t.id having sum(amount) <> 0;
+---------------------------------+---------+
| id                              | balance |
+---------------------------------+---------+
| 182fedf6bf740de209da658362307d6 |    0.00 |
| 1e6f698e737158fcfb04eed030ca038 |   26.65 |
| 3c6cb1aa8df451e0d2a234bea919edd |    0.00 |
| 40509d65d986eaad08b06ef59afce31 |   -0.00 |
| 74bc51a3fba5c0961fa3aac5afb7dc3 |   -0.00 |
| 81487469bdbc9e862ddaf068086aabe |   -0.00 |
| 93e8e62b5da5b4366de03e6e62ade25 |    0.00 |
| 96779b5478b7b4cda07e639729ac4ff |   -0.00 |
| 98c3a0a07af42bb0b96539e8ca6d31d |    0.00 |
| 9c6ab1d5a822205fbba82d5e93613dd |   -0.00 |
| a482d3416841b6870e22aeb7bc1e65b |   -0.00 |
| d7976b8667435bdb6939d5354a38102 |    0.00 |
| d8f6bf34a82b65005af11e53b69e9d0 |   -0.00 |
| de59667992e5045ff98e8f0b798bb06 |    0.00 |
| df6705732c57ce0979873bd566dd9ae |    0.00 |
| e8e1fcd15fc82ae2cdc057341efe4af |    0.00 |
+---------------------------------+---------+
16 rows in set (0.00 sec)

mysql> 

mysql> 
--------------------------------------------------------------------------------------
miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select t.id, sum(amount) as balance from transaction as t inner join
    -> split as s on t.id = s.transaction group by t.id having sum(amount) <> 0;
+---------------------------------+---------+
| id                              | balance |
+---------------------------------+---------+
| 1e6f698e737158fcfb04eed030ca038 |   26.65 | 
+---------------------------------+---------+
1 row in set (0.01 sec)

mysql> select t.id, sum(amount) as balance from transaction as t inner join
    -> split as s on t.id = s.transaction group by t.id having sum(amount) <> 0 order
    -> by balance;
+---------------------------------+---------+
| id                              | balance |
+---------------------------------+---------+
| 1e6f698e737158fcfb04eed030ca038 |   26.65 | 
+---------------------------------+---------+
1 row in set (0.01 sec)

mysql>
[31 Jan 2007 5:51] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19061

ChangeSet@1.2608, 2007-01-31 09:51:05+04:00, ramil@mysql.com +10 -0
  fix for bug #19690: ORDER BY eliminates rows from the result
  
  Depending on the queries we use different data processing methods
  and can lose some data in case of double (and decimal in 4.1) fields.
  
  The fix consists of two parts:
  1. double comparison changed, now double a is equal to double b 
  if (a-b) is less than 5*0.1^(1 + max(a->decimals, b->decimals)). 
  For example, if a->decimals==1, b->decimals==2, a==b if (a-b)<0.005
  2. if we use a temporary table, store double values there as is 
  to avoid any data conversion (rounding).
[14 Feb 2007 14:58] Chad MILLER 
Available in 4.1.23, 5.0.36, and 5.1.16-beta.
[21 Feb 2007 3:17] Paul DuBois 
Noted in 4.1.23, 5.0.36, 5.1.16 changelogs.

ORDER BY on DOUBLE values could change the set of rows returned by a
query. In 4.1, this also applies to DECIMAL.