Bug #111899 json generated column null problem
Submitted: 27 Jul 2023 2:35 Modified: 27 Jul 2023 7:59
Reporter: Baolin Huang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.32, 8.0.34, 5.7.43 OS:Any
Assigned to: CPU Architecture:Any

[27 Jul 2023 2:35] Baolin Huang 
Description:
When creating a not null generated column for json.
If the corresponding generated column has no value, the generated column appears to be treated as '' instead of null.

However, when creating an index on this generated column, an error will be reported "Column cannot be null"

How to repeat:
```
CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `tableType` varchar(45) DEFAULT NULL,
  `jkey` varchar(45) DEFAULT NULL,
  `jval` json DEFAULT NULL,
  PRIMARY KEY (`id`)
);

insert into table1(id, jval) values(1, '{"group": "group1@abc.com", "user": "user1"}'),(2, null);

alter table table1 add user_g varchar(250) generated ALWAYS as  (`jval` ->> '$.group') not null;

select * from table1 where user_g is null; 
select * from table1 where user_g ='';

alter table table1 add index ind(user_g);
ERROR 1048 (23000): Column 'user_g' cannot be null

```
[27 Jul 2023 7:59] MySQL Verification Team 
Hello Huang,

Thank you for the report and test case.
Verified as described.

regards,
Umesh