Op 13-01-2022 om 13:43 schreef Alvaro Herrera:
Apologies, there was a merge failure and I failed to notice. Here's the
correct patch.
(This is also in github.com/alvherre/postgres/tree/merge-15)
[20220113/v6-0001-MERGE-SQL-Command-following-SQL-2016.patch]
Good morning,
I got into this crash (may be the same as Jaime's):
#-----
# bash
t1=table1
t2=table2
psql -qX << SQL
drop table if exists $t1 cascade;
drop table if exists $t2 cascade;
create table $t1 as select /*cast(id::text as jsonb) js,*/ id from
generate_series(1,20) as f(id);
create table $t2 as select /*cast(id::text as jsonb) js,*/ id from
generate_series(1,20) as f(id);
delete from $t1 where id % 2 = 1;
delete from $t2 where id % 2 = 0;
( select 't1 - target', count(*) t1 from $t1
union all select 't2 - source', count(*) t2 from $t2 ) order by 1;
merge into $t1 as t1 using $t2 as t2 on t1.id = t2.id
when not matched and (t2.id > 10) and (t1.id > 10)
then do nothing
when not matched then insert values ( id )
when matched then do nothing ;
( select 't1 - target', count(*) t1 from $t1
union all select 't2 - source', count(*) t2 from $t2 ) order by 1 ;
SQL
#-----
Referencing alias 't1' in the WHEN NOT MATCHED member seems what
triggers his crash: when I remove the phrase 'and (t1.id > 10)', the
statement finishes correctly.
And I don't know if it is related but if I use this phrase:
when not matched and (id > 10)
I get:
ERROR: column "id" does not exist
LINE 2: when not matched and id > 0 -- (t2.id > 10) and (t1.id > 10)
^
HINT: There is a column named "id" in table "t1", but it cannot be
referenced from this part of the query.
Is that hint correct? Seems a bit strange.
Thanks,
Erik Rijkers
