I have a virtual column named '_cpf' which is extracted from the '_document' column in json. Here is the following query:
CREATE TABLE 'user' (
'_document' json DEFAULT NULL,
'_id' varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract('_document','$.id'))) STORED NOT NULL,
'_cpf' varchar(11) GENERATED ALWAYS AS (json_unquote(json_extract('_document','$.cpf'))) VIRTUAL,
'_externalId' int(11) GENERATED ALWAYS AS (json_unquote(json_extract('_document','$.externalId'))) VIRTUAL NOT NULL,
'_created_at' datetime NOT NULL,
'_updated_at' datetime NOT NULL,
'_status_id' int(11) NOT NULL DEFAULT '1',
PRIMARY KEY ('_id'),
UNIQUE KEY ('_empresaId', '_cpf'),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The problem is when I insert a line with cpf equal to null, the virtual column saves as varchar and not NULL, as it should. That is, if I do a 'select * from user where _cpf is null' it does not work.
There is also the problem in inserting another line with _cpf null and with the same value in the _Identium column, resulting in the error of duplicate values of the UNIQUE KEY command.