| From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Cc: | 303677365(at)qq(dot)com |
| Subject: | BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c |
| Date: | 2026-11-08 03:28:37 |
| Message-ID: | 19851-f88328048ff9770a@postgresql.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 19851
Logged by: chunling qin
Email address: 303677365(at)qq(dot)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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | PG Bug reporting form | 2026-11-08 04:31:23 | BUG #19852: NOT IN -> anti-join conversion gives wrong results when the equality operator returns NULL for non-n |
| Previous Message | PG Bug reporting form | 2026-11-08 03:25:19 | BUG #19850: SQL/JSON .datetime() output depends on DateStyle/TimeZone but is judged IMMUTABLE - expression index |