can't mysql generated int columns be nullable?

Question

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.

===================
UPDATE:

There seems to be two ways to go...

1. Ditching JSON in the DB as I ended up doing following the answer and comments provided by @Bill Karwin

2. If you still want to try using JSON in the DB, you should probably follow the answer provided by @aland (which if I would have received earlier I would have probably tried).

"Two roads diverged in a yellow wood, And sorry I could not travel both...."

(maybe next time)

Answer 1

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.

Answer 2

After reading the answer from Bill,

You could/should do:

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

see: DBFIDDLE

Answer 3

If you used JSON_VALUE (since 8.0.21), that would work as you expect

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

This then works insert into participants(puuid,data) values('aasd1','{"project":null}'); and gives expected result.

See https://stackoverflow.com/a/75743288/131391