pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation
5+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2026-11-06 3d72e63b82 e405a46c45 03550d3cfb c7656ac9e6 JSON_TABLE: fix missing collation assignments
* BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation
@ 2026-10-16 22:51 PG Bug reporting form <noreply@postgresql.org>
2026-10-16 23:06 ` Re: BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation Venu K <venu2805k@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2026-10-16 22:51 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: venu2805k@gmail.com
The following bug has been logged on the website:
Bug reference: 19768
Logged by: Venu Kummari
Email address: venu2805k@gmail.com
PostgreSQL version: 18.6
Operating system: Linux x86_64
Description:
PostgreSQL versions: 17.11, 18.6, master (26bd08f). Tested from source on
Linux x86_64.
The JSON_TABLE context-item expression fails to assign collations correctly,
causing incorrect results for row comparisons and collation errors for valid
text comparisons.
1. Incorrect result / assertion failure:
SELECT * FROM JSON_TABLE(
jsonb_build_object('k', ROW(1, 2) < ROW(3, 4)),
'$'
COLUMNS (a bool PATH '$.k')
);
Expected: t
Actual: f on PostgreSQL 17.11 and 18.6. On an assertion-enabled master
build, the backend fails with:
TRAP: failed Assert("list_length(rcexpr->inputcollids) == nopers"), File:
"execExpr.c", Line: 2078
2. Collation error in the context item:
SELECT * FROM JSON_TABLE(
jsonb_build_object('k', 'a' < 'b' COLLATE "C"),
'$'
COLUMNS (a bool PATH '$.k')
);
Expected: t
Actual: ERROR: could not determine which collation to use for string
comparison
3. Collation error in a PASSING argument:
SELECT * FROM JSON_TABLE(
jsonb '{}',
'$' PASSING 'a' < 'b' COLLATE "C" AS x
COLUMNS (a bool PATH '$x')
);
This also fails with "could not determine which collation to use for string
comparison", despite the explicit COLLATE clause.
The same expressions work outside JSON_TABLE.
Suspected cause: transformJsonTable() in
src/backend/parser/parse_jsontable.c does not call assign_expr_collations()
for the context-item expression and PASSING arguments. The corresponding
XMLTABLE transformation performs collation assignment, as do JSON_TABLE
column expressions.
Without the required collation information, a RowCompareExpr can have an
empty inputcollids list, causing the executor to compare zero columns and
return an incorrect result.
A proposed patch adds the missing collation assignment and regression tests.
The patch was tested on master, and the core regression suite passed
(239/239 tests). The source change applies to REL_17_STABLE and
REL_18_STABLE.
The bug was discovered through grammar-based fuzzing.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation
2026-10-16 22:51 BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation PG Bug reporting form <noreply@postgresql.org>
@ 2026-10-16 23:06 ` Venu K <venu2805k@gmail.com>
2026-10-23 19:14 ` Re: BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation Venu K <venu2805k@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Venu K @ 2026-10-16 23:06 UTC (permalink / raw)
To: venu2805k@gmail.com; pgsql-bugs@lists.postgresql.org
Hello,
Please find attached a proposed patch for BUG #19768.
The patch adds the missing collation assignment in JSON_TABLE expression
transformation and includes regression tests for the reported incorrect
row-comparison result and collation-resolution errors.
The patch was tested on master, and the core regression suite passed
(239/239 tests).
Regards,
Venu Kummari
On Sat, Oct 17, 2026 at 4:21 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 19768
> Logged by: Venu Kummari
> Email address: venu2805k@gmail.com
> PostgreSQL version: 18.6
> Operating system: Linux x86_64
> Description:
>
> PostgreSQL versions: 17.11, 18.6, master (26bd08f). Tested from source on
> Linux x86_64.
>
> The JSON_TABLE context-item expression fails to assign collations
> correctly,
> causing incorrect results for row comparisons and collation errors for
> valid
> text comparisons.
>
> 1. Incorrect result / assertion failure:
>
> SELECT * FROM JSON_TABLE(
> jsonb_build_object('k', ROW(1, 2) < ROW(3, 4)),
> '$'
> COLUMNS (a bool PATH '$.k')
> );
>
> Expected: t
> Actual: f on PostgreSQL 17.11 and 18.6. On an assertion-enabled master
> build, the backend fails with:
>
> TRAP: failed Assert("list_length(rcexpr->inputcollids) == nopers"), File:
> "execExpr.c", Line: 2078
>
> 2. Collation error in the context item:
>
> SELECT * FROM JSON_TABLE(
> jsonb_build_object('k', 'a' < 'b' COLLATE "C"),
> '$'
> COLUMNS (a bool PATH '$.k')
> );
>
> Expected: t
> Actual: ERROR: could not determine which collation to use for string
> comparison
>
> 3. Collation error in a PASSING argument:
>
> SELECT * FROM JSON_TABLE(
> jsonb '{}',
> '$' PASSING 'a' < 'b' COLLATE "C" AS x
> COLUMNS (a bool PATH '$x')
> );
>
> This also fails with "could not determine which collation to use for string
> comparison", despite the explicit COLLATE clause.
>
> The same expressions work outside JSON_TABLE.
>
> Suspected cause: transformJsonTable() in
> src/backend/parser/parse_jsontable.c does not call assign_expr_collations()
> for the context-item expression and PASSING arguments. The corresponding
> XMLTABLE transformation performs collation assignment, as do JSON_TABLE
> column expressions.
>
> Without the required collation information, a RowCompareExpr can have an
> empty inputcollids list, causing the executor to compare zero columns and
> return an incorrect result.
>
> A proposed patch adds the missing collation assignment and regression
> tests.
> The patch was tested on master, and the core regression suite passed
> (239/239 tests). The source change applies to REL_17_STABLE and
> REL_18_STABLE.
>
> The bug was discovered through grammar-based fuzzing.
>
>
>
Attachments:
v1-0001-Assign-collations-to-JSON_TABLE-s-context-item-an.patch (application/octet-stream, 3.6K) download
From b73becf0eac28d6df9d0de859454f05e0270d18e Mon Sep 17 00:00:00 2001
From: Venu Kummari <venu2805k@gmail.com>
Date: Fri, 16 Oct 2026 22:59:44 +0000
Subject: [PATCH v1] Assign collations to JSON_TABLE's context item and PASSING
arguments
transformJsonTable() transforms the context item and PASSING arguments
into TableFunc.docexpr (and copies the PASSING arguments into
passingvalexprs), but unlike XMLTABLE it never ran
assign_expr_collations() over the result. Expressions in there were
therefore 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 on
assert-enabled builds.
Found by grammar-based fuzzing.
Bug: #19768
---
src/backend/parser/parse_jsontable.c | 1 +
.../regress/expected/sqljson_jsontable.out | 29 +++++++++++++++++++
src/test/regress/sql/sqljson_jsontable.sql | 10 +++++++
3 files changed, 40 insertions(+)
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index d86c694..55f0a81 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -155,6 +155,7 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
jfe->on_error = jt->on_error;
jfe->location = jt->location;
tf->docexpr = transformExpr(pstate, (Node *) jfe, EXPR_KIND_FROM_FUNCTION);
+ assign_expr_collations(pstate, tf->docexpr);
/*
* Create a JsonTablePlan that will generate row pattern that becomes
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index ae64dbe..acdca93 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -1926,3 +1926,32 @@ CREATE OR REPLACE VIEW public.json_table_view_on_empty AS
) ERROR ON ERROR
)
DROP VIEW json_table_view_on_empty;
+-- Collations must be assigned in the context item and PASSING arguments
+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 2a33aae..7d9d6e8 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -1052,3 +1052,13 @@ SELECT * FROM JSON_TABLE(jsonb '{}', '$' AS p0
\sv json_table_view_on_empty;
DROP VIEW json_table_view_on_empty;
+
+-- Collations must be assigned in the context item and PASSING arguments
+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'));
--
2.43.0
* Re: BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation
2026-10-16 22:51 BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation PG Bug reporting form <noreply@postgresql.org>
2026-10-16 23:06 ` Re: BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation Venu K <venu2805k@gmail.com>
@ 2026-10-23 19:14 ` Venu K <venu2805k@gmail.com>
2026-11-06 02:31 ` Re: BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation Amit Langote <amitlangote09@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Venu K @ 2026-10-23 19:14 UTC (permalink / raw)
To: venu2805k@gmail.com; pgsql-bugs@lists.postgresql.org; +Cc: amitlan@postgresql.org
Hi,
Gentle ping on this. The bug still reproduces on current master
(126976a), and the patch upthread still applies cleanly there. The
one-line fix in parse_jsontable.c also applies to REL_17_STABLE and
REL_18_STABLE; the regression test hunks need minor adjustment on
those branches.
CC'ing Amit as the committer of JSON_TABLE.
Thanks,
Venu
On Sat, Oct 17, 2026 at 4:36 AM Venu K <venu2805k@gmail.com> wrote:
>
> Hello,
>
> Please find attached a proposed patch for BUG #19768.
>
> The patch adds the missing collation assignment in JSON_TABLE expression transformation and includes regression tests for the reported incorrect row-comparison result and collation-resolution errors.
>
> The patch was tested on master, and the core regression suite passed (239/239 tests).
>
> Regards,
> Venu Kummari
>
>
> On Sat, Oct 17, 2026 at 4:21 AM PG Bug reporting form <noreply@postgresql.org> wrote:
>>
>> The following bug has been logged on the website:
>>
>> Bug reference: 19768
>> Logged by: Venu Kummari
>> Email address: venu2805k@gmail.com
>> PostgreSQL version: 18.6
>> Operating system: Linux x86_64
>> Description:
>>
>> PostgreSQL versions: 17.11, 18.6, master (26bd08f). Tested from source on
>> Linux x86_64.
>>
>> The JSON_TABLE context-item expression fails to assign collations correctly,
>> causing incorrect results for row comparisons and collation errors for valid
>> text comparisons.
>>
>> 1. Incorrect result / assertion failure:
>>
>> SELECT * FROM JSON_TABLE(
>> jsonb_build_object('k', ROW(1, 2) < ROW(3, 4)),
>> '$'
>> COLUMNS (a bool PATH '$.k')
>> );
>>
>> Expected: t
>> Actual: f on PostgreSQL 17.11 and 18.6. On an assertion-enabled master
>> build, the backend fails with:
>>
>> TRAP: failed Assert("list_length(rcexpr->inputcollids) == nopers"), File:
>> "execExpr.c", Line: 2078
>>
>> 2. Collation error in the context item:
>>
>> SELECT * FROM JSON_TABLE(
>> jsonb_build_object('k', 'a' < 'b' COLLATE "C"),
>> '$'
>> COLUMNS (a bool PATH '$.k')
>> );
>>
>> Expected: t
>> Actual: ERROR: could not determine which collation to use for string
>> comparison
>>
>> 3. Collation error in a PASSING argument:
>>
>> SELECT * FROM JSON_TABLE(
>> jsonb '{}',
>> '$' PASSING 'a' < 'b' COLLATE "C" AS x
>> COLUMNS (a bool PATH '$x')
>> );
>>
>> This also fails with "could not determine which collation to use for string
>> comparison", despite the explicit COLLATE clause.
>>
>> The same expressions work outside JSON_TABLE.
>>
>> Suspected cause: transformJsonTable() in
>> src/backend/parser/parse_jsontable.c does not call assign_expr_collations()
>> for the context-item expression and PASSING arguments. The corresponding
>> XMLTABLE transformation performs collation assignment, as do JSON_TABLE
>> column expressions.
>>
>> Without the required collation information, a RowCompareExpr can have an
>> empty inputcollids list, causing the executor to compare zero columns and
>> return an incorrect result.
>>
>> A proposed patch adds the missing collation assignment and regression tests.
>> The patch was tested on master, and the core regression suite passed
>> (239/239 tests). The source change applies to REL_17_STABLE and
>> REL_18_STABLE.
>>
>> The bug was discovered through grammar-based fuzzing.
>>
>>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation
2026-10-16 22:51 BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation PG Bug reporting form <noreply@postgresql.org>
2026-10-16 23:06 ` Re: BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation Venu K <venu2805k@gmail.com>
2026-10-23 19:14 ` Re: BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation Venu K <venu2805k@gmail.com>
@ 2026-11-06 02:31 ` Amit Langote <amitlangote09@gmail.com>
2026-11-06 08:34 ` Re: BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation Amit Langote <amitlangote09@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Amit Langote @ 2026-11-06 02:31 UTC (permalink / raw)
To: Venu K <venu2805k@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org; amitlan@postgresql.org
Hi,
On Sat, Oct 24, 2026 at 4:14 AM Venu K <venu2805k@gmail.com> wrote:
> Gentle ping on this. The bug still reproduces on current master
> (126976a), and the patch upthread still applies cleanly there. The
> one-line fix in parse_jsontable.c also applies to REL_17_STABLE and
> REL_18_STABLE; the regression test hunks need minor adjustment on
> those branches.
>
> CC'ing Amit as the committer of JSON_TABLE.
Thanks for notifying me.
Patch looks good. Here is v2, which I intend to commit down to 17 by
tomorrow barring objections.
--
Thanks, Amit Langote
Attachments: download all (2 files, .tar.gz)
v2-master-0001-JSON_TABLE-fix-missing-collation-assignmen.patch (application/octet-stream, 4.5K) download
From 079e1d30c443a9566506a28420461938b68ef6f8 Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Fri, 6 Nov 2026 11:11:45 +0900
Subject: [PATCH v2-master] 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 +++++
.../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 0b469ba636f..801bcb461bf 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -162,6 +162,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 fd5e32160d4..1dcb9479de3 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -2019,3 +2019,33 @@ CREATE OR REPLACE VIEW public.json_table_view_on_empty AS
) ERROR ON ERROR
)
DROP VIEW json_table_view_on_empty;
+-- 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 c24d9ffdd29..73e60411df6 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -1106,3 +1106,14 @@ SELECT * FROM JSON_TABLE(jsonb '{}', '$' AS p0
\sv json_table_view_on_empty;
DROP VIEW json_table_view_on_empty;
+
+-- 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'));
--
2.47.3
v2-REL_17-19-0001-JSON_TABLE-fix-missing-collation-assign.patch (application/octet-stream, 4.5K) download
From 8ccb3f1043c6b58d48895273a558674d07f0d5c6 Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Fri, 6 Nov 2026 11:29:02 +0900
Subject: [PATCH v2-REL_17-19] 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 +++++
.../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..7c7399928b8 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'));
--
2.47.3
* Re: BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation
2026-10-16 22:51 BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation PG Bug reporting form <noreply@postgresql.org>
2026-10-16 23:06 ` Re: BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation Venu K <venu2805k@gmail.com>
2026-10-23 19:14 ` Re: BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation Venu K <venu2805k@gmail.com>
2026-11-06 02:31 ` Re: BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation Amit Langote <amitlangote09@gmail.com>
@ 2026-11-06 08:34 ` Amit Langote <amitlangote09@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Amit Langote @ 2026-11-06 08:34 UTC (permalink / raw)
To: Venu K <venu2805k@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org; amitlan@postgresql.org
On Fri, Nov 6, 2026 at 11:31 AM Amit Langote <amitlangote09@gmail.com> wrote:
>
> Hi,
>
> On Sat, Oct 24, 2026 at 4:14 AM Venu K <venu2805k@gmail.com> wrote:
> > Gentle ping on this. The bug still reproduces on current master
> > (126976a), and the patch upthread still applies cleanly there. The
> > one-line fix in parse_jsontable.c also applies to REL_17_STABLE and
> > REL_18_STABLE; the regression test hunks need minor adjustment on
> > those branches.
> >
> > CC'ing Amit as the committer of JSON_TABLE.
>
> Thanks for notifying me.
>
> Patch looks good. Here is v2, which I intend to commit down to 17 by
> tomorrow barring objections.
Pushed.
--
Thanks, Amit Langote
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-11-06 08:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-16 22:51 BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation PG Bug reporting form <noreply@postgresql.org>
2026-10-16 23:06 ` Venu K <venu2805k@gmail.com>
2026-10-23 19:14 ` Venu K <venu2805k@gmail.com>
2026-11-06 02:31 ` Amit Langote <amitlangote09@gmail.com>
2026-11-06 08:34 ` Amit Langote <amitlangote09@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox