Bug with subqueries in recursive CTEs?

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Bug with subqueries in recursive CTEs?
Date: 2020-04-30 03:18:49
Message-ID: fe5c4444e1e148dc0ee0ddc5fa62797926ef00c8.camel@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2020-04-30 03:26:20 Re: Poll: are people okay with function/operator table redesign?
Previous Message Amit Kapila 2020-04-30 03:06:55 Re: PG compilation error with Visual Studio 2015/2017/2019