pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
[PATCH] Reject duplicate SQL/JSON PASSING argument names
2+ messages / 2 participants
[nested] [flat]

* [PATCH] Reject duplicate SQL/JSON PASSING argument names
@ 2026-11-18 02:55 Dewei Dai <daidewei1970@163.com>
  2026-11-18 06:31 ` Re: [PATCH] Reject duplicate SQL/JSON PASSING argument names Chao Li <li.evan.chao@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Dewei Dai @ 2026-11-18 02:55 UTC (permalink / raw)
  To: Pgsql Hackers <pgsql-hackers@lists.postgresql.org>

Hi,

The attached patch makes SQL/JSON PASSING clauses reject duplicate
variable names instead of silently using the first matching value.

For example, this previously returned only "comedy":

  SELECT jt.kind
  FROM JSON_TABLE(
    '{ "favorites":[{ "kind" : "comedy" },{ "kind" : "drama" }] }',
    '$.favorites[*]?(@.kind == $k1)'
    PASSING 'comedy' AS k1, 'drama' AS k1
    COLUMNS (kind text PATH '$.kind')
  ) AS jt;

With the patch it raises:

  ERROR:  PASSING argument name "k1" is not unique

The same check applies to JSON_EXISTS, JSON_QUERY, JSON_VALUE, and
JSON_TABLE because they all use transformJsonPassingArgs().  The patch
also adds regression tests for JSON_QUERY and JSON_TABLE.

The patch was tested with the full regression suite on current master.

Thanks,
DeWei Dai
HighGo Software Co., Ltd.
https://www.highgo.com/




daidewei1970@163.com


Attachments:

0001-Reject-duplicate-PASSING-argument-names.patch (application/octet-stream, 5.3K) download From: DeWei Dai <daidewei1970@163.com> Date: Wed, 18 Nov 2026 10:38:31 +0800 Subject: [PATCH] Reject duplicate SQL/JSON PASSING argument names SQL/JSON query functions and JSON_TABLE allow a PASSING clause to bind variables used in the path expression. The SQL standard requires the variable names in a PASSING clause to be unique, but transformJsonPassingArgs() did not enforce that. GetJsonPathVar() then selected the first matching variable, so later values with the same name were silently ignored. Reject duplicate names during parse analysis using ERRCODE_DUPLICATE_ALIAS, and add regression coverage for JSON_QUERY and JSON_TABLE. For example, this previously returned only "comedy", depending on which value was listed first: PASSING 'comedy' AS k1, 'drama' AS k1 --- src/backend/parser/parse_expr.c | 11 ++++++++++- src/test/regress/expected/sqljson_jsontable.out | 11 +++++++++++ src/test/regress/expected/sqljson_queryfuncs.out | 9 +++++++++ src/test/regress/sql/sqljson_jsontable.sql | 9 +++++++++ src/test/regress/sql/sqljson_queryfuncs.sql | 4 ++++ 5 files changed, 43 insertions(+), 1 deletion(-) diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 728392f436f..a34014e924c 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -4792,12 +4792,21 @@ transformJsonPassingArgs(ParseState *pstate, const char *constructName, foreach(lc, args) { JsonArgument *arg = castNode(JsonArgument, lfirst(lc)); + String *name = makeString(arg->name); Node *expr = transformJsonValueExpr(pstate, constructName, arg->val, format, InvalidOid, true); + if (list_member(*passing_names, name)) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_ALIAS), + errmsg("PASSING argument name \"%s\" is not unique", + arg->name), + parser_errposition(pstate, + exprLocation((Node *) arg->val->raw_expr)))); + *passing_values = lappend(*passing_values, expr); - *passing_names = lappend(*passing_names, makeString(arg->name)); + *passing_names = lappend(*passing_names, name); } } diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out index 1dcb9479de3..021eef7f562 100644 --- a/src/test/regress/expected/sqljson_jsontable.out +++ b/src/test/regress/expected/sqljson_jsontable.out @@ -755,6 +755,17 @@ FROM JSON_TABLE( 2 (2 rows) +-- Duplicate PASSING argument names are not allowed +SELECT * +FROM JSON_TABLE( + '{ "favorites":[{ "kind":"comedy" },{ "kind":"drama" }] }', + '$.favorites[*]?(@.kind == $k1)' + PASSING 'comedy' AS k1, 'drama' AS k1 + COLUMNS (kind text PATH '$.kind') + ) jt; +ERROR: PASSING argument name "k1" is not unique +LINE 5: PASSING 'comedy' AS k1, 'drama' AS k1 + ^ -- PASSING arguments are also passed to column paths SELECT * FROM JSON_TABLE( diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out index 19db825c8ab..36b507990bf 100644 --- a/src/test/regress/expected/sqljson_queryfuncs.out +++ b/src/test/regress/expected/sqljson_queryfuncs.out @@ -1546,6 +1546,15 @@ SELECT JSON_QUERY(jsonb 'null', '$"Xyz"' PASSING 1 AS "Xyz"); 1 (1 row) +-- Duplicate PASSING argument names are not allowed +SELECT JSON_QUERY(jsonb 'null', '$x' PASSING 1 AS x, 2 AS x); +ERROR: PASSING argument name "x" is not unique +LINE 1: ...ELECT JSON_QUERY(jsonb 'null', '$x' PASSING 1 AS x, 2 AS x); + ^ +SELECT JSON_QUERY(jsonb 'null', '$x' PASSING 1 AS x, 2 AS "x"); +ERROR: PASSING argument name "x" is not unique +LINE 1: ...ECT JSON_QUERY(jsonb 'null', '$x' PASSING 1 AS x, 2 AS "x"); + ^ -- Test PASSING and toasted values CREATE TABLE test_passing_toast (t text); ALTER TABLE test_passing_toast ALTER COLUMN t SET STORAGE EXTENDED; diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql index 73e60411df6..1957f2c7c23 100644 --- a/src/test/regress/sql/sqljson_jsontable.sql +++ b/src/test/regress/sql/sqljson_jsontable.sql @@ -342,6 +342,15 @@ FROM JSON_TABLE( COLUMNS (y text FORMAT JSON PATH '$') ) jt; +-- Duplicate PASSING argument names are not allowed +SELECT * +FROM JSON_TABLE( + '{ "favorites":[{ "kind":"comedy" },{ "kind":"drama" }] }', + '$.favorites[*]?(@.kind == $k1)' + PASSING 'comedy' AS k1, 'drama' AS k1 + COLUMNS (kind text PATH '$.kind') + ) jt; + -- PASSING arguments are also passed to column paths SELECT * FROM JSON_TABLE( diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql index 5c675d98235..a6e1dca6bcf 100644 --- a/src/test/regress/sql/sqljson_queryfuncs.sql +++ b/src/test/regress/sql/sqljson_queryfuncs.sql @@ -517,6 +517,10 @@ SELECT JSON_QUERY(jsonb 'null', '$Xyz' PASSING 1 AS Xyz); SELECT JSON_QUERY(jsonb 'null', '$Xyz' PASSING 1 AS "Xyz"); SELECT JSON_QUERY(jsonb 'null', '$"Xyz"' PASSING 1 AS "Xyz"); +-- Duplicate PASSING argument names are not allowed +SELECT JSON_QUERY(jsonb 'null', '$x' PASSING 1 AS x, 2 AS x); +SELECT JSON_QUERY(jsonb 'null', '$x' PASSING 1 AS x, 2 AS "x"); + -- Test PASSING and toasted values CREATE TABLE test_passing_toast (t text); ALTER TABLE test_passing_toast ALTER COLUMN t SET STORAGE EXTENDED;
^ permalink raw reply [nested|flat] 2+ messages in thread

* Re: [PATCH] Reject duplicate SQL/JSON PASSING argument names
  2026-11-18 02:55 [PATCH] Reject duplicate SQL/JSON PASSING argument names Dewei Dai <daidewei1970@163.com>
@ 2026-11-18 06:31 ` Chao Li <li.evan.chao@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Chao Li @ 2026-11-18 06:31 UTC (permalink / raw)
  To: Dewei Dai <daidewei1970@163.com>; +Cc: Pgsql Hackers <pgsql-hackers@lists.postgresql.org>



> On Nov 18, 2026, at 10:55, Dewei Dai <daidewei1970@163.com> wrote:
> 
> Hi,
> 
> The attached patch makes SQL/JSON PASSING clauses reject duplicate
> variable names instead of silently using the first matching value.
> 
> For example, this previously returned only "comedy":
> 
>   SELECT jt.kind
>   FROM JSON_TABLE(
>     '{ "favorites":[{ "kind" : "comedy" },{ "kind" : "drama" }] }',
>     '$.favorites[*]?(@.kind == $k1)'
>     PASSING 'comedy' AS k1, 'drama' AS k1
>     COLUMNS (kind text PATH '$.kind')
>   ) AS jt;
> 
> With the patch it raises:
> 
>   ERROR:  PASSING argument name "k1" is not unique
> 
> The same check applies to JSON_EXISTS, JSON_QUERY, JSON_VALUE, and
> JSON_TABLE because they all use transformJsonPassingArgs().  The patch
> also adds regression tests for JSON_QUERY and JSON_TABLE.
> 
> The patch was tested with the full regression suite on current master.

I think it's reasonable to reject duplicate PASSING argument names.

Oracle also rejects duplicates:
```
SELECT JSON_VALUE(
    '{"a":1}',
    '$.a?(@ == $x)'
    PASSING 1 AS "x", 2 AS "x"
) FROM dual;

ORA-40464: JSON PASSING clause contained duplicate variable name 'x'.
```

I'm not trying to say PG has to follow Oracle, but explicitly rejecting duplicates is better than silently making a decision for users. I happen to have an ongoing patch [1] that is under discussion between two approaches: 1) silently discarding empty path elements or 2) rejecting empty path elements, and people seem to be more keen on explicitly rejecting them. I think that's in the same spirit as this rejection.

Also, when there are duplicate names, users may be confused about whether the earlier one or the later one should win. And the current doc doesn't have an explanation for that.

Going back to the patch code changes, I have a few comments.

1
```
inline patch 1: src/backend/parser/parse_expr.c (+8/-0 lines) download inline-1.patch--- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -4792,12 +4792,21 @@ transformJsonPassingArgs(ParseState *pstate, const char *constructName, foreach(lc, args) { JsonArgument *arg = castNode(JsonArgument, lfirst(lc)); + String *name = makeString(arg->name); Node *expr = transformJsonValueExpr(pstate, constructName, arg->val, format, InvalidOid, true); + if (list_member(*passing_names, name)) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_ALIAS), + errmsg("PASSING argument name \"%s\" is not unique", + arg->name), + parser_errposition(pstate, + exprLocation((Node *) arg->val->raw_expr))));
``` The result of transformJsonValueExpr() doesn't matter for the dup name check, so we can do the check before calling transformJsonValueExpr(). 2 ``` + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_ALIAS), + errmsg("PASSING argument name \"%s\" is not unique", + arg->name), + parser_errposition(pstate, + exprLocation((Node *) arg->val->raw_expr)))); ``` Modern ereport() style doesn't require the extra parentheses around errcode(), errmsg(), etc. Since this file already has a mix of old and new styles, I think we should use the modern style for new code. 3. The current doc in func-json.sgml doesn't explicitly say anything about duplicate PASSING argument names: ``` The optional <literal>PASSING</literal> clause provides data values for the variables mentioned in the <replaceable>path_expression</replaceable>. The result of the input data evaluation using the aforementioned elements is called the <firstterm>row pattern</firstterm>, which is used as the source for row values in the constructed view. ``` So, I think this patch should update the doc accordingly, for example, adding a simple sentence saying that duplicate PASSING argument names are not allowed. 4. The tests cover JSON_QUERY and JSON_TABLE, but as you explained in the patch email, JSON_EXISTS and JSON_VALUE are also affected. Maybe add test cases for JSON_EXISTS and JSON_VALUE as well. 5. Maybe also mention JSON_EXISTS, JSON_QUERY, and JSON_VALUE in the commit message, as they are fixed by this patch as well. [1] https://www.postgresql.org/message-id/DC2D9FA5-45C4-494F-99FC-F9B95AD4FF55%40gmail.com Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ ^ permalink raw reply [nested|flat] 2+ messages in thread


end of thread, other threads:[~2026-11-18 06:31 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-18 02:55 [PATCH] Reject duplicate SQL/JSON PASSING argument names Dewei Dai <daidewei1970@163.com>
2026-11-18 06:31 ` Chao Li <li.evan.chao@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