Snowflake中Cross Join后Left Lateral Flatten未返回预期行数问题

我有一个半结构化列，希望在Cross Join之后执行Left Lateral Join，但结果不符合预期。

我的SQL代码 #

with t as (
    select parse_json('{"1": 1, "2": 2}') as col
)

, cartesian as (
    select 1 as a union
    select 2 as a union
    select 3 as a
)

select *
from t
cross join cartesian
left join lateral flatten(input => t.col) as js
  on js.key::int = cartesian.a::int

预期与实际结果 #

• 预期：Cross Join将1行扩展为3行，Left Lateral Join不会减少行数，最终返回3行（包括a=3的行，对应js列全部为NULL）。
• 实际结果：只返回了2行（a=1和a=2的行）：

原因分析 #

问题出在LEFT JOIN LATERAL FLATTEN的写法逻辑上：当前写法是先对t.col做全量展开（得到2行），再与笛卡尔积后的3行做左连接。Snowflake中，这种写法下当FLATTEN结果与主表行无匹配on条件时，不会触发左连接的主表行保留规则，导致丢失a=3的行。

解决方案 #

将过滤条件嵌入FLATTEN的子查询中，确保对笛卡尔积后的每一行只尝试展开匹配的键值对，无匹配时返回空结果，左连接会自动保留主表行：

with t as (
    select parse_json('{"1": 1, "2": 2}') as col
)
, cartesian as (
    select 1 as a union
    select 2 as a union
    select 3 as a
)
select *
from t
cross join cartesian
left lateral join (
    select * 
    from flatten(input => t.col) 
    where key::int = cartesian.a::int
) as js

执行后会得到预期的3行结果，其中a=3的行对应的js列全部为NULL。

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