github.com/dolthub/go-mysql-server@v0.18.0/enginetest/queries/create_table_queries.go (about) 1 // Copyright 2022 Dolthub, Inc. 2 // 3 // Licensed under the Apache License, Version 2.0 (the "License"); 4 // you may not use this file except in compliance with the License. 5 // You may obtain a copy of the License at 6 // 7 // http://www.apache.org/licenses/LICENSE-2.0 8 // 9 // Unless required by applicable law or agreed to in writing, software 10 // distributed under the License is distributed on an "AS IS" BASIS, 11 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 // See the License for the specific language governing permissions and 13 // limitations under the License. 14 15 package queries 16 17 import ( 18 "time" 19 20 "github.com/dolthub/go-mysql-server/sql" 21 "github.com/dolthub/go-mysql-server/sql/types" 22 ) 23 24 var CreateTableQueries = []WriteQueryTest{ 25 { 26 WriteQuery: `create table tableWithComment (pk int) COMMENT 'Table Comments Work!'`, 27 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 28 SelectQuery: "SHOW CREATE TABLE tableWithComment", 29 ExpectedSelect: []sql.Row{{"tableWithComment", "CREATE TABLE `tableWithComment` (\n `pk` int\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin COMMENT='Table Comments Work!'"}}, 30 }, 31 { 32 WriteQuery: `create table tableWithComment (pk int) COMMENT='Table Comments=Still Work'`, 33 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 34 SelectQuery: "SHOW CREATE TABLE tableWithComment", 35 ExpectedSelect: []sql.Row{{"tableWithComment", "CREATE TABLE `tableWithComment` (\n `pk` int\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin COMMENT='Table Comments=Still Work'"}}, 36 }, 37 { 38 WriteQuery: `create table tableWithComment (pk int) COMMENT "~!@ #$ %^ &* ()"`, 39 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 40 SelectQuery: "SHOW CREATE TABLE tableWithComment", 41 ExpectedSelect: []sql.Row{{"tableWithComment", "CREATE TABLE `tableWithComment` (\n `pk` int\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin COMMENT='~!@ #$ %^ &* ()'"}}, 42 }, 43 { 44 WriteQuery: `create table floattypedefs (a float(10), b float(10, 2), c double(10, 2))`, 45 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 46 SelectQuery: "SHOW CREATE TABLE floattypedefs", 47 ExpectedSelect: []sql.Row{sql.Row{"floattypedefs", "CREATE TABLE `floattypedefs` (\n `a` float,\n `b` float,\n `c` double\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 48 }, 49 { 50 WriteQuery: `CREATE TABLE t1 (a INTEGER, b TEXT, c DATE, d TIMESTAMP, e VARCHAR(20), f BLOB NOT NULL, b1 BOOL, b2 BOOLEAN NOT NULL, g DATETIME, h CHAR(40))`, 51 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 52 SelectQuery: "SHOW CREATE TABLE t1", 53 ExpectedSelect: []sql.Row{{"t1", "CREATE TABLE `t1` (\n `a` int,\n `b` text,\n `c` date,\n `d` timestamp,\n `e` varchar(20),\n `f` blob NOT NULL,\n `b1` tinyint(1),\n `b2` tinyint(1) NOT NULL,\n `g` datetime,\n `h` char(40)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 54 }, 55 { 56 WriteQuery: `CREATE TABLE t1 (a INTEGER NOT NULL PRIMARY KEY, b VARCHAR(10) NOT NULL)`, 57 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 58 SelectQuery: "SHOW CREATE TABLE t1", 59 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `a` int NOT NULL,\n `b` varchar(10) NOT NULL,\n PRIMARY KEY (`a`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 60 }, 61 { 62 WriteQuery: `CREATE TABLE t1 (a INTEGER, b TEXT NOT NULL COMMENT 'comment', c bool, primary key (a))`, 63 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 64 SelectQuery: "SHOW CREATE TABLE t1", 65 ExpectedSelect: []sql.Row{{"t1", "CREATE TABLE `t1` (\n `a` int NOT NULL,\n `b` text NOT NULL COMMENT 'comment',\n `c` tinyint(1),\n PRIMARY KEY (`a`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 66 }, 67 { 68 WriteQuery: `CREATE TABLE t1 (a INTEGER, create_time timestamp(6) NOT NULL DEFAULT NOW(6), primary key (a))`, 69 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 70 SelectQuery: "SHOW CREATE TABLE t1", 71 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `a` int NOT NULL,\n `create_time` timestamp(6) NOT NULL DEFAULT (NOW(6)),\n PRIMARY KEY (`a`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 72 }, 73 { 74 WriteQuery: `CREATE TABLE t1 LIKE mytable`, 75 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 76 SelectQuery: "SHOW CREATE TABLE t1", 77 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `i` bigint NOT NULL,\n `s` varchar(20) NOT NULL COMMENT 'column s',\n PRIMARY KEY (`i`),\n KEY `idx_si` (`s`,`i`),\n KEY `mytable_i_s` (`i`,`s`),\n UNIQUE KEY `mytable_s` (`s`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 78 }, 79 { 80 WriteQuery: `CREATE TABLE t1 ( 81 pk bigint primary key, 82 v1 bigint default (2) comment 'hi there', 83 index idx_v1 (v1) comment 'index here' 84 )`, 85 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 86 SelectQuery: "SHOW CREATE TABLE t1", 87 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `pk` bigint NOT NULL,\n `v1` bigint DEFAULT (2) COMMENT 'hi there',\n PRIMARY KEY (`pk`),\n KEY `idx_v1` (`v1`) COMMENT 'index here'\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 88 }, 89 { 90 WriteQuery: `create table t1 like foo.othertable`, 91 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 92 SelectQuery: "SHOW CREATE TABLE t1", 93 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `text` varchar(20) NOT NULL,\n `number` mediumint,\n PRIMARY KEY (`text`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 94 }, 95 { 96 WriteQuery: `CREATE TABLE t1 (a INTEGER NOT NULL PRIMARY KEY, b VARCHAR(10) UNIQUE)`, 97 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 98 SelectQuery: "SHOW CREATE TABLE t1", 99 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `a` int NOT NULL,\n `b` varchar(10),\n PRIMARY KEY (`a`),\n UNIQUE KEY `b` (`b`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 100 }, 101 { 102 WriteQuery: `CREATE TABLE t1 (a INTEGER NOT NULL PRIMARY KEY, b VARCHAR(10) UNIQUE KEY)`, 103 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 104 SelectQuery: "SHOW CREATE TABLE t1", 105 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `a` int NOT NULL,\n `b` varchar(10),\n PRIMARY KEY (`a`),\n UNIQUE KEY `b` (`b`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 106 }, 107 { 108 WriteQuery: `CREATE TABLE t1 SELECT * from mytable`, 109 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 110 SelectQuery: "SHOW CREATE TABLE t1", 111 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `i` bigint NOT NULL,\n `s` varchar(20) NOT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 112 }, 113 { 114 WriteQuery: `CREATE TABLE mydb.t1 (a INTEGER NOT NULL PRIMARY KEY, b VARCHAR(10) NOT NULL)`, 115 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 116 SelectQuery: "SHOW CREATE TABLE mydb.t1", 117 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `a` int NOT NULL,\n `b` varchar(10) NOT NULL,\n PRIMARY KEY (`a`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 118 }, 119 { 120 WriteQuery: `CREATE TABLE t1 (i int primary key, j int auto_increment unique)`, 121 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 122 SelectQuery: "SHOW CREATE TABLE t1", 123 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `i` int NOT NULL,\n `j` int AUTO_INCREMENT,\n PRIMARY KEY (`i`),\n UNIQUE KEY `j` (`j`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 124 }, 125 { 126 WriteQuery: `CREATE TABLE t1 (i int primary key, j int auto_increment, index (j))`, 127 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 128 SelectQuery: "SHOW CREATE TABLE t1", 129 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `i` int NOT NULL,\n `j` int AUTO_INCREMENT,\n PRIMARY KEY (`i`),\n KEY `j` (`j`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 130 }, 131 { 132 WriteQuery: `CREATE TABLE t1 (i int primary key, j int auto_increment, k int, unique(j,k))`, 133 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 134 SelectQuery: "SHOW CREATE TABLE t1", 135 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `i` int NOT NULL,\n `j` int AUTO_INCREMENT,\n `k` int,\n PRIMARY KEY (`i`),\n UNIQUE KEY `jk` (`j`,`k`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 136 }, 137 { 138 WriteQuery: `CREATE TABLE t1 (i int primary key, j int auto_increment, k int, index (j,k))`, 139 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 140 SelectQuery: "SHOW CREATE TABLE t1", 141 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `i` int NOT NULL,\n `j` int AUTO_INCREMENT,\n `k` int,\n PRIMARY KEY (`i`),\n KEY `jk` (`j`,`k`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 142 }, 143 { 144 WriteQuery: `CREATE TABLE t1 ( 145 pk int NOT NULL, 146 col1 blob DEFAULT (_utf8mb4'abc'), 147 col2 json DEFAULT (json_object(_utf8mb4'a',1)), 148 col3 text DEFAULT (_utf8mb4'abc'), 149 PRIMARY KEY (pk) 150 )`, 151 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 152 SelectQuery: "SHOW CREATE TABLE t1", 153 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `pk` int NOT NULL,\n `col1` blob DEFAULT ('abc'),\n `col2` json DEFAULT (json_object('a',1)),\n `col3` text DEFAULT ('abc'),\n PRIMARY KEY (`pk`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 154 }, 155 { 156 WriteQuery: `CREATE TABLE td ( 157 pk int PRIMARY KEY, 158 col2 int NOT NULL DEFAULT 2, 159 col3 double NOT NULL DEFAULT (round(-(1.58),0)), 160 col4 varchar(10) DEFAULT 'new row', 161 col5 float DEFAULT 33.33, 162 col6 int DEFAULT NULL, 163 col7 timestamp DEFAULT NOW(), 164 col8 bigint DEFAULT (NOW()) 165 )`, 166 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 167 SelectQuery: "SHOW CREATE TABLE td", 168 ExpectedSelect: []sql.Row{sql.Row{"td", "CREATE TABLE `td` (\n `pk` int NOT NULL,\n `col2` int NOT NULL DEFAULT '2',\n `col3` double NOT NULL DEFAULT (round(-1.58,0)),\n `col4` varchar(10) DEFAULT 'new row',\n `col5` float DEFAULT '33.33',\n `col6` int DEFAULT NULL,\n `col7` timestamp DEFAULT CURRENT_TIMESTAMP,\n `col8` bigint DEFAULT CURRENT_TIMESTAMP,\n PRIMARY KEY (`pk`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 169 }, 170 { 171 WriteQuery: `create table t1 (i int primary key, b1 blob, b2 blob, index(b1(123), b2(456)))`, 172 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 173 SelectQuery: `show create table t1`, 174 ExpectedSelect: []sql.Row{{"t1", "CREATE TABLE `t1` (\n `i` int NOT NULL,\n `b1` blob,\n `b2` blob,\n PRIMARY KEY (`i`),\n KEY `b1b2` (`b1`(123),`b2`(456))\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 175 }, 176 { 177 WriteQuery: `create table t1 (i int primary key, b1 blob, b2 blob, unique index(b1(123), b2(456)))`, 178 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 179 SelectQuery: `show create table t1`, 180 ExpectedSelect: []sql.Row{{"t1", "CREATE TABLE `t1` (\n `i` int NOT NULL,\n `b1` blob,\n `b2` blob,\n PRIMARY KEY (`i`),\n UNIQUE KEY `b1b2` (`b1`(123),`b2`(456))\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 181 }, 182 { 183 WriteQuery: `create table t1 (i int primary key, b1 blob, b2 blob, index(b1(10)), index(b2(20)), index(b1(123), b2(456)))`, 184 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 185 SelectQuery: `show create table t1`, 186 ExpectedSelect: []sql.Row{{"t1", "CREATE TABLE `t1` (\n `i` int NOT NULL,\n `b1` blob,\n `b2` blob,\n PRIMARY KEY (`i`),\n KEY `b1` (`b1`(10)),\n KEY `b1b2` (`b1`(123),`b2`(456)),\n KEY `b2` (`b2`(20))\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 187 }, 188 { 189 WriteQuery: `CREATE TABLE t1 as select * from mytable`, 190 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 191 SelectQuery: `select * from t1 order by i`, 192 ExpectedSelect: []sql.Row{{1, "first row"}, {2, "second row"}, {3, "third row"}}, 193 }, 194 { 195 WriteQuery: `CREATE TABLE t1 as select * from mytable`, 196 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 197 SelectQuery: `show create table t1`, 198 ExpectedSelect: []sql.Row{{"t1", "CREATE TABLE `t1` (\n `i` bigint NOT NULL,\n `s` varchar(20) NOT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 199 }, 200 { 201 WriteQuery: `CREATE TABLE t1 as select s, i from mytable`, 202 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 203 SelectQuery: `select * from t1 order by i`, 204 ExpectedSelect: []sql.Row{{"first row", 1}, {"second row", 2}, {"third row", 3}}, 205 }, 206 { 207 WriteQuery: `CREATE TABLE t1 as select distinct s, i from mytable`, 208 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 209 SelectQuery: `select * from t1 order by i`, 210 ExpectedSelect: []sql.Row{{"first row", 1}, {"second row", 2}, {"third row", 3}}, 211 }, 212 { 213 WriteQuery: `CREATE TABLE t1 as select s, i from mytable order by s`, 214 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 215 SelectQuery: `select * from t1 order by i`, 216 ExpectedSelect: []sql.Row{{"first row", 1}, {"second row", 2}, {"third row", 3}}, 217 }, 218 // TODO: the second column should be named `sum(i)` but is `SUM(mytable.i)` 219 { 220 WriteQuery: `CREATE TABLE t1 as select s, sum(i) from mytable group by s`, 221 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 222 SelectQuery: `select * from t1 order by s`, // other column is named `SUM(mytable.i)` 223 ExpectedSelect: []sql.Row{{"first row", float64(1)}, {"second row", float64(2)}, {"third row", float64(3)}}, 224 }, 225 { 226 WriteQuery: `CREATE TABLE t1 as select s, sum(i) from mytable group by s having sum(i) > 2`, 227 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 228 SelectQuery: "select * from t1", 229 ExpectedSelect: []sql.Row{{"third row", float64(3)}}, 230 }, 231 { 232 WriteQuery: `CREATE TABLE t1 as select s, i from mytable order by s limit 1`, 233 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 234 SelectQuery: `select * from t1 order by i`, 235 ExpectedSelect: []sql.Row{{"first row", 1}}, 236 }, 237 { 238 WriteQuery: `CREATE TABLE t1 as select concat("new", s), i from mytable`, 239 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 240 SelectQuery: `select * from t1 order by i`, 241 ExpectedSelect: []sql.Row{{"newfirst row", 1}, {"newsecond row", 2}, {"newthird row", 3}}, 242 }, 243 { 244 WriteQuery: `CREATE TABLE t1 (pk varchar(10) primary key collate binary)`, 245 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 246 SelectQuery: `SHOW CREATE TABLE t1`, 247 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `pk` varbinary(10) NOT NULL,\n PRIMARY KEY (`pk`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 248 }, 249 { 250 WriteQuery: `CREATE TABLE t1 (pk varchar(10) primary key charset binary)`, 251 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 252 SelectQuery: `SHOW CREATE TABLE t1`, 253 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `pk` varbinary(10) NOT NULL,\n PRIMARY KEY (`pk`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 254 }, 255 { 256 WriteQuery: `CREATE TABLE t1 (pk varchar(10) primary key character set binary)`, 257 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 258 SelectQuery: `SHOW CREATE TABLE t1`, 259 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `pk` varbinary(10) NOT NULL,\n PRIMARY KEY (`pk`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 260 }, 261 { 262 WriteQuery: `CREATE TABLE t1 (pk varchar(10) primary key charset binary collate binary)`, 263 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 264 SelectQuery: `SHOW CREATE TABLE t1`, 265 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `pk` varbinary(10) NOT NULL,\n PRIMARY KEY (`pk`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 266 }, 267 { 268 WriteQuery: `CREATE TABLE t1 (pk varchar(10) primary key character set binary collate binary)`, 269 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 270 SelectQuery: `SHOW CREATE TABLE t1`, 271 ExpectedSelect: []sql.Row{sql.Row{"t1", "CREATE TABLE `t1` (\n `pk` varbinary(10) NOT NULL,\n PRIMARY KEY (`pk`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 272 }, 273 } 274 275 var CreateTableScriptTests = []ScriptTest{ 276 { 277 // https://github.com/dolthub/dolt/issues/6682 278 Name: "display width for numeric types", 279 SetUpScript: []string{ 280 "CREATE TABLE numericDisplayWidthTest (pk int primary key, b boolean, ti tinyint, ti1 tinyint(1), ti2 tinyint(2), i1 int(1));", 281 }, 282 Assertions: []ScriptTestAssertion{ 283 { 284 Query: "SHOW CREATE TABLE numericDisplayWidthTest;", 285 Expected: []sql.Row{{"numericDisplayWidthTest", 286 "CREATE TABLE `numericDisplayWidthTest` (\n `pk` int NOT NULL,\n `b` tinyint(1),\n `ti` tinyint,\n `ti1` tinyint(1),\n `ti2` tinyint,\n `i1` int,\n PRIMARY KEY (`pk`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 287 }, 288 { 289 // MySQL only honors display width when it is set to 1 and used with the TINYINT type; 290 // all other uses parse correctly, but are dropped. 291 Query: "SHOW FULL FIELDS FROM numericDisplayWidthTest;", 292 Expected: []sql.Row{ 293 {"pk", "int", interface{}(nil), "NO", "PRI", "NULL", "", "", ""}, 294 {"b", "tinyint(1)", interface{}(nil), "YES", "", "NULL", "", "", ""}, 295 {"ti", "tinyint", interface{}(nil), "YES", "", "NULL", "", "", ""}, 296 {"ti1", "tinyint(1)", interface{}(nil), "YES", "", "NULL", "", "", ""}, 297 {"ti2", "tinyint", interface{}(nil), "YES", "", "NULL", "", "", ""}, 298 {"i1", "int", interface{}(nil), "YES", "", "NULL", "", "", ""}, 299 }, 300 }, 301 { 302 Query: "CREATE TABLE errorTest(pk int primary key, ti tinyint(-1));", 303 ExpectedErrStr: "syntax error at position 56 near 'tinyint'", 304 }, 305 }, 306 }, 307 { 308 Name: "Validate that CREATE LIKE preserves checks", 309 SetUpScript: []string{ 310 "CREATE TABLE t1 (pk int primary key, test_score int, height int CHECK (height < 10) , CONSTRAINT mycheck CHECK (test_score >= 50))", 311 "CREATE TABLE t2 LIKE t1", 312 }, 313 Assertions: []ScriptTestAssertion{ 314 { 315 Query: "INSERT INTO t2 VALUE (1, 40, 5)", 316 ExpectedErr: sql.ErrCheckConstraintViolated, 317 }, 318 { 319 Query: "INSERT INTO t2 VALUE (1, 60, 15)", 320 ExpectedErr: sql.ErrCheckConstraintViolated, 321 }, 322 }, 323 }, 324 { 325 Name: "datetime precision", 326 SetUpScript: []string{ 327 "CREATE TABLE t1 (pk int primary key, d datetime)", 328 "CREATE TABLE t2 (pk int primary key, d datetime(3))", 329 "CREATE TABLE t3 (pk int primary key, d datetime(6))", 330 }, 331 Assertions: []ScriptTestAssertion{ 332 { 333 Query: "show create table t1", 334 Expected: []sql.Row{{"t1", 335 "CREATE TABLE `t1` (\n" + 336 " `pk` int NOT NULL,\n" + 337 " `d` datetime,\n" + 338 " PRIMARY KEY (`pk`)\n" + 339 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 340 }, 341 { 342 Query: "insert into t1 values (1, '2020-01-01 00:00:00.123456')", 343 Expected: []sql.Row{{types.NewOkResult(1)}}, 344 }, 345 { 346 Query: "select * from t1 order by pk", 347 Expected: []sql.Row{{1, MustParseTime(time.DateTime, "2020-01-01 00:00:00")}}, 348 }, 349 { 350 Query: "show create table t2", 351 Expected: []sql.Row{{"t2", 352 "CREATE TABLE `t2` (\n" + 353 " `pk` int NOT NULL,\n" + 354 " `d` datetime(3),\n" + 355 " PRIMARY KEY (`pk`)\n" + 356 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 357 }, 358 { 359 Query: "insert into t2 values (1, '2020-01-01 00:00:00.123456')", 360 Expected: []sql.Row{{types.NewOkResult(1)}}, 361 }, 362 { 363 Query: "select * from t2 order by pk", 364 Expected: []sql.Row{{1, MustParseTime(time.RFC3339Nano, "2020-01-01T00:00:00.123000000Z")}}, 365 }, 366 { 367 Query: "show create table t3", 368 Expected: []sql.Row{{"t3", 369 "CREATE TABLE `t3` (\n" + 370 " `pk` int NOT NULL,\n" + 371 " `d` datetime(6),\n" + 372 " PRIMARY KEY (`pk`)\n" + 373 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 374 }, 375 { 376 Query: "insert into t3 values (1, '2020-01-01 00:00:00.123456')", 377 Expected: []sql.Row{{types.NewOkResult(1)}}, 378 }, 379 { 380 Query: "select * from t3 order by pk", 381 Expected: []sql.Row{{1, MustParseTime(time.RFC3339Nano, "2020-01-01T00:00:00.123456000Z")}}, 382 }, 383 { 384 Query: "create table t4 (pk int primary key, d datetime(-1))", 385 ExpectedErr: sql.ErrSyntaxError, 386 }, 387 { 388 Query: "create table t4 (pk int primary key, d datetime(7))", 389 ExpectedErrStr: "DATETIME supports precision from 0 to 6", 390 }, 391 }, 392 }, 393 { 394 Name: "timestamp precision", 395 SetUpScript: []string{ 396 "CREATE TABLE t1 (pk int primary key, d timestamp)", 397 "CREATE TABLE t2 (pk int primary key, d timestamp(3))", 398 "CREATE TABLE t3 (pk int primary key, d timestamp(6))", 399 }, 400 Assertions: []ScriptTestAssertion{ 401 { 402 Query: "show create table t1", 403 Expected: []sql.Row{{"t1", 404 "CREATE TABLE `t1` (\n" + 405 " `pk` int NOT NULL,\n" + 406 " `d` timestamp,\n" + 407 " PRIMARY KEY (`pk`)\n" + 408 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 409 }, 410 { 411 Query: "insert into t1 values (1, '2020-01-01 00:00:00.123456')", 412 Expected: []sql.Row{{types.NewOkResult(1)}}, 413 }, 414 { 415 SkipResultCheckOnServerEngine: true, // the nanosecond is returned over the wire 416 Query: "select * from t1 order by pk", 417 Expected: []sql.Row{{1, MustParseTime(time.DateTime, "2020-01-01 00:00:00")}}, 418 }, 419 { 420 Query: "show create table t2", 421 Expected: []sql.Row{{"t2", 422 "CREATE TABLE `t2` (\n" + 423 " `pk` int NOT NULL,\n" + 424 " `d` timestamp(3),\n" + 425 " PRIMARY KEY (`pk`)\n" + 426 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 427 }, 428 { 429 Query: "insert into t2 values (1, '2020-01-01 00:00:00.123456')", 430 Expected: []sql.Row{{types.NewOkResult(1)}}, 431 }, 432 { 433 SkipResultCheckOnServerEngine: true, // the nanosecond is returned over the wire 434 Query: "select * from t2 order by pk", 435 Expected: []sql.Row{{1, MustParseTime(time.RFC3339Nano, "2020-01-01T00:00:00.123000000Z")}}, 436 }, 437 { 438 Query: "show create table t3", 439 Expected: []sql.Row{{"t3", 440 "CREATE TABLE `t3` (\n" + 441 " `pk` int NOT NULL,\n" + 442 " `d` timestamp(6),\n" + 443 " PRIMARY KEY (`pk`)\n" + 444 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 445 }, 446 { 447 Query: "insert into t3 values (1, '2020-01-01 00:00:00.123456')", 448 Expected: []sql.Row{{types.NewOkResult(1)}}, 449 }, 450 { 451 SkipResultCheckOnServerEngine: true, // the nanosecond is returned over the wire 452 Query: "select * from t3 order by pk", 453 Expected: []sql.Row{{1, MustParseTime(time.RFC3339Nano, "2020-01-01T00:00:00.123456000Z")}}, 454 }, 455 { 456 Query: "create table t4 (pk int primary key, d TIMESTAMP(-1))", 457 ExpectedErr: sql.ErrSyntaxError, 458 }, 459 { 460 Query: "create table t4 (pk int primary key, d TIMESTAMP(7))", 461 ExpectedErrStr: "TIMESTAMP supports precision from 0 to 6", 462 }, 463 }, 464 }, 465 { 466 Name: "Identifier lengths", 467 SetUpScript: []string{ 468 "create table parent (a int primary key)", 469 }, 470 Assertions: []ScriptTestAssertion{ 471 { 472 // 64 characters 473 Query: "create table abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijkl (a int primary key)", 474 Expected: []sql.Row{{types.NewOkResult(0)}}, 475 }, 476 { 477 // 65 characters 478 Query: "create table abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklm (a int primary key)", 479 ExpectedErr: sql.ErrInvalidIdentifier, 480 }, 481 { 482 // 64 characters 483 Query: "create table a (abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijkl int primary key)", 484 Expected: []sql.Row{{types.NewOkResult(0)}}, 485 }, 486 { 487 // 65 characters 488 Query: "create table a (abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklm int primary key)", 489 ExpectedErr: sql.ErrInvalidIdentifier, 490 }, 491 { 492 // 64 characters 493 Query: "create table b (a int primary key, constraint abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijkl check (a > 0))", 494 Expected: []sql.Row{{types.NewOkResult(0)}}, 495 }, 496 { 497 // 65 characters 498 Query: "create table b (a int primary key, constraint abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklm check (a > 0))", 499 ExpectedErr: sql.ErrInvalidIdentifier, 500 }, 501 { 502 // 64 characters 503 Query: "create table c (a int primary key, b int, key abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijkl (b))", 504 Expected: []sql.Row{{types.NewOkResult(0)}}, 505 }, 506 { 507 // 65 characters 508 Query: "create table c (a int primary key, b int, key abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklm (b))", 509 ExpectedErr: sql.ErrInvalidIdentifier, 510 }, 511 { 512 // 64 characters 513 Query: "create table d (a int primary key, constraint abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijkl foreign key (a) references parent(a))", 514 Expected: []sql.Row{{types.NewOkResult(0)}}, 515 }, 516 { 517 // 65 characters 518 Query: "create table d (a int primary key, constraint abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklm foreign key (a) references parent(a))", 519 ExpectedErr: sql.ErrInvalidIdentifier, 520 }, 521 }, 522 }, 523 { 524 Name: "case insensitive column name uniqueness", 525 Assertions: []ScriptTestAssertion{ 526 { 527 Query: "create table t1 (abc int, abc int)", 528 ExpectedErr: sql.ErrDuplicateColumn, 529 }, 530 { 531 Query: "create table t2 (ABC int, ABC int)", 532 ExpectedErr: sql.ErrDuplicateColumn, 533 }, 534 { 535 Query: "create table t3 (a int, A int)", 536 ExpectedErr: sql.ErrDuplicateColumn, 537 }, 538 { 539 Query: "create table t4 (abc int, def int, Abc int)", 540 ExpectedErr: sql.ErrDuplicateColumn, 541 }, 542 }, 543 }, 544 { 545 Name: "valid character set and collation options", 546 SetUpScript: []string{ 547 "create table parent (a int primary key)", 548 }, 549 Assertions: []ScriptTestAssertion{ 550 { 551 Query: `CREATE TABLE t1 (pk varbinary(10) primary key collate utf8mb4_0900_bin)`, 552 ExpectedErr: types.ErrBinaryCollation, 553 }, 554 { 555 Query: `CREATE TABLE t1 (pk varbinary(10) primary key charset utf8mb4_0900_bin)`, 556 ExpectedErr: types.ErrCharacterSetOnInvalidType, 557 }, 558 { 559 Query: `CREATE TABLE t1 (pk varbinary(10) primary key character set utf8mb4)`, 560 ExpectedErr: types.ErrCharacterSetOnInvalidType, 561 }, 562 { 563 Query: `CREATE TABLE t1 (pk varbinary(10) primary key charset utf8mb4 collate utf8mb4_0900_bin)`, 564 ExpectedErr: types.ErrCharacterSetOnInvalidType, 565 }, 566 { 567 Query: `CREATE TABLE t1 (pk varbinary(10) primary key character set utf8mb4 collate utf8mb4_0900_bin)`, 568 ExpectedErr: types.ErrCharacterSetOnInvalidType, 569 }, 570 { 571 Query: `CREATE TABLE t1 (pk int primary key character set utf8mb4)`, 572 ExpectedErr: types.ErrCharacterSetOnInvalidType, 573 }, 574 }, 575 }, 576 } 577 578 var CreateTableAutoIncrementTests = []ScriptTest{ 579 { 580 Name: "create table with non primary auto_increment column", 581 SetUpScript: []string{}, 582 Assertions: []ScriptTestAssertion{ 583 { 584 Query: "create table t1 (a int auto_increment unique, b int, primary key(b))", 585 Expected: []sql.Row{{types.NewOkResult(0)}}, 586 }, 587 { 588 Query: "insert into t1 (b) values (1), (2)", 589 Expected: []sql.Row{ 590 { 591 types.OkResult{ 592 RowsAffected: 2, 593 InsertID: 1, 594 }, 595 }, 596 }, 597 }, 598 { 599 Query: "show create table t1", 600 Expected: []sql.Row{{"t1", 601 "CREATE TABLE `t1` (\n" + 602 " `a` int AUTO_INCREMENT,\n" + 603 " `b` int NOT NULL,\n" + 604 " PRIMARY KEY (`b`),\n" + 605 " UNIQUE KEY `a` (`a`)\n" + 606 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 607 }, 608 { 609 Query: "select * from t1 order by b", 610 Expected: []sql.Row{{1, 1}, {2, 2}}, 611 }, 612 }, 613 }, 614 { 615 Name: "create table with non primary auto_increment column, separate unique key", 616 SetUpScript: []string{}, 617 Assertions: []ScriptTestAssertion{ 618 { 619 Query: "create table t1 (a int auto_increment, b int, primary key(b), unique key(a))", 620 Expected: []sql.Row{{types.NewOkResult(0)}}, 621 }, 622 { 623 Query: "insert into t1 (b) values (1), (2)", 624 Expected: []sql.Row{ 625 { 626 types.OkResult{ 627 RowsAffected: 2, 628 InsertID: 1, 629 }, 630 }, 631 }, 632 }, 633 { 634 Query: "show create table t1", 635 Expected: []sql.Row{{"t1", 636 "CREATE TABLE `t1` (\n" + 637 " `a` int AUTO_INCREMENT,\n" + 638 " `b` int NOT NULL,\n" + 639 " PRIMARY KEY (`b`),\n" + 640 " UNIQUE KEY `a` (`a`)\n" + 641 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 642 }, 643 { 644 Query: "select * from t1 order by b", 645 Expected: []sql.Row{{1, 1}, {2, 2}}, 646 }, 647 }, 648 }, 649 { 650 Name: "create table with non primary auto_increment column, missing unique key", 651 SetUpScript: []string{}, 652 Assertions: []ScriptTestAssertion{ 653 { 654 Query: "create table t1 (a int auto_increment, b int, primary key(b))", 655 ExpectedErr: sql.ErrInvalidAutoIncCols, 656 }, 657 }, 658 }, 659 } 660 661 var BrokenCreateTableQueries = []WriteQueryTest{ 662 { 663 // TODO: We don't support table comments that contain single quotes due to how table options are parsed. 664 // Vitess parses them, but turns any double quotes into single quotes and puts all table options back 665 // into a string that GMS has to reparse. This means we can't tell if the single quote is the end of 666 // the quoted string, or if it was a single quote inside of double quotes and needs to be escaped. 667 // To fix this, Vitess should return the parsed table options as structured data, instead of as a 668 // single string. 669 WriteQuery: `create table tableWithComment (pk int) COMMENT "'"`, 670 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 671 SelectQuery: "SHOW CREATE TABLE tableWithComment", 672 ExpectedSelect: []sql.Row{{"tableWithComment", "CREATE TABLE `tableWithComment` (\n `pk` int\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin COMMENT=''''"}}, 673 }, 674 { 675 WriteQuery: `create table t1 (b blob, primary key(b(1)))`, 676 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 677 SelectQuery: `show create table t1`, 678 ExpectedSelect: []sql.Row{{"t1", "CREATE TABLE `t1` (\n `b` blob NOT NULL,\n PRIMARY KEY (`b`(1))\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 679 }, 680 { 681 WriteQuery: `create table t1 (b1 blob, b2 blob, primary key(b1(123), b2(456)))`, 682 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 683 SelectQuery: `show create table t1`, 684 ExpectedSelect: []sql.Row{{"t1", "CREATE TABLE `t1` (\n `b1` blob NOT NULL,\n `b2` blob NOT NULL,\n PRIMARY KEY (`b1`(123),`b2`(456))\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 685 }, 686 { 687 WriteQuery: `create table t1 (i int, b1 blob, b2 blob, primary key(b1(123), b2(456), i))`, 688 ExpectedWriteResult: []sql.Row{{types.NewOkResult(0)}}, 689 SelectQuery: `show create table t1`, 690 ExpectedSelect: []sql.Row{{"t1", "CREATE TABLE `t1` (\n `i` int NOT NULL,\n `b1` blob NOT NULL,\n `b2` blob NOT NULL,\n PRIMARY KEY (`b1`(123),`b2`(456),`i`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 691 }, 692 }