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

[23 Oct 2008 9:32] Mike Lischke
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.
[23 Oct 2008 9:39] Mike Lischke
Increasing severity as this bugs prevents a successful migration and potentially every other client which uses SHOW CREATE VIEW (e.g. mysqldump etc.).
[23 Oct 2008 10:03] Valeriy Kravchuk
Thank you for a bug report. Verified just as decribed, also - with 5.1.28.
[3 Jan 2009 11:38] Valeriy Kravchuk
Bug #41834 was marked as a duplicate of this one.
[3 Jan 2009 11:41] Valeriy Kravchuk
Bug #26446 was marked as a duplicate of this one.
[4 Feb 2009 14:46] MySQL Verification Team
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. "
[17 Feb 2009 22:17] Bugs System
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
[17 Feb 2009 22:30] Bugs System
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.
[18 Feb 2009 13:08] Bugs System
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.
[27 Mar 2009 18:25] Valeriy Kravchuk
Bug #43916  was marked as a duplicate of this one.
[7 Apr 2009 18:36] Paul DuBois
Related issue: See Bug#36287. The fix for Bug#40277 should reduce the incidence of the problem.
[4 Sep 2009 22:22] Bugs System
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.
[3 Feb 2010 19:09] Bugs System
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.
[13 Feb 2010 12:16] Bugs System
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.
[22 Feb 2010 11:29] Matthias Leich
test script

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

[22 Feb 2010 11:51] Matthias Leich
include file

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

[22 Feb 2010 11:52] Matthias Leich
Expected results

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

[24 Feb 2010 13:50] Matthias Leich
Archive with updated test

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

[25 Feb 2010 10:21] Bugs System
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.
[27 Feb 2010 11:25] Bugs System
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.
[27 Feb 2010 14:00] Bugs System
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.
[9 Mar 2010 10:36] Bugs System
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.
[9 Mar 2010 21:57] Davi Arnaut
Queued to mysql-5.1-bugteam
[26 Mar 2010 8:22] Bugs System
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)
[26 Mar 2010 8:26] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)
[26 Mar 2010 8:30] Bugs System
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)
[6 Apr 2010 7:59] Bugs System
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)
[6 Apr 2010 12:42] Jon Stephens
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.
[8 Apr 2010 7:53] Jon Stephens
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
[17 Jun 2010 12:18] Bugs System
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)
[17 Jun 2010 13:05] Bugs System
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)
[17 Jun 2010 13:45] Bugs System
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)
[10 Nov 2010 14:58] Davi Arnaut
Bug#58078 has been closed as a duplicate of this one.