Search code examples
mysqlmysql-json

can't mysql generated int columns be nullable?

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

Results of show columns

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)

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.