github.com/dolthub/go-mysql-server@v0.18.0/enginetest/queries/insert_queries.go (about) 1 // Copyright 2020-2021 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 "math" 19 "time" 20 21 "github.com/dolthub/vitess/go/mysql" 22 23 "github.com/dolthub/go-mysql-server/sql" 24 "github.com/dolthub/go-mysql-server/sql/types" 25 ) 26 27 var InsertQueries = []WriteQueryTest{ 28 { 29 WriteQuery: "INSERT INTO keyless VALUES ();", 30 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 31 SelectQuery: "SELECT * FROM keyless WHERE c0 IS NULL;", 32 ExpectedSelect: []sql.Row{{nil, nil}}, 33 }, 34 { 35 WriteQuery: "INSERT INTO keyless () VALUES ();", 36 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 37 SelectQuery: "SELECT * FROM keyless WHERE c0 IS NULL;", 38 ExpectedSelect: []sql.Row{{nil, nil}}, 39 }, 40 { 41 WriteQuery: "INSERT INTO mytable (s, i) VALUES ('x', '10.0');", 42 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 43 SelectQuery: "SELECT i FROM mytable WHERE s = 'x';", 44 ExpectedSelect: []sql.Row{{int64(10)}}, 45 }, 46 { 47 WriteQuery: "INSERT INTO mytable (s, i) VALUES ('x', '64.6');", 48 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 49 SelectQuery: "SELECT i FROM mytable WHERE s = 'x';", 50 ExpectedSelect: []sql.Row{{int64(64)}}, 51 }, 52 { 53 WriteQuery: "INSERT INTO mytable (s, i) VALUES ('x', 999);", 54 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 55 SelectQuery: "SELECT i FROM mytable WHERE s = 'x';", 56 ExpectedSelect: []sql.Row{{int64(999)}}, 57 }, 58 { 59 WriteQuery: "INSERT INTO niltable (i, f) VALUES (10, 10.0), (12, 12.0);", 60 ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}}, 61 SelectQuery: "SELECT i,f FROM niltable WHERE f IN (10.0, 12.0) ORDER BY f;", 62 ExpectedSelect: []sql.Row{{int64(10), 10.0}, {int64(12), 12.0}}, 63 }, 64 { 65 WriteQuery: "INSERT INTO mytable SET s = 'x', i = 999;", 66 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 67 SelectQuery: "SELECT i FROM mytable WHERE s = 'x';", 68 ExpectedSelect: []sql.Row{{int64(999)}}, 69 }, 70 { 71 WriteQuery: "INSERT INTO mytable VALUES (999, 'x');", 72 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 73 SelectQuery: "SELECT i FROM mytable WHERE s = 'x';", 74 ExpectedSelect: []sql.Row{{int64(999)}}, 75 }, 76 { 77 WriteQuery: "INSERT INTO mytable SET i = 999, s = 'x';", 78 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 79 SelectQuery: "SELECT i FROM mytable WHERE s = 'x';", 80 ExpectedSelect: []sql.Row{{int64(999)}}, 81 }, 82 { 83 WriteQuery: "INSERT INTO mytable VALUES (999, _binary 'x');", 84 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 85 SelectQuery: "SELECT s FROM mytable WHERE i = 999;", 86 ExpectedSelect: []sql.Row{{"x"}}, 87 }, 88 { 89 WriteQuery: "INSERT INTO mytable SET i = 999, s = _binary 'x';", 90 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 91 SelectQuery: "SELECT s FROM mytable WHERE i = 999;", 92 ExpectedSelect: []sql.Row{{"x"}}, 93 }, 94 { 95 WriteQuery: `INSERT INTO typestable VALUES ( 96 999, 127, 32767, 2147483647, 9223372036854775807, 97 255, 65535, 4294967295, 18446744073709551615, 98 3.40282346638528859811704183484516925440e+38, 1.797693134862315708145274237317043567981e+308, 99 '2037-04-05 12:51:36', '2231-11-07', 100 'random text', true, '{"key":"value"}', 'blobdata', 'v1', 'v2' 101 );`, 102 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 103 SelectQuery: "SELECT * FROM typestable WHERE id = 999;", 104 ExpectedSelect: []sql.Row{{ 105 int64(999), int8(math.MaxInt8), int16(math.MaxInt16), int32(math.MaxInt32), int64(math.MaxInt64), 106 uint8(math.MaxUint8), uint16(math.MaxUint16), uint32(math.MaxUint32), uint64(math.MaxUint64), 107 float32(math.MaxFloat32), float64(math.MaxFloat64), 108 sql.MustConvert(types.Timestamp.Convert("2037-04-05 12:51:36")), sql.MustConvert(types.Date.Convert("2231-11-07")), 109 "random text", sql.True, types.MustJSON(`{"key":"value"}`), []byte("blobdata"), "v1", "v2", 110 }}, 111 }, 112 { 113 WriteQuery: `INSERT INTO typestable SET 114 id = 999, i8 = 127, i16 = 32767, i32 = 2147483647, i64 = 9223372036854775807, 115 u8 = 255, u16 = 65535, u32 = 4294967295, u64 = 18446744073709551615, 116 f32 = 3.40282346638528859811704183484516925440e+38, f64 = 1.797693134862315708145274237317043567981e+308, 117 ti = '2037-04-05 12:51:36', da = '2231-11-07', 118 te = 'random text', bo = true, js = '{"key":"value"}', bl = 'blobdata', e1 = 'v1', s1 = 'v2' 119 ;`, 120 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 121 SelectQuery: "SELECT * FROM typestable WHERE id = 999;", 122 ExpectedSelect: []sql.Row{{ 123 int64(999), int8(math.MaxInt8), int16(math.MaxInt16), int32(math.MaxInt32), int64(math.MaxInt64), 124 uint8(math.MaxUint8), uint16(math.MaxUint16), uint32(math.MaxUint32), uint64(math.MaxUint64), 125 float32(math.MaxFloat32), float64(math.MaxFloat64), 126 sql.MustConvert(types.Timestamp.Convert("2037-04-05 12:51:36")), sql.MustConvert(types.Date.Convert("2231-11-07")), 127 "random text", sql.True, types.MustJSON(`{"key":"value"}`), []byte("blobdata"), "v1", "v2", 128 }}, 129 }, 130 { 131 SkipServerEngine: true, // the datetime returned is not non-zero 132 WriteQuery: `INSERT INTO typestable VALUES ( 133 999, -128, -32768, -2147483648, -9223372036854775808, 134 0, 0, 0, 0, 135 1.401298464324817070923729583289916131280e-45, 4.940656458412465441765687928682213723651e-324, 136 '0000-00-00 00:00:00', '0000-00-00', 137 '', false, '""', '', '', '' 138 );`, 139 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 140 SelectQuery: "SELECT * FROM typestable WHERE id = 999;", 141 ExpectedSelect: []sql.Row{{ 142 int64(999), int8(-math.MaxInt8 - 1), int16(-math.MaxInt16 - 1), int32(-math.MaxInt32 - 1), int64(-math.MaxInt64 - 1), 143 uint8(0), uint16(0), uint32(0), uint64(0), 144 float32(math.SmallestNonzeroFloat32), float64(math.SmallestNonzeroFloat64), 145 types.Timestamp.Zero(), types.Date.Zero(), 146 "", sql.False, types.MustJSON(`""`), []byte(""), "", "", 147 }}, 148 }, 149 { 150 SkipServerEngine: true, // the datetime returned is not non-zero 151 WriteQuery: `INSERT INTO typestable SET 152 id = 999, i8 = -128, i16 = -32768, i32 = -2147483648, i64 = -9223372036854775808, 153 u8 = 0, u16 = 0, u32 = 0, u64 = 0, 154 f32 = 1.401298464324817070923729583289916131280e-45, f64 = 4.940656458412465441765687928682213723651e-324, 155 ti = '0000-00-00 00:00:00', da = '0000-00-00', 156 te = '', bo = false, js = '""', bl = '', e1 = 'v1', s1 = 'v2' 157 ;`, 158 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 159 SelectQuery: "SELECT * FROM typestable WHERE id = 999;", 160 ExpectedSelect: []sql.Row{{ 161 int64(999), int8(-math.MaxInt8 - 1), int16(-math.MaxInt16 - 1), int32(-math.MaxInt32 - 1), int64(-math.MaxInt64 - 1), 162 uint8(0), uint16(0), uint32(0), uint64(0), 163 float32(math.SmallestNonzeroFloat32), float64(math.SmallestNonzeroFloat64), 164 types.Timestamp.Zero(), types.Date.Zero(), 165 "", sql.False, types.MustJSON(`""`), []byte(""), "v1", "v2", 166 }}, 167 }, 168 { 169 SkipServerEngine: true, // the datetime returned is not non-zero 170 WriteQuery: `INSERT INTO typestable SET 171 id = 999, i8 = -128, i16 = -32768, i32 = -2147483648, i64 = -9223372036854775808, 172 u8 = 0, u16 = 0, u32 = 0, u64 = 0, 173 f32 = 1.401298464324817070923729583289916131280e-45, f64 = 4.940656458412465441765687928682213723651e-324, 174 ti = '2037-04-05 12:51:36 -0000 UTC', da = '0000-00-00', 175 te = '', bo = false, js = '""', bl = '', e1 = 'v1', s1 = 'v2' 176 ;`, 177 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 178 SelectQuery: "SELECT * FROM typestable WHERE id = 999;", 179 ExpectedSelect: []sql.Row{{ 180 int64(999), int8(-math.MaxInt8 - 1), int16(-math.MaxInt16 - 1), int32(-math.MaxInt32 - 1), int64(-math.MaxInt64 - 1), 181 uint8(0), uint16(0), uint32(0), uint64(0), 182 float32(math.SmallestNonzeroFloat32), float64(math.SmallestNonzeroFloat64), 183 sql.MustConvert(types.Timestamp.Convert("2037-04-05 12:51:36")), types.Date.Zero(), 184 "", sql.False, types.MustJSON(`""`), []byte(""), "v1", "v2", 185 }}, 186 }, 187 { 188 WriteQuery: `INSERT INTO mytable (i,s) VALUES (10, 'NULL')`, 189 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 190 SelectQuery: "SELECT * FROM mytable WHERE i = 10;", 191 ExpectedSelect: []sql.Row{{int64(10), "NULL"}}, 192 }, 193 { 194 WriteQuery: `INSERT INTO typestable VALUES (999, null, null, null, null, null, null, null, null, 195 null, null, null, null, null, null, null, null, null, null);`, 196 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 197 SelectQuery: "SELECT * FROM typestable WHERE id = 999;", 198 ExpectedSelect: []sql.Row{{int64(999), nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil}}, 199 }, 200 { 201 WriteQuery: `INSERT INTO typestable (id, ti, da) VALUES (999, '2021-09-1', '2021-9-01');`, 202 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 203 SelectQuery: "SELECT id, ti, da FROM typestable WHERE id = 999;", 204 ExpectedSelect: []sql.Row{{int64(999), sql.MustConvert(types.Timestamp.Convert("2021-09-01")), sql.MustConvert(types.Date.Convert("2021-09-01"))}}, 205 }, 206 { 207 WriteQuery: `INSERT INTO typestable SET id=999, i8=null, i16=null, i32=null, i64=null, u8=null, u16=null, u32=null, u64=null, 208 f32=null, f64=null, ti=null, da=null, te=null, bo=null, js=null, bl=null, e1=null, s1=null;`, 209 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 210 SelectQuery: "SELECT * FROM typestable WHERE id = 999;", 211 ExpectedSelect: []sql.Row{{int64(999), nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil}}, 212 }, 213 { 214 WriteQuery: "INSERT INTO mytable SELECT i+100,s FROM mytable", 215 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 216 SelectQuery: "SELECT * FROM mytable ORDER BY i", 217 ExpectedSelect: []sql.Row{ 218 {int64(1), "first row"}, 219 {int64(2), "second row"}, 220 {int64(3), "third row"}, 221 {int64(101), "first row"}, 222 {int64(102), "second row"}, 223 {int64(103), "third row"}, 224 }, 225 }, 226 { 227 WriteQuery: "INSERT INTO emptytable SELECT * FROM mytable", 228 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 229 SelectQuery: "SELECT * FROM emptytable ORDER BY i", 230 ExpectedSelect: []sql.Row{ 231 {int64(1), "first row"}, 232 {int64(2), "second row"}, 233 {int64(3), "third row"}, 234 }, 235 }, 236 { 237 WriteQuery: "INSERT INTO emptytable SELECT * FROM mytable where mytable.i > 2", 238 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 239 SelectQuery: "SELECT * FROM emptytable ORDER BY i", 240 ExpectedSelect: []sql.Row{ 241 {int64(3), "third row"}, 242 }, 243 }, 244 { 245 WriteQuery: "INSERT INTO niltable (i,f) SELECT i+10, NULL FROM mytable where mytable.i > 2", 246 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 247 SelectQuery: "SELECT * FROM niltable where i > 10 ORDER BY i", 248 ExpectedSelect: []sql.Row{ 249 {13, nil, nil, nil}, 250 }, 251 }, 252 { 253 WriteQuery: "INSERT INTO mytable (i,s) SELECT i+10, 'new' FROM mytable", 254 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 255 SelectQuery: "SELECT * FROM mytable ORDER BY i", 256 ExpectedSelect: []sql.Row{ 257 {int64(1), "first row"}, 258 {int64(2), "second row"}, 259 {int64(3), "third row"}, 260 {int64(11), "new"}, 261 {int64(12), "new"}, 262 {int64(13), "new"}, 263 }, 264 }, 265 { 266 WriteQuery: "INSERT INTO mytable SELECT i2+100, s2 FROM othertable", 267 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 268 SelectQuery: "SELECT * FROM mytable ORDER BY i,s", 269 ExpectedSelect: []sql.Row{ 270 {int64(1), "first row"}, 271 {int64(2), "second row"}, 272 {int64(3), "third row"}, 273 {int64(101), "third"}, 274 {int64(102), "second"}, 275 {int64(103), "first"}, 276 }, 277 }, 278 { 279 WriteQuery: "INSERT INTO emptytable (s,i) SELECT * FROM othertable", 280 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 281 SelectQuery: "SELECT * FROM emptytable ORDER BY i,s", 282 ExpectedSelect: []sql.Row{ 283 {int64(1), "third"}, 284 {int64(2), "second"}, 285 {int64(3), "first"}, 286 }, 287 }, 288 { 289 WriteQuery: "INSERT INTO emptytable (s,i) SELECT concat(m.s, o.s2), m.i FROM othertable o JOIN mytable m ON m.i=o.i2", 290 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 291 SelectQuery: "SELECT * FROM emptytable ORDER BY i,s", 292 ExpectedSelect: []sql.Row{ 293 {int64(1), "first rowthird"}, 294 {int64(2), "second rowsecond"}, 295 {int64(3), "third rowfirst"}, 296 }, 297 }, 298 { 299 WriteQuery: `INSERT INTO emptytable (s,i) SELECT s,i from mytable where i = 1 300 union select s,i from mytable where i = 3`, 301 ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}}, 302 SelectQuery: "SELECT * FROM emptytable ORDER BY i,s", 303 ExpectedSelect: []sql.Row{ 304 {int64(1), "first row"}, 305 {int64(3), "third row"}, 306 }, 307 }, 308 { 309 WriteQuery: `INSERT INTO emptytable (s,i) SELECT s,i from mytable where i = 1 310 union select s,i from mytable where i = 3 311 union select s,i from mytable where i > 2`, 312 ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}}, 313 SelectQuery: "SELECT * FROM emptytable ORDER BY i,s", 314 ExpectedSelect: []sql.Row{ 315 {int64(1), "first row"}, 316 {int64(3), "third row"}, 317 }, 318 }, 319 { 320 WriteQuery: `INSERT INTO emptytable (s,i) 321 SELECT s,i from mytable where i = 1 322 union all select s,i+1 from mytable where i < 2 323 union all select s,i+2 from mytable where i in (1)`, 324 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 325 SelectQuery: "SELECT * FROM emptytable ORDER BY i,s", 326 ExpectedSelect: []sql.Row{ 327 {int64(1), "first row"}, 328 {int64(2), "first row"}, 329 {int64(3), "first row"}, 330 }, 331 }, 332 { 333 WriteQuery: "INSERT INTO emptytable (s,i) SELECT distinct s,i from mytable", 334 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 335 SelectQuery: "SELECT * FROM emptytable ORDER BY i,s", 336 ExpectedSelect: []sql.Row{ 337 {int64(1), "first row"}, 338 {int64(2), "second row"}, 339 {int64(3), "third row"}, 340 }, 341 }, 342 { 343 WriteQuery: "INSERT INTO mytable (i,s) SELECT (i + 10.0) / 10.0 + 10 + i, concat(s, ' new') FROM mytable", 344 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 345 SelectQuery: "SELECT * FROM mytable ORDER BY i, s", 346 ExpectedSelect: []sql.Row{ 347 {int64(1), "first row"}, 348 {int64(2), "second row"}, 349 {int64(3), "third row"}, 350 {int64(12), "first row new"}, 351 {int64(13), "second row new"}, 352 {int64(14), "third row new"}, 353 }, 354 }, 355 { 356 WriteQuery: "INSERT INTO mytable (i,s) SELECT CHAR_LENGTH(s), concat('numrows: ', count(*)) from mytable group by 1", 357 ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}}, 358 SelectQuery: "SELECT * FROM mytable ORDER BY i, s", 359 ExpectedSelect: []sql.Row{ 360 {1, "first row"}, 361 {2, "second row"}, 362 {3, "third row"}, 363 {9, "numrows: 2"}, 364 {10, "numrows: 1"}, 365 }, 366 }, 367 { 368 WriteQuery: "INSERT INTO mytable (i,s) SELECT CHAR_LENGTH(s) as len, concat('numrows: ', count(*)) from mytable group by 1 HAVING len > 9", 369 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 370 SelectQuery: "SELECT * FROM mytable ORDER BY i, s", 371 ExpectedSelect: []sql.Row{ 372 {1, "first row"}, 373 {2, "second row"}, 374 {3, "third row"}, 375 {10, "numrows: 1"}, 376 }, 377 }, 378 { 379 WriteQuery: "INSERT INTO mytable (i,s) SELECT i * 2, concat(s,s) from mytable order by 1 desc limit 1", 380 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 381 SelectQuery: "SELECT * FROM mytable ORDER BY i, s", 382 ExpectedSelect: []sql.Row{ 383 {1, "first row"}, 384 {2, "second row"}, 385 {3, "third row"}, 386 {6, "third rowthird row"}, 387 }, 388 }, 389 { 390 WriteQuery: "INSERT INTO mytable (i,s) SELECT i + 3, concat(s,s) from mytable order by 1 desc", 391 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 392 SelectQuery: "SELECT * FROM mytable ORDER BY i, s", 393 ExpectedSelect: []sql.Row{ 394 {1, "first row"}, 395 {2, "second row"}, 396 {3, "third row"}, 397 {4, "first rowfirst row"}, 398 {5, "second rowsecond row"}, 399 {6, "third rowthird row"}, 400 }, 401 }, 402 { 403 WriteQuery: `INSERT INTO mytable (i,s) SELECT sub.i + 10, ot.s2 404 FROM othertable ot INNER JOIN 405 (SELECT i, i2, s2 FROM mytable INNER JOIN othertable ON i = i2) sub 406 ON sub.i = ot.i2 order by 1`, 407 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 408 SelectQuery: "SELECT * FROM mytable where i > 10 ORDER BY i, s", 409 ExpectedSelect: []sql.Row{ 410 {11, "third"}, 411 {12, "second"}, 412 {13, "first"}, 413 }, 414 }, 415 { 416 WriteQuery: `INSERT INTO mytable (i,s) SELECT sub.i + 10, ot.s2 417 FROM (SELECT i, i2, s2 FROM mytable INNER JOIN othertable ON i = i2) sub 418 INNER JOIN othertable ot ON sub.i = ot.i2 order by 1`, 419 ExpectedWriteResult: []sql.Row{{types.NewOkResult(3)}}, 420 SelectQuery: "SELECT * FROM mytable where i > 10 ORDER BY i, s", 421 ExpectedSelect: []sql.Row{ 422 {11, "third"}, 423 {12, "second"}, 424 {13, "first"}, 425 }, 426 }, 427 { 428 WriteQuery: "INSERT INTO mytable (i,s) values (1, 'hello') ON DUPLICATE KEY UPDATE s='hello'", 429 ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}}, 430 SelectQuery: "SELECT * FROM mytable WHERE i = 1", 431 ExpectedSelect: []sql.Row{{int64(1), "hello"}}, 432 }, 433 { 434 WriteQuery: "INSERT INTO mytable (i,s) values (1, 'hello2') ON DUPLICATE KEY UPDATE s='hello3'", 435 ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}}, 436 SelectQuery: "SELECT * FROM mytable WHERE i = 1", 437 ExpectedSelect: []sql.Row{{int64(1), "hello3"}}, 438 }, 439 { 440 WriteQuery: "INSERT INTO mytable (i,s) values (1, 'hello') ON DUPLICATE KEY UPDATE i=10", 441 ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}}, 442 SelectQuery: "SELECT * FROM mytable WHERE i = 10", 443 ExpectedSelect: []sql.Row{{int64(10), "first row"}}, 444 }, 445 { 446 WriteQuery: "INSERT INTO mytable (i,s) values (1, 'hello2') ON DUPLICATE KEY UPDATE s='hello3'", 447 ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}}, 448 SelectQuery: "SELECT * FROM mytable WHERE i = 1", 449 ExpectedSelect: []sql.Row{{int64(1), "hello3"}}, 450 }, 451 { 452 WriteQuery: "INSERT INTO mytable (i,s) values (1, 'hello2'), (2, 'hello3'), (4, 'no conflict') ON DUPLICATE KEY UPDATE s='hello4'", 453 ExpectedWriteResult: []sql.Row{{types.NewOkResult(5)}}, 454 SelectQuery: "SELECT * FROM mytable ORDER BY 1", 455 ExpectedSelect: []sql.Row{ 456 {1, "hello4"}, 457 {2, "hello4"}, 458 {3, "third row"}, 459 {4, "no conflict"}, 460 }, 461 }, 462 { 463 WriteQuery: "INSERT INTO mytable (i,s) values (10, 'hello') ON DUPLICATE KEY UPDATE s='hello'", 464 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 465 SelectQuery: "SELECT * FROM mytable ORDER BY 1", 466 ExpectedSelect: []sql.Row{ 467 {1, "first row"}, 468 {2, "second row"}, 469 {3, "third row"}, 470 {10, "hello"}, 471 }, 472 }, 473 { 474 WriteQuery: "INSERT INTO mytable (i,s) values (1,'hi') ON DUPLICATE KEY UPDATE s=VALUES(s)", 475 ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}}, 476 SelectQuery: "SELECT * FROM mytable WHERE i = 1", 477 ExpectedSelect: []sql.Row{{int64(1), "hi"}}, 478 }, 479 { 480 WriteQuery: "INSERT INTO mytable (s,i) values ('dup',1) ON DUPLICATE KEY UPDATE s=CONCAT(VALUES(s), 'licate')", 481 ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}}, 482 SelectQuery: "SELECT * FROM mytable WHERE i = 1", 483 ExpectedSelect: []sql.Row{{int64(1), "duplicate"}}, 484 }, 485 { 486 WriteQuery: "INSERT INTO mytable (i,s) values (1,'mar'), (2,'par') ON DUPLICATE KEY UPDATE s=CONCAT(VALUES(s), 'tial')", 487 ExpectedWriteResult: []sql.Row{{types.NewOkResult(4)}}, 488 SelectQuery: "SELECT * FROM mytable WHERE i IN (1,2) ORDER BY i", 489 ExpectedSelect: []sql.Row{{int64(1), "martial"}, {int64(2), "partial"}}, 490 }, 491 { 492 WriteQuery: "INSERT INTO mytable (i,s) values (1,'maybe') ON DUPLICATE KEY UPDATE i=VALUES(i)+8000, s=VALUES(s)", 493 ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}}, 494 SelectQuery: "SELECT * FROM mytable WHERE i = 8001", 495 ExpectedSelect: []sql.Row{{int64(8001), "maybe"}}, 496 }, 497 { 498 WriteQuery: "INSERT INTO auto_increment_tbl (c0) values (44)", 499 ExpectedWriteResult: []sql.Row{{types.OkResult{RowsAffected: 1, InsertID: 4}}}, 500 SelectQuery: "SELECT * FROM auto_increment_tbl ORDER BY pk", 501 ExpectedSelect: []sql.Row{ 502 {1, 11}, 503 {2, 22}, 504 {3, 33}, 505 {4, 44}, 506 }, 507 }, 508 { 509 WriteQuery: "INSERT INTO auto_increment_tbl (c0) values (44),(55)", 510 ExpectedWriteResult: []sql.Row{{types.OkResult{RowsAffected: 2, InsertID: 4}}}, 511 SelectQuery: "SELECT * FROM auto_increment_tbl ORDER BY pk", 512 ExpectedSelect: []sql.Row{ 513 {1, 11}, 514 {2, 22}, 515 {3, 33}, 516 {4, 44}, 517 {5, 55}, 518 }, 519 }, 520 { 521 WriteQuery: "INSERT INTO auto_increment_tbl values (NULL, 44)", 522 ExpectedWriteResult: []sql.Row{{types.OkResult{RowsAffected: 1, InsertID: 4}}}, 523 SelectQuery: "SELECT * FROM auto_increment_tbl ORDER BY pk", 524 ExpectedSelect: []sql.Row{ 525 {1, 11}, 526 {2, 22}, 527 {3, 33}, 528 {4, 44}, 529 }, 530 }, 531 { 532 WriteQuery: "INSERT INTO auto_increment_tbl values (0, 44)", 533 ExpectedWriteResult: []sql.Row{{types.OkResult{RowsAffected: 1, InsertID: 4}}}, 534 SelectQuery: "SELECT * FROM auto_increment_tbl ORDER BY pk", 535 ExpectedSelect: []sql.Row{ 536 {1, 11}, 537 {2, 22}, 538 {3, 33}, 539 {4, 44}, 540 }, 541 }, 542 { 543 WriteQuery: "INSERT INTO auto_increment_tbl values (5, 44)", 544 ExpectedWriteResult: []sql.Row{{types.OkResult{RowsAffected: 1, InsertID: 5}}}, 545 SelectQuery: "SELECT * FROM auto_increment_tbl ORDER BY pk", 546 ExpectedSelect: []sql.Row{ 547 {1, 11}, 548 {2, 22}, 549 {3, 33}, 550 {5, 44}, 551 }, 552 }, 553 { 554 WriteQuery: "INSERT INTO auto_increment_tbl values " + 555 "(NULL, 44), (NULL, 55), (9, 99), (NULL, 110), (NULL, 121)", 556 ExpectedWriteResult: []sql.Row{{types.OkResult{RowsAffected: 5, InsertID: 4}}}, 557 SelectQuery: "SELECT * FROM auto_increment_tbl ORDER BY pk", 558 ExpectedSelect: []sql.Row{ 559 {1, 11}, 560 {2, 22}, 561 {3, 33}, 562 {4, 44}, 563 {5, 55}, 564 {9, 99}, 565 {10, 110}, 566 {11, 121}, 567 }, 568 }, 569 { 570 WriteQuery: `INSERT INTO auto_increment_tbl (c0) SELECT 44 FROM dual`, 571 ExpectedWriteResult: []sql.Row{{types.OkResult{RowsAffected: 1, InsertID: 4}}}, 572 SelectQuery: "SELECT * FROM auto_increment_tbl", 573 ExpectedSelect: []sql.Row{ 574 {1, 11}, 575 {2, 22}, 576 {3, 33}, 577 {4, 44}, 578 }, 579 }, 580 { 581 WriteQuery: `INSERT INTO othertable VALUES ("fourth", 1) ON DUPLICATE KEY UPDATE s2="fourth"`, 582 ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}}, 583 SelectQuery: "SELECT * FROM othertable", 584 ExpectedSelect: []sql.Row{ 585 sql.NewRow("first", int64(3)), 586 sql.NewRow("second", int64(2)), 587 sql.NewRow("fourth", int64(1)), 588 }, 589 }, 590 { 591 WriteQuery: `INSERT INTO othertable(S2,I2) values ('fourth',0)`, 592 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 593 SelectQuery: `SELECT * FROM othertable where s2='fourth'`, 594 ExpectedSelect: []sql.Row{ 595 {"fourth", 0}, 596 }, 597 }, 598 { 599 WriteQuery: `INSERT INTO auto_increment_tbl VALUES ('4', 44)`, 600 ExpectedWriteResult: []sql.Row{ 601 {types.OkResult{InsertID: 4, RowsAffected: 1}}, 602 }, 603 SelectQuery: `SELECT * from auto_increment_tbl where pk=4`, 604 ExpectedSelect: []sql.Row{ 605 {4, 44}, 606 }, 607 }, 608 { 609 WriteQuery: `INSERT INTO keyless (c0, c1) SELECT * from keyless where c0=0 and c1=0`, 610 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 611 SelectQuery: `SELECT * from keyless where c0=0`, 612 ExpectedSelect: []sql.Row{ 613 {0, 0}, 614 {0, 0}, 615 }, 616 }, 617 { 618 WriteQuery: `insert into keyless (c0, c1) select a.c0, a.c1 from (select 1, 1) as a(c0, c1) join keyless on a.c0 = keyless.c0`, 619 ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}}, 620 SelectQuery: `SELECT * from keyless where c0=1`, 621 ExpectedSelect: []sql.Row{ 622 {1, 1}, 623 {1, 1}, 624 {1, 1}, 625 {1, 1}, 626 }, 627 }, 628 { 629 WriteQuery: "with t (i,f) as (select 4,'fourth row' from dual) insert into mytable select i,f from t", 630 ExpectedWriteResult: []sql.Row{{types.OkResult{RowsAffected: 1}}}, 631 SelectQuery: "select * from mytable order by i", 632 ExpectedSelect: []sql.Row{ 633 sql.NewRow(1, "first row"), 634 sql.NewRow(2, "second row"), 635 sql.NewRow(3, "third row"), 636 sql.NewRow(4, "fourth row"), 637 }, 638 }, 639 { 640 WriteQuery: "with recursive t (i,f) as (select 4,4 from dual union all select i + 1, i + 1 from t where i < 5) insert into mytable select i,f from t", 641 ExpectedWriteResult: []sql.Row{{types.OkResult{RowsAffected: 2}}}, 642 SelectQuery: "select * from mytable order by i", 643 ExpectedSelect: []sql.Row{ 644 sql.NewRow(1, "first row"), 645 sql.NewRow(2, "second row"), 646 sql.NewRow(3, "third row"), 647 sql.NewRow(4, "4"), 648 sql.NewRow(5, "5"), 649 }, 650 }, 651 } 652 653 var SpatialInsertQueries = []WriteQueryTest{ 654 { 655 WriteQuery: "INSERT INTO point_table VALUES (1, POINT(1,1));", 656 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 657 SelectQuery: "SELECT * FROM point_table;", 658 ExpectedSelect: []sql.Row{{5, types.Point{X: 1, Y: 2}}, {1, types.Point{X: 1, Y: 1}}}, 659 }, 660 { 661 WriteQuery: "INSERT INTO point_table VALUES (1, 0x000000000101000000000000000000F03F0000000000000040);", 662 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 663 SelectQuery: "SELECT * FROM point_table;", 664 ExpectedSelect: []sql.Row{{5, types.Point{X: 1, Y: 2}}, {1, types.Point{X: 1, Y: 2}}}, 665 }, 666 { 667 WriteQuery: "INSERT INTO line_table VALUES (2, LINESTRING(POINT(1,2),POINT(3,4)));", 668 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 669 SelectQuery: "SELECT * FROM line_table;", 670 ExpectedSelect: []sql.Row{{0, types.LineString{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, {1, types.LineString{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}, {X: 5, Y: 6}}}}, {2, types.LineString{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}}, 671 }, 672 { 673 WriteQuery: "INSERT INTO line_table VALUES (2, 0x00000000010200000002000000000000000000F03F000000000000004000000000000008400000000000001040);", 674 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 675 SelectQuery: "SELECT * FROM line_table;", 676 ExpectedSelect: []sql.Row{{0, types.LineString{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, {1, types.LineString{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}, {X: 5, Y: 6}}}}, {2, types.LineString{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}}, 677 }, 678 { 679 WriteQuery: "INSERT INTO polygon_table VALUES (2, POLYGON(LINESTRING(POINT(1,1),POINT(1,-1),POINT(-1,-1),POINT(-1,1),POINT(1,1))));", 680 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 681 SelectQuery: "SELECT * FROM polygon_table;", 682 ExpectedSelect: []sql.Row{ 683 {0, types.Polygon{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 0, Y: 1}, {X: 1, Y: 1}, {X: 0, Y: 0}}}}}}, 684 {1, types.Polygon{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 0, Y: 1}, {X: 1, Y: 1}, {X: 0, Y: 0}}}, {Points: []types.Point{{X: 0, Y: 0}, {X: 0, Y: 1}, {X: 1, Y: 1}, {X: 0, Y: 0}}}}}}, 685 {2, types.Polygon{Lines: []types.LineString{{Points: []types.Point{{X: 1, Y: 1}, {X: 1, Y: -1}, {X: -1, Y: -1}, {X: -1, Y: 1}, {X: 1, Y: 1}}}}}}, 686 }, 687 }, 688 { 689 WriteQuery: "INSERT INTO polygon_table VALUES (2, 0x0000000001030000000100000005000000000000000000F03F000000000000F03F000000000000F03F000000000000F0BF000000000000F0BF000000000000F0BF000000000000F0BF000000000000F03F000000000000F03F000000000000F03F);", 690 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 691 SelectQuery: "SELECT * FROM polygon_table;", 692 ExpectedSelect: []sql.Row{ 693 {0, types.Polygon{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 0, Y: 1}, {X: 1, Y: 1}, {X: 0, Y: 0}}}}}}, 694 {1, types.Polygon{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 0, Y: 1}, {X: 1, Y: 1}, {X: 0, Y: 0}}}, {Points: []types.Point{{X: 0, Y: 0}, {X: 0, Y: 1}, {X: 1, Y: 1}, {X: 0, Y: 0}}}}}}, 695 {2, types.Polygon{Lines: []types.LineString{{Points: []types.Point{{X: 1, Y: 1}, {X: 1, Y: -1}, {X: -1, Y: -1}, {X: -1, Y: 1}, {X: 1, Y: 1}}}}}}}, 696 }, 697 { 698 WriteQuery: "INSERT INTO geometry_table VALUES (100, POINT(123.456,7.89));", 699 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 700 SelectQuery: "SELECT * FROM geometry_table;", 701 ExpectedSelect: []sql.Row{ 702 {1, types.Point{X: 1, Y: 2}}, 703 {2, types.Point{SRID: 4326, X: 1, Y: 2}}, 704 {3, types.LineString{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, 705 {4, types.LineString{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}, 706 {5, types.Polygon{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 0, Y: 1}, {X: 1, Y: 1}, {X: 0, Y: 0}}}}}}, 707 {6, types.Polygon{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 0, Y: 0}, {SRID: 4326, X: 0, Y: 1}, {SRID: 4326, X: 1, Y: 1}, {SRID: 4326, X: 0, Y: 0}}}}}}, 708 {7, types.MultiPoint{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, 709 {8, types.MultiPoint{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}, 710 {9, types.MultiLineString{Lines: []types.LineString{{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}}}, 711 {10, types.MultiLineString{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}}}, 712 {11, types.MultiPolygon{Polygons: []types.Polygon{{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 1, Y: 2}, {X: 3, Y: 4}, {X: 0, Y: 0}}}}}}}}, 713 {12, types.MultiPolygon{SRID: 4326, Polygons: []types.Polygon{{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 0, Y: 0}, {SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}, {SRID: 4326, X: 0, Y: 0}}}}}}}}, 714 {13, types.GeomColl{Geoms: []types.GeometryValue{types.GeomColl{Geoms: []types.GeometryValue{}}}}}, 715 {14, types.GeomColl{SRID: 4326, Geoms: []types.GeometryValue{types.GeomColl{SRID: 4326, Geoms: []types.GeometryValue{}}}}}, 716 {100, types.Point{X: 123.456, Y: 7.89}}, 717 }, 718 }, 719 { 720 WriteQuery: "INSERT INTO geometry_table VALUES (100, 0x00000000010100000077BE9F1A2FDD5E408FC2F5285C8F1F40);", 721 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 722 SelectQuery: "SELECT * FROM geometry_table;", 723 ExpectedSelect: []sql.Row{ 724 {1, types.Point{X: 1, Y: 2}}, 725 {2, types.Point{SRID: 4326, X: 1, Y: 2}}, 726 {3, types.LineString{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, 727 {4, types.LineString{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}, 728 {5, types.Polygon{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 0, Y: 1}, {X: 1, Y: 1}, {X: 0, Y: 0}}}}}}, 729 {6, types.Polygon{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 0, Y: 0}, {SRID: 4326, X: 0, Y: 1}, {SRID: 4326, X: 1, Y: 1}, {SRID: 4326, X: 0, Y: 0}}}}}}, 730 {7, types.MultiPoint{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, 731 {8, types.MultiPoint{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}, 732 {9, types.MultiLineString{Lines: []types.LineString{{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}}}, 733 {10, types.MultiLineString{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}}}, 734 {11, types.MultiPolygon{Polygons: []types.Polygon{{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 1, Y: 2}, {X: 3, Y: 4}, {X: 0, Y: 0}}}}}}}}, 735 {12, types.MultiPolygon{SRID: 4326, Polygons: []types.Polygon{{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 0, Y: 0}, {SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}, {SRID: 4326, X: 0, Y: 0}}}}}}}}, 736 {13, types.GeomColl{Geoms: []types.GeometryValue{types.GeomColl{Geoms: []types.GeometryValue{}}}}}, 737 {14, types.GeomColl{SRID: 4326, Geoms: []types.GeometryValue{types.GeomColl{SRID: 4326, Geoms: []types.GeometryValue{}}}}}, 738 {100, types.Point{X: 123.456, Y: 7.89}}, 739 }, 740 }, 741 { 742 WriteQuery: "INSERT INTO geometry_table VALUES (100, LINESTRING(POINT(1,2),POINT(3,4)));", 743 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 744 SelectQuery: "SELECT * FROM geometry_table;", 745 ExpectedSelect: []sql.Row{ 746 {1, types.Point{X: 1, Y: 2}}, 747 {2, types.Point{SRID: 4326, X: 1, Y: 2}}, 748 {3, types.LineString{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, 749 {4, types.LineString{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}, 750 {5, types.Polygon{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 0, Y: 1}, {X: 1, Y: 1}, {X: 0, Y: 0}}}}}}, 751 {6, types.Polygon{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 0, Y: 0}, {SRID: 4326, X: 0, Y: 1}, {SRID: 4326, X: 1, Y: 1}, {SRID: 4326, X: 0, Y: 0}}}}}}, 752 {7, types.MultiPoint{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, 753 {8, types.MultiPoint{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}, 754 {9, types.MultiLineString{Lines: []types.LineString{{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}}}, 755 {10, types.MultiLineString{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}}}, 756 {11, types.MultiPolygon{Polygons: []types.Polygon{{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 1, Y: 2}, {X: 3, Y: 4}, {X: 0, Y: 0}}}}}}}}, 757 {12, types.MultiPolygon{SRID: 4326, Polygons: []types.Polygon{{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 0, Y: 0}, {SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}, {SRID: 4326, X: 0, Y: 0}}}}}}}}, 758 {13, types.GeomColl{Geoms: []types.GeometryValue{types.GeomColl{Geoms: []types.GeometryValue{}}}}}, 759 {14, types.GeomColl{SRID: 4326, Geoms: []types.GeometryValue{types.GeomColl{SRID: 4326, Geoms: []types.GeometryValue{}}}}}, 760 {100, types.LineString{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, 761 }, 762 }, 763 { 764 WriteQuery: "INSERT INTO geometry_table VALUES (100, 0x00000000010200000002000000000000000000F03F000000000000004000000000000008400000000000001040);", 765 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 766 SelectQuery: "SELECT * FROM geometry_table;", 767 ExpectedSelect: []sql.Row{ 768 {1, types.Point{X: 1, Y: 2}}, 769 {2, types.Point{SRID: 4326, X: 1, Y: 2}}, 770 {3, types.LineString{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, 771 {4, types.LineString{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}, 772 {5, types.Polygon{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 0, Y: 1}, {X: 1, Y: 1}, {X: 0, Y: 0}}}}}}, 773 {6, types.Polygon{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 0, Y: 0}, {SRID: 4326, X: 0, Y: 1}, {SRID: 4326, X: 1, Y: 1}, {SRID: 4326, X: 0, Y: 0}}}}}}, 774 {7, types.MultiPoint{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, 775 {8, types.MultiPoint{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}, 776 {9, types.MultiLineString{Lines: []types.LineString{{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}}}, 777 {10, types.MultiLineString{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}}}, 778 {11, types.MultiPolygon{Polygons: []types.Polygon{{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 1, Y: 2}, {X: 3, Y: 4}, {X: 0, Y: 0}}}}}}}}, 779 {12, types.MultiPolygon{SRID: 4326, Polygons: []types.Polygon{{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 0, Y: 0}, {SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}, {SRID: 4326, X: 0, Y: 0}}}}}}}}, 780 {13, types.GeomColl{Geoms: []types.GeometryValue{types.GeomColl{Geoms: []types.GeometryValue{}}}}}, 781 {14, types.GeomColl{SRID: 4326, Geoms: []types.GeometryValue{types.GeomColl{SRID: 4326, Geoms: []types.GeometryValue{}}}}}, 782 {100, types.LineString{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, 783 }, 784 }, 785 { 786 WriteQuery: "INSERT INTO geometry_table VALUES (100, POLYGON(LINESTRING(POINT(1,1),POINT(1,-1),POINT(-1,-1),POINT(-1,1),POINT(1,1))));", 787 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 788 SelectQuery: "SELECT * FROM geometry_table;", 789 ExpectedSelect: []sql.Row{ 790 {1, types.Point{X: 1, Y: 2}}, 791 {2, types.Point{SRID: 4326, X: 1, Y: 2}}, 792 {3, types.LineString{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, 793 {4, types.LineString{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}, 794 {5, types.Polygon{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 0, Y: 1}, {X: 1, Y: 1}, {X: 0, Y: 0}}}}}}, 795 {6, types.Polygon{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 0, Y: 0}, {SRID: 4326, X: 0, Y: 1}, {SRID: 4326, X: 1, Y: 1}, {SRID: 4326, X: 0, Y: 0}}}}}}, 796 {7, types.MultiPoint{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, 797 {8, types.MultiPoint{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}, 798 {9, types.MultiLineString{Lines: []types.LineString{{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}}}, 799 {10, types.MultiLineString{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}}}, 800 {11, types.MultiPolygon{Polygons: []types.Polygon{{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 1, Y: 2}, {X: 3, Y: 4}, {X: 0, Y: 0}}}}}}}}, 801 {12, types.MultiPolygon{SRID: 4326, Polygons: []types.Polygon{{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 0, Y: 0}, {SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}, {SRID: 4326, X: 0, Y: 0}}}}}}}}, 802 {13, types.GeomColl{Geoms: []types.GeometryValue{types.GeomColl{Geoms: []types.GeometryValue{}}}}}, 803 {14, types.GeomColl{SRID: 4326, Geoms: []types.GeometryValue{types.GeomColl{SRID: 4326, Geoms: []types.GeometryValue{}}}}}, 804 {100, types.Polygon{Lines: []types.LineString{{Points: []types.Point{{X: 1, Y: 1}, {X: 1, Y: -1}, {X: -1, Y: -1}, {X: -1, Y: 1}, {X: 1, Y: 1}}}}}}, 805 }, 806 }, 807 { 808 WriteQuery: "INSERT INTO geometry_table VALUES (100, 0x0000000001030000000100000005000000000000000000F03F000000000000F03F000000000000F03F000000000000F0BF000000000000F0BF000000000000F0BF000000000000F0BF000000000000F03F000000000000F03F000000000000F03F);", 809 ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}}, 810 SelectQuery: "SELECT * FROM geometry_table;", 811 ExpectedSelect: []sql.Row{ 812 {1, types.Point{X: 1, Y: 2}}, 813 {2, types.Point{SRID: 4326, X: 1, Y: 2}}, 814 {3, types.LineString{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, 815 {4, types.LineString{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}, 816 {5, types.Polygon{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 0, Y: 1}, {X: 1, Y: 1}, {X: 0, Y: 0}}}}}}, 817 {6, types.Polygon{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 0, Y: 0}, {SRID: 4326, X: 0, Y: 1}, {SRID: 4326, X: 1, Y: 1}, {SRID: 4326, X: 0, Y: 0}}}}}}, 818 {7, types.MultiPoint{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}, 819 {8, types.MultiPoint{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}, 820 {9, types.MultiLineString{Lines: []types.LineString{{Points: []types.Point{{X: 1, Y: 2}, {X: 3, Y: 4}}}}}}, 821 {10, types.MultiLineString{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}}}}}}, 822 {11, types.MultiPolygon{Polygons: []types.Polygon{{Lines: []types.LineString{{Points: []types.Point{{X: 0, Y: 0}, {X: 1, Y: 2}, {X: 3, Y: 4}, {X: 0, Y: 0}}}}}}}}, 823 {12, types.MultiPolygon{SRID: 4326, Polygons: []types.Polygon{{SRID: 4326, Lines: []types.LineString{{SRID: 4326, Points: []types.Point{{SRID: 4326, X: 0, Y: 0}, {SRID: 4326, X: 1, Y: 2}, {SRID: 4326, X: 3, Y: 4}, {SRID: 4326, X: 0, Y: 0}}}}}}}}, 824 {13, types.GeomColl{Geoms: []types.GeometryValue{types.GeomColl{Geoms: []types.GeometryValue{}}}}}, 825 {14, types.GeomColl{SRID: 4326, Geoms: []types.GeometryValue{types.GeomColl{SRID: 4326, Geoms: []types.GeometryValue{}}}}}, 826 {100, types.Polygon{Lines: []types.LineString{{Points: []types.Point{{X: 1, Y: 1}, {X: 1, Y: -1}, {X: -1, Y: -1}, {X: -1, Y: 1}, {X: 1, Y: 1}}}}}}, 827 }, 828 }, 829 } 830 831 var InsertScripts = []ScriptTest{ 832 { 833 // https://github.com/dolthub/dolt/issues/7322 834 Name: "issue 7322: values expression is subquery", 835 SetUpScript: []string{ 836 "create table xy (x int auto_increment primary key, y varchar(50) not null)", 837 "create table uv (u int auto_increment primary key, v varchar(50) not null, x_id int, constraint u_x_fk foreign key (x_id) references xy (x))", 838 "insert into xy values (1,'admin'), (2, 'standard')", 839 }, 840 Assertions: []ScriptTestAssertion{ 841 { 842 Query: "INSERT INTO uv(v, x_id) VALUES ('test', (SELECT x FROM xy WHERE y = 'admin'));", 843 Expected: []sql.Row{{types.OkResult{RowsAffected: 1, InsertID: 1}}}, 844 }, 845 { 846 Query: "INSERT INTO uv(v, x_id) VALUES ('test', (SELECT x FROM xy WHERE x > 0));", 847 ExpectedErr: sql.ErrExpectedSingleRow, 848 }, 849 { 850 Query: "select * from uv", 851 Expected: []sql.Row{{1, "test", 1}}, 852 }, 853 }, 854 }, 855 { 856 // https://github.com/dolthub/dolt/issues/6675 857 Name: "issue 6675: on duplicate rearranged getfield indexes from select source", 858 SetUpScript: []string{ 859 "create table xy (x int primary key, y datetime)", 860 "insert into xy values (0,'2023-09-16')", 861 }, 862 Assertions: []ScriptTestAssertion{ 863 { 864 Query: "INSERT INTO xy (y,x) select * from (select cast('2019-12-31T12:00:00Z' as date), 0) dt(a,b) ON DUPLICATE KEY UPDATE x=dt.b+1, y=dt.a", 865 Expected: []sql.Row{{types.NewOkResult(2)}}, 866 }, 867 { 868 Query: "select * from xy", 869 Expected: []sql.Row{{1, time.Date(2019, time.December, 31, 0, 0, 0, 0, time.UTC)}}, 870 }, 871 }, 872 }, 873 { 874 // https://github.com/dolthub/dolt/issues/4857 875 Name: "issue 4857: insert cte column alias with table alias qualify panic", 876 SetUpScript: []string{ 877 "create table xy (x int primary key, y int)", 878 "insert into xy values (0,0), (1,1), (2,2)", 879 }, 880 Assertions: []ScriptTestAssertion{ 881 { 882 Query: `With a as ( 883 With b as ( 884 Select sum(x) as x, y from xy where x < 2 group by y 885 ) 886 Select * from b d 887 ) insert into xy (x,y) select x+9,y+9 from a;`, 888 Expected: []sql.Row{{types.OkResult{RowsAffected: 2, InsertID: 0}}}, 889 }, 890 }, 891 }, 892 { 893 Name: "INSERT zero date DATETIME NOT NULL is valid", 894 SetUpScript: []string{ 895 "CREATE TABLE t1 (dt datetime not null)", 896 }, 897 Assertions: []ScriptTestAssertion{ 898 { 899 Query: "INSERT INTO t1 (dt) VALUES ('0001-01-01 00:00:00');", 900 Expected: []sql.Row{{types.NewOkResult(1)}}, 901 }, 902 }, 903 }, 904 { 905 Name: "insert into sparse auto_increment table", 906 SetUpScript: []string{ 907 "create table auto (pk int primary key auto_increment)", 908 "insert into auto values (10), (20), (30)", 909 "insert into auto values (NULL)", 910 "insert into auto values (40)", 911 "insert into auto values (0)", 912 }, 913 Assertions: []ScriptTestAssertion{ 914 { 915 Query: "select * from auto order by 1", 916 Expected: []sql.Row{ 917 {10}, {20}, {30}, {31}, {40}, {41}, 918 }, 919 }, 920 }, 921 }, 922 { 923 Name: "insert negative values into auto_increment values", 924 SetUpScript: []string{ 925 "create table auto (pk int primary key auto_increment)", 926 "insert into auto values (10), (20), (30)", 927 "insert into auto values (-1), (-2), (-3)", 928 "insert into auto () values ()", 929 "insert into auto values (0), (0), (0)", 930 }, 931 Assertions: []ScriptTestAssertion{ 932 { 933 Query: "select * from auto order by 1", 934 Expected: []sql.Row{ 935 {-3}, {-2}, {-1}, {10}, {20}, {30}, {31}, {32}, {33}, {34}, 936 }, 937 }, 938 }, 939 }, 940 { 941 Name: "insert into auto_increment unique key column", 942 SetUpScript: []string{ 943 "create table auto (pk int primary key, npk int unique auto_increment)", 944 "insert into auto (pk) values (10), (20), (30)", 945 }, 946 Assertions: []ScriptTestAssertion{ 947 { 948 Query: "select * from auto order by 1", 949 Expected: []sql.Row{ 950 {10, 1}, {20, 2}, {30, 3}, 951 }, 952 }, 953 }, 954 }, 955 { 956 Name: "insert into auto_increment with multiple unique key columns", 957 SetUpScript: []string{ 958 "create table auto (pk int primary key, npk1 int auto_increment, npk2 int, unique(npk1, npk2))", 959 "insert into auto (pk) values (10), (20), (30)", 960 }, 961 Assertions: []ScriptTestAssertion{ 962 { 963 Query: "select * from auto order by 1", 964 Expected: []sql.Row{ 965 {10, 1, nil}, {20, 2, nil}, {30, 3, nil}, 966 }, 967 }, 968 }, 969 }, 970 { 971 Name: "insert into auto_increment key/index column", 972 SetUpScript: []string{ 973 "create table auto_no_primary (i int auto_increment, index(i))", 974 "insert into auto_no_primary (i) values (0), (0), (0)", 975 }, 976 Assertions: []ScriptTestAssertion{ 977 { 978 Query: "select * from auto_no_primary order by 1", 979 Expected: []sql.Row{ 980 {1}, {2}, {3}, 981 }, 982 }, 983 }, 984 }, 985 { 986 Name: "insert into auto_increment with multiple key/index columns", 987 SetUpScript: []string{ 988 "create table auto_no_primary (i int auto_increment, j int, index(i))", 989 "insert into auto_no_primary (i) values (0), (0), (0)", 990 }, 991 Assertions: []ScriptTestAssertion{ 992 { 993 Query: "select * from auto_no_primary order by 1", 994 Expected: []sql.Row{ 995 {1, nil}, {2, nil}, {3, nil}, 996 }, 997 }, 998 }, 999 }, 1000 { 1001 Name: "auto increment table handles deletes", 1002 SetUpScript: []string{ 1003 "create table auto (pk int primary key auto_increment)", 1004 "insert into auto values (10)", 1005 "delete from auto where pk = 10", 1006 "insert into auto values (NULL)", 1007 }, 1008 Assertions: []ScriptTestAssertion{ 1009 { 1010 Query: "select * from auto order by 1", 1011 Expected: []sql.Row{ 1012 {11}, 1013 }, 1014 }, 1015 }, 1016 }, 1017 { 1018 Name: "create auto_increment table with out-of-line primary key def", 1019 SetUpScript: []string{ 1020 `create table auto ( 1021 pk int auto_increment, 1022 c0 int, 1023 primary key(pk) 1024 );`, 1025 "insert into auto values (NULL,10), (NULL,20), (NULL,30)", 1026 }, 1027 Assertions: []ScriptTestAssertion{ 1028 { 1029 Query: "select * from auto order by 1", 1030 Expected: []sql.Row{ 1031 {1, 10}, {2, 20}, {3, 30}, 1032 }, 1033 }, 1034 }, 1035 }, 1036 { 1037 Name: "alter auto_increment value", 1038 SetUpScript: []string{ 1039 `create table auto ( 1040 pk int auto_increment, 1041 c0 int, 1042 primary key(pk) 1043 );`, 1044 "insert into auto values (NULL,10), (NULL,20), (NULL,30)", 1045 "alter table auto auto_increment 9;", 1046 }, 1047 Assertions: []ScriptTestAssertion{ 1048 { 1049 Query: "SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'auto' AND table_schema = DATABASE()", 1050 Expected: []sql.Row{{uint64(9)}}, 1051 }, 1052 { 1053 Query: "insert into auto values (NULL,90)", 1054 Expected: []sql.Row{{types.OkResult{ 1055 RowsAffected: 1, 1056 InsertID: 9, 1057 }}}, 1058 }, 1059 { 1060 Query: "select * from auto order by 1", 1061 Expected: []sql.Row{ 1062 {1, 10}, {2, 20}, {3, 30}, {9, 90}, 1063 }, 1064 }, 1065 }, 1066 }, 1067 { 1068 Name: "alter auto_increment value to float", 1069 SetUpScript: []string{ 1070 `create table auto ( 1071 pk int auto_increment, 1072 c0 int, 1073 primary key(pk) 1074 );`, 1075 "insert into auto values (NULL,10), (NULL,20), (NULL,30)", 1076 "alter table auto auto_increment = 19.9;", 1077 "insert into auto values (NULL,190)", 1078 }, 1079 Assertions: []ScriptTestAssertion{ 1080 { 1081 Query: "select * from auto order by 1", 1082 Expected: []sql.Row{ 1083 {1, 10}, {2, 20}, {3, 30}, {19, 190}, 1084 }, 1085 }, 1086 }, 1087 }, 1088 { 1089 Name: "auto increment on tinyint", 1090 SetUpScript: []string{ 1091 "create table auto (pk tinyint primary key auto_increment)", 1092 "insert into auto values (NULL),(10),(0)", 1093 }, 1094 Assertions: []ScriptTestAssertion{ 1095 { 1096 Query: "select * from auto order by 1", 1097 Expected: []sql.Row{ 1098 {1}, {10}, {11}, 1099 }, 1100 }, 1101 }, 1102 }, 1103 { 1104 Name: "auto increment on smallint", 1105 SetUpScript: []string{ 1106 "create table auto (pk smallint primary key auto_increment)", 1107 "insert into auto values (NULL),(10),(0)", 1108 }, 1109 Assertions: []ScriptTestAssertion{ 1110 { 1111 Query: "select * from auto order by 1", 1112 Expected: []sql.Row{ 1113 {1}, {10}, {11}, 1114 }, 1115 }, 1116 }, 1117 }, 1118 { 1119 Name: "auto increment on mediumint", 1120 SetUpScript: []string{ 1121 "create table auto (pk mediumint primary key auto_increment)", 1122 "insert into auto values (NULL),(10),(0)", 1123 }, 1124 Assertions: []ScriptTestAssertion{ 1125 { 1126 Query: "select * from auto order by 1", 1127 Expected: []sql.Row{ 1128 {1}, {10}, {11}, 1129 }, 1130 }, 1131 }, 1132 }, 1133 { 1134 Name: "auto increment on int", 1135 SetUpScript: []string{ 1136 "create table auto (pk int primary key auto_increment)", 1137 "insert into auto values (NULL),(10),(0)", 1138 }, 1139 Assertions: []ScriptTestAssertion{ 1140 { 1141 Query: "select * from auto order by 1", 1142 Expected: []sql.Row{ 1143 {1}, {10}, {11}, 1144 }, 1145 }, 1146 }, 1147 }, 1148 { 1149 Name: "auto increment on bigint", 1150 SetUpScript: []string{ 1151 "create table auto (pk bigint primary key auto_increment)", 1152 "insert into auto values (NULL),(10),(0)", 1153 }, 1154 Assertions: []ScriptTestAssertion{ 1155 { 1156 Query: "select * from auto order by 1", 1157 Expected: []sql.Row{ 1158 {1}, {10}, {11}, 1159 }, 1160 }, 1161 }, 1162 }, 1163 { 1164 Name: "auto increment on tinyint unsigned", 1165 SetUpScript: []string{ 1166 "create table auto (pk tinyint unsigned primary key auto_increment)", 1167 "insert into auto values (NULL),(10),(0)", 1168 }, 1169 Assertions: []ScriptTestAssertion{ 1170 { 1171 Query: "select * from auto order by 1", 1172 Expected: []sql.Row{ 1173 {uint64(1)}, {uint64(10)}, {uint64(11)}, 1174 }, 1175 }, 1176 }, 1177 }, 1178 { 1179 Name: "auto increment on smallint unsigned", 1180 SetUpScript: []string{ 1181 "create table auto (pk smallint unsigned primary key auto_increment)", 1182 "insert into auto values (NULL),(10),(0)", 1183 }, 1184 Assertions: []ScriptTestAssertion{ 1185 { 1186 Query: "select * from auto order by 1", 1187 Expected: []sql.Row{ 1188 {uint64(1)}, {uint64(10)}, {uint64(11)}, 1189 }, 1190 }, 1191 }, 1192 }, 1193 { 1194 Name: "auto increment on mediumint unsigned", 1195 SetUpScript: []string{ 1196 "create table auto (pk mediumint unsigned primary key auto_increment)", 1197 "insert into auto values (NULL),(10),(0)", 1198 }, 1199 Assertions: []ScriptTestAssertion{ 1200 { 1201 Query: "select * from auto order by 1", 1202 Expected: []sql.Row{ 1203 {uint64(1)}, {uint64(10)}, {uint64(11)}, 1204 }, 1205 }, 1206 }, 1207 }, 1208 { 1209 Name: "auto increment on int unsigned", 1210 SetUpScript: []string{ 1211 "create table auto (pk int unsigned primary key auto_increment)", 1212 "insert into auto values (NULL),(10),(0)", 1213 }, 1214 Assertions: []ScriptTestAssertion{ 1215 { 1216 Query: "select * from auto order by 1", 1217 Expected: []sql.Row{ 1218 {uint64(1)}, {uint64(10)}, {uint64(11)}, 1219 }, 1220 }, 1221 }, 1222 }, 1223 { 1224 Name: "auto increment on bigint unsigned", 1225 SetUpScript: []string{ 1226 "create table auto (pk bigint unsigned primary key auto_increment)", 1227 "insert into auto values (NULL),(10),(0)", 1228 }, 1229 Assertions: []ScriptTestAssertion{ 1230 { 1231 Query: "select * from auto order by 1", 1232 Expected: []sql.Row{ 1233 {uint64(1)}, {uint64(10)}, {uint64(11)}, 1234 }, 1235 }, 1236 }, 1237 }, 1238 { 1239 Name: "auto increment on float", 1240 SetUpScript: []string{ 1241 "create table auto (pk float primary key auto_increment)", 1242 "insert into auto values (NULL),(10),(0)", 1243 }, 1244 Assertions: []ScriptTestAssertion{ 1245 { 1246 Query: "select * from auto order by 1", 1247 Expected: []sql.Row{ 1248 {float64(1)}, {float64(10)}, {float64(11)}, 1249 }, 1250 }, 1251 }, 1252 }, 1253 { 1254 Name: "auto increment on double", 1255 SetUpScript: []string{ 1256 "create table auto (pk double primary key auto_increment)", 1257 "insert into auto values (NULL),(10),(0)", 1258 }, 1259 Assertions: []ScriptTestAssertion{ 1260 { 1261 Query: "select * from auto order by 1", 1262 Expected: []sql.Row{ 1263 {float64(1)}, {float64(10)}, {float64(11)}, 1264 }, 1265 }, 1266 }, 1267 }, 1268 { 1269 Name: "explicit DEFAULT", 1270 SetUpScript: []string{ 1271 "CREATE TABLE t1(id int DEFAULT '2', dt datetime DEFAULT now());", 1272 "CREATE TABLE t2(id varchar(100) DEFAULT (uuid()));", 1273 "CREATE TABLE t3(a int DEFAULT '1', b int default (2 * a));", 1274 "CREATE TABLE t4(c0 varchar(10) null default 'c0', c1 varchar(10) null default 'c1');", 1275 // MySQL allows the current_timestamp() function to NOT be in parens when used as a default 1276 // https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html 1277 "CREATE TABLE t5(c0 varchar(100) DEFAULT (repeat('_', 100)), c1 datetime DEFAULT current_timestamp());", 1278 // Regression test case for custom column ordering: https://github.com/dolthub/dolt/issues/4004 1279 "create table t6 (color enum('red', 'blue', 'green') default 'blue', createdAt timestamp default (current_timestamp()));", 1280 }, 1281 Assertions: []ScriptTestAssertion{ 1282 { 1283 Query: "INSERT INTO T1 values (DEFAULT, DEFAULT)", 1284 Expected: []sql.Row{{types.OkResult{RowsAffected: 1}}}, 1285 }, 1286 { 1287 Query: "INSERT INTO t1 (id, dt) values (DEFAULT, DEFAULT)", 1288 Expected: []sql.Row{{types.OkResult{RowsAffected: 1}}}, 1289 }, 1290 { 1291 Query: "INSERT INTO t1 (dt, ID) values (DEFAULT, DEFAULT)", 1292 Expected: []sql.Row{{types.OkResult{RowsAffected: 1}}}, 1293 }, 1294 { 1295 Query: "INSERT INTO t1 (ID) values (DEFAULT), (3)", 1296 Expected: []sql.Row{{types.OkResult{RowsAffected: 2}}}, 1297 }, 1298 { 1299 Query: "INSERT INTO t1 (dt) values (DEFAULT), ('1981-02-16 00:00:00')", 1300 Expected: []sql.Row{{types.OkResult{RowsAffected: 2}}}, 1301 }, 1302 { 1303 Query: "INSERT INTO t1 values (100, '2000-01-01 12:34:56'), (DEFAULT, DEFAULT)", 1304 Expected: []sql.Row{{types.OkResult{RowsAffected: 2}}}, 1305 }, 1306 { 1307 Query: "INSERT INTO t1 (id, dt) values (100, '2022-01-01 01:01:01'), (DEFAULT, DEFAULT)", 1308 Expected: []sql.Row{{types.OkResult{RowsAffected: 2}}}, 1309 }, 1310 { 1311 Query: "INSERT INTO t1 (id) values (10), (DEFAULT)", 1312 Expected: []sql.Row{{types.OkResult{RowsAffected: 2}}}, 1313 }, 1314 { 1315 Query: "INSERT INTO t1 (DT) values ('2022-02-02 02:02:02'), (DEFAULT)", 1316 Expected: []sql.Row{{types.OkResult{RowsAffected: 2}}}, 1317 }, 1318 { 1319 Query: "INSERT INTO t2 values ('10'), (DEFAULT)", 1320 Expected: []sql.Row{{types.OkResult{RowsAffected: 2}}}, 1321 }, 1322 { 1323 Query: "INSERT INTO t2 (id) values (DEFAULT), ('11'), (DEFAULT)", 1324 Expected: []sql.Row{{types.OkResult{RowsAffected: 3}}}, 1325 }, 1326 { 1327 Query: "select count(distinct id) from t2", 1328 Expected: []sql.Row{{5}}, 1329 }, 1330 { 1331 Query: "INSERT INTO t3 (a) values (DEFAULT), ('2'), (DEFAULT)", 1332 Expected: []sql.Row{{types.OkResult{RowsAffected: 3}}}, 1333 }, 1334 { 1335 Query: "SELECT b from t3 order by b asc", 1336 Expected: []sql.Row{{2}, {2}, {4}}, 1337 }, 1338 { 1339 Query: "INSERT INTO T4 (c1, c0) values (DEFAULT, NULL)", 1340 Expected: []sql.Row{{types.OkResult{RowsAffected: 1}}}, 1341 }, 1342 { 1343 Query: "select * from t4", 1344 Expected: []sql.Row{{nil, "c1"}}, 1345 }, 1346 { 1347 Query: "INSERT INTO T5 values (DEFAULT, DEFAULT)", 1348 Expected: []sql.Row{{types.OkResult{RowsAffected: 1}}}, 1349 }, 1350 { 1351 Query: "INSERT INTO T5 (c0, c1) values (DEFAULT, DEFAULT)", 1352 Expected: []sql.Row{{types.OkResult{RowsAffected: 1}}}, 1353 }, 1354 { 1355 Query: "INSERT INTO T5 (c1) values (DEFAULT)", 1356 Expected: []sql.Row{{types.OkResult{RowsAffected: 1}}}, 1357 }, 1358 { 1359 // Custom column order should use the correct column defaults 1360 Query: "insert into T6(createdAt, color) values (DEFAULT, DEFAULT);", 1361 Expected: []sql.Row{{types.OkResult{RowsAffected: 1}}}, 1362 }, 1363 }, 1364 }, 1365 { 1366 Name: "Explicit default with column reference", 1367 SetUpScript: []string{ 1368 "CREATE TABLE t1 (a int default 1, b int default (a+1));", 1369 }, 1370 Assertions: []ScriptTestAssertion{ 1371 { 1372 Query: "INSERT INTO t1 (a,b) values (1, DEFAULT)", 1373 Expected: []sql.Row{{types.OkResult{RowsAffected: 1}}}, 1374 }, 1375 { 1376 Query: "select * from t1 order by a", 1377 Expected: []sql.Row{{1, 2}}, 1378 }, 1379 { 1380 Query: "INSERT INTO t1 values (2, DEFAULT)", 1381 Expected: []sql.Row{{types.OkResult{RowsAffected: 1}}}, 1382 }, 1383 { 1384 Query: "select * from t1 where a = 2 order by a", 1385 Expected: []sql.Row{{2, 3}}, 1386 }, 1387 { 1388 Query: "INSERT INTO t1 (b,a) values (DEFAULT, 3)", 1389 Expected: []sql.Row{{types.OkResult{RowsAffected: 1}}}, 1390 }, 1391 { 1392 Query: "select * from t1 where a = 3 order by a", 1393 Expected: []sql.Row{{3, 4}}, 1394 }, 1395 }, 1396 }, 1397 { 1398 Name: "Try INSERT IGNORE with primary key, non null, and single row violations", 1399 SetUpScript: []string{ 1400 "CREATE TABLE y (pk int primary key, c1 int NOT NULL);", 1401 "INSERT IGNORE INTO y VALUES (1, 1), (1,2), (2, 2), (3, 3)", 1402 }, 1403 Assertions: []ScriptTestAssertion{ 1404 { 1405 Query: "SELECT * FROM y", 1406 Expected: []sql.Row{ 1407 {1, 1}, {2, 2}, {3, 3}, 1408 }, 1409 }, 1410 { 1411 Query: "INSERT IGNORE INTO y VALUES (1, 2), (4,4)", 1412 Expected: []sql.Row{ 1413 {types.OkResult{RowsAffected: 1}}, 1414 }, 1415 ExpectedWarning: mysql.ERDupEntry, 1416 }, 1417 { 1418 Query: "INSERT IGNORE INTO y VALUES (5, NULL)", 1419 Expected: []sql.Row{ 1420 {types.OkResult{RowsAffected: 1}}, 1421 }, 1422 ExpectedWarning: mysql.ERBadNullError, 1423 }, 1424 { 1425 Query: "INSERT IGNORE INTO y SELECT * FROM y WHERE pk=(SELECT pk+10 FROM y WHERE pk > 1);", 1426 Expected: []sql.Row{ 1427 {types.OkResult{RowsAffected: 0}}, 1428 }, 1429 ExpectedWarning: mysql.ERSubqueryNo1Row, 1430 }, 1431 { 1432 Query: "INSERT IGNORE INTO y SELECT 10, 0 FROM dual WHERE 1=(SELECT 1 FROM dual UNION SELECT 2 FROM dual);", 1433 Expected: []sql.Row{ 1434 {types.OkResult{RowsAffected: 0}}, 1435 }, 1436 ExpectedWarning: mysql.ERSubqueryNo1Row, 1437 }, 1438 { 1439 Query: "INSERT IGNORE INTO y SELECT 11, 0 FROM dual WHERE 1=(SELECT 1 FROM dual UNION SELECT 2 FROM dual) UNION SELECT 12, 0 FROM dual;", 1440 Expected: []sql.Row{ 1441 {types.OkResult{RowsAffected: 1}}, 1442 }, 1443 ExpectedWarning: mysql.ERSubqueryNo1Row, 1444 }, 1445 { 1446 Query: "INSERT IGNORE INTO y SELECT 13, 0 FROM dual UNION SELECT 14, 0 FROM dual WHERE 1=(SELECT 1 FROM dual UNION SELECT 2 FROM dual);", 1447 Expected: []sql.Row{ 1448 {types.OkResult{RowsAffected: 1}}, 1449 }, 1450 ExpectedWarning: mysql.ERSubqueryNo1Row, 1451 }, 1452 { 1453 Query: "INSERT IGNORE INTO y VALUES (3, 8)", 1454 Expected: []sql.Row{ 1455 {types.OkResult{RowsAffected: 0}}, 1456 }, 1457 ExpectedWarning: mysql.ERDupEntry, 1458 }, 1459 }, 1460 }, 1461 { 1462 Name: "INSERT Accumulator tests", 1463 SetUpScript: []string{ 1464 "CREATE TABLE test(pk int primary key, val int)", 1465 "INSERT INTO test values (1,1)", 1466 }, 1467 Assertions: []ScriptTestAssertion{ 1468 { 1469 Query: `INSERT INTO test VALUES (2,2),(2,3)`, 1470 ExpectedErr: sql.ErrPrimaryKeyViolation, 1471 }, 1472 { 1473 Query: `DELETE FROM test where pk = 1;`, 1474 Expected: []sql.Row{{types.OkResult{RowsAffected: 1}}}, 1475 }, 1476 { 1477 Query: `INSERT INTO test VALUES (1,1)`, 1478 Expected: []sql.Row{ 1479 {types.OkResult{RowsAffected: 1}}, 1480 }, 1481 }, 1482 }, 1483 }, 1484 { 1485 Name: "INSERT Case Sensitivity", 1486 SetUpScript: []string{ 1487 "CREATE TABLE test (PK int PRIMARY KEY);", 1488 }, 1489 Assertions: []ScriptTestAssertion{ 1490 { 1491 Query: "insert into test(pk) values (1)", 1492 Expected: []sql.Row{{types.NewOkResult(1)}}, 1493 }, 1494 }, 1495 }, 1496 { 1497 Name: "INSERT string with exact char length but extra byte length", 1498 SetUpScript: []string{ 1499 "CREATE TABLE city (id int PRIMARY KEY, district char(20) NOT NULL DEFAULT '');", 1500 }, 1501 Assertions: []ScriptTestAssertion{ 1502 { 1503 Query: "INSERT INTO city VALUES (1,'San Pedro de MacorÃs');", 1504 Expected: []sql.Row{{types.NewOkResult(1)}}, 1505 }, 1506 }, 1507 }, 1508 { 1509 Name: "Insert on duplicate key", 1510 SetUpScript: []string{ 1511 `CREATE TABLE users ( 1512 id varchar(42) PRIMARY KEY 1513 )`, 1514 `CREATE TABLE nodes ( 1515 id varchar(42) PRIMARY KEY, 1516 owner varchar(42), 1517 status varchar(12), 1518 timestamp bigint NOT NULL, 1519 FOREIGN KEY(owner) REFERENCES users(id) 1520 )`, 1521 "INSERT INTO users values ('milo'), ('dabe')", 1522 "INSERT INTO nodes values ('id1', 'milo', 'off', 1)", 1523 }, 1524 Assertions: []ScriptTestAssertion{ 1525 { 1526 Query: "insert into nodes(id,owner,status,timestamp) values('id1','dabe','off',2) on duplicate key update owner='milo',status='on'", 1527 Expected: []sql.Row{ 1528 {types.OkResult{RowsAffected: 2}}, 1529 }, 1530 }, 1531 { 1532 Query: "insert into nodes(id,owner,status,timestamp) values('id2','dabe','off',3) on duplicate key update owner='milo',status='on'", 1533 Expected: []sql.Row{ 1534 {types.OkResult{RowsAffected: 1}}, 1535 }, 1536 }, 1537 { 1538 Query: "select * from nodes", 1539 Expected: []sql.Row{ 1540 {"id1", "milo", "on", 1}, 1541 {"id2", "dabe", "off", 3}, 1542 }, 1543 }, 1544 }, 1545 }, 1546 { 1547 Name: "Insert on duplicate key references table in subquery", 1548 SetUpScript: []string{ 1549 `create table a (i int primary key)`, 1550 `insert into a values (1)`, 1551 `create table b (j int primary key)`, 1552 `insert into b values (1), (2), (3)`, 1553 }, 1554 Assertions: []ScriptTestAssertion{ 1555 { 1556 Query: `insert into a (select * from b) on duplicate key update a.i = b.j + 100`, 1557 Expected: []sql.Row{ 1558 {types.OkResult{RowsAffected: 4}}, 1559 }, 1560 }, 1561 { 1562 Query: "select * from a", 1563 Expected: []sql.Row{ 1564 {101}, 1565 {2}, 1566 {3}, 1567 }, 1568 }, 1569 }, 1570 }, 1571 { 1572 Name: "Insert on duplicate key references table in aliased subquery", 1573 SetUpScript: []string{ 1574 `create table a (i int primary key)`, 1575 `insert into a values (1)`, 1576 `create table b (j int primary key)`, 1577 `insert into b values (1), (2), (3)`, 1578 }, 1579 Assertions: []ScriptTestAssertion{ 1580 { 1581 Query: `insert into a (select * from b as t) on duplicate key update a.i = b.j + 100`, 1582 ExpectedErr: sql.ErrTableNotFound, 1583 }, 1584 { 1585 Query: `insert into a (select * from b as t) on duplicate key update a.i = t.j + 100`, 1586 Expected: []sql.Row{ 1587 {types.OkResult{RowsAffected: 4}}, 1588 }, 1589 }, 1590 { 1591 Query: "select * from a", 1592 Expected: []sql.Row{ 1593 {101}, 1594 {2}, 1595 {3}, 1596 }, 1597 }, 1598 }, 1599 }, 1600 { 1601 Name: "insert on duplicate key update errors", 1602 SetUpScript: []string{ 1603 `create table a (i int primary key)`, 1604 `create table b (i int primary key)`, 1605 }, 1606 Assertions: []ScriptTestAssertion{ 1607 { 1608 Query: `insert into a (select * from b) on duplicate key update i = i`, 1609 ExpectedErr: sql.ErrAmbiguousColumnName, 1610 }, 1611 { 1612 Query: `insert into a (select * from b) on duplicate key update b.i = a.i`, 1613 ExpectedErr: sql.ErrTableNotFound, 1614 }, 1615 }, 1616 }, 1617 { 1618 Name: "Insert on duplicate key references table in subquery with join", 1619 SetUpScript: []string{ 1620 `create table a (i int primary key, j int)`, 1621 `insert into a values (1,1)`, 1622 `create table b (x int primary key)`, 1623 `insert into b values (1), (2), (3)`, 1624 `create table c (y int primary key)`, 1625 `insert into c values (1), (2), (3)`, 1626 }, 1627 Assertions: []ScriptTestAssertion{ 1628 { 1629 Query: `insert into a (select * from b join c where b.x = c.y) on duplicate key update a.j = b.x + c.y + 100`, 1630 Expected: []sql.Row{ 1631 {types.OkResult{RowsAffected: 4}}, 1632 }, 1633 }, 1634 { 1635 Query: "select * from a", 1636 Expected: []sql.Row{ 1637 {1, 102}, 1638 {2, 2}, 1639 {3, 3}, 1640 }, 1641 }, 1642 }, 1643 }, 1644 { 1645 // refer to https://github.com/dolthub/dolt/issues/6437 1646 Name: "Insert on duplicate key references table in subquery with alias", 1647 SetUpScript: []string{ 1648 `create table a (i int primary key)`, 1649 `insert into a values (1)`, 1650 `create table b (i int primary key)`, 1651 `insert into b values (1), (2), (3)`, 1652 }, 1653 Assertions: []ScriptTestAssertion{ 1654 { 1655 Query: `insert into a (select t.i from b as t, b where t.i = b.i) on duplicate key update i = b.i;`, 1656 Skip: true, 1657 Expected: []sql.Row{ 1658 {types.OkResult{RowsAffected: 2}}, 1659 }, 1660 }, 1661 { 1662 Query: "select * from a", 1663 Skip: true, 1664 Expected: []sql.Row{ 1665 {1}, 1666 {2}, 1667 {3}, 1668 }, 1669 }, 1670 }, 1671 }, 1672 { 1673 Name: "Insert on duplicate key references table in cte", 1674 SetUpScript: []string{ 1675 `create table a (i int primary key)`, 1676 `insert into a values (1)`, 1677 `create table b (j int primary key)`, 1678 `insert into b values (1), (2), (3)`, 1679 }, 1680 Assertions: []ScriptTestAssertion{ 1681 { 1682 Query: `insert into a with cte as (select * from b) select * from cte on duplicate key update a.i = cte.j + 100`, 1683 Skip: true, 1684 Expected: []sql.Row{ 1685 {types.OkResult{RowsAffected: 4}}, 1686 }, 1687 }, 1688 { 1689 Query: "select * from a", 1690 Skip: true, 1691 Expected: []sql.Row{ 1692 {101}, 1693 {2}, 1694 {3}, 1695 }, 1696 }, 1697 }, 1698 }, 1699 { 1700 Name: "Insert throws primary key violations", 1701 SetUpScript: []string{ 1702 "CREATE TABLE t (pk int PRIMARY key);", 1703 "CREATE TABLE t2 (pk1 int, pk2 int, PRIMARY KEY (pk1, pk2));", 1704 }, 1705 Assertions: []ScriptTestAssertion{ 1706 { 1707 Query: "INSERT INTO t VALUES (1), (2);", 1708 Expected: []sql.Row{{types.NewOkResult(2)}}, 1709 }, 1710 { 1711 Query: "INSERT into t VALUES (1);", 1712 ExpectedErr: sql.ErrPrimaryKeyViolation, 1713 }, 1714 { 1715 Query: "SELECT * from t;", 1716 Expected: []sql.Row{{1}, {2}}, 1717 }, 1718 { 1719 Query: "INSERT into t2 VALUES (1, 1), (2, 2);", 1720 Expected: []sql.Row{{types.NewOkResult(2)}}, 1721 }, 1722 { 1723 Query: "INSERT into t2 VALUES (1, 1);", 1724 ExpectedErr: sql.ErrPrimaryKeyViolation, 1725 }, 1726 { 1727 Query: "SELECT * from t2;", 1728 Expected: []sql.Row{{1, 1}, {2, 2}}, 1729 }, 1730 }, 1731 }, 1732 { 1733 Name: "Insert throws unique key violations", 1734 SetUpScript: []string{ 1735 "CREATE TABLE t (pk int PRIMARY key, col1 int UNIQUE);", 1736 "CREATE TABLE t2 (pk int PRIMARY key, col1 int, col2 int, UNIQUE KEY (col1, col2));", 1737 "INSERT into t VALUES (1, 1);", 1738 "INSERT into t2 VALUES (1, 1, 1);", 1739 }, 1740 Assertions: []ScriptTestAssertion{ 1741 { 1742 Query: "INSERT INTO t VALUES (2, 2), (3, 1), (4, 4);", 1743 ExpectedErr: sql.ErrUniqueKeyViolation, 1744 }, 1745 { 1746 Query: "SELECT * from t;", 1747 Expected: []sql.Row{{1, 1}}, 1748 }, 1749 { 1750 Query: "INSERT INTO t2 VALUES (2, 2, 2), (3, 1, 1), (4, 4, 4);", 1751 ExpectedErr: sql.ErrUniqueKeyViolation, 1752 }, 1753 { 1754 Query: "SELECT * from t2;", 1755 Expected: []sql.Row{{1, 1, 1}}, 1756 }, 1757 { 1758 Query: "INSERT INTO t VALUES (5, 2), (6, 2);", 1759 ExpectedErr: sql.ErrUniqueKeyViolation, 1760 }, 1761 { 1762 Query: "SELECT * from t;", 1763 Expected: []sql.Row{{1, 1}}, 1764 }, 1765 { 1766 Query: "INSERT INTO t2 VALUES (5, 2, 2), (6, 2, 2);", 1767 ExpectedErr: sql.ErrUniqueKeyViolation, 1768 }, 1769 { 1770 Query: "SELECT * from t2;", 1771 Expected: []sql.Row{{1, 1, 1}}, 1772 }, 1773 { 1774 Query: "INSERT into t2 VALUES (5, NULL, 1), (6, NULL, 1), (7, 1, NULL), (8, 1, NULL), (9, NULL, NULL), (10, NULL, NULL)", 1775 Expected: []sql.Row{{types.NewOkResult(6)}}, 1776 }, 1777 { 1778 Query: "SELECT * from t2;", 1779 Expected: []sql.Row{{1, 1, 1}, {5, nil, 1}, {6, nil, 1}, {7, 1, nil}, {8, 1, nil}, {9, nil, nil}, {10, nil, nil}}, 1780 }, 1781 }, 1782 }, 1783 { 1784 Name: "Insert throws unique key violations for keyless tables", 1785 SetUpScript: []string{ 1786 "CREATE TABLE t (not_pk int NOT NULL, col1 int UNIQUE);", 1787 "CREATE TABLE t2 (not_pk int NOT NULL, col1 int, col2 int, UNIQUE KEY (col1, col2));", 1788 "INSERT into t VALUES (1, 1);", 1789 "INSERT into t2 VALUES (1, 1, 1);", 1790 }, 1791 Assertions: []ScriptTestAssertion{ 1792 { 1793 Query: "INSERT INTO t VALUES (2, 2), (3, 1), (4, 4);", 1794 ExpectedErr: sql.ErrUniqueKeyViolation, 1795 }, 1796 { 1797 Query: "SELECT * from t;", 1798 Expected: []sql.Row{{1, 1}}, 1799 }, 1800 { 1801 Query: "INSERT INTO t2 VALUES (2, 2, 2), (3, 1, 1), (4, 4, 4);", 1802 ExpectedErr: sql.ErrUniqueKeyViolation, 1803 }, 1804 { 1805 Query: "SELECT * from t2;", 1806 Expected: []sql.Row{{1, 1, 1}}, 1807 }, 1808 { 1809 Query: "INSERT INTO t VALUES (5, 2), (6, 2);", 1810 ExpectedErr: sql.ErrUniqueKeyViolation, 1811 }, 1812 { 1813 Query: "SELECT * from t;", 1814 Expected: []sql.Row{{1, 1}}, 1815 }, 1816 { 1817 Query: "INSERT INTO t2 VALUES (5, 2, 2), (6, 2, 2);", 1818 ExpectedErr: sql.ErrUniqueKeyViolation, 1819 }, 1820 { 1821 Query: "SELECT * from t2;", 1822 Expected: []sql.Row{{1, 1, 1}}, 1823 }, 1824 { 1825 Query: "INSERT into t2 VALUES (5, NULL, 1), (6, NULL, 1), (7, 1, NULL), (8, 1, NULL), (9, NULL, NULL), (10, NULL, NULL)", 1826 Expected: []sql.Row{{types.NewOkResult(6)}}, 1827 }, 1828 { 1829 Query: "SELECT * from t2;", 1830 Expected: []sql.Row{{1, 1, 1}, {5, nil, 1}, {6, nil, 1}, {7, 1, nil}, {8, 1, nil}, {9, nil, nil}, {10, nil, nil}}, 1831 }, 1832 }, 1833 }, 1834 { 1835 Name: "Insert into unique key that overlaps with primary key", 1836 SetUpScript: []string{ 1837 "CREATE TABLE t (pk1 int, pk2 int, col int, PRIMARY KEY(pk1, pk2), UNIQUE KEY(col, pk2));", 1838 "INSERT into t (pk1, pk2, col) VALUES (1, 1, 1), (2, 1, 2);", 1839 }, 1840 Assertions: []ScriptTestAssertion{ 1841 { 1842 Query: "INSERT INTO t (pk1, pk2, col) VALUES (3, 1, 1);", 1843 ExpectedErr: sql.ErrUniqueKeyViolation, 1844 }, 1845 { 1846 Query: "UPDATE t SET col = col + 1", 1847 ExpectedErr: sql.ErrUniqueKeyViolation, 1848 }, 1849 }, 1850 }, 1851 { 1852 Name: "INSERT INTO ... SELECT works properly with ENUM", 1853 SetUpScript: []string{ 1854 "CREATE TABLE test (pk BIGINT PRIMARY KEY NOT NULL, v1 ENUM('a','b','c'));", 1855 }, 1856 Assertions: []ScriptTestAssertion{ 1857 { 1858 Query: "INSERT INTO test (pk, v1) VALUES (1, 'a');", 1859 Expected: []sql.Row{{types.NewOkResult(1)}}, 1860 }, 1861 { 1862 Query: "INSERT INTO test (pk, v1) SELECT 2 as pk, 'a' as v1;", 1863 Expected: []sql.Row{{types.NewOkResult(1)}}, 1864 }, 1865 }, 1866 }, 1867 { 1868 Name: "INSERT INTO ... SELECT works properly with SET", 1869 SetUpScript: []string{ 1870 "CREATE TABLE test (pk BIGINT PRIMARY KEY NOT NULL, v1 SET('a','b','c'));", 1871 }, 1872 Assertions: []ScriptTestAssertion{ 1873 { 1874 Query: "INSERT INTO test (pk, v1) VALUES (1, 'a');", 1875 Expected: []sql.Row{{types.NewOkResult(1)}}, 1876 }, 1877 { 1878 Query: "INSERT INTO test (pk, v1) SELECT 2 as pk, 'a' as v1;", 1879 Expected: []sql.Row{{types.NewOkResult(1)}}, 1880 }, 1881 }, 1882 }, 1883 { 1884 // https://github.com/dolthub/dolt/issues/5411 1885 Name: "Defaults with escaped strings", 1886 SetUpScript: []string{ 1887 `CREATE TABLE escpe ( 1888 id int NOT NULL AUTO_INCREMENT, 1889 t1 varchar(15) DEFAULT 'foo''s baz', 1890 t2 varchar(15) DEFAULT 'who\'s dat', 1891 t3 varchar(15) DEFAULT "joe\'s bar", 1892 t4 varchar(15) DEFAULT "quote""bazzar", 1893 t5 varchar(15) DEFAULT 'back\\''slash', 1894 t6 varchar(15) DEFAULT 'tab\ttab', 1895 t7 varchar(15) DEFAULT 'new\nline', 1896 PRIMARY KEY (id) 1897 );`, 1898 "INSERT INTO escpe VALUES ();", 1899 }, 1900 Assertions: []ScriptTestAssertion{ 1901 { 1902 Query: "SELECT t1 from escpe", 1903 Expected: []sql.Row{{"foo's baz"}}, 1904 }, 1905 { 1906 Query: "SELECT t2 from escpe", 1907 Expected: []sql.Row{{"who's dat"}}, 1908 }, 1909 { 1910 Query: "SELECT t3 from escpe", 1911 Expected: []sql.Row{{"joe's bar"}}, 1912 }, 1913 { 1914 Query: "SELECT t4 from escpe", 1915 Expected: []sql.Row{{"quote\"bazzar"}}, 1916 }, 1917 { 1918 Query: "SELECT t5 from escpe", 1919 Expected: []sql.Row{{"back\\'slash"}}, 1920 }, 1921 { 1922 Query: "SELECT t6 from escpe", 1923 Expected: []sql.Row{{"tab\ttab"}}, 1924 }, 1925 { 1926 Query: "SELECT t7 from escpe", 1927 Expected: []sql.Row{{"new\nline"}}, 1928 }, 1929 }, 1930 }, 1931 { 1932 // https://github.com/dolthub/dolt/issues/5411 1933 Name: "check constrains with escaped strings", 1934 SetUpScript: []string{ 1935 `CREATE TABLE quoted ( id int NOT NULL AUTO_INCREMENT, 1936 val varchar(15) NOT NULL CHECK (val IN ('joe''s', 1937 "jan's", 1938 'mia\\''s', 1939 'bob\'s', 1940 'tab\tvs\tcoke', 1941 'percent\%')), 1942 PRIMARY KEY (id));`, 1943 `INSERT INTO quoted VALUES (0,"joe's");`, 1944 `INSERT INTO quoted VALUES (0,"jan's");`, 1945 `INSERT INTO quoted VALUES (0,"mia\\'s");`, 1946 `INSERT INTO quoted VALUES (0,"bob's");`, 1947 `INSERT INTO quoted VALUES (0,"tab\tvs\tcoke");`, 1948 }, 1949 Assertions: []ScriptTestAssertion{ 1950 { 1951 Query: "SELECT val from quoted order by id", 1952 Expected: []sql.Row{ 1953 {"joe's"}, 1954 {"jan's"}, 1955 {"mia\\'s"}, 1956 {"bob's"}, 1957 {"tab\tvs\tcoke"}}, 1958 }, 1959 }, 1960 }, 1961 { 1962 // https://github.com/dolthub/dolt/issues/5799 1963 Name: "check IN TUPLE constraint with duplicate key update", 1964 SetUpScript: []string{ 1965 "create table alphabet (letter varchar(1), constraint `good_letters` check (letter in ('a','l','e','c')))", 1966 }, 1967 Assertions: []ScriptTestAssertion{ 1968 { 1969 // dolt table import with -u option generates a duplicate key update with values(col) 1970 Query: "insert into alphabet values ('a') on duplicate key update letter = values(letter)", 1971 Expected: []sql.Row{ 1972 {types.NewOkResult(1)}, 1973 }, 1974 }, 1975 { 1976 Query: "insert into alphabet values ('z') on duplicate key update letter = values(letter)", 1977 ExpectedErr: sql.ErrCheckConstraintViolated, 1978 }, 1979 }, 1980 }, 1981 { 1982 Name: "INSERT IGNORE works with FK Violations", 1983 SetUpScript: []string{ 1984 "CREATE TABLE t1 (id INT PRIMARY KEY, v int);", 1985 "CREATE TABLE t2 (id INT PRIMARY KEY, v2 int, CONSTRAINT mfk FOREIGN KEY (v2) REFERENCES t1(id));", 1986 "INSERT INTO t1 values (1,1)", 1987 }, 1988 Assertions: []ScriptTestAssertion{ 1989 { 1990 Query: "INSERT IGNORE INTO t2 VALUES (1,2);", 1991 Expected: []sql.Row{ 1992 {types.OkResult{RowsAffected: 0}}, 1993 }, 1994 ExpectedWarning: mysql.ErNoReferencedRow2, 1995 }, 1996 }, 1997 }, 1998 { 1999 Name: "insert duplicate key doesn't prevent other updates", 2000 SetUpScript: []string{ 2001 "CREATE TABLE t1 (pk BIGINT PRIMARY KEY, v1 VARCHAR(3));", 2002 "INSERT INTO t1 VALUES (1, 'abc');", 2003 }, 2004 Assertions: []ScriptTestAssertion{ 2005 { 2006 Query: "select * from t1 order by pk", 2007 Expected: []sql.Row{{1, "abc"}}, 2008 }, 2009 { 2010 Query: "INSERT INTO t1 VALUES (1, 'abc');", 2011 ExpectedErr: sql.ErrPrimaryKeyViolation, 2012 }, 2013 { 2014 Query: "INSERT INTO t1 VALUES (2, 'def');", 2015 Expected: []sql.Row{{types.NewOkResult(1)}}, 2016 }, 2017 { 2018 Query: "select * from t1 order by pk", 2019 Expected: []sql.Row{{1, "abc"}, {2, "def"}}, 2020 }, 2021 }, 2022 }, 2023 { 2024 Name: "insert duplicate key doesn't prevent other updates, autocommit off", 2025 SetUpScript: []string{ 2026 "CREATE TABLE t1 (pk BIGINT PRIMARY KEY, v1 VARCHAR(3));", 2027 "INSERT INTO t1 VALUES (1, 'abc');", 2028 "SET autocommit = 0;", 2029 }, 2030 Assertions: []ScriptTestAssertion{ 2031 { 2032 Query: "select * from t1 order by pk", 2033 Expected: []sql.Row{{1, "abc"}}, 2034 }, 2035 { 2036 Query: "INSERT INTO t1 VALUES (1, 'abc');", 2037 ExpectedErr: sql.ErrPrimaryKeyViolation, 2038 }, 2039 { 2040 Query: "INSERT INTO t1 VALUES (2, 'def');", 2041 Expected: []sql.Row{{types.NewOkResult(1)}}, 2042 }, 2043 { 2044 Query: "commit", 2045 SkipResultsCheck: true, 2046 }, 2047 { 2048 Query: "select * from t1 order by pk", 2049 Expected: []sql.Row{{1, "abc"}, {2, "def"}}, 2050 }, 2051 }, 2052 }, 2053 } 2054 2055 var InsertDuplicateKeyKeyless = []ScriptTest{ 2056 { 2057 Name: "insert on duplicate key for keyless table", 2058 SetUpScript: []string{ 2059 `create table t (i int unique, j varchar(128))`, 2060 }, 2061 Assertions: []ScriptTestAssertion{ 2062 { 2063 Query: `insert into t values (0, "first")`, 2064 Expected: []sql.Row{ 2065 {types.NewOkResult(1)}, 2066 }, 2067 }, 2068 { 2069 Query: `insert into t values (0, "second") on duplicate key update j = "third"`, 2070 Expected: []sql.Row{ 2071 {types.NewOkResult(2)}, 2072 }, 2073 }, 2074 { 2075 Query: `select i, j from t order by i`, 2076 Expected: []sql.Row{ 2077 {0, "third"}, 2078 }, 2079 }, 2080 }, 2081 }, 2082 { 2083 Name: "insert on duplicate key for keyless table multiple unique columns", 2084 SetUpScript: []string{ 2085 `create table t (c1 int, c2 int, c3 int, unique key(c1,c2))`, 2086 }, 2087 Assertions: []ScriptTestAssertion{ 2088 { 2089 Query: `insert into t(c1, c2, c3) values (0, 0, 0) on duplicate key update c3 = 0`, 2090 Expected: []sql.Row{ 2091 {types.NewOkResult(1)}, 2092 }, 2093 }, 2094 { 2095 Query: `select c1, c2, c3 from t order by c1`, 2096 Expected: []sql.Row{ 2097 {0, 0, 0}, 2098 }, 2099 }, 2100 { 2101 Query: `insert into t(c1, c2, c3) values (0, 0, 1) on duplicate key update c3 = 0`, 2102 Expected: []sql.Row{ 2103 {types.NewOkResult(0)}, 2104 }, 2105 }, 2106 { 2107 Query: `select c1, c2, c3 from t order by c1`, 2108 Expected: []sql.Row{ 2109 {0, 0, 0}, 2110 }, 2111 }, 2112 { 2113 Query: `insert into t(c1, c2, c3) values (0, 0, 0) on duplicate key update c3 = 1`, 2114 Expected: []sql.Row{ 2115 {types.NewOkResult(2)}, 2116 }, 2117 }, 2118 { 2119 Query: `select c1, c2, c3 from t order by c1`, 2120 Expected: []sql.Row{ 2121 {0, 0, 1}, 2122 }, 2123 }, 2124 }, 2125 }, 2126 { 2127 Name: "insert on duplicate key for keyless tables with nulls", 2128 SetUpScript: []string{ 2129 `create table t (c1 int, c2 int, c3 int, unique key(c1, c2))`, 2130 }, 2131 Assertions: []ScriptTestAssertion{ 2132 { 2133 Query: `insert into t(c1, c2, c3) values (0, null, 0) on duplicate key update c3 = 0`, 2134 Expected: []sql.Row{ 2135 {types.NewOkResult(1)}, 2136 }, 2137 }, 2138 { 2139 Query: `select c1, c2, c3 from t order by c1`, 2140 Expected: []sql.Row{ 2141 {0, nil, 0}, 2142 }, 2143 }, 2144 { 2145 Query: `insert into t(c1, c2, c3) values (0, null, 1) on duplicate key update c3 = 0`, 2146 Expected: []sql.Row{ 2147 {types.NewOkResult(1)}, 2148 }, 2149 }, 2150 { 2151 Query: `select c1, c2, c3 from t order by c1, c2, c3`, 2152 Expected: []sql.Row{ 2153 {0, nil, 0}, 2154 {0, nil, 1}, 2155 }, 2156 }, 2157 { 2158 Query: `insert into t(c1, c2, c3) values (0, null, 0) on duplicate key update c3 = 1`, 2159 Expected: []sql.Row{ 2160 {types.NewOkResult(1)}, 2161 }, 2162 }, 2163 { 2164 Query: `select c1, c2, c3 from t order by c1, c2, c3`, 2165 Expected: []sql.Row{ 2166 {0, nil, 0}, 2167 {0, nil, 0}, 2168 {0, nil, 1}, 2169 }, 2170 }, 2171 { 2172 Query: `insert into t(c1, c2, c3) values (0, 0, 0) on duplicate key update c3 = null`, 2173 Expected: []sql.Row{ 2174 {types.NewOkResult(1)}, 2175 }, 2176 }, 2177 { 2178 Query: `select c1, c2, c3 from t order by c1, c2, c3`, 2179 Expected: []sql.Row{ 2180 {0, nil, 0}, 2181 {0, nil, 0}, 2182 {0, nil, 1}, 2183 {0, 0, 0}, 2184 }, 2185 }, 2186 { 2187 Query: `insert into t(c1, c2, c3) values (0, 0, 0) on duplicate key update c3 = null`, 2188 Expected: []sql.Row{ 2189 {types.NewOkResult(2)}, 2190 }, 2191 }, 2192 { 2193 Query: `select c1, c2, c3 from t order by c1, c2, c3`, 2194 Expected: []sql.Row{ 2195 {0, nil, 0}, 2196 {0, nil, 0}, 2197 {0, nil, 1}, 2198 {0, 0, nil}, 2199 }, 2200 }, 2201 }, 2202 }, 2203 { 2204 Name: "insert on duplicate key for keyless table mixed ordering", 2205 SetUpScript: []string{ 2206 `create table t (c1 int, c2 int, c3 int, unique key(c2, c1))`, 2207 }, 2208 Assertions: []ScriptTestAssertion{ 2209 { 2210 Query: `insert into t(c1, c2, c3) values (0, 0, 0) on duplicate key update c3 = 0`, 2211 Expected: []sql.Row{ 2212 {types.NewOkResult(1)}, 2213 }, 2214 }, 2215 { 2216 Query: `select c1, c2, c3 from t order by c1`, 2217 Expected: []sql.Row{ 2218 {0, 0, 0}, 2219 }, 2220 }, 2221 { 2222 Query: `insert into t(c1, c2, c3) values (0, 0, 1) on duplicate key update c3 = 0`, 2223 Expected: []sql.Row{ 2224 {types.NewOkResult(0)}, 2225 }, 2226 }, 2227 { 2228 Query: `select c1, c2, c3 from t order by c1`, 2229 Expected: []sql.Row{ 2230 {0, 0, 0}, 2231 }, 2232 }, 2233 { 2234 Query: `insert into t(c1, c2, c3) values (0, 0, 0) on duplicate key update c3 = 1`, 2235 Expected: []sql.Row{ 2236 {types.NewOkResult(2)}, 2237 }, 2238 }, 2239 { 2240 Query: `select c1, c2, c3 from t order by c1`, 2241 Expected: []sql.Row{ 2242 {0, 0, 1}, 2243 }, 2244 }, 2245 }, 2246 }, 2247 { 2248 Name: "insert on duplicate key for keyless table multiple unique columns batched", 2249 SetUpScript: []string{ 2250 `create table t (c1 int, c2 int, c3 int, unique key(c1,c2))`, 2251 }, 2252 Assertions: []ScriptTestAssertion{ 2253 { 2254 Query: `insert into t(c1, c2, c3) values (0, 0, 0), (0, 0, 0), (0, 0, 1), (0, 0, 1) on duplicate key update c3 = 1`, 2255 Expected: []sql.Row{ 2256 {types.NewOkResult(3)}, 2257 }, 2258 }, 2259 { 2260 Query: `select c1, c2, c3 from t order by c1, c2, c3`, 2261 Expected: []sql.Row{ 2262 {0, 0, 1}, 2263 }, 2264 }, 2265 { 2266 Query: `insert into t(c1, c2, c3) values (0, 0, 1), (0, 0, 2), (0, 0, 3), (0, 0, 4) on duplicate key update c3 = 100`, 2267 Expected: []sql.Row{ 2268 {types.NewOkResult(2)}, 2269 }, 2270 }, 2271 { 2272 Query: `select c1, c2, c3 from t order by c1, c2, c3`, 2273 Expected: []sql.Row{ 2274 {0, 0, 100}, 2275 }, 2276 }, 2277 { 2278 Query: `insert into t(c1, c2, c3) values (0, 0, 1), (0, 1, 1), (0, 2, 2), (0, 3, 3) on duplicate key update c3 = 200`, 2279 Expected: []sql.Row{ 2280 {types.NewOkResult(5)}, 2281 }, 2282 }, 2283 { 2284 Query: `select c1, c2, c3 from t order by c1, c2, c3`, 2285 Expected: []sql.Row{ 2286 {0, 0, 200}, 2287 {0, 1, 1}, 2288 {0, 2, 2}, 2289 {0, 3, 3}, 2290 }, 2291 }, 2292 }, 2293 }, 2294 } 2295 2296 var InsertErrorTests = []GenericErrorQueryTest{ 2297 { 2298 Name: "try to insert empty into col without default value", 2299 Query: "INSERT INTO mytable VALUES ();", 2300 }, 2301 { 2302 Name: "try to insert empty into col without default value", 2303 Query: "INSERT INTO mytable () VALUES ();", 2304 }, 2305 { 2306 Name: "too few values", 2307 Query: "INSERT INTO mytable (s, i) VALUES ('x');", 2308 }, 2309 { 2310 Name: "too many values one column", 2311 Query: "INSERT INTO mytable (s) VALUES ('x', 999);", 2312 }, 2313 { 2314 Name: "missing binding", 2315 Query: "INSERT INTO mytable (s) VALUES (?);", 2316 }, 2317 { 2318 Name: "too many values two columns", 2319 Query: "INSERT INTO mytable (i, s) VALUES (999, 'x', 'y');", 2320 }, 2321 { 2322 Name: "too few values no columns specified", 2323 Query: "INSERT INTO mytable VALUES (999);", 2324 }, 2325 { 2326 Name: "too many values no columns specified", 2327 Query: "INSERT INTO mytable VALUES (999, 'x', 'y');", 2328 }, 2329 { 2330 Name: "non-existent column values", 2331 Query: "INSERT INTO mytable (i, s, z) VALUES (999, 'x', 999);", 2332 }, 2333 { 2334 Name: "non-existent column set", 2335 Query: "INSERT INTO mytable SET i = 999, s = 'x', z = 999;", 2336 }, 2337 { 2338 Name: "duplicate column", 2339 Query: "INSERT INTO mytable (i, s, s) VALUES (999, 'x', 'x');", 2340 }, 2341 { 2342 Name: "duplicate column set", 2343 Query: "INSERT INTO mytable SET i = 999, s = 'y', s = 'y';", 2344 }, 2345 { 2346 Name: "null given to non-nullable", 2347 Query: "INSERT INTO mytable (i, s) VALUES (null, 'y');", 2348 }, 2349 { 2350 Name: "incompatible types", 2351 Query: "INSERT INTO mytable (i, s) select * FROM othertable", 2352 }, 2353 { 2354 Name: "column count mismatch in select", 2355 Query: "INSERT INTO mytable (i) select * FROM othertable", 2356 }, 2357 { 2358 Name: "column count mismatch in select", 2359 Query: "INSERT INTO mytable select s FROM othertable", 2360 }, 2361 { 2362 Name: "column count mismatch in join select", 2363 Query: "INSERT INTO mytable (s,i) SELECT * FROM othertable o JOIN mytable m ON m.i=o.i2", 2364 }, 2365 { 2366 Name: "duplicate key", 2367 Query: "INSERT INTO mytable (i,s) values (1, 'hello')", 2368 }, 2369 { 2370 Name: "duplicate keys", 2371 Query: "INSERT INTO mytable SELECT * from mytable", 2372 }, 2373 { 2374 Name: "bad column in on duplicate key update clause", 2375 Query: "INSERT INTO mytable values (10, 'b') ON DUPLICATE KEY UPDATE notExist = 1", 2376 }, 2377 } 2378 2379 var InsertErrorScripts = []ScriptTest{ 2380 { 2381 Name: "create table with non-pk auto_increment column", 2382 Query: "create table bad (pk int primary key, c0 int auto_increment);", 2383 ExpectedErr: sql.ErrInvalidAutoIncCols, 2384 }, 2385 { 2386 Name: "create multiple auto_increment columns", 2387 Query: "create table bad (pk1 int auto_increment, pk2 int auto_increment, primary key (pk1,pk2));", 2388 ExpectedErr: sql.ErrInvalidAutoIncCols, 2389 }, 2390 { 2391 Name: "create auto_increment column with default", 2392 Query: "create table bad (pk1 int auto_increment default 10, c0 int);", 2393 ExpectedErr: sql.ErrInvalidAutoIncCols, 2394 }, 2395 { 2396 Name: "try inserting string that is too long", 2397 SetUpScript: []string{ 2398 "create table bad (s varchar(9))", 2399 }, 2400 Query: "insert into bad values ('1234567890')", 2401 ExpectedErr: types.ErrLengthBeyondLimit, 2402 }, 2403 { 2404 Name: "try inserting varbinary larger than max limit", 2405 SetUpScript: []string{ 2406 "create table bad (vb varbinary(65535))", 2407 }, 2408 Query: "insert into bad values (repeat('0', 65536))", 2409 ExpectedErr: types.ErrLengthBeyondLimit, 2410 }, 2411 } 2412 2413 var InsertIgnoreScripts = []ScriptTest{ 2414 { 2415 Name: "Test that INSERT IGNORE with Non nullable columns works", 2416 SetUpScript: []string{ 2417 "CREATE TABLE x (pk int primary key, c1 varchar(20) NOT NULL);", 2418 "INSERT IGNORE INTO x VALUES (1, NULL)", 2419 "CREATE TABLE y (pk int primary key, c1 int NOT NULL);", 2420 "INSERT IGNORE INTO y VALUES (1, NULL);", 2421 }, 2422 Assertions: []ScriptTestAssertion{ 2423 { 2424 Query: "SELECT * FROM x", 2425 Expected: []sql.Row{ 2426 {1, ""}, 2427 }, 2428 }, 2429 { 2430 Query: "SELECT * FROM y", 2431 Expected: []sql.Row{ 2432 {1, 0}, 2433 }, 2434 }, 2435 { 2436 Query: "INSERT IGNORE INTO y VALUES (2, NULL)", 2437 Expected: []sql.Row{ 2438 {types.OkResult{RowsAffected: 1}}, 2439 }, 2440 ExpectedWarning: mysql.ERBadNullError, 2441 }, 2442 }, 2443 }, 2444 { 2445 Name: "Test that INSERT IGNORE properly addresses data conversion", 2446 SetUpScript: []string{ 2447 "CREATE TABLE t1 (pk int primary key, v1 int)", 2448 "CREATE TABLE t2 (pk int primary key, v2 varchar(1))", 2449 }, 2450 Assertions: []ScriptTestAssertion{ 2451 { 2452 Query: "INSERT IGNORE INTO t1 VALUES (1, 'dasd')", 2453 Expected: []sql.Row{ 2454 {types.OkResult{RowsAffected: 1}}, 2455 }, 2456 ExpectedWarning: mysql.ERTruncatedWrongValueForField, 2457 }, 2458 { 2459 Query: "SELECT * FROM t1", 2460 Expected: []sql.Row{ 2461 {1, 0}, 2462 }, 2463 }, 2464 { 2465 Query: "INSERT IGNORE INTO t2 values (1, 'adsda')", 2466 Expected: []sql.Row{ 2467 {types.OkResult{RowsAffected: 1}}, 2468 }, 2469 ExpectedWarning: mysql.ERUnknownError, 2470 }, 2471 { 2472 Query: "SELECT * FROM t2", 2473 Expected: []sql.Row{ 2474 {1, "a"}, 2475 }, 2476 }, 2477 }, 2478 }, 2479 { 2480 Name: "Insert Ignore works correctly with ON DUPLICATE UPDATE", 2481 SetUpScript: []string{ 2482 "CREATE TABLE t1 (id INT PRIMARY KEY, v int);", 2483 "INSERT INTO t1 VALUES (1,1)", 2484 "CREATE TABLE t2 (pk int primary key, v2 varchar(1))", 2485 "ALTER TABLE t2 ADD CONSTRAINT cx CHECK (pk < 100)", 2486 }, 2487 Assertions: []ScriptTestAssertion{ 2488 { 2489 Query: "INSERT IGNORE INTO t1 VALUES (1,2) ON DUPLICATE KEY UPDATE v='dsd';", 2490 Expected: []sql.Row{ 2491 {types.OkResult{RowsAffected: 2}}, 2492 }, 2493 ExpectedWarning: mysql.ERTruncatedWrongValueForField, 2494 }, 2495 { 2496 Query: "SELECT * FROM t1", 2497 Expected: []sql.Row{ 2498 {1, 0}, 2499 }, 2500 }, 2501 { 2502 Query: "INSERT IGNORE INTO t2 values (1, 'adsda')", 2503 Expected: []sql.Row{ 2504 {types.OkResult{RowsAffected: 1}}, 2505 }, 2506 ExpectedWarning: mysql.ERUnknownError, 2507 }, 2508 { 2509 Query: "SELECT * FROM t2", 2510 Expected: []sql.Row{ 2511 {1, "a"}, 2512 }, 2513 }, 2514 { 2515 Query: "INSERT IGNORE INTO t2 VALUES (1, 's') ON DUPLICATE KEY UPDATE pk = 1000", // violates constraint 2516 Expected: []sql.Row{{types.OkResult{RowsAffected: 0}}}, 2517 }, 2518 { 2519 Query: "SELECT * FROM t2", 2520 Expected: []sql.Row{ 2521 {1, "a"}, 2522 }, 2523 }, 2524 }, 2525 }, 2526 { 2527 Name: "Test that INSERT IGNORE INTO works with unique keys", 2528 SetUpScript: []string{ 2529 "CREATE TABLE one_uniq(pk int PRIMARY KEY, col1 int UNIQUE)", 2530 "CREATE TABLE two_uniq(pk int PRIMARY KEY, col1 int, col2 int, UNIQUE KEY col1_col2_uniq (col1, col2))", 2531 "INSERT INTO one_uniq values (1, 1)", 2532 "INSERT INTO two_uniq values (1, 1, 1)", 2533 }, 2534 Assertions: []ScriptTestAssertion{ 2535 { 2536 Query: "INSERT IGNORE INTO one_uniq VALUES (3, 2), (2, 1), (4, null), (5, null)", 2537 Expected: []sql.Row{ 2538 {types.OkResult{RowsAffected: 3}}, 2539 }, 2540 ExpectedWarning: mysql.ERDupEntry, 2541 }, 2542 { 2543 Query: "SELECT * from one_uniq;", 2544 Expected: []sql.Row{ 2545 {1, 1}, {3, 2}, {4, nil}, {5, nil}, 2546 }, 2547 }, 2548 { 2549 Query: "INSERT IGNORE INTO two_uniq VALUES (4, 1, 2), (5, 2, 1), (6, null, 1), (7, null, 1), (12, 1, 1), (8, 1, null), (9, 1, null), (10, null, null), (11, null, null)", 2550 Expected: []sql.Row{ 2551 {types.OkResult{RowsAffected: 8}}, 2552 }, 2553 ExpectedWarning: mysql.ERDupEntry, 2554 }, 2555 { 2556 Query: "SELECT * from two_uniq;", 2557 Expected: []sql.Row{ 2558 {1, 1, 1}, {4, 1, 2}, {5, 2, 1}, {6, nil, 1}, {7, nil, 1}, {8, 1, nil}, {9, 1, nil}, {10, nil, nil}, {11, nil, nil}, 2559 }, 2560 }, 2561 }, 2562 }, 2563 } 2564 2565 var IgnoreWithDuplicateUniqueKeyKeylessScripts = []ScriptTest{ 2566 { 2567 Name: "Test that INSERT IGNORE INTO works with unique keys on a keyless table", 2568 SetUpScript: []string{ 2569 "CREATE TABLE one_uniq(not_pk int, value int UNIQUE)", 2570 "CREATE TABLE two_uniq(not_pk int, col1 int, col2 int, UNIQUE KEY col1_col2_uniq (col1, col2));", 2571 "INSERT INTO one_uniq values (1, 1)", 2572 "INSERT INTO two_uniq values (1, 1, 1)", 2573 }, 2574 Assertions: []ScriptTestAssertion{ 2575 { 2576 Query: "INSERT IGNORE INTO one_uniq VALUES (3, 2), (2, 1), (4, null), (5, null)", 2577 Expected: []sql.Row{ 2578 {types.OkResult{RowsAffected: 3}}, 2579 }, 2580 ExpectedWarning: mysql.ERDupEntry, 2581 }, 2582 { 2583 Query: "SELECT * from one_uniq;", 2584 Expected: []sql.Row{ 2585 {1, 1}, {3, 2}, {4, nil}, {5, nil}, 2586 }, 2587 }, 2588 { 2589 Query: "INSERT IGNORE INTO two_uniq VALUES (4, 1, 2), (5, 2, 1), (6, null, 1), (7, null, 1), (12, 1, 1), (8, 1, null), (9, 1, null), (10, null, null), (11, null, null)", 2590 Expected: []sql.Row{ 2591 {types.OkResult{RowsAffected: 8}}, 2592 }, 2593 ExpectedWarning: mysql.ERDupEntry, 2594 }, 2595 { 2596 Query: "SELECT * from two_uniq;", 2597 Expected: []sql.Row{ 2598 {1, 1, 1}, {4, 1, 2}, {5, 2, 1}, {6, nil, 1}, {7, nil, 1}, {8, 1, nil}, {9, 1, nil}, {10, nil, nil}, {11, nil, nil}, 2599 }, 2600 }, 2601 }, 2602 }, 2603 { 2604 Name: "INSERT IGNORE INTO multiple violations of a unique secondary index", 2605 SetUpScript: []string{ 2606 "CREATE TABLE keyless(pk int, val int)", 2607 "INSERT INTO keyless values (1, 1), (2, 2), (3, 3)", 2608 }, 2609 Assertions: []ScriptTestAssertion{ 2610 { 2611 Query: "INSERT IGNORE INTO keyless VALUES (1, 2);", 2612 Expected: []sql.Row{{types.NewOkResult(1)}}, 2613 }, 2614 { 2615 Query: "ALTER TABLE keyless ADD CONSTRAINT c UNIQUE(val)", 2616 ExpectedErr: sql.ErrUniqueKeyViolation, 2617 }, 2618 { 2619 Query: "DELETE FROM keyless where pk = 1 and val = 2", 2620 Expected: []sql.Row{{types.NewOkResult(1)}}, 2621 }, 2622 { 2623 Query: "ALTER TABLE keyless ADD CONSTRAINT c UNIQUE(val)", 2624 Expected: []sql.Row{{types.NewOkResult(0)}}, 2625 }, 2626 { 2627 Query: "INSERT IGNORE INTO keyless VALUES (1, 3)", 2628 Expected: []sql.Row{{types.NewOkResult(0)}}, 2629 ExpectedWarning: mysql.ERDupEntry, 2630 }, 2631 }, 2632 }, 2633 { 2634 Name: "UPDATE IGNORE keyless tables and secondary indexes", 2635 SetUpScript: []string{ 2636 "CREATE TABLE keyless(pk int, val int)", 2637 "INSERT INTO keyless VALUES (1, 1), (2, 2), (3, 3)", 2638 }, 2639 Assertions: []ScriptTestAssertion{ 2640 { 2641 Query: "UPDATE IGNORE keyless SET val = 2 where pk = 1", 2642 Expected: []sql.Row{{newUpdateResult(1, 1)}}, 2643 }, 2644 { 2645 Query: "SELECT * FROM keyless ORDER BY pk", 2646 Expected: []sql.Row{{1, 2}, {2, 2}, {3, 3}}, 2647 }, 2648 { 2649 Query: "ALTER TABLE keyless ADD CONSTRAINT c UNIQUE(val)", 2650 ExpectedErr: sql.ErrUniqueKeyViolation, 2651 }, 2652 { 2653 Query: "UPDATE IGNORE keyless SET val = 1 where pk = 1", 2654 Expected: []sql.Row{{newUpdateResult(1, 1)}}, 2655 ExpectedWarning: mysql.ERDupEntry, 2656 }, 2657 { 2658 Query: "ALTER TABLE keyless ADD CONSTRAINT c UNIQUE(val)", 2659 Expected: []sql.Row{{types.NewOkResult(0)}}, 2660 }, 2661 { 2662 Query: "UPDATE IGNORE keyless SET val = 3 where pk = 1", 2663 Expected: []sql.Row{{newUpdateResult(1, 0)}}, 2664 ExpectedWarning: mysql.ERDupEntry, 2665 }, 2666 { 2667 Query: "SELECT * FROM keyless ORDER BY pk", 2668 Expected: []sql.Row{{1, 1}, {2, 2}, {3, 3}}, 2669 }, 2670 { 2671 Query: "UPDATE IGNORE keyless SET val = val + 1 ORDER BY pk", 2672 Expected: []sql.Row{{newUpdateResult(3, 1)}}, 2673 ExpectedWarning: mysql.ERDupEntry, 2674 }, 2675 { 2676 Query: "SELECT * FROM keyless ORDER BY pk", 2677 Expected: []sql.Row{{1, 1}, {2, 2}, {3, 4}}, 2678 }, 2679 }, 2680 }, 2681 } 2682 2683 var InsertBrokenScripts = []ScriptTest{ 2684 // TODO: Condense all of our casting logic into a single error. 2685 { 2686 Name: "Test that INSERT IGNORE assigns the closest dataype correctly", 2687 SetUpScript: []string{ 2688 "CREATE TABLE x (pk int primary key, c1 varchar(20) NOT NULL);", 2689 `INSERT IGNORE INTO x VALUES (1, "one"), (2, TRUE), (3, "three")`, 2690 "CREATE TABLE y (pk int primary key, c1 int NOT NULL);", 2691 `INSERT IGNORE INTO y VALUES (1, 1), (2, "two"), (3,3);`, 2692 }, 2693 Assertions: []ScriptTestAssertion{ 2694 { 2695 Query: "SELECT * FROM x", 2696 Expected: []sql.Row{ 2697 {1, "one"}, {2, 1}, {3, "three"}, 2698 }, 2699 }, 2700 { 2701 Query: "SELECT * FROM y", 2702 Expected: []sql.Row{ 2703 {1, 1}, {2, 0}, {3, 3}, 2704 }, 2705 }, 2706 { 2707 Query: `INSERT IGNORE INTO y VALUES (4, "four")`, 2708 Expected: []sql.Row{ 2709 {types.OkResult{RowsAffected: 1}}, 2710 }, 2711 ExpectedWarning: mysql.ERTruncatedWrongValueForField, 2712 }, 2713 }, 2714 }, 2715 }