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? [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f585671055d1c56d6fba0bc5835d28e68248ccfe -- Sami Imseih Amazon Web Services (AWS)
v1-0001-Disallow-outer-reference-and-WHERE-clause-aggrega.patch
Description: Binary data
