Merge branch '10.3' into 10.4bb-10.4-MDEV-23468

1 files changed, 39 insertions, 4 deletions

diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result
index d23618344bd..ee2320ce0ea 100644
--- a/mysql-test/main/cte_nonrecursive.result
+++ b/mysql-test/main/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(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
+v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS with t(c) 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,6 +1690,41 @@ 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
 #
 # MDEV-21673: several references to CTE that uses