Re: SQL/JSON: FOR ORDINALITY bug

On 2022-05-04 We 16:09, Erik Rijkers 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.

Oracle also gives 10 rows for that query according to my testing, so I
suspect either DB2 and/or its docs are wrong.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com