Details
Critical 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
|
|
Attachments
Issue Links
- duplicates
-
MDEV-19664 JSON_VALUE and null values
-
- Closed
-