From ba7725dace48d403187eb2a418a2081703fe5c9d Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 20 Sep 2019 15:59:54 -0700 Subject: MDEV-20229 CTE defined with table value constructor cannot be used in views A CTE can be defined as a table values constructor. In this case the CTE is always materialized in a temporary table. If the definition of the CTE contains a list of the names of the CTE columns then the query expression that uses this CTE can refer to the CTE columns by these names. Otherwise the names of the columns are taken from the names of the columns in the result set of the query that specifies the CTE. Thus if the column names of a CTE are provided in the definition the columns of result set should be renamed. In a general case renaming of the columns is done in the select lists of the query specifying the CTE. If a CTE is specified by a table value constructor then there are no such select lists and renaming is actually done for the columns of the result of materialization. Now if a view is specified by a query expression that uses a CTE specified by a table value constructor saving the column names of the CTE in the stored view definition becomes critical: without these names the query expression is not able to refer to the columns of the CTE. This patch saves the given column names of CTEs in stored view definitions that use them. --- mysql-test/main/table_value_constr.result | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'mysql-test/main/table_value_constr.result') diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 6356d4ecd75..03e378e68f2 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2586,3 +2586,16 @@ create view v1 as union ( values (5), (7), (1), (3), (4) order by 2 limit 2 ); ERROR 42S22: Unknown column '2' in 'order clause' +# +# MDEV-20229: view defined as select using +# CTE with named columns defined as TVC +# +create view v1 as with t(a) as (values (2), (1)) select a from t; +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 with t(a) as (values (2),(1))select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci +select * from v1; +a +2 +1 +drop view v1; -- cgit v1.2.1