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

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.

>
> 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.

>
> 3. [cosmetic] Invalid DROP PROPERTIES diagnosed via internal error message
>
> CREATE PROPERTY GRAPH g2 VERTEX TABLES (
> v KEY (id) LABEL la PROPERTIES (flag) LABEL lb PROPERTIES (name));
> ALTER PROPERTY GRAPH g2 ALTER VERTEX TABLE v ALTER LABEL la DROP PROPERTIES (name);
> -- ERROR: could not find tuple for label property 0
>
> Opus says propoid is found graph wide, but the label property oid is not
> checked before performDeletion(), so the intended "label has no property"
> error never fires.
>

This has been already reported and being discussed in [1]

>
> 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().

[1] https://www.postgresql.org/message-id/1DA5D52A-4AFA-426E-83F7-42ED974D682B@gmail.com

--
Best Wishes,
Ashutosh Bapat