MySQL Bugs: #65388: MySQL server creates invalid VIEW definition

Bug #65388	MySQL server creates invalid VIEW definition
Submitted:	22 May 2012 5:22	Modified:	4 Dec 2012 18:34
Reporter:	Shlomi Noach (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	>= 5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump, UNION ALL, VIEW

Description:
A valid view is dumped. The SQL from the dump makes for an invalid view definition.
Hence, restore from backup fails; unable to create replicating servers...

How to repeat:
mysql>

create database bugs;
use bugs;
CREATE VIEW bad_dump AS
  (SELECT 'hello' AS some_column FROM DUAL) UNION ALL (SELECT '' FROM DUAL)
;

SELECT * FROM bad_dump;
+-------------+
| some_column |
+-------------+
| hello       |
|             |
+-------------+
2 rows in set (0.00 sec)

---------

bash$
mysqldump bugs

(outputs:)
...
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `bad_dump` AS (select 'hello' AS `some_column`) union all (select '' AS ``) */;
...

---------
try to import:
mysqldump bugs | mysql other_bugs

ERROR 1166 (42000) at line 43: Incorrect column name ''

Suggested fix:
Since column aliases are meaningless in secondary SELECTs within a UNION [ALL] statement, just throw in *some* alias which is non-empty.

Actually bad view definition is created by server:

macbook-pro:5.1 openxs$ bin/mysql -uroot b
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.1.64-debug 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> CREATE VIEW bad_dump AS
    ->   (SELECT 'hello' AS some_column FROM DUAL) UNION ALL (SELECT '' FROM DUAL)
    -> ;
Query OK, 0 rows affected (0.07 sec)

mysql> show create view bad_dump\G
*************************** 1. row ***************************
                View: bad_dump
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bad_dump` AS (select 'hello' AS `some_column`) union all (select '' AS ``)
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.04 sec)

and this leads to mysqldump creating a dump that can not be loaded successfully.

Thank you :)
Have edited title to reflect your diagnosis

Bug #65675 was marked as a duplicate of this one.

Noted in 5.7.0 changelog.

For a view defined on a UNION, the server could create an invalid
view definition.

Bug #68157 was marked as duplicate of this one.

I can reproduce the problem in 5.6.17 :

create view v as
select 1 union all select floor(floor(floor(floor(floor(floor(floor(floor(floor(floor(floor(0)))))))))));

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

It works fine in 5.7.4 :

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select 1 AS `1` union all select floor(floor(floor(floor(floor(floor(floor(floor(floor(floor(floor(0))))))))))) AS `Name_exp_2`
   -> ;

The workaround is to alias these long columns / expressions before 5.7.