From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Erik Rijkers <er(at)xs4all(dot)nl> |
Cc: | Andrew Dunstan <andrew(at)dunslane(dot)net>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: SQL/JSON: FOR ORDINALITY bug |
Date: | 2022-05-04 20:43:00 |
Message-ID: | CAKFQuwbginZXWHiu5tLAHh77FMyFPRwSg+VVfcO-p0+9hONYZw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, May 4, 2022 at 1:09 PM Erik Rijkers <er(at)xs4all(dot)nl> wrote:
> Op 04-05-2022 om 21:12 schreef Andrew Dunstan:
> >
> >>>>
> >>>> I don't see how rowseq can be anything but 1. Each invocation of
> >>
> >>
> >> After some further experimentation, I now think you must be right,
> David.
> >>
> >> Also, looking at the DB2 docs:
> >> https://www.ibm.com/docs/en/i/7.2?topic=data-using-json-table
> >> (see especially under 'Handling nested information')
> >>
> >> There, I gathered some example data + statements where one is the case
> >> at hand. I also made them runnable under postgres (attached).
> >>
> >> I thought that was an instructive example, with those
> >> 'outer_ordinality' and 'inner_ordinality' columns.
> >>
> >>
> >
> > Yeah, I just reviewed the latest version of that page (7.5) and the
> > example seems fairly plain that we are doing the right thing, or if not
> > we're in pretty good company, so I guess this is probably a false alarm.
> > Looks like ordinality is for the number of the element produced by the
> > path expression. So a path of 'lax $' should just produce ordinality of
> > 1 in each case, while a path of 'lax $[*]' will produce increasing
> > ordinality for each element of the root array.
>
> Agreed.
>
> You've probably noticed then that on that same page under 'Sibling
> Nesting' is a statement that gives a 13-row resultset on DB2 whereas in
> 15devel that statement yields just 10 rows. I don't know which is correct.
>
>
There should be 12 results (minimum would be 8 - 5 of which are used for
real matches, plus 4 new row producing matches).
Our result seems internally inconsistent; conceptually there are two kinds
of nulls here and we cannot collapse them.
null-val: we are outputting the record from the nested path but there is no
actual value to output so we output null-val
null-union: we are not outputting the record for the nested path (we are
doing a different one) but we need to output something for this column so
we output null-union.
Sally, null-val, null-union
Sally, null-union, null-val
We only have one Sally but need both (11)
We are also missing:
Mary, null-union, null-val (12)
The fact that we agree on John means that we at least agree on UNION
meaning we output a pair of rows when there are two nested paths.
I point to relative comparisons for fear of reading the specification
here...
David J.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | David Zhang | 2022-05-04 21:38:54 | Re: postgres_fdw: commit remote (sub)transactions in parallel during pre-commit |
Previous Message | Erik Rijkers | 2022-05-04 20:09:51 | Re: SQL/JSON: FOR ORDINALITY bug |