[PATCH]Fix planner's strictness check for JsonExpr
Hi, while testing JSON PASSING, I ran into a minor strictness handling bug.
JsonExpr is implicitly treated as a strict node in
contain_nonstrict_functions_walker(), but it
isn't actually strict with respect to its PASSING arguments. For instance:
```sql
SELECT v FROM (VALUES (1)) a
LEFT JOIN (SELECT json_value('1', '$' PASSING y AS p) v
FROM (VALUES (1),(2)) b(y)) ss ON false;
-- Return 1, not correct
-- pull-up plan
EXPLAIN (COSTS OFF)
SELECT v FROM (VALUES (1)) a
LEFT JOIN (SELECT json_value('1', '$' PASSING y AS p) v
FROM (VALUES (1),(2)) b(y)) ss ON false;
QUERY PLAN
------------------------------------
Nested Loop Left Join
Disabled: true
Join Filter: false
-> Result
-> Result
Replaces: Scan on *VALUES*
One-Time Filter: false
Optimizer: Postgres-based planner
(8 rows)
-- Using OFFSET 0 to prevent pulling up
SELECT v FROM (VALUES (1)) a
LEFT JOIN (SELECT json_value('1', '$' PASSING y AS p) v
FROM (VALUES (1),(2)) b(y) OFFSET 0) ss ON false;
-- Return NULL, correct
```
Fix by treating JsonExpr as non-strict, as we already do for CASE,
COALESCE and similar constructs. This is conservative -- a JsonExpr
with no PASSING arguments and constant ON EMPTY / ON ERROR behaviors
is in fact strict -- but distinguishing those cases hardly seems worth
the trouble.
Patch attached.
Regards,
Zhenglong Li
On Monday, August 24th, 2026 at 6:13 AM, Keyerror Smart <smartkeyerror@gmail.com> wrote:
Hi, while testing JSON PASSING, I ran into a minor strictness handling bug.
JsonExpr is implicitly treated as a strict node in contain_nonstrict_functions_walker(), but it
isn't actually strict with respect to its PASSING arguments. For instance:
```sqlSELECT v FROM (VALUES (1)) a
LEFT JOIN (SELECT json_value('1', '$' PASSING y AS p) v
FROM (VALUES (1),(2)) b(y)) ss ON false;
-- Return 1, not correct-- pull-up plan
EXPLAIN (COSTS OFF)
SELECT v FROM (VALUES (1)) a
LEFT JOIN (SELECT json_value('1', '$' PASSING y AS p) v
FROM (VALUES (1),(2)) b(y)) ss ON false;
QUERY PLAN
------------------------------------
Nested Loop Left Join
Disabled: true
Join Filter: false
-> Result
-> Result
Replaces: Scan on *VALUES*
One-Time Filter: false
Optimizer: Postgres-based planner
(8 rows)-- Using OFFSET 0 to prevent pulling up
SELECT v FROM (VALUES (1)) a
LEFT JOIN (SELECT json_value('1', '$' PASSING y AS p) v
FROM (VALUES (1),(2)) b(y) OFFSET 0) ss ON false;
-- Return NULL, correct
```Fix by treating JsonExpr as non-strict, as we already do for CASE,
COALESCE and similar constructs. This is conservative -- a JsonExpr
with no PASSING arguments and constant ON EMPTY / ON ERROR behaviors
is in fact strict -- but distinguishing those cases hardly seems worth
the trouble.Patch attached.
Regards,
Zhenglong Li
Hello Zhenglong Li,
I just happened upon this same issue yesterday in my testing. I was about
to start a thread when I found yours so I reviewed your patch.
The diagnosis is right, JsonExpr is not strict with respect to its PASSING
arguments, so treating it as non-strict in contain_nonstrict_functions_walker()
is the correct call, and matching CASE/COALESCE seems to me to be the natural
place for it.
So +1 from me on the approach and the patch. The conservative choice seems
right, that case is rare and the cost of getting it wrong is a wrong answer.
best.
-greg
Hi Greg Burd,
Thanks for the review and the +1, and glad to hear you ran into the
same issue.
Regards,
Zhenglong Li
Greg Burd <gregburd@protonmail.com> 于2026年10月21日周三 02:56写道:
Show quoted text
On Monday, August 24th, 2026 at 6:13 AM, Keyerror Smart <
smartkeyerror@gmail.com> wrote:Hi, while testing JSON PASSING, I ran into a minor strictness handling
bug.
JsonExpr is implicitly treated as a strict node in
contain_nonstrict_functions_walker(), but it
isn't actually strict with respect to its PASSING arguments. For
instance:
```sqlSELECT v FROM (VALUES (1)) a
LEFT JOIN (SELECT json_value('1', '$' PASSING y AS p) v
FROM (VALUES (1),(2)) b(y)) ss ON false;
-- Return 1, not correct-- pull-up plan
EXPLAIN (COSTS OFF)
SELECT v FROM (VALUES (1)) a
LEFT JOIN (SELECT json_value('1', '$' PASSING y AS p) v
FROM (VALUES (1),(2)) b(y)) ss ON false;
QUERY PLAN
------------------------------------
Nested Loop Left Join
Disabled: true
Join Filter: false
-> Result
-> Result
Replaces: Scan on *VALUES*
One-Time Filter: false
Optimizer: Postgres-based planner
(8 rows)-- Using OFFSET 0 to prevent pulling up
SELECT v FROM (VALUES (1)) a
LEFT JOIN (SELECT json_value('1', '$' PASSING y AS p) v
FROM (VALUES (1),(2)) b(y) OFFSET 0) ss ON false;
-- Return NULL, correct
```Fix by treating JsonExpr as non-strict, as we already do for CASE,
COALESCE and similar constructs. This is conservative -- a JsonExpr
with no PASSING arguments and constant ON EMPTY / ON ERROR behaviors
is in fact strict -- but distinguishing those cases hardly seems worth
the trouble.Patch attached.
Regards,
Zhenglong LiHello Zhenglong Li,
I just happened upon this same issue yesterday in my testing. I was about
to start a thread when I found yours so I reviewed your patch.The diagnosis is right, JsonExpr is not strict with respect to its PASSING
arguments, so treating it as non-strict in
contain_nonstrict_functions_walker()
is the correct call, and matching CASE/COALESCE seems to me to be the
natural
place for it.So +1 from me on the approach and the patch. The conservative choice seems
right, that case is rare and the cost of getting it wrong is a wrong
answer.best.
-greg
Show quoted text
Hi Zhenglong,
This is another review of CF 7186 v1 [1].Contents & Purpose
==================
The patch treats JsonExpr as non-strict so that outer-join pull-up and
STRICT SQL-function inlining preserves NULL semantics. It includes a
regression test for the outer-join case.Initial Run
==================
The patch applies cleanly to HEAD. Baseline and patched
builds each pass all 239 core tests, and the patched build passes all 582
make installcheck-world tests. TAP was disabled; PL/Perl, PL/Python and
SSL was not configured.Performance
==================
The conservative check prevents the safe inlining of a context-only wrapper:CREATE FUNCTION value_ctx(text) RETURNS text
LANGUAGE sql IMMUTABLE STRICT
AS SELECTJSONVALUE($1::jsonb,'$');SELECT sum(length(value_ctx(doc))) FROM perf;
With 100,000 rows, median tested execution time increased
from 34.9 ms to 137.3 ms, about 3.93x on macOS with JIT and parallelism
off.Nitpicking & Conclusion
==================
Nit: could you add a STRICT-inlining regression test alongside the outer-join
test? For example:CREATE TEMP TABLE args(x integer);
INSERT INTO args VALUES (NULL), (1);
CREATE FUNCTION pg_temp.f(integer) RETURNS text
LANGUAGE sql IMMUTABLE STRICT
AS SELECTJSONVALUE('1','$'PASSING$1ASp);SELECT x, pg_temp.f(x) FROM args;
For the NULL row, unpatched master returns '1'; v1 correctly returns NULL.
This approach looks appropriate for the correctness fix.
Since this is a correctness fix, preserving safe context-only inlining could be a follow-up optimization.
+1 from me on the approach and the patch.References
==================
[1] CF 7186, 2026-08-24, [PATCH]Fix planner's strictness check for JsonExpr
/messages/by-id/CAD=-kXbK=GGAAK62N-r6tKuU2dVmtJ4GvHTr=CJJGHczuuUGCQ@mail.gmail.com
Hi Ethan,
Thanks for the thorough review, and for the +1.
The conservative check prevents the safe inlining of a context-only
wrapper:
...
With 100,000 rows, median tested execution time increased
from 34.9 ms to 137.3 ms, about 3.93x on macOS with JIT and parallelism
off.
Thanks for measuring this, I overlooked the performance issue with inlining,
and can reproduce in my local env.
In v1 I wrote that distinguishing the strict cases "hardly seems worth
the trouble", but your numbers show otherwise, and since this is meant
to be back-patched to v17, I'd rather not have people's existing
context-only wrappers slow down by ~4x in a minor release. So rather
than leaving it as a follow-up, the attached v2 makes the check precise.
`ExecInitJsonExpr()` returns NULL as soon as the context item or the
path specification evaluates to NULL, before PASSING arguments, the
jsonpath evaluation or the ON EMPTY / ON ERROR behaviors are even
looked at. So a JsonExpr is strict with respect to those two inputs,
and the non-strictness can only come from:
1. PASSING arguments, and
2. inputs of the ON EMPTY / ON ERROR expressions (the parser rejects
column references in DEFAULT, but not Params, so e.g.
"DEFAULT $1 + 0 ON EMPTY" inside a SQL function body is accepted).
v2 therefore treats a `JsonExpr` as non-strict if it has any PASSING
argument or a non-Const ON EMPTY / ON ERROR expression, and otherwise
checks only formatted_expr and path_spec. The Const test is a bit
conservative (a DEFAULT that needs a coercion is not a bare Const), but
the default behaviors are, which covers the common case. Note that we
can't just let expression_tree_walker() descend into the JsonExpr, as
it would then see the PASSING Vars/Params as perfectly strict inputs.
Nit: could you add a STRICT-inlining regression test alongside the
outer-join test?
Done, using your example. v2 now has these tests:
- outer-join pull-up with PASSING (as in v1)
- STRICT SQL function with PASSING $1 (your example)
- STRICT SQL function with DEFAULT $1 + 0 ON EMPTY
- STRICT SQL function that only uses its argument as the context item:
EXPLAIN VERBOSE shows it is inlined, plus a check of the results
The first three return a wrong non-NULL value on unpatched master and
NULL with v2. With v2, value_ctx() is inlined again, so the plan is the
same as on unpatched master. Likewise, a context-only JSON_VALUE() in a
subquery pulled up under an outer join no longer gets an unnecessary
PlaceHolderVar, which v1 added.
Greg, since v2 changes the approach from "always non-strict" to the
precise check, another look would be welcome if you have time.
Regards,
Zhenglong Li
<ethantran@quwin.dev> 于2026年10月29日周四 10:47写道:
Show quoted text
Hi Zhenglong,
This is another review of CF 7186 v1 [1].
Contents & Purpose
==================
The patch treats JsonExpr as non-strict so that outer-join pull-up and
STRICT SQL-function inlining preserves NULL semantics. It includes a
regression test for the outer-join case.
Initial Run
==================
The patch applies cleanly to HEAD. Baseline and patched
builds each pass all 239 core tests, and the patched build passes all 582
make installcheck-world tests. TAP was disabled; PL/Perl, PL/Python and
SSL was not configured.
Performance
==================
The conservative check prevents the safe inlining of a context-only
wrapper:CREATE FUNCTION value_ctx(text) RETURNS text
LANGUAGE sql IMMUTABLE STRICT
AS SELECTJSONVALUE($1::jsonb,'$');
SELECT sum(length(value_ctx(doc))) FROM perf;
With 100,000 rows, median tested execution time increased
from 34.9 ms to 137.3 ms, about 3.93x on macOS with JIT and parallelism
off.
Nitpicking & Conclusion
==================
Nit: could you add a STRICT-inlining regression test alongside the
outer-jointest? For example:
CREATE TEMP TABLE args(x integer);
INSERT INTO args VALUES (NULL), (1);
CREATE FUNCTION pg_temp.f(integer) RETURNS text
LANGUAGE sql IMMUTABLE STRICT
AS SELECTJSONVALUE('1','$'PASSING$1ASp);
SELECT x, pg_temp.f(x) FROM args;
For the NULL row, unpatched master returns '1'; v1 correctly returns NULL.
This approach looks appropriate for the correctness fix.
Since this is a correctness fix, preserving safe context-only inlining
could be a follow-up optimization.+1 from me on the approach and the patch.
References
==================
[1] CF 7186, 2026-08-24, [PATCH]Fix planner's strictness check for JsonExpr
/messages/by-id/CAD=-kXbK=GGAAK62N-r6tKuU2dVmtJ4GvHTr=CJJGHczuuUGCQ@mail.gmail.com
Hi Zhenglong,
I found one remaining correctness issue involving result-domain coercion:
ExecInitJsonExpr() returns NULL as soon as the context item or the
path specification evaluates to NULL
Result coercion still runs after that shortcut. A RETURNING domain with
NOT NULL can reject NULL and invoke EMPTY ARRAY ON ERROR, producing [].
With v2, the attached reproducer returns [] for a NULL argument to an
inlined STRICT wrapper; preventing inlining returns SQL NULL.
Outer-join pull-up has the same problem.
In local testing, a conservative domain guard fixes these cases, but also
disables some safe inlining. An unconstrained-domain JSON_QUERY wrapper went
from about 35 ms to 196 ms over 100,000 rows (5.6x), while direct-expression
timings remained comparable.
Should we preserve unconstrained-domain inlining, with cached plans rebuilt
if constraints are added to the domain or its parents?
Thanks,
Ethan
Attached: domain-reproducer.sql

Attachments:
Hi Ethan,
On Wed, Nov 4, 2026 at 11:05 PM Ethan Tran <ethantran@quwin.dev> wrote:
Result coercion still runs after that shortcut. A RETURNING domain with
NOT NULL can reject NULL and invoke EMPTY ARRAY ON ERROR, producing [].
You're right, thanks for catching this. My description in v2 was
wrong: the NULL produced for a NULL context item or path specification
doesn't go straight to the end, it falls through into the result
coercion step, and when RETURNING a domain the ON ERROR steps are
always emitted. So a NOT NULL domain turns the NULL into a soft error,
and a non-NULL ON ERROR behavior then replaces it. I can reproduce
both the inlining and the outer-join pull-up cases. With the attached
v3, all four queries in your domain-reproducer.sql return NULL.
Should we preserve unconstrained-domain inlining, with cached plans
rebuilt if constraints are added to the domain or its parents?
There is a precedent for that: eval_const_expressions() replaces a
CoerceToDomain with a RelabelType when the domain has no constraints,
and calls record_plan_type_dependency() so the plan is rebuilt if
constraints are added later. But contain_nonstrict_functions() has no
PlannerInfo to record such a dependency, so we'd need to change its
signature or add a variant, and pass the root through its callers.
That doesn't seem appropriate for a fix that is going to be
back-patched to v17.
Rather than adding a domain-specific condition on top of v2, v3
simplifies the rule. With a NULL context item or path specification,
a non-NULL result can only come from two places: the jsonpath
evaluation, which still runs if there are PASSING arguments, and the ON
EMPTY / ON ERROR behaviors. So v3 treats a JsonExpr as strict only if
it has no PASSING arguments and both behaviors are NULL or ERROR, and
otherwise as non-strict. It no longer checks whether the behavior
expression is a Const, or whether the RETURNING type is a domain. When
the rule passes, the walker just recurses into the inputs as usual,
since there are no PASSING arguments left to be mistaken for strict
inputs.
With ERROR ON ERROR a domain violation is raised as an error, and with
NULL ON ERROR the result is either NULL or the "could not coerce ON
ERROR expression" error. Neither is a wrong non-NULL result, and
raising an error for a NULL input is what we already accept for
CoerceToDomain, which is treated as strict.
The common case you measured, a STRICT SQL wrapper around JSON_VALUE()
or JSON_QUERY() with the default behaviors, is still inlined, including
when RETURNING a domain. What we give up compared to v2 are wrappers
with an explicit non-NULL behavior (DEFAULT <const>, EMPTY ARRAY, etc.),
and JSON_EXISTS() wrappers, since JSON_EXISTS defaults to FALSE ON
ERROR. Those are no longer inlined, unless they say ERROR ON ERROR. I
think that's an acceptable price for a rule that is easy to verify and
back-patch. If it matters, the more precise check with a plan
dependency could be done separately on master.
Could you check which ON ERROR behavior your 35 ms -> 196 ms benchmark
used? If it used the default, v3 should keep it inlined.
Greg, since v3 changes the rule again, another look would be welcome if
you have time.
Regards,
Zhenglong Li
Hi Zhenglong,
Could you check which ON ERROR behavior your 35 ms -> 196 ms benchmark
used? If it used the default, v3 should keep it inlined.
Yes, the benchmark used default NULL ON EMPTY/ON ERROR.
On my rerun, v3 now keeps it inlined without any performance issues.
So v3 treats a JsonExpr as strict only if
it has no PASSING arguments and both behaviors are NULL or ERROR
Would UNKNOWN ON ERROR be worth including, since it is another NULL
fallback? Constant NULL defaults also seem to fit the NULL-or-error rule,
but these could be left for a follow-up.
I think that's an acceptable price for a rule that is easy to verify and
back-patch. If it matters, the more precise check with a plan dependency
could be done separately on master.
I agree, keeping the back-patch straightforward and leaving other optimization rules
for a separate follow-up change seems like the best option here.
Thanks,
Ethan
Hi Ethan,
Yes, the benchmark used default NULL ON EMPTY/ON ERROR.
On my rerun, v3 now keeps it inlined without any performance issues.
Thanks for rerunning it.
Would UNKNOWN ON ERROR be worth including, since it is another NULL
fallback?
Yes, it's the same as NULL ON ERROR, and including it is a one-line
change, so v4 does that. It only applies to JSON_EXISTS, which has no
RETURNING clause, so the domain case doesn't come up. v4 adds a test
that a STRICT SQL function using JSON_EXISTS(... UNKNOWN ON ERROR) is
inlined.
Constant NULL defaults also seem to fit the NULL-or-error rule,
but these could be left for a follow-up.
Agreed, I'd leave those for a follow-up. DEFAULT NULL does the same
as NULL, so there's little reason to write it, and I'd rather keep the
back-patched rule to checking the behavior types only.
v4 is also rebased over 627c4df3b73 ("SQL/JSON: Fix NULL result leakage
in JSON_VALUE() RETURNING json/jsonb"), which added regression tests at
the same spot in sqljson_queryfuncs.sql; cfbot was reporting that v3 no
longer applied.
Regards,
Zhenglong Li
Hi,
Thanks for the fixes. I've accidentally tripped over this issue while
working on a nearby fix [1]/messages/by-id/b368a3ca-b196-4c28-8050-50320e9c955d@vondra.me, dealing with pushdown to FULL JOIN, so
kinda in the opposite direction.
I've posted a more complex fix in that thread, which also inspects the
expressions in the PASSING/ON ERROR and ON/EMPTY clauses. Which does
allow treating the JsonExpr as strict in more cases (well, at least I
believe so). But when I tried to use your fix, it only really changed a
single query in the (fairly extensive) regression tests I added:
# Values Scan on "*VALUES*"
# - Output: JSON_QUERY("*VALUES*".column1, "*VALUES*".column2 PASSING
0 AS p RETURNING jsonb WITH UNCONDITIONAL WRAPPER KEEP QUOTES EMPTY
ARRAY ON EMPTY EMPTY OBJECT ON ERROR), JSON_EXISTS("*VALUES*".column1,
"*VALUES*".column2 PASSING 0 AS p TRUE ON ERROR)
# + Output: sqljson_query_strict("*VALUES*".column1,
"*VALUES*".column2), sqljson_exists_strict("*VALUES*".column1,
"*VALUES*".column2)
# (2 rows)
#
There are two such failures, but both are about blocking inlining of a
SQL function with JSON_QUERY/JSON_VALUE.
The one thing this v4 fix does not handle is RETURNING with
null-rejecting domains, which is violating strictness (as it turns NULL
into an ERROR). But once I added this:
if (jsexpr->use_json_coercion &&
get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN)
return true;
it disappeared. The CoerceToDomain is an independent issue, but it does
overlap with the JsonExpr in this detail. So maybe this fix should do
this bit too.
There's probably ways to refine these checks. I guess the simpler the
check the better for backpatching. I wonder if this might cause trouble
for users by disabling optimization in cases when it'd be OK. But then
again, better slower than incorrect. I don't have a very good intuition
how common these optimizations are, though.
regards
[1]: /messages/by-id/b368a3ca-b196-4c28-8050-50320e9c955d@vondra.me
/messages/by-id/b368a3ca-b196-4c28-8050-50320e9c955d@vondra.me
--
Tomas Vondra
On 11/17/26 18:10, Tomas Vondra wrote:
Hi,
Thanks for the fixes. I've accidentally tripped over this issue while
working on a nearby fix [1], dealing with pushdown to FULL JOIN, so
kinda in the opposite direction.I've posted a more complex fix in that thread, which also inspects the
expressions in the PASSING/ON ERROR and ON/EMPTY clauses. Which does
allow treating the JsonExpr as strict in more cases (well, at least I
believe so). But when I tried to use your fix, it only really changed a
single query in the (fairly extensive) regression tests I added:# Values Scan on "*VALUES*"
# - Output: JSON_QUERY("*VALUES*".column1, "*VALUES*".column2 PASSING
0 AS p RETURNING jsonb WITH UNCONDITIONAL WRAPPER KEEP QUOTES EMPTY
ARRAY ON EMPTY EMPTY OBJECT ON ERROR), JSON_EXISTS("*VALUES*".column1,
"*VALUES*".column2 PASSING 0 AS p TRUE ON ERROR)
# + Output: sqljson_query_strict("*VALUES*".column1,
"*VALUES*".column2), sqljson_exists_strict("*VALUES*".column1,
"*VALUES*".column2)
# (2 rows)
#There are two such failures, but both are about blocking inlining of a
SQL function with JSON_QUERY/JSON_VALUE.
To clarify, that does not mean the cases treated as non-strict by v4 is
very narrow. Expressions that stop being inlined in a STRICT SQL
function are for example:
- plain JSON_EXISTS(js, '$.a'), because of its implicit FALSE ON ERROR
- TRUE ON ERROR
- a constant PASSING 1 AS v , in JSON_EXISTS (with ERROR ON ERROR) and
in JSON_VALUE
- DEFAULT 0 ON EMPTY, DEFAULT -1 ON ERROR, and even DEFAULT NULL
- EMPTY ARRAY ON EMPTY and EMPTY OBJECT ON ERROR
AFAIK all of those are treated as strict with my fix. This can trigger
annoying plan changes:
- An expression index on the inlined expression is no longer used: Index
Scan becomes Seq Scan.
- A subquery pulled up from the nullable side of an outer join now gets
PlaceHolderVars.
...
But I'm still not convinced that makes it a serious issue, because how
many people are actually creating SQL functions wrapping JSON queries?
regards
--
Tomas Vondra
Hi Tomas,
Thanks for looking at this, and for cross-checking v4 against your
test suite.
The one thing this v4 fix does not handle is RETURNING with
null-rejecting domains, which is violating strictness (as it turns
NULL into an ERROR).
You're right, and it's worse than a missed case: v4 even had a test
asserting that this gets inlined. My reasoning was that with NULL /
UNKNOWN / ERROR behaviors the result is "either NULL or an error", but
of course an error is not NULL -- the STRICT function returns NULL
without evaluating its body, while the inlined expression raises. With
a NOT NULL domain, both the default NULL ON ERROR ("could not coerce ON
ERROR expression (NULL) to the RETURNING type") and ERROR ON ERROR fail
for a NULL context item, for both the inlining and the outer-join
pull-up paths.
Attached v5 adopts your check as-is:
if (jsexpr->use_json_coercion &&
get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN)
return true;
so the rule is now: a JsonExpr is non-strict if it coerces its result
to a domain via json_populate_type(), if it has PASSING arguments, or
if either ON EMPTY / ON ERROR is anything other than NULL, UNKNOWN or
ERROR; otherwise its inputs are checked as usual. Like yours, it
doesn't look at DomainHasConstraints(), which also keeps it independent
of constraints added after planning.
Expressions that stop being inlined in a STRICT SQL function are for
example: [...]
Agreed, the list is longer than "narrow". For a back-patched fix I'd
still prefer the simple rule: it only looks at node fields, and it
errs on the side of not inlining, which seems the right trade-off
given that (as you say) STRICT SQL wrappers around JSON queries are
probably not that common.
Regards,
Zhenglong
Attachments:
t253525_12v5-0001-Fix-planner-s-strictness-check-for-JsonExpr.patchapplication/octet-stream; name=v5-0001-Fix-planner-s-strictness-check-for-JsonExpr.patchDownload+266-1
Hi,
Tomas (added him back) asked me to take a look at the SQL/JSON side of
the issues he's tackling at:
/messages/by-id/b368a3ca-b196-4c28-8050-50320e9c955d@vondra.me
His description there of how a NULL context item, path, and PASSING
value are handled matches what ExecEvalJsonExprPath() does. I
understand that for the JsonExpr part we're preferring the patch in
this thread.
Also, there's some relevance to the issues described at:
/messages/by-id/19851-f88328048ff9770a@postgresql.org
where I'm about to post a patch that removes the use_json_coercion
flag from JsonExpr. This patch relies on that flag, so the two
conflict, and I have some comments given that.
On Thu, Nov 19, 2026 at 10:37 AM Keyerror Smart <smartkeyerror@gmail.com> wrote:
Hi Tomas,
Thanks for looking at this, and for cross-checking v4 against your
test suite.The one thing this v4 fix does not handle is RETURNING with
null-rejecting domains, which is violating strictness (as it turns
NULL into an ERROR).You're right, and it's worse than a missed case: v4 even had a test
asserting that this gets inlined. My reasoning was that with NULL /
UNKNOWN / ERROR behaviors the result is "either NULL or an error", but
of course an error is not NULL -- the STRICT function returns NULL
without evaluating its body, while the inlined expression raises. With
a NOT NULL domain, both the default NULL ON ERROR ("could not coerce ON
ERROR expression (NULL) to the RETURNING type") and ERROR ON ERROR fail
for a NULL context item, for both the inlining and the outer-join
pull-up paths.Attached v5 adopts your check as-is:
if (jsexpr->use_json_coercion &&
get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN)
return true;so the rule is now: a JsonExpr is non-strict if it coerces its result
to a domain via json_populate_type(), if it has PASSING arguments, or
if either ON EMPTY / ON ERROR is anything other than NULL, UNKNOWN or
ERROR; otherwise its inputs are checked as usual. Like yours, it
doesn't look at DomainHasConstraints(), which also keeps it independent
of constraints added after planning.
Given how use_json_coercion is set in the parser in
transformJsonFuncExpr() for JSON_VALUE():
/*
* Set up to coerce the result value of JsonPathValue() to the
* RETURNING type (default or user-specified), if needed.
*/
if (jsexpr->returning->typid != TEXTOID)
{
if (get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN &&
DomainHasConstraints(jsexpr->returning->typid, NULL))
jsexpr->use_json_coercion = true;
else
jsexpr->use_io_coercion = true;
}
looking at use_json_coercion already means that you're relying on
DomainHasConstraints() to make a planning decision that would go stale
if a constraint is added or dropped. In the other thread I mentioned,
the idea is to remove that flag exactly because it can become stale if
the answer of DomainHasConstraints() changes after it is stored in the
flag, which causes an issue both for a plan that is stored and reused
and for a parse tree that gets stored in the catalog, such as for
views.
Today the executor relies on the same flag, so it can skip checking a
NULL result against a domain whose constraints were added later, which
is one of the things being fixed in the other thread. Once the
executor always performs that check, a JsonExpr returning any domain
can turn a NULL input into an error or the ON ERROR value, so it has
to be treated as non-strict whatever the flag says.
So we should remove the reference to use_json_coercion from this
patch, which I'm planning to make obsolete anyway (remove it in master
and keep it in the back-branches but not use its value), and keep only
the get_typtype() test. That adds JSON_VALUE() returning a domain
without constraints to the cases that lose inlining. v5 already loses
it for JSON_QUERY() returning any domain, since the flag is set there
whenever the RETURNING type isn't jsonb. I don't see a back-patchable
way to avoid either, because, as already noted upthread, that would
need a plan dependency on the domain, which
contain_nonstrict_functions() has no way to record.
Re the v5 patch:
The commit message and the test comment say that for a NULL context
item or path the ON EMPTY / ON ERROR behavior can still supply a
non-NULL value. That only happens when the RETURNING type is a domain
that rejects the NULL, which the domain check already covers;
otherwise those behaviors aren't consulted at all. The only way a
behavior can make the expression non-strict by itself is a DEFAULT
expression that references a parameter, as in your DEFAULT $1 + 0
test. So treating every behavior other than NULL, UNKNOWN or ERROR as
non-strict is broader than needed, e.g. for EMPTY ARRAY, a constant
DEFAULT, or a plain JSON_EXISTS() with its implicit FALSE ON ERROR.
Tomas's 0004 on the other thread does the precise thing: it treats the
JsonExpr as non-strict only if a Var, PlaceHolderVar or Param appears
in the PASSING values or in the ON EMPTY / ON ERROR expressions. It's
small and only looks at the node, so I don't think it's any harder to
back-patch. Attached is a variant of it that applies on top of v5,
with the domain check without the flag and the behavior part done as a
JsonBehavior case in the walker. All of v5's tests pass unchanged
with it. The only thing given up then is inlining when RETURNING a
domain, so I'd suggest going that way in the next version, with a test
showing that a wrapper RETURNING a domain without constraints is not
inlined.
--
Thanks, Amit Langote
Attachments:
t253525_13jsonexpr-strictness-precise.diffapplication/octet-stream; name=jsonexpr-strictness-precise.diffDownload+57-0
FYI I've pushed a fix for the FULL JOIN issue, without the JsonExpr and
CoerceToDomain fixes. That adds a new walker mimicking
contain_nonstrict_functions_walker-like, so once we figure out the
solution here, we should apply the fix to both places.
regards
[1]: /messages/by-id/215b59bd-a42e-4ed2-948d-8faef00aebd1@vondra.me
/messages/by-id/215b59bd-a42e-4ed2-948d-8faef00aebd1@vondra.me
--
Tomas Vondra
On 11/19/26 14:40, Amit Langote wrote:
Hi,
Tomas (added him back) asked me to take a look at the SQL/JSON side of
the issues he's tackling at:
/messages/by-id/b368a3ca-b196-4c28-8050-50320e9c955d@vondra.meHis description there of how a NULL context item, path, and PASSING
value are handled matches what ExecEvalJsonExprPath() does. I
understand that for the JsonExpr part we're preferring the patch in
this thread.
Right. I think I prefer the simpler patch for backpatching. We may
choose to do the "better" version as an improvement on master, if
someone presents a good case for it (shows a *plausible* query that is
affected by lack of the optimization).
Also, there's some relevance to the issues described at:
/messages/by-id/19851-f88328048ff9770a@postgresql.orgwhere I'm about to post a patch that removes the use_json_coercion
flag from JsonExpr. This patch relies on that flag, so the two
conflict, and I have some comments given that.
OK
On Thu, Nov 19, 2026 at 10:37 AM Keyerror Smart <smartkeyerror@gmail.com> wrote:
Hi Tomas,
Thanks for looking at this, and for cross-checking v4 against your
test suite.The one thing this v4 fix does not handle is RETURNING with
null-rejecting domains, which is violating strictness (as it turns
NULL into an ERROR).You're right, and it's worse than a missed case: v4 even had a test
asserting that this gets inlined. My reasoning was that with NULL /
UNKNOWN / ERROR behaviors the result is "either NULL or an error", but
of course an error is not NULL -- the STRICT function returns NULL
without evaluating its body, while the inlined expression raises. With
a NOT NULL domain, both the default NULL ON ERROR ("could not coerce ON
ERROR expression (NULL) to the RETURNING type") and ERROR ON ERROR fail
for a NULL context item, for both the inlining and the outer-join
pull-up paths.Attached v5 adopts your check as-is:
if (jsexpr->use_json_coercion &&
get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN)
return true;so the rule is now: a JsonExpr is non-strict if it coerces its result
to a domain via json_populate_type(), if it has PASSING arguments, or
if either ON EMPTY / ON ERROR is anything other than NULL, UNKNOWN or
ERROR; otherwise its inputs are checked as usual. Like yours, it
doesn't look at DomainHasConstraints(), which also keeps it independent
of constraints added after planning.Given how use_json_coercion is set in the parser in
transformJsonFuncExpr() for JSON_VALUE():/*
* Set up to coerce the result value of JsonPathValue() to the
* RETURNING type (default or user-specified), if needed.
*/
if (jsexpr->returning->typid != TEXTOID)
{
if (get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN &&
DomainHasConstraints(jsexpr->returning->typid, NULL))
jsexpr->use_json_coercion = true;
else
jsexpr->use_io_coercion = true;
}looking at use_json_coercion already means that you're relying on
DomainHasConstraints() to make a planning decision that would go stale
if a constraint is added or dropped. In the other thread I mentioned,
the idea is to remove that flag exactly because it can become stale if
the answer of DomainHasConstraints() changes after it is stored in the
flag, which causes an issue both for a plan that is stored and reused
and for a parse tree that gets stored in the catalog, such as for
views.
Does it mean we'll remove the risk of it being stale entirely? So the
executor will keep using the "old" value used when planning?
Today the executor relies on the same flag, so it can skip checking a
NULL result against a domain whose constraints were added later, which
is one of the things being fixed in the other thread. Once the
executor always performs that check, a JsonExpr returning any domain
can turn a NULL input into an error or the ON ERROR value, so it has
to be treated as non-strict whatever the flag says.So we should remove the reference to use_json_coercion from this
patch, which I'm planning to make obsolete anyway (remove it in master
and keep it in the back-branches but not use its value), and keep only
the get_typtype() test. That adds JSON_VALUE() returning a domain
without constraints to the cases that lose inlining. v5 already loses
it for JSON_QUERY() returning any domain, since the flag is set there
whenever the RETURNING type isn't jsonb. I don't see a back-patchable
way to avoid either, because, as already noted upthread, that would
need a plan dependency on the domain, which
contain_nonstrict_functions() has no way to record.
How will that affect backbranches / backpatch version of the fix? I
suppose that'll still need to check the use_json_coercion flag?
Re the v5 patch:
The commit message and the test comment say that for a NULL context
item or path the ON EMPTY / ON ERROR behavior can still supply a
non-NULL value. That only happens when the RETURNING type is a domain
that rejects the NULL, which the domain check already covers;
otherwise those behaviors aren't consulted at all. The only way a
behavior can make the expression non-strict by itself is a DEFAULT
expression that references a parameter, as in your DEFAULT $1 + 0
test. So treating every behavior other than NULL, UNKNOWN or ERROR as
non-strict is broader than needed, e.g. for EMPTY ARRAY, a constant
DEFAULT, or a plain JSON_EXISTS() with its implicit FALSE ON ERROR.Tomas's 0004 on the other thread does the precise thing: it treats the
JsonExpr as non-strict only if a Var, PlaceHolderVar or Param appears
in the PASSING values or in the ON EMPTY / ON ERROR expressions. It's
small and only looks at the node, so I don't think it's any harder to
back-patch. Attached is a variant of it that applies on top of v5,
with the domain check without the flag and the behavior part done as a
JsonBehavior case in the walker. All of v5's tests pass unchanged
with it. The only thing given up then is inlining when RETURNING a
domain, so I'd suggest going that way in the next version, with a test
showing that a wrapper RETURNING a domain without constraints is not
inlined.
WFM. I'd be fine with the simpler approach, mostly because I'm new to
the SQL/JSON thing and I wasn't sure it's 100% correct. So I was opting
for the more conservative fix. But if you agree it's correct, we can
backpatch, that's fine.
regards
--
Tomas Vondra
Hi Amit, Tomas,
Thanks, Amit, for the review and the patch.
On Thu, Nov 19, 2026 at 13:40, Amit Langote wrote:
So we should remove the reference to use_json_coercion from this
patch, [...] and keep only the get_typtype() test.
Agreed. I hadn't considered that use_json_coercion for JSON_VALUE()
already depends on DomainHasConstraints() at parse time, so the v5
check could go stale in the same way the executor does. Checking only
the type is simpler and doesn't interact with your patch removing the
flag, in master or in the back branches.
Tomas's 0004 on the other thread does the precise thing [...]
Attached is a variant of it that applies on top of v5
Attached v6 takes your variant as-is: a JsonExpr is non-strict if it
returns any domain, or if a Var, PlaceHolderVar or Param appears in
its PASSING values or ON EMPTY / ON ERROR expressions (the latter as a
JsonBehavior case in the walker). Changes relative to your diff are
limited to comments, which I've tightened a bit, and the commit
message, which now describes the precise rule.
Tests:
- all of v5's tests are kept unchanged and pass;
- as you suggested, a wrapper RETURNING a domain without constraints
is shown not to be inlined;
- new EXPLAIN tests show that wrappers with a constant PASSING value
and constant DEFAULT ON EMPTY / ON ERROR, with EMPTY ARRAY ON EMPTY,
and a plain JSON_EXISTS() with its implicit FALSE ON ERROR are
inlined again (v5 didn't inline these), together with a check that
they still return NULL for a NULL argument.
The patch is rebased over 72b480e55d7 and applies to REL_18_STABLE and
REL_17_STABLE as well. I've added Amit as a co-author.
On Thu, Nov 19, 2026 at 14:56, Tomas Vondra wrote:
But if you agree it's correct, we can backpatch, that's fine.
Since the precise check only inspects the node itself, I think it's as
back-patchable as the v5 rule, and it avoids the loss of inlining you
listed earlier, so I went that way.
On Thu, Nov 19, 2026 at 14:46, Tomas Vondra wrote:
That adds a new walker mimicking
contain_nonstrict_functions_walker-like, so once we figure out the
solution here, we should apply the fix to both places.
Right. Once we agree on v6, I can add the same JsonExpr / JsonBehavior
handling to full_join_expr_nonstrict_walker(), with a FULL JOIN test,
either as a 0002 here or folded into 0001 -- whichever you prefer.
Regards,
Zhenglong
Attachments:
t253525_16v6-0001-Fix-planner-s-strictness-check-for-JsonExpr.patchapplication/octet-stream; name=v6-0001-Fix-planner-s-strictness-check-for-JsonExpr.patchDownload+385-1
Hi Tomas,
On Thu, Nov 19, 2026 at 11:56 PM Tomas Vondra <tomas@vondra.me> wrote:
On 11/19/26 14:40, Amit Langote wrote:
Hi,
Tomas (added him back) asked me to take a look at the SQL/JSON side of
the issues he's tackling at:
/messages/by-id/b368a3ca-b196-4c28-8050-50320e9c955d@vondra.meHis description there of how a NULL context item, path, and PASSING
value are handled matches what ExecEvalJsonExprPath() does. I
understand that for the JsonExpr part we're preferring the patch in
this thread.Right. I think I prefer the simpler patch for backpatching. We may
choose to do the "better" version as an improvement on master, if
someone presents a good case for it (shows a *plausible* query that is
affected by lack of the optimization).
It felt overly conservative to me even for the back branches; more on
that below, where I think we are in agreement.
Also, there's some relevance to the issues described at:
/messages/by-id/19851-f88328048ff9770a@postgresql.orgwhere I'm about to post a patch that removes the use_json_coercion
flag from JsonExpr. This patch relies on that flag, so the two
conflict, and I have some comments given that.OK
On Thu, Nov 19, 2026 at 10:37 AM Keyerror Smart <smartkeyerror@gmail.com> wrote:
Hi Tomas,
Thanks for looking at this, and for cross-checking v4 against your
test suite.The one thing this v4 fix does not handle is RETURNING with
null-rejecting domains, which is violating strictness (as it turns
NULL into an ERROR).You're right, and it's worse than a missed case: v4 even had a test
asserting that this gets inlined. My reasoning was that with NULL /
UNKNOWN / ERROR behaviors the result is "either NULL or an error", but
of course an error is not NULL -- the STRICT function returns NULL
without evaluating its body, while the inlined expression raises. With
a NOT NULL domain, both the default NULL ON ERROR ("could not coerce ON
ERROR expression (NULL) to the RETURNING type") and ERROR ON ERROR fail
for a NULL context item, for both the inlining and the outer-join
pull-up paths.Attached v5 adopts your check as-is:
if (jsexpr->use_json_coercion &&
get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN)
return true;so the rule is now: a JsonExpr is non-strict if it coerces its result
to a domain via json_populate_type(), if it has PASSING arguments, or
if either ON EMPTY / ON ERROR is anything other than NULL, UNKNOWN or
ERROR; otherwise its inputs are checked as usual. Like yours, it
doesn't look at DomainHasConstraints(), which also keeps it independent
of constraints added after planning.Given how use_json_coercion is set in the parser in
transformJsonFuncExpr() for JSON_VALUE():/*
* Set up to coerce the result value of JsonPathValue() to the
* RETURNING type (default or user-specified), if needed.
*/
if (jsexpr->returning->typid != TEXTOID)
{
if (get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN &&
DomainHasConstraints(jsexpr->returning->typid, NULL))
jsexpr->use_json_coercion = true;
else
jsexpr->use_io_coercion = true;
}looking at use_json_coercion already means that you're relying on
DomainHasConstraints() to make a planning decision that would go stale
if a constraint is added or dropped. In the other thread I mentioned,
the idea is to remove that flag exactly because it can become stale if
the answer of DomainHasConstraints() changes after it is stored in the
flag, which causes an issue both for a plan that is stored and reused
and for a parse tree that gets stored in the catalog, such as for
views.Does it mean we'll remove the risk of it being stale entirely? So the
executor will keep using the "old" value used when planning?
Yes to the first, no to the second. The executor will decide which
coercion to use afresh during initialization in ExecInitJsonExpr(),
without looking at DomainHasConstraints(), so the choice is the same
whether or not the domain has constraints at any given time. None of
that matters for this patch though, since it will consider a JsonExpr
non-strict whenever its RETURNING type is a domain, that is, once it
no longer checks JsonExpr.use_json_coercion.
Today the executor relies on the same flag, so it can skip checking a
NULL result against a domain whose constraints were added later, which
is one of the things being fixed in the other thread. Once the
executor always performs that check, a JsonExpr returning any domain
can turn a NULL input into an error or the ON ERROR value, so it has
to be treated as non-strict whatever the flag says.So we should remove the reference to use_json_coercion from this
patch, which I'm planning to make obsolete anyway (remove it in master
and keep it in the back-branches but not use its value), and keep only
the get_typtype() test. That adds JSON_VALUE() returning a domain
without constraints to the cases that lose inlining. v5 already loses
it for JSON_QUERY() returning any domain, since the flag is set there
whenever the RETURNING type isn't jsonb. I don't see a back-patchable
way to avoid either, because, as already noted upthread, that would
need a plan dependency on the domain, which
contain_nonstrict_functions() has no way to record.How will that affect backbranches / backpatch version of the fix? I
suppose that'll still need to check the use_json_coercion flag?
No. In the back branches my other patch will leave the field in
place, and the parser code that sets it, but nothing downstream will
look at it. The executor will compute the equivalent afresh during
initialization. So this patch shouldn't refer to it in any branch.
Nor does this patch depend on the other one, given that:
* Until that patch goes in, treating every domain as non-strict is a
bit more conservative than what the executor does for JSON_VALUE()
today, which is harmless. For example, when RETURNING a domain that
had no constraints at parse time, the expression is in fact strict,
because a NULL result isn't checked against the domain.
* Once that patch is in, a NULL will always be checked against the
domain's current constraints, so what this patch adds is needed for
any domain that rejects NULLs, including one whose constraint was
added after the query was parsed.
So it would in fact be better for this patch to go in first, or at
least in the same release.
Re the v5 patch:
The commit message and the test comment say that for a NULL context
item or path the ON EMPTY / ON ERROR behavior can still supply a
non-NULL value. That only happens when the RETURNING type is a domain
that rejects the NULL, which the domain check already covers;
otherwise those behaviors aren't consulted at all. The only way a
behavior can make the expression non-strict by itself is a DEFAULT
expression that references a parameter, as in your DEFAULT $1 + 0
test. So treating every behavior other than NULL, UNKNOWN or ERROR as
non-strict is broader than needed, e.g. for EMPTY ARRAY, a constant
DEFAULT, or a plain JSON_EXISTS() with its implicit FALSE ON ERROR.Tomas's 0004 on the other thread does the precise thing: it treats the
JsonExpr as non-strict only if a Var, PlaceHolderVar or Param appears
in the PASSING values or in the ON EMPTY / ON ERROR expressions. It's
small and only looks at the node, so I don't think it's any harder to
back-patch. Attached is a variant of it that applies on top of v5,
with the domain check without the flag and the behavior part done as a
JsonBehavior case in the walker. All of v5's tests pass unchanged
with it. The only thing given up then is inlining when RETURNING a
domain, so I'd suggest going that way in the next version, with a test
showing that a wrapper RETURNING a domain without constraints is not
inlined.WFM. I'd be fine with the simpler approach, mostly because I'm new to
the SQL/JSON thing and I wasn't sure it's 100% correct. So I was opting
for the more conservative fix. But if you agree it's correct, we can
backpatch, that's fine.
Being cautious about that makes sense, but yes, I do think it's correct.
A JsonExpr is strict in its context item and path, because a NULL in
either causes it to return NULL without consulting the ON EMPTY / ON
ERROR behaviors. It is not strict in a variable used in a PASSING
argument, where a NULL merely becomes a JSON null, nor in one used in
an ON EMPTY / ON ERROR expression, which is evaluated only if that
behavior applies. The one exception to the first point is a RETURNING
domain, which can reject the NULL and so turn it into an error or the
ON ERROR value. Those three are what the check looks for. Anything
else non-strict inside the sub-expressions is left to the walker's
usual rules.
That said, I have a correction to the diff I attached yesterday. Its
JsonBehavior case returned without letting the walker look at the
behavior's expression any further, which misses a DEFAULT expression
containing a subquery that refers to a nullable column, e.g. DEFAULT
num_nulls((SELECT t.y)) ON EMPTY. Master and your 0004 get that
right. The attached version falls through to the normal checks in
that case, so it is now equivalent to 0004 for the PASSING and
behavior parts.
It'd be better to keep cross-branch differences to a minimum, so I'd
rather use the same check in all branches.
Note: I haven't looked at the just posted v6 yet.
--
Thanks, Amit Langote