MySQL Bugs: #31474: Parameter in order by clause doesn't order

Bug #31474	Parameter in order by clause doesn't order
Submitted:	9 Oct 2007 11:17	Modified:	23 Jan 2008 10:20
Reporter:	Martin Steel
Status:	Not a Bug
Category:	Server: PS	Severity:	S3 (Non-critical)
Version:	5.0.46	OS:	Linux (Centos 4.5)
Assigned to:		Target Version:

Description:
Using a parameter in the order by clause of a prepared statement no longer orders the
query results when upgrading from MySql Server 5.0.27 to 5.0.46.

This is reproduceable using two releases of DBD::mysql from Perl and using two releases
of MySql Connector.NET under Windows.

How to repeat:
My table is defined as:

CREATE TABLE `tblinbox` (
`inbox_msg_id` varchar(50) NOT NULL default '',
`username` varchar(100) default NULL,
`phone_number` varchar(20) default NULL,
`rec_time` datetime default NULL,
PRIMARY KEY  (`inbox_msg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The query being ran is:

SELECT inbox_msg_id, phone_number, rec_time FROM tblinbox WHERE username = ? ORDER BY ?
DESC LIMIT 10;

The parameters are set to 'username' and rec_time.

Tested using:
Centos 4.5 (Redhat Enterprise Linux 4.5 based)
DBI Version: 1.40
Perl version: 5.8.5
DBD::mysql Version: 3.0004 and 3.0002

Running this against MySql Server versions 5.0.22 and 5.0.27 return the rows in
descending order based upon the order by parameter.

Running against MySql Server 5.0.46 the records are returned in the same order as without
an order by clause.

I've also tested with with the following setup:
Windows 2003 Server Standard Edition Service Pack 2
MySql Connector.NET 5.0.3 and 5.0.8
Microsoft .NET Framework 2.0.50727.832

This produces the same result when using named parameters and a MySqlDataReader.
 
i.e. "SELECT inbox_msg_id, phone_number, rec_time FROM tblinbox WHERE username = ?user
ORDER BY ?order DESC LIMIT 10"

Please do not submit the same bug more than once. An existing bug report, Bug #31001,
already describes this very problem. Even if you feel that your issue is somewhat
different, the resolution is likely to be the same. Because of this, we hope you add your
comments to the original bug instead.

Thank you for your interest in MySQL.

This bug is still present in MySQL Enterprise 5.0.54 running on Centos 4.6.  It looks like
it is a separate problem to that flagged as a duplicate.

Database, queries and Perl release are as above on original bug report, OS has been
updated to Centos 4.6 to allow updated MySQL RPM packages to be installed.

Running the query with a parameter for the ORDER BY clause e.g. "ORDER BY ? DESC LIMIT
10" still returns the rows out of order.  This is different to the behaviour of MySQL
Server 5.0.27 which ordered the data based on the parameter.

Please, provide a complete test case then. With data for your table. I am absolutely sure
that bug #33001 is fixed in 5.0.54 (MySQL binaries).

Table Schema and Data

Attachment: bug_31474_data.sql (application/octet-stream, text), 2.08 KiB.

Perl Script to show bug

Attachment: bug_31474_demo.pl (application/octet-stream, text), 1.19 KiB.

I've attached an SQL file containing the table schema and some sample data and a perl
script that can be used to demonstrate the problem.

On MySQL 5.0.27 the first set of results is in descending order by the rec_time column as
expected, on MySQL 5.0.54 the data is ordered by the primary key.

Thank you for taking the time to write to us, but this is not a bug. Please double-check
the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read manual at http://dev.mysql.com/doc/refman/5.0/en/sqlps.html:

"Parameter markers can be used only where data values should appear, not for SQL
keywords, identifiers, and so forth."

Can I suggest the change log is ammended to reflect this as the behaviour changed between
MySQL Server 5.0.27 and MySQL 5.0.46.  

I am not sure exactly which version changed this as I have not tested every intermediate
release but can confirm ordering by a parameter orders the results in MySQL 5.0.27 as in
the previously attached test case and does not in 5.0.46.