Debian Codegraph @

mysql-test/suite/rpl/t/rpl_json.test

raw
1 # Created: 2014-06-26 Lalit Choudhary
2 # WL#7909
3 # JSON datatype and funtions testing with replication.
4
5 --let $skip_configuration_privilege_checks_user= 'skip'
6 --let $rpl_privilege_checks_user_grant_all= 1
7
8 --source include/master-slave.inc
9
10 --connection master
11 SET SESSION sql_log_bin= 0;
12 call mtr.add_suppression('.*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*');
13 SET SESSION sql_log_bin= 1;
14
15 --connection slave
16 SET SESSION sql_log_bin= 0;
17 call mtr.add_suppression('.*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*');
18 SET SESSION sql_log_bin= 1;
19
20 --connection master
21
22 --echo #
23 --echo # WL#8132 JSON datatype and binary storage format
24 --echo #
25
26 CREATE TABLE t1 (i INT PRIMARY KEY, j JSON);
27 INSERT INTO t1 VALUES (0, NULL);
28 INSERT INTO t1 VALUES (1, '{"a": 2}');
29 INSERT INTO t1 VALUES (2, '[1,2]');
30 INSERT INTO t1 VALUES (3, '{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}');
31 INSERT INTO t1 VALUES (4, '["here", ["I", "am"], "!!!"]');
32 INSERT INTO t1 VALUES (5, '"scalar string"');
33 INSERT INTO t1 VALUES (6, 'true');
34 INSERT INTO t1 VALUES (7, 'false');
35 INSERT INTO t1 VALUES (8, 'null');
36 INSERT INTO t1 VALUES (9, '-1');
37 INSERT INTO t1 VALUES (10, CAST(CAST(1 AS UNSIGNED) AS JSON));
38 INSERT INTO t1 VALUES (11, '32767');
39 INSERT INTO t1 VALUES (12, '32768');
40 INSERT INTO t1 VALUES (13, '-32768');
41 INSERT INTO t1 VALUES (14, '-32769');
42 INSERT INTO t1 VALUES (15, '2147483647');
43 INSERT INTO t1 VALUES (16, '2147483648');
44 INSERT INTO t1 VALUES (17, '-2147483648');
45 INSERT INTO t1 VALUES (18, '-2147483649');
46 INSERT INTO t1 VALUES (19, '18446744073709551615');
47 INSERT INTO t1 VALUES (20, '18446744073709551616');
48 INSERT INTO t1 VALUES (21, '3.14');
49 INSERT INTO t1 VALUES (22, '{}');
50 INSERT INTO t1 VALUES (23, '[]');
51 INSERT INTO t1 VALUES (24, CAST(CAST('2015-01-15 23:24:25' AS DATETIME) AS JSON));
52 INSERT INTO t1 VALUES (25, CAST(CAST('23:24:25' AS TIME) AS JSON));
53 INSERT INTO t1 VALUES (26, CAST(CAST('2015-01-15' AS DATE) AS JSON));
54 INSERT INTO t1 VALUES (27, CAST(TIMESTAMP'2015-01-15 23:24:25' AS JSON));
55 INSERT INTO t1 VALUES (28, CAST(ST_GeomFromText('POINT(1 1)') AS JSON));
56 INSERT INTO t1 VALUES (29, CAST('[]' AS CHAR CHARACTER SET 'ascii'));
57 INSERT INTO t1 VALUES (30, CAST(x'cafebabe' AS JSON));
58 SELECT * FROM t1 ORDER BY i;
59
60 --echo # Copy JSON values
61 CREATE TABLE t2 (i INT PRIMARY KEY, j JSON);
62 INSERT INTO t2 SELECT * FROM t1;
63 SELECT *, JSON_TYPE(j) FROM t2 ORDER BY i;
64
65 --echo # Convert JSON values to TEXT
66 CREATE TABLE t3 (i INT PRIMARY KEY, txt TEXT);
67 INSERT INTO t3 SELECT * FROM t1;
68 SELECT *, JSON_TYPE(txt) FROM t3 ORDER BY i;
69
70 --echo # Convert TEXT values back to JSON
71 CREATE TABLE t4 (i INT PRIMARY KEY, j JSON);
72 INSERT INTO t4 SELECT * FROM t3;
73 SELECT *, JSON_TYPE(j) FROM t4 ORDER BY i;
74
75 --echo #
76 --echo # WL#8249 JSON comparator
77 --echo #
78
79 CREATE TABLE t5 (id INT PRIMARY KEY AUTO_INCREMENT, j JSON, x INT);
80 INSERT INTO t5(j) VALUES (NULL), (CAST(0 AS JSON)), (CAST(1 AS JSON)),
81 (CAST(2 AS JSON)), (CAST(3 AS JSON)), (CAST(3.14 AS JSON)), ('3.14'),
82 ('"0"'), ('"1"'), ('"2"'), ('"3"'), ('true'), ('false'), ('"true"'),
83 ('"false"'), ('null'), ('"null"'), (JSON_ARRAY(1, 2, 3)), ('"[1, 2, 3]"'),
84 (JSON_ARRAY(1.0e0, 2.0e0, 3.0e0)), (JSON_ARRAY(1, 2, 3.1)),
85 (JSON_OBJECT()), (JSON_OBJECT('a', 'b')), (JSON_OBJECT('a', 'c'));
86 SELECT * FROM t5 ORDER BY id;
87
88 --echo # Expect a single row to be updated (integer 0)
89 UPDATE t5 SET x = 1 WHERE j = 0;
90 SELECT * FROM t5 WHERE x = 1 ORDER BY id;
91
92 --echo # Expect a single row to be updated (string "0")
93 UPDATE t5 SET x = 2 WHERE j = "0";
94 SELECT * FROM t5 WHERE x = 2 ORDER BY id;
95
96 --echo # Expect a single row to be updated (boolean true)
97 UPDATE t5 SET x = 3 WHERE j = true;
98 SELECT * FROM t5 WHERE x = 3 ORDER BY id;
99
100 --echo # Expect a single row to be updated (boolean false)
101 UPDATE t5 SET x = 4 WHERE j = false;
102 SELECT * FROM t5 WHERE x = 4 ORDER BY id;
103
104 --echo # Expect a single row to be updated (string "true")
105 UPDATE t5 SET x = 5 WHERE j = 'true';
106 SELECT * FROM t5 WHERE x = 5 ORDER BY id;
107
108 --echo # Expect a single row to be updated (string "false")
109 UPDATE t5 SET x = 6 WHERE j = 'false';
110 SELECT * FROM t5 WHERE x = 6 ORDER BY id;
111
112 --echo # Expect a single row to be updated (string "[1, 2, 3]")
113 UPDATE t5 SET x = 7 WHERE j = '[1, 2, 3]';
114 SELECT * FROM t5 WHERE x = 7 ORDER BY id;
115
116 --echo # Expect two rows to be updated (array [1, 2, 3])
117 UPDATE t5 SET x = 8 WHERE j = JSON_ARRAY(1, 2, 3);
118 SELECT * FROM t5 WHERE x = 8 ORDER BY id;
119
120 --echo # Expect two rows to be updated (number 3.14)
121 UPDATE t5 SET x = 9 WHERE j = 3.14;
122 SELECT * FROM t5 WHERE x = 9 ORDER BY id;
123
124 --echo # Expect no rows to be updated (string "3.14")
125 UPDATE t5 SET x = 10 WHERE j = '3.14';
126 SELECT * FROM t5 WHERE x = 10 ORDER BY id;
127
128 --echo # Expect a single row to be updated (object {"a":"b"})
129 UPDATE t5 SET x = 11 WHERE j = CAST('{"a":"b"}' AS JSON);
130 SELECT * FROM t5 WHERE x = 11 ORDER BY id;
131
132 --echo # Expect four rows to be updated
133 UPDATE t5 SET x = 12 WHERE JSON_EXTRACT(j, '$[0]') = 1;
134 SELECT * FROM t5 WHERE x = 12 ORDER BY id;
135
136 --echo # Expect a single row to be updated
137 UPDATE t5 SET x = 13 WHERE JSON_EXTRACT(j, '$[0]') = '1';
138 SELECT * FROM t5 WHERE x = 13 ORDER BY id;
139
140 --echo #
141 --echo # WL#8539 Ordering of scalar JSON values
142 --echo #
143
144 DELETE FROM t2;
145 INSERT INTO t2 SELECT i, j FROM t1 ORDER BY j, i;
146 INSERT INTO t2 SELECT i + 100, j FROM t1 ORDER BY j DESC, i;
147 INSERT INTO t2 SELECT id + 200, j FROM t5 ORDER BY j, id;
148 INSERT INTO t2 SELECT id + 300, j FROM t5 ORDER BY j DESC, id;
149 SELECT * FROM t2 ORDER BY i;
150
151 --echo #
152 --echo # WL#7909 Server side JSON functions
153 --echo #
154
155 ALTER TABLE t1 ADD COLUMN j2 JSON;
156
157 UPDATE t1 SET j2 = JSON_ARRAY(JSON_TYPE(j));
158 SELECT * FROM t1 ORDER BY i;
159
160 UPDATE t1 SET j2 = CAST(JSON_VALID(j) AS JSON);
161 SELECT * FROM t1 ORDER BY i;
162
163 UPDATE t1 SET j2 = JSON_KEYS(j);
164 SELECT * FROM t1 ORDER BY i;
165
166 UPDATE t1 SET j2 = JSON_ARRAY(j,j,j);
167 SELECT * FROM t1 ORDER BY i;
168
169 UPDATE t1 SET j2 = JSON_EXTRACT(j2, '$[1]');
170 SELECT * FROM t1 ORDER BY i;
171
172 UPDATE t1 SET j2 = JSON_REMOVE(j, '$[1]');
173 SELECT * FROM t1 ORDER BY i;
174
175 UPDATE t1 SET j2 = JSON_ARRAY_APPEND(j, '$[1]', 'abc');
176 SELECT * FROM t1 ORDER BY i;
177
178 UPDATE t1 SET j2 = JSON_SET(j, '$[1]', 'abc');
179 SELECT * FROM t1 ORDER BY i;
180
181 UPDATE t1 SET j2 = JSON_INSERT(j, '$[1]', 'abc');
182 SELECT * FROM t1 ORDER BY i;
183
184 UPDATE t1 SET j2 = JSON_REPLACE(j, '$[1]', 'abc');
185 SELECT * FROM t1 ORDER BY i;
186
187 UPDATE t1 SET j2 = JSON_MERGE_PATCH(j, '{"a": "patched", "c": null}');
188 SELECT * FROM t1 ORDER BY i;
189
190 UPDATE t1 SET j2 = JSON_MERGE_PRESERVE(j, j);
191 SELECT * FROM t1 ORDER BY i;
192
193 UPDATE t1 SET j2 = JSON_SEARCH(j, 'one', 'abc');
194 SELECT * FROM t1 ORDER BY i;
195
196 UPDATE t1 SET j2 = CAST(JSON_CONTAINS(j, '[1]') AS JSON);
197 SELECT * FROM t1 ORDER BY i;
198
199 UPDATE t1 SET j2 = CAST(JSON_CONTAINS_PATH(j, 'one', '$.a') AS JSON);
200 SELECT * FROM t1 ORDER BY i;
201
202 UPDATE t1 SET j2 = CAST(JSON_LENGTH(j) AS JSON);
203 SELECT * FROM t1 ORDER BY i;
204
205 UPDATE t1 SET j2 = CAST(JSON_DEPTH(j) AS JSON);
206 SELECT * FROM t1 ORDER BY i;
207
208 UPDATE t1 SET j2 = JSON_OBJECT('a', j, 'b', j);
209 SELECT * FROM t1 ORDER BY i;
210
211 UPDATE t1 SET j2 = JSON_ARRAY(JSON_UNQUOTE(j));
212 SELECT * FROM t1 ORDER BY i;
213
214 UPDATE t1 SET j2 = CAST(JSON_QUOTE(CAST(j AS CHAR)) AS JSON);
215 SELECT * FROM t1 ORDER BY i;
216
217 ALTER TABLE t1 ADD COLUMN j3 JSON AS (JSON_EXTRACT(j2, '$.abc'));
218 SELECT * FROM t1 ORDER BY i;
219 UPDATE t1 SET j2 = JSON_OBJECT('abc', j);
220 SELECT * FROM t1 ORDER BY i;
221
222 --source include/sync_slave_sql_with_master.inc
223 --echo [Connection Slave]
224
225 --let $diff_tables= master:t1, slave:t1
226 --let $diff_tables= master:t2, slave:t2
227 --let $diff_tables= master:t3, slave:t3
228 --let $diff_tables= master:t4, slave:t4
229 --let $diff_tables= master:t5, slave:t5
230 --source include/diff_tables.inc
231
232 --echo [Connection Master]
233 --connection master
234 DROP TABLE t1, t2, t3, t4, t5;
235
236 --source include/sync_slave_sql_with_master.inc
237
238 --echo #
239 --echo # WL#7987: JSON AGGREGATION FUNCTIONS
240 --echo #
241
242 --connection master
243
244 --echo # Create tables of various data types
245 CREATE TABLE data_table (pkey INT PRIMARY KEY AUTO_INCREMENT,
246 txt TEXT, vcol VARCHAR(20), num INT,
247 gcol INT AS (num * 2) VIRTUAL);
248
249 CREATE TABLE json_data (nkey INT PRIMARY KEY AUTO_INCREMENT, jcol JSON);
250
251 --echo # Insert values into the tables using the aggregation functions
252 INSERT INTO data_table(txt, vcol, num) VALUES ('ailemac', 'namor', 15),
253 ('nielk', 'uciov', 12),
254 ('nuicarc', 'ierdna', 13),
255 ('ihcseruj', 'elisav', 14),
256 ('qweasdas', 'jugcvb', 16);
257
258 --disable_warnings
259 INSERT INTO json_data(jcol) SELECT JSON_OBJECTAGG(pkey, gcol) FROM data_table;
260 INSERT INTO json_data(jcol) SELECT JSON_ARRAYAGG(txt) FROM data_table;
261 INSERT INTO json_data(jcol) SELECT JSON_ARRAYAGG(gcol) FROM data_table;
262 INSERT INTO json_data(jcol) SELECT JSON_OBJECTAGG(num , vcol) FROM data_table;
263 INSERT INTO json_data(jcol) SELECT JSON_OBJECTAGG(pkey , txt) FROM data_table;
264 INSERT INTO json_data(jcol) SELECT JSON_ARRAYAGG(pkey) FROM data_table;
265 --enable_warnings
266
267 --source include/sync_slave_sql_with_master.inc
268 --echo [Connection Slave]
269
270 --echo # Check whether tables were created on the slave
271
272 SELECT * FROM json_data;
273 SELECT JSON_OBJECTAGG(t1.pkey, t2.jcol) FROM data_table AS t1, json_data AS t2;
274 SELECT JSON_ARRAYAGG(jcol) FROM json_data;
275
276 --let $diff_tables = master:json_data, slave:json_data
277 --source include/diff_tables.inc
278
279 --echo [Connection Master]
280 --connection master
281
282 --echo # Update values in the table
283 --disable_warnings
284 UPDATE json_data SET jcol= (SELECT JSON_ARRAYAGG(vcol) FROM data_table) WHERE nkey = 1;
285 --enable_warnings
286
287 --source include/sync_slave_sql_with_master.inc
288 --echo [Connection Slave]
289
290 SELECT * FROM json_data WHERE nkey = 1;
291
292 --let $diff_tables = master:json_data, slave:json_data
293 --source include/diff_tables.inc
294
295 --echo [Connection Master]
296 --connection master
297
298 --echo # Delete values from the table
299 --disable_warnings
300 DELETE FROM json_data WHERE jcol = (SELECT JSON_ARRAYAGG(pkey) FROM data_table);
301 --enable_warnings
302
303 --source include/sync_slave_sql_with_master.inc
304 --echo [Connection Slave]
305
306 SELECT * FROM json_data;
307
308 --let $diff_tables = master:json_data, slave:json_data
309 --source include/diff_tables.inc
310
311 --echo [Connection Master]
312 --connection master
313
314 DROP TABLE data_table;
315 DROP TABLE json_data;
316
317 --source include/sync_slave_sql_with_master.inc
318
319 --echo #
320 --echo # WL#9191: ADD JSON_PRETTY FUNCTION
321 --echo #
322
323 --echo [Connection Master]
324 --connection master
325
326 --echo # Create tables of various data types
327 CREATE TABLE data (pkey INT PRIMARY KEY AUTO_INCREMENT,
328 txt TEXT, vcol VARCHAR(20), num INT,
329 gcol INT AS (num * 2) VIRTUAL,
330 gcol2 JSON AS (JSON_PRETTY(JSON_OBJECT(num,gcol))));
331
332 CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT, jcol TEXT, jgcol TEXT AS (JSON_PRETTY(jcol)));
333
334 --echo # Insert values into the tables using the pretty function
335 INSERT INTO data (txt, vcol, num) VALUES ('abc', 'namotgr', 150),
336 ('fwjh4', 'ucierov', 142),
337 ('8942rhkjh', 'roiu3r', 913),
338 ('imfmf', 'r3jr2', 15),
339 ('32rj2jr', 'r2ihrhr', 32);
340
341 --disable_warnings
342 INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_OBJECTAGG(pkey, gcol)) FROM data;
343 INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_ARRAYAGG(txt)) FROM data;
344 INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_PRETTY(gcol2)) FROM data;
345 INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_OBJECT(num , vcol)) FROM data;
346 INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_OBJECTAGG(pkey , txt)) FROM data;
347 INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_ARRAY(gcol)) FROM data;
348 --enable_warnings
349
350
351 --source include/sync_slave_sql_with_master.inc
352 --echo [Connection Slave]
353
354 --echo # Check whether tables were created on the slave
355 SELECT * FROM json_data;
356 SELECT gcol2 FROM data;
357
358 --let $diff_tables = master:json_data, slave:json_data
359 --source include/diff_tables.inc
360
361 --let $diff_tables = master:data, slave:data
362 --source include/diff_tables.inc
363
364 --echo [Connection Master]
365 --connection master
366
367 --echo # Update values in the table
368 --disable_warnings
369 UPDATE json_data SET jcol= (SELECT JSON_PRETTY(JSON_ARRAYAGG(gcol2)) FROM data) WHERE pk= 2;
370 UPDATE json_data SET jcol= (SELECT JSON_PRETTY(JSON_OBJECTAGG(pk,gcol)) FROM data) WHERE pk = 1;
371 --enable_warnings
372
373 --source include/sync_slave_sql_with_master.inc
374 --echo [Connection Slave]
375
376 SELECT * FROM json_data;
377
378 --let $diff_tables = master:json_data, slave:json_data
379 --source include/diff_tables.inc
380
381 --echo [Connection Master]
382 --connection master
383
384
385 --echo # Delete values from the table
386 --disable_warnings
387 DELETE FROM json_data WHERE JSON_PRETTY(jgcol) = (SELECT JSON_PRETTY(JSON_OBJECTAGG(pkey,txt)) FROM data);
388 --enable_warnings
389
390 --source include/sync_slave_sql_with_master.inc
391 --echo [Connection Slave]
392
393 SELECT * FROM json_data;
394
395 --let $diff_tables = master:json_data, slave:json_data
396 --source include/diff_tables.inc
397
398 --echo [Connection Master]
399 --connection master
400
401 DROP TABLE data;
402 DROP TABLE json_data;
403
404 --source include/sync_slave_sql_with_master.inc
405
406 --echo #
407 --echo # WL#8963: SUPPORT FOR PARTIAL UPDATE OF JSON IN THE OPTIMIZER
408 --echo # WL#9192: ADD JSON_STORAGE_SIZE / JSON_STORAGE_FREE FUNCTIONS
409 --echo #
410
411 --echo [Connection Master]
412 --connection master
413
414 --echo # Create table containing various types of data
415
416 CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT,
417 id INT,
418 jd JSON,
419 td TEXT,
420 gcol INT AS (JSON_STORAGE_SIZE(jd)),
421 gcol2 INT AS (JSON_STORAGE_FREE(jd)) VIRTUAL,
422 gcol3 JSON AS (JSON_ARRAY(id,jd,td,gcol,gcol2)),
423 gcol4 JSON AS (JSON_OBJECT(id,jd,gcol,td)) VIRTUAL);
424
425 --echo # Insert data into the table
426
427 INSERT INTO json_data(id,jd,td) VALUES(10, '{"a":1}', 'characters'),
428 (20, '[10,20,30]', 'alphabet'),
429 (30, '[{"a":"abc"},{"b":"abcd"}]', 'words'),
430 (40, '{"key":["array", "of", "strings"]}', 'letters'),
431 (50, '{"key":[{"key":"value"},{"key":"value2"}]}', 'strings');
432
433 --source include/sync_slave_sql_with_master.inc
434 --echo [Connection Slave]
435
436 --echo # Check whether table was created on the slave
437
438 SELECT * FROM json_data;
439
440 --let $diff_tables = master:json_data, slave:json_data
441 --source include/diff_tables.inc
442
443 --echo [Connection Master]
444 --connection master
445
446 --echo # Update values in the table
447
448 UPDATE json_data SET jd = JSON_REPLACE(jd, '$[0].a',"a");
449
450 --source include/sync_slave_sql_with_master.inc
451 --echo [Connection Slave]
452
453 --echo # Check whether values were updated
454
455 SELECT * FROM json_data;
456 SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data;
457 SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data;
458
459 --let $diff_tables = master:json_data, slave:json_data
460 --source include/diff_tables.inc
461
462 --echo [Connection Master]
463 --connection master
464
465 --echo # Update more values in the table
466
467 UPDATE json_data SET jd = JSON_SET(jd, '$.key[0]', "ar");
468
469 --source include/sync_slave_sql_with_master.inc
470 --echo [Connection Slave]
471
472 --echo # Check whether values were updated
473
474 SELECT * FROM json_data;
475 SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data;
476 SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data;
477
478 --let $diff_tables = master:json_data, slave:json_data
479 --source include/diff_tables.inc
480
481 --echo [Connection Master]
482 --connection master
483
484 --echo # Update values again
485
486 UPDATE json_data SET jd = JSON_REPLACE(jd, '$.key', "hello");
487
488 --source include/sync_slave_sql_with_master.inc
489 --echo [Connection Slave]
490
491 --echo # Check whether values were updated
492
493 SELECT * FROM json_data;
494 SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data;
495 SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data;
496
497 --let $diff_tables = master:json_data, slave:json_data
498 --source include/diff_tables.inc
499
500 --echo [Connection Master]
501 --connection master
502
503 --echo # Update values such that they can fill up the "free" space
504
505 UPDATE json_data SET jd= JSON_SET(jd, '$.key', '["h","e","l","l","o"]');
506
507 --source include/sync_slave_sql_with_master.inc
508 --echo [Connection Slave]
509
510 --echo # Check whether values were updated
511
512 SELECT * FROM json_data;
513 SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data;
514 SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data;
515
516 --source include/rpl_connection_master.inc
517 --echo # Update via an updatable view.
518 CREATE VIEW json_data_view AS SELECT * FROM json_data;
519 UPDATE json_data_view SET jd = JSON_SET(jd, '$.key', '[]'),
520 jd = JSON_REMOVE(jd, '$[0].a');
521
522 --source include/sync_slave_sql_with_master.inc
523 --echo [Connection Slave]
524
525 --echo # Check whether values were updated
526
527 SELECT * FROM json_data;
528 SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data;
529 SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data;
530
531 --let $diff_tables = master:json_data, slave:json_data
532 --source include/diff_tables.inc
533
534 --echo [Connection Master]
535 --connection master
536
537 --echo # Clean up
538
539 DROP VIEW json_data_view;
540 DROP TABLE json_data;
541
542 --source include/sync_slave_sql_with_master.inc
543
544 --echo #
545 --echo # WL#9831: RANGES IN JSON PATH EXPRESSIONS
546 --echo #
547
548 --echo [Connection Master]
549 --connection master
550
551 --echo # Create table with JSON data
552
553 CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT,
554 id INT,
555 jd JSON,
556 td TEXT,
557 gcol JSON AS (JSON_EXTRACT(jd, '$[last -2 to last]')) VIRTUAL,
558 gcol2 VARCHAR(100) AS (JSON_EXTRACT(td, '$[0 to 3]')) STORED,
559 gcol3 JSON AS (JSON_EXTRACT(JSON_ARRAY(id,jd,td,gcol,gcol2),
560 '$[2][last -3 to last -1]')) VIRTUAL,
561 gcol4 JSON AS (JSON_EXTRACT(JSON_OBJECT(id,jd,gcol,td),
562 '$.id[0 to last]')) STORED,
563 key(gcol2));
564
565 --echo # Insert data into the table
566
567 INSERT INTO json_data(id,jd,td) VALUES(10, '["abc", "def", "ghi", "jkl", "mno"]',
568 '["pqr", "stu", "vwx", "xyz"]'),
569 (20, '[10, 20, 30, 40, 50]',
570 '["alpha", "beta", "gamma", "delta"]'),
571 (30, '["z", "y", "x", "w", "v"]',
572 '["Bengaluru", "Mysuru", "Davangere", "Belagaum"]'),
573 (40, '["array", "of", "strings", "and", "more"]',
574 '[2, 3, 5, 7]'),
575 (50, '[{"key":[{"key":"value"},{"key":"value2"}]},
576 {"key":[{"key":"value3"},{"key":"value4"}]}]',
577 '["N", "U", "L", "L"]');
578
579 --source include/sync_slave_sql_with_master.inc
580 --echo [Connection Slave]
581
582 --echo # Check whether table was created on the slave
583
584 SELECT * FROM json_data;
585
586 --let $diff_tables = master:json_data, slave:json_data
587 --source include/diff_tables.inc
588
589 --echo [Connection Master]
590 --connection master
591
592 --echo # Update values in the table
593
594 UPDATE json_data SET jd = JSON_SET(jd, '$[1]', "replaced") WHERE
595 JSON_CONTAINS(JSON_EXTRACT(jd, '$[0 to last]'), '[10, 20, 30, 40, 50]');
596
597 --source include/sync_slave_sql_with_master.inc
598 --echo [Connection Slave]
599
600 --echo # Check whether values were updated
601
602 SELECT * FROM json_data;
603 SELECT JSON_EXTRACT(td, '$[0 to last]') FROM json_data;
604
605 --echo [Connection Master]
606 --connection master
607
608 --echo # Clean up
609
610 DROP TABLE json_data;
611
612 --source include/sync_slave_sql_with_master.inc
613
614 --echo #
615 --echo # WL#9692: ADD JSON_MERGE_PATCH, RENAME JSON_MERGE TO JSON_MERGE_PRESERVE
616 --echo #
617
618 --echo [Connection Master]
619 --connection master
620
621 --echo # Create table with JSON data
622
623 CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT,
624 id INT,
625 jd JSON,
626 td TEXT,
627 gcol VARCHAR(30) AS (JSON_EXTRACT(JSON_MERGE_PATCH(jd,JSON_OBJECT(id,td)), '$.a')) VIRTUAL,
628 KEY(gcol));
629
630 --echo # Insert data into the table
631
632 INSERT INTO json_data(id,jd,td) VALUES(10,'["a","b","c","d","e"]', "pink"),
633 (20,'{"a":[1,2,6,7,9]}', "floyd"),
634 (30,'{"a":["ears","nose","eyes"]}', "megadeth"),
635 (40,'[null,null,"null",0,1]', "def"),
636 (50,'{"a":null}', "leppard");
637
638 --source include/sync_slave_sql_with_master.inc
639 --echo [Connection Slave]
640
641 --echo # Check whether table was created on the slave
642
643 SELECT * FROM json_data;
644
645 --let $diff_tables = master:json_data, slave:json_data
646 --source include/diff_tables.inc
647
648 --echo [Connection Master]
649 --connection master
650
651 --echo # Create tables using the JSON_MERGE_PATCH function
652
653 CREATE TABLE json_data2(field1 JSON);
654 INSERT INTO json_data2
655 SELECT JSON_MERGE_PATCH(JSON_ARRAYAGG(jd), JSON_OBJECTAGG(pk, jd))
656 FROM json_data;
657
658 CREATE TABLE json_data3(field1 JSON);
659 INSERT INTO json_data3
660 SELECT JSON_MERGE_PATCH(JSON_OBJECTAGG(id, gcol), JSON_ARRAYAGG(td))
661 FROM json_data;
662
663 CREATE TABLE json_data4(field1 JSON);
664 INSERT INTO json_data4
665 SELECT JSON_MERGE_PATCH(JSON_OBJECT(id, jd), JSON_ARRAY(gcol))
666 FROM json_data;
667
668 --source include/sync_slave_sql_with_master.inc
669 --echo [Connection Slave]
670
671 --echo # Check whether the tables were created on the slave
672
673 SELECT * FROM json_data2;
674 SELECT * FROM json_data3;
675 SELECT * FROM json_data4;
676
677 SELECT JSON_MERGE_PATCH(t1.field1, t2.field1) FROM json_data2 as t1, json_data3 as t2;
678
679 SELECT JSON_MERGE_PRESERVE(t1.field1, t2.field1) FROM json_data3 as t1, json_data4 as t2;
680
681 --echo [Connection Master]
682 --connection master
683
684 --echo # Update values in the table
685
686 UPDATE json_data SET jd = JSON_SET(jd, '$[1]', JSON_MERGE_PATCH(JSON_ARRAY(pk),JSON_ARRAY(td))) WHERE id IN (10,40);
687
688 --source include/sync_slave_sql_with_master.inc
689 --echo [Connection Slave]
690
691 --let $diff_tables = master:json_data, slave:json_data
692 --source include/diff_tables.inc
693
694 --echo # Check whether values were updated
695
696 SELECT * FROM json_data;
697
698 --echo [Connection Master]
699 --connection master
700
701 --echo # Clean up
702
703 DROP TABLE json_data, json_data2, json_data3, json_data4;
704
705 --source include/sync_slave_sql_with_master.inc
706
707 --echo #
708 --echo # WL#8867: ADD JSON TABLE FUNCTIONS
709 --echo #
710
711 --connection master
712 --echo [Connection Master]
713
714 --echo # Create table using data obtained from JSON_TABLE function
715 CREATE TABLE json_data(j json);
716 INSERT INTO json_data VALUES('{"a":1, "b":"trucks", "c":[1,2,3], "d":"taste", "e":"93-01-01", "f":"03:48:07"}');
717 INSERT INTO json_data VALUES('{"a":2, "b":"rickshaws", "c":{"key":"value"}, "d":"smell" , "e":"12-11-12", "f":"09:09:10"}');
718 INSERT INTO json_data VALUES('{"a":3, "b":"cars", "c":["a","b","c","d"], "d":"sight", "e":"01-01-01", "f":"05:18:12"}');
719 INSERT INTO json_data VALUES('{"a":4, "b":"bikes", "c":{"a":[1,2]}, "d":"hearing", "e":"98-05-06", "f":"00:00:00" }');
720 INSERT INTO json_data VALUES('{"a":5, "b":"mopeds", "c":["one", "two", "three"], "d":"touch", "e":"56-12-07", "f":"12:12:12"}');
721
722 CREATE TABLE data(i INT, v VARCHAR(10), j JSON, b BLOB, d DATE, t TIME, n JSON);
723 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;
724
725 --source include/sync_slave_sql_with_master.inc
726 --connection slave
727 --echo [Connection Slave]
728
729 --echo # Check whether the table was created on the slave
730 SELECT * FROM data;
731
732 --let $diff_tables= master:data, slave:data
733 --source include/diff_tables.inc
734
735 --connection master
736 --echo [Connection Master]
737
738 --echo # Update values in the table with data obtained from JSON_TABLE
739
740 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;
741
742 UPDATE data SET v = (SELECT ucol FROM JSON_TABLE('{"key":["update"]}', '$' COLUMNS(ucol VARCHAR(10) PATH '$.key[0]')) AS alias) WHERE i = 4;
743
744 --source include/sync_slave_sql_with_master.inc
745 --connection slave
746 --echo [Connection Slave]
747
748 --echo # Check whether the values were updated
749 SELECT j,v FROM data;
750
751 --let $diff_tables= master:data, slave:data
752 --source include/diff_tables.inc
753
754 --connection master
755 --echo [Connection Master]
756
757 --echo # Delete values from the table
758 DELETE FROM data WHERE i = (SELECT dcol FROM JSON_TABLE('{"key":["delete",1,"2"]}', '$' COLUMNS(dcol INT PATH '$.key[1]')) AS alias);
759
760 --source include/sync_slave_sql_with_master.inc
761 --connection slave
762 --echo [Connection Slave]
763
764 --echo # Check whether the row was deleted
765
766 SELECT * FROM data;
767
768 --let $diff_tables= master:data, slave:data
769 --source include/diff_tables.inc
770
771 --connection master
772 --echo [Connection Master]
773
774 --echo # cleanup
775
776 DROP TABLE data,json_data;
777
778 --source include/sync_slave_sql_with_master.inc
779
780 --echo #
781 --echo # Bug#26177130: SLAVE CAN'T FIND ROW USING HASH_SCAN
782 --echo # IF JSON HAS DIFFERENT BINARY FORMAT
783 --echo #
784
785 # We are only interested in testing this with binlog_format=row.
786 # Replace the options binlog_format=row/mixed/statement by the options
787 # slave_rows_search_algorithms=hash_scan/table_scan/index_scan.
788 --source include/rpl_connection_slave.inc
789 --let $replace_combination_from= BOTH.binlog_format=row,mixed,statement
790 --let $replace_combination_to= GLOBAL.slave_rows_search_algorithms=HASH_SCAN,TABLE_SCAN,INDEX_SCAN
791 # To avoid an error 'ER_RUNNING_APPLIER_PREVENTS_SWITCH_GLOBAL_BINLOG_FORMAT',
792 # which will be caused by the following include/end_replace_combination.inc
793 --source include/stop_slave_sql.inc
794 --source include/begin_replace_combination.inc
795 --source include/start_slave_sql.inc
796 --source include/rpl_connection_master.inc
797 SET @old_binlog_format = @@SESSION.binlog_format;
798 SET SESSION binlog_format = ROW;
799
800 # Restart slave threads to make the change in binlog format take effect
801 # for them.
802 --source include/rpl_connection_slave.inc
803 --source include/stop_slave.inc
804 --source include/start_slave.inc
805
806 --source include/rpl_connection_master.inc
807 CREATE TABLE t (j JSON);
808 SET SQL_LOG_BIN = 0;
809 INSERT INTO t VALUES ('["abc", "abcdefghij"]');
810 UPDATE t SET j = JSON_SET(j, '$[0]', 'a');
811 SET SQL_LOG_BIN = 1;
812 SELECT j, JSON_STORAGE_SIZE(j), JSON_STORAGE_FREE(j) FROM t;
813 --source include/sync_slave_sql_with_master.inc
814 INSERT INTO t VALUES ('["a", "abcdefghij"]');
815 SELECT j, JSON_STORAGE_SIZE(j), JSON_STORAGE_FREE(j) FROM t;
816 --echo # At this point, the master and the slave have the same JSON document,
817 --echo # but the binary representation is different (the master has a 'gap' due
818 --echo # to partial update).
819 --source include/rpl_connection_master.inc
820 DELETE FROM t;
821 --echo # Verify that the slave and the master are in sync after the DELETE.
822 --echo # Before the fix, the slave could not locate the row due with
823 --echo # slave_rows_search_algorithms=HASH_SCAN because of the difference in
824 --echo # the representation.
825 --source include/sync_slave_sql_with_master.inc
826 --echo # Clean up
827 --source include/rpl_connection_master.inc
828 DROP TABLE t;
829 --source include/sync_slave_sql_with_master.inc
830
831 # To avoid an error 'ER_RUNNING_APPLIER_PREVENTS_SWITCH_GLOBAL_BINLOG_FORMAT',
832 # which will be caused by the following include/end_replace_combination.inc
833 --source include/stop_slave.inc
834 # Stop mapping row/mixed/statement to hash_scan/table_scan/index_scan,
835 # and restart the slave to make it pick up the change.
836 --source include/end_replace_combination.inc
837 --source include/start_slave.inc
838 --source include/rpl_connection_master.inc
839 SET SESSION binlog_format = @old_binlog_format;
840
841 --source include/rpl_end.inc
842
843 # End: