contain_nonstrict_functions_walker() fails to consider JsonExpr and CoerceToDomain

From: Tomas Vondra <tomas(at)vondra(dot)me>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: contain_nonstrict_functions_walker() fails to consider JsonExpr and CoerceToDomain
Date: 2026-11-16 13:38:40
Message-ID: b368a3ca-b196-4c28-8050-50320e9c955d@vondra.me
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

while hacking on fixing the FULL JOIN bug in COALESCE handling [1], I
was wondering if contain_nonstrict_functions_walker() is handling all
the nodes it should be handling, and I think I found two missing pieces:
JsonExpr (representing JSON_VALUE/JSON_EXISTS/JSON_QUERY), and
CoerceToDomain. In both cases it can result in incorrect query results,
not just during FULL JOIN, but for pull-up and inlining (which are
fairly common).

I'm adding Amit on CC, as I know he was involved in the SQL/JSON and
fixing various issues since that got committed. Amit, could you please
take a look? (Or anyone else, of course.)

Consider these trivial examples (the second query is a control with
OFFSET 0 to block inlining / pull-up - that should not change results):

1) JsonExpr

The JsonExpr clauses are strict "with respect" to the context and path,
and AFAICS that's fine. But that does not extend to the other parts of
the clause, like PASSING, RETURNING and ON ERROR/EMPTY. Those can make
it non-strict. And if we pull the clause above the join, we get bogus
results for the null-extended rows.

-----
SELECT a.x, b.passing_var
FROM (VALUES (0), (1)) AS a(x)
LEFT JOIN (
SELECT v.x,
JSON_VALUE('0'::jsonb, '$' PASSING v.x AS p RETURNING int)
AS passing_var
FROM (VALUES (0, '0'::jsonb, '$'::jsonpath), (2, '2', '$'))
AS v(x, js, p)
) AS b ON a.x = b.x
ORDER BY a.x;

x | passing_var
---+-------------
0 | 0
1 | 0
(2 rows)

SELECT a.x, b.passing_var
FROM (VALUES (0), (1)) AS a(x)
LEFT JOIN (
SELECT v.x,
JSON_VALUE('0'::jsonb, '$' PASSING v.x AS p RETURNING int)
AS passing_var
FROM (VALUES (0, '0'::jsonb, '$'::jsonpath), (2, '2', '$'))
AS v(x, js, p)
OFFSET 0) AS b ON a.x = b.x
ORDER BY a.x;

x | passing_var
---+-------------
0 | 0
1 |
(2 rows)
-----

2) CoerceToDomain

A domain with a check that rejects NULLs is clearly non-strict. We fail
to notice that, and end up pulling the coercion above the join, where it
runs on null-extended rows, triggering the crash.

-----
CREATE DOMAIN nnint AS int NOT NULL;

SELECT a.x, b.d
FROM (VALUES (1), (2)) AS a(x)
LEFT JOIN (
SELECT x, x::nnint AS d
FROM (VALUES (1), (3)) AS v(x)
) AS b ON a.x = b.x;

ERROR: domain nnint does not allow null values

SELECT a.x, b.d
FROM (VALUES (1), (2)) AS a(x)
LEFT JOIN (
SELECT x, x::nnint AS d
FROM (VALUES (1), (3)) AS v(x)
OFFSET 0) AS b ON a.x = b.x;

x | d
---+---
1 | 1
2 |
(2 rows)
-----

Clearly, there are some loose ends. I'm attaching a series of patches
that tries to address this - first for JsonExpr, then CoerceToDomain.
Those cases are not entirely independent, because of the RETURNING
clause for the JsonExpr node.

The initial fixes are conservative, treating the nodes as non-strict.
That can prohibit the optimization in some legal cases, possibly in
cases that are fairly common (hard to say, but both inlining and pull-up
are widely applicable). Then the follow-up patches relax the condition,
by allowing safe cases.

Another thing is that I kept the tests in separate commits, interleaved
with fixes. A commit introduces tests (failing, at that time), and then
the next commit adjusts the code to fix those. So the "fix" commits
don't have any test changes. This makes it easier to spot the behavior
change, without having to revert parts of a commit.

I've also kept "controls" which use OFFSET 0 to block the optimization,
and produce correct results (to make it obvious the optimization returns
incorrect ones).

In the end, all of this should be squashed into fewer commits. Certainly
the tests + appropriate fix commits, perhaps even more.

Note: In fact, there's exactly one test in the core regression tests
affected by all of this (in subselect.sql). Doesn't seem like enough
coverage.

I think for the CoerceToDomain the mechanics of the issue is pretty
simple. We can't throw an error for NULLs from a strict function, that
makes it non-strict.

For the JsonExpr it's bit more complex, and I don't claim to understand
it perfectly (I don't think I've dealt with SQL/JSON before). But the
gist of the issue is that it's not "perfectly" strict because it matters
which parameter is NULL (because ExecEvalJsonExprPath handles different
cases differently). It matters which JsonExpr parameters actually depend
on the Vars (and thus may become NULL for null-extended rows).

Perhaps the simplest example is this:

SELECT JSON_VALUE(jsonb '1', '$' PASSING NULL AS v RETURNING int);

which returns "1". Another example (which somewhat ties the JsonExpr to
the CoerceToDomain case) is:

CREATE DOMAIN int_nn AS int NOT NULL;
SELECT JSON_VALUE(NULL::jsonb, '$' RETURNING int_nn
DEFAULT 0 ON ERROR);

which returns 0 for NULL input. The new regression tests demonstrate
this issue on a bunch of other examples, with the various clauses (ON
EMPTY / ON ERROR).

I know very little about the SQL/JSON stuff, but the way I understand
ExecEvalJsonExprPath, it treats a NULL in each input differently:

* context - result is NULL; ON EMPTY / ON ERROR are skipped

* path - same as the context item

* PASSING value - the path variable becomes a JSON null

* DEFAULT ... ON EMPTY / ON ERROR - evaluated only when that behavior
applies

So it's strict in its context item and path, but not in its PASSING
values or DEFAULT expressions. There may be some confusion due to NULL
vs. JSON null, perhaps?

Furthermore, there's one exception for the context item and path. With
JSON coercion (`use_json_coercion`, i.e. through json_populate_type()`),
the NULL result is still coerced to the RETURNING type, so that domain
constraints get checked. If that type is a domain rejecting NULL, the
coercion fails and ON ERROR applies. That's the relation to
CoerceToDomain I mentioned before.

The fix addresses this by jsonexpr_has_nonstrict_inputs(), which makes
JsonExpr nonstrict if either of these holds:

1. A Var, PlaceHolderVar or Param appears anywhere in the PASSING
values, the ON EMPTY behavior or the ON ERROR behavior.

2. `use_json_coercion` is set and the RETURNING type is a domain,
whatever the ON ERROR mode and whether or not the domain has constraints.

Otherwise the walker treats the JsonExpr node as strict and checks all
of its subexpressions. A nonstrict construct in the context item or path
still counts, for example `JSON_VALUE(COALESCE(c, '0'), ...)`.

For CoerceToDomain it's a bit simpler, because there are no parameters
like PASSING etc. It's fixed by only treating domains with any
constraints - per DomainHasConstraints() - as nonstrict. So if a domain
has a `NOT NULL` or `CHECK` constraint (possibly inherited), it's
nonstrict. Maybe it'd be possible to inspect the constraints and only
disable strictness if there really is NOT NULL one? But could be tricky,
not sure it's worth it. Safety first.

I'm not entirely sure this is 100% correct, because IIUC the constraints
can change after planning. But maybe it is, because the coercions deemed
strict are translated to RelabelType which ditches the constraints and
thus can't fail (or am I wrong?)

There may be a gap in handling concurrent DDL:

1. concurrent DDL during planning - The query takes no lock on the
domain. If another session's constraint becomes visible between the
strictness check and `eval_const_expressions()`, the plan keeps a
CoerceToDomain that was treated as strict.

2. constraints added to a base domain - The plan dependency is recorded
only for the domain being coerced to, so adding a constraint to its base
domain doesn't invalidate the plan. Strictness is still fine, because
the plan contains only a RelabelType. But the new constraint isn't checked.

But both of these seem mostly orthogonal to the strictness issue, and
also pre-existing.

regards

[1] https://postgr.es/m/0074fbf7-a92c-417a-937a-6b1fc27aed82@vondra.me

--
Tomas Vondra

Attachment Content-Type Size
v1-0011-Test-domain-constraint-plan-invalidation.patch text/x-patch 6.8 KB
v1-0010-Refine-strictness-checks-for-domain-coercions.patch text/x-patch 9.8 KB
v1-0009-Extend-tests-for-NULL-rejecting-domains.patch text/x-patch 18.1 KB
v1-0008-Fix-domain-coercion-strictness-check.patch text/x-patch 4.2 KB
v1-0007-Test-NULL-rejecting-domain-casts-during-pull-up-a.patch text/x-patch 5.0 KB
v1-0006-Treat-SQL-JSON-domain-error-fallbacks-as-nonstric.patch text/x-patch 3.6 KB
v1-0005-Test-SQL-JSON-domain-error-fallbacks.patch text/x-patch 6.3 KB
v1-0004-Refine-JsonExpr-strictness-checks.patch text/x-patch 3.9 KB
v1-0003-Test-JsonExpr-inlining-with-constant-side-inputs.patch text/x-patch 16.9 KB
v1-0002-Treat-JsonExpr-as-nonstrict.patch text/x-patch 1.7 KB
v1-0001-Test-SQL-JSON-strictness-during-optimization.patch text/x-patch 22.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2026-11-16 13:43:56 Re: reconsider_full_join_clause fails to consider if COALESCE() arguments are NULL-preserving
Previous Message Rajesh Kumar Thandapani 2026-11-16 13:36:18 Re: Re: Re: BUG #19776: pg_combinebackup ignores the truncation_block_length of an intermediate incremental backup