261 lines
9.7 KiB
Plaintext
261 lines
9.7 KiB
Plaintext
RUN DROP DATABASE IF EXISTS xplugintest
|
|
|
|
0 rows affected
|
|
RUN CREATE DATABASE xplugintest
|
|
|
|
1 rows affected
|
|
RUN USE xplugintest
|
|
|
|
0 rows affected
|
|
RUN CREATE TABLE t1 ( col1 INT , col2 FLOAT(7,4), col3 REAL(7,4) , col4 VARCHAR(20) , col5 BLOB )
|
|
|
|
0 rows affected
|
|
RUN CREATE TABLE t2 ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB , col6 JSON )
|
|
|
|
0 rows affected
|
|
RUN CREATE TEMPORARY TABLE t3 ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB , col6 JSON )
|
|
|
|
0 rows affected
|
|
RUN CREATE TABLE t6 (val INT , col2 JSON) PARTITION BY LIST(val)( PARTITION mypart1 VALUES IN (1,3,5), PARTITION MyPart2 VALUES IN (2,4,6) )
|
|
|
|
0 rows affected
|
|
RUN CREATE TABLE t7 ( col1 INT PRIMARY KEY , col2 DOUBLE(7,4) UNIQUE KEY, col3 DECIMAL(7,4) NOT NULL COMMENT 'not_null', col4 VARCHAR(20) DEFAULT 'xyz' , col5 BLOB , col6 JSON )
|
|
|
|
0 rows affected
|
|
RUN CREATE TABLE t8 ( col1 INT REFERENCES t7(col1), col6 JSON )
|
|
|
|
0 rows affected
|
|
RUN CREATE TABLE t9 ( col1 INT NOT NULL AUTO_INCREMENT, col2 DOUBLE(7,4) , col3 DECIMAL(7,4) COMMENT 'Test', col4 VARCHAR(20) DEFAULT 'xyz' , col5 BLOB , col6 JSON , PRIMARY KEY (col1), INDEX `idx1` (col2) , UNIQUE KEY `idx2` (col4) )
|
|
|
|
0 rows affected
|
|
RUN CREATE TABLE t10 ( col1 INT NOT NULL AUTO_INCREMENT, col2 DOUBLE(7,4) , col3 DECIMAL(7,4) COMMENT 'Test', col4 VARCHAR(20) DEFAULT 'xyz' , col5 BLOB , col6 JSON , PRIMARY KEY (col1), INDEX `idx1` (col2) , UNIQUE KEY `idx2` (col4) ) ENGINE = Innodb , MAX_ROWS=100
|
|
|
|
0 rows affected
|
|
RUN SET @tabname = 't11'
|
|
|
|
0 rows affected
|
|
RUN SET @stmt1 = CONCAT('CREATE TABLE ',@tabname ,' ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB , col6 JSON)')
|
|
|
|
0 rows affected
|
|
RUN PREPARE stmt2 FROM @stmt1
|
|
|
|
0 rows affected
|
|
Statement prepared
|
|
RUN EXECUTE stmt2
|
|
|
|
0 rows affected
|
|
RUN DROP TABLE t11
|
|
|
|
0 rows affected
|
|
RUN EXECUTE stmt2
|
|
|
|
0 rows affected
|
|
RUN DEALLOCATE PREPARE stmt2
|
|
|
|
0 rows affected
|
|
RUN INSERT INTO t1 VALUES (1, 999.10009 , 999.10009 , 'Row one' , REPEAT('t1r1',512))
|
|
|
|
1 rows affected
|
|
RUN INSERT INTO t2 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t2r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
|
|
|
|
1 rows affected
|
|
RUN INSERT INTO t3 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t3r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
|
|
|
|
1 rows affected
|
|
RUN INSERT INTO t6 VALUES (1, '{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
|
|
|
|
1 rows affected
|
|
RUN INSERT INTO t7 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t7r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
|
|
|
|
1 rows affected
|
|
RUN INSERT INTO t8 VALUES (1, '{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
|
|
|
|
1 rows affected
|
|
RUN INSERT INTO t9 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t9r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
|
|
|
|
1 rows affected
|
|
last insert id: 1
|
|
RUN INSERT INTO t10 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t10r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
|
|
|
|
1 rows affected
|
|
last insert id: 1
|
|
RUN INSERT INTO t11 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t10r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
|
|
|
|
1 rows affected
|
|
RUN CREATE TABLE t4 LIKE t2
|
|
|
|
0 rows affected
|
|
RUN CREATE TABLE t5 SELECT col1,col6 FROM t2
|
|
|
|
1 rows affected
|
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10) FROM t1
|
|
col1 col2 col3 col4 SUBSTR(col5,1,10)
|
|
1 999.1001 999.1001 Row one t1r1t1r1t1
|
|
0 rows affected
|
|
RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t2
|
|
col1 col2 col3 col4 SUBSTR(col5,1,10) col6
|
|
1 999.1001 999.1001 Row one t2r1t2r1t2 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
|
|
0 rows affected
|
|
RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t3
|
|
col1 col2 col3 col4 SUBSTR(col5,1,10) col6
|
|
1 999.1001 999.1001 Row one t3r1t3r1t3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
|
|
0 rows affected
|
|
RUN SELECT * FROM t4
|
|
col1 col2 col3 col4 col5 col6
|
|
0 rows affected
|
|
RUN SELECT * FROM t5
|
|
col1 col6
|
|
1 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
|
|
0 rows affected
|
|
RUN SELECT * FROM t6
|
|
val col2
|
|
1 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
|
|
0 rows affected
|
|
RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t7
|
|
col1 col2 col3 col4 SUBSTR(col5,1,10) col6
|
|
1 999.1001 999.1001 Row one t7r1t7r1t7 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
|
|
0 rows affected
|
|
RUN SELECT * FROM t8
|
|
col1 col6
|
|
1 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
|
|
0 rows affected
|
|
RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t9
|
|
col1 col2 col3 col4 SUBSTR(col5,1,10) col6
|
|
1 999.1001 999.1001 Row one t9r1t9r1t9 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
|
|
0 rows affected
|
|
RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t10
|
|
col1 col2 col3 col4 SUBSTR(col5,1,10) col6
|
|
1 999.1001 999.1001 Row one t10r1t10r1 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
|
|
0 rows affected
|
|
RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t11
|
|
col1 col2 col3 col4 SUBSTR(col5,1,10) col6
|
|
1 999.1001 999.1001 Row one t10r1t10r1 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
|
|
0 rows affected
|
|
RUN CREATE TABLE t1 ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB )
|
|
While executing CREATE TABLE t1 ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB ) :
|
|
Got expected error: Table 't1' already exists (code 1050)
|
|
RUN CREATE TABLE t1 ( col1 INVALID_DATA_TYPE , col2 JSON)
|
|
While executing CREATE TABLE t1 ( col1 INVALID_DATA_TYPE , col2 JSON) :
|
|
Got expected error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INVALID_DATA_TYPE , col2 JSON)' at line 1 (code 1064)
|
|
RUN CREATE TABLE t1_ ( col1 INT NULL PRIMARY KEY , col2 JSON)
|
|
While executing CREATE TABLE t1_ ( col1 INT NULL PRIMARY KEY , col2 JSON) :
|
|
Got expected error: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead (code 1171)
|
|
RUN CREATE TABLE t1_ ( col1 INT NULL , col2 JSON,PRIMARY KEY(col3))
|
|
While executing CREATE TABLE t1_ ( col1 INT NULL , col2 JSON,PRIMARY KEY(col3)) :
|
|
Got expected error: Key column 'col3' doesn't exist in table (code 1072)
|
|
RUN CREATE TABLE t1_ ( col1 INT NULL , col2 JSON) ENGINE=INVALID
|
|
While executing CREATE TABLE t1_ ( col1 INT NULL , col2 JSON) ENGINE=INVALID:
|
|
Got expected error: Unknown storage engine 'INVALID' (code 1286)
|
|
RUN LOCK TABLES t1 READ
|
|
|
|
0 rows affected
|
|
RUN CREATE TABLE t2 ( col1 INT )
|
|
While executing CREATE TABLE t2 ( col1 INT ):
|
|
Got expected error: Table 't2' was not locked with LOCK TABLES (code 1100)
|
|
RUN UNLOCK TABLES
|
|
|
|
0 rows affected
|
|
RUN ALTER TABLE t2 ADD COLUMN (col7 JSON),
|
|
ADD COLUMN (col8 JSON DEFAULT NULL),
|
|
ADD COLUMN (col9 VARCHAR(100) DEFAULT 'testdata'),
|
|
ADD INDEX `idx9` (col9),
|
|
ADD FULLTEXT INDEX `idx_fulltext` (col9)
|
|
|
|
0 rows affected
|
|
Records: 0 Duplicates: 0 Warnings: 1
|
|
RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6,col7,col8,col9 FROM t2
|
|
col1 col2 col3 col4 SUBSTR(col5,1,10) col6 col7 col8 col9
|
|
1 999.1001 999.1001 Row one t2r1t2r1t2 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} null null testdata
|
|
0 rows affected
|
|
RUN ALTER TABLE t2 DROP INDEX `idx_fulltext` , ALGORITHM=COPY
|
|
|
|
1 rows affected
|
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
RUN ALTER TABLE t2 DROP INDEX `idx9` , ALGORITHM=INPLACE
|
|
|
|
0 rows affected
|
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
RUN ALTER TABLE t2 DROP COLUMN col7 , ALGORITHM=INPLACE
|
|
|
|
0 rows affected
|
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6,col8,col9 FROM t2
|
|
col1 col2 col3 col4 SUBSTR(col5,1,10) col6 col8 col9
|
|
1 999.1001 999.1001 Row one t2r1t2r1t2 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} null testdata
|
|
0 rows affected
|
|
RUN ALTER TABLE t6 REMOVE PARTITIONING
|
|
|
|
1 rows affected
|
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
RUN SHOW CREATE TABLE t2
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`col1` int(11) DEFAULT NULL,
|
|
`col2` double(7,4) DEFAULT NULL,
|
|
`col3` decimal(7,4) DEFAULT NULL,
|
|
`col4` varchar(20) DEFAULT NULL,
|
|
`col5` blob,
|
|
`col6` json DEFAULT NULL,
|
|
`col8` json DEFAULT NULL,
|
|
`col9` varchar(100) DEFAULT 'testdata'
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
0 rows affected
|
|
RUN SELECT * FROM t6
|
|
val col2
|
|
1 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
|
|
0 rows affected
|
|
RUN CREATE INDEX `idx9` ON t2 ( col9(50) ) USING BTREE ALGORITHM=DEFAULT LOCK=SHARED
|
|
|
|
0 rows affected
|
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
RUN CREATE UNIQUE INDEX `idx5` ON t2 (col9(50))
|
|
|
|
0 rows affected
|
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
RUN CREATE FULLTEXT INDEX `idx_fulltext` ON t2 (col9)
|
|
|
|
0 rows affected
|
|
Records: 0 Duplicates: 0 Warnings: 1
|
|
RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6,col8,col9 FROM t2
|
|
col1 col2 col3 col4 SUBSTR(col5,1,10) col6 col8 col9
|
|
1 999.1001 999.1001 Row one t2r1t2r1t2 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} null testdata
|
|
0 rows affected
|
|
RUN ALTER TABLE t2 ADD PRIMARY KEY (col1)
|
|
|
|
1 rows affected
|
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
RUN DROP INDEX `idx9` ON t2 ALGORITHM=DEFAULT LOCK=SHARED
|
|
|
|
0 rows affected
|
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
RUN DROP INDEX `idx5` ON t2
|
|
|
|
0 rows affected
|
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
RUN DROP INDEX `PRIMARY` ON t2
|
|
|
|
1 rows affected
|
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
RUN SHOW CREATE TABLE t2
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`col1` int(11) NOT NULL,
|
|
`col2` double(7,4) DEFAULT NULL,
|
|
`col3` decimal(7,4) DEFAULT NULL,
|
|
`col4` varchar(20) DEFAULT NULL,
|
|
`col5` blob,
|
|
`col6` json DEFAULT NULL,
|
|
`col8` json DEFAULT NULL,
|
|
`col9` varchar(100) DEFAULT 'testdata',
|
|
FULLTEXT KEY `idx_fulltext` (`col9`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
0 rows affected
|
|
RUN DROP DATABASE IF EXISTS xplugintest
|
|
|
|
10 rows affected
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|