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