MySQL Bugs: #63233: FROM DUAL raises error with alias and ORDER BY

Bug #63233	FROM DUAL raises error with alias and ORDER BY
Submitted:	13 Nov 2011 16:39	Modified:	14 Jul 2015 10:44
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.1.59 - probably any	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Not an important issue of course! 
Just some parsing imperfection.

:-)

How to repeat:
SELECT 1 AS no1, 2 AS no2; -- success
SELECT 1 AS no1, 2 AS no2 ORDER BY no2; -- success

SELECT 1 AS no1, 2 AS no2 FROM DUAL; -- success
SELECT 1 AS no1, 2 AS no2 FROM DUAL ORDER BY no2; -- syntax error

Suggested fix:
If ORDER BY on such alias as `no2` here does not raise an error without any table specification in the query, I don't think it should with dummy table specification of "FROM DUAL" either

http://dev.mysql.com/doc/refman/5.1/en/select.html
.. just tells 
"DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced."
.. and such restriction wwith ORDER BY is not listed.

Why does it read "*may* ignore the clause" (and not *will* ignore) ? Here if does not ignore obviously - but it should IMHO as I think this is a server/parser bug I think and not a documentation bug.

Fixed typo in synopsis.

Thank you for the problem report. Here I'd say we do have a bug, as this works:

mysql> explain extended SELECT 1 AS no1, 2 AS no2 order by no2; 
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select 1 AS `no1`,2 AS `no2` order by 2
1 row in set (0.00 sec)

a similar discussion here: http://bugs.mysql.com/bug.php?id=63234

Actually I can use any non-empty table as the 'dummy' table.

SELECT * FROM t1;
/*
  id  
------
     3
     4
*/

SELECT 1 AS no1, 2 AS no2 FROM t1 ORDER BY no2 LIMIT 1;
/* returns

   no1   no2  
------  ------
     1       2
*/

I may even "CREATE TABLE `dual` .. ;", insert one or more rows into it and use this one

SELECT 1 AS no1, 2 AS no2 FROM `dual` ORDER BY no2 LIMIT 1;

Posted by developer:
 
This was fixed in 5.7.4 by the fix for bug#17703542:

mysql> select version();
No connection. Trying to reconnect...
Connection id:    1
Current database: dbt3

+-----------+
| version() |
+-----------+
| 5.7.4-m14 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT 1 AS no1, 2 AS no2 FROM DUAL ORDER BY no2;
+-----+-----+
| no1 | no2 |
+-----+-----+
|   1 |   2 |
+-----+-----+
1 row in set (0.00 sec)