BUG #19824: OMIT QUOTES breaks non-string results: array / composite / array-domain coercions in JSON_QUERY / JS
BUG #19824: OMIT QUOTES breaks non-string results: array / composite / array-domain coercions in JSON_QUERY / JS
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website:
Bug reference: 19824
Logged by: chunling qin
Email address: 303677365@qq.com
PostgreSQL version: 18.6
Operating system: Linux (TencentOS 4.4, x86_64)
Description:
Hello,
Per the documentation, OMIT QUOTES only affects scalar *string* results.
In practice the flag is applied to non-scalar results as well, so
coercions to array / composite types break. Two shapes of damage:
with ERROR ON ERROR the statement fails with a "malformed literal"
error; with the default NULL ON ERROR the value silently becomes NULL.
Reproducer (self-contained, on 18.6 and on 20devel):
CREATE TYPE comp AS (a int, b int);
CREATE DOMAIN d_arr AS int[];
CREATE DOMAIN dcomp3 AS comp CHECK ((VALUE).a > 0);
-- the same query, three quote modes -- only OMIT QUOTES breaks:
SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING int[] KEEP QUOTES ERROR
ON ERROR); -- {1,2}
SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING int[] ERROR
ON ERROR); -- {1,2}
SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING int[] OMIT QUOTES ERROR
ON ERROR);
-- ERROR: malformed array literal: "[1, 2]"
SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING int[] OMIT QUOTES);
-- (NULL) <- silently dropped under the default NULL ON ERROR
-- domains over arrays, composites, and domain-over-composite likewise:
SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING d_arr OMIT QUOTES ERROR
ON ERROR);
-- ERROR: malformed array literal
SELECT JSON_QUERY(jsonb '{"a":1,"b":2}', '$' RETURNING comp OMIT QUOTES
ERROR ON ERROR);
-- ERROR: malformed record literal: "{"a": 1, "b": 2}"
SELECT JSON_QUERY(jsonb '{"a":1,"b":2}', '$' RETURNING dcomp3 OMIT
QUOTES ERROR ON ERROR);
-- ERROR: malformed record literal
-- JSON_TABLE columns behave identically:
SELECT * FROM JSON_TABLE(jsonb '[1,2]', '$' COLUMNS (x int[] PATH '$'));
-- {1,2}
SELECT * FROM JSON_TABLE(jsonb '[1,2]', '$' COLUMNS (x int[] PATH '$'
OMIT QUOTES)); -- NULL
Expected: OMIT QUOTES should only strip quotes from scalar strings; for
array / object results it should be a no-op, so "OMIT QUOTES" must agree
with "KEEP QUOTES" and with the default ({1,2}, (1,2), ...).
Actual: every non-string target type (int[], text[], int[][], domains
over arrays, composite types, domains over composites) either fails with
a malformed-literal error or silently loses the value. The silent-NULL
shape also corrupts filtering: "WHERE ..." then
returns the wrong row set (verified separately).
Root cause pointers: json_populate_type()
(src/backend/utils/adt/jsonfuncs.c, around lines 3386-3396) applies
JsonbUnquote() (src/backend/utils/adt/jsonb.c:2028) to non-scalars too;
transformJsonFuncExpr() (src/backend/parser/parse_expr.c:4675) sets the
flag based solely on "quotes == JS_QUOTES_OMIT".
Reproduced on:
- PostgreSQL 18.6 (REL_18_STABLE, self-built, snapshot 2026-10-26)
- PostgreSQL 20devel (snapshot of 2026-10-29)
Regards,
Chunling Qin