skip to Main Content

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.

2

Answers


  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.

    Login or Signup to reply.
  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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search