CODEWITHSUNDEEP
mysql
Toddish
Toddish

Reputation: 516

MYSQL double WHERE clause not working with = and AND

I have a query, generated by DataMapper (an ORM for CodeIgniter) that is returning results on one server, but not on another.

Both databases are exactly the same. The query works on servers with MYSQL versions 5.1.41, 5.1.63 and 5.5.20, and fails on 5.1.52.

The query is:

SELECT DISTINCT `clients`.`id`
FROM (`clients`)

LEFT OUTER JOIN `clients_locations`
    clients_locations ON `clients`.`id` = `clients_locations`.`client_id`

LEFT OUTER JOIN `locations`
    locations ON `locations`.`id` = `clients_locations`.`location_id`

LEFT OUTER JOIN `reports`
    location_reports ON `locations`.`id` = `location_reports`.`location_id`

WHERE
    `location_reports`.`id` =  1
AND 
    `clients`.`id` =  2

I've done some testing, and I've found the following things out:

If you just have 1 WHERE clause, it returns results.
If you change any of the operators to LIKE, != etc, it returns results
If you change the AND to an OR, it returns the results.

Similar queries are used throughout the site, so I don't know why it is this one that's failing. For instance:

SELECT DISTINCT `clients`.`id`
FROM (`clients`)

LEFT OUTER JOIN `clients_locations`
    clients_locations ON `clients`.`id` = `clients_locations`.`client_id`

LEFT OUTER JOIN `locations`
    locations ON `locations`.`id` = `clients_locations`.`location_id`

LEFT OUTER JOIN
    `locations_questionnaires` location_locations_questionnaires 
ON 
    `locations`.`id` = `location_locations_questionnaires`.`location_id`

WHERE
    `location_locations_questionnaires`.`questionnaire_id` =  3
AND 
     `clients`.`id` =  2

returns the correct results on all the servers listed above.

Has anyone seen this before?

To clarify:

I can't edit the SQL, as it's produced by an ORM.

Both datasets are EXACTLY the same.

The issue is why the query doesn't return any results on one server, when it does on the other.

UPDATE: I've just upgraded the server to 5.1.61, and the query runs fine. So looks like it's a bug in 5.1.52.

Upvotes: 1

Views: 2445

Answers (2)

Rahul
Rahul

Reputation: 77876

Try doing it like this:

SELECT DISTINCT `clients`.`id`
FROM (`clients`)

LEFT OUTER JOIN `clients_locations`
    clients_locations ON `clients`.`id` = `clients_locations`.`client_id`
    AND `clients`.`id` =  2

LEFT OUTER JOIN `locations`
    locations ON `locations`.`id` = `clients_locations`.`location_id`

LEFT OUTER JOIN `reports`
    location_reports ON `locations`.`id` = `location_reports`.`location_id`
    AND `location_reports`.`id` =  1

Upvotes: 1

user330315
user330315

Reputation:

The WHERE condition on an outer joined table, e.g.

WHERE location_reports.id =  1

effectively turns the outer join into an inner join. If you move that condition into the JOIN condition, it works correctly (as you have already noticed).

The reason is that the WHERE condition is applied after the JOINs, as the outer joined rows do not have a value for location_reports.id the condition is false for them and they are removed from the result.

Upvotes: 2

Related Questions