Re: SQL/JSON: FOR ORDINALITY bug

On 2022-05-03 Tu 20:39, David G. Johnston wrote:
> On Tue, May 3, 2022 at 5:27 PM Andrew Dunstan <andrew(at)dunslane(dot)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
> 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