| 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).
| 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 |