MIME-Version: 1.0
References: 
 <CAN-LCVP7HXmGu-WcinsHvdKqMGEdv=1Y67H4U58F6Y=Q0M5GyQ@mail.gmail.com>
 <CA+HiwqGqz1+jvrWHHzYSnzuVZPp4v-vfEyX-hB=3hTP3Asf78Q@mail.gmail.com>
 <CAN-LCVNSSCTdMjSFHKMkPUDcxNiGX1qn=EWMmmph8dmhkOaj_g@mail.gmail.com>
 <CA+HiwqErd3+tMB5yOerBeNJ5HzYWcByYjn=kJpEcSvdU9OADkg@mail.gmail.com>
 <CAN-LCVOH0AKMXT83SxyP7KKaCPbt_KF_Uibbpmn5_tfFvcnbWA@mail.gmail.com>
 <CAN-LCVPSYfLZHcmvct-_nGZ7peTat-Bo--04K9VXGU8p_TcbWg@mail.gmail.com>
 <CAN-LCVMk6pBMBaghaq5h5awLyWje1iaDXUPJNpWhecFvwWonkw@mail.gmail.com>
 <CAN-LCVMUFri=hm=OFxOnw=oY+yR-WQudRy35R2ZBbCxGhcM_Aw@mail.gmail.com>
 <CAPpHfdvnacwjFGhqit7gE2RevJ8TEHM3bqZjMxb=fJ7P6ZNvKw@mail.gmail.com>
 <CAN-LCVMvrkaNM+pUN=KvoJ4_c6yCVaW5gNucQWWNtsAmMxzOCA@mail.gmail.com>
 <CAN-LCVPU_NTSD+DUguOH58h0X1K5jiVeWRWshFSHzNnHys1T5A@mail.gmail.com>
 <CAPpHfdu-1oDzhKHXRF0vVSqab5Q3V5Sx1iQ5+tOmL_+nqY5_tA@mail.gmail.com>
 <CAPpHfdtJB2APBjD7Npa58Cg4P0KqFjNKq_mT4tpNWN2y+5zKoA@mail.gmail.com>
 <CAN-LCVMpMMf1D7qT5+4sBShzPa_n97wm96zUME2-NKq0-naSTw@mail.gmail.com>
 <CAPpHfdtjXz=MekGoqmST4FWJXgHuvZ9bG8qoqRWUQU8emBhKBQ@mail.gmail.com>
 <CAA-aLv7aZGSExnbjJRw8eKkoXbu34TdoKLLA2gPye3aHjO5OSA@mail.gmail.com>
 <CAPpHfdt=LncQH9PAq9O8qO7KZcTT9rOsxLLanscRF7xDFvK8mA@mail.gmail.com>
In-Reply-To: 
 <CAPpHfdt=LncQH9PAq9O8qO7KZcTT9rOsxLLanscRF7xDFvK8mA@mail.gmail.com>
From: Thom Brown <thom@linux.com>
Date: Thu, 9 Jul 2026 21:28:37 +0100
Message-ID: 
 <CAA-aLv7uFpTK_AxCpMSiKSEZhNuS4YFEpUAOT5Y-C+pa=YnsCw@mail.gmail.com>
Subject: Re: SQL/JSON json_table plan clause
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Nikita Malakhov <hukutoc@gmail.com>,
 Amit Langote <amitlangote09@gmail.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAA-aLv7uFpTK_AxCpMSiKSEZhNuS4YFEpUAOT5Y-C%2Bpa%3DYnsCw%40mail.gmail.com>
Precedence: bulk

On Thu, 9 Jul 2026 at 20:52, Alexander Korotkov <aekorotkov@gmail.com> wrot=
e:
>
> Hi, Thom!
>
> On Thu, Jul 9, 2026 at 7:12=E2=80=AFPM Thom Brown <thom@linux.com> wrote:
> > On Wed, 8 Jul 2026 at 18:04, Alexander Korotkov <aekorotkov@gmail.com> =
wrote:
> > >
> > > On Wed, Jul 8, 2026 at 6:35=E2=80=AFPM Nikita Malakhov <hukutoc@gmail=
.com> wrote:
> > > > Alexander, thank you for your participation. I am very sorry, I've =
seen your previous
> > > > email but was busy with other tasks and didn't have time to work on=
 your notes.
> > > > Thank you very much!
> > >
> > > OK, no problem.  Are you good with the current shape of the patch?
> >
> > I've been giving this a test drive. I saw that this has been
> > committed, and re-tested against that, and the issues I identified
> > (unless I've misunderstood some functionality) seem to have survived.
>
> I tried my best to check if all the critics is addressed.  SQL/JSON patch=
es
> go through so many threads.  Sorry that I missed your valuable insights.

You didn't miss anything from me, so nothing to be sorry about. I just
meant I retested the issues I had found against the commit to see if
they had been fixed prior to submitting them. I hadn't raised them
prior to today.

> > First, doesn't this need a catversion bump? This adds fields to
> > existing node types.
>
> Thank you for noticing, catversion is bumped.
>
> > I only get the first column from the following. The second one
> > disappears without error:
> >
> > SELECT * FROM JSON_TABLE(
> >   jsonb '[{"x":[1],"y":[2]}]', '$[*]' AS p0
> >   COLUMNS (
> >     NESTED PATH '$.x[*]' AS json_table_path_0 COLUMNS (x int PATH '$'),
> >     NESTED PATH '$.y[*]' COLUMNS (y int PATH '$')
> >   )
> >   PLAN (p0 OUTER json_table_path_0)
> > ) jt;
> >  x
> > ---
> >  1
> > (1 row)
>
> Yes, there is a bug in assignment of generated names.  Fixed in 0003.
>
> > I have the following view:
> > CREATE VIEW v_chain AS
> > SELECT * FROM JSON_TABLE(
> >   jsonb '[{"x": [{"y": [1,2]}]}]', '$[*]' AS p0
> >   COLUMNS ( NESTED PATH '$.x[*]' AS p1 COLUMNS (
> >    NESTED PATH '$.y[*]' AS p11 COLUMNS ( y int PATH '$' ) ) )
> >   PLAN (p0 OUTER (p1 INNER p11))
> > ) jt;
> >
> > When I dump it, I get:
> >
> > CREATE VIEW public.v_chain AS
> >  SELECT y
> >    FROM JSON_TABLE(
> >         '[{"x": [{"y": [1, 2]}]}]'::jsonb, '$[*]' AS p0
> >         COLUMNS (
> >             NESTED PATH '$."x"[*]' AS p1
> >             COLUMNS (
> >                 NESTED PATH '$."y"[*]' AS p11
> >                 COLUMNS (
> >                     y integer PATH '$'
> >                 )
> >             )
> >         )
> >         PLAN (p0 OUTER p1 INNER p11)
> >     ) jt;
> >
> > The parentheses around "p1 INNER p11" aren't preserved.
>
> Yes, there is a bug in the deparsing.  0002 fixes that.
>
> > Another dump issue with the following:
> >
> > CREATE VIEW v_onempty AS SELECT * FROM JSON_TABLE(
> >   jsonb '{}', '$' AS p0
> >   COLUMNS ( a int PATH '$.nosuch' ERROR ON EMPTY )
> >   ERROR ON ERROR
> > ) jt;
> >
> > This dumps as:
> >
> > CREATE VIEW public.v_onempty AS
> >  SELECT a
> >    FROM JSON_TABLE(
> >             '{}'::jsonb, '$' AS p0
> >             COLUMNS (
> >                 a integer PATH '$."nosuch"'
> >             ) ERROR ON ERROR
> >         ) jt;
> >
> > It's lost "ERROR ON EMPTY".
> >
> >
> > Another example:
> >
> > SELECT * FROM JSON_TABLE(jsonb '"mystring"', '$'
> >   COLUMNS (a int PATH '$')
> >   ERROR ON ERROR
> > ) jt;
> >
> > This gives me:
> >
> > ERROR:  invalid input syntax for type integer: "mystring"
> >
> > But the documentation says that the table-level clause "does not
> > affect the errors that occur when evaluating columns". Am I missing
> > something here?
>
> I've rechecked with SQL 2023 standard.  AFAICS, ON ERROR clause should
> be propagated from table to column.  We don't propagate it, so that must =
be
> a bug since PG 17.  But this patch shouldn't try to change it, it's out o=
f scope.
> 0001 reverts attempt to change ON ERROR handling (this also fixes the cas=
e
> about of invalid dumpinged).  I think we need to consider this subject se=
parately.

Yeah, I haven't exercised JSON_TABLE enough since release, so I'm
clearly late in hitting this.

Thanks for the fixes. I'll try to get round to testing them tomorrow
unless someone beats me to it.

Thom