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

[22 May 2012 5:22] Shlomi Noach
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.
[22 May 2012 5:53] Valeriy Kravchuk
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.
[22 May 2012 6:57] Shlomi Noach
Thank you :)
Have edited title to reflect your diagnosis
[19 Jun 2012 20:42] Valeriy Kravchuk
Bug #65675 was marked as a duplicate of this one.
[4 Dec 2012 18:34] Paul DuBois
Noted in 5.7.0 changelog.

For a view defined on a UNION, the server could create an invalid
view definition.
[24 Jan 2013 16:14] Sveta Smirnova
Bug #68157 was marked as duplicate of this one.
[15 May 2014 9:17] Arnaud Adant
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.