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

Hibernate JIRA | Sverker Abrahamsson | 1 decade ago
tip
Your exception is missing from the Samebug knowledge base.
Here are the best solutions we found on the Internet.
Click on the to mark the helpful solution and get rewards for you help.
  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

    Using a NamedQuery with a composite class

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

    Hibernate not able to fetch based on mixed case quoted column

    Stack Overflow | 3 years ago | alok
    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