revision-id: d3a0c240415fbeb86eeac5f4140d2829fc01832a (mariadb-10.2.31-604-gd3a0c24) parent(s): 50d7eddc3d21b9bad66db6961900e3222a6fa356 author: Igor Babaev committer: Igor Babaev timestamp: 2020-12-04 14:18:51 -0800 message: MDEV-22781 CREATE VIEW containing WITH clause Signal 11 For table references to CTEs the field TABLE_LIST::db must be set to an empty string as it's done for table references to derived tables in order CTEs to be processed similar to how derived tables are processed. --- mysql-test/r/cte_nonrecursive.result | 43 ++++++++++++++++++++++++++++++++---- mysql-test/r/cte_recursive.result | 4 ++-- mysql-test/t/cte_nonrecursive.test | 29 ++++++++++++++++++++++++ sql/sql_cte.cc | 1 + sql/sql_view.cc | 2 ++ 5 files changed, 73 insertions(+), 6 deletions(-) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index 746fcbc..a47fdcd 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -571,7 +571,7 @@ with t as (select a from t1 where b >= 'c') select * from t2,t where t2.c=t.a; 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 as (select `t1`.`a` AS `a` from `t1` where `t1`.`b` >= 'c')select `t2`.`c` AS `c`,`t`.`a` AS `a` from (`t2` join `t`) where `t2`.`c` = `t`.`a` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `test`.`t2`.`c` AS `c`,`t`.`a` AS `a` from (`test`.`t2` join `t`) where `test`.`t2`.`c` = `t`.`a` latin1 latin1_swedish_ci select * from v1; c a 4 4 @@ -588,7 +588,7 @@ with t as (select a, count(*) from t1 where b >= 'c' group by a) select * from t2,t where t2.c=t.a; 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 with t as (select `t1`.`a` AS `a`,count(0) AS `count(*)` from `t1` where `t1`.`b` >= 'c' group by `t1`.`a`)select `t2`.`c` AS `c`,`t`.`a` AS `a`,`t`.`count(*)` AS `count(*)` from (`t2` join `t`) where `t2`.`c` = `t`.`a` latin1 latin1_swedish_ci +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with t as (select `test`.`t1`.`a` AS `a`,count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` >= 'c' group by `test`.`t1`.`a`)select `test`.`t2`.`c` AS `c`,`t`.`a` AS `a`,`t`.`count(*)` AS `count(*)` from (`test`.`t2` join `t`) where `test`.`t2`.`c` = `t`.`a` latin1 latin1_swedish_ci select * from v2; c a count(*) 4 4 2 @@ -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 as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4 latin1 latin1_swedish_ci select * from v3; c 4 @@ -1349,7 +1349,7 @@ r.r_regionkey in select r_regionkey from t where r_name <> "ASIA"); show create view v; View Create View character_set_client collation_connection -v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `n`.`n_nationkey` AS `n_nationkey`,`n`.`n_name` AS `n_name`,`n`.`n_regionkey` AS `n_regionkey`,`r`.`r_regionkey` AS `r_regionkey`,`r`.`r_name` AS `r_name` from (`nation` `n` join `region` `r`) where `n`.`n_regionkey` = `r`.`r_regionkey` and `r`.`r_regionkey` in (with t as (select `region`.`r_regionkey` AS `r_regionkey`,`region`.`r_name` AS `r_name` from `region` where `region`.`r_regionkey` <= 3)select `t`.`r_regionkey` from `t` where `t`.`r_name` <> 'ASIA') latin1 latin1_swedish_ci +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `n`.`n_nationkey` AS `n_nationkey`,`n`.`n_name` AS `n_name`,`n`.`n_regionkey` AS `n_regionkey`,`r`.`r_regionkey` AS `r_regionkey`,`r`.`r_name` AS `r_name` from (`test`.`nation` `n` join `test`.`region` `r`) where `n`.`n_regionkey` = `r`.`r_regionkey` and `r`.`r_regionkey` in (with t as (select `test`.`region`.`r_regionkey` AS `r_regionkey`,`test`.`region`.`r_name` AS `r_name` from `test`.`region` where `test`.`region`.`r_regionkey` <= 3)select `t`.`r_regionkey` from `t` where `t`.`r_name` <> 'ASIA') latin1 latin1_swedish_ci select * from v; n_nationkey n_name n_regionkey r_regionkey r_name 0 ALGERIA 0 0 AFRICA @@ -1690,4 +1690,39 @@ ERROR 3D000: No database selected DROP TABLE test.t; connection default; disconnect con1; +# +# MDEV-22781: create view with CTE without default database +# +drop database test; +create database db1; +create table db1.t1 (a int); +insert into db1.t1 values (3),(7),(1); +create view db1.v1 as with t as (select * from db1.t1) select * from t; +show create view db1.v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS with t as (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci +select * from db1.v1; +a +3 +7 +1 +drop view db1.v1; +prepare stmt from " +create view db1.v1 as with t as (select * from db1.t1) select * from t; +"; +execute stmt; +deallocate prepare stmt; +show create view db1.v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS with t as (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci +select * from db1.v1; +a +3 +7 +1 +drop view db1.v1; +drop table db1.t1; +drop database db1; +create database test; +use test; # End of 10.2 tests diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 85883d3..405bfbb 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -818,7 +818,7 @@ where p.id = a.father or p.id = a.mother select * from ancestors; 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 recursive ancestors as (select `folks`.`id` AS `id`,`folks`.`name` AS `name`,`folks`.`dob` AS `dob`,`folks`.`father` AS `father`,`folks`.`mother` AS `mother` from `folks` where `folks`.`name` = 'Me' and `folks`.`dob` = '2000-01-01' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `a`) where `p`.`id` = `a`.`father` or `p`.`id` = `a`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with recursive ancestors as (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' and `test`.`folks`.`dob` = '2000-01-01' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `a`) where `p`.`id` = `a`.`father` or `p`.`id` = `a`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci select * from v1; id name dob father mother 100 Me 2000-01-01 20 30 @@ -849,7 +849,7 @@ where p.id = ma.mother select * from ancestors; 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 with recursive ancestors as (select `folks`.`id` AS `id`,`folks`.`name` AS `name`,`folks`.`dob` AS `dob`,`folks`.`father` AS `father`,`folks`.`mother` AS `mother` from `folks` where `folks`.`name` = 'Me' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `fa`) where `p`.`id` = `fa`.`father` union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `ma`) where `p`.`id` = `ma`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with recursive ancestors as (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 select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `fa`) where `p`.`id` = `fa`.`father` union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `ma`) where `p`.`id` = `ma`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci select * from v2; id name dob father mother 100 Me 2000-01-01 20 30 diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index 41a5b81..0174ddb 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -1201,4 +1201,33 @@ DROP TABLE test.t; --connection default --disconnect con1 +--echo # +--echo # MDEV-22781: create view with CTE without default database +--echo # + +drop database test; +create database db1; +create table db1.t1 (a int); +insert into db1.t1 values (3),(7),(1); + +create view db1.v1 as with t as (select * from db1.t1) select * from t; +show create view db1.v1; +select * from db1.v1; +drop view db1.v1; + +prepare stmt from " +create view db1.v1 as with t as (select * from db1.t1) select * from t; +"; +execute stmt; +deallocate prepare stmt; +show create view db1.v1; +select * from db1.v1; +drop view db1.v1; + +drop table db1.t1; +drop database db1; + +create database test; +use test; + --echo # End of 10.2 tests diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index fe8e0de..a8bccf0 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -227,6 +227,7 @@ With_element *With_clause::find_table_def(TABLE_LIST *table, !table->is_fqtn) { table->set_derived(); + table->db= empty_c_string; return with_elem; } } diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 8bfe689..0701c52 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -291,6 +291,8 @@ bool create_view_precheck(THD *thd, TABLE_LIST *tables, TABLE_LIST *view, { for (tbl= sl->get_table_list(); tbl; tbl= tbl->next_local) { + if (!tbl->with && tbl->select_lex) + tbl->with= tbl->select_lex->find_table_def_in_with_clauses(tbl); /* Ensure that we have some privileges on this table, more strict check will be done on column level after preparation,