[MDEV-15157] Infinite recursive loop in CTE Created: 2018-02-01  Updated: 2018-02-09  Resolved: 2018-02-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.2, 10.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Alice Sherepa
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

testcase:

WITH RECURSIVE cte AS
  (SELECT 1 FROM cte UNION ALL SELECT 1)
SELECT * FROM cte;

Should probably be an error, that in anchor part table cte is not used



 Comments   
Comment by Alice Sherepa [ 2018-02-01 ]

here should probably be another error message.

MariaDB [test]> WITH RECURSIVE cte AS
    ->   (SELECT 1 FROM cte UNION ALL SELECT 1 FROM cte)
    -> SELECT * FROM cte;
ERROR 4005 (HY000): No anchors for recursive WITH element 'cte'

Comment by Igor Babaev [ 2018-02-05 ]

Alice,
For the reported query the anchor is 'SELECT 1'.
What's wrong with the error message for the second query?

Comment by Alice Sherepa [ 2018-02-06 ]

Sorry for a not clear explanation, what I meant and being misleading with a comment, I wanted to indicate different problems, but reported them in the same bug.
1) is it allowed to use table cte in anchor part ( e.g

WITH RECURSIVE cte AS (SELECT * FROM cte UNION SELECT 1) SELECT * FROM cte;

And if the type of CTE is determined only by the type of anchor parts, what type should be in this case then.
2) is it allowed to not use table cte in the recursive part, while CTE type is RECURSIVE, e.g.

WITH RECURSIVE cte AS (SELECT * FROM t1 UNION SELECT 5 from t1) SELECT * FROM cte;

If recursive part returns const and we have union all in the query, then it results in infinite loop

Comment by Igor Babaev [ 2018-02-09 ]

Alice,
1)

MariaDB [test]> WITH RECURSIVE cte AS (SELECT * FROM cte UNION SELECT 1) SELECT * FROM cte;
+---+
| 1 |
+---+
| 1 |
+---+

The anchor (non-recursive part of the union is SELECT 1. It's type is int(11).
We start the recursive iteration and see that the table stabilized on the second iteration.
2)
The word RECURSIVE means only that it is only potentially recursive (see SQL Standard).
Your second query is not actually recursive.

Comment by Igor Babaev [ 2018-02-09 ]

See my comments.

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