MDEV-23886 Reusing CTE inside a function fails with table doesn't exist

In the code existed just before this patch binding of a table reference to
the specification of the corresponding CTE happens in the function
open_and_process_table(). If the table reference is not the first in the
query the specification is cloned in the same way as the specification of
a view is cloned for any reference of the view. This works fine for
standalone queries, but does not work for stored procedures / functions
for the following reason.
When the first call of a stored procedure/ function SP is processed the
body of SP is parsed. When a query of SP is parsed the info on each
encountered table reference is put into a TABLE_LIST object linked into
a global chain associated with the query. When parsing of the query is
finished the basic info on the table references from this chain except
table references to derived tables and information schema tables is put
in one hash table associated with SP. When parsing of the body of SP is
finished this hash table is used to construct TABLE_LIST objects for all
table references mentioned in SP and link them into the list of such
objects passed to a pre-locking process that calls open_and_process_table()
for each table from the list.
When a TABLE_LIST for a view is encountered the view is opened and its
specification is parsed. For any table reference occurred in
the specification a new TABLE_LIST object is created to be included into
the list for pre-locking. After all objects in the pre-locking have been
looked through the tables mentioned in the list are locked. Note that the
objects referenced CTEs are just skipped here as it is impossible to
resolve these references without any info on the context where they occur.
Now the statements from the body of SP are executed one by one that.
At the very beginning of the execution of a query the tables used in the
query are opened and open_and_process_table() now is called for each table
reference mentioned in the list of TABLE_LIST objects associated with the
query that was built when the query was parsed.
For each table reference first the reference is checked against CTEs
definitions in whose scope it occurred. If such definition is found the
reference is considered resolved and if this is not the first reference
to the found CTE the the specification of the CTE is re-parsed and the
result of the parsing is added to the parsing tree of the query as a
sub-tree. If this sub-tree contains table references to other tables they
are added to the list of TABLE_LIST objects associated with the query in
order the referenced tables to be opened. When the procedure that opens
the tables comes to the TABLE_LIST object created for a non-first
reference to a CTE it discovers that the referenced table instance is not
locked and reports an error.
Thus processing non-first table references to a CTE similar to how
references to view are processed does not work for queries used in stored
procedures / functions. And the main problem is that the current
pre-locking mechanism employed for stored procedures / functions does not
allow to save the context in which a CTE reference occur. It's not trivial
to save the info about the context where a CTE reference occurs while the
resolution of the table reference cannot be done without this context and
consequentially the specification for the table reference cannot be
determined.

This patch solves the above problem by moving resolution of all CTE
references at the parsing stage. More exactly references to CTEs occurred in
a query are resolved right after parsing of the query has finished. After
resolution any CTE reference it is marked as a reference to to derived
table. So it is excluded from the hash table created for pre-locking used
base tables and view when the first call of a stored procedure / function
is processed.
This solution required recursive calls of the parser. The function
THD::sql_parser() has been added specifically for recursive invocations of
the parser.

1 files changed, 202 insertions, 0 deletions

diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test
index c2ebc92c858..f994781548f 100644
--- a/mysql-test/t/cte_nonrecursive.test
+++ b/mysql-test/t/cte_nonrecursive.test
@@ -1261,4 +1261,206 @@ select a, c from cte as r2 where a > 4;
 
 drop table t1;
 
+--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();
+
+create view v1 as (select c1 from t1);
+
+create function f4() returns int return
+( select sum(c1) from
+  (select * from v1 union all select * from v1) dt
+);
+select f4();
+
+create function f5() returns int return
+( select sum(s.c1) from v1 as s, v1 as t where s.c1=t.c1
+);
+select f5();
+
+create view v2(s) as
+with cte1 as (select c1 from t1)
+select sum(c1) from (select * from cte1 union all select * from cte1) dt;
+
+create function f6() returns int return
+(select s from v2);
+select f6();
+
+create function f7() returns int return
+( select r.s from v2 as r, v2 as t where r.s=t.s
+);
+select f7();
+
+select f5() + f6();
+
+prepare stmt from "select f5() + f6();";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop function f1;
+drop function f2;
+drop function f3;
+drop function f4;
+drop function f5;
+drop function f6;
+drop function f7;
+
+drop view v1;
+drop view v2;
+
+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;
+
+--echo # checking CTE resolution for queries with hanging CTEs
+
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where a >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
+select * from cte3;
+
+select * from t2;
+
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where a >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
+select * from t2;
+
+--error ER_BAD_FIELD_ERROR
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where c1 >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
+select * from t2;
+
+--error ER_BAD_FIELD_ERROR
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where a >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.c1)
+select * from t2;
+
+with
+cte1 as (select * from t1 where c1 <= 2),
+cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select * from cte2;
+
+with
+cte1 as (select * from t1 where c1 <= 2),
+cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select * from t2;
+
+--error ER_NON_UNIQ_ERROR
+with
+cte1 as (select * from t1 where c1 <= 2),
+cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=c1)
+select * from t2;
+
+with cte3 as
+( with cte2(a,b) as
+  ( with cte1 as (select * from t1 where c1 <= 2)
+    select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+  select r1.a,r2.b from cte2 as r1, cte2 as r2)
+select * from cte3;
+
+with cte3 as
+( with cte2(a,b) as
+  ( with cte1 as (select * from t1 where c1 <= 2)
+    select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+  select r1.a,r2.b from cte2 as r1, cte2 as r2)
+select * from t2;
+
+--error ER_BAD_FIELD_ERROR
+with cte3 as
+( with cte2(a,b) as
+  ( with cte1 as (select * from t1 where c1 <= 2)
+    select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+  select r1.c1,r2.c1 from cte2 as r1, cte2 as r2)
+select * from t2;
+
+delimiter |;
+
+create procedure p1()
+begin
+insert into t2
+with cte1 as (select c1 from t1)
+select * from t1 as s, t1 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;
+
+delimiter |;
+
+create procedure p1()
+begin
+insert into t2
+with cte1 as (select a from t1)
+select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
+end |
+
+delimiter ;|
+
+--error ER_BAD_FIELD_ERROR
+call p1();
+
+drop procedure p1;
+
+drop table t1,t2;
+
 --echo # End of 10.2 tests