# # The following test takes 2 tables containing a JSON column and attempts # to repair them. # # The tables header is (Description, Expected, Actual), where description # shows a brief description what the JSON value is testing in the MariaDB # implementation. Expected is the longtext string and actual is the JSON # column that needs to be converted to MariaDB's representation of # LONGTEXT. # call mtr.add_suppression("Table rebuild required"); call mtr.add_suppression("is marked as crashed"); call mtr.add_suppression("Checking"); SET NAMES utf8; # # Check that only ALTER TABLE ... FORCE is allowed on a MySQL 5.7 table # with a JSON column. # show create table tempty; ERROR HY000: Table rebuild required. Please do "ALTER TABLE `test.tempty` FORCE" or dump/reload to fix it! select * from tempty; ERROR HY000: Table rebuild required. Please do "ALTER TABLE `test.tempty` FORCE" or dump/reload to fix it! alter table tempty force; show create table tempty; Table Create Table tempty CREATE TABLE `tempty` ( `t` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci show create table mysql_json_test; ERROR HY000: Table rebuild required. Please do "ALTER TABLE `test.mysql_json_test` FORCE" or dump/reload to fix it! select * from mysql_json_test; ERROR HY000: Table rebuild required. Please do "ALTER TABLE `test.mysql_json_test` FORCE" or dump/reload to fix it! CREATE TABLE t2 AS SELECT * FROM mysql_json_test; ERROR HY000: Table rebuild required. Please do "ALTER TABLE `test.mysql_json_test` FORCE" or dump/reload to fix it! CREATE TABLE t2 (a mysql_json /*new column*/) AS SELECT * FROM mysql_json_test; ERROR HY000: 'MYSQL_JSON' is not allowed in this context CREATE TABLE t2 (actual mysql_json /*existing column*/) AS SELECT * FROM mysql_json_test; ERROR HY000: 'MYSQL_JSON' is not allowed in this context LOCK TABLES mysql_json_test WRITE; ERROR HY000: Table rebuild required. Please do "ALTER TABLE `test.mysql_json_test` FORCE" or dump/reload to fix it! alter table mysql_json_test force; select description, expected, actual, expected = actual from mysql_json_test; description expected actual expected = actual Array LITERALS: ["prefix", false, "suffix", 1] ["prefix", false, "suffix", 1] 1 Array LITERALS: ["prefix", null, "suffix", 1] ["prefix", null, "suffix", 1] 1 Array LITERALS: ["prefix", true, "suffix", 1] ["prefix", true, "suffix", 1] 1 DateTime as Raw Value: "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000" 1 DateTime as Raw Value: "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000" 1 DateTime as Raw Value: "2015-01-15" "2015-01-15" 1 DateTime as Raw Value: "23:24:25.000000" "23:24:25.000000" 1 Empty JSON Object/Array: [] [] 1 Empty JSON Object/Array: {} {} 1 GeoJSON {"type": "GeometryCollection", "geometries": []} {"type": "GeometryCollection", "geometries": []} 1 GeoJSON {"type": "LineString", "coordinates": [[0, 5], [5, 10], [10, 15]]} {"type": "LineString", "coordinates": [[0, 5], [5, 10], [10, 15]]} 1 GeoJSON {"type": "MultiPoint", "coordinates": [[1, 1], [2, 2], [3, 3]]} {"type": "MultiPoint", "coordinates": [[1, 1], [2, 2], [3, 3]]} 1 GeoJSON {"type": "Point", "coordinates": [11.1111, 12.22222]} {"type": "Point", "coordinates": [11.1111, 12.22222]} 1 JSON LITERALS: {"val": false} {"val": false} 1 JSON LITERALS: {"val": null} {"val": null} 1 JSON LITERALS: {"val": true} {"val": true} 1 Opaque Types: opaque_mysql_type_binary "base64:type254:YWJjAAAAAAAAAA==" "base64:type254:YWJjAAAAAAAAAA==" 1 Opaque Types: opaque_mysql_type_bit "base64:type16:yv4=" "base64:type16:yv4=" 1 Opaque Types: opaque_mysql_type_blob "base64:type252:yv66vg==" "base64:type252:yv66vg==" 1 Opaque Types: opaque_mysql_type_date "2015-01-15" "2015-01-15" 1 Opaque Types: opaque_mysql_type_datetime "2015-01-15 23:24:25.000000" "2015-01-15 23:24:25.000000" 1 Opaque Types: opaque_mysql_type_enum "b" "b" 1 Opaque Types: opaque_mysql_type_geom {"type": "Point", "coordinates": [1, 1]} {"type": "Point", "coordinates": [1, 1]} 1 Opaque Types: opaque_mysql_type_longblob "base64:type251:yv66vg==" "base64:type251:yv66vg==" 1 Opaque Types: opaque_mysql_type_mediumblob "base64:type250:yv66vg==" "base64:type250:yv66vg==" 1 Opaque Types: opaque_mysql_type_set "b,c" "b,c" 1 Opaque Types: opaque_mysql_type_time "23:24:25.000000" "23:24:25.000000" 1 Opaque Types: opaque_mysql_type_tinyblob "base64:type249:yv66vg==" "base64:type249:yv66vg==" 1 Opaque Types: opaque_mysql_type_varbinary "base64:type15:YWJj" "base64:type15:YWJj" 1 Opaque Types: opaque_mysql_type_varchar "base64:type15:Zm9v" "base64:type15:Zm9v" 1 Opaque Types: opaque_mysql_type_year "base64:type13:MjAxOQ==" "base64:type13:MjAxOQ==" 1 Raw LITERALS: false false 1 Raw LITERALS: null null 1 Raw LITERALS: true true 1 Raw doubles as JSON -2.2250738585072014e-308 -2.2250738585072014e-308 1 Raw doubles as JSON -5678.987 -5678.987 1 Raw doubles as JSON 0.0 0.0 1 Raw doubles as JSON 2.2250738585072014e-308 2.2250738585072014e-308 1 Raw doubles as JSON 3.14 3.14 1 Raw integers as JSON -127 -127 1 Raw integers as JSON -2147483648 -2147483648 1 Raw integers as JSON -32768 -32768 1 Raw integers as JSON -9223372036854775807 -9223372036854775807 1 Raw integers as JSON 0 0 1 Raw integers as JSON 128 128 1 Raw integers as JSON 18446744073709551615 18446744073709551615 1 Raw integers as JSON 2147483647 2147483647 1 Raw integers as JSON 32767 32767 1 Raw integers as JSON 4294967295 4294967295 1 Raw integers as JSON 65535 65535 1 Raw integers as JSON 65536 65536 1 Raw integers as JSON 9223372036854775807 9223372036854775807 1 Simple Array as Base Key [1, 2, 3, 4, 5, [], "a", "b", "c"] [1, 2, 3, 4, 5, [], "a", "b", "c"] 1 Simple Array as Value {"a": [1, 2], "b": ["x", "y"]} {"a": [1, 2], "b": ["x", "y"]} 1 Simple JSON test {"key1": "val1", "key2": "val2"} {"key1": "val1", "key2": "val2"} 1 Special Characters: "" "" 1 Special Characters: "'" "'" 1 Special Characters: "'" "'" 1 Special Characters: "'" "'" 1 Special Characters: "''" "''" 1 Special Characters: "\"" "\"" 1 Special Characters: "\\" "\\" 1 Special Characters: "\\b" "\\b" 1 Special Characters: "\b" "\b" 1 Special Characters: "\f" "\f" 1 Special Characters: "\n" "\n" 1 Special Characters: "\r" "\r" 1 Special Characters: "\t" "\t" 1 Special Characters: "f" "f" 1 Special Characters: "key1 - with \" val " "key1 - with \" val " 1 Special Characters: "q" "q" 1 Special Characters: "some_string" "some_string" 1 Special Characters: ["a ' b", "c ' d"] ["a ' b", "c ' d"] 1 Special Characters: ["a \" b", "c \" d"] ["a \" b", "c \" d"] 1 Special Characters: ["a \\ b", "c \\ d"] ["a \\ b", "c \\ d"] 1 Special Characters: ["a \b b", "c \b d"] ["a \b b", "c \b d"] 1 Special Characters: ["a \f b", "c \f d"] ["a \f b", "c \f d"] 1 Special Characters: ["a \r b", "c \r d"] ["a \r b", "c \r d"] 1 Special Characters: ["a \t b", "c \t d"] ["a \t b", "c \t d"] 1 Special Characters: {"[": "]"} {"[": "]"} 1 Special Characters: {"key ' key": "val ' val"} {"key ' key": "val ' val"} 1 Special Characters: {"key \" key": "val \" val"} {"key \" key": "val \" val"} 1 Special Characters: {"key \\ key": "val \\ val"} {"key \\ key": "val \\ val"} 1 Special Characters: {"key \\0 key": "val \n val"} {"key \\0 key": "val \n val"} 1 Special Characters: {"key \\Z key": "val ' val"} {"key \\Z key": "val ' val"} 1 Special Characters: {"key \b key": "val \b val"} {"key \b key": "val \b val"} 1 Special Characters: {"key \f key": "val \f val"} {"key \f key": "val \f val"} 1 Special Characters: {"key \n key": "val \n val"} {"key \n key": "val \n val"} 1 Special Characters: {"key \r key": "val \r val"} {"key \r key": "val \r val"} 1 Special Characters: {"key \t key": "val \t val"} {"key \t key": "val \t val"} 1 Special Characters: {"key1 and \n\"key2\"": "val1\t val2"} {"key1 and \n\"key2\"": "val1\t val2"} 1 Special Characters: {"{": "}"} {"{": "}"} 1 Special Characters: {"{": "}"} {"{": "}"} 1 Special String Cases: [""] [""] 1 Special String Cases: {"": ""} {"": ""} 1 Timestamp as RawValue "2019-12-26 19:56:03.000000" "2019-12-26 19:56:03.000000" 1 UTF8 Characters: "Anel Husaković - test: đžšćč" "Anel Husaković - test: đžšćč" 1 UTF8 Characters: {"Name": "Anel Husaković - test: đžšćč"} {"Name": "Anel Husaković - test: đžšćč"} 1 UTF8 Characters: {"Person": "EMP", "details": {"Name": "Anel Husaković - test: đžšćč"}} {"Person": "EMP", "details": {"Name": "Anel Husaković - test: đžšćč"}} 1 UTF8 Characters: {"details": {"Name": "Anel Husaković - test: đžšćč"}, "\"Anel Husaković - test: đžšćč\"": "EMP"} {"details": {"Name": "Anel Husaković - test: đžšćč"}, "\"Anel Husaković - test: đžšćč\"": "EMP"} 1 # # A quick check that all rows match from the original MySQL Table. # select count(*) as 'Total_Number_of_Tests', sum(expected = actual) as 'Succesful_Tests' from mysql_json_test; Total_Number_of_Tests Succesful_Tests 100 100 show create table mysql_json_test; Table Create Table mysql_json_test CREATE TABLE `mysql_json_test` ( `description` varchar(100) DEFAULT NULL, `expected` longtext DEFAULT NULL, `actual` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci show create table mysql_json_test_big; ERROR HY000: Table rebuild required. Please do "ALTER TABLE `test.mysql_json_test_big` FORCE" or dump/reload to fix it! select * from mysql_json_test_big; ERROR HY000: Table rebuild required. Please do "ALTER TABLE `test.mysql_json_test_big` FORCE" or dump/reload to fix it! # # This test checks the long format implementation of MySQL's JSON # Not printing the actual contents as they are not readable by a human, # just compare the strings, make sure they match. # alter table mysql_json_test_big force; select count(*) as 'Total_Number_of_Tests', sum(expected = actual) as 'Succesful_Tests', sum(JSON_VALID(actual)) as 'String_is_valid_JSON' from mysql_json_test_big; Total_Number_of_Tests Succesful_Tests String_is_valid_JSON 1 1 1 drop table tempty; drop table mysql_json_test; drop table mysql_json_test_big; # # MDEV-32790: Output result in show create table # for mysql_json type should be longtext # create table t1(j json); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `j` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`j`)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; create table t1(j mysql_json); ERROR HY000: 'MYSQL_JSON' is not allowed in this context create table `testjson` ( `t` json /* JSON from MySQL 5.7*/ CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL ) ENGINE=InnoDB DEFAULT CH...' at line 2 create table `testjson` ( `t` json /* JSON from MySQL 5.7*/ COLLATE utf8mb4_bin NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; show create table testjson; Table Create Table testjson CREATE TABLE `testjson` ( `t` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`t`)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table testjson; create table `testjson` ( `t` longtext /* JSON from MySQL 5.7 */ CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; show create table testjson; Table Create Table testjson CREATE TABLE `testjson` ( `t` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table testjson; # # MDEV-32235: mysql_json cannot be used on newly created table # CREATE TABLE t(j mysql_json); ERROR HY000: 'MYSQL_JSON' is not allowed in this context CREATE TABLE IF NOT EXISTS t(j mysql_json); ERROR HY000: 'MYSQL_JSON' is not allowed in this context CREATE OR REPLACE TABLE t(j mysql_json); ERROR HY000: 'MYSQL_JSON' is not allowed in this context CREATE TEMPORARY TABLE t(j mysql_json); ERROR HY000: 'MYSQL_JSON' is not allowed in this context CREATE TABLE t1 (a TEXT); ALTER TABLE t1 MODIFY a mysql_json; ERROR HY000: 'MYSQL_JSON' is not allowed in this context DROP TABLE t1; CREATE FUNCTION f1() RETURNS mysql_json RETURN NULL; ERROR HY000: 'MYSQL_JSON' is not allowed in this context CREATE FUNCTION f1(a mysql_json) RETURNS INT RETURN 0; ERROR HY000: 'MYSQL_JSON' is not allowed in this context CREATE PROCEDURE p1() BEGIN DECLARE a mysql_json; END; $$ ERROR HY000: 'MYSQL_JSON' is not allowed in this context # # End of 10.5 tests #