MySQL Bugs: #93498: JSON_TABLE does not handle JSON NULL correctly with VARCHAR

Bug #93498	JSON_TABLE does not handle JSON NULL correctly with VARCHAR
Submitted:	5 Dec 2018 22:55	Modified:	4 Jan 2019 16:08
Reporter:	Mark Drake	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S2 (Serious)
Version:	8.0, 8.0.13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysql> select json_array(null);
+------------------+
| json_array(null) |
+------------------+
| [null]           |
+------------------+
1 row in set (0.00 sec)

ERROR 1305 (42000): FUNCTION sys.json_arary does not exist
mysql> select data.X from json_table(json_array(null),"$" columns (X varchar(40) Path '$[0]' NULL ON ERROR))  data where data.x is not null;
+------+
| X    |
+------+
| null |
+------+
1 row in set (0.00 sec)

mysql> select data.X from json_table(json_array(null),"$" columns (X varchar(40) Path '$[0]' NULL ON ERROR))  data where data.x is null;
Empty set (0.00 sec)

mysql>

How to repeat:
The results for this t/c are inverted. The first statement should not return a row, the second should return a row. Then input to the JSON_ARRAY is a SQL NULL, which correctly becomes a JSON NULL in the generated output. However when JSON_TABLE encounters the JSON NULL - Which is not a string, that would be ["null"] it converts it to a VARCHAR with the value 'null', not a NULL varchar.

Hello Mark Drake,

Thank you for the report.

regards,
Umesh

Thanks for the bug report.

I believe this is the intended behaviour of JSON_TABLE. https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html says:

  "JSON_TABLE() extracts data as JSON then coerces it to the column type, using the regular automatic type conversion applying to JSON data in MySQL."

JSON_TABLE's conversion of the JSON null literal to the string 'null' is consistent with how this is done in other situations:

mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(JSON_ARRAY(NULL), '$[0]'));
+------------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(JSON_ARRAY(NULL), '$[0]')) |
+------------------------------------------------------+
| null                                                 |
+------------------------------------------------------+
1 row in set (0,00 sec)

mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(JSON_ARRAY(NULL), '$[0]')) IS NULL;
+--------------------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(JSON_ARRAY(NULL), '$[0]')) IS NULL |
+--------------------------------------------------------------+
|                                                            0 |
+--------------------------------------------------------------+
1 row in set (0,01 sec)

mysql> CREATE TABLE t(x VARCHAR(100));
Query OK, 0 rows affected (0,06 sec)

mysql> INSERT INTO t VALUES (JSON_EXTRACT(JSON_ARRAY(NULL), '$[0]'));
Query OK, 1 row affected (0,00 sec)

mysql> SELECT x, x IS NULL FROM t;
+------+-----------+
| x    | x IS NULL |
+------+-----------+
| null |         0 |
+------+-----------+
1 row in set (0,00 sec)

I would argue that MySQL Null handling is wrong in other situations too. It is certainly inconsistent. How would you differentiate between these two JSON key/value pairs

{ "nullValue" : null, "nullText" : "null"}

The Oracle RDBMS definitely maps a JSON null to a SQL NULL In this case.

Hi Mark,

To distinguish between the JSON null literal and the string "null", you can use the JSON_TYPE function.

mysql> create table t(j json);
Query OK, 0 rows affected (0,06 sec)

mysql> insert into t values ('{ "nullValue" : null, "nullText" : "null"}');
Query OK, 1 row affected (0,02 sec)

mysql> select j, json_type(j->'$.nullValue'), json_type(j->'$.nullText') from t;
+-----------------------------------------+-----------------------------+----------------------------+
| j                                       | json_type(j->'$.nullValue') | json_type(j->'$.nullText') |
+-----------------------------------------+-----------------------------+----------------------------+
| {"nullText": "null", "nullValue": null} | NULL                        | STRING                     |
+-----------------------------------------+-----------------------------+----------------------------+
1 row in set (0,00 sec)