sql - 为什么 Postgres 允许我有不在分组依据中的列？

Question

为什么这个查询:

SELECT inv.batch_number_id AS batch_number_id,
   inv.expiry_date AS expiry_date,
   inv.facility_id AS facility_id,
   inv.id AS id,
   fac.code AS facility_code,
   inv_st.description AS status,
   bn.batch_nbr AS batch_number,
   bn.expiry_date AS batch_expiry_date,
   sum(CASE WHEN al.status_id < 90 THEN al.alloc_qty ELSE 0 END) AS alloc_qty
FROM inventory inv
INNER JOIN facility fac ON inv.facility_id = fac.id
INNER JOIN inventory_status inv_st ON inv.status_id = inv_st.id
LEFT OUTER JOIN batch_number bn ON inv.batch_number_id = bn.id
LEFT OUTER JOIN allocation al ON al.from_inventory_id = inv.id
GROUP BY inv.id,
     facility_code,
     status,
     batch_number,
     batch_expiry_date

在 postgres 9.1.6 中工作？

来自文档:“当存在 GROUP BY 时，SELECT 列表表达式引用未分组的列是无效的，除非在聚合函数内，因为对于未分组的列将返回多个可能的值。”

如果我将 fac.id 添加到选择表达式，我会得到预期的“错误:列“fac.id”必须出现在 GROUP BY 子句中或用于聚合函数中”。为什么 postgres 提示该列却忽略了 facility_id？ (原始查询中还有一大堆其他列也以同样的方式违反了我的预期。为了清楚起见，该示例被缩短了。)

Answer 1

The whole quote is :

When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or if the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

更新:此功能已添加到 9.1 中