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

Hi,

On Fri, Oct 23, 2026 at 3:55 AM Ayush Tiwari
<ayushtiwari(dot)slg01(at)gmail(dot)com> wrote:
>
> 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.

+1

> > 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].
>
> Adding Amit in cc for comments on this.

Thanks for adding me Ayush.

> 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);

The fix makes sense. Here's that in a patch. I found another issue
like this where INSERT fails with the same error due to a missing
copyObject(), which I posted at [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..

Yeah, let's discuss that separately.

--
Thanks, Amit Langote

[1] https://www.postgresql.org/message-id/CA%2BHiwqHCZLZV_20nz3fQX%2B1%3DvyFLDMbG0_3wY%3DQujNKZWts0nw%40mail.gmail.com