Executing SQL queries - different results on Windows and Linux

Question

The generated request from Hibernate (except that I replaced the list of fields with *):

select * from resource resource0_, resourceOrganization resourceor1_ where resource0_.active=1 and resource0_.published=1 and ( resource0_.resourcePublic=1 or resourceor1_.resource_id=resource0_.id and resourceor1_.organization_id=2 and ( resourceor1_.resource_id=resource0_.id and resourceor1_.forever=1 or resourceor1_.resource_id=resource0_.id and ( current_date between resourceor1_.startDate and resourceor1_.endDate ) ) ) 

Currently, I have 200+ entries in both Windows and Linux databases, and currently the following is done for each entry: active = 1 published = 1 resourcePublic = 1

When I run this directly in the SQL client, this SQL query gets me all the relevant records in Windows, but not one of them in Linux. I have MySQL 5.1 on both Windows and Linux.

If I apply logical logic (true and true and (true or something else)), I expect the result to be true. This is true for Windows, but false for Linux !!!

If I modify the query as follows, it works on both Windows and Linux:

 select * from resource resource0_ where resource0_.active=1 and resource0_.published=1 and ( resource0_.resourcePublic=1 ) 

So, only the presence of conditions related to resourceOrganization leads to the fact that the query returns 0 results in Linux, and I expected that since this is the second part of the “or” condition, the first part of which is true, the result should be true.

Any idea why this difference in behavior between the two OSs and why something that should obviously work on Linux doesn't work!

Thanks in advance!

Answer 1

• Check Case Sensitivity and Sort Kits ( Sort Issues )
• Check table case sensitivity . In particular, note that in windows, table names are not case sensitive, and on Linux they are case sensitive.
• Have you tried a simple test case on both systems?

Answer 2

Make sure current_date () returns the same format in both formats

Answer 3

Also, be sure to check compliance and case sensitivity, if one server uses a different sort for another, then you will have the same problem.

Answer 4

I notice that the second test query only processes the resource table, not the resourceOrganisation table.

I suspect that the resourceOrganisation table is populated differently on two machines and the corresponding rows may not exist in your Linux MySQL.

What returns this query?

 select * from resource resource0_, resourceOrganization resourceor1_ where resource0_.active=1 and resource0_.published=1 and ( resource0_.resourcePublic=1 or resourceor1_.resource_id=resource0_.id and resourceor1_.organization_id=2 )