mysql avg排序问题,当包含某些列时，MySQL ORDER BY AVG（）DESC不起作用

I'm doing a query to return all the rows in table1, along with their average rating from table2:

SELECT `table1`.`description`, AVG( `table2`.`rating` ) AS avg_rating

FROM `table1` LEFT JOIN `table2` ON ( `table2`.`botid` = `table1`.`id` )

GROUP BY `table1`.`id`

ORDER BY avg_rating DESC

The problem is that even though I specify DESC, the results are being returned ASC:

+-------------+------------+

| description | avg_rating |

+-------------+------------+

| test2 | 1.0000 |

| test3 | 3.0000 |

| test4 | 3.0000 |

| saasdf | 4.0000 |

+-------------+------------+

Why isn't MySQL honoring ORDER BY...DESC?

Even weirder, when I remove table1.description from the list of columns to retrieve, it works properly:

SELECT AVG( `table2`.`rating` ) AS avg_rating

FROM `table1` LEFT JOIN `table2` ON ( `table2`.`botid` = `table1`.`id` )

GROUP BY `table1`.`id`

ORDER BY avg_rating DESC

Returns:

+------------+

| avg_rating |

+------------+

| 4.0000 |

| 3.0000 |

| 3.0000 |

| 1.0000 |

+------------+

Here is my data:

table1:

id|description

--+-----------

6|test2

16|test3

54|test4

72|saasdf

table2:

botid|rating

-----+------

6|1

16|3

54|3

72|4

(For the sake of this example there is a one-to-one relationship between the records in table1 and table2, but in reality there will be a one-to-many relationship.)

And my schema:

CREATE TABLE `table1` (

`id` int(11) NOT NULL,

`description` longtext NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `table2` (

`botid` int(11) NOT NULL,

`rating` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

There are indexes on both table1.id and table2.botid, although that shouldn't affect the results. I'm using MySQL 5.7.7-rc-log.

I have plenty of experience using aggregate functions, GROUP BY and ORDER BY but I've never come across anything like this. Any suggestions?

解决方案

Please upgrade to a GA version (5.7.9 was the first; 5.7.18 exists), then test again. IIRC, there was a bug somewhere in this area.

If the bug persists, provide the commands to reproduce the error and submit it to bugs.mysql.com .

I strongly recommend you change from MyISAM to InnoDB. Oracle may throw out the bug report since it involves MyISAM.

Meanwhile, you could see if this gives you the correct ordering:

SELECT `table1`.`description`,

( SELECT AVG(`rating` )

FROM table2

WHERE botid = table1.id

) AS avg_rating

FROM `table1`

ORDER BY avg_rating DESC

Provide EXPLAIN FORMAT=JSON SELECT ... for both your version and my version.

Explanation

Your original query appears to have the "inflate-deflate" problem of JOIN ... GROUP BY. First the JOIN gathers more "rows" than you started with, then the GROUP BY shrinks it back to the original number.

My rewrite sticks to the original number of rows (in table1) and probes table 2 for the necessary stuff. Primarily (in this situation) it avoids the tmp table and filesort.