postgres.git / summary / log / commit / refs
commit 1ecc3cee7824e607cc3b59c2dd8043c03b90126d
Author: Amit Langote <amitlan@postgresql.org>
Date: Thu Oct 29 01:38:40 2026 +0000
First released in: 17.12, 18.7, 19.0
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 +++-
src/test/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 32a1e8629b2..a870892beef 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -117,12 +117,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;
@@ -430,7 +436,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 1c847b70d0b..70eb2aae666 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -732,6 +732,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 686621bd276..60ce4f0429f 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -331,6 +331,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));
[parent: 67f375edb58e]