List:Commits« Previous MessageNext Message »
From:Davi Arnaut Date:February 17 2009 10:17pm
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
      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

per-file messages:
  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 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:17:31 +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_0`;
+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_0`,' c3' AS `c3`,' c4 ' AS `Name_exp_1`;
+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:17:31 +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:17:31 +0000
@@ -154,6 +154,37 @@ 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++))
+  {
+    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 +581,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;

Thread
bzr commit into mysql-5.1-bugteam branch (davi:2805) Bug#40277Davi Arnaut17 Feb