WHERE statement returning out of bounds returns

I have this query that I’m trying to work with, and ultimately I’m trying to get it to return results as soon as it matches one of my status WHERE requirements….but that’s not even my question. My question is that my query is returning a value that is TOTALLY out of bounds of my WHERE requirements, and it’s just perplexing me as to why.

I have a table named leads. It has about ~30,000 rows in the table, but the specific dataset I want to be targeting is here:

+---------+--------+---------+
| lead_id | status | owner   |
+---------+--------+---------+
|    3653 |     18 | 6585993 |
|    3984 |      3 | 6585993 |
|    4100 |     18 | 6585993 |
|    8916 |     21 | 6585993 |
|   20329 |      1 | 6585993 |
+---------+--------+---------+

Lead ID is the primary key, it’s int(11) and AUTO_INCREMENTing, status is a FOREIGN KEY, and owner is a FOREIGN KEY as well.

Here is the query I’m working with:

SELECT lead_id, status, owner FROM leads WHERE owner = 6585993 AND (`status` = 1) OR (`status` = 21) OR (`status` = 3) LIMIT 1;

My ultimate goal with this query is I want to have it cascade through my last three WHERE requirements (ie, first check the owner requirement, then – if there exists no lead with a status of “1”, then check for leads with a status of “21” and if no lead exists with a status of “1” or “21”, then check for leads with a status of “3”, and if no leads exist with any of those three statuses who also have the correct owner requirement, return none.

With how the query is written above, it returns the what I expect:

+---------+--------+---------+
| lead_id | status | owner   |
+---------+--------+---------+
|   20329 |      1 | 6585993 |
+---------+--------+---------+

Here’s the problem…when I change my status cascade to look like this (change it so that the status of “21” is to be selected first):

SELECT lead_id, status, owner FROM leads WHERE owner = 6585993 AND (`status` = 21) OR (`status` = 1) OR (`status` = 3) LIMIT 1;

It returns this:

+---------+--------+---------+
| lead_id | status | owner   |
+---------+--------+---------+
|    9435 |      1 | 1083618 |
+---------+--------+---------+

And herein lies my ultimate question…

Why does the above query return a response with a completely wrong owner?

Go to Source
Author: Adam McGurk