#
# 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!
select table_name, table_comment from information_schema.tables where table_schema='test' and table_comment!='VIEW';
table_name	table_comment
mysql_json_test	Table rebuild required. Please do "ALTER TABLE `test.mysql_json_test` FORCE" or dump/reload to fix it!
mysql_json_test_big	Table rebuild required. Please do "ALTER TABLE `test.mysql_json_test_big` FORCE" or dump/reload to fix it!
tempty	Table rebuild required. Please do "ALTER TABLE `test.tempty` FORCE" or dump/reload to fix it!
Warnings:
Warning	1707	Table rebuild required. Please do "ALTER TABLE `test.mysql_json_test_big` FORCE" or dump/reload to fix it!
Warning	1707	Table rebuild required. Please do "ALTER TABLE `test.mysql_json_test` FORCE" or dump/reload to fix it!
Warning	1707	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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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
#