Odd parentheses behavior in Where clause

Question

SETUP:

MySQL 5.7.14 (Google SQL)

DESCRIPTION:

In the following scenario it appears I am getting some false matches in my where clause where I'm NOT using parentheses. But adding the parentheses DOES yield the correct results.

This Query DOES return results with tsd.StatusID = 3 (wrong):

SELECT 
tsee.ID, tsd.StatusID

FROM TSShiftDetails tsd
JOIN TSShiftEmployees tse
    ON tse.ShiftID = tsd.ID
JOIN TSShiftEmpEntries tsee
    ON tsee.ShiftEmpID = tse.ID

WHERE tsee.CCID IN (4590) OR tsee.CCID LIKE null
AND tsd.StatusID != 3

While this query DOES NOT return results with AND tsd.StatusID = 3 (correct):

SELECT 
tsee.ID, tsd.StatusID

FROM TSShiftDetails tsd
JOIN TSShiftEmployees tse
    ON tse.ShiftID = tsd.ID
JOIN TSShiftEmpEntries tsee
    ON tsee.ShiftEmpID = tse.ID

WHERE (tsee.CCID IN (4590) OR tsee.CCID LIKE null)
AND tsd.StatusID != 3

QUESTION:

While I feel I completely understand why the query WITH the parentheses is working. My question is WHY is the one without parentheses returning records with a StatusID == 3? I would think without any functional ordering of parentheses, the AND tsd.StatusID != 3 clause would be applied to every match regardless of the preceding OR.

What Ya'll think? Am I misunderstanding, or is MySQL behaving inconsistently here?

P.S.

FYI, Yes there is a front end application reason for the need to have the Where clause formatted this way. eg. tsee.CCID IN (4590) as opposed to tsee.CCID =4590

Answer 1

The explanation has nothing to do with LIKE NULL or IN ( ).

Boolean expressions follow an order of operator precedence, just like arithmetic.

In arithmetic, you may remember that multiplication has higher precedence than addition:

A + B * C

Without parentheses, this works exactly like:

A + (B * C)

If you want the addition to be evaluated first, you must use parentheses to override the default operator precedence:

(A + B) * C

Similarly, in boolean expressions, AND has higher precedence than OR.

A OR B AND C

Works like:

A OR (B AND C)

If you want the OR to be evaluated first, you must use parentheses to override the default operator precedence:

(A OR B) AND C

How does this explain what you're seeing?

WHERE tsee.CCID IN (4590) OR tsee.CCID LIKE null
AND tsd.StatusID != 3

This works as if you had done:

WHERE tsee.CCID IN (4590) OR (tsee.CCID LIKE null
AND tsd.StatusID != 3)

So if it finds a row with CCID 4590, that row satisfies the whole WHERE clause, because true OR (anything) is still true.