1include/master-slave.inc 2Warnings: 3Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. 4Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 5[connection master] 6SET SESSION sql_log_bin= 0; 7call mtr.add_suppression('.*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*'); 8SET SESSION sql_log_bin= 1; 9SET SESSION sql_log_bin= 0; 10call mtr.add_suppression('.*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*'); 11SET SESSION sql_log_bin= 1; 12# 13# WL#8132 JSON datatype and binary storage format 14# 15CREATE TABLE t1 (i INT PRIMARY KEY, j JSON); 16INSERT INTO t1 VALUES (0, NULL); 17INSERT INTO t1 VALUES (1, '{"a": 2}'); 18INSERT INTO t1 VALUES (2, '[1,2]'); 19INSERT INTO t1 VALUES (3, '{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}'); 20INSERT INTO t1 VALUES (4, '["here", ["I", "am"], "!!!"]'); 21INSERT INTO t1 VALUES (5, '"scalar string"'); 22INSERT INTO t1 VALUES (6, 'true'); 23INSERT INTO t1 VALUES (7, 'false'); 24INSERT INTO t1 VALUES (8, 'null'); 25INSERT INTO t1 VALUES (9, '-1'); 26INSERT INTO t1 VALUES (10, CAST(CAST(1 AS UNSIGNED) AS JSON)); 27INSERT INTO t1 VALUES (11, '32767'); 28INSERT INTO t1 VALUES (12, '32768'); 29INSERT INTO t1 VALUES (13, '-32768'); 30INSERT INTO t1 VALUES (14, '-32769'); 31INSERT INTO t1 VALUES (15, '2147483647'); 32INSERT INTO t1 VALUES (16, '2147483648'); 33INSERT INTO t1 VALUES (17, '-2147483648'); 34INSERT INTO t1 VALUES (18, '-2147483649'); 35INSERT INTO t1 VALUES (19, '18446744073709551615'); 36INSERT INTO t1 VALUES (20, '18446744073709551616'); 37INSERT INTO t1 VALUES (21, '3.14'); 38INSERT INTO t1 VALUES (22, '{}'); 39INSERT INTO t1 VALUES (23, '[]'); 40INSERT INTO t1 VALUES (24, CAST(CAST('2015-01-15 23:24:25' AS DATETIME) AS JSON)); 41INSERT INTO t1 VALUES (25, CAST(CAST('23:24:25' AS TIME) AS JSON)); 42INSERT INTO t1 VALUES (26, CAST(CAST('2015-01-15' AS DATE) AS JSON)); 43INSERT INTO t1 VALUES (27, CAST(TIMESTAMP'2015-01-15 23:24:25' AS JSON)); 44INSERT INTO t1 VALUES (28, CAST(ST_GeomFromText('POINT(1 1)') AS JSON)); 45INSERT INTO t1 VALUES (29, CAST('[]' AS CHAR CHARACTER SET 'ascii')); 46INSERT INTO t1 VALUES (30, CAST(x'cafebabe' AS JSON)); 47SELECT * FROM t1 ORDER BY i; 48i j 490 NULL 501 {"a": 2} 512 [1, 2] 523 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 534 ["here", ["I", "am"], "!!!"] 545 "scalar string" 556 true 567 false 578 null 589 -1 5910 1 6011 32767 6112 32768 6213 -32768 6314 -32769 6415 2147483647 6516 2147483648 6617 -2147483648 6718 -2147483649 6819 18446744073709551615 6920 1.8446744073709552e19 7021 3.14 7122 {} 7223 [] 7324 "2015-01-15 23:24:25.000000" 7425 "23:24:25.000000" 7526 "2015-01-15" 7627 "2015-01-15 23:24:25.000000" 7728 {"type": "Point", "coordinates": [1.0, 1.0]} 7829 [] 7930 "base64:type15:yv66vg==" 80# Copy JSON values 81CREATE TABLE t2 (i INT PRIMARY KEY, j JSON); 82INSERT INTO t2 SELECT * FROM t1; 83SELECT *, JSON_TYPE(j) FROM t2 ORDER BY i; 84i j JSON_TYPE(j) 850 NULL NULL 861 {"a": 2} OBJECT 872 [1, 2] ARRAY 883 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} OBJECT 894 ["here", ["I", "am"], "!!!"] ARRAY 905 "scalar string" STRING 916 true BOOLEAN 927 false BOOLEAN 938 null NULL 949 -1 INTEGER 9510 1 UNSIGNED INTEGER 9611 32767 INTEGER 9712 32768 INTEGER 9813 -32768 INTEGER 9914 -32769 INTEGER 10015 2147483647 INTEGER 10116 2147483648 INTEGER 10217 -2147483648 INTEGER 10318 -2147483649 INTEGER 10419 18446744073709551615 UNSIGNED INTEGER 10520 1.8446744073709552e19 DOUBLE 10621 3.14 DOUBLE 10722 {} OBJECT 10823 [] ARRAY 10924 "2015-01-15 23:24:25.000000" DATETIME 11025 "23:24:25.000000" TIME 11126 "2015-01-15" DATE 11227 "2015-01-15 23:24:25.000000" DATETIME 11328 {"type": "Point", "coordinates": [1.0, 1.0]} OBJECT 11429 [] ARRAY 11530 "base64:type15:yv66vg==" BLOB 116# Convert JSON values to TEXT 117CREATE TABLE t3 (i INT PRIMARY KEY, txt TEXT); 118INSERT INTO t3 SELECT * FROM t1; 119SELECT *, JSON_TYPE(txt) FROM t3 ORDER BY i; 120i txt JSON_TYPE(txt) 1210 NULL NULL 1221 {"a": 2} OBJECT 1232 [1, 2] ARRAY 1243 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} OBJECT 1254 ["here", ["I", "am"], "!!!"] ARRAY 1265 "scalar string" STRING 1276 true BOOLEAN 1287 false BOOLEAN 1298 null NULL 1309 -1 INTEGER 13110 1 INTEGER 13211 32767 INTEGER 13312 32768 INTEGER 13413 -32768 INTEGER 13514 -32769 INTEGER 13615 2147483647 INTEGER 13716 2147483648 INTEGER 13817 -2147483648 INTEGER 13918 -2147483649 INTEGER 14019 18446744073709551615 UNSIGNED INTEGER 14120 1.8446744073709552e19 DOUBLE 14221 3.14 DOUBLE 14322 {} OBJECT 14423 [] ARRAY 14524 "2015-01-15 23:24:25.000000" STRING 14625 "23:24:25.000000" STRING 14726 "2015-01-15" STRING 14827 "2015-01-15 23:24:25.000000" STRING 14928 {"type": "Point", "coordinates": [1.0, 1.0]} OBJECT 15029 [] ARRAY 15130 "base64:type15:yv66vg==" STRING 152# Convert TEXT values back to JSON 153CREATE TABLE t4 (i INT PRIMARY KEY, j JSON); 154INSERT INTO t4 SELECT * FROM t3; 155SELECT *, JSON_TYPE(j) FROM t4 ORDER BY i; 156i j JSON_TYPE(j) 1570 NULL NULL 1581 {"a": 2} OBJECT 1592 [1, 2] ARRAY 1603 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} OBJECT 1614 ["here", ["I", "am"], "!!!"] ARRAY 1625 "scalar string" STRING 1636 true BOOLEAN 1647 false BOOLEAN 1658 null NULL 1669 -1 INTEGER 16710 1 INTEGER 16811 32767 INTEGER 16912 32768 INTEGER 17013 -32768 INTEGER 17114 -32769 INTEGER 17215 2147483647 INTEGER 17316 2147483648 INTEGER 17417 -2147483648 INTEGER 17518 -2147483649 INTEGER 17619 18446744073709551615 UNSIGNED INTEGER 17720 1.8446744073709552e19 DOUBLE 17821 3.14 DOUBLE 17922 {} OBJECT 18023 [] ARRAY 18124 "2015-01-15 23:24:25.000000" STRING 18225 "23:24:25.000000" STRING 18326 "2015-01-15" STRING 18427 "2015-01-15 23:24:25.000000" STRING 18528 {"type": "Point", "coordinates": [1.0, 1.0]} OBJECT 18629 [] ARRAY 18730 "base64:type15:yv66vg==" STRING 188# 189# WL#8249 JSON comparator 190# 191CREATE TABLE t5 (id INT PRIMARY KEY AUTO_INCREMENT, j JSON, x INT); 192INSERT INTO t5(j) VALUES (NULL), (CAST(0 AS JSON)), (CAST(1 AS JSON)), 193(CAST(2 AS JSON)), (CAST(3 AS JSON)), (CAST(3.14 AS JSON)), ('3.14'), 194('"0"'), ('"1"'), ('"2"'), ('"3"'), ('true'), ('false'), ('"true"'), 195('"false"'), ('null'), ('"null"'), (JSON_ARRAY(1, 2, 3)), ('"[1, 2, 3]"'), 196(JSON_ARRAY(1.0e0, 2.0e0, 3.0e0)), (JSON_ARRAY(1, 2, 3.1)), 197(JSON_OBJECT()), (JSON_OBJECT('a', 'b')), (JSON_OBJECT('a', 'c')); 198SELECT * FROM t5 ORDER BY id; 199id j x 2001 NULL NULL 2012 0 NULL 2023 1 NULL 2034 2 NULL 2045 3 NULL 2056 3.14 NULL 2067 3.14 NULL 2078 "0" NULL 2089 "1" NULL 20910 "2" NULL 21011 "3" NULL 21112 true NULL 21213 false NULL 21314 "true" NULL 21415 "false" NULL 21516 null NULL 21617 "null" NULL 21718 [1, 2, 3] NULL 21819 "[1, 2, 3]" NULL 21920 [1.0, 2.0, 3.0] NULL 22021 [1, 2, 3.1] NULL 22122 {} NULL 22223 {"a": "b"} NULL 22324 {"a": "c"} NULL 224# Expect a single row to be updated (integer 0) 225UPDATE t5 SET x = 1 WHERE j = 0; 226SELECT * FROM t5 WHERE x = 1 ORDER BY id; 227id j x 2282 0 1 229# Expect a single row to be updated (string "0") 230UPDATE t5 SET x = 2 WHERE j = "0"; 231SELECT * FROM t5 WHERE x = 2 ORDER BY id; 232id j x 2338 "0" 2 234# Expect a single row to be updated (boolean true) 235UPDATE t5 SET x = 3 WHERE j = true; 236SELECT * FROM t5 WHERE x = 3 ORDER BY id; 237id j x 23812 true 3 239# Expect a single row to be updated (boolean false) 240UPDATE t5 SET x = 4 WHERE j = false; 241SELECT * FROM t5 WHERE x = 4 ORDER BY id; 242id j x 24313 false 4 244# Expect a single row to be updated (string "true") 245UPDATE t5 SET x = 5 WHERE j = 'true'; 246SELECT * FROM t5 WHERE x = 5 ORDER BY id; 247id j x 24814 "true" 5 249# Expect a single row to be updated (string "false") 250UPDATE t5 SET x = 6 WHERE j = 'false'; 251SELECT * FROM t5 WHERE x = 6 ORDER BY id; 252id j x 25315 "false" 6 254# Expect a single row to be updated (string "[1, 2, 3]") 255UPDATE t5 SET x = 7 WHERE j = '[1, 2, 3]'; 256SELECT * FROM t5 WHERE x = 7 ORDER BY id; 257id j x 25819 "[1, 2, 3]" 7 259# Expect two rows to be updated (array [1, 2, 3]) 260UPDATE t5 SET x = 8 WHERE j = JSON_ARRAY(1, 2, 3); 261SELECT * FROM t5 WHERE x = 8 ORDER BY id; 262id j x 26318 [1, 2, 3] 8 26420 [1.0, 2.0, 3.0] 8 265# Expect two rows to be updated (number 3.14) 266UPDATE t5 SET x = 9 WHERE j = 3.14; 267SELECT * FROM t5 WHERE x = 9 ORDER BY id; 268id j x 2696 3.14 9 2707 3.14 9 271# Expect no rows to be updated (string "3.14") 272UPDATE t5 SET x = 10 WHERE j = '3.14'; 273SELECT * FROM t5 WHERE x = 10 ORDER BY id; 274id j x 275# Expect a single row to be updated (object {"a":"b"}) 276UPDATE t5 SET x = 11 WHERE j = CAST('{"a":"b"}' AS JSON); 277SELECT * FROM t5 WHERE x = 11 ORDER BY id; 278id j x 27923 {"a": "b"} 11 280# Expect four rows to be updated 281UPDATE t5 SET x = 12 WHERE JSON_EXTRACT(j, '$[0]') = 1; 282SELECT * FROM t5 WHERE x = 12 ORDER BY id; 283id j x 2843 1 12 28518 [1, 2, 3] 12 28620 [1.0, 2.0, 3.0] 12 28721 [1, 2, 3.1] 12 288# Expect a single row to be updated 289UPDATE t5 SET x = 13 WHERE JSON_EXTRACT(j, '$[0]') = '1'; 290SELECT * FROM t5 WHERE x = 13 ORDER BY id; 291id j x 2929 "1" 13 293# 294# WL#8539 Ordering of scalar JSON values 295# 296DELETE FROM t2; 297INSERT INTO t2 SELECT i, j FROM t1 ORDER BY j, i; 298Warnings: 299Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values' 300INSERT INTO t2 SELECT i + 100, j FROM t1 ORDER BY j DESC, i; 301Warnings: 302Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values' 303INSERT INTO t2 SELECT id + 200, j FROM t5 ORDER BY j, id; 304Warnings: 305Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values' 306INSERT INTO t2 SELECT id + 300, j FROM t5 ORDER BY j DESC, id; 307Warnings: 308Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values' 309SELECT * FROM t2 ORDER BY i; 310i j 3110 NULL 3121 {"a": 2} 3132 [1, 2] 3143 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 3154 ["here", ["I", "am"], "!!!"] 3165 "scalar string" 3176 true 3187 false 3198 null 3209 -1 32110 1 32211 32767 32312 32768 32413 -32768 32514 -32769 32615 2147483647 32716 2147483648 32817 -2147483648 32918 -2147483649 33019 18446744073709551615 33120 1.8446744073709552e19 33221 3.14 33322 {} 33423 [] 33524 "2015-01-15 23:24:25.000000" 33625 "23:24:25.000000" 33726 "2015-01-15" 33827 "2015-01-15 23:24:25.000000" 33928 {"type": "Point", "coordinates": [1.0, 1.0]} 34029 [] 34130 "base64:type15:yv66vg==" 342100 NULL 343101 {"a": 2} 344102 [1, 2] 345103 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 346104 ["here", ["I", "am"], "!!!"] 347105 "scalar string" 348106 true 349107 false 350108 null 351109 -1 352110 1 353111 32767 354112 32768 355113 -32768 356114 -32769 357115 2147483647 358116 2147483648 359117 -2147483648 360118 -2147483649 361119 18446744073709551615 362120 1.8446744073709552e19 363121 3.14 364122 {} 365123 [] 366124 "2015-01-15 23:24:25.000000" 367125 "23:24:25.000000" 368126 "2015-01-15" 369127 "2015-01-15 23:24:25.000000" 370128 {"type": "Point", "coordinates": [1.0, 1.0]} 371129 [] 372130 "base64:type15:yv66vg==" 373201 NULL 374202 0 375203 1 376204 2 377205 3 378206 3.14 379207 3.14 380208 "0" 381209 "1" 382210 "2" 383211 "3" 384212 true 385213 false 386214 "true" 387215 "false" 388216 null 389217 "null" 390218 [1, 2, 3] 391219 "[1, 2, 3]" 392220 [1.0, 2.0, 3.0] 393221 [1, 2, 3.1] 394222 {} 395223 {"a": "b"} 396224 {"a": "c"} 397301 NULL 398302 0 399303 1 400304 2 401305 3 402306 3.14 403307 3.14 404308 "0" 405309 "1" 406310 "2" 407311 "3" 408312 true 409313 false 410314 "true" 411315 "false" 412316 null 413317 "null" 414318 [1, 2, 3] 415319 "[1, 2, 3]" 416320 [1.0, 2.0, 3.0] 417321 [1, 2, 3.1] 418322 {} 419323 {"a": "b"} 420324 {"a": "c"} 421# 422# WL#7909 Server side JSON functions 423# 424ALTER TABLE t1 ADD COLUMN j2 JSON; 425UPDATE t1 SET j2 = JSON_ARRAY(JSON_TYPE(j)); 426SELECT * FROM t1 ORDER BY i; 427i j j2 4280 NULL [null] 4291 {"a": 2} ["OBJECT"] 4302 [1, 2] ["ARRAY"] 4313 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} ["OBJECT"] 4324 ["here", ["I", "am"], "!!!"] ["ARRAY"] 4335 "scalar string" ["STRING"] 4346 true ["BOOLEAN"] 4357 false ["BOOLEAN"] 4368 null ["NULL"] 4379 -1 ["INTEGER"] 43810 1 ["UNSIGNED INTEGER"] 43911 32767 ["INTEGER"] 44012 32768 ["INTEGER"] 44113 -32768 ["INTEGER"] 44214 -32769 ["INTEGER"] 44315 2147483647 ["INTEGER"] 44416 2147483648 ["INTEGER"] 44517 -2147483648 ["INTEGER"] 44618 -2147483649 ["INTEGER"] 44719 18446744073709551615 ["UNSIGNED INTEGER"] 44820 1.8446744073709552e19 ["DOUBLE"] 44921 3.14 ["DOUBLE"] 45022 {} ["OBJECT"] 45123 [] ["ARRAY"] 45224 "2015-01-15 23:24:25.000000" ["DATETIME"] 45325 "23:24:25.000000" ["TIME"] 45426 "2015-01-15" ["DATE"] 45527 "2015-01-15 23:24:25.000000" ["DATETIME"] 45628 {"type": "Point", "coordinates": [1.0, 1.0]} ["OBJECT"] 45729 [] ["ARRAY"] 45830 "base64:type15:yv66vg==" ["BLOB"] 459UPDATE t1 SET j2 = CAST(JSON_VALID(j) AS JSON); 460SELECT * FROM t1 ORDER BY i; 461i j j2 4620 NULL NULL 4631 {"a": 2} true 4642 [1, 2] true 4653 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} true 4664 ["here", ["I", "am"], "!!!"] true 4675 "scalar string" true 4686 true true 4697 false true 4708 null true 4719 -1 true 47210 1 true 47311 32767 true 47412 32768 true 47513 -32768 true 47614 -32769 true 47715 2147483647 true 47816 2147483648 true 47917 -2147483648 true 48018 -2147483649 true 48119 18446744073709551615 true 48220 1.8446744073709552e19 true 48321 3.14 true 48422 {} true 48523 [] true 48624 "2015-01-15 23:24:25.000000" true 48725 "23:24:25.000000" true 48826 "2015-01-15" true 48927 "2015-01-15 23:24:25.000000" true 49028 {"type": "Point", "coordinates": [1.0, 1.0]} true 49129 [] true 49230 "base64:type15:yv66vg==" true 493UPDATE t1 SET j2 = JSON_KEYS(j); 494SELECT * FROM t1 ORDER BY i; 495i j j2 4960 NULL NULL 4971 {"a": 2} ["a"] 4982 [1, 2] NULL 4993 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} ["a", "c", "ab", "bc"] 5004 ["here", ["I", "am"], "!!!"] NULL 5015 "scalar string" NULL 5026 true NULL 5037 false NULL 5048 null NULL 5059 -1 NULL 50610 1 NULL 50711 32767 NULL 50812 32768 NULL 50913 -32768 NULL 51014 -32769 NULL 51115 2147483647 NULL 51216 2147483648 NULL 51317 -2147483648 NULL 51418 -2147483649 NULL 51519 18446744073709551615 NULL 51620 1.8446744073709552e19 NULL 51721 3.14 NULL 51822 {} [] 51923 [] NULL 52024 "2015-01-15 23:24:25.000000" NULL 52125 "23:24:25.000000" NULL 52226 "2015-01-15" NULL 52327 "2015-01-15 23:24:25.000000" NULL 52428 {"type": "Point", "coordinates": [1.0, 1.0]} ["type", "coordinates"] 52529 [] NULL 52630 "base64:type15:yv66vg==" NULL 527UPDATE t1 SET j2 = JSON_ARRAY(j,j,j); 528SELECT * FROM t1 ORDER BY i; 529i j j2 5300 NULL [null, null, null] 5311 {"a": 2} [{"a": 2}, {"a": 2}, {"a": 2}] 5322 [1, 2] [[1, 2], [1, 2], [1, 2]] 5333 {"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"]}] 5344 ["here", ["I", "am"], "!!!"] [["here", ["I", "am"], "!!!"], ["here", ["I", "am"], "!!!"], ["here", ["I", "am"], "!!!"]] 5355 "scalar string" ["scalar string", "scalar string", "scalar string"] 5366 true [true, true, true] 5377 false [false, false, false] 5388 null [null, null, null] 5399 -1 [-1, -1, -1] 54010 1 [1, 1, 1] 54111 32767 [32767, 32767, 32767] 54212 32768 [32768, 32768, 32768] 54313 -32768 [-32768, -32768, -32768] 54414 -32769 [-32769, -32769, -32769] 54515 2147483647 [2147483647, 2147483647, 2147483647] 54616 2147483648 [2147483648, 2147483648, 2147483648] 54717 -2147483648 [-2147483648, -2147483648, -2147483648] 54818 -2147483649 [-2147483649, -2147483649, -2147483649] 54919 18446744073709551615 [18446744073709551615, 18446744073709551615, 18446744073709551615] 55020 1.8446744073709552e19 [1.8446744073709552e19, 1.8446744073709552e19, 1.8446744073709552e19] 55121 3.14 [3.14, 3.14, 3.14] 55222 {} [{}, {}, {}] 55323 [] [[], [], []] 55424 "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"] 55525 "23:24:25.000000" ["23:24:25.000000", "23:24:25.000000", "23:24:25.000000"] 55626 "2015-01-15" ["2015-01-15", "2015-01-15", "2015-01-15"] 55727 "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"] 55828 {"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]}] 55929 [] [[], [], []] 56030 "base64:type15:yv66vg==" ["base64:type15:yv66vg==", "base64:type15:yv66vg==", "base64:type15:yv66vg=="] 561UPDATE t1 SET j2 = JSON_EXTRACT(j2, '$[1]'); 562SELECT * FROM t1 ORDER BY i; 563i j j2 5640 NULL null 5651 {"a": 2} {"a": 2} 5662 [1, 2] [1, 2] 5673 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 5684 ["here", ["I", "am"], "!!!"] ["here", ["I", "am"], "!!!"] 5695 "scalar string" "scalar string" 5706 true true 5717 false false 5728 null null 5739 -1 -1 57410 1 1 57511 32767 32767 57612 32768 32768 57713 -32768 -32768 57814 -32769 -32769 57915 2147483647 2147483647 58016 2147483648 2147483648 58117 -2147483648 -2147483648 58218 -2147483649 -2147483649 58319 18446744073709551615 18446744073709551615 58420 1.8446744073709552e19 1.8446744073709552e19 58521 3.14 3.14 58622 {} {} 58723 [] [] 58824 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000" 58925 "23:24:25.000000" "23:24:25.000000" 59026 "2015-01-15" "2015-01-15" 59127 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000" 59228 {"type": "Point", "coordinates": [1.0, 1.0]} {"type": "Point", "coordinates": [1.0, 1.0]} 59329 [] [] 59430 "base64:type15:yv66vg==" "base64:type15:yv66vg==" 595UPDATE t1 SET j2 = JSON_REMOVE(j, '$[1]'); 596SELECT * FROM t1 ORDER BY i; 597i j j2 5980 NULL NULL 5991 {"a": 2} {"a": 2} 6002 [1, 2] [1] 6013 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 6024 ["here", ["I", "am"], "!!!"] ["here", "!!!"] 6035 "scalar string" "scalar string" 6046 true true 6057 false false 6068 null null 6079 -1 -1 60810 1 1 60911 32767 32767 61012 32768 32768 61113 -32768 -32768 61214 -32769 -32769 61315 2147483647 2147483647 61416 2147483648 2147483648 61517 -2147483648 -2147483648 61618 -2147483649 -2147483649 61719 18446744073709551615 18446744073709551615 61820 1.8446744073709552e19 1.8446744073709552e19 61921 3.14 3.14 62022 {} {} 62123 [] [] 62224 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000" 62325 "23:24:25.000000" "23:24:25.000000" 62426 "2015-01-15" "2015-01-15" 62527 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000" 62628 {"type": "Point", "coordinates": [1.0, 1.0]} {"type": "Point", "coordinates": [1.0, 1.0]} 62729 [] [] 62830 "base64:type15:yv66vg==" "base64:type15:yv66vg==" 629UPDATE t1 SET j2 = JSON_ARRAY_APPEND(j, '$[1]', 'abc'); 630SELECT * FROM t1 ORDER BY i; 631i j j2 6320 NULL NULL 6331 {"a": 2} {"a": 2} 6342 [1, 2] [1, [2, "abc"]] 6353 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 6364 ["here", ["I", "am"], "!!!"] ["here", ["I", "am", "abc"], "!!!"] 6375 "scalar string" "scalar string" 6386 true true 6397 false false 6408 null null 6419 -1 -1 64210 1 1 64311 32767 32767 64412 32768 32768 64513 -32768 -32768 64614 -32769 -32769 64715 2147483647 2147483647 64816 2147483648 2147483648 64917 -2147483648 -2147483648 65018 -2147483649 -2147483649 65119 18446744073709551615 18446744073709551615 65220 1.8446744073709552e19 1.8446744073709552e19 65321 3.14 3.14 65422 {} {} 65523 [] [] 65624 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000" 65725 "23:24:25.000000" "23:24:25.000000" 65826 "2015-01-15" "2015-01-15" 65927 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000" 66028 {"type": "Point", "coordinates": [1.0, 1.0]} {"type": "Point", "coordinates": [1.0, 1.0]} 66129 [] [] 66230 "base64:type15:yv66vg==" "base64:type15:yv66vg==" 663UPDATE t1 SET j2 = JSON_SET(j, '$[1]', 'abc'); 664SELECT * FROM t1 ORDER BY i; 665i j j2 6660 NULL NULL 6671 {"a": 2} [{"a": 2}, "abc"] 6682 [1, 2] [1, "abc"] 6693 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} [{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}, "abc"] 6704 ["here", ["I", "am"], "!!!"] ["here", "abc", "!!!"] 6715 "scalar string" ["scalar string", "abc"] 6726 true [true, "abc"] 6737 false [false, "abc"] 6748 null [null, "abc"] 6759 -1 [-1, "abc"] 67610 1 [1, "abc"] 67711 32767 [32767, "abc"] 67812 32768 [32768, "abc"] 67913 -32768 [-32768, "abc"] 68014 -32769 [-32769, "abc"] 68115 2147483647 [2147483647, "abc"] 68216 2147483648 [2147483648, "abc"] 68317 -2147483648 [-2147483648, "abc"] 68418 -2147483649 [-2147483649, "abc"] 68519 18446744073709551615 [18446744073709551615, "abc"] 68620 1.8446744073709552e19 [1.8446744073709552e19, "abc"] 68721 3.14 [3.14, "abc"] 68822 {} [{}, "abc"] 68923 [] ["abc"] 69024 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000", "abc"] 69125 "23:24:25.000000" ["23:24:25.000000", "abc"] 69226 "2015-01-15" ["2015-01-15", "abc"] 69327 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000", "abc"] 69428 {"type": "Point", "coordinates": [1.0, 1.0]} [{"type": "Point", "coordinates": [1.0, 1.0]}, "abc"] 69529 [] ["abc"] 69630 "base64:type15:yv66vg==" ["base64:type15:yv66vg==", "abc"] 697UPDATE t1 SET j2 = JSON_INSERT(j, '$[1]', 'abc'); 698SELECT * FROM t1 ORDER BY i; 699i j j2 7000 NULL NULL 7011 {"a": 2} [{"a": 2}, "abc"] 7022 [1, 2] [1, 2] 7033 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} [{"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}, "abc"] 7044 ["here", ["I", "am"], "!!!"] ["here", ["I", "am"], "!!!"] 7055 "scalar string" ["scalar string", "abc"] 7066 true [true, "abc"] 7077 false [false, "abc"] 7088 null [null, "abc"] 7099 -1 [-1, "abc"] 71010 1 [1, "abc"] 71111 32767 [32767, "abc"] 71212 32768 [32768, "abc"] 71313 -32768 [-32768, "abc"] 71414 -32769 [-32769, "abc"] 71515 2147483647 [2147483647, "abc"] 71616 2147483648 [2147483648, "abc"] 71717 -2147483648 [-2147483648, "abc"] 71818 -2147483649 [-2147483649, "abc"] 71919 18446744073709551615 [18446744073709551615, "abc"] 72020 1.8446744073709552e19 [1.8446744073709552e19, "abc"] 72121 3.14 [3.14, "abc"] 72222 {} [{}, "abc"] 72323 [] ["abc"] 72424 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000", "abc"] 72525 "23:24:25.000000" ["23:24:25.000000", "abc"] 72626 "2015-01-15" ["2015-01-15", "abc"] 72727 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000", "abc"] 72828 {"type": "Point", "coordinates": [1.0, 1.0]} [{"type": "Point", "coordinates": [1.0, 1.0]}, "abc"] 72929 [] ["abc"] 73030 "base64:type15:yv66vg==" ["base64:type15:yv66vg==", "abc"] 731UPDATE t1 SET j2 = JSON_REPLACE(j, '$[1]', 'abc'); 732SELECT * FROM t1 ORDER BY i; 733i j j2 7340 NULL NULL 7351 {"a": 2} {"a": 2} 7362 [1, 2] [1, "abc"] 7373 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 7384 ["here", ["I", "am"], "!!!"] ["here", "abc", "!!!"] 7395 "scalar string" "scalar string" 7406 true true 7417 false false 7428 null null 7439 -1 -1 74410 1 1 74511 32767 32767 74612 32768 32768 74713 -32768 -32768 74814 -32769 -32769 74915 2147483647 2147483647 75016 2147483648 2147483648 75117 -2147483648 -2147483648 75218 -2147483649 -2147483649 75319 18446744073709551615 18446744073709551615 75420 1.8446744073709552e19 1.8446744073709552e19 75521 3.14 3.14 75622 {} {} 75723 [] [] 75824 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000" 75925 "23:24:25.000000" "23:24:25.000000" 76026 "2015-01-15" "2015-01-15" 76127 "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000" 76228 {"type": "Point", "coordinates": [1.0, 1.0]} {"type": "Point", "coordinates": [1.0, 1.0]} 76329 [] [] 76430 "base64:type15:yv66vg==" "base64:type15:yv66vg==" 765UPDATE t1 SET j2 = JSON_MERGE_PATCH(j, '{"a": "patched", "c": null}'); 766SELECT * FROM t1 ORDER BY i; 767i j j2 7680 NULL NULL 7691 {"a": 2} {"a": "patched"} 7702 [1, 2] {"a": "patched"} 7713 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} {"a": "patched", "ab": "abc", "bc": ["x", "y"]} 7724 ["here", ["I", "am"], "!!!"] {"a": "patched"} 7735 "scalar string" {"a": "patched"} 7746 true {"a": "patched"} 7757 false {"a": "patched"} 7768 null {"a": "patched"} 7779 -1 {"a": "patched"} 77810 1 {"a": "patched"} 77911 32767 {"a": "patched"} 78012 32768 {"a": "patched"} 78113 -32768 {"a": "patched"} 78214 -32769 {"a": "patched"} 78315 2147483647 {"a": "patched"} 78416 2147483648 {"a": "patched"} 78517 -2147483648 {"a": "patched"} 78618 -2147483649 {"a": "patched"} 78719 18446744073709551615 {"a": "patched"} 78820 1.8446744073709552e19 {"a": "patched"} 78921 3.14 {"a": "patched"} 79022 {} {"a": "patched"} 79123 [] {"a": "patched"} 79224 "2015-01-15 23:24:25.000000" {"a": "patched"} 79325 "23:24:25.000000" {"a": "patched"} 79426 "2015-01-15" {"a": "patched"} 79527 "2015-01-15 23:24:25.000000" {"a": "patched"} 79628 {"type": "Point", "coordinates": [1.0, 1.0]} {"a": "patched", "type": "Point", "coordinates": [1.0, 1.0]} 79729 [] {"a": "patched"} 79830 "base64:type15:yv66vg==" {"a": "patched"} 799UPDATE t1 SET j2 = JSON_MERGE_PRESERVE(j, j); 800SELECT * FROM t1 ORDER BY i; 801i j j2 8020 NULL NULL 8031 {"a": 2} {"a": [2, 2]} 8042 [1, 2] [1, 2, 1, 2] 8053 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} {"a": ["b", "b"], "c": ["d", "d"], "ab": ["abc", "abc"], "bc": ["x", "y", "x", "y"]} 8064 ["here", ["I", "am"], "!!!"] ["here", ["I", "am"], "!!!", "here", ["I", "am"], "!!!"] 8075 "scalar string" ["scalar string", "scalar string"] 8086 true [true, true] 8097 false [false, false] 8108 null [null, null] 8119 -1 [-1, -1] 81210 1 [1, 1] 81311 32767 [32767, 32767] 81412 32768 [32768, 32768] 81513 -32768 [-32768, -32768] 81614 -32769 [-32769, -32769] 81715 2147483647 [2147483647, 2147483647] 81816 2147483648 [2147483648, 2147483648] 81917 -2147483648 [-2147483648, -2147483648] 82018 -2147483649 [-2147483649, -2147483649] 82119 18446744073709551615 [18446744073709551615, 18446744073709551615] 82220 1.8446744073709552e19 [1.8446744073709552e19, 1.8446744073709552e19] 82321 3.14 [3.14, 3.14] 82422 {} {} 82523 [] [] 82624 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000", "2015-01-15 23:24:25.000000"] 82725 "23:24:25.000000" ["23:24:25.000000", "23:24:25.000000"] 82826 "2015-01-15" ["2015-01-15", "2015-01-15"] 82927 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000", "2015-01-15 23:24:25.000000"] 83028 {"type": "Point", "coordinates": [1.0, 1.0]} {"type": ["Point", "Point"], "coordinates": [1.0, 1.0, 1.0, 1.0]} 83129 [] [] 83230 "base64:type15:yv66vg==" ["base64:type15:yv66vg==", "base64:type15:yv66vg=="] 833UPDATE t1 SET j2 = JSON_SEARCH(j, 'one', 'abc'); 834SELECT * FROM t1 ORDER BY i; 835i j j2 8360 NULL NULL 8371 {"a": 2} NULL 8382 [1, 2] NULL 8393 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} "$.ab" 8404 ["here", ["I", "am"], "!!!"] NULL 8415 "scalar string" NULL 8426 true NULL 8437 false NULL 8448 null NULL 8459 -1 NULL 84610 1 NULL 84711 32767 NULL 84812 32768 NULL 84913 -32768 NULL 85014 -32769 NULL 85115 2147483647 NULL 85216 2147483648 NULL 85317 -2147483648 NULL 85418 -2147483649 NULL 85519 18446744073709551615 NULL 85620 1.8446744073709552e19 NULL 85721 3.14 NULL 85822 {} NULL 85923 [] NULL 86024 "2015-01-15 23:24:25.000000" NULL 86125 "23:24:25.000000" NULL 86226 "2015-01-15" NULL 86327 "2015-01-15 23:24:25.000000" NULL 86428 {"type": "Point", "coordinates": [1.0, 1.0]} NULL 86529 [] NULL 86630 "base64:type15:yv66vg==" NULL 867UPDATE t1 SET j2 = CAST(JSON_CONTAINS(j, '[1]') AS JSON); 868SELECT * FROM t1 ORDER BY i; 869i j j2 8700 NULL NULL 8711 {"a": 2} false 8722 [1, 2] true 8733 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} false 8744 ["here", ["I", "am"], "!!!"] false 8755 "scalar string" false 8766 true false 8777 false false 8788 null false 8799 -1 false 88010 1 false 88111 32767 false 88212 32768 false 88313 -32768 false 88414 -32769 false 88515 2147483647 false 88616 2147483648 false 88717 -2147483648 false 88818 -2147483649 false 88919 18446744073709551615 false 89020 1.8446744073709552e19 false 89121 3.14 false 89222 {} false 89323 [] false 89424 "2015-01-15 23:24:25.000000" false 89525 "23:24:25.000000" false 89626 "2015-01-15" false 89727 "2015-01-15 23:24:25.000000" false 89828 {"type": "Point", "coordinates": [1.0, 1.0]} false 89929 [] false 90030 "base64:type15:yv66vg==" false 901UPDATE t1 SET j2 = CAST(JSON_CONTAINS_PATH(j, 'one', '$.a') AS JSON); 902SELECT * FROM t1 ORDER BY i; 903i j j2 9040 NULL NULL 9051 {"a": 2} true 9062 [1, 2] false 9073 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} true 9084 ["here", ["I", "am"], "!!!"] false 9095 "scalar string" false 9106 true false 9117 false false 9128 null false 9139 -1 false 91410 1 false 91511 32767 false 91612 32768 false 91713 -32768 false 91814 -32769 false 91915 2147483647 false 92016 2147483648 false 92117 -2147483648 false 92218 -2147483649 false 92319 18446744073709551615 false 92420 1.8446744073709552e19 false 92521 3.14 false 92622 {} false 92723 [] false 92824 "2015-01-15 23:24:25.000000" false 92925 "23:24:25.000000" false 93026 "2015-01-15" false 93127 "2015-01-15 23:24:25.000000" false 93228 {"type": "Point", "coordinates": [1.0, 1.0]} false 93329 [] false 93430 "base64:type15:yv66vg==" false 935UPDATE t1 SET j2 = CAST(JSON_LENGTH(j) AS JSON); 936SELECT * FROM t1 ORDER BY i; 937i j j2 9380 NULL NULL 9391 {"a": 2} 1 9402 [1, 2] 2 9413 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 4 9424 ["here", ["I", "am"], "!!!"] 3 9435 "scalar string" 1 9446 true 1 9457 false 1 9468 null 1 9479 -1 1 94810 1 1 94911 32767 1 95012 32768 1 95113 -32768 1 95214 -32769 1 95315 2147483647 1 95416 2147483648 1 95517 -2147483648 1 95618 -2147483649 1 95719 18446744073709551615 1 95820 1.8446744073709552e19 1 95921 3.14 1 96022 {} 0 96123 [] 0 96224 "2015-01-15 23:24:25.000000" 1 96325 "23:24:25.000000" 1 96426 "2015-01-15" 1 96527 "2015-01-15 23:24:25.000000" 1 96628 {"type": "Point", "coordinates": [1.0, 1.0]} 2 96729 [] 0 96830 "base64:type15:yv66vg==" 1 969UPDATE t1 SET j2 = CAST(JSON_DEPTH(j) AS JSON); 970SELECT * FROM t1 ORDER BY i; 971i j j2 9720 NULL NULL 9731 {"a": 2} 2 9742 [1, 2] 2 9753 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 3 9764 ["here", ["I", "am"], "!!!"] 3 9775 "scalar string" 1 9786 true 1 9797 false 1 9808 null 1 9819 -1 1 98210 1 1 98311 32767 1 98412 32768 1 98513 -32768 1 98614 -32769 1 98715 2147483647 1 98816 2147483648 1 98917 -2147483648 1 99018 -2147483649 1 99119 18446744073709551615 1 99220 1.8446744073709552e19 1 99321 3.14 1 99422 {} 1 99523 [] 1 99624 "2015-01-15 23:24:25.000000" 1 99725 "23:24:25.000000" 1 99826 "2015-01-15" 1 99927 "2015-01-15 23:24:25.000000" 1 100028 {"type": "Point", "coordinates": [1.0, 1.0]} 3 100129 [] 1 100230 "base64:type15:yv66vg==" 1 1003UPDATE t1 SET j2 = JSON_OBJECT('a', j, 'b', j); 1004SELECT * FROM t1 ORDER BY i; 1005i j j2 10060 NULL {"a": null, "b": null} 10071 {"a": 2} {"a": {"a": 2}, "b": {"a": 2}} 10082 [1, 2] {"a": [1, 2], "b": [1, 2]} 10093 {"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"]}} 10104 ["here", ["I", "am"], "!!!"] {"a": ["here", ["I", "am"], "!!!"], "b": ["here", ["I", "am"], "!!!"]} 10115 "scalar string" {"a": "scalar string", "b": "scalar string"} 10126 true {"a": true, "b": true} 10137 false {"a": false, "b": false} 10148 null {"a": null, "b": null} 10159 -1 {"a": -1, "b": -1} 101610 1 {"a": 1, "b": 1} 101711 32767 {"a": 32767, "b": 32767} 101812 32768 {"a": 32768, "b": 32768} 101913 -32768 {"a": -32768, "b": -32768} 102014 -32769 {"a": -32769, "b": -32769} 102115 2147483647 {"a": 2147483647, "b": 2147483647} 102216 2147483648 {"a": 2147483648, "b": 2147483648} 102317 -2147483648 {"a": -2147483648, "b": -2147483648} 102418 -2147483649 {"a": -2147483649, "b": -2147483649} 102519 18446744073709551615 {"a": 18446744073709551615, "b": 18446744073709551615} 102620 1.8446744073709552e19 {"a": 1.8446744073709552e19, "b": 1.8446744073709552e19} 102721 3.14 {"a": 3.14, "b": 3.14} 102822 {} {"a": {}, "b": {}} 102923 [] {"a": [], "b": []} 103024 "2015-01-15 23:24:25.000000" {"a": "2015-01-15 23:24:25.000000", "b": "2015-01-15 23:24:25.000000"} 103125 "23:24:25.000000" {"a": "23:24:25.000000", "b": "23:24:25.000000"} 103226 "2015-01-15" {"a": "2015-01-15", "b": "2015-01-15"} 103327 "2015-01-15 23:24:25.000000" {"a": "2015-01-15 23:24:25.000000", "b": "2015-01-15 23:24:25.000000"} 103428 {"type": "Point", "coordinates": [1.0, 1.0]} {"a": {"type": "Point", "coordinates": [1.0, 1.0]}, "b": {"type": "Point", "coordinates": [1.0, 1.0]}} 103529 [] {"a": [], "b": []} 103630 "base64:type15:yv66vg==" {"a": "base64:type15:yv66vg==", "b": "base64:type15:yv66vg=="} 1037UPDATE t1 SET j2 = JSON_ARRAY(JSON_UNQUOTE(j)); 1038SELECT * FROM t1 ORDER BY i; 1039i j j2 10400 NULL [null] 10411 {"a": 2} ["{\"a\": 2}"] 10422 [1, 2] ["[1, 2]"] 10433 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} ["{\"a\": \"b\", \"c\": \"d\", \"ab\": \"abc\", \"bc\": [\"x\", \"y\"]}"] 10444 ["here", ["I", "am"], "!!!"] ["[\"here\", [\"I\", \"am\"], \"!!!\"]"] 10455 "scalar string" ["scalar string"] 10466 true ["true"] 10477 false ["false"] 10488 null ["null"] 10499 -1 ["-1"] 105010 1 ["1"] 105111 32767 ["32767"] 105212 32768 ["32768"] 105313 -32768 ["-32768"] 105414 -32769 ["-32769"] 105515 2147483647 ["2147483647"] 105616 2147483648 ["2147483648"] 105717 -2147483648 ["-2147483648"] 105818 -2147483649 ["-2147483649"] 105919 18446744073709551615 ["18446744073709551615"] 106020 1.8446744073709552e19 ["1.8446744073709552e19"] 106121 3.14 ["3.14"] 106222 {} ["{}"] 106323 [] ["[]"] 106424 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000"] 106525 "23:24:25.000000" ["23:24:25.000000"] 106626 "2015-01-15" ["2015-01-15"] 106727 "2015-01-15 23:24:25.000000" ["2015-01-15 23:24:25.000000"] 106828 {"type": "Point", "coordinates": [1.0, 1.0]} ["{\"type\": \"Point\", \"coordinates\": [1.0, 1.0]}"] 106929 [] ["[]"] 107030 "base64:type15:yv66vg==" ["base64:type15:yv66vg=="] 1071UPDATE t1 SET j2 = CAST(JSON_QUOTE(CAST(j AS CHAR)) AS JSON); 1072SELECT * FROM t1 ORDER BY i; 1073i j j2 10740 NULL NULL 10751 {"a": 2} "{\"a\": 2}" 10762 [1, 2] "[1, 2]" 10773 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} "{\"a\": \"b\", \"c\": \"d\", \"ab\": \"abc\", \"bc\": [\"x\", \"y\"]}" 10784 ["here", ["I", "am"], "!!!"] "[\"here\", [\"I\", \"am\"], \"!!!\"]" 10795 "scalar string" "\"scalar string\"" 10806 true "true" 10817 false "false" 10828 null "null" 10839 -1 "-1" 108410 1 "1" 108511 32767 "32767" 108612 32768 "32768" 108713 -32768 "-32768" 108814 -32769 "-32769" 108915 2147483647 "2147483647" 109016 2147483648 "2147483648" 109117 -2147483648 "-2147483648" 109218 -2147483649 "-2147483649" 109319 18446744073709551615 "18446744073709551615" 109420 1.8446744073709552e19 "1.8446744073709552e19" 109521 3.14 "3.14" 109622 {} "{}" 109723 [] "[]" 109824 "2015-01-15 23:24:25.000000" "\"2015-01-15 23:24:25.000000\"" 109925 "23:24:25.000000" "\"23:24:25.000000\"" 110026 "2015-01-15" "\"2015-01-15\"" 110127 "2015-01-15 23:24:25.000000" "\"2015-01-15 23:24:25.000000\"" 110228 {"type": "Point", "coordinates": [1.0, 1.0]} "{\"type\": \"Point\", \"coordinates\": [1.0, 1.0]}" 110329 [] "[]" 110430 "base64:type15:yv66vg==" "\"base64:type15:yv66vg==\"" 1105ALTER TABLE t1 ADD COLUMN j3 JSON AS (JSON_EXTRACT(j2, '$.abc')); 1106SELECT * FROM t1 ORDER BY i; 1107i j j2 j3 11080 NULL NULL NULL 11091 {"a": 2} "{\"a\": 2}" NULL 11102 [1, 2] "[1, 2]" NULL 11113 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} "{\"a\": \"b\", \"c\": \"d\", \"ab\": \"abc\", \"bc\": [\"x\", \"y\"]}" NULL 11124 ["here", ["I", "am"], "!!!"] "[\"here\", [\"I\", \"am\"], \"!!!\"]" NULL 11135 "scalar string" "\"scalar string\"" NULL 11146 true "true" NULL 11157 false "false" NULL 11168 null "null" NULL 11179 -1 "-1" NULL 111810 1 "1" NULL 111911 32767 "32767" NULL 112012 32768 "32768" NULL 112113 -32768 "-32768" NULL 112214 -32769 "-32769" NULL 112315 2147483647 "2147483647" NULL 112416 2147483648 "2147483648" NULL 112517 -2147483648 "-2147483648" NULL 112618 -2147483649 "-2147483649" NULL 112719 18446744073709551615 "18446744073709551615" NULL 112820 1.8446744073709552e19 "1.8446744073709552e19" NULL 112921 3.14 "3.14" NULL 113022 {} "{}" NULL 113123 [] "[]" NULL 113224 "2015-01-15 23:24:25.000000" "\"2015-01-15 23:24:25.000000\"" NULL 113325 "23:24:25.000000" "\"23:24:25.000000\"" NULL 113426 "2015-01-15" "\"2015-01-15\"" NULL 113527 "2015-01-15 23:24:25.000000" "\"2015-01-15 23:24:25.000000\"" NULL 113628 {"type": "Point", "coordinates": [1.0, 1.0]} "{\"type\": \"Point\", \"coordinates\": [1.0, 1.0]}" NULL 113729 [] "[]" NULL 113830 "base64:type15:yv66vg==" "\"base64:type15:yv66vg==\"" NULL 1139UPDATE t1 SET j2 = JSON_OBJECT('abc', j); 1140SELECT * FROM t1 ORDER BY i; 1141i j j2 j3 11420 NULL {"abc": null} null 11431 {"a": 2} {"abc": {"a": 2}} {"a": 2} 11442 [1, 2] {"abc": [1, 2]} [1, 2] 11453 {"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"]} 11464 ["here", ["I", "am"], "!!!"] {"abc": ["here", ["I", "am"], "!!!"]} ["here", ["I", "am"], "!!!"] 11475 "scalar string" {"abc": "scalar string"} "scalar string" 11486 true {"abc": true} true 11497 false {"abc": false} false 11508 null {"abc": null} null 11519 -1 {"abc": -1} -1 115210 1 {"abc": 1} 1 115311 32767 {"abc": 32767} 32767 115412 32768 {"abc": 32768} 32768 115513 -32768 {"abc": -32768} -32768 115614 -32769 {"abc": -32769} -32769 115715 2147483647 {"abc": 2147483647} 2147483647 115816 2147483648 {"abc": 2147483648} 2147483648 115917 -2147483648 {"abc": -2147483648} -2147483648 116018 -2147483649 {"abc": -2147483649} -2147483649 116119 18446744073709551615 {"abc": 18446744073709551615} 18446744073709551615 116220 1.8446744073709552e19 {"abc": 1.8446744073709552e19} 1.8446744073709552e19 116321 3.14 {"abc": 3.14} 3.14 116422 {} {"abc": {}} {} 116523 [] {"abc": []} [] 116624 "2015-01-15 23:24:25.000000" {"abc": "2015-01-15 23:24:25.000000"} "2015-01-15 23:24:25.000000" 116725 "23:24:25.000000" {"abc": "23:24:25.000000"} "23:24:25.000000" 116826 "2015-01-15" {"abc": "2015-01-15"} "2015-01-15" 116927 "2015-01-15 23:24:25.000000" {"abc": "2015-01-15 23:24:25.000000"} "2015-01-15 23:24:25.000000" 117028 {"type": "Point", "coordinates": [1.0, 1.0]} {"abc": {"type": "Point", "coordinates": [1.0, 1.0]}} {"type": "Point", "coordinates": [1.0, 1.0]} 117129 [] {"abc": []} [] 117230 "base64:type15:yv66vg==" {"abc": "base64:type15:yv66vg=="} "base64:type15:yv66vg==" 1173include/sync_slave_sql_with_master.inc 1174[Connection Slave] 1175include/diff_tables.inc [master:t5, slave:t5] 1176[Connection Master] 1177DROP TABLE t1, t2, t3, t4, t5; 1178include/sync_slave_sql_with_master.inc 1179# 1180# WL#7987: JSON AGGREGATION FUNCTIONS 1181# 1182# Create tables of various data types 1183CREATE TABLE data_table (pkey INT PRIMARY KEY AUTO_INCREMENT, 1184txt TEXT, vcol VARCHAR(20), num INT, 1185gcol INT AS (num * 2) VIRTUAL); 1186CREATE TABLE json_data (nkey INT PRIMARY KEY AUTO_INCREMENT, jcol JSON); 1187# Insert values into the tables using the aggregation functions 1188INSERT INTO data_table(txt, vcol, num) VALUES ('ailemac', 'namor', 15), 1189('nielk', 'uciov', 12), 1190('nuicarc', 'ierdna', 13), 1191('ihcseruj', 'elisav', 14), 1192('qweasdas', 'jugcvb', 16); 1193INSERT INTO json_data(jcol) SELECT JSON_OBJECTAGG(pkey, gcol) FROM data_table; 1194INSERT INTO json_data(jcol) SELECT JSON_ARRAYAGG(txt) FROM data_table; 1195INSERT INTO json_data(jcol) SELECT JSON_ARRAYAGG(gcol) FROM data_table; 1196INSERT INTO json_data(jcol) SELECT JSON_OBJECTAGG(num , vcol) FROM data_table; 1197INSERT INTO json_data(jcol) SELECT JSON_OBJECTAGG(pkey , txt) FROM data_table; 1198INSERT INTO json_data(jcol) SELECT JSON_ARRAYAGG(pkey) FROM data_table; 1199include/sync_slave_sql_with_master.inc 1200[Connection Slave] 1201# Check whether tables were created on the slave 1202SELECT * FROM json_data; 1203nkey jcol 12041 {"1": 30, "2": 24, "3": 26, "4": 28, "5": 32} 12052 ["ailemac", "nielk", "nuicarc", "ihcseruj", "qweasdas"] 12063 [30, 24, 26, 28, 32] 12074 {"12": "uciov", "13": "ierdna", "14": "elisav", "15": "namor", "16": "jugcvb"} 12085 {"1": "ailemac", "2": "nielk", "3": "nuicarc", "4": "ihcseruj", "5": "qweasdas"} 12096 [1, 2, 3, 4, 5] 1210SELECT JSON_OBJECTAGG(t1.pkey, t2.jcol) FROM data_table AS t1, json_data AS t2; 1211JSON_OBJECTAGG(t1.pkey, t2.jcol) 1212{"1": [1, 2, 3, 4, 5], "2": [1, 2, 3, 4, 5], "3": [1, 2, 3, 4, 5], "4": [1, 2, 3, 4, 5], "5": [1, 2, 3, 4, 5]} 1213SELECT JSON_ARRAYAGG(jcol) FROM json_data; 1214JSON_ARRAYAGG(jcol) 1215[{"1": 30, "2": 24, "3": 26, "4": 28, "5": 32}, ["ailemac", "nielk", "nuicarc", "ihcseruj", "qweasdas"], [30, 24, 26, 28, 32], {"12": "uciov", "13": "ierdna", "14": "elisav", "15": "namor", "16": "jugcvb"}, {"1": "ailemac", "2": "nielk", "3": "nuicarc", "4": "ihcseruj", "5": "qweasdas"}, [1, 2, 3, 4, 5]] 1216include/diff_tables.inc [master:json_data, slave:json_data] 1217[Connection Master] 1218# Update values in the table 1219UPDATE json_data SET jcol= (SELECT JSON_ARRAYAGG(vcol) FROM data_table) WHERE nkey = 1; 1220include/sync_slave_sql_with_master.inc 1221[Connection Slave] 1222SELECT * FROM json_data WHERE nkey = 1; 1223nkey jcol 12241 ["namor", "uciov", "ierdna", "elisav", "jugcvb"] 1225include/diff_tables.inc [master:json_data, slave:json_data] 1226[Connection Master] 1227# Delete values from the table 1228DELETE FROM json_data WHERE jcol = (SELECT JSON_ARRAYAGG(pkey) FROM data_table); 1229include/sync_slave_sql_with_master.inc 1230[Connection Slave] 1231SELECT * FROM json_data; 1232nkey jcol 12331 ["namor", "uciov", "ierdna", "elisav", "jugcvb"] 12342 ["ailemac", "nielk", "nuicarc", "ihcseruj", "qweasdas"] 12353 [30, 24, 26, 28, 32] 12364 {"12": "uciov", "13": "ierdna", "14": "elisav", "15": "namor", "16": "jugcvb"} 12375 {"1": "ailemac", "2": "nielk", "3": "nuicarc", "4": "ihcseruj", "5": "qweasdas"} 1238include/diff_tables.inc [master:json_data, slave:json_data] 1239[Connection Master] 1240DROP TABLE data_table; 1241DROP TABLE json_data; 1242include/sync_slave_sql_with_master.inc 1243# 1244# WL#9191: ADD JSON_PRETTY FUNCTION 1245# 1246[Connection Master] 1247# Create tables of various data types 1248CREATE TABLE data (pkey INT PRIMARY KEY AUTO_INCREMENT, 1249txt TEXT, vcol VARCHAR(20), num INT, 1250gcol INT AS (num * 2) VIRTUAL, 1251gcol2 JSON AS (JSON_PRETTY(JSON_OBJECT(num,gcol)))); 1252CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT, jcol TEXT, jgcol TEXT AS (JSON_PRETTY(jcol))); 1253# Insert values into the tables using the pretty function 1254INSERT INTO data (txt, vcol, num) VALUES ('abc', 'namotgr', 150), 1255('fwjh4', 'ucierov', 142), 1256('8942rhkjh', 'roiu3r', 913), 1257('imfmf', 'r3jr2', 15), 1258('32rj2jr', 'r2ihrhr', 32); 1259INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_OBJECTAGG(pkey, gcol)) FROM data; 1260INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_ARRAYAGG(txt)) FROM data; 1261INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_PRETTY(gcol2)) FROM data; 1262INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_OBJECT(num , vcol)) FROM data; 1263INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_OBJECTAGG(pkey , txt)) FROM data; 1264INSERT INTO json_data(jcol) SELECT JSON_PRETTY(JSON_ARRAY(gcol)) FROM data; 1265include/sync_slave_sql_with_master.inc 1266[Connection Slave] 1267# Check whether tables were created on the slave 1268SELECT * FROM json_data; 1269pk jcol jgcol 12701 { 1271 "1": 300, 1272 "2": 284, 1273 "3": 1826, 1274 "4": 30, 1275 "5": 64 1276} { 1277 "1": 300, 1278 "2": 284, 1279 "3": 1826, 1280 "4": 30, 1281 "5": 64 1282} 12832 [ 1284 "abc", 1285 "fwjh4", 1286 "8942rhkjh", 1287 "imfmf", 1288 "32rj2jr" 1289] [ 1290 "abc", 1291 "fwjh4", 1292 "8942rhkjh", 1293 "imfmf", 1294 "32rj2jr" 1295] 12963 { 1297 "150": 300 1298} { 1299 "150": 300 1300} 13014 { 1302 "142": 284 1303} { 1304 "142": 284 1305} 13065 { 1307 "913": 1826 1308} { 1309 "913": 1826 1310} 13116 { 1312 "15": 30 1313} { 1314 "15": 30 1315} 13167 { 1317 "32": 64 1318} { 1319 "32": 64 1320} 132110 { 1322 "150": "namotgr" 1323} { 1324 "150": "namotgr" 1325} 132611 { 1327 "142": "ucierov" 1328} { 1329 "142": "ucierov" 1330} 133112 { 1332 "913": "roiu3r" 1333} { 1334 "913": "roiu3r" 1335} 133613 { 1337 "15": "r3jr2" 1338} { 1339 "15": "r3jr2" 1340} 134114 { 1342 "32": "r2ihrhr" 1343} { 1344 "32": "r2ihrhr" 1345} 134617 { 1347 "1": "abc", 1348 "2": "fwjh4", 1349 "3": "8942rhkjh", 1350 "4": "imfmf", 1351 "5": "32rj2jr" 1352} { 1353 "1": "abc", 1354 "2": "fwjh4", 1355 "3": "8942rhkjh", 1356 "4": "imfmf", 1357 "5": "32rj2jr" 1358} 135918 [ 1360 300 1361] [ 1362 300 1363] 136419 [ 1365 284 1366] [ 1367 284 1368] 136920 [ 1370 1826 1371] [ 1372 1826 1373] 137421 [ 1375 30 1376] [ 1377 30 1378] 137922 [ 1380 64 1381] [ 1382 64 1383] 1384SELECT gcol2 FROM data; 1385gcol2 1386{"150": 300} 1387{"142": 284} 1388{"913": 1826} 1389{"15": 30} 1390{"32": 64} 1391include/diff_tables.inc [master:json_data, slave:json_data] 1392include/diff_tables.inc [master:data, slave:data] 1393[Connection Master] 1394# Update values in the table 1395UPDATE json_data SET jcol= (SELECT JSON_PRETTY(JSON_ARRAYAGG(gcol2)) FROM data) WHERE pk= 2; 1396UPDATE json_data SET jcol= (SELECT JSON_PRETTY(JSON_OBJECTAGG(pk,gcol)) FROM data) WHERE pk = 1; 1397include/sync_slave_sql_with_master.inc 1398[Connection Slave] 1399SELECT * FROM json_data; 1400pk jcol jgcol 14011 { 1402 "1": 64 1403} { 1404 "1": 64 1405} 14062 [ 1407 { 1408 "150": 300 1409 }, 1410 { 1411 "142": 284 1412 }, 1413 { 1414 "913": 1826 1415 }, 1416 { 1417 "15": 30 1418 }, 1419 { 1420 "32": 64 1421 } 1422] [ 1423 { 1424 "150": 300 1425 }, 1426 { 1427 "142": 284 1428 }, 1429 { 1430 "913": 1826 1431 }, 1432 { 1433 "15": 30 1434 }, 1435 { 1436 "32": 64 1437 } 1438] 14393 { 1440 "150": 300 1441} { 1442 "150": 300 1443} 14444 { 1445 "142": 284 1446} { 1447 "142": 284 1448} 14495 { 1450 "913": 1826 1451} { 1452 "913": 1826 1453} 14546 { 1455 "15": 30 1456} { 1457 "15": 30 1458} 14597 { 1460 "32": 64 1461} { 1462 "32": 64 1463} 146410 { 1465 "150": "namotgr" 1466} { 1467 "150": "namotgr" 1468} 146911 { 1470 "142": "ucierov" 1471} { 1472 "142": "ucierov" 1473} 147412 { 1475 "913": "roiu3r" 1476} { 1477 "913": "roiu3r" 1478} 147913 { 1480 "15": "r3jr2" 1481} { 1482 "15": "r3jr2" 1483} 148414 { 1485 "32": "r2ihrhr" 1486} { 1487 "32": "r2ihrhr" 1488} 148917 { 1490 "1": "abc", 1491 "2": "fwjh4", 1492 "3": "8942rhkjh", 1493 "4": "imfmf", 1494 "5": "32rj2jr" 1495} { 1496 "1": "abc", 1497 "2": "fwjh4", 1498 "3": "8942rhkjh", 1499 "4": "imfmf", 1500 "5": "32rj2jr" 1501} 150218 [ 1503 300 1504] [ 1505 300 1506] 150719 [ 1508 284 1509] [ 1510 284 1511] 151220 [ 1513 1826 1514] [ 1515 1826 1516] 151721 [ 1518 30 1519] [ 1520 30 1521] 152222 [ 1523 64 1524] [ 1525 64 1526] 1527include/diff_tables.inc [master:json_data, slave:json_data] 1528[Connection Master] 1529# Delete values from the table 1530DELETE FROM json_data WHERE JSON_PRETTY(jgcol) = (SELECT JSON_PRETTY(JSON_OBJECTAGG(pkey,txt)) FROM data); 1531include/sync_slave_sql_with_master.inc 1532[Connection Slave] 1533SELECT * FROM json_data; 1534pk jcol jgcol 15351 { 1536 "1": 64 1537} { 1538 "1": 64 1539} 15402 [ 1541 { 1542 "150": 300 1543 }, 1544 { 1545 "142": 284 1546 }, 1547 { 1548 "913": 1826 1549 }, 1550 { 1551 "15": 30 1552 }, 1553 { 1554 "32": 64 1555 } 1556] [ 1557 { 1558 "150": 300 1559 }, 1560 { 1561 "142": 284 1562 }, 1563 { 1564 "913": 1826 1565 }, 1566 { 1567 "15": 30 1568 }, 1569 { 1570 "32": 64 1571 } 1572] 15733 { 1574 "150": 300 1575} { 1576 "150": 300 1577} 15784 { 1579 "142": 284 1580} { 1581 "142": 284 1582} 15835 { 1584 "913": 1826 1585} { 1586 "913": 1826 1587} 15886 { 1589 "15": 30 1590} { 1591 "15": 30 1592} 15937 { 1594 "32": 64 1595} { 1596 "32": 64 1597} 159810 { 1599 "150": "namotgr" 1600} { 1601 "150": "namotgr" 1602} 160311 { 1604 "142": "ucierov" 1605} { 1606 "142": "ucierov" 1607} 160812 { 1609 "913": "roiu3r" 1610} { 1611 "913": "roiu3r" 1612} 161313 { 1614 "15": "r3jr2" 1615} { 1616 "15": "r3jr2" 1617} 161814 { 1619 "32": "r2ihrhr" 1620} { 1621 "32": "r2ihrhr" 1622} 162318 [ 1624 300 1625] [ 1626 300 1627] 162819 [ 1629 284 1630] [ 1631 284 1632] 163320 [ 1634 1826 1635] [ 1636 1826 1637] 163821 [ 1639 30 1640] [ 1641 30 1642] 164322 [ 1644 64 1645] [ 1646 64 1647] 1648include/diff_tables.inc [master:json_data, slave:json_data] 1649[Connection Master] 1650DROP TABLE data; 1651DROP TABLE json_data; 1652include/sync_slave_sql_with_master.inc 1653# 1654# WL#8963: SUPPORT FOR PARTIAL UPDATE OF JSON IN THE OPTIMIZER 1655# WL#9192: ADD JSON_STORAGE_SIZE / JSON_STORAGE_FREE FUNCTIONS 1656# 1657[Connection Master] 1658# Create table containing various types of data 1659CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT, 1660id INT, 1661jd JSON, 1662td TEXT, 1663gcol INT AS (JSON_STORAGE_SIZE(jd)), 1664gcol2 INT AS (JSON_STORAGE_FREE(jd)) VIRTUAL, 1665gcol3 JSON AS (JSON_ARRAY(id,jd,td,gcol,gcol2)), 1666gcol4 JSON AS (JSON_OBJECT(id,jd,gcol,td)) VIRTUAL); 1667# Insert data into the table 1668INSERT INTO json_data(id,jd,td) VALUES(10, '{"a":1}', 'characters'), 1669(20, '[10,20,30]', 'alphabet'), 1670(30, '[{"a":"abc"},{"b":"abcd"}]', 'words'), 1671(40, '{"key":["array", "of", "strings"]}', 'letters'), 1672(50, '{"key":[{"key":"value"},{"key":"value2"}]}', 'strings'); 1673include/sync_slave_sql_with_master.inc 1674[Connection Slave] 1675# Check whether table was created on the slave 1676SELECT * FROM json_data; 1677pk id jd td gcol gcol2 gcol3 gcol4 16781 10 {"a": 1} characters 13 0 [10, {"a": 1}, "characters", 13, 0] {"10": {"a": 1}, "13": "characters"} 16792 20 [10, 20, 30] alphabet 14 0 [20, [10, 20, 30], "alphabet", 14, 0] {"14": "alphabet", "20": [10, 20, 30]} 16803 30 [{"a": "abc"}, {"b": "abcd"}] words 44 0 [30, [{"a": "abc"}, {"b": "abcd"}], "words", 44, 0] {"30": [{"a": "abc"}, {"b": "abcd"}], "44": "words"} 16814 40 {"key": ["array", "of", "strings"]} letters 45 0 [40, {"key": ["array", "of", "strings"]}, "letters", 45, 0] {"40": {"key": ["array", "of", "strings"]}, "45": "letters"} 16825 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"} 1683include/diff_tables.inc [master:json_data, slave:json_data] 1684[Connection Master] 1685# Update values in the table 1686UPDATE json_data SET jd = JSON_REPLACE(jd, '$[0].a',"a"); 1687include/sync_slave_sql_with_master.inc 1688[Connection Slave] 1689# Check whether values were updated 1690SELECT * FROM json_data; 1691pk id jd td gcol gcol2 gcol3 gcol4 16921 10 {"a": "a"} characters 15 0 [10, {"a": "a"}, "characters", 15, 0] {"10": {"a": "a"}, "15": "characters"} 16932 20 [10, 20, 30] alphabet 14 0 [20, [10, 20, 30], "alphabet", 14, 0] {"14": "alphabet", "20": [10, 20, 30]} 16943 30 [{"a": "a"}, {"b": "abcd"}] words 44 2 [30, [{"a": "a"}, {"b": "abcd"}], "words", 44, 2] {"30": [{"a": "a"}, {"b": "abcd"}], "44": "words"} 16954 40 {"key": ["array", "of", "strings"]} letters 45 0 [40, {"key": ["array", "of", "strings"]}, "letters", 45, 0] {"40": {"key": ["array", "of", "strings"]}, "45": "letters"} 16965 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"} 1697SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data; 1698JSON_STORAGE_SIZE(gcol3) JSON_STORAGE_SIZE(gcol4) 169945 48 170042 45 170167 70 170272 75 170393 96 1704SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data; 1705JSON_STORAGE_FREE(gcol3) JSON_STORAGE_FREE(gcol4) 17060 0 17070 0 17080 0 17090 0 17100 0 1711include/diff_tables.inc [master:json_data, slave:json_data] 1712[Connection Master] 1713# Update more values in the table 1714UPDATE json_data SET jd = JSON_SET(jd, '$.key[0]', "ar"); 1715include/sync_slave_sql_with_master.inc 1716[Connection Slave] 1717# Check whether values were updated 1718SELECT * FROM json_data; 1719pk id jd td gcol gcol2 gcol3 gcol4 17201 10 {"a": "a"} characters 15 0 [10, {"a": "a"}, "characters", 15, 0] {"10": {"a": "a"}, "15": "characters"} 17212 20 [10, 20, 30] alphabet 14 0 [20, [10, 20, 30], "alphabet", 14, 0] {"14": "alphabet", "20": [10, 20, 30]} 17223 30 [{"a": "a"}, {"b": "abcd"}] words 44 2 [30, [{"a": "a"}, {"b": "abcd"}], "words", 44, 2] {"30": [{"a": "a"}, {"b": "abcd"}], "44": "words"} 17234 40 {"key": ["ar", "of", "strings"]} letters 45 3 [40, {"key": ["ar", "of", "strings"]}, "letters", 45, 3] {"40": {"key": ["ar", "of", "strings"]}, "45": "letters"} 17245 50 {"key": ["ar", {"key": "value2"}]} strings 66 17 [50, {"key": ["ar", {"key": "value2"}]}, "strings", 66, 17] {"50": {"key": ["ar", {"key": "value2"}]}, "66": "strings"} 1725SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data; 1726JSON_STORAGE_SIZE(gcol3) JSON_STORAGE_SIZE(gcol4) 172745 48 172842 45 172967 70 173069 72 173176 79 1732SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data; 1733JSON_STORAGE_FREE(gcol3) JSON_STORAGE_FREE(gcol4) 17340 0 17350 0 17360 0 17370 0 17380 0 1739include/diff_tables.inc [master:json_data, slave:json_data] 1740[Connection Master] 1741# Update values again 1742UPDATE json_data SET jd = JSON_REPLACE(jd, '$.key', "hello"); 1743include/sync_slave_sql_with_master.inc 1744[Connection Slave] 1745# Check whether values were updated 1746SELECT * FROM json_data; 1747pk id jd td gcol gcol2 gcol3 gcol4 17481 10 {"a": "a"} characters 15 0 [10, {"a": "a"}, "characters", 15, 0] {"10": {"a": "a"}, "15": "characters"} 17492 20 [10, 20, 30] alphabet 14 0 [20, [10, 20, 30], "alphabet", 14, 0] {"14": "alphabet", "20": [10, 20, 30]} 17503 30 [{"a": "a"}, {"b": "abcd"}] words 44 2 [30, [{"a": "a"}, {"b": "abcd"}], "words", 44, 2] {"30": [{"a": "a"}, {"b": "abcd"}], "44": "words"} 17514 40 {"key": "hello"} letters 45 24 [40, {"key": "hello"}, "letters", 45, 24] {"40": {"key": "hello"}, "45": "letters"} 17525 50 {"key": "hello"} strings 66 45 [50, {"key": "hello"}, "strings", 66, 45] {"50": {"key": "hello"}, "66": "strings"} 1753SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data; 1754JSON_STORAGE_SIZE(gcol3) JSON_STORAGE_SIZE(gcol4) 175545 48 175642 45 175767 70 175848 51 175948 51 1760SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data; 1761JSON_STORAGE_FREE(gcol3) JSON_STORAGE_FREE(gcol4) 17620 0 17630 0 17640 0 17650 0 17660 0 1767include/diff_tables.inc [master:json_data, slave:json_data] 1768[Connection Master] 1769# Update values such that they can fill up the "free" space 1770UPDATE json_data SET jd= JSON_SET(jd, '$.key', '["h","e","l","l","o"]'); 1771include/sync_slave_sql_with_master.inc 1772[Connection Slave] 1773# Check whether values were updated 1774SELECT * FROM json_data; 1775pk id jd td gcol gcol2 gcol3 gcol4 17761 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"} 17772 20 [10, 20, 30] alphabet 14 0 [20, [10, 20, 30], "alphabet", 14, 0] {"14": "alphabet", "20": [10, 20, 30]} 17783 30 [{"a": "a"}, {"b": "abcd"}] words 44 2 [30, [{"a": "a"}, {"b": "abcd"}], "words", 44, 2] {"30": [{"a": "a"}, {"b": "abcd"}], "44": "words"} 17794 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"} 17805 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"} 1781SELECT JSON_STORAGE_SIZE(gcol3), JSON_STORAGE_SIZE(gcol4) FROM json_data; 1782JSON_STORAGE_SIZE(gcol3) JSON_STORAGE_SIZE(gcol4) 178377 80 178442 45 178567 70 178664 67 178764 67 1788SELECT JSON_STORAGE_FREE(gcol3), JSON_STORAGE_FREE(gcol4) FROM json_data; 1789JSON_STORAGE_FREE(gcol3) JSON_STORAGE_FREE(gcol4) 17900 0 17910 0 17920 0 17930 0 17940 0 1795include/diff_tables.inc [master:json_data, slave:json_data] 1796[Connection Master] 1797# Clean up 1798DROP TABLE json_data; 1799include/sync_slave_sql_with_master.inc 1800# 1801# WL#9831: RANGES IN JSON PATH EXPRESSIONS 1802# 1803[Connection Master] 1804# Create table with JSON data 1805CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT, 1806id INT, 1807jd JSON, 1808td TEXT, 1809gcol JSON AS (JSON_EXTRACT(jd, '$[last -2 to last]')) VIRTUAL, 1810gcol2 VARCHAR(100) AS (JSON_EXTRACT(td, '$[0 to 3]')) STORED, 1811gcol3 JSON AS (JSON_EXTRACT(JSON_ARRAY(id,jd,td,gcol,gcol2), 1812'$[2][last -3 to last -1]')) VIRTUAL, 1813gcol4 JSON AS (JSON_EXTRACT(JSON_OBJECT(id,jd,gcol,td), 1814'$.id[0 to last]')) STORED, 1815key(gcol2)); 1816# Insert data into the table 1817INSERT INTO json_data(id,jd,td) VALUES(10, '["abc", "def", "ghi", "jkl", "mno"]', 1818'["pqr", "stu", "vwx", "xyz"]'), 1819(20, '[10, 20, 30, 40, 50]', 1820'["alpha", "beta", "gamma", "delta"]'), 1821(30, '["z", "y", "x", "w", "v"]', 1822'["Bengaluru", "Mysuru", "Davangere", "Belagaum"]'), 1823(40, '["array", "of", "strings", "and", "more"]', 1824'[2, 3, 5, 7]'), 1825(50, '[{"key":[{"key":"value"},{"key":"value2"}]}, 1826 {"key":[{"key":"value3"},{"key":"value4"}]}]', 1827'["N", "U", "L", "L"]'); 1828include/sync_slave_sql_with_master.inc 1829[Connection Slave] 1830# Check whether table was created on the slave 1831SELECT * FROM json_data; 1832pk id jd td gcol gcol2 gcol3 gcol4 18331 10 ["abc", "def", "ghi", "jkl", "mno"] ["pqr", "stu", "vwx", "xyz"] ["ghi", "jkl", "mno"] ["pqr", "stu", "vwx", "xyz"] NULL NULL 18342 20 [10, 20, 30, 40, 50] ["alpha", "beta", "gamma", "delta"] [30, 40, 50] ["alpha", "beta", "gamma", "delta"] NULL NULL 18353 30 ["z", "y", "x", "w", "v"] ["Bengaluru", "Mysuru", "Davangere", "Belagaum"] ["x", "w", "v"] ["Bengaluru", "Mysuru", "Davangere", "Belagaum"] NULL NULL 18364 40 ["array", "of", "strings", "and", "more"] [2, 3, 5, 7] ["strings", "and", "more"] [2, 3, 5, 7] NULL NULL 18375 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 1838include/diff_tables.inc [master:json_data, slave:json_data] 1839[Connection Master] 1840# Update values in the table 1841UPDATE json_data SET jd = JSON_SET(jd, '$[1]', "replaced") WHERE 1842JSON_CONTAINS(JSON_EXTRACT(jd, '$[0 to last]'), '[10, 20, 30, 40, 50]'); 1843include/sync_slave_sql_with_master.inc 1844[Connection Slave] 1845# Check whether values were updated 1846SELECT * FROM json_data; 1847pk id jd td gcol gcol2 gcol3 gcol4 18481 10 ["abc", "def", "ghi", "jkl", "mno"] ["pqr", "stu", "vwx", "xyz"] ["ghi", "jkl", "mno"] ["pqr", "stu", "vwx", "xyz"] NULL NULL 18492 20 [10, "replaced", 30, 40, 50] ["alpha", "beta", "gamma", "delta"] [30, 40, 50] ["alpha", "beta", "gamma", "delta"] NULL NULL 18503 30 ["z", "y", "x", "w", "v"] ["Bengaluru", "Mysuru", "Davangere", "Belagaum"] ["x", "w", "v"] ["Bengaluru", "Mysuru", "Davangere", "Belagaum"] NULL NULL 18514 40 ["array", "of", "strings", "and", "more"] [2, 3, 5, 7] ["strings", "and", "more"] [2, 3, 5, 7] NULL NULL 18525 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 1853SELECT JSON_EXTRACT(td, '$[0 to last]') FROM json_data; 1854JSON_EXTRACT(td, '$[0 to last]') 1855["pqr", "stu", "vwx", "xyz"] 1856["alpha", "beta", "gamma", "delta"] 1857["Bengaluru", "Mysuru", "Davangere", "Belagaum"] 1858[2, 3, 5, 7] 1859["N", "U", "L", "L"] 1860[Connection Master] 1861# Clean up 1862DROP TABLE json_data; 1863include/sync_slave_sql_with_master.inc 1864# 1865# WL#9692: ADD JSON_MERGE_PATCH, RENAME JSON_MERGE TO JSON_MERGE_PRESERVE 1866# 1867[Connection Master] 1868# Create table with JSON data 1869CREATE TABLE json_data(pk INT PRIMARY KEY AUTO_INCREMENT, 1870id INT, 1871jd JSON, 1872td TEXT, 1873gcol VARCHAR(30) AS (JSON_EXTRACT(JSON_MERGE_PATCH(jd,JSON_OBJECT(id,td)), '$.a')) VIRTUAL, 1874KEY(gcol)); 1875# Insert data into the table 1876INSERT INTO json_data(id,jd,td) VALUES(10,'["a","b","c","d","e"]', "pink"), 1877(20,'{"a":[1,2,6,7,9]}', "floyd"), 1878(30,'{"a":["ears","nose","eyes"]}', "megadeth"), 1879(40,'[null,null,"null",0,1]', "def"), 1880(50,'{"a":null}', "leppard"); 1881include/sync_slave_sql_with_master.inc 1882[Connection Slave] 1883# Check whether table was created on the slave 1884SELECT * FROM json_data; 1885pk id jd td gcol 18861 10 ["a", "b", "c", "d", "e"] pink NULL 18872 20 {"a": [1, 2, 6, 7, 9]} floyd [1, 2, 6, 7, 9] 18883 30 {"a": ["ears", "nose", "eyes"]} megadeth ["ears", "nose", "eyes"] 18894 40 [null, null, "null", 0, 1] def NULL 18905 50 {"a": null} leppard null 1891include/diff_tables.inc [master:json_data, slave:json_data] 1892[Connection Master] 1893# Create tables using the JSON_MERGE_PATCH function 1894CREATE TABLE json_data2(field1 JSON); 1895INSERT INTO json_data2 1896SELECT JSON_MERGE_PATCH(JSON_ARRAYAGG(jd), JSON_OBJECTAGG(pk, jd)) 1897FROM json_data; 1898CREATE TABLE json_data3(field1 JSON); 1899INSERT INTO json_data3 1900SELECT JSON_MERGE_PATCH(JSON_OBJECTAGG(id, gcol), JSON_ARRAYAGG(td)) 1901FROM json_data; 1902CREATE TABLE json_data4(field1 JSON); 1903INSERT INTO json_data4 1904SELECT JSON_MERGE_PATCH(JSON_OBJECT(id, jd), JSON_ARRAY(gcol)) 1905FROM json_data; 1906include/sync_slave_sql_with_master.inc 1907[Connection Slave] 1908# Check whether the tables were created on the slave 1909SELECT * FROM json_data2; 1910field1 1911{"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": {}} 1912SELECT * FROM json_data3; 1913field1 1914["pink", "floyd", "megadeth", "def", "leppard"] 1915SELECT * FROM json_data4; 1916field1 1917[null] 1918["[1, 2, 6, 7, 9]"] 1919["[\"ears\", \"nose\", \"eyes\"]"] 1920[null] 1921["null"] 1922SELECT JSON_MERGE_PATCH(t1.field1, t2.field1) FROM json_data2 as t1, json_data3 as t2; 1923JSON_MERGE_PATCH(t1.field1, t2.field1) 1924["pink", "floyd", "megadeth", "def", "leppard"] 1925SELECT JSON_MERGE_PRESERVE(t1.field1, t2.field1) FROM json_data3 as t1, json_data4 as t2; 1926JSON_MERGE_PRESERVE(t1.field1, t2.field1) 1927["pink", "floyd", "megadeth", "def", "leppard", null] 1928["pink", "floyd", "megadeth", "def", "leppard", "[1, 2, 6, 7, 9]"] 1929["pink", "floyd", "megadeth", "def", "leppard", "[\"ears\", \"nose\", \"eyes\"]"] 1930["pink", "floyd", "megadeth", "def", "leppard", null] 1931["pink", "floyd", "megadeth", "def", "leppard", "null"] 1932[Connection Master] 1933# Update values in the table 1934UPDATE json_data SET jd = JSON_SET(jd, '$[1]', JSON_MERGE_PATCH(JSON_ARRAY(pk),JSON_ARRAY(td))) WHERE id IN (10,40); 1935include/sync_slave_sql_with_master.inc 1936[Connection Slave] 1937include/diff_tables.inc [master:json_data, slave:json_data] 1938# Check whether values were updated 1939SELECT * FROM json_data; 1940pk id jd td gcol 19411 10 ["a", ["pink"], "c", "d", "e"] pink NULL 19422 20 {"a": [1, 2, 6, 7, 9]} floyd [1, 2, 6, 7, 9] 19433 30 {"a": ["ears", "nose", "eyes"]} megadeth ["ears", "nose", "eyes"] 19444 40 [null, ["def"], "null", 0, 1] def NULL 19455 50 {"a": null} leppard null 1946[Connection Master] 1947# Clean up 1948DROP TABLE json_data, json_data2, json_data3, json_data4; 1949include/sync_slave_sql_with_master.inc 1950# 1951# WL#8867: ADD JSON TABLE FUNCTIONS 1952# 1953[Connection Master] 1954# Create table using data obtained from JSON_TABLE function 1955CREATE TABLE json_data(j json); 1956INSERT INTO json_data VALUES('{"a":1, "b":"trucks", "c":[1,2,3], "d":"taste", "e":"93-01-01", "f":"03:48:07"}'); 1957INSERT INTO json_data VALUES('{"a":2, "b":"rickshaws", "c":{"key":"value"}, "d":"smell" , "e":"12-11-12", "f":"09:09:10"}'); 1958INSERT INTO json_data VALUES('{"a":3, "b":"cars", "c":["a","b","c","d"], "d":"sight", "e":"01-01-01", "f":"05:18:12"}'); 1959INSERT INTO json_data VALUES('{"a":4, "b":"bikes", "c":{"a":[1,2]}, "d":"hearing", "e":"98-05-06", "f":"00:00:00" }'); 1960INSERT INTO json_data VALUES('{"a":5, "b":"mopeds", "c":["one", "two", "three"], "d":"touch", "e":"56-12-07", "f":"12:12:12"}'); 1961CREATE TABLE data(i INT, v VARCHAR(10), j JSON, b BLOB, d DATE, t TIME, n JSON); 1962INSERT 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; 1963include/sync_slave_sql_with_master.inc 1964[Connection Slave] 1965# Check whether the table was created on the slave 1966SELECT * FROM data; 1967i v j b d t n 19681 trucks [1, 2, 3] taste 1993-01-01 03:48:07 1 19692 rickshaws {"key": "value"} smell 2012-11-12 09:09:10 {"key": "value"} 19703 cars ["a", "b", "c", "d"] sight 2001-01-01 05:18:12 "a" 19714 bikes {"a": [1, 2]} hearing 1998-05-06 00:00:00 {"a": [1, 2]} 19725 mopeds ["one", "two", "three"] touch 2056-12-07 12:12:12 "one" 1973include/diff_tables.inc [master:data, slave:data] 1974[Connection Master] 1975# Update values in the table with data obtained from JSON_TABLE 1976UPDATE 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; 1977UPDATE data SET v = (SELECT ucol FROM JSON_TABLE('{"key":["update"]}', '$' COLUMNS(ucol VARCHAR(10) PATH '$.key[0]')) AS alias) WHERE i = 4; 1978include/sync_slave_sql_with_master.inc 1979[Connection Slave] 1980# Check whether the values were updated 1981SELECT j,v FROM data; 1982j v 1983[1, 2, 3] trucks 1984{"a": 2, "b": "rickshaws", "c": {"key": "value"}, "d": "smell", "e": "12-11-12", "f": "09:09:10"} rickshaws 1985["a", "b", "c", "d"] cars 1986{"a": [1, 2]} update 1987["one", "two", "three"] mopeds 1988include/diff_tables.inc [master:data, slave:data] 1989[Connection Master] 1990# Delete values from the table 1991DELETE FROM data WHERE i = (SELECT dcol FROM JSON_TABLE('{"key":["delete",1,"2"]}', '$' COLUMNS(dcol INT PATH '$.key[1]')) AS alias); 1992include/sync_slave_sql_with_master.inc 1993[Connection Slave] 1994# Check whether the row was deleted 1995SELECT * FROM data; 1996i v j b d t n 19972 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"} 19983 cars ["a", "b", "c", "d"] sight 2001-01-01 05:18:12 "a" 19994 update {"a": [1, 2]} hearing 1998-05-06 00:00:00 {"a": [1, 2]} 20005 mopeds ["one", "two", "three"] touch 2056-12-07 12:12:12 "one" 2001include/diff_tables.inc [master:data, slave:data] 2002[Connection Master] 2003# cleanup 2004DROP TABLE data,json_data; 2005include/sync_slave_sql_with_master.inc 2006# 2007# Bug#26177130: SLAVE CAN'T FIND ROW USING HASH_SCAN 2008# IF JSON HAS DIFFERENT BINARY FORMAT 2009# 2010[connection slave] 2011include/stop_slave_sql.inc 2012include/begin_replace_combination.inc [BOTH.binlog_format=row,mixed,statement -> GLOBAL.slave_rows_search_algorithms=HASH_SCAN,TABLE_SCAN,INDEX_SCAN] 2013Warnings: 2014Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release. 2015include/start_slave_sql.inc 2016[connection master] 2017SET @old_binlog_format = @@SESSION.binlog_format; 2018SET SESSION binlog_format = ROW; 2019[connection slave] 2020include/stop_slave.inc 2021include/start_slave.inc 2022[connection master] 2023CREATE TABLE t (j JSON); 2024SET SQL_LOG_BIN = 0; 2025INSERT INTO t VALUES ('["abc", "abcdefghij"]'); 2026UPDATE t SET j = JSON_SET(j, '$[0]', 'a'); 2027SET SQL_LOG_BIN = 1; 2028SELECT j, JSON_STORAGE_SIZE(j), JSON_STORAGE_FREE(j) FROM t; 2029j JSON_STORAGE_SIZE(j) JSON_STORAGE_FREE(j) 2030["a", "abcdefghij"] 26 2 2031include/sync_slave_sql_with_master.inc 2032INSERT INTO t VALUES ('["a", "abcdefghij"]'); 2033SELECT j, JSON_STORAGE_SIZE(j), JSON_STORAGE_FREE(j) FROM t; 2034j JSON_STORAGE_SIZE(j) JSON_STORAGE_FREE(j) 2035["a", "abcdefghij"] 24 0 2036# At this point, the master and the slave have the same JSON document, 2037# but the binary representation is different (the master has a 'gap' due 2038# to partial update). 2039[connection master] 2040DELETE FROM t; 2041# Verify that the slave and the master are in sync after the DELETE. 2042# Before the fix, the slave could not locate the row due with 2043# slave_rows_search_algorithms=HASH_SCAN because of the difference in 2044# the representation. 2045include/sync_slave_sql_with_master.inc 2046# Clean up 2047[connection master] 2048DROP TABLE t; 2049include/sync_slave_sql_with_master.inc 2050include/stop_slave.inc 2051include/end_replace_combination.inc [BOTH.binlog_format=row,mixed,statement -> GLOBAL.slave_rows_search_algorithms=HASH_SCAN,TABLE_SCAN,INDEX_SCAN] 2052Warnings: 2053Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release. 2054include/start_slave.inc 2055[connection master] 2056SET SESSION binlog_format = @old_binlog_format; 2057include/rpl_end.inc 2058