MySQL Bugs: #118084: varchar NULL after alter json not null,value is null

Bug #118084	varchar NULL after alter json not null,value is null
Submitted:	29 Apr 9:52	Modified:	31 Aug 12:54
Reporter:	xuzong sun	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.35, 8.0.37	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
type varchar insert NULL value, and then alter json not NULL. 
This data change to null , and if you update this data, you can get a '' value from binlog. 
But , slave can replica this data successful.

SQL_MODE is default.

How to repeat:
MySQL [xuzong]> create table q(id int ,age varchar(200) default NULL);
Query OK, 0 rows affected (0.0107 sec)

MySQL [xuzong]> insert into q values(1,NULL);
Query OK, 1 row affected (0.0040 sec)

MySQL [xuzong]> select * from q;
+----+------+
| id | age  |
+----+------+
|  1 | NULL |
+----+------+
1 row in set (0.0020 sec)

MySQL [xuzong]> alter table q modify age json not null ;
Query OK, 1 row affected, 1 warning (0.0164 sec)

/*This is a warning, not error.*/

MySQL [xuzong]> select * from q;
+----+------+
| id | age  |
+----+------+
|  1 | null |
+----+------+
1 row in set (0.0020 sec)

MySQL [xuzong]> show create table q \G
*************************** 1. row ***************************
       Table: q
Create Table: CREATE TABLE `q` (
  `id` int(11) DEFAULT NULL,
  `age` json NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

MySQL [xuzong]> select * from q  WHERE JSON_EXTRACT(age, '$.age') IS NULL;
+----+------+
| id | age  |
+----+------+
|  1 | null |
+----+------+
1 row in set (0.0019 sec)

/* If you update this data ，binlog can record '' */

MySQL [xuzong]> update q set id=2 where id=1;
Query OK, 1 row affected (0.0023 sec)
Rows matched: 1  Changed: 1  Warnings: 0

binlog:
### UPDATE `xuzong`.`t`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='' /* JSON meta=4 nullable=0 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='' /* JSON meta=4 nullable=0 is_null=0 */

Suggested fix:
I suggest this warngin change to error , if sql_mode is strict mode.

Sorry , I retest this bugs, sql_mode is empty

Sorry , I retest this bugs, sql_mode is empty

Hello xuzong sun,

Thank you for the bug report.
Could you please retest with sql_mode Strict?
Setting sql_mode = 'STRICT_ALL_TABLES' produces error not warning.
For more info, please see the doc https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict

Regards,
Ashwini Patil

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".