Bug #21786 ORDER BY not working in PREPARED Statement

• Submitted: 22 Aug 2006 14:57
• Modified: 23 Aug 2006 17:23
• Reporter: Ruturaj Vartak
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.19 and 5.1.11-beta
• OS: Linux (Fedora 5)
• CPU Architecture: Any
• Tags: order by, prepared statement

[22 Aug 2006 14:57] Ruturaj Vartak

Description:
If I take a complete string of order in a Stored Procedure as 
call proc1('revenue desc', 10);

if I have the first param as a user variable string in the prepared statement, the sorting does not work.

How to repeat:
1. Create a table with 2 columns, fielda integer and revenue decimal(33,18)
2. Create a procedure like below.
----------------------------------------------------------
delimiter //

create procedure proc1(sortstr varchar(100), limitlength int)
begin

set @usortstr := sortstr;
set @ulimitlength := limitlength;

@query := 'select fielda, revenue from table_name order by ? limit ?';
prepare stmt from @query;
execute stmt using @usortstr, @ulimitlength;

end //

delimiter ;
----------------------------------------------------------
3. Execute procedure using
call proc1('revenue desc', 10);

Suggested fix:
This is a WORKAROUND
-------------------------
Workaround is to concat the @query with the user variable like

@query := concat('select fielda, revenue from table_name order by ', @usortstr, ' limit ?');

and then use the @query

[22 Aug 2006 14:59] Ruturaj Vartak

Updating Severity to Serious.

[22 Aug 2006 15:31] MySQL Verification Team

Thank you for the bug report. Could you please provide a complete test
case table definition/insert data/wrong result you got.

Thanks in advance.

[23 Aug 2006 5:18] Ruturaj Vartak

Following are the details to recreate test case.

root@localhost test> desc t1;
+---------+----------------+------+-----+---------+-------+
| Field   | Type           | Null | Key | Default | Extra |
+---------+----------------+------+-----+---------+-------+
| fielda  | int(11)        | YES  |     | NULL    |       |
| revenue | decimal(33,18) | YES  |     | NULL    |       |
+---------+----------------+------+-----+---------+-------+

root@localhost test> select * from t1;
+--------+-----------------------+
| fielda | revenue               |
+--------+-----------------------+
|      1 | 33.430000000000000000 |
|      2 | 18.560000000000000000 |
|      6 | 56.890000000000000000 |
|      9 | 75.120000000000000000 |
|     65 |  9.450000000000000000 |
+--------+-----------------------+
5 rows in set (0.04 sec)

Stored proc creation script.
----------------------------
DELIMITER $$

CREATE PROCEDURE `test`.`proc1` (sortstr varchar(100), limitlength int)
BEGIN

set @usortstr := sortstr;
set @ulimitlength := limitlength;   
set @qry := 'select fielda, revenue from t1 order by ? limit ?';    
prepare stmt from @qry;     
execute stmt using @usortstr, @ulimitlength;

END$$

DELIMITER ;
---------------------------------

Results:
---------------------------------
root@localhost test> call proc1('revenue desc', 10);
+--------+-----------------------+
| fielda | revenue               |
+--------+-----------------------+
|      1 | 33.430000000000000000 |
|      2 | 18.560000000000000000 |
|      6 | 56.890000000000000000 |
|      9 | 75.120000000000000000 |
|     65 |  9.450000000000000000 |
+--------+-----------------------+
5 rows in set (0.08 sec)
---------------------------------------

Expected Results: 
---------------------------------------
root@localhost test> select * from t1 order by revenue desc limit 10;
+--------+-----------------------+
| fielda | revenue               |
+--------+-----------------------+
|      9 | 75.120000000000000000 |
|      6 | 56.890000000000000000 |
|      1 | 33.430000000000000000 |
|      2 | 18.560000000000000000 |
|     65 |  9.450000000000000000 |
+--------+-----------------------+
5 rows in set (0.07 sec)
---------------------------------------

[23 Aug 2006 17:23] MySQL Verification Team

Thank you for the bug report. From the Manual:

http://dev.mysql.com/doc/refman/5.0/en/sqlps.html

PREPARE stmt_name FROM preparable_stmt

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