MySQL Bugs: #40277: SHOW CREATE VIEW returns invalid SQL

Bug #40277	SHOW CREATE VIEW returns invalid SQL
Submitted:	23 Oct 2008 9:32	Modified:	18 Jun 2010 1:21
Reporter:	Mike Lischke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.0.67, 5.1.28	OS:	Windows (Vista 64-bit)
Assigned to:	Davi Arnaut	CPU Architecture:	Any

Description:
SHOW CREATE VIEW returns invalid SQL if the definition contains a select with more plain text than what is allowed as column name, as this text is also used as alias (in the AS clause).

How to repeat:
1) Create a view like this:

DROP VIEW IF EXISTS `test`.`bug36670`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bug36670` AS
select ' M searches       Tags   log AAAA BBBBBBB CCCCCCCC DDDDDDDD EEEEEEEE FFFFFFF GGGGG';

2) View the view creation code:

mysql> show create view bug36670\G
*************************** 1. row ***************************
       View: bug36670
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bug36670` AS select _utf8'
 M searches       Tags   log AAAA BBBBBBB CCCCCCCC DDDDDDDD EEEEEEEE FFFFFFF GGGGG' AS `M searches       Tags   log AAAA
 BBBBBBB CCCCCCCC DDDDDDDD EEEEEEEE FFFFFFF GGGGG`
1 row in set (0.00 sec)

mysql>

As you can see SHOW CREATE VIEW added an AS clause using the plain text as (quoted) identifier without trimming it to the allowed length.

Suggested fix:
Either trim the returned column alias to the allowed length or

MUCH PREFERRED

don't return an AS clause if none was given in the original DDL. Generally speaking, the DDL code should be return exactly as given previously, including all formatting, comments and what not.

Increasing severity as this bugs prevents a successful migration and potentially every other client which uses SHOW CREATE VIEW (e.g. mysqldump etc.).

Thank you for a bug report. Verified just as decribed, also - with 5.1.28.

Bug #41834 was marked as a duplicate of this one.

Bug #26446 was marked as a duplicate of this one.

This seems closely related due to SHOW CREATE VIEW returning invalid SQL if the definition contains a trailing space as well and the resulting AS clause.

This produces no warnings on master, fails on slave, notice trailing space in 'val2 '

drop view if exists v1;
create view v1 as
select 'val1' ,'val2 ' ;

Last_Error: Error 'Incorrect column name 'val2 '' on query. Default database: 'test'. Query: 'CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'val1' AS `val1`,_latin1'val2 ' AS `val2 `'

http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

"Database, table, and column names should not end with space characters. "

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66704

2805 Davi Arnaut	2009-02-17
      Bug#40277: SHOW CREATE VIEW returns invalid SQL
      
      The problem is that not all column names retrieved from a SELECT
      statement can be used as view column names due to length and format
      restrictions and the server failed to properly check the conformity
      of those automatically generated column names before storing the
      final view definition on disk.
      
      Since columns retrieved from a SELECT statement can be anything
      ranging from functions to constants values of any format and length,
      the solution is to rewrite to a pre-defined format any names that
      are not acceptable as a view column name.
      
      The name is rewritten to "Name_exp_%u" where %u translates to the
      number of conversions done so far.
      
      To avoid this conversion scheme, define explict names for the view
      columns via the column_list clause.
      modified:
        mysql-test/r/view.result
        mysql-test/t/view.test
        sql/sql_view.cc

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66706

2805 Davi Arnaut	2009-02-17
      Bug#40277: SHOW CREATE VIEW returns invalid SQL
      
      The problem is that not all column names retrieved from a SELECT
      statement can be used as view column names due to length and format
      restrictions and the server failed to properly check the conformity
      of those automatically generated column names before storing the
      final view definition on disk.
      
      Since columns retrieved from a SELECT statement can be anything
      ranging from functions to constants values of any format and length,
      the solution is to rewrite to a pre-defined format any names that
      are not acceptable as a view column name.
      
      The name is rewritten to "Name_exp_%u" where %u translates to the
      position of the column. To avoid this conversion scheme, define
      explict names for the view columns via the column_list clause.
     @ mysql-test/r/view.result
        Add test case result for Bug#40277
     @ mysql-test/t/view.test
        Add test case for Bug#40277
     @ sql/sql_view.cc
        Check if auto generated column names are conforming.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66760

2805 Davi Arnaut	2009-02-18
      Bug#40277: SHOW CREATE VIEW returns invalid SQL
      
      The problem is that not all column names retrieved from a SELECT
      statement can be used as view column names due to length and format
      restrictions and the server failed to properly check the conformity
      of those automatically generated column names before storing the
      final view definition on disk.
      
      Since columns retrieved from a SELECT statement can be anything
      ranging from functions to constants values of any format and length,
      the solution is to rewrite to a pre-defined format any names that
      are not acceptable as a view column name.
      
      The name is rewritten to "Name_exp_%u" where %u translates to the
      position of the column. To avoid this conversion scheme, define
      explict names for the view columns via the column_list clause.
     @ mysql-test/r/view.result
        Add test case result for Bug#40277
     @ mysql-test/t/view.test
        Add test case for Bug#40277
     @ sql/sql_view.cc
        Check if auto generated column names are conforming.

Bug #43916  was marked as a duplicate of this one.

Related issue: See Bug#36287. The fix for Bug#40277 should reduce the incidence of the problem.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82513

3114 Davi Arnaut	2009-09-04
      Bug#40277: SHOW CREATE VIEW returns invalid SQL
      
      The problem is that not all column names retrieved from a SELECT
      statement can be used as view column names due to length and format
      restrictions and the server failed to properly check the conformity
      of those automatically generated column names before storing the
      final view definition on disk.
      
      Since columns retrieved from a SELECT statement can be anything
      ranging from functions to constants values of any format and length,
      the solution is to not print a auto generated names that are not
      acceptable as view column names.
      
      To avoid this kind of problem, users are encouraged to define
       names for the view columns via the column_list clause.
      Existing views with invalid query strings that contain invalid
      column names should be dropped re-created.
     @ mysql-test/r/date_formats.result
        Update test case result.
     @ mysql-test/r/subselect.result
        Update test case result.
     @ mysql-test/r/subselect3.result
        Update test case result.
     @ mysql-test/r/type_blob.result
        Update test case result.
     @ mysql-test/r/view.result
        Add test case result for Bug#40277
     @ mysql-test/t/view.test
        Add test case for Bug#40277
     @ sql/item.cc
        Don't print invalid column names.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/99140

3353 Davi Arnaut	2010-02-03
      Bug#40277: SHOW CREATE VIEW returns invalid SQL
      
      The problem is that not all column names retrieved from a SELECT
      statement can be used as view column names due to length and format
      restrictions. The server failed to properly check the conformity
      of those automatically generated column names before storing the
      final view definition on disk.
      
      Since columns retrieved from a SELECT statement can be anything
      ranging from functions to constants values of any format and length,
      the solution is to rewrite to a pre-defined format any names that
      are not acceptable as a view column name.
      
      The name is rewritten to "Name_exp_%u" where %u translates to the
      position of the column. To avoid this conversion scheme, define
      explict names for the view columns via the column_list clause.
     @ mysql-test/r/view.result
        Add test case result for Bug#40277
     @ mysql-test/t/view.test
        Add test case for Bug#40277
     @ sql/sql_view.cc
        Check if auto generated column names are conforming. Also, the
        make_unique_view_field_name function is not used as it uses the
        original name to construct a new one, which does not work if the
        name is invalid.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/100248

3340 Davi Arnaut	2010-02-13
      Bug#40277: SHOW CREATE VIEW returns invalid SQL
      
      The problem is that not all column names retrieved from a SELECT
      statement can be used as view column names due to length and format
      restrictions. The server failed to properly check the conformity
      of those automatically generated column names before storing the
      final view definition on disk.
      
      Since columns retrieved from a SELECT statement can be anything
      ranging from functions to constants values of any format and length,
      the solution is to rewrite to a pre-defined format any names that
      are not acceptable as a view column name.
      
      The name is rewritten to "Name_exp_%u" where %u translates to the
      position of the column. To avoid this conversion scheme, define
      explict names for the view columns via the column_list clause.
      Also, aliases are now only generated for top level statements.
     @ mysql-test/r/view.result
        Add test case result for Bug#40277
     @ mysql-test/t/view.test
        Add test case for Bug#40277
     @ sql/sql_view.cc
        Check if auto generated column names are conforming. Also, the
        make_unique_view_field_name function is not used as it uses the
        original name to construct a new one, which does not work if the
        name is invalid.

test script

Attachment: view_bug40277.test (application/octet-stream, text), 1.41 KiB.

include file

Attachment: bug40277.inc (application/octet-stream, text), 1.05 KiB.

Expected results

Attachment: view_bug40277.result (application/octet-stream, text), 3.64 KiB.

Archive with updated test

Attachment: bug40277_test.tgz (application/x-compressed-tar, text), 1.29 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/101425

3354 Davi Arnaut	2010-02-25
      Bug#40277: SHOW CREATE VIEW returns invalid SQL
      
      The problem is that not all column names retrieved from a SELECT
      statement can be used as view column names due to length and format
      restrictions. The server failed to properly check the conformity
      of those automatically generated column names before storing the
      final view definition on disk.
      
      Since columns retrieved from a SELECT statement can be anything
      ranging from functions to constants values of any format and length,
      the solution is to rewrite to a pre-defined format any names that
      are not acceptable as a view column name.
      
      The name is rewritten to "Name_exp_%u" where %u translates to the
      position of the column. To avoid this conversion scheme, define
      explict names for the view columns via the column_list clause.
      Also, aliases are now only generated for top level statements.
     @ mysql-test/include/bug40277.inc
        Add test case for Bug#40277
     @ mysql-test/r/compare.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/group_by.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/ps.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/subselect.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/subselect3.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/type_datetime.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/union.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/view.result
        Add test case result for Bug#40277
     @ mysql-test/r/view_bug40277.result
        Add test case result for Bug#40277
     @ mysql-test/t/view.test
        Add test case for Bug#40277
     @ mysql-test/t/view_bug40277.test
        Add test case for Bug#40277
     @ sql/sql_view.cc
        Check if auto generated column names are conforming. Also, the
        make_unique_view_field_name function is not used as it uses the
        original name to construct a new one, which does not work if the
        name is invalid.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/101752

3368 Davi Arnaut	2010-02-27
      Bug#40277: SHOW CREATE VIEW returns invalid SQL
      
      The problem is that not all column names retrieved from a SELECT
      statement can be used as view column names due to length and format
      restrictions. The server failed to properly check the conformity
      of those automatically generated column names before storing the
      final view definition on disk.
      
      Since columns retrieved from a SELECT statement can be anything
      ranging from functions to constants values of any format and length,
      the solution is to rewrite to a pre-defined format any names that
      are not acceptable as a view column name.
      
      The name is rewritten to "Name_exp_%u" where %u translates to the
      position of the column. To avoid this conversion scheme, define
      explict names for the view columns via the column_list clause.
      Also, aliases are now only generated for top level statements.
     @ mysql-test/include/bug40277.inc
        Add test case for Bug#40277
     @ mysql-test/r/compare.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/group_by.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/ps.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/subselect.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/subselect3.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/type_datetime.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/union.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/view.result
        Add test case result for Bug#40277
     @ mysql-test/r/view_bug40277.result
        Add test case result for Bug#40277
     @ mysql-test/t/view.test
        Add test case for Bug#40277
     @ mysql-test/t/view_bug40277.test
        Add test case for Bug#40277
     @ sql/sql_view.cc
        Check if auto generated column names are conforming. Also, the
        make_unique_view_field_name function is not used as it uses the
        original name to construct a new one, which does not work if the
        name is invalid.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/101755

3368 Davi Arnaut	2010-02-27
      Bug#40277: SHOW CREATE VIEW returns invalid SQL
      
      The problem is that not all column names retrieved from a SELECT
      statement can be used as view column names due to length and format
      restrictions. The server failed to properly check the conformity
      of those automatically generated column names before storing the
      final view definition on disk.
      
      Since columns retrieved from a SELECT statement can be anything
      ranging from functions to constants values of any format and length,
      the solution is to rewrite to a pre-defined format any names that
      are not acceptable as a view column name.
      
      The name is rewritten to "Name_exp_%u" where %u translates to the
      position of the column. To avoid this conversion scheme, define
      explict names for the view columns via the column_list clause.
      Also, aliases are now only generated for top level statements.
     @ mysql-test/include/bug40277.inc
        Add test case for Bug#40277
     @ mysql-test/r/compare.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/group_by.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/ps.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/subselect.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/subselect3.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/type_datetime.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/union.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/view.result
        Add test case result for Bug#40277
     @ mysql-test/r/view_bug40277.result
        Add test case result for Bug#40277
     @ mysql-test/t/view.test
        Add test case for Bug#40277
     @ mysql-test/t/view_bug40277.test
        Add test case for Bug#40277
     @ sql/sql_view.cc
        Check if auto generated column names are conforming. Also, the
        make_unique_view_field_name function is not used as it uses the
        original name to construct a new one, which does not work if the
        name is invalid.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/102678

3379 Davi Arnaut	2010-03-09
      Bug#40277: SHOW CREATE VIEW returns invalid SQL
      
      The problem is that not all column names retrieved from a SELECT
      statement can be used as view column names due to length and format
      restrictions. The server failed to properly check the conformity
      of those automatically generated column names before storing the
      final view definition on disk.
      
      Since columns retrieved from a SELECT statement can be anything
      ranging from functions to constants values of any format and length,
      the solution is to rewrite to a pre-defined format any names that
      are not acceptable as a view column name.
      
      The name is rewritten to "Name_exp_%u" where %u translates to the
      position of the column. To avoid this conversion scheme, define
      explict names for the view columns via the column_list clause.
      Also, aliases are now only generated for top level statements.
     @ mysql-test/include/view_alias.inc
        Add test case for Bug#40277
     @ mysql-test/r/compare.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/group_by.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/ps.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/subselect.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/subselect3.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/type_datetime.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/union.result
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
     @ mysql-test/r/view.result
        Add test case result for Bug#40277
     @ mysql-test/r/view_alias.result
        Add test case result for Bug#40277
     @ mysql-test/t/view_alias.test
        Add test case for Bug#40277
     @ sql/sql_view.cc
        Check if auto generated column names are conforming. Also, the
        make_unique_view_field_name function is not used as it uses the
        original name to construct a new one, which does not work if the
        name is invalid.

Queued to mysql-5.1-bugteam

Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100312095153-t4rtoqc7p96lmxvh) (merge vers: 5.5.3-m2) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:davi.arnaut@sun.com-20100309103626-ifpdcxaw3qgeem8a) (merge vers: 5.1.45) (pib:16)

Documented bugfix in the 5.1.46, 5.5.3, and 6.0.14 changelogs, as follows:

      SHOW CREATE VIEW returned invalid SQL if the definition contained 
      a SELECT 'some string' statement where the string was longer than 
      the maximum length of a column name, due to the fact that this text 
      was also used as an alias (in the AS clause). 
      
      Because not all names retrieved from arbitrary SELECT statements 
      can be used as view column names due to length and format
      restrictions, the server now checks the conformity of 
      automatically generated column names and rewrites according to a
      predefined format any names that are not acceptable as view column 
      names before storing the final view definition on disk.
      
      In such cases, the name is now rewritten as Name_exp_%u, where 
      %u is the position of the column. To avoid this conversion scheme, 
      define explicit, valid names for view columns using the column_list 
      clause of the CREATE VIEW statement.
      
      As part of this fix, aliases are now generated only for top-level
      statements.      

Closed.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Bug#58078 has been closed as a duplicate of this one.