ambiguous column name with criteria api and maxresults

damianpenney · **Joined:** Tue Mar 30, 2004 10:20 pm **Posts:** 2

The criteria api is generating ambiguous column names which prevents setMaxResults from working

this is the pertinent sql that is generated

Code:

select * from (
             this.id as id3_,
             dealcommen1_.id as id__, 
             x0_.id as id1_, 
             x1_.id as id__, 

from Deal this left outer join deal_comments dealcommen1_ on this.id=dealcommen1_.deal_id 
             inner join Company x0_ on this.company_id=x0_.id 
             inner join Address x1_ on x0_.id=x1_.company_id 
             where lower(x1_.city) like '%san francisco%' ) 
where rownum <= 40

As you can see all the joined classes have the same id alias which prevents the select * from working, not sure why it wraps it with select * when just adding and rownum <=40 does the trick too.

Here is the criteria code

Code:

if(!"".equals(dealSearch.getCity()))
{                
             c.createCriteria("company").createCriteria("addresses").add(Expression.ilike("city", dealSearch.getCity(), MatchMode.ANYWHERE));
}

c.setMaxResults(40);

Any thoughts on how to fix this ? Changing the mapping/busobjects is not the route I want to take. DB is Oracle 9i

Damian

emmanuel · **Posted:** Wed Mar 31, 2004 10:04 am

Post that to JIRA please.

gavin · **Posted:** Wed Mar 31, 2004 10:10 am

Make sure you include actual runnable code, so that we can reproduce this.

jwray · **Joined:** Fri Sep 26, 2003 4:29 pm **Posts:** 16

I just came across a similar error. When using .setMaxResults on a criteria I get a "ORA-00918 column ambigously defined" error from Oracle 9i. (v 9.2.0.1)

What suprised me is that I've been using the .setMaxResults construct fine for 3-4 months. Just adding a new query today produced the error. The only thing that is different about today's query is that it's more complex (joining more tables) than a previous one.

I did some experimentation and I've got a feeling it's a limit with Oracle. It's not a hibernate problem, the same thing happens with raw sql. The sql that is produced is along the lines of:

select * from ( .... inner query .... ) where rownum < ?

which looks to me like it should be ok. In fact, it has been working fine with other criteria generated queries.

If I run the inner query as is, all is fine. If I do

select count(*) from ( .... inner query .... ) where rownum < ?

all is fine. If I select a specific element (or multiple)

select a from (.... inner query .... ) where rownum < ?

all is fine.

This looks to me like the select * syntax with a large inner query is hitting on an oracle problem. As I said, such syntax works with smaller inner queries. I did search the oracle documentation and found nothing. But then when does searching the oracle documentation find anything of use?

To give some idea of scale, the inner query causing the problem selects about 200 values by joining about 25 tables.

I've gotten round the problem another way, but thought I would add what I'd found out to this message.

Jonny

christian · **Posted:** Thu Apr 29, 2004 6:11 pm

Enable Hibernate's SQL log output and verify the queries. Joining 25 tables is... a bad idea.

christian · **Posted:** Thu Apr 29, 2004 6:12 pm

Oh, nevermind, Oracle problem.

damianpenney · **Joined:** Tue Mar 30, 2004 10:20 pm **Posts:** 2

I did change a number of things and when I tried again it worked so yes this is probably an Oracle bug. However I don't think it's related to the number of tables, I was joining five, six tables max.

Damian