Debian Codegraph @

mysql-test/suite/rpl/r/rpl_row_jsondiff_basic_pk.result

raw
1 include/rpl_init.inc [topology=1->2->3->4->5->6]
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 Warnings:
6 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
7 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
8 Warnings:
9 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
10 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
11 Warnings:
12 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
13 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
14 Warnings:
15 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
16 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
17 include/rpl_default_connections.inc
18 include/rpl_stop_slaves.inc
19 include/begin_replace_combination.inc [BOTH.BINLOG_FORMAT=ROW,MIXED,STATEMENT -> GLOBAL.SLAVE_ROWS_SEARCH_ALGORITHMS=INDEX_SCAN,HASH_SCAN,TABLE_SCAN]
20 Warnings:
21 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
22 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
23 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
24 Warnings:
25 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
26 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
27 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
28 Warnings:
29 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
30 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
31 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
32 Warnings:
33 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
34 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
35 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
36 Warnings:
37 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
38 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
39 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
40 Warnings:
41 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
42 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
43 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
44 Warnings:
45 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
46 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
47 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
48 Warnings:
49 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
50 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
51 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
52 include/rpl_start_slaves.inc
53 ######## INITIALIZE ########
54 include/rpl_for_each_server_stmt.inc [SET @@SESSION.BINLOG_ROW_IMAGE = MINIMAL;
55 SET @@SESSION.BINLOG_ROW_VALUE_OPTIONS = PARTIAL_JSON;
56 SET @@GLOBAL.BINLOG_ROW_IMAGE = MINIMAL;
57 SET @@GLOBAL.BINLOG_ROW_VALUE_OPTIONS = PARTIAL_JSON;
58 ]
59 include/rpl_stop_slaves.inc
60 include/rpl_start_slaves.inc
61 ******** Single JSON column ********
62 ==== Full format ====
63 ---- 1. Update one row using full format (wrong column used) ----
64 include/rpl_row_jsondiff_scenario.inc
65 * CREATE TABLE test.t (i INT PRIMARY KEY, j JSON, k JSON)
66 # Before update
67 i j k
68 1 [1, 2] [3, 4]
69 UPDATE t SET j = JSON_SET(k, '$[0]', 'a') WHERE i = 1
70 # After update
71 i j k
72 1 ["a", 4] [3, 4]
73 # Decoded rows
74 UPDATE `test`.`t` SET j='["a", 4]' WHERE i=1;
75 # Decoded rows, full image
76 UPDATE `test`.`t` SET i=1, j='["a", 4]', k=k WHERE i=1 AND j=CAST('[1, 2]' AS JSON) AND k=CAST('[3, 4]' AS JSON);
77 # Decoded rows, minimal image when master has full image
78 UPDATE `test`.`t` SET i=1, j='["a", 4]', k=k WHERE i=1;
79 ---- 2. Update one row using full format (no JSON function used) ----
80 include/rpl_row_jsondiff_scenario.inc
81 * CREATE TABLE test.t (i INT PRIMARY KEY, j JSON)
82 # Before update
83 i j
84 1 [1, 2]
85 UPDATE t SET j = CAST(7 AS JSON) WHERE i = 1
86 # After update
87 i j
88 1 7
89 # Decoded rows
90 UPDATE `test`.`t` SET j='7' WHERE i=1;
91 # Decoded rows, full image
92 UPDATE `test`.`t` SET i=1, j='7' WHERE i=1 AND j=CAST('[1, 2]' AS JSON);
93 # Decoded rows, minimal image when master has full image
94 UPDATE `test`.`t` SET i=1, j='7' WHERE i=1;
95 ---- 3. Update one row using full format (wrong JSON function used (1)) ----
96 include/rpl_row_jsondiff_scenario.inc
97 # Before update
98 i j
99 1 [1, 2]
100 UPDATE t SET j = JSON_MERGE(j, '[8]') WHERE i = 1
101 Warnings:
102 Warning 1287 'JSON_MERGE' is deprecated and will be removed in a future release. Please use JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead
103 # After update
104 i j
105 1 [1, 2, 8]
106 # Decoded rows
107 UPDATE `test`.`t` SET j='[1, 2, 8]' WHERE i=1;
108 # Decoded rows, full image
109 UPDATE `test`.`t` SET i=1, j='[1, 2, 8]' WHERE i=1 AND j=CAST('[1, 2]' AS JSON);
110 # Decoded rows, minimal image when master has full image
111 UPDATE `test`.`t` SET i=1, j='[1, 2, 8]' WHERE i=1;
112 ---- 4. Update one row using full format (wrong JSON function used (2)) ----
113 include/rpl_row_jsondiff_scenario.inc
114 # Before update
115 i j
116 1 [1, 2]
117 UPDATE t SET j = JSON_MERGE(JSON_REPLACE(j, '$[0]', 3), '[8]') WHERE i = 1
118 Warnings:
119 Warning 1287 'JSON_MERGE' is deprecated and will be removed in a future release. Please use JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead
120 # After update
121 i j
122 1 [3, 2, 8]
123 # Decoded rows
124 UPDATE `test`.`t` SET j='[3, 2, 8]' WHERE i=1;
125 # Decoded rows, full image
126 UPDATE `test`.`t` SET i=1, j='[3, 2, 8]' WHERE i=1 AND j=CAST('[1, 2]' AS JSON);
127 # Decoded rows, minimal image when master has full image
128 UPDATE `test`.`t` SET i=1, j='[3, 2, 8]' WHERE i=1;
129 ---- 5. Update one row using full format (wrong JSON function used (3)) ----
130 include/rpl_row_jsondiff_scenario.inc
131 # Before update
132 i j
133 1 [1, 2]
134 UPDATE t SET j = JSON_REPLACE(JSON_MERGE(j, '[8]'), '$[0]', 3) WHERE i = 1
135 Warnings:
136 Warning 1287 'JSON_MERGE' is deprecated and will be removed in a future release. Please use JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead
137 # After update
138 i j
139 1 [3, 2, 8]
140 # Decoded rows
141 UPDATE `test`.`t` SET j='[3, 2, 8]' WHERE i=1;
142 # Decoded rows, full image
143 UPDATE `test`.`t` SET i=1, j='[3, 2, 8]' WHERE i=1 AND j=CAST('[1, 2]' AS JSON);
144 # Decoded rows, minimal image when master has full image
145 UPDATE `test`.`t` SET i=1, j='[3, 2, 8]' WHERE i=1;
146 ---- 6. Update one row using full format (full smaller than partial) ----
147 include/rpl_row_jsondiff_scenario.inc
148 # Before update
149 i j
150 1 [1, 2]
151 UPDATE t SET j = JSON_SET(j, '$[0]', 'abcdefghijklmnopqrstuvwxyz', '$[0]', 3) WHERE i = 1
152 # After update
153 i j
154 1 [3, 2]
155 # Decoded rows
156 UPDATE `test`.`t` SET j='[3, 2]' WHERE i=1;
157 # Decoded rows, full image
158 UPDATE `test`.`t` SET i=1, j='[3, 2]' WHERE i=1 AND j=CAST('[1, 2]' AS JSON);
159 # Decoded rows, minimal image when master has full image
160 UPDATE `test`.`t` SET i=1, j='[3, 2]' WHERE i=1;
161 ==== Single diffs ====
162 ---- 7. Update one row (REPLACE using JSON_REPLACE in array) ----
163 include/rpl_row_jsondiff_scenario.inc
164 # Before update
165 i j
166 1 [1, {"a": 2}]
167 UPDATE t SET j = JSON_REPLACE(j, '$[0]', 7) WHERE i = 1
168 # After update
169 i j
170 1 [7, {"a": 2}]
171 # Decoded rows
172 UPDATE `test`.`t` SET j=JSON_REPLACE(j, '$[0]', 7) WHERE i=1;
173 # Decoded rows, full image
174 UPDATE `test`.`t` SET i=1, j=JSON_REPLACE(j, '$[0]', 7) WHERE i=1 AND j=CAST('[1, {"a": 2}]' AS JSON);
175 # Decoded rows, minimal image when master has full image
176 UPDATE `test`.`t` SET i=1, j=JSON_REPLACE(j, '$[0]', 7) WHERE i=1;
177 ---- 8. Update one row (REPLACE using JSON_SET in array) ----
178 include/rpl_row_jsondiff_scenario.inc
179 # Before update
180 i j
181 1 [1, {"a": 2}]
182 UPDATE t SET j = JSON_SET(j, '$[0]', "7") WHERE i = 1
183 # After update
184 i j
185 1 ["7", {"a": 2}]
186 # Decoded rows
187 UPDATE `test`.`t` SET j=JSON_REPLACE(j, '$[0]', '7') WHERE i=1;
188 # Decoded rows, full image
189 UPDATE `test`.`t` SET i=1, j=JSON_REPLACE(j, '$[0]', '7') WHERE i=1 AND j=CAST('[1, {"a": 2}]' AS JSON);
190 # Decoded rows, minimal image when master has full image
191 UPDATE `test`.`t` SET i=1, j=JSON_REPLACE(j, '$[0]', '7') WHERE i=1;
192 ---- 9. Update one row (REPLACE using JSON_REPLACE in object) ----
193 include/rpl_row_jsondiff_scenario.inc
194 # Before update
195 i j
196 1 [1, {"a": 2}]
197 UPDATE t SET j = JSON_REPLACE(j, '$[1].a', CAST('[7]' AS JSON)) WHERE i = 1
198 # After update
199 i j
200 1 [1, {"a": [7]}]
201 # Decoded rows
202 UPDATE `test`.`t` SET j=JSON_REPLACE(j, '$[1].a', CAST('[7]' AS JSON)) WHERE i=1;
203 # Decoded rows, full image
204 UPDATE `test`.`t` SET i=1, j=JSON_REPLACE(j, '$[1].a', CAST('[7]' AS JSON)) WHERE i=1 AND j=CAST('[1, {"a": 2}]' AS JSON);
205 # Decoded rows, minimal image when master has full image
206 UPDATE `test`.`t` SET i=1, j=JSON_REPLACE(j, '$[1].a', CAST('[7]' AS JSON)) WHERE i=1;
207 ---- 10. Update one row (REPLACE using JSON_SET in object) ----
208 include/rpl_row_jsondiff_scenario.inc
209 # Before update
210 i j
211 1 [1, {"a": 2}]
212 UPDATE t SET j = JSON_SET(j, '$[1].a', CAST('{"a":7}' AS JSON)) WHERE i = 1
213 # After update
214 i j
215 1 [1, {"a": {"a": 7}}]
216 # Decoded rows
217 UPDATE `test`.`t` SET j=JSON_REPLACE(j, '$[1].a', CAST('{"a": 7}' AS JSON)) WHERE i=1;
218 # Decoded rows, full image
219 UPDATE `test`.`t` SET i=1, j=JSON_REPLACE(j, '$[1].a', CAST('{"a": 7}' AS JSON)) WHERE i=1 AND j=CAST('[1, {"a": 2}]' AS JSON);
220 # Decoded rows, minimal image when master has full image
221 UPDATE `test`.`t` SET i=1, j=JSON_REPLACE(j, '$[1].a', CAST('{"a": 7}' AS JSON)) WHERE i=1;
222 ---- 11. Update one row (INSERT using JSON_SET in array) ----
223 include/rpl_row_jsondiff_scenario.inc
224 # Before update
225 i j
226 1 [1, {"a": 2}]
227 UPDATE t SET j = JSON_SET(j, '$[2]', 3) WHERE i = 1
228 # After update
229 i j
230 1 [1, {"a": 2}, 3]
231 # Decoded rows
232 UPDATE `test`.`t` SET j=JSON_SET(j, '$[2]', 3) WHERE i=1;
233 # Decoded rows, full image
234 UPDATE `test`.`t` SET i=1, j=JSON_SET(j, '$[2]', 3) WHERE i=1 AND j=CAST('[1, {"a": 2}]' AS JSON);
235 # Decoded rows, minimal image when master has full image
236 UPDATE `test`.`t` SET i=1, j=JSON_SET(j, '$[2]', 3) WHERE i=1;
237 ---- 12. Update one row (INSERT using JSON_SET in object) ----
238 include/rpl_row_jsondiff_scenario.inc
239 # Before update
240 i j
241 1 [1, {"a": 2}]
242 UPDATE t SET j = JSON_SET(j, '$[1].b', CAST(4 AS DECIMAL)) WHERE i = 1
243 # After update
244 i j
245 1 [1, {"a": 2, "b": 4}]
246 # Decoded rows
247 UPDATE `test`.`t` SET j=JSON_SET(j, '$[1].b', 4) WHERE i=1;
248 # Decoded rows, full image
249 UPDATE `test`.`t` SET i=1, j=JSON_SET(j, '$[1].b', 4) WHERE i=1 AND j=CAST('[1, {"a": 2}]' AS JSON);
250 # Decoded rows, minimal image when master has full image
251 UPDATE `test`.`t` SET i=1, j=JSON_SET(j, '$[1].b', 4) WHERE i=1;
252 ---- 13. Update one row (REMOVE using JSON_REMOVE in array) ----
253 include/rpl_row_jsondiff_scenario.inc
254 # Before update
255 i j
256 1 [1, {"a": 2}]
257 UPDATE t SET j = JSON_REMOVE(j, '$[1]') WHERE i = 1
258 # After update
259 i j
260 1 [1]
261 # Decoded rows
262 UPDATE `test`.`t` SET j=JSON_REMOVE(j, '$[1]') WHERE i=1;
263 # Decoded rows, full image
264 UPDATE `test`.`t` SET i=1, j=JSON_REMOVE(j, '$[1]') WHERE i=1 AND j=CAST('[1, {"a": 2}]' AS JSON);
265 # Decoded rows, minimal image when master has full image
266 UPDATE `test`.`t` SET i=1, j=JSON_REMOVE(j, '$[1]') WHERE i=1;
267 ---- 14. Update one row (REMOVE using JSON_REMOVE in object) ----
268 include/rpl_row_jsondiff_scenario.inc
269 # Before update
270 i j
271 1 [1, {"a": 2}]
272 UPDATE t SET j = JSON_REMOVE(j, '$[1].a') WHERE i = 1
273 # After update
274 i j
275 1 [1, {}]
276 # Decoded rows
277 UPDATE `test`.`t` SET j=JSON_REMOVE(j, '$[1].a') WHERE i=1;
278 # Decoded rows, full image
279 UPDATE `test`.`t` SET i=1, j=JSON_REMOVE(j, '$[1].a') WHERE i=1 AND j=CAST('[1, {"a": 2}]' AS JSON);
280 # Decoded rows, minimal image when master has full image
281 UPDATE `test`.`t` SET i=1, j=JSON_REMOVE(j, '$[1].a') WHERE i=1;
282 ==== Multiple diffs from one function call ====
283 ---- 15. Update one row (many different update types, using JSON_SET) ----
284 include/rpl_row_jsondiff_scenario.inc
285 # Before update
286 i j
287 1 [1, 2, {"a": 3, "b": 4, "a long string that ensures that the diff format is smaller than the full format": ""}]
288 UPDATE t SET j = JSON_SET(j, '$[0]', 'one', '$[1]', 'two', '$[2].a', 'three', '$[2].b', 'four', '$[2].c', 'five', '$[2].d', 'six', '$[3]', 'seven', '$[4]', 'eight') WHERE i = 1
289 # After update
290 i j
291 1 ["one", "two", {"a": "three", "b": "four", "c": "five", "d": "six", "a long string that ensures that the diff format is smaller than the full format": ""}, "seven", "eight"]
292 # Decoded rows
293 UPDATE `test`.`t` SET j=JSON_SET( JSON_SET( JSON_REPLACE(j, '$[0]', 'one', '$[1]', 'two', '$[2].a', 'three', '$[2].b', 'four'), '$[2].c', 'five', '$[2].d', 'six'), '$[3]', 'seven', '$[4]', 'eight') WHERE i=1;
294 # Decoded rows, full image
295 UPDATE `test`.`t` SET i=1, j=JSON_SET( JSON_SET( JSON_REPLACE(j, '$[0]', 'one', '$[1]', 'two', '$[2].a', 'three', '$[2].b', 'four'), '$[2].c', 'five', '$[2].d', 'six'), '$[3]', 'seven', '$[4]', 'eight') WHERE i=1 AND j=CAST('[1, 2, {"a": 3, "b": 4, "a long string that ensures that the diff format is smaller than the full format": ""}]' AS JSON);
296 # Decoded rows, minimal image when master has full image
297 UPDATE `test`.`t` SET i=1, j=JSON_SET( JSON_SET( JSON_REPLACE(j, '$[0]', 'one', '$[1]', 'two', '$[2].a', 'three', '$[2].b', 'four'), '$[2].c', 'five', '$[2].d', 'six'), '$[3]', 'seven', '$[4]', 'eight') WHERE i=1;
298 ---- 16. Update one row (many different update types, using JSON_REPLACE) ----
299 include/rpl_row_jsondiff_scenario.inc
300 # Before update
301 i j
302 1 [1, 2, {"a": 3, "b": 4, "a long string that ensures that the diff format is smaller than the full format": ""}]
303 UPDATE t SET j = JSON_REPLACE(j, '$[0]', 'one', '$[1]', 'two', '$[3].a', 'three', '$[3].b', 'four', '$[0]', 'ONE', '$[1]', 'TWO', '$[3].a', 'THREE', '$[3].b', 'FOUR') WHERE i = 1
304 # After update
305 i j
306 1 ["ONE", "TWO", {"a": 3, "b": 4, "a long string that ensures that the diff format is smaller than the full format": ""}]
307 # Decoded rows
308 UPDATE `test`.`t` SET j=JSON_REPLACE(j, '$[0]', 'one', '$[1]', 'two', '$[0]', 'ONE', '$[1]', 'TWO') WHERE i=1;
309 # Decoded rows, full image
310 UPDATE `test`.`t` SET i=1, j=JSON_REPLACE(j, '$[0]', 'one', '$[1]', 'two', '$[0]', 'ONE', '$[1]', 'TWO') WHERE i=1 AND j=CAST('[1, 2, {"a": 3, "b": 4, "a long string that ensures that the diff format is smaller than the full format": ""}]' AS JSON);
311 # Decoded rows, minimal image when master has full image
312 UPDATE `test`.`t` SET i=1, j=JSON_REPLACE(j, '$[0]', 'one', '$[1]', 'two', '$[0]', 'ONE', '$[1]', 'TWO') WHERE i=1;
313 ---- 17. Update one row (using JSON_REMOVE) ----
314 include/rpl_row_jsondiff_scenario.inc
315 # Before update
316 i j
317 1 [1, 2, {"a": 3, "b": 4, "a long string that ensures that the diff format is smaller than the full format": ""}]
318 UPDATE t SET j = JSON_REMOVE(j, '$[1]', '$[2].b', '$[2].a', '$[0]') WHERE i = 1
319 # After update
320 i j
321 1 [{"a": 3, "b": 4, "a long string that ensures that the diff format is smaller than the full format": ""}]
322 # Decoded rows
323 UPDATE `test`.`t` SET j=JSON_REMOVE(j, '$[1]', '$[0]') WHERE i=1;
324 # Decoded rows, full image
325 UPDATE `test`.`t` SET i=1, j=JSON_REMOVE(j, '$[1]', '$[0]') WHERE i=1 AND j=CAST('[1, 2, {"a": 3, "b": 4, "a long string that ensures that the diff format is smaller than the full format": ""}]' AS JSON);
326 # Decoded rows, minimal image when master has full image
327 UPDATE `test`.`t` SET i=1, j=JSON_REMOVE(j, '$[1]', '$[0]') WHERE i=1;
328 ==== Multiple diffs from multiple function calls ====
329 ---- 18. Update one row (using JSON_SET, JSON_REPLACE, and JSON_REMOVE) ----
330 include/rpl_row_jsondiff_scenario.inc
331 # Before update
332 i j
333 1 [1, 2, 3, "a long string that ensures that the diff format is smaller than the full format"]
334 UPDATE t SET j = JSON_REMOVE(JSON_SET(JSON_REPLACE(JSON_SET(j, '$[0]', 'one'), '$[1]', 'two', '$[2]', CAST('{"a": 1}' AS JSON)), '$[2].b', 2, '$[2].c', 3), '$[0]', '$[0]') WHERE i = 1
335 # After update
336 i j
337 1 [{"a": 1, "b": 2, "c": 3}, "a long string that ensures that the diff format is smaller than the full format"]
338 # Decoded rows
339 UPDATE `test`.`t` SET j=JSON_REMOVE( JSON_SET( JSON_REPLACE(j, '$[0]', 'one', '$[1]', 'two', '$[2]', CAST('{"a": 1}' AS JSON)), '$[2].b', 2, '$[2].c', 3), '$[0]', '$[0]') WHERE i=1;
340 # Decoded rows, full image
341 UPDATE `test`.`t` SET i=1, j=JSON_REMOVE( JSON_SET( JSON_REPLACE(j, '$[0]', 'one', '$[1]', 'two', '$[2]', CAST('{"a": 1}' AS JSON)), '$[2].b', 2, '$[2].c', 3), '$[0]', '$[0]') WHERE i=1 AND j=CAST('[1, 2, 3, "a long string that ensures that the diff format is smaller than the full format"]' AS JSON);
342 # Decoded rows, minimal image when master has full image
343 UPDATE `test`.`t` SET i=1, j=JSON_REMOVE( JSON_SET( JSON_REPLACE(j, '$[0]', 'one', '$[1]', 'two', '$[2]', CAST('{"a": 1}' AS JSON)), '$[2].b', 2, '$[2].c', 3), '$[0]', '$[0]') WHERE i=1;
344 ==== NULL values ====
345 ---- 19. Updating from non-NULL to NULL using JSON_SET ----
346 include/rpl_row_jsondiff_scenario.inc
347 # Before update
348 i j
349 1 [1, 2, 3, 4]
350 UPDATE t SET j = JSON_SET(j, NULL, 'a') WHERE i = 1
351 # After update
352 i j
353 1 NULL
354 # Decoded rows
355 UPDATE `test`.`t` SET j=NULL WHERE i=1;
356 # Decoded rows, full image
357 UPDATE `test`.`t` SET i=1, j=NULL WHERE i=1 AND j=CAST('[1, 2, 3, 4]' AS JSON);
358 # Decoded rows, minimal image when master has full image
359 UPDATE `test`.`t` SET i=1, j=NULL WHERE i=1;
360 ---- 20. Updating from non-NULL to NULL using JSON_REPLACE ----
361 include/rpl_row_jsondiff_scenario.inc
362 # Before update
363 i j
364 1 [1, 2, 3, 4]
365 UPDATE t SET j = JSON_REPLACE(j, NULL, 'a') WHERE i = 1
366 # After update
367 i j
368 1 NULL
369 # Decoded rows
370 UPDATE `test`.`t` SET j=NULL WHERE i=1;
371 # Decoded rows, full image
372 UPDATE `test`.`t` SET i=1, j=NULL WHERE i=1 AND j=CAST('[1, 2, 3, 4]' AS JSON);
373 # Decoded rows, minimal image when master has full image
374 UPDATE `test`.`t` SET i=1, j=NULL WHERE i=1;
375 ---- 21. Updating from non-NULL to NULL using JSON_REMOVE ----
376 include/rpl_row_jsondiff_scenario.inc
377 # Before update
378 i j
379 1 [1, 2, 3, 4]
380 UPDATE t SET j = JSON_REMOVE(j, NULL) WHERE i = 1
381 # After update
382 i j
383 1 NULL
384 # Decoded rows
385 UPDATE `test`.`t` SET j=NULL WHERE i=1;
386 # Decoded rows, full image
387 UPDATE `test`.`t` SET i=1, j=NULL WHERE i=1 AND j=CAST('[1, 2, 3, 4]' AS JSON);
388 # Decoded rows, minimal image when master has full image
389 UPDATE `test`.`t` SET i=1, j=NULL WHERE i=1;
390 ---- 22. Updating from NULL to non-NULL using JSON_SET ----
391 include/rpl_row_jsondiff_scenario.inc
392 # Before update
393 i j
394 1 NULL
395 UPDATE t SET j = JSON_SET('{"a": "a"}', '$.b', j) WHERE i = 1
396 # After update
397 i j
398 1 {"a": "a", "b": null}
399 # Decoded rows
400 UPDATE `test`.`t` SET j='{"a": "a", "b": null}' WHERE i=1;
401 # Decoded rows, full image
402 UPDATE `test`.`t` SET i=1, j='{"a": "a", "b": null}' WHERE i=1 AND j IS NULL;
403 # Decoded rows, minimal image when master has full image
404 UPDATE `test`.`t` SET i=1, j='{"a": "a", "b": null}' WHERE i=1;
405 ---- 23. Updating from NULL to non-NULL using JSON_REPLACE ----
406 include/rpl_row_jsondiff_scenario.inc
407 # Before update
408 i j
409 1 NULL
410 UPDATE t SET j = JSON_REPLACE('{"a": "a"}', '$.a', j) WHERE i = 1
411 # After update
412 i j
413 1 {"a": null}
414 # Decoded rows
415 UPDATE `test`.`t` SET j='{"a": null}' WHERE i=1;
416 # Decoded rows, full image
417 UPDATE `test`.`t` SET i=1, j='{"a": null}' WHERE i=1 AND j IS NULL;
418 # Decoded rows, minimal image when master has full image
419 UPDATE `test`.`t` SET i=1, j='{"a": null}' WHERE i=1;
420 ---- 24. Updating from NULL to non-NULL using JSON_REMOVE ----
421 include/rpl_row_jsondiff_scenario.inc
422 # Before update
423 i j
424 1 NULL
425 UPDATE t SET j = JSON_REPLACE('{"a": "a", "b": "b"}', '$.b', 'BB') WHERE i = 1
426 # After update
427 i j
428 1 {"a": "a", "b": "BB"}
429 # Decoded rows
430 UPDATE `test`.`t` SET j='{"a": "a", "b": "BB"}' WHERE i=1;
431 # Decoded rows, full image
432 UPDATE `test`.`t` SET i=1, j='{"a": "a", "b": "BB"}' WHERE i=1 AND j IS NULL;
433 # Decoded rows, minimal image when master has full image
434 UPDATE `test`.`t` SET i=1, j='{"a": "a", "b": "BB"}' WHERE i=1;
435 ==== No-op updates ====
436 ---- 25. Making a no-op not mentioning the column ----
437 include/rpl_row_jsondiff_scenario.inc
438 * CREATE TABLE test.t (i INT PRIMARY KEY, j JSON, x INT)
439 # Before update
440 i j x
441 1 ["a", {"b": "c"}] 1
442 UPDATE t SET x = 2 WHERE i = 1
443 # After update
444 i j x
445 1 ["a", {"b": "c"}] 2
446 # Decoded rows
447 UPDATE `test`.`t` SET x=2 WHERE i=1;
448 # Decoded rows, full image
449 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1 AND j=CAST('["a", {"b": "c"}]' AS JSON) AND x=1;
450 # Decoded rows, minimal image when master has full image
451 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1;
452 ---- 26. Making a no-op setting the column to itself ----
453 include/rpl_row_jsondiff_scenario.inc
454 # Before update
455 i j x
456 1 ["a", {"b": "c"}] 1
457 UPDATE t SET x = 2, j = j WHERE i = 1
458 # After update
459 i j x
460 1 ["a", {"b": "c"}] 2
461 # Decoded rows
462 UPDATE `test`.`t` SET j=j, x=2 WHERE i=1;
463 # Decoded rows, full image
464 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1 AND j=CAST('["a", {"b": "c"}]' AS JSON) AND x=1;
465 # Decoded rows, minimal image when master has full image
466 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1;
467 ---- 27. Making a no-op setting the column to its own value (1) ----
468 include/rpl_row_jsondiff_scenario.inc
469 # Before update
470 i j x
471 1 ["a", {"b": "c"}] 1
472 UPDATE t SET x = 2, j = CONCAT(j, '') WHERE i = 1
473 # After update
474 i j x
475 1 ["a", {"b": "c"}] 2
476 # Decoded rows
477 UPDATE `test`.`t` SET j=j, x=2 WHERE i=1;
478 # Decoded rows, full image
479 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1 AND j=CAST('["a", {"b": "c"}]' AS JSON) AND x=1;
480 # Decoded rows, minimal image when master has full image
481 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1;
482 ---- 28. Making a no-op setting the column to its own value (2) ----
483 include/rpl_row_jsondiff_scenario.inc
484 # Before update
485 i j x
486 1 ["a", {"b": "c"}] 1
487 UPDATE t SET x = 2, j = '["a", {"b": "c"}]' WHERE i = 1
488 # After update
489 i j x
490 1 ["a", {"b": "c"}] 2
491 # Decoded rows
492 UPDATE `test`.`t` SET j=j, x=2 WHERE i=1;
493 # Decoded rows, full image
494 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1 AND j=CAST('["a", {"b": "c"}]' AS JSON) AND x=1;
495 # Decoded rows, minimal image when master has full image
496 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1;
497 ---- 29. Making a no-op using JSON_SET inserting object in non-object or array element in non-array ----
498 include/rpl_row_jsondiff_scenario.inc
499 # Before update
500 i j x
501 1 ["a", {"b": "c"}] 1
502 UPDATE t SET x = 2, j = JSON_SET(j, '$.a', 0, '$[1][2]', 1, '$[1].b.x', 2) WHERE i = 1
503 # After update
504 i j x
505 1 ["a", [{"b": "c"}, 1]] 2
506 # Decoded rows
507 UPDATE `test`.`t` SET j=JSON_REPLACE(j, '$[1]', CAST('[{"b": "c"}, 1]' AS JSON)), x=2 WHERE i=1;
508 # Decoded rows, full image
509 UPDATE `test`.`t` SET i=1, j=JSON_REPLACE(j, '$[1]', CAST('[{"b": "c"}, 1]' AS JSON)), x=2 WHERE i=1 AND j=CAST('["a", {"b": "c"}]' AS JSON) AND x=1;
510 # Decoded rows, minimal image when master has full image
511 UPDATE `test`.`t` SET i=1, j=JSON_REPLACE(j, '$[1]', CAST('[{"b": "c"}, 1]' AS JSON)), x=2 WHERE i=1;
512 ---- 30. Making a no-op using JSON_REPLACE with non-existing paths ----
513 include/rpl_row_jsondiff_scenario.inc
514 # Before update
515 i j x
516 1 ["a", {"b": "c"}] 1
517 UPDATE t SET x = 2, j = JSON_REPLACE(j, '$[9]', 0, '$.x', 1, '$[1].x', 2) WHERE i = 1
518 # After update
519 i j x
520 1 ["a", {"b": "c"}] 2
521 # Decoded rows
522 UPDATE `test`.`t` SET j=j, x=2 WHERE i=1;
523 # Decoded rows, full image
524 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1 AND j=CAST('["a", {"b": "c"}]' AS JSON) AND x=1;
525 # Decoded rows, minimal image when master has full image
526 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1;
527 ---- 31. Making a no-op removing a non-existing path, using JSON_REMOVE ----
528 include/rpl_row_jsondiff_scenario.inc
529 # Before update
530 i j x
531 1 ["a", {"b": "c"}] 1
532 UPDATE t SET x = 2, j = JSON_REMOVE(j, '$[9]', '$[1].x', '$.foo', '$[1][0]') WHERE i = 1
533 # After update
534 i j x
535 1 ["a"] 2
536 # Decoded rows
537 UPDATE `test`.`t` SET j=JSON_REMOVE(j, '$[1]'), x=2 WHERE i=1;
538 # Decoded rows, full image
539 UPDATE `test`.`t` SET i=1, j=JSON_REMOVE(j, '$[1]'), x=2 WHERE i=1 AND j=CAST('["a", {"b": "c"}]' AS JSON) AND x=1;
540 # Decoded rows, minimal image when master has full image
541 UPDATE `test`.`t` SET i=1, j=JSON_REMOVE(j, '$[1]'), x=2 WHERE i=1;
542 ---- 32. Making a no-op replacing a JSON value by itself, using JSON_SET ----
543 include/rpl_row_jsondiff_scenario.inc
544 # Before update
545 i j x
546 1 ["a", {"b": "c"}] 1
547 UPDATE t SET x = 2, j = JSON_SET(j, '$[0]', 'a', '$[1].b', 'c', '$[0]', 'a') WHERE i = 1
548 # After update
549 i j x
550 1 ["a", {"b": "c"}] 2
551 # Decoded rows
552 UPDATE `test`.`t` SET j=j, x=2 WHERE i=1;
553 # Decoded rows, full image
554 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1 AND j=CAST('["a", {"b": "c"}]' AS JSON) AND x=1;
555 # Decoded rows, minimal image when master has full image
556 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1;
557 ---- 33. Making a no-op replacing a JSON value by itself, using JSON_REPLACE ----
558 include/rpl_row_jsondiff_scenario.inc
559 # Before update
560 i j x
561 1 ["a", {"b": "c"}] 1
562 UPDATE t SET x = 2, j = JSON_REPLACE(j, '$[0]', 'a', '$[1].b', 'c', '$[0]', 'a') WHERE i = 1
563 # After update
564 i j x
565 1 ["a", {"b": "c"}] 2
566 # Decoded rows
567 UPDATE `test`.`t` SET j=j, x=2 WHERE i=1;
568 # Decoded rows, full image
569 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1 AND j=CAST('["a", {"b": "c"}]' AS JSON) AND x=1;
570 # Decoded rows, minimal image when master has full image
571 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1;
572 ---- 34. Making a no-op update using JSON_SET, JSON_REPLACE, and JSON_REMOVE ----
573 include/rpl_row_jsondiff_scenario.inc
574 # Before update
575 i j x
576 1 ["a", {"b": "c"}] 1
577 UPDATE t SET x = 2, j = JSON_SET(JSON_REMOVE(JSON_REPLACE(j,
578 '$[0]', 'a', '$[1].b', 'c', '$[0]', 'a'),
579 '$[9]', '$[1].x', '$.foo'),
580 '$[0]', 'a', '$[1].b', 'c', '$[0]', 'a') WHERE i = 1
581 # After update
582 i j x
583 1 ["a", {"b": "c"}] 2
584 # Decoded rows
585 UPDATE `test`.`t` SET j=j, x=2 WHERE i=1;
586 # Decoded rows, full image
587 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1 AND j=CAST('["a", {"b": "c"}]' AS JSON) AND x=1;
588 # Decoded rows, minimal image when master has full image
589 UPDATE `test`.`t` SET i=1, j=j, x=2 WHERE i=1;
590 ==== Long paths ====
591 ---- 35. Update using long path ----
592 include/rpl_row_jsondiff_scenario.inc
593 * CREATE TABLE test.t (i INT PRIMARY KEY, j JSON)
594 # Before update
595 i j
596 1 {"abc": "def"}
597 UPDATE t SET j = JSON_SET(j, '$.aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', 'ghi') WHERE i = 1
598 # After update
599 i j
600 1 {"abc": "def", "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa": "ghi"}
601 # Decoded rows
602 UPDATE `test`.`t` SET j=JSON_SET(j, '$.aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', 'ghi') WHERE i=1;
603 # Decoded rows, full image
604 UPDATE `test`.`t` SET i=1, j=JSON_SET(j, '$.aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', 'ghi') WHERE i=1 AND j=CAST('{"abc": "def"}' AS JSON);
605 # Decoded rows, minimal image when master has full image
606 UPDATE `test`.`t` SET i=1, j=JSON_SET(j, '$.aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', 'ghi') WHERE i=1;
607 ---- 36. Update using long document ----
608 include/rpl_row_jsondiff_scenario.inc
609 # Before update
610 i j
611 1 {"abc": "def"}
612 UPDATE t SET j = JSON_SET(j, '$.ghi', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') WHERE i = 1
613 # After update
614 i j
615 1 {"abc": "def", "ghi": "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"}
616 # Decoded rows
617 UPDATE `test`.`t` SET j=JSON_SET(j, '$.ghi', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') WHERE i=1;
618 # Decoded rows, full image
619 UPDATE `test`.`t` SET i=1, j=JSON_SET(j, '$.ghi', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') WHERE i=1 AND j=CAST('{"abc": "def"}' AS JSON);
620 # Decoded rows, minimal image when master has full image
621 UPDATE `test`.`t` SET i=1, j=JSON_SET(j, '$.ghi', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') WHERE i=1;
622 ---- 37. Update using very long document ----
623 include/rpl_row_jsondiff_scenario.inc
624 ==== Auto-wrapping ====
625 ---- 38. Auto-wrap using JSON_SET ----
626 include/rpl_row_jsondiff_scenario.inc
627 # Before update
628 i j
629 1 {"a": 1}
630 UPDATE t SET j = JSON_SET(j, '$[1]', 4711) WHERE i = 1
631 # After update
632 i j
633 1 [{"a": 1}, 4711]
634 # Decoded rows
635 UPDATE `test`.`t` SET j='[{"a": 1}, 4711]' WHERE i=1;
636 # Decoded rows, full image
637 UPDATE `test`.`t` SET i=1, j='[{"a": 1}, 4711]' WHERE i=1 AND j=CAST('{"a": 1}' AS JSON);
638 # Decoded rows, minimal image when master has full image
639 UPDATE `test`.`t` SET i=1, j='[{"a": 1}, 4711]' WHERE i=1;
640 ==== Multi-row updates ====
641 ---- 39. Update two rows: both partial ----
642 include/rpl_row_jsondiff_scenario.inc
643 # Before update
644 i j
645 1 [1, 2, 3]
646 2 [4, 5, 6, "a long string that ensures that the diff format is smaller than the full format"]
647 3 [7, 8, 9]
648 4 [10, 11, 12, "a long string that ensures that the diff format is smaller than the full format"]
649 UPDATE t SET j = JSON_SET(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i = 2 OR i = 4
650 # After update
651 i j
652 1 [1, 2, 3]
653 2 [0, 5, 6, "a long string that ensures that the diff format is smaller than the full format"]
654 3 [7, 8, 9]
655 4 [0, 11, 12, "a long string that ensures that the diff format is smaller than the full format"]
656 # Decoded rows
657 UPDATE `test`.`t` SET j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=2;
658 UPDATE `test`.`t` SET j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=4;
659 # Decoded rows, full image
660 UPDATE `test`.`t` SET i=2, j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=2 AND j=CAST('[4, 5, 6, "a long string that ensures that the diff format is smaller than the full format"]' AS JSON);
661 UPDATE `test`.`t` SET i=4, j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=4 AND j=CAST('[10, 11, 12, "a long string that ensures that the diff format is smaller than the full format"]' AS JSON);
662 # Decoded rows, minimal image when master has full image
663 UPDATE `test`.`t` SET i=2, j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=2;
664 UPDATE `test`.`t` SET i=4, j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=4;
665 ---- 40. Update two rows: first partial, then full ----
666 include/rpl_row_jsondiff_scenario.inc
667 # Before update
668 i j
669 1 [1, 2, 3]
670 2 [4, 5, 6, "a long string that ensures that the diff format is smaller than the full format"]
671 3 [7, 8, 9]
672 4 [10, 11, 12, "a long string that ensures that the diff format is smaller than the full format"]
673 UPDATE t SET j = JSON_SET(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i = 2 OR i = 3
674 # After update
675 i j
676 1 [1, 2, 3]
677 2 [0, 5, 6, "a long string that ensures that the diff format is smaller than the full format"]
678 3 [0, 8, 9]
679 4 [10, 11, 12, "a long string that ensures that the diff format is smaller than the full format"]
680 # Decoded rows
681 UPDATE `test`.`t` SET j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=2;
682 UPDATE `test`.`t` SET j='[0, 8, 9]' WHERE i=3;
683 # Decoded rows, full image
684 UPDATE `test`.`t` SET i=2, j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=2 AND j=CAST('[4, 5, 6, "a long string that ensures that the diff format is smaller than the full format"]' AS JSON);
685 UPDATE `test`.`t` SET i=3, j='[0, 8, 9]' WHERE i=3 AND j=CAST('[7, 8, 9]' AS JSON);
686 # Decoded rows, minimal image when master has full image
687 UPDATE `test`.`t` SET i=2, j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=2;
688 UPDATE `test`.`t` SET i=3, j='[0, 8, 9]' WHERE i=3;
689 ---- 41. Update two rows: first full, then partial ----
690 include/rpl_row_jsondiff_scenario.inc
691 # Before update
692 i j
693 1 [1, 2, 3]
694 2 [4, 5, 6, "a long string that ensures that the diff format is smaller than the full format"]
695 3 [7, 8, 9]
696 4 [10, 11, 12, "a long string that ensures that the diff format is smaller than the full format"]
697 UPDATE t SET j = JSON_SET(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i = 1 OR i = 2
698 # After update
699 i j
700 1 [0, 2, 3]
701 2 [0, 5, 6, "a long string that ensures that the diff format is smaller than the full format"]
702 3 [7, 8, 9]
703 4 [10, 11, 12, "a long string that ensures that the diff format is smaller than the full format"]
704 # Decoded rows
705 UPDATE `test`.`t` SET j='[0, 2, 3]' WHERE i=1;
706 UPDATE `test`.`t` SET j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=2;
707 # Decoded rows, full image
708 UPDATE `test`.`t` SET i=1, j='[0, 2, 3]' WHERE i=1 AND j=CAST('[1, 2, 3]' AS JSON);
709 UPDATE `test`.`t` SET i=2, j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=2 AND j=CAST('[4, 5, 6, "a long string that ensures that the diff format is smaller than the full format"]' AS JSON);
710 # Decoded rows, minimal image when master has full image
711 UPDATE `test`.`t` SET i=1, j='[0, 2, 3]' WHERE i=1;
712 UPDATE `test`.`t` SET i=2, j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=2;
713 ---- 42. Update two rows: both full ----
714 include/rpl_row_jsondiff_scenario.inc
715 # Before update
716 i j
717 1 [1, 2, 3]
718 2 [4, 5, 6, "a long string that ensures that the diff format is smaller than the full format"]
719 3 [7, 8, 9]
720 4 [10, 11, 12, "a long string that ensures that the diff format is smaller than the full format"]
721 UPDATE t SET j = JSON_SET(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i = 1 OR i = 3
722 # After update
723 i j
724 1 [0, 2, 3]
725 2 [4, 5, 6, "a long string that ensures that the diff format is smaller than the full format"]
726 3 [0, 8, 9]
727 4 [10, 11, 12, "a long string that ensures that the diff format is smaller than the full format"]
728 # Decoded rows
729 UPDATE `test`.`t` SET j='[0, 2, 3]' WHERE i=1;
730 UPDATE `test`.`t` SET j='[0, 8, 9]' WHERE i=3;
731 # Decoded rows, full image
732 UPDATE `test`.`t` SET i=1, j='[0, 2, 3]' WHERE i=1 AND j=CAST('[1, 2, 3]' AS JSON);
733 UPDATE `test`.`t` SET i=3, j='[0, 8, 9]' WHERE i=3 AND j=CAST('[7, 8, 9]' AS JSON);
734 # Decoded rows, minimal image when master has full image
735 UPDATE `test`.`t` SET i=1, j='[0, 2, 3]' WHERE i=1;
736 UPDATE `test`.`t` SET i=3, j='[0, 8, 9]' WHERE i=3;
737 ---- 43. Update four rows: full, partial, full, partial ----
738 include/rpl_row_jsondiff_scenario.inc
739 # Before update
740 i j
741 1 [1, 2, 3]
742 2 [4, 5, 6, "a long string that ensures that the diff format is smaller than the full format"]
743 3 [7, 8, 9]
744 4 [10, 11, 12, "a long string that ensures that the diff format is smaller than the full format"]
745 UPDATE t SET j = JSON_SET(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i IN (1, 2, 3, 4)
746 # After update
747 i j
748 1 [0, 2, 3]
749 2 [0, 5, 6, "a long string that ensures that the diff format is smaller than the full format"]
750 3 [0, 8, 9]
751 4 [0, 11, 12, "a long string that ensures that the diff format is smaller than the full format"]
752 # Decoded rows
753 UPDATE `test`.`t` SET j='[0, 2, 3]' WHERE i=1;
754 UPDATE `test`.`t` SET j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=2;
755 UPDATE `test`.`t` SET j='[0, 8, 9]' WHERE i=3;
756 UPDATE `test`.`t` SET j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=4;
757 # Decoded rows, full image
758 UPDATE `test`.`t` SET i=1, j='[0, 2, 3]' WHERE i=1 AND j=CAST('[1, 2, 3]' AS JSON);
759 UPDATE `test`.`t` SET i=2, j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=2 AND j=CAST('[4, 5, 6, "a long string that ensures that the diff format is smaller than the full format"]' AS JSON);
760 UPDATE `test`.`t` SET i=3, j='[0, 8, 9]' WHERE i=3 AND j=CAST('[7, 8, 9]' AS JSON);
761 UPDATE `test`.`t` SET i=4, j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=4 AND j=CAST('[10, 11, 12, "a long string that ensures that the diff format is smaller than the full format"]' AS JSON);
762 # Decoded rows, minimal image when master has full image
763 UPDATE `test`.`t` SET i=1, j='[0, 2, 3]' WHERE i=1;
764 UPDATE `test`.`t` SET i=2, j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=2;
765 UPDATE `test`.`t` SET i=3, j='[0, 8, 9]' WHERE i=3;
766 UPDATE `test`.`t` SET i=4, j=JSON_REPLACE(j, '$[0]', 'abcdefghijklmnopqrstuvxyz', '$[0]', 0) WHERE i=4;
767 ==== Invalid paths ====
768 TODO
769 ******** Multiple JSON columns ********
770 ---- 44. Update after null and after non-modified column ----
771 include/rpl_row_jsondiff_scenario.inc
772 * CREATE TABLE test.t (i INT PRIMARY KEY, j JSON, k JSON, l JSON, m JSON)
773 # Before update
774 i j k l m
775 1 [1, 2, 3, 4] [5, 6, 7, 8] {"a": 1, "b": 2, "c": 3, "d": 4} {"e": 8, "f": 6, "g": 7}
776 UPDATE t SET j = NULL, l = JSON_SET(l, '$.d', "DDD")
777 # After update
778 i j k l m
779 1 NULL [5, 6, 7, 8] {"a": 1, "b": 2, "c": 3, "d": "DDD"} {"e": 8, "f": 6, "g": 7}
780 # Decoded rows
781 UPDATE `test`.`t` SET j=NULL, l=JSON_REPLACE(l, '$.d', 'DDD') WHERE i=1;
782 # Decoded rows, full image
783 UPDATE `test`.`t` SET i=1, j=NULL, k=k, l=JSON_REPLACE(l, '$.d', 'DDD'), m=m WHERE i=1 AND j=CAST('[1, 2, 3, 4]' AS JSON) AND k=CAST('[5, 6, 7, 8]' AS JSON) AND l=CAST('{"a": 1, "b": 2, "c": 3, "d": 4}' AS JSON) AND m=CAST('{"e": 8, "f": 6, "g": 7}' AS JSON);
784 # Decoded rows, minimal image when master has full image
785 UPDATE `test`.`t` SET i=1, j=NULL, k=k, l=JSON_REPLACE(l, '$.d', 'DDD'), m=m WHERE i=1;
786 ******** Differences between master and slave ********
787 TODO: applies ok, does not apply, and mix
788 ######## CLEAN UP ########
789 include/rpl_for_each_server_stmt.inc [SET @@SESSION.BINLOG_ROW_IMAGE = FULL;
790 SET @@SESSION.BINLOG_ROW_VALUE_OPTIONS = '';
791 SET @@GLOBAL.BINLOG_ROW_IMAGE = FULL;
792 SET @@GLOBAL.BINLOG_ROW_VALUE_OPTIONS = '';
793 SET SESSION SQL_LOG_BIN = 0; DROP TABLE test.t; SET SESSION SQL_LOG_BIN = 1;
794 ]
795 include/rpl_stop_slaves.inc
796 include/end_replace_combination.inc [BOTH.BINLOG_FORMAT=ROW,MIXED,STATEMENT -> GLOBAL.SLAVE_ROWS_SEARCH_ALGORITHMS=INDEX_SCAN,HASH_SCAN,TABLE_SCAN]
797 Warnings:
798 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
799 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
800 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
801 Warnings:
802 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
803 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
804 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
805 Warnings:
806 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
807 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
808 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
809 Warnings:
810 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
811 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
812 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
813 Warnings:
814 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
815 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
816 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
817 Warnings:
818 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
819 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
820 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
821 Warnings:
822 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
823 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
824 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
825 Warnings:
826 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
827 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
828 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
829 include/rpl_start_slaves.inc
830 include/rpl_end.inc