MySQL Bugs: #58970: Problem Subquery (without referencing a table) and Order By

Bug #58970	Problem Subquery (without referencing a table) and Order By
Submitted:	16 Dec 2010 11:11	Modified:	11 Jan 2011 16:55
Reporter:	William Chiquito	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.5.8	OS:	Any
Assigned to:	Magne Mæhre	CPU Architecture:	Any
Tags:	order by, regression, subquery

Description:
When trying to run a subquery without referencing a table with an order by failure.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, 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> select (select 0 as a union select 1 as a order by a desc limit 1) as dev /* don't work */;
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 a desc limit 1) as dev' at line 1

mysql> select (select 0 as a union select 1 as a /*order by a desc*/ limit 1) as dev /* work */;
+-----+
| dev |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

An option to work is to make the following:

mysql> select (select 0 as a from (select null) t union select 1 as a from (select null) t order by a desc limit 1) as dev /* work */;

with "dual" the problem persists:

mysql> select (select 0 as a from dual union select 1 as a from dual order by a desc limit 1) as dev /* don't work */;

Works in 5.1.53:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.1.53-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select (select 0 as a union select 1 as a order by a desc limit 1) as dev
;
+-----+
| dev |
+-----+
|   1 |
+-----+
1 row in set (0.09 sec)

So looks like a regression bug.

Is correct, it works correctly in earlier versions. I tested on 5.0.91 and 5.1.54

Verified on Windows XP:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.8 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select (select 0 as a union select 1 as a order by a desc limit 1) as dev
;
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 a desc limit 1) as dev' at line 1

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/127598

3473 Magne Mahre	2010-12-27
      Bug#58970 Problem Subquery (without referencing a table) 
                and Order By
      
      When having a UNION statement in a subquery, with no
      referenced tables (or only a reference to the virtual
      table 'dual'), the UNION did not allow an ORDER BY clause.
      
      i.e:
          SELECT(SELECT 1 AS a UNION 
                 SELECT 0 AS a 
                 ORDER BY a) AS b  or
          SELECT(SELECT 1 AS a FROM dual UNION 
                 SELECT 0 as a 
                 ORDER BY a) AS b
      
      
      In addition, an ORDER BY / LIMIT clause was not accepted
      in subqueries even for single SELECT statements with no 
      referenced tables (or with 'dual' as table reference)
      
      i.e: 
          SELECT(SELECT 1 AS a ORDER BY a) AS b  or
          SELECT(SELECT 1 AS a FROM dual ORDER BY a) AS b
      
      The fix was to allow an optional ORDER BY/LIMIT clause to
      the grammar for these cases.
      
      See also: Bug#57986

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128283

3227 Magne Mahre	2011-01-10
      Bug#58970 Problem Subquery (without referencing a table) 
                and Order By
            
      When having a UNION statement in a subquery, with no
      referenced tables (or only a reference to the virtual
      table 'dual'), the UNION did not allow an ORDER BY clause.
            
            i.e:
                SELECT(SELECT 1 AS a UNION 
                       SELECT 0 AS a 
                       ORDER BY a) AS b  or
                SELECT(SELECT 1 AS a FROM dual UNION 
                       SELECT 0 as a 
                       ORDER BY a) AS b
            
            
      In addition, an ORDER BY / LIMIT clause was not accepted
      in subqueries even for single SELECT statements with no 
      referenced tables (or with 'dual' as table reference)
            
         i.e: 
            SELECT(SELECT 1 AS a ORDER BY a) AS b  or
            SELECT(SELECT 1 AS a FROM dual ORDER BY a) AS b
            
      The fix was to allow an optional ORDER BY/LIMIT clause to
      the grammar for these cases.
            
      See also: Bug#57986

Pushed into mysql-trunk 5.6.2 (revid:magne.mahre@oracle.com-20110110124553-cehvirmcokxx3hsb) (version source revid:magne.mahre@oracle.com-20110110124553-cehvirmcokxx3hsb) (merge vers: 5.6.2) (pib:24)

Pushed into mysql-5.5 5.5.9 (revid:magne.mahre@oracle.com-20110110124312-awlhreokzgvyt6ow) (version source revid:magne.mahre@oracle.com-20110110124312-awlhreokzgvyt6ow) (merge vers: 5.5.9) (pib:24)

Noted in 5.5.9, 5.6.2 changelogs.

In a subquery, a UNION with no referenced tables (or only a reference
to the virtual table dual) did not allow an ORDER BY clause.