Search code examples
mysqlsql-order-bymysql-error-1054

MySQL Order-By Bug

If I run the following query:

select * from mysql.user order by abcdef;

MySQL throws the following error:

ERROR 1054 (42S22): Unknown column 'abcdef' in 'order clause'

If I run the following similar query:

select * from mysql.user order by "abcdef";

MySQL now runs the query and disregards the order by clause (since the mysql.user table lacks a column called 'abcdef').

Is this a bug in MySQL? Why would you want the order by to fail silently when the phrase is in quotes? Wouldn't an error message be appropriate when running order by on a non-existent column?

Solution

  • It doesn't disregard it, it orders by the string "abcdef", not the column. It does exactly what you ask : ordering by some arbitrary string (which most likely does nothing at all).

    Most RDBMS doesn't accept to order by a constant (it doesn't make sense), but MySQL does.