Re: SQL/JSON: FOR ORDINALITY bug

[Erik Rijkers]

Op 04-05-2022 om 13:55 schreef Andrew Dunstan:

On 2022-05-03 Tu 20:39, David G. Johnston wrote:

On Tue, May 3, 2022 at 5:27 PM Andrew Dunstan <and...@dunslane.net> wrote:


     On 2022-05-03 Tu 11:19, Erik Rijkers wrote:
     > Hi
     >
     > I've copied some statements from the .pdf called:
     > "TECHNICAL REPORT ISO/IEC TR 19075-6   First edition 2017-03
     > Part SQL Notation support 6: (JSON) for JavaScript Object"
     > (not available anymore although there should be a similar
     replacement
     > file)
     >
     > In that pdf I found the data and statement (called 'table 15' in the
     > .pdf) as in the attached bash file.  But the result is different: as
     > implemented by 15devel, the column rowseq is always 1.  It seems
     to me
     > that that is wrong; it should count 1, 2, 3 as indeed the
     > example-result column in that pdf shows.
     >
     > What do you think?
     >
     >

     Possibly.


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')





Erik

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.

json_table is given a single jsonb record via the lateral reference to
bookclub.jcol.  It produces one result, having a rowseq 1.  It does
this for all three outer lateral reference tuples and thus produces
three output rows each with one match numbered rowseq 1.

I imagine we could overcome that by stashing the sequence counter
somewhere it would survive across calls. The question really is what is
the right thing to do? I'm also a bit worried about how correct is
ordinal numbering with nested paths, e.g. (from the regression tests):


select
     jt.*
from
     jsonb_table_test jtt,
     json_table (
         jtt.js,'strict $[*]' as p
         columns (
             n for ordinality,
             a int path 'lax $.a' default -1 on empty,
             nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
             nested path 'strict $.c[*]' as pc columns ( c int path '$' )
         )
     ) jt;
  n | a  | b | c
---+----+---+----
  1 |  1 |   |
  2 |  2 | 1 |
  2 |  2 | 2 |
  2 |  2 | 3 |
  2 |  2 |   | 10
  2 |  2 |   |
  2 |  2 |   | 20
  3 |  3 | 1 |
  3 |  3 | 2 |
  4 | -1 | 1 |
  4 | -1 | 2 |


cheers


andrew

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

json_ordinality_db2.sh
Description: application/shellscript