Description:
When updating a view, "JSON partial update" does not work.
SET @@session.binlog_row_value_options = PARTIAL_JSON;
SET @@session.binlog_row_image = MINIMAL;
CREATE TABLE json_partial(id INT PRIMARY KEY AUTO_INCREMENT,
json_col JSON,
name VARCHAR(100) AS (json_col->>'$.name'),
age INT AS (json_col->'$.age'));
INSERT INTO json_partial(json_col) VALUES
(JSON_OBJECT('name', 'Joe', 'age', 24,
'data', REPEAT('x', 10 * 1000 * 1000))),
(JSON_OBJECT('name', 'Sue', 'age', 32,
'data', REPEAT('y', 10 * 1000 * 1000))),
(JSON_OBJECT('name', 'Pete', 'age', 40,
'data', REPEAT('z', 10 * 1000 * 1000))),
(JSON_OBJECT('name', 'Jenny', 'age', 27,
'data', REPEAT('w', 10 * 1000 * 1000)));
INSERT INTO json_partial(json_col) SELECT json_col FROM json_partial;
INSERT INTO json_partial(json_col) SELECT json_col FROM json_partial;
create view v_json_partial as select * from json_partial;
JSON PARTIAL UPDATE works:
mysql> update json_partial set json_partial.json_col = json_set(json_col, '$.age', age + 1);
Query OK, 16 rows affected (0.95 sec)
Rows matched: 16 Changed: 16 Warnings: 0
JSON PARTIAL UPDATE does not work:
mysql> update v_json set v_json.json_col = json_set(json_col, '$.age', age + 1);
Query OK, 16 rows affected (10.00 sec)
Rows matched: 16 Changed: 16 Warnings: 0
Breakpoint at "Item_json_func::supports_partial_update",
which is called by "prepare_partial_update" to check whether the item "json_set(json_col, '$.age', age + 1)" supports partial update,
you can find the function return false because type of v_json.json_col is Item_ref, not Item_ident.
How to repeat:
As above.
Description: When updating a view, "JSON partial update" does not work. SET @@session.binlog_row_value_options = PARTIAL_JSON; SET @@session.binlog_row_image = MINIMAL; CREATE TABLE json_partial(id INT PRIMARY KEY AUTO_INCREMENT, json_col JSON, name VARCHAR(100) AS (json_col->>'$.name'), age INT AS (json_col->'$.age')); INSERT INTO json_partial(json_col) VALUES (JSON_OBJECT('name', 'Joe', 'age', 24, 'data', REPEAT('x', 10 * 1000 * 1000))), (JSON_OBJECT('name', 'Sue', 'age', 32, 'data', REPEAT('y', 10 * 1000 * 1000))), (JSON_OBJECT('name', 'Pete', 'age', 40, 'data', REPEAT('z', 10 * 1000 * 1000))), (JSON_OBJECT('name', 'Jenny', 'age', 27, 'data', REPEAT('w', 10 * 1000 * 1000))); INSERT INTO json_partial(json_col) SELECT json_col FROM json_partial; INSERT INTO json_partial(json_col) SELECT json_col FROM json_partial; create view v_json_partial as select * from json_partial; JSON PARTIAL UPDATE works: mysql> update json_partial set json_partial.json_col = json_set(json_col, '$.age', age + 1); Query OK, 16 rows affected (0.95 sec) Rows matched: 16 Changed: 16 Warnings: 0 JSON PARTIAL UPDATE does not work: mysql> update v_json set v_json.json_col = json_set(json_col, '$.age', age + 1); Query OK, 16 rows affected (10.00 sec) Rows matched: 16 Changed: 16 Warnings: 0 Breakpoint at "Item_json_func::supports_partial_update", which is called by "prepare_partial_update" to check whether the item "json_set(json_col, '$.age', age + 1)" supports partial update, you can find the function return false because type of v_json.json_col is Item_ref, not Item_ident. How to repeat: As above.