On Wed, May 4, 2022 at 1:09 PM Erik Rijkers <er@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')
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.
Thinking this over - I think the difference is we implemented a FULL OUTER JOIN to combine the siblings - including the behavior of that construct and the absence of rows. DB2 took the word "UNION" for the plan modifier literally and unioned (actually union all) the two subpaths together using the null concepts above (though somehow ensuring that at least one row was produced from each subpath...).
Thus we are indeed back to seeing whether the standard defines sibling combining as union or join, or some other special construct. I'm now leaning toward what we've done as at least being the more sane option.
Even if our outer join process is correct the existing wording is odd.
"Use FULL OUTER JOIN ON FALSE, so that both parent and child rows are included into the output, with NULL values inserted into both child and parent columns for all missing values."
I don't think it helps to mention parent here. This aspect of plan doesn't concern itself with the final output, only the output of the subplan which is then combined with the parent using a join. I would probably want to phrase the default more like:
"This is the default option for joining the combined child rows to the parent."
