Re: SQL/JSON: JSON_TRANSFORM (SQL standard, subclause 6.44)

Hi,

changes in v2:

On Thu, Jun 18, 2026 at 9:51 PM Srinath Reddy Sadipiralla <
srinath2133(at)gmail(dot)com> wrote:

> - A column reference or sub-select used as a pathspec or value
> currently crashes the backend (e.g. REPLACE '$.a' = some_column).
>

0001 - The cause was that expression_tree_walker did not go into the
action's sub-expressions (action->pathspec, action->value_expr). Because of
that, expr_setup_walker never counted the value's attribute number, so
last_scan was too low; the EEOP_SCAN_FETCHSOME step then deformed too few
attributes (tts_nvalid), and evaluating the Var failed
Assert(attnum >= 0 && attnum < scanslot->tts_nvalid).

The same gap in expression_tree_mutator caused a segfault with joins,
fix_join_expr runs through the mutator, so the value's Var was never
remapped;
a varno that isn't INNER/OUTER then defaults to a SCAN var, but a join node
has no scan slot, so CheckVarSlotCompatibility dereferenced a NULL scanslot.

Fixed by teaching both the walker and the mutator to traverse
action->pathspec and action->value_expr.

Also fixed the "label followed by a declaration" compile error clang
reported
in the RENAME parse-analysis case.

On Sun, Jun 21, 2026 at 7:18 PM Srinath Reddy Sadipiralla <
srinath2133(at)gmail(dot)com> wrote:

>
> On Sun, Jun 21, 2026 at 3:01 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
> wrote:
>
>>
>>
>> 1. Isn't rename supposed to default to `REPLACE ON EXISTING`? [1]
>>
>> SELECT JSON_TRANSFORM('{"a":1,"b":2}'::jsonb, RENAME '$.a' = 'b'); --
>> returns {"b":2}, shouldn't be {"b":1}?
>>
>
> This is simply a bug on my side. The current {"b":2} is an result of
> jsonb's silent key de-duplication, not intended behavior: renaming '$.a' to
> 'b' produces {"b":1,"b":2}, and since a JSON object can't have duplicate
> keys this should raise an error , which is what both the SQL standard and
> Oracle do:
> - SQL standard: data exception , non-unique keys in a JSON object.
> - Oracle: ORA-40767, "field with this name already exists".
> So it doesn't actually default to REPLACE ON EXISTING, on Oracle this
> raises an error rather than producing {"b":1}. I'll fix RENAME to detect
> the
> collision and raise an error, which matches both the standard and Oracle.
>

0002 - Fixed by enabling object key-uniqueness (parseState->unique_keys =
true) for the object targeted by RENAME, so a collision now raises an error
instead of silently de-duplicating. (It currently reuses jsonb's generic
"duplicate JSON object key value" error)

--
Thanks :)
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/