ORA-38104: Columns referenced in the ON Clause cannot be updated

[AnghelLeonard]

Expected behavior

Expected a result set.

Actual behavior

ORA-38104: Columns referenced in the ON Clause cannot be updated

Steps to reproduce the problem

Tried to run this query and got ORA-38104:

ctx.mergeInto(PRODUCT) 
     .usingDual() 
     .on(PRODUCT.PRODUCT_NAME.eq("1952 Alpine Renault 1300")) 
     .whenMatchedThenUpdate() 
     .set(PRODUCT.PRODUCT_NAME, "1952 Alpine Renault 1600") 
     .whenNotMatchedThenInsert(PRODUCT.PRODUCT_NAME) 
     .values("1952 Alpine Renault 1600") 
     .execute();

The workarounds from here are working fine:
https://blog.jooq.org/how-to-work-around-ora-38104-columns-referenced-in-the-on-clause-cannot-be-updated/

Versions

• jOOQ:3.14.4
• Java:14
• Database (include vendor):Oracle 18c, Express Edition (XE) Release 18.4.0.0.0
• OS:Windows 10
• JDBC Driver (include name if inofficial driver): ojdbc8-19.3.0.0 and ucp-19.3.0.0

[lukaseder]

This also appears on INSERT .. ON DUPLICATE KEY UPDATE queries that have policies on them (see #2682), which makes fixing this more important now.

[lukaseder]

The workarounds from here are working fine:
https://blog.jooq.org/how-to-work-around-ora-38104-columns-referenced-in-the-on-clause-cannot-be-updated/

Not on 23c anymore. Most don't work anymore, probably because someone at Oracle read my blog post 😅. The approach using the scalar subquery still works, though, and it still has the performance issue mentioned in the blog post, though the alternative is simply that the query doesn't work at all.

[lukaseder]

Fixed for jOOQ 3.19.

I won't backport this fix as it might break assumptions by other customers (as it reverts #8158 and #10056, superseding those previous fixes), plus the workarounds can always be applied manually on queries.