Description:
I assume that the CREATE VIEW statement returned by SHOW CREATE is intended to be a valid statement that can be used to recreate the view. It is not when the view is a union that includes an unnamed column in the non-first union, if the defaulted column name isn't a valid column name.
Originally the view was created:
------------------------------------------------------
CREATE or Replace VIEW test.viewbug AS
select 'this' AS `col`
union all
select 'long string with no explicit column name because it does not need one except for this bug'
------------------------------------------------------
And the SHOW CREATE returns a statement with the unnamed column rendered explicit:
CREATE or replace ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`viewbug` AS select _latin1'this' AS `col` union all select _latin1'long string with no explicit column name because it does not need one except for this bug' AS `long string with no explicit column name because it does not need one except for this bug`
--------------------------------------------------------------------------
Executing this fails because the defaulted column name is too long.
(I realize this is a fairly obscure bug that I can work around by explicitly naming all the columns in all my views, but it screwed up a bunch of my automatic processing, so I thought it worth reporting).
==============CONSOLE LOG =================
mysql> CREATE or Replace VIEW test.viewbug AS
-> select 'this' AS `col`
-> union all
-> select 'long string with no explicit column name because it does not need
one except for this bug';
Query OK, 0 rows affected (0.05 sec)
mysql> show create view test.viewbug;
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------+
| View | Create View
|
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------+
| viewbug | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY D
EFINER VIEW `test`.`viewbug` AS select _latin1'this' AS `col` union all select _
latin1'long string with no explicit column name because it does not need one exc
ept for this bug' AS `long string with no explicit column name because it does n
ot need one except for this bug` |
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------+
1 row in set (0.02 sec)
mysql> CREATE or replace ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECU
RITY DEFINER VIEW `test`.`viewbug` AS select _latin1'this' AS `col` union all se
lect _latin1'long string with no explicit column name because it does not need o
ne except for this bug' AS `long string with no explicit column name because it
does not need one except for this bug`;
ERROR 1166 (42000): Incorrect column name 'long string with no explicit column n
ame because it does not need one except for this bug'
mysql>
How to repeat:
CREATE or Replace VIEW test.viewbug AS
select 'this' AS `col`
union all
select 'long string with no explicit column name because it does not need one except for this bug'
then
show create view test.viewbug;
cut & paste the value in the CREATE VIEW column, and execute it.
Suggested fix:
perhaps you omit the "AS `col`" part of the show create if the generated column name is invalid. (Since the original view is valid, such omissions shouldn't have any side effects).