Re: [PATCH]Fix planner's strictness check for JsonExpr

From: Amit Langote <amitlangote09(at)gmail(dot)com>
To: Tomas Vondra <tomas(at)vondra(dot)me>
Cc: Keyerror Smart <smartkeyerror(at)gmail(dot)com>, ethantran(at)quwin(dot)dev, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: [PATCH]Fix planner's strictness check for JsonExpr
Date: 2026-11-20 12:00:37
Message-ID: CA+HiwqG7m4rMeJ8rK4Us2oTrwG_vrbhM0q2tR6LmbKAn3bkvZg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Tomas,

On Thu, Nov 19, 2026 at 11:56 PM Tomas Vondra <tomas(at)vondra(dot)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:
> > https://www.postgresql.org/message-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.
> >
>
> 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:
> > https://www.postgresql.org/message-id/19851-f88328048ff9770a%40postgresql.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.
>
> OK
>
> > On Thu, Nov 19, 2026 at 10:37 AM Keyerror Smart <smartkeyerror(at)gmail(dot)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

Attachment Content-Type Size
jsonexpr-strictness-on-top-of-v5-v2.diff application/octet-stream 4.0 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2026-11-20 12:05:39 Re: [BUG] Take a long time to reach consistent after pg_rewind
Previous Message Xuneng Zhou 2026-11-20 11:56:57 Re: [PATCH v1 0/2] Preserve input sign across numeric parameter parsing