org.hibernate.exception.SQLGrammarException: could n ot execute query

Hibernate JIRA | Sverker Abrahamsson | 1 decade ago
  1. 0

    Hi, with the new query parser I get an exception when doing a query which contain a join between two entities with a many-to-many relation inbetween. This is the query: session.createQuery("select distinct category from Category category " + "left join fetch category.content as content " + "left join fetch content.price " + "where category.id = :categoryId") .setLong("categoryId", id.longValue() ).list(); And this is the result: 02:50:08,140 INFO [STDOUT] Hibernate: select distinct category0_.ID as ID0_, content2_.ID as ID1_, price3_.ID as ID2_, category0_.NAME as NAME3_0_, category0_.DESCRIPTION as DESCRIPT3_3_0_, category0_.HEADER_IMAGE as HEADER4_3_0_, category0_.MAIN_IMAGE as MAIN5_3_0_, category0_.TIME as TIME3_0_, category0_.PARENT_FK a s PARENT7_3_0_, content2_.DESCRIPTION as DESCRIPT2_7_1_, content2_.NAME as NAME7_1_, content2_.TIME as TIME7_1_, content2_.PRIORITY as PRIORITY7_1_, content2_.BRAND as BRAND7_1_, content2_.PRICE_FK as PRICE7_7_1_, content1_.CATEGORIES_FK as CATEGORIES1___, content1_.CONTENT_FK as CONTENT2___, price3_.CURRENCY as CURRENCY2_2_, price3_.AMOUNT as AMOUNT2_2_ from CATEGORY category0_ left outer join CATEGORIES2CONTENT content1_ on category0_.ID=content1_.CATEGORIES_FK left outer j oin CONTENT content2_ on content1_.CONTENT_FK=content2_.ID left outer join PRICE price3_ on content2_.PRICE_FK=price3_.ID where (category0_.ID=?) order by content2_.CONTENT_FK 02:50:08,296 WARN [JDBCExceptionReporter] SQL Error: 1054, SQLState: 42S22 02:50:08,296 ERROR [JDBCExceptionReporter] Unknown column 'content2_.CONTENT_FK' in 'order clause' 02:50:08,312 INFO [STDOUT] org.hibernate.exception.SQLGrammarException: could n ot execute query 02:57:47,984 INFO [STDOUT] org.hibernate.exception.SQLGrammarException: could n ot execute query at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:1502) at org.hibernate.loader.Loader.list(Loader.java:1482) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:365) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:268) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:782) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74) In the SQL query an alias content1_ is created for the relation table and content2_ for the Content entity table. The error is that in the order by clause it try to order on content2_.CONTENT_FK and there is no such column. The order by clause should be either on content1_.CONTENT_FK or content2_.ID. Here is another query that shows the same behaviour: 03:20:47,171 INFO [STDOUT] Hibernate: select content0_.ID as ID0_, category2_.ID as ID1_, filesets3_.ID as ID2_, price4_.ID as ID3_, content0_.DESCRIPTION as DESCRIPT2_7_0_, content0_.NAME as NAME7_0_, content0_.TIME as TIME7_0_, content0_.PRIORITY as PRIORITY7_0_, content0_.BRAND as BRAND7_0_, content0_.PRICE_FK as PRICE7_7_0_, category2_.NAME as NAME3_1_, category2_.DESCRIPTION as DESCRIPT3_3_1_, category2_.HEADER_IMAGE as HEADER4_3_1_, category2_.MAIN_IMAGE as MAIN5_3_1_, category2_.TIME as TIME3_1_, category2_.PARENT_FK as PARENT7_3_1_, categories1_.CONTENT_FK as CONTENT2___, categories1_.CATEGORIES_FK as CATEGORIES1___, filesets3_.ORDER_ID as ORDER2_9_2_, filesets3_.HANDLER_CLASS as HANDLER3_9_2_, filesets3_.CONTENT_FK as CONTENT4_9_2_, filesets3_.CONTENT_FK as CONTENT4___, filesets3_.ID as ID__, price4_.CURRENCY as CURRENCY2_3_, price4_.AMOUNT as AMOUNT2_3_ from CONTENT content0_ left outer join CATEGORIES2CONTENT categories1_ on content0_.ID=categories1_.CONTENT_FK left outer join CATEGORY category2_ on categories1_.CATEGORIES_FK=category2_.ID inner join FILESET filesets3_ on content0_.ID=filesets3_.CONTENT_FK left outer join PRICE price4_ on content0_.PRICE_FK=price4_.ID where (category2_.ID=?) order by category2_.CATEGORIES_FK , filesets3_.CONTENT_FK 03:20:47,218 WARN [JDBCExceptionReporter] SQL Error: 1054, SQLState: 42S22 03:20:47,218 ERROR [JDBCExceptionReporter] Unknown column 'category2_.CATEGORIES_FK' in 'order clause' 03:20:47,218 INFO [STDOUT] org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:1502) at org.hibernate.loader.Loader.list(Loader.java:1482) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:365) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:268) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:782) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)

    Hibernate JIRA | 1 decade ago | Sverker Abrahamsson
    org.hibernate.exception.SQLGrammarException: could n ot execute query
  2. 0

    Hi, with the new query parser I get an exception when doing a query which contain a join between two entities with a many-to-many relation inbetween. This is the query: session.createQuery("select distinct category from Category category " + "left join fetch category.content as content " + "left join fetch content.price " + "where category.id = :categoryId") .setLong("categoryId", id.longValue() ).list(); And this is the result: 02:50:08,140 INFO [STDOUT] Hibernate: select distinct category0_.ID as ID0_, content2_.ID as ID1_, price3_.ID as ID2_, category0_.NAME as NAME3_0_, category0_.DESCRIPTION as DESCRIPT3_3_0_, category0_.HEADER_IMAGE as HEADER4_3_0_, category0_.MAIN_IMAGE as MAIN5_3_0_, category0_.TIME as TIME3_0_, category0_.PARENT_FK a s PARENT7_3_0_, content2_.DESCRIPTION as DESCRIPT2_7_1_, content2_.NAME as NAME7_1_, content2_.TIME as TIME7_1_, content2_.PRIORITY as PRIORITY7_1_, content2_.BRAND as BRAND7_1_, content2_.PRICE_FK as PRICE7_7_1_, content1_.CATEGORIES_FK as CATEGORIES1___, content1_.CONTENT_FK as CONTENT2___, price3_.CURRENCY as CURRENCY2_2_, price3_.AMOUNT as AMOUNT2_2_ from CATEGORY category0_ left outer join CATEGORIES2CONTENT content1_ on category0_.ID=content1_.CATEGORIES_FK left outer j oin CONTENT content2_ on content1_.CONTENT_FK=content2_.ID left outer join PRICE price3_ on content2_.PRICE_FK=price3_.ID where (category0_.ID=?) order by content2_.CONTENT_FK 02:50:08,296 WARN [JDBCExceptionReporter] SQL Error: 1054, SQLState: 42S22 02:50:08,296 ERROR [JDBCExceptionReporter] Unknown column 'content2_.CONTENT_FK' in 'order clause' 02:50:08,312 INFO [STDOUT] org.hibernate.exception.SQLGrammarException: could n ot execute query 02:57:47,984 INFO [STDOUT] org.hibernate.exception.SQLGrammarException: could n ot execute query at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:1502) at org.hibernate.loader.Loader.list(Loader.java:1482) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:365) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:268) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:782) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74) In the SQL query an alias content1_ is created for the relation table and content2_ for the Content entity table. The error is that in the order by clause it try to order on content2_.CONTENT_FK and there is no such column. The order by clause should be either on content1_.CONTENT_FK or content2_.ID. Here is another query that shows the same behaviour: 03:20:47,171 INFO [STDOUT] Hibernate: select content0_.ID as ID0_, category2_.ID as ID1_, filesets3_.ID as ID2_, price4_.ID as ID3_, content0_.DESCRIPTION as DESCRIPT2_7_0_, content0_.NAME as NAME7_0_, content0_.TIME as TIME7_0_, content0_.PRIORITY as PRIORITY7_0_, content0_.BRAND as BRAND7_0_, content0_.PRICE_FK as PRICE7_7_0_, category2_.NAME as NAME3_1_, category2_.DESCRIPTION as DESCRIPT3_3_1_, category2_.HEADER_IMAGE as HEADER4_3_1_, category2_.MAIN_IMAGE as MAIN5_3_1_, category2_.TIME as TIME3_1_, category2_.PARENT_FK as PARENT7_3_1_, categories1_.CONTENT_FK as CONTENT2___, categories1_.CATEGORIES_FK as CATEGORIES1___, filesets3_.ORDER_ID as ORDER2_9_2_, filesets3_.HANDLER_CLASS as HANDLER3_9_2_, filesets3_.CONTENT_FK as CONTENT4_9_2_, filesets3_.CONTENT_FK as CONTENT4___, filesets3_.ID as ID__, price4_.CURRENCY as CURRENCY2_3_, price4_.AMOUNT as AMOUNT2_3_ from CONTENT content0_ left outer join CATEGORIES2CONTENT categories1_ on content0_.ID=categories1_.CONTENT_FK left outer join CATEGORY category2_ on categories1_.CATEGORIES_FK=category2_.ID inner join FILESET filesets3_ on content0_.ID=filesets3_.CONTENT_FK left outer join PRICE price4_ on content0_.PRICE_FK=price4_.ID where (category2_.ID=?) order by category2_.CATEGORIES_FK , filesets3_.CONTENT_FK 03:20:47,218 WARN [JDBCExceptionReporter] SQL Error: 1054, SQLState: 42S22 03:20:47,218 ERROR [JDBCExceptionReporter] Unknown column 'category2_.CATEGORIES_FK' in 'order clause' 03:20:47,218 INFO [STDOUT] org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:1502) at org.hibernate.loader.Loader.list(Loader.java:1482) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:365) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:268) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:782) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)

    Hibernate JIRA | 1 decade ago | Sverker Abrahamsson
    org.hibernate.exception.SQLGrammarException: could n ot 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 n ot execute query

      at org.hibernate.exception.ErrorCodeConverter.convert()
    2. Hibernate
      QueryLoader.list
      1. org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
      2. org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
      3. org.hibernate.loader.Loader.doList(Loader.java:1502)
      4. org.hibernate.loader.Loader.list(Loader.java:1482)
      5. org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:365)
      5 frames
    3. Hibernate HQL/JP-QL Parser
      QueryTranslatorImpl.list
      1. org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:268)
      1 frame
    4. Hibernate
      QueryImpl.list
      1. org.hibernate.impl.SessionImpl.list(SessionImpl.java:782)
      2. org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
      2 frames