Re: Bug with subqueries in recursive CTEs?

On Thu, 2020-04-30 at 04:37 +0100, Andrew Gierth wrote:
> "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

Thanks for looking at this!

> 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.

Ah, of course. You are right.

> 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.

But I wonder about that.

The source says that
"Per spec, aggregates can't appear in a recursive term."
Is that the only reason for that error message, or is there a deeper reason
to forbid it?

It feels wrong that a subquery would make using an aggregate legal when
it is illegal without the subquery.
But then, it doesn't bother me enough to research, and as long as the result
as such is correct, I feel much better.

Yours,
Laurenz Albe