Bug #21786 | ORDER BY not working in PREPARED Statement | ||
---|---|---|---|
Submitted: | 22 Aug 2006 14:57 | Modified: | 23 Aug 2006 17:23 |
Reporter: | Ruturaj Vartak | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 5.0.19 and 5.1.11-beta | OS: | Linux (Fedora 5) |
Assigned to: | CPU Architecture: | Any | |
Tags: | order by, prepared statement |
[22 Aug 2006 14:57]
Ruturaj Vartak
[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.