Bug #29568 ORDER BY with a quoted column doesn't work
Submitted: 5 Jul 2007 9:14 Modified: 6 Jul 2007 8:59
Reporter: Engelbert Höfer Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.41/4.1/5.1 OS:Windows
Assigned to: CPU Architecture:Any
Tags: order by timestamp, regression

[5 Jul 2007 9:14] Engelbert Höfer 
Description:
After I made an update from Version 5.0.24a to 5.0.41 the ORDER BY clause with a timestamp column doesn't work any more.

How to repeat:
file 'testbug.txt' contains the following SQL-Statements:

USE test;
DROP TABLE IF EXISTS animals;
CREATE TABLE animals (id integer NOT NULL PRIMARY KEY AUTO_INCREMENT, birth timestamp(14), name text);
INSERT INTO animals VALUES (NULL, '2006-01-03 12:00:00', 'Charly');
INSERT INTO animals VALUES (NULL, '2007-05-03 12:00:00', 'Daisy');
INSERT INTO animals VALUES (NULL, '2007-05-03 15:00:00', 'Donald');

SELECT * FROM animals ORDER BY 'birth' DESC;
SELECT version();

The output version 5.0.24a on the console is:

C:\mysql\bin>mysql -u bert -p < testbug.txt
Enter password: ********
id      birth   name
3       2007-05-03 15:00:00     Donald
2       2007-05-03 12:00:00     Daisy
1       2006-01-03 12:00:00     Charly
version()
5.0.24a-community-nt

The output version 5.0.41 on the console is:

C:\xampp\mysql\bin>mysql -u admin -p < testbug.txt
Enter password: ***
id      birth   name
1       2006-01-03 12:00:00     Charly
2       2007-05-03 12:00:00     Daisy
3       2007-05-03 15:00:00     Donald
version()
5.0.41-community-nt
[5 Jul 2007 9:18] Engelbert Höfer 
SQL-Statements

Attachment: testbug.txt (text/plain), 419 bytes.
[5 Jul 2007 11:51] MySQL Verification Team 
Thank you for the bug report.

c:\dev\5.0>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.46-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE test;
Database changed
mysql> DROP TABLE IF EXISTS animals;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE animals (id integer NOT NULL PRIMARY KEY AUTO_INCREMENT, birth timestamp(14),
    -> name text);
Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> INSERT INTO animals VALUES (NULL, '2006-01-03 12:00:00', 'Charly');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO animals VALUES (NULL, '2007-05-03 12:00:00', 'Daisy');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO animals VALUES (NULL, '2007-05-03 15:00:00', 'Donald');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM animals ORDER BY 'birth' DESC;
+----+---------------------+--------+
| id | birth               | name   |
+----+---------------------+--------+
|  1 | 2006-01-03 12:00:00 | Charly |
|  2 | 2007-05-03 12:00:00 | Daisy  |
|  3 | 2007-05-03 15:00:00 | Donald |
+----+---------------------+--------+
3 rows in set (0.00 sec)

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.0.46-nt |
+-----------+
1 row in set (0.00 sec)

mysql> exit
Bye
*******************************************************************************
c:\mysql-5.0.27-win32>bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE test;
Database changed
mysql> DROP TABLE IF EXISTS animals;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE animals (id integer NOT NULL PRIMARY KEY AUTO_INCREMENT, birth timestamp(14),
    -> name text);
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> INSERT INTO animals VALUES (NULL, '2006-01-03 12:00:00', 'Charly');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO animals VALUES (NULL, '2007-05-03 12:00:00', 'Daisy');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO animals VALUES (NULL, '2007-05-03 15:00:00', 'Donald');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM animals ORDER BY 'birth' DESC;
+----+---------------------+--------+
| id | birth               | name   |
+----+---------------------+--------+
|  3 | 2007-05-03 15:00:00 | Donald |
|  2 | 2007-05-03 12:00:00 | Daisy  |
|  1 | 2006-01-03 12:00:00 | Charly |
+----+---------------------+--------+
3 rows in set (0.00 sec)

mysql> SELECT version();
+---------------------+
| version()           |
+---------------------+
| 5.0.27-community-nt |
+---------------------+
1 row in set (0.00 sec)

mysql>
***************************************************************************
[miguel@light 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.46-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DROP TABLE IF EXISTS animals;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE animals (id integer NOT NULL PRIMARY KEY AUTO_INCREMENT, birth timestamp(14),
    -> name text);
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> INSERT INTO animals VALUES (NULL, '2006-01-03 12:00:00', 'Charly');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO animals VALUES (NULL, '2007-05-03 12:00:00', 'Daisy');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO animals VALUES (NULL, '2007-05-03 15:00:00', 'Donald');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT * FROM animals ORDER BY 'birth' DESC;
+----+---------------------+--------+
| id | birth               | name   |
+----+---------------------+--------+
|  1 | 2006-01-03 12:00:00 | Charly | 
|  2 | 2007-05-03 12:00:00 | Daisy  | 
|  3 | 2007-05-03 15:00:00 | Donald | 
+----+---------------------+--------+
3 rows in set (0.00 sec)

mysql> SELECT version();
+--------------+
| version()    |
+--------------+
| 5.0.46-debug | 
+--------------+
1 row in set (0.00 sec)

mysql>
[5 Jul 2007 12:03] MySQL Verification Team 
[miguel@light 5.1]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.21-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

<cut>

mysql> SELECT * FROM animals ORDER BY 'birth' DESC;
+----+---------------------+--------+
| id | birth               | name   |
+----+---------------------+--------+
|  1 | 2006-01-03 12:00:00 | Charly | 
|  2 | 2007-05-03 12:00:00 | Daisy  | 
|  3 | 2007-05-03 15:00:00 | Donald | 
+----+---------------------+--------+
3 rows in set (0.00 sec)

mysql> SELECT version();
+-------------------+
| version()         |
+-------------------+
| 5.1.21-beta-debug | 
+-------------------+
1 row in set (0.00 sec)

[miguel@light 4.1]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DROP TABLE IF EXISTS animals;
Query OK, 0 rows affected (0.04 sec)

<cut>

mysql> SELECT * FROM animals ORDER BY 'birth' DESC;
+----+---------------------+--------+
| id | birth               | name   |
+----+---------------------+--------+
|  1 | 2006-01-03 12:00:00 | Charly |
|  2 | 2007-05-03 12:00:00 | Daisy  |
|  3 | 2007-05-03 15:00:00 | Donald |
+----+---------------------+--------+
3 rows in set (0.00 sec)

mysql> SELECT version();
+--------------+
| version()    |
+--------------+
| 4.1.23-debug |
+--------------+
1 row in set (0.00 sec)
[5 Jul 2007 12:52] Martin Friebe 
this seems to be Bug #14019.

single quotes can not be used for identifiers. So the sql provided does order by a constant value, rather than the column.
[5 Jul 2007 13:09] MySQL Verification Team 
Hi Martin,

Thank you for your comment, yes you are rigth:

c:\dev\5.0>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.46-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM animals ORDER BY 'birth' DESC;
+----+---------------------+--------+
| id | birth               | name   |
+----+---------------------+--------+
|  1 | 2006-01-03 12:00:00 | Charly |
|  2 | 2007-05-03 12:00:00 | Daisy  |
|  3 | 2007-05-03 15:00:00 | Donald |
+----+---------------------+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM animals ORDER BY birth DESC;
+----+---------------------+--------+
| id | birth               | name   |
+----+---------------------+--------+
|  3 | 2007-05-03 15:00:00 | Donald |
|  2 | 2007-05-03 12:00:00 | Daisy  |
|  1 | 2006-01-03 12:00:00 | Charly |
+----+---------------------+--------+
3 rows in set (0.00 sec)

mysql>
[5 Jul 2007 22:45] MySQL Verification Team 
Changing the Synopsis to reflect the real bug.
[5 Jul 2007 22:48] MySQL Verification Team 
Bug: http://bugs.mysql.com/bug.php?id=29586 was marked as duplicate of
this one.
[6 Jul 2007 8:59] Sergei Golubchik 
No bug here.
'birth' is a string literal, not a column.
http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html
[6 Jul 2007 9:47] Jan Bolmeson 
The problem that occurs if this bug is discarded is an enourmous BC-break since e.g. in PHP the first rule of programming (don't trust user input) is broken. 

Most programmers including me quotes fieldnames in order to avoid sql-injection. 

I don't really see the reason why to break backwards compability in this case.

Best regards,
//jan
[6 Jul 2007 10:06] Sergei Golubchik 
Of course, you can quote field names, but you need to quote them as identifiers, not as string literals. Compare

SELECT 'birth' FROM animals;
SELECT `birth` FROM animals;