Description:
This problem found while testing the wl8960 ,but this bug is nothing to do with wl8960, the partial update functionality should also work on the views, as base tables. Here is the logfile. The same query plan shows partial update on the base table, but does not show on the views.
CREATE TABLE tab(id INT PRIMARY KEY, j1 JSON, j2 JSON);
INSERT INTO tab VALUES(1, '{"a":"a","b":"b"}', '{"a":"a","b":"b"}');
INSERT INTO tab VALUES(2, NULL, NULL);
INSERT INTO tab VALUES(3, '{"a":"aa","b":"bb"}', '{"a":"aa","b":"bb"}');
INSERT INTO tab VALUES(4, '["Sachin","Tendulkar"]','["Virat","Kohili"]');
INSERT INTO tab VALUES(5, '[100 ,{"A1": "Amarnath","B1": "Binni"}]','[200,{"A2": "Azar","S1": "Srikanth"}]');
INSERT INTO tab VALUES(6, '{"Chetan": [300,400],"Sasri": [500,600]}','{"Dhoni": [700,800],"Sidhu": [900,100]}');
CREATE VIEW view_tab AS SELECT * FROM tab;
ANALYZE TABLE tab;
EXPLAIN FORMAT=JSON UPDATE view_tab SET j1 = JSON_SET(j1, '$.a', 'x'),
j2 = JSON_SET(j2, '$.a', 'y'),
j1 = JSON_SET(j1, '$.b', 'z');
EXPLAIN FORMAT=JSON UPDATE view_tab SET j1 = JSON_REPLACE(j1,'$[1].A1', 'XX') ;
EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_SET(j1, '$.a', 'x'),
j2 = JSON_SET(j2, '$.a', 'y'),
j1 = JSON_SET(j1, '$.b', 'z');
EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_REPLACE(j1,'$[1].A1', 'XX') ;
mysql> CREATE TABLE tab(id INT PRIMARY KEY, j1 JSON, j2 JSON);
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO tab VALUES(1, '{"a":"a","b":"b"}', '{"a":"a","b":"b"}');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tab VALUES(2, NULL, NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tab VALUES(3, '{"a":"aa","b":"bb"}', '{"a":"aa","b":"bb"}');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tab VALUES(4, '["Sachin","Tendulkar"]','["Virat","Kohili"]');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tab VALUES(5, '[100 ,{"A1": "Amarnath","B1": "Binni"}]','[200,{"A2": "Azar","S1": "Srikanth"}]');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tab VALUES(6, '{"Chetan": [300,400],"Sasri": [500,600]}','{"Dhoni": [700,800],"Sidhu": [900,100]}');
Query OK, 1 row affected (0.00 sec)
mysql> CREATE VIEW view_tab AS SELECT * FROM tab;
Query OK, 0 rows affected (0.02 sec)
mysql> ANALYZE TABLE tab;
+----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------+---------+----------+----------+
| test.tab | analyze | status | OK |
+----------+---------+----------+----------+
1 row in set (0.02 sec)
mysql> EXPLAIN FORMAT=JSON UPDATE view_tab SET j1 = JSON_SET(j1, '$.a', 'x'),
-> j2 = JSON_SET(j2, '$.a', 'y'),
-> j1 = JSON_SET(j1, '$.b', 'z');
| EXPLAIN | {
"query_block": {
"select_id": 1,
"table": {
"update": true,
"table_name": "tab",
"access_type": "index",
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"rows_examined_per_scan": 6,
"filtered": "100.00"
}
}
} |
1 row in set (0.01 sec)
mysql> EXPLAIN FORMAT=JSON UPDATE view_tab SET j1 = JSON_REPLACE(j1,'$[1].A1', 'XX') ;
| EXPLAIN |
| {
"query_block": {
"select_id": 1,
"table": {
"update": true,
"table_name": "tab",
"access_type": "index",
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"rows_examined_per_scan": 6,
"filtered": "100.00"
}
}
} |
1 row in set (0.00 sec)
mysql> EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_SET(j1, '$.a', 'x'),
-> j2 = JSON_SET(j2, '$.a', 'y'),
-> j1 = JSON_SET(j1, '$.b', 'z');
| EXPLAIN |
| {
"query_block": {
"select_id": 1,
"table": {
"update": true,
"table_name": "tab",
"access_type": "index",
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"rows_examined_per_scan": 6,
"filtered": "100.00",
"partial_update_columns": [
"j1",
"j2"
]
}
}
} |
1 row in set (0.00 sec)
mysql>
mysql> EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_REPLACE(j1,'$[1].A1', 'XX') ;
| EXPLAIN |
| {
"query_block": {
"select_id": 1,
"table": {
"update": true,
"table_name": "tab",
"access_type": "index",
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"rows_examined_per_scan": 6,
"filtered": "100.00",
"partial_update_columns": [
"j1"
]
}
}
} |
1 row in set (0.00 sec)
mysql>
How to repeat:
CREATE TABLE tab(id INT PRIMARY KEY, j1 JSON, j2 JSON);
INSERT INTO tab VALUES(1, '{"a":"a","b":"b"}', '{"a":"a","b":"b"}');
INSERT INTO tab VALUES(2, NULL, NULL);
INSERT INTO tab VALUES(3, '{"a":"aa","b":"bb"}', '{"a":"aa","b":"bb"}');
INSERT INTO tab VALUES(4, '["Sachin","Tendulkar"]','["Virat","Kohili"]');
INSERT INTO tab VALUES(5, '[100 ,{"A1": "Amarnath","B1": "Binni"}]','[200,{"A2": "Azar","S1": "Srikanth"}]');
INSERT INTO tab VALUES(6, '{"Chetan": [300,400],"Sasri": [500,600]}','{"Dhoni": [700,800],"Sidhu": [900,100]}');
CREATE VIEW view_tab AS SELECT * FROM tab;
ANALYZE TABLE tab;
EXPLAIN FORMAT=JSON UPDATE view_tab SET j1 = JSON_SET(j1, '$.a', 'x'),
j2 = JSON_SET(j2, '$.a', 'y'),
j1 = JSON_SET(j1, '$.b', 'z');
EXPLAIN FORMAT=JSON UPDATE view_tab SET j1 = JSON_REPLACE(j1,'$[1].A1', 'XX') ;
Suggested fix:
partial update should be allowed on views too.
