Re: remaining sql/json patches

On Tue, Mar 26, 2024 at 6:16 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> On Fri, Mar 22, 2024 at 12:08 AM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> >
> > On Wed, Mar 20, 2024 at 9:53 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > > I'll push 0001 tomorrow.
> >
> > Pushed that one. Here's the remaining JSON_TABLE() patch.
> >

hi.
I don't fully understand all the code in json_table patch.
maybe we can split it into several patches, like:
* no nested json_table_column.
* nested json_table_column, with PLAN DEFAULT
* nested json_table_column, with PLAN ( json_table_plan )

i can understand the "no nested json_table_column" part,
which seems to be how oracle[1] implemented it.
I think we can make the "no nested json_table_column" part into v17.
i am not sure about other complex parts.
lack of comment, makes it kind of hard to fully understand.

[1] https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/img_text/json_table.html

+/* Reset context item of a scan, execute JSON path and reset a scan */
+static void
+JsonTableResetContextItem(JsonTableScanState *scan, Datum item)
+{
+ MemoryContext oldcxt;
+ JsonPathExecResult res;
+ Jsonb *js = (Jsonb *) DatumGetJsonbP(item);
+
+ JsonValueListClear(&scan->found);
+
+ MemoryContextResetOnly(scan->mcxt);
+
+ oldcxt = MemoryContextSwitchTo(scan->mcxt);
+
+ res = executeJsonPath(scan->path, scan->args,
+ GetJsonPathVar, CountJsonPathVars,
+ js, scan->errorOnError, &scan->found,
+ false /* FIXME */ );
+
+ MemoryContextSwitchTo(oldcxt);
+
+ if (jperIsError(res))
+ {
+ Assert(!scan->errorOnError);
+ JsonValueListClear(&scan->found); /* EMPTY ON ERROR case */
+ }
+
+ JsonTableRescan(scan);
+}

"FIXME".
set the last argument in executeJsonPath to true also works as expected.
also there is no test related to the "FIXME"
i am not 100% sure about the "FIXME".

see demo (after set the executeJsonPath's "useTz" argument to true).

create table ss(js jsonb);
INSERT into ss select '{"a": "2018-02-21 12:34:56 +10"}';
INSERT into ss select '{"b": "2018-02-21 12:34:56 "}';
PREPARE q2 as SELECT jt.* FROM ss, JSON_TABLE(js, '$.a.datetime()'
COLUMNS ("int7" timestamptz PATH '$')) jt;
PREPARE qb as SELECT jt.* FROM ss, JSON_TABLE(js, '$.b.datetime()'
COLUMNS ("tstz" timestamptz PATH '$')) jt;
PREPARE q3 as SELECT jt.* FROM ss, JSON_TABLE(js, '$.a.datetime()'
COLUMNS ("ts" timestamp PATH '$')) jt;

begin;
set time zone +10;
EXECUTE q2;
set time zone -10;
EXECUTE q2;
rollback;

begin;
set time zone +10;
SELECT JSON_VALUE(js, '$.a' returning timestamptz) from ss;
set time zone -10;
SELECT JSON_VALUE(js, '$.a' returning timestamptz) from ss;
rollback;
---------------------------------------------------------------------
begin;
set time zone +10;
EXECUTE qb;
set time zone -10;
EXECUTE qb;
rollback;

begin;
set time zone +10;
SELECT JSON_VALUE(js, '$.b' returning timestamptz) from ss;
set time zone -10;
SELECT JSON_VALUE(js, '$.b' returning timestamptz) from ss;
rollback;
---------------------------------------------------------------------
begin;
set time zone +10;
EXECUTE q3;
set time zone -10;
EXECUTE q3;
rollback;

begin;
set time zone +10;
SELECT JSON_VALUE(js, '$.b' returning timestamp) from ss;
set time zone -10;
SELECT JSON_VALUE(js, '$.b' returning timestamp) from ss;
rollback;