CODEWITHSUNDEEP
mysqlsql-order-bymysql-error-1054
David
David

Reputation: 13

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?

Upvotes: 1

Views: 559

Answers (3)

Mauro Morales
Mauro Morales

Reputation: 1687

The order by expects the field you want to order and if you want it ascending you put asc next to it, and if you want it descending you put a desc like as follows

SELECT *
  FROM mysq.user
 ORDER BY username ASC

Upvotes: -1

Vincent Savard
Vincent Savard

Reputation: 35927

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.

Upvotes: 4

Adriaan Stander
Adriaan Stander

Reputation: 166396

When you are not using quotes, it would assume that you are referring to a column name, which does not exist.

This is correct functionallity

Upvotes: 3

Related Questions