postgres.git / summary / log / commit / refs

commit    03550d3cfb941af2cc5312d8b8efcf90b8d8dbec
Author:   Amit Langote <amitlan@postgresql.org>
Date:     Fri Nov 06 07:18:29 2026 +0000

    JSON_TABLE: fix missing collation assignments
    
    transformJsonTable() transforms the context item and the PASSING
    arguments into the JsonExpr stored in TableFunc.docexpr, and copies the
    transformed PASSING arguments into TableFunc.passingvalexprs, but never
    ran assign_expr_collations() over them.  That is needed here because
    assign_query_collations() doesn't look into the range table, which is
    why transformRangeTableFunc() does it for XMLTABLE, as does
    transformJsonTableColumn() for JSON_TABLE's own column expressions.
    
    As a result, expressions in there were left without collations.  A text
    comparison failed with "could not determine which collation to use for
    string comparison", even with an explicit COLLATE clause, and a row
    comparison was left with an empty inputcollids list, which made the
    executor compare zero columns and return the result for equal rows (so
    ROW(1, 2) < ROW(3, 4) yielded false), or trip an assertion in
    ExecInitExprRec() in assert-enabled builds.
    
    Fix by calling assign_expr_collations() on the transformed JsonExpr,
    before the PASSING arguments are copied out of it.
    
    Oversight in commit de3600452b6, so back-patch to 17.
    
    Bug: #19768
    Reported-by: Venu Kummari <venu2805k@gmail.com>
    Author: Venu Kummari <venu2805k@gmail.com>
    Discussion: https://postgr.es/m/19768-3633daa830be95db@postgresql.org
    Backpatch-through: 17


 src/backend/parser/parse_jsontable.c            |  8 +++++++
 src/test/regress/expected/sqljson_jsontable.out | 30 +++++++++++++++++++++++++
 src/test/regress/sql/sqljson_jsontable.sql      | 11 +++++++++
 3 files changed, 49 insertions(+)

diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index 7a71f6a6363..bde20a5c43b 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -136,6 +136,14 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
 	jfe->location = jt->location;
 	tf->docexpr = transformExpr(pstate, (Node *) jfe, EXPR_KIND_FROM_FUNCTION);
 
+	/*
+	 * Assign collations now, because assign_query_collations() doesn't look
+	 * into the range table.  This must happen before the PASSING arguments
+	 * are copied into tf->passingvalexprs below, so that the copies carry the
+	 * collations too.
+	 */
+	assign_expr_collations(pstate, tf->docexpr);
+
 	/*
 	 * Create a JsonTablePlan that will generate row pattern that becomes
 	 * source data for JSON path expressions in jt->columns.  This also adds
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index fe4a0f54590..14c396eeed3 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -1286,3 +1286,33 @@ CREATE OR REPLACE VIEW public.json_table_view9 AS
             )
         )
 DROP VIEW json_table_view8, json_table_view9;
+-- Collations must be assigned to expressions in the context item and in
+-- the PASSING arguments (bug #19768)
+SELECT * FROM JSON_TABLE(jsonb_build_object('k', ROW(1, 2) < ROW(3, 4)), '$'
+	COLUMNS (a bool PATH '$.k'));
+ a 
+---
+ t
+(1 row)
+
+SELECT * FROM JSON_TABLE(jsonb_build_object('k', 'a' < 'b' COLLATE "C"), '$'
+	COLUMNS (a bool PATH '$.k'));
+ a 
+---
+ t
+(1 row)
+
+SELECT * FROM JSON_TABLE(jsonb '{}', '$' PASSING 'a' < 'b' COLLATE "C" AS x
+	COLUMNS (a bool PATH '$x'));
+ a 
+---
+ t
+(1 row)
+
+SELECT * FROM JSON_TABLE(jsonb '{}', '$' PASSING ROW(1, 2) < ROW(3, 4) AS x
+	COLUMNS (a bool PATH '$x'));
+ a 
+---
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index 60ce4f0429f..ecefe9b765c 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -624,3 +624,14 @@ CREATE VIEW json_table_view9 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a t
 \sv json_table_view9;
 
 DROP VIEW json_table_view8, json_table_view9;
+
+-- Collations must be assigned to expressions in the context item and in
+-- the PASSING arguments (bug #19768)
+SELECT * FROM JSON_TABLE(jsonb_build_object('k', ROW(1, 2) < ROW(3, 4)), '$'
+	COLUMNS (a bool PATH '$.k'));
+SELECT * FROM JSON_TABLE(jsonb_build_object('k', 'a' < 'b' COLLATE "C"), '$'
+	COLUMNS (a bool PATH '$.k'));
+SELECT * FROM JSON_TABLE(jsonb '{}', '$' PASSING 'a' < 'b' COLLATE "C" AS x
+	COLUMNS (a bool PATH '$x'));
+SELECT * FROM JSON_TABLE(jsonb '{}', '$' PASSING ROW(1, 2) < ROW(3, 4) AS x
+	COLUMNS (a bool PATH '$x'));


[parent: 2a3ac15548c6]