revision-id: 540e36ca0dc7471374502acaae67b2182ac82060 (mariadb-10.4.11-424-g540e36c) parent(s): a6f956488c712bef3b13660584d1b905e0c676cc author: Igor Babaev committer: Igor Babaev timestamp: 2020-12-26 14:13:12 -0800 message: MDEV-23886 Reusing CTE inside a function fails with table doesn't exist Intermediate commit (not to be pushed). --- mysql-test/main/brackets.result | 14 +- mysql-test/main/cte_nonrecursive.result | 64 +++++- mysql-test/main/cte_nonrecursive.test | 63 ++++++ mysql-test/main/cte_recursive.result | 279 +++++++++++++++++++++++- mysql-test/main/cte_recursive.test | 50 +++++ sql/item_subselect.cc | 1 - sql/sp_head.cc | 3 +- sql/sql_base.cc | 47 +--- sql/sql_class.cc | 56 +++++ sql/sql_class.h | 8 +- sql/sql_cte.cc | 374 ++++++++++++++++++++++---------- sql/sql_cte.h | 87 +++++++- sql/sql_derived.cc | 2 + sql/sql_lex.cc | 17 +- sql/sql_lex.h | 25 ++- sql/sql_parse.cc | 15 +- sql/sql_prepare.cc | 3 - sql/sql_select.cc | 5 +- sql/sql_union.cc | 42 ++-- sql/sql_view.cc | 9 - sql/sql_yacc.yy | 27 ++- sql/sql_yacc_ora.yy | 16 +- sql/table.cc | 1 + sql/table.h | 37 ++++ 24 files changed, 1002 insertions(+), 243 deletions(-) diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result index dedd9a2..1da5168 100644 --- a/mysql-test/main/brackets.result +++ b/mysql-test/main/brackets.result @@ -4397,7 +4397,7 @@ with t as (select * from t1 where a <=3) select * 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 as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3)select `t`.`a` AS `a` from `t` 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`.`a` <= 3)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci select * from v1; a 3 @@ -4413,7 +4413,7 @@ order by a desc limit 3 ) select a from t1 where a=4 union select a from t where a=7; 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`.`a` < 3 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3 order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 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`.`a` < 3 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3 order by `a` desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 latin1 latin1_swedish_ci select * from v1; a 4 @@ -4429,7 +4429,7 @@ order by a desc limit 3 ) (select a from t1 where a=4 union select a from t where a=7 order by a desc); 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`.`a` < 3) union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3) order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc 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`.`a` < 3) union (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3) order by `a` desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 @@ -4469,7 +4469,7 @@ order by 1 desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 order by a desc; 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) union (values (4),(7)) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(a) as (values (2),(1) union (values (4),(7)) order by 1 desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 @@ -4484,7 +4484,7 @@ order by 1 desc limit 3 ) select a from t1 where a=1 union select a from t where a=7 order by a desc; 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)) union (values (4),(7) order by 1 desc) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(a) as ((values (2),(1)) union (values (4),(7) order by 1 desc) order by 1 desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 @@ -4496,7 +4496,7 @@ s as (select * from t1 where a > 3) select a from t where a=1 union select a from s where a=7 order by a desc; 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`.`a` < 3), s as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc 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`.`a` < 3), s as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 @@ -4508,7 +4508,7 @@ s as (select * from t where a > 3) select a from t where a=1 union select a from s where a=7 order by a desc; 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 `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `t`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) `t` where `t`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc 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`.`a` < 3), s as (select `t`.`a` AS `a` from `t` where `t`.`a` > 3)select `t`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 1 diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index d236183..efc02db 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 @@ -1811,4 +1811,60 @@ set sql_mode="oracle"; with data as (select 1 as id) select id into @myid from data; set sql_mode= @save_sql_mode; +# +# MDEV-23886: Stored Function returning the result of a query +# that uses CTE over a table twice +# +create table t1 (c1 int); +insert into t1 values (1),(2),(6); +create function f1() returns int return +( with cte1 as (select c1 from t1) +select sum(c1) from +(select * from cte1 union all select * from cte1) dt +); +select f1(); +f1() +18 +create function f2() returns int return +( with cte1 as (select c1 from t1) +select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1 +); +select f2(); +f2() +9 +create function f3() returns int return +( with cte1 as (select c1 from t1) +select +case +when exists(select 1 from cte1 where c1 between 1 and 2) then 1 +when exists(select 1 from cte1 where c1 between 5 and 6) then 2 +else 0 +end +); +select f3(); +f3() +1 +drop function f1; +drop function f2; +drop function f3; +create table t2 (a int, b int); +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5; +select * from t2; +a b +6 6 +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | +call p1(); +select * from t2; +a b +6 6 +2 2 +drop procedure p1; +drop table t1,t2; # End of 10.4 tests diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test index 1f5b2a6..52e6d95 100644 --- a/mysql-test/main/cte_nonrecursive.test +++ b/mysql-test/main/cte_nonrecursive.test @@ -1318,4 +1318,67 @@ with data as (select 1 as id) select id into @myid from data; set sql_mode= @save_sql_mode; +--echo # +--echo # MDEV-23886: Stored Function returning the result of a query +--echo # that uses CTE over a table twice +--echo # + +create table t1 (c1 int); +insert into t1 values (1),(2),(6); + +create function f1() returns int return +( with cte1 as (select c1 from t1) + select sum(c1) from + (select * from cte1 union all select * from cte1) dt +); +select f1(); + +create function f2() returns int return +( with cte1 as (select c1 from t1) + select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1 +); +select f2(); + +create function f3() returns int return +( with cte1 as (select c1 from t1) + select + case + when exists(select 1 from cte1 where c1 between 1 and 2) then 1 + when exists(select 1 from cte1 where c1 between 5 and 6) then 2 + else 0 + end +); +select f3(); + +drop function f1; +drop function f2; +drop function f3; + +create table t2 (a int, b int); + +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5; + +select * from t2; + +delimiter |; + +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | + +delimiter ;| + +call p1(); +select * from t2; + +drop procedure p1; + +drop table t1,t2; + --echo # End of 10.4 tests + diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 7ed8a301..78e1e44 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/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 @@ -1301,7 +1301,7 @@ select ancestors.name, ancestors.dob from ancestors; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24 4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where -6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 +6 UNION <derived3> ALL NULL NULL NULL NULL 12 5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL 3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where @@ -4008,6 +4008,279 @@ YEAR d1 d2 DROP PROCEDURE p; DROP TABLE t1,t2,t3,t4; # +# MDEV-23619: recursive CTE used only in the second operand of UNION +# +create table t1 ( +a bigint(10) not null auto_increment, +b int(5) not null, +c bigint(10) default null, +primary key (a) +) engine myisam; +insert into t1 values +(1,3,12), (2,7,15), (3,1,3), (4,3,1); +explain with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DERIVED s ALL NULL NULL NULL NULL 4 +3 RECURSIVE UNION t1 ALL NULL NULL NULL NULL 4 Using where +3 RECURSIVE UNION <derived2> ref key0 key0 9 test.t1.c 2 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +4 UNION <derived2> ALL NULL NULL NULL NULL 4 +with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; +b +0 +3 +7 +1 +3 +analyze format=json with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<union1,4>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 4, + "operation": "UNION", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union2,3>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "s", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "t1.c is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "9", + "used_key_parts": ["a"], + "ref": ["test.t1.c"], + "r_loops": 4, + "rows": 2, + "r_rows": 0.5, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + ] + } + } + } + } + } + } + ] + } + } +} +prepare stmt from "with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t"; +execute stmt; +b +0 +3 +7 +1 +3 +execute stmt; +b +0 +3 +7 +1 +3 +deallocate prepare stmt; +#checking hanging cte that uses a recursive cte +explain with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tt ALL NULL NULL NULL NULL 4 +with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; +a b c +1 3 12 +2 7 15 +3 1 3 +4 3 1 +analyze format=json with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "tt", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } +} +prepare stmt from "with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt"; +execute stmt; +a b c +1 3 12 +2 7 15 +3 1 3 +4 3 1 +execute stmt; +a b c +1 3 12 +2 7 15 +3 1 3 +4 3 1 +deallocate prepare stmt; +drop table t1; +# +# MDEV-24019: query with recursive CTE when no default database is set +# +drop database test; +with recursive a as +(select 1 from dual union select * from a as r) +select * from a; +1 +1 +create database db1; +create table db1.t1 (a int); +insert into db1.t1 values (3), (7), (1); +with recursive cte as +(select * from db1.t1 union select * from (select * from cte) as t) +select * from cte; +a +3 +7 +1 +explain with recursive cte as +(select * from db1.t1 union select * from (select * from cte) as t) +select * from cte; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 +2 DERIVED t1 ALL NULL NULL NULL NULL 3 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 3 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +prepare stmt from "with recursive cte as +(select * from db1.t1 union select * from (select * from cte) as t) +select * from cte"; +execute stmt; +a +3 +7 +1 +execute stmt; +a +3 +7 +1 +deallocate prepare stmt; +drop database db1; +create database test; +use test; +# # End of 10.2 tests # # diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index 94da45b..58264ba 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -2676,6 +2676,56 @@ DROP PROCEDURE p; DROP TABLE t1,t2,t3,t4; --echo # +--echo # MDEV-23619: recursive CTE used only in the second operand of UNION +--echo # + +create table t1 ( + a bigint(10) not null auto_increment, + b int(5) not null, + c bigint(10) default null, + primary key (a) +) engine myisam; +insert into t1 values + (1,3,12), (2,7,15), (3,1,3), (4,3,1); + +let $q= +with recursive r_cte as +( select * from t1 as s + union + select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; + +eval explain $q; +eval $q; +--source include/analyze-format.inc +eval analyze format=json $q; +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +--echo #checking hanging cte that uses a recursive cte +let $q1= +with h_cte as +( with recursive r_cte as + ( select * from t1 as s + union + select t1.* from t1, r_cte as r where t1.c = r.a ) + select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; + +eval explain $q1; +eval $q1; +--source include/analyze-format.inc +eval analyze format=json $q1; +eval prepare stmt from "$q1"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 513aad7..da53bf6 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1722,7 +1722,6 @@ double Item_in_subselect::val_real() As far as Item_in_subselect called only from Item_in_optimizer this method should not be used */ - DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); if (forced_const) return value; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index e91364b..032b045 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -3411,8 +3411,7 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp, Json_writer_object trace_command(thd); Json_writer_array trace_command_steps(thd, "steps"); if (open_tables) - res= check_dependencies_in_with_clauses(m_lex->with_clauses_list) || - instr->exec_open_and_lock_tables(thd, m_lex->query_tables); + res= instr->exec_open_and_lock_tables(thd, m_lex->query_tables); if (likely(!res)) { diff --git a/sql/sql_base.cc b/sql/sql_base.cc index ede62b7..a340173 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -3690,7 +3690,11 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags, if (tables->derived) { if (!tables->view) + { + if (!tables->is_derived()) + tables->set_derived(); goto end; + } /* We restore view's name and database wiped out by derived tables processing and fall back to standard open process in order to @@ -3700,35 +3704,6 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags, tables->db= tables->view_db; tables->table_name= tables->view_name; } - else if (tables->select_lex) - { - /* - Check whether 'tables' refers to a table defined in a with clause. - If so set the reference to the definition in tables->with. - */ - if (!tables->with) - tables->with= tables->select_lex->find_table_def_in_with_clauses(tables); - /* - If 'tables' is defined in a with clause set the pointer to the - specification from its definition in tables->derived. - */ - if (tables->with) - { - if (tables->is_recursive_with_table() && - !tables->is_with_table_recursive_reference()) - { - tables->with->rec_outer_references++; - With_element *with_elem= tables->with; - while ((with_elem= with_elem->get_next_mutually_recursive()) != - tables->with) - with_elem->rec_outer_references++; - } - if (tables->set_as_with_table(thd, tables->with)) - DBUG_RETURN(1); - else - goto end; - } - } if (!tables->derived && is_infoschema_db(&tables->db)) { @@ -8089,21 +8064,21 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, /* Ensure that we have access rights to all fields to be inserted. Under some circumstances, this check may be skipped. - + - If any_privileges is true, skip the check. - + - If the SELECT privilege has been found as fulfilled already for both the TABLE and TABLE_LIST objects (and both of these exist, of course), the check is skipped. - + - If the SELECT privilege has been found fulfilled for the TABLE object and the TABLE_LIST represents a derived table other than a view (see below), the check is skipped. - + - If the TABLE_LIST object represents a view, we may skip checking if the SELECT privilege has been found fulfilled for it, regardless of the TABLE object. - + - If there is no TABLE object, the test is skipped if either * the TABLE_LIST does not represent a view, or * the SELECT privilege has been found fulfilled. @@ -8114,8 +8089,8 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, */ if (!((table && tables->is_non_derived() && (table->grant.privilege & SELECT_ACL)) || - ((!tables->is_non_derived() && - (tables->grant.privilege & SELECT_ACL)))) && + ((!tables->is_non_derived() && + (tables->grant.privilege & SELECT_ACL)))) && !any_privileges) { field_iterator.set(tables); diff --git a/sql/sql_class.cc b/sql/sql_class.cc index e57196e..46c90b3 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -2804,6 +2804,62 @@ void THD::close_active_vio() #endif +/* + @brief MySQL parser used for recursive invocations + + @param old_lex The LEX structure in the state when this parser + is called recursively + @param lex The LEX structure used to parse a new SQL fragment + @param str The SQL fragment to parse + @param str_len The length of the SQL fragment to parse + @param stmt_prepare_mode true <=> when parsing a prepare statement + + @details + This function is to be used when parsing of an SQL fragment is + needed within one of the grammar rules. + + @notes + Currently the function is used only when the specification of a CTE + is parsed for the not first and not recursive references of the CTE. + + @retval false On a succesful parsing of the fragment + @retval true Otherwise +*/ + +bool THD::sql_parser(LEX *old_lex, LEX *lex, + char *str, uint str_len, bool stmt_prepare_mode) +{ + extern int MYSQLparse(THD * thd); + extern int ORAparse(THD * thd); + + bool parse_status= false; + Parser_state parser_state; + Parser_state *old_parser_state= m_parser_state; + + if (parser_state.init(this, str, str_len)) + return true; + + m_parser_state= &parser_state; + parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode; + parser_state.m_lip.multi_statements= false; + parser_state.m_lip.m_digest= NULL; + + lex->param_list= old_lex->param_list; + lex->sphead= old_lex->sphead; + lex->spname= old_lex->spname; + lex->spcont= old_lex->spcont; + lex->sp_chistics= old_lex->sp_chistics; + + parse_status= (variables.sql_mode & MODE_ORACLE) ? + ORAparse(this) : MYSQLparse(this) != 0; + + m_parser_state= old_parser_state; + + return parse_status; +} + + + struct Item_change_record: public ilink { Item **place; diff --git a/sql/sql_class.h b/sql/sql_class.h index 64cd1ed..cb78e3c 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4232,14 +4232,11 @@ class THD: public THD_count, /* this must be first */ to resolve all CTE names as we don't need this message to be thrown for any CTE references. */ - if (!lex->with_clauses_list) + if (!lex->with_cte_resolution) { my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0)); return TRUE; } - /* This will allow to throw an error later for non-CTE references */ - to->str= NULL; - to->length= 0; return FALSE; } @@ -5027,6 +5024,9 @@ class THD: public THD_count, /* this must be first */ Item *sp_prepare_func_item(Item **it_addr, uint cols= 1); bool sp_eval_expr(Field *result_field, Item **expr_item_ptr); + bool sql_parser(LEX *old_lex, LEX *lex, + char *str, uint str_len, bool stmt_prepare_mode); + }; /** A short cut for thd->get_stmt_da()->set_ok_status(). */ diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index d4b6d81..4d2293b 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1,3 +1,4 @@ + /* Copyright (c) 2016, 2017 MariaDB @@ -83,7 +84,7 @@ void st_select_lex_unit::set_with_clause(With_clause *with_cl) true on failure */ -bool check_dependencies_in_with_clauses(With_clause *with_clauses_list) +bool LEX::check_dependencies_in_with_clauses() { for (With_clause *with_clause= with_clauses_list; with_clause; @@ -99,6 +100,165 @@ bool check_dependencies_in_with_clauses(With_clause *with_clauses_list) } +bool +LEX::resolve_references_to_cte_in_hanging_cte() +{ + for (With_clause *with_clause= with_clauses_list; + with_clause; with_clause= with_clause->next_with_clause) + { + for (With_element *with_elem= with_clause->with_list.first; + with_elem; with_elem= with_elem->next) + { + if (!with_elem->is_referenced()) + { + TABLE_LIST *first_tbl= + with_elem->spec->first_select()->table_list.first; + TABLE_LIST **with_elem_end_pos= with_elem->head->tables_pos.end_pos; + if (first_tbl && resolve_references_to_cte(first_tbl, with_elem_end_pos)) + return true; + } + } + } + return false; +} + + +/** + @brief + Resolve table references to CTE from a sunchain of table references + + @param tables Points to the beginning of the subchain + @param tables_last Points to the address with the subchain barrier + + @details + The method resolves tables references to CTE from the chain of + table refereces specified by the parameters 'tables' and 'tables_last'. + It resolves the references against the CTE definition occured in a query + or the specification of a CTE whose parsing tree is represented by + this LEX structure. The method is always called right after the process + of parsing the query or of the specification of a CTE has been finished, + thus the chain of table references used in the parsed fragment has been + already built. It is assumed that parameters of the methos specify a + a subchain of this chain. + If a table reference can be potentially a table reference to a CTE and it + has not been resolved yet then the method tries to find the definition + of the CTE against which the reference can be resolved. If it succeeds + it sets the field TABLE_LIST::with to point to the found definition. + It also sets the field TABLE_LIST::derived to point to the specification + of the found CTE and sets TABLE::db.str to empty_c_string. This will + allow to handle this table reference like a reference to a derived handle. + If another table reference has been already resolved against this CTE + and this CTE is not recursive then a clone of the CTE specification is + constructed using the function With_element::clone_parsed_spec() and + TABLE_LIST::derived is set to point to this clone rather than to the + original specification. + If the method does not find a matched CTE definition in the parsed fragment + then in the case when the flag this->only_cte_resolution is set to true + it just moves to the resolution of the next table reference from the + specified subchain while in the case when this->only_cte_resolution is set + to false the method additionally sets an mdl request for this table + reference. + + @notes + The flag this->only_cte_resolution is set to true in the cases when + the failure to resolve a table reference as a CTE reference within + the fragment assiciated with this LEX structure does not imply that + this table reference cannot be resolved as such at all. + + @retval false On success: no errors reported, no memory allocations failed + @retval true Otherwise + +*/ + +bool LEX::resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last) +{ + With_element *with_elem= 0; + + for (TABLE_LIST *tbl= tables; tbl != *tables_last; tbl= tbl->next_global) + { + if (tbl->derived) + continue; + if (!tbl->db.str && !tbl->with) + tbl->with= tbl->select_lex->find_table_def_in_with_clauses(tbl); + if (!tbl->with) // no CTE matches table reference tbl + { + if (only_cte_resolution) + continue; + if (!tbl->db.str) // no database specified in table reference tbl + { + if (!thd->db.str) // no default database is set + { + my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0)); + return true; + } + if (copy_db_to(&tbl->db)) + return true; + if (!(tbl->table_options & TL_OPTION_ALIAS)) + tbl->mdl_request.init(MDL_key::TABLE, tbl->db.str, + tbl->table_name.str, + tbl->mdl_type, MDL_TRANSACTION); + tbl->mdl_request.set_type((tbl->lock_type >= TL_WRITE_ALLOW_WRITE) ? + MDL_SHARED_WRITE : MDL_SHARED_READ); + } + continue; + } + with_elem= tbl->with; + if (tbl->is_recursive_with_table() && + !tbl->is_with_table_recursive_reference()) + { + tbl->with->rec_outer_references++; + while ((with_elem= with_elem->get_next_mutually_recursive()) != + tbl->with) + with_elem->rec_outer_references++; + } + if (!with_elem->is_used_in_query || with_elem->is_recursive) + { + tbl->derived= with_elem->spec; + if (tbl->derived != tbl->select_lex->master_unit() && + !with_elem->is_recursive && + !tbl->is_with_table_recursive_reference()) + { + tbl->derived->move_as_slave(tbl->select_lex); + } + with_elem->is_used_in_query= true; + } + else + { + if (!(tbl->derived= tbl->with->clone_parsed_spec(thd->lex, tbl))) + return true; + } + tbl->db.str= empty_c_string; + tbl->db.length= 0; + tbl->schema_table= 0; + if (tbl->derived) + { + tbl->derived->first_select()->set_linkage(DERIVED_TABLE_TYPE); + tbl->select_lex->add_statistics(tbl->derived); + } + if (tbl->with->is_recursive && tbl->is_with_table_recursive_reference()) + continue; + with_elem->inc_references(); + } + return false; +} + + +bool +LEX::check_cte_dependencies_and_resolve_references() +{ + if (check_dependencies_in_with_clauses()) + return true; + if (!with_cte_resolution) + return false; + if (resolve_references_to_cte(query_tables, query_tables_last)) + return true; + if (resolve_references_to_cte_in_hanging_cte()) + return true; + return false; +} + + /** @brief Check dependencies between tables defined in this with clause @@ -137,10 +297,11 @@ bool With_clause::check_dependencies() elem != with_elem; elem= elem->next) { - if (lex_string_cmp(system_charset_info, with_elem->query_name, - elem->query_name) == 0) + if (lex_string_cmp(system_charset_info, with_elem->get_name(), + elem->get_name()) == 0) { - my_error(ER_DUP_QUERY_NAME, MYF(0), with_elem->query_name->str); + my_error(ER_DUP_QUERY_NAME, MYF(0), + with_elem->get_name_str()); return true; } } @@ -247,11 +408,12 @@ With_element *With_clause::find_table_def(TABLE_LIST *table, with_elem != barrier; with_elem= with_elem->next) { - if (my_strcasecmp(system_charset_info, with_elem->query_name->str, - table->table_name.str) == 0 && + if (my_strcasecmp(system_charset_info, with_elem->get_name_str(), + table->table_name.str) == 0 && !table->is_fqtn) { table->set_derived(); + with_elem->referenced= true; return with_elem; } } @@ -608,7 +770,7 @@ bool With_clause::check_anchors() if (elem == with_elem) { my_error(ER_RECURSIVE_WITHOUT_ANCHORS, MYF(0), - with_elem->query_name->str); + with_elem->get_name_str()); return true; } } @@ -641,7 +803,7 @@ bool With_clause::check_anchors() if (elem->work_dep_map & elem->get_elem_map()) { my_error(ER_UNACCEPTABLE_MUTUAL_RECURSION, MYF(0), - with_elem->query_name->str); + with_elem->get_name_str()); return true; } } @@ -795,9 +957,10 @@ bool With_element::set_unparsed_spec(THD *thd, @brief Create a clone of the specification for the given with table - @param thd The context of the statement containing this with element + @param old_lex The LEX structure created for the query or CTE specification + where this With_element is defined @param with_table The reference to the table defined in this element for which - the clone is created. + the clone is created. @details The method creates a clone of the specification used in this element. @@ -805,12 +968,13 @@ bool With_element::set_unparsed_spec(THD *thd, this element. The clone is created when the string with the specification saved in unparsed_spec is fed into the parser as an input string. The parsing - this string a unit object representing the specification is build. + this string a unit object representing the specification is built. A chain of all table references occurred in the specification is also formed. The method includes the new unit and its sub-unit into hierarchy of the units of the main query. I also insert the constructed chain of the table references into the chain of all table references of the main query. + The method resolves all references to CTE in the clone. @note Clones is created only for not first references to tables defined in @@ -826,99 +990,127 @@ bool With_element::set_unparsed_spec(THD *thd, NULL - otherwise */ -st_select_lex_unit *With_element::clone_parsed_spec(THD *thd, +st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, TABLE_LIST *with_table) { + THD *thd= old_lex->thd; LEX *lex; - st_select_lex_unit *res= NULL; - Query_arena backup; - Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); + st_select_lex_unit *res= NULL; if (!(lex= (LEX*) new(thd->mem_root) st_lex_local)) - { - if (arena) - thd->restore_active_arena(arena, &backup); return res; - } - LEX *old_lex= thd->lex; thd->lex= lex; bool parse_status= false; - Parser_state parser_state; - TABLE_LIST *spec_tables; - TABLE_LIST *spec_tables_tail; st_select_lex *with_select; char save_end= unparsed_spec.str[unparsed_spec.length]; ((char*) &unparsed_spec.str[unparsed_spec.length])[0]= '\0'; - if (parser_state.init(thd, (char*) unparsed_spec.str, (unsigned int)unparsed_spec.length)) - goto err; - parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode; - parser_state.m_lip.multi_statements= false; - parser_state.m_lip.m_digest= NULL; lex_start(thd); lex->clone_spec_offset= unparsed_spec_offset; - lex->param_list= old_lex->param_list; - lex->sphead= old_lex->sphead; - lex->spname= old_lex->spname; - lex->spcont= old_lex->spcont; - lex->sp_chistics= old_lex->sp_chistics; - - lex->stmt_lex= old_lex; - parse_status= parse_sql(thd, &parser_state, 0); + lex->with_cte_resolution= true; + + /* + The specification of a CTE is to be parsed as a regular query. + At the very end of the parsing query the function + check_cte_dependencies_and_resolve_references() will be called. + It will check the dependencies between CTEs that are defined + within the query and will resolve CTE references in this query. + If a table reference is not resolved as a CTE reference within + this query it still can be resolved as a reference to a CTE defined + in the same clause as the CTE whose specification is to be parsed + or defined in an embedding CTE definition. + + Example: + with + cte1 as ( ... ), + cte2 as ([WITH ...] select ... from cte1 ...) + select ... from cte2 as r, ..., cte2 as s ... + + Here the specification of cte2 has be cloned for table reference + with alias s1. The specification contains a reference to cte1 + that is defined outside this specification. If the reference to + cte1 cannot be resolved within the specification of cte2 it's + not necessarily has to be a reference to a non-CTE table. That's + why the flag lex->only_cte_resolution has to be set to true + before parsing of the specification of cte2 invoked by this + function starts. Otherwise an mdl_lock would be requested for s + and this would not be correct + */ + + lex->only_cte_resolution= true; + + lex->stmt_lex= old_lex->stmt_lex ? old_lex->stmt_lex : old_lex; + + parse_status= thd->sql_parser(old_lex, lex, + (char*) unparsed_spec.str, + (unsigned int)unparsed_spec.length, + stmt_prepare_mode); + ((char*) &unparsed_spec.str[unparsed_spec.length])[0]= save_end; with_select= lex->first_select_lex(); if (parse_status) goto err; - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - goto err; - - spec_tables= lex->query_tables; - spec_tables_tail= 0; - for (TABLE_LIST *tbl= spec_tables; - tbl; - tbl= tbl->next_global) - { - if (!tbl->derived && !tbl->schema_table && - thd->open_temporary_table(tbl)) - goto err; - spec_tables_tail= tbl; - } - if (check_table_access(thd, SELECT_ACL, spec_tables, FALSE, UINT_MAX, FALSE)) - goto err; - if (spec_tables) + /* + The global chain of TABLE_LIST objects created for the specification that + just has been parsed is added to such chain that contains the reference + to the CTE whose specification is parsed right after the TABLE_LIST object + created for the reference. + */ + if (lex->query_tables) { - if (with_table->next_global) + head->tables_pos.set_start_pos(&with_table->next_global); + head->tables_pos.set_end_pos(lex->query_tables_last); + TABLE_LIST *next_tbl= with_table->next_global; + if (next_tbl) { - spec_tables_tail->next_global= with_table->next_global; - with_table->next_global->prev_global= &spec_tables_tail->next_global; + *(lex->query_tables->prev_global= next_tbl->prev_global)= + lex->query_tables; + *(next_tbl->prev_global= lex->query_tables_last)= next_tbl; } else { - old_lex->query_tables_last= &spec_tables_tail->next_global; + *(lex->query_tables->prev_global= old_lex->query_tables_last)= + lex->query_tables; + old_lex->query_tables_last= lex->query_tables_last; } - spec_tables->prev_global= &with_table->next_global; - with_table->next_global= spec_tables; } res= &lex->unit; + /* + The unit of the specification that just has been parsed is included + as a slave of the select that contained in its from list the table + reference for which the unit has been created. + */ lex->unit.include_down(with_table->select_lex); - lex->unit.set_slave(with_select); + lex->unit.set_slave(with_select); + lex->unit.cloned_from= spec; old_lex->all_selects_list= (st_select_lex*) (lex->all_selects_list-> insert_chain_before( (st_select_lex_node **) &(old_lex->all_selects_list), with_select)); - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) + + /* + Now all references to the CTE defined outside of the cloned specification + has to be resolved. Additionally if old_lex->only_cte_resolution == false + for the table references that has not been resolved requests for mdl_locks + has to be set. + */ + lex->only_cte_resolution= old_lex->only_cte_resolution; + if (lex->resolve_references_to_cte(lex->query_tables, + lex->query_tables_last)) + { res= NULL; + goto err; + } + lex->sphead= NULL; // in order not to delete lex->sphead lex_end(lex); err: - if (arena) - thd->restore_active_arena(arena, &backup); thd->lex= old_lex; return res; } @@ -1088,58 +1280,6 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) } -/** - @brief - Set the specifying unit in this reference to a with table - - @details - The method assumes that the given element with_elem defines the table T - this table reference refers to. - If this is the first reference to T the method just sets its specification - in the field 'derived' as the unit that yields T. Otherwise the method - first creates a clone specification and sets rather this clone in this field. - - @retval - false on success - true on failure -*/ - -bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) -{ - if (table) - { - /* - This table was prematurely identified as a temporary table. - We correct it here, but it's not a nice solution in the case - when the temporary table with this name is not used anywhere - else in the query. - */ - thd->mark_tmp_table_as_free_for_reuse(table); - table= 0; - } - with= with_elem; - schema_table= NULL; - if (!with_elem->is_referenced() || with_elem->is_recursive) - { - derived= with_elem->spec; - if (derived != select_lex->master_unit() && - !with_elem->is_recursive && - !is_with_table_recursive_reference()) - { - derived->move_as_slave(select_lex); - } - } - else - { - if(!(derived= with_elem->clone_parsed_spec(thd, this))) - return true; - } - derived->first_select()->set_linkage(DERIVED_TABLE_TYPE); - select_lex->add_statistics(derived); - with_elem->inc_references(); - return false; -} - bool TABLE_LIST::is_recursive_with_table() { @@ -1240,7 +1380,7 @@ bool st_select_lex::check_unrestricted_recursive(bool only_standard_compliant) if (only_standard_compliant && with_elem->is_unrestricted()) { my_error(ER_NOT_STANDARD_COMPLIANT_RECURSIVE, - MYF(0), with_elem->query_name->str); + MYF(0), with_elem->get_name_str()); return true; } @@ -1440,7 +1580,7 @@ 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); + str->append(get_name()); if (column_list.elements) { List_iterator_fast<LEX_CSTRING> li(column_list); diff --git a/sql/sql_cte.h b/sql/sql_cte.h index 80d5664..abd8852 100644 --- a/sql/sql_cte.h +++ b/sql/sql_cte.h @@ -23,6 +23,38 @@ class select_unit; struct st_unit_ctxt_elem; +/** + @class With_element_head + @brief Head of the definition of a CTE table + + It contains the name of the CTE and it contains the position of the subchain + of table references used in the definition in the global chain of table + references used in the query where this definition is encountered. +*/ + +class With_element_head : public Sql_alloc +{ + /* The name of the defined CTE */ + LEX_CSTRING *query_name; + +public: + /* + The structure describing the subchain of the table references used in + the specification of the defined CTE in the global chain of table + references used in the query. The structure is fully defined only + after the CTE definition has been parsed. + */ + TABLE_CHAIN tables_pos; + + With_element_head(LEX_CSTRING *name) + : query_name(name) + { + tables_pos.set_start_pos(0); + tables_pos.set_end_pos(0); + } + friend class With_element; +}; + /** @class With_element @@ -85,9 +117,22 @@ class With_element : public Sql_alloc subqueries and specifications of other with elements). */ uint references; + + /* + true <=> this With_element is referred in the query in which the + element is defined + */ + bool referenced; + + /* + true <=> this With_element is needed for the execution of the query + in which the element is defined + */ + bool is_used_in_query; + /* Unparsed specification of the query that specifies this element. - It used to build clones of the specification if they are needed. + It's used to build clones of the specification if they are needed. */ LEX_CSTRING unparsed_spec; /* Offset of the specification in the input string */ @@ -101,10 +146,12 @@ class With_element : public Sql_alloc public: /* - The name of the table introduced by this with elememt. The name - can be used in FROM lists of the queries in the scope of the element. + Contains the name of the defined With element and the position of + the subchain of the tables references used by its definition in the + global chain of TABLE_LIST objects created for the whole query. */ - LEX_CSTRING *query_name; + With_element_head *head; + /* Optional list of column names to name the columns of the table introduced by this with element. It is used in the case when the names are not @@ -162,18 +209,27 @@ class With_element : public Sql_alloc /* List of derived tables containing recursive references to this CTE */ SQL_I_List<TABLE_LIST> derived_with_rec_ref; - With_element(LEX_CSTRING *name, + With_element(With_element_head *h, List <LEX_CSTRING> list, st_select_lex_unit *unit) : next(NULL), base_dep_map(0), derived_dep_map(0), sq_dep_map(0), work_dep_map(0), mutually_recursive(0), top_level_dep_map(0), sq_rec_ref(NULL), next_mutually_recursive(NULL), references(0), - query_name(name), column_list(list), spec(unit), + referenced(false), is_used_in_query(false), + head(h), column_list(list), spec(unit), is_recursive(false), rec_outer_references(0), with_anchor(false), level(0), rec_result(NULL) { unit->with_element= this; } + LEX_CSTRING *get_name() { return head->query_name; } + const char *get_name_str() { return get_name()->str; } + + void set_tables_start_pos(TABLE_LIST **pos) + { head->tables_pos.set_start_pos(pos); } + void set_tables_end_pos(TABLE_LIST **pos) + { head->tables_pos.set_end_pos(pos); } + bool check_dependencies_in_spec(); void check_dependencies_in_select(st_select_lex *sl, st_unit_ctxt_elem *ctxt, @@ -200,9 +256,9 @@ class With_element : public Sql_alloc bool set_unparsed_spec(THD *thd, const char *spec_start, const char *spec_end, my_ptrdiff_t spec_offset); - st_select_lex_unit *clone_parsed_spec(THD *thd, TABLE_LIST *with_table); + st_select_lex_unit *clone_parsed_spec(LEX *old_lex, TABLE_LIST *with_table); - bool is_referenced() { return references != 0; } + bool is_referenced() { return referenced; } void inc_references() { references++; } @@ -260,6 +316,12 @@ class With_element : public Sql_alloc void prepare_for_next_iteration(); friend class With_clause; + + friend + bool LEX::resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last); + friend + bool LEX::resolve_references_to_cte_in_hanging_cte(); }; const uint max_number_of_elements_in_with_clause= sizeof(table_map)*8; @@ -291,6 +353,7 @@ class With_clause : public Sql_alloc in the current statement */ With_clause *next_with_clause; + /* Set to true if dependencies between with elements have been checked */ bool dependencies_are_checked; /* @@ -338,7 +401,7 @@ class With_clause : public Sql_alloc void attach_to(st_select_lex *select_lex); With_clause *pop() { return embedding_with_clause; } - + bool check_dependencies(); bool check_anchors(); @@ -357,9 +420,11 @@ class With_clause : public Sql_alloc friend class With_element; + friend + bool LEX::check_dependencies_in_with_clauses(); + friend - bool - check_dependencies_in_with_clauses(With_clause *with_clauses_list); + bool LEX::resolve_references_to_cte_in_hanging_cte(); }; inline diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 3b31222..b37c29e 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -916,6 +916,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) #ifndef NO_EMBEDDED_ACCESS_CHECKS if (derived->is_view()) table->grant= derived->grant; +#if 1 else { DBUG_ASSERT(derived->is_derived()); @@ -924,6 +925,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) derived->grant.privilege= SELECT_ACL; } #endif +#endif /* Add new temporary table to list of open derived tables */ if (!derived->is_with_table_recursive_reference()) { diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index cbb1138..54c3d87 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -721,6 +721,8 @@ void LEX::start(THD *thd_arg) explain_json= false; context_analysis_only= 0; derived_tables= 0; + with_cte_resolution= false; + only_cte_resolution= false; safe_to_cache_query= 1; parsing_options.reset(); empty_field_list_on_rset= 0; @@ -2388,6 +2390,7 @@ void st_select_lex_unit::init_query() is_view= false; with_clause= 0; with_element= 0; + cloned_from= 0; columns_are_renamed= false; intersect_mark= NULL; with_wrapped_tvc= false; @@ -4829,9 +4832,11 @@ void st_select_lex::set_explain_type(bool on_the_fly) /* pos_in_table_list=NULL for e.g. post-join aggregation JOIN_TABs. */ - if (tab->table && tab->table->pos_in_table_list && - tab->table->pos_in_table_list->with && - tab->table->pos_in_table_list->with->is_recursive) + if (!(tab->table && tab->table->pos_in_table_list)) + continue; + TABLE_LIST *tbl= tab->table->pos_in_table_list; + if (tbl->with && tbl->with->is_recursive && + tbl->is_with_table_recursive_reference()) { uses_cte= true; break; @@ -8262,6 +8267,8 @@ bool LEX::check_main_unit_semantics() if (unit.set_nest_level(0) || unit.check_parameters(first_select_lex())) return TRUE; + if (check_cte_dependencies_and_resolve_references()) + return TRUE; return FALSE; } @@ -8944,8 +8951,8 @@ void st_select_lex::add_statistics(SELECT_LEX_UNIT *unit) bool LEX::main_select_push(bool service) { DBUG_ENTER("LEX::main_select_push"); - current_select_number= 1; - builtin_select.select_number= 1; + current_select_number= ++thd->lex->stmt_lex->current_select_number; + builtin_select.select_number= current_select_number; builtin_select.is_service_select= service; if (push_select(&builtin_select)) DBUG_RETURN(TRUE); diff --git a/sql/sql_lex.h b/sql/sql_lex.h index c8fca74..61199c0 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -917,6 +917,8 @@ class st_select_lex_unit: public st_select_lex_node { With_clause *with_clause; /* With element where this unit is used as the specification (if any) */ With_element *with_element; + /* The unit used as a CTE specification from which this unit is cloned */ + st_select_lex_unit *cloned_from; /* thread handler */ THD *thd; /* @@ -1492,7 +1494,9 @@ class st_select_lex: public st_select_lex_node } With_element *get_with_element() { - return master_unit()->with_element; + return master_unit()->cloned_from ? + master_unit()->cloned_from->with_element : + master_unit()->with_element; } With_element *find_table_def_in_with_clauses(TABLE_LIST *table); bool check_unrestricted_recursive(bool only_standard_compliant); @@ -3314,6 +3318,20 @@ struct LEX: public Query_tables_list */ uint8 derived_tables; uint8 context_analysis_only; + /* + true <=> The parsed fragment requires resolution of references to CTE + at the end of parsing. This name resolution process involves searching + for possible dependencies between CTE defined in the parsed fragment and + detecting possible recursive references. + The flag is set to true if the fragment contains CTE definitions. + */ + bool with_cte_resolution; + /* + true <=> only resolution of references to CTE are required in the parsed + fragment, no checking of dependecies between CTE is required. + This flag is used only when parsing clones of CTE specifications. + */ + bool only_cte_resolution; bool local_file; bool check_exists; bool autocommit; @@ -4541,6 +4559,11 @@ struct LEX: public Query_tables_list select_stack[0]->is_service_select); } + bool check_dependencies_in_with_clauses(); + bool resolve_references_to_cte_in_hanging_cte(); + bool check_cte_dependencies_and_resolve_references(); + bool resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last); }; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 909cb24..90e998f 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3362,9 +3362,6 @@ mysql_execute_command(THD *thd) thd->get_stmt_da()->opt_clear_warning_info(thd->query_id); } - if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) - DBUG_RETURN(1); - #ifdef HAVE_REPLICATION if (unlikely(thd->slave_thread)) { @@ -8088,7 +8085,7 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, ptr->is_fqtn= TRUE; ptr->db= table->db; } - else if (lex->copy_db_to(&ptr->db)) + else if (!lex->with_cte_resolution && lex->copy_db_to(&ptr->db)) DBUG_RETURN(0); else ptr->is_fqtn= FALSE; @@ -8105,7 +8102,9 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, } ptr->table_name= table->table; - ptr->lock_type= lock_type; + ptr->lock_type= lock_type; + ptr->mdl_type= mdl_type; + ptr->table_options= table_options; ptr->updating= MY_TEST(table_options & TL_OPTION_UPDATING); /* TODO: remove TL_OPTION_FORCE_INDEX as it looks like it's not used */ ptr->force_index= MY_TEST(table_options & TL_OPTION_FORCE_INDEX); @@ -8787,8 +8786,10 @@ void st_select_lex::set_lock_for_tables(thr_lock_type lock_type, bool for_update { tables->lock_type= lock_type; tables->updating= for_update; - tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ? - MDL_SHARED_WRITE : MDL_SHARED_READ); + + if (tables->db.str && tables->db.str[0]) + tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ? + MDL_SHARED_WRITE : MDL_SHARED_READ); } DBUG_VOID_RETURN; } diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index e5b178b..25ceed0 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2303,9 +2303,6 @@ static bool check_prepared_statement(Prepared_statement *stmt) if (tables) thd->get_stmt_da()->opt_clear_warning_info(thd->query_id); - if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) - goto error; - if (sql_command_flags[sql_command] & CF_HA_CLOSE) mysql_ha_rm_tables(thd, tables); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ee166c3..9b1e3f6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -13717,6 +13717,9 @@ void JOIN::join_free() for (tmp_unit= select_lex->first_inner_unit(); tmp_unit; tmp_unit= tmp_unit->next_unit()) + { + if (tmp_unit->with_element && tmp_unit->with_element->is_recursive) + continue; for (sl= tmp_unit->first_select(); sl; sl= sl->next_select()) { Item_subselect *subselect= sl->master_unit()->item; @@ -13734,7 +13737,7 @@ void JOIN::join_free() /* Can't unlock if at least one JOIN is still needed */ can_unlock= can_unlock && full_local; } - + } /* We are not using tables anymore Unlock all tables. We may be in an INSERT .... SELECT statement. diff --git a/sql/sql_union.cc b/sql/sql_union.cc index a487f4d..994e366 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1900,13 +1900,7 @@ bool st_select_lex_unit::cleanup() { DBUG_RETURN(FALSE); } - /* - When processing a PS/SP or an EXPLAIN command cleanup of a unit can - be performed immediately when the unit is reached in the cleanup - traversal initiated by the cleanup of the main unit. - */ - if (!thd->stmt_arena->is_stmt_prepare() && !thd->lex->describe && - with_element && with_element->is_recursive && union_result) + if (with_element && with_element->is_recursive && union_result) { select_union_recursive *result= with_element->rec_result; if (++result->cleanup_count == with_element->rec_outer_references) @@ -2088,27 +2082,31 @@ bool st_select_lex::cleanup() if (join) { + List_iterator<TABLE_LIST> ti(leaf_tables); + TABLE_LIST *tbl; + while ((tbl= ti++)) + { + if (tbl->is_recursive_with_table() && + !tbl->is_with_table_recursive_reference()) + { + /* + If query is killed before open_and_process_table() for tbl + is called then 'with' is already set, but 'derived' is not. + */ + st_select_lex_unit *unit= tbl->with->spec; + error|= (bool) error | (uint) unit->cleanup(); + } + } DBUG_ASSERT((st_select_lex*)join->select_lex == this); error= join->destroy(); delete join; join= 0; } - for (TABLE_LIST *tbl= get_table_list(); tbl; tbl= tbl->next_local) - { - if (tbl->is_recursive_with_table() && - !tbl->is_with_table_recursive_reference()) - { - /* - If query is killed before open_and_process_table() for tbl - is called then 'with' is already set, but 'derived' is not. - */ - st_select_lex_unit *unit= tbl->with->spec; - error|= (bool) error | (uint) unit->cleanup(); - } - } for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ; lex_unit= lex_unit->next_unit()) { + if (lex_unit->with_element && lex_unit->with_element->is_recursive) + continue; error= (bool) ((uint) error | (uint) lex_unit->cleanup()); } inner_refs_list.empty(); @@ -2128,8 +2126,12 @@ void st_select_lex::cleanup_all_joins(bool full) join->cleanup(full); for (unit= first_inner_unit(); unit; unit= unit->next_unit()) + { + if (unit->with_element && unit->with_element->is_recursive) + continue; for (sl= unit->first_select(); sl; sl= sl->next_select()) sl->cleanup_all_joins(full); + } DBUG_VOID_RETURN; } diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 0a18a85..f64872a 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -429,12 +429,6 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views, lex->link_first_table_back(view, link_to_local); view->open_type= OT_BASE_ONLY; - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - { - res= TRUE; - goto err; - } - WSREP_TO_ISOLATION_BEGIN(WSREP_MYSQL_DB, NULL, NULL); /* @@ -1411,9 +1405,6 @@ bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table, TABLE_LIST *tbl; Security_context *security_ctx= 0; - if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) - goto err; - /* Check rights to run commands which show underlying tables. In the optimizer trace we would not like to show trace for diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 83323e3..f70d72d 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -762,6 +762,7 @@ Virtual_column_info *add_virtual_expression(THD *thd, Item *expr) class sp_head *sphead; class sp_name *spname; class sp_variable *spvar; + class With_element_head *with_element_head; class With_clause *with_clause; class Virtual_column_info *virtual_column; @@ -2190,7 +2191,7 @@ END_OF_INPUT %type <with_clause> with_clause -%type <lex_str_ptr> query_name +%type <with_element_head> with_element_head %type <lex_str_list> opt_with_column_list @@ -3338,7 +3339,11 @@ call: if (unlikely(Lex->call_statement_start(thd, $2))) MYSQL_YYABORT; } - opt_sp_cparam_list {} + opt_sp_cparam_list + { + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; + } ; /* CALL parameters */ @@ -4187,6 +4192,8 @@ sp_proc_stmt_return: LEX *lex= Lex; sp_head *sp= lex->sphead; Lex->pop_select(); //main select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; if (unlikely(sp->m_handler->add_instr_freturn(thd, sp, lex->spcont, $3, lex)) || unlikely(sp->restore_lex(thd))) @@ -13332,6 +13339,8 @@ do: { Lex->insert_list= $3; Lex->pop_select(); //main select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; } ; @@ -15494,6 +15503,7 @@ with_clause: if (unlikely(with_clause == NULL)) MYSQL_YYABORT; lex->derived_tables|= DERIVED_WITH; + lex->with_cte_resolution= true; lex->curr_with_clause= with_clause; with_clause->add_to_list(Lex->with_clauses_list_last_next); if (lex->current_select && @@ -15521,7 +15531,7 @@ with_list: with_list_element: - query_name + with_element_head opt_with_column_list { $2= new List<LEX_CSTRING> (Lex->with_column_list); @@ -15541,6 +15551,7 @@ with_list_element: if (elem->set_unparsed_spec(thd, spec_start, $7.pos(), spec_start - query_start)) MYSQL_YYABORT; + elem->set_tables_end_pos(lex->query_tables_last); } ; @@ -15567,16 +15578,18 @@ with_column_list: ; -query_name: +with_element_head: ident { - $$= (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); - if (unlikely($$ == NULL)) + LEX_CSTRING *name= + (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); + $$= new (thd->mem_root) With_element_head(name); + if (unlikely(name == NULL || $$ == NULL)) MYSQL_YYABORT; + $$->tables_pos.set_start_pos(Lex->query_tables_last); } ; - /********************************************************************** ** Creating different items. diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index c8084f8..bd80c2e 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -240,6 +240,7 @@ void ORAerror(THD *thd, const char *s) class sp_head *sphead; class sp_name *spname; class sp_variable *spvar; + class With_element_head *with_element_head; class With_clause *with_clause; class Virtual_column_info *virtual_column; @@ -1691,7 +1692,7 @@ END_OF_INPUT %type <with_clause> with_clause -%type <lex_str_ptr> query_name +%type <with_element_head> with_element_head %type <lex_str_list> opt_with_column_list @@ -15621,6 +15622,7 @@ with_clause: if (unlikely(with_clause == NULL)) MYSQL_YYABORT; lex->derived_tables|= DERIVED_WITH; + lex->with_cte_resolution= true; lex->curr_with_clause= with_clause; with_clause->add_to_list(Lex->with_clauses_list_last_next); if (lex->current_select && @@ -15648,7 +15650,7 @@ with_list: with_list_element: - query_name + with_element_head opt_with_column_list { $2= new List<LEX_CSTRING> (Lex->with_column_list); @@ -15668,6 +15670,7 @@ with_list_element: if (elem->set_unparsed_spec(thd, spec_start, $7.pos(), spec_start - query_start)) MYSQL_YYABORT; + elem->set_tables_end_pos(lex->query_tables_last); } ; @@ -15694,12 +15697,15 @@ with_column_list: ; -query_name: +with_element_head: ident { - $$= (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); - if (unlikely($$ == NULL)) + LEX_CSTRING *name= + (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); + $$= new (thd->mem_root) With_element_head(name); + if (unlikely(name == NULL || $$ == NULL)) MYSQL_YYABORT; + $$->tables_pos.set_start_pos(Lex->query_tables_last); } ; diff --git a/sql/table.cc b/sql/table.cc index 38777de..80bed60 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -9144,6 +9144,7 @@ void TABLE_LIST::set_lock_type(THD *thd, enum thr_lock_type lock) } } + bool TABLE_LIST::is_with_table() { return derived && derived->with_element; diff --git a/sql/table.h b/sql/table.h index 15ab359..e90aa0a 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2139,6 +2139,29 @@ struct vers_select_conds_t struct LEX; class Index_hint; + +/* + @struct TABLE_CHAIN + @brief Subchain of global chain of table references + + The structure contains a pointer to the address of the next_global + pointer to the first TABLE_LIST objectof the subchain and the address + of the next_global pointer to the element right after the last + TABLE_LIST object of the subchain. For an empty subchain both pointers + have the same value. +*/ + +struct TABLE_CHAIN +{ + TABLE_CHAIN() {} + + TABLE_LIST **start_pos; + TABLE_LIST ** end_pos; + + void set_start_pos(TABLE_LIST **pos) { start_pos= pos; } + void set_end_pos(TABLE_LIST **pos) { end_pos= pos; } +}; + struct TABLE_LIST { TABLE_LIST() {} /* Remove gcc warning */ @@ -2474,6 +2497,20 @@ struct TABLE_LIST /* call back function for asking handler about caching in query cache */ qc_engine_callback callback_func; thr_lock_type lock_type; + + /* + Two fields below are set during parsing this table reference in the cases + when the table reference can be potentially a reference to a CTE table. + In this cases the fact that the reference is a reference to a CTE or not + will be ascertained at the very end of parsing of the query when referencies + to CTE are resolved. For references to CTE and to derived tables no mdl + requests are needed while for other table references they are. If a request + is possibly postponed the info that allows to issue this request must be + saved in 'mdl_type' and 'table_options'. + */ + enum_mdl_type mdl_type; + ulong table_options; + uint outer_join; /* Which join type */ uint shared; /* Used in multi-upd */ bool updatable; /* VIEW/TABLE can be updated now */