Bug #118627 while exexuting prepare statement including 'order by ?', the sort of result set is not right

• Submitted: 10 Jul 7:26
• Modified: 23 Jul 13:04
• Reporter: SamonBing SamonBing
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any
• Tags: prepare stetement

[10 Jul 7:26] SamonBing SamonBing

Description:
while exexuting prepare statement including 'order  by ?', the sort of result set is not right.

mysql> select a,b from t1 where a>1 order by 2 desc;
+------+------+
| a    | b    |
+------+------+
|    2 |    5 |
|    2 |    4 |
|    2 |    3 |
+------+------+
3 rows in set (0.01 sec)

mysql> set @sql_text1='select a,b from t1 where a>1 order by ? desc;';
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt1 FROM @sql_text1;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @val1=2;
Query OK, 0 rows affected (0.01 sec)

mysql> EXECUTE stmt1 using @val1;
+------+------+
| a    | b    |
+------+------+
|    2 |    3 |
|    2 |    4 |
|    2 |    5 |
+------+------+
3 rows in set (0.00 sec)

How to repeat:
CREATE TABLE `t1` (
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into t1 values(1,2);
insert into t1 values(1,3);
insert into t1 values(2,3);
insert into t1 values(2,4);
insert into t1 values(2,5);

select a,b from t1 where a>1 order by ? desc;

set @sql_text1='select a,b from t1 where a>1 order by ? desc;';
PREPARE stmt1 FROM @sql_text1;
set @val1=2;
EXECUTE stmt1 using @val1;

[23 Jul 13:04] MySQL Verification Team

Hello SamonBing,

Thank you for the bug report.
Imho this is not a bug. For more info, please see the doc https://dev.mysql.com/doc/refman/8.4/en/sql-prepared-statements.html

The manual lists SQL statements that can be used as prepared statements.

Regards,
Ashwini Patil