Renan Alves Fonseca <renanfons...@gmail.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
