[MDEV-26095] Infinite recursion when processing embedded recursive CTE with missing RECURSIVE Created: 2021-07-06  Updated: 2022-09-27  Resolved: 2021-07-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.2.40, 10.3.31, 10.4.21, 10.5.12, 10.6.4

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-29361 Infinite recursive calls when detecti... Closed

 Description   

If the definition of a recursive CTE r is used in the WITH clause without RECURSIVE and this clause is part of the query expression that defines another CTE then the server falls into an infinite recursion calling With_element::clone_parsed_spec() again and again. Ultimately it causes a crash of the server.
Here's a test case that causes such crash:

create table t1 (a int);
insert into t1 values (5), (7);
with cte_e as (
   with cte_r as ( 
      select a from t1   union  select a+1 as a from cte_r r where a < 10
   )   select * from cte_r 
) select * from cte_e;

It should be noted that the definition of cte_r is not considered as the definition of a recursive CTE. Rather it is considered as the definition of a non-recursive CTE referencing the base table or view with the name cte_r. This is because the WITH clause containing this definition lacks the keyword RECURSIVE.
So the expected reaction of the server should be similar to this:

MariaDB [test]>    with cte_r as ( 
    ->       select a from t1   union  select a+1 as a from cte_r r where a < 10
    ->    )   select * from cte_r 
    -> ;
ERROR 1146 (42S02): Table 'test.cte_r' doesn't exist



 Comments   
Comment by Oleksandr Byelkin [ 2021-07-08 ]

OK to push

Comment by Igor Babaev [ 2021-07-09 ]

A fix for this bug was pushed into 10.2. It must be merged upstream as it is.

Generated at Thu Feb 08 09:42:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.