Emulate INSERT .. DEFAULT VALUES .. ON CONFLICT using MERGE

[lukaseder]

The current implementation of INSERT .. DEFAULT VALUES .. ON CONFLICT .. DO UPDATE (see #6083) only works for PostgreSQL. It is difficult to emulate using MERGE, because the assumption here is that a conflict on a default value would lead to an update.

Given:

create table "t"(
  "i" int null default 1,
  primary key ("i")
);

Run this twice:

insert into "t"
default values
on conflict ("i")
do update set "i" = 2
returning *;

In PostgreSQL, this can be run twice indeed, but not e.g. with H2, where this generates:

merge into "t"
using (
  select 1 "one"
)
on "i" = null -- We don't really have a join criteria to use here
when matched then update set
  "i" = 2
when not matched then insert
values (); -- This syntax works but is not documented. H2 supports DEFAULT VALUES, however

Affected dialects (at least)

• [ ] Db2
• [ ] Derby
• [ ] Firebird
• [ ] H2

Related edge case

• https://github.com/jOOQ/jOOQ/issues/14568, where the DEFAULT on an identity should be generated

[katzyn]

I think jOOQ shouldn't produce VALUES () in any INSERT statements, the standard DEFAULT VALUES or VALUES (DEFAULT [, ...]) should be used instead for safety. I have no idea why H2 accepts incomplete constructor with smaller count of columns, but I also don't see any requirement in the SQL Standard for its completeness. Am I missing something?

Unfortunately, I also have no idea how to evaluate default values of target table into the source table here. H2 has MySQL-style INSERT … ON DUPLICATE KEY UPDATE …, but it is accepted only in MySQL compatibility mode.

[lukaseder]

I think jOOQ shouldn't produce VALUES () in any INSERT statements

Agreed, that's a bug in jOOQ that just happens to work on H2

but I also don't see any requirement in the SQL Standard for its completeness. Am I missing something?

The standard requires at least one element, syntactically:

<merge insert value list> ::=
  <left paren>
    <merge insert value element> [ { <comma> <merge insert value element> }... ]
    <right paren>

Unfortunately, I also have no idea how to evaluate default values of target table into the source table here.

jOOQ knows the DEFAULT expressions, so jOOQ could handle this. After all, this is not a MERGE feature, but an emulation of PostgreSQL's INSERT .. ON CONFLICT, so the semantics is clear