Hibernate version:
2.1.4

Database version
Cloudscape 10

HQL
BillConfig.getAllBillingConfigurationsByOwnerId =
SELECT BC
FROM BillConfig BC
WHERE BC.reportConfig.usageContract.owner.id = :ownerId
GROUP BY BC



Generated SQL and error

select billconfig0_.ID as ID1_, case when billconfig0__3_.ID is not null then 1 when billconfig0__4_.ID is not null then 2 when billconfig0__5_.ID is not null then 3 when billconfig0__1_.ID is not null then 4 when billconfig0__2_.ID is not null then 5 when billconfig0_.ID is not null then 0 end as clazz_1_, billconfig0_.REPORT_CONFIG_ID as REPORT_C3_39_1_, reportconf1_.RLQueryContext as RLQuery51_0_ from RPT_BILL_CONFIG billconfig0_ left outer join RPT_EXTERNAL_BILL_CONFIG billconfig0__1_ on billconfig0_.ID=billconfig0__1_.ID left outer join RPT_COST_BILL_CONFIG billconfig0__2_ on billconfig0_.ID=billconfig0__2_.ID left outer join RPT_UNIQUE_USER_BILL_CONFIG billconfig0__3_ on billconfig0_.ID=billconfig0__3_.ID left outer join RPT_HIGH_WATERMARK_BILL_CONFIG billconfig0__4_ on billconfig0_.ID=billconfig0__4_.ID left outer join RPT_TOKEN_BILL_CONFIG billconfig0__5_ on billconfig0_.ID=billconfig0__5_.ID left outer join ReportConfiguration reportconf1_ on billconfig0_.REPORT_CONFIG_ID=reportconf1_.id where billconfig0_.ID=33226753

Note the error message below which doesn't seem to make sense.

2005-01-05 22:16:13,096 ERROR [upm.actions] [PoolThread-11] Failed to display billing configurations summary
2005-01-05 22:16:13,096 ERROR [upm.actions] [PoolThread-11] Failed to display billing configurations summary
[Incident# 1730-962] com.macrovision.flexnet.platform.exceptions.FLEXnetHibernateException: :Cause: could not load by id: [com.macrovision.flexnet.reporting.bizobjects.BillConfig#33226753] :Cause: Types 'INTEGER' and 'CHAR' are not type compatible. (Neither type is assignable to the other type.)
at com.macrovision.flexnet.platform.services.persistence.PersistenceService.getQueryResultsByHQLQuery(PersistenceService.java:351)
at com.macrovision.flexnet.platform.services.persistence.PersistenceService.getQueryResults(PersistenceService.java:371)



SQL that works

changing integers to strings seems to work

select billconfig0_.ID as ID1_, case when billconfig0__3_.ID is not null then '1' when billconfig0__4_.ID is not null then '2' when billconfig0__5_.ID is not null then '3' when billconfig0__1_.ID is not null then '4' when billconfig0__2_.ID is not null then '5' when billconfig0_.ID is not null then '0' end as clazz_1_, billconfig0_.REPORT_CONFIG_ID as



Is it possible to tell Hibernate to generate the right SQL? Could we change the Hibernate Dialect for Cloudscape?

if you have a patch that make it work then sure, change the dialect and submit it to JIRA

Even better, fix this bug in cloudscape!

(You might notice that cloudscape/derby is not officially supported, because of a raft of small bugs like this.)

Check http://opensource.atlassian.com/project ... se/HB-1224, it may help