使用unnest时DuckDB与PostgreSQL SQL结果不一致求助

问题原因 #

PostgreSQL中，unnest(string_to_array(...))会严格按原字符串拆分后的数组顺序输出元素，子查询里的row_number() over()在未指定ORDER BY时，默认遵循unnest的输出顺序生成序号。但DuckDB中，窗口函数row_number()如果没有明确指定ORDER BY子句，无法保证序号与unnest的元素顺序完全一致，这会导致后续分组时的(nr-1)//4计算错误，最终造成字段值错位。

修复方案 #

修改子查询部分，通过unnest ... WITH ORDINALITY直接获取元素的原始位置序号，替代依赖窗口函数生成序号的方式，确保序号与拆分后的元素顺序严格对应。

修改后的DuckDB代码 #

a = duckdb.sql("""
create table test (txn_id int, description varchar(200));
insert into test values 
(3332654,'CAD@10.00@0.9397@$10.64@THB@150.00@23.8235@$6.30@KRW@36,000.00@860.3722@$41.84@FJD@20.00@1.5711@$12.73@EUR@5.00@0.6013@$8.32@SGP@15.00@10.6013@$18.32'),
(3332655,'CAD@11.00@0.8197@$11.64@THB@110.00@21.8135@$61.30@KRW@36,001.00@861.3722@$411.84@FJD@21.00@11.5711@$11.73');

select 
    txn_id,
    max(case when nr%4=1 then elem end) cncy_cd,
    max(case when nr%4=2 then elem end) txn_cncy_amt,
    max(case when nr%4=3 then elem end) txn_exch_rate,
    max(case when nr%4=0 then elem end) txn_nzd_amt
from test t
left join lateral (
    select elem, ordinality as nr 
    from unnest(string_to_array(t.description, '@')) WITH ORDINALITY AS a(elem, ordinality)
) ON true  
group by txn_id, ((nr-1)//4);
""")

关键修改点 #

1. 用unnest ... WITH ORDINALITY替代单纯的unnest，直接获取每个元素在拆分数组中的原始位置序号（ordinality），避免窗口函数无排序时的顺序不确定性。
2. 子查询中直接用ordinality作为nr，不再依赖row_number() over()生成序号，确保序号与元素顺序完全匹配。

修改后DuckDB的输出结果会与PostgreSQL完全一致，不会出现字段错位问题。

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