MySQL Bugs: #88033: Generated INT column with value from JSON document

Bug #88033	Generated INT column with value from JSON document
Submitted:	9 Oct 2017 18:38	Modified:	6 Jun 2021 8:13
Reporter:	Geert Vanderkelen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	8.0.24, 5.7.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I'm trying to generate a column based on a value found in JSON. This value is an integer in the document, or it can be null.

Whenever the JSON column has an integer, no problem. When it is null, I get the 

ERROR 3156 (22018): Invalid JSON value for CAST to INTEGER from column json_extract

However, the generate column is set to allow NULL. Why a CAST is needed, and how would that cast be done? If documented, where? Or simply a bug thus :)

How to repeat:
DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (
  id BIGINT PRIMARY KEY,
  c1 BIGINT AS (c2->>"$.c1") STORED NULL,
  c2 JSON, INDEX(c1));

mysql> INSERT INTO t2 (id, c2) VALUES (1, '{"c1": null}');
ERROR 3156 (22018): Invalid JSON value for CAST to INTEGER from column json_extract at row 1

mysql> INSERT INTO t2 (id, c2) VALUES (1, '{"c1": 1}');
Query OK, 1 row affected (0.01 sec)

The insert statements were using the incorrect table (was using a few to try), but no matter. A more scaled down example:

CREATE TABLE t1 (
  id INT PRIMARY KEY,
  c1 INT AS (c2->>"$.c1") STORED NULL,
  c2 JSON);

mysql> INSERT INTO t1 (id, c2) VALUES (1, '{"c1": null}');
ERROR 1366 (HY000): Incorrect integer value: 'null' for column 'c1' at row 1

mysql> INSERT INTO t1 (id, c2) VALUES (1, '{"c1": 1}');
Query OK, 1 row affected (0.00 sec)

Workaround is to omit the entry in JSON document when saving:

INSERT INTO t1 (id, c2) VALUES (1, '{}');

That will insert a NULL value since $c2->".c1" is not available.
Though that is application side; would be great if JSON null is NULL.

Hello Geert,

Thank you for the report and test case.
This is even observed in 5.7.19.

Regards,
Umesh

Using version 8.0.13 this is still an issue, and I'd say raise the severity to S2 at least.

I am pre-extracting the fields/paths from the JSON document before dynamically generating a view off of JSON value found in base JSON table.

Numerics fail across the board for NULL values when trying to INSERT, but when running the view itself the fields will resolve as expected - why is CAST needed here, and what purposes do/should the ON ERROR/ON EMPTY clauses for JSON_TABLE( ..., COLUMNS() ) serve if not this exact scenario?

Still an issue with MySQL 8.0.24.

This bit me again, and the only documentation that ".. [STORED|VIRTUAL] NULL" is not working is in the bugs.mysql.com data.

If this is a long term "cannot fix", please update the information in the documentation:
  https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html

Removing they syntax all together, and add to limitations?

Just remembered that 8.0.22 added JSON_VALUE, so this can now be solved!

CREATE TABLE t1 (
   id BIGINT PRIMARY KEY,
  c1 BIGINT AS (JSON_VALUE(c2, "$.c1" RETURNING SIGNED NULL ON EMPTY)) STORED NULL,
  c2 JSON, INDEX(c1));

mysql> INSERT INTO t1 (id, c2) VALUES (1, '{"c1": null}');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+------+--------------+
| id | c1   | c2           |
+----+------+--------------+
|  1 | NULL | {"c1": null} |
+----+------+--------------+

I believe it would help lots of people if this could be documented, because this his a hard one :)