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

JSON handing of DECIMAL is odd

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.6.1
    • Fix Version/s: 10.6
    • Component/s: JSON
    • Labels:
      None
    • Environment:
      Linux CentOS 7.7

      Description

      When a DECIMAL value is passed as an argument to create a JSON object, it is handled differently in the generated JSON from, say, a DOUBLE, despite DECIMAL not being a datatype in JSON and is treated like DOUBLE in other cases. In particular, trailing zeros are retained.

      CREATE TABLE tab1(c1 INTEGER NOT NULL PRIMARY KEY, c1 DECIMAL(10,5) NOT NULL);
      INSERT INTO tab1 VALUES(1, 10);
      INSERT INTO tab1 VALUES(1, 10.8);
      SELECT JSON_OBJECT('c1', c1, 'c2', c2) FROM tab1;
      +---------------------------------+
      | JSON_OBJECT('c1', c1, 'c2', c2) |
      +---------------------------------+
      | {"c1": 1, "c2": 10.00000}       |
      | {"c1": 2, "c2": 10.80000}       |
      +---------------------------------+
      

      Note that the trailing zeros are retained, which is meaningless in JSON, somehow the notion that DOUBLE is different from DECIMAL, which makes sense in SQL, is retained in JSON, where it really doesn't make sense. Also

      SELECT JSON_VALUE(JSON_OBJECT('c1', c1, 'c2', c2), '$.c2') value,
         JSON_TYPE(JSON_VALUE(JSON_OBJECT('c1', c1, 'c2', c2), '$.c2')) type FROM tab1;
      +----------+--------+
      | value    | type   |
      +----------+--------+
      | 10.00000 | DOUBLE |
      | 10.80000 | DOUBLE |
      +----------+--------+
      

        Attachments

          Activity

            People

            Assignee:
            rucha174 Rucha Deodhar
            Reporter:
            karlsson Anders Karlsson
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.