Snowflake: Using multiple AND/OR statements breaks my date filter

Question

Using Snowflake I am searching for entries where an individual carried out an action. I search for two identifiers AGENT_NAME and AGENTID and then I use a BETWEEN to search for actions created on that day. If I search for one person the report works perfectly. If I include a second person, the date column breaks and starts displaying all days.

When I run the this it works perfectly.

WHERE
    AGENT_NAME = 'John Wick'
    AND AGENT_ID = '1234'
    AND ACTION_CREATED_DATE BETWEEN '2023-01-17 00:00:00.000' AND '2023-01-17 23:59:59.000'

When I try to incorporate multiple people like this, the ACTION_CREATED_DATE column displays results from all times.

WHERE
    (AGENT_NAME = 'John Wick' AND AGENT_ID = '1234')
    OR (AGENT_NAME = 'Tom Cruise' AND AGENT_ID = '5678')
    AND ACTION_CREATED_DATE BETWEEN '2023-01-17 00:00:00.000' AND '2023-01-17 23:59:59.000'

I would prefer to set up 24 different people's AGENT_NAME and AGENT_ID somewhere else within the same query as I do not have permissions to create separate tables/schemas etc, and then in the WHERE statement write their name, calling on the pre-stored data. I tried defining a list of individuals and ID's in a CTE, DECLARE statements, subqueries and temporary tables.

I put everyone in using parentheses and AND/OR in the WHERE condition but testing with two people breaks the BETWEEN function.

Answer 1

It requires additional parenthesis around OR:

WHERE
    ((AGENT_NAME = 'John Wick' AND AGENT_ID = '1234')
    OR (AGENT_NAME = 'Tom Cruise' AND AGENT_ID = '5678'))
    AND ACTION_CREATED_DATE BETWEEN '2023-01-17 00:00:00.000' 
                                AND '2023-01-17 23:59:59.000

A more readable way is using IN operator:

WHERE
 (AGENT_NAME,AGENT_ID) IN (( 'John Wick', '1234'), ('Tom Cruise','5678'))
 AND ACTION_CREATED_DATE BETWEEN '2023-01-17 00:00:00.000' 
                             AND '2023-01-17 23:59:59.000

Answer 2

You need to put brackets around the things that are in each OR and the OR as whole.

WHERE A OR ( b AND c) OR (d AND e) AND f AND g 

most likely should be

WHERE (A OR ( b AND c) OR (d AND e)) AND f AND g 

but OR are bad for performance as a big picture, so another way is to run many passes and UNION ALL the distinct parts together,

thus:

WHERE A AND f AND g 

UNION ALL

WHERE (NOT A) AND ( b AND c) AND f AND g 

UNION ALL

WHERE (NOT (A AND b AND c)) AND f AND g 

...

thus give you each leg of the OR tree as distinct SETS of values, will perform much faster (if you can write it this way, albeit it might be super more ugly to read, and prove is equal/correct)