MySQL ORDER BY Clause – Using AND Operator

MySQL

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.

Best Answer

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

Related Solutions

MySQL – Normal Set of Privileges in phpMyAdmin

In reality, those three user accounts are actually quite dangerous. They pose a very great threat to test databases.

Unfortunately, mysql comes with full access to test databases. How can you find them ?

Run this query:

 SELECT user,host,db from mysql.db;

Upon installation of mysql, you will see two rows that give full access to any database named 'test' or whose first 5 characters are 'test_'.

Why is this a problem ???

Try running this command:

$ mysql -u'' -Dtest

You will have connected the test database without a password.

Now, create a table and load it with a row:

CREATE TABLE mytable (a int);
INSERT INTO mytable VALUES (1);

OK, big deal. Could you double this table in size 30 times ???

INSERT INTO mytable SELECT * FROM mytable;
INSERT INTO mytable SELECT * FROM mytable;
... (30 times)
INSERT INTO mytable SELECT * FROM mytable;

What do you get ?? A table with 1,073,741,824 rows. Easily, 4GB+.

Imagine creating any table of any size. How about creating a bunch of tables in the test database and freely accessing them at will ?

The best thing you can do under these circumstances is to run this query:

DELETE FROM mysql.db;

and restart mysql. Then, those three accounts will be properly rendered inoperative.

Give it a Try !!!

UPDATE 2011-09-12 10:00 EDT

This delete:

DELETE FROM mysql.db;
FLUSH PRIVILEGES;

is just what you need for an initial installation. However, if you have users already established, You can run this instead:

DELETE FROM mysql.db WHERE db IN ('test','test_%');
FLUSH PRIVILEGES;

This will remove the specific two DB permissions.

As I mentioned in my answer, the three permissions are very dangerous for test databases. Running this DELETE will neutralize those accounts for having full rights to test databases.

MySql: #1221 – Incorrect usage of UNION and ORDER BY

There are several ways to pass the IN / LIMIT limitation and the UNION / ORDER BY syntax requirements.

Wrap the Union in a derived table:

SELECT * FROM comment AS tbl WHERE sscm_id IN
  ( SELECT sscm_id
    FROM     
      ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 
          ORDER BY t1.sscm_id DESC LIMIT 3)
      UNION  ALL
        ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 
          ORDER BY t2.sscm_id DESC LIMIT 3)
      ) AS tmp 
  ) ;

Use JOIN instead of WHERE / IN:
UNION is required here instead of UNION ALL to avoid duplicates

SELECT tbl.* 
FROM comment AS tbl 
    JOIN 
        ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 
            ORDER BY t1.sscm_id DESC LIMIT 3)
        UNION 
          ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 
            ORDER BY t2.sscm_id DESC LIMIT 3)
        ) AS tmp 
      ON tmp.sscm_id = tbl.sscm_id ;

Best Answer

Related Solutions

MySQL – Normal Set of Privileges in phpMyAdmin

MySql: #1221 – Incorrect usage of UNION and ORDER BY

Related Question