insert into select... with h2

[Colin]

I suspect there might be a jOOq bug.

I am using:

open jdk v11.0.9

h2 1.4.200

jooq 3.13.4

I have the following code:

val t1 = JTable1.TABLE1

val t2 = JTable2.TABLE2

val t3 = JTable2.TABLE3

val t4 = JTable2.TABLE4

val t5 = JTable2.TABLE5

val dateHourField = trunc(t2.RECORD_TIMESTAMP, DatePart.HOUR).`as`("date_hour")

val insert = db.insertInto(t1)

  .columns(t1.COL1_ID, t1.COL2_ID, t1.COL3, t1.COL4, t1.COL5, t1.COL6_ID, t1.COL7)

  .select(

    select(t2.COL1_ID, t2.COL2_ID, sum(t2.COL3).cast(Long::class.java), sum(t2.COL4).cast(Long::class.java), dateHourField, t5.ID, COL7)

    .from(t2)

    .leftJoin(t3).on(t3.COL8.eq(t2.COL8).and(t3.COL9.eq(t2.COL9)))

    .leftJoin(t4).on(t4.COL10_ID.eq(t3.ID))

    .leftJoin(t5).on(t5.ID.eq(t4.COL6_ID))

    .where(t2.COL4.gt(0).or(t2.COL3.gt(0)))

    .and(t2.RECORD_TIMESTAMP.gt(start.atOffset(ZoneOffset.UTC)))

    .and(t2.RECORD_TIMESTAMP.lt(end.atOffset(ZoneOffset.UTC)))

    .groupBy(dateHourField, t2.COL1_ID, t2.COL2_ID, t5.ID))

insert.onDuplicateKeyIgnore().execute()

When using h2, it produces the following erroneous SQL:

insert into PUBLIC.TABLE1 (

  COL1_ID, 

  COL2_ID, 

  COL3, 

  COL4, 

  COL5, 

  COL6_ID, 

  COL7

)

select 

  T.COL1_ID, 

  T.COL2_ID, 

  T.COL3, 

  T.COL4, 

  T.COL5, 

  T.COL6_ID, 

  T.COL7

from (

  select 

    null COL1_ID, 

    null COL2_ID, 

    null COL3, 

    null COL4, 

    null COL5, 

    null COL6_ID, 

    null COL7

  where not exists (

    select 1 ONE

    from PUBLIC.TABLE1

    where (

      PUBLIC.TABLE1.COL5 = null

      and PUBLIC.TABLE1.COL3 = null

      and PUBLIC.TABLE1.COL6_ID = null

      and PUBLIC.TABLE1.COL7 = null

      and PUBLIC.TABLE1.COL1_ID = null

      and PUBLIC.TABLE1.COL2_ID = null

      and PUBLIC.TABLE1.COL4 = null

    )

  )

) T

But it works with postgres as expected:

insert into PUBLIC.TABLE1 (

  COL1_ID, 

  COL2_ID, 

  COL3, 

  COL4, 

  COL5, 

  COL6_ID, 

  COL7

)

select 

  PUBLIC.TABLE2.COL1_ID, 

  PUBLIC.TABLE2.COL2_ID, 

  cast(sum(PUBLIC.TABLE2.COL3) as bigint), 

  cast(sum(PUBLIC.TABLE2.COL4) as bigint), 

  date_trunc('hour', PUBLIC.TABLE2.RECORD_TIMESTAMP) as DATE_HOUR, 

  PUBLIC.TABLE5.ID, 

  'SomeRandomConstant'

from PUBLIC.TABLE2

  left outer join PUBLIC.TABLE3

    on (

      PUBLIC.TABLE3.COL8 = PUBLIC.TABLE2.COL8

      and PUBLIC.TABLE3.COL9 = PUBLIC.TABLE2.COL9

    )

  left outer join PUBLIC.TABLE4

    on PUBLIC.TABLE4.COL10_ID = PUBLIC.TABLE3.ID

  left outer join PUBLIC.TABLE5

    on PUBLIC.TABLE5.ID = PUBLIC.TABLE4.COL6_ID

where (

  (

    PUBLIC.TABLE2.COL4 > 0

    or PUBLIC.TABLE2.COL3 > 0

  )

  and PUBLIC.TABLE2.RECORD_TIMESTAMP > timestamp with time zone '2019-05-02 00:00:00+00:00'

  and PUBLIC.TABLE2.RECORD_TIMESTAMP < timestamp with time zone '2019-05-05 00:00:00+00:00'

)

group by 

  COL5, 

  PUBLIC.TABLE2.COL1_ID, 

  PUBLIC.TABLE2.COL2_ID, 

  PUBLIC.TABLE5.ID

on conflict  do nothing

Thanks!

Colin

[Lukas Eder]

Thanks a lot for your report. I'll investigate this early next week.

Thanks

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/a06a3af9-4ea7-4644-8ba6-5b5d0132671en%40googlegroups.com.

[Lukas Eder]

I'm looking into this now. I can't reproduce this anymore with jOOQ 3.14, which now generates a MERGE statement for H2. Nevertheless, the issue may still be present in other dialects. Will further track this as: https://github.com/jOOQ/jOOQ/issues/10989