Jooq: Oracle 12c support for OFFSET .. FETCH causes ORA-00918 regression on queries with ambiguous column names

[lukaseder]

jOOQ 3.4 introduced a fix (https://github.com/jOOQ/jOOQ/issues/2335) that works around a limitation in SQL where subqueries are not allowed to produce "ambiguous" column names.

jOOQ users may write top-level queries as such:

DSL.using(configuration)
   .select()
   .from(BOOK)
   .join(AUTHOR).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
   .limit(1)
   .fetch();

There are ambiguous column names BOOK.ID and AUTHOR.ID, although from a user perspective, this doesn't matter because the two columns are referenced from a top level select. jOOQ, however, emulates LIMIT for Oracle using derived tables and ROWNUM filtering. #2335 Renames all columns in the nested select, then applies ROWNUM filtering, then renames columns back again to their original names.

With support for Oracle 12c OFFSET .. FETCH (#2607), there is now a regression as Oracle 12c doesn't support "ambiguous" column names in top level selects that contain this clause:

We'll have to re-implement the same workaround again

[michael-simons]

Me seeing real limit clause with jOOQ 3.9 and 12c:

and then running into that bug

[lukaseder]

@michael-simons : Hah, yes indeed. This was my reaction as well... I don't see a reasonable workaround yet, except for going back to nesting the query (with all the restriction this imposes...)

[michael-simons]

I think it should be in the release notes that one can run into a breaking change with that upgrade. We have rolled back to 3.8.x for the time being, because we have more than one of those.

[lukaseder]

You're right, this might have deserved mentioning - although where? A section with known issues, perhaps?

Note, you can use SQLDialect.ORACLE11G with jOOQ 3.9 to keep generating ROWNUM filtering.

[McNetic]

You already have a section "Breaking changes" in http://www.jooq.org/notes.

[lukaseder]

I think it would be confusing to list "support for Oracle's OFFSET .. FETCH" as a breaking change. I mean, yes, it's a regression, but it'll (hopefully) be fixed in 3.9.x, soon.

So, a known issue is a bit less strong than a breaking change, in my opinion. For instance, there are also some known issues with java.time support...

[McNetic]

Well, as Michael mentioned, it certainly broke our project (at runtime). So if someone uses this in obscure places, it might break without first noticing.

[michael-simons]

Thanks @lukaseder for the hint regarding the dialect.

Regarding breaking: that's the issue we talked about when discussing testing: when assuming that generated queries are correct, those "wrong" queries only pop up during integration or worse in our case first time in click test (yeah I know we are missing a dedicated Integration test)

[lukaseder]

@McNetic: Thanks for clarifying.

Anyway, let's focus on the fix. I'll implement some logic that checks:

• Are we using SQLDialect.ORACLE or SQLDialect.ORACLE12C
• Are we in a top-level SELECT (unnecessary check, because ambiguous column names fail in non-top level selects regardless of dialect version)
• Are there two fields in the SELECT list by the same name

If the above is true, we'll revert the behaviour to SQLDialect.ORACLE11G for now

[lukaseder]

As a side-note, we'll create new categories in the release notes:

• "breaking changes (known issues)" these will be fixed in a subsequent patch release
• "breaking changes (new features)" these will not be fixed, because they're intentionally breaking

Possibly, the term "breaking change" will be replaced with something less ambiguous.

I've created a separate issue for this: https://github.com/jOOQ/jOOQ/issues/5784. If you think there's still something missing, please, feel free to comment directly on that issue.

[michael-simons]

The fix seems to be the best option as you cannot use generated field aliases… JPA has the easier job here, as the column aliases / field names doesn't really matter as long as one doesn't write a custom query, hence it can use generated field names.

Thanks for taking care!

[lukaseder]

Well, the ORACLE11G implementation uses a set of generated field aliases in the "middle" derived table that is used for ROWNUM filtering:
https://github.com/jOOQ/jOOQ/issues/2335

The generated aliases are then again renamed to their original names in the top-level query. This is the implementation that the Oracle database probably should use internally as well, because I suspect they're translating OFFSET .. FETCH to ROWNUM filtering, internally.

JPA has an easier job in many ways, because even if you did write a custom query, there are hardly any query features available...

[lukaseder]

Fixed for 3.10. Will be merged to 3.9.1 (https://github.com/jOOQ/jOOQ/issues/5801). Release hopefully today.

[michael-simons]

Thanks.

And sorry, I have to:

[lukaseder]