pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19871: JSON_TABLE evaluates each PASSING argument once per column - a named variable $v gets different valu
Date: Wed, 11 Nov 2026 08:53:50 +0000
Message-ID: <19871-e2aef51dcfd941f3@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19871
Logged by:          chunling qin
Email address:      303677365@qq.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.








Message-ID: <19871-e2aef51dcfd941f3@postgresql.org>
Permalink:  ../19871-e2aef51dcfd941f3@postgresql.org/
Also on:    postgresql.org/message-id/19871-e2aef51dcfd941f3@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, 303677365@qq.com
  Subject: Re: BUG #19871: JSON_TABLE evaluates each PASSING argument once per column - a named variable $v gets different valu
  In-Reply-To: <19871-e2aef51dcfd941f3@postgresql.org>
  References: <19871-e2aef51dcfd941f3@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox