Question 352089 in Mysql Question posted in
The official MySQL documentation can be found here.

can't mysql generated int culumns 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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search