| 1 |
--echo Verify that JSON columns do not support indexes on create table |
| 2 |
|
| 3 |
--error 3152 |
| 4 |
CREATE TABLE bad(i INT, j1bad JSON NOT NULL PRIMARY KEY); |
| 5 |
--error 3152 |
| 6 |
CREATE TABLE bad(i INT, j1bad JSON NOT NULL, INDEX j1badindex (j1bad)); |
| 7 |
--error 3152 |
| 8 |
CREATE TABLE bad(i INT, j1bad JSON NOT NULL, UNIQUE INDEX j1badindex (j1bad)); |
| 9 |
--error 3152 |
| 10 |
CREATE TABLE bad(i INT, j1bad JSON NOT NULL, UNIQUE INDEX j1badindex (j1bad) USING HASH); |
| 11 |
|
| 12 |
CREATE TABLE t1(i INT PRIMARY KEY); |
| 13 |
ALTER TABLE t1 ADD COLUMN j1 JSON; |
| 14 |
ALTER TABLE t1 ADD COLUMN j2 JSON NOT NULL; |
| 15 |
# A JSON column cannot be used as a key. |
| 16 |
--error ER_JSON_USED_AS_KEY |
| 17 |
CREATE INDEX t1_idx_j ON t1(j1); |
| 18 |
--error ER_JSON_USED_AS_KEY |
| 19 |
CREATE INDEX t1_idx_i_j ON t1(i, j1); |
| 20 |
--error ER_JSON_USED_AS_KEY |
| 21 |
CREATE INDEX t1_idx_j_i ON t1(j1, i); |
| 22 |
CREATE INDEX t1_idx_i ON t1(i); |
| 23 |
DROP INDEX t1_idx_i ON t1; |
| 24 |
|
| 25 |
DROP TABLE t1; |
| 26 |
|
| 27 |
--echo Verify that JSON columns do not support indexes on alter table |
| 28 |
CREATE TABLE t2(i INT PRIMARY KEY, j1 JSON, j2 JSON NOT NULL); |
| 29 |
--error 3152 |
| 30 |
ALTER TABLE t2 ADD INDEX j1badindex (j1); |
| 31 |
--error 3152 |
| 32 |
ALTER TABLE t2 ADD UNIQUE INDEX j1badindex (j1); |
| 33 |
--error 3152 |
| 34 |
ALTER TABLE t2 ADD UNIQUE INDEX j1badindex (j1) USING HASH; |
| 35 |
|
| 36 |
--echo Verify that JSON columns can be dropped |
| 37 |
ALTER TABLE t2 DROP COLUMN j1; |
| 38 |
ALTER TABLE t2 DROP COLUMN j2; |
| 39 |
|
| 40 |
DROP TABLE t2; |
| 41 |
|
| 42 |
# JSON is a non-reserved keyword, so it should be possible to use it |
| 43 |
# as an identifier. |
| 44 |
CREATE TABLE json(json int); |
| 45 |
INSERT INTO json(json) VALUES (1); |
| 46 |
SELECT json FROM json; |
| 47 |
DROP TABLE json; |
| 48 |
|
| 49 |
# And it should be possible to use it as a label in a stored procedure. |
| 50 |
DELIMITER |; |
| 51 |
CREATE PROCEDURE p() |
| 52 |
BEGIN |
| 53 |
json: LOOP |
| 54 |
LEAVE json; |
| 55 |
END LOOP json; |
| 56 |
END| |
| 57 |
DELIMITER ;| |
| 58 |
CALL p(); |
| 59 |
DROP PROCEDURE p; |
| 60 |
|
| 61 |
--echo # |
| 62 |
--echo # Bug#22278524: ALTER TABLE SOMETIMES CONVERTS TEXT TO JSON WITHOUT |
| 63 |
--echo # SYNTAX CHECKING |
| 64 |
--echo # |
| 65 |
CREATE TABLE t1(txt TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin); |
| 66 |
INSERT INTO t1 VALUES ('not JSON'); |
| 67 |
--replace_regex /#sql-[0-9a-f_]*/#sql-temporary/ |
| 68 |
--error ER_INVALID_JSON_TEXT |
| 69 |
ALTER TABLE t1 MODIFY COLUMN txt JSON; |
| 70 |
SELECT * FROM t1; |
| 71 |
CREATE TABLE t2(j JSON); |
| 72 |
INSERT INTO t2 VALUES (JSON_OBJECT('a', 'b')); |
| 73 |
ALTER TABLE t2 MODIFY COLUMN j TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; |
| 74 |
SELECT * FROM t2; |
| 75 |
CREATE TABLE t3 (j JSON); |
| 76 |
INSERT INTO t3 VALUES (JSON_OBJECT('a', 'b')); |
| 77 |
CREATE TABLE t4 AS SELECT UPPER(j) AS jj FROM t3; |
| 78 |
INSERT INTO t4 VALUES ('not JSON'); |
| 79 |
--replace_regex /#sql-[0-9a-f_]*/#sql-temporary/ |
| 80 |
--error ER_INVALID_JSON_TEXT |
| 81 |
ALTER TABLE t4 MODIFY COLUMN jj JSON; |
| 82 |
SELECT * FROM t4 order by jj; |
| 83 |
DROP TABLE t1, t2, t3, t4; |
| 84 |
|
| 85 |
--echo # |
| 86 |
--echo # Bug#23535703: WRONG TABLE CHECKSUM ON TABLES WITH JSON COLUMNS |
| 87 |
--echo # |
| 88 |
CREATE TABLE t(j JSON); |
| 89 |
INSERT INTO t VALUES ('[1,2,3]'); |
| 90 |
CHECKSUM TABLE t EXTENDED; |
| 91 |
DROP TABLE t; |