[MDEV-32949] Undesired max_recursive_iterations warning Created: 2023-12-05  Updated: 2023-12-05

Status: Open
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 11.1.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Tadas Balaišis Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Win64


 Description   

Sample query

WITH a AS (SELECT 5 mx)
 
SELECT column_value FROM a,
 
(
 
WITH recursive r as
 
(SELECT 1 level
 
union all
 
select LEVEL+1 FROM r
 
)
 
select LEVEL column_value FROM r
 
) i
 
WHERE i.column_value<=a.mx

runs OK, but on insert or temporary table create:

CREATE OR REPLACE TEMPORARY TABLE tmp0 as
 
WITH a AS (SELECT 5 mx)
 
SELECT column_value FROM a,
 
(
 
WITH recursive r as
 
(SELECT 1 level
 
union all
 
select LEVEL+1 FROM r
 
)
 
select LEVEL column_value FROM r
 
) i
 
WHERE i.column_value<=a.mx

it pops with error/warning "Query execution was interrupted. The query exceeded max_recursive_iterations = 100000. The query result may be incomplete".
I'd like to lose that unreasonable warning, just like it goes silent in select action. How can I do it?

 Comments   
Comment by Sergei Golubchik [ 2023-12-05 ]

you don't really want to disable the warning, because it says that in the second case CTE generates 100,000 rows (and hits the limit), while in the first case it — I suppose — generates only 5, as requested. What you want is the second CTE to generate only 5 rows and not to waste time going all the way to 100,000.

Comment by Tadas Balaišis [ 2023-12-05 ]

I'm not as so confident as you are to state what does other person really want. But yes, I want both things. First - that insert /CTAS would work as fast as select does. Second - that warning wouldn't appear.

Generated at Thu Feb 08 10:35:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.