Re: SQL Property Graph Queries (SQL/PGQ)

On Fri, Mar 27, 2026 at 3:28 PM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>
> On 26.03.26 08:08, Ashutosh Bapat wrote:
> > Consider following query
> > Query1
> > SELECT x1.a, g.* FROM x1, GRAPH_TABLE (myshop MATCH (x1 IS
> > customers)-[IS customer_orders]->(o IS orders WHERE o.order_id = x1.a)
> > COLUMNS (x1.name AS customer_name, x1.customer_id AS cid)) g;
> >
> > x1 is a table referenced in from clause as well as an element pattern
> > variable in path pattern. If we don't allow backward (cross)
> > referencing, x1.a in the element pattern o resolves to the table x1's
> > column a, but x1.name resolves to element x1's property reference
> > name. So within the same graph pattern x1 may get resolved to two
> > different things, even though x1 was declared before using it. Is that
> > how we expect it to happen? Let's call this inconsistency1.
>
> I don't think this interpretation is correct.
>
> Even if we don't support non-local element variable references, we still
> need to *recognize* them. We just don't need to be able to process
> them, we can error out if we see them.

patch 0002 in the attached patchset implements it this way. It
collects all the element variables before transforming the path
pattern. Later it uses this list to detect non-local variable
references and throws error.

While implementing this I found another bug. In trasformColumnRef() we
try to resolve the column ref as a table.column first which may
resolve a property reference if a lateral table has the same name as
the variable name and has a column with the same name as a property.
Given that variables in the GRAPH_TABLE form the innermost namespace,
we should try resolving the column reference as a property before
lateral table reference. Since we do not allow subquery inside
GRAPH_TABLE, there is no way a tables/columns/function will form the
innermost namespace. The patch has a test demonstrating the bug and
also the fix. Please let me know what you do you think of the fix.

>
> > 0001: is a small adjustment to make sure that we add an element
> > variable name to the graph table namespace only once. This isn't a
> > problem right now, but we will have to do that anyway in [1] and it
> > has some effect one 0002.
>
> committed

Thanks.

>
> > 0002: prohibits cross element variable references within graph_table
> > clause. It adds a new member GraphTableParseState::cur_variable to
> > track the variable name of the current pattern being transformed. We
> > can not use llast() simply because a. pattern currently being
> > transformed may not have name, so llast will be misleading b. changes
> > in 0001. We may want to combine 0001 and 0002 when committing.
>
> (under discussion)
>
> > 0003: prohibits consecutive element patterns of the same kind
>
> committed (I made the error message wording a bit more compact.)

That looks better. Thanks.

>
> > 0004: some cleanup remaining from
> > 5282bf535e474dc2517f2e835d147420ae2144de. We may want to wait to
> > accumulate more cleanups.
>
> I don't think this patch goes into the right direction. The existing
> code seems preferable.
>

Removed from my patch set.

--
Best Wishes,
Ashutosh Bapat