revision-id: ba7725dace48d403187eb2a418a2081703fe5c9d (mariadb-10.3.18-16-gba7725d) parent(s): 90a9c4cae74d2ef1008e3f216026b7fd2697e46b author: Igor Babaev committer: Igor Babaev timestamp: 2019-09-20 15:59:54 -0700 message: 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/cte_nonrecursive.result | 4 ++-- mysql-test/main/cte_recursive.result | 6 +++--- mysql-test/main/table_value_constr.result | 13 +++++++++++++ mysql-test/main/table_value_constr.test | 10 ++++++++++ sql/sql_cte.cc | 16 ++++++++++++++++ sql/sql_union.cc | 1 + 6 files changed, 45 insertions(+), 5 deletions(-) diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index 69494d0..d6904b8 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -606,7 +606,7 @@ with t(c) as (select a from t1 where b >= 'c') select * from t r1 where r1.c=4; 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 with t as (select `t1`.`a` AS `c` from `t1` where `t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4 latin1 latin1_swedish_ci +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS with t(c) as (select `t1`.`a` AS `c` from `t1` where `t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4 latin1 latin1_swedish_ci select * from v3; c 4 @@ -618,7 +618,7 @@ with t(c) as (select a from t1 where b >= 'c') select * from t r1, t r2 where r1.c=r2.c and r2.c=4; 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 with t as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c') `r2`) where `r1`.`c` = `r2`.`c` and `r2`.`c` = 4 latin1 latin1_swedish_ci +v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS with t(c) as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c') `r2`) where `r1`.`c` = `r2`.`c` and `r2`.`c` = 4 latin1 latin1_swedish_ci select * from v4; c d 4 4 diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index f2ae992..9b2aa2b 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -699,7 +699,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 5 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 with recursive ancestor_couple_ids as (/* select#2 */ select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors as (/* select#3 */ select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all /* select#4 */ select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all /* select#5 */ select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_ id`)/* select#1 */ select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id` +Note 1003 with recursive ancestor_couple_ids(h_id,w_id) as (/* select#2 */ select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors(id,name,dob,father,mother) as (/* select#3 */ select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all /* select#4 */ select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all /* select#5 */ select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_cou ple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)/* select#1 */ select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id` # simple mutual recursion with recursive ancestor_couple_ids(h_id, w_id) @@ -3091,7 +3091,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 16 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 with recursive destinations as (/* select#2 */ select `test`.`a`.`arrival` AS `city`,1 AS `legs` from `test`.`flights` `a` where `test`.`a`.`departure` = 'Cairo' union /* select#3 */ select `test`.`b`.`arrival` AS `arrival`,`r`.`legs` + 1 AS `r.legs + 1` from `destinations` `r` join `test`.`flights` `b` where `r`.`city` = `test`.`b`.`departure` and !<in_optimizer>(`test`.`b`.`arrival`,<exists>(/* select#4 */ select `destinations`.`city` from `destinations` where trigcond(`test`.`b`.`arrival` = `destinations`.`city` or `destinations`.`city` is null) having trigcond(`destinations`.`city` is null))))/* select#1 */ select `destinations`.`city` AS `city`,`destinations`.`legs` AS `legs` from `destinations` +Note 1003 with recursive destinations(city,legs) as (/* select#2 */ select `test`.`a`.`arrival` AS `city`,1 AS `legs` from `test`.`flights` `a` where `test`.`a`.`departure` = 'Cairo' union /* select#3 */ select `test`.`b`.`arrival` AS `arrival`,`r`.`legs` + 1 AS `r.legs + 1` from `destinations` `r` join `test`.`flights` `b` where `r`.`city` = `test`.`b`.`departure` and !<in_optimizer>(`test`.`b`.`arrival`,<exists>(/* select#4 */ select `destinations`.`city` from `destinations` where trigcond(`test`.`b`.`arrival` = `destinations`.`city` or `destinations`.`city` is null) having trigcond(`destinations`.`city` is null))))/* select#1 */ select `destinations`.`city` AS `city`,`destinations`.`legs` AS `legs` from `destinations` set standard_compliant_cte=default; drop table flights; # @@ -3378,7 +3378,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 with recursive rcte as (/* select#2 */ select 1 AS `a` union /* select#3 */ select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (/* select#4 */ select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (/* select#5 */ select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)/* select#1 */ select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2` +Note 1003 with recursive rcte(a) as (/* select#2 */ select 1 AS `a` union /* select#3 */ select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (/* select#4 */ select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (/* select#5 */ select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)/* select#1 */ select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2` prepare stmt from "with recursive rcte(a) as (select 1 union select cast(a+1 as unsigned) from rcte where a < 10), diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 6356d4e..03e378e 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; diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 6b89816..4464eb7 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; diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index 247d7e5..08d5e20 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1430,6 +1430,22 @@ void With_clause::print(String *str, enum_query_type query_type) void With_element::print(String *str, enum_query_type query_type) { str->append(query_name); + if (column_list.elements) + { + List_iterator_fast<LEX_CSTRING> li(column_list); + str->append('('); + for (LEX_CSTRING *col_name= li++; ; ) + { + str->append(col_name); + col_name= li++; + if (!col_name) + { + str->append(')'); + break; + } + str->append(','); + } + } str->append(STRING_WITH_LEN(" as ")); str->append('('); spec->print(str, query_type); diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 6ab2619..c119f1e 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1858,6 +1858,7 @@ bool st_select_lex_unit::cleanup() DBUG_RETURN(FALSE); } } + columns_are_renamed= false; cleaned= 1; for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select())