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
-
Activity
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).
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.
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.
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')1Warnings:Warning 1292 Truncated incorrect DOUBLE value: 'null'SELECT 1 + JSON_EXTRACT('{"nulltest": null}', '$.nulltest');1 + JSON_EXTRACT('{"nulltest": null}', '$.nulltest')1SELECT 1 + NULL;1 + NULLNULL