having with calculated field generates bad sql on mysql

Description

This query:

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

note the generated sql below ... col_0_0 is used for the calculated distance in the first reference then "distance" later

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 ?

mappings:

<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"/>
<perperty name="distance" column="distance"/>
<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>

Here is the thread in the groups:

http://forum.hibernate.org/viewtopic.php?p=2273946#2273946

Environment

hibernate 3.1, mysql 5

Assignee

Unassigned

Reporter

Mark Stephens

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Minor
Configure