mysql - SQL 查询似乎返回不完整的结果集

Question

以下查询返回许多正确的行，但不返回 seed = '1985.00-Miller-13' 的行(还有其他缺失，但这只是一个示例):

SELECT g.dam_alias "Seed" 
FROM genetic g LEFT OUTER JOIN (genetic g1d)
ON (g.dam_alias = g1d.genetic_alias)
GROUP BY g1d.dam_alias , g1d.sire_alias;

但是，如果我将 WHERE 子句添加到指定我认为缺失的行的查询中，它就会显示出来。这是修改后的查询:

SELECT g.dam_alias "Seed"
FROM genetic g LEFT OUTER JOIN (genetic g1d)
ON (g.dam_alias = g1d.genetic_alias)
WHERE g.dam_alias = '1985.00-Miller-13' -- this is the added line
GROUP BY g1d.dam_alias , g1d.sire_alias;

如果我的原始查询确实不应该返回种子“1985.00-Miller-13”的行，我会期望第二个查询不返回任何行。

起初我怀疑我的键/索引已损坏，所以我进行了数据库转储并从生成的 sql 脚本中重建。我已经使用 MYSQL v5.6 和 MariasDB v 10.0.17 复制了这个问题

我已经手动检查了数据并在纸上遍历了查询，没有发现任何与我的预期结果不一致的地方。

如有任何建议，我们将不胜感激。我可以提供任何人可能需要的任何额外信息/模式/数据。

谢谢。

Answer 1

您在 g1d.dam_alias 上分组, 但选择 g.dam_alias .

大多数其他 RDBMS 产品不允许从组内选择未聚合的列，因为从组内哪个记录中返回一个值是不明确的。然而，MySQL 确实允许此操作作为性能增强，尽管文档清楚地表明在这种情况下的结果是不确定的:

参见 MySQL Handling of GROUP BY (强调):

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

发生了什么(大概——我们不能在没有看到基础数据的情况下肯定地说)发生的是 g.dam_alias = '1985.00-Miller-13'存在于某些组中，但 g.dam_alias 的值不同而是从这些组中的其他记录中选择。当您添加过滤器时，没有其他值可供选择，因此选择的值保证是您期望的值。

如果不了解所需查询的语义，就很难提出解决此问题的建议。