Wrong query result w/ propgraph single lateral col reference

From: Noah Misch <noah(at)leadboat(dot)com>
To: Peter Eisentraut <peter(at)eisentraut(dot)org>, Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Wrong query result w/ propgraph single lateral col reference
Date: 2026-06-30 17:30:53
Message-ID: 20260630173053.51.noahmisch@microsoft.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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);
}

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

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.

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.

Browse pgsql-hackers by date

  From Date Subject
Next Message Salma El-Sayed 2026-06-30 17:36:54 Re: [GSoC 2026] - B-tree Index Bloat Reduction - Approach & Questions
Previous Message Greg Burd 2026-06-30 17:21:07 Tepid: selective index updates for heap relations