Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uthQd-00Crwz-BP for pgsql-hackers@arkaria.postgresql.org; Wed, 03 Sep 2025 06:57:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uthQb-007csG-61 for pgsql-hackers@arkaria.postgresql.org; Wed, 03 Sep 2025 06:57:09 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uthQa-007cs8-QV for pgsql-hackers@lists.postgresql.org; Wed, 03 Sep 2025 06:57:09 +0000 Received: from mail-pj1-x1036.google.com ([2607:f8b0:4864:20::1036]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uthQX-000J6J-0j for pgsql-hackers@postgresql.org; Wed, 03 Sep 2025 06:57:08 +0000 Received: by mail-pj1-x1036.google.com with SMTP id 98e67ed59e1d1-327ceef65afso6269822a91.0 for ; Tue, 02 Sep 2025 23:57:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1756882624; x=1757487424; darn=postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=rTaXyS3BuGuSvtAMn05JGpKbgXP6N8S4JSG+tCgWhFU=; b=ZxDopsOAx1kgfQ9BAU9K8RKm+qVp0UyXmryyQ2aLo4mqQLE9ShoQHbiDlVIIskQzED g+RF7NQep1gabR1hnWlKasUZZIzxAZlXv8qwDNcv5sRLKb4fwg0ak7E9259Di2xF0Mr6 D+shnxsQ1KHJoLAWHkBP1wTEHRbjHTtrARY85pZAByx6IISb5TS62mendhIIWKLQLfCu CI97ubgenROpKYSZaro/OmF561YhkhnZRshsICQyuVRPkDkZKVZ68cipwH4GDkb92eGs z3thBrXcS6BeWH8ObLyb+syTTnNtQ6xCGNXeB+a+ENIZqoLhZA2pR/qN4ggcJ5WgqVR9 FSww== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1756882624; x=1757487424; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=rTaXyS3BuGuSvtAMn05JGpKbgXP6N8S4JSG+tCgWhFU=; b=pOGf4bn8kAYYQugdn3fSuGX8V7kxOMZKYYULpbmkyHrgOGerza0pOqZ3A+nld4fqP/ UEMDsCb8qyrgKyWP/X4ueVl5E2l2Y64J26nuOnsGD4jaVtGAC70DC1rxOiQK2JH8iKka h3FZmDragBttQEvBfflwn3GC2qHqPX/CrWTsDYKfTwj0a3k4cS3Q/XD+zyFrnpiDPFki 8tmZDAua8HSI/Z+n7n7On0Defedp7xsPOFg60GwMUJE9ICR3hKLyu3e6ZRKJ7tJ26OOu yDcTnskhtMBK4sYM0PvwyfMEIHUeF7DX8ZelctU3Wl3aklY0hW0BHHLWixaSjpTg+nYm 5eeQ== X-Forwarded-Encrypted: i=1; AJvYcCXFQmTShcNKuGgdgmHQ9ZDsIcy3hZqmFEJHwWETNEpx1axQzfrGaXk9kp8PCFAPJH6jO/HZFwD3IjwK5Igg@postgresql.org X-Gm-Message-State: AOJu0YzwJveo3FoN64LKTf423lyYwRBuyKOrssCLpK+ZcXvYzPssmXqv pPO7m0dhcKeLSUnKNCa1EY8SVlK9YTu/PEW46junKox4RtC0yTkL9ddd X-Gm-Gg: ASbGncs1oOZMydG7X0wWYqCC1v+VkenedAGMP9ZjOVm+V8uqFXQ769EpOzhdx9SAMoW 0jRkRs+eun7lYn+axnk6vezGYZCEqmeGnkJxop4X3OSirDwVfO6wkCnAgNgb/cl0pXTVM2Jeiig WiwlvyAIfzIm7GD5mIjEZdCHnw3R6QaIjc8H0gRyl0V1RhUxBdqLuhnIxcrHiLHt2chr7QLkz66 Au/eqtY1vkCEf7yWXE7+COqvMIAjViJAIn9AOfyMxeizy7MpdKa0nd9WhDI4dKn2v2V7KntEzCA cFyt2T3ss0CYtlemgIKNrabR2MeoD7+X15AKMCAeAIZ+76fRWXjSX5T4nFT9RBVnNZTvivOUo/e z4cmjzZafz/HEHb3zn27+mCmPvqXRbhjN X-Google-Smtp-Source: AGHT+IF98R8MrFU9p6dFRh6fr4MiGiy+Ev9v7Pb/bf3vvfgCtDtdCIECiy09O08Pvj3ZjVz3EpLZ+g== X-Received: by 2002:a17:90b:582b:b0:328:acc:da37 with SMTP id 98e67ed59e1d1-32815414342mr13196136a91.5.1756882624202; Tue, 02 Sep 2025 23:57:04 -0700 (PDT) Received: from smtpclient.apple ([142.171.105.12]) by smtp.gmail.com with ESMTPSA id 98e67ed59e1d1-327976b5074sm9275416a91.4.2025.09.02.23.56.58 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 02 Sep 2025 23:57:03 -0700 (PDT) From: Chao Li Message-Id: <052E941B-C905-448F-AE3A-753C12AAC938@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_3843D48C-954D-483D-8903-4D9700BDE3E8" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\)) Subject: Re: SQL:2023 JSON simplified accessor support Date: Wed, 3 Sep 2025 14:56:44 +0800 In-Reply-To: Cc: Nikita Glukhov , jian he , Nikita Malakhov , Vik Fearing , Mark Dilger , Matheus Alcantara , Peter Eisentraut , Andrew Dunstan , PostgreSQL Hackers , "David E. Wheeler" To: Alexandra Wang References: <21fa36b4-6d56-4556-b524-9e3955b922c8@eisentraut.org> <290418A2-3A9B-4304-AAAD-4A40CFF1CB93@gmail.com> <98C6F445-773B-4C82-8198-7F5CD56D5252@gmail.com> X-Mailer: Apple Mail (2.3826.700.81) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_3843D48C-954D-483D-8903-4D9700BDE3E8 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Sep 3, 2025, at 10:20, Alexandra Wang = wrote: >=20 > This change would give an incorrect result for an accessor like > "[0].a" when the jsonb column data is a jsonb object instead of a > jsonb array. I've added two new test cases to cover this scenario: >=20 > In jsonb.sql, the newly added tests are: > select (jb)[0].a from test_jsonb_dot_notation; -- returns same result = as (jb).a > select (jb)[1].a from test_jsonb_dot_notation; -- returns NULL I think the latest patch is still wrong. Ultimately, dot notation = =E2=80=9C.a", index =E2=80=9C[0] and slice "[1:4]=E2=80=9D rely on = jsonpath, and subscript [=E2=80=9Ca=E2=80=9D] relies on the rest logic = in jsonb_subscript_transform() in =E2=80=9Cforeach=E2=80=9D. Now =E2=80=9Cjsonb_check_jsonpath_needed()=E2=80=9D checks only dot = nation and slice, but not checking index case. So that reason why your = case =E2=80=9Cselect (jb)[0].a=E2=80=9D works is because the second = indirection is a dot nation. However, =E2=80=9Cselect = (jb)[0][=E2=80=98a=E2=80=99]=E2=80=9D will not work. See my test: ``` evantest=3D# select ('{"name": "Alice", "age": 30}'::jsonb)[0].name; name --------- "Alice" (1 row) evantest=3D# select ('{"name": "Alice", "age": 30}'::jsonb)[0]['name']; jsonb ------- (1 row) ``` In my test, (jsonb)[0][=E2=80=99name=E2=80=99] should also return = =E2=80=9CAlice=E2=80=9D. So, end up, =E2=80=9Cjsonb_check_jsonpath_needed()=E2=80=9D only does = incomplete and inaccurate checks, only jsonb_subscript_make_jsonpath() = can make an accurate decision and return a null jsonpath upon subscript = =E2=80=9C[=E2=80=98a=E2=80=99]=E2=80=9D. As a result, =E2=80=9Cjson_check_jsonpath_needed()=E2=80=9D feels not = needed at all. In jsonb_subscript_transform(), just go ahead to run = jsonb_subscript_make_jsonpath() first, if returned jsonpath is NULL, = then run rest of logic. With my dirty change of removing json_check_jsonpath_needed: ``` chaol@ChaodeMacBook-Air postgresql % git diff diff --git a/src/backend/utils/adt/jsonbsubs.c = b/src/backend/utils/adt/jsonbsubs.c index 374040b3b4e..d9faab5c799 100644 --- a/src/backend/utils/adt/jsonbsubs.c +++ b/src/backend/utils/adt/jsonbsubs.c @@ -416,12 +416,12 @@ jsonb_subscript_transform(SubscriptingRef *sbsref, sbsref->refrestype =3D JSONBOID; sbsref->reftypmod =3D -1; - if (jsonb_check_jsonpath_needed(*indirection, isSlice)) - { + //if (jsonb_check_jsonpath_needed(*indirection, isSlice)) + //{ jsonb_subscript_make_jsonpath(pstate, indirection, = sbsref); if (sbsref->refjsonbpath) return; - } + //} /* * We reach here only in two cases: (a) the JSON simplified = accessor is ``` You can see: ``` evantest=3D# select ('{"name": "Alice", "age": 30}'::jsonb)[0]['name']; jsonb --------- "Alice" (1 row) evantest=3D# select ('{"name": "Alice", "age": 30}'::jsonb)[0].name; name --------- "Alice" (1 row) ``` Then I found =E2=80=9Cmake check=E2=80=9D failed. For example the first = broken test case: ``` @@ -4998,7 +4998,7 @@ select ('123'::jsonb)[0]; jsonb ------- - + 123 (1 row) ``` The test expected an empty result, which implies =E2=80=9Cstrict=E2=80=9D = mode. But the problem is, which mode should be the default? JSON_QUERY() uses = =E2=80=9Clax=E2=80=9D as default mode. And from Peter Eisentraut=E2=80=99s= blog: = https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-finished-here-is-= whats-new ``` The semantics of this are defined in terms of JSON_QUERY and JSON_VALUE = constructions (which have been available since SQL:2016), so this really = just syntactic sugar. ``` Also feels like =E2=80=9Clax=E2=80=9D should be the default mode. If = that is true, then my dirty change of removing = =E2=80=9Cjson_check_jsonpath_need()=E2=80=9D works properly. The current logic with this patch sounds strange. Because = =E2=80=9Cjson_check_jsonpath_need()=E2=80=9D iterate through unprocessed = indirections to decide if jsonpath is needed (lax mode). With this = logic: 1) if index [0] directly following dot notation, like (data).a[0], = it=E2=80=99s lax mode 2) if index [0] directly following subscript [=E2=80=98a=E2=80=99], like = (data)[=E2=80=98a=E2=80=99][0], it=E2=80=99s strict mode 3) if index [0] directly following the data column, then if there is a = dot nation in indirection list, use lax mode, otherwise strict mode. For = the failed test case, as there is no more indirection following [0], so = it expected strict mode. I wonder where this behavior is defined? With my change, 1) and 2) are the same, for 3), if index [0] directly = following the data column, regardless what indirections are followed, = it=E2=80=99s by default lax mode. So, I think this is a design decision. Maybe I missed something from = your previous design, but I don=E2=80=99t find anything about that from = the commit comments. I feel this would be better aligned with 1) and 2). Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ --Apple-Mail=_3843D48C-954D-483D-8903-4D9700BDE3E8 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Sep 3, 2025, at 10:20, Alexandra Wang = <alexandra.wang.oss@gmail.com> wrote:

This change = would give an incorrect result for an accessor like
"[0].a" when the = jsonb column data is a jsonb object instead of a
jsonb array.  = I've added two new test cases to cover this scenario:

In jsonb.sql, = the newly added tests are:
select = (jb)[0].a from test_jsonb_dot_notation; -- returns same result as = (jb).a
select (jb)[1].a from = test_jsonb_dot_notation; -- returns = NULL

I think the = latest patch is still wrong. Ultimately, dot notation =E2=80=9C.a", = index =E2=80=9C[0] and slice "[1:4]=E2=80=9D rely on jsonpath, and = subscript [=E2=80=9Ca=E2=80=9D] relies on the rest logic in = jsonb_subscript_transform() in = =E2=80=9Cforeach=E2=80=9D.

Now = =E2=80=9Cjsonb_check_jsonpath_needed()=E2=80=9D checks only dot nation = and slice, but not checking index case. So that reason why your case = =E2=80=9Cselect (jb)[0].a=E2=80=9D works is because the second = indirection is a dot nation. However, =E2=80=9Cselect = (jb)[0][=E2=80=98a=E2=80=99]=E2=80=9D will not work. See my = test:

```
evantest=3D# select = ('{"name": "Alice", "age": 30}'::jsonb)[0].name;
  = name
---------
 "Alice"
(1 = row)

evantest=3D# select ('{"name": "Alice", = "age": = 30}'::jsonb)[0]['name'];
 jsonb
-------

(1 row)
```

In = my test, (jsonb)[0][=E2=80=99name=E2=80=99] should also return = =E2=80=9CAlice=E2=80=9D.

So, end up, = =E2=80=9Cjsonb_check_jsonpath_needed()=E2=80=9D only does incomplete and = inaccurate checks, only jsonb_subscript_make_jsonpath() can make an = accurate decision and return a null jsonpath upon subscript = =E2=80=9C[=E2=80=98a=E2=80=99]=E2=80=9D.

As a = result, =E2=80=9Cjson_check_jsonpath_needed()=E2=80=9D feels not needed = at all. In jsonb_subscript_transform(), just go ahead to run = jsonb_subscript_make_jsonpath() first, if returned jsonpath is NULL, = then run rest of logic.

With my dirty change of = removing = json_check_jsonpath_needed:
```
chaol@ChaodeMacBo= ok-Air postgresql % git diff
diff --git = a/src/backend/utils/adt/jsonbsubs.c = b/src/backend/utils/adt/jsonbsubs.c
index = 374040b3b4e..d9faab5c799 100644
--- = a/src/backend/utils/adt/jsonbsubs.c
+++ = b/src/backend/utils/adt/jsonbsubs.c
@@ -416,12 +416,12 @@ = jsonb_subscript_transform(SubscriptingRef *sbsref,
  =       sbsref->refrestype =3D = JSONBOID;
        sbsref->reftypmod =3D = -1;

-       if = (jsonb_check_jsonpath_needed(*indirection, isSlice))
-   =     {
+       //if = (jsonb_check_jsonpath_needed(*indirection, isSlice))
+   =     //{
            =     jsonb_subscript_make_jsonpath(pstate, indirection, = sbsref);
              =   if (sbsref->refjsonbpath)
      =                   = return;
-       }
+     =   //}

        = /*
         * We reach here only in = two cases: (a) the JSON simplified accessor = is
```

You can = see:
```
evantest=3D# select ('{"name": = "Alice", "age": 30}'::jsonb)[0]['name'];
  = jsonb
---------
 "Alice"
(1 = row)

evantest=3D# select ('{"name": "Alice", = "age": 30}'::jsonb)[0].name;
  = name
---------
 "Alice"
(1 = row)
```

Then I found =E2=80=9Cma= ke check=E2=80=9D failed. For example the first broken test = case:

```
@@ -4998,7 +4998,7 = @@
 select ('123'::jsonb)[0];
  = jsonb
 -------
-
+ = 123
 (1 = row)
```

The test expected an = empty result, which implies =E2=80=9Cstrict=E2=80=9D = mode.

But the problem is, which mode should be = the default? JSON_QUERY() uses =E2=80=9Clax=E2=80=9D as default mode. = And from Peter Eisentraut=E2=80=99s blog: https://peter.eisentraut.org/blog/2023/04/04/sql-2023-i= s-finished-here-is-whats-new

```
The semantics of this are defined in = terms of JSON_QUERY and JSON_VALUE constructions (which have been available since = SQL:2016), so this really just syntactic = sugar.
```

Also feels like = =E2=80=9Clax=E2=80=9D should be the default mode. If that is true, then = my dirty change of removing =E2=80=9Cjson_check_jsonpath_need()=E2=80=9D = works properly.

The current logic with this = patch sounds strange. Because =E2=80=9Cjson_check_jsonpath_need()=E2=80=9D= iterate through unprocessed indirections to decide if jsonpath is = needed (lax mode). With this logic:

1) if index = [0] directly following dot notation, like (data).a[0], it=E2=80=99s lax = mode
2) if index [0] directly following subscript [=E2=80=98a=E2= =80=99], like (data)[=E2=80=98a=E2=80=99][0], it=E2=80=99s strict = mode
3) if index [0] directly following the data column, then = if there is a dot nation in indirection list, use lax mode, otherwise = strict mode. For the failed test case, as there is no more indirection = following [0], so it expected strict mode.

I = wonder where this behavior is defined?

With my = change, 1) and 2) are the same, for 3), if index [0] directly following = the data column, regardless what indirections are followed, it=E2=80=99s = by default lax mode.

So, I think this is a = design decision. Maybe I missed something from your previous design, but = I don=E2=80=99t find anything about that from the commit comments. I = feel this would be better aligned with 1) and = 2).

Best regards,
--
Chao Li (Evan)
HighGo Software = Co., Ltd.
https://www.highgo.com/




= --Apple-Mail=_3843D48C-954D-483D-8903-4D9700BDE3E8--