Mysql – WHERE statement returning out of bounds returns

MySQLmysql-5.7

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?

Best Answer

First and foremost:

Change your where clause and put all OR's inside a single parenthesis.

SELECT 
    lead_id, status, owner 
FROM 
    leads 
WHERE 
    owner = 6585993 
    AND ((`status` = 1) OR (`status` = 21) OR (`status` = 3))
ORDER BY
    CASE `status` 
        WHEN 1 THEN 0 
        WHEN 21 THEN 1
        WHEN 3 THEN 2
    END
LIMIT 1
;

Then, if you prefer and order, set and ORDER BY clause using a case to assign the new values, 1 = First (0), 21 = Second (1) and 3 = Last (2).

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

db<>fiddle here