On 10/11/2025 22:05, Tom Lane wrote:
> [ cc'ing Peter and Vik for possible input on SQL-standard question ]
Thanks!
> WITH a AS (
> SELECT id FROM (VALUES (1), (2)) AS v(id)
> ),
> b AS (
> SELECT max((SELECT sum(id) FROM a)) AS agg
> )
> SELECT agg FROM b;
snip
> I looked at the SQL standard for possible guidance and found none:
> they disallow subqueries altogether within aggregate arguments,
> so they need not consider such cases. I am curious though whether
> Peter or Vik know if the committee ever considered relaxing that
> restriction, and if so whether they stopped to think about this
> particular point.
I am not seeing that restriction in the standard. For this test case,
we have MAX which has the lineage:
<aggregate function>
<general set function>
<set function type>
<computational operation>
MAX
Its argument, (SELECT SUM(id) FROM a), has this lineage:
<value expression>
<common value expression>
<numeric value expression>
<term>
<factor>
<numeric primary>
<value expression primary>
<non-parenthesized value expression primary>
<scalar subquery>
<subquery>
Since there are no outer column references, the subquery should be
independent. And if we inline it:
WITH
b (agg) AS (
SELECT MAX((
SELECT SUM(id)
FROM (VALUES (1), (2)) AS v (id)
))
-- FROM nothing
)
TABLE b
then the query works as expected. MATERIALIZEDing either or both CTEs
has no effect, which I find strange.
--
Vik Fearing
