Re: Remove restrictions in recursive query

On Thu, Mar 27, 2025 at 10:50 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> 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().

Cannot SUM() without GROUP BY, right? (I mean the SUM() in the recursive query)

> 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)
> ^

Sorry, I forgot to mention that this query only works in my local hack
(I simply removed the check that raises this error message)

> 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 suspected that this restriction came straight from the specs. I
understand that while the proposed solution can help in some specific
use cases, it is not enough to justify an exception to the spec.

> 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