diff options
| -rw-r--r-- | mysql-test/main/cte_recursive.result | 586 | ||||
| -rw-r--r-- | mysql-test/main/cte_recursive.test | 664 | ||||
| -rw-r--r-- | sql/sql_cte.cc | 100 | ||||
| -rw-r--r-- | sql/sql_cte.h | 20 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 3 | ||||
| -rw-r--r-- | sql/sql_yacc_ora.yy | 3 |
6 files changed, 1341 insertions, 35 deletions
diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index bf95ba11a7d..7f535fc3125 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -5017,5 +5017,591 @@ t2 CREATE TABLE `t2` ( set @@sql_mode=default; drop table t1,t2; # +# MDEV-29361: Embedded recursive / non-recursive CTE within +# the scope of another embedded CTE with the same name +# +create table t1 (a int); +insert into t1 values (4), (5); +create table t2 (a int); +insert into t2 values (6), (8); +create table t3 (a int); +insert into t3 values (1), (9); +with recursive +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1; +a +4 +5 +6 +7 +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2; +a +6 +8 +10 +with +cte as +( +with recursive +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a in ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +a +6 +with +cte as +( +with recursive +x(a) as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select s1.a from x as s1, x +where s1.a = x.a and +x.a in ( +with recursive +x(a) as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +a +6 +with +cte as +( +with +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a in ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +ERROR 42S02: Table 'test.x' doesn't exist +with +cte as +( +with recursive +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +a +6 +7 +with +cte as +( +with +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +ERROR 42S02: Table 'test.x' doesn't exist +with +cte as +( +with recursive +y as +( +select a from t1 union select a+1 from y as r1 where a < 7 +) +select * from y as s1 +where s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +ERROR 42S02: Table 'test.x' doesn't exist +with +cte as +( +with +y(a) as +( +select a+5 from t1 +) +select * from y as s1 +where s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +ERROR 42S02: Table 'test.x' doesn't exist +with +cte as +( +select ( +with +x as +( +select a from x as r1 +) +select * from x as s1 +where s1.a in ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) as r +from t3 +) +select * from cte; +ERROR 42S02: Table 'test.x' doesn't exist +with +cte as +( +select ( +with +x as +( +select a from x as r1 +) +select * from x as s1 +where s1.a < 5 and +s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) as r +from t3 +) +select * from cte; +ERROR 42S02: Table 'test.x' doesn't exist +with +cte as +( +select ( +with recursive +x(a) as +( +select a+3 from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a < 8 and +s1.a in ( +with recursive +x(a) as +( +select a-2 from t2 +union +select a+1 from x as r2 where a < 10 +) +select a from x as s2 +) +) as r +from t3 +) +select * from cte; +r +7 +7 +with +cte as +( +select ( +with recursive +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a in ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) as r +from t3 +) +select * from cte; +r +6 +6 +create table x (a int); +insert into x values (3), (7), (1), (5), (6); +with +cte as +( +select ( +with +x as +( +select ( select a from x as r1 ) as a from t1 +) +select * from x as s1 +where s1.a in ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x s2 +) +) as r +from t3 +) +select * from cte; +ERROR 21000: Subquery returns more than 1 row +with +cte as +( +select ( +with +x as +( +select ( select a from x ) as a from t1 +) +select exists ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x +) +) as r +from t3 +) +select * from cte; +r +1 +1 +with +cte_e as +( +with +cte as +( +select ( +with +x as +( +select ( select a from x ) from t1 +) +select exists ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x +) +) as r +from t3 +) +select * from cte +) +select s1.*, s2.* from cte_e as s1, cte_e as s2; +r r +1 1 +1 1 +1 1 +1 1 +with +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1; +a +4 +5 +2 +6 +7 +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2; +a +6 +8 +5 +9 +3 +7 +with recursive +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1; +a +4 +5 +6 +7 +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2; +a +6 +8 +10 +with +cte as +( +with +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a in ( +with recursive +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +a +6 +with +cte as +( +with +x as +( +select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1 +where s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +a +4 +6 +7 +with +cte as +( +with recursive +y as +( +select a from t1 union select a+1 from y as r1 where a < 7 +) +select * from y as s1 +where s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +a +5 +6 +7 +with +cte as +( +with +y(a) as +( +select a+5 from t1 +) +select * from y as s1 +where s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) +select * from cte; +a +9 +with +cte as +( +select ( +with +x as +( +select a from x as r1 +) +select * from x as s1 +where s1.a in ( +with +recursive x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) as r +from t3 +) +select * from cte; +r +6 +6 +with +cte as +( +select ( +with +x as +( +select a from x as r1 +) +select * from x as s1 +where s1.a < 5 and +s1.a in ( +with +x as +( +select a from t2 +union +select a+2 from x as r2 where a < 10 +) +select a from x as s2 +) +) as r +from t3 +) +select * from cte; +r +3 +3 +drop table t1,t2,t3,x; +# # End of 10.3 tests # diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index ca97c2d2900..f5babc6cb65 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -3207,5 +3207,669 @@ set @@sql_mode=default; drop table t1,t2; --echo # +--echo # MDEV-29361: Embedded recursive / non-recursive CTE within +--echo # the scope of another embedded CTE with the same name +--echo # + +create table t1 (a int); +insert into t1 values (4), (5); +create table t2 (a int); +insert into t2 values (6), (8); +create table t3 (a int); +insert into t3 values (1), (9); + + +with recursive +x as +( + select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1; + +with recursive +x as +( + select a from t2 + union + select a+2 from x as r2 where a < 10 +) +select a from x as s2; + +# All recursive CTEs with name x are embedded in in the definition of 'cte', +# without this embedding CTE the bug could not be reproduced + +# two recursive CTEs with name x, the second CTE is in the scope +# of the first one, but does not use it +# before fix of this bug: wrong result set + +with +cte as +( + with recursive + x as + ( + select a from t1 union select a+1 from x as r1 where a < 7 + ) + select * from x as s1 + where s1.a in ( + with recursive + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) +) +select * from cte; + + +# two recursive CTEs with name x, the second CTE is in the scope of the first +# one, but does not use it; there are two non-recursive references to the latter +# before fix of this bug: wrong result set + +with +cte as +( + with recursive + x(a) as + ( + select a from t1 union select a+1 from x as r1 where a < 7 + ) + select s1.a from x as s1, x + where s1.a = x.a and + x.a in ( + with recursive + x(a) as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) +) +select * from cte; + + +# x as r1 belongs to the definition of CTE x from non-RECURSIVE with clause +# before fix of this bug: infinite sequence of recursive calls + +--error ER_NO_SUCH_TABLE +with +cte as +( + with + x as + ( + select a from t1 union select a+1 from x as r1 where a < 7 + ) + select * from x as s1 + where s1.a in ( + with recursive + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) +) +select * from cte; + + +# x as r2 belongs to the definition of CTE x from non-RECURSIVE with clause +# yet it is in the scope of another CTE with the same name +# before fix of this bug: crash in With_element::get_name() + +with +cte as +( + with recursive + x as + ( + select a from t1 union select a+1 from x as r1 where a < 7 + ) + select * from x as s1 + where s1.a in ( + with + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) +) +select * from cte; + + +# x as r1 is in the definition of CTE x from non-RECURSIVE with clause, thus +# although x as r2 is in the scope of the first CTE x an error is expected +# before fix of this bug: crash in With_element::get_name() + +--error ER_NO_SUCH_TABLE +with +cte as +( + with + x as + ( + select a from t1 union select a+1 from x as r1 where a < 7 + ) + select * from x as s1 + where s1.a in ( + with + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) +) +select * from cte; + + +# x as r2 belongs to the definition of CTE x from non-RECURSIVE with clause +# and in the scope of recursive CTE y, but does not use the latter +# before fix of this bug: crash in With_element::get_name() + +--error ER_NO_SUCH_TABLE +with +cte as +( + with recursive + y as + ( + select a from t1 union select a+1 from y as r1 where a < 7 + ) + select * from y as s1 + where s1.a in ( + with + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) +) +select * from cte; + + +# x as r2 belongs to the definition of CTE x from non-RECURSIVE with clause +# and in the scope of non-recursive CTE y, but does not use the latter +# before fix of this bug: crash in With_element::get_name() + +--error ER_NO_SUCH_TABLE +with +cte as +( + with + y(a) as + ( + select a+5 from t1 + ) + select * from y as s1 + where s1.a in ( + with + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) +) +select * from cte; + + +# in the subquery of the embedding CTE cte: +# x as r1 is in the definition of CTE x from non-RECURSIVE with clause; +# x as t2 is in the definition of CTE x from RECURSIVE with clause; +# an error is expected to be reported for x as r1 +# before fix of this bug: infinite sequence of recursive calls + +--error ER_NO_SUCH_TABLE +with +cte as +( + select ( + with + x as + ( + select a from x as r1 + ) + select * from x as s1 + where s1.a in ( + with recursive + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) + ) as r + from t3 +) +select * from cte; + + +# in the subquery of the embedding CTE cte: +# x as r1 is in the definition of CTE x from non-RECURSIVE with clause, thus +# although x as r2 is in the scope of the first CTE x an error is expected +# before fix of this bug: crash in With_element::get_name() + +--error ER_NO_SUCH_TABLE +with +cte as +( + select ( + with + x as + ( + select a from x as r1 + ) + select * from x as s1 + where s1.a < 5 and + s1.a in ( + with + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) + ) as r + from t3 +) +select * from cte; + + +# in the subquery of the embedding CTE cte: +# two recursive CTEs with name x, the second CTE is in the scope +# of the first one, but does not use it +# before fix of this bug: wrong result set + +with +cte as +( + select ( + with recursive + x(a) as + ( + select a+3 from t1 union select a+1 from x as r1 where a < 7 + ) + select * from x as s1 + where s1.a < 8 and + s1.a in ( + with recursive + x(a) as + ( + select a-2 from t2 + union + select a+1 from x as r2 where a < 10 + ) + select a from x as s2 + ) + ) as r + from t3 +) +select * from cte; + + +# in the subquery of the embedding CTE cte: +# two recursive CTEs with name x, the second CTE is in the scope +# of the first one, but does not use it +# before fix of this bug: Subquery returns more than 1 row + +with +cte as +( + select ( + with recursive + x as + ( + select a from t1 union select a+1 from x as r1 where a < 7 + ) + select * from x as s1 + where s1.a in ( + with recursive + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) + ) as r + from t3 +) +select * from cte; + + +create table x (a int); +insert into x values (3), (7), (1), (5), (6); + + +# in the subquery of the embedding CTE cte: +# one non-recursive CTEs with name x using table t in a subquery, the second +# CTE x is recursive and is in the scope of the first one, but does not use it; +# the query uses both CTE with name x. +# before fix of this bug: infinite sequence of recursive calls + +--error ER_SUBQUERY_NO_1_ROW +with +cte as +( + select ( + with + x as + ( + select ( select a from x as r1 ) as a from t1 + ) + select * from x as s1 + where s1.a in ( + with recursive + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x s2 + ) + ) as r + from t3 +) +select * from cte; + + +# in the subquery of the embedding CTE cte: +# one non-recursive CTEs with name x using table t, the second CTE x is +# recursive and is in the scope of the first one, but does not use it; +# the query uses only the second CTE with name x. +# before fix of this bug: Subquery returns more than 1 row + +with +cte as +( + select ( + with + x as + ( + select ( select a from x ) as a from t1 + ) + select exists ( + with recursive + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x + ) + ) as r + from t3 +) +select * from cte; + + +# in the subquery of the embedding CTE cte embedded in the CTE cte_e +# one non-recursive CTEs with name x uses table t1, the second CTE x is +# recursive and is in the scope of the first one, but does not use it; +# CTE cte uses only the second CTE with name x; +# the query has two refeences to cte_e +# before fix of this bug: infinite sequence of recursive calls + +with +cte_e as +( + with + cte as + ( + select ( + with + x as + ( + select ( select a from x ) from t1 + ) + select exists ( + with recursive + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x + ) + ) as r + from t3 + ) + select * from cte +) +select s1.*, s2.* from cte_e as s1, cte_e as s2; + + +# check : with base table x all queries abobe that returned error +# message ER_NO_SUCH_TABLE now return proper result sets + +with +x as +( + select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1; + +with +x as +( + select a from t2 + union + select a+2 from x as r2 where a < 10 +) +select a from x as s2; + +with recursive +x as +( + select a from t1 union select a+1 from x as r1 where a < 7 +) +select * from x as s1; + +with recursive +x as +( + select a from t2 + union + select a+2 from x as r2 where a < 10 +) +select a from x as s2; + + +# x as r1 is bound to table x, x as s1 is bound to the first CTE x +# x as r2 and x as s2 are bound to the second CTE x +# before fix of this bug: infinite sequence of recursive calls + +with +cte as +( + with + x as + ( + select a from t1 union select a+1 from x as r1 where a < 7 + ) + select * from x as s1 + where s1.a in ( + with recursive + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) +) +select * from cte; + + +# x as r1 is bound to table x, x as s1 is bound to the first CTE x +# x as r1 is bound to the first CTE x, x as s2 is bound to the second CTE x +# before fix of this bug: crash in With_element::get_name() + +with +cte as +( + with + x as + ( + select a from t1 union select a+1 from x as r1 where a < 7 + ) + select * from x as s1 + where s1.a in ( + with + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) +) +select * from cte; + + +# x as r2 is bound to table x, x as s2 is bound to CTE x +# before fix of this bug: crash in With_element::get_name() + +with +cte as +( + with recursive + y as + ( + select a from t1 union select a+1 from y as r1 where a < 7 + ) + select * from y as s1 + where s1.a in ( + with + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) +) +select * from cte; + + +# x as r2 is bound to table x, x as s2 is bound to CTE x +# before fix of this bug: crash in With_element::get_name() + +with +cte as +( + with + y(a) as + ( + select a+5 from t1 + ) + select * from y as s1 + where s1.a in ( + with + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) +) +select * from cte; + + +# x as r1 is bound to table x, x as s1 is bound to the first CTE x +# x as r2 and x as s2 are bound to the second CTE x +# before fix of this bug: infinite sequence of recursive calls + +with +cte as +( + select ( + with + x as + ( + select a from x as r1 + ) + select * from x as s1 + where s1.a in ( + with + recursive x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) + ) as r + from t3 +) +select * from cte; + + +# x as r1 is bound to table x, x as s1 is bound to the first CTE x +# x as r2 is bound to the first CTE x, x as s2 is bound to the second CTE x +# before fix of this bug: crash in With_element::get_name() + +with +cte as +( + select ( + with + x as + ( + select a from x as r1 + ) + select * from x as s1 + where s1.a < 5 and + s1.a in ( + with + x as + ( + select a from t2 + union + select a+2 from x as r2 where a < 10 + ) + select a from x as s2 + ) + ) as r + from t3 +) +select * from cte; + + +drop table t1,t2,t3,x; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index 9acdf2278ac..d7e3eec78e8 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -400,9 +400,17 @@ bool With_element::check_dependencies_in_spec() { for (st_select_lex *sl= spec->first_select(); sl; sl= sl->next_select()) { - st_unit_ctxt_elem ctxt0= {NULL, owner->owner}; - st_unit_ctxt_elem ctxt1= {&ctxt0, spec}; - check_dependencies_in_select(sl, &ctxt1, false, &sl->with_dep); + if (owner->with_recursive) + { + st_unit_ctxt_elem ctxt0= {NULL, owner->owner}; + st_unit_ctxt_elem ctxt1= {&ctxt0, spec}; + check_dependencies_in_select(sl, &ctxt1, false, &sl->with_dep); + } + else + { + st_unit_ctxt_elem ctxt= {NULL, spec}; + check_dependencies_in_select(sl, &ctxt, false, &sl->with_dep); + } base_dep_map|= sl->with_dep; } return false; @@ -470,29 +478,50 @@ With_element *With_clause::find_table_def(TABLE_LIST *table, With_element *find_table_def_in_with_clauses(TABLE_LIST *tbl, st_unit_ctxt_elem *ctxt) { - With_element *barrier= NULL; + With_element *found= 0; for (st_unit_ctxt_elem *unit_ctxt_elem= ctxt; unit_ctxt_elem; unit_ctxt_elem= unit_ctxt_elem->prev) { st_select_lex_unit *unit= unit_ctxt_elem->unit; With_clause *with_clause= unit->with_clause; - if (with_clause && - (tbl->with= with_clause->find_table_def(tbl, barrier))) - return tbl->with; - barrier= NULL; - if (unit->with_element && !unit->with_element->get_owner()->with_recursive) + /* + First look for the table definition in the with clause attached to 'unit' + if there is any such clause. + */ + if (with_clause) { - /* - This unit is the specification if the with element unit->with_element. - The with element belongs to a with clause without the specifier RECURSIVE. - So when searching for the matching definition of tbl this with clause must - be looked up to this with element - */ - barrier= unit->with_element; + found= with_clause->find_table_def(tbl, NULL); + if (found) + break; + } + /* + If 'unit' is the unit that defines a with element then reset 'unit' + to the unit whose attached with clause contains this with element. + */ + With_element *with_elem= unit->with_element; + if (with_elem) + { + if (!(unit_ctxt_elem= unit_ctxt_elem->prev)) + break; + unit= unit_ctxt_elem->unit; + } + with_clause= unit->with_clause; + /* + Now look for the table definition in this with clause. If the with clause + contains RECURSIVE the search is performed through all CTE definitions in + clause, otherwise up to the definition of 'with_elem' unless it is NULL. + */ + if (with_clause) + { + found= with_clause->find_table_def(tbl, + with_clause->with_recursive ? + NULL : with_elem); + if (found) + break; } } - return NULL; + return found; } @@ -522,22 +551,30 @@ void With_element::check_dependencies_in_select(st_select_lex *sl, bool in_subq, table_map *dep_map) { - With_clause *with_clause= sl->get_with_clause(); + bool is_spec_select= sl->get_with_element() == this; + for (TABLE_LIST *tbl= sl->table_list.first; tbl; tbl= tbl->next_local) { - if (tbl->derived || tbl->nested_join) + if (tbl->with || tbl->derived || tbl->nested_join) continue; tbl->with_internal_reference_map= 0; /* - If there is a with clause attached to the unit containing sl - look first for the definition of tbl in this with clause. - If such definition is not found there look in the with - clauses of the upper levels. + Look first for the definition of tbl in the with clause to which + this with element belongs. If such definition is not found there + look in the with clauses of the upper levels via the context + chain of embedding with elements. If the definition of tbl is found somewhere in with clauses - then tbl->with is set to point to this definition + then tbl->with is set to point to this definition. */ - if (with_clause && !tbl->with) - tbl->with= with_clause->find_table_def(tbl, NULL); + if (is_spec_select) + { + With_clause *with_clause= sl->master_unit()->with_clause; + if (with_clause) + tbl->with= with_clause->find_table_def(tbl, NULL); + if (!tbl->with) + tbl->with= owner->find_table_def(tbl, + owner->with_recursive ? NULL : this); + } if (!tbl->with) tbl->with= find_table_def_in_with_clauses(tbl, ctxt); @@ -564,8 +601,7 @@ void With_element::check_dependencies_in_select(st_select_lex *sl, st_select_lex_unit *inner_unit= sl->first_inner_unit(); for (; inner_unit; inner_unit= inner_unit->next_unit()) { - if (!inner_unit->with_element) - check_dependencies_in_unit(inner_unit, ctxt, in_subq, dep_map); + check_dependencies_in_unit(inner_unit, ctxt, in_subq, dep_map); } } @@ -643,10 +679,14 @@ void With_element::check_dependencies_in_unit(st_select_lex_unit *unit, bool in_subq, table_map *dep_map) { + st_unit_ctxt_elem unit_ctxt_elem= {ctxt, unit}; if (unit->with_clause) - check_dependencies_in_with_clause(unit->with_clause, ctxt, in_subq, dep_map); + { + (void) unit->with_clause->check_dependencies(); + check_dependencies_in_with_clause(unit->with_clause, &unit_ctxt_elem, + in_subq, dep_map); + } in_subq |= unit->item != NULL; - st_unit_ctxt_elem unit_ctxt_elem= {ctxt, unit}; st_select_lex *sl= unit->first_select(); for (; sl; sl= sl->next_select()) { diff --git a/sql/sql_cte.h b/sql/sql_cte.h index 47071dd06fd..b2708186008 100644 --- a/sql/sql_cte.h +++ b/sql/sql_cte.h @@ -391,10 +391,24 @@ public: bool add_with_element(With_element *elem); /* Add this with clause to the list of with clauses used in the statement */ - void add_to_list(With_clause ** &last_next) + void add_to_list(With_clause **ptr, With_clause ** &last_next) { - *last_next= this; - last_next= &this->next_with_clause; + if (embedding_with_clause) + { + /* + An embedded with clause is always placed before the embedding one + in the list of with clauses used in the query. + */ + while (*ptr != embedding_with_clause) + ptr= &(*ptr)->next_with_clause; + *ptr= this; + next_with_clause= embedding_with_clause; + } + else + { + *last_next= this; + last_next= &this->next_with_clause; + } } void set_owner(st_select_lex_unit *unit) { owner= unit; } diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index d89ef534486..73783f26fdd 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -15379,7 +15379,8 @@ with_clause: 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); + with_clause->add_to_list(&Lex->with_clauses_list, + Lex->with_clauses_list_last_next); } with_list { diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 2a42abe5d20..bcb6c982da3 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -15352,7 +15352,8 @@ with_clause: 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); + with_clause->add_to_list(&Lex->with_clauses_list, + Lex->with_clauses_list_last_next); } with_list { |