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
On Fri, Nov 6, 2020 at 4:16 PM Lukas Eder <[email protected]> wrote: > Thanks a lot for your report. I'll investigate this early next week. > > Thanks > Lukas > > On Thu, Nov 5, 2020 at 7:03 PM Colin <[email protected]> wrote: > >> 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 >> >> >> -- >> 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 [email protected]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/jooq-user/a06a3af9-4ea7-4644-8ba6-5b5d0132671en%40googlegroups.com >> <https://groups.google.com/d/msgid/jooq-user/a06a3af9-4ea7-4644-8ba6-5b5d0132671en%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4XWTgXR7hs_QgMT%2Bd4nNU6SHnH2o23OTg66foCDOqudQ%40mail.gmail.com.
