Re: BUG #19768: JSON_TABLE: row/text comparisons in context item or PASSING give wrong results or spurious collation

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(at)gmail(dot)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(at)postgresql(dot)org> wrote:
>>
>> The following bug has been logged on the website:
>>
>> Bug reference: 19768
>> Logged by: Venu Kummari
>> Email address: venu2805k(at)gmail(dot)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.
>>
>>