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
|
|
Critical MDEV-19664 JSON_VALUE and null values
