MySQL Bugs: #74251: Syntax error when using DUAL dummy table in UNION subselect with ORDER BY

Bug #74251	Syntax error when using DUAL dummy table in UNION subselect with ORDER BY
Submitted:	7 Oct 2014 14:54	Modified:	23 Jul 2024 14:08
Reporter:	Lukas Eder	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1/5.5/5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When using the DUAL dummy table in UNION subqueries (for interoperability reasons), a syntax error is reported

How to repeat:
This query works:

    select 'A' as `x`
    union 
    select 'B'
    union 
    select 'C'
    order by `x` asc;

So does this one:

    select 'A' as `x` from dual
    union 
    select 'B'        from dual
    union 
    select 'C'
    order by `x` asc;

But this one doesn't:

    select 'A' as `x` from dual
    union 
    select 'B'        from dual
    union 
    select 'C'        from dual
    order by `x` asc;

A workaround would be to wrap the last subselect in parentheses:

    select 'A' as `x` from dual
    union 
    select 'B'        from dual
    union (
      select 'C'        from dual
    )
    order by `x` asc;

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;

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 >

Sorry I meant already fixed on 5.7 source.

Good news, thanks for the feedback!

Posted by developer:
 
Fixed with parser refactoring in version 8.0

Documented fix as follows in the MySQL 8.0.0 changelog, together with the fix for BUG#50510:

    This also fixes an issue with UNION subselects using FROM DUAL;
    for example, the following query raised an error:

  SELECT 'A' AS `x` FROM DUAL
    UNION
  SELECT 'B' FROM DUAL
    UNION
  SELECT 'C' FROM DUAL
    ORDER BY `x` ASC;

Closed.