Documentation on usage of brackets() in mysql queries
Putting parentheses in a BETWEEN leads to strangenesses:

date between (x and y) and filter1 = filter2


is treated as

(date between (x and y) and filter1) = filter2


Why?


BETWEEN expects 2 'arguments'; you have given it (1) (x and y), which returns true or false, which are numerically 1 or 0, and filter1.
The result of that (probably 0) is then compared:  0 = filter, which is likely to be false (0).


Recommendations:


Do not use parens that way in BETWEEN.  These are OK:

(date between x and y)

(date) between (x) and (y)  -- but this is clutter

When mixing AND and OR use parens to make it clear what you want to do.  AND will be done before OR, but too many SQL programmers fail know that, so be explicit:

a AND (b OR c)  -- Without parens, you get  (a AND b) OR c

x = 1 AND ( y = 2 OR z = 3 )

Don't clutter:  Rarely do you need (x) instead lf x.
When in doubt, add parens, even if it clutters.
When using function calls, no space before the (; there are cases where the parser decides whether it is a function call versus something else by using that spacing:  DATE(foo), not DATE (foo).


The documentation spells all of this out, but not necessarily in an obvious way.  Here are clues:


The BETWEEN syntax does not show the parens you asked about.
Any expression can have parens around it.
There is a "precedence" that says what happens when mixing AND and OR -- which is just like * and +.  (You do understand that 1+2*3 is 7, not 9 because * is evaluated before +?)