-
Type:
Bug
-
Status: Done
-
Priority:
Medium
-
Resolution: Fixed
-
Affects Version/s: 5.7.30-33
-
Fix Version/s: 5.7.31-34
-
Component/s: None
-
Labels:None
-
Upstream Bug URL:
-
Needs Review:Yes
Description:
When running an INSERT ON DUPLICATE KEY UPDATE operation that does not actually change data, datatype of a field in JSON column changes unexpectedly. This leads to an unnecessary UPDATE written to binlog.
On 8.0.20 this unneeded conversion does not happen.
How to repeat:
mysql> CREATE TABLE `j2` ( `id` int(11) NOT NULL, `j` json DEFAULT NULL, `tombstone` tinyint(4) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; mysql> insert into j2 values(2, '{"amount": 70.0}', 1); Query OK, 1 row affected (0.01 sec) mysql> select json_type(j->"$.amount") from j2; +--------------------------+ | json_type(j->"$.amount") | +--------------------------+ | DOUBLE | +--------------------------+ 1 row in set (0.01 sec) mysql> insert into j2(id,j,tombstone) values(2, '{}', 0) on duplicate key update j=if(tombstone=0, '{}', j); Query OK, 2 rows affected (0.01 sec) mysql> select json_type(j->"$.amount") from j2; +--------------------------+ | json_type(j->"$.amount") | +--------------------------+ | INTEGER | +--------------------------+ 1 row in set (0.00 sec)
The update should set "j = j" since "tombstone != 0", or not make any change. However, "2 rows affected" is reported since the datatype got changes, and this operation goes to binlog as an UPDATE.
Suggested fix:
5.7 should leave the datatype as-is in this case.