jOOQ - group by uses alias instead of columns

user_17505774 0
Viewed 221

I'm running jOOQ 3.13.6, on Oracle 11g springboot environment.

To use listagg function, I'm trying the solution provided here: https://stackoverflow.com/a/69482329/17505774

Example code:

    @Autowired
    private DSLContext dsl;

    // setting tables
    Table table1 = DSL.table("table_1").as("t1");
    Table table2 = DSL.table("table_2").as("t2");

    // creating fields
    final List<Field<?>> fields = new ArrayList<Field<?>>();

    fields.add(DSL.field(DSL.name("t1", "t1_id")).as("id"));
    Field cf = DSL.field(DSL.name("t2", "t2_code")).as("code");
    fields.add(listAgg(cf, ";", null)); // no order

    // running query
    dsl.settings().withRenderQuotedNames(RenderQuotedNames.EXPLICIT_DEFAULT_UNQUOTED);

    dsl.select(fields)
            .from(table1)
            .join(table2).on("t1.t1_id = t2.t1_id")
            .where("t1.t1_id = ?", id)
            .groupBy(fields)

However, it executes the following query:

select t1.t1_id      id,
       listagg(t2.t2_code) within group (null) as code
  from table_1 t1
  join table_2 t2 on ( t1.t1_id = t2.t2_id )
 where t1.t1_id = 1
 group by id

The group by should use the original column names (t1.t1_id), not the alias.

listagg jooq spring-boot oracle sql
1 Answers

Note, for this answer, and for brevity reasons, I'm assuming you had been using the code generator. The answer is the same without code generation usage.

Why the current behaviour?

Note, this behaviour is also documented here in the manual.

In jOOQ, an aliased column expression T1.T1_ID.as("id") can only generate 2 different versions of itself:

  • T1.T1_ID as ID, i.e. the alias declaration (when inside of SELECT, at the top level)
  • ID, i.e. the alias reference (when inside of any other clause / expression than the SELECT clause)

There isn't a third type of generated SQL that depends on the location of where you embed the alias expression, e.g. the unaliased column expression T1.T1_ID when you put the expression in WHERE or GROUP BY, etc. The rationale is simple. What would a user expect when they write:

groupBy(T1.T1_ID.as("id"))

Why would they expect the as() call to be a no-op? That would be more surprising than the status quo.

Consistency with other rendering modes

There are other types of QueryPart in jOOQ, which have similar aliasing capabilities:

  • Field
  • Table
  • WindowSpecification
  • Parameter
  • CTE

Let's look at the CTE example:

Table<?> cte = name("cte").as(select(...))

That cte reference has 2 modes of rendering itself to SQL:

  • The CTE declaration (if placed in the WITH clause)
  • The CTE reference (if placed in FROM, etc.)

I don't think you'd expect that cte reference to ever ignore the aliasing, and just render the SELECT itself?

Likewise with table aliasing:

T1 x = T1.as("x");

This can render itself as:

  • The alias declaration (if placed in the FROM clause)
  • The alias reference

Because the FROM clause is logically before any other clauses, you'd never expect your x reference to render only T1, instead of x or T1 as x, right?

So, for consistency reasons across the jOOQ API, the Field aliases must also behave like all the others.

What to do instead?

Don't re-use the alias expression outside of your SELECT clause. Write the jOOQ SQL exactly as you'd write the actual SQL:

ctx.select(T1.T1_ID.as("id"), ...)
   .from(T1)
   .groupBy(T1.T1_ID)
   ...
user_5217990
Related