1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
|
#
# 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
#
|
