Re: Wrong query result w/ propgraph single lateral col reference

On Wed, Jul 1, 2026 at 10:26 PM Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
>
> On Tue, Jun 30, 2026 at 11:00 PM Noah Misch <noah(at)leadboat(dot)com> wrote:
> >
> > I had Opus 4.8 look for user-visible defects in SQL/PGQ (commit 2f094e7).
> > Setup:
> >
> > CREATE TABLE v (flag boolean, id int PRIMARY KEY, name text);
> > INSERT INTO v VALUES (true,1,'a'), (false,2,'b'), (true,3,'c');
> > CREATE PROPERTY GRAPH g VERTEX TABLES (v KEY (id) LABEL l PROPERTIES (id, flag, name));
> >
> > Findings:
> >
> > 0. A WHERE clause that is a single lateral col reference gives wrong query results:
> >
> > SELECT t.b, gt.name FROM (VALUES (false)) AS t(b),
> > GRAPH_TABLE (g MATCH (x IS l) WHERE t.b COLUMNS (x.name AS name)) gt;
> > -- got 2 rows, want 0
> >
> > Adding "AND true" corrects the result:
> >
> > SELECT t.b, gt.name FROM (VALUES (false)) AS t(b),
> > GRAPH_TABLE (g MATCH (x IS l) WHERE t.b AND true COLUMNS (x.name AS name)) gt;
> > -- got 0 rows, want 0
> >
> > 1. A WHERE clause that is a single property reference gives a spurious error:
> >
> > SELECT name FROM GRAPH_TABLE (g MATCH (x IS l) WHERE x.flag COLUMNS (x.name));
> > -- ERROR: unrecognized node type: 63
> >
> > Adding "AND true" again corrects the result:
> >
> > SELECT name FROM GRAPH_TABLE (g MATCH (x IS l) WHERE x.flag AND true COLUMNS (x.name));
> > -- got 2 rows, want 2
> >
> > For (0) and (1), Opus's opinion is that the right fix is:
> >
> > --- a/src/backend/rewrite/rewriteGraphTable.c
> > +++ b/src/backend/rewrite/rewriteGraphTable.c
> > @@ replace_property_refs(Oid propgraphid, Node *node, const List *mappings)
> > context.mappings = mappings;
> > context.propgraphid = propgraphid;
> >
> > - return expression_tree_mutator(node, replace_property_refs_mutator, &context);
> > + return replace_property_refs_mutator(node, &context);
> > }
> >
>
> This matches the pattern in the other expression mutators, and also
> fixes the problems. Thanks for the report, that was quite subtle.

Fix attached here.

>
> >
> > 2. A property whose value is an untyped literal stays type unknown.
> >
> > CREATE PROPERTY GRAPH gu VERTEX TABLES (v KEY (id) LABEL lu PROPERTIES ('hi' AS p));
> > SELECT p FROM GRAPH_TABLE (gu MATCH (n IS lu) COLUMNS (n.p));
> > -- ERROR: failed to find conversion function from unknown to text
> >
>
> I think property's data type should be set to text, not unknown. I
> think we should combine the fix for this in
> https://www.postgresql.org/message-id/CAHg+QDcyKNWyzDoKMxiZNjv7C-wAxs8y0ZoNkOV137Y+nk3UXg@mail.gmail.com.
>

Attaching the fix here. But I will also report it on the other thread
[2] and discussion can continue there.

>
> >
> > 4. [cosmetic] Duplicate property names found only via catalog unique key
> >
> > CREATE PROPERTY GRAPH g3 VERTEX TABLES (v KEY (id) LABEL lu PROPERTIES (flag, flag));
> > -- ERROR: duplicate key value violates unique constraint "pg_propgraph_property_name_index"
> >
> > Opus thinks this is unintentional and cites lack of CommandCounterIncrement()
> > between property inserts.
>
> We get the same error even if we split the duplicate properties across
> two commands.
> CREATE PROPERTY GRAPH g3 VERTEX TABLES (v KEY (id) LABEL lu PROPERTIES (flag));
> alter property graph g3 alter vertex table v alter label lu add
> properties(flag);
>
> I think we need to check for duplicate records in
> pg_propgraph_label_property catalog in insert_property_record(). The
> function already checks for duplicate records in
> pg_propgraph_property. Additionally we might need
> CommandCounterIncrement().

Fixed this as well.

I generate all the patches for SQL/PGQ bug fixes from the same branch.
These patches modify the same test and code files and thus conflict
with each other. Since we are talking about multiple issues here, I am
attaching all the patches that may be required to be applied before
applying the fixes being discussed here. I will highlight the patches
that actually fix the issues discussed here and the conflicting
patches. Ofc, the patches can be applied without conflict in the order
in which they are numbered. Each patch also has a link to the thread
which discusses the corresponding issue.

0011 fixes issues 0 and 1. The conflict is in changes to
graph_table.sql. You will need 0005 onwards patches to apply this
patch.

0005 fixes issue 2

0004 fixes issue 3

0003 fixes issue 4. Patches 0001 and 0002 are required to apply this
patch. They fix issues being discussed in [1]. 0002 adds a test file
which 0003 uses.

[1] https://www.postgresql.org/message-id/CAHg+QDeP=mTHTV48R23zKMy1SBmCKZ_L7-z5zKnYyw+K0x-gCg@mail.gmail.com
[2] https://www.postgresql.org/message-id/CAHg+QDcyKNWyzDoKMxiZNjv7C-wAxs8y0ZoNkOV137Y+nk3UXg@mail.gmail.com.

--
Best Wishes,
Ashutosh Bapat