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)
I think this is a bug, should I report it?
|