[MDEV-19664] JSON_VALUE and null values Created: 2019-05-31  Updated: 2022-12-09  Resolved: 2022-12-09

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.4.5, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.3.38, 10.4.28, 10.5.19, 10.6.12, 10.7.8

Type: Bug Priority: Major
Reporter: Wolfgang Draxler Assignee: Rucha Deodhar
Resolution: Duplicate Votes: 9
Labels: None
Environment:

Windows

Issue Links:
Duplicate
is duplicated by MDEV-27151 JSON_VALUE() does not parse NULL prop... Closed

 Description   

I have found the following bug:

> set @xxx= JSON_INSERT('{}', '$.C', NULL);
 
> select @xxx
 
+-------------+
 
| @xxx        |
 
+-------------+
 
| {"C": null} |
 
+-------------+
 
1 row in set (0.001 sec){quote}
 
 
 
> select nvl(json_value(@xxx, '$.C'),'NVL-NULL-VALUE');
 
+-----------------------------------------------+
 
| nvl(json_value(@xxx, '$.C'),'NVL-NULL-VALUE') |
 
+-----------------------------------------------+
 
| null                                          |
 
+-----------------------------------------------+
 
1 row in set (0.001 sec)

I was expecting

> select nvl(json_value(@xxx, '$.C'),'NVL-NULL-VALUE');
 
+-----------------------------------------------+
 
| nvl(json_value(@xxx, '$.C'),'NVL-NULL-VALUE') |
 
+-----------------------------------------------+
 
| NVL-NULL-VALUE                                |
 
+-----------------------------------------------+
 
1 row in set (0.001 sec)



 Comments   
Comment by Elena Stepanova [ 2019-06-03 ]

That's because JSON null is not the NULL. That's how we can see it, for example:

MySQL [test]> select json_extract(json_object('a',NULL),'$.a') is null;
 
+---------------------------------------------------+
 
| json_extract(json_object('a',NULL),'$.a') is null |
 
+---------------------------------------------------+
 
|                                                 0 |
 
+---------------------------------------------------+
 
1 row in set (0.001 sec)

Same with MySQL 5.7.

I'll leave it to holyfoot to comment whether it's supposed to be so or not.

Comment by Kacper Cioch (Inactive) [ 2019-11-28 ]

Hey.
I am also looking for answer to this question. Please let us know, whether this behaves as designed or is it a bug. I can provide following example:

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

It returns string 'null' instead of value NULL.
I was expecting

MariaDB [test]> SELECT json_value('{"a": null}','$.a');
 
+---------------------------------+
 
| json_value('{"a": null}','$.a') |
 
+---------------------------------+
 
| NULL                            |
 
+---------------------------------+
 
1 row in set (0.001 sec)

tested on 10.4.10

I think it is a bug because of this (different jsdoc, same result)

MariaDB [test]> SELECT json_value('{"a": "null"}','$.a') = json_value('{"a": null}','$.a'); 
+---------------------------------------------------------------------+
 
| json_value('{"a": "null"}','$.a') = json_value('{"a": null}','$.a') |
 
+---------------------------------------------------------------------+
 
|                                                                   1 |
 
+---------------------------------------------------------------------+
 
1 row in set (0.001 sec)

Comment by Jan Venekamp [ 2020-01-06 ]

I think not many people would expect that null equals to the string 'null', so I second Kacper Cioch that this should be considered as a bug.

This behavior bit me in an other way. Inspired by this article https://mariadb.com/resources/blog/json-with-mariadb-10-2/ I started using virtual columns based on JSON values. Here is an example of how this behaves rather differently than expected.

CREATE TABLE "opt_ref" (
 
    "id" INT UNSIGNED PRIMARY KEY
 
) ENGINE = "InnoDB";
 
 
 
CREATE TABLE "foo" (
 
    "data" JSON NULL,
 
    "opt_ref_id" INT UNSIGNED AS (JSON_VALUE("data", '$.opt_ref_id')) PERSISTENT,
 
    FOREIGN KEY ("opt_ref_id") REFERENCES "opt_ref" ("id")
 
) ENGINE = "InnoDB";
 
 
 
INSERT INTO "foo" ("data") VALUES ('{"opt_ref_id":null}');
 
SELECT * FROM "foo";

Expected

data opt_ref_id
{"opt_ref_id":null} NULL

Actual

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails ("test"."foo", CONSTRAINT "foo_ibfk_1" FOREIGN KEY ("opt_ref_id") REFERENCES "opt_ref" ("id"))

Workaround

For other people stumbling upon this, here is a workaround:

CREATE TABLE "foo" (
 
    "data" JSON NULL,
 
    "opt_ref_id" INT UNSIGNED AS (IF(JSON_TYPE(JSON_EXTRACT("data", '$.opt_ref_id')) = 'NULL', NULL, JSON_VALUE("data", '$.opt_ref_id'))) PERSISTENT,
 
    FOREIGN KEY ("opt_ref_id") REFERENCES "opt_ref" ("id")
 
) ENGINE = "InnoDB";

Comment by Kolbe Kegel [ 2020-02-18 ]

Maybe what's needed is something like a JSON_CAST function that combines JSON_TYPE/JSON_EXTRACT/JSON_VALUE functionality to return a json path cast to the most appropriate MariaDB type.

Comment by Timucin Bahsi [ 2021-07-13 ]

Let's explore this a little:

select
 
    json_value('{"a":null}',
 
    '$.a'),
 
    json_value('{\' a\':null}',
 
    '$.a'),
 
    json_value('{"a":NULL}',
 
    '$.a'),
 
    json_value('{"a":nullrandomletters}',
 
    '$.a'),
 
    json_value('{"a":randomletters}',
 
    '$.a'),
 
    null;

Output:

json_value(' {"a":null}

', '$.a')

 json_value(' {"a":NULL}

', '$.a')

 json_value(' {"a":nullrandomletters}

', '$.a')

 json_value(' {"a":randomletters}

', '$.a')

 NULL
null NULL null NULL NULL

Now, first we see that it returns a string with "null" in it. Ok, maybe it doesn't recognize null in json, but it does when we use "NULL" in capitals. Then we see the weirdest thing, if the expression starts with the leters "null", it ignores the rest. This is very different than treating it as a string. Then we actually see it's not how it treats strings. If you don't use quotes around strings it's just null.

This doesn't seem like intended at all. It doesn't see it as null, it doesn't see it as a string either. There's certainly a special case for this specific sequence of characters and it doesn't any sense that i can think of.

Note: tested under MariaDB [10.3.17-MariaDB-1:10.3.17+maria~bionic]

Comment by Assen Totin (Inactive) [ 2021-12-02 ]

Clearly a bug to me. JSON makes a very visible difference between "null" as a string and null as a value. See MDEV-27151.

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