Bug with subqueries in recursive CTEs? - Mailing list pgsql-hackers

From Laurenz Albe
Subject Bug with subqueries in recursive CTEs?
Date
Msg-id fe5c4444e1e148dc0ee0ddc5fa62797926ef00c8.camel@cybertec.at
Whole thread Raw
Responses Re: Bug with subqueries in recursive CTEs?  (Andrew Gierth <andrew@tao11.riddles.org.uk>)
List 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




pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: PG compilation error with Visual Studio 2015/2017/2019
Next
From: Tom Lane
Date:
Subject: Re: Poll: are people okay with function/operator table redesign?