[Commits] 57fe4b4: MDEV-22781 CREATE VIEW containing WITH clause Signal 11
revision-id: 57fe4b4b2c7cd38ebfef78adc9c0263a52176db0 (mariadb-10.2.31-604-g57fe4b4) parent(s): 50d7eddc3d21b9bad66db6961900e3222a6fa356 author: Igor Babaev committer: Igor Babaev timestamp: 2020-12-04 14:20:52 -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. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- 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,
Sanja This patch has changes in addition to changes of the fix for MDEV-24314 They are needed to resolve a similar problem as the problem of MDEV-24314 but for CTEs. Please review. Regards, Igor. On 12/04/2020 02:20 PM, IgorBabaev wrote:
revision-id: 57fe4b4b2c7cd38ebfef78adc9c0263a52176db0 (mariadb-10.2.31-604-g57fe4b4) parent(s): 50d7eddc3d21b9bad66db6961900e3222a6fa356 author: Igor Babaev committer: Igor Babaev timestamp: 2020-12-04 14:20:52 -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.
Approved by Oleksandr Byelkin <sanja@mariadb.com>
--- 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, _______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
participants (2)
-
Igor Babaev -
IgorBabaev