MySQL 8 不检测选择不同查询中的功能依赖性

Question

我使用此查询创建一个表:

create table a (
  `id` int not null auto_increment,
  b varchar(10),
  primary key (`id`)
);

执行中

select distinct `id` from a order by `b`;

导致此错误:

ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'portal.a.b' which is not in SELECT list; this is incompatible with DISTINCT

但是如果我将查询更改为

select `id` from a group by `id` order by `b`;

这在逻辑上是等价的，它成功了。

我正在使用official Docker image for MySQL和 mysql --version 显示

mysql Ver 8.0.12 for Linux on x86_64 (MySQL Community Server - GPL)

MySQL 似乎仍然无法检测 select different 查询中的功能依赖关系。我对吗？ MySQL 的开发人员会修复这个问题吗？

Answer 1

相反的行为实际上被报告为错误并且 fixed in MySQL 5.7.5 :

Several issues related to the ONLY_FULL_GROUP_BY SQL mode were corrected:

• With ONLY_FULL_GROUP_BY enabled, some valid queries were rejected if the accessed table was replaced by a view.

• Queries of the form SELECT DISTINCT col1 ... ORDER BY col2 qualify as forbidden by SQL2003 (hidden ORDER BY columns combined with DISTINCT), but were not rejected with the ONLY_FULL_GROUP_BY SQL mode enabled.

此外，documentation明确指出这是预期的行为:

To prevent this problem, a query that has DISTINCT and ORDER BY is rejected as invalid if any ORDER BY expression does not satisfy at least one of these conditions:

• The expression is equal to one in the select list

• All columns referenced by the expression and belonging to the query's selected tables are elements of the select list

没有提及功能依赖性。与 group by 相比，相关错误消息也没有引用功能依赖项。

虽然 sql 标准中的可选功能 T301 函数依赖项确实修改了 group by(以及其他)的一致性规则，但它并没有不改变对 order by 加上 distinct 的任何限制，这意味着它仍然是被禁止的。