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

Hi,

On Thu, 22 Oct 2026 at 11:56, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> 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.

Thanks for the report and repro.

> 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.

Hmm, you are right.

We probably need to give each analysis its own copy of the raw
arguments. Somewhat like [1].

We may need to do something about volatile PASSING expressions
being evaluated once per column per row. That looks like a separate
issue to me, and fixing it would probably need executor changes..

Adding Amit in cc for comments on this.

Regards,
Ayush

[1]
diff --git a/src/backend/parser/parse_jsontable.c
b/src/backend/parser/parse_jsontable.c
index d86c6946ee9..82da8929d8e 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -150,7 +150,7 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
jfe->op = JSON_TABLE_OP;
jfe->context_item = jt->context_item;
jfe->pathspec = (Node *) rootPathSpec->string;
- jfe->passing = jt->passing;
+ jfe->passing = copyObject(jt->passing);
jfe->on_empty = NULL;
jfe->on_error = jt->on_error;
jfe->location = jt->location;
@@ -529,7 +529,7 @@ transformJsonTableColumn(JsonTableColumn *jtc,
Node *contextItemExpr,
pathspec = makeStringConst(path.data, -1);
}
jfexpr->pathspec = pathspec;
- jfexpr->passing = passingArgs;
+ jfexpr->passing = copyObject(passingArgs);
jfexpr->output = makeNode(JsonOutput);
jfexpr->output->typeName = jtc->typeName;
jfexpr->output->returning = makeNode(JsonReturning);