MIME-Version: 1.0
References: <202401181146.fuoeskfzriq7@alvherre.pgsql>
 <202401181711.qxjxpnl3ohnw@alvherre.pgsql>
 <CA+HiwqEZP-ybbvTZ_2gKJD2p60hvDkL9-UsoKrzirZkwdV60Ng@mail.gmail.com>
 <CACJufxFTRw_o-Fey-Km1uavuUAZagXFgQLf8DsMoT_qU4AU8hw@mail.gmail.com>
 <CA+HiwqGsYmyHy8EZA42JoW3ywGRFDGYQWz6X6-c_MG9y9+AkEA@mail.gmail.com>
In-Reply-To: 
 <CA+HiwqGsYmyHy8EZA42JoW3ywGRFDGYQWz6X6-c_MG9y9+AkEA@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Mon, 22 Jan 2024 23:46:15 +0800
Message-ID: 
 <CACJufxEe4nXHAfHJXa8UgHy5qZ35H+eomVE0tqOE_Euk=aE7rQ@mail.gmail.com>
Subject: Re: remaining sql/json patches
To: Amit Langote <amitlangote09@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>,
 Andres Freund <andres@anarazel.de>,
	Erik Rijkers <er@xs4all.nl>, Andrew Dunstan <andrew@dunslane.net>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CACJufxEe4nXHAfHJXa8UgHy5qZ35H%2BeomVE0tqOE_Euk%3DaE7rQ%40mail.gmail.com>
Precedence: bulk

On Mon, Jan 22, 2024 at 10:28=E2=80=AFPM Amit Langote <amitlangote09@gmail.=
com> wrote:
>
> > based on v35.
> > Now I only applied from 0001 to 0007.
> > For {DEFAULT expression  ON EMPTY}  | {DEFAULT expression ON ERROR}
> > restrict DEFAULT expression be either Const node or FuncExpr node.
> > so these 3 SQL/JSON functions can be used in the btree expression index=
.
>
> I'm not really excited about adding these restrictions into the
> transformJsonFuncExpr() path.  Index or any other code that wants to
> put restrictions already have those in place, no need to add them
> here.  Moreover, by adding these restrictions, we might end up
> preventing users from doing useful things with this like specify
> column references.  If there are semantic issues with allowing that,
> we should discuss them.
>

after applying v36.
The following index creation and query operation works. I am not 100%
sure about these cases.
just want confirmation, sorry for bothering you....

drop table t;
create table t(a jsonb, b  int);
insert into t select '{"hello":11}',1;
insert into t select '{"hello":12}',2;
CREATE INDEX t_idx2 ON t (JSON_query(a, '$.hello1' RETURNING int
default b + random() on error));
CREATE INDEX t_idx3 ON t (JSON_query(a, '$.hello1' RETURNING int
default random()::int on error));
SELECT JSON_query(a, '$.hello1'  RETURNING int default ret_setint() on
error) from t;
SELECT JSON_query(a, '$.hello1'  RETURNING int default sum(b) over()
on error) from t;
SELECT JSON_query(a, '$.hello1'  RETURNING int default sum(b) on
error) from t group by a;

but the following cases will fail related to index and default expression.
create table zz(a int, b int);
CREATE INDEX zz_idx1 ON zz ( (b + random()::int));
create table ssss(a int, b int default ret_setint());
create table ssss(a int, b int default sum(b) over());