在SQLite GROUP BY查询的SELECT列表中包括额外的列是否安全？

Question

我有一个简单的SQLite表，称为“消息”：

sequence INTEGER PRIMARY KEY
type TEXT
content TEXT

我想获取每种类型的最后一条消息的内容（由其顺序确定）。令我惊讶的是，以下简单查询有效：

SELECT MAX(sequence), type, content
FROM message
GROUP BY type

令人惊讶，因为我知道MSSQL或Postgres会拒绝在SELECT列表中包括不属于GROUP BY子句或聚合函数一部分的列，因此我必须进行联接，如下所示：

SELECT m.sequence, m.type, m.content
FROM
(
    SELECT MAX(sequence) as sequence, type
    FROM message
    GROUP BY type
) g
JOIN message m
ON g.sequence = m.message_sequence

我的问题是：在SQLite中使用第一种更简单的查询形式是否安全？从直觉上讲，它选择的是与“ MAX（sequence）”值匹配的“
content”值，但是文档似乎根本没有讨论这一点。当然，如果序列不是唯一的，那么结果将是不确定的。但是，如果序列是唯一的，例如在我的情况下，这是可以保证的，还是只是幸运的实现细节会随时更改？

Answer 1

如果多余的列在 功能上取决于 您分组的列，则可以“安全”地使用这些查询，即不会导致歧义的结果：

SELECT c.parent_id, COUNT(*), p.any_column
FROM child_table c 
JOIN parent_table p USING (parent_id)
GROUP BY c.parent_id;

上面的示例将在SQLite中工作，并产生明确的结果，因为p.any_column每个组不可能有多个值。但是，此查询严格违反SQL标准，并且大多数品牌的RDBMS都会引发错误。

但是，编写产生歧义结果的查询太容易了。当命名每个组具有多个值的列时，您将无法控制在结果集中返回哪个值。

实际上，MySQL从物理存储的 第一 行返回该值，而SQLite从 最后
一行返回该值。但这完全依赖于实现且不可靠。如果任一软件的下一版本更改了内部结构，则升级后您可能会获得不同的查询结果。因此，最好不要依赖此行为。

对于您的示例，wherecontent应该“直观地”具有sequenceMAX为行中的值。但这真的很直观吗？考虑以下其他情况：

SELECT MAX(sequence), MIN(sequence), type, content
FROM message
GROUP BY type

那么，现在哪一行提供了价值content？行sequence是MAX，还是行sequenceMIN？

如果您使用非唯一列（例如date），并且有多行具有相同的MAX值date，但具有不同的值content怎么办？

SELECT MAX(date), type, content
FROM message
GROUP BY type

其他聚合函数（例如AVG()或）SUM()呢？汇总的值可能对应于表中没有单独的行。那么，现在应该为哪一行提供值content？

SELECT AVG(sequence), type, content
FROM message
GROUP BY type