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

Поиск
Список
Период
Сортировка
Искать

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

От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
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).




FAQ