Hang on merge statement with data change delta table

[htran1]

Hang is observed in 2.1.210, but not 2.0.206.

In the following script, the first execution of the merge returns 1 row. The second execution hangs.

drop table if exists test;
create table test (id int primary key, val1 int, val2 int);

insert into test (id, val1, val2) values (1, 2, 3);
insert into test (id, val1, val2) values (2, 2, 3);
insert into test (id, val1, val2) values (3, 2, 3);
commit;

select test.id from final table (
    merge into test using (
        select t.id, t.val1, t.val2 from (select 1, 2, 3) t (id, val1, val2)) t on test.id = t.id when matched and test.id = 1 then update set test.id = 1, test.val1 = 2, test.val2 = 3 when not matched then insert(id, val1, val2) values (t.id, t.val1, t.val2)) test;

select test.id from final table (
    merge into test using (
        select t.id, t.val1, t.val2 from (select 1, 2, 3) t (id, val1, val2)) t on test.id = t.id when matched and test.id = 1 then update set test.id = 1, test.val1 = 2, test.val2 = 3 when not matched then insert(id, val1, val2) values (t.id, t.val1, t.val2)) test;

[katzyn]

Table filter is somehow joined to itself.

It looks like different aliases in subquery (t (id, val1, val2)) and in the main query (t) can be used as a temporary workaround, this test case fails only if t is used in both places.

[htran1]

I'm using JOOQ and it generates the queries using the same alias and I don't think there is a way to control that.

[lukaseder]

I'll look into generating alternative aliases as I've run across this regression as well, see #3398

[lukaseder]

A workaround seems to be using StatementType.STATIC_STATEMENT in your Settings to avoid the PreparedStatement. I couldn't reproduce the problem with static statements in JDBC or in a SQL editor.

[lukaseder]

The fix for this problem has been released in jOOQ 3.16.3