Jooq: PostgreSQL UNION requires parentheses if subquery has WITH clause

[krzysztof-osiecki]

public void jooqQueryTest() {
    DefaultDSLContext dsl = new DefaultDSLContext(SQLDialect.POSTGRES);
    Table<Record> sysUserTable = DSL.table("sys_user");
    org.jooq.Field<Long> idField = sysUserTable.field("id", Long.class);
    SelectQuery query1 = dsl.select(idField).from(sysUserTable).getQuery();
    SelectQuery<Record1<Long>> query2 = dsl.select(idField).from(sysUserTable).getQuery();
    SelectJoinStep<Record1<Long>> wrapped = dsl.with("wraped").as(dsl.select(idField).from(sysUserTable))
                .select(idField).from(query2);
    Select selectQuery = query1.union(wrapped);
    System.out.println(selectQuery.getSQL(ParamType.INLINED));
}

In the sample above on jOOQ 3.13.1 and 3.13.0 printed output looks like this:
select * from sys_user union with "wraped" as (select * from sys_user) select * from (select * from sys_user) as "alias_87490841"
Running that on postgres provided that the table exists produces the following error:
SQL Error [42601]: ERROR: syntax error at or near "with"

In version 3.12.3 the same sample code produces:
(select * from sys_user) union (with "wraped" as (select * from sys_user) select * from (select * from sys_user) as "alias_87490841")
which that code works fine.

Seems like extra parentheses were removed, and it seems to work fine with regular union statements, but when the 'with clause' appears it no longer works.

Possibly related issue

[lukaseder]

Thank you very much for yuor report. This is a regression caused by https://github.com/jOOQ/jOOQ/issues/3676.

I'll fix this ASAP and backport to 3.13.2

[lukaseder]

Finally looking into this now. Indeed, this is a regression caused by https://github.com/jOOQ/jOOQ/issues/3676 and is an easy fix.

[lukaseder]

Interestingly, H2 can handle this syntax:

select 1 a
union
with t as (select 2 a)
select a from t;

I don't see why not. Anyway, for now, I'll re-add the parens regardless of the dialect.

[lukaseder]

Fixed in jOOQ 3.14.0. Backport scheduled for 3.13.6 (#10709)

[lukaseder]

3.14 fix included in another commit: https://github.com/jOOQ/jOOQ/commit/def8ba781c6cb42a95831878639f05b80878e6a6

[lukaseder]

Some dialects require additional emulations to be able to have WITH in set operation subqueries: https://github.com/jOOQ/jOOQ/issues/10711