为什么子查询中的ORDER BY解决方法不能一致工作

Question

为了获得某个标识符组合的最新记录，我使用以下查询：

SELECT t1.*
FROM (
SELECT id, b_id, c_id
FROM a
ORDER BY epoch DESC
LIMIT 18446744073709551615
) AS t1
GROUP BY t1.b_id, t1.c_id

如果存在b_id+c_id的组合的多个记录，则它将始终选择具有最高值epoch的记录(因此，也是时间上最晚的记录(。

添加LIMIT是为了强制MariaDB实际排序结果。我在应用程序中成功地使用了很多这种构造，其他人也是如此。

然而，现在我在我的应用程序中遇到了一个完全相同的查询；意外地"；在子查询中使用了超出严格要求的列数：

SELECT t1.*
FROM (
SELECT id, b_id, c_id, and, some, other, columns, ...
FROM a
ORDER BY epoch DESC
LIMIT 18446744073709551615
) AS t1
GROUP BY t1.b_id, t1.c_id

我已经测试了这两个查询。而完全相同的查询，但只更改那些附加列，会使结果变得不正确。事实上，列的数量决定了结果。如果我有<=28列，结果还可以。如果我有29列，那么它会给出第三个最新的记录(这也是错误的(，如果我有30-36列，它总是给出第二个最新记录(36是表a的总数(。在我的测试中，删除或添加哪一列似乎并不重要。

我很难弄清楚添加更多列后行为发生变化的确切原因。此外，也许是偶然的，它昨天仍然给出了正确的结果。但今天，结果突然发生了变化，可能是在a表中添加了新的记录(具有不相关的标识符(之后。我试过使用EXPLAIN:

# The first query, with columns: id, b_id, c_id
id     select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   PRIMARY     <derived2>  ALL     NULL    NULL    NULL    NULL    280     Using where; Using temporary; Using filesort
2   DERIVED     a   ALL     NULL    NULL    NULL    NULL    280     Using filesort
# The second query, with columns: id, b_id, c_id, and, some, other, columns, ...
id     select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   PRIMARY     <derived2>  ALL     NULL    NULL    NULL    NULL    276     Using where; Using temporary; Using filesort
2   DERIVED     a   ALL     NULL    NULL    NULL    NULL    276     Using filesort

但这对我没有太大帮助，除此之外，我可以看到key_len是不同的。在第二查询中错误接收的第二个最新记录是其中id = 276的记录，其使用第一查询正确检索的实际最新记录为其中id = 278的记录。现在总共有307排，昨天可能只有300排。我不知道如何解释这些结果来理解出了什么问题。有人知道吗？如果没有，我还能做些什么来找出是什么导致了这些奇怪的结果？

Answer 1

这是一个格式错误的查询，应该会生成语法错误：

SELECT t1.*
FROM (SELECT id, b_id, c_id
FROM a
ORDER BY epoch DESC
LIMIT 18446744073709551615
) t1
GROUP BY t1.b_id, t1.c_id;

为什么？您正在选择3个没有聚合函数的列。但group by只有两列。令人高兴的是，这是MySQL中的一个语法错误，使用默认设置。最后(MySQL在8.0版本之前接受这种非标准语法。(

您可以使用相关的子查询：来执行您想要的操作

select a.*
from a
where a.epoch = (select max(a2.epoch)
from a a2
where a2.b_id = a.b_id and a2.c_id = a.c_id
);

对于a(b_id, c_id, epoch)上的索引，这可能也比聚合快——即使在某些情况下这种方法也能正常工作。

Answer 2

为什么不使用窗口函数，而不是这个肮脏的解决方法，它依赖于MySQL/MariaDB关于group by的非标准行为？

select *
from (
select a.*, row_number() over(partition by b_id, c_id order by epoch desc) rn
from a
) a
where rn = 1

这适用于MySQL 8.0和Maria DB 10.2或更高版本。在早期版本中，一种替代方案是相关子查询：

select *
from a
where epoch = (select max(a1.epoch) from a a1 where a1.b_id = a.b_id and a1.c_id = a.c_id)