AND operator in ORDER BY clause

Question

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.

Answer 1

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