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

Hi,

On Wed, 28 Oct 2026 at 16:10, Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> >
> > 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.
> > > 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].

Thanks for the patch, I tested it and the changes lgtm.

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

Sounds good.

Regards,
Ayush