Search code examples
mysqlboolean-operations

Odd parentheses behavior in Where clause

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

Solution

  • 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.