Description:
SHOW CREATE VIEW and select information_schema.views return a view definition that is invalid for (re-)creating the view, when there are column names that have been created by default and the defaulting yields invalid column names.
If a view that is a union of selects, CREATE VIEW does not use the column names on the second and subsequent selects. When MySQL reconstructs the view definition for SHOW CREATE it inserts column names, and these columns name can be invalid. As a result, the returned view definition cannot be used to recreate the view.
mysql> drop database if exists aatest;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create database aatest;
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> create or replace VIEW aatest.aatest AS
-> select 'a description' AS `description`
-> union all
-> select
-> 'a longer description that is too long to be a column name but it does not get used so it should not matter';
mysql>
mysql>
mysql> show create table aatest.aatest;
+--------+------------------------------------------------------------------
| View | Create View | character_set_client | collation_connection |
+--------+------------------------------------------------------------------
| aatest | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `aatest`.`aatest` AS select 'a description' AS `description` union all select 'a longer description that is too long to be a column name but it does not get used so it should not matter' AS `a longer description that is too long to be a column name but it does not get used so it should not matter` | latin1 | latin1_swedish_ci |
+--------+------------------------------------------------------------------
1 row in set (0.00 sec)
mysql>
mysql> /* re create the view */
mysql> create or replace VIEW aatest.aatest AS select 'a description' AS `description` union all select 'a longer description that is too long to be a column name but it does not get used so it should not matter' AS `a longer description that is too long to be a column name but it does not get used so it should not matter`;
ERROR 1166 (42000): Incorrect column name 'a longer description that is too long to be a column name but it does not get used so it should not '
mysql>
mysql> select * from information_schema.views where table_schema='aatest' and table_name='aatest';
+---------------+--------------+------------+-------------------------------
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION | CHECK_OPTION | IS_UPDATABLE | DEFINER | SECURITY_TYPE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
+---------------+--------------+------------+-------------------------------
| NULL | aatest | aatest | select 'a description' AS `description` union all select 'a longer description that is too long to be a column name but it does not get used so it should not matter' AS `a longer description that is too long to be a column name but it does not get used so it should not matter` | NONE | NO | root@localhost | DEFINER | latin1 | latin1_swedish_ci |
+---------------+--------------+------------+-------------------------------
1 row in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73 |
+-----------+
1 row in set (0.00 sec)
How to repeat:
drop database if exists aatest;
create database aatest;
create or replace VIEW aatest.aatest AS
select 'a description' AS `description`
union all
select
'a longer description that is too long to be a column name but it does not get used so it should not matter';
show create table aatest.aatest;
select * from information_schema.views where table_schema='aatest' and table_name='aatest';
/* re create the view using the definition that was returned */
create or replace VIEW aatest.aatest AS select 'a description' AS `description` union all select 'a longer description that is too long to be a column name but it does not get used so it should not matter' AS `a longer description that is too long to be a column name but it does not get used so it should not matter`;
Suggested fix:
It is highly desirable that show create view yields view definitions that always validity recreate the original view.
Either
make returned value of view definition omit 'AS `column name` for columns names that were omitted in the original create view
or
make it apply the rules to convert into a valid column name.
Note that SELECT tolerates column names that are too long to be variable
mysql> select 'a longer description that is too long to be a column name but it does not get used so it should not matter';
+------------------------------------------------------------------------------------------------------------+
| a longer description that is too long to be a column name but it does not get used so it should not matter |
+------------------------------------------------------------------------------------------------------------+
| a longer description that is too long to be a column name but it does not get used so it should not matter |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
BUT create table rejects them
mysql> create table aatest.table(`a longer description that is too long to be a column name but it does not get used so it should not matter` int );
ERROR 1059 (42000): Identifier name 'a longer description that is too long to be a column name but it does not get used so it should not ' is too long
mysql>