BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink"

The following bug has been logged on the website:

Bug reference: 19786
Logged by: Alperen Keles
Email address: alpkeles99(at)gmail(dot)com
PostgreSQL version: 17.0
Operating system: Ubuntu 24.04 x86_64
Description:

Hello,

A subquery in a JSON_TABLE PASSING argument makes the query fail at
parse time with an internal error, as soon as the table has a column
other than FOR ORDINALITY:

SELECT * FROM JSON_TABLE('[1,2]'::jsonb, '$[*]' PASSING (SELECT 1) AS v
COLUMNS (o FOR ORDINALITY)) jt;
o
---
1
2

SELECT * FROM JSON_TABLE('[1,2]'::jsonb, '$[*]' PASSING (SELECT 1) AS v
COLUMNS (a int PATH '$')) jt;
ERROR: XX000: unexpected non-SELECT command in SubLink
LOCATION: transformSubLink, parse_expr.c:1902

The same PASSING works in JSON_QUERY:

SELECT JSON_QUERY('[1,2]'::jsonb, '$[*] ? (@ == $v)' PASSING (SELECT 1) AS
v);
json_query
------------
1

The error is the same for a correlated subquery, for EXISTS (...),
ARRAY(...) and x IN (SELECT ...), for a subquery inside a larger
expression (1 + (SELECT 1), (SELECT 1)::text), whether or not any path
refers to the variable, and for any column that has a path: regular,
EXISTS, FORMAT JSON, or a regular column without PATH, at top level or
only under NESTED PATH. CREATE VIEW, PREPARE and SQL function bodies
containing such a query fail the same way. Only tables whose columns
are all FOR ORDINALITY are accepted.

Reproduced on 17.0 (REL_17_0), 17.11, 18.6 (REL_18_STABLE dfbc1507cdb),
19rc1 (REL_19_STABLE 1212d5d33b1) and master a261fc2b722, Ubuntu 24.04
x86_64, --enable-cassert.

As far as I can tell, transformJsonTable() in parse_jsontable.c hands
the raw jt->passing list both to the JsonFuncExpr it builds for the
row pattern (docexpr) and, through transformJsonTableColumns(), to the
JsonFuncExpr of every path column (jfexpr->passing = passingArgs in
transformJsonTableColumn()), and each of those is passed to
transformExpr(). So every raw PASSING expression is transformed once
for the row pattern and once more per column. transformSubLink()
stores the analyzed Query back into the raw SubLink, so the second
transformation finds a Query instead of a SELECT statement. This dates
from the JSON_TABLE commit de3600452b6.

The per-column copies are also visible without a subquery: each
column evaluates its own copy of the PASSING expressions, once per row,
while the row pattern uses the TableFunc's copy, evaluated once. With
a volatile expression, the row pattern and each column see different
values of the same variable:

CREATE SEQUENCE s;
SELECT * FROM JSON_TABLE('[1,2,3]'::jsonb, '$[*]'
PASSING nextval('s') AS v
COLUMNS (o FOR ORDINALITY, a int PATH '$v', b int PATH '$v')) jt;
o | a | b
---+---+---
1 | 2 | 3
2 | 4 | 5
3 | 6 | 7

SELECT currval('s'); -- 7; with only the ordinality column it is 1

and a column filter with the same condition as the row pattern can
disagree with it:

SELECT setval('s', 1, false);
SELECT * FROM JSON_TABLE('[1,2,3,4,5]'::jsonb, '$[*] ? (@ >= $v)'
PASSING nextval('s') AS v
COLUMNS (x int PATH '$', vv int PATH '$v',
ok bool EXISTS PATH '$ ? (@ >= $v)')) jt;
x | vv | ok
---+----+----
1 | 3 | f
2 | 6 | f
3 | 9 | f
4 | 12 | f
5 | 15 | f

(Every row passed "@ >= $v" in the row pattern, with v = 1, yet "ok" is
false for all of them.) JSON_QUERY with the same PASSING calls nextval
once. This is not the JsonValueExpr double evaluation reported in
"Volatile expressions in SQL/JSON functions are evaluated twice"; the
count here grows with columns times rows.

Found with a query fuzzer.

Regards,
Alperen Keles