Re: Bug with subqueries in recursive CTEs?

>>>>> "Laurenz" == Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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)