Debian Codegraph @

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

raw
1 include/master-slave.inc
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 [connection master]
6 SET SESSION sql_log_bin= 0;
7 call mtr.add_suppression('.*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*');
8 SET SESSION sql_log_bin= 1;
9 SET SESSION sql_log_bin= 0;
10 call mtr.add_suppression('.*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*');
11 SET SESSION sql_log_bin= 1;
12 #
13 # WL#8132 JSON datatype and binary storage format
14 #
15 CREATE TABLE t1 (i INT PRIMARY KEY, j JSON);
16 INSERT INTO t1 VALUES (0, NULL);
17 INSERT INTO t1 VALUES (1, '{"a": 2}');
18 INSERT INTO t1 VALUES (2, '[1,2]');
19 INSERT INTO t1 VALUES (3, '{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}');
20 INSERT INTO t1 VALUES (4, '["here", ["I", "am"], "!!!"]');
21 INSERT INTO t1 VALUES (5, '"scalar string"');
22 INSERT INTO t1 VALUES (6, 'true');
23 INSERT INTO t1 VALUES (7, 'false');
24 INSERT INTO t1 VALUES (8, 'null');
25 INSERT INTO t1 VALUES (9, '-1');
26 INSERT INTO t1 VALUES (10, CAST(CAST(1 AS UNSIGNED) AS JSON));
27 INSERT INTO t1 VALUES (11, '32767');
28 INSERT INTO t1 VALUES (12, '32768');
29 INSERT INTO t1 VALUES (13, '-32768');
30 INSERT INTO t1 VALUES (14, '-32769');
31 INSERT INTO t1 VALUES (15, '2147483647');
32 INSERT INTO t1 VALUES (16, '2147483648');
33 INSERT INTO t1 VALUES (17, '-2147483648');
34 INSERT INTO t1 VALUES (18, '-2147483649');
35 INSERT INTO t1 VALUES (19, '18446744073709551615');
36 INSERT INTO t1 VALUES (20, '18446744073709551616');
37 INSERT INTO t1 VALUES (21, '3.14');
38 INSERT INTO t1 VALUES (22, '{}');
39 INSERT INTO t1 VALUES (23, '[]');
40 INSERT INTO t1 VALUES (24, CAST(CAST('2015-01-15 23:24:25' AS DATETIME) AS JSON));
41 INSERT INTO t1 VALUES (25, CAST(CAST('23:24:25' AS TIME) AS JSON));
42 INSERT INTO t1 VALUES (26, CAST(CAST('2015-01-15' AS DATE) AS JSON));
43 INSERT INTO t1 VALUES (27, CAST(TIMESTAMP'2015-01-15 23:24:25' AS JSON));
44 INSERT INTO t1 VALUES (28, CAST(ST_GeomFromText('POINT(1 1)') AS JSON));
45 INSERT INTO t1 VALUES (29, CAST('[]' AS CHAR CHARACTER SET 'ascii'));
46 INSERT INTO t1 VALUES (30, CAST(x'cafebabe' AS JSON));
47 SELECT * FROM t1 ORDER BY i;
48 i j
49 0 NULL
50 1 {"a": 2}
51 2 [1, 2]
52 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
53 4 ["here", ["I", "am"], "!!!"]
54 5 "scalar string"
55 6 true
56 7 false
57 8 null
58 9 -1
59 10 1
60 11 32767
61 12 32768
62 13 -32768
63 14 -32769
64 15 2147483647
65 16 2147483648
66 17 -2147483648
67 18 -2147483649
68 19 18446744073709551615
69 20 1.8446744073709552e19
70 21 3.14
71 22 {}
72 23 []
73 24 "2015-01-15 23:24:25.000000"
74 25 "23:24:25.000000"
75 26 "2015-01-15"
76 27 "2015-01-15 23:24:25.000000"
77 28 {"type": "Point", "coordinates": [1.0, 1.0]}
78 29 []
79 30 "base64:type15:yv66vg=="
80 # Copy JSON values
81 CREATE TABLE t2 (i INT PRIMARY KEY, j JSON);
82 INSERT INTO t2 SELECT * FROM t1;
83 SELECT *, JSON_TYPE(j) FROM t2 ORDER BY i;
84 i j JSON_TYPE(j)
85 0 NULL NULL
86 1 {"a": 2} OBJECT
87 2 [1, 2] ARRAY
88 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} OBJECT
89 4 ["here", ["I", "am"], "!!!"] ARRAY
90 5 "scalar string" STRING
91 6 true BOOLEAN
92 7 false BOOLEAN
93 8 null NULL
94 9 -1 INTEGER
95 10 1 UNSIGNED INTEGER
96 11 32767 INTEGER
97 12 32768 INTEGER
98 13 -32768 INTEGER
99 14 -32769 INTEGER
100 15 2147483647 INTEGER
101 16 2147483648 INTEGER
102 17 -2147483648 INTEGER
103 18 -2147483649 INTEGER
104 19 18446744073709551615 UNSIGNED INTEGER
105 20 1.8446744073709552e19 DOUBLE
106 21 3.14 DOUBLE
107 22 {} OBJECT
108 23 [] ARRAY
109 24 "2015-01-15 23:24:25.000000" DATETIME
110 25 "23:24:25.000000" TIME
111 26 "2015-01-15" DATE
112 27 "2015-01-15 23:24:25.000000" DATETIME
113 28 {"type": "Point", "coordinates": [1.0, 1.0]} OBJECT
114 29 [] ARRAY
115 30 "base64:type15:yv66vg==" BLOB
116 # Convert JSON values to TEXT
117 CREATE TABLE t3 (i INT PRIMARY KEY, txt TEXT);
118 INSERT INTO t3 SELECT * FROM t1;
119 SELECT *, JSON_TYPE(txt) FROM t3 ORDER BY i;
120 i txt JSON_TYPE(txt)
121 0 NULL NULL
122 1 {"a": 2} OBJECT
123 2 [1, 2] ARRAY
124 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} OBJECT
125 4 ["here", ["I", "am"], "!!!"] ARRAY
126 5 "scalar string" STRING
127 6 true BOOLEAN
128 7 false BOOLEAN
129 8 null NULL
130 9 -1 INTEGER
131 10 1 INTEGER
132 11 32767 INTEGER
133 12 32768 INTEGER
134 13 -32768 INTEGER
135 14 -32769 INTEGER
136 15 2147483647 INTEGER
137 16 2147483648 INTEGER
138 17 -2147483648 INTEGER
139 18 -2147483649 INTEGER
140 19 18446744073709551615 UNSIGNED INTEGER
141 20 1.8446744073709552e19 DOUBLE
142 21 3.14 DOUBLE
143 22 {} OBJECT
144 23 [] ARRAY
145 24 "2015-01-15 23:24:25.000000" STRING
146 25 "23:24:25.000000" STRING
147 26 "2015-01-15" STRING
148 27 "2015-01-15 23:24:25.000000" STRING
149 28 {"type": "Point", "coordinates": [1.0, 1.0]} OBJECT
150 29 [] ARRAY
151 30 "base64:type15:yv66vg==" STRING
152 # Convert TEXT values back to JSON
153 CREATE TABLE t4 (i INT PRIMARY KEY, j JSON);
154 INSERT INTO t4 SELECT * FROM t3;
155 SELECT *, JSON_TYPE(j) FROM t4 ORDER BY i;
156 i j JSON_TYPE(j)
157 0 NULL NULL
158 1 {"a": 2} OBJECT
159 2 [1, 2] ARRAY
160 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} OBJECT
161 4 ["here", ["I", "am"], "!!!"] ARRAY
162 5 "scalar string" STRING
163 6 true BOOLEAN
164 7 false BOOLEAN
165 8 null NULL
166 9 -1 INTEGER
167 10 1 INTEGER
168 11 32767 INTEGER
169 12 32768 INTEGER
170 13 -32768 INTEGER
171 14 -32769 INTEGER
172 15 2147483647 INTEGER
173 16 2147483648 INTEGER
174 17 -2147483648 INTEGER
175 18 -2147483649 INTEGER
176 19 18446744073709551615 UNSIGNED INTEGER
177 20 1.8446744073709552e19 DOUBLE
178 21 3.14 DOUBLE
179 22 {} OBJECT
180 23 [] ARRAY
181 24 "2015-01-15 23:24:25.000000" STRING
182 25 "23:24:25.000000" STRING
183 26 "2015-01-15" STRING
184 27 "2015-01-15 23:24:25.000000" STRING
185 28 {"type": "Point", "coordinates": [1.0, 1.0]} OBJECT
186 29 [] ARRAY
187 30 "base64:type15:yv66vg==" STRING
188 #
189 # WL#8249 JSON comparator
190 #
191 CREATE TABLE t5 (id INT PRIMARY KEY AUTO_INCREMENT, j JSON, x INT);
192 INSERT INTO t5(j) VALUES (NULL), (CAST(0 AS JSON)), (CAST(1 AS JSON)),
193 (CAST(2 AS JSON)), (CAST(3 AS JSON)), (CAST(3.14 AS JSON)), ('3.14'),
194 ('"0"'), ('"1"'), ('"2"'), ('"3"'), ('true'), ('false'), ('"true"'),
195 ('"false"'), ('null'), ('"null"'), (JSON_ARRAY(1, 2, 3)), ('"[1, 2, 3]"'),
196 (JSON_ARRAY(1.0e0, 2.0e0, 3.0e0)), (JSON_ARRAY(1, 2, 3.1)),
197 (JSON_OBJECT()), (JSON_OBJECT('a', 'b')), (JSON_OBJECT('a', 'c'));
198 SELECT * FROM t5 ORDER BY id;
199 id j x
200 1 NULL NULL
201 2 0 NULL
202 3 1 NULL
203 4 2 NULL
204 5 3 NULL
205 6 3.14 NULL
206 7 3.14 NULL
207 8 "0" NULL
208 9 "1" NULL
209 10 "2" NULL
210 11 "3" NULL
211 12 true NULL
212 13 false NULL
213 14 "true" NULL
214 15 "false" NULL
215 16 null NULL
216 17 "null" NULL
217 18 [1, 2, 3] NULL
218 19 "[1, 2, 3]" NULL
219 20 [1.0, 2.0, 3.0] NULL
220 21 [1, 2, 3.1] NULL
221 22 {} NULL
222 23 {"a": "b"} NULL
223 24 {"a": "c"} NULL
224 # Expect a single row to be updated (integer 0)
225 UPDATE t5 SET x = 1 WHERE j = 0;
226 SELECT * FROM t5 WHERE x = 1 ORDER BY id;
227 id j x
228 2 0 1
229 # Expect a single row to be updated (string "0")
230 UPDATE t5 SET x = 2 WHERE j = "0";
231 SELECT * FROM t5 WHERE x = 2 ORDER BY id;
232 id j x
233 8 "0" 2
234 # Expect a single row to be updated (boolean true)
235 UPDATE t5 SET x = 3 WHERE j = true;
236 SELECT * FROM t5 WHERE x = 3 ORDER BY id;
237 id j x
238 12 true 3
239 # Expect a single row to be updated (boolean false)
240 UPDATE t5 SET x = 4 WHERE j = false;
241 SELECT * FROM t5 WHERE x = 4 ORDER BY id;
242 id j x
243 13 false 4
244 # Expect a single row to be updated (string "true")
245 UPDATE t5 SET x = 5 WHERE j = 'true';
246 SELECT * FROM t5 WHERE x = 5 ORDER BY id;
247 id j x
248 14 "true" 5
249 # Expect a single row to be updated (string "false")
250 UPDATE t5 SET x = 6 WHERE j = 'false';
251 SELECT * FROM t5 WHERE x = 6 ORDER BY id;
252 id j x
253 15 "false" 6
254 # Expect a single row to be updated (string "[1, 2, 3]")
255 UPDATE t5 SET x = 7 WHERE j = '[1, 2, 3]';
256 SELECT * FROM t5 WHERE x = 7 ORDER BY id;
257 id j x
258 19 "[1, 2, 3]" 7
259 # Expect two rows to be updated (array [1, 2, 3])
260 UPDATE t5 SET x = 8 WHERE j = JSON_ARRAY(1, 2, 3);
261 SELECT * FROM t5 WHERE x = 8 ORDER BY id;
262 id j x
263 18 [1, 2, 3] 8
264 20 [1.0, 2.0, 3.0] 8
265 # Expect two rows to be updated (number 3.14)
266 UPDATE t5 SET x = 9 WHERE j = 3.14;
267 SELECT * FROM t5 WHERE x = 9 ORDER BY id;
268 id j x
269 6 3.14 9
270 7 3.14 9
271 # Expect no rows to be updated (string "3.14")
272 UPDATE t5 SET x = 10 WHERE j = '3.14';
273 SELECT * FROM t5 WHERE x = 10 ORDER BY id;
274 id j x
275 # Expect a single row to be updated (object {"a":"b"})
276 UPDATE t5 SET x = 11 WHERE j = CAST('{"a":"b"}' AS JSON);
277 SELECT * FROM t5 WHERE x = 11 ORDER BY id;
278 id j x
279 23 {"a": "b"} 11
280 # Expect four rows to be updated
281 UPDATE t5 SET x = 12 WHERE JSON_EXTRACT(j, '$[0]') = 1;
282 SELECT * FROM t5 WHERE x = 12 ORDER BY id;
283 id j x
284 3 1 12
285 18 [1, 2, 3] 12
286 20 [1.0, 2.0, 3.0] 12
287 21 [1, 2, 3.1] 12
288 # Expect a single row to be updated
289 UPDATE t5 SET x = 13 WHERE JSON_EXTRACT(j, '$[0]') = '1';
290 SELECT * FROM t5 WHERE x = 13 ORDER BY id;
291 id j x
292 9 "1" 13
293 #
294 # WL#8539 Ordering of scalar JSON values
295 #
296 DELETE FROM t2;
297 INSERT INTO t2 SELECT i, j FROM t1 ORDER BY j, i;
298 Warnings:
299 Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
300 INSERT INTO t2 SELECT i + 100, j FROM t1 ORDER BY j DESC, i;
301 Warnings:
302 Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
303 INSERT INTO t2 SELECT id + 200, j FROM t5 ORDER BY j, id;
304 Warnings:
305 Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
306 INSERT INTO t2 SELECT id + 300, j FROM t5 ORDER BY j DESC, id;
307 Warnings:
308 Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
309 SELECT * FROM t2 ORDER BY i;
310 i j
311 0 NULL
312 1 {"a": 2}
313 2 [1, 2]
314 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
315 4 ["here", ["I", "am"], "!!!"]
316 5 "scalar string"
317 6 true
318 7 false
319 8 null
320 9 -1
321 10 1
322 11 32767
323 12 32768
324 13 -32768
325 14 -32769
326 15 2147483647
327 16 2147483648
328 17 -2147483648
329 18 -2147483649
330 19 18446744073709551615
331 20 1.8446744073709552e19
332 21 3.14
333 22 {}
334 23 []
335 24 "2015-01-15 23:24:25.000000"
336 25 "23:24:25.000000"
337 26 "2015-01-15"
338 27 "2015-01-15 23:24:25.000000"
339 28 {"type": "Point", "coordinates": [1.0, 1.0]}
340 29 []
341 30 "base64:type15:yv66vg=="
342 100 NULL
343 101 {"a": 2}
344 102 [1, 2]
345 103 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
346 104 ["here", ["I", "am"], "!!!"]
347 105 "scalar string"
348 106 true
349 107 false
350 108 null
351 109 -1
352 110 1
353 111 32767
354 112 32768
355 113 -32768
356 114 -32769
357 115 2147483647
358 116 2147483648
359 117 -2147483648
360 118 -2147483649
361 119 18446744073709551615
362 120 1.8446744073709552e19
363 121 3.14
364 122 {}
365 123 []
366 124 "2015-01-15 23:24:25.000000"
367 125 "23:24:25.000000"
368 126 "2015-01-15"
369 127 "2015-01-15 23:24:25.000000"
370 128 {"type": "Point", "coordinates": [1.0, 1.0]}
371 129 []
372 130 "base64:type15:yv66vg=="
373 201 NULL
374 202 0
375 203 1
376 204 2
377 205 3
378 206 3.14
379 207 3.14
380 208 "0"
381 209 "1"
382 210 "2"
383 211 "3"
384 212 true
385 213 false
386 214 "true"
387 215 "false"
388 216 null
389 217 "null"
390 218 [1, 2, 3]
391 219 "[1, 2, 3]"
392 220 [1.0, 2.0, 3.0]
393 221 [1, 2, 3.1]
394 222 {}
395 223 {"a": "b"}
396 224 {"a": "c"}
397 301 NULL
398 302 0
399 303 1
400 304 2
401 305 3
402 306 3.14
403 307 3.14
404 308 "0"
405 309 "1"
406 310 "2"
407 311 "3"
408 312 true
409 313 false
410 314 "true"
411 315 "false"
412 316 null
413 317 "null"
414 318 [1, 2, 3]
415 319 "[1, 2, 3]"
416 320 [1.0, 2.0, 3.0]
417 321 [1, 2, 3.1]
418 322 {}
419 323 {"a": "b"}
420 324 {"a": "c"}
421 #
422 # WL#7909 Server side JSON functions
423 #
424 ALTER TABLE t1 ADD COLUMN j2 JSON;
425 UPDATE t1 SET j2 = JSON_ARRAY(JSON_TYPE(j));
426 SELECT * FROM t1 ORDER BY i;
427 i j j2
428 0 NULL [null]
429 1 {"a": 2} ["OBJECT"]
430 2 [1, 2] ["ARRAY"]
431 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} ["OBJECT"]
432 4 ["here", ["I", "am"], "!!!"] ["ARRAY"]
433 5 "scalar string" ["STRING"]
434 6 true ["BOOLEAN"]
435 7 false ["BOOLEAN"]
436 8 null ["NULL"]
437 9 -1 ["INTEGER"]
438 10 1 ["UNSIGNED INTEGER"]
439 11 32767 ["INTEGER"]
440 12 32768 ["INTEGER"]
441 13 -32768 ["INTEGER"]
442 14 -32769 ["INTEGER"]
443 15 2147483647 ["INTEGER"]
444 16 2147483648 ["INTEGER"]
445 17 -2147483648 ["INTEGER"]
446 18 -2147483649 ["INTEGER"]
447 19 18446744073709551615 ["UNSIGNED INTEGER"]
448 20 1.8446744073709552e19 ["DOUBLE"]
449 21 3.14 ["DOUBLE"]
450 22 {} ["OBJECT"]
451 23 [] ["ARRAY"]
452 24 "2015-01-15 23:24:25.000000" ["DATETIME"]
453 25 "23:24:25.000000" ["TIME"]
454 26 "2015-01-15" ["DATE"]
455 27 "2015-01-15 23:24:25.000000" ["DATETIME"]
456 28 {"type": "Point", "coordinates": [1.0, 1.0]} ["OBJECT"]
457 29 [] ["ARRAY"]
458 30 "base64:type15:yv66vg==" ["BLOB"]
459 UPDATE t1 SET j2 = CAST(JSON_VALID(j) AS JSON);
460 SELECT * FROM t1 ORDER BY i;
461 i j j2
462 0 NULL NULL
463 1 {"a": 2} true
464 2 [1, 2] true
465 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} true
466 4 ["here", ["I", "am"], "!!!"] true
467 5 "scalar string" true
468 6 true true
469 7 false true
470 8 null true
471 9 -1 true
472 10 1 true
473 11 32767 true
474 12 32768 true
475 13 -32768 true
476 14 -32769 true
477 15 2147483647 true
478 16 2147483648 true
479 17 -2147483648 true
480 18 -2147483649 true
481 19 18446744073709551615 true
482 20 1.8446744073709552e19 true
483 21 3.14 true
484 22 {} true
485 23 [] true
486 24 "2015-01-15 23:24:25.000000" true
487 25 "23:24:25.000000" true
488 26 "2015-01-15" true
489 27 "2015-01-15 23:24:25.000000" true
490 28 {"type": "Point", "coordinates": [1.0, 1.0]} true
491 29 [] true
492 30 "base64:type15:yv66vg==" true
493 UPDATE t1 SET j2 = JSON_KEYS(j);
494 SELECT * FROM t1 ORDER BY i;
495 i j j2
496 0 NULL NULL
497 1 {"a": 2} ["a"]
498 2 [1, 2] NULL
499 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} ["a", "c", "ab", "bc"]
500 4 ["here", ["I", "am"], "!!!"] NULL
501 5 "scalar string" NULL
502 6 true NULL
503 7 false NULL
504 8 null NULL
505 9 -1 NULL
506 10 1 NULL
507 11 32767 NULL
508 12 32768 NULL
509 13 -32768 NULL
510 14 -32769 NULL
511 15 2147483647 NULL
512 16 2147483648 NULL
513 17 -2147483648 NULL
514 18 -2147483649 NULL
515 19 18446744073709551615 NULL
516 20 1.8446744073709552e19 NULL
517 21 3.14 NULL
518 22 {} []
519 23 [] NULL
520 24 "2015-01-15 23:24:25.000000" NULL
521 25 "23:24:25.000000" NULL
522 26 "2015-01-15" NULL
523 27 "2015-01-15 23:24:25.000000" NULL
524 28 {"type": "Point", "coordinates": [1.0, 1.0]} ["type", "coordinates"]
525 29 [] NULL
526 30 "base64:type15:yv66vg==" NULL
527 UPDATE t1 SET j2 = JSON_ARRAY(j,j,j);
528 SELECT * FROM t1 ORDER BY i;
529 i j j2
530 0 NULL [null, null, null]
531 1 {"a": 2} [{"a": 2}, {"a": 2}, {"a": 2}]
532 2 [1, 2] [[1, 2], [1, 2], [1, 2]]
533 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} [{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}, {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}, {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}]
534 4 ["here", ["I", "am"], "!!!"] [["here", ["I", "am"], "!!!"], ["here", ["I", "am"], "!!!"], ["here", ["I", "am"], "!!!"]]
535 5 "scalar string" ["scalar string", "scalar string", "scalar string"]
536 6 true [true, true, true]
537 7 false [false, false, false]
538 8 null [null, null, null]
539 9 -1 [-1, -1, -1]
540 10 1 [1, 1, 1]
541 11 32767 [32767, 32767, 32767]
542 12 32768 [32768, 32768, 32768]
543 13 -32768 [-32768, -32768, -32768]
544 14 -32769 [-32769, -32769, -32769]
545 15 2147483647 [2147483647, 2147483647, 2147483647]
546 16 2147483648 [2147483648, 2147483648, 2147483648]
547 17 -2147483648 [-2147483648, -2147483648, -2147483648]
548 18 -2147483649 [-2147483649, -2147483649, -2147483649]
549 19 18446744073709551615 [18446744073709551615, 18446744073709551615, 18446744073709551615]
550 20 1.8446744073709552e19 [1.8446744073709552e19, 1.8446744073709552e19, 1.8446744073709552e19]
551 21 3.14 [3.14, 3.14, 3.14]
552 22 {} [{}, {}, {}]
553 23 [] [[], [], []]
554 24 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000", "2015-01-15 23:24:25.000000", "2015-01-15 23:24:25.000000"]
555 25 "23:24:25.000000" ["23:24:25.000000", "23:24:25.000000", "23:24:25.000000"]
556 26 "2015-01-15" ["2015-01-15", "2015-01-15", "2015-01-15"]
557 27 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000", "2015-01-15 23:24:25.000000", "2015-01-15 23:24:25.000000"]
558 28 {"type": "Point", "coordinates": [1.0, 1.0]} [{"type": "Point", "coordinates": [1.0, 1.0]}, {"type": "Point", "coordinates": [1.0, 1.0]}, {"type": "Point", "coordinates": [1.0, 1.0]}]
559 29 [] [[], [], []]
560 30 "base64:type15:yv66vg==" ["base64:type15:yv66vg==", "base64:type15:yv66vg==", "base64:type15:yv66vg=="]
561 UPDATE t1 SET j2 = JSON_EXTRACT(j2, '$[1]');
562 SELECT * FROM t1 ORDER BY i;
563 i j j2
564 0 NULL null
565 1 {"a": 2} {"a": 2}
566 2 [1, 2] [1, 2]
567 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
568 4 ["here", ["I", "am"], "!!!"] ["here", ["I", "am"], "!!!"]
569 5 "scalar string" "scalar string"
570 6 true true
571 7 false false
572 8 null null
573 9 -1 -1
574 10 1 1
575 11 32767 32767
576 12 32768 32768
577 13 -32768 -32768
578 14 -32769 -32769
579 15 2147483647 2147483647
580 16 2147483648 2147483648
581 17 -2147483648 -2147483648
582 18 -2147483649 -2147483649
583 19 18446744073709551615 18446744073709551615
584 20 1.8446744073709552e19 1.8446744073709552e19
585 21 3.14 3.14
586 22 {} {}
587 23 [] []
588 24 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000"
589 25 "23:24:25.000000" "23:24:25.000000"
590 26 "2015-01-15" "2015-01-15"
591 27 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000"
592 28 {"type": "Point", "coordinates": [1.0, 1.0]} {"type": "Point", "coordinates": [1.0, 1.0]}
593 29 [] []
594 30 "base64:type15:yv66vg==" "base64:type15:yv66vg=="
595 UPDATE t1 SET j2 = JSON_REMOVE(j, '$[1]');
596 SELECT * FROM t1 ORDER BY i;
597 i j j2
598 0 NULL NULL
599 1 {"a": 2} {"a": 2}
600 2 [1, 2] [1]
601 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
602 4 ["here", ["I", "am"], "!!!"] ["here", "!!!"]
603 5 "scalar string" "scalar string"
604 6 true true
605 7 false false
606 8 null null
607 9 -1 -1
608 10 1 1
609 11 32767 32767
610 12 32768 32768
611 13 -32768 -32768
612 14 -32769 -32769
613 15 2147483647 2147483647
614 16 2147483648 2147483648
615 17 -2147483648 -2147483648
616 18 -2147483649 -2147483649
617 19 18446744073709551615 18446744073709551615
618 20 1.8446744073709552e19 1.8446744073709552e19
619 21 3.14 3.14
620 22 {} {}
621 23 [] []
622 24 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000"
623 25 "23:24:25.000000" "23:24:25.000000"
624 26 "2015-01-15" "2015-01-15"
625 27 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000"
626 28 {"type": "Point", "coordinates": [1.0, 1.0]} {"type": "Point", "coordinates": [1.0, 1.0]}
627 29 [] []
628 30 "base64:type15:yv66vg==" "base64:type15:yv66vg=="
629 UPDATE t1 SET j2 = JSON_ARRAY_APPEND(j, '$[1]', 'abc');
630 SELECT * FROM t1 ORDER BY i;
631 i j j2
632 0 NULL NULL
633 1 {"a": 2} {"a": 2}
634 2 [1, 2] [1, [2, "abc"]]
635 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
636 4 ["here", ["I", "am"], "!!!"] ["here", ["I", "am", "abc"], "!!!"]
637 5 "scalar string" "scalar string"
638 6 true true
639 7 false false
640 8 null null
641 9 -1 -1
642 10 1 1
643 11 32767 32767
644 12 32768 32768
645 13 -32768 -32768
646 14 -32769 -32769
647 15 2147483647 2147483647
648 16 2147483648 2147483648
649 17 -2147483648 -2147483648
650 18 -2147483649 -2147483649
651 19 18446744073709551615 18446744073709551615
652 20 1.8446744073709552e19 1.8446744073709552e19
653 21 3.14 3.14
654 22 {} {}
655 23 [] []
656 24 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000"
657 25 "23:24:25.000000" "23:24:25.000000"
658 26 "2015-01-15" "2015-01-15"
659 27 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000"
660 28 {"type": "Point", "coordinates": [1.0, 1.0]} {"type": "Point", "coordinates": [1.0, 1.0]}
661 29 [] []
662 30 "base64:type15:yv66vg==" "base64:type15:yv66vg=="
663 UPDATE t1 SET j2 = JSON_SET(j, '$[1]', 'abc');
664 SELECT * FROM t1 ORDER BY i;
665 i j j2
666 0 NULL NULL
667 1 {"a": 2} [{"a": 2}, "abc"]
668 2 [1, 2] [1, "abc"]
669 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} [{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}, "abc"]
670 4 ["here", ["I", "am"], "!!!"] ["here", "abc", "!!!"]
671 5 "scalar string" ["scalar string", "abc"]
672 6 true [true, "abc"]
673 7 false [false, "abc"]
674 8 null [null, "abc"]
675 9 -1 [-1, "abc"]
676 10 1 [1, "abc"]
677 11 32767 [32767, "abc"]
678 12 32768 [32768, "abc"]
679 13 -32768 [-32768, "abc"]
680 14 -32769 [-32769, "abc"]
681 15 2147483647 [2147483647, "abc"]
682 16 2147483648 [2147483648, "abc"]
683 17 -2147483648 [-2147483648, "abc"]
684 18 -2147483649 [-2147483649, "abc"]
685 19 18446744073709551615 [18446744073709551615, "abc"]
686 20 1.8446744073709552e19 [1.8446744073709552e19, "abc"]
687 21 3.14 [3.14, "abc"]
688 22 {} [{}, "abc"]
689 23 [] ["abc"]
690 24 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000", "abc"]
691 25 "23:24:25.000000" ["23:24:25.000000", "abc"]
692 26 "2015-01-15" ["2015-01-15", "abc"]
693 27 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000", "abc"]
694 28 {"type": "Point", "coordinates": [1.0, 1.0]} [{"type": "Point", "coordinates": [1.0, 1.0]}, "abc"]
695 29 [] ["abc"]
696 30 "base64:type15:yv66vg==" ["base64:type15:yv66vg==", "abc"]
697 UPDATE t1 SET j2 = JSON_INSERT(j, '$[1]', 'abc');
698 SELECT * FROM t1 ORDER BY i;
699 i j j2
700 0 NULL NULL
701 1 {"a": 2} [{"a": 2}, "abc"]
702 2 [1, 2] [1, 2]
703 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} [{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}, "abc"]
704 4 ["here", ["I", "am"], "!!!"] ["here", ["I", "am"], "!!!"]
705 5 "scalar string" ["scalar string", "abc"]
706 6 true [true, "abc"]
707 7 false [false, "abc"]
708 8 null [null, "abc"]
709 9 -1 [-1, "abc"]
710 10 1 [1, "abc"]
711 11 32767 [32767, "abc"]
712 12 32768 [32768, "abc"]
713 13 -32768 [-32768, "abc"]
714 14 -32769 [-32769, "abc"]
715 15 2147483647 [2147483647, "abc"]
716 16 2147483648 [2147483648, "abc"]
717 17 -2147483648 [-2147483648, "abc"]
718 18 -2147483649 [-2147483649, "abc"]
719 19 18446744073709551615 [18446744073709551615, "abc"]
720 20 1.8446744073709552e19 [1.8446744073709552e19, "abc"]
721 21 3.14 [3.14, "abc"]
722 22 {} [{}, "abc"]
723 23 [] ["abc"]
724 24 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000", "abc"]
725 25 "23:24:25.000000" ["23:24:25.000000", "abc"]
726 26 "2015-01-15" ["2015-01-15", "abc"]
727 27 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000", "abc"]
728 28 {"type": "Point", "coordinates": [1.0, 1.0]} [{"type": "Point", "coordinates": [1.0, 1.0]}, "abc"]
729 29 [] ["abc"]
730 30 "base64:type15:yv66vg==" ["base64:type15:yv66vg==", "abc"]
731 UPDATE t1 SET j2 = JSON_REPLACE(j, '$[1]', 'abc');
732 SELECT * FROM t1 ORDER BY i;
733 i j j2
734 0 NULL NULL
735 1 {"a": 2} {"a": 2}
736 2 [1, 2] [1, "abc"]
737 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
738 4 ["here", ["I", "am"], "!!!"] ["here", "abc", "!!!"]
739 5 "scalar string" "scalar string"
740 6 true true
741 7 false false
742 8 null null
743 9 -1 -1
744 10 1 1
745 11 32767 32767
746 12 32768 32768
747 13 -32768 -32768
748 14 -32769 -32769
749 15 2147483647 2147483647
750 16 2147483648 2147483648
751 17 -2147483648 -2147483648
752 18 -2147483649 -2147483649
753 19 18446744073709551615 18446744073709551615
754 20 1.8446744073709552e19 1.8446744073709552e19
755 21 3.14 3.14
756 22 {} {}
757 23 [] []
758 24 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000"
759 25 "23:24:25.000000" "23:24:25.000000"
760 26 "2015-01-15" "2015-01-15"
761 27 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000"
762 28 {"type": "Point", "coordinates": [1.0, 1.0]} {"type": "Point", "coordinates": [1.0, 1.0]}
763 29 [] []
764 30 "base64:type15:yv66vg==" "base64:type15:yv66vg=="
765 UPDATE t1 SET j2 = JSON_MERGE_PATCH(j, '{"a": "patched", "c": null}');
766 SELECT * FROM t1 ORDER BY i;
767 i j j2
768 0 NULL NULL
769 1 {"a": 2} {"a": "patched"}
770 2 [1, 2] {"a": "patched"}
771 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} {"a": "patched", "ab": "abc", "bc": ["x", "y"]}
772 4 ["here", ["I", "am"], "!!!"] {"a": "patched"}
773 5 "scalar string" {"a": "patched"}
774 6 true {"a": "patched"}
775 7 false {"a": "patched"}
776 8 null {"a": "patched"}
777 9 -1 {"a": "patched"}
778 10 1 {"a": "patched"}
779 11 32767 {"a": "patched"}
780 12 32768 {"a": "patched"}
781 13 -32768 {"a": "patched"}
782 14 -32769 {"a": "patched"}
783 15 2147483647 {"a": "patched"}
784 16 2147483648 {"a": "patched"}
785 17 -2147483648 {"a": "patched"}
786 18 -2147483649 {"a": "patched"}
787 19 18446744073709551615 {"a": "patched"}
788 20 1.8446744073709552e19 {"a": "patched"}
789 21 3.14 {"a": "patched"}
790 22 {} {"a": "patched"}
791 23 [] {"a": "patched"}
792 24 "2015-01-15 23:24:25.000000" {"a": "patched"}
793 25 "23:24:25.000000" {"a": "patched"}
794 26 "2015-01-15" {"a": "patched"}
795 27 "2015-01-15 23:24:25.000000" {"a": "patched"}
796 28 {"type": "Point", "coordinates": [1.0, 1.0]} {"a": "patched", "type": "Point", "coordinates": [1.0, 1.0]}
797 29 [] {"a": "patched"}
798 30 "base64:type15:yv66vg==" {"a": "patched"}
799 UPDATE t1 SET j2 = JSON_MERGE_PRESERVE(j, j);
800 SELECT * FROM t1 ORDER BY i;
801 i j j2
802 0 NULL NULL
803 1 {"a": 2} {"a": [2, 2]}
804 2 [1, 2] [1, 2, 1, 2]
805 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} {"a": ["b", "b"], "c": ["d", "d"], "ab": ["abc", "abc"], "bc": ["x", "y", "x", "y"]}
806 4 ["here", ["I", "am"], "!!!"] ["here", ["I", "am"], "!!!", "here", ["I", "am"], "!!!"]
807 5 "scalar string" ["scalar string", "scalar string"]
808 6 true [true, true]
809 7 false [false, false]
810 8 null [null, null]
811 9 -1 [-1, -1]
812 10 1 [1, 1]
813 11 32767 [32767, 32767]
814 12 32768 [32768, 32768]
815 13 -32768 [-32768, -32768]
816 14 -32769 [-32769, -32769]
817 15 2147483647 [2147483647, 2147483647]
818 16 2147483648 [2147483648, 2147483648]
819 17 -2147483648 [-2147483648, -2147483648]
820 18 -2147483649 [-2147483649, -2147483649]
821 19 18446744073709551615 [18446744073709551615, 18446744073709551615]
822 20 1.8446744073709552e19 [1.8446744073709552e19, 1.8446744073709552e19]
823 21 3.14 [3.14, 3.14]
824 22 {} {}
825 23 [] []
826 24 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000", "2015-01-15 23:24:25.000000"]
827 25 "23:24:25.000000" ["23:24:25.000000", "23:24:25.000000"]
828 26 "2015-01-15" ["2015-01-15", "2015-01-15"]
829 27 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000", "2015-01-15 23:24:25.000000"]
830 28 {"type": "Point", "coordinates": [1.0, 1.0]} {"type": ["Point", "Point"], "coordinates": [1.0, 1.0, 1.0, 1.0]}
831 29 [] []
832 30 "base64:type15:yv66vg==" ["base64:type15:yv66vg==", "base64:type15:yv66vg=="]
833 UPDATE t1 SET j2 = JSON_SEARCH(j, 'one', 'abc');
834 SELECT * FROM t1 ORDER BY i;
835 i j j2
836 0 NULL NULL
837 1 {"a": 2} NULL
838 2 [1, 2] NULL
839 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} "$.ab"
840 4 ["here", ["I", "am"], "!!!"] NULL
841 5 "scalar string" NULL
842 6 true NULL
843 7 false NULL
844 8 null NULL
845 9 -1 NULL
846 10 1 NULL
847 11 32767 NULL
848 12 32768 NULL
849 13 -32768 NULL
850 14 -32769 NULL
851 15 2147483647 NULL
852 16 2147483648 NULL
853 17 -2147483648 NULL
854 18 -2147483649 NULL
855 19 18446744073709551615 NULL
856 20 1.8446744073709552e19 NULL
857 21 3.14 NULL
858 22 {} NULL
859 23 [] NULL
860 24 "2015-01-15 23:24:25.000000" NULL
861 25 "23:24:25.000000" NULL
862 26 "2015-01-15" NULL
863 27 "2015-01-15 23:24:25.000000" NULL
864 28 {"type": "Point", "coordinates": [1.0, 1.0]} NULL
865 29 [] NULL
866 30 "base64:type15:yv66vg==" NULL
867 UPDATE t1 SET j2 = CAST(JSON_CONTAINS(j, '[1]') AS JSON);
868 SELECT * FROM t1 ORDER BY i;
869 i j j2
870 0 NULL NULL
871 1 {"a": 2} false
872 2 [1, 2] true
873 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} false
874 4 ["here", ["I", "am"], "!!!"] false
875 5 "scalar string" false
876 6 true false
877 7 false false
878 8 null false
879 9 -1 false
880 10 1 false
881 11 32767 false
882 12 32768 false
883 13 -32768 false
884 14 -32769 false
885 15 2147483647 false
886 16 2147483648 false
887 17 -2147483648 false
888 18 -2147483649 false
889 19 18446744073709551615 false
890 20 1.8446744073709552e19 false
891 21 3.14 false
892 22 {} false
893 23 [] false
894 24 "2015-01-15 23:24:25.000000" false
895 25 "23:24:25.000000" false
896 26 "2015-01-15" false
897 27 "2015-01-15 23:24:25.000000" false
898 28 {"type": "Point", "coordinates": [1.0, 1.0]} false
899 29 [] false
900 30 "base64:type15:yv66vg==" false
901 UPDATE t1 SET j2 = CAST(JSON_CONTAINS_PATH(j, 'one', '$.a') AS JSON);
902 SELECT * FROM t1 ORDER BY i;
903 i j j2
904 0 NULL NULL
905 1 {"a": 2} true
906 2 [1, 2] false
907 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} true
908 4 ["here", ["I", "am"], "!!!"] false
909 5 "scalar string" false
910 6 true false
911 7 false false
912 8 null false
913 9 -1 false
914 10 1 false
915 11 32767 false
916 12 32768 false
917 13 -32768 false
918 14 -32769 false
919 15 2147483647 false
920 16 2147483648 false
921 17 -2147483648 false
922 18 -2147483649 false
923 19 18446744073709551615 false
924 20 1.8446744073709552e19 false
925 21 3.14 false
926 22 {} false
927 23 [] false
928 24 "2015-01-15 23:24:25.000000" false
929 25 "23:24:25.000000" false
930 26 "2015-01-15" false
931 27 "2015-01-15 23:24:25.000000" false
932 28 {"type": "Point", "coordinates": [1.0, 1.0]} false
933 29 [] false
934 30 "base64:type15:yv66vg==" false
935 UPDATE t1 SET j2 = CAST(JSON_LENGTH(j) AS JSON);
936 SELECT * FROM t1 ORDER BY i;
937 i j j2
938 0 NULL NULL
939 1 {"a": 2} 1
940 2 [1, 2] 2
941 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 4
942 4 ["here", ["I", "am"], "!!!"] 3
943 5 "scalar string" 1
944 6 true 1
945 7 false 1
946 8 null 1
947 9 -1 1
948 10 1 1
949 11 32767 1
950 12 32768 1
951 13 -32768 1
952 14 -32769 1
953 15 2147483647 1
954 16 2147483648 1
955 17 -2147483648 1
956 18 -2147483649 1
957 19 18446744073709551615 1
958 20 1.8446744073709552e19 1
959 21 3.14 1
960 22 {} 0
961 23 [] 0
962 24 "2015-01-15 23:24:25.000000" 1
963 25 "23:24:25.000000" 1
964 26 "2015-01-15" 1
965 27 "2015-01-15 23:24:25.000000" 1
966 28 {"type": "Point", "coordinates": [1.0, 1.0]} 2
967 29 [] 0
968 30 "base64:type15:yv66vg==" 1
969 UPDATE t1 SET j2 = CAST(JSON_DEPTH(j) AS JSON);
970 SELECT * FROM t1 ORDER BY i;
971 i j j2
972 0 NULL NULL
973 1 {"a": 2} 2
974 2 [1, 2] 2
975 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 3
976 4 ["here", ["I", "am"], "!!!"] 3
977 5 "scalar string" 1
978 6 true 1
979 7 false 1
980 8 null 1
981 9 -1 1
982 10 1 1
983 11 32767 1
984 12 32768 1
985 13 -32768 1
986 14 -32769 1
987 15 2147483647 1
988 16 2147483648 1
989 17 -2147483648 1
990 18 -2147483649 1
991 19 18446744073709551615 1
992 20 1.8446744073709552e19 1
993 21 3.14 1
994 22 {} 1
995 23 [] 1
996 24 "2015-01-15 23:24:25.000000" 1
997 25 "23:24:25.000000" 1
998 26 "2015-01-15" 1
999 27 "2015-01-15 23:24:25.000000" 1
1000 28 {"type": "Point", "coordinates": [1.0, 1.0]} 3
1001 29 [] 1
1002 30 "base64:type15:yv66vg==" 1
1003 UPDATE t1 SET j2 = JSON_OBJECT('a', j, 'b', j);
1004 SELECT * FROM t1 ORDER BY i;
1005 i j j2
1006 0 NULL {"a": null, "b": null}
1007 1 {"a": 2} {"a": {"a": 2}, "b": {"a": 2}}
1008 2 [1, 2] {"a": [1, 2], "b": [1, 2]}
1009 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} {"a": {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}, "b": {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}}
1010 4 ["here", ["I", "am"], "!!!"] {"a": ["here", ["I", "am"], "!!!"], "b": ["here", ["I", "am"], "!!!"]}
1011 5 "scalar string" {"a": "scalar string", "b": "scalar string"}
1012 6 true {"a": true, "b": true}
1013 7 false {"a": false, "b": false}
1014 8 null {"a": null, "b": null}
1015 9 -1 {"a": -1, "b": -1}
1016 10 1 {"a": 1, "b": 1}
1017 11 32767 {"a": 32767, "b": 32767}
1018 12 32768 {"a": 32768, "b": 32768}
1019 13 -32768 {"a": -32768, "b": -32768}
1020 14 -32769 {"a": -32769, "b": -32769}
1021 15 2147483647 {"a": 2147483647, "b": 2147483647}
1022 16 2147483648 {"a": 2147483648, "b": 2147483648}
1023 17 -2147483648 {"a": -2147483648, "b": -2147483648}
1024 18 -2147483649 {"a": -2147483649, "b": -2147483649}
1025 19 18446744073709551615 {"a": 18446744073709551615, "b": 18446744073709551615}
1026 20 1.8446744073709552e19 {"a": 1.8446744073709552e19, "b": 1.8446744073709552e19}
1027 21 3.14 {"a": 3.14, "b": 3.14}
1028 22 {} {"a": {}, "b": {}}
1029 23 [] {"a": [], "b": []}
1030 24 "2015-01-15 23:24:25.000000" {"a": "2015-01-15 23:24:25.000000", "b": "2015-01-15 23:24:25.000000"}
1031 25 "23:24:25.000000" {"a": "23:24:25.000000", "b": "23:24:25.000000"}
1032 26 "2015-01-15" {"a": "2015-01-15", "b": "2015-01-15"}
1033 27 "2015-01-15 23:24:25.000000" {"a": "2015-01-15 23:24:25.000000", "b": "2015-01-15 23:24:25.000000"}
1034 28 {"type": "Point", "coordinates": [1.0, 1.0]} {"a": {"type": "Point", "coordinates": [1.0, 1.0]}, "b": {"type": "Point", "coordinates": [1.0, 1.0]}}
1035 29 [] {"a": [], "b": []}
1036 30 "base64:type15:yv66vg==" {"a": "base64:type15:yv66vg==", "b": "base64:type15:yv66vg=="}
1037 UPDATE t1 SET j2 = JSON_ARRAY(JSON_UNQUOTE(j));
1038 SELECT * FROM t1 ORDER BY i;
1039 i j j2
1040 0 NULL [null]
1041 1 {"a": 2} ["{\"a\": 2}"]
1042 2 [1, 2] ["[1, 2]"]
1043 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} ["{\"a\": \"b\", \"c\": \"d\", \"ab\": \"abc\", \"bc\": [\"x\", \"y\"]}"]
1044 4 ["here", ["I", "am"], "!!!"] ["[\"here\", [\"I\", \"am\"], \"!!!\"]"]
1045 5 "scalar string" ["scalar string"]
1046 6 true ["true"]
1047 7 false ["false"]
1048 8 null ["null"]
1049 9 -1 ["-1"]
1050 10 1 ["1"]
1051 11 32767 ["32767"]
1052 12 32768 ["32768"]
1053 13 -32768 ["-32768"]
1054 14 -32769 ["-32769"]
1055 15 2147483647 ["2147483647"]
1056 16 2147483648 ["2147483648"]
1057 17 -2147483648 ["-2147483648"]
1058 18 -2147483649 ["-2147483649"]
1059 19 18446744073709551615 ["18446744073709551615"]
1060 20 1.8446744073709552e19 ["1.8446744073709552e19"]
1061 21 3.14 ["3.14"]
1062 22 {} ["{}"]
1063 23 [] ["[]"]
1064 24 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000"]
1065 25 "23:24:25.000000" ["23:24:25.000000"]
1066 26 "2015-01-15" ["2015-01-15"]
1067 27 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000"]
1068 28 {"type": "Point", "coordinates": [1.0, 1.0]} ["{\"type\": \"Point\", \"coordinates\": [1.0, 1.0]}"]
1069 29 [] ["[]"]
1070 30 "base64:type15:yv66vg==" ["base64:type15:yv66vg=="]
1071 UPDATE t1 SET j2 = CAST(JSON_QUOTE(CAST(j AS CHAR)) AS JSON);
1072 SELECT * FROM t1 ORDER BY i;
1073 i j j2
1074 0 NULL NULL
1075 1 {"a": 2} "{\"a\": 2}"
1076 2 [1, 2] "[1, 2]"
1077 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} "{\"a\": \"b\", \"c\": \"d\", \"ab\": \"abc\", \"bc\": [\"x\", \"y\"]}"
1078 4 ["here", ["I", "am"], "!!!"] "[\"here\", [\"I\", \"am\"], \"!!!\"]"
1079 5 "scalar string" "\"scalar string\""
1080 6 true "true"
1081 7 false "false"
1082 8 null "null"
1083 9 -1 "-1"
1084 10 1 "1"
1085 11 32767 "32767"
1086 12 32768 "32768"
1087 13 -32768 "-32768"
1088 14 -32769 "-32769"
1089 15 2147483647 "2147483647"
1090 16 2147483648 "2147483648"
1091 17 -2147483648 "-2147483648"
1092 18 -2147483649 "-2147483649"
1093 19 18446744073709551615 "18446744073709551615"
1094 20 1.8446744073709552e19 "1.8446744073709552e19"
1095 21 3.14 "3.14"
1096 22 {} "{}"
1097 23 [] "[]"
1098 24 "2015-01-15 23:24:25.000000" "\"2015-01-15 23:24:25.000000\""
1099 25 "23:24:25.000000" "\"23:24:25.000000\""
1100 26 "2015-01-15" "\"2015-01-15\""
1101 27 "2015-01-15 23:24:25.000000" "\"2015-01-15 23:24:25.000000\""
1102 28 {"type": "Point", "coordinates": [1.0, 1.0]} "{\"type\": \"Point\", \"coordinates\": [1.0, 1.0]}"
1103 29 [] "[]"
1104 30 "base64:type15:yv66vg==" "\"base64:type15:yv66vg==\""
1105 ALTER TABLE t1 ADD COLUMN j3 JSON AS (JSON_EXTRACT(j2, '$.abc'));
1106 SELECT * FROM t1 ORDER BY i;
1107 i j j2 j3
1108 0 NULL NULL NULL
1109 1 {"a": 2} "{\"a\": 2}" NULL
1110 2 [1, 2] "[1, 2]" NULL
1111 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} "{\"a\": \"b\", \"c\": \"d\", \"ab\": \"abc\", \"bc\": [\"x\", \"y\"]}" NULL
1112 4 ["here", ["I", "am"], "!!!"] "[\"here\", [\"I\", \"am\"], \"!!!\"]" NULL
1113 5 "scalar string" "\"scalar string\"" NULL
1114 6 true "true" NULL
1115 7 false "false" NULL
1116 8 null "null" NULL
1117 9 -1 "-1" NULL
1118 10 1 "1" NULL
1119 11 32767 "32767" NULL
1120 12 32768 "32768" NULL
1121 13 -32768 "-32768" NULL
1122 14 -32769 "-32769" NULL
1123 15 2147483647 "2147483647" NULL
1124 16 2147483648 "2147483648" NULL
1125 17 -2147483648 "-2147483648" NULL
1126 18 -2147483649 "-2147483649" NULL
1127 19 18446744073709551615 "18446744073709551615" NULL
1128 20 1.8446744073709552e19 "1.8446744073709552e19" NULL
1129 21 3.14 "3.14" NULL
1130 22 {} "{}" NULL
1131 23 [] "[]" NULL
1132 24 "2015-01-15 23:24:25.000000" "\"2015-01-15 23:24:25.000000\"" NULL
1133 25 "23:24:25.000000" "\"23:24:25.000000\"" NULL
1134 26 "2015-01-15" "\"2015-01-15\"" NULL
1135 27 "2015-01-15 23:24:25.000000" "\"2015-01-15 23:24:25.000000\"" NULL
1136 28 {"type": "Point", "coordinates": [1.0, 1.0]} "{\"type\": \"Point\", \"coordinates\": [1.0, 1.0]}" NULL
1137 29 [] "[]" NULL
1138 30 "base64:type15:yv66vg==" "\"base64:type15:yv66vg==\"" NULL
1139 UPDATE t1 SET j2 = JSON_OBJECT('abc', j);
1140 SELECT * FROM t1 ORDER BY i;
1141 i j j2 j3
1142 0 NULL {"abc": null} null
1143 1 {"a": 2} {"abc": {"a": 2}} {"a": 2}
1144 2 [1, 2] {"abc": [1, 2]} [1, 2]
1145 3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} {"abc": {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}} {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
1146 4 ["here", ["I", "am"], "!!!"] {"abc": ["here", ["I", "am"], "!!!"]} ["here", ["I", "am"], "!!!"]
1147 5 "scalar string" {"abc": "scalar string"} "scalar string"
1148 6 true {"abc": true} true
1149 7 false {"abc": false} false
1150 8 null {"abc": null} null
1151 9 -1 {"abc": -1} -1
1152 10 1 {"abc": 1} 1
1153 11 32767 {"abc": 32767} 32767
1154 12 32768 {"abc": 32768} 32768
1155 13 -32768 {"abc": -32768} -32768
1156 14 -32769 {"abc": -32769} -32769
1157 15 2147483647 {"abc": 2147483647} 2147483647
1158 16 2147483648 {"abc": 2147483648} 2147483648
1159 17 -2147483648 {"abc": -2147483648} -2147483648
1160 18 -2147483649 {"abc": -2147483649} -2147483649
1161 19 18446744073709551615 {"abc": 18446744073709551615} 18446744073709551615
1162 20 1.8446744073709552e19 {"abc": 1.8446744073709552e19} 1.8446744073709552e19
1163 21 3.14 {"abc": 3.14} 3.14
1164 22 {} {"abc": {}} {}
1165 23 [] {"abc": []} []
1166 24 "2015-01-15 23:24:25.000000" {"abc": "2015-01-15 23:24:25.000000"} "2015-01-15 23:24:25.000000"
1167 25 "23:24:25.000000" {"abc": "23:24:25.000000"} "23:24:25.000000"
1168 26 "2015-01-15" {"abc": "2015-01-15"} "2015-01-15"
1169 27 "2015-01-15 23:24:25.000000" {"abc": "2015-01-15 23:24:25.000000"} "2015-01-15 23:24:25.000000"
1170 28 {"type": "Point", "coordinates": [1.0, 1.0]} {"abc": {"type": "Point", "coordinates": [1.0, 1.0]}} {"type": "Point", "coordinates": [1.0, 1.0]}
1171 29 [] {"abc": []} []
1172 30 "base64:type15:yv66vg==" {"abc": "base64:type15:yv66vg=="} "base64:type15:yv66vg=="
1173 include/sync_slave_sql_with_master.inc
1174 [Connection Slave]
1175 include/diff_tables.inc [master:t5, slave:t5]
1176 [Connection Master]
1177 DROP TABLE t1, t2, t3, t4, t5;
1178 include/sync_slave_sql_with_master.inc
1179 #
1180 # WL#7987: JSON AGGREGATION FUNCTIONS
1181 #
1182 # Create tables of various data types
1183 CREATE TABLE data_table (pkey INT PRIMARY KEY AUTO_INCREMENT,
1184 txt TEXT, vcol VARCHAR(20), num INT,
1185 gcol INT AS (num * 2) VIRTUAL);
1186 CREATE TABLE json_data (nkey INT PRIMARY KEY AUTO_INCREMENT, jcol JSON);
1187 # Insert values into the tables using the aggregation functions
1188 INSERT INTO data_table(txt, vcol, num) VALUES ('ailemac', 'namor', 15),
1189 ('nielk', 'uciov', 12),
1190 ('nuicarc', 'ierdna', 13),
1191 ('ihcseruj', 'elisav', 14),
1192 ('qweasdas', 'jugcvb', 16);
1193 INSERT INTO json_data(jcol) SELECT JSON_OBJECTAGG(pkey, gcol) FROM data_table;
1194 INSERT INTO json_data(jcol) SELECT JSON_ARRAYAGG(txt) FROM data_table;
1195 INSERT INTO json_data(jcol) SELECT JSON_ARRAYAGG(gcol) FROM data_table;
1196 INSERT INTO json_data(jcol) SELECT JSON_OBJECTAGG(num , vcol) FROM data_table;
1197 INSERT INTO json_data(jcol) SELECT JSON_OBJECTAGG(pkey , txt) FROM data_table;
1198 INSERT INTO json_data(jcol) SELECT JSON_ARRAYAGG(pkey) FROM data_table;
1199 include/sync_slave_sql_with_master.inc
1200 [Connection Slave]
1201 # Check whether tables were created on the slave
1202 SELECT * FROM json_data;
1203 nkey jcol
1204 1 {"1": 30, "2": 24, "3": 26, "4": 28, "5": 32}
1205 2 ["ailemac", "nielk", "nuicarc", "ihcseruj", "qweasdas"]
1206 3 [30, 24, 26, 28, 32]
1207 4 {"12": "uciov", "13": "ierdna", "14": "elisav", "15": "namor", "16": "jugcvb"}
1208 5 {"1": "ailemac", "2": "nielk", "3": "nuicarc", "4": "ihcseruj", "5": "qweasdas"}
1209 6 [1, 2, 3, 4, 5]
1210 SELECT JSON_OBJECTAGG(t1.pkey, t2.jcol) FROM data_table AS t1, json_data AS t2;
1211 JSON_OBJECTAGG(t1.pkey, t2.jcol)
1212 {"1": [1, 2, 3, 4, 5], "2": [1, 2, 3, 4, 5], "3": [1, 2, 3, 4, 5], "4": [1, 2, 3, 4, 5], "5": [1, 2, 3, 4, 5]}
1213 SELECT JSON_ARRAYAGG(jcol) FROM json_data;
1214 JSON_ARRAYAGG(jcol)
1215 [{"1": 30, "2": 24, "3": 26, "4": 28, "5": 32}, ["ailemac", "nielk", "nuicarc", "ihcseruj", "qweasdas"], [30, 24, 26, 28, 32], {"12": "uciov", "13": "ierdna", "14": "elisav", "15": "namor", "16": "jugcvb"}, {"1": "ailemac", "2": "nielk", "3": "nuicarc", "4": "ihcseruj", "5": "qweasdas"}, [1, 2, 3, 4, 5]]
1216 include/diff_tables.inc [master:json_data, slave:json_data]
1217 [Connection Master]
1218 # Update values in the table
1219 UPDATE json_data SET jcol= (SELECT JSON_ARRAYAGG(vcol) FROM data_table) WHERE nkey = 1;
1220 include/sync_slave_sql_with_master.inc
1221 [Connection Slave]
1222 SELECT * FROM json_data WHERE nkey = 1;
1223 nkey jcol
1224 1 ["namor", "uciov", "ierdna", "elisav", "jugcvb"]
1225 include/diff_tables.inc [master:json_data, slave:json_data]
1226 [Connection Master]
1227 # Delete values from the table
1228 DELETE FROM json_data WHERE jcol = (SELECT JSON_ARRAYAGG(pkey) FROM data_table);
1229 include/sync_slave_sql_with_master.inc
1230 [Connection Slave]
1231 SELECT * FROM json_data;
1232 nkey jcol
1233 1 ["namor", "uciov", "ierdna", "elisav", "jugcvb"]
1234 2 ["ailemac", "nielk", "nuicarc", "ihcseruj", "qweasdas"]
1235 3 [30, 24, 26, 28, 32]
1236 4 {"12": "uciov", "13": "ierdna", "14": "elisav", "15": "namor", "16": "jugcvb"}
1237 5 {"1": "ailemac", "2": "nielk", "3": "nuicarc", "4": "ihcseruj", "5": "qweasdas"}
1238 include/diff_tables.inc [master:json_data, slave:json_data]
1239 [Connection Master]
1240 DROP TABLE data_table;
1241 DROP TABLE json_data;
1242 include/sync_slave_sql_with_master.inc
1243 #
1244 # WL#9191: ADD JSON_PRETTY FUNCTION
1245 #
1246 [Connection Master]
1247 # Create tables of various data types
1248 CREATE TABLE data (pkey INT PRIMARY KEY AUTO_INCREMENT,
1249 txt TEXT, vcol VARCHAR(20), num INT,
1250 gcol INT AS (num * 2) VIRTUAL,
1251 gcol2 JSON AS (JSON_PRETTY(JSON_OBJECT(num,gcol))));
1252 CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT, jcol TEXT, jgcol TEXT AS (JSON_PRETTY(jcol)));
1253 # Insert values into the tables using the pretty function
1254 INSERT INTO data (txt, vcol, num) VALUES ('abc', 'namotgr', 150),
1255 ('fwjh4', 'ucierov', 142),
1256 ('8942rhkjh', 'roiu3r', 913),
1257 ('imfmf', 'r3jr2', 15),
1258 ('32rj2jr', 'r2ihrhr', 32);
1259 INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_OBJECTAGG(pkey, gcol)) FROM data;
1260 INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_ARRAYAGG(txt)) FROM data;
1261 INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_PRETTY(gcol2)) FROM data;
1262 INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_OBJECT(num , vcol)) FROM data;
1263 INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_OBJECTAGG(pkey , txt)) FROM data;
1264 INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_ARRAY(gcol)) FROM data;
1265 include/sync_slave_sql_with_master.inc
1266 [Connection Slave]
1267 # Check whether tables were created on the slave
1268 SELECT * FROM json_data;
1269 pk jcol jgcol
1270 1 {
1271 "1": 300,
1272 "2": 284,
1273 "3": 1826,
1274 "4": 30,
1275 "5": 64
1276 } {
1277 "1": 300,
1278 "2": 284,
1279 "3": 1826,
1280 "4": 30,
1281 "5": 64
1282 }
1283 2 [
1284 "abc",
1285 "fwjh4",
1286 "8942rhkjh",
1287 "imfmf",
1288 "32rj2jr"
1289 ] [
1290 "abc",
1291 "fwjh4",
1292 "8942rhkjh",
1293 "imfmf",
1294 "32rj2jr"
1295 ]
1296 3 {
1297 "150": 300
1298 } {
1299 "150": 300
1300 }
1301 4 {
1302 "142": 284
1303 } {
1304 "142": 284
1305 }
1306 5 {
1307 "913": 1826
1308 } {
1309 "913": 1826
1310 }
1311 6 {
1312 "15": 30
1313 } {
1314 "15": 30
1315 }
1316 7 {
1317 "32": 64
1318 } {
1319 "32": 64
1320 }
1321 10 {
1322 "150": "namotgr"
1323 } {
1324 "150": "namotgr"
1325 }
1326 11 {
1327 "142": "ucierov"
1328 } {
1329 "142": "ucierov"
1330 }
1331 12 {
1332 "913": "roiu3r"
1333 } {
1334 "913": "roiu3r"
1335 }
1336 13 {
1337 "15": "r3jr2"
1338 } {
1339 "15": "r3jr2"
1340 }
1341 14 {
1342 "32": "r2ihrhr"
1343 } {
1344 "32": "r2ihrhr"
1345 }
1346 17 {
1347 "1": "abc",
1348 "2": "fwjh4",
1349 "3": "8942rhkjh",
1350 "4": "imfmf",
1351 "5": "32rj2jr"
1352 } {
1353 "1": "abc",
1354 "2": "fwjh4",
1355 "3": "8942rhkjh",
1356 "4": "imfmf",
1357 "5": "32rj2jr"
1358 }
1359 18 [
1360 300
1361 ] [
1362 300
1363 ]
1364 19 [
1365 284
1366 ] [
1367 284
1368 ]
1369 20 [
1370 1826
1371 ] [
1372 1826
1373 ]
1374 21 [
1375 30
1376 ] [
1377 30
1378 ]
1379 22 [
1380 64
1381 ] [
1382 64
1383 ]
1384 SELECT gcol2 FROM data;
1385 gcol2
1386 {"150": 300}
1387 {"142": 284}
1388 {"913": 1826}
1389 {"15": 30}
1390 {"32": 64}
1391 include/diff_tables.inc [master:json_data, slave:json_data]
1392 include/diff_tables.inc [master:data, slave:data]
1393 [Connection Master]
1394 # Update values in the table
1395 UPDATE json_data SET jcol= (SELECT JSON_PRETTY(JSON_ARRAYAGG(gcol2)) FROM data) WHERE pk= 2;
1396 UPDATE json_data SET jcol= (SELECT JSON_PRETTY(JSON_OBJECTAGG(pk,gcol)) FROM data) WHERE pk = 1;
1397 include/sync_slave_sql_with_master.inc
1398 [Connection Slave]
1399 SELECT * FROM json_data;
1400 pk jcol jgcol
1401 1 {
1402 "1": 64
1403 } {
1404 "1": 64
1405 }
1406 2 [
1407 {
1408 "150": 300
1409 },
1410 {
1411 "142": 284
1412 },
1413 {
1414 "913": 1826
1415 },
1416 {
1417 "15": 30
1418 },
1419 {
1420 "32": 64
1421 }
1422 ] [
1423 {
1424 "150": 300
1425 },
1426 {
1427 "142": 284
1428 },
1429 {
1430 "913": 1826
1431 },
1432 {
1433 "15": 30
1434 },
1435 {
1436 "32": 64
1437 }
1438 ]
1439 3 {
1440 "150": 300
1441 } {
1442 "150": 300
1443 }
1444 4 {
1445 "142": 284
1446 } {
1447 "142": 284
1448 }
1449 5 {
1450 "913": 1826
1451 } {
1452 "913": 1826
1453 }
1454 6 {
1455 "15": 30
1456 } {
1457 "15": 30
1458 }
1459 7 {
1460 "32": 64
1461 } {
1462 "32": 64
1463 }
1464 10 {
1465 "150": "namotgr"
1466 } {
1467 "150": "namotgr"
1468 }
1469 11 {
1470 "142": "ucierov"
1471 } {
1472 "142": "ucierov"
1473 }
1474 12 {
1475 "913": "roiu3r"
1476 } {
1477 "913": "roiu3r"
1478 }
1479 13 {
1480 "15": "r3jr2"
1481 } {
1482 "15": "r3jr2"
1483 }
1484 14 {
1485 "32": "r2ihrhr"
1486 } {
1487 "32": "r2ihrhr"
1488 }
1489 17 {
1490 "1": "abc",
1491 "2": "fwjh4",
1492 "3": "8942rhkjh",
1493 "4": "imfmf",
1494 "5": "32rj2jr"
1495 } {
1496 "1": "abc",
1497 "2": "fwjh4",
1498 "3": "8942rhkjh",
1499 "4": "imfmf",
1500 "5": "32rj2jr"
1501 }
1502 18 [
1503 300
1504 ] [
1505 300
1506 ]
1507 19 [
1508 284
1509 ] [
1510 284
1511 ]
1512 20 [
1513 1826
1514 ] [
1515 1826
1516 ]
1517 21 [
1518 30
1519 ] [
1520 30
1521 ]
1522 22 [
1523 64
1524 ] [
1525 64
1526 ]
1527 include/diff_tables.inc [master:json_data, slave:json_data]
1528 [Connection Master]
1529 # Delete values from the table
1530 DELETE FROM json_data WHERE JSON_PRETTY(jgcol) = (SELECT JSON_PRETTY(JSON_OBJECTAGG(pkey,txt)) FROM data);
1531 include/sync_slave_sql_with_master.inc
1532 [Connection Slave]
1533 SELECT * FROM json_data;
1534 pk jcol jgcol
1535 1 {
1536 "1": 64
1537 } {
1538 "1": 64
1539 }
1540 2 [
1541 {
1542 "150": 300
1543 },
1544 {
1545 "142": 284
1546 },
1547 {
1548 "913": 1826
1549 },
1550 {
1551 "15": 30
1552 },
1553 {
1554 "32": 64
1555 }
1556 ] [
1557 {
1558 "150": 300
1559 },
1560 {
1561 "142": 284
1562 },
1563 {
1564 "913": 1826
1565 },
1566 {
1567 "15": 30
1568 },
1569 {
1570 "32": 64
1571 }
1572 ]
1573 3 {
1574 "150": 300
1575 } {
1576 "150": 300
1577 }
1578 4 {
1579 "142": 284
1580 } {
1581 "142": 284
1582 }
1583 5 {
1584 "913": 1826
1585 } {
1586 "913": 1826
1587 }
1588 6 {
1589 "15": 30
1590 } {
1591 "15": 30
1592 }
1593 7 {
1594 "32": 64
1595 } {
1596 "32": 64
1597 }
1598 10 {
1599 "150": "namotgr"
1600 } {
1601 "150": "namotgr"
1602 }
1603 11 {
1604 "142": "ucierov"
1605 } {
1606 "142": "ucierov"
1607 }
1608 12 {
1609 "913": "roiu3r"
1610 } {
1611 "913": "roiu3r"
1612 }
1613 13 {
1614 "15": "r3jr2"
1615 } {
1616 "15": "r3jr2"
1617 }
1618 14 {
1619 "32": "r2ihrhr"
1620 } {
1621 "32": "r2ihrhr"
1622 }
1623 18 [
1624 300
1625 ] [
1626 300
1627 ]
1628 19 [
1629 284
1630 ] [
1631 284
1632 ]
1633 20 [
1634 1826
1635 ] [
1636 1826
1637 ]
1638 21 [
1639 30
1640 ] [
1641 30
1642 ]
1643 22 [
1644 64
1645 ] [
1646 64
1647 ]
1648 include/diff_tables.inc [master:json_data, slave:json_data]
1649 [Connection Master]
1650 DROP TABLE data;
1651 DROP TABLE json_data;
1652 include/sync_slave_sql_with_master.inc
1653 #
1654 # WL#8963: SUPPORT FOR PARTIAL UPDATE OF JSON IN THE OPTIMIZER
1655 # WL#9192: ADD JSON_STORAGE_SIZE / JSON_STORAGE_FREE FUNCTIONS
1656 #
1657 [Connection Master]
1658 # Create table containing various types of data
1659 CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT,
1660 id INT,
1661 jd JSON,
1662 td TEXT,
1663 gcol INT AS (JSON_STORAGE_SIZE(jd)),
1664 gcol2 INT AS (JSON_STORAGE_FREE(jd)) VIRTUAL,
1665 gcol3 JSON AS (JSON_ARRAY(id,jd,td,gcol,gcol2)),
1666 gcol4 JSON AS (JSON_OBJECT(id,jd,gcol,td)) VIRTUAL);
1667 # Insert data into the table
1668 INSERT INTO json_data(id,jd,td) VALUES(10, '{"a":1}', 'characters'),
1669 (20, '[10,20,30]', 'alphabet'),
1670 (30, '[{"a":"abc"},{"b":"abcd"}]', 'words'),
1671 (40, '{"key":["array", "of", "strings"]}', 'letters'),
1672 (50, '{"key":[{"key":"value"},{"key":"value2"}]}', 'strings');
1673 include/sync_slave_sql_with_master.inc
1674 [Connection Slave]
1675 # Check whether table was created on the slave
1676 SELECT * FROM json_data;
1677 pk id jd td gcol gcol2 gcol3 gcol4
1678 1 10 {"a": 1} characters 13 0 [10, {"a": 1}, "characters", 13, 0] {"10": {"a": 1}, "13": "characters"}
1679 2 20 [10, 20, 30] alphabet 14 0 [20, [10, 20, 30], "alphabet", 14, 0] {"14": "alphabet", "20": [10, 20, 30]}
1680 3 30 [{"a": "abc"}, {"b": "abcd"}] words 44 0 [30, [{"a": "abc"}, {"b": "abcd"}], "words", 44, 0] {"30": [{"a": "abc"}, {"b": "abcd"}], "44": "words"}
1681 4 40 {"key": ["array", "of", "strings"]} letters 45 0 [40, {"key": ["array", "of", "strings"]}, "letters", 45, 0] {"40": {"key": ["array", "of", "strings"]}, "45": "letters"}
1682 5 50 {"key": [{"key": "value"}, {"key": "value2"}]} strings 66 0 [50, {"key": [{"key": "value"}, {"key": "value2"}]}, "strings", 66, 0] {"50": {"key": [{"key": "value"}, {"key": "value2"}]}, "66": "strings"}
1683 include/diff_tables.inc [master:json_data, slave:json_data]
1684 [Connection Master]
1685 # Update values in the table
1686 UPDATE json_data SET jd = JSON_REPLACE(jd, '$[0].a',"a");
1687 include/sync_slave_sql_with_master.inc
1688 [Connection Slave]
1689 # Check whether values were updated
1690 SELECT * FROM json_data;
1691 pk id jd td gcol gcol2 gcol3 gcol4
1692 1 10 {"a": "a"} characters 15 0 [10, {"a": "a"}, "characters", 15, 0] {"10": {"a": "a"}, "15": "characters"}
1693 2 20 [10, 20, 30] alphabet 14 0 [20, [10, 20, 30], "alphabet", 14, 0] {"14": "alphabet", "20": [10, 20, 30]}
1694 3 30 [{"a": "a"}, {"b": "abcd"}] words 44 2 [30, [{"a": "a"}, {"b": "abcd"}], "words", 44, 2] {"30": [{"a": "a"}, {"b": "abcd"}], "44": "words"}
1695 4 40 {"key": ["array", "of", "strings"]} letters 45 0 [40, {"key": ["array", "of", "strings"]}, "letters", 45, 0] {"40": {"key": ["array", "of", "strings"]}, "45": "letters"}
1696 5 50 {"key": [{"key": "value"}, {"key": "value2"}]} strings 66 0 [50, {"key": [{"key": "value"}, {"key": "value2"}]}, "strings", 66, 0] {"50": {"key": [{"key": "value"}, {"key": "value2"}]}, "66": "strings"}
1697 SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data;
1698 JSON_STORAGE_SIZE(gcol3) JSON_STORAGE_SIZE(gcol4)
1699 45 48
1700 42 45
1701 67 70
1702 72 75
1703 93 96
1704 SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data;
1705 JSON_STORAGE_FREE(gcol3) JSON_STORAGE_FREE(gcol4)
1706 0 0
1707 0 0
1708 0 0
1709 0 0
1710 0 0
1711 include/diff_tables.inc [master:json_data, slave:json_data]
1712 [Connection Master]
1713 # Update more values in the table
1714 UPDATE json_data SET jd = JSON_SET(jd, '$.key[0]', "ar");
1715 include/sync_slave_sql_with_master.inc
1716 [Connection Slave]
1717 # Check whether values were updated
1718 SELECT * FROM json_data;
1719 pk id jd td gcol gcol2 gcol3 gcol4
1720 1 10 {"a": "a"} characters 15 0 [10, {"a": "a"}, "characters", 15, 0] {"10": {"a": "a"}, "15": "characters"}
1721 2 20 [10, 20, 30] alphabet 14 0 [20, [10, 20, 30], "alphabet", 14, 0] {"14": "alphabet", "20": [10, 20, 30]}
1722 3 30 [{"a": "a"}, {"b": "abcd"}] words 44 2 [30, [{"a": "a"}, {"b": "abcd"}], "words", 44, 2] {"30": [{"a": "a"}, {"b": "abcd"}], "44": "words"}
1723 4 40 {"key": ["ar", "of", "strings"]} letters 45 3 [40, {"key": ["ar", "of", "strings"]}, "letters", 45, 3] {"40": {"key": ["ar", "of", "strings"]}, "45": "letters"}
1724 5 50 {"key": ["ar", {"key": "value2"}]} strings 66 17 [50, {"key": ["ar", {"key": "value2"}]}, "strings", 66, 17] {"50": {"key": ["ar", {"key": "value2"}]}, "66": "strings"}
1725 SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data;
1726 JSON_STORAGE_SIZE(gcol3) JSON_STORAGE_SIZE(gcol4)
1727 45 48
1728 42 45
1729 67 70
1730 69 72
1731 76 79
1732 SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data;
1733 JSON_STORAGE_FREE(gcol3) JSON_STORAGE_FREE(gcol4)
1734 0 0
1735 0 0
1736 0 0
1737 0 0
1738 0 0
1739 include/diff_tables.inc [master:json_data, slave:json_data]
1740 [Connection Master]
1741 # Update values again
1742 UPDATE json_data SET jd = JSON_REPLACE(jd, '$.key', "hello");
1743 include/sync_slave_sql_with_master.inc
1744 [Connection Slave]
1745 # Check whether values were updated
1746 SELECT * FROM json_data;
1747 pk id jd td gcol gcol2 gcol3 gcol4
1748 1 10 {"a": "a"} characters 15 0 [10, {"a": "a"}, "characters", 15, 0] {"10": {"a": "a"}, "15": "characters"}
1749 2 20 [10, 20, 30] alphabet 14 0 [20, [10, 20, 30], "alphabet", 14, 0] {"14": "alphabet", "20": [10, 20, 30]}
1750 3 30 [{"a": "a"}, {"b": "abcd"}] words 44 2 [30, [{"a": "a"}, {"b": "abcd"}], "words", 44, 2] {"30": [{"a": "a"}, {"b": "abcd"}], "44": "words"}
1751 4 40 {"key": "hello"} letters 45 24 [40, {"key": "hello"}, "letters", 45, 24] {"40": {"key": "hello"}, "45": "letters"}
1752 5 50 {"key": "hello"} strings 66 45 [50, {"key": "hello"}, "strings", 66, 45] {"50": {"key": "hello"}, "66": "strings"}
1753 SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data;
1754 JSON_STORAGE_SIZE(gcol3) JSON_STORAGE_SIZE(gcol4)
1755 45 48
1756 42 45
1757 67 70
1758 48 51
1759 48 51
1760 SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data;
1761 JSON_STORAGE_FREE(gcol3) JSON_STORAGE_FREE(gcol4)
1762 0 0
1763 0 0
1764 0 0
1765 0 0
1766 0 0
1767 include/diff_tables.inc [master:json_data, slave:json_data]
1768 [Connection Master]
1769 # Update values such that they can fill up the "free" space
1770 UPDATE json_data SET jd= JSON_SET(jd, '$.key', '["h","e","l","l","o"]');
1771 include/sync_slave_sql_with_master.inc
1772 [Connection Slave]
1773 # Check whether values were updated
1774 SELECT * FROM json_data;
1775 pk id jd td gcol gcol2 gcol3 gcol4
1776 1 10 {"a": "a", "key": "[\"h\",\"e\",\"l\",\"l\",\"o\"]"} characters 47 0 [10, {"a": "a", "key": "[\"h\",\"e\",\"l\",\"l\",\"o\"]"}, "characters", 47, 0] {"10": {"a": "a", "key": "[\"h\",\"e\",\"l\",\"l\",\"o\"]"}, "47": "characters"}
1777 2 20 [10, 20, 30] alphabet 14 0 [20, [10, 20, 30], "alphabet", 14, 0] {"14": "alphabet", "20": [10, 20, 30]}
1778 3 30 [{"a": "a"}, {"b": "abcd"}] words 44 2 [30, [{"a": "a"}, {"b": "abcd"}], "words", 44, 2] {"30": [{"a": "a"}, {"b": "abcd"}], "44": "words"}
1779 4 40 {"key": "[\"h\",\"e\",\"l\",\"l\",\"o\"]"} letters 45 8 [40, {"key": "[\"h\",\"e\",\"l\",\"l\",\"o\"]"}, "letters", 45, 8] {"40": {"key": "[\"h\",\"e\",\"l\",\"l\",\"o\"]"}, "45": "letters"}
1780 5 50 {"key": "[\"h\",\"e\",\"l\",\"l\",\"o\"]"} strings 66 29 [50, {"key": "[\"h\",\"e\",\"l\",\"l\",\"o\"]"}, "strings", 66, 29] {"50": {"key": "[\"h\",\"e\",\"l\",\"l\",\"o\"]"}, "66": "strings"}
1781 SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data;
1782 JSON_STORAGE_SIZE(gcol3) JSON_STORAGE_SIZE(gcol4)
1783 77 80
1784 42 45
1785 67 70
1786 64 67
1787 64 67
1788 SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data;
1789 JSON_STORAGE_FREE(gcol3) JSON_STORAGE_FREE(gcol4)
1790 0 0
1791 0 0
1792 0 0
1793 0 0
1794 0 0
1795 [connection master]
1796 # Update via an updatable view.
1797 CREATE VIEW json_data_view AS SELECT * FROM json_data;
1798 UPDATE json_data_view SET jd = JSON_SET(jd, '$.key', '[]'),
1799 jd = JSON_REMOVE(jd, '$[0].a');
1800 include/sync_slave_sql_with_master.inc
1801 [Connection Slave]
1802 # Check whether values were updated
1803 SELECT * FROM json_data;
1804 pk id jd td gcol gcol2 gcol3 gcol4
1805 1 10 {"key": "[]"} characters 47 29 [10, {"key": "[]"}, "characters", 47, 29] {"10": {"key": "[]"}, "47": "characters"}
1806 2 20 [10, 20, 30] alphabet 14 0 [20, [10, 20, 30], "alphabet", 14, 0] {"14": "alphabet", "20": [10, 20, 30]}
1807 3 30 [{}, {"b": "abcd"}] words 44 12 [30, [{}, {"b": "abcd"}], "words", 44, 12] {"30": [{}, {"b": "abcd"}], "44": "words"}
1808 4 40 {"key": "[]"} letters 45 27 [40, {"key": "[]"}, "letters", 45, 27] {"40": {"key": "[]"}, "45": "letters"}
1809 5 50 {"key": "[]"} strings 66 48 [50, {"key": "[]"}, "strings", 66, 48] {"50": {"key": "[]"}, "66": "strings"}
1810 SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data;
1811 JSON_STORAGE_SIZE(gcol3) JSON_STORAGE_SIZE(gcol4)
1812 48 51
1813 42 45
1814 57 60
1815 45 48
1816 45 48
1817 SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data;
1818 JSON_STORAGE_FREE(gcol3) JSON_STORAGE_FREE(gcol4)
1819 0 0
1820 0 0
1821 0 0
1822 0 0
1823 0 0
1824 include/diff_tables.inc [master:json_data, slave:json_data]
1825 [Connection Master]
1826 # Clean up
1827 DROP VIEW json_data_view;
1828 DROP TABLE json_data;
1829 include/sync_slave_sql_with_master.inc
1830 #
1831 # WL#9831: RANGES IN JSON PATH EXPRESSIONS
1832 #
1833 [Connection Master]
1834 # Create table with JSON data
1835 CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT,
1836 id INT,
1837 jd JSON,
1838 td TEXT,
1839 gcol JSON AS (JSON_EXTRACT(jd, '$[last -2 to last]')) VIRTUAL,
1840 gcol2 VARCHAR(100) AS (JSON_EXTRACT(td, '$[0 to 3]')) STORED,
1841 gcol3 JSON AS (JSON_EXTRACT(JSON_ARRAY(id,jd,td,gcol,gcol2),
1842 '$[2][last -3 to last -1]')) VIRTUAL,
1843 gcol4 JSON AS (JSON_EXTRACT(JSON_OBJECT(id,jd,gcol,td),
1844 '$.id[0 to last]')) STORED,
1845 key(gcol2));
1846 # Insert data into the table
1847 INSERT INTO json_data(id,jd,td) VALUES(10, '["abc", "def", "ghi", "jkl", "mno"]',
1848 '["pqr", "stu", "vwx", "xyz"]'),
1849 (20, '[10, 20, 30, 40, 50]',
1850 '["alpha", "beta", "gamma", "delta"]'),
1851 (30, '["z", "y", "x", "w", "v"]',
1852 '["Bengaluru", "Mysuru", "Davangere", "Belagaum"]'),
1853 (40, '["array", "of", "strings", "and", "more"]',
1854 '[2, 3, 5, 7]'),
1855 (50, '[{"key":[{"key":"value"},{"key":"value2"}]},
1856 {"key":[{"key":"value3"},{"key":"value4"}]}]',
1857 '["N", "U", "L", "L"]');
1858 include/sync_slave_sql_with_master.inc
1859 [Connection Slave]
1860 # Check whether table was created on the slave
1861 SELECT * FROM json_data;
1862 pk id jd td gcol gcol2 gcol3 gcol4
1863 1 10 ["abc", "def", "ghi", "jkl", "mno"] ["pqr", "stu", "vwx", "xyz"] ["ghi", "jkl", "mno"] ["pqr", "stu", "vwx", "xyz"] NULL NULL
1864 2 20 [10, 20, 30, 40, 50] ["alpha", "beta", "gamma", "delta"] [30, 40, 50] ["alpha", "beta", "gamma", "delta"] NULL NULL
1865 3 30 ["z", "y", "x", "w", "v"] ["Bengaluru", "Mysuru", "Davangere", "Belagaum"] ["x", "w", "v"] ["Bengaluru", "Mysuru", "Davangere", "Belagaum"] NULL NULL
1866 4 40 ["array", "of", "strings", "and", "more"] [2, 3, 5, 7] ["strings", "and", "more"] [2, 3, 5, 7] NULL NULL
1867 5 50 [{"key": [{"key": "value"}, {"key": "value2"}]}, {"key": [{"key": "value3"}, {"key": "value4"}]}] ["N", "U", "L", "L"] [{"key": [{"key": "value"}, {"key": "value2"}]}, {"key": [{"key": "value3"}, {"key": "value4"}]}] ["N", "U", "L", "L"] NULL NULL
1868 include/diff_tables.inc [master:json_data, slave:json_data]
1869 [Connection Master]
1870 # Update values in the table
1871 UPDATE json_data SET jd = JSON_SET(jd, '$[1]', "replaced") WHERE
1872 JSON_CONTAINS(JSON_EXTRACT(jd, '$[0 to last]'), '[10, 20, 30, 40, 50]');
1873 include/sync_slave_sql_with_master.inc
1874 [Connection Slave]
1875 # Check whether values were updated
1876 SELECT * FROM json_data;
1877 pk id jd td gcol gcol2 gcol3 gcol4
1878 1 10 ["abc", "def", "ghi", "jkl", "mno"] ["pqr", "stu", "vwx", "xyz"] ["ghi", "jkl", "mno"] ["pqr", "stu", "vwx", "xyz"] NULL NULL
1879 2 20 [10, "replaced", 30, 40, 50] ["alpha", "beta", "gamma", "delta"] [30, 40, 50] ["alpha", "beta", "gamma", "delta"] NULL NULL
1880 3 30 ["z", "y", "x", "w", "v"] ["Bengaluru", "Mysuru", "Davangere", "Belagaum"] ["x", "w", "v"] ["Bengaluru", "Mysuru", "Davangere", "Belagaum"] NULL NULL
1881 4 40 ["array", "of", "strings", "and", "more"] [2, 3, 5, 7] ["strings", "and", "more"] [2, 3, 5, 7] NULL NULL
1882 5 50 [{"key": [{"key": "value"}, {"key": "value2"}]}, {"key": [{"key": "value3"}, {"key": "value4"}]}] ["N", "U", "L", "L"] [{"key": [{"key": "value"}, {"key": "value2"}]}, {"key": [{"key": "value3"}, {"key": "value4"}]}] ["N", "U", "L", "L"] NULL NULL
1883 SELECT JSON_EXTRACT(td, '$[0 to last]') FROM json_data;
1884 JSON_EXTRACT(td, '$[0 to last]')
1885 ["pqr", "stu", "vwx", "xyz"]
1886 ["alpha", "beta", "gamma", "delta"]
1887 ["Bengaluru", "Mysuru", "Davangere", "Belagaum"]
1888 [2, 3, 5, 7]
1889 ["N", "U", "L", "L"]
1890 [Connection Master]
1891 # Clean up
1892 DROP TABLE json_data;
1893 include/sync_slave_sql_with_master.inc
1894 #
1895 # WL#9692: ADD JSON_MERGE_PATCH, RENAME JSON_MERGE TO JSON_MERGE_PRESERVE
1896 #
1897 [Connection Master]
1898 # Create table with JSON data
1899 CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT,
1900 id INT,
1901 jd JSON,
1902 td TEXT,
1903 gcol VARCHAR(30) AS (JSON_EXTRACT(JSON_MERGE_PATCH(jd,JSON_OBJECT(id,td)), '$.a')) VIRTUAL,
1904 KEY(gcol));
1905 # Insert data into the table
1906 INSERT INTO json_data(id,jd,td) VALUES(10,'["a","b","c","d","e"]', "pink"),
1907 (20,'{"a":[1,2,6,7,9]}', "floyd"),
1908 (30,'{"a":["ears","nose","eyes"]}', "megadeth"),
1909 (40,'[null,null,"null",0,1]', "def"),
1910 (50,'{"a":null}', "leppard");
1911 include/sync_slave_sql_with_master.inc
1912 [Connection Slave]
1913 # Check whether table was created on the slave
1914 SELECT * FROM json_data;
1915 pk id jd td gcol
1916 1 10 ["a", "b", "c", "d", "e"] pink NULL
1917 2 20 {"a": [1, 2, 6, 7, 9]} floyd [1, 2, 6, 7, 9]
1918 3 30 {"a": ["ears", "nose", "eyes"]} megadeth ["ears", "nose", "eyes"]
1919 4 40 [null, null, "null", 0, 1] def NULL
1920 5 50 {"a": null} leppard null
1921 include/diff_tables.inc [master:json_data, slave:json_data]
1922 [Connection Master]
1923 # Create tables using the JSON_MERGE_PATCH function
1924 CREATE TABLE json_data2(field1 JSON);
1925 INSERT INTO json_data2
1926 SELECT JSON_MERGE_PATCH(JSON_ARRAYAGG(jd), JSON_OBJECTAGG(pk, jd))
1927 FROM json_data;
1928 CREATE TABLE json_data3(field1 JSON);
1929 INSERT INTO json_data3
1930 SELECT JSON_MERGE_PATCH(JSON_OBJECTAGG(id, gcol), JSON_ARRAYAGG(td))
1931 FROM json_data;
1932 CREATE TABLE json_data4(field1 JSON);
1933 INSERT INTO json_data4
1934 SELECT JSON_MERGE_PATCH(JSON_OBJECT(id, jd), JSON_ARRAY(gcol))
1935 FROM json_data;
1936 include/sync_slave_sql_with_master.inc
1937 [Connection Slave]
1938 # Check whether the tables were created on the slave
1939 SELECT * FROM json_data2;
1940 field1
1941 {"1": ["a", "b", "c", "d", "e"], "2": {"a": [1, 2, 6, 7, 9]}, "3": {"a": ["ears", "nose", "eyes"]}, "4": [null, null, "null", 0, 1], "5": {}}
1942 SELECT * FROM json_data3;
1943 field1
1944 ["pink", "floyd", "megadeth", "def", "leppard"]
1945 SELECT * FROM json_data4;
1946 field1
1947 [null]
1948 ["[1, 2, 6, 7, 9]"]
1949 ["[\"ears\", \"nose\", \"eyes\"]"]
1950 [null]
1951 ["null"]
1952 SELECT JSON_MERGE_PATCH(t1.field1, t2.field1) FROM json_data2 as t1, json_data3 as t2;
1953 JSON_MERGE_PATCH(t1.field1, t2.field1)
1954 ["pink", "floyd", "megadeth", "def", "leppard"]
1955 SELECT JSON_MERGE_PRESERVE(t1.field1, t2.field1) FROM json_data3 as t1, json_data4 as t2;
1956 JSON_MERGE_PRESERVE(t1.field1, t2.field1)
1957 ["pink", "floyd", "megadeth", "def", "leppard", null]
1958 ["pink", "floyd", "megadeth", "def", "leppard", "[1, 2, 6, 7, 9]"]
1959 ["pink", "floyd", "megadeth", "def", "leppard", "[\"ears\", \"nose\", \"eyes\"]"]
1960 ["pink", "floyd", "megadeth", "def", "leppard", null]
1961 ["pink", "floyd", "megadeth", "def", "leppard", "null"]
1962 [Connection Master]
1963 # Update values in the table
1964 UPDATE json_data SET jd = JSON_SET(jd, '$[1]', JSON_MERGE_PATCH(JSON_ARRAY(pk),JSON_ARRAY(td))) WHERE id IN (10,40);
1965 include/sync_slave_sql_with_master.inc
1966 [Connection Slave]
1967 include/diff_tables.inc [master:json_data, slave:json_data]
1968 # Check whether values were updated
1969 SELECT * FROM json_data;
1970 pk id jd td gcol
1971 1 10 ["a", ["pink"], "c", "d", "e"] pink NULL
1972 2 20 {"a": [1, 2, 6, 7, 9]} floyd [1, 2, 6, 7, 9]
1973 3 30 {"a": ["ears", "nose", "eyes"]} megadeth ["ears", "nose", "eyes"]
1974 4 40 [null, ["def"], "null", 0, 1] def NULL
1975 5 50 {"a": null} leppard null
1976 [Connection Master]
1977 # Clean up
1978 DROP TABLE json_data, json_data2, json_data3, json_data4;
1979 include/sync_slave_sql_with_master.inc
1980 #
1981 # WL#8867: ADD JSON TABLE FUNCTIONS
1982 #
1983 [Connection Master]
1984 # Create table using data obtained from JSON_TABLE function
1985 CREATE TABLE json_data(j json);
1986 INSERT INTO json_data VALUES('{"a":1, "b":"trucks", "c":[1,2,3], "d":"taste", "e":"93-01-01", "f":"03:48:07"}');
1987 INSERT INTO json_data VALUES('{"a":2, "b":"rickshaws", "c":{"key":"value"}, "d":"smell" , "e":"12-11-12", "f":"09:09:10"}');
1988 INSERT INTO json_data VALUES('{"a":3, "b":"cars", "c":["a","b","c","d"], "d":"sight", "e":"01-01-01", "f":"05:18:12"}');
1989 INSERT INTO json_data VALUES('{"a":4, "b":"bikes", "c":{"a":[1,2]}, "d":"hearing", "e":"98-05-06", "f":"00:00:00" }');
1990 INSERT INTO json_data VALUES('{"a":5, "b":"mopeds", "c":["one", "two", "three"], "d":"touch", "e":"56-12-07", "f":"12:12:12"}');
1991 CREATE TABLE data(i INT, v VARCHAR(10), j JSON, b BLOB, d DATE, t TIME, n JSON);
1992 INSERT INTO data SELECT jint, jchar, jjson, jblob, jdate, jtime, njson FROM json_data,JSON_TABLE(j, '$' COLUMNS(o FOR ORDINALITY, jexst INT EXISTS PATH '$.g', jint INT PATH '$.a', jchar VARCHAR(10) PATH '$.b', jjson JSON PATH '$.c', jblob BLOB PATH '$.d', jdate DATE PATH '$.e', jtime TIME PATH '$.f', NESTED PATH '$.c' COLUMNS(njson JSON PATH '$[0]' DEFAULT '{"json":"json_val"}' ON EMPTY ))) AS alias;
1993 include/sync_slave_sql_with_master.inc
1994 [Connection Slave]
1995 # Check whether the table was created on the slave
1996 SELECT * FROM data;
1997 i v j b d t n
1998 1 trucks [1, 2, 3] taste 1993-01-01 03:48:07 1
1999 2 rickshaws {"key": "value"} smell 2012-11-12 09:09:10 {"key": "value"}
2000 3 cars ["a", "b", "c", "d"] sight 2001-01-01 05:18:12 "a"
2001 4 bikes {"a": [1, 2]} hearing 1998-05-06 00:00:00 {"a": [1, 2]}
2002 5 mopeds ["one", "two", "three"] touch 2056-12-07 12:12:12 "one"
2003 include/diff_tables.inc [master:data, slave:data]
2004 [Connection Master]
2005 # Update values in the table with data obtained from JSON_TABLE
2006 UPDATE data SET j = (SELECT ucol FROM json_data,JSON_TABLE(j, '$' COLUMNS(ucol JSON PATH '$', ucol2 INT PATH '$.a')) AS alias WHERE ucol2 = 2) WHERE i = 2;
2007 UPDATE data SET v = (SELECT ucol FROM JSON_TABLE('{"key":["update"]}', '$' COLUMNS(ucol VARCHAR(10) PATH '$.key[0]')) AS alias) WHERE i = 4;
2008 include/sync_slave_sql_with_master.inc
2009 [Connection Slave]
2010 # Check whether the values were updated
2011 SELECT j,v FROM data;
2012 j v
2013 [1, 2, 3] trucks
2014 {"a": 2, "b": "rickshaws", "c": {"key": "value"}, "d": "smell", "e": "12-11-12", "f": "09:09:10"} rickshaws
2015 ["a", "b", "c", "d"] cars
2016 {"a": [1, 2]} update
2017 ["one", "two", "three"] mopeds
2018 include/diff_tables.inc [master:data, slave:data]
2019 [Connection Master]
2020 # Delete values from the table
2021 DELETE FROM data WHERE i = (SELECT dcol FROM JSON_TABLE('{"key":["delete",1,"2"]}', '$' COLUMNS(dcol INT PATH '$.key[1]')) AS alias);
2022 include/sync_slave_sql_with_master.inc
2023 [Connection Slave]
2024 # Check whether the row was deleted
2025 SELECT * FROM data;
2026 i v j b d t n
2027 2 rickshaws {"a": 2, "b": "rickshaws", "c": {"key": "value"}, "d": "smell", "e": "12-11-12", "f": "09:09:10"} smell 2012-11-12 09:09:10 {"key": "value"}
2028 3 cars ["a", "b", "c", "d"] sight 2001-01-01 05:18:12 "a"
2029 4 update {"a": [1, 2]} hearing 1998-05-06 00:00:00 {"a": [1, 2]}
2030 5 mopeds ["one", "two", "three"] touch 2056-12-07 12:12:12 "one"
2031 include/diff_tables.inc [master:data, slave:data]
2032 [Connection Master]
2033 # cleanup
2034 DROP TABLE data,json_data;
2035 include/sync_slave_sql_with_master.inc
2036 #
2037 # Bug#26177130: SLAVE CAN'T FIND ROW USING HASH_SCAN
2038 # IF JSON HAS DIFFERENT BINARY FORMAT
2039 #
2040 [connection slave]
2041 include/stop_slave_sql.inc
2042 include/begin_replace_combination.inc [BOTH.binlog_format=row,mixed,statement -> GLOBAL.slave_rows_search_algorithms=HASH_SCAN,TABLE_SCAN,INDEX_SCAN]
2043 Warnings:
2044 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
2045 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
2046 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
2047 include/start_slave_sql.inc
2048 [connection master]
2049 SET @old_binlog_format = @@SESSION.binlog_format;
2050 Warnings:
2051 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
2052 SET SESSION binlog_format = ROW;
2053 Warnings:
2054 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
2055 [connection slave]
2056 include/stop_slave.inc
2057 include/start_slave.inc
2058 [connection master]
2059 CREATE TABLE t (j JSON);
2060 SET SQL_LOG_BIN = 0;
2061 INSERT INTO t VALUES ('["abc", "abcdefghij"]');
2062 UPDATE t SET j = JSON_SET(j, '$[0]', 'a');
2063 SET SQL_LOG_BIN = 1;
2064 SELECT j, JSON_STORAGE_SIZE(j), JSON_STORAGE_FREE(j) FROM t;
2065 j JSON_STORAGE_SIZE(j) JSON_STORAGE_FREE(j)
2066 ["a", "abcdefghij"] 26 2
2067 include/sync_slave_sql_with_master.inc
2068 INSERT INTO t VALUES ('["a", "abcdefghij"]');
2069 SELECT j, JSON_STORAGE_SIZE(j), JSON_STORAGE_FREE(j) FROM t;
2070 j JSON_STORAGE_SIZE(j) JSON_STORAGE_FREE(j)
2071 ["a", "abcdefghij"] 24 0
2072 # At this point, the master and the slave have the same JSON document,
2073 # but the binary representation is different (the master has a 'gap' due
2074 # to partial update).
2075 [connection master]
2076 DELETE FROM t;
2077 # Verify that the slave and the master are in sync after the DELETE.
2078 # Before the fix, the slave could not locate the row due with
2079 # slave_rows_search_algorithms=HASH_SCAN because of the difference in
2080 # the representation.
2081 include/sync_slave_sql_with_master.inc
2082 # Clean up
2083 [connection master]
2084 DROP TABLE t;
2085 include/sync_slave_sql_with_master.inc
2086 include/stop_slave.inc
2087 include/end_replace_combination.inc [BOTH.binlog_format=row,mixed,statement -> GLOBAL.slave_rows_search_algorithms=HASH_SCAN,TABLE_SCAN,INDEX_SCAN]
2088 Warnings:
2089 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
2090 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
2091 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
2092 include/start_slave.inc
2093 [connection master]
2094 SET SESSION binlog_format = @old_binlog_format;
2095 Warnings:
2096 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
2097 include/rpl_end.inc