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.

1 files changed, 32 insertions, 0 deletions

diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index c66990c54c6..5ec80dfb621 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -155,6 +155,35 @@ err:
   DBUG_RETURN(TRUE);
 }
 
+
+/**
+  Check if auto generated column names are conforming and
+  possibly generate a conforming name for them if not.
+
+  @param item_list  List of Items which should be checked
+*/
+
+static void make_valid_column_names(List<Item> &item_list)
+{
+  Item *item;
+  uint name_len;
+  List_iterator_fast<Item> it(item_list);
+  char buff[NAME_LEN];
+  DBUG_ENTER("make_valid_column_names");
+
+  for (uint column_no= 1; (item= it++); column_no++)
+  {
+    if (!item->is_autogenerated_name || !check_column_name(item->name))
+      continue;
+    name_len= my_snprintf(buff, NAME_LEN, "Name_exp_%u", column_no);
+    item->orig_name= item->name;
+    item->set_name(buff, name_len, system_charset_info);
+  }
+
+  DBUG_VOID_RETURN;
+}
+
+
 /*
   Fill defined view parts
 
@@ -548,6 +577,9 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views,
     }
   }
 
+  /* Check if the auto generated column names are conforming. */
+  make_valid_column_names(select_lex->item_list);
+
   if (check_duplicate_names(select_lex->item_list, 1))
   {
     res= TRUE;