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

[30 Dec 2009 9:38] Alon Lubin
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.
[30 Dec 2009 10:20] Sveta Smirnova
Thank you for the report.

Which script fail after rerun? Please explain.
[30 Dec 2009 11:14] Alon Lubin
The command that "show create table v2" generates will fail on the empty alias
[30 Dec 2009 13:23] Valeriy Kravchuk
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 ''
[4 Apr 2013 6:02] Nisha Padmini Gopalakrishnan
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".