我有一个半 struct 的列,我想在交叉连接后进行左侧连接.
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
在上面的例子中,我预计原始的交叉联接会将结果计数从1扩展到3;而左侧联接不应该减少返回的行数.
我得到的结果是出乎意料的:
COL | A | SEQ | KEY | PATH | INDEX | VALUE | THIS |
---|---|---|---|---|---|---|---|
{ "1": 1, "2": 2 } |
1 | 1 | 1 | ['1'] | NULL | 1 | { "1": 1, "2": 2 } |
{ "1": 1, "2": 2 } |
2 | 2 | 2 | ['2'] | NULL | 2 | { "1": 1, "2": 2 } |
是我疯了,还是left
关键字在这里没有发挥应有的作用?