Problem solve Get help with specific problems with your technologies, process and projects.

ANDs and ORs and NULLs in MySQL

I've searched everywhere for an answer to this one: I'm using MySQL and built a form where variables can be entered by a user and then results are selected based on those variables. Everyone keeps telling me that ANDs have a higher precedence than ORs... so why won't this work?

$query = "SELECT * FROM music WHERE";
if ($artist !=''){
  $query ="$query artist LIKE '%$artist%'";
if ($date !=''){
  if ($query_mod !="0"){$query="$query &&";}
  $query = "$query date OR altdate LIKE '%$date%'";
if ($title !=''){
  if ($query_mod !="0"){$query="$query &&";}
  $query = "$query title LIKE '%$title%'";

Basically the entire statement ends up being translated as an OR statement no matter if I use parentheses or not. How can I force it to search both date and altdate (while still allowing that only one of these fields will ever have a value?)

A very interesting question. First of all, your code will be somewhat simpler if you use the "WHERE 1=1" technique as described in The "any" option in dynamic search SQL.

Secondly, it appears that you are generating double ampersands instead of AND keywords; while this does work in MySQL, I think it's non-standard SQL and you should use AND instead. Similarly, it's also wise never to use a reserved word like DATE to name a column.

Furthermore, the use of LIKE against date columns requires conversion of the date values to strings, which is slower. While this does allow partial matching (for example, entering only the year should return all date strings with that year), this could actually work against you, since entering a value of 02 will match not only 2003-02-21, but also 2002-11-29 and 2003-11-02.

The real problem is this clause:

[WHERE/AND] date OR altdate LIKE '%$date%' 

What's happening is that MySQL sees the OR keyword right after the DATE column name, and therefore decides to evaluate the date value as a logical value by itself. If the date value is zero (unlikely), it evaluates logically to 1, while if it's non-zero, it evaluates to 0 -- in other words, false. But if it's NULL, it evaluates to NULL, which could throw everything off. Prior to version 4.0.5, MySQL, bless its heart, evaluates NULL OR 0 as NULL, and 0 OR NULL as 0. Whoa.

What you meant is that the clause should be interpreted as --

[WHERE/AND] ( (date OR altdate) LIKE '%$date%' )

What you have to say is --

[WHERE/AND] ( date LIKE '%$date%' 
           OR altdate LIKE '%$date%' )

Even so, that's still not quite good enough. You said "only one of these fields will ever have a value." Therefore, you have to guard against NULLs, like this --

[WHERE/AND] ( ( altdate IS NULL AND date LIKE '%$date%' )
           OR ( date IS NULL AND altdate LIKE '%$date%' ) )

Those inner parentheses can be dropped, because ANDs do have priority over ORs, but the outer ones cannot.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.