List:Commits« Previous MessageNext Message »
From:Davi Arnaut Date:February 17 2009 10:30pm
Subject:bzr commit into mysql-5.1-bugteam branch (davi:2805) Bug#40277
View as plain text  
# At a local mysql-5.1-bugteam repository of davi

 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.

    modified:
      mysql-test/r/view.result
      mysql-test/t/view.test
      sql/sql_view.cc
=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result	2008-12-13 19:42:12 +0000
+++ b/mysql-test/r/view.result	2009-02-17 22:29:58 +0000
@@ -3817,6 +3817,14 @@ call p();
 call p();
 drop view a;
 drop procedure p;
+CREATE VIEW v1 AS SELECT '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555';
+DROP VIEW v1;
+CREATE VIEW v1 AS select '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555' AS `Name_exp_1`;
+DROP VIEW v1;
+CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ';
+DROP VIEW v1;
+CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`;
+DROP VIEW v1;
 # -----------------------------------------------------------------
 # -- End of 5.1 tests.
 # -----------------------------------------------------------------

=== modified file 'mysql-test/t/view.test'
--- a/mysql-test/t/view.test	2008-12-13 19:42:12 +0000
+++ b/mysql-test/t/view.test	2009-02-17 22:29:58 +0000
@@ -3745,6 +3745,22 @@ call p();
 drop view a;
 drop procedure p;
 
+#
+# Bug#40277 SHOW CREATE VIEW returns invalid SQL
+#
+
+CREATE VIEW v1 AS SELECT '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555';
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ';
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
 ###########################################################################
 
 --echo # -----------------------------------------------------------------

=== modified file 'sql/sql_view.cc'
--- a/sql/sql_view.cc	2009-01-15 18:11:25 +0000
+++ b/sql/sql_view.cc	2009-02-17 22:29:58 +0000
@@ -154,6 +154,38 @@ err:
   DBUG_RETURN(TRUE);
 }
 
+
+/**
+  Check if auto generated column names are conforming and
+  possibly generate a conforming name for then if not.
+
+  @param item_list  List of Items which should be checked
+*/
+
+static void check_column_names(List<Item> &item_list)
+{
+  Item *item;
+  char *name;
+  uint name_len, count= 0;
+  List_iterator_fast<Item> it(item_list);
+  char buff[NAME_LEN];
+  DBUG_ENTER("check_column_names");
+
+  while ((item= it++))
+  {
+    count++;
+    if (!item->is_autogenerated_name || !check_column_name(item->name))
+      continue;
+    name= item->orig_name ? item->orig_name : item->name;
+    name_len= my_snprintf(buff, NAME_LEN, "Name_exp_%u", count);
+    item->orig_name= item->name;
+    item->set_name(buff, name_len, system_charset_info);
+  }
+
+  DBUG_VOID_RETURN;
+}
+
+
 /*
   Fill defined view parts
 
@@ -550,6 +582,9 @@ bool mysql_create_view(THD *thd, TABLE_L
     }
   }
 
+  /* Check if the auto generated column names are conforming. */
+  check_column_names(select_lex->item_list);
+
   if (check_duplicate_names(select_lex->item_list, 1))
   {
     res= TRUE;


Attachment: [text/bzr-bundle] bzr/davi.arnaut@sun.com-20090217222958-97xj6ga5lw6prsyx.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (davi:2805) Bug#40277Davi Arnaut17 Feb