public inbox for [email protected]
help / color / mirror / Atom feedFrom: SATYANARAYANA NARLAPURAM <[email protected]>
To: Ashutosh Bapat <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Subject: Re: Bug: Missing collation assignment for GRAPH_TABLE COLUMNS expressions
Date: Fri, 10 Apr 2026 11:06:05 -0700
Message-ID: <CAHg+QDfdgVT0qZv6fGcxS7Sk1se8yYBsaVUGoQqbLVVs9PsxZQ@mail.gmail.com> (raw)
In-Reply-To: <CAExHW5s5Q4U3GPHaGJMAZBb8=7bm4pDc9_MWi5AsTaFC-OB2HQ@mail.gmail.com>
References: <CAHg+QDc4aaiufYSgrwMMPMMRTPtQ66SghcrPFbWJFZMqNaG+BA@mail.gmail.com>
<CAExHW5s5Q4U3GPHaGJMAZBb8=7bm4pDc9_MWi5AsTaFC-OB2HQ@mail.gmail.com>
Hi Ashutosh,
On Fri, Apr 10, 2026 at 9:25 AM Ashutosh Bapat <[email protected]>
wrote:
> Hi Satya,
> Thanks for the report and patch.
>
> On Fri, Apr 10, 2026 at 9:12 PM SATYANARAYANA NARLAPURAM
> <[email protected]> wrote:
> >
> > 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.
>
> I think the fix is in the right direction. It's better to call
> assign_expr_collation only once on all the columns at the end of loop
> of rgt->columns, just like assign_expr_collation is called on all the
> conditions in WHERE clause once
Addressed this in v2 patch.
>
>
Good to see tests also included in the patch. Do we need all three
> queries? Also those queries should be placed near the section "-- test
> collation specified in the expression" and add a query for explicit
> collation in COLUMNs expression.
>
Removed two tests and moved the test. Explicit collate test already exists.
Thanks,
Satya
Attachments:
[application/octet-stream] v2-0001-fix-graph-table-columns-collation.patch (2.4K, 3-v2-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..2f12ed36 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1003,6 +1003,12 @@ transformRangeGraphTable(ParseState *pstate, RangeGraphTable *rgt)
columns = lappend(columns, te);
}
+ /*
+ * Assign collations to column expressions, just as we do for the WHERE
+ * clause in transformGraphPattern().
+ */
+ assign_expr_collations(pstate, (Node *) columns);
+
table_close(rel, NoLock);
pstate->p_graph_table_pstate = NULL;
diff --git a/src/test/regress/expected/graph_table.out b/src/test/regress/expected/graph_table.out
index b579e3df..0a84c7eb 100644
--- a/src/test/regress/expected/graph_table.out
+++ b/src/test/regress/expected/graph_table.out
@@ -679,6 +679,23 @@ SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS se
v33 | e331
(1 row)
+-- test 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)
+
-- property graph with some of the elements, labels and properties same as the
-- previous one. Test whether components from the specified property graph are
-- used. Also test explicit collation specification in property.
diff --git a/src/test/regress/sql/graph_table.sql b/src/test/regress/sql/graph_table.sql
index 4ff98817..ffb7cbe6 100644
--- a/src/test/regress/sql/graph_table.sql
+++ b/src/test/regress/sql/graph_table.sql
@@ -400,6 +400,8 @@ SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS se
SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b IS el2 WHERE b.ename > 'E331' COLLATE "C"]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name));
SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) WHERE b.ename > 'E331' COLLATE "C" COLUMNS (a.vname AS self, b.ename AS loop_name));
SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) WHERE loop_name > 'E331' COLLATE "C";
+-- test collation assignment for COLUMNS expressions
+SELECT * FROM GRAPH_TABLE (g1 MATCH (src)->(dest) COLUMNS (upper(src.vname) AS u)) ORDER BY u;
-- property graph with some of the elements, labels and properties same as the
-- previous one. Test whether components from the specified property graph are
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+QDfdgVT0qZv6fGcxS7Sk1se8yYBsaVUGoQqbLVVs9PsxZQ@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