summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/view.result37
-rw-r--r--mysql-test/t/view.test37
-rw-r--r--sql/sql_view.cc60
3 files changed, 120 insertions, 14 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 9100022cb7f..c31b4f5216b 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -4109,3 +4109,40 @@ DROP TABLE t1;
DROP DATABASE IF EXISTS nodb;
CREATE VIEW nodb.a AS SELECT 1;
ERROR 42000: Unknown database 'nodb'
+#
+# BUG#14117018 - MYSQL SERVER CREATES INVALID VIEW DEFINITION
+# BUG#18405221 - SHOW CREATE VIEW OUTPUT INCORRECT
+#
+CREATE VIEW v1 AS (SELECT '' FROM DUAL);
+CREATE VIEW v2 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL
+(SELECT '' FROM DUAL);
+CREATE VIEW v3 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL
+(SELECT '' FROM DUAL) UNION ALL
+(SELECT '' FROM DUAL);
+CREATE VIEW v4 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL
+(SELECT '' AS col2 FROM DUAL) UNION ALL
+(SELECT '' FROM DUAL);
+# Name for the column in select1 is set properly with or
+# without this fix.
+SHOW CREATE VIEW v1;
+View Create View character_set_client collation_connection
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select '' AS `Name_exp_1`) latin1 latin1_swedish_ci
+# Name for the column in select2 is set with this fix.
+# Without this fix, name would not have set for the
+# columns in select2.
+SHOW CREATE VIEW v2;
+View Create View character_set_client collation_connection
+v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS (select 'BUG#14117018' AS `col1`) union all (select '' AS `Name_exp_2`) latin1 latin1_swedish_ci
+# Name for the field item in select2 & select3 is set with this fix.
+# Without this fix, name would not have set for the
+# columns in select2 & select3.
+SHOW CREATE VIEW v3;
+View Create View character_set_client collation_connection
+v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS (select 'BUG#14117018' AS `col1`) union all (select '' AS `Name_exp_2`) union all (select '' AS `Name_exp_3`) latin1 latin1_swedish_ci
+# Name for the field item in select3 is set with this fix.
+# Without this fix, name would not have set for the
+# columns in select3.
+SHOW CREATE VIEW v4;
+View Create View character_set_client collation_connection
+v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS (select 'BUG#14117018' AS `col1`) union all (select '' AS `col2`) union all (select '' AS `Name_exp_3`) latin1 latin1_swedish_ci
+DROP VIEW v1, v2, v3, v4;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 603b3e5cdb9..959153ee851 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -4147,6 +4147,43 @@ DROP DATABASE IF EXISTS nodb;
--error ER_BAD_DB_ERROR
CREATE VIEW nodb.a AS SELECT 1;
+
+--echo #
+--echo # BUG#14117018 - MYSQL SERVER CREATES INVALID VIEW DEFINITION
+--echo # BUG#18405221 - SHOW CREATE VIEW OUTPUT INCORRECT
+--echo #
+
+CREATE VIEW v1 AS (SELECT '' FROM DUAL);
+CREATE VIEW v2 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL
+ (SELECT '' FROM DUAL);
+CREATE VIEW v3 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL
+ (SELECT '' FROM DUAL) UNION ALL
+ (SELECT '' FROM DUAL);
+CREATE VIEW v4 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL
+ (SELECT '' AS col2 FROM DUAL) UNION ALL
+ (SELECT '' FROM DUAL);
+
+--echo # Name for the column in select1 is set properly with or
+--echo # without this fix.
+SHOW CREATE VIEW v1;
+
+--echo # Name for the column in select2 is set with this fix.
+--echo # Without this fix, name would not have set for the
+--echo # columns in select2.
+SHOW CREATE VIEW v2;
+
+--echo # Name for the field item in select2 & select3 is set with this fix.
+--echo # Without this fix, name would not have set for the
+--echo # columns in select2 & select3.
+SHOW CREATE VIEW v3;
+
+--echo # Name for the field item in select3 is set with this fix.
+--echo # Without this fix, name would not have set for the
+--echo # columns in select3.
+SHOW CREATE VIEW v4;
+
+DROP VIEW v1, v2, v3, v4;
+
# Check that all connections opened by test cases in this file are really
# gone so execution of other tests won't be affected by their presence.
--source include/wait_until_count_sessions.inc
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 4a10c397508..6bbc475565b 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -1,4 +1,4 @@
-/* Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -163,27 +163,61 @@ err:
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
+ @param lex Lex for this thread.
+
+ @retval false Operation was a success.
+ @retval true An error occurred.
*/
-static void make_valid_column_names(List<Item> &item_list)
+static bool make_valid_column_names(LEX *lex)
{
Item *item;
uint name_len;
- List_iterator_fast<Item> it(item_list);
char buff[NAME_LEN];
+ uint column_no= 1;
DBUG_ENTER("make_valid_column_names");
- for (uint column_no= 1; (item= it++); column_no++)
+ for (SELECT_LEX *sl= &lex->select_lex; sl; sl= sl->next_select())
{
- 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);
+ for (List_iterator_fast<Item> it(sl->item_list); (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);
+ }
+
+ /*
+ There is a possibility of generating same name for column in more than
+ one SELECT_LEX. For Example:
+
+ CREATE TABLE t1 (Name_exp_1 INT, Name_exp_2 INT, Name_exp_3 INT);
+ CREATE TABLE t2 (Name_exp_1 INT, Name_exp_2 INT, Name_exp_3 INT);
+
+ CREATE VIEW v1 AS SELECT '', t1.Name_exp_2 AS Name_exp_2 FROM t1
+ UNION
+ SELECT '', t2.Name_exp_1 AS Name_exp_1 from t2;
+
+ But, column names of the first SELECT_LEX is considered
+ for the output.
+
+ mysql> SELECT * FROM v1;
+ +------------+------------+
+ | Name_exp_1 | Name_exp_2 |
+ +------------+------------+
+ | | 2 |
+ | | 3 |
+ +------------+------------+
+
+ So, checking for duplicate names in only "sl", current
+ SELECT_LEX.
+ */
+ if (check_duplicate_names(sl->item_list, 1))
+ DBUG_RETURN(true);
}
- DBUG_VOID_RETURN;
+ DBUG_RETURN(false);
}
@@ -590,9 +624,7 @@ 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))
+ if (make_valid_column_names(lex))
{
res= TRUE;
goto err;
View Lorry Controller Status