Re: SQL/JSON: FOR ORDINALITY bug - Mailing list pgsql-hackers

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. 


Here's what the standard says in section 7.11 in I think the relevant
bit of mindbogglingly impenetrable prose:


General Rules
1)
If a <table primary> simply contains a <JSON table primitive> JTP, then:
a) If the value of the <JSON context item> simply contained in the <JSON
API common syntax> is the null value, then the result of <JSON table
primitive> is an empty table and no further General Rules of this
Subclause are applied.
b) Let JACS be the <JSON API common syntax> simply contained in JTP.
c) Let JTEB be the <JSON table error behavior> simply contained in JTP.
d) The General Rules of Subclause 10.14, “<JSON API common syntax>”, are
applied with JACS as JSON API COMMON SYNTAX; let ROWST be the STATUS and
let ROWSEQ be the SQL/JSON SEQUENCE returned from the application of
those General Rules.
460
Foundation (SQL/Foundation)
e) Case:
i) If ROWST is an exception condition, then
Case:
1) If JTEB is ERROR, then the exception condition ROWST is raised.
2) Otherwise, the result of JTP is an empty table.
ii) Otherwise, let NI be the number of SQL/JSON items in ROWSEQ, let Ij,
1 (one) ≤ j ≤ NI, be those SQL/JSON items in order, let NCD be the
number of <JSON table primitive column definition>s contained in JTP,
and let JTCDi, 1 (one) ≤ i ≤ NCD, be those <JSON table primitive column
definition>s.
For all j, 1 (one) ≤ j ≤ NI, and for all i, 1 (one) ≤ i ≤ NCD, the value
of the i-th column of the j-th row in the result of JTP is determined as
follows:
Case:
1) If JTCDi is a <JSON table ordinality column definition>, then the
value of the i-th column
of the j-th row is j.


Maybe some language lawyer can turn that into comprehensible English.

This should probably be an open item for release 15, but I don't really
know what the precise behaviour should be, so it's hard to modify it.

If we can't get it right maybe we should disable the "WITH ORDINALITY"
clause, although that would be a pity.


cheers


andrew


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