public inbox for [email protected]
help / color / mirror / Atom feedBug: Missing collation assignment for GRAPH_TABLE COLUMNS expressions
6+ messages / 3 participants
[nested] [flat]
* Bug: Missing collation assignment for GRAPH_TABLE COLUMNS expressions
@ 2026-04-10 15:41 SATYANARAYANA NARLAPURAM <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: SATYANARAYANA NARLAPURAM @ 2026-04-10 15:41 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>; Ashutosh Bapat <[email protected]>; Peter Eisentraut <[email protected]>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug: Missing collation assignment for GRAPH_TABLE COLUMNS expressions
@ 2026-04-10 16:25 Ashutosh Bapat <[email protected]>
parent: SATYANARAYANA NARLAPURAM <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Ashutosh Bapat @ 2026-04-10 16:25 UTC (permalink / raw)
To: SATYANARAYANA NARLAPURAM <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Peter Eisentraut <[email protected]>
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.
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.
--
Best Wishes,
Ashutosh Bapat
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug: Missing collation assignment for GRAPH_TABLE COLUMNS expressions
@ 2026-04-10 18:06 SATYANARAYANA NARLAPURAM <[email protected]>
parent: Ashutosh Bapat <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: SATYANARAYANA NARLAPURAM @ 2026-04-10 18:06 UTC (permalink / raw)
To: Ashutosh Bapat <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Peter Eisentraut <[email protected]>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug: Missing collation assignment for GRAPH_TABLE COLUMNS expressions
@ 2026-04-15 15:07 Ashutosh Bapat <[email protected]>
parent: SATYANARAYANA NARLAPURAM <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Ashutosh Bapat @ 2026-04-15 15:07 UTC (permalink / raw)
To: SATYANARAYANA NARLAPURAM <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Peter Eisentraut <[email protected]>
On Fri, Apr 10, 2026 at 11:36 PM SATYANARAYANA NARLAPURAM
<[email protected]> wrote:
>
> 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.
>
If we call assign_expr_collations() on a list, the List expression
also gets a collation, which isn't what we want here. We want to
assign collations to the individual COLUMNs expression independently.
assign_list_collations() is better suited for that. I must say that
your earlier patch had got it right in this regard since it was
calling assign_expr_collations independently on each COLUMNs
expression. However, considering that an all properties reference is
replaced by a list of GraphPropertyRefs in place, I think calling
assign_list_collations() once on all COLUMNs expressions is a
future-proof fix. This is also inline with how collations are assigned
to targetlist expressions in a Query.
>>
>>
>>
>> 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.
I merged this test into an existing test to avoid adding yet another
query in the file that has many many queries already. Yes, an explicit
collation test is not needed separately.
While at it I added comments to explain why we aren't performing
en-masse collation assignment on a GraphTable or GraphPathPattern.
Please let me know what you think of the attached patch.
--
Best Wishes,
Ashutosh Bapat
Attachments:
[text/x-patch] v20260415-0001-Collation-of-expressions-in-GRAPH_TABLE-CO.patch (5.6K, 2-v20260415-0001-Collation-of-expressions-in-GRAPH_TABLE-CO.patch)
download | inline diff:
From 7481f0d29508fc656efdd5ff2a692eeea406ac7a Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <[email protected]>
Date: Wed, 15 Apr 2026 16:18:37 +0530
Subject: [PATCH v20260415 1/3] Collation of expressions in GRAPH_TABLE COLUMNS
clause
GRAPH_TABLE clause is converted into a rangetable entry which is ignored
by assign_query_collations(). Hence we assign collations while
transforming its parts. Expressions in COLUMNS clause missed that
treatment.
While at also add comments about collation assignment to the parts of
GRAPH_TABLE clause and also fix a small grammar issue.
Reported-by: Satyanarayana Narlapuram <[email protected]>
Author: Satyanarayana Narlapuram <[email protected]>
Author: Ashutosh Bapat <[email protected]>
Discussion: https://www.postgresql.org/message-id/CAHg+QDc4aaiufYSgrwMMPMMRTPtQ66SghcrPFbWJFZMqNaG+BA@mail.gmail.com
---
src/backend/parser/parse_clause.c | 6 ++++++
src/backend/parser/parse_graphtable.c | 12 +++++++++++-
src/test/regress/expected/graph_table.out | 8 ++++----
src/test/regress/sql/graph_table.sql | 4 ++--
4 files changed, 23 insertions(+), 7 deletions(-)
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 967eea44f1c..4270c2382c4 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 now since
+ * assign_query_collations() does not process rangetable entries.
+ */
+ assign_list_collations(pstate, columns);
+
table_close(rel, NoLock);
pstate->p_graph_table_pstate = NULL;
diff --git a/src/backend/parser/parse_graphtable.c b/src/backend/parser/parse_graphtable.c
index 30ddce5aa9f..87386859a5c 100644
--- a/src/backend/parser/parse_graphtable.c
+++ b/src/backend/parser/parse_graphtable.c
@@ -252,6 +252,11 @@ transformGraphElementPattern(ParseState *pstate, GraphElementPattern *gep)
gep->labelexpr = transformLabelExpr(gpstate, gep->labelexpr);
gep->whereClause = transformExpr(pstate, gep->whereClause, EXPR_KIND_WHERE);
+
+ /*
+ * Assign collations here for the reason mentioned in the prologue of
+ * transformGraphPattern().
+ */
assign_expr_collations(pstate, gep->whereClause);
gpstate->cur_gep = NULL;
@@ -366,9 +371,14 @@ transformPathPatternList(ParseState *pstate, List *path_pattern)
* Transform a GraphPattern.
*
* A GraphPattern consists of a list of one or more path patterns and an
- * optional where clause. Transform them. We use the previously constructure
+ * optional where clause. Transform them. We use the previously constructed
* list of variables in the GraphTableParseState to resolve property references
* in the WHERE clause.
+ *
+ * Since most parts of the GraphPattern do not require collation assignment, we
+ * assign collations to the required expressions as they are transformed. This
+ * avoids the need to traverse the whole GraphPattern again and avoids exposing
+ * it to assign_expr_collations().
*/
Node *
transformGraphPattern(ParseState *pstate, GraphPattern *graph_pattern)
diff --git a/src/test/regress/expected/graph_table.out b/src/test/regress/expected/graph_table.out
index b579e3df635..057f283c43d 100644
--- a/src/test/regress/expected/graph_table.out
+++ b/src/test/regress/expected/graph_table.out
@@ -652,13 +652,13 @@ SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-(a) COLUMNS (a.vname AS self));
v33
(1 row)
--- test collation specified in the expression
+-- test explicit and implicit collation assignment
INSERT INTO e3_3 VALUES (2003, 2003, 'E331', 10011);
-SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) ORDER BY loop_name COLLATE "C" ASC;
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (upper(a.vname) AS self, b.ename AS loop_name)) ORDER BY loop_name COLLATE "C" ASC;
self | loop_name
------+-----------
- v33 | E331
- v33 | e331
+ V33 | E331
+ V33 | e331
(2 rows)
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));
diff --git a/src/test/regress/sql/graph_table.sql b/src/test/regress/sql/graph_table.sql
index 4ff98817420..278064818ff 100644
--- a/src/test/regress/sql/graph_table.sql
+++ b/src/test/regress/sql/graph_table.sql
@@ -394,9 +394,9 @@ SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(c)-[b]->(d) COLUMNS (a.vname AS an
SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[c]-(a) COLUMNS (a.vname AS self, c.ename AS loop_name));
SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-(a) COLUMNS (a.vname AS self));
--- test collation specified in the expression
+-- test explicit and implicit collation assignment
INSERT INTO e3_3 VALUES (2003, 2003, 'E331', 10011);
-SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) ORDER BY loop_name COLLATE "C" ASC;
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (upper(a.vname) AS self, b.ename AS loop_name)) ORDER BY loop_name COLLATE "C" ASC;
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";
base-commit: f30d0c720f2ec979ab1b5b44b1f9f201d6efdf8c
--
2.34.1
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug: Missing collation assignment for GRAPH_TABLE COLUMNS expressions
@ 2026-04-15 17:00 SATYANARAYANA NARLAPURAM <[email protected]>
parent: Ashutosh Bapat <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: SATYANARAYANA NARLAPURAM @ 2026-04-15 17:00 UTC (permalink / raw)
To: Ashutosh Bapat <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Peter Eisentraut <[email protected]>
Hi Ashutosh,
On Wed, Apr 15, 2026 at 8:07 AM Ashutosh Bapat <[email protected]>
wrote:
> On Fri, Apr 10, 2026 at 11:36 PM SATYANARAYANA NARLAPURAM
> <[email protected]> wrote:
> >
> > 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.
> >
>
> If we call assign_expr_collations() on a list, the List expression
> also gets a collation, which isn't what we want here. We want to
> assign collations to the individual COLUMNs expression independently.
> assign_list_collations() is better suited for that. I must say that
> your earlier patch had got it right in this regard since it was
> calling assign_expr_collations independently on each COLUMNs
> expression. However, considering that an all properties reference is
> replaced by a list of GraphPropertyRefs in place, I think calling
> assign_list_collations() once on all COLUMNs expressions is a
> future-proof fix. This is also inline with how collations are assigned
> to targetlist expressions in a Query.
>
Nice catch!
>
> >>
> >>
> >>
> >> 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.
>
> I merged this test into an existing test to avoid adding yet another
> query in the file that has many many queries already. Yes, an explicit
> collation test is not needed separately.
>
Thanks!
>
> While at it I added comments to explain why we aren't performing
> en-masse collation assignment on a GraphTable or GraphPathPattern.
>
> Please let me know what you think of the attached patch.
>
This LGTM. I reran the tests and all of them are passing.
Thanks,
Satya
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug: Missing collation assignment for GRAPH_TABLE COLUMNS expressions
@ 2026-04-24 06:52 Peter Eisentraut <[email protected]>
parent: Ashutosh Bapat <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Peter Eisentraut @ 2026-04-24 06:52 UTC (permalink / raw)
To: Ashutosh Bapat <[email protected]>; SATYANARAYANA NARLAPURAM <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On 15.04.26 17:07, Ashutosh Bapat wrote:
> I merged this test into an existing test to avoid adding yet another
> query in the file that has many many queries already. Yes, an explicit
> collation test is not needed separately.
>
> While at it I added comments to explain why we aren't performing
> en-masse collation assignment on a GraphTable or GraphPathPattern.
>
> Please let me know what you think of the attached patch.
committed
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-04-24 06:52 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-10 15:41 Bug: Missing collation assignment for GRAPH_TABLE COLUMNS expressions SATYANARAYANA NARLAPURAM <[email protected]>
2026-04-10 16:25 ` Ashutosh Bapat <[email protected]>
2026-04-10 18:06 ` SATYANARAYANA NARLAPURAM <[email protected]>
2026-04-15 15:07 ` Ashutosh Bapat <[email protected]>
2026-04-15 17:00 ` SATYANARAYANA NARLAPURAM <[email protected]>
2026-04-24 06:52 ` Peter Eisentraut <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox