Bug #81717 show create view has bad column names
Submitted: 3 Jun 2016 16:44 Modified: 9 Jun 2016 9:14
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1.73 OS:CentOS
Assigned to: CPU Architecture:Any

[3 Jun 2016 16:44] Dave Pullin
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>
[9 Jun 2016 9:14] MySQL Verification Team
Hello Dave,

Thank you for the report and test case.
I'm not able to locate the bug which fixed this but this seems to be fixed in 5.5 and above.

-- 5.6.31/5.5.50

mysql> drop database if exists aatest;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create database aatest;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> create or replace VIEW aatest.aatest AS
select
    -> 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';
Query OK, 0 rows affected (0.00 sec)

mysql> show create table aatest.aatest\G
*************************** 1. row ***************************
                View: aatest
         Create View: 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 `Name_exp_2`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql> select * from information_schema.views  where table_schema='aatest' and table_name='aatest'\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: aatest
          TABLE_NAME: aatest
     VIEW_DEFINITION: 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 `Name_exp_2`
        CHECK_OPTION: NONE
        IS_UPDATABLE: NO
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.00 sec)

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 `Name_exp_2`;
Query OK, 0 rows affected (0.01 sec)

Thanks,
Umesh