List:Commits« Previous MessageNext Message »
From:Davi Arnaut Date:February 3 2010 7:09pm
Subject:bzr commit into mysql-5.1-bugteam branch (davi:3353) Bug#40277
View as plain text  
# At a local mysql-5.1-bugteam repository of davi

 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.

    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	2009-08-31 14:09:09 +0000
+++ b/mysql-test/r/view.result	2010-02-03 19:09:08 +0000
@@ -3836,6 +3836,18 @@ 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;
+CREATE VIEW v1 AS SELECT (SELECT ' c1 ');
+DROP VIEW v1;
+CREATE VIEW v1 AS select (select ' c1 ' AS `Name_exp_1`) AS `(SELECT ' c1 ')`;
+DROP VIEW v1;
 #
 # Bug #44860: ALTER TABLE on view crashes server
 #

=== modified file 'mysql-test/t/view.test'
--- a/mysql-test/t/view.test	2009-08-31 14:09:09 +0000
+++ b/mysql-test/t/view.test	2010-02-03 19:09:08 +0000
@@ -3857,6 +3857,28 @@ 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;
+
+CREATE VIEW v1 AS SELECT (SELECT ' c1 ');
+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;
+
 ###########################################################################
 
 

=== modified file 'sql/sql_view.cc'
--- a/sql/sql_view.cc	2010-01-24 07:03:23 +0000
+++ b/sql/sql_view.cc	2010-02-03 19:09:08 +0000
@@ -155,6 +155,35 @@ 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;
+  uint name_len;
+  List_iterator_fast<Item> it(item_list);
+  char buff[NAME_LEN];
+  DBUG_ENTER("check_column_names");
+
+  for (uint c= 1; (item= it++); c++)
+  {
+    if (!item->is_autogenerated_name || !check_column_name(item->name))
+      continue;
+    name_len= my_snprintf(buff, NAME_LEN, "Name_exp_%u", c);
+    item->orig_name= item->name;
+    item->set_name(buff, name_len, system_charset_info);
+  }
+
+  DBUG_VOID_RETURN;
+}
+
+
 /*
   Fill defined view parts
 
@@ -389,9 +418,7 @@ bool mysql_create_view(THD *thd, TABLE_L
   TABLE_LIST *tables= lex->query_tables;
   TABLE_LIST *tbl;
   SELECT_LEX *select_lex= &lex->select_lex;
-#ifndef NO_EMBEDDED_ACCESS_CHECKS
   SELECT_LEX *sl;
-#endif
   SELECT_LEX_UNIT *unit= &lex->unit;
   bool res= FALSE;
   DBUG_ENTER("mysql_create_view");
@@ -551,10 +578,19 @@ bool mysql_create_view(THD *thd, TABLE_L
     }
   }
 
-  if (check_duplicate_names(select_lex->item_list, 1))
+  /*
+    Check if the auto generated column names are conforming.
+    The check is also extended to subqueries and alike.
+  */
+  for (sl= lex->all_selects_list; sl; sl= sl->next_select_in_list())
   {
-    res= TRUE;
-    goto err;
+    check_column_names(sl->item_list);
+
+    if (check_duplicate_names(sl->item_list, 1))
+    {
+      res= TRUE;
+      goto err;
+    }
   }
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS


Attachment: [text/bzr-bundle] bzr/davi.arnaut@sun.com-20100203190908-5zxqg3yxtq4c0k91.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (davi:3353) Bug#40277Davi Arnaut3 Feb
  • Re: bzr commit into mysql-5.1-bugteam branch (davi:3353) Bug#40277Konstantin Osipov12 Feb