Hibernate version:
2.1.4


Name and version of the database you are using:
Cloudscape 10

The following HQL:

SELECT rights.featureName, rights.featureVersion, rights.vendorDaemonName, sum(rights.seatCount) FROM com.macrovision.flexnet.manager.entities.LicenseRights AS rights WHERE rights.licenseFile.licenseServer.master=1 GROUP BY rights.featureName, rights.featureVersion, rights.vendorDaemonName ORDER BY rights.featureName, rights.featureVersion, rights.vendorDaemonName

Results in SQL that does not work in Cloudscape
select licenserig0_.featureName as x0_0_, licenserig0_.featureVersion as x1_0_, licenserig0_.vendorDaemonName as x2_0_, sum(licenserig0_.seatCount) as x3_0_ from LicenseRights licenserig0_, LicenseFile licensefil1_, LicenseServer licenseser2_ where (licenseser2_.master=1 and licenserig0_.LicenseFile_id=licensefil1_.id and licensefil1_.LicenseServer_id=licenseser2_.id) group by licenserig0_.featureName , licenserig0_.featureVersion , licenserig0_.vendorDaemonName
order by licenserig0_.featureName , licenserig0_.featureVersion , licenserig0_.vendorDaemonName

After some testing we realized the SQL works if we order by the alias by the column name.

The error message
Caused by: SQL Exception: Column 'LICENSERIG0_.VENDORDAEMONNAME' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'LICENSERIG0_.VENDORDAEMONNAME' is not a column in the target table.


The following query seems to work

select licenserig0_.featureName as x0_0_, licenserig0_.featureVersion as x1_0_, licenserig0_.vendorDaemonName as x2_0_,
sum(licenserig0_.seatCount) as x3_0_ from LicenseRights licenserig0_, LicenseFile licensefil1_, LicenseServer
licenseser2_ where (licenseser2_.master=1 and licenserig0_.LicenseFile_id=licensefil1_.id and licensefil1_.LicenseServer_id=licenseser2_.id) group by licenserig0_.featureName , licenserig0_.featureVersion , licenserig0_.vendorDaemonName
order by x0_0_, x1_0_, x2_0_

If we do not have the group by, order by seems to work with the column name ordering.

Any ideas how to tweak the HQL so Hibernate can generate the right SQL?

Thanks

Query is fine.It is cloudscape problem
try yet
...
order by 1,2,3

regards
Peco