Debian Codegraph @

mysql-test/suite/json/inc/json_insert.inc

raw
1 --echo # Test of the JSON data type.
2 --echo # ----------------------------------------------------------------------
3
4 SET NAMES utf8;
5
6 CREATE TABLE t1 (i INT, j JSON) CHARSET utf8mb4;
7 SHOW CREATE TABLE t1;
8 INSERT INTO t1 VALUES (0, NULL);
9 INSERT INTO t1 VALUES (1, '{"a": 2}');
10 INSERT INTO t1 VALUES (2, '[1,2]');
11 INSERT INTO t1 VALUES (3, '{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}');
12 INSERT INTO t1 VALUES (4, '["here", ["I", "am"], "!!!"]');
13 INSERT INTO t1 VALUES (5, '"scalar string"');
14 INSERT INTO t1 VALUES (6, 'true');
15 INSERT INTO t1 VALUES (7, 'false');
16 INSERT INTO t1 VALUES (8, 'null');
17 INSERT INTO t1 VALUES (9, '-1');
18 INSERT INTO t1 VALUES (10, CAST(CAST(1 AS UNSIGNED) AS JSON));
19 INSERT INTO t1 VALUES (11, '32767');
20 INSERT INTO t1 VALUES (12, '32768');
21 INSERT INTO t1 VALUES (13, '-32768');
22 INSERT INTO t1 VALUES (14, '-32769');
23 INSERT INTO t1 VALUES (15, '2147483647');
24 INSERT INTO t1 VALUES (16, '2147483648');
25 INSERT INTO t1 VALUES (17, '-2147483648');
26 INSERT INTO t1 VALUES (18, '-2147483649');
27 INSERT INTO t1 VALUES (19, '18446744073709551615');
28 INSERT INTO t1 VALUES (20, '18446744073709551616');
29 INSERT INTO t1 VALUES (21, '3.14');
30 INSERT INTO t1 VALUES (22, '{}');
31 INSERT INTO t1 VALUES (23, '[]');
32 INSERT INTO t1 VALUES (24, CAST(CAST('2015-01-15 23:24:25' AS DATETIME) AS JSON));
33 INSERT INTO t1 VALUES (25, CAST(CAST('23:24:25' AS TIME) AS JSON));
34 INSERT INTO t1 VALUES (26, CAST(CAST('2015-01-15' AS DATE) AS JSON));
35 INSERT INTO t1 VALUES (27, CAST(TIMESTAMP'2015-01-15 23:24:25' AS JSON));
36 INSERT INTO t1 VALUES (28, CAST(ST_GeomFromText('POINT(1 1)') AS JSON));
37 # auto-convert to utf8mb4
38 INSERT INTO t1 VALUES (29, CAST('[]' AS CHAR CHARACTER SET 'ascii'));
39 INSERT INTO t1 VALUES (30, CAST(x'cafe' AS JSON));
40 INSERT INTO t1 VALUES (31, CAST(x'cafebabe' AS JSON));
41
42 # Maximum allowed key length is 64k-1
43 INSERT INTO t1 VALUES (100, CONCAT('{"', REPEAT('a', 64 * 1024 - 1), '":123}'));
44 --error ER_JSON_KEY_TOO_BIG
45 INSERT INTO t1 VALUES (101, CONCAT('{"', REPEAT('a', 64 * 1024), '":123}'));
46 --error ER_JSON_KEY_TOO_BIG
47 INSERT INTO t1 VALUES (102, CAST(CONCAT('{"', REPEAT('a', 64 * 1024), '":123}') AS JSON));
48
49 SELECT i, LENGTH(j), j FROM t1 ORDER BY i;
50
51 CREATE TABLE auxtbl(ts TIMESTAMP);
52 INSERT INTO auxtbl VALUES ('2015-02-24 18:52:00');
53
54 --error ER_INVALID_JSON_TEXT
55 INSERT INTO t1(j) VALUES ('');
56 --error ER_INVALID_JSON_TEXT
57 INSERT INTO t1(j) VALUES ('[');
58 --error ER_INVALID_JSON_CHARSET
59 INSERT INTO t1(j) VALUES (x'cafebabe');
60 --error ER_INVALID_JSON_CHARSET
61 INSERT INTO t1(j) VALUES (ST_GeomFromText('POINT(1 1)'));
62 --error ER_INVALID_JSON_TEXT
63 INSERT INTO t1(j) VALUES (-1);
64 --error ER_INVALID_JSON_TEXT
65 INSERT INTO t1(j) VALUES (CAST(1 AS UNSIGNED));
66 --error ER_INVALID_JSON_TEXT
67 INSERT INTO t1(j) VALUES (3.14);
68 --error ER_INVALID_JSON_TEXT
69 INSERT INTO t1(j) VALUES (3.14E30);
70 --error ER_INVALID_JSON_TEXT
71 INSERT INTO t1(j) VALUES (PI());
72 --error ER_INVALID_JSON_TEXT
73 INSERT INTO t1(j) select ts from auxtbl;
74 DROP TABLE auxtbl;
75 --error ER_INVALID_JSON_TEXT
76 INSERT INTO t1(j) VALUES (CAST('15:52:00' as TIME));
77 --error ER_INVALID_JSON_TEXT
78 INSERT INTO t1(j) VALUES (CAST('2015-02-24' as DATE));
79 --error ER_INVALID_JSON_TEXT
80 INSERT INTO t1(j) VALUES (TIMESTAMP '2015-02-24 15:52:00');
81
82 --echo # ----------------------------------------------------------------------
83 --echo # INSERT using prepared statement (PS)
84 --echo # ----------------------------------------------------------------------
85 create table pt1(j json);
86 create table pt2(j json);
87
88 set @a=123;
89 set @b='123';
90 set @c=3.14;
91 set @d=3.14E1;
92 set @e='"123"';
93 set @f='2010-12-01 18:44:25';
94 set @g=null;
95
96 prepare ps1 from 'insert into pt1 values (cast(? as json))';
97 execute ps1 using @a;
98
99 prepare ps1 from 'insert into pt1 values (cast(? as json))';
100 execute ps1 using @b;
101
102 prepare ps1 from 'insert into pt1 values (cast(? as json))';
103 execute ps1 using @c;
104
105 prepare ps1 from 'insert into pt1 values (cast(? as json))';
106 execute ps1 using @d;
107
108 prepare ps1 from 'insert into pt1 values (cast(? as json))';
109 execute ps1 using @e;
110
111 prepare ps1 from 'insert into pt1 values (cast(? as json))';
112 --error ER_INVALID_JSON_TEXT_IN_PARAM
113 execute ps1 using @f;
114
115 prepare ps1 from 'insert into pt1 values (cast(? as json))';
116 execute ps1 using @g;
117
118 prepare ps2 from 'insert into pt2 values (?)';
119 --error ER_INVALID_JSON_TEXT
120 execute ps2 using @a;
121
122 prepare ps2 from 'insert into pt2 values (?)';
123 execute ps2 using @b;
124
125 prepare ps2 from 'insert into pt2 values (?)';
126 --error ER_INVALID_JSON_TEXT
127 execute ps2 using @c;
128
129 prepare ps2 from 'insert into pt2 values (?)';
130 --error ER_INVALID_JSON_TEXT
131 execute ps2 using @d;
132
133 prepare ps2 from 'insert into pt2 values (?)';
134 execute ps2 using @e;
135
136 prepare ps2 from 'insert into pt2 values (?)';
137 --error ER_INVALID_JSON_TEXT
138 execute ps2 using @f;
139
140 prepare ps2 from 'insert into pt2 values (?)';
141 execute ps2 using @g;
142
143 select json_type(j), j from pt1 order by j;
144 select json_type(j), j from pt2 order by j;
145
146 delete from pt1;
147 delete from pt2;
148
149 # Check different character sets
150 set @string_utf8=CONVERT(x'616263C3A6C3B8C3A578797A' USING utf8mb4);
151 set @json_utf8=CONCAT('{"label" : "', @string_utf8, '"}');
152 set @json_latin1=CONVERT(@json_utf8 USING LATIN1);
153 select @string_utf8, @json_utf8, @json_latin1;
154
155 execute ps1 using @json_utf8;
156 execute ps1 using @json_latin1;
157 execute ps2 using @json_utf8;
158 execute ps2 using @json_latin1;
159
160 set @string_ucs2=_ucs2 0x0627064A0631062706460650;
161 # What to expect when reading from the JSON column:
162 select HEX(CONVERT(@string_ucs2 USING utf8mb4));
163 set @json_ucs2=CONCAT('{"label" : "', @string_ucs2, '"}');
164 # Check it's a UCS2 string:
165 select collation(@json_ucs2);
166
167 # insert:
168 execute ps1 using @json_ucs2;
169 execute ps2 using @json_ucs2;
170
171 # and read:
172 select hex(j->>"$.label") from pt1 order by (j->>"$.label");
173 select hex(j->>"$.label") from pt2 order by (j->>"$.label");
174
175 drop prepare ps1;
176 drop prepare ps2;
177 drop table pt1;
178 drop table pt2;
179 drop table t1;
180
181 --echo #
182 --echo # Bug#26867509: JSON_OBJECT CREATES INVALID JSON CODE
183 --echo #
184
185 CREATE TABLE t1(i INT, KEY(i));
186 INSERT INTO t1 VALUES (1), (2), (3);
187 CREATE TABLE t2(i INT, vc VARCHAR(100));
188 INSERT INTO t2 SELECT i, JSON_ARRAY(i) FROM t1 GROUP BY i;
189 SELECT * FROM t2 ORDER BY i;
190 DROP TABLE t1, t2;