[Solved] Why does this recursive CTE cause an error or infinite loop?WITH RECURSIVE tally AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM tally) SELECT * FROM tally WHERE num <= 5; | SQL

SQL - Common Table Expressions (CTEs)

Why does this recursive CTE cause an error or infinite loop?

WITH RECURSIVE tally AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM tally) SELECT * FROM tally WHERE num <= 5;

AMissing a stopping condition in the recursive part

BIncorrect anchor member syntax

CUsing UNION instead of UNION ALL

DFiltering rows in the outer query instead of the recursive part

Step-by-Step Solution

Solution:

Step 1: Check recursive part
The recursive SELECT lacks a WHERE clause to limit recursion.
Step 2: Effect of missing stopping condition
Without a stopping condition, recursion continues indefinitely causing an error or infinite loop.
Step 3: Filtering in outer query
Filtering after recursion does not stop recursion itself.
Final Answer:
Missing a stopping condition in the recursive part -> Option A
Quick Check:
Recursive part must limit recursion [OK]

Quick Trick: Stopping condition must be inside recursive SELECT [OK]

Common Mistakes:

Master "Common Table Expressions (CTEs)" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

Why does this recursive CTE cause an error or infinite loop?