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 1xR5j2-00000000fn9-0o7D for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 10:38:44 +0000 Received: from localhost ([127.0.0.1]:44518 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xR5iz-0000000AE0t-1jsW for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 10:38:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:37968) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR49j-00000009hjy-0J9K for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 08:58:11 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR49f-00000000Qqc-238a for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 08:58:11 +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=jt/h1UbgxqLueSsOXK8JV33jdP5LxTcCcQjomnek5Xs=; b=m3hA6tJGtjdswETy1MJMCvuwEP 5QawuKzXWTJXlU5OSWBokjp7ZbWsfUCuuMVb0F7sJjATFuKg3M2ewphLHN0+57mroA0eYa3CIeynR KNnHnvzNeDj3bCSaZ/nY59lL3hS6Ehgfqvg/G6B31zIktIhozumgsNvnESTKl5CsrYR7m1TX9cjEi fjZEZng9VPnocKvE6zolXlfXo/GbnoJbrVpZHhfZ3GBAvqrIAP4FjEJNeLqE4y6y6Mvq60UT79r4w ZXqqmrvTxhw1vz2bgl2WVjUUmPilJoQwq7DeJU7L0bZgBw/X6jH4/qSaQrr0HmBNR89Yx3PLPCuNl IiNkYJGg==; 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 1xR49c-00000000x90-3DIQ for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 08:58:06 +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 1xR49b-00000002Vog-37k2 for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 08:58:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19872: JSON_TABLE: an UNUSED PASSING argument's COLLATE leaks into the output columns (wrong results, persi 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: Wed, 11 Nov 2026 08:57:56 +0000 Message-ID: <19872-fe8b70fdefd5d598@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: 19872 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: 19rc1 Operating system: Linux x86_64; platform-independent (reproduced on Description: =20 **Description:** Two defects, one root cause (`assign_collations_walker()` treats `JsonExpr.passing_values` as participating in deriving the output columns' collation): 1. **M1 =E2=80=94 wrong results (silent), persisted**: adding a PASSING arg= ument that is never referenced by any path, carrying an explicit `COLLATE`, changes the output columns' collation. Comparisons and ORDER BY on those columns now use the leaked collation, and `CREATE TABLE AS SELECT` / `CREATE VIEW` persist it (`pg_attribute.attcollation` =3D the leaked collation), so the wrong results survive. 2. **M2 =E2=80=94 spurious rejection**: two independent PASSING arguments w= ith different explicit COLLATEs are rejected with `42P21 collation mismatch`, although the two values have no comparison relationship (for a JSON_TABLE with only a FOR ORDINALITY column this query was accepted before the entry fix). **Reproduction (self-contained SQL; M1 verified on master, M2 on 18.6 and master):** ```sql -- M1: an UNUSED PASSING argument's COLLATE leaks into the output column -- (DB default collation is C; the text column's collation should be "default"/C): SELECT a, a < 'B' FROM JSON_TABLE(jsonb '{"x":"a"}', '$' COLUMNS (a text PATH '$.x')); -- a | f <- correct (C collation) SELECT a, a < 'B' FROM JSON_TABLE(jsonb '{"x":"a"}', '$' PASSING 'zzz_unused' COLLATE "en_US.utf8" AS v -- never referenced by any path COLUMNS (a text PATH '$.x')); -- a | t <- WRONG: the unused argument's collation leaked into column a -- the leak is persisted by CTAS: CREATE TABLE ctas_leak AS SELECT a FROM JSON_TABLE(jsonb '{"x":"a"}', '$' PASSING 'zzz_unused' COLLATE "en_US.utf8" AS v COLUMNS (a text PATH '$.x')); SELECT attcollation FROM pg_attribute WHERE attrelid =3D 'ctas_leak'::regclass AND attname =3D 'a'; -- "en_US.utf8" <- persisted (control table without PASSING: "default") -- M2: spurious collation-mismatch rejection (introduced by e405a46c45b; on 18.6 accepted): SELECT ROW('a' COLLATE "en_US.utf8", 'b' COLLATE "C"); -- accepted (as everywhere) SELECT * FROM JSON_TABLE(jsonb '{}', '$' PASSING 'a' COLLATE "en_US.utf8" AS x, 'b' COLLATE "C" AS y COLUMNS (n FOR ORDINALITY)); -- ERROR 42P21: collation mismatch between explicit collations "en_US.utf8" and "C" -- (on 18.6 the same query returns n=3D1; expected: accepted =E2=80=94 the = two passing -- values are independent path variables) ``` **Expected behavior:** PASSING values are path variables, not part of any output column expression: their collation must not be consulted when deriving the output columns' collation (M1), and independent passing values must not be merged into one collation derivation (M2).