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