Add support for MySQL 8.0.19 INSERT .. VALUES .. AS .. ON DUPLICATE KEY UPDATE

[devslash-paul]

Your question:

Does Jooq currently have a way to support MySQL 8.0.19's as syntax for values, for duplicate key update.

An example of valid SQL can be seen in this link

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;

The prior way using VALUES as mentioned in #11882 | #2134 is deprecated in the latest MySQL release.

Versions:

• jOOQ: 3.14.11
• Java: 1.11
• Database (include vendor): MySQL 8.0.19
• OS: Mac OS Big Sur

[lukaseder]

Thanks a lot for your message and for pointing out the new feature and the deprecation of the old one. I've closed #11882 as "won't fix", then.

We currently don't support this syntax. You'll have to use an ExecuteListener to patch your generated SQL for now. From what I understand, you can always do that, irrespective of the presence of an ON DUPLICATE KEY UPDATE clause, so this seems to work:

create table t (i int);
insert into t values (1) as new;
insert into t values (2),(3) as new;

[lukaseder]

I guess the syntax only works for INSERT .. VALUES, not for INSERT .. SELECT. It's not strictly needed in the latter case, as aliasing columns is possible with INSERT .. SELECT (though aliasing tables isn't)

[devslash-paul]

👍 makes sense. For now the deprecation has been noted but there's no timeline on removal of VALUES. See you in 5 years when it turns out they do remove it 🚀

[lukaseder]

We won't support this syntax in the jOOQ API, but instead, support the PostgreSQL syntax via EXCLUDED: #5214

As described, we'll emulate it using the AS syntax. I guess we could simply name things AS excluded to have better PostgreSQL interop...