当包含某些列时，MySQL ORDER BY AVG() DESC 不起作用

我正在执行一个查询以返回 table1 中的所有行以及 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

问题是即使我指定DESC，正在返回结果ASC:

+-------------+------------+
| description | avg_rating |
+-------------+------------+
| test2       |     1.0000 |
| test3       |     3.0000 |
| test4       |     3.0000 |
| saasdf      |     4.0000 |
+-------------+------------+

为什么 MySQL 不尊重ORDER BY...DESC?

更奇怪的是，当我删除table1.description从要检索的列列表中，它可以正常工作：

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 |
+------------+

这是我的数据：

table1:

id|description
--+-----------
 6|test2
16|test3
54|test4
72|saasdf

table2:

botid|rating
-----+------
    6|1
   16|3
   54|3
   72|4

（就本示例而言，中的记录之间存在一对一的关系table1 and table2，但实际上会有一对多的关系。）

和我的架构：

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;

两者都有索引table1.id and table2.botid，尽管这不应该影响结果。我正在使用 MySQL 5.7.7-rc-log。

我有丰富的使用聚合函数、GROUP BY 和 ORDER BY 的经验，但我从未遇到过这样的事情。有什么建议么？

请升级到GA版本（5.7.9是第一个；5.7.18存在），然后再次测试。 IIRC，这个区域的某个地方有一个错误。

如果错误仍然存​​在，请提供重现错误的命令并将其提交到 bugs.mysql.com 。

我强烈建议您从 MyISAM 更改为 InnoDB。 Oracle可能会丢弃该错误报告，因为它涉及MyISAM。

同时，您可以看看这是否为您提供了正确的顺序：

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 ...对于你的版本和我的版本。

解释

您的原始查询似乎存在“膨胀-放气”问题JOIN ... GROUP BY。首先是JOIN收集比你开始时更多的“行”，然后GROUP BY将其缩小回原始数字。

我的重写坚持原来的行数（在table1）并探索表 2 以获取必要的内容。主要（在这种情况下）它避免了 tmp 表和文件排序。