SQL/JSON: Fix NULL result leakage in JSON_VALUE() RETURNING json/jsonb Once JSON_VALUE(... RETURNING json/jsonb) returned NULL, every later evaluation of the same expression in the same statement also returned NULL, even for inputs that had a valid value.
In ExecEvalJsonExprPath(), the JSON_VALUE_OP branch for RETURNING json or jsonb converts the matched item to its string form but, unlike the sibling branches, never sets *op->resnull to false. So a NULL result from a previous evaluation, whether due to a JSON null item, an empty match, or an error handled by NULL ON ERROR, remained in effect. That caused the subsequent coercion via the type's input function, which is guarded by that flag, to be skipped, and the valid item to be returned as SQL NULL. The result thus depended on what earlier rows, or earlier expressions sharing the same ExprState, had returned. Fix by setting *op->resnull to false in that branch. Also add a comment noting that ExecGetJsonValueItemString() takes care of that in the final branch. Oversight in commit 6185c9737cf, so back-patch to 17. Bug: #19695 Reported-by: Chaitanya Choudhary <[email protected]> Author: Srinath Reddy Sadipiralla <[email protected]> Reviewed-by: Manu <[email protected]> Discussion: https://postgr.es/m/[email protected] Backpatch-through: 17 Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/7150d0fd494166107e6efcc7e786690fd76eff08 Modified Files -------------- src/backend/executor/execExprInterp.c | 2 + src/test/regress/expected/sqljson_queryfuncs.out | 49 ++++++++++++++++++++++++ src/test/regress/sql/sqljson_queryfuncs.sql | 10 +++++ 3 files changed, 61 insertions(+)
