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]