pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c
Date: Sun, 08 Nov 2026 03:28:37 +0000
Message-ID: <19851-f88328048ff9770a@postgresql.org> (raw)

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.








view thread (4+ messages)  latest in thread

Message-ID: <19851-f88328048ff9770a@postgresql.org>
Permalink:  ../19851-f88328048ff9770a@postgresql.org/
Also on:    postgresql.org/message-id/19851-f88328048ff9770a@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, 303677365@qq.com
  Subject: Re: BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c
  In-Reply-To: <19851-f88328048ff9770a@postgresql.org>
  References: <19851-f88328048ff9770a@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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