maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi Alexey,

The standard says

"An ordinality column provides a sequential numbering of rows. Row numbering is
1-based."

Consider this example from the standard:

https://gist.github.com/spetrunia/d4d8564a3ed26148ae92035b24e1f294

SELECT 
  jt.rowseq, jt.name, jt.zip
FROM 
  bookclub,
  JSON_TABLE(bookclub.jcol, "lax $"
             COLUMNS ( rowSeq FOR ORDINALITY,
                      name VARCHAR(30) PATH 'lax $.Name',
                      zip CHAR(5) PATH 'lax $.address.postalCode')
            ) AS jt

+--------+---------+------+
| rowseq | name    | zip  |
+--------+---------+------+
|      0 | John Sm | 1    |
|      1 | Peter W | 9    |
|      2 | James L | NULL |
+--------+---------+------+

The numbering is 0-based, instead of 1-based.

What is not entirely clear for me is when the numbering should be reset.
For the above example, the SQL standard shows the resultset with rowseq 1-2-3
which hints that the numbering is "global".

MySQL-8 produces "1-1-1", which looks like they reset the numbering for every
scan of the JSON_TABLE output.

What are you thoughts on this?

If the numbering should be "global", should it be query-level global, or
subselect-level global?

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog