MySQL Bugs: #63234: Table alias for dummy DUAL table returns error

Bug #63234	Table alias for dummy DUAL table returns error
Submitted:	13 Nov 2011 16:44	Modified:	13 Nov 2011 19:38
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.1.59	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
similar to http://bugs.mysql.com/bug.php?id=63233

How to repeat:
SELECT 1 AS no1, 2 AS no2 FROM DUAL; -- success
SELECT 1 AS no1, 2 AS no2 FROM DUAL AS dummy; -- syntax error

Suggested fix:
Why should it not work?

if not then at least http://dev.mysql.com/doc/refman/5.1/en/select.html sshould be updated to tell that DUAL table cannot be aliased.

Again I had to fix a typo in synopsis.  Sigh :-(

This is easy to explain:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.60 Source distribution

Copyright (c) 2000, 2011, 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 1 AS no1, 2 AS no2 FROM DUAL; 
+-----+-----+
| no1 | no2 |
+-----+-----+
|   1 |   2 |
+-----+-----+
1 row in set (0.00 sec)

mysql> SELECT 1 AS no1, 2 AS no2 FROM DUAL as d; 
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 'as d' at line 1

Let's check execution plan:

mysql> explain extended SELECT 1 AS no1, 2 AS no2 FROM DUAL; 
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| 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)

and real query MySQL executes:

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

Surely adding extra "as d" to the above leads to error.

So this looks like a feature request for the real, Oracle-like DUAL.

OK .. got the point.  The optimixed query does not have any table reference so adding an alias caused invalid reference

Whether this is a (non-serious) bug or a feature request is not important to me. However
http://dev.mysql.com/doc/refman/5.1/en/select.html
..  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."

.. so I think the discussion whether MySQL should fully accept DUAL like those RDBMS that require and/or support is as far as I can understand not something that is subject for discussion now. That discussion was concluded many years ago with the conclusion that it should (But I have to admit that I have no clue if Oracel, SQL Server etc. will accept an alias for the dummy DUAL table).

Could I suggest that the query 
SELECT 1 AS no1, 2 AS no2 FROM DUAL AS dummy;
..gets optimized to
SELECT 1 AS `no1`,2 AS `no2`; -- simply!

So this report should maybe be reclassified from 'parser' to 'optimizer' category?

".. so adding an alias caused invalid *syntax*" .. I meant

OK, let's say we have a valid optimizer feature request here.