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
|
--source include/have_utf8.inc
--source include/have_type_mysql_json.inc
--echo #
--echo # The following test takes 2 tables containing a JSON column and attempts
--echo # to repair them.
--echo #
--echo # The tables header is (Description, Expected, Actual), where description
--echo # shows a brief description what the JSON value is testing in the MariaDB
--echo # implementation. Expected is the longtext string and actual is the JSON
--echo # column that needs to be converted to MariaDB's representation of
--echo # LONGTEXT.
--echo #
call mtr.add_suppression("Table rebuild required");
call mtr.add_suppression("is marked as crashed");
call mtr.add_suppression("Checking");
let $MYSQLD_DATADIR= `select @@datadir`;
SET NAMES utf8;
--copy_file std_data/mysql_json/tempty.frm $MYSQLD_DATADIR/test/tempty.frm
--copy_file std_data/mysql_json/tempty.MYI $MYSQLD_DATADIR/test/tempty.MYI
--copy_file std_data/mysql_json/tempty.MYD $MYSQLD_DATADIR/test/tempty.MYD
--copy_file std_data/mysql_json/mysql_json_test.frm $MYSQLD_DATADIR/test/mysql_json_test.frm
--copy_file std_data/mysql_json/mysql_json_test.MYI $MYSQLD_DATADIR/test/mysql_json_test.MYI
--copy_file std_data/mysql_json/mysql_json_test.MYD $MYSQLD_DATADIR/test/mysql_json_test.MYD
--copy_file std_data/mysql_json/mysql_json_test_big.frm $MYSQLD_DATADIR/test/mysql_json_test_big.frm
--copy_file std_data/mysql_json/mysql_json_test_big.MYI $MYSQLD_DATADIR/test/mysql_json_test_big.MYI
--copy_file std_data/mysql_json/mysql_json_test_big.MYD $MYSQLD_DATADIR/test/mysql_json_test_big.MYD
--echo #
--echo # Check that only ALTER TABLE ... FORCE is allowed on a MySQL 5.7 table
--echo # with a JSON column.
--echo #
--error ER_TABLE_NEEDS_REBUILD
show create table tempty;
--error ER_TABLE_NEEDS_REBUILD
select * from tempty;
--sorted_result
select table_name, table_comment from information_schema.tables where table_schema='test' and table_comment!='VIEW';
alter table tempty force;
show create table tempty;
--error ER_TABLE_NEEDS_REBUILD
show create table mysql_json_test;
--error ER_TABLE_NEEDS_REBUILD
select * from mysql_json_test;
--error ER_TABLE_NEEDS_REBUILD
CREATE TABLE t2 AS SELECT * FROM mysql_json_test;
--error ER_NOT_ALLOWED_IN_THIS_CONTEXT
CREATE TABLE t2 (a mysql_json /*new column*/) AS SELECT * FROM mysql_json_test;
--error ER_NOT_ALLOWED_IN_THIS_CONTEXT
CREATE TABLE t2 (actual mysql_json /*existing column*/) AS SELECT * FROM mysql_json_test;
--error ER_TABLE_NEEDS_REBUILD
LOCK TABLES mysql_json_test WRITE;
alter table mysql_json_test force;
--sorted_result
select description, expected, actual, expected = actual from mysql_json_test;
--echo #
--echo # A quick check that all rows match from the original MySQL Table.
--echo #
select count(*) as 'Total_Number_of_Tests',
sum(expected = actual) as 'Succesful_Tests'
from mysql_json_test;
show create table mysql_json_test;
--error ER_TABLE_NEEDS_REBUILD
show create table mysql_json_test_big;
--error ER_TABLE_NEEDS_REBUILD
select * from mysql_json_test_big;
--echo #
--echo # This test checks the long format implementation of MySQL's JSON
--echo # Not printing the actual contents as they are not readable by a human,
--echo # just compare the strings, make sure they match.
--echo #
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;
drop table tempty;
drop table mysql_json_test;
drop table mysql_json_test_big;
--echo #
--echo # MDEV-32790: Output result in show create table
--echo # for mysql_json type should be longtext
--echo #
create table t1(j json);
show create table t1;
drop table t1;
--error ER_NOT_ALLOWED_IN_THIS_CONTEXT
create table t1(j mysql_json);
# `json` type should not have character set and collation other than utf8mb4_bin
--error ER_PARSE_ERROR
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;
# By removing character set from `json` field query should work and
# expand to `longtext` with characterset
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;
drop table testjson;
# `longtext` that is alias can have character set
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;
drop table testjson;
--echo #
--echo # MDEV-32235: mysql_json cannot be used on newly created table
--echo #
--error ER_NOT_ALLOWED_IN_THIS_CONTEXT
CREATE TABLE t(j mysql_json);
--error ER_NOT_ALLOWED_IN_THIS_CONTEXT
CREATE TABLE IF NOT EXISTS t(j mysql_json);
--error ER_NOT_ALLOWED_IN_THIS_CONTEXT
CREATE OR REPLACE TABLE t(j mysql_json);
--error ER_NOT_ALLOWED_IN_THIS_CONTEXT
CREATE TEMPORARY TABLE t(j mysql_json);
CREATE TABLE t1 (a TEXT);
--error ER_NOT_ALLOWED_IN_THIS_CONTEXT
ALTER TABLE t1 MODIFY a mysql_json;
DROP TABLE t1;
--error ER_NOT_ALLOWED_IN_THIS_CONTEXT
CREATE FUNCTION f1() RETURNS mysql_json RETURN NULL;
--error ER_NOT_ALLOWED_IN_THIS_CONTEXT
CREATE FUNCTION f1(a mysql_json) RETURNS INT RETURN 0;
DELIMITER $$;
--error ER_NOT_ALLOWED_IN_THIS_CONTEXT
CREATE PROCEDURE p1()
BEGIN
DECLARE a mysql_json;
END;
$$
DELIMITER ;$$
--echo #
--echo # End of 10.5 tests
--echo #
|
