BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c
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>
Дата:
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 ` 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.
Re: BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
PG Bug reporting form 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
Re: BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c
От:
Amit Langote <amitlangote09@gmail.com>
Дата:
On Fri, Nov 13, 2026 at 6:32 AM Tom Lane wrote: > PG Bug reporting form 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
Re: BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c
От:
Amit Langote <amitlangote09@gmail.com>
Дата:
On Fri, Nov 13, 2026 at 10:28 PM Amit Langote wrote: > On Fri, Nov 13, 2026 at 6:32 AM Tom Lane wrote: > > PG Bug reporting form 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