BUG #19871: JSON_TABLE evaluates each PASSING argument once per column - a named variable $v gets different valu

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.

Browse pgsql-bugs by date

  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