Debian Codegraph @

mysql-test/suite/json/r/json_ddl_innodb.result

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