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 #19872: JSON_TABLE: an UNUSED PASSING argument's COLLATE leaks into the output columns (wrong results, persi
Date: Wed, 11 Nov 2026 08:57:56 +0000
Message-ID: <19872-fe8b70fdefd5d598@postgresql.org> (raw)

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:        

**Description:**

Two defects, one root cause (`assign_collations_walker()` treats
`JsonExpr.passing_values` as participating in deriving the output columns'
collation):

1. **M1 — wrong results (silent), persisted**: adding a PASSING argument
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` = the leaked collation), so
the wrong results survive.
2. **M2 — spurious rejection**: two independent PASSING arguments with
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 = 'ctas_leak'::regclass AND attname = '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=1; expected: accepted — 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).








Message-ID: <19872-fe8b70fdefd5d598@postgresql.org>
Permalink:  ../19872-fe8b70fdefd5d598@postgresql.org/
Also on:    postgresql.org/message-id/19872-fe8b70fdefd5d598@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 #19872: JSON_TABLE: an UNUSED PASSING argument's COLLATE leaks into the output columns (wrong results, persi
  In-Reply-To: <19872-fe8b70fdefd5d598@postgresql.org>
  References: <19872-fe8b70fdefd5d598@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