| Bug #108432 | Using the prepare statement causes an "unknown column in 'order clause'" error | ||
|---|---|---|---|
| Submitted: | 8 Sep 2022 15:06 | Modified: | 8 Sep 2022 17:45 |
| Reporter: | s yc | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Prepared statements | Severity: | S3 (Non-critical) |
| Version: | 5.7.38/5.7.39 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Sep 2022 17:45]
MySQL Verification Team
Thank you for the bug report. 8.0.30 not affected for this bug:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)
mysql> USE test
Database changed
mysql> prepare s1 from '
'> select * from (
'> select 2*a as b
'> from (select 1 as a) as t1
'> inner join (select 3 as c) as t2
'> on 1=1
'> ) as t3
'> order by t3.b
'> ';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql>
mysql> execute s1;
ERROR 1054 (42S22): Unknown column 't3.b' in 'order clause'
mysql>
================================================================
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.30 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> USE test
Database changed
mysql> prepare s1 from '
'> select * from (
'> select 2*a as b
'> from (select 1 as a) as t1
'> inner join (select 3 as c) as t2
'> on 1=1
'> ) as t3
'> order by t3.b
'> ';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql>
mysql> execute s1;
+---+
| b |
+---+
| 2 |
+---+
1 row in set (0.00 sec)
mysql>
Description: Using the prepare statement causes an "unknown column in 'order clause'" error, but there is no error without using the prepare statement. How to repeat: ```sql prepare s1 from ' select * from ( select 2*a as b from (select 1 as a) as t1 inner join (select 3 as c) as t2 on 1=1 ) as t3 order by t3.b '; execute s1; -- ERROR 1054 (42S22): Unknown column 't3.b' in 'order clause' ``` Suggested fix: Return results without errors.