| 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 #19871: JSON_TABLE evaluates each PASSING argument once per column - a named variable $v gets different valu |
| Date: | 2026-11-11 08:53:50 |
| Message-ID: | 19871-e2aef51dcfd941f3@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: 19871
Logged by: chunling qin
Email address: 303677365(at)qq(dot)com
PostgreSQL version: 18.6
Operating system: Linux x86_64; platform-independent (reproduced ide
Description:
**Description:**
JSON_TABLE re-evaluates every PASSING expression for each output column (and
once more for the row pattern). With a VOLATILE PASSING expression this is
directly observable, and worse: **the same named variable `$v` read by two
different columns — or by the row-pattern filter and a column — gets
different values within a single row**, so filter results and column values
disagree.
**Reproduction (self-contained SQL; verified on 18.6 and master):**
```sql
-- M1: the SAME variable $v read by two columns differs:
DROP SEQUENCE IF EXISTS s1, s2, s3, s4;
CREATE SEQUENCE s1;
SELECT * FROM JSON_TABLE(jsonb '[10]', '$[*]'
PASSING nextval('s1') AS v
COLUMNS (v1 int PATH '$v', v2 int PATH '$v', item int PATH '$'));
-- v1 = 1, v2 = 2, item = 10 (two columns disagree about $v)
SELECT last_value AS evaluations_count FROM s1; -- 4 (1 + 3 columns)
-- M2: the row-pattern filter and a column read of $v disagree in ONE row:
CREATE SEQUENCE s2;
SELECT item, v_seen, (item = v_seen) AS consistent
FROM JSON_TABLE(jsonb '[1,2,3]', '$[*] ? (@ == $v)'
PASSING nextval('s2') AS v
COLUMNS (item int PATH '$', v_seen int PATH '$v'));
-- consistent = f for every row (filter picked the row using one value of
$v,
-- the column reports another)
-- control: immutable PASSING -> always consistent:
SELECT item, v_seen, (item = v_seen) AS consistent
FROM JSON_TABLE(jsonb '[1,2,3]', '$[*] ? (@ == $v)'
PASSING 2 AS v
COLUMNS (item int PATH '$', v_seen int PATH '$v'));
-- M3: even a single column evaluates PASSING twice:
CREATE SEQUENCE s3;
SELECT * FROM JSON_TABLE(jsonb '[10]', '$[*]'
PASSING nextval('s3') AS v
COLUMNS (v1 int PATH '$v'));
SELECT last_value AS evaluations_count FROM s3; -- 2
-- control: a regular SQL/JSON function evaluates its PASSING once:
CREATE SEQUENCE s4;
SELECT JSON_QUERY(jsonb '[10]', '$[*]' PASSING nextval('s4') AS v RETURNING
jsonb) AS q,
nextval('s4') AS after; -- after = 2 => one evaluation inside
JSON_QUERY
```
**Actual results:** each PASSING expression is evaluated once per column
plus once for the row pattern (last_value = 4 for three columns, 2 for one);
the same `$v` yields different values across columns and between the filter
and the columns.
**Expected behavior:** PASSING expressions should be evaluated once per
output row (as the regular SQL/JSON functions already do — see the
JSON_QUERY control), with every column and the row pattern seeing the same
value.
**Impact:** with VOLATILE/STABLE PASSING expressions the results are wrong
within a row; side effects are multiplied by the column count.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | PG Bug reporting form | 2026-11-11 08:57:56 | BUG #19872: JSON_TABLE: an UNUSED PASSING argument's COLLATE leaks into the output columns (wrong results, persi |
| Previous Message | PG Bug reporting form | 2026-11-11 08:48:00 | BUG #19870: WinGetFuncArgInPartition(..., WINDOW_SEEK_TAIL, ...) returns wrong rows under IGNORE NULLS |