Hibernate - different behavior on Linux and Windows

Question

I've run into a peculiar problem where a hql query is working as expected on Windows but does not on Linux.

Here is the query:

select distinct resource from Resource resource , ResourceOrganization ro 
where (resource.active=true) and (resource.published=true) and 
((resource.resourcePublic=true) or ((ro.resource.id=resource.id and 
ro.organization.id=2) and ((ro.resource.id=resource.id and ro.forever=true) or 
(ro.resource.id=resource.id and current_date between ro.startDate and ro.endDate))))

Explanation: I'm fetching resources from database where they are active, published and either public or shared with an organization such that the sharing is either forever or between 2 dates.

I have the same data in both the databases (exported from Linux and imported in Windows). On windows I get Result size = 275

and in Linux I get Result size = 0

I've looked at the data in Linux and I see that I should get non-zero result size.

Windows has Java 1.5 whereas Linux has Java 1.6

Any suggestions on where I should look to address this problem?

Thanks!

Answer 1

1. In a SQL command-line tool, enter the SQL one phrase at a time and see when the Linux version goes awry. For best results, do the same thing on Windows.
2. Make sure the SQL generated is the same on windows and linux.
3. and you're sure they are referring to exactly the same database, and using the same login? (edit - I re-read and saw I have the same data - Are You Suuuuuure?)
4. and finally, I see this: and ro.organization.id=2 Are you sure the ID is 2 on both systems? You could get lit up by the sequence numbers/autokey IDs being different.