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

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 ?

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

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

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

Put the expression in the having clause.

Thanks!

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.