| From: | Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> |
|---|---|
| To: | Dewei Dai <daidewei1970(at)163(dot)com> |
| Cc: | Pgsql Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: [PATCH] Reject duplicate SQL/JSON PASSING argument names |
| Date: | 2026-11-18 06:31:33 |
| Message-ID: | 1C45CA29-5CEA-463B-A895-3969DDF0B78A@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
> On Nov 18, 2026, at 10:55, Dewei Dai <daidewei1970(at)163(dot)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
```
--- 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/
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Bertrand Drouvot | 2026-11-18 06:43:47 | Re: Add a hook for handling logical decoding messages on subscribers. |
| Previous Message | Koshino Taiki | 2026-11-18 06:28:25 | Re: Proposal: Include CHECK constraint expression in violation error DETAIL |