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.
