Re: Bug with subqueries in recursive CTEs?

>>>>> "Laurenz" == Laurenz Albe <laurenz.albe@cybertec.at> writes:

 Laurenz> I played with a silly example and got a result that surprises
 Laurenz> me:

 Laurenz>   WITH RECURSIVE fib AS (
 Laurenz>         SELECT n, "fibₙ"
 Laurenz>         FROM (VALUES (1, 1::bigint), (2, 1)) AS f(n,"fibₙ")
 Laurenz>      UNION ALL
 Laurenz>         SELECT max(n) + 1,
 Laurenz>                sum("fibₙ")::bigint
 Laurenz>         FROM (SELECT n, "fibₙ"
 Laurenz>               FROM fib
 Laurenz>               ORDER BY n DESC
 Laurenz>               LIMIT 2) AS tail
 Laurenz>         HAVING max(n) < 10
 Laurenz>   )
 Laurenz>   SELECT * FROM fib;

 Laurenz> I would have expected either the Fibonacci sequence or

 Laurenz>   ERROR: aggregate functions are not allowed in a recursive
 Laurenz>   query's recursive term

You don't get a Fibonacci sequence because the recursive term only sees
the rows (in this case only one row) added by the previous iteration,
not the entire result set so far.

So the result seems correct as far as that goes. The reason the
"aggregate functions are not allowed" error isn't hit is that the
aggregate and the recursive reference aren't ending up in the same query
- the check for aggregates is looking at the rangetable of the query
level containing the agg to see if it has an RTE_CTE entry which is a
recursive reference.

--
Andrew (irc:RhodiumToad)