innodb/t/blob_partial_update.test

7 # [Note] LOB partial update of field=(j1) on index=(GEN_CLUST_INDEX)
18 CREATE TABLE tab(j1 JSON);
25 select json_extract(j1, '$[0]') from tab;
26 EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_SET(j1, '$[0]', 'VISWA');
27 UPDATE tab SET j1 = JSON_SET(j1, '$[0]', 'Viswa');
29 select json_extract(j1, '$[0]') from tab;
30 EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Nath');
31 UPDATE tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Nath');
33 select json_extract(j1, '$[0]') from tab;
37 ALTER TABLE tab MODIFY j1 LONGTEXT ;
39 EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_SET(j1, '$[0]', 'Setlongtext');
41 UPDATE tab SET j1 = JSON_SET(j1, '$[0]', 'Setlongtext');
46 SELECT JSON_STORAGE_SIZE(j1) FROM tab;
48 SELECT JSON_STORAGE_FREE(j1) FROM tab;
51 SELECT json_extract(j1, '$[0]') FROM tab;
53 EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Replongtext');
55 UPDATE tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Replongtext');
60 SELECT JSON_STORAGE_SIZE(j1) FROM tab;
62 SELECT JSON_STORAGE_FREE(j1) FROM tab;
65 SELECT json_extract(j1, '$[0]') from tab;
68 ALTER TABLE tab MODIFY j1 JSON ;
70 EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_SET(j1, '$[0]', 'Setjson');
72 UPDATE tab SET j1 = JSON_SET(j1, '$[0]', 'Setjson');
77 SELECT  json_extract(j1, '$[0]') FROM tab;
79 EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Repjson');
81 UPDATE tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Repjson');
86 SELECT json_extract(j1, '$[0]') FROM tab;
93 EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_SET(j1, '$[0]', 'Setcomp');
95 UPDATE tab SET j1 = JSON_SET(j1, '$[0]', 'Setcomp');
100 SELECT  json_extract(j1, '$[0]') FROM tab;
102 EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Repcomp');
104 UPDATE tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Repcomp');
107 SELECT json_extract(j1, '$[0]') FROM tab;
116 EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_SET(j1, '$[0]', 'Setdyna');
118 UPDATE tab SET j1 = JSON_SET(j1, '$[0]', 'Setdyna');
123 SELECT  json_extract(j1, '$[0]') FROM tab;
125 EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Repdyna');
127 UPDATE tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Repdyna');
132 SELECT json_extract(j1, '$[0]') FROM tab;
139 EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_SET(j1, '$[0]', 'Setredu');
141 UPDATE tab SET j1 = JSON_SET(j1, '$[0]', 'Setredu');
146 SELECT  json_extract(j1, '$[0]') FROM tab;
148 EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Repredu');
150 UPDATE tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Repredu');
153 SELECT json_extract(j1, '$[0]') FROM tab;
162 EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_SET(j1, '$[0]', 'Setcomp');
164 UPDATE tab SET j1 = JSON_SET(j1, '$[0]', 'Setcomp');
169 SELECT  json_extract(j1, '$[0]') FROM tab;
171 EXPLAIN FORMAT=JSON UPDATE tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Repcomp');
173 UPDATE tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Repcomp');
176 SELECT json_extract(j1, '$[0]') FROM tab;
181 EXPLAIN FORMAT=JSON UPDATE view_tab SET j1 = JSON_SET(j1, '$[0]', 'Setview');
183 UPDATE view_tab SET j1 = JSON_SET(j1, '$[0]', 'Setview');
188 SELECT JSON_STORAGE_SIZE(j1) FROM view_tab;
190 SELECT JSON_STORAGE_FREE(j1) FROM view_tab;
193 SELECT  json_extract(j1, '$[0]') FROM view_tab;
195 EXPLAIN FORMAT=JSON UPDATE view_tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Repview');
197 UPDATE view_tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Repview');
200 SELECT json_extract(j1, '$[0]') FROM view_tab;
205 SELECT JSON_STORAGE_SIZE(j1) FROM view_tab;
207 SELECT JSON_STORAGE_FREE(j1) FROM view_tab;
220 UPDATE tab SET j1 = JSON_REPLACE(j1, '$[0]', 'Reppro');
231 SELECT  json_extract(j1, '$[0]') FROM tab;
234 SELECT JSON_STORAGE_SIZE(j1) FROM view_tab;
236 SELECT JSON_STORAGE_FREE(j1) FROM view_tab;
251 UPDATE tab SET j1 = JSON_SET(j1, '$[0]', 'SetLZ');
264 SELECT  json_extract(j1, '$[0]') FROM tab;
273 CREATE TABLE tab1(id INT PRIMARY KEY, j1 JSON);
279 ALTER TABLE tab1 modify j1 BLOB;
284 UPDATE tab1 SET j1 = JSON_SET(j1, '$.a', 'x');
287 SELECT JSON_STORAGE_SIZE(j1) FROM tab1;
290 SELECT JSON_STORAGE_FREE(j1) FROM tab1;
303 CREATE TABLE tab3 (a INT, j1 JSON, j2 JSON) PARTITION BY LINEAR HASH(a) PARTITIONS 3;
322 EXPLAIN FORMAT=JSON UPDATE tab3 SET j1 = JSON_SET(j1, '$.a', 'x'),
324 j1 = JSON_SET(j1, '$.b', 'z');
326 UPDATE tab3 SET j1 = JSON_SET(j1, '$.a', 'x'),
328 j1 = JSON_SET(j1, '$.b', 'z');
333 EXPLAIN FORMAT=JSON UPDATE tab3 SET j1 = JSON_REPLACE(j1,'$[1].A1', 'partRe') ;
335 UPDATE tab3 SET j1 = JSON_REPLACE(j1,'$[1].A1', 'PartRe') ;
338 SELECT json_extract(j1, '$[1].A1') FROM tab3;