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

一尘不染

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

sql

我有一个简单的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”值，但是文档似乎根本没有讨论这一点。当然，如果序列不是唯一的，那么结果将是不确定的。但是，如果序列是唯一的，例如在我的情况下，这是可以保证的，还是只是幸运的实现细节会随时更改？

阅读 174

2021-03-08

共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

2021-03-08