diff options
| author | Nisha Gopalakrishnan <nisha.gopalakrishnan@oracle.com> | 2015-07-23 10:47:58 +0530 |
|---|---|---|
| committer | Nisha Gopalakrishnan <nisha.gopalakrishnan@oracle.com> | 2015-07-23 10:47:58 +0530 |
| commit | b5380e092c1cac3050a711c308136eee15c51826 (patch) | |
| tree | a5bf864661855865459c8009226c5d1fb6f181db /mysql-test/r/view.result | |
| parent | 888fabd6909237f55ed9b9cf7a0c852c2e5f0beb (diff) | |
| download | mariadb-git-b5380e092c1cac3050a711c308136eee15c51826.tar.gz | |
BUG#19886430: VIEW CREATION WITH NAMED COLUMNS, OVER UNION,
IS REJECTED.
Analysis
========
View creation with named columns over UNION is rejected.
Consider the following view definition:
CREATE VIEW v1 (fld1, fld2) AS SELECT 1 AS a, 2 AS b
UNION ALL SELECT 1 AS a, 1 AS a;
A 'duplicate column' error was reported due to the duplicate
alias name in the secondary SELECT. The VIEW column names
are either explicitly specified or determined from the
first SELECT (which can be auto generated if not specified).
Since a duplicate column name check was performed even
for the secondary SELECTs, an error was reported.
Fix
====
Check for duplicate column names only for the named
columns if specified or only for the first SELECT.
Diffstat (limited to 'mysql-test/r/view.result')
| -rw-r--r-- | mysql-test/r/view.result | 35 |
1 files changed, 35 insertions, 0 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index c31b4f5216b..a3adf80c096 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4146,3 +4146,38 @@ 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; +# +# BUG#19886430: VIEW CREATION WITH NAMED COLUMNS, OVER UNION, +# IS REJECTED +# Without the patch, reports an error. +CREATE VIEW v1 (fld1, fld2) AS +SELECT 1 AS a, 2 AS b +UNION ALL +SELECT 1 AS a, 1 AS a; +# The column names are explicitly specified and not duplicates, hence +# succeeds. +CREATE VIEW v2 (fld1, fld2) AS +SELECT 1 AS a, 2 AS a +UNION ALL +SELECT 1 AS a, 1 AS a; +# The column name in the first SELECT are not duplicates, hence succeeds. +CREATE VIEW v3 AS +SELECT 1 AS a, 2 AS b +UNION ALL +SELECT 1 AS a, 1 AS a; +# Should report an error, since the explicitly specified column names are +# duplicates. +CREATE VIEW v4 (fld1, fld1) AS +SELECT 1 AS a, 2 AS b +UNION ALL +SELECT 1 AS a, 1 AS a; +ERROR 42S21: Duplicate column name 'fld1' +# Should report an error, since duplicate column name is specified in the +# First SELECT. +CREATE VIEW v4 AS +SELECT 1 AS a, 2 AS a +UNION ALL +SELECT 1 AS a, 1 AS a; +ERROR 42S21: Duplicate column name 'a' +# Cleanup +DROP VIEW v1, v2, v3; |