Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xNpuh-00000002lw7-256T for pgsql-bugs@arkaria.postgresql.org; Mon, 02 Nov 2026 11:09:20 +0000 Received: from localhost ([127.0.0.1]:55870 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xNpug-00000003HjH-49Yv for pgsql-bugs@arkaria.postgresql.org; Mon, 02 Nov 2026 11:09:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:37848) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xNQbn-0000000GbKZ-3ctD for pgsql-bugs@lists.postgresql.org; Sun, 01 Nov 2026 08:08:07 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xNQbm-00000001yYa-08Bf for pgsql-bugs@lists.postgresql.org; Sun, 01 Nov 2026 08:08:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=C18nkoJY37RNbYkRvmi08a2ormLa5ezMANbBG1O2RRI=; b=GFgyvhPbE0zt9+YVISXfGFbKv1 tQss42DTmtX7+pUW8PBsYg3uzdJB4juYtd0m4xMPz6bg5YXaSwH90ZIMpBSqWwArhilLwRXq4A3p0 GEqwrP+6RNOUHe/F227v99XnlYPLRlkfSkwMJPYLvv7ABm8OaTpXhvdq7h+7lqfkaAqhc6A9IZHM+ 87j7+EvGQ4PKwhQsJSzCaLC0ghtx01DQDnx0fKQDF8R9FZzVcIIZU87v4HAdo/A2wu53m2wMLT4p3 /yf/7mWQYpYEMpzsyZdvZlyZafjfjbrgHZyHN1QR29lFG1CSAmA534JskTAoTDuSgR1z9JMqz9JDQ e0IyDOeA==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xNQbk-00000005k1t-3PhM for pgsql-bugs@lists.postgresql.org; Sun, 01 Nov 2026 08:08:05 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xNQbi-00000009tML-2JW5 for pgsql-bugs@lists.postgresql.org; Sun, 01 Nov 2026 08:08:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19824: OMIT QUOTES breaks non-string results: array / composite / array-domain coercions in JSON_QUERY / JS To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 303677365@qq.com Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org Date: Sun, 01 Nov 2026 08:07:24 +0000 Message-ID: <19824-6b112e27c335304a@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 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 =3D=3D 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