Fixed the bug mdev-12519.

This patch fixed some problems that occurred with subqueries that
contained directly or indirectly recursive references to recursive CTEs.

1. A [NOT] IN predicate with a constant left operand and a non-correlated
subquery as the right operand used in the specification of a recursive CTE
was considered as a constant predicate and was evaluated only once.
Now such a predicate is re-evaluated after every iteration of the process
that produces the records of the recursive CTE.
2. The Exists-To-IN transformation could be applied to [NOT] IN predicates
with recursive references. This opened a possibility of materialization
for the subqueries used as right operands. Yet, materialization
is prohibited for the subqueries if they contain a recursive reference.
Now the Exists-To-IN transformation cannot be applied for subquery
predicates with recursive references.

The function st_select_lex::check_subqueries_with_recursive_references()
is called now only for the first execution of the SELECT.

1 files changed, 1 insertions, 0 deletions

diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index d76ee13a010..c9976baeabd 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -1229,6 +1229,7 @@ bool st_select_lex::check_subqueries_with_recursive_references()
 	continue;
       Item_subselect *subq= (Item_subselect *) sl_master->item;
       subq->with_recursive_reference= true;
+      subq->register_as_with_rec_ref(tbl->with);
     }
   }
   return false;