| 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: | |
| 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 |
[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.
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? :)