MDEV-18323 Convert MySQL JSON type to MariaDB TEXT in mysql_upgrade

This patch solves two key problems.
1. There is a type number clash between MySQL and MariaDB. The number
   245, used for MariaDB Virtual Fields is the same as MySQL's JSON.
   This leads to corrupt FRM errors if unhandled. The code properly
   checks frm table version number and if it matches 5.7+ (until 10.0+)
   it will assume it is dealing with a MySQL table with the JSON
   datatype.
2. MySQL JSON datatype uses a proprietary format to pack JSON data. The
   patch introduces a datatype plugin which parses the format and convers
   it to its string representation.

The intended conversion path is to only use the JSON datatype within
ALTER TABLE <table> FORCE, to force a table recreate. This happens
during mysql_upgrade or via a direct ALTER TABLE <table> FORCE.

1 files changed, 171 insertions, 0 deletions

diff --git a/mysql-test/main/mysql_json_table_recreate.result b/mysql-test/main/mysql_json_table_recreate.result
new file mode 100644
index 00000000000..3dfc6d7a259
--- /dev/null
+++ b/mysql-test/main/mysql_json_table_recreate.result
@@ -0,0 +1,171 @@
+#
+# 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
+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!
+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) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `expected` longtext COLLATE utf8mb4_unicode_ci 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;