Gecko [ www.jdcpatala.org ]

Name	Size	Permission
json_agg.result	34.15 KB	-rw-r--r--
json_client_innodb.result	1.29 KB	-rw-r--r--
json_client_ndb.result	1.43 KB	-rw-r--r--
json_conversions.result	1.57 MB	-rw-r--r--
json_ddl_innodb.result	2.86 KB	-rw-r--r--
json_ddl_ndb.result	3.54 KB	-rw-r--r--
json_fulltext_innodb.result	7 KB	-rw-r--r--
json_functions_innodb.result	260.05 KB	-rw-r--r--
json_functions_ndb.result	271.62 KB	-rw-r--r--
json_gcol_innodb.result	10.8 KB	-rw-r--r--
json_group_concat_innodb.resul...	3.02 KB	-rw-r--r--
json_group_concat_ndb.result	3.09 KB	-rw-r--r--
json_innodb.result	8.52 KB	-rw-r--r--
json_insert_innodb.result	71.19 KB	-rw-r--r--
json_insert_ndb.result	71.59 KB	-rw-r--r--
json_no_table.result	174.17 KB	-rw-r--r--

Code Editor : json_ddl_innodb.result

set default_storage_engine=innodb;
Verify that JSON columns do not support indexes on create table
CREATE TABLE bad(i INT, j1bad JSON NOT NULL PRIMARY KEY);
ERROR 42000: JSON column 'j1bad' cannot be used in key specification.
CREATE TABLE bad(i INT, j1bad JSON NOT NULL, INDEX j1badindex (j1bad));
ERROR 42000: JSON column 'j1bad' cannot be used in key specification.
CREATE TABLE bad(i INT, j1bad JSON NOT NULL, UNIQUE INDEX j1badindex (j1bad));
ERROR 42000: JSON column 'j1bad' cannot be used in key specification.
CREATE TABLE bad(i INT, j1bad JSON NOT NULL, UNIQUE INDEX j1badindex (j1bad) USING HASH);
ERROR 42000: JSON column 'j1bad' cannot be used in key specification.
CREATE TABLE t1(i INT PRIMARY KEY);
ALTER TABLE t1 ADD COLUMN j1 JSON;
ALTER TABLE t1 ADD COLUMN j2 JSON NOT NULL;
CREATE INDEX t1_idx_j ON t1(j1);
ERROR 42000: JSON column 'j1' cannot be used in key specification.
CREATE INDEX t1_idx_i_j ON t1(i, j1);
ERROR 42000: JSON column 'j1' cannot be used in key specification.
CREATE INDEX t1_idx_j_i ON t1(j1, i);
ERROR 42000: JSON column 'j1' cannot be used in key specification.
CREATE INDEX t1_idx_i ON t1(i);
DROP INDEX t1_idx_i ON t1;
DROP TABLE t1;
Verify that JSON columns do not support indexes on alter table
CREATE TABLE t2(i INT PRIMARY KEY, j1 JSON, j2 JSON NOT NULL);
ALTER TABLE t2 ADD INDEX j1badindex (j1);
ERROR 42000: JSON column 'j1' cannot be used in key specification.
ALTER TABLE t2 ADD UNIQUE INDEX j1badindex (j1);
ERROR 42000: JSON column 'j1' cannot be used in key specification.
ALTER TABLE t2 ADD UNIQUE INDEX j1badindex (j1) USING HASH;
ERROR 42000: JSON column 'j1' cannot be used in key specification.
Verify that JSON columns can be dropped
ALTER TABLE t2 DROP COLUMN j1;
ALTER TABLE t2 DROP COLUMN j2;
DROP TABLE t2;
CREATE TABLE json(json int);
INSERT INTO json(json) VALUES (1);
SELECT json FROM json;
json
1
DROP TABLE json;
CREATE PROCEDURE p()
BEGIN
json: LOOP
LEAVE json;
END LOOP json;
END|
CALL p();
DROP PROCEDURE p;
#
#  Bug#22278524: ALTER TABLE SOMETIMES CONVERTS TEXT TO JSON WITHOUT
#                SYNTAX CHECKING
#
CREATE TABLE t1(txt TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);
INSERT INTO t1 VALUES ('not JSON');
ALTER TABLE t1 MODIFY COLUMN txt JSON;
ERROR 22032: Invalid JSON text: "Invalid value." at position 1 in value for column '#sql-temporary.txt'.
SELECT * FROM t1;
txt
not JSON
CREATE TABLE t2(j JSON);
INSERT INTO t2 VALUES (JSON_OBJECT('a', 'b'));
ALTER TABLE t2 MODIFY COLUMN j TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
SELECT * FROM t2;
j
{"a": "b"}
CREATE TABLE t3 (j JSON);
INSERT INTO t3 VALUES (JSON_OBJECT('a', 'b'));
CREATE TABLE t4 AS SELECT UPPER(j) AS jj FROM t3;
INSERT INTO t4 VALUES ('not JSON');
ALTER TABLE t4 MODIFY COLUMN jj JSON;
ERROR 22032: Invalid JSON text: "Invalid value." at position 1 in value for column '#sql-temporary.jj'.
SELECT * FROM t4 order by jj;
jj
not JSON
{"A": "B"}
DROP TABLE t1, t2, t3, t4;

${this.title}

Code Editor : json_ddl_innodb.result