[MDEV-22296] JSON_TABLE: Ordinality is calculated incorrectly (counts all rows instead of rows at its level) Created: 2020-04-18  Updated: 2020-04-25  Resolved: 2020-04-25

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-17399 Add support for JSON_TABLE Closed

 Description   

Basic example:

drop table if exists t;
 
create table t (id int, j text);
 
insert into t values (1,'{"a":"b"}'),(2,'{"c":"d"}');
 
select * from t, json_table(t.j,'$' columns (ord for ordinality)) as jt;

Aside ordinality values starting from a wrong value in MariaDB, note them growing in MariaDB and staying the same in MySQL:

bb-10.5-hf 6dfb3fab2

 
+------+-----------+------+
 
| id   | j         | ord  |
 
+------+-----------+------+
 
|    1 | {"a":"b"} |    0 |
 
|    2 | {"c":"d"} |    1 |
 
+------+-----------+------+
 
2 rows in set (0.001 sec)

MySQL 8.0.19

 
MySQL [test]> select * from t, json_table(t.j,'$' columns (ord for ordinality)) as jt;
 
+------+-----------+------+
 
| id   | j         | ord  |
 
+------+-----------+------+
 
|    1 | {"a":"b"} |    1 |
 
|    2 | {"c":"d"} |    1 |
 
+------+-----------+------+
 
2 rows in set (0.002 sec)

Here is a more meaningful example taken from stackoverflow, with minimal changes to make it applicable to MySQL/MariaDB syntax. See the discussion there.

DROP TABLE IF EXISTS test1;
 
CREATE TABLE test1 (json_data TEXT);
 
INSERT INTO test1 VALUES ('[
 
  {
 
    "debitOverturn": "939.34",
 
    "table": [
 
      {
 
        "debit": "",
 
        "credit": "939.34"
 
      },
 
      {
 
        "debit": "939.34",
 
        "credit": ""
 
      }
 
    ]
 
  },
 
  {
 
    "debitOverturn": "939.34",
 
    "table": [
 
      {
 
        "debit": "",
 
        "credit": "939.34"
 
      },
 
      {
 
        "debit": "939.34",
 
        "credit": ""
 
      }
 
    ]
 
  }
 
]');
 
 
 
SELECT jt.u_lvl,
 
       jt.debitOverturn,
 
       jt.l_lvl,
 
       jt.debit,
 
       jt.credit
 
 FROM test1 s,
 
     JSON_TABLE(s.json_data,'$[*]' COLUMNS(
 
        u_lvl FOR ORDINALITY,
 
        debitOverturn VARCHAR(20) PATH '$.debitOverturn',
 
        NESTED PATH '$.table[*]'
 
            COLUMNS (
 
                l_lvl FOR ORDINALITY,
 
                debit VARCHAR(38) PATH '$.debit',
 
                credit VARCHAR(38) PATH '$.credit'))) AS jt
 
;

Note u_lvl and l_lvl columns:

bb-10.5-hf 6dfb3fab2

 
+-------+---------------+-------+--------+--------+
 
| u_lvl | debitOverturn | l_lvl | debit  | credit |
 
+-------+---------------+-------+--------+--------+
 
|     0 | 939.3         |     0 |        | 939.34 |
 
|     1 | 939.3         |     1 | 939.34 |        |
 
|     2 | 939.3         |     2 |        | 939.34 |
 
|     3 | 939.3         |     3 | 939.34 |        |
 
+-------+---------------+-------+--------+--------+
 
4 rows in set (0.002 sec)

Result on MySQL 8.0 matches the expected result from stackoverflow, where the expectation was confirmed to be correct:

MySQL 8.0.19

 
+-------+---------------+-------+--------+--------+
 
| u_lvl | debitOverturn | l_lvl | debit  | credit |
 
+-------+---------------+-------+--------+--------+
 
|     1 | 939.34        |     1 |        | 939.34 |
 
|     1 | 939.34        |     2 | 939.34 |        |
 
|     2 | 939.34        |     1 |        | 939.34 |
 
|     2 | 939.34        |     2 | 939.34 |        |
 
+-------+---------------+-------+--------+--------+
 
4 rows in set (0.002 sec)



 Comments   
Comment by Alexey Botchkov [ 2020-04-25 ]

Dunno. The standard doesn't say much how this all is supposed to work. I decided to do it in simplest possible way.
Though i changed it to behave in Oracle's way.
Now it produces results like this:

MariaDB [test]> select * from t1, JSON_TABLE(t1.json, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, b INT PATH '$'))) as jt;
 
+------+-----------------------------------------------------------------------------+-------+------+---------+------+
 
| id   | json                                                                        | js_id | a    | l_js_id | b    |
 
+------+-----------------------------------------------------------------------------+-------+------+---------+------+
 
| j1   | [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]                          |     1 |    1 |       1 |   11 |
 
| j1   | [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]                          |     1 |    1 |       2 |  111 |
 
| j1   | [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]                          |     2 |    2 |       1 |   22 |
 
| j1   | [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]                          |     2 |    2 |       2 |  222 |
 
| j2   | [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] |     1 |    3 |       1 |   11 |
 
| j2   | [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] |     1 |    3 |       2 |  111 |
 
| j2   | [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] |     2 |    4 |       1 |   22 |
 
| j2   | [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] |     2 |    4 |       2 |  222 |
 
| j2   | [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] |     3 |    5 |       1 |   22 |
 
| j2   | [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] |     3 |    5 |       2 |  222 |
 
+------+-----------------------------------------------------------------------------+-------+------+---------+------+

Generated at Thu Feb 08 09:13:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.