inline with name in returning section ignores value in Jooq

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 
  '[e-mail]', 
   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 
  '[e-mail]', 
   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

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