Bug #74251 | Syntax error when using DUAL dummy table in UNION subselect with ORDER BY | ||
---|---|---|---|
Submitted: | 7 Oct 2014 14:54 | Modified: | 7 Oct 2014 15:55 |
Reporter: | Lukas Eder | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.1/5.5/5.6 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[7 Oct 2014 14:54]
Lukas Eder
[7 Oct 2014 14:55]
Lukas Eder
Another workaround: select 'A' as `x` from dual union select 'B' from dual union select 'C' from (select 1 from dual) x order by 1 asc;
[7 Oct 2014 15:40]
MySQL Verification Team
Thank you for the bug report. Already fixed on 5.6 source: C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 --prompt="mysql 5.7 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.6-m16 Source distribution Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. 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 5.7 > select 'A' as `x` from dual -> union -> select 'B' from dual -> union -> select 'C' from dual -> order by `x` asc; +---+ | x | +---+ | A | | B | | C | +---+ 3 rows in set (0.00 sec) mysql 5.7 > EXIT Bye C:\dbs>56 C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.6.22-debug Source distribution Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. 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 5.6 > select 'A' as `x` from dual -> union -> select 'B' from dual -> union -> select 'C' from dual -> order by `x` asc; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by `x` asc' at line 6 mysql 5.6 >
[7 Oct 2014 15:41]
MySQL Verification Team
Sorry I meant already fixed on 5.7 source.
[7 Oct 2014 15:55]
Lukas Eder
Good news, thanks for the feedback!