org.hibernate.exception.SQLGrammarException: could not execute query

Hibernate JIRA | Petri Rautakoski | 1 decade ago
  1. 0

    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)

    Hibernate JIRA | 1 decade ago | Petri Rautakoski
    org.hibernate.exception.SQLGrammarException: could not execute query
  2. 0

    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)

    Hibernate JIRA | 1 decade ago | Petri Rautakoski
    org.hibernate.exception.SQLGrammarException: could not execute query
  3. 0

    Full text search exception with special characters in PostgreSQL

    Stack Overflow | 4 years ago | christopher wilbert
    org.hibernate.exception.SQLGrammarException: could not execute query
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    selecting list of object using HQL when the object is a property of another entity class

    Stack Overflow | 2 years ago
    org.hibernate.exception.SQLGrammarException: could not execute query
  6. 0

    Using a NamedQuery with a composite class

    Stack Overflow | 8 years ago | Ascalonian
    org.hibernate.exception.SQLGrammarException: could not execute query

    3 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. org.hibernate.exception.SQLGrammarException

      could not execute query

      at org.hibernate.exception.SQLStateConverter.convert()
    2. Hibernate
      QueryLoader.list
      1. org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
      2. org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
      3. org.hibernate.loader.Loader.doList(Loader.java:2148)
      4. org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
      5. org.hibernate.loader.Loader.list(Loader.java:2024)
      6. org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
      6 frames
    3. Hibernate HQL/JP-QL Parser
      QueryTranslatorImpl.list
      1. org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
      1 frame
    4. Hibernate
      QueryImpl.list
      1. org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
      2. org.hibernate.impl.SessionImpl.list(SessionImpl.java:1129)
      3. org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
      3 frames