pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink"
5+ messages / 3 participants
[nested] [flat]

Commits from this thread:
  2026-10-29 8793978e30 6cb715e2d6 1ecc3cee78 35b349a530  Fix JSON_TABLE with sublinks in PASSING arguments
* BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink"
@ 2026-10-21 22:14 PG Bug reporting form <noreply@postgresql.org>
  2026-10-22 18:55 ` Re: BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: PG Bug reporting form @ 2026-10-21 22:14 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: alpkeles99@gmail.com

The following bug has been logged on the website:

Bug reference:      19786
Logged by:          Alperen Keles
Email address:      alpkeles99@gmail.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








^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink"
  2026-10-21 22:14 BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" PG Bug reporting form <noreply@postgresql.org>
@ 2026-10-22 18:55 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-28 10:39   ` Re: BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" Amit Langote <amitlangote09@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Ayush Tiwari @ 2026-10-22 18:55 UTC (permalink / raw)
  To: alpkeles99@gmail.com, pgsql-bugs@lists.postgresql.org, Amit Langote <amitlangote09@gmail.com>

Hi,

On Thu, 22 Oct 2026 at 11:56, PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      19786
> Logged by:          Alperen Keles
> Email address:      alpkeles99@gmail.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
inline patch 1: src/backend/parser/parse_jsontable.c (+1/-1 lines) download inline-1.patch--- 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); ^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink"
  2026-10-21 22:14 BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" PG Bug reporting form <noreply@postgresql.org>
  2026-10-22 18:55 ` Re: BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" Ayush Tiwari <ayushtiwari.slg01@gmail.com>
@ 2026-10-28 10:39   ` Amit Langote <amitlangote09@gmail.com>
  2026-10-28 14:41     ` Re: BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Amit Langote @ 2026-10-28 10:39 UTC (permalink / raw)
  To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi,

On Fri, Oct 23, 2026 at 3:55 AM Ayush Tiwari
<ayushtiwari.slg01@gmail.com> wrote:
>
> Hi,
>
> On Thu, 22 Oct 2026 at 11:56, PG Bug reporting form
> <noreply@postgresql.org> wrote:
> >
> > The following bug has been logged on the website:
> >
> > Bug reference:      19786
> > Logged by:          Alperen Keles
> > Email address:      alpkeles99@gmail.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%40...

Attachments:

v1-0001-Fix-JSON_TABLE-with-sublinks-in-PASSING-arguments.patch (application/octet-stream, 7.1K) download From 37a4ab831853e4c9d9597125c238cfd6cea841fa Mon Sep 17 00:00:00 2001 From: Amit Langote <amitlan@postgresql.org> Date: Wed, 28 Oct 2026 19:37:49 +0900 Subject: [PATCH v1] Fix JSON_TABLE with sublinks in PASSING arguments transformJsonTable() handed the raw PASSING argument list both to the JsonFuncExpr built for the row pattern and, via transformJsonTableColumn(), to the JsonFuncExpr built for each column other than FOR ORDINALITY, so the same raw expressions were run through transformExpr() once for the row pattern and again for every such column. That doesn't work for sublinks: transformSubLink() replaces the SubLink's raw subselect with the analyzed Query, so the second pass failed with "unexpected non-SELECT command in SubLink". Other nodes that are transformed in place, such as NullTest, failed with "unrecognized node type". Any such PASSING argument therefore made JSON_TABLE fail unless all of its columns were FOR ORDINALITY. Fix by giving each transformation its own copy of the raw PASSING arguments. Bug: #19786 Reported-by: Alperen Keles <alpkeles99@gmail.com> Author: Ayush Tiwari <ayushtiwari.slg01@gmail.com> Co-authored-by: Amit Langote <amitlangote09@gmail.com> Discussion: https://postgr.es/m/19786-70758103a4b2bff2@postgresql.org Backpatch-through: 17 --- src/backend/parser/parse_jsontable.c | 11 ++- .../regress/expected/sqljson_jsontable.out | 80 +++++++++++++++++++ src/test/regress/sql/sqljson_jsontable.sql | 49 ++++++++++++ 3 files changed, 138 insertions(+), 2 deletions(-) diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c index d86c6946ee9..0b469ba636f 100644 --- a/src/backend/parser/parse_jsontable.c +++ b/src/backend/parser/parse_jsontable.c @@ -145,12 +145,18 @@ transformJsonTable(ParseState *pstate, JsonTable *jt) /* * Transform JsonFuncExpr representing the top JSON_TABLE context_item and * pathspec into a dummy JSON_TABLE_OP JsonExpr. + * + * The raw PASSING arguments are transformed here and again for each + * column that has a path (see transformJsonTableColumn()). Since + * transformExpr() modifies some raw nodes in place, such as replacing a + * SubLink's subselect with its Query, each transformation must be given + * its own copy, so that jt->passing stays untouched. */ jfe = makeNode(JsonFuncExpr); 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 +535,8 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, pathspec = makeStringConst(path.data, -1); } jfexpr->pathspec = pathspec; - jfexpr->passing = passingArgs; + /* Each column gets its own copy; see transformJsonTable(). */ + jfexpr->passing = copyObject(passingArgs); jfexpr->output = makeNode(JsonOutput); jfexpr->output->typeName = jtc->typeName; jfexpr->output->returning = makeNode(JsonReturning); diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out index ae64dbed303..441ee6306b8 100644 --- a/src/test/regress/expected/sqljson_jsontable.out +++ b/src/test/regress/expected/sqljson_jsontable.out @@ -757,6 +757,86 @@ FROM JSON_TABLE( (3 rows) +-- PASSING arguments containing sublinks, used by the row pattern and by +-- regular, FORMAT JSON and EXISTS columns +SELECT * +FROM JSON_TABLE( + jsonb '[1,2,3]', + '$[*] ? (@ < $x)' + PASSING (SELECT 3) AS x, 1 + (SELECT 1) AS y + COLUMNS ( + a int PATH '$', + b text FORMAT JSON PATH '$ ? (@ == $y)', + c bool EXISTS PATH '$ ? (@ >= $y)' + ) + ) jt; + a | b | c +---+---+--- + 1 | | f + 2 | 2 | t +(2 rows) + +-- Correlated sublinks of various kinds in PASSING, used only under NESTED PATH +SELECT x, jt.* +FROM + generate_series(1, 3) x, + JSON_TABLE( + jsonb '[[1,2,3]]', + '$[*]' + PASSING (SELECT x) AS x, + EXISTS (SELECT 1 WHERE x > 1) AS e, + x IN (SELECT 2) AS i, + to_jsonb(ARRAY(SELECT x)) AS arr + COLUMNS ( + NESTED PATH '$[*] ? (@ >= $x)' + COLUMNS ( + z int PATH '$', + e bool PATH '$e', + i bool PATH '$i', + arr jsonb PATH '$arr' + ) + ) + ) jt; + x | z | e | i | arr +---+---+---+---+----- + 1 | 1 | f | f | [1] + 1 | 2 | f | f | [1] + 1 | 3 | f | f | [1] + 2 | 2 | t | t | [2] + 2 | 3 | t | t | [2] + 3 | 3 | t | f | [3] +(6 rows) + +-- Sublinks in PASSING arguments survive a view's definition +CREATE VIEW jsonb_table_view_passing_sublink AS +SELECT * +FROM JSON_TABLE( + jsonb '[1,2,3]', + '$[*] ? (@ < $x)' + PASSING (SELECT 3) AS x + COLUMNS (a int PATH '$', b bool EXISTS PATH '$ ? (@ == $x - 1)') + ) jt; +\sv jsonb_table_view_passing_sublink +CREATE OR REPLACE VIEW public.jsonb_table_view_passing_sublink AS + SELECT a, + b + FROM JSON_TABLE( + '[1, 2, 3]'::jsonb, '$[*]?(@ < $"x")' AS json_table_path_0 + PASSING + ( SELECT 3) AS x + COLUMNS ( + a integer PATH '$', + b boolean EXISTS PATH '$?(@ == $"x" - 1)' + ) + ) jt +SELECT * FROM jsonb_table_view_passing_sublink; + a | b +---+--- + 1 | f + 2 | t +(2 rows) + +DROP VIEW jsonb_table_view_passing_sublink; -- Should fail (not supported) SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)); ERROR: only string constants are supported in JSON_TABLE path specification diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql index 2a33aaec57f..f889727cc0b 100644 --- a/src/test/regress/sql/sqljson_jsontable.sql +++ b/src/test/regress/sql/sqljson_jsontable.sql @@ -346,6 +346,55 @@ FROM JSON_TABLE( COLUMNS (a text FORMAT JSON PATH '$ ? (@ < $y)') ) jt; +-- PASSING arguments containing sublinks, used by the row pattern and by +-- regular, FORMAT JSON and EXISTS columns +SELECT * +FROM JSON_TABLE( + jsonb '[1,2,3]', + '$[*] ? (@ < $x)' + PASSING (SELECT 3) AS x, 1 + (SELECT 1) AS y + COLUMNS ( + a int PATH '$', + b text FORMAT JSON PATH '$ ? (@ == $y)', + c bool EXISTS PATH '$ ? (@ >= $y)' + ) + ) jt; + +-- Correlated sublinks of various kinds in PASSING, used only under NESTED PATH +SELECT x, jt.* +FROM + generate_series(1, 3) x, + JSON_TABLE( + jsonb '[[1,2,3]]', + '$[*]' + PASSING (SELECT x) AS x, + EXISTS (SELECT 1 WHERE x > 1) AS e, + x IN (SELECT 2) AS i, + to_jsonb(ARRAY(SELECT x)) AS arr + COLUMNS ( + NESTED PATH '$[*] ? (@ >= $x)' + COLUMNS ( + z int PATH '$', + e bool PATH '$e', + i bool PATH '$i', + arr jsonb PATH '$arr' + ) + ) + ) jt; + +-- Sublinks in PASSING arguments survive a view's definition +CREATE VIEW jsonb_table_view_passing_sublink AS +SELECT * +FROM JSON_TABLE( + jsonb '[1,2,3]', + '$[*] ? (@ < $x)' + PASSING (SELECT 3) AS x + COLUMNS (a int PATH '$', b bool EXISTS PATH '$ ? (@ == $x - 1)') + ) jt; +\sv jsonb_table_view_passing_sublink +SELECT * FROM jsonb_table_view_passing_sublink; +DROP VIEW jsonb_table_view_passing_sublink; + -- Should fail (not supported) SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)); -- 2.47.3
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink"
  2026-10-21 22:14 BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" PG Bug reporting form <noreply@postgresql.org>
  2026-10-22 18:55 ` Re: BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-28 10:39   ` Re: BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" Amit Langote <amitlangote09@gmail.com>
@ 2026-10-28 14:41     ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-29 01:40       ` Re: BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" Amit Langote <amitlangote09@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Ayush Tiwari @ 2026-10-28 14:41 UTC (permalink / raw)
  To: Amit Langote <amitlangote09@gmail.com>; +Cc: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Hi,

On Wed, 28 Oct 2026 at 16:10, Amit Langote <amitlangote09@gmail.com> wrote:
> >
> > On Thu, 22 Oct 2026 at 11:56, PG Bug reporting form
> > <noreply@postgresql.org> wrote:
> > >
> > > The following bug has been logged on the website:
> > >
> > > Bug reference:      19786
> > > Logged by:          Alperen Keles
> > > Email address:      alpkeles99@gmail.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






^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink"
  2026-10-21 22:14 BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" PG Bug reporting form <noreply@postgresql.org>
  2026-10-22 18:55 ` Re: BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  2026-10-28 10:39   ` Re: BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" Amit Langote <amitlangote09@gmail.com>
  2026-10-28 14:41     ` Re: BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" Ayush Tiwari <ayushtiwari.slg01@gmail.com>
@ 2026-10-29 01:40       ` Amit Langote <amitlangote09@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Amit Langote @ 2026-10-29 01:40 UTC (permalink / raw)
  To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

On Wed, Oct 28, 2026 at 11:41 PM Ayush Tiwari
<ayushtiwari.slg01@gmail.com> wrote:
> On Wed, 28 Oct 2026 at 16:10, Amit Langote <amitlangote09@gmail.com> wrote:
> > 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.

Thanks, pushed.

-- 
Thanks, Amit Langote






^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2026-10-29 01:40 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-21 22:14 BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" PG Bug reporting form <noreply@postgresql.org>
2026-10-22 18:55 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-10-28 10:39   ` Amit Langote <amitlangote09@gmail.com>
2026-10-28 14:41     ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-10-29 01:40       ` Amit Langote <amitlangote09@gmail.com>

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