can't mysql generated int culumns be nullable?

March 3, 2024

using Mysql with the following table:

CREATE TABLE participants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    puuid CHAR(36) NOT NULL,
    data JSON,
    project INT GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.project'))) STORED NULL,
    schoolCode VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.schoolCode'))) STORED,
    UNIQUE KEY (puuid)
);

when I try to run :

insert into participants(puuid,data) values('aasd','{"project":1}');

it work witout a problem
but when I try

insert into participants(puuid,data) values('aasd1','{"project":null}');

I get an error saying:

Error Code: 1366. Incorrect integer value: 'null' for column 'project' at row 1

I double checked using

 show columns from participants;

which returns

why won’t it allow the JSON to have a null value to the project even though it states that the project column does allow nulls?

Is there no other solution than something like:

project INT GENERATED ALWAYS AS (COALESCE(JSON_UNQUOTE(JSON_EXTRACT(data, '$.project')), 0)) STORED NULL,

UPDATE:
actually is seems that neither

 project INT GENERATED ALWAYS AS (COALESCE(JSON_UNQUOTE(JSON_EXTRACT(data, '$.project')), 0)) STORED NULL,

nor

 project INT GENERATED ALWAYS AS (IFNULL(JSON_UNQUOTE(JSON_EXTRACT(data, '$.project')), 0)) STORED NULL,

work.
I am about to give up on this GENERATED COLUMNS idea.

>Solution :

A missing JSON key extracts as an SQL NULL.

mysql> select json_extract('{"project":null}', '$.xyzzy') is null as is_it_null;
+------------+
| is_it_null |
+------------+
|          1 |
+------------+

A JSON null value does not extract to an SQL NULL. It extracts as a JSON document which is the scalar value 'null'.

mysql> select json_extract('{"project":null}', '$.project') is null as is_it_null;
+------------+
| is_it_null |
+------------+
|          0 |
+------------+

JSON_UNQUOTE() does not turn the JSON value 'null' into an SQL NULL. It turns it it into the SQL string value 'null'.

mysql> select json_unquote(json_extract('{"project":null}', '$.project')) is null as is_it_null;
+------------+
| is_it_null |
+------------+
|          0 |
+------------+

So your generated column is trying to parse a string value 'null' to cast it as an integer, which fails in strict mode (the default):

mysql> select cast('null' as signed) as bad_integer;
+-------------+
| bad_integer |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect INTEGER value: 'null'

You could fix it by comparing that string value to 'null' and substituting SQL NULL this way:

mysql> select nullif(json_unquote(json_extract('{"project":null}', '$.project')), 'null') is null as is_it_null;
+------------+
| is_it_null |
+------------+
|          1 |
+------------+

But this cannot be distinguished from the JSON string value 'null':

mysql> select nullif(json_unquote(json_extract('{"project":"null"}', '$.project')), 'null') is null as is_it_null;
+------------+
| is_it_null |
+------------+
|          1 |
+------------+

Basically, JSON is a hot mess when combined with SQL. There are so many edge cases that behave in counter-intuitive ways that I have to say it is the worst feature to be added to SQL in many years.