| From: | Sami Imseih <samimseih(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Cc: | Ewan Young <kdbase(dot)hack(at)gmail(dot)com>, Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> |
| Subject: | Disallow outer-level and WHERE-clause aggregates in GRAPH_TABLE |
| Date: | 2026-08-12 15:34:51 |
| Message-ID: | CAA5RZ0tvdYODLQvYwVzAxUPe5=E3vSe8Zy7TvrQq+syvGKpHSQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
Commit f585671055d1 [1] disallowed aggregates, window functions, and SRFs in a
GRAPH_TABLE COLUMNS list. For the aggregate case it tests pstate->p_hasAggs
after transforming the columns. That is not enough. An aggregate that
references an outer query is attributed to a parent query level, so
check_agglevels_and_constraints sets p_hasAggs on that parent ParseState and
not on the GRAPH_TABLE's own, so the aggregate is not caught.
There are two places this shows up.
1. An outer-referencing aggregate in the COLUMNS list.
```
postgres=# CREATE TABLE customers (customer_id int PRIMARY KEY, name text);
CREATE TABLE
postgres=# CREATE PROPERTY GRAPH myshop VERTEX TABLES (customers);
CREATE PROPERTY GRAPH
postgres=# SELECT (SELECT num
postgres(# FROM GRAPH_TABLE (myshop MATCH (c IS customers)
postgres(# COLUMNS (count(o.customer_id) AS num)) t)
postgres-# FROM customers o;
ERROR: Aggref found in non-Agg plan node
```
2. The graph pattern WHERE clause was not checked at all in f585671055d1. A
same-level aggregate there is already rejected with "aggregate functions are
not allowed in WHERE", but an outer-referencing one is attributed to a parent
level is not caught, failing instead with "Aggref found in non-Agg plan node".
```
postgres=# -- same-level aggregate
postgres=# SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers
postgres(# WHERE count(c.customer_id) > 0)
postgres(# COLUMNS (c.name AS nm));
ERROR: aggregate functions are not allowed in WHERE
postgres=# -- outer-referencing aggregate
postgres=# SELECT (SELECT nm
postgres(# FROM GRAPH_TABLE (myshop MATCH (c IS customers
postgres(# WHERE count(o.customer_id) > 0)
postgres(# COLUMNS (c.name AS nm)) t)
postgres-# FROM customers o;
ERROR: Aggref found in non-Agg plan node
```
Unlike the repro in f585671055d1, neither case results in an assertion
failure. But surfacing an internal planner error from user SQL is wrong on its
own.
The attached patch closes both gaps by walking the transformed COLUMNS list and
the graph pattern for Aggref and GroupingFunc nodes. Unlike p_hasAggs, the walk
detects an aggregate by its presence in those the trees. Window functions
and SRFs only mark the local ParseState, so their checks introduced in
f585671055d1 remain in place.
Thoughts?
--
Sami Imseih
Amazon Web Services (AWS)
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Disallow-outer-reference-and-WHERE-clause-aggrega.patch | application/octet-stream | 7.2 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Daniel Gustafsson | 2026-08-12 15:42:57 | Re: [PATCH] pg_upgrade: add --initdb option to create the new cluster automatically |
| Previous Message | Jacob Champion | 2026-08-12 15:34:14 | Re: LibreSSL and OpenSSL separation in libpq to support 1.1.1 deprecation |