| 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: | Whole Thread | Raw Message | 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
| 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 |