Using Hibernate 2.1 against MySql 5.

When I perform a distinct with an order by on the same column in the same table, Hibernate returns an unordered result set. For example:

Here is my Java hql:

String hql = "select distinct aircraft.platform from Aircraft aircraft order by aircraft.platform";

Here is the prepared statement generated by Hibernate:

select distinct aircraft0_.PLATFORM as x0_0_ from aircraft aircraft0_ order by aircraft0_.PLATFORM

The result set is unordered because of the alias "x0_0_". I need to somehow remove the alias on the distinct platform value or use the alias in the order by.

Any ideas on how to solve this?

I would imagine your platform property in aircraft is either not mapped at all in aircraft or mapped incorrectly. Post your mappings files.

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>
<!--
Created by the Middlegen Hibernate plugin

http://boss.bekk.no/boss/middlegen/
http://hibernate.sourceforge.net/
-->

<class
name="com.smiths.aerospace.forecasting.bo.Aircraft"
table="aircraft"
>
<meta attribute="class-description" inherit="false">
@hibernate.class
table="aircraft"
</meta>

<id
name="aircraftId"
type="java.lang.Integer"
column="AIRCRAFT_ID"
>
<meta attribute="field-description">
@hibernate.id
generator-class="assigned"
type="java.lang.Integer"
column="AIRCRAFT_ID"

</meta>
<generator class="assigned" />
</id>

<property
name="platform"
type="java.lang.String"
column="PLATFORM"
not-null="true"
length="30"
>
<meta attribute="field-description">
@hibernate.property
column="PLATFORM"
length="30"
not-null="true"
</meta>
</property>
<property
name="aircraftDescription"
type="java.lang.String"
column="AIRCRAFT_DESCRIPTION"
not-null="true"
length="250"
>
<meta attribute="field-description">
@hibernate.property
column="AIRCRAFT_DESCRIPTION"
length="250"
not-null="true"
</meta>
</property>

<!-- associations -->
<!-- bi-directional one-to-many association to FitList -->
<set
name="fitLists"
lazy="true"
inverse="true"
>
<meta attribute="field-description">
@hibernate.set
lazy="true"
inverse="true"

@hibernate.collection-key
column="AIRCRAFT_ID"

@hibernate.collection-one-to-many
class="com.smiths.aerospace.forecasting.bo.FitList"
</meta>
<key>
<column name="AIRCRAFT_ID" />
</key>
<one-to-many
class="com.smiths.aerospace.forecasting.bo.FitList"
/>
</set>
<!-- bi-directional one-to-many association to AircraftInventory -->
<set
name="aircraftInventories"
lazy="true"
inverse="true"
>
<meta attribute="field-description">
@hibernate.set
lazy="true"
inverse="true"

@hibernate.collection-key
column="AIRCRAFT_ID"

@hibernate.collection-one-to-many
class="com.smiths.aerospace.forecasting.bo.AircraftInventory"
</meta>
<key>
<column name="AIRCRAFT_ID" />
</key>
<one-to-many
class="com.smiths.aerospace.forecasting.bo.AircraftInventory"
/>
</set>
<!-- bi-directional many-to-one association to AcManufacturer -->
<many-to-one
name="acManufacturer"
class="com.smiths.aerospace.forecasting.bo.AcManufacturer"
not-null="true"
>
<meta attribute="field-description">
@hibernate.many-to-one
not-null="true"
@hibernate.column name="AC_MANUFACTURER_ID"
</meta>
<column name="AC_MANUFACTURER_ID" />
</many-to-one>
<!-- bi-directional many-to-one association to AircraftSery -->
<many-to-one
name="aircraftSery"
class="com.smiths.aerospace.forecasting.bo.AircraftSery"
not-null="true"
>
<meta attribute="field-description">
@hibernate.many-to-one
not-null="true"
@hibernate.column name="SERIES_ID"
</meta>
<column name="SERIES_ID" />
</many-to-one>

</class>
</hibernate-mapping>

I took a closer look at the generated query and ran it myself on some dummy data and it seemed to be doing the ordering fine. I don't think theres anything wrong with the generated query.



On this data ( select * from platform )

this query



produced this



whereas this



produced this



Maybe the data in the db isn't what you think it is?

The caveat to this issue is that value for platform in this case can exist multiple times on the table. Try executing the distinct query with the order by agains the data below.


AIRCRAFT_ID PLATFORM SERIES_ID
1 A300 22
2 A300 6
3 A300 12
4 A310 22
5 A310 12
6 A318 22
7 A319 22
8 A320 22
9 A321 22
10 A330 22
11 A340 22
12 A340 13
13 A340 14
14 A380 22
15 BAe ATP 22
16 BAe 146 22
17 BAe 146 11
18 B717 22
19 B727 22
20 B727 2
21 B727 10
22 B737 22
23 B737 3
24 B737 4
25 B737 15
26 B737 16
27 B737 7
28 B737 17
29 B747 22
30 B747 4
31 B747 18
32 B747 19
33 B757 22
34 B757 2
35 B767 22
36 B767 4
37 B767 15
38 B767 2
39 B767 20
40 B777 22
41 B777 20
42 B777 2
43 DC9 22
44 DC10 22
45 MD11 22
46 MD80 22
47 MD90 22
48 MD90 21
49 F27 22
50 F28 22

I tried it with some duplicate data and it worked fine. What db are you using? Oracle has some weird order by syntax I think so maybe that's not working properly if you're using Oracle.

Using MySql 5 with the MySql connector 3.0.14