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 #19824: OMIT QUOTES breaks non-string results: array / composite / array-domain coercions in JSON_QUERY / JS
Date: Sun, 01 Nov 2026 08:07:24 +0000
Message-ID: <19824-6b112e27c335304a@postgresql.org> (raw)

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 <the extracted column> ..." 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








Message-ID: <19824-6b112e27c335304a@postgresql.org>
Permalink:  ../19824-6b112e27c335304a@postgresql.org/
Also on:    postgresql.org/message-id/19824-6b112e27c335304a@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 #19824: OMIT QUOTES breaks non-string results: array / composite / array-domain coercions in JSON_QUERY / JS
  In-Reply-To: <19824-6b112e27c335304a@postgresql.org>
  References: <19824-6b112e27c335304a@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