Jooq: Cannot combine INSERT .. RETURNING with INSERT .. ON DUPLICATE KEY .. due to DSL API flaw

[lukaseder]

The INSERT DSL API does not allow to combine INSERT ... RETURNING with INSERT ... ON DUPLICATE KEY { UPDATE | IGNORE }. This is due to the declaration of InsertOnDuplicateStep:

public interface 
    InsertOnDuplicateStep<R extends Record>
extends 
    InsertFinalStep<R>, InsertReturningStep<R> {

Here, users will have to make a choice whether they go into the on duplicate syntax, or the returning syntax

This also affects the PostgreSQL ON CONFLICT syntax: https://github.com/jOOQ/jOOQ/issues/5897

[rajgopalv]

We had an urgent requirement to have this functionality in my org. I was able to get this working by making the following changes.

InsertOnDuplicateStep extends InsertReturningStep which itself is a child interface of InsertFinalStep, So I left that unchanged. but,

InsertOnDuplicateStep.onDuplicateKeyIgnore() should return InsertReturningStep<R>

also,

InsertOnDuplicateSetMoreStep must extend InsertReturningStep<R> (instead of InsertFinalStep )

Please advice if I'm missing something. I can submit a pull request.

[lukaseder]

Thank you very much for your feedback, @rajgopalv, and for your suggested PR (#5493). May I ask you to read the contributions guideline and sign the transfer of rights agreement linked in it?
https://github.com/jOOQ/jOOQ/blob/master/CONTRIBUTING.md

This agreement will allow us to dual-license jOOQ, including your contribution.

[lukaseder]

Note, the reason why this has been postponed thus far is because it's not very easy to get this right in the context of a variety of supported emulations for this clause combination...

[advoretsky]

Are there any chances to get this in 3.10? Is it a hard task for the developer outside of core team to do?

[lukaseder]

Yes, this should make it into 3.10, I hope. It won't make sense to accept a PR here, because the change itself seems trivial, but checking the implications of it in terms of backwards compatibility are much harder - which is why this task has been postponed thus far.

[lukaseder]

Happy easter everyone 🐰

[DheerajKumarReddyKotha]

I have a urgent need to use this. Any suggestions or sample example code to handle this issue.

[lukaseder]

@DheerajKumarReddyKotha This is just an API flaw. The implementation already supports this feature.

As a workaround, cast the type returned by InsertOnDuplicateStep.onDuplicateKeyIgnore() to InsertReturningStep<R> and call returning() on that. Or, alternatively, use the model API (DSLContext.insertQuery()), which supports this already.

[txsmith]

Hi Lukas,
I've tried the two things you suggested above but had no success with either because the returned result always seems to be null when attempting to insert a duplicate entry.

I've tried casting:

InsertReturningStep<R> query = 
    (InsertReturningStep<R>) db.insertInto(...).values(...).onDuplicateKeyIgnore();

R result = query.returning().fetchOne();

but result becomes null when inserting something duplicate .

With the model API I've tried this:

InsertQuery<R> query = db.insertQuery(...);
query.addValue(...);
query.onDuplicateKeyIgnore(true);
query.setReturning(...);

query.getReturnedRecord(); // null

With the same result.
I'm not sure how to go about this so any advice is much appreciated!

[lukaseder]

@txsmith, I think that's unrelated. There had been an issue which I wasn't able to reproduce: https://github.com/jOOQ/jOOQ/issues/3140. Would you mind commenting on there, in case you find a way to reproduce it in a minimal example?

[alexanderjohn]

Confirming this is still an issue with jOOQ 3.13+ and MySQL 8.0.17.

WeatherLocationRecord record = jooqTransformer.toRecord(dto);
InsertReturningStep<WeatherLocationRecord> onDuplicateKeyUpdate =
    (InsertReturningStep<WeatherLocationRecord>) writeContext.insertInto(WEATHER_LOCATION).set(record)
        .onDuplicateKeyUpdate().set(record);
return onDuplicateKeyUpdate.returning().fetchOne().into(WeatherLocationDTO.class);

This return NPE the second time I call this method. the .returning(). does not return any value. Do I open this up as a fresh ticket? Do i report this again here?:

https://github.com/jOOQ/jOOQ/issues/6764

My test code:
```@Test
public void test() {
WeatherLocationDTO weatherLocation = WeatherLocationDTO.builder().latitude(LATITUDE).longitude(LONGITUDE)
.createdTimestampSec(Instant.now().getEpochSecond()).build();

    WeatherLocationDTO save = weatherLocationRepository.save(weatherLocation);
    assertThat(save).isNotNull();

    WeatherLocationDTO save2 = weatherLocationRepository.save(weatherLocation);
    assertThat(save2.getId()).isEqualTo(save.getId());
}```

[lukaseder]

Thanks, @alexanderjohn.

This issue here is about API design, not some NPEs that may arise from calling things in one way or another, so definitely a different issue from this one here, perhaps the same as #6764.

If you think there's anything we've overlooked in #6764, feel free to add it there.