Hi, during migration from Hibernate 3.3.2 to 3.6.2 I notice strange behaviourr of parsing hql queries.
The old query was:
Code:
select distinct a2p.article
from ArticleToProduct a2p
where a2p.product = :product
and a2p.article.active = true
and a2p.article.showAtProduct = true
and a2p.article.publicationStartDate <= :nowTime
and (a2p.article.publicationEndDate >= :nowTime
or a2p.article.publicationEndDate is null)
order by a2p.article.publicationStartDate desc
Which produce:
Code:
Hibernate: select distinct articletop0_.article_id as col_0_0_, article1_.publication_start_date as col_1_0_, article1_.id as id151_, article1_.type as type151_, article1_.kind as kind151_, article1_.position as position151_, article1_.template as template151_, article1_.icon as icon151_, article1_.name as name151_, article1_.publication_start_date as publicat8_151_, article1_.publication_end_date as publicat9_151_, article1_.introduction as introdu10_151_, article1_.content as content151_, article1_.active as active151_, article1_.name_for_product as name13_151_, article1_.show_at_product as show14_151_, article1_.photo as photo151_, article1_.video as video151_, article1_.link_name as link17_151_, article1_.redirect_link as redirect18_151_, article1_.on_main_page as on19_151_, article1_.main_page_rank as main20_151_, article1_.create_date as create21_151_, article1_.recommended as recomme22_151_, article1_.extra_words as extra23_151_, article1_.title as title151_, article1_.description as descrip25_151_, article1_.keywords as keywords151_, article1_.on_recommended_page as on27_151_, article1_.empty_body as empty28_151_, article1_.seo_ping_needed as seo29_151_, article1_.top_list_id as top30_151_, article1_.service_id as service31_151_, article1_.main_node_id as main32_151_, article1_.atp_min_to_display as atp33_151_, article1_.atp_max_to_display as atp34_151_, article1_.pta_min_to_display as pta35_151_, article1_.pta_max_to_display as pta36_151_
from not_replic.b24_article_to_product articletop0_ inner join not_replic.b24_articles article1_ on articletop0_.article_id=article1_.id
where articletop0_.product_id=?
and article1_.active=true and article1_.show_at_product=true and article1_.publication_start_date<=? and (article1_.publication_end_date>=? or article1_.publication_end_date is null) order by article1_.publication_start_date desc limit ?
But after the migration I get this error:
Code:
Caused by: org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
at org.hibernate.loader.Loader.doQuery(Loader.java:802)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2533)
... 77 more
And some more details:
Code:
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [select distinct article1_.id as id151_, article1_.type as type151_, article1_.kind as kind151_, article1_.position as position151_, article1_.template as template151_, article1_.icon as icon151_, article1_.name as name151_, article1_.publication_start_date as publicat8_151_, article1_.publication_end_date as publicat9_151_, article1_.introduction as introdu10_151_, article1_.content as content151_, article1_.active as active151_, article1_.name_for_product as name13_151_, article1_.show_at_product as show14_151_, article1_.photo as photo151_, article1_.video as video151_, article1_.link_name as link17_151_, article1_.redirect_link as redirect18_151_, article1_.on_main_page as on19_151_, article1_.main_page_rank as main20_151_, article1_.create_date as create21_151_, article1_.recommended as recomme22_151_, article1_.extra_words as extra23_151_, article1_.title as title151_, article1_.description as descrip25_151_, article1_.keywords as keywords151_, article1_.on_recommended_page as on27_151_, article1_.empty_body as empty28_151_, article1_.seo_ping_needed as seo29_151_, article1_.top_list_id as top30_151_, article1_.service_id as service31_151_, article1_.main_node_id as main32_151_, article1_.atp_min_to_display as atp33_151_, article1_.atp_max_to_display as atp34_151_, article1_.pta_min_to_display as pta35_151_, article1_.pta_max_to_display as pta36_151_
from not_replic.b24_article_to_product articletop0_ inner join not_replic.b24_articles article1_ on articletop0_.article_id=article1_.id cross join not_replic.b24_articles article2_
where articletop0_.article_id=article2_.id and articletop0_.product_id=?
and article2_.active=true and article2_.show_at_product=true and article2_.publication_start_date<=? and (article2_.publication_end_date>=? or article2_.publication_end_date is null) order by article2_.publication_start_date desc];
SQL state [90068]; error code [90068]; Wyrazenie sortowania "ARTICLE2_.PUBLICATION_START_DATE" musi byc na liscie wyboru w tym przypadku
Order by expression "ARTICLE2_.PUBLICATION_START_DATE" must be in the result list in this case; SQL statement:
So I correct my query to:
Code:
select distinct a
from ArticleToProduct a2p
join a2p.article a
where a2p.product = :product
and a.active = true
and a.showAtProduct = true
and a.publicationStartDate <= :nowTime
and (a.publicationEndDate >= :nowTime
or a.publicationEndDate is null)
order by a.publicationStartDate desc
And now the query parsing result is the same as in old hibernate version, but I wondering what was changed. I noticed that old query (using implicite join) with new hibernate is trying to generate sql with duoble join:
Code:
from not_replic.b24_article_to_product articletop0_ inner join not_replic.b24_articles article1_ on articletop0_.article_id=article1_.id cross join not_replic.b24_articles article2_
I try to find something about this (I'm not sure if this hhh are really connected to my problem):
http://opensource.atlassian.com/projects/hibernate/browse/HHH-280http://opensource.atlassian.com/projects/hibernate/browse/HHH-294Anyway, what is the issue with this queries? Is the old version (with hib 3.6.2) bad? Or maybe there is some simple explanation?
Login
FAQ
Search