| 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; |