[MDEV-27151] JSON_VALUE() does not parse NULL properties properly Created: 2021-12-01  Updated: 2022-10-07  Resolved: 2022-08-11

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4, 10.8.3, 10.9.1
Fix Version/s: 10.3.37, 10.4.27, 10.5.18, 10.6.10, 10.7.6, 10.8.5, 10.9.3

Type: Bug Priority: Critical
Reporter: Assen Totin (Inactive) Assignee: Rucha Deodhar
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Duplicate
duplicates MDEV-19664 JSON_VALUE and null values Closed

 Description   

A bug exists in the JSON_VALUE() function while parsing a JSON property that has a value of NULL. The function produces a string value "null" while it should actually produce a NULL value. The JSON standard clearly separates "null" as a string value from a NULL value for an object property (see Example 3 below).

Example 1: Call the JSON_VALUE() function with a JSON where a property is set to NULL. Pay attention that the returned value of is lowercase; MariaDB CLI always prints the actual NULL values in uppercase in the result. 

MariaDB [xml]> SELECT JSON_VALUE('{"nulltest": null}', '$.nulltest');
 
+------------------------------------------------+
 
| JSON_VALUE('{"nulltest": null}', '$.nulltest') |
 
+------------------------------------------------+
 
| null                                           |
 
+------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [xml]> SELECT null;
 
+------+
 
| NULL |
 
+------+
 
| NULL |
 
+------+
 
1 row in set (0.000 sec)

Example 2: Alternative way to verify the bug is to use PL/SQL code. 

DELIMITER //
 
CREATE OR REPlACE PROCEDURE NULLTEST()
 
   IS
 
      nulltest   VARCHAR2(255);
 
   BEGIN
 
      SELECT JSON_VALUE('{"nulltest": null}', '$.nulltest') INTO nulltest;
 
 
 
      IF nulltest IS NULL THEN
 
         SELECT 'The value of nulltest was NULL' INTO @sess_nulltest;
 
      ELSE
 
         SELECT 'The value of nulltest was NOT NULL' INTO @sess_nulltest;
 
      END IF;
 
   END;
 
//
 
DELIMITER ;
 
 
 
MariaDB [xml]> CALL NULLTEST();
 
Query OK, 2 rows affected (0.000 sec)
 
 
 
MariaDB [xml]> SELECT @sess_nulltest;
 
+------------------------------------+
 
| @sess_nulltest                     |
 
+------------------------------------+
 
| The value of nulltest was NOT NULL |
 
+------------------------------------+
 
1 row in set (0.000 sec)

Example 3: Javascript code to illustrate the standard handling of NULL as a value of an object property through JSON serialisation(run under NodeJS):

 
 
var a = {
 
        nulltest: null
 
};
 
console.log('Object a:');
 
console.log(a);
 
console.log();
 
 
 
var b = JSON.stringify(a);
 
console.log('JSON string b:');
 
console.log(b);
 
console.log();
 
 
 
var c = JSON.parse(b);
 
console.log('Object c:');
 
console.log(c);
 
console.log();
 
 
 
if (c.nulltest == null)
 
    console.log('c.nulltest is NULL');
 
else
 
    console.log('c.nulltest is NOT NULL');
 
 
 
[assen.totin@archimed ~]$ node nulltest.js 
Object a:
 
{ nulltest: null }
 
 
 
JSON string b:
 
{"nulltest":null}
 
 
 
Object c:
 
{ nulltest: null }
 
 
 
c.nulltest is NULL



 Comments   
Comment by Oleksandr Byelkin [ 2022-08-01 ]

There is other function which behave the same, and here is a bit better test to show difference:

SELECT 1 + JSON_VALUE('{"nulltest": null}', '$.nulltest');
 
SELECT 1 + JSON_EXTRACT('{"nulltest": null}', '$.nulltest');
 
SELECT 1 + NULL;

result is:

SELECT 1 + JSON_VALUE('{"nulltest": null}', '$.nulltest');
 
1 + JSON_VALUE('{"nulltest": null}', '$.nulltest')
 
1
 
Warnings:
 
Warning	1292	Truncated incorrect DOUBLE value: 'null'
 
SELECT 1 + JSON_EXTRACT('{"nulltest": null}', '$.nulltest');
 
1 + JSON_EXTRACT('{"nulltest": null}', '$.nulltest')
 
1
 
SELECT 1 + NULL;
 
1 + NULL
 
NULL

Comment by Oleksandr Byelkin [ 2022-08-01 ]

Actually parser correctly set JSON_VALUE_NULL type, but it should be only correctly processed in val* methods.

Item_func_json_extract::val_decimal even explicit put 0 value on it (I put there TODO marking to fix it).

Comment by Rucha Deodhar [ 2022-08-01 ]

Patch: https://github.com/MariaDB/server/commit/8c667011a66fdbbe0cc4689f3886e174cf9045ba

Comment by Sergei Golubchik [ 2022-08-01 ]

JSON_VALUE should definitely return NULL for JSON_VALUE_NULL.
JSON_EXTRACT — probably, not. it returns a json literal, like in

MariaDB [test]> select json_extract('{"a":null, "b":10, "c":"null"}', '$.a');
 
+-------------------------------------------------------+
 
| json_extract('{"a":null, "b":10, "c":"null"}', '$.a') |
 
+-------------------------------------------------------+
 
| null                                                  |
 
+-------------------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> select json_extract('{"a":null, "b":10, "c":"null"}', '$.c');
 
+-------------------------------------------------------+
 
| json_extract('{"a":null, "b":10, "c":"null"}', '$.c') |
 
+-------------------------------------------------------+
 
| "null"                                                |
 
+-------------------------------------------------------+
 
1 row in set (0.001 sec)

See, the string is quoted "null", so a JSON NULL value should be null not SQL NULL.

Comment by Oleksandr Byelkin [ 2022-08-11 ]

rucha174 add please json_extract un the test case (with comment why it is correct). just to 1) have coverage 2) if in the future we return to this again our decision will be documented and tested. After this it is OK to push.

ah, and remove my TODO from the fources of json_extract.

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