Debian Codegraph @

mysql-test/suite/x/t/create_alter_sql.test

raw
1 ########### ../t/create_alter_sql.test #############
2 ## #
3 ## This test runs aims to run CREATE statement varaiant /ALTER/ #
4 ## CREATE INDEX with mysqlxtest client. #
5 ## Test covers #
6 ## - Create statement with supported data type #
7 ## - Create statement syntax for #
8 ## a) plain create table statement #
9 ## b) Create ... like ... #
10 ## c) create ...select .. #
11 ## d) create with column definition #
12 ## e) create with table option #
13 ## - ALTER statement with ADD/DROP column,ADD/DROP INDEX,ALGORITHM #
14 ## options #
15 ## - CREATE/DROP INDEX #
16 ## #
17 #######################################################################
18
19 --source include/xplugin_preamble.inc
20 --source include/xplugin_create_user.inc
21
22 ## Test starts here
23 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
24 -->sql
25 #-- "Creating database for testcase"
26 DROP DATABASE IF EXISTS xplugintest;
27 CREATE DATABASE xplugintest;
28 USE xplugintest;
29 #-- "Create table with different data type (add other when support is added)"
30 CREATE TABLE t1 ( col1 INT , col2 FLOAT(7,4), col3 REAL(7,4) , col4 VARCHAR(20) , col5 BLOB ) ;
31 #-- "Json table"
32 CREATE TABLE t2 ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB , col6 JSON ) ;
33 #-- "Termporary Table"
34 CREATE TEMPORARY TABLE t3 ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB , col6 JSON ) ;
35 #-- "Partition table"
36 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) );
37 #-- "Table with constraint at column_definition"
38 #-- " With Index (Primary , unique) , not null , defaukt constraint"
39 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 ) ;
40 #-- "Table with foreign key constraint"
41 CREATE TABLE t8 ( col1 INT REFERENCES t7(col1), col6 JSON ) ;
42 #-- "Table with constraint"
43 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) ) ;
44 #-- "Table with constraint at table_option"
45 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 ;
46 #-- "Create table using prepared statement"
47 SET @tabname = 't11';
48 SET @stmt1 = CONCAT('CREATE TABLE ',@tabname ,' ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB , col6 JSON)');
49 PREPARE stmt2 FROM @stmt1;
50 EXECUTE stmt2 ;
51 DROP TABLE t11;
52 EXECUTE stmt2 ;
53 DEALLOCATE PREPARE stmt2;
54
55 #-- "Insert row"
56 INSERT INTO t1 VALUES (1, 999.10009 , 999.10009 , 'Row one' , REPEAT('t1r1',512));
57 INSERT INTO t2 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t2r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' );
58 INSERT INTO t3 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t3r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' );
59 INSERT INTO t6 VALUES (1, '{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' );
60 INSERT INTO t7 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t7r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' );
61 INSERT INTO t8 VALUES (1, '{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' );
62 INSERT INTO t9 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t9r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' );
63 INSERT INTO t10 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t10r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' );
64 INSERT INTO t11 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t10r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' );
65
66 #-- "Create table using LIKE"
67 CREATE TABLE t4 LIKE t2;
68 #-- "Create table using SELECT"
69 CREATE TABLE t5 SELECT col1,col6 FROM t2;
70
71
72 #-- "Verify Added row"
73 SELECT col1,col2,col3,col4,SUBSTR(col5,1,10) FROM t1;
74 SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t2;
75 SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t3;
76 SELECT * FROM t4;
77 SELECT * FROM t5;
78 SELECT * FROM t6;
79 SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t7;
80 SELECT * FROM t8;
81 SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t9;
82 SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t10;
83 SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t11;
84
85
86 #-- "Negative cases of create"
87 #-- "Try to create same table"
88 -->endsql
89 -->expecterror 1050
90 -->sql
91 CREATE TABLE t1 ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB ) ;
92 #-- "Incorrect data type"
93 -->endsql
94 -->expecterror 1064
95 -->sql
96 CREATE TABLE t1 ( col1 INVALID_DATA_TYPE , col2 JSON) ;
97 #-- "Incorrect column option"
98 -->endsql
99 -->expecterror 1171
100 -->sql
101 CREATE TABLE t1_ ( col1 INT NULL PRIMARY KEY , col2 JSON) ;
102 #-- "Incorrect constraint"
103 -->endsql
104 -->expecterror 1072
105 -->sql
106 CREATE TABLE t1_ ( col1 INT NULL , col2 JSON,PRIMARY KEY(col3)) ;
107 #-- "Incorrect table option"
108 -->endsql
109 -->expecterror 1286
110 -->sql
111 CREATE TABLE t1_ ( col1 INT NULL , col2 JSON) ENGINE=INVALID;
112 #-- "Can not create table until lock is released"
113 LOCK TABLES t1 READ;
114 -->endsql
115 -->expecterror 1100
116 -->sql
117 CREATE TABLE t2 ( col1 INT );
118 UNLOCK TABLES;
119
120
121
122 #-- "ALTER TABLE commands"
123 ALTER TABLE t2 ADD COLUMN (col7 JSON),
124 ADD COLUMN (col8 JSON DEFAULT NULL),
125 ADD COLUMN (col9 VARCHAR(100) DEFAULT 'testdata'),
126 ADD INDEX `idx9` (col9),
127 ADD FULLTEXT INDEX `idx_fulltext` (col9);
128 SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6,col7,col8,col9 FROM t2;
129 ALTER TABLE t2 DROP INDEX `idx_fulltext` , ALGORITHM=COPY;
130 ALTER TABLE t2 DROP INDEX `idx9` , ALGORITHM=INPLACE;
131 ALTER TABLE t2 DROP COLUMN col7 , ALGORITHM=INPLACE;
132 SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6,col8,col9 FROM t2;
133 ALTER TABLE t6 REMOVE PARTITIONING;
134 SHOW CREATE TABLE t2;
135 SELECT * FROM t6;
136
137 #-- "CREATE INDEX"
138 CREATE INDEX `idx9` ON t2 ( col9(50) ) USING BTREE ALGORITHM=DEFAULT LOCK=SHARED;
139 CREATE UNIQUE INDEX `idx5` ON t2 (col9(50)) ;
140 CREATE FULLTEXT INDEX `idx_fulltext` ON t2 (col9);
141 SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6,col8,col9 FROM t2;
142 ALTER TABLE t2 ADD PRIMARY KEY (col1);
143 DROP INDEX `idx9` ON t2 ALGORITHM=DEFAULT LOCK=SHARED;
144 DROP INDEX `idx5` ON t2 ;
145 DROP INDEX `PRIMARY` ON t2;
146 SHOW CREATE TABLE t2;
147
148 -->endsql
149 ## Cleanup
150 -->sql
151 DROP DATABASE IF EXISTS xplugintest;
152 -->endsql
153 EOF
154
155 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
156
157 ## Cleanup
158 --source ../include/xplugin_cleanup.inc