Re: remaining sql/json patches

On Mon, Jan 22, 2024 at 10:28 PM Amit Langote <amitlangote09(at)gmail(dot)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());