BUG #19872: JSON_TABLE: an UNUSED PASSING argument's COLLATE leaks into the output columns (wrong results, persi

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: 303677365(at)qq(dot)com
Subject: BUG #19872: JSON_TABLE: an UNUSED PASSING argument's COLLATE leaks into the output columns (wrong results, persi
Date: 2026-11-11 08:57:56
Message-ID: 19872-fe8b70fdefd5d598@postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 19872
Logged by: chunling qin
Email address: 303677365(at)qq(dot)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).

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2026-11-11 09:25:17 BUG #19873: planner silently drops the second ORDER BY sort key when both keys reference the same expression und
Previous Message PG Bug reporting form 2026-11-11 08:53:50 BUG #19871: JSON_TABLE evaluates each PASSING argument once per column - a named variable $v gets different valu