Thread: 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