Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJmBQ-00000000QYI-3JL6 for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Oct 2026 06:21:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xJmBO-00000009A7C-36Fy for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Oct 2026 06:21:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJeaS-00000007NNO-3cqV for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 22:15:08 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJeaQ-00000000Iai-0eqc for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 22:15:08 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=kSADhpkikHVkZ8ZiidHBtA5EcouzZqVJwfwe06IgzF0=; b=Qk9HshncFspt8T6wfsEQIJmrnL d9lG6MXbm4JTi6yS4ARDfn8pFD4mhhVp1DY4r5utRAzoDWb42HsXd5EhbKuZgxDwWzoQIG+5gO0e5 SqW8JbZhlASEVnIqJACf+Q+w6e0C5KeEG42LVh5tDZcRiOfVPS+i/Sc7VcMguhb0Q4BNXwhaSzxnM hxbpKL54Hr7q9kM4VOprMc1b8AJTxdmgtz5D/TN81BhtNjXvLVGwUxBX/Osk80JtOq1/lLk3RnA1x utxp/lZF2fnPqdXi8PucfY4R2W5B9C1qXThBoV8CIvbZ9Umq7O2SOL6r3kV0gXsWb9ObLDp1tvG7a wCloywaA==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJeaP-00000000lfd-1w83 for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 22:15:05 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xJeaO-00000002CNK-1q0Q for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 22:15:04 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19786: JSON_TABLE: subquery in PASSING gives "unexpected non-SELECT command in SubLink" To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: alpkeles99@gmail.com Reply-To: alpkeles99@gmail.com, pgsql-bugs@lists.postgresql.org Date: Wed, 21 Oct 2026 22:14:57 +0000 Message-ID: <19786-70758103a4b2bff2@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 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, '$[*] ? (@ =3D=3D $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 =3D 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, '$[*] ? (@ >=3D $v)' PASSING nextval('s') AS v COLUMNS (x int PATH '$', vv int PATH '$v', ok bool EXISTS PATH '$ ? (@ >=3D $v)')) jt; x | vv | ok ---+----+---- 1 | 3 | f 2 | 6 | f 3 | 9 | f 4 | 12 | f 5 | 15 | f (Every row passed "@ >=3D $v" in the row pattern, with v =3D 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