Jooq Java CTE wrong render

Tags: , , ,



I have CTE that is been creating from DSL.values:

final RowN[] rowNS = tasksId
                .stream()
                .map(x -> DSL.row(x.toString()))
                .toArray(RowN[]::new);

        CommonTableExpression<Record1<String>> cte = DSL
                .name("cte")
                .fields("id")
                .as(DSL
                        .select(
                                DSL.field("id", String.class)
                        )
                        .from(
                                DSL.values(rowNS).as("temp", "id")
                        )
                );

Then i am trying use it to get needed values from DB

                dslContext
                        .with(
                                cte
                        )
                        .select(
                                DSL.field(TaskStateTable.taskId.getColumnName(), String.class),
                                DSL.field(TaskStateTable.state.getColumnName(), String.class)
                        )
                        .from(
                                DSL.table(tableNameProperties.getFullNameTableTaskState())
                        )
                        .join(
                                cte
                        )
                        .on(
                                DSL.field(tableNameProperties.getFullNameTableTaskState() + "." + TaskStateTable.taskId.getColumnName())
                                        .eq(cte.field("id"))
                        )
                        .fetchMap(
                                DSL.field(TaskStateTable.taskId.getColumnName(), String.class),
                                DSL.field(TaskStateTable.state.getColumnName(), String.class)
                        )

But i have only the next error

jOOQ; uncategorized SQLException for SQL [with cte(id) as (select id from ((select null id where 1 = 0) union all (select * from (values(cast(? as varchar))) temp)) temp) select task_id, state from rtm.task_state join cte on rtm.task_state.task_id = cte.id]

I suppose it happens because of (select * from (values(cast(? as varchar))

How to fix it? Other methods without cte work fine.

Answer

The problem was there:

DSL.field("id", String.class)

“Id” is needed to be wraped by DSL.name()

DSL.field(DSL.name("id"), String.class)


Source: stackoverflow