-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Different query parsing results, during migration from 3.3.2
PostPosted: Thu Mar 31, 2011 3:47 am 
Newbie

Joined: Thu Mar 31, 2011 3:15 am
Posts: 1
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-280
http://opensource.atlassian.com/projects/hibernate/browse/HHH-294

Anyway, what is the issue with this queries? Is the old version (with hib 3.6.2) bad? Or maybe there is some simple explanation?


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.