[MDEV-17850] json_extract returns null is any (even nested) data contains 0 written in scientific notation (0E-10) Created: 2018-11-27  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.3.11, 10.2, 10.3
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Tomas Lamr Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Hi, the scientific notation handling was partially fixed in MDEV-15561

But this still fails

MariaDB [(none)]> select json_extract('{"test":0e-10}','$.test') from dual;
 
+-----------------------------------------+
 
| json_extract('{"test":0e-10}','$.test') |
 
+-----------------------------------------+
 
| NULL                                    |
 
+-----------------------------------------+
 
1 row in set, 1 warning (0.000 sec)
 
 
 
MariaDB [(none)]> select json_extract('{"test":0E-10}','$.test') from dual;
 
+-----------------------------------------+
 
| json_extract('{"test":0E-10}','$.test') |
 
+-----------------------------------------+
 
| NULL                                    |
 
+-----------------------------------------+
 
1 row in set, 1 warning (0.000 sec)
 
 
 
MariaDB [(none)]> show warnings;
 
+---------+------+-----------------------------------------------------------------------------------+
 
| Level   | Code | Message                                                                           |
 
+---------+------+-----------------------------------------------------------------------------------+
 
| Warning | 4038 | Syntax error in JSON text in argument 1 to function 'json_extract' at position 10 |
 
+---------+------+-----------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)

although this works

 
 
MariaDB [(none)]> select json_extract('{"test":1E-10}','$.test') from dual;
 
+-----------------------------------------+
 
| json_extract('{"test":1E-10}','$.test') |
 
+-----------------------------------------+
 
| 1E-10                                   |
 
+-----------------------------------------+
 
1 row in set (0.000 sec)



 Comments   
Comment by Tomas Lamr [ 2018-11-27 ]

I know that the zero encoded in scientific notation seems to be strange, but it is just how one of the most used json library works by default.
When you use https://github.com/FasterXML/jackson and serialize java's BigDecimal that have set scale but it's value will be 0, it will by default serialize it this way.

This is what Oracle 18c returns

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
 
Version 18.3.0.0.0
 
 
 
SQL> select json_value('{"test":0E-10}','$.test') from dual;
 
 
 
JSON_VALUE('{"TEST":0E-10}','$.TEST')
 
--------------------------------------------------------------------------------
 
0

Comment by Tomas Lamr [ 2018-11-27 ]

ah, it just seems to be json_extract specific, json_value work also on mariadb

MariaDB [(none)]> select JSON_VALUE('{"TEST":0E-10}','$.TEST') from dual;
 
+---------------------------------------+
 
| JSON_VALUE('{"TEST":0E-10}','$.TEST') |
 
+---------------------------------------+
 
| 0                                     |
 
+---------------------------------------+
 
1 row in set (0.004 sec)
 
 
 
MariaDB [(none)]> select JSON_EXTRACT('{"TEST":0E-10}','$.TEST') from dual;
 
+-----------------------------------------+
 
| JSON_EXTRACT('{"TEST":0E-10}','$.TEST') |
 
+-----------------------------------------+
 
| NULL                                    |
 
+-----------------------------------------+
 
1 row in set, 1 warning (0.000 sec)

Comment by Alice Sherepa [ 2018-11-27 ]

repeatable on 10.2,10.3

MariaDB [test]> select json_value('{"test":0e-10}','$.test') from dual;
 
+---------------------------------------+
 
| json_value('{"test":0e-10}','$.test') |
 
+---------------------------------------+
 
| 0                                     |
 
+---------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select json_extract('{"test":0e-10}','$.test') from dual;
 
+-----------------------------------------+
 
| json_extract('{"test":0e-10}','$.test') |
 
+-----------------------------------------+
 
| NULL                                    |
 
+-----------------------------------------+
 
1 row in set, 1 warning (0.000 sec)
 
 
 
Warning (Code 4038): Syntax error in JSON text in argument 1 to function 'json_extract' at position 10

MySQL 8.0.12

mysql> select json_extract('{"test":0e-10}','$.test') from dual;
 
+-----------------------------------------+
 
| json_extract('{"test":0e-10}','$.test') |
 
+-----------------------------------------+
 
| 0.0                                     |
 
+-----------------------------------------+
 
1 row in set (0.00 sec)

Comment by Tomas Lamr [ 2018-11-27 ]

Thanks a lot for such quick feedback!

Generated at Thu Feb 08 08:39:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.