Debian Codegraph @

mysql-test/suite/x/r/create_alter_sql.result

raw
1 RUN DROP DATABASE IF EXISTS xplugintest
2
3 0 rows affected
4 RUN CREATE DATABASE xplugintest
5
6 1 rows affected
7 RUN USE xplugintest
8
9 0 rows affected
10 RUN CREATE TABLE t1 ( col1 INT , col2 FLOAT(7,4), col3 REAL(7,4) , col4 VARCHAR(20) , col5 BLOB )
11
12 0 rows affected
13 RUN CREATE TABLE t2 ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB , col6 JSON )
14
15 0 rows affected
16 RUN CREATE TEMPORARY TABLE t3 ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB , col6 JSON )
17
18 0 rows affected
19 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) )
20
21 0 rows affected
22 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 )
23
24 0 rows affected
25 RUN CREATE TABLE t8 ( col1 INT REFERENCES t7(col1), col6 JSON )
26
27 0 rows affected
28 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) )
29
30 0 rows affected
31 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
32
33 0 rows affected
34 RUN SET @tabname = 't11'
35
36 0 rows affected
37 RUN SET @stmt1 = CONCAT('CREATE TABLE ',@tabname ,' ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB , col6 JSON)')
38
39 0 rows affected
40 RUN PREPARE stmt2 FROM @stmt1
41
42 0 rows affected
43 Statement prepared
44 RUN EXECUTE stmt2
45
46 0 rows affected
47 RUN DROP TABLE t11
48
49 0 rows affected
50 RUN EXECUTE stmt2
51
52 0 rows affected
53 RUN DEALLOCATE PREPARE stmt2
54
55 0 rows affected
56 RUN INSERT INTO t1 VALUES (1, 999.10009 , 999.10009 , 'Row one' , REPEAT('t1r1',512))
57
58 1 rows affected
59 RUN INSERT INTO t2 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t2r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
60
61 1 rows affected
62 RUN INSERT INTO t3 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t3r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
63
64 1 rows affected
65 RUN INSERT INTO t6 VALUES (1, '{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
66
67 1 rows affected
68 RUN INSERT INTO t7 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t7r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
69
70 1 rows affected
71 RUN INSERT INTO t8 VALUES (1, '{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
72
73 1 rows affected
74 RUN INSERT INTO t9 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t9r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
75
76 1 rows affected
77 last insert id: 1
78 RUN INSERT INTO t10 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t10r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
79
80 1 rows affected
81 last insert id: 1
82 RUN INSERT INTO t11 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t10r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' )
83
84 1 rows affected
85 RUN CREATE TABLE t4 LIKE t2
86
87 0 rows affected
88 RUN CREATE TABLE t5 SELECT col1,col6 FROM t2
89
90 1 rows affected
91 Records: 1 Duplicates: 0 Warnings: 0
92 RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10) FROM t1
93 col1 col2 col3 col4 SUBSTR(col5,1,10)
94 1 999.1001 999.1001 Row one t1r1t1r1t1
95 0 rows affected
96 RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t2
97 col1 col2 col3 col4 SUBSTR(col5,1,10) col6
98 1 999.1001 999.1001 Row one t2r1t2r1t2 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
99 0 rows affected
100 RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t3
101 col1 col2 col3 col4 SUBSTR(col5,1,10) col6
102 1 999.1001 999.1001 Row one t3r1t3r1t3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
103 0 rows affected
104 RUN SELECT * FROM t4
105 col1 col2 col3 col4 col5 col6
106 0 rows affected
107 RUN SELECT * FROM t5
108 col1 col6
109 1 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
110 0 rows affected
111 RUN SELECT * FROM t6
112 val col2
113 1 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
114 0 rows affected
115 RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t7
116 col1 col2 col3 col4 SUBSTR(col5,1,10) col6
117 1 999.1001 999.1001 Row one t7r1t7r1t7 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
118 0 rows affected
119 RUN SELECT * FROM t8
120 col1 col6
121 1 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
122 0 rows affected
123 RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t9
124 col1 col2 col3 col4 SUBSTR(col5,1,10) col6
125 1 999.1001 999.1001 Row one t9r1t9r1t9 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
126 0 rows affected
127 RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t10
128 col1 col2 col3 col4 SUBSTR(col5,1,10) col6
129 1 999.1001 999.1001 Row one t10r1t10r1 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
130 0 rows affected
131 RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t11
132 col1 col2 col3 col4 SUBSTR(col5,1,10) col6
133 1 999.1001 999.1001 Row one t10r1t10r1 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
134 0 rows affected
135 RUN CREATE TABLE t1 ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB )
136 While executing CREATE TABLE t1 ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB ) :
137 Got expected error: Table 't1' already exists (code 1050)
138 RUN CREATE TABLE t1 ( col1 INVALID_DATA_TYPE , col2 JSON)
139 While executing CREATE TABLE t1 ( col1 INVALID_DATA_TYPE , col2 JSON) :
140 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)
141 RUN CREATE TABLE t1_ ( col1 INT NULL PRIMARY KEY , col2 JSON)
142 While executing CREATE TABLE t1_ ( col1 INT NULL PRIMARY KEY , col2 JSON) :
143 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)
144 RUN CREATE TABLE t1_ ( col1 INT NULL , col2 JSON,PRIMARY KEY(col3))
145 While executing CREATE TABLE t1_ ( col1 INT NULL , col2 JSON,PRIMARY KEY(col3)) :
146 Got expected error: Key column 'col3' doesn't exist in table (code 1072)
147 RUN CREATE TABLE t1_ ( col1 INT NULL , col2 JSON) ENGINE=INVALID
148 While executing CREATE TABLE t1_ ( col1 INT NULL , col2 JSON) ENGINE=INVALID:
149 Got expected error: Unknown storage engine 'INVALID' (code 1286)
150 RUN LOCK TABLES t1 READ
151
152 0 rows affected
153 RUN CREATE TABLE t2 ( col1 INT )
154 While executing CREATE TABLE t2 ( col1 INT ):
155 Got expected error: Table 't2' was not locked with LOCK TABLES (code 1100)
156 RUN UNLOCK TABLES
157
158 0 rows affected
159 RUN ALTER TABLE t2 ADD COLUMN (col7 JSON),
160 ADD COLUMN (col8 JSON DEFAULT NULL),
161 ADD COLUMN (col9 VARCHAR(100) DEFAULT 'testdata'),
162 ADD INDEX `idx9` (col9),
163 ADD FULLTEXT INDEX `idx_fulltext` (col9)
164
165 0 rows affected
166 Records: 0 Duplicates: 0 Warnings: 1
167 RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6,col7,col8,col9 FROM t2
168 col1 col2 col3 col4 SUBSTR(col5,1,10) col6 col7 col8 col9
169 1 999.1001 999.1001 Row one t2r1t2r1t2 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} null null testdata
170 0 rows affected
171 RUN ALTER TABLE t2 DROP INDEX `idx_fulltext` , ALGORITHM=COPY
172
173 1 rows affected
174 Records: 1 Duplicates: 0 Warnings: 0
175 RUN ALTER TABLE t2 DROP INDEX `idx9` , ALGORITHM=INPLACE
176
177 0 rows affected
178 Records: 0 Duplicates: 0 Warnings: 0
179 RUN ALTER TABLE t2 DROP COLUMN col7 , ALGORITHM=INPLACE
180
181 0 rows affected
182 Records: 0 Duplicates: 0 Warnings: 0
183 RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6,col8,col9 FROM t2
184 col1 col2 col3 col4 SUBSTR(col5,1,10) col6 col8 col9
185 1 999.1001 999.1001 Row one t2r1t2r1t2 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} null testdata
186 0 rows affected
187 RUN ALTER TABLE t6 REMOVE PARTITIONING
188
189 1 rows affected
190 Records: 1 Duplicates: 0 Warnings: 0
191 RUN SHOW CREATE TABLE t2
192 Table Create Table
193 t2 CREATE TABLE `t2` (
194 `col1` int DEFAULT NULL,
195 `col2` double(7,4) DEFAULT NULL,
196 `col3` decimal(7,4) DEFAULT NULL,
197 `col4` varchar(20) DEFAULT NULL,
198 `col5` blob,
199 `col6` json DEFAULT NULL,
200 `col8` json DEFAULT NULL,
201 `col9` varchar(100) DEFAULT 'testdata'
202 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
203 0 rows affected
204 RUN SELECT * FROM t6
205 val col2
206 1 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]}
207 0 rows affected
208 RUN CREATE INDEX `idx9` ON t2 ( col9(50) ) USING BTREE ALGORITHM=DEFAULT LOCK=SHARED
209
210 0 rows affected
211 Records: 0 Duplicates: 0 Warnings: 0
212 RUN CREATE UNIQUE INDEX `idx5` ON t2 (col9(50))
213
214 0 rows affected
215 Records: 0 Duplicates: 0 Warnings: 0
216 RUN CREATE FULLTEXT INDEX `idx_fulltext` ON t2 (col9)
217
218 0 rows affected
219 Records: 0 Duplicates: 0 Warnings: 1
220 RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6,col8,col9 FROM t2
221 col1 col2 col3 col4 SUBSTR(col5,1,10) col6 col8 col9
222 1 999.1001 999.1001 Row one t2r1t2r1t2 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} null testdata
223 0 rows affected
224 RUN ALTER TABLE t2 ADD PRIMARY KEY (col1)
225
226 1 rows affected
227 Records: 1 Duplicates: 0 Warnings: 0
228 RUN DROP INDEX `idx9` ON t2 ALGORITHM=DEFAULT LOCK=SHARED
229
230 0 rows affected
231 Records: 0 Duplicates: 0 Warnings: 0
232 RUN DROP INDEX `idx5` ON t2
233
234 0 rows affected
235 Records: 0 Duplicates: 0 Warnings: 0
236 RUN DROP INDEX `PRIMARY` ON t2
237
238 1 rows affected
239 Records: 1 Duplicates: 0 Warnings: 0
240 RUN SHOW CREATE TABLE t2
241 Table Create Table
242 t2 CREATE TABLE `t2` (
243 `col1` int NOT NULL,
244 `col2` double(7,4) DEFAULT NULL,
245 `col3` decimal(7,4) DEFAULT NULL,
246 `col4` varchar(20) DEFAULT NULL,
247 `col5` blob,
248 `col6` json DEFAULT NULL,
249 `col8` json DEFAULT NULL,
250 `col9` varchar(100) DEFAULT 'testdata',
251 FULLTEXT KEY `idx_fulltext` (`col9`)
252 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
253 0 rows affected
254 RUN DROP DATABASE IF EXISTS xplugintest
255
256 10 rows affected
257 Mysqlx.Ok {
258 msg: "bye!"
259 }
260 ok