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

json_extract returns NULL with numbers in scientific notation

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.13, 10.2(EOL), 10.3(EOL)
    • 10.3.6
    • JSON
    • None
    • tested with MariaDB 10.2.13 on Debian 8
    • 10.3.6-1

    Description

      json_extract returns NULL for a number in scientific notation.

      MariaDB [(none)]> select json_extract('{"test":8.437e-5}','$.test'); 
      +--------------------------------------------+
      		 
      | json_extract('{"test":8.437e-5}','$.test') |
      		 
      +--------------------------------------------+
      		 
      | NULL                                       |
      		 
      +--------------------------------------------+

      with the following warning

      MariaDB [(none)]> show warnings;
      		 
      +---------+------+----------------------------------------------------------------------------------+
      		 
      | Level   | Code | Message                                                                          |
      		 
      +---------+------+----------------------------------------------------------------------------------+
      		 
      | Warning | 4038 | Syntax error in JSON text in argument 1 to function 'json_extract' at position 8 |
      		 
      +---------+------+----------------------------------------------------------------------------------+

      json value works.

      +------------------------------------------+
      		 
      | json_value('{"test":8.437e-5}','$.test') |
      		 
      +------------------------------------------+
      		 
      | 8.437e-5                                 |
      		 
      +------------------------------------------+
      		 
      1 row in set (0.00 sec)

      json_valid returns true for the given document

      MariaDB [(none)]> select json_valid('{"test":8.437e-5}');
      		 
      +---------------------------------+
      		 
      | json_valid('{"test":8.437e-5}') |
      		 
      +---------------------------------+
      		 
      |                               1 |
      		 
      +---------------------------------+

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          Richard Richard Stracke created issue -
          Richard Richard Stracke made changes -
          Field Original Value New Value
          Description json_extract returns NULL for a number in scientific notation.

          {code:java}
          MariaDB [(none)]> select json_extract('{"test":8.437e-5}','$.test');
          +--------------------------------------------+
          | json_extract('{"test":8.437e-5}','$.test') |
          +--------------------------------------------+
          | NULL |
          +--------------------------------------------+
          {code}
           


          json value works.

          {code:java}
          +------------------------------------------+
          | json_value('{"test":8.437e-5}','$.test') |
          +------------------------------------------+
          | 8.437e-5 |
          +------------------------------------------+
          1 row in set (0.00 sec)

          {code}


          json_valid returns true for the given document


          {code:java}
          MariaDB [(none)]> select json_valid('{"test":8.437e-5}');
          +---------------------------------+
          | json_valid('{"test":8.437e-5}') |
          +---------------------------------+
          | 1 |
          +---------------------------------+
          {code}
          		json_extract returns NULL for a number in scientific notation.

          {code:java}
          MariaDB [(none)]> select json_extract('{"test":8.437e-5}','$.test');
          +--------------------------------------------+
          | json_extract('{"test":8.437e-5}','$.test') |
          +--------------------------------------------+
          | NULL |
          +--------------------------------------------+
          {code}
           
          with the following warning


          {code:java}
          MariaDB [(none)]> show warnings;
          +---------+------+----------------------------------------------------------------------------------+
          | Level | Code | Message |
          +---------+------+----------------------------------------------------------------------------------+
          | Warning | 4038 | Syntax error in JSON text in argument 1 to function 'json_extract' at position 8 |
          +---------+------+----------------------------------------------------------------------------------+

          {code}



          json value works.

          {code:java}
          +------------------------------------------+
          | json_value('{"test":8.437e-5}','$.test') |
          +------------------------------------------+
          | 8.437e-5 |
          +------------------------------------------+
          1 row in set (0.00 sec)

          {code}


          json_valid returns true for the given document


          {code:java}
          MariaDB [(none)]> select json_valid('{"test":8.437e-5}');
          +---------------------------------+
          | json_valid('{"test":8.437e-5}') |
          +---------------------------------+
          | 1 |
          +---------------------------------+
          {code}
          elenst Elena Stepanova made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          elenst Elena Stepanova added a comment -

          MySQL works:

          MySQL [test]> select json_extract('{"test":8.437e-5}','$.test');
          		 
          +--------------------------------------------+
          		 
          | json_extract('{"test":8.437e-5}','$.test') |
          		 
          +--------------------------------------------+
          		 
          | 0.00008437                                 |
          		 
          +--------------------------------------------+
          		 
          1 row in set (0.00 sec)

          elenst Elena Stepanova added a comment - MySQL works: MySQL [test]> select json_extract( '{"test":8.437e-5}' , '$.test' ); + --------------------------------------------+ | json_extract( '{"test":8.437e-5}' , '$.test' ) | + --------------------------------------------+ | 0.00008437 | + --------------------------------------------+ 1 row in set (0.00 sec)
          elenst Elena Stepanova made changes -
          Fix Version/s 10.2 [ 14601 ]
          Fix Version/s 10.3 [ 22126 ]
          Affects Version/s 10.2 [ 14601 ]
          Affects Version/s 10.3 [ 22126 ]
          Assignee Alexey Botchkov [ holyfoot ]
          holyfoot Alexey Botchkov made changes -
          Sprint 10.3.6-0 [ 237 ]
          holyfoot Alexey Botchkov made changes -
          Status Confirmed [ 10101 ] In Progress [ 3 ]
          holyfoot Alexey Botchkov added a comment -

          http://lists.askmonty.org/pipermail/commits/2018-March/012131.html

          holyfoot Alexey Botchkov added a comment - http://lists.askmonty.org/pipermail/commits/2018-March/012131.html
          holyfoot Alexey Botchkov made changes -
          issue.field.resolutiondate 2018-03-24 21:04:01.0 2018-03-24 21:04:01.724
          holyfoot Alexey Botchkov made changes -
          Fix Version/s 10.3.6 [ 23003 ]
          Fix Version/s 10.2 [ 14601 ]
          Fix Version/s 10.3 [ 22126 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          tlamr Tomas Lamr added a comment -

          Hi guys! Thank you for your hard work, it seems that json_extract is unable to extract 0 as scientific notation (i.e. 0E-10), described it MDEV-17850

          tlamr Tomas Lamr added a comment - Hi guys! Thank you for your hard work, it seems that json_extract is unable to extract 0 as scientific notation (i.e. 0E-10), described it MDEV-17850
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 86020 ] MariaDB v4 [ 153953 ]
          mariadb-jira-automation Jira Automation (IT) made changes -
          Zendesk Related Tickets 127099

          People

            holyfoot Alexey Botchkov
            Richard Richard Stracke
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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