Bug with subqueries in recursive CTEs?
I played with a silly example and got a result that surprises me:
WITH RECURSIVE fib AS (
SELECT n, "fibₙ"
FROM (VALUES (1, 1::bigint), (2, 1)) AS f(n,"fibₙ")
UNION ALL
SELECT max(n) + 1,
sum("fibₙ")::bigint
FROM (SELECT n, "fibₙ"
FROM fib
ORDER BY n DESC
LIMIT 2) AS tail
HAVING max(n) < 10
)
SELECT * FROM fib;
n | fibₙ
----+------
1 | 1
2 | 1
3 | 2
4 | 2
5 | 2
6 | 2
7 | 2
8 | 2
9 | 2
10 | 2
(10 rows)
I would have expected either the Fibonacci sequence or
ERROR: aggregate functions are not allowed in a recursive query's recursive term
Yours,
Laurenz Albe
"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)
On Thu, 2020-04-30 at 04:37 +0100, Andrew Gierth wrote:
"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
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