BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c

Started by PG Bug reporting form15 days ago4 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

The following bug has been logged on the website:

Bug reference: 19851
Logged by: chunling qin
Email address: 303677365@qq.com
PostgreSQL version: 18.6
Operating system: Linux x86_64; platform-independent (reproduced ide
Description:

**Description:**

Two domains with the *identical value set* — one declared without
constraints, one with only a tautological `CHECK (true)` that can never
reject anything — give *different results* for the same `JSON_VALUE`
expression. `CHECK(true)` is a no-op semantically, so the two results must
agree, and both must agree with the base type.

Actual divergences (JSON boolean `true` as input, `RETURNING <type>` then
cast to text for display):

| RETURNING type | plain | domain (no constraint) | domain + CHECK(true) |
|---|---|---|---|
| text | `t` | `t` | **`true`** |
| char(3) | `t` | `t` | **(NULL)** — silent data loss |
| jsonb | `true` | **(NULL)** | `true` |

And for a string scalar with domains over jsonb: base `RETURNING jsonb`
yields `"1"` (a JSON string), while both domains yield `1` (a JSON number) —
a type change of the represented value.

The official expected baseline is recorded in the regression suite
(`sqljson_queryfuncs.out`): `JSON_VALUE(jsonb 'true', '$') = t`.

**Root cause:** for a RETURNING type that is a domain,
`DomainHasConstraints()` switches the coercion path inside
`ExecEvalJsonExprPath` (use_io_coercion vs use_json_coercion), and the two
paths represent the same scalar differently; the presence of any CHECK
constraint — even a tautology — flips which path is taken.

**Reproduction (self-contained SQL; verified identically on 18.6 and
master):**

```sql
CREATE DOMAIN d_txt_nc AS text; CREATE DOMAIN d_txt_ct AS text CHECK
(true);
CREATE DOMAIN d_ch_nc AS char(3); CREATE DOMAIN d_ch_ct AS char(3) CHECK
(true);
CREATE DOMAIN d_jb_nc AS jsonb; CREATE DOMAIN d_jb_ct AS jsonb CHECK
(true);

-- 0. official baseline: JSON boolean rendered to text
SELECT JSON_VALUE(jsonb 'true', '$') AS base_text; -- t

-- 1. per base type: plain / no-constraint domain / CHECK(true) domain must
agree
SELECT 'text' AS base,
JSON_VALUE(jsonb 'true', '$' RETURNING text)::text AS plain,
JSON_VALUE(jsonb 'true', '$' RETURNING d_txt_nc)::text AS
dom_no_constr,
JSON_VALUE(jsonb 'true', '$' RETURNING d_txt_ct)::text AS
dom_check_true
UNION ALL SELECT 'char(3)',
JSON_VALUE(jsonb 'true', '$' RETURNING char(3))::text,
JSON_VALUE(jsonb 'true', '$' RETURNING d_ch_nc)::text,
JSON_VALUE(jsonb 'true', '$' RETURNING d_ch_ct)::text
UNION ALL SELECT 'jsonb',
JSON_VALUE(jsonb 'true', '$' RETURNING jsonb)::text,
JSON_VALUE(jsonb 'true', '$' RETURNING d_jb_nc)::text,
JSON_VALUE(jsonb 'true', '$' RETURNING d_jb_ct)::text;
-- text: t | t | true
-- char(3): t | t | (NULL) <- silent data loss
-- jsonb: true| (NULL) | true

-- 2. string scalar: domain (over jsonb) vs base jsonb
SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING jsonb) AS base_jsonb, -- "1"
(JSON string)
JSON_VALUE(jsonb '"1"', '$' RETURNING d_jb_nc) AS dom_no_constr,-- 1
(JSON number)
JSON_VALUE(jsonb '"1"', '$' RETURNING d_jb_ct) AS dom_check_true;-- 1
```

**Expected behavior:** the presence of a constraint must not change the
coerced value: `CHECK(true)` never fails, so `d_*_nc` and `d_*_ct` have
identical value sets and must return identical results; and a domain without
constraints should behave like its base type.

**Impact:** silent NULL (data loss) and value/type changes for
`JSON_VALUE`/`JSON_QUERY` with domain RETURNING types; results become
dependent on irrelevant metadata (whether the domain happens to carry any
CHECK constraint), which also breaks views/CHECK constraints defined against
such expressions after a domain is altered.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: PG Bug reporting form (#1)
Re: BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c

PG Bug reporting form <noreply@postgresql.org> writes:

Two domains with the *identical value set* — one declared without
constraints, one with only a tautological `CHECK (true)` that can never
reject anything — give *different results* for the same `JSON_VALUE`
expression. `CHECK(true)` is a no-op semantically, so the two results must
agree, and both must agree with the base type.

Yeah, this is a mess. The entire design of JsonExpr in this area
is a disaster, starting here:

/*
* Information about converting the result of jsonpath functions
* JsonPathQuery() and JsonPathValue() to the RETURNING type.
*/
bool use_io_coercion;
bool use_json_coercion;

Possibly, if these flags hadn't been so utterly undocumented,
transformJsonFuncExpr wouldn't be so confused about what it needs
to do with domains. But there are worse problems. The broken
logic is in parse_expr.c:

if (get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN &&
DomainHasConstraints(jsexpr->returning->typid, NULL))
jsexpr->use_json_coercion = true;
else
jsexpr->use_io_coercion = true;

Quite aside from whether this is the right thing, testing
DomainHasConstraints *at parse time* is insane. The situation
could be completely different by the time we execute the expression.
At the earliest this decision should be made in the planner; but
probably doing it at executor startup would be better.

Another reason for doing it later is that we cannot fix the behavior
in released versions given this structure: even if we end up still
using exactly two booleans, any stored JsonExpr will already have
locked down the values of these flags. What I'm contemplating as a
potential fix design is to remove these flags in master, leave them
present-but-unused in back branches, and adjust the runtime
expression representation instead.

I'm afraid now to look through the rest of the SQL/JSON logic for
similar errors.

regards, tom lane

#3Amit Langote
amitlangote09@gmail.com
In reply to: Tom Lane (#2)
Re: BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c

On Fri, Nov 13, 2026 at 6:32 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

PG Bug reporting form <noreply@postgresql.org> writes:

Two domains with the *identical value set* — one declared without
constraints, one with only a tautological `CHECK (true)` that can never
reject anything — give *different results* for the same `JSON_VALUE`
expression. `CHECK(true)` is a no-op semantically, so the two results must
agree, and both must agree with the base type.

Yeah, this is a mess. The entire design of JsonExpr in this area
is a disaster, starting here:

/*
* Information about converting the result of jsonpath functions
* JsonPathQuery() and JsonPathValue() to the RETURNING type.
*/
bool use_io_coercion;
bool use_json_coercion;

Possibly, if these flags hadn't been so utterly undocumented,
transformJsonFuncExpr wouldn't be so confused about what it needs
to do with domains. But there are worse problems. The broken
logic is in parse_expr.c:

if (get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN &&
DomainHasConstraints(jsexpr->returning->typid, NULL))
jsexpr->use_json_coercion = true;
else
jsexpr->use_io_coercion = true;

Quite aside from whether this is the right thing, testing
DomainHasConstraints *at parse time* is insane. The situation
could be completely different by the time we execute the expression.
At the earliest this decision should be made in the planner; but
probably doing it at executor startup would be better.

Yeah, I should have noticed that the result of a parse-time
DomainHasConstraints() test gets frozen into the stored JsonExpr,
while the domain's constraints can change at any time after that.
Those flags are derivable from the rest of the node and shouldn't have
been stored in it to begin with.

Another reason for doing it later is that we cannot fix the behavior
in released versions given this structure: even if we end up still
using exactly two booleans, any stored JsonExpr will already have
locked down the values of these flags. What I'm contemplating as a
potential fix design is to remove these flags in master, leave them
present-but-unused in back branches, and adjust the runtime
expression representation instead.

I've started working on a patch, which I'll post in a couple of days.
The main change is that the choice of coercion method will depend only
on which function it is (JSON_VALUE, JSON_QUERY or JSON_EXISTS) and
the RETURNING type, not on whether the domain has constraints, so a
domain gets the same result as its base type. The constraint test was
there so that a NULL result would still get the domain's constraints
checked; the patch will pass the NULL to domain_in() instead.

With that, nothing in the decision can change after parsing, but I'd
still make it at executor startup, because the flags already stored in
back-branch JsonExprs were computed by the old rule and have to be
ignored.

In master, as you say, the patch will remove the two flags from
JsonExpr and also the parser code (in transformJsonFuncExpr()) that
sets them, while leaving all of that in place in the back branches.

I'm afraid now to look through the rest of the SQL/JSON logic for
similar errors.

I'll scan the code to look for more. From a quick look, I don't see
another place where something that can change later is tested at parse
time and the result stored in the node, but there are a few places in
SQL/JSON code in parse_expr.c where the type is compared against
json/jsonb/bytea without looking through domains, so a domain over one
of those behaves differently from the base type. I'll go through
those more carefully. I'm also looking at some other reported
SQL/JSON bugs in the meantime.

--
Thanks, Amit Langote

#4Amit Langote
amitlangote09@gmail.com
In reply to: Amit Langote (#3)
Re: BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c

On Fri, Nov 13, 2026 at 10:28 PM Amit Langote <amitlangote09@gmail.com> wrote:

On Fri, Nov 13, 2026 at 6:32 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

PG Bug reporting form <noreply@postgresql.org> writes:

Two domains with the *identical value set* — one declared without
constraints, one with only a tautological `CHECK (true)` that can never
reject anything — give *different results* for the same `JSON_VALUE`
expression. `CHECK(true)` is a no-op semantically, so the two results must
agree, and both must agree with the base type.

Yeah, this is a mess. The entire design of JsonExpr in this area
is a disaster, starting here:

/*
* Information about converting the result of jsonpath functions
* JsonPathQuery() and JsonPathValue() to the RETURNING type.
*/
bool use_io_coercion;
bool use_json_coercion;

Possibly, if these flags hadn't been so utterly undocumented,
transformJsonFuncExpr wouldn't be so confused about what it needs
to do with domains. But there are worse problems. The broken
logic is in parse_expr.c:

if (get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN &&
DomainHasConstraints(jsexpr->returning->typid, NULL))
jsexpr->use_json_coercion = true;
else
jsexpr->use_io_coercion = true;

Quite aside from whether this is the right thing, testing
DomainHasConstraints *at parse time* is insane. The situation
could be completely different by the time we execute the expression.
At the earliest this decision should be made in the planner; but
probably doing it at executor startup would be better.

Yeah, I should have noticed that the result of a parse-time
DomainHasConstraints() test gets frozen into the stored JsonExpr,
while the domain's constraints can change at any time after that.
Those flags are derivable from the rest of the node and shouldn't have
been stored in it to begin with.

Another reason for doing it later is that we cannot fix the behavior
in released versions given this structure: even if we end up still
using exactly two booleans, any stored JsonExpr will already have
locked down the values of these flags. What I'm contemplating as a
potential fix design is to remove these flags in master, leave them
present-but-unused in back branches, and adjust the runtime
expression representation instead.

I've started working on a patch, which I'll post in a couple of days.

Just a note: I got busy with a family matter in the last few days but
I'm working on the patch and will post it after working on it a bit
more.

--
Thanks, Amit Langote