Bug #43916 SHOW CREATE VIEW returns invalid CREATE VIEW statement
Submitted: 27 Mar 2009 17:05 Modified: 27 Mar 2009 18:24
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.67-community-log OS:Windows (w2K also Linux)
Assigned to: CPU Architecture:Any
Tags: qc

[27 Mar 2009 17:05] Dave Pullin
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).
[27 Mar 2009 18:24] Valeriy Kravchuk
This is a duplicate of bug #40277.