Looks like there is no possibility to inline value with name in returning section in Jooq 3.10
I tried such queries
val role = name("role").fields("role_id")
.`as`(select(ROLES.ROLE_ID)
.from(ROLES)
.where(ROLES.LEGACY_ID.eq(roleId)))
return dsl.with(role)
.insertInto(USER)
.columns(
USER.EMAIL,
USER.ROLE_ID)
.select(
select(
inline(email),
role.field("role_id", Int::class.java))
.from(role))
.onConflict(USER.EMAIL)
.doNothing()
.returning(USER.EMAIL, inline(roleId).as("role))
and
val role = name("role").fields("role_id")
.`as`(select(ROLES.ROLE_ID)
.from(ROLES)
.where(ROLES.LEGACY_ID.eq(roleId)))
return dsl.with(role)
.insertInto(USER)
.columns(
USER.EMAIL,
USER.ROLE_ID)
.select(
select(
inline(email),
role.field("role_id", Int::class.java))
.from(role))
.onConflict(USER.EMAIL)
.doNothing()
.returning(USER.EMAIL, inline(roleId))
In the first case it is converted into such sql
select
'new_email@expedia.com',
role.role_id
from role
on conflict (email) do nothing
returning
auction.user.email,
role
And I receive an error that there is no role field
and in the second case it is converted into
select
'new_email@expedia.com',
role.role_id
from role
on conflict (email) do nothing
returning
auction.user.email,
2
And I can’t refer to the inlined column by name on fetching stage
Advertisement
Answer
Indeed, there was a known and very unfortunate limitation of that InsertReturningStep.returning()
method, it is only allowed to project columns from the table that you insert into, no expressions.
This problem was solved in jOOQ 3.11 via the new InsertReturningStep.returningResult()
method via issue #1234. The original method was not retrofitted due to backwards compatibility constraints. You can now do:
.returningResult(USER.EMAIL, inline(roleId))
I don’t think there’s an easy way to work around this problem in jOOQ 3.11, short of converting the entire query to a plain SQL templating query