pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dewei Dai <daidewei1970@163.com>
To: Pgsql Hackers <pgsql-hackers@lists.postgresql.org>
Subject: [PATCH] Reject duplicate SQL/JSON PASSING argument names
Date: Wed, 18 Nov 2026 10:55:55 +0800
Message-ID: <202611181055551263943@163.com> (raw)

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;

view thread (2+ messages)  latest in thread

Message-ID: <202611181055551263943@163.com>
Permalink:  ../202611181055551263943@163.com/
Also on:    postgresql.org/message-id/202611181055551263943@163.com

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-hackers@postgresql.org
  Cc: daidewei1970@163.com
  Subject: Re: [PATCH] Reject duplicate SQL/JSON PASSING argument names
  In-Reply-To: <202611181055551263943@163.com>
  References: <202611181055551263943@163.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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