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.

1 files changed, 10 insertions, 0 deletions

diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test
index 6b89816cc0c..4464eb7b77b 100644
--- a/mysql-test/main/table_value_constr.test
+++ b/mysql-test/main/table_value_constr.test
@@ -1316,3 +1316,13 @@ create view v1 as
 ( values (5), (7), (1), (3), (4) limit 2 offset 1 )
   union
 ( values (5), (7), (1), (3), (4) order by 2 limit 2 );
+
+--echo #
+--echo # MDEV-20229: view defined as select using
+--echo #             CTE with named columns defined as TVC
+--echo #
+
+create view v1 as with t(a) as (values (2), (1)) select a from t;
+show create view v1;
+select * from v1;
+drop view v1;