On Wed, May 4, 2022 at 1:09 PM Erik Rijkers <e...@xs4all.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.