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 9:17	Modified:	23 Jan 2008 9:20
Reporter:	Martin Steel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.0.46	OS:	Linux (Centos 4.5)
Assigned to:		CPU Architecture:	Any

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.