public inbox for [email protected]
help / color / mirror / Atom feedFrom: SATYANARAYANA NARLAPURAM <[email protected]>
To: PostgreSQL Hackers <[email protected]>
To: Ashutosh Bapat <[email protected]>
To: Peter Eisentraut <[email protected]>
Subject: Bug: Missing collation assignment for GRAPH_TABLE COLUMNS expressions
Date: Fri, 10 Apr 2026 08:41:58 -0700
Message-ID: <CAHg+QDc4aaiufYSgrwMMPMMRTPtQ66SghcrPFbWJFZMqNaG+BA@mail.gmail.com> (raw)
Hi hackers,
GRAPH_TABLE COLUMNS expressions that involve collation-dependent functions
or operators fail with:
ERROR: could not determine which collation to use for upper() function
HINT: Use the COLLATE clause to set the collation explicitly.
Setup:
CREATE TABLE vtx (id int PRIMARY KEY, name text);
CREATE TABLE edg (id int PRIMARY KEY,
src int REFERENCES vtx(id),
dst int REFERENCES vtx(id));
INSERT INTO vtx VALUES (1,'Alice'),(2,'Bob'),(3,'Carol');
INSERT INTO edg VALUES (1,1,2),(2,2,3);
CREATE PROPERTY GRAPH g
VERTEX TABLES (vtx KEY (id))
EDGE TABLES (edg KEY (id)
SOURCE KEY (src) REFERENCES vtx (id)
DESTINATION KEY (dst) REFERENCES vtx (id));
postgres=# SELECT * FROM GRAPH_TABLE (g
MATCH (a IS vtx)-[e IS edg]->(b IS vtx) COLUMNS (upper(a.name) AS
src_upper));
ERROR: could not determine which collation to use for upper() function
HINT: Use the COLLATE clause to set the collation explicitly.
In transformRangeGraphTable(), the COLUMNS transformation loop calls
transformExpr()
on each column expression but omits the subsequent assign_expr_collations()
call. Both
WHERE clause transformation sites in parse_graphtable.c correctly include
it.
Attached a patch to fix this.
Thanks,
Satya
Attachments:
[application/octet-stream] 0001-fix-graph-table-columns-collation.patch (3.0K, 3-0001-fix-graph-table-columns-collation.patch)
download | inline diff:
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 967eea44..42d35a18 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -980,6 +980,7 @@ transformRangeGraphTable(ParseState *pstate, RangeGraphTable *rgt)
char *colname;
colexpr = transformExpr(pstate, rt->val, EXPR_KIND_SELECT_TARGET);
+ assign_expr_collations(pstate, colexpr);
if (rt->name)
colname = rt->name;
diff --git a/src/test/regress/expected/graph_table.out b/src/test/regress/expected/graph_table.out
index b579e3df..26ba95e5 100644
--- a/src/test/regress/expected/graph_table.out
+++ b/src/test/regress/expected/graph_table.out
@@ -1022,4 +1022,53 @@ SELECT sname, dname FROM GRAPH_TABLE (g1 MATCH (src)->(dest) WHERE src.vprop1 >
ERROR: subqueries within GRAPH_TABLE reference are not supported
SELECT sname, dname FROM GRAPH_TABLE (g1 MATCH (src)->(dest) WHERE out_degree(src.vname) > (SELECT max(out_degree(nname)) FROM GRAPH_TABLE (g1 MATCH (node) COLUMNS (node.vname AS nname))) COLUMNS(src.vname AS sname, dest.vname AS dname));
ERROR: subqueries within GRAPH_TABLE reference are not supported
+-- collation assignment for COLUMNS expressions
+SELECT * FROM GRAPH_TABLE (g1 MATCH (src)->(dest) COLUMNS (upper(src.vname) AS u)) ORDER BY u;
+ u
+-----
+ V11
+ V11
+ V11
+ V12
+ V13
+ V21
+ V22
+ V23
+ V32
+ V33
+ V33
+(11 rows)
+
+SELECT * FROM GRAPH_TABLE (g1 MATCH (src)->(dest) COLUMNS (src.vname || dest.vname AS combined)) ORDER BY combined;
+ combined
+----------
+ v11v22
+ v11v31
+ v11v33
+ v12v21
+ v13v23
+ v21v12
+ v22v32
+ v23v13
+ v32v22
+ v33v33
+ v33v33
+(11 rows)
+
+SELECT * FROM GRAPH_TABLE (g1 MATCH (src)->(dest) COLUMNS (replace(src.vname, 'S', 'X') AS replaced)) ORDER BY replaced;
+ replaced
+----------
+ v11
+ v11
+ v11
+ v12
+ v13
+ v21
+ v22
+ v23
+ v32
+ v33
+ v33
+(11 rows)
+
-- leave the objects behind for pg_upgrade/pg_dump tests
diff --git a/src/test/regress/sql/graph_table.sql b/src/test/regress/sql/graph_table.sql
index 4ff98817..13ece6d8 100644
--- a/src/test/regress/sql/graph_table.sql
+++ b/src/test/regress/sql/graph_table.sql
@@ -582,4 +582,9 @@ SELECT * FROM customers co WHERE co.customer_id = (SELECT customer_id FROM GRAPH
SELECT sname, dname FROM GRAPH_TABLE (g1 MATCH (src)->(dest) WHERE src.vprop1 > (SELECT max(v1.vprop1) FROM v1) COLUMNS(src.vname AS sname, dest.vname AS dname));
SELECT sname, dname FROM GRAPH_TABLE (g1 MATCH (src)->(dest) WHERE out_degree(src.vname) > (SELECT max(out_degree(nname)) FROM GRAPH_TABLE (g1 MATCH (node) COLUMNS (node.vname AS nname))) COLUMNS(src.vname AS sname, dest.vname AS dname));
+-- collation assignment for COLUMNS expressions
+SELECT * FROM GRAPH_TABLE (g1 MATCH (src)->(dest) COLUMNS (upper(src.vname) AS u)) ORDER BY u;
+SELECT * FROM GRAPH_TABLE (g1 MATCH (src)->(dest) COLUMNS (src.vname || dest.vname AS combined)) ORDER BY combined;
+SELECT * FROM GRAPH_TABLE (g1 MATCH (src)->(dest) COLUMNS (replace(src.vname, 'S', 'X') AS replaced)) ORDER BY replaced;
+
-- leave the objects behind for pg_upgrade/pg_dump tests
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Bug: Missing collation assignment for GRAPH_TABLE COLUMNS expressions
In-Reply-To: <CAHg+QDc4aaiufYSgrwMMPMMRTPtQ66SghcrPFbWJFZMqNaG+BA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox