Re: Remove restrictions in recursive query

Renan Alves Fonseca <renanfonseca(at)gmail(dot)com> writes:
> The solution using GROUP BY in the recursive query is the following:

> with recursive t1(node,nb_path) as
> (select 1,1::numeric
> union all
> (select dag.target, sum(nb_path)
> from t1 join dag on t1.node=dag.source
> group by 1)
> ) select sum(nb_path) from t1 join sink_nodes using (node) ;

This is not about the GROUP BY; it's about the SUM().
If you try this example you get

regression=# with recursive t1(node,nb_path) as
(select 1,1::numeric
union all
(select dag.target, sum(nb_path)
from t1 join dag on t1.node=dag.source
group by 1)
) select sum(nb_path) from t1 join sink_nodes using (node) ;
ERROR: aggregate functions are not allowed in a recursive query's recursive term
LINE 4: (select dag.target, sum(nb_path)
^

The code says that that restriction is from the SQL spec, and
it seems to be correct as of SQL:2021. 7.17 <query expression>
SR 3)j)ix)5)C) says

C) WQEi shall not contain a <query specification> QS such that QS
immediately contains a <table expression> TE that contains a
<query name> referencing WQNX and either of the following is true:

I) TE immediately contains a <having clause> that contains a
<set function specification>.

II) QS immediately contains a <select list> SL that contains
either a <window function>, or a <set function
specification>, or both.

(<set function specification> is spec-ese for "aggregate function
call")

I don't know the SQL committee's precise reasoning for this
restriction, but I suspect it's because the recursive query
expansion is not well-defined in the presence of an aggregate.
The spec has an interesting comment at the bottom of sub-rule ix:

NOTE 310 — The restrictions insure that each WLEi, viewed as a
transformation of the query names of the stratum, is monotonically
increasing. According to Tarski’s fixed point theorem, this
insures that there is a fixed point. The General Rules use
Kleene’s fixed point theorem to define a sequence that converges
to the minimal fixed point.

regards, tom lane