SQL query execution - different outcomes on Windows and Linux

The following is generated query from Hibernate (except 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+ records in both the Windows and Linux databases and currently for each record, the following happens to be true: active = 1 published = 1 resourcePublic = 1

When I run this directly in a SQL client, this SQL query gets me all the matching records on Windows but none on Linux. I've MySQL 5.1 on both Windows and Linux.

If I apply the Boolean logic, (true and true and (true or whatever)), I expect the outcome to be true. It indeed is true on Windows but false on Linux!!!

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

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

So, just the presence of conditions related to resourceOrganization is making the query bring 0 results on Linux and I ex开发者_JAVA百科pected that since it is the second part of an 'or' condition whose first part is true, the outcome should be true.

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

Thanks in advance!

• Check the case sensitivity and collation sets (Collation issues)
• Check the table case sensitivity. In particular note that on windows the table names are case-insensitive and on Linux they are case-sensitive.
• Have you tried a simple test case on both system?

Check that current_date() returns the same format in both plataforms

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

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

What does this query return?

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 
    )

Also don't forget to check the collation and case sensitivity, if one server uses a different collation to the other then you will have this same issue.