Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22296

JSON_TABLE: Ordinality is calculated incorrectly (counts all rows instead of rows at its level)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: N/A
    • Fix Version/s: N/A
    • Component/s: JSON
    • Labels:
      None

      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)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              holyfoot Alexey Botchkov
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: