Debian Codegraph @

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

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