BigQuery中NULL数组写入表后转为空数组是否为预期行为？

执行以下CTE查询时，得到预期结果[7]：

WITH Items AS (
  SELECT [] AS num1, CAST(NULL AS ARRAY<INTEGER>) as num2
)
SELECT COALESCE(num2, [7]) FROM Items;

但将Items保存为临时表后，执行相同逻辑的查询得到的却是[]，且通过WHERE子句确认该结果并非NULL：

CREATE TEMP TABLE Items
AS
(
  SELECT [] AS num1, CAST(NULL AS ARRAY<INTEGER>) as num2
);

WITH t as
(
  SELECT COALESCE(num2, [7]) as test FROM Items
)
SELECT *
FROM t
WHERE array_length(test)=0

根据BigQuery官方文档说明，空数组与NULL数组是不同的，请问此现象是否为预期行为？

解答 #

这是预期行为，核心原因是BigQuery在临时表存储中对数组类型NULL值的特殊处理逻辑：

• 在CTE场景中，CAST(NULL AS ARRAY<INTEGER>)定义的是真正的数组类型NULL值，COALESCE会识别该值为NULL，因此触发替换逻辑返回备选值[7]。
• 但创建临时表时，BigQuery不会存储数组类型的NULL值，而是自动将其转换为空数组[]。当你从临时表读取num2时，它已经是一个空数组而非NULL，此时COALESCE不会执行替换（因为空数组不属于NULL范畴），最终返回空数组[]。

你可以通过以下查询直接验证临时表中num2的实际值：

SELECT num2, num2 IS NULL, num2 = [] FROM Items;

执行结果会显示num2 IS NULL为false，num2 = []为true，证明临时表中的num2已被转换为空数组。

内容的提问来源于stack exchange，提问作者Dominik