[SQL오류] Recursive query aborted after 1001 iterations.

문제발생

WITH RECURSIVE TIME AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1 FROM TIME)
    
SELECT HOUR, COUNT(HOUR)-1 FROM (
    SELECT HOUR FROM TIME
    UNION ALL
    SELECT HOUR(DATETIME) FROM ANIMAL_OUTS) VALID
GROUP BY HOUR

오류) Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

 

 

 

 

해결

1001번 반복 이후부터 리컬시브 쿼리가 유실되었다는 뜻. HOUR+1(0부터 1씩 증가) 입력후 반복을 언제까지 할 것인지 지정해 주지 않아 발생한 오류이다. WHERE절 조건을 입력해 주면 됨.

WITH RECURSIVE TIME AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1 FROM TIME WHERE HOUR < 23)
    
SELECT HOUR, COUNT(HOUR)-1 FROM (
    SELECT HOUR FROM TIME
    UNION ALL
    SELECT HOUR(DATETIME) FROM ANIMAL_OUTS) VALID
GROUP BY HOUR