Jooq: SQLDialectNotSupportedException: The ON DUPLICATE KEY UPDATE clause cannot be emulated for DEFAULT when using the Batch API with named parameters

[JanecekPetr]

Expected behavior and actual behavior:

When invoking an insert-on-duplicate-key-do-update statement on Postgres 9.5 in batch mode with named parameters, I expect the query to work. Unfortunately, it does not, and one needs to avoid named parameters.

Instead, I get this error (even though my SQLDialect _is_ set to Postgres 9.5!):

13:46:38.988 [main] WARN  org.jooq.exception.SQLDialectNotSupportedException - Not supported by dialect : The ON DUPLICATE KEY UPDATE clause cannot be emulated for DEFAULT
Exception in thread "main" org.jooq.exception.SQLDialectNotSupportedException: The ON DUPLICATE KEY UPDATE clause cannot be emulated for DEFAULT
    at org.jooq.impl.InsertQueryImpl.accept0(InsertQueryImpl.java:301)
    at org.jooq.impl.AbstractDMLQuery.accept(AbstractDMLQuery.java:273)
    at org.jooq.impl.AbstractBindContext.bindInternal(AbstractBindContext.java:254)
    at org.jooq.impl.ParamCollector.bindInternal(ParamCollector.java:99)
    at org.jooq.impl.AbstractBindContext.visit0(AbstractBindContext.java:88)
    at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:423)
    at org.jooq.impl.AbstractContext.visit(AbstractContext.java:184)
    at org.jooq.impl.DefaultDSLContext.extractBindValues(DefaultDSLContext.java:664)
    at org.jooq.impl.AbstractQuery.getBindValues(AbstractQuery.java:137)
    at org.jooq.impl.AbstractDelegatingQuery.getBindValues(AbstractDelegatingQuery.java:74)
    at org.jooq.impl.BatchSingle.bind(BatchSingle.java:134)
    at org.jooq.impl.BatchSingle.bind(BatchSingle.java:126)
    at org.jooq.impl.BatchSingle.bind(BatchSingle.java:69)
    at playground.Playground.main(Playground.java:38)

Steps to reproduce the problem:

Given such schema:

CREATE TABLE a (
    column_a BIGSERIAL PRIMARY KEY,
    column_b VARCHAR(100) NOT NULL UNIQUE
);

Run this code:

Configuration jooqConfig = new DefaultConfiguration()
    .set(dataSource)
    .set(SQLDialect.POSTGRES_9_5);

DSLContext context = DSL.using(jooqConfig);

InsertOnDuplicateSetMoreStep<ARecord> insert = insertInto(A)
    .columns(A.COLUMN_B)
    .values(param("columnB", A.COLUMN_B))
    .onDuplicateKeyUpdate()
        .set(A.COLUMN_B, "whatever");

BatchBindStep batch = context.batch(insert);
for (String valueToInsert : ImmutableList.of("abc", "def")) {
    batch.bind(ImmutableMap.of("columnB", valueToInsert));    // somehow this is a problem
}
batch.execute();

Boom. Did I misuse something, or does the batch somehow not see the set SQL dialect?

The same exception occurs for:

.onConflict(A.COLUMN_A)
    .doUpdate()
    .set(A.COLUMN_B, "whatever");

and even for the functionally nonequivalent:

.onConflict(A.COLUMN_A, A.COLUMN_B)
    .doUpdate()
    .set(A.COLUMN_B, "whatever");

The interesting part is that this works just fine (and it does not matter if the param is named or unnamed):

BatchBindStep batch = context.batch(insert);
for (String valueToInsert : ImmutableList.of("abc", "def")) {
    batch.bind(valueToInsert);   // Huh!
}
batch.execute();

Versions:

• jOOQ: 3.10.5
• Java: 8u161
• Database (include vendor): Postgres 9.5
• OS: both Win/Linux
• JDBC Driver (include name if inofficial driver): org.postgresql:postgresql:42.1.4

[lukaseder]

Thanks a lot for the detailed report. Will look into this soon

[lukaseder]

I cannot seem to reproduce this. Do you have a converter on COLUMN_B, perhaps? Would it be possible to share your full Playground to help reproduce this?

[JanecekPetr]

Sure. https://gitlab.com/janecekpetr/jooq-boom under the name Kaboom

Don't mind the code, it's intentionally silly. Should work just fine, it only expects a running DB, see pom.xml

<db.host>localhost</db.host>
<db.port>5432</db.port>
<db.name>jooq-boom</db.name>
<db.username>postgres</db.username>
<db.password>postgres</db.password>

I can run it in embedded, I was just lazy and I'm sure you'll manage ;)

---

__EDIT:__ Oh, and the testBindSimple() fails, too, but with a different exception that I find strange, but can live with. The testBindNamedParam() is where my curious case is.

[lukaseder]

Thank you very much for your test case. I will try to reproduce this soon

[akawalsky]

Hey, any update here?

[lukaseder]

Terribly sorry for the delay. I'm investigating this right now. I can already reproduce the issue reported by @JanecekPetr

[lukaseder]

The example project actually seems to reproduce two issues. One of them is: #7691

[lukaseder]

Also, there's a testBindSimple() test in the example project, which seems to illustrate another problem, namely when the number of indexed bind values in the query doesn't match the number of provided bind values in the batch statement. This should already emit a warning in the log files: #4071 (for backwards compatibility reasons, there's no exception thrown here)

[lukaseder]

A workaround is to call:

// Create the statement from the DSLContext, not from DSL
InsertOnDuplicateSetMoreStep<ARecord> insert = context.insertInto(A)
    .columns(A.COLUMN_B)
    .values(param("columnB", A.COLUMN_B))
    .onDuplicateKeyUpdate()
        .set(A.COLUMN_B, "whatever");

[lukaseder]

The fix is simple. When extracting the bind values from the query being batched, we must use the Configuration of the batch statement, not that of the query.

[lukaseder]

Fixed for jOOQ 3.12. Backport scheduled for 3.11.5 (#7779) and 3.10.9 (#7780)

[JanecekPetr]

seems to reproduce two issues

yes

testBindSimple() seems to illustrate another problem

yes (should have used inline("nope") of course)

A workaround is to call: context.insertInto(A)

Ohhh!

Fixed for jOOQ 3.12. Backport scheduled for 3.11.5 (#7779) and 3.10.9 (#7780)

<3

[akawalsky]

I'm still having issues doing this with onConflict / doUpdate. Can someone confirm whether or not the proposed solution should work with onConflict as well as onDuplicateKeyUpdate?

[lukaseder]

@akawalsky: Check out the version numbers in my comment: https://github.com/jOOQ/jOOQ/issues/7319#issuecomment-414626716

[akawalsky]

@JanecekPetr were you able to figure out how to get the value you specified in the binding to actually be persisted to the db instead of the hardcoded string "nope" on conflict?

https://gitlab.com/janecekpetr/jooq-boom/blob/master/src/main/java/com/gitlab/janecekpetr/jooqboom/Kaboom.java#L36

[akawalsky]

For future reference - I was able to get this working by referencing the same param in the set call as referenced in the values call. For clarity,

 InsertResultStep<UsersRecord> insert = db.insertInto(USERS)
      .columns(USERS.EMAIL, USERS.FIRST_NAME, USERS.LAST_NAME)
      .values(
          DSL.param(EMAIL_MAPPING, USERS.EMAIL),
          DSL.param(FIRST_NAME_MAPPING, USERS.FIRST_NAME),
          DSL.param(LAST_NAME_MAPPING, USERS.LAST_NAME)
      )
      .onConflict(USERS.EMAIL)
      .doUpdate()
      .set(USERS.FIRST_NAME, DSL.param(FIRST_NAME_MAPPING, USERS.FIRST_NAME))
      .set(USERS.LAST_NAME, DSL.param(LAST_NAME_MAPPING, USERS.LAST_NAME))
      .returning(USERS.ID);

BatchBindStep usersBatch = db.batch(insert);

for (UserRecord r : records) {
      usersBatch.bind(
          ImmutableMap.of(
              EMAIL_MAPPING, r.email,
              FIRST_NAME_MAPPING, r.firstName,
              LAST_NAME_MAPPING, r.lastName
          )
      );
    }

Thank you @lukaseder