-->
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.  [ 9 posts ] 
Author Message
 Post subject: problems with having clause
PostPosted: Wed Nov 16, 2005 11:44 am 
Beginner
Beginner

Joined: Tue May 03, 2005 11:45 pm
Posts: 43
I don't know why hibernate doesn't like my having clause, see details below. I saw a mention that some databases don't support having and mysql was noted, but the following query in mysql works just great:

select zipcode, latitude, longitude, acos(sin(0.590156348) * sin(latitude/57.29) + cos(0.590156348) * cos(latitude/57.29) * cos(longitude/57.29 - -1.475641918)) * 3963 as distance from address having distance < 20 limit 5


+---------+-----------+------------+-----------------+
| zipcode | latitude | longitude | distance |
+---------+-----------+------------+-----------------+
| 30126 | 33.816905 | -84.500130 | 2.3131387511929 |
| 30126 | 33.825405 | -84.576030 | 2.3512106908091 |
| 30126 | 33.819405 | -84.498830 | 2.4265396794354 |
| 30126 | 33.820705 | -84.580630 | 2.4744507668895 |
| 30126 | 33.825437 | -84.578600 | 2.4848328160623 |
+---------+-----------+------------+-----------------+
5 rows in set (0.08 sec)



Hibernate version:

3.1 rc2

Mapping documents:

<hibernate-mapping>
<class name="value.Business" table="business" lazy="false">
<id name="id" column="biz_id">
<generator class="native"/>
</id>
<property name="name" column="biz_name"/>
<property name="currentRating" column="current_rating"/>
<property name="numRatings" column="num_ratings"/>
<property name="numPositive" column="num_positive"/>
<property name="numNegative" column="num_negative"/>
<property name="statusCode" column="status_cd"/>
<property name="yearStarted">
<column name="started_yr" not-null="false"/>
</property>
<many-to-one name="address" class="value.Address" column="address_id" cascade="all"/>
</class>
</hibernate-mapping>


<hibernate-mapping>
<class name="value.Address" table="address" lazy="false">
<id name="id" column="address_id">
<generator class="native"/>
</id>
<property name="latitude"/>
<property name="longitude"/>
<property name="line1"/>
<property name="line2"/>
<property name="city"/>
<property name="state"/>
<property name="zipcode"/>
<property name="zip4"/>
<property name="phone1"/>
<property name="phone2"/>
<property name="fax"/>
<property name="url"/>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

select acos(sin(:lat) * sin(biz.address.latitude/57.29) + cos(:lat) * cos(biz.address.latitude/57.29) * cos(biz.address.longitude/57.29 - :lon) ) * 3956 as distance, biz from Business biz having distance < 10

Query query = session.createQuery(hql)
.setDouble("lat", targetZip.getLatitude())
.setDouble("lon", targetZip.getLongitude())
.setMaxResults(10);
List list = query.list();

Full stack trace of any exception that occurs:

at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:242)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:153)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:103)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:473)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1060)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1010)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at com.chaos.unittest.HibernateUnitTest.testHqlSearch(HibernateUnitTest.java:553)
at com.chaos.unittest.HibernateUnitTest.main(HibernateUnitTest.java:599)
Caused by: line 1:209: unexpected token: having
at org.hibernate.hql.antlr.HqlBaseParser.fromClassOrOuterQueryPath(HqlBaseParser.java:1938)
at org.hibernate.hql.antlr.HqlBaseParser.fromRange(HqlBaseParser.java:1564)
at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1408)
at org.hibernate.hql.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1130)
at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:702)
at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:296)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:159)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:236)
... 8 more

Name and version of the database you are using:

MYSQL 5


Top
 Profile  
 
 Post subject: dialect problem?
PostPosted: Wed Nov 16, 2005 2:25 pm 
Beginner
Beginner

Joined: Tue May 03, 2005 11:45 pm
Posts: 43
Is it possible this is a problem in the mysql dialect?

I forgot to add the generated sql ... this is WITHOUT the having clause because that generates an exception before it even gets to this stage

Hibernate: select acos(sin(?)*sin(address1_.latitude/57.29)+cos(?)*cos(address1_.latitude/57.29)*cos(address1_.longitude/57.29-?))*3956 as col_0_0_, business0_.biz_id as col_1_0_, business0_.biz_id as biz1_1_, business0_.biz_name as biz2_1_, business0_.distance as distance1_, business0_.current_rating as current4_1_, business0_.num_ratings as num5_1_, business0_.num_positive as num6_1_, business0_.num_negative as num7_1_, business0_.status_cd as status8_1_, business0_.started_yr as started9_1_, business0_.address_id as address10_1_ from business business0_, address address1_ where business0_.address_id=address1_.address_id limit ?


Top
 Profile  
 
 Post subject: This having goes through ....
PostPosted: Wed Nov 16, 2005 2:31 pm 
Beginner
Beginner

Joined: Tue May 03, 2005 11:45 pm
Posts: 43
I just figured out that this "having" generates sql but doesn't work as expected. There are no results:

select acos(sin(:lat) * sin(biz.address.latitude/57.29) + cos(:lat) * cos(biz.address.latitude/57.29) * cos(biz.address.longitude/57.29 - :lon) ) * 3956 as distance, biz from Business biz group by biz.name having distance > 4

Hibernate: select acos(sin(?)*sin(address1_.latitude/57.29)+cos(?)*cos(address1_.latitude/57.29)*cos(address1_.longitude/57.29-?))*3956 as col_0_0_, business0_.biz_id as col_1_0_, business0_.biz_id as biz1_1_, business0_.biz_name as biz2_1_, business0_.distance as distance1_, business0_.current_rating as current4_1_, business0_.num_ratings as num5_1_, business0_.num_positive as num6_1_, business0_.num_negative as num7_1_, business0_.status_cd as status8_1_, business0_.started_yr as started9_1_, business0_.address_id as address10_1_ from business business0_, address address1_ where business0_.address_id=address1_.address_id group by business0_.biz_name having distance>4 limit ?

0 elements
took 1057 millis


Top
 Profile  
 
 Post subject: Re: This having goes through ....
PostPosted: Tue Nov 29, 2005 2:47 pm 
Expert
Expert

Joined: Thu Jan 08, 2004 6:17 pm
Posts: 278
mrsnospam wrote:
select acos(sin(:lat) * sin(biz.address.latitude/57.29) + cos(:lat) * cos(biz.address.latitude/57.29) * cos(biz.address.longitude/57.29 - :lon) ) * 3956 as distance, biz from Business biz group by biz.name having distance > 4

Hibernate: select acos(sin(?)*sin(address1_.latitude/57.29)+cos(?)*cos(address1_.latitude/57.29)*cos(address1_.longitude/57.29-?))*3956 as col_0_0_, business0_.biz_id as col_1_0_, business0_.biz_id as biz1_1_, business0_.biz_name as biz2_1_, business0_.distance as distance1_, business0_.current_rating as current4_1_, business0_.num_ratings as num5_1_, business0_.num_positive as num6_1_, business0_.num_negative as num7_1_, business0_.status_cd as status8_1_, business0_.started_yr as started9_1_, business0_.address_id as address10_1_ from business business0_, address address1_ where business0_.address_id=address1_.address_id group by business0_.biz_name having distance>4 limit ?

0 elements
took 1057 millis

Looks to me like Hibernate's SQL munging is renaming your "distance" column to "col_0_0_". It would be a terrible hack, but would it work to change your HQL to this?

select acos(sin(:lat) * sin(biz.address.latitude/57.29) + cos(:lat) * cos(biz.address.latitude/57.29) * cos(biz.address.longitude/57.29 - :lon) ) * 3956 as col_0_0_, biz from Business biz group by biz.name having col_0_0_ > 4

It's terrible, but sometimes terrible things work :-) Of course this really does sound like a bug in Hibernate, in which case you should JIRA it.

Cheers! (And if this helped, please rate me ;-)
Rob


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 06, 2005 12:41 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
HQL select aliases are not meant to be used in other parts of the query. They are really for use with select new(...) or Hibernate Tools.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 06, 2005 2:13 pm 
Expert
Expert

Joined: Thu Jan 08, 2004 6:17 pm
Posts: 278
gavin wrote:
HQL select aliases are not meant to be used in other parts of the query. They are really for use with select new(...) or Hibernate Tools.

Gavin, I love your presence on these forums, but sometimes your replies tend to be of the form "You shouldn't be doing that that way." ... with no statement of what would be better.

Should she not even be trying to write this kind of HQL at all? Should she be doing a straight SQL query (perhaps with the right brace-expressions to get Hibernate objects back)? Or is there some other way to write the HQL here such that her having clause will actually work?

What she's trying to do seems very reasonable, but you provided only negative input (what not to do), rather than also positive input (what she *should* do instead). Here's hoping :-)

Cheers!
Rob


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 06, 2005 2:57 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Put the expression in the having clause.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 06, 2005 3:31 pm 
Expert
Expert

Joined: Thu Jan 08, 2004 6:17 pm
Posts: 278
Thanks!


Top
 Profile  
 
 Post subject: Re: problems with having clause
PostPosted: Tue Jun 05, 2012 3:46 pm 
Newbie

Joined: Tue Jun 05, 2012 3:41 pm
Posts: 1
What well informative article from yours about "problems with having clause" ! Really I like your measure distance between cities post because I am a new about your topic. Thanks for your nice sharing.


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

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.