Possible Bug when rendering an aliased field with group by in the Oracle Dialect.

[Stanislas Nanchen]

Hi everyone,

We have the following problem when rendering queries in the Oracle Dialect (we are using Jooq 3.2.5)

Assume we have the following table

| TABLE_X              |

|----|-----------------|

| ID | NUMBER NOT NULL |

| A  | NUMBER NOT NULL |

| B  | NUMBER          |

|----|-----------------|

We would like to build the following Query

Field<Long> field = DSL.coalesce(B, A).as("BA");

dsl.select(field)

   .from(Tables.TABLE_X)

   .groupBy(field)

In the Oracle dialect, this is rendered as follows.

select coalesce(B, A) BA

from TABLE_X

group by BA

But aliases are not available in group by expressions and therefore, we would like to have

the following rendering:

select coalesce(B, A) BA

from TABLE_X

group by coalesce(B, A)

Have you had the same problem? and if yes how did you solve it?

Thanks!

Cheers. stan.

[Lukas Eder]

Hi Stan,

I believe we've had similar feedback on the user group before but I cannot seem to find it. In essence, these two expressions are quite different:

DSL.coalesce(B, A)

DSL.coalesce(B, A).as("BA")

The first is the actual expression whereas the second is a named reference (alias) to that function. In jOOQ, such named references have two ways of being rendered:

- as a declaration (only in the SELECT clause)

- as a reference (in all other clauses)

Imagine that field is a column from a derived table, then the GROUP BY clause would behave correctly as it is today. In fact, there are even databases that allow for referencing column declarations from the SELECT clause in GROUP BY, e.g. MySQL, SQLite, PostgreSQL:

SELECT COALESCE(a, b) AS c, count(*)

FROM (

  VALUES(null, 1),

        (null, 2),

        (1   , 2)

) AS t  (a   , b)

GROUP BY c -- Non-standard SQL reference to column alias

ORDER BY c -- Standard SQL reference to column alias

Result:

| C | COUNT |
|---|-------|
| 1 |     2 |
| 2 |     1 |

See it in action here:

http://sqlfiddle.com/#!15/d41d8/2513

I guess, the workaround is this:

Field<Long> field = DSL.coalesce(B, A);

dsl.select(field.as("BA"))

   .from(Tables.TABLE_X)

   .groupBy(field)

Hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Stanislas Nanchen]

Hi Lukas,

Thanks for the answer :). Yeah, that's what we do. But conceptually, I have put together an expression that has corresponds to an "abstract" field and it would be nice to be able to manipulating it without having to think in which context it is used. IMO, it is a design error in the SQL standard :)

Cheers, stan.

[Lukas Eder]

IMO, it is a design error in the SQL standard :)

That's probably true. The ideal way to rename columns from a syntax perspective is through derived column lists, e.g.

(SELECT a, b FROM x) AS table(column1, column2)

jOOQ supports this syntax, but few people know it. Besides, you will lose some of the typesafety.

But simple column aliasing is useful for the quick-and-dirty renaming job, so I guess we cannot do without it. Maybe, we can factor out a more general use-case for your issue? For that, I'd need to know a bit more about that "abstract" field, though.

Best,

Lukas

[Stanislas Nanchen]

i did not know that syntax. will dig into it. thanks a lot! :)

cheers stan.

[Lukas Eder]

Oracle unfortunately doesn't support it natively...

Some more details here:

- http://blog.jooq.org/2013/01/07/simulating-the-sql-standard/

- http://blog.jooq.org/2013/05/03/sql-query-transformation-fun-predicates-with-row-value-expressions/

The closest what Oracle can offer to derived column lists are common table expressions (now supported with jOOQ 3.4), but unlike derived column lists, CTE influence execution plans, so they may not be a good choice in some cases...