Bug #25376 wrong order with order by using sum and division
Submitted: 2 Jan 2007 22:33 Modified: 16 Mar 2007 16:31
Reporter: Matthew Kent Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.34-BK, 5.0.27-max-log OS:Linux (Linux, Fedora Core 3)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[2 Jan 2007 22:33] Matthew Kent
Description:
Getting some interesting results when trying to use the result of a division of 2 sums in an order by.

Problem being it works from the command line using an alias, but when entered as a view the alias is removed and the query produces out of order results. 

Can reproduce the problem as a one liner.

Perhaps I'm missing something obvious?

How to repeat:
This is neat!

first the correct result:

mysql> select a,(sum(b)/sum(c)) as ratio from (select 1 as a, 2 as b, 3 as c union select 9,8,3 union select 19,4,3 union select 1,4,9) as totals group by a order by ratio;
--------------
select a,(sum(b)/sum(c)) as ratio from (select 1 as a, 2 as b, 3 as c union select 9,8,3 union select 19,4,3 union select 1,4,9) as totals group by a order by ratio
--------------

+----+--------+
| a  | ratio  |
+----+--------+
|  1 | 0.5000 |
| 19 | 1.3333 |
|  9 | 2.6667 |
+----+--------+
3 rows in set (0.00 sec)

okay great, now lets try the version with the alias removed (the same one that is entered when I create my view):

mysql> explain extended select a,(sum(b)/sum(c)) as ratio from (select 1 as a, 2 as b, 3 as c union select 9,8,3 union select 19,4,3 union select 1,4,9) as totals group by a order by ratio;
--------------
explain extended select a,(sum(b)/sum(c)) as ratio from (select 1 as a, 2 as b, 3 as c union select 9,8,3 union select 19,4,3 union select 1,4,9) as totals group by a order by ratio
--------------

+----+--------------+----------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type  | table          | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+--------------+----------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY      | <derived2>     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using temporary; Using filesort |
|  2 | DERIVED      | NULL           | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used                  |
|  3 | UNION        | NULL           | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used                  |
|  4 | UNION        | NULL           | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used                  |
|  5 | UNION        | NULL           | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used                  |
| NULL | UNION RESULT | <union2,3,4,5> | ALL  | NULL          | NULL | NULL    | NULL | NULL |                                 |
+----+--------------+----------------+------+---------------+------+---------+------+------+---------------------------------+
6 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
--------------
show warnings
--------------

+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                          |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `totals`.`a` AS `a`,(sum(`totals`.`b`) / sum(`totals`.`c`)) AS `ratio` from (select 1 AS `a`,2 AS `b`,3 AS `c` union select 9 AS `9`,8 AS `8`,3 AS `3` union select 19 AS `19`,4 AS `4`,3 AS `3` union select 1 AS `1`,4 AS `4`,9 AS `9`) `totals` group by `totals`.`a` order by (sum(`totals`.`b`) / sum(`totals`.`c`)) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select `totals`.`a` AS `a`,(sum(`totals`.`b`) / sum(`totals`.`c`)) AS `ratio` from (select 1 AS `a`,2 AS `b`,3 AS `c` union select 9 AS `9`,8 AS `8`,3 AS `3` union select 19 AS `19`,4 AS `4`,3 AS `3` union select 1 AS `1`,4 AS `4`,9 AS `9`) `totals` group by `totals`.`a` order by (sum(`totals`.`b`) / sum(`totals`.`c`))
    -> ;
--------------
select `totals`.`a` AS `a`,(sum(`totals`.`b`) / sum(`totals`.`c`)) AS `ratio` from (select 1 AS `a`,2 AS `b`,3 AS `c` union select 9 AS `9`,8 AS `8`,3 AS `3` union select 19 AS `19`,4 AS `4`,3 AS `3` union select 1 AS `1`,4 AS `4`,9 AS `9`) `totals` group by `totals`.`a` order by (sum(`totals`.`b`) / sum(`totals`.`c`))
--------------

+----+--------+
| a  | ratio  |
+----+--------+
|  1 | 0.5000 |
|  9 | 2.6667 |
| 19 | 1.3333 |
+----+--------+
3 rows in set (0.00 sec)

mysql>

oops! that's not right.

Suggested fix:
Make the order by obey me? :)
[3 Jan 2007 11:16] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.34-BK on Linux:

openxs@suse:~/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 1
Server version: 5.0.34-debug Source distribution

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

mysql> select a,(sum(b)/sum(c)) as ratio from (select 1 as a, 2 as b, 3 as c
    -> union select 9,8,3 union select 19,4,3 union select 1,4,9) as totals gro
up by a
    -> order by ratio;
+----+--------+
| a  | ratio  |
+----+--------+
|  1 | 0.5000 |
| 19 | 1.3333 |
|  9 | 2.6667 |
+----+--------+
3 rows in set (0.01 sec)

mysql> select a,(sum(b)/sum(c)) as ratio from (select 1 as a, 2 as b, 3 as c un
ion select 9,8,3 union select 19,4,3 union select 1,4,9) as totals group by a o
rder by sum(b)/sum(c);
+----+--------+
| a  | ratio  |
+----+--------+
|  1 | 0.5000 |
|  9 | 2.6667 |
| 19 | 1.3333 |
+----+--------+
3 rows in set (0.01 sec)
[12 Feb 2007 17:08] Chad MILLER
Not related to union or view: 

CREATE TABLE `bug25376` (`a` int not null primary key auto_increment, `b` int, `c` int);
insert into bug25376 (b, c) values (2, 3);
insert into bug25376 (b, c) values (8, 3);
insert into bug25376 (b, c) values (4, 3);
select sum(b)/sum(c) as sort_column from bug25376 group by a order by sort_column desc;
sort_column
2.6667
1.3333
0.6667
select sum(b)/sum(c) as sort_column from bug25376 group by a order by sum(b)/sum(c) desc;
sort_column
0.6667
2.6667
1.3333
drop table bug25376;
[6 Mar 2007 18:43] 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/21270

ChangeSet@1.2431, 2007-03-06 21:40:47+03:00, evgen@moonbone.local +3 -0
  Bug#25376: Incomplete setup of ORDER BY clause results in a wrong result.
  
  Functions over sum functions wasn't set up correctly for the ORDER BY clause
  which leads to a wrong order of the result set.
  
  The find_order_in_list() function now calls split_sum_func() function for
  the ORDER BY item being fixed to setup sum functions.
[6 Mar 2007 20:59] 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/21297

ChangeSet@1.2431, 2007-03-06 23:58:10+03:00, evgen@moonbone.local +3 -0
  Bug#25376: Incomplete setup of ORDER BY clause results in a wrong result.
  
  Functions over sum functions wasn't set up correctly for the ORDER BY clause
  which leads to a wrong order of the result set.
  
  The split_sum_func() function is called now for each ORDER BY item that
  contains a sum function to set it up correctly.
[12 Mar 2007 5:11] Igor Babaev
Pushed to 5.0.38, 5.1.17
[16 Mar 2007 16:31] Paul Dubois
Noted in 5.0.38, 5.1.17 changelogs.

Expressions involving SUM(), when used in an ORDER BY clause, could
lead to out-of-order results.
[23 Mar 2007 9:42] Sveta Smirnova
Bug #27386 was marked as duplicate of this one.