pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c
4+ messages / 3 participants
[nested] [flat]

* BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c
@ 2026-11-08 03:28 PG Bug reporting form <noreply@postgresql.org>
  2026-11-12 21:32 ` 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>
  0 siblings, 1 reply; 4+ messages in thread

From: PG Bug reporting form @ 2026-11-08 03:28 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: 303677365@qq.com

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.








^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c
  2026-11-08 03:28 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>
@ 2026-11-12 21:32 ` Tom Lane <tgl@sss.pgh.pa.us>
  2026-11-13 13:28   ` 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>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2026-11-12 21:32 UTC (permalink / raw)
  To: 303677365@qq.com; +Cc: pgsql-bugs@lists.postgresql.org, Amit Langote <amitlangote09@gmail.com>

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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c
  2026-11-08 03:28 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>
  2026-11-12 21:32 ` 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>
@ 2026-11-13 13:28   ` Amit Langote <amitlangote09@gmail.com>
  2026-11-18 06:08     ` 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>
  0 siblings, 1 reply; 4+ messages in thread

From: Amit Langote @ 2026-11-13 13:28 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 303677365@qq.com; pgsql-bugs@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c
  2026-11-08 03:28 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>
  2026-11-12 21:32 ` 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>
  2026-11-13 13:28   ` 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>
@ 2026-11-18 06:08     ` Amit Langote <amitlangote09@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Amit Langote @ 2026-11-18 06:08 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 303677365@qq.com; pgsql-bugs@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2026-11-18 06:08 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-08 03:28 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>
2026-11-12 21:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-13 13:28   ` Amit Langote <amitlangote09@gmail.com>
2026-11-18 06:08     ` Amit Langote <amitlangote09@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox