MySQL Bugs: #100307: JSON field datatype changes unexpectedly

Bug #100307	JSON field datatype changes unexpectedly
Submitted:	23 Jul 2020 14:08	Modified:	23 Jul 2020 14:43
Reporter:	Sami Ahlroos	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.7.30	OS:	Any
Assigned to:		CPU Architecture:	Any

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:
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((@t := (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.

Thank you for the bug report.

c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p  --prompt="mysql 5.7 > " --default-character-set=latin1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.32 Source distribution BUILT: 2020/07/19

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > USE test;
Database changed
mysql 5.7 > CREATE TABLE `j2` (
    ->   `id` int(11) NOT NULL,
    ->   `j` json DEFAULT NULL,
    ->   `tombstone` tinyint(4) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > insert into j2 values(2, '{"amount": 70.0}', 1);
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > select json_type(j->"$.amount") from j2;
+--------------------------+
| json_type(j->"$.amount") |
+--------------------------+
| DOUBLE                   |
+--------------------------+
1 row in set (0.00 sec)

mysql 5.7 > insert into j2(id,j,tombstone) values(2, '{}', 0) on duplicate key update j=if((@t := (tombstone=0)), '{}', j);
Query OK, 2 rows affected (0.00 sec)

mysql 5.7 > select json_type(j->"$.amount") from j2;
+--------------------------+
| json_type(j->"$.amount") |
+--------------------------+
| INTEGER                  |
+--------------------------+
1 row in set (0.00 sec)

mysql 5.7 > exit
Bye

c:\dbs>80c

c:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22 Source distribution BUILT: 2020/07/19

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > USE test;
Database changed
mysql 8.0 > CREATE TABLE `j2` (
    ->   `id` int(11) NOT NULL,
    ->   `j` json DEFAULT NULL,
    ->   `tombstone` tinyint(4) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql 8.0 > insert into j2 values(2, '{"amount": 70.0}', 1);
Query OK, 1 row affected (0.02 sec)

mysql 8.0 > select json_type(j->"$.amount") from j2;
+--------------------------+
| json_type(j->"$.amount") |
+--------------------------+
| DOUBLE                   |
+--------------------------+
1 row in set (0.00 sec)

mysql 8.0 > insert into j2(id,j,tombstone) values(2, '{}', 0) on duplicate key update j=if((@t := (tombstone=0)), '{}', j);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 8.0 > select json_type(j->"$.amount") from j2;
+--------------------------+
| json_type(j->"$.amount") |
+--------------------------+
| DOUBLE                   |
+--------------------------+
1 row in set (0.00 sec)

JSON fields are using Field_blob::cmp_binary to validate if data
has changed. 8.0 uses Field_json::cmp_binary
in order to do native JSON comparison.

Patch based on 5.7.31 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 100307.patch (text/x-patch), 2.98 KiB.