[hibernate-issues] [Hibernate-JIRA] Closed: (HHH-1436) setMaxResults causes column ambiguously defined exception

[Steve Ebersole (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1436?page=all ]
     
Steve Ebersole closed HHH-1436:
-------------------------------

    Resolution: Duplicate

> setMaxResults causes column ambiguously defined exception
> ---------------------------------------------------------
>
>          Key: HHH-1436
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1436
>      Project: Hibernate3
>         Type: Bug

>   Components: core
>     Versions: 3.1.2
>  Environment: Oracle 10g
>     Reporter: Petri Rautakoski
>     Priority: Minor

>
>
> This is duplicate to HHH-951, but in my opinion this is a bug with Blocker status and I recreated it
> It seems that if you set any WHERE clause into HQL query it appends this WHERE clause column into SELECT part. This works fine if you just execute it, but if you for example set maximum result size for Query it will produce this HQL query as inner select and wraps it into SELECT * FROM (...) WHERE rownum <= ? query. For example:
> Mapping:
> <?xml version="1.0" encoding="UTF-8"?>
> <!DOCTYPE hibernate-mapping PUBLIC
>    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
>    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
> <hibernate-mapping>
>    <class
>            name="Foo"
>            table="foo"
>            lazy="true"
>    >
>        <id
>            name="id"
>            column="id"
>            type="integer"
>        >
>            <generator
>                class="sequence"
>            >
>                <param name="sequence">
>                    <![CDATA[S_FOO]>
>                </param>
>            </generator>
>        </id>
>    </class>
> </hibernate-mapping>
> Java code:
> Query hql = session.createQuery("FROM Foo WHERE id = 123");
> hql.setMaxResultSize(2);
> List results query.list();
> produced SQL:
> SELECT * FROM (SELECT foo0_.id as id12_, foo0_.ID as ID12_ FROM foo as foo0_ WHERE foo0_.id=123) WHERE rownum <= 2
> Thrown Exception:
> [JDBCExceptionReporter] SQL Error: 918, SQLState: 42000
> [JDBCExceptionReporter] ORA-00918: column ambiguously defined
> org.hibernate.exception.SQLGrammarException: could not execute query
>        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
>        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
>        at org.hibernate.loader.Loader.doList(Loader.java:2148)
>        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
>        at org.hibernate.loader.Loader.list(Loader.java:2024)
>        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
>        at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
>        at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
>        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1129)
>        at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira