CODEWITHSUNDEEP
mysqlsqlwindowslinux
Dilip Shah
Dilip Shah

Reputation: 297

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 expected 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!

Upvotes: 4

Views: 1689

Answers (4)

DeveloperChris
DeveloperChris

Reputation: 3448

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.

Upvotes: 0

Ben
Ben

Reputation: 35643

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 
    )

Upvotes: 0

Pat
Pat

Reputation: 5911

Upvotes: 2

Daniel Hermosel
Daniel Hermosel

Reputation: 423

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

Upvotes: 0

Related Questions