| 1 |
set default_storage_engine=innodb; |
| 2 |
Verify that JSON columns do not support indexes on create table |
| 3 |
CREATE TABLE bad(i INT, j1bad JSON NOT NULL PRIMARY KEY); |
| 4 |
ERROR 42000: JSON column 'j1bad' supports indexing only via generated columns on a specified JSON path. |
| 5 |
CREATE TABLE bad(i INT, j1bad JSON NOT NULL, INDEX j1badindex (j1bad)); |
| 6 |
ERROR 42000: JSON column 'j1bad' supports indexing only via generated columns on a specified JSON path. |
| 7 |
CREATE TABLE bad(i INT, j1bad JSON NOT NULL, UNIQUE INDEX j1badindex (j1bad)); |
| 8 |
ERROR 42000: JSON column 'j1bad' supports indexing only via generated columns on a specified JSON path. |
| 9 |
CREATE TABLE bad(i INT, j1bad JSON NOT NULL, UNIQUE INDEX j1badindex (j1bad) USING HASH); |
| 10 |
ERROR 42000: JSON column 'j1bad' supports indexing only via generated columns on a specified JSON path. |
| 11 |
CREATE TABLE t1(i INT PRIMARY KEY); |
| 12 |
ALTER TABLE t1 ADD COLUMN j1 JSON; |
| 13 |
ALTER TABLE t1 ADD COLUMN j2 JSON NOT NULL; |
| 14 |
CREATE INDEX t1_idx_j ON t1(j1); |
| 15 |
ERROR 42000: JSON column 'j1' supports indexing only via generated columns on a specified JSON path. |
| 16 |
CREATE INDEX t1_idx_i_j ON t1(i, j1); |
| 17 |
ERROR 42000: JSON column 'j1' supports indexing only via generated columns on a specified JSON path. |
| 18 |
CREATE INDEX t1_idx_j_i ON t1(j1, i); |
| 19 |
ERROR 42000: JSON column 'j1' supports indexing only via generated columns on a specified JSON path. |
| 20 |
CREATE INDEX t1_idx_i ON t1(i); |
| 21 |
DROP INDEX t1_idx_i ON t1; |
| 22 |
DROP TABLE t1; |
| 23 |
Verify that JSON columns do not support indexes on alter table |
| 24 |
CREATE TABLE t2(i INT PRIMARY KEY, j1 JSON, j2 JSON NOT NULL); |
| 25 |
ALTER TABLE t2 ADD INDEX j1badindex (j1); |
| 26 |
ERROR 42000: JSON column 'j1' supports indexing only via generated columns on a specified JSON path. |
| 27 |
ALTER TABLE t2 ADD UNIQUE INDEX j1badindex (j1); |
| 28 |
ERROR 42000: JSON column 'j1' supports indexing only via generated columns on a specified JSON path. |
| 29 |
ALTER TABLE t2 ADD UNIQUE INDEX j1badindex (j1) USING HASH; |
| 30 |
ERROR 42000: JSON column 'j1' supports indexing only via generated columns on a specified JSON path. |
| 31 |
Verify that JSON columns can be dropped |
| 32 |
ALTER TABLE t2 DROP COLUMN j1; |
| 33 |
ALTER TABLE t2 DROP COLUMN j2; |
| 34 |
DROP TABLE t2; |
| 35 |
CREATE TABLE json(json int); |
| 36 |
INSERT INTO json(json) VALUES (1); |
| 37 |
SELECT json FROM json; |
| 38 |
json |
| 39 |
1 |
| 40 |
DROP TABLE json; |
| 41 |
CREATE PROCEDURE p() |
| 42 |
BEGIN |
| 43 |
json: LOOP |
| 44 |
LEAVE json; |
| 45 |
END LOOP json; |
| 46 |
END| |
| 47 |
CALL p(); |
| 48 |
DROP PROCEDURE p; |
| 49 |
# |
| 50 |
# Bug#22278524: ALTER TABLE SOMETIMES CONVERTS TEXT TO JSON WITHOUT |
| 51 |
# SYNTAX CHECKING |
| 52 |
# |
| 53 |
CREATE TABLE t1(txt TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin); |
| 54 |
INSERT INTO t1 VALUES ('not JSON'); |
| 55 |
ALTER TABLE t1 MODIFY COLUMN txt JSON; |
| 56 |
ERROR 22032: Invalid JSON text: "Invalid value." at position 1 in value for column '#sql-temporary.txt'. |
| 57 |
SELECT * FROM t1; |
| 58 |
txt |
| 59 |
not JSON |
| 60 |
CREATE TABLE t2(j JSON); |
| 61 |
INSERT INTO t2 VALUES (JSON_OBJECT('a', 'b')); |
| 62 |
ALTER TABLE t2 MODIFY COLUMN j TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; |
| 63 |
SELECT * FROM t2; |
| 64 |
j |
| 65 |
{"a": "b"} |
| 66 |
CREATE TABLE t3 (j JSON); |
| 67 |
INSERT INTO t3 VALUES (JSON_OBJECT('a', 'b')); |
| 68 |
CREATE TABLE t4 AS SELECT UPPER(j) AS jj FROM t3; |
| 69 |
INSERT INTO t4 VALUES ('not JSON'); |
| 70 |
ALTER TABLE t4 MODIFY COLUMN jj JSON; |
| 71 |
ERROR 22032: Invalid JSON text: "Invalid value." at position 1 in value for column '#sql-temporary.jj'. |
| 72 |
SELECT * FROM t4 order by jj; |
| 73 |
jj |
| 74 |
not JSON |
| 75 |
{"A": "B"} |
| 76 |
DROP TABLE t1, t2, t3, t4; |
| 77 |
# |
| 78 |
# Bug#23535703: WRONG TABLE CHECKSUM ON TABLES WITH JSON COLUMNS |
| 79 |
# |
| 80 |
CREATE TABLE t(j JSON); |
| 81 |
INSERT INTO t VALUES ('[1,2,3]'); |
| 82 |
CHECKSUM TABLE t EXTENDED; |
| 83 |
Table Checksum |
| 84 |
test.t 3005741875 |
| 85 |
DROP TABLE t; |