ORA-00918: column ambiguously defined

Description

Hi,

in my project I use hibernate 4.2.7, but I also tried another versions.
I saw similar questions/problems but without any solution for me.

I have two entities

Entity Link and Behaviour
entity Link has property
@ManyToOne(fetch = FetchType.LAZY)
private Behaviour behaviour;
and
entity Behaviour has property
@OneToMany(cascade={CascadeType.ALL}, fetch = FetchType.LAZY, mappedBy="behaviour_id")
@OrderBy("name ASC")
private List<Link> links;

In Entity Link I have a namedQuery
@NamedQuery(name = "Link.myQuery", query = "SELECT (SELECT p.id from PayItem p where p.las_id=l.id and ROWNUM <=1) as hasPayitems, l FROM Link l LEFT OUTER JOIN FETCH l.behaviour WHERE l.name LIKE :name and l.oldpk LIKE :oldpk and l.description LIKE :description ORDER BY l.name")
where I use LEFT OUTER JOIN FETCH because I need hibernate to generate query with left join.

In my DAO
Query query = em.createNamedQuery("Link.myQuery");
query.setFirstResult(position);
query.setMaxResults(recordsCount);

query.setParameter( "name", "%something%" );
query.setParameter( "description", "%something%" );
query.setParameter( "oldpk", "%something%" );

return query.getResultList();

Hibernate generates this query
select * from ( select (select payitem2_.id from PAYITEM payitem2_ where payitem2_.las_id=link0_.id and ROWNUM<=1) as col_0_0_, link0_.id as col_1_0_, behaviour1_.id as id2_1_, link0_.id as id1_0_, behaviour1_.id as id2_1_, link0_.accountClass as accountC2_1_0_, link0_.behaviour_id as behaviour3_1_0_, link0_.company_id as company4_1_0_, link0_.countryCode as countryC5_1_0_, link0_.description as descript6_1_0_, link0_.historicId as historicId1_0_, link0_.kind as kind1_0_, link0_.name as name1_0_, link0_.oldpk as oldpk1_0_, link0_.oldtable as oldtable1_0_, link0_.validFrom as validFrom1_0_, link0_.validTo as validTo1_0_, behaviour1_.classification as classifi2_2_1_, behaviour1_.countryCode as countryC3_2_1_, behaviour1_.description as descript4_2_1_, behaviour1_.entity as entity2_1_, behaviour1_.name as name2_1_, behaviour1_.oldpk as oldpk2_1_, behaviour1_.oldtable as oldtable2_1_ from LINK link0_ left outer join BEHAVIOUR behaviour1_ on link0_.behaviour_id=behaviour1_.id where (link0_.name like ?) and (link0_.oldpk like ?) and (link0_.description like ?) order by link0_.name ) where rownum <= ?

which is almost correct, except the duplicate behaviour1_.id as id2_1_

Is there any way to avoid this ?
I know when I didnt have there the inner select, or I wouldnt use query.setMaxResults(recordsCount); or I wouldnt use LEFT OUTER JOIN FETCH the query would be all right, but I need everything what is in the query.

Is there any solution ? Or any other suggestions how could I get all the data I need ?

thx
Matej

Environment

oracle 11g
hibernate 4.2.7

Activity

Show:
matej mayer
October 31, 2013, 1:48 PM

here they are

Brett Meyer
November 21, 2013, 7:43 PM

Looks like I'll need PayItem (and potentially others) for the named query. Attach them?

matej mayer
November 22, 2013, 8:34 AM

I attached all entities

matej mayer
January 8, 2014, 12:17 PM

Hi there,

any progress ?

regards
Matej

Brett Meyer
January 9, 2014, 8:31 PM

Thanks much for the test case, although I just realized this is already documented at HHH-951. Keep an eye on it!

Assignee

Brett Meyer

Reporter

matej mayer

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure