MySQL Bugs: #93167: Improper JSON NULL handling

Bug #93167	Improper JSON NULL handling
Submitted:	13 Nov 2018 1:55	Modified:	21 Nov 2018 13:20
Reporter:	Philippe Riand	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S2 (Serious)
Version:	8.0.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	json null

Description:
Looks like JSON null handling changed, as some of our code using MySQL 8 from last year doesn't work anymore with the latest 8.0.13. Not sure what version broke it though. 

It looks like a JSON value of 'null' is not returned as 'NULL' anymore. See the sample SQL bellow, easy to reproduce from the workbench.

How to repeat:
SET @doc = JSON_OBJECT('a', NULL); 
SELECT JSON_UNQUOTE(JSON_EXTRACT(@doc,"$.a")) as C1, 
JSON_UNQUOTE(JSON_EXTRACT(@doc,"$.b")) as C2; 

C2 is effectively set as NULL, but C1 is returned as the 4 char 'null' string.

Suggested fix:
JSON_EXTRACT() should return the NULL value for a JSON null, and not the string 'null'

Hi Philippe,

Can you clarify what change in behaviour you are seeing? With the test case you provided, I'm seeing the exact same behaviour with MySQL 8.0.13 as I'm seeing with MySQL 5.7.9, which was the release that originally introduced JSON support in MySQL.

mysql> SET @doc = JSON_OBJECT('a', NULL);
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(@doc,"$.a")) as C1,
    -> JSON_UNQUOTE(JSON_EXTRACT(@doc,"$.b")) as C2;
+------+------+
| C1   | C2   |
+------+------+
| null | NULL |
+------+------+
1 row in set (0,01 sec)

mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(@doc,"$.a")) IS NULL as C1,
    -> JSON_UNQUOTE(JSON_EXTRACT(@doc,"$.b")) IS NULL as C2;
+----+----+
| C1 | C2 |
+----+----+
|  0 |  1 |
+----+----+
1 row in set (0,00 sec)

That is, C1 is the string 'null' and C2 is the SQL NULL/unknown value, and it looks like it has been this way since MySQL introduced JSON support. Is there something I'm missing?

Thanks.

Changing status to Need feedback.
Also, this looks like duplicate of Bug #85755

You are actually right, as I reinstalled an earlier version (8.0.2) and the same problem exists. We actually found the problem because we restarted the unit tests which now fails, while they were not a few months ago.
Anyway, return the string 'null' for a null value is not the expected result and I think is a bug. All the other DBs we tried (Postgrsql, MSSQL, DB2, SQLite) are returning the NULL value.

Thank you for the details, this is duplicate of Bug #85755, please see Bug #85755

regards,
Umesh