1

While debugging some code, I stumbled into an error pretty much like the following:

SELECT *
FROM person
WHERE family_size > 3
ORDER BY birthday AND registered=0;

The error is that the AND clause was intended to be in the WHERE clause. Ie:

WHERE family_size > 3 AND registered=0

The "flawed" query throws no SQL syntax error or warning. So I assume it's valid SQL. (right?)

But more interestingly, what is it supposed to do? I tested this is a couple of RDBMS and my best bet is that it's doing something like:

ORDER BY (birthday AND registered=0);

Am I right?

Is this intended SQL behavior? (such as expected resolution of operator precedence) Or is it some sort of fall-back?


Some details I should add after reading comments:

  • The original query was for use in MySQL, but I'm mostly interested in the intended use of the SQL language.
  • I did a quick test in MSAccess and behaved pretty much the same.
  • The columns were originally integers, but for the MSAccess test I did, I used Date/Time for 'birthday', just because.
  • Are you running MySQL? – ypercubeᵀᴹ Sep 23 '14 at 15:40
  • And what are the datatypes of the 2 columns? – ypercubeᵀᴹ Sep 23 '14 at 15:44
2

It's valid SQL as long as both sub-expressions, (birthday) and (registered=0) are boolean expressions (or can be implicitly converted to). And while registered = 0 is boolean, the birthday is probably not.

In MySQL though, integer expressions can be converted to boolean and vice versa, according to the rules: FALSE is 0, TRUE is 1 (and anything other than 0 is TRUE for the integer->boolean conversion).

So, that's what happens, an implicit conversion. For any birthday value other than 0, the expression is evaluated as:

(TRUE AND registered=0) 

which is equivalent to just (registered=0).

In Access, if I remember right, -1 stands for TRUE (instead of 1), but anything other than 0 is also converted to true, so the observed behaviour would be the same.

The final use of the expression in the ORDER BY depends on how the DBMS sorts boolean values. I suppose that both (MySQL and Access) use FALSE < TRUE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.