MySQL Bugs: #49989: Show create table for a view with empty column generates incorrect script

Bug #49989	Show create table for a view with empty column generates incorrect script
Submitted:	30 Dec 2009 9:38	Modified:	4 Apr 2013 6:02
Reporter:	Alon Lubin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.89, 5.1.41, 5.1.43	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	create, script, SHOW CREATE TABLE, VIEW

Description:
When creating a view with an empty column like '' without alias, the script generated by show create table will add an empty alias which cause the script to fail upon rerun.

How to repeat:
Run the following:

create view v2 as select '';

show create table v2;

The result is:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v2` AS select '' AS ``;

The script fails on the empty alias.

Thank you for the report.

Which script fail after rerun? Please explain.

The command that "show create table v2" generates will fail on the empty alias

Verified just as described with recent 5.1.43 from bzr:

77-52-7-73: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.43-debug Source distribution

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

mysql> create view v2 as select '';
Query OK, 0 rows affected (0.12 sec)

mysql> 
mysql> show create table v2;
+------+---------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                             | character_set_client | collation_connection |
+------+---------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v2   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select '' AS `` | latin1               | latin1_swedish_ci    |
+------+---------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> drop view v2;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select '' AS ``
    -> ;
ERROR 1166 (42000): Incorrect column name ''

Duplicate of bug40277. A blank as an alias is invalid. As part of the fix for bug40277, invalid alias name is rewritten to "Name_exp_%u".