include/master-slave.inc
Warnings:
Note	####	Sending passwords in plain text without SSL/TLS is extremely insecure.
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.
[connection master]
SET SESSION sql_log_bin= 0;
call mtr.add_suppression('.*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*');
SET SESSION sql_log_bin= 1;
SET SESSION sql_log_bin= 0;
call mtr.add_suppression('.*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*');
SET SESSION sql_log_bin= 1;
#
# WL#8132 JSON datatype and binary storage format
#
CREATE TABLE t1 (i INT PRIMARY KEY, j JSON);
INSERT INTO t1 VALUES (0, NULL);
INSERT INTO t1 VALUES (1, '{"a": 2}');
INSERT INTO t1 VALUES (2, '[1,2]');
INSERT INTO t1 VALUES (3, '{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}');
INSERT INTO t1 VALUES (4, '["here", ["I", "am"], "!!!"]');
INSERT INTO t1 VALUES (5, '"scalar string"');
INSERT INTO t1 VALUES (6, 'true');
INSERT INTO t1 VALUES (7, 'false');
INSERT INTO t1 VALUES (8, 'null');
INSERT INTO t1 VALUES (9, '-1');
INSERT INTO t1 VALUES (10, CAST(CAST(1 AS UNSIGNED) AS JSON));
INSERT INTO t1 VALUES (11, '32767');
INSERT INTO t1 VALUES (12, '32768');
INSERT INTO t1 VALUES (13, '-32768');
INSERT INTO t1 VALUES (14, '-32769');
INSERT INTO t1 VALUES (15, '2147483647');
INSERT INTO t1 VALUES (16, '2147483648');
INSERT INTO t1 VALUES (17, '-2147483648');
INSERT INTO t1 VALUES (18, '-2147483649');
INSERT INTO t1 VALUES (19, '18446744073709551615');
INSERT INTO t1 VALUES (20, '18446744073709551616');
INSERT INTO t1 VALUES (21, '3.14');
INSERT INTO t1 VALUES (22, '{}');
INSERT INTO t1 VALUES (23, '[]');
INSERT INTO t1 VALUES (24, CAST(CAST('2015-01-15 23:24:25' AS DATETIME) AS JSON));
INSERT INTO t1 VALUES (25, CAST(CAST('23:24:25' AS TIME) AS JSON));
INSERT INTO t1 VALUES (26, CAST(CAST('2015-01-15' AS DATE) AS JSON));
INSERT INTO t1 VALUES (27, CAST(TIMESTAMP'2015-01-15 23:24:25' AS JSON));
INSERT INTO t1 VALUES (28, CAST(ST_GeomFromText('POINT(1 1)') AS JSON));
INSERT INTO t1 VALUES (29, CAST('[]' AS CHAR CHARACTER SET 'ascii'));
INSERT INTO t1 VALUES (30, CAST(x'cafebabe' AS JSON));
SELECT * FROM t1 ORDER BY i;
i	j
0	NULL
1	{"a": 2}
2	[1, 2]
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
4	["here", ["I", "am"], "!!!"]
5	"scalar string"
6	true
7	false
8	null
9	-1
10	1
11	32767
12	32768
13	-32768
14	-32769
15	2147483647
16	2147483648
17	-2147483648
18	-2147483649
19	18446744073709551615
20	1.8446744073709552e19
21	3.14
22	{}
23	[]
24	"2015-01-15 23:24:25.000000"
25	"23:24:25.000000"
26	"2015-01-15"
27	"2015-01-15 23:24:25.000000"
28	{"type": "Point", "coordinates": [1.0, 1.0]}
29	[]
30	"base64:type15:yv66vg=="
# Copy JSON values
CREATE TABLE t2 (i INT PRIMARY KEY, j JSON);
INSERT INTO t2 SELECT * FROM t1;
SELECT *, JSON_TYPE(j) FROM t2 ORDER BY i;
i	j	JSON_TYPE(j)
0	NULL	NULL
1	{"a": 2}	OBJECT
2	[1, 2]	ARRAY
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	OBJECT
4	["here", ["I", "am"], "!!!"]	ARRAY
5	"scalar string"	STRING
6	true	BOOLEAN
7	false	BOOLEAN
8	null	NULL
9	-1	INTEGER
10	1	UNSIGNED INTEGER
11	32767	INTEGER
12	32768	INTEGER
13	-32768	INTEGER
14	-32769	INTEGER
15	2147483647	INTEGER
16	2147483648	INTEGER
17	-2147483648	INTEGER
18	-2147483649	INTEGER
19	18446744073709551615	UNSIGNED INTEGER
20	1.8446744073709552e19	DOUBLE
21	3.14	DOUBLE
22	{}	OBJECT
23	[]	ARRAY
24	"2015-01-15 23:24:25.000000"	DATETIME
25	"23:24:25.000000"	TIME
26	"2015-01-15"	DATE
27	"2015-01-15 23:24:25.000000"	DATETIME
28	{"type": "Point", "coordinates": [1.0, 1.0]}	OBJECT
29	[]	ARRAY
30	"base64:type15:yv66vg=="	BLOB
# Convert JSON values to TEXT
CREATE TABLE t3 (i INT PRIMARY KEY, txt TEXT);
INSERT INTO t3 SELECT * FROM t1;
SELECT *, JSON_TYPE(txt) FROM t3 ORDER BY i;
i	txt	JSON_TYPE(txt)
0	NULL	NULL
1	{"a": 2}	OBJECT
2	[1, 2]	ARRAY
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	OBJECT
4	["here", ["I", "am"], "!!!"]	ARRAY
5	"scalar string"	STRING
6	true	BOOLEAN
7	false	BOOLEAN
8	null	NULL
9	-1	INTEGER
10	1	INTEGER
11	32767	INTEGER
12	32768	INTEGER
13	-32768	INTEGER
14	-32769	INTEGER
15	2147483647	INTEGER
16	2147483648	INTEGER
17	-2147483648	INTEGER
18	-2147483649	INTEGER
19	18446744073709551615	UNSIGNED INTEGER
20	1.8446744073709552e19	DOUBLE
21	3.14	DOUBLE
22	{}	OBJECT
23	[]	ARRAY
24	"2015-01-15 23:24:25.000000"	STRING
25	"23:24:25.000000"	STRING
26	"2015-01-15"	STRING
27	"2015-01-15 23:24:25.000000"	STRING
28	{"type": "Point", "coordinates": [1.0, 1.0]}	OBJECT
29	[]	ARRAY
30	"base64:type15:yv66vg=="	STRING
# Convert TEXT values back to JSON
CREATE TABLE t4 (i INT PRIMARY KEY, j JSON);
INSERT INTO t4 SELECT * FROM t3;
SELECT *, JSON_TYPE(j) FROM t4 ORDER BY i;
i	j	JSON_TYPE(j)
0	NULL	NULL
1	{"a": 2}	OBJECT
2	[1, 2]	ARRAY
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	OBJECT
4	["here", ["I", "am"], "!!!"]	ARRAY
5	"scalar string"	STRING
6	true	BOOLEAN
7	false	BOOLEAN
8	null	NULL
9	-1	INTEGER
10	1	INTEGER
11	32767	INTEGER
12	32768	INTEGER
13	-32768	INTEGER
14	-32769	INTEGER
15	2147483647	INTEGER
16	2147483648	INTEGER
17	-2147483648	INTEGER
18	-2147483649	INTEGER
19	18446744073709551615	UNSIGNED INTEGER
20	1.8446744073709552e19	DOUBLE
21	3.14	DOUBLE
22	{}	OBJECT
23	[]	ARRAY
24	"2015-01-15 23:24:25.000000"	STRING
25	"23:24:25.000000"	STRING
26	"2015-01-15"	STRING
27	"2015-01-15 23:24:25.000000"	STRING
28	{"type": "Point", "coordinates": [1.0, 1.0]}	OBJECT
29	[]	ARRAY
30	"base64:type15:yv66vg=="	STRING
#
# WL#8249 JSON comparator
#
CREATE TABLE t5 (id INT PRIMARY KEY AUTO_INCREMENT, j JSON, x INT);
INSERT INTO t5(j) VALUES (NULL), (CAST(0 AS JSON)), (CAST(1 AS JSON)),
(CAST(2 AS JSON)), (CAST(3 AS JSON)), (CAST(3.14 AS JSON)), ('3.14'),
('"0"'), ('"1"'), ('"2"'), ('"3"'), ('true'), ('false'), ('"true"'),
('"false"'), ('null'), ('"null"'), (JSON_ARRAY(1, 2, 3)), ('"[1, 2, 3]"'),
(JSON_ARRAY(1.0e0, 2.0e0, 3.0e0)), (JSON_ARRAY(1, 2, 3.1)),
(JSON_OBJECT()), (JSON_OBJECT('a', 'b')), (JSON_OBJECT('a', 'c'));
SELECT * FROM t5 ORDER BY id;
id	j	x
1	NULL	NULL
2	0	NULL
3	1	NULL
4	2	NULL
5	3	NULL
6	3.14	NULL
7	3.14	NULL
8	"0"	NULL
9	"1"	NULL
10	"2"	NULL
11	"3"	NULL
12	true	NULL
13	false	NULL
14	"true"	NULL
15	"false"	NULL
16	null	NULL
17	"null"	NULL
18	[1, 2, 3]	NULL
19	"[1, 2, 3]"	NULL
20	[1.0, 2.0, 3.0]	NULL
21	[1, 2, 3.1]	NULL
22	{}	NULL
23	{"a": "b"}	NULL
24	{"a": "c"}	NULL
# Expect a single row to be updated (integer 0)
UPDATE t5 SET x = 1 WHERE j = 0;
SELECT * FROM t5 WHERE x = 1 ORDER BY id;
id	j	x
2	0	1
# Expect a single row to be updated (string "0")
UPDATE t5 SET x = 2 WHERE j = "0";
SELECT * FROM t5 WHERE x = 2 ORDER BY id;
id	j	x
8	"0"	2
# Expect a single row to be updated (boolean true)
UPDATE t5 SET x = 3 WHERE j = true;
SELECT * FROM t5 WHERE x = 3 ORDER BY id;
id	j	x
12	true	3
# Expect a single row to be updated (boolean false)
UPDATE t5 SET x = 4 WHERE j = false;
SELECT * FROM t5 WHERE x = 4 ORDER BY id;
id	j	x
13	false	4
# Expect a single row to be updated (string "true")
UPDATE t5 SET x = 5 WHERE j = 'true';
SELECT * FROM t5 WHERE x = 5 ORDER BY id;
id	j	x
14	"true"	5
# Expect a single row to be updated (string "false")
UPDATE t5 SET x = 6 WHERE j = 'false';
SELECT * FROM t5 WHERE x = 6 ORDER BY id;
id	j	x
15	"false"	6
# Expect a single row to be updated (string "[1, 2, 3]")
UPDATE t5 SET x = 7 WHERE j = '[1, 2, 3]';
SELECT * FROM t5 WHERE x = 7 ORDER BY id;
id	j	x
19	"[1, 2, 3]"	7
# Expect two rows to be updated (array [1, 2, 3])
UPDATE t5 SET x = 8 WHERE j = JSON_ARRAY(1, 2, 3);
SELECT * FROM t5 WHERE x = 8 ORDER BY id;
id	j	x
18	[1, 2, 3]	8
20	[1.0, 2.0, 3.0]	8
# Expect two rows to be updated (number 3.14)
UPDATE t5 SET x = 9 WHERE j = 3.14;
SELECT * FROM t5 WHERE x = 9 ORDER BY id;
id	j	x
6	3.14	9
7	3.14	9
# Expect no rows to be updated (string "3.14")
UPDATE t5 SET x = 10 WHERE j = '3.14';
SELECT * FROM t5 WHERE x = 10 ORDER BY id;
id	j	x
# Expect a single row to be updated (object {"a":"b"})
UPDATE t5 SET x = 11 WHERE j = CAST('{"a":"b"}' AS JSON);
SELECT * FROM t5 WHERE x = 11 ORDER BY id;
id	j	x
23	{"a": "b"}	11
# Expect four rows to be updated
UPDATE t5 SET x = 12 WHERE JSON_EXTRACT(j, '$[0]') = 1;
SELECT * FROM t5 WHERE x = 12 ORDER BY id;
id	j	x
3	1	12
18	[1, 2, 3]	12
20	[1.0, 2.0, 3.0]	12
21	[1, 2, 3.1]	12
# Expect a single row to be updated
UPDATE t5 SET x = 13 WHERE JSON_EXTRACT(j, '$[0]') = '1';
SELECT * FROM t5 WHERE x = 13 ORDER BY id;
id	j	x
9	"1"	13
#
# WL#8539 Ordering of scalar JSON values
#
DELETE FROM t2;
INSERT INTO t2 SELECT i, j FROM t1 ORDER BY j, i;
Warnings:
Warning	1235	This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
INSERT INTO t2 SELECT i + 100, j FROM t1 ORDER BY j DESC, i;
Warnings:
Warning	1235	This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
INSERT INTO t2 SELECT id + 200, j FROM t5 ORDER BY j, id;
Warnings:
Warning	1235	This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
INSERT INTO t2 SELECT id + 300, j FROM t5 ORDER BY j DESC, id;
Warnings:
Warning	1235	This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
SELECT * FROM t2 ORDER BY i;
i	j
0	NULL
1	{"a": 2}
2	[1, 2]
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
4	["here", ["I", "am"], "!!!"]
5	"scalar string"
6	true
7	false
8	null
9	-1
10	1
11	32767
12	32768
13	-32768
14	-32769
15	2147483647
16	2147483648
17	-2147483648
18	-2147483649
19	18446744073709551615
20	1.8446744073709552e19
21	3.14
22	{}
23	[]
24	"2015-01-15 23:24:25.000000"
25	"23:24:25.000000"
26	"2015-01-15"
27	"2015-01-15 23:24:25.000000"
28	{"type": "Point", "coordinates": [1.0, 1.0]}
29	[]
30	"base64:type15:yv66vg=="
100	NULL
101	{"a": 2}
102	[1, 2]
103	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
104	["here", ["I", "am"], "!!!"]
105	"scalar string"
106	true
107	false
108	null
109	-1
110	1
111	32767
112	32768
113	-32768
114	-32769
115	2147483647
116	2147483648
117	-2147483648
118	-2147483649
119	18446744073709551615
120	1.8446744073709552e19
121	3.14
122	{}
123	[]
124	"2015-01-15 23:24:25.000000"
125	"23:24:25.000000"
126	"2015-01-15"
127	"2015-01-15 23:24:25.000000"
128	{"type": "Point", "coordinates": [1.0, 1.0]}
129	[]
130	"base64:type15:yv66vg=="
201	NULL
202	0
203	1
204	2
205	3
206	3.14
207	3.14
208	"0"
209	"1"
210	"2"
211	"3"
212	true
213	false
214	"true"
215	"false"
216	null
217	"null"
218	[1, 2, 3]
219	"[1, 2, 3]"
220	[1.0, 2.0, 3.0]
221	[1, 2, 3.1]
222	{}
223	{"a": "b"}
224	{"a": "c"}
301	NULL
302	0
303	1
304	2
305	3
306	3.14
307	3.14
308	"0"
309	"1"
310	"2"
311	"3"
312	true
313	false
314	"true"
315	"false"
316	null
317	"null"
318	[1, 2, 3]
319	"[1, 2, 3]"
320	[1.0, 2.0, 3.0]
321	[1, 2, 3.1]
322	{}
323	{"a": "b"}
324	{"a": "c"}
#
# WL#7909 Server side JSON functions
#
ALTER TABLE t1 ADD COLUMN j2 JSON;
UPDATE t1 SET j2 = JSON_ARRAY(JSON_TYPE(j));
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	[null]
1	{"a": 2}	["OBJECT"]
2	[1, 2]	["ARRAY"]
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	["OBJECT"]
4	["here", ["I", "am"], "!!!"]	["ARRAY"]
5	"scalar string"	["STRING"]
6	true	["BOOLEAN"]
7	false	["BOOLEAN"]
8	null	["NULL"]
9	-1	["INTEGER"]
10	1	["UNSIGNED INTEGER"]
11	32767	["INTEGER"]
12	32768	["INTEGER"]
13	-32768	["INTEGER"]
14	-32769	["INTEGER"]
15	2147483647	["INTEGER"]
16	2147483648	["INTEGER"]
17	-2147483648	["INTEGER"]
18	-2147483649	["INTEGER"]
19	18446744073709551615	["UNSIGNED INTEGER"]
20	1.8446744073709552e19	["DOUBLE"]
21	3.14	["DOUBLE"]
22	{}	["OBJECT"]
23	[]	["ARRAY"]
24	"2015-01-15 23:24:25.000000"	["DATETIME"]
25	"23:24:25.000000"	["TIME"]
26	"2015-01-15"	["DATE"]
27	"2015-01-15 23:24:25.000000"	["DATETIME"]
28	{"type": "Point", "coordinates": [1.0, 1.0]}	["OBJECT"]
29	[]	["ARRAY"]
30	"base64:type15:yv66vg=="	["BLOB"]
UPDATE t1 SET j2 = CAST(JSON_VALID(j) AS JSON);
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	NULL
1	{"a": 2}	true
2	[1, 2]	true
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	true
4	["here", ["I", "am"], "!!!"]	true
5	"scalar string"	true
6	true	true
7	false	true
8	null	true
9	-1	true
10	1	true
11	32767	true
12	32768	true
13	-32768	true
14	-32769	true
15	2147483647	true
16	2147483648	true
17	-2147483648	true
18	-2147483649	true
19	18446744073709551615	true
20	1.8446744073709552e19	true
21	3.14	true
22	{}	true
23	[]	true
24	"2015-01-15 23:24:25.000000"	true
25	"23:24:25.000000"	true
26	"2015-01-15"	true
27	"2015-01-15 23:24:25.000000"	true
28	{"type": "Point", "coordinates": [1.0, 1.0]}	true
29	[]	true
30	"base64:type15:yv66vg=="	true
UPDATE t1 SET j2 = JSON_KEYS(j);
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	NULL
1	{"a": 2}	["a"]
2	[1, 2]	NULL
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	["a", "c", "ab", "bc"]
4	["here", ["I", "am"], "!!!"]	NULL
5	"scalar string"	NULL
6	true	NULL
7	false	NULL
8	null	NULL
9	-1	NULL
10	1	NULL
11	32767	NULL
12	32768	NULL
13	-32768	NULL
14	-32769	NULL
15	2147483647	NULL
16	2147483648	NULL
17	-2147483648	NULL
18	-2147483649	NULL
19	18446744073709551615	NULL
20	1.8446744073709552e19	NULL
21	3.14	NULL
22	{}	[]
23	[]	NULL
24	"2015-01-15 23:24:25.000000"	NULL
25	"23:24:25.000000"	NULL
26	"2015-01-15"	NULL
27	"2015-01-15 23:24:25.000000"	NULL
28	{"type": "Point", "coordinates": [1.0, 1.0]}	["type", "coordinates"]
29	[]	NULL
30	"base64:type15:yv66vg=="	NULL
UPDATE t1 SET j2 = JSON_ARRAY(j,j,j);
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	[null, null, null]
1	{"a": 2}	[{"a": 2}, {"a": 2}, {"a": 2}]
2	[1, 2]	[[1, 2], [1, 2], [1, 2]]
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"]}]
4	["here", ["I", "am"], "!!!"]	[["here", ["I", "am"], "!!!"], ["here", ["I", "am"], "!!!"], ["here", ["I", "am"], "!!!"]]
5	"scalar string"	["scalar string", "scalar string", "scalar string"]
6	true	[true, true, true]
7	false	[false, false, false]
8	null	[null, null, null]
9	-1	[-1, -1, -1]
10	1	[1, 1, 1]
11	32767	[32767, 32767, 32767]
12	32768	[32768, 32768, 32768]
13	-32768	[-32768, -32768, -32768]
14	-32769	[-32769, -32769, -32769]
15	2147483647	[2147483647, 2147483647, 2147483647]
16	2147483648	[2147483648, 2147483648, 2147483648]
17	-2147483648	[-2147483648, -2147483648, -2147483648]
18	-2147483649	[-2147483649, -2147483649, -2147483649]
19	18446744073709551615	[18446744073709551615, 18446744073709551615, 18446744073709551615]
20	1.8446744073709552e19	[1.8446744073709552e19, 1.8446744073709552e19, 1.8446744073709552e19]
21	3.14	[3.14, 3.14, 3.14]
22	{}	[{}, {}, {}]
23	[]	[[], [], []]
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"]
25	"23:24:25.000000"	["23:24:25.000000", "23:24:25.000000", "23:24:25.000000"]
26	"2015-01-15"	["2015-01-15", "2015-01-15", "2015-01-15"]
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"]
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]}]
29	[]	[[], [], []]
30	"base64:type15:yv66vg=="	["base64:type15:yv66vg==", "base64:type15:yv66vg==", "base64:type15:yv66vg=="]
UPDATE t1 SET j2 = JSON_EXTRACT(j2, '$[1]');
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	null
1	{"a": 2}	{"a": 2}
2	[1, 2]	[1, 2]
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
4	["here", ["I", "am"], "!!!"]	["here", ["I", "am"], "!!!"]
5	"scalar string"	"scalar string"
6	true	true
7	false	false
8	null	null
9	-1	-1
10	1	1
11	32767	32767
12	32768	32768
13	-32768	-32768
14	-32769	-32769
15	2147483647	2147483647
16	2147483648	2147483648
17	-2147483648	-2147483648
18	-2147483649	-2147483649
19	18446744073709551615	18446744073709551615
20	1.8446744073709552e19	1.8446744073709552e19
21	3.14	3.14
22	{}	{}
23	[]	[]
24	"2015-01-15 23:24:25.000000"	"2015-01-15 23:24:25.000000"
25	"23:24:25.000000"	"23:24:25.000000"
26	"2015-01-15"	"2015-01-15"
27	"2015-01-15 23:24:25.000000"	"2015-01-15 23:24:25.000000"
28	{"type": "Point", "coordinates": [1.0, 1.0]}	{"type": "Point", "coordinates": [1.0, 1.0]}
29	[]	[]
30	"base64:type15:yv66vg=="	"base64:type15:yv66vg=="
UPDATE t1 SET j2 = JSON_REMOVE(j, '$[1]');
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	NULL
1	{"a": 2}	{"a": 2}
2	[1, 2]	[1]
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
4	["here", ["I", "am"], "!!!"]	["here", "!!!"]
5	"scalar string"	"scalar string"
6	true	true
7	false	false
8	null	null
9	-1	-1
10	1	1
11	32767	32767
12	32768	32768
13	-32768	-32768
14	-32769	-32769
15	2147483647	2147483647
16	2147483648	2147483648
17	-2147483648	-2147483648
18	-2147483649	-2147483649
19	18446744073709551615	18446744073709551615
20	1.8446744073709552e19	1.8446744073709552e19
21	3.14	3.14
22	{}	{}
23	[]	[]
24	"2015-01-15 23:24:25.000000"	"2015-01-15 23:24:25.000000"
25	"23:24:25.000000"	"23:24:25.000000"
26	"2015-01-15"	"2015-01-15"
27	"2015-01-15 23:24:25.000000"	"2015-01-15 23:24:25.000000"
28	{"type": "Point", "coordinates": [1.0, 1.0]}	{"type": "Point", "coordinates": [1.0, 1.0]}
29	[]	[]
30	"base64:type15:yv66vg=="	"base64:type15:yv66vg=="
UPDATE t1 SET j2 = JSON_ARRAY_APPEND(j, '$[1]', 'abc');
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	NULL
1	{"a": 2}	{"a": 2}
2	[1, 2]	[1, [2, "abc"]]
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
4	["here", ["I", "am"], "!!!"]	["here", ["I", "am", "abc"], "!!!"]
5	"scalar string"	"scalar string"
6	true	true
7	false	false
8	null	null
9	-1	-1
10	1	1
11	32767	32767
12	32768	32768
13	-32768	-32768
14	-32769	-32769
15	2147483647	2147483647
16	2147483648	2147483648
17	-2147483648	-2147483648
18	-2147483649	-2147483649
19	18446744073709551615	18446744073709551615
20	1.8446744073709552e19	1.8446744073709552e19
21	3.14	3.14
22	{}	{}
23	[]	[]
24	"2015-01-15 23:24:25.000000"	"2015-01-15 23:24:25.000000"
25	"23:24:25.000000"	"23:24:25.000000"
26	"2015-01-15"	"2015-01-15"
27	"2015-01-15 23:24:25.000000"	"2015-01-15 23:24:25.000000"
28	{"type": "Point", "coordinates": [1.0, 1.0]}	{"type": "Point", "coordinates": [1.0, 1.0]}
29	[]	[]
30	"base64:type15:yv66vg=="	"base64:type15:yv66vg=="
UPDATE t1 SET j2 = JSON_SET(j, '$[1]', 'abc');
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	NULL
1	{"a": 2}	[{"a": 2}, "abc"]
2	[1, 2]	[1, "abc"]
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	[{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}, "abc"]
4	["here", ["I", "am"], "!!!"]	["here", "abc", "!!!"]
5	"scalar string"	["scalar string", "abc"]
6	true	[true, "abc"]
7	false	[false, "abc"]
8	null	[null, "abc"]
9	-1	[-1, "abc"]
10	1	[1, "abc"]
11	32767	[32767, "abc"]
12	32768	[32768, "abc"]
13	-32768	[-32768, "abc"]
14	-32769	[-32769, "abc"]
15	2147483647	[2147483647, "abc"]
16	2147483648	[2147483648, "abc"]
17	-2147483648	[-2147483648, "abc"]
18	-2147483649	[-2147483649, "abc"]
19	18446744073709551615	[18446744073709551615, "abc"]
20	1.8446744073709552e19	[1.8446744073709552e19, "abc"]
21	3.14	[3.14, "abc"]
22	{}	[{}, "abc"]
23	[]	["abc"]
24	"2015-01-15 23:24:25.000000"	["2015-01-15 23:24:25.000000", "abc"]
25	"23:24:25.000000"	["23:24:25.000000", "abc"]
26	"2015-01-15"	["2015-01-15", "abc"]
27	"2015-01-15 23:24:25.000000"	["2015-01-15 23:24:25.000000", "abc"]
28	{"type": "Point", "coordinates": [1.0, 1.0]}	[{"type": "Point", "coordinates": [1.0, 1.0]}, "abc"]
29	[]	["abc"]
30	"base64:type15:yv66vg=="	["base64:type15:yv66vg==", "abc"]
UPDATE t1 SET j2 = JSON_INSERT(j, '$[1]', 'abc');
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	NULL
1	{"a": 2}	[{"a": 2}, "abc"]
2	[1, 2]	[1, 2]
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	[{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}, "abc"]
4	["here", ["I", "am"], "!!!"]	["here", ["I", "am"], "!!!"]
5	"scalar string"	["scalar string", "abc"]
6	true	[true, "abc"]
7	false	[false, "abc"]
8	null	[null, "abc"]
9	-1	[-1, "abc"]
10	1	[1, "abc"]
11	32767	[32767, "abc"]
12	32768	[32768, "abc"]
13	-32768	[-32768, "abc"]
14	-32769	[-32769, "abc"]
15	2147483647	[2147483647, "abc"]
16	2147483648	[2147483648, "abc"]
17	-2147483648	[-2147483648, "abc"]
18	-2147483649	[-2147483649, "abc"]
19	18446744073709551615	[18446744073709551615, "abc"]
20	1.8446744073709552e19	[1.8446744073709552e19, "abc"]
21	3.14	[3.14, "abc"]
22	{}	[{}, "abc"]
23	[]	["abc"]
24	"2015-01-15 23:24:25.000000"	["2015-01-15 23:24:25.000000", "abc"]
25	"23:24:25.000000"	["23:24:25.000000", "abc"]
26	"2015-01-15"	["2015-01-15", "abc"]
27	"2015-01-15 23:24:25.000000"	["2015-01-15 23:24:25.000000", "abc"]
28	{"type": "Point", "coordinates": [1.0, 1.0]}	[{"type": "Point", "coordinates": [1.0, 1.0]}, "abc"]
29	[]	["abc"]
30	"base64:type15:yv66vg=="	["base64:type15:yv66vg==", "abc"]
UPDATE t1 SET j2 = JSON_REPLACE(j, '$[1]', 'abc');
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	NULL
1	{"a": 2}	{"a": 2}
2	[1, 2]	[1, "abc"]
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
4	["here", ["I", "am"], "!!!"]	["here", "abc", "!!!"]
5	"scalar string"	"scalar string"
6	true	true
7	false	false
8	null	null
9	-1	-1
10	1	1
11	32767	32767
12	32768	32768
13	-32768	-32768
14	-32769	-32769
15	2147483647	2147483647
16	2147483648	2147483648
17	-2147483648	-2147483648
18	-2147483649	-2147483649
19	18446744073709551615	18446744073709551615
20	1.8446744073709552e19	1.8446744073709552e19
21	3.14	3.14
22	{}	{}
23	[]	[]
24	"2015-01-15 23:24:25.000000"	"2015-01-15 23:24:25.000000"
25	"23:24:25.000000"	"23:24:25.000000"
26	"2015-01-15"	"2015-01-15"
27	"2015-01-15 23:24:25.000000"	"2015-01-15 23:24:25.000000"
28	{"type": "Point", "coordinates": [1.0, 1.0]}	{"type": "Point", "coordinates": [1.0, 1.0]}
29	[]	[]
30	"base64:type15:yv66vg=="	"base64:type15:yv66vg=="
UPDATE t1 SET j2 = JSON_MERGE_PATCH(j, '{"a": "patched", "c": null}');
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	NULL
1	{"a": 2}	{"a": "patched"}
2	[1, 2]	{"a": "patched"}
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	{"a": "patched", "ab": "abc", "bc": ["x", "y"]}
4	["here", ["I", "am"], "!!!"]	{"a": "patched"}
5	"scalar string"	{"a": "patched"}
6	true	{"a": "patched"}
7	false	{"a": "patched"}
8	null	{"a": "patched"}
9	-1	{"a": "patched"}
10	1	{"a": "patched"}
11	32767	{"a": "patched"}
12	32768	{"a": "patched"}
13	-32768	{"a": "patched"}
14	-32769	{"a": "patched"}
15	2147483647	{"a": "patched"}
16	2147483648	{"a": "patched"}
17	-2147483648	{"a": "patched"}
18	-2147483649	{"a": "patched"}
19	18446744073709551615	{"a": "patched"}
20	1.8446744073709552e19	{"a": "patched"}
21	3.14	{"a": "patched"}
22	{}	{"a": "patched"}
23	[]	{"a": "patched"}
24	"2015-01-15 23:24:25.000000"	{"a": "patched"}
25	"23:24:25.000000"	{"a": "patched"}
26	"2015-01-15"	{"a": "patched"}
27	"2015-01-15 23:24:25.000000"	{"a": "patched"}
28	{"type": "Point", "coordinates": [1.0, 1.0]}	{"a": "patched", "type": "Point", "coordinates": [1.0, 1.0]}
29	[]	{"a": "patched"}
30	"base64:type15:yv66vg=="	{"a": "patched"}
UPDATE t1 SET j2 = JSON_MERGE_PRESERVE(j, j);
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	NULL
1	{"a": 2}	{"a": [2, 2]}
2	[1, 2]	[1, 2, 1, 2]
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	{"a": ["b", "b"], "c": ["d", "d"], "ab": ["abc", "abc"], "bc": ["x", "y", "x", "y"]}
4	["here", ["I", "am"], "!!!"]	["here", ["I", "am"], "!!!", "here", ["I", "am"], "!!!"]
5	"scalar string"	["scalar string", "scalar string"]
6	true	[true, true]
7	false	[false, false]
8	null	[null, null]
9	-1	[-1, -1]
10	1	[1, 1]
11	32767	[32767, 32767]
12	32768	[32768, 32768]
13	-32768	[-32768, -32768]
14	-32769	[-32769, -32769]
15	2147483647	[2147483647, 2147483647]
16	2147483648	[2147483648, 2147483648]
17	-2147483648	[-2147483648, -2147483648]
18	-2147483649	[-2147483649, -2147483649]
19	18446744073709551615	[18446744073709551615, 18446744073709551615]
20	1.8446744073709552e19	[1.8446744073709552e19, 1.8446744073709552e19]
21	3.14	[3.14, 3.14]
22	{}	{}
23	[]	[]
24	"2015-01-15 23:24:25.000000"	["2015-01-15 23:24:25.000000", "2015-01-15 23:24:25.000000"]
25	"23:24:25.000000"	["23:24:25.000000", "23:24:25.000000"]
26	"2015-01-15"	["2015-01-15", "2015-01-15"]
27	"2015-01-15 23:24:25.000000"	["2015-01-15 23:24:25.000000", "2015-01-15 23:24:25.000000"]
28	{"type": "Point", "coordinates": [1.0, 1.0]}	{"type": ["Point", "Point"], "coordinates": [1.0, 1.0, 1.0, 1.0]}
29	[]	[]
30	"base64:type15:yv66vg=="	["base64:type15:yv66vg==", "base64:type15:yv66vg=="]
UPDATE t1 SET j2 = JSON_SEARCH(j, 'one', 'abc');
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	NULL
1	{"a": 2}	NULL
2	[1, 2]	NULL
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	"$.ab"
4	["here", ["I", "am"], "!!!"]	NULL
5	"scalar string"	NULL
6	true	NULL
7	false	NULL
8	null	NULL
9	-1	NULL
10	1	NULL
11	32767	NULL
12	32768	NULL
13	-32768	NULL
14	-32769	NULL
15	2147483647	NULL
16	2147483648	NULL
17	-2147483648	NULL
18	-2147483649	NULL
19	18446744073709551615	NULL
20	1.8446744073709552e19	NULL
21	3.14	NULL
22	{}	NULL
23	[]	NULL
24	"2015-01-15 23:24:25.000000"	NULL
25	"23:24:25.000000"	NULL
26	"2015-01-15"	NULL
27	"2015-01-15 23:24:25.000000"	NULL
28	{"type": "Point", "coordinates": [1.0, 1.0]}	NULL
29	[]	NULL
30	"base64:type15:yv66vg=="	NULL
UPDATE t1 SET j2 = CAST(JSON_CONTAINS(j, '[1]') AS JSON);
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	NULL
1	{"a": 2}	false
2	[1, 2]	true
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	false
4	["here", ["I", "am"], "!!!"]	false
5	"scalar string"	false
6	true	false
7	false	false
8	null	false
9	-1	false
10	1	false
11	32767	false
12	32768	false
13	-32768	false
14	-32769	false
15	2147483647	false
16	2147483648	false
17	-2147483648	false
18	-2147483649	false
19	18446744073709551615	false
20	1.8446744073709552e19	false
21	3.14	false
22	{}	false
23	[]	false
24	"2015-01-15 23:24:25.000000"	false
25	"23:24:25.000000"	false
26	"2015-01-15"	false
27	"2015-01-15 23:24:25.000000"	false
28	{"type": "Point", "coordinates": [1.0, 1.0]}	false
29	[]	false
30	"base64:type15:yv66vg=="	false
UPDATE t1 SET j2 = CAST(JSON_CONTAINS_PATH(j, 'one', '$.a') AS JSON);
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	NULL
1	{"a": 2}	true
2	[1, 2]	false
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	true
4	["here", ["I", "am"], "!!!"]	false
5	"scalar string"	false
6	true	false
7	false	false
8	null	false
9	-1	false
10	1	false
11	32767	false
12	32768	false
13	-32768	false
14	-32769	false
15	2147483647	false
16	2147483648	false
17	-2147483648	false
18	-2147483649	false
19	18446744073709551615	false
20	1.8446744073709552e19	false
21	3.14	false
22	{}	false
23	[]	false
24	"2015-01-15 23:24:25.000000"	false
25	"23:24:25.000000"	false
26	"2015-01-15"	false
27	"2015-01-15 23:24:25.000000"	false
28	{"type": "Point", "coordinates": [1.0, 1.0]}	false
29	[]	false
30	"base64:type15:yv66vg=="	false
UPDATE t1 SET j2 = CAST(JSON_LENGTH(j) AS JSON);
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	NULL
1	{"a": 2}	1
2	[1, 2]	2
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	4
4	["here", ["I", "am"], "!!!"]	3
5	"scalar string"	1
6	true	1
7	false	1
8	null	1
9	-1	1
10	1	1
11	32767	1
12	32768	1
13	-32768	1
14	-32769	1
15	2147483647	1
16	2147483648	1
17	-2147483648	1
18	-2147483649	1
19	18446744073709551615	1
20	1.8446744073709552e19	1
21	3.14	1
22	{}	0
23	[]	0
24	"2015-01-15 23:24:25.000000"	1
25	"23:24:25.000000"	1
26	"2015-01-15"	1
27	"2015-01-15 23:24:25.000000"	1
28	{"type": "Point", "coordinates": [1.0, 1.0]}	2
29	[]	0
30	"base64:type15:yv66vg=="	1
UPDATE t1 SET j2 = CAST(JSON_DEPTH(j) AS JSON);
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	NULL
1	{"a": 2}	2
2	[1, 2]	2
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	3
4	["here", ["I", "am"], "!!!"]	3
5	"scalar string"	1
6	true	1
7	false	1
8	null	1
9	-1	1
10	1	1
11	32767	1
12	32768	1
13	-32768	1
14	-32769	1
15	2147483647	1
16	2147483648	1
17	-2147483648	1
18	-2147483649	1
19	18446744073709551615	1
20	1.8446744073709552e19	1
21	3.14	1
22	{}	1
23	[]	1
24	"2015-01-15 23:24:25.000000"	1
25	"23:24:25.000000"	1
26	"2015-01-15"	1
27	"2015-01-15 23:24:25.000000"	1
28	{"type": "Point", "coordinates": [1.0, 1.0]}	3
29	[]	1
30	"base64:type15:yv66vg=="	1
UPDATE t1 SET j2 = JSON_OBJECT('a', j, 'b', j);
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	{"a": null, "b": null}
1	{"a": 2}	{"a": {"a": 2}, "b": {"a": 2}}
2	[1, 2]	{"a": [1, 2], "b": [1, 2]}
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"]}}
4	["here", ["I", "am"], "!!!"]	{"a": ["here", ["I", "am"], "!!!"], "b": ["here", ["I", "am"], "!!!"]}
5	"scalar string"	{"a": "scalar string", "b": "scalar string"}
6	true	{"a": true, "b": true}
7	false	{"a": false, "b": false}
8	null	{"a": null, "b": null}
9	-1	{"a": -1, "b": -1}
10	1	{"a": 1, "b": 1}
11	32767	{"a": 32767, "b": 32767}
12	32768	{"a": 32768, "b": 32768}
13	-32768	{"a": -32768, "b": -32768}
14	-32769	{"a": -32769, "b": -32769}
15	2147483647	{"a": 2147483647, "b": 2147483647}
16	2147483648	{"a": 2147483648, "b": 2147483648}
17	-2147483648	{"a": -2147483648, "b": -2147483648}
18	-2147483649	{"a": -2147483649, "b": -2147483649}
19	18446744073709551615	{"a": 18446744073709551615, "b": 18446744073709551615}
20	1.8446744073709552e19	{"a": 1.8446744073709552e19, "b": 1.8446744073709552e19}
21	3.14	{"a": 3.14, "b": 3.14}
22	{}	{"a": {}, "b": {}}
23	[]	{"a": [], "b": []}
24	"2015-01-15 23:24:25.000000"	{"a": "2015-01-15 23:24:25.000000", "b": "2015-01-15 23:24:25.000000"}
25	"23:24:25.000000"	{"a": "23:24:25.000000", "b": "23:24:25.000000"}
26	"2015-01-15"	{"a": "2015-01-15", "b": "2015-01-15"}
27	"2015-01-15 23:24:25.000000"	{"a": "2015-01-15 23:24:25.000000", "b": "2015-01-15 23:24:25.000000"}
28	{"type": "Point", "coordinates": [1.0, 1.0]}	{"a": {"type": "Point", "coordinates": [1.0, 1.0]}, "b": {"type": "Point", "coordinates": [1.0, 1.0]}}
29	[]	{"a": [], "b": []}
30	"base64:type15:yv66vg=="	{"a": "base64:type15:yv66vg==", "b": "base64:type15:yv66vg=="}
UPDATE t1 SET j2 = JSON_ARRAY(JSON_UNQUOTE(j));
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	[null]
1	{"a": 2}	["{\"a\": 2}"]
2	[1, 2]	["[1, 2]"]
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	["{\"a\": \"b\", \"c\": \"d\", \"ab\": \"abc\", \"bc\": [\"x\", \"y\"]}"]
4	["here", ["I", "am"], "!!!"]	["[\"here\", [\"I\", \"am\"], \"!!!\"]"]
5	"scalar string"	["scalar string"]
6	true	["true"]
7	false	["false"]
8	null	["null"]
9	-1	["-1"]
10	1	["1"]
11	32767	["32767"]
12	32768	["32768"]
13	-32768	["-32768"]
14	-32769	["-32769"]
15	2147483647	["2147483647"]
16	2147483648	["2147483648"]
17	-2147483648	["-2147483648"]
18	-2147483649	["-2147483649"]
19	18446744073709551615	["18446744073709551615"]
20	1.8446744073709552e19	["1.8446744073709552e19"]
21	3.14	["3.14"]
22	{}	["{}"]
23	[]	["[]"]
24	"2015-01-15 23:24:25.000000"	["2015-01-15 23:24:25.000000"]
25	"23:24:25.000000"	["23:24:25.000000"]
26	"2015-01-15"	["2015-01-15"]
27	"2015-01-15 23:24:25.000000"	["2015-01-15 23:24:25.000000"]
28	{"type": "Point", "coordinates": [1.0, 1.0]}	["{\"type\": \"Point\", \"coordinates\": [1.0, 1.0]}"]
29	[]	["[]"]
30	"base64:type15:yv66vg=="	["base64:type15:yv66vg=="]
UPDATE t1 SET j2 = CAST(JSON_QUOTE(CAST(j AS CHAR)) AS JSON);
SELECT * FROM t1 ORDER BY i;
i	j	j2
0	NULL	NULL
1	{"a": 2}	"{\"a\": 2}"
2	[1, 2]	"[1, 2]"
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	"{\"a\": \"b\", \"c\": \"d\", \"ab\": \"abc\", \"bc\": [\"x\", \"y\"]}"
4	["here", ["I", "am"], "!!!"]	"[\"here\", [\"I\", \"am\"], \"!!!\"]"
5	"scalar string"	"\"scalar string\""
6	true	"true"
7	false	"false"
8	null	"null"
9	-1	"-1"
10	1	"1"
11	32767	"32767"
12	32768	"32768"
13	-32768	"-32768"
14	-32769	"-32769"
15	2147483647	"2147483647"
16	2147483648	"2147483648"
17	-2147483648	"-2147483648"
18	-2147483649	"-2147483649"
19	18446744073709551615	"18446744073709551615"
20	1.8446744073709552e19	"1.8446744073709552e19"
21	3.14	"3.14"
22	{}	"{}"
23	[]	"[]"
24	"2015-01-15 23:24:25.000000"	"\"2015-01-15 23:24:25.000000\""
25	"23:24:25.000000"	"\"23:24:25.000000\""
26	"2015-01-15"	"\"2015-01-15\""
27	"2015-01-15 23:24:25.000000"	"\"2015-01-15 23:24:25.000000\""
28	{"type": "Point", "coordinates": [1.0, 1.0]}	"{\"type\": \"Point\", \"coordinates\": [1.0, 1.0]}"
29	[]	"[]"
30	"base64:type15:yv66vg=="	"\"base64:type15:yv66vg==\""
ALTER TABLE t1 ADD COLUMN j3 JSON AS (JSON_EXTRACT(j2, '$.abc'));
SELECT * FROM t1 ORDER BY i;
i	j	j2	j3
0	NULL	NULL	NULL
1	{"a": 2}	"{\"a\": 2}"	NULL
2	[1, 2]	"[1, 2]"	NULL
3	{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}	"{\"a\": \"b\", \"c\": \"d\", \"ab\": \"abc\", \"bc\": [\"x\", \"y\"]}"	NULL
4	["here", ["I", "am"], "!!!"]	"[\"here\", [\"I\", \"am\"], \"!!!\"]"	NULL
5	"scalar string"	"\"scalar string\""	NULL
6	true	"true"	NULL
7	false	"false"	NULL
8	null	"null"	NULL
9	-1	"-1"	NULL
10	1	"1"	NULL
11	32767	"32767"	NULL
12	32768	"32768"	NULL
13	-32768	"-32768"	NULL
14	-32769	"-32769"	NULL
15	2147483647	"2147483647"	NULL
16	2147483648	"2147483648"	NULL
17	-2147483648	"-2147483648"	NULL
18	-2147483649	"-2147483649"	NULL
19	18446744073709551615	"18446744073709551615"	NULL
20	1.8446744073709552e19	"1.8446744073709552e19"	NULL
21	3.14	"3.14"	NULL
22	{}	"{}"	NULL
23	[]	"[]"	NULL
24	"2015-01-15 23:24:25.000000"	"\"2015-01-15 23:24:25.000000\""	NULL
25	"23:24:25.000000"	"\"23:24:25.000000\""	NULL
26	"2015-01-15"	"\"2015-01-15\""	NULL
27	"2015-01-15 23:24:25.000000"	"\"2015-01-15 23:24:25.000000\""	NULL
28	{"type": "Point", "coordinates": [1.0, 1.0]}	"{\"type\": \"Point\", \"coordinates\": [1.0, 1.0]}"	NULL
29	[]	"[]"	NULL
30	"base64:type15:yv66vg=="	"\"base64:type15:yv66vg==\""	NULL
UPDATE t1 SET j2 = JSON_OBJECT('abc', j);
SELECT * FROM t1 ORDER BY i;
i	j	j2	j3
0	NULL	{"abc": null}	null
1	{"a": 2}	{"abc": {"a": 2}}	{"a": 2}
2	[1, 2]	{"abc": [1, 2]}	[1, 2]
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"]}
4	["here", ["I", "am"], "!!!"]	{"abc": ["here", ["I", "am"], "!!!"]}	["here", ["I", "am"], "!!!"]
5	"scalar string"	{"abc": "scalar string"}	"scalar string"
6	true	{"abc": true}	true
7	false	{"abc": false}	false
8	null	{"abc": null}	null
9	-1	{"abc": -1}	-1
10	1	{"abc": 1}	1
11	32767	{"abc": 32767}	32767
12	32768	{"abc": 32768}	32768
13	-32768	{"abc": -32768}	-32768
14	-32769	{"abc": -32769}	-32769
15	2147483647	{"abc": 2147483647}	2147483647
16	2147483648	{"abc": 2147483648}	2147483648
17	-2147483648	{"abc": -2147483648}	-2147483648
18	-2147483649	{"abc": -2147483649}	-2147483649
19	18446744073709551615	{"abc": 18446744073709551615}	18446744073709551615
20	1.8446744073709552e19	{"abc": 1.8446744073709552e19}	1.8446744073709552e19
21	3.14	{"abc": 3.14}	3.14
22	{}	{"abc": {}}	{}
23	[]	{"abc": []}	[]
24	"2015-01-15 23:24:25.000000"	{"abc": "2015-01-15 23:24:25.000000"}	"2015-01-15 23:24:25.000000"
25	"23:24:25.000000"	{"abc": "23:24:25.000000"}	"23:24:25.000000"
26	"2015-01-15"	{"abc": "2015-01-15"}	"2015-01-15"
27	"2015-01-15 23:24:25.000000"	{"abc": "2015-01-15 23:24:25.000000"}	"2015-01-15 23:24:25.000000"
28	{"type": "Point", "coordinates": [1.0, 1.0]}	{"abc": {"type": "Point", "coordinates": [1.0, 1.0]}}	{"type": "Point", "coordinates": [1.0, 1.0]}
29	[]	{"abc": []}	[]
30	"base64:type15:yv66vg=="	{"abc": "base64:type15:yv66vg=="}	"base64:type15:yv66vg=="
include/sync_slave_sql_with_master.inc
[Connection Slave]
include/diff_tables.inc [master:t5, slave:t5]
[Connection Master]
DROP TABLE t1, t2, t3, t4, t5;
include/sync_slave_sql_with_master.inc
#
# WL#7987: JSON AGGREGATION FUNCTIONS
#
# Create tables of various data types
CREATE TABLE data_table (pkey INT PRIMARY KEY AUTO_INCREMENT,
txt TEXT, vcol VARCHAR(20), num INT,
gcol INT AS (num * 2) VIRTUAL);
CREATE TABLE json_data (nkey INT PRIMARY KEY AUTO_INCREMENT, jcol JSON);
# Insert values into the tables using the aggregation functions
INSERT INTO data_table(txt, vcol, num) VALUES ('ailemac', 'namor', 15),
('nielk', 'uciov', 12),
('nuicarc', 'ierdna', 13),
('ihcseruj', 'elisav', 14),
('qweasdas', 'jugcvb', 16);
INSERT INTO json_data(jcol) SELECT JSON_OBJECTAGG(pkey, gcol) FROM data_table;
INSERT INTO json_data(jcol) SELECT JSON_ARRAYAGG(txt) FROM data_table;
INSERT INTO json_data(jcol) SELECT JSON_ARRAYAGG(gcol) FROM data_table;
INSERT INTO json_data(jcol) SELECT JSON_OBJECTAGG(num , vcol) FROM data_table;
INSERT INTO json_data(jcol) SELECT JSON_OBJECTAGG(pkey , txt) FROM data_table;
INSERT INTO json_data(jcol) SELECT JSON_ARRAYAGG(pkey) FROM data_table;
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether tables were created on the slave
SELECT * FROM json_data;
nkey	jcol
1	{"1": 30, "2": 24, "3": 26, "4": 28, "5": 32}
2	["ailemac", "nielk", "nuicarc", "ihcseruj", "qweasdas"]
3	[30, 24, 26, 28, 32]
4	{"12": "uciov", "13": "ierdna", "14": "elisav", "15": "namor", "16": "jugcvb"}
5	{"1": "ailemac", "2": "nielk", "3": "nuicarc", "4": "ihcseruj", "5": "qweasdas"}
6	[1, 2, 3, 4, 5]
SELECT JSON_OBJECTAGG(t1.pkey, t2.jcol) FROM data_table AS t1, json_data AS t2;
JSON_OBJECTAGG(t1.pkey, t2.jcol)
{"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]}
SELECT JSON_ARRAYAGG(jcol) FROM json_data;
JSON_ARRAYAGG(jcol)
[{"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]]
include/diff_tables.inc [master:json_data, slave:json_data]
[Connection Master]
# Update values in the table
UPDATE json_data SET jcol= (SELECT JSON_ARRAYAGG(vcol) FROM data_table) WHERE nkey = 1;
include/sync_slave_sql_with_master.inc
[Connection Slave]
SELECT * FROM json_data WHERE nkey = 1;
nkey	jcol
1	["namor", "uciov", "ierdna", "elisav", "jugcvb"]
include/diff_tables.inc [master:json_data, slave:json_data]
[Connection Master]
# Delete values from the table
DELETE FROM json_data WHERE jcol  = (SELECT JSON_ARRAYAGG(pkey) FROM data_table);
include/sync_slave_sql_with_master.inc
[Connection Slave]
SELECT * FROM json_data;
nkey	jcol
1	["namor", "uciov", "ierdna", "elisav", "jugcvb"]
2	["ailemac", "nielk", "nuicarc", "ihcseruj", "qweasdas"]
3	[30, 24, 26, 28, 32]
4	{"12": "uciov", "13": "ierdna", "14": "elisav", "15": "namor", "16": "jugcvb"}
5	{"1": "ailemac", "2": "nielk", "3": "nuicarc", "4": "ihcseruj", "5": "qweasdas"}
include/diff_tables.inc [master:json_data, slave:json_data]
[Connection Master]
DROP TABLE data_table;
DROP TABLE json_data;
include/sync_slave_sql_with_master.inc
#
# WL#9191: ADD JSON_PRETTY FUNCTION
#
[Connection Master]
# Create tables of various data types
CREATE TABLE data (pkey INT PRIMARY KEY AUTO_INCREMENT,
txt TEXT, vcol VARCHAR(20), num INT,
gcol INT AS (num * 2) VIRTUAL,
gcol2 JSON AS (JSON_PRETTY(JSON_OBJECT(num,gcol))));
CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT, jcol TEXT, jgcol TEXT AS (JSON_PRETTY(jcol)));
# Insert values into the tables using the pretty function
INSERT INTO data (txt, vcol, num) VALUES ('abc', 'namotgr', 150),
('fwjh4', 'ucierov', 142),
('8942rhkjh', 'roiu3r', 913),
('imfmf', 'r3jr2', 15),
('32rj2jr', 'r2ihrhr', 32);
INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_OBJECTAGG(pkey, gcol)) FROM data;
INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_ARRAYAGG(txt)) FROM data;
INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_PRETTY(gcol2)) FROM data;
INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_OBJECT(num , vcol)) FROM data;
INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_OBJECTAGG(pkey , txt)) FROM data;
INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_ARRAY(gcol)) FROM data;
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether tables were created on the slave
SELECT * FROM json_data;
pk	jcol	jgcol
1	{
  "1": 300,
  "2": 284,
  "3": 1826,
  "4": 30,
  "5": 64
}	{
  "1": 300,
  "2": 284,
  "3": 1826,
  "4": 30,
  "5": 64
}
2	[
  "abc",
  "fwjh4",
  "8942rhkjh",
  "imfmf",
  "32rj2jr"
]	[
  "abc",
  "fwjh4",
  "8942rhkjh",
  "imfmf",
  "32rj2jr"
]
3	{
  "150": 300
}	{
  "150": 300
}
4	{
  "142": 284
}	{
  "142": 284
}
5	{
  "913": 1826
}	{
  "913": 1826
}
6	{
  "15": 30
}	{
  "15": 30
}
7	{
  "32": 64
}	{
  "32": 64
}
10	{
  "150": "namotgr"
}	{
  "150": "namotgr"
}
11	{
  "142": "ucierov"
}	{
  "142": "ucierov"
}
12	{
  "913": "roiu3r"
}	{
  "913": "roiu3r"
}
13	{
  "15": "r3jr2"
}	{
  "15": "r3jr2"
}
14	{
  "32": "r2ihrhr"
}	{
  "32": "r2ihrhr"
}
17	{
  "1": "abc",
  "2": "fwjh4",
  "3": "8942rhkjh",
  "4": "imfmf",
  "5": "32rj2jr"
}	{
  "1": "abc",
  "2": "fwjh4",
  "3": "8942rhkjh",
  "4": "imfmf",
  "5": "32rj2jr"
}
18	[
  300
]	[
  300
]
19	[
  284
]	[
  284
]
20	[
  1826
]	[
  1826
]
21	[
  30
]	[
  30
]
22	[
  64
]	[
  64
]
SELECT gcol2  FROM data;
gcol2
{"150": 300}
{"142": 284}
{"913": 1826}
{"15": 30}
{"32": 64}
include/diff_tables.inc [master:json_data, slave:json_data]
include/diff_tables.inc [master:data, slave:data]
[Connection Master]
# Update values in the table
UPDATE json_data SET jcol= (SELECT JSON_PRETTY(JSON_ARRAYAGG(gcol2)) FROM data) WHERE pk= 2;
UPDATE json_data SET jcol= (SELECT JSON_PRETTY(JSON_OBJECTAGG(pk,gcol)) FROM data) WHERE pk = 1;
include/sync_slave_sql_with_master.inc
[Connection Slave]
SELECT * FROM json_data;
pk	jcol	jgcol
1	{
  "1": 64
}	{
  "1": 64
}
2	[
  {
    "150": 300
  },
  {
    "142": 284
  },
  {
    "913": 1826
  },
  {
    "15": 30
  },
  {
    "32": 64
  }
]	[
  {
    "150": 300
  },
  {
    "142": 284
  },
  {
    "913": 1826
  },
  {
    "15": 30
  },
  {
    "32": 64
  }
]
3	{
  "150": 300
}	{
  "150": 300
}
4	{
  "142": 284
}	{
  "142": 284
}
5	{
  "913": 1826
}	{
  "913": 1826
}
6	{
  "15": 30
}	{
  "15": 30
}
7	{
  "32": 64
}	{
  "32": 64
}
10	{
  "150": "namotgr"
}	{
  "150": "namotgr"
}
11	{
  "142": "ucierov"
}	{
  "142": "ucierov"
}
12	{
  "913": "roiu3r"
}	{
  "913": "roiu3r"
}
13	{
  "15": "r3jr2"
}	{
  "15": "r3jr2"
}
14	{
  "32": "r2ihrhr"
}	{
  "32": "r2ihrhr"
}
17	{
  "1": "abc",
  "2": "fwjh4",
  "3": "8942rhkjh",
  "4": "imfmf",
  "5": "32rj2jr"
}	{
  "1": "abc",
  "2": "fwjh4",
  "3": "8942rhkjh",
  "4": "imfmf",
  "5": "32rj2jr"
}
18	[
  300
]	[
  300
]
19	[
  284
]	[
  284
]
20	[
  1826
]	[
  1826
]
21	[
  30
]	[
  30
]
22	[
  64
]	[
  64
]
include/diff_tables.inc [master:json_data, slave:json_data]
[Connection Master]
# Delete values from the table
DELETE FROM json_data WHERE JSON_PRETTY(jgcol)  = (SELECT JSON_PRETTY(JSON_OBJECTAGG(pkey,txt)) FROM data);
include/sync_slave_sql_with_master.inc
[Connection Slave]
SELECT * FROM json_data;
pk	jcol	jgcol
1	{
  "1": 64
}	{
  "1": 64
}
2	[
  {
    "150": 300
  },
  {
    "142": 284
  },
  {
    "913": 1826
  },
  {
    "15": 30
  },
  {
    "32": 64
  }
]	[
  {
    "150": 300
  },
  {
    "142": 284
  },
  {
    "913": 1826
  },
  {
    "15": 30
  },
  {
    "32": 64
  }
]
3	{
  "150": 300
}	{
  "150": 300
}
4	{
  "142": 284
}	{
  "142": 284
}
5	{
  "913": 1826
}	{
  "913": 1826
}
6	{
  "15": 30
}	{
  "15": 30
}
7	{
  "32": 64
}	{
  "32": 64
}
10	{
  "150": "namotgr"
}	{
  "150": "namotgr"
}
11	{
  "142": "ucierov"
}	{
  "142": "ucierov"
}
12	{
  "913": "roiu3r"
}	{
  "913": "roiu3r"
}
13	{
  "15": "r3jr2"
}	{
  "15": "r3jr2"
}
14	{
  "32": "r2ihrhr"
}	{
  "32": "r2ihrhr"
}
18	[
  300
]	[
  300
]
19	[
  284
]	[
  284
]
20	[
  1826
]	[
  1826
]
21	[
  30
]	[
  30
]
22	[
  64
]	[
  64
]
include/diff_tables.inc [master:json_data, slave:json_data]
[Connection Master]
DROP TABLE data;
DROP TABLE json_data;
include/sync_slave_sql_with_master.inc
#
# WL#8963: SUPPORT FOR PARTIAL UPDATE OF JSON IN THE OPTIMIZER
# WL#9192: ADD JSON_STORAGE_SIZE / JSON_STORAGE_FREE FUNCTIONS
#
[Connection Master]
# Create table containing various types of data
CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT,
id INT,
jd JSON,
td TEXT,
gcol INT AS (JSON_STORAGE_SIZE(jd)),
gcol2 INT AS (JSON_STORAGE_FREE(jd)) VIRTUAL,
gcol3 JSON AS (JSON_ARRAY(id,jd,td,gcol,gcol2)),
gcol4 JSON AS (JSON_OBJECT(id,jd,gcol,td)) VIRTUAL);
# Insert data into the table
INSERT INTO json_data(id,jd,td) VALUES(10, '{"a":1}', 'characters'),
(20, '[10,20,30]', 'alphabet'),
(30, '[{"a":"abc"},{"b":"abcd"}]', 'words'),
(40, '{"key":["array", "of", "strings"]}', 'letters'),
(50, '{"key":[{"key":"value"},{"key":"value2"}]}', 'strings');
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether table was created on the slave
SELECT * FROM json_data;
pk	id	jd	td	gcol	gcol2	gcol3	gcol4
1	10	{"a": 1}	characters	13	0	[10, {"a": 1}, "characters", 13, 0]	{"10": {"a": 1}, "13": "characters"}
2	20	[10, 20, 30]	alphabet	14	0	[20, [10, 20, 30], "alphabet", 14, 0]	{"14": "alphabet", "20": [10, 20, 30]}
3	30	[{"a": "abc"}, {"b": "abcd"}]	words	44	0	[30, [{"a": "abc"}, {"b": "abcd"}], "words", 44, 0]	{"30": [{"a": "abc"}, {"b": "abcd"}], "44": "words"}
4	40	{"key": ["array", "of", "strings"]}	letters	45	0	[40, {"key": ["array", "of", "strings"]}, "letters", 45, 0]	{"40": {"key": ["array", "of", "strings"]}, "45": "letters"}
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"}
include/diff_tables.inc [master:json_data, slave:json_data]
[Connection Master]
# Update values in the table
UPDATE json_data SET jd = JSON_REPLACE(jd, '$[0].a',"a");
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether values were updated
SELECT * FROM json_data;
pk	id	jd	td	gcol	gcol2	gcol3	gcol4
1	10	{"a": "a"}	characters	15	0	[10, {"a": "a"}, "characters", 15, 0]	{"10": {"a": "a"}, "15": "characters"}
2	20	[10, 20, 30]	alphabet	14	0	[20, [10, 20, 30], "alphabet", 14, 0]	{"14": "alphabet", "20": [10, 20, 30]}
3	30	[{"a": "a"}, {"b": "abcd"}]	words	44	2	[30, [{"a": "a"}, {"b": "abcd"}], "words", 44, 2]	{"30": [{"a": "a"}, {"b": "abcd"}], "44": "words"}
4	40	{"key": ["array", "of", "strings"]}	letters	45	0	[40, {"key": ["array", "of", "strings"]}, "letters", 45, 0]	{"40": {"key": ["array", "of", "strings"]}, "45": "letters"}
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"}
SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data;
JSON_STORAGE_SIZE(gcol3)	JSON_STORAGE_SIZE(gcol4)
45	48
42	45
67	70
72	75
93	96
SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data;
JSON_STORAGE_FREE(gcol3)	JSON_STORAGE_FREE(gcol4)
0	0
0	0
0	0
0	0
0	0
include/diff_tables.inc [master:json_data, slave:json_data]
[Connection Master]
# Update more values in the table
UPDATE json_data SET jd = JSON_SET(jd, '$.key[0]', "ar");
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether values were updated
SELECT * FROM json_data;
pk	id	jd	td	gcol	gcol2	gcol3	gcol4
1	10	{"a": "a"}	characters	15	0	[10, {"a": "a"}, "characters", 15, 0]	{"10": {"a": "a"}, "15": "characters"}
2	20	[10, 20, 30]	alphabet	14	0	[20, [10, 20, 30], "alphabet", 14, 0]	{"14": "alphabet", "20": [10, 20, 30]}
3	30	[{"a": "a"}, {"b": "abcd"}]	words	44	2	[30, [{"a": "a"}, {"b": "abcd"}], "words", 44, 2]	{"30": [{"a": "a"}, {"b": "abcd"}], "44": "words"}
4	40	{"key": ["ar", "of", "strings"]}	letters	45	3	[40, {"key": ["ar", "of", "strings"]}, "letters", 45, 3]	{"40": {"key": ["ar", "of", "strings"]}, "45": "letters"}
5	50	{"key": ["ar", {"key": "value2"}]}	strings	66	17	[50, {"key": ["ar", {"key": "value2"}]}, "strings", 66, 17]	{"50": {"key": ["ar", {"key": "value2"}]}, "66": "strings"}
SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data;
JSON_STORAGE_SIZE(gcol3)	JSON_STORAGE_SIZE(gcol4)
45	48
42	45
67	70
69	72
76	79
SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data;
JSON_STORAGE_FREE(gcol3)	JSON_STORAGE_FREE(gcol4)
0	0
0	0
0	0
0	0
0	0
include/diff_tables.inc [master:json_data, slave:json_data]
[Connection Master]
# Update values again
UPDATE json_data SET jd = JSON_REPLACE(jd, '$.key', "hello");
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether values were updated
SELECT * FROM json_data;
pk	id	jd	td	gcol	gcol2	gcol3	gcol4
1	10	{"a": "a"}	characters	15	0	[10, {"a": "a"}, "characters", 15, 0]	{"10": {"a": "a"}, "15": "characters"}
2	20	[10, 20, 30]	alphabet	14	0	[20, [10, 20, 30], "alphabet", 14, 0]	{"14": "alphabet", "20": [10, 20, 30]}
3	30	[{"a": "a"}, {"b": "abcd"}]	words	44	2	[30, [{"a": "a"}, {"b": "abcd"}], "words", 44, 2]	{"30": [{"a": "a"}, {"b": "abcd"}], "44": "words"}
4	40	{"key": "hello"}	letters	45	24	[40, {"key": "hello"}, "letters", 45, 24]	{"40": {"key": "hello"}, "45": "letters"}
5	50	{"key": "hello"}	strings	66	45	[50, {"key": "hello"}, "strings", 66, 45]	{"50": {"key": "hello"}, "66": "strings"}
SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data;
JSON_STORAGE_SIZE(gcol3)	JSON_STORAGE_SIZE(gcol4)
45	48
42	45
67	70
48	51
48	51
SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data;
JSON_STORAGE_FREE(gcol3)	JSON_STORAGE_FREE(gcol4)
0	0
0	0
0	0
0	0
0	0
include/diff_tables.inc [master:json_data, slave:json_data]
[Connection Master]
# Update values such that they can fill up the "free" space
UPDATE json_data SET jd= JSON_SET(jd, '$.key', '["h","e","l","l","o"]');
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether values were updated
SELECT * FROM json_data;
pk	id	jd	td	gcol	gcol2	gcol3	gcol4
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"}
2	20	[10, 20, 30]	alphabet	14	0	[20, [10, 20, 30], "alphabet", 14, 0]	{"14": "alphabet", "20": [10, 20, 30]}
3	30	[{"a": "a"}, {"b": "abcd"}]	words	44	2	[30, [{"a": "a"}, {"b": "abcd"}], "words", 44, 2]	{"30": [{"a": "a"}, {"b": "abcd"}], "44": "words"}
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"}
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"}
SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data;
JSON_STORAGE_SIZE(gcol3)	JSON_STORAGE_SIZE(gcol4)
77	80
42	45
67	70
64	67
64	67
SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data;
JSON_STORAGE_FREE(gcol3)	JSON_STORAGE_FREE(gcol4)
0	0
0	0
0	0
0	0
0	0
[connection master]
# Update via an updatable view.
CREATE VIEW json_data_view AS SELECT * FROM json_data;
UPDATE json_data_view SET jd = JSON_SET(jd, '$.key', '[]'),
jd = JSON_REMOVE(jd, '$[0].a');
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether values were updated
SELECT * FROM json_data;
pk	id	jd	td	gcol	gcol2	gcol3	gcol4
1	10	{"key": "[]"}	characters	47	29	[10, {"key": "[]"}, "characters", 47, 29]	{"10": {"key": "[]"}, "47": "characters"}
2	20	[10, 20, 30]	alphabet	14	0	[20, [10, 20, 30], "alphabet", 14, 0]	{"14": "alphabet", "20": [10, 20, 30]}
3	30	[{}, {"b": "abcd"}]	words	44	12	[30, [{}, {"b": "abcd"}], "words", 44, 12]	{"30": [{}, {"b": "abcd"}], "44": "words"}
4	40	{"key": "[]"}	letters	45	27	[40, {"key": "[]"}, "letters", 45, 27]	{"40": {"key": "[]"}, "45": "letters"}
5	50	{"key": "[]"}	strings	66	48	[50, {"key": "[]"}, "strings", 66, 48]	{"50": {"key": "[]"}, "66": "strings"}
SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data;
JSON_STORAGE_SIZE(gcol3)	JSON_STORAGE_SIZE(gcol4)
48	51
42	45
57	60
45	48
45	48
SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data;
JSON_STORAGE_FREE(gcol3)	JSON_STORAGE_FREE(gcol4)
0	0
0	0
0	0
0	0
0	0
include/diff_tables.inc [master:json_data, slave:json_data]
[Connection Master]
# Clean up
DROP VIEW json_data_view;
DROP TABLE json_data;
include/sync_slave_sql_with_master.inc
#
# WL#9831: RANGES IN JSON PATH EXPRESSIONS
#
[Connection Master]
# Create table with JSON data
CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT,
id INT,
jd JSON,
td TEXT,
gcol JSON AS (JSON_EXTRACT(jd, '$[last -2 to last]')) VIRTUAL,
gcol2 VARCHAR(100) AS (JSON_EXTRACT(td, '$[0 to 3]')) STORED,
gcol3 JSON AS (JSON_EXTRACT(JSON_ARRAY(id,jd,td,gcol,gcol2),
'$[2][last -3 to last -1]')) VIRTUAL,
gcol4 JSON AS (JSON_EXTRACT(JSON_OBJECT(id,jd,gcol,td),
'$.id[0 to last]')) STORED,
key(gcol2));
# Insert data into the table
INSERT INTO json_data(id,jd,td) VALUES(10, '["abc", "def", "ghi", "jkl", "mno"]',
'["pqr", "stu", "vwx", "xyz"]'),
(20, '[10, 20, 30, 40, 50]',
'["alpha", "beta", "gamma", "delta"]'),
(30, '["z", "y", "x", "w", "v"]',
'["Bengaluru", "Mysuru", "Davangere", "Belagaum"]'),
(40, '["array", "of", "strings", "and", "more"]',
'[2, 3, 5, 7]'),
(50, '[{"key":[{"key":"value"},{"key":"value2"}]},
                                             {"key":[{"key":"value3"},{"key":"value4"}]}]',
'["N", "U", "L", "L"]');
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether table was created on the slave
SELECT * FROM json_data;
pk	id	jd	td	gcol	gcol2	gcol3	gcol4
1	10	["abc", "def", "ghi", "jkl", "mno"]	["pqr", "stu", "vwx", "xyz"]	["ghi", "jkl", "mno"]	["pqr", "stu", "vwx", "xyz"]	NULL	NULL
2	20	[10, 20, 30, 40, 50]	["alpha", "beta", "gamma", "delta"]	[30, 40, 50]	["alpha", "beta", "gamma", "delta"]	NULL	NULL
3	30	["z", "y", "x", "w", "v"]	["Bengaluru", "Mysuru", "Davangere", "Belagaum"]	["x", "w", "v"]	["Bengaluru", "Mysuru", "Davangere", "Belagaum"]	NULL	NULL
4	40	["array", "of", "strings", "and", "more"]	[2, 3, 5, 7]	["strings", "and", "more"]	[2, 3, 5, 7]	NULL	NULL
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
include/diff_tables.inc [master:json_data, slave:json_data]
[Connection Master]
# Update values in the table
UPDATE json_data SET jd = JSON_SET(jd, '$[1]', "replaced") WHERE
JSON_CONTAINS(JSON_EXTRACT(jd, '$[0 to last]'), '[10, 20, 30, 40, 50]');
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether values were updated
SELECT * FROM json_data;
pk	id	jd	td	gcol	gcol2	gcol3	gcol4
1	10	["abc", "def", "ghi", "jkl", "mno"]	["pqr", "stu", "vwx", "xyz"]	["ghi", "jkl", "mno"]	["pqr", "stu", "vwx", "xyz"]	NULL	NULL
2	20	[10, "replaced", 30, 40, 50]	["alpha", "beta", "gamma", "delta"]	[30, 40, 50]	["alpha", "beta", "gamma", "delta"]	NULL	NULL
3	30	["z", "y", "x", "w", "v"]	["Bengaluru", "Mysuru", "Davangere", "Belagaum"]	["x", "w", "v"]	["Bengaluru", "Mysuru", "Davangere", "Belagaum"]	NULL	NULL
4	40	["array", "of", "strings", "and", "more"]	[2, 3, 5, 7]	["strings", "and", "more"]	[2, 3, 5, 7]	NULL	NULL
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
SELECT JSON_EXTRACT(td, '$[0 to last]') FROM json_data;
JSON_EXTRACT(td, '$[0 to last]')
["pqr", "stu", "vwx", "xyz"]
["alpha", "beta", "gamma", "delta"]
["Bengaluru", "Mysuru", "Davangere", "Belagaum"]
[2, 3, 5, 7]
["N", "U", "L", "L"]
[Connection Master]
# Clean up
DROP TABLE json_data;
include/sync_slave_sql_with_master.inc
#
# WL#9692: ADD JSON_MERGE_PATCH, RENAME JSON_MERGE TO JSON_MERGE_PRESERVE
#
[Connection Master]
# Create table with JSON data
CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT,
id INT,
jd JSON,
td TEXT,
gcol VARCHAR(30) AS (JSON_EXTRACT(JSON_MERGE_PATCH(jd,JSON_OBJECT(id,td)), '$.a')) VIRTUAL,
KEY(gcol));
# Insert data into the table
INSERT INTO json_data(id,jd,td) VALUES(10,'["a","b","c","d","e"]', "pink"),
(20,'{"a":[1,2,6,7,9]}', "floyd"),
(30,'{"a":["ears","nose","eyes"]}', "megadeth"),
(40,'[null,null,"null",0,1]', "def"),
(50,'{"a":null}', "leppard");
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether table was created on the slave
SELECT * FROM json_data;
pk	id	jd	td	gcol
1	10	["a", "b", "c", "d", "e"]	pink	NULL
2	20	{"a": [1, 2, 6, 7, 9]}	floyd	[1, 2, 6, 7, 9]
3	30	{"a": ["ears", "nose", "eyes"]}	megadeth	["ears", "nose", "eyes"]
4	40	[null, null, "null", 0, 1]	def	NULL
5	50	{"a": null}	leppard	null
include/diff_tables.inc [master:json_data, slave:json_data]
[Connection Master]
# Create tables using the JSON_MERGE_PATCH function
CREATE TABLE json_data2(field1 JSON);
INSERT INTO json_data2
SELECT JSON_MERGE_PATCH(JSON_ARRAYAGG(jd), JSON_OBJECTAGG(pk, jd))
FROM json_data;
CREATE TABLE json_data3(field1 JSON);
INSERT INTO json_data3
SELECT JSON_MERGE_PATCH(JSON_OBJECTAGG(id, gcol), JSON_ARRAYAGG(td))
FROM json_data;
CREATE TABLE json_data4(field1 JSON);
INSERT INTO json_data4
SELECT JSON_MERGE_PATCH(JSON_OBJECT(id, jd), JSON_ARRAY(gcol))
FROM json_data;
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether the tables were created on the slave
SELECT * FROM json_data2;
field1
{"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": {}}
SELECT * FROM json_data3;
field1
["pink", "floyd", "megadeth", "def", "leppard"]
SELECT * FROM json_data4;
field1
[null]
["[1, 2, 6, 7, 9]"]
["[\"ears\", \"nose\", \"eyes\"]"]
[null]
["null"]
SELECT JSON_MERGE_PATCH(t1.field1, t2.field1) FROM json_data2 as t1, json_data3 as t2;
JSON_MERGE_PATCH(t1.field1, t2.field1)
["pink", "floyd", "megadeth", "def", "leppard"]
SELECT JSON_MERGE_PRESERVE(t1.field1, t2.field1) FROM json_data3 as t1, json_data4 as t2;
JSON_MERGE_PRESERVE(t1.field1, t2.field1)
["pink", "floyd", "megadeth", "def", "leppard", null]
["pink", "floyd", "megadeth", "def", "leppard", "[1, 2, 6, 7, 9]"]
["pink", "floyd", "megadeth", "def", "leppard", "[\"ears\", \"nose\", \"eyes\"]"]
["pink", "floyd", "megadeth", "def", "leppard", null]
["pink", "floyd", "megadeth", "def", "leppard", "null"]
[Connection Master]
# Update values in the table
UPDATE json_data SET jd = JSON_SET(jd, '$[1]', JSON_MERGE_PATCH(JSON_ARRAY(pk),JSON_ARRAY(td))) WHERE id IN (10,40);
include/sync_slave_sql_with_master.inc
[Connection Slave]
include/diff_tables.inc [master:json_data, slave:json_data]
# Check whether values were updated
SELECT * FROM json_data;
pk	id	jd	td	gcol
1	10	["a", ["pink"], "c", "d", "e"]	pink	NULL
2	20	{"a": [1, 2, 6, 7, 9]}	floyd	[1, 2, 6, 7, 9]
3	30	{"a": ["ears", "nose", "eyes"]}	megadeth	["ears", "nose", "eyes"]
4	40	[null, ["def"], "null", 0, 1]	def	NULL
5	50	{"a": null}	leppard	null
[Connection Master]
# Clean up
DROP TABLE json_data, json_data2, json_data3, json_data4;
include/sync_slave_sql_with_master.inc
#
# WL#8867: ADD JSON TABLE FUNCTIONS
#
[Connection Master]
# Create table using data obtained from JSON_TABLE function
CREATE TABLE json_data(j json);
INSERT INTO json_data VALUES('{"a":1, "b":"trucks", "c":[1,2,3], "d":"taste", "e":"93-01-01", "f":"03:48:07"}');
INSERT INTO json_data VALUES('{"a":2, "b":"rickshaws", "c":{"key":"value"}, "d":"smell" , "e":"12-11-12", "f":"09:09:10"}');
INSERT INTO json_data VALUES('{"a":3, "b":"cars", "c":["a","b","c","d"], "d":"sight", "e":"01-01-01", "f":"05:18:12"}');
INSERT INTO json_data VALUES('{"a":4, "b":"bikes", "c":{"a":[1,2]}, "d":"hearing", "e":"98-05-06", "f":"00:00:00" }');
INSERT INTO json_data VALUES('{"a":5, "b":"mopeds", "c":["one", "two", "three"], "d":"touch", "e":"56-12-07", "f":"12:12:12"}');
CREATE TABLE data(i INT, v VARCHAR(10), j JSON, b BLOB, d DATE, t TIME, n JSON);
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;
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether the table was created on the slave
SELECT * FROM data;
i	v	j	b	d	t	n
1	trucks	[1, 2, 3]	taste	1993-01-01	03:48:07	1
2	rickshaws	{"key": "value"}	smell	2012-11-12	09:09:10	{"key": "value"}
3	cars	["a", "b", "c", "d"]	sight	2001-01-01	05:18:12	"a"
4	bikes	{"a": [1, 2]}	hearing	1998-05-06	00:00:00	{"a": [1, 2]}
5	mopeds	["one", "two", "three"]	touch	2056-12-07	12:12:12	"one"
include/diff_tables.inc [master:data, slave:data]
[Connection Master]
# Update values in the table with data obtained from JSON_TABLE
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;
UPDATE data SET v = (SELECT ucol FROM JSON_TABLE('{"key":["update"]}', '$' COLUMNS(ucol VARCHAR(10) PATH '$.key[0]')) AS alias) WHERE i = 4;
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether the values were updated
SELECT j,v FROM data;
j	v
[1, 2, 3]	trucks
{"a": 2, "b": "rickshaws", "c": {"key": "value"}, "d": "smell", "e": "12-11-12", "f": "09:09:10"}	rickshaws
["a", "b", "c", "d"]	cars
{"a": [1, 2]}	update
["one", "two", "three"]	mopeds
include/diff_tables.inc [master:data, slave:data]
[Connection Master]
# Delete values from the table
DELETE FROM data WHERE i = (SELECT dcol FROM JSON_TABLE('{"key":["delete",1,"2"]}', '$' COLUMNS(dcol INT PATH '$.key[1]')) AS alias);
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether the row was deleted
SELECT * FROM data;
i	v	j	b	d	t	n
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"}
3	cars	["a", "b", "c", "d"]	sight	2001-01-01	05:18:12	"a"
4	update	{"a": [1, 2]}	hearing	1998-05-06	00:00:00	{"a": [1, 2]}
5	mopeds	["one", "two", "three"]	touch	2056-12-07	12:12:12	"one"
include/diff_tables.inc [master:data, slave:data]
[Connection Master]
# cleanup
DROP TABLE data,json_data;
include/sync_slave_sql_with_master.inc
#
# Bug#26177130: SLAVE CAN'T FIND ROW USING HASH_SCAN
#               IF JSON HAS DIFFERENT BINARY FORMAT
#
[connection slave]
include/stop_slave_sql.inc
include/begin_replace_combination.inc [BOTH.binlog_format=row,mixed,statement -> GLOBAL.slave_rows_search_algorithms=HASH_SCAN,TABLE_SCAN,INDEX_SCAN]
Warnings:
Warning	1287	'@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
Warning	1287	'@@binlog_format' is deprecated and will be removed in a future release.
Warning	1287	'@@binlog_format' is deprecated and will be removed in a future release.
include/start_slave_sql.inc
[connection master]
SET @old_binlog_format = @@SESSION.binlog_format;
Warnings:
Warning	1287	'@@binlog_format' is deprecated and will be removed in a future release.
SET SESSION binlog_format = ROW;
Warnings:
Warning	1287	'@@binlog_format' is deprecated and will be removed in a future release.
[connection slave]
include/stop_slave.inc
include/start_slave.inc
[connection master]
CREATE TABLE t (j JSON);
SET SQL_LOG_BIN = 0;
INSERT INTO t VALUES ('["abc", "abcdefghij"]');
UPDATE t SET j = JSON_SET(j, '$[0]', 'a');
SET SQL_LOG_BIN = 1;
SELECT j, JSON_STORAGE_SIZE(j), JSON_STORAGE_FREE(j) FROM t;
j	JSON_STORAGE_SIZE(j)	JSON_STORAGE_FREE(j)
["a", "abcdefghij"]	26	2
include/sync_slave_sql_with_master.inc
INSERT INTO t VALUES ('["a", "abcdefghij"]');
SELECT j, JSON_STORAGE_SIZE(j), JSON_STORAGE_FREE(j) FROM t;
j	JSON_STORAGE_SIZE(j)	JSON_STORAGE_FREE(j)
["a", "abcdefghij"]	24	0
# At this point, the master and the slave have the same JSON document,
# but the binary representation is different (the master has a 'gap' due
# to partial update).
[connection master]
DELETE FROM t;
# Verify that the slave and the master are in sync after the DELETE.
# Before the fix, the slave could not locate the row due with
# slave_rows_search_algorithms=HASH_SCAN because of the difference in
# the representation.
include/sync_slave_sql_with_master.inc
# Clean up
[connection master]
DROP TABLE t;
include/sync_slave_sql_with_master.inc
include/stop_slave.inc
include/end_replace_combination.inc [BOTH.binlog_format=row,mixed,statement -> GLOBAL.slave_rows_search_algorithms=HASH_SCAN,TABLE_SCAN,INDEX_SCAN]
Warnings:
Warning	1287	'@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
Warning	1287	'@@binlog_format' is deprecated and will be removed in a future release.
Warning	1287	'@@binlog_format' is deprecated and will be removed in a future release.
include/start_slave.inc
[connection master]
SET SESSION binlog_format = @old_binlog_format;
Warnings:
Warning	1287	'@@binlog_format' is deprecated and will be removed in a future release.
include/rpl_end.inc