Bug #25376 Submitted: wrong order with order by using sum and division 2 Jan 2007 22:33 16 Mar 2007 16:31 Matthew Kent Closed None MySQL Server S2 (Serious) 5.0.34-BK, 5.0.27-max-log Linux (Linux, Fedora Core 3) Evgeny Potemkin 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
`Bug #27386 was marked as duplicate of this one.`