Oracle + addLimit = ORA-00918: column ambiguously defined

[Witold Szczerba]

Hi,

my colleague has found a problem today, when he was applying #addLimit to queries in our system.

We are working with Oracle 11g using JOOQ 2.6.3.

As far as I know, there is no direct support for limit in this database. JOOQ is wrapping original query within another query with extra "rownum" column to fulfil the task.

The problem is the "trick" does not work when the original query has at least two columns with same name and this is very common case when joining. The result is ORA-00918 error (column ambiguously defined).

I have prepared a simplified queries to show the problem:

----------------------------------------------------

--before applying #addLimit(int)

select "FOLDER"."ID", "SUBJECT"."ID"
from "FOLDER"
left outer join "SUBJECT" on "FOLDER"."LANDLORD" = "SUBJECT"."ID"

--after applying #addLimit(int)
select * from (
 select "limit_9992791".*, rownum as "rownum_9992791"
 from (
  select "FOLDER"."ID", "SUBJECT"."ID"
  from "FOLDER"
  left outer join "SUBJECT" on "FOLDER"."LANDLORD" = "SUBJECT"."ID"
 ) "limit_9992791"
 where rownum <= (? + ?)
) 
where "rownum_9992791" > ?

Result: ORA-00918: column ambiguously defined
----------------------------------------------------

The only workaround (temporary I hope) we were able to apply was to #fetchLazy into Cursor and skip+break the loop and manually apply the mappers within.

I have a hypothesis: the problem could be easily solved by providing unique aliases to every TableField used by the SelectQuery used to produce the query.

What do you think? Is it good idea? Is it easy to fix the library that way? Help!

Thanks,
Witold Szczerba

[Lukas Eder]

Hi,

Thanks for reporting this. I have registered #2335 for this problem:

https://github.com/jOOQ/jOOQ/issues/2335

I'll have to think about a solution within jOOQ. I'm not sure if there is one for Oracle, SQL Server, DB2 and Sybase SQL Anywhere, where these kinds of subqueries are generated to simulate LIMIT ... OFFSET.

Your hypothesis would work at the client site. You should explicitly rename FOLDER.ID and SUBJECT.ID to something like f_id and s_id, as a workaround to avoid this ambiguity

Cheers

Lukas

[Witold Szczerba]

Hi,

my test case with aliasing FOLDER.ID.as(f_id) and SUBJECT.ID.as(s_id) works fine, but it is impossible to apply that workaround to queries across the system, as we have rather complex mappers, most of the time specified elsewhere (some are reusable) and those mappers does not know about the aliases used to create the query. Also, queries produce like 30-40 columns, so we would have to enumerate (and alias) each and every column in every query (most of the times we use implicit columns by just not adding any to the queries). All that would turn simple and concise queries into huge, hard to maintain monsters.

I was thinking, if JOOQ could automatically alias the TableFields when rendering the inner query (or any query). Alias could be a concatenation of a table and column, with dot replaced by underscore, example:

  select "FOLDER"."ID" FOLDER_ID, "SUBJECT"."ID" SUBJECT_ID
  from "FOLDER"

or even better, the alias could be more generic, not related to columns; I think it would be safer, simpler and less verbose:
  select "FOLDER"."ID" c1, "SUBJECT"."ID" c2, ... c3, ...cN, ... cN+1
  from "FOLDER" join ...

The only backward incompatibility I can think of, is when user already specified an explicit alias with identical name, but it seems very unlikely.

Regards,

Witold Szczerba

--
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/groups/opt_out.
 
 

[Lukas Eder]

2013/3/14 Witold Szczerba <pljos...@gmail.com>

Hi,

my test case with aliasing FOLDER.ID.as(f_id) and SUBJECT.ID.as(s_id) works fine, but it is impossible to apply that workaround to queries across the system, as we have rather complex mappers, most of the time specified elsewhere (some are reusable) and those mappers does not know about the aliases used to create the query. Also, queries produce like 30-40 columns, so we would have to enumerate (and alias) each and every column in every query (most of the times we use implicit columns by just not adding any to the queries). All that would turn simple and concise queries into huge, hard to maintain monsters.

I understand

 

I was thinking, if JOOQ could automatically alias the TableFields when rendering the inner query (or any query). Alias could be a concatenation of a table and column, with dot replaced by underscore, example:

  select "FOLDER"."ID" FOLDER_ID, "SUBJECT"."ID" SUBJECT_ID
  from "FOLDER" 

The problem with that is: How would you recognise those system-generated aliases? You wouldn't be able to access any data from the result anymore, except if you access values by index... This gets worse if you start nesting SELECT statements. Then, jOOQ would "system-rename" the columns twice.

The only backward incompatibility I can think of, is when user already specified an explicit alias with identical name, but it seems very unlikely.

There is no such thing as "unlikeliness". Everything that can happen, will happen. A solution to this problem has to be sound, predictable and it has to work the same way for all databases - including the ones with native LIMIT .. OFFSET support.

This needs some more thinking...

Cheers

Lukas

[Lukas Eder]

Hi Witold,

After all this time, we have found a fix for #2335:

https://github.com/jOOQ/jOOQ/issues/2335

This issue is now fixed for jOOQ 3.4.0, and might be merged to 3.3.3 and 3.2.6 although I cannot promise the merge, as the change was quite complex and it currently depends on changes to jOOQ's internals for jOOQ 3.4.0

Essentially, instead of just blindly selecting *:

select * from (
 select "limit_9992791".*, rownum as "rownum_9992791" 
 from (
  select "FOLDER"."ID", "SUBJECT"."ID" 
  from "FOLDER" 
  left outer join "SUBJECT" on "FOLDER"."LANDLORD" = "SUBJECT"."ID"
 ) "limit_9992791" 
 where rownum <= (? + ?)
)  
where "rownum_9992791" > ?

... we now rename columns in each subselect:

select col_1 "ID", col_2 "ID"                         -- (ID, ID)
from (
 select "limit_9992791".*, rownum as "rownum_9992791" -- (col_1, col_2, rownum_9992791)
 from (
  select "FOLDER"."ID" col_1, "SUBJECT"."ID" col_2    -- (col_1, col_2)

  from "FOLDER" 
  left outer join "SUBJECT" on "FOLDER"."LANDLORD" = "SUBJECT"."ID"
 ) "limit_9992791" 
 where rownum <= (? + ?)
)  
where "rownum_9992791" > ?

The actual solution doesn't look like this, but you get the idea. The point here is that columns need to be renamed in the inner-most subselect, in order to avoid ambiguous columns in a subselect which is prohibited in all SQL dialects. This was issue #2335. Then, in the outer-most select, the columns are renamed back again to their original names.

Cheers,

Lukas