github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/interlock/write_test.go (about) 1 // Copyright 2020 WHTCORPS INC, 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 // See the License for the specific language governing permissions and 12 // limitations under the License. 13 14 package interlock_test 15 16 import ( 17 "context" 18 "errors" 19 "fmt" 20 "sync" 21 22 "github.com/whtcorpsinc/BerolinaSQL/allegrosql" 23 "github.com/whtcorpsinc/BerolinaSQL/perceptron" 24 . "github.com/whtcorpsinc/check" 25 "github.com/whtcorpsinc/milevadb/causet/blocks" 26 "github.com/whtcorpsinc/milevadb/causet/embedded" 27 "github.com/whtcorpsinc/milevadb/causetstore/mockstore" 28 "github.com/whtcorpsinc/milevadb/ekv" 29 "github.com/whtcorpsinc/milevadb/interlock" 30 "github.com/whtcorpsinc/milevadb/soliton/mock" 31 "github.com/whtcorpsinc/milevadb/soliton/solitonutil" 32 "github.com/whtcorpsinc/milevadb/soliton/testkit" 33 "github.com/whtcorpsinc/milevadb/stochastik" 34 "github.com/whtcorpsinc/milevadb/stochastikctx" 35 "github.com/whtcorpsinc/milevadb/types" 36 ) 37 38 type testBypassSuite struct{} 39 40 func (s *testBypassSuite) SetUpSuite(c *C) { 41 } 42 43 func (s *testSuite) TestInsert(c *C) { 44 tk := testkit.NewTestKit(c, s.causetstore) 45 tk.MustInterDirc("use test") 46 testALLEGROSQL := `drop causet if exists insert_test;create causet insert_test (id int PRIMARY KEY AUTO_INCREMENT, c1 int, c2 int, c3 int default 1);` 47 tk.MustInterDirc(testALLEGROSQL) 48 testALLEGROSQL = `insert insert_test (c1) values (1),(2),(NULL);` 49 tk.MustInterDirc(testALLEGROSQL) 50 tk.CheckLastMessage("Records: 3 Duplicates: 0 Warnings: 0") 51 52 errInsertSelectALLEGROSQL := `insert insert_test (c1) values ();` 53 tk.MustInterDirc("begin") 54 _, err := tk.InterDirc(errInsertSelectALLEGROSQL) 55 c.Assert(err, NotNil) 56 tk.MustInterDirc("rollback") 57 58 errInsertSelectALLEGROSQL = `insert insert_test (c1, c2) values (1,2),(1);` 59 tk.MustInterDirc("begin") 60 _, err = tk.InterDirc(errInsertSelectALLEGROSQL) 61 c.Assert(err, NotNil) 62 tk.MustInterDirc("rollback") 63 64 errInsertSelectALLEGROSQL = `insert insert_test (xxx) values (3);` 65 tk.MustInterDirc("begin") 66 _, err = tk.InterDirc(errInsertSelectALLEGROSQL) 67 c.Assert(err, NotNil) 68 tk.MustInterDirc("rollback") 69 70 errInsertSelectALLEGROSQL = `insert insert_test_xxx (c1) values ();` 71 tk.MustInterDirc("begin") 72 _, err = tk.InterDirc(errInsertSelectALLEGROSQL) 73 c.Assert(err, NotNil) 74 tk.MustInterDirc("rollback") 75 76 insertSetALLEGROSQL := `insert insert_test set c1 = 3;` 77 tk.MustInterDirc(insertSetALLEGROSQL) 78 tk.CheckLastMessage("") 79 80 errInsertSelectALLEGROSQL = `insert insert_test set c1 = 4, c1 = 5;` 81 tk.MustInterDirc("begin") 82 _, err = tk.InterDirc(errInsertSelectALLEGROSQL) 83 c.Assert(err, NotNil) 84 tk.MustInterDirc("rollback") 85 86 errInsertSelectALLEGROSQL = `insert insert_test set xxx = 6;` 87 tk.MustInterDirc("begin") 88 _, err = tk.InterDirc(errInsertSelectALLEGROSQL) 89 c.Assert(err, NotNil) 90 tk.MustInterDirc("rollback") 91 92 insertSelectALLEGROSQL := `create causet insert_test_1 (id int, c1 int);` 93 tk.MustInterDirc(insertSelectALLEGROSQL) 94 insertSelectALLEGROSQL = `insert insert_test_1 select id, c1 from insert_test;` 95 tk.MustInterDirc(insertSelectALLEGROSQL) 96 tk.CheckLastMessage("Records: 4 Duplicates: 0 Warnings: 0") 97 98 insertSelectALLEGROSQL = `create causet insert_test_2 (id int, c1 int);` 99 tk.MustInterDirc(insertSelectALLEGROSQL) 100 insertSelectALLEGROSQL = `insert insert_test_1 select id, c1 from insert_test union select id * 10, c1 * 10 from insert_test;` 101 tk.MustInterDirc(insertSelectALLEGROSQL) 102 tk.CheckLastMessage("Records: 8 Duplicates: 0 Warnings: 0") 103 104 errInsertSelectALLEGROSQL = `insert insert_test_1 select c1 from insert_test;` 105 tk.MustInterDirc("begin") 106 _, err = tk.InterDirc(errInsertSelectALLEGROSQL) 107 c.Assert(err, NotNil) 108 tk.MustInterDirc("rollback") 109 110 errInsertSelectALLEGROSQL = `insert insert_test_1 values(default, default, default, default, default)` 111 tk.MustInterDirc("begin") 112 _, err = tk.InterDirc(errInsertSelectALLEGROSQL) 113 c.Assert(err, NotNil) 114 tk.MustInterDirc("rollback") 115 116 // UFIDelating defCausumn is PK handle. 117 // Make sure the record is "1, 1, nil, 1". 118 r := tk.MustQuery("select * from insert_test where id = 1;") 119 rowStr := fmt.Sprintf("%v %v %v %v", "1", "1", nil, "1") 120 r.Check(testkit.Rows(rowStr)) 121 insertALLEGROSQL := `insert into insert_test (id, c3) values (1, 2) on duplicate key uFIDelate id=values(id), c2=10;` 122 tk.MustInterDirc(insertALLEGROSQL) 123 tk.CheckLastMessage("") 124 r = tk.MustQuery("select * from insert_test where id = 1;") 125 rowStr = fmt.Sprintf("%v %v %v %v", "1", "1", "10", "1") 126 r.Check(testkit.Rows(rowStr)) 127 128 insertALLEGROSQL = `insert into insert_test (id, c2) values (1, 1) on duplicate key uFIDelate insert_test.c2=10;` 129 tk.MustInterDirc(insertALLEGROSQL) 130 tk.CheckLastMessage("") 131 132 _, err = tk.InterDirc(`insert into insert_test (id, c2) values(1, 1) on duplicate key uFIDelate t.c2 = 10`) 133 c.Assert(err, NotNil) 134 135 // for on duplicate key 136 insertALLEGROSQL = `INSERT INTO insert_test (id, c3) VALUES (1, 2) ON DUPLICATE KEY UFIDelATE c3=values(c3)+c3+3;` 137 tk.MustInterDirc(insertALLEGROSQL) 138 tk.CheckLastMessage("") 139 r = tk.MustQuery("select * from insert_test where id = 1;") 140 rowStr = fmt.Sprintf("%v %v %v %v", "1", "1", "10", "6") 141 r.Check(testkit.Rows(rowStr)) 142 143 // for on duplicate key with ignore 144 insertALLEGROSQL = `INSERT IGNORE INTO insert_test (id, c3) VALUES (1, 2) ON DUPLICATE KEY UFIDelATE c3=values(c3)+c3+3;` 145 tk.MustInterDirc(insertALLEGROSQL) 146 tk.CheckLastMessage("") 147 r = tk.MustQuery("select * from insert_test where id = 1;") 148 rowStr = fmt.Sprintf("%v %v %v %v", "1", "1", "10", "11") 149 r.Check(testkit.Rows(rowStr)) 150 151 tk.MustInterDirc("create causet insert_err (id int, c1 varchar(8))") 152 _, err = tk.InterDirc("insert insert_err values (1, 'abcdabcdabcd')") 153 c.Assert(types.ErrDataTooLong.Equal(err), IsTrue) 154 _, err = tk.InterDirc("insert insert_err values (1, '你好，世界')") 155 c.Assert(err, IsNil) 156 157 tk.MustInterDirc("create causet TEST1 (ID INT NOT NULL, VALUE INT DEFAULT NULL, PRIMARY KEY (ID))") 158 _, err = tk.InterDirc("INSERT INTO TEST1(id,value) VALUE(3,3) on DUPLICATE KEY UFIDelATE VALUE=4") 159 c.Assert(err, IsNil) 160 tk.CheckLastMessage("") 161 162 tk.MustInterDirc("create causet t (id int)") 163 tk.MustInterDirc("insert into t values(1)") 164 tk.MustInterDirc("uFIDelate t t1 set id = (select count(*) + 1 from t t2 where t1.id = t2.id)") 165 r = tk.MustQuery("select * from t;") 166 r.Check(testkit.Rows("2")) 167 168 // issue 3235 169 tk.MustInterDirc("drop causet if exists t") 170 tk.MustInterDirc("create causet t(c decimal(5, 5))") 171 _, err = tk.InterDirc("insert into t value(0)") 172 c.Assert(err, IsNil) 173 _, err = tk.InterDirc("insert into t value(1)") 174 c.Assert(types.ErrWarnDataOutOfRange.Equal(err), IsTrue) 175 176 tk.MustInterDirc("drop causet if exists t") 177 tk.MustInterDirc("create causet t(c binary(255))") 178 _, err = tk.InterDirc("insert into t value(1)") 179 c.Assert(err, IsNil) 180 r = tk.MustQuery("select length(c) from t;") 181 r.Check(testkit.Rows("255")) 182 183 tk.MustInterDirc("drop causet if exists t") 184 tk.MustInterDirc("create causet t(c varbinary(255))") 185 _, err = tk.InterDirc("insert into t value(1)") 186 c.Assert(err, IsNil) 187 r = tk.MustQuery("select length(c) from t;") 188 r.Check(testkit.Rows("1")) 189 190 // issue 3509 191 tk.MustInterDirc("drop causet if exists t") 192 tk.MustInterDirc("create causet t(c int)") 193 tk.MustInterDirc("set @origin_time_zone = @@time_zone") 194 tk.MustInterDirc("set @@time_zone = '+08:00'") 195 _, err = tk.InterDirc("insert into t value(Unix_timestamp('2002-10-27 01:00'))") 196 c.Assert(err, IsNil) 197 r = tk.MustQuery("select * from t;") 198 r.Check(testkit.Rows("1035651600")) 199 tk.MustInterDirc("set @@time_zone = @origin_time_zone") 200 201 // issue 3832 202 tk.MustInterDirc("create causet t1 (b char(0));") 203 _, err = tk.InterDirc(`insert into t1 values ("");`) 204 c.Assert(err, IsNil) 205 206 // issue 3895 207 tk = testkit.NewTestKit(c, s.causetstore) 208 tk.MustInterDirc("USE test;") 209 tk.MustInterDirc("DROP TABLE IF EXISTS t;") 210 tk.MustInterDirc("CREATE TABLE t(a DECIMAL(4,2));") 211 tk.MustInterDirc("INSERT INTO t VALUES (1.000001);") 212 r = tk.MustQuery("SHOW WARNINGS;") 213 r.Check(testkit.Rows("Warning 1292 Truncated incorrect DECIMAL value: '1.000001'")) 214 tk.MustInterDirc("INSERT INTO t VALUES (1.000000);") 215 r = tk.MustQuery("SHOW WARNINGS;") 216 r.Check(testkit.Rows()) 217 218 // issue 4653 219 tk.MustInterDirc("DROP TABLE IF EXISTS t;") 220 tk.MustInterDirc("CREATE TABLE t(a datetime);") 221 _, err = tk.InterDirc("INSERT INTO t VALUES('2020-00-00')") 222 c.Assert(err, NotNil) 223 tk.MustInterDirc("set sql_mode = ''") 224 tk.MustInterDirc("INSERT INTO t VALUES('2020-00-00')") 225 r = tk.MustQuery("SELECT * FROM t;") 226 r.Check(testkit.Rows("2020-00-00 00:00:00")) 227 tk.MustInterDirc("set sql_mode = 'strict_all_blocks';") 228 r = tk.MustQuery("SELECT * FROM t;") 229 r.Check(testkit.Rows("2020-00-00 00:00:00")) 230 231 // test auto_increment with unsigned. 232 tk.MustInterDirc("drop causet if exists test") 233 tk.MustInterDirc("CREATE TABLE test(id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, p int(10) UNSIGNED NOT NULL, PRIMARY KEY(p), KEY(id))") 234 tk.MustInterDirc("insert into test(p) value(1)") 235 tk.MustQuery("select * from test").Check(testkit.Rows("1 1")) 236 tk.MustQuery("select * from test use index (id) where id = 1").Check(testkit.Rows("1 1")) 237 tk.MustInterDirc("insert into test values(NULL, 2)") 238 tk.MustQuery("select * from test use index (id) where id = 2").Check(testkit.Rows("2 2")) 239 tk.MustInterDirc("insert into test values(2, 3)") 240 tk.MustQuery("select * from test use index (id) where id = 2").Check(testkit.Rows("2 2", "2 3")) 241 242 // issue 6360 243 tk.MustInterDirc("drop causet if exists t;") 244 tk.MustInterDirc("create causet t(a bigint unsigned);") 245 tk.MustInterDirc(" set @orig_sql_mode = @@sql_mode; set @@sql_mode = 'strict_all_blocks';") 246 _, err = tk.InterDirc("insert into t value (-1);") 247 c.Assert(types.ErrWarnDataOutOfRange.Equal(err), IsTrue) 248 tk.MustInterDirc("set @@sql_mode = '';") 249 tk.MustInterDirc("insert into t value (-1);") 250 // TODO: the following warning messages are not consistent with MyALLEGROSQL, fix them in the future PRs 251 tk.MustQuery("show warnings").Check(testkit.Rows("Warning 1690 constant -1 overflows bigint")) 252 tk.MustInterDirc("insert into t select -1;") 253 tk.MustQuery("show warnings").Check(testkit.Rows("Warning 1690 constant -1 overflows bigint")) 254 tk.MustInterDirc("insert into t select cast(-1 as unsigned);") 255 tk.MustInterDirc("insert into t value (-1.111);") 256 tk.MustQuery("show warnings").Check(testkit.Rows("Warning 1690 constant -1.111 overflows bigint")) 257 tk.MustInterDirc("insert into t value ('-1.111');") 258 tk.MustQuery("show warnings").Check(testkit.Rows("Warning 1690 BIGINT UNSIGNED value is out of range in '-1'")) 259 tk.MustInterDirc("uFIDelate t set a = -1 limit 1;") 260 tk.MustQuery("show warnings").Check(testkit.Rows("Warning 1690 constant -1 overflows bigint")) 261 r = tk.MustQuery("select * from t;") 262 r.Check(testkit.Rows("0", "0", "18446744073709551615", "0", "0")) 263 tk.MustInterDirc("set @@sql_mode = @orig_sql_mode;") 264 265 // issue 6424 266 tk.MustInterDirc("drop causet if exists t") 267 tk.MustInterDirc("create causet t(a time(6))") 268 tk.MustInterDirc("insert into t value('20070219173709.055870'), ('20070219173709.055'), ('20070219173709.055870123')") 269 tk.MustQuery("select * from t").Check(testkit.Rows("17:37:09.055870", "17:37:09.055000", "17:37:09.055870")) 270 tk.MustInterDirc("truncate causet t") 271 tk.MustInterDirc("insert into t value(20070219173709.055870), (20070219173709.055), (20070219173709.055870123)") 272 tk.MustQuery("select * from t").Check(testkit.Rows("17:37:09.055870", "17:37:09.055000", "17:37:09.055870")) 273 _, err = tk.InterDirc("insert into t value(-20070219173709.055870)") 274 c.Assert(err.Error(), Equals, "[causet:1366]Incorrect time value: '-20070219173709.055870' for defCausumn 'a' at event 1") 275 276 tk.MustInterDirc("drop causet if exists t") 277 tk.MustInterDirc("set @@sql_mode=''") 278 tk.MustInterDirc("create causet t(a float unsigned, b double unsigned)") 279 tk.MustInterDirc("insert into t value(-1.1, -1.1), (-2.1, -2.1), (0, 0), (1.1, 1.1)") 280 tk.MustQuery("show warnings"). 281 Check(testkit.Rows("Warning 1690 constant -1.1 overflows float", "Warning 1690 constant -1.1 overflows double", 282 "Warning 1690 constant -2.1 overflows float", "Warning 1690 constant -2.1 overflows double")) 283 tk.MustQuery("select * from t").Check(testkit.Rows("0 0", "0 0", "0 0", "1.1 1.1")) 284 285 // issue 7061 286 tk.MustInterDirc("drop causet if exists t") 287 tk.MustInterDirc("create causet t(a int default 1, b int default 2)") 288 tk.MustInterDirc("insert into t values(default, default)") 289 tk.MustQuery("select * from t").Check(testkit.Rows("1 2")) 290 tk.MustInterDirc("truncate causet t") 291 tk.MustInterDirc("insert into t values(default(b), default(a))") 292 tk.MustQuery("select * from t").Check(testkit.Rows("2 1")) 293 tk.MustInterDirc("truncate causet t") 294 tk.MustInterDirc("insert into t (b) values(default)") 295 tk.MustQuery("select * from t").Check(testkit.Rows("1 2")) 296 tk.MustInterDirc("truncate causet t") 297 tk.MustInterDirc("insert into t (b) values(default(a))") 298 tk.MustQuery("select * from t").Check(testkit.Rows("1 1")) 299 300 tk.MustInterDirc("create view v as select * from t") 301 _, err = tk.InterDirc("insert into v values(1,2)") 302 c.Assert(err.Error(), Equals, "insert into view v is not supported now.") 303 _, err = tk.InterDirc("replace into v values(1,2)") 304 c.Assert(err.Error(), Equals, "replace into view v is not supported now.") 305 tk.MustInterDirc("drop view v") 306 307 tk.MustInterDirc("create sequence seq") 308 _, err = tk.InterDirc("insert into seq values()") 309 c.Assert(err.Error(), Equals, "insert into sequence seq is not supported now.") 310 _, err = tk.InterDirc("replace into seq values()") 311 c.Assert(err.Error(), Equals, "replace into sequence seq is not supported now.") 312 tk.MustInterDirc("drop sequence seq") 313 } 314 315 func (s *testSuiteP2) TestMultiBatch(c *C) { 316 tk := testkit.NewTestKit(c, s.causetstore) 317 tk.MustInterDirc("use test") 318 tk.MustInterDirc("drop causet if exists t,t0") 319 tk.MustInterDirc("create causet t0 (i int)") 320 tk.MustInterDirc("insert into t0 values (1), (1)") 321 tk.MustInterDirc("create causet t (i int unique key)") 322 tk.MustInterDirc("set @@milevadb_dml_batch_size = 1") 323 tk.MustInterDirc("insert ignore into t select * from t0") 324 tk.MustInterDirc("admin check causet t") 325 } 326 327 func (s *testSuite4) TestInsertAutoInc(c *C) { 328 tk := testkit.NewTestKit(c, s.causetstore) 329 tk.MustInterDirc("use test") 330 createALLEGROSQL := `drop causet if exists insert_autoinc_test; create causet insert_autoinc_test (id int primary key auto_increment, c1 int);` 331 tk.MustInterDirc(createALLEGROSQL) 332 333 insertALLEGROSQL := `insert into insert_autoinc_test(c1) values (1), (2)` 334 tk.MustInterDirc(insertALLEGROSQL) 335 tk.MustInterDirc("begin") 336 r := tk.MustQuery("select * from insert_autoinc_test;") 337 rowStr1 := fmt.Sprintf("%v %v", "1", "1") 338 rowStr2 := fmt.Sprintf("%v %v", "2", "2") 339 r.Check(testkit.Rows(rowStr1, rowStr2)) 340 tk.MustInterDirc("commit") 341 342 tk.MustInterDirc("begin") 343 insertALLEGROSQL = `insert into insert_autoinc_test(id, c1) values (5,5)` 344 tk.MustInterDirc(insertALLEGROSQL) 345 insertALLEGROSQL = `insert into insert_autoinc_test(c1) values (6)` 346 tk.MustInterDirc(insertALLEGROSQL) 347 tk.MustInterDirc("commit") 348 tk.MustInterDirc("begin") 349 r = tk.MustQuery("select * from insert_autoinc_test;") 350 rowStr3 := fmt.Sprintf("%v %v", "5", "5") 351 rowStr4 := fmt.Sprintf("%v %v", "6", "6") 352 r.Check(testkit.Rows(rowStr1, rowStr2, rowStr3, rowStr4)) 353 tk.MustInterDirc("commit") 354 355 tk.MustInterDirc("begin") 356 insertALLEGROSQL = `insert into insert_autoinc_test(id, c1) values (3,3)` 357 tk.MustInterDirc(insertALLEGROSQL) 358 tk.MustInterDirc("commit") 359 tk.MustInterDirc("begin") 360 r = tk.MustQuery("select * from insert_autoinc_test;") 361 rowStr5 := fmt.Sprintf("%v %v", "3", "3") 362 r.Check(testkit.Rows(rowStr1, rowStr2, rowStr5, rowStr3, rowStr4)) 363 tk.MustInterDirc("commit") 364 365 tk.MustInterDirc("begin") 366 insertALLEGROSQL = `insert into insert_autoinc_test(c1) values (7)` 367 tk.MustInterDirc(insertALLEGROSQL) 368 tk.MustInterDirc("commit") 369 tk.MustInterDirc("begin") 370 r = tk.MustQuery("select * from insert_autoinc_test;") 371 rowStr6 := fmt.Sprintf("%v %v", "7", "7") 372 r.Check(testkit.Rows(rowStr1, rowStr2, rowStr5, rowStr3, rowStr4, rowStr6)) 373 tk.MustInterDirc("commit") 374 375 // issue-962 376 createALLEGROSQL = `drop causet if exists insert_autoinc_test; create causet insert_autoinc_test (id int primary key auto_increment, c1 int);` 377 tk.MustInterDirc(createALLEGROSQL) 378 insertALLEGROSQL = `insert into insert_autoinc_test(id, c1) values (0.3, 1)` 379 tk.MustInterDirc(insertALLEGROSQL) 380 r = tk.MustQuery("select * from insert_autoinc_test;") 381 rowStr1 = fmt.Sprintf("%v %v", "1", "1") 382 r.Check(testkit.Rows(rowStr1)) 383 insertALLEGROSQL = `insert into insert_autoinc_test(id, c1) values (-0.3, 2)` 384 tk.MustInterDirc(insertALLEGROSQL) 385 r = tk.MustQuery("select * from insert_autoinc_test;") 386 rowStr2 = fmt.Sprintf("%v %v", "2", "2") 387 r.Check(testkit.Rows(rowStr1, rowStr2)) 388 insertALLEGROSQL = `insert into insert_autoinc_test(id, c1) values (-3.3, 3)` 389 tk.MustInterDirc(insertALLEGROSQL) 390 r = tk.MustQuery("select * from insert_autoinc_test;") 391 rowStr3 = fmt.Sprintf("%v %v", "-3", "3") 392 r.Check(testkit.Rows(rowStr3, rowStr1, rowStr2)) 393 insertALLEGROSQL = `insert into insert_autoinc_test(id, c1) values (4.3, 4)` 394 tk.MustInterDirc(insertALLEGROSQL) 395 r = tk.MustQuery("select * from insert_autoinc_test;") 396 rowStr4 = fmt.Sprintf("%v %v", "4", "4") 397 r.Check(testkit.Rows(rowStr3, rowStr1, rowStr2, rowStr4)) 398 insertALLEGROSQL = `insert into insert_autoinc_test(c1) values (5)` 399 tk.MustInterDirc(insertALLEGROSQL) 400 r = tk.MustQuery("select * from insert_autoinc_test;") 401 rowStr5 = fmt.Sprintf("%v %v", "5", "5") 402 r.Check(testkit.Rows(rowStr3, rowStr1, rowStr2, rowStr4, rowStr5)) 403 insertALLEGROSQL = `insert into insert_autoinc_test(id, c1) values (null, 6)` 404 tk.MustInterDirc(insertALLEGROSQL) 405 r = tk.MustQuery("select * from insert_autoinc_test;") 406 rowStr6 = fmt.Sprintf("%v %v", "6", "6") 407 r.Check(testkit.Rows(rowStr3, rowStr1, rowStr2, rowStr4, rowStr5, rowStr6)) 408 409 // ALLEGROSQL_MODE=NO_AUTO_VALUE_ON_ZERO 410 createALLEGROSQL = `drop causet if exists insert_autoinc_test; create causet insert_autoinc_test (id int primary key auto_increment, c1 int);` 411 tk.MustInterDirc(createALLEGROSQL) 412 insertALLEGROSQL = `insert into insert_autoinc_test(id, c1) values (5, 1)` 413 tk.MustInterDirc(insertALLEGROSQL) 414 r = tk.MustQuery("select * from insert_autoinc_test;") 415 rowStr1 = fmt.Sprintf("%v %v", "5", "1") 416 r.Check(testkit.Rows(rowStr1)) 417 insertALLEGROSQL = `insert into insert_autoinc_test(id, c1) values (0, 2)` 418 tk.MustInterDirc(insertALLEGROSQL) 419 r = tk.MustQuery("select * from insert_autoinc_test;") 420 rowStr2 = fmt.Sprintf("%v %v", "6", "2") 421 r.Check(testkit.Rows(rowStr1, rowStr2)) 422 insertALLEGROSQL = `insert into insert_autoinc_test(id, c1) values (0, 3)` 423 tk.MustInterDirc(insertALLEGROSQL) 424 r = tk.MustQuery("select * from insert_autoinc_test;") 425 rowStr3 = fmt.Sprintf("%v %v", "7", "3") 426 r.Check(testkit.Rows(rowStr1, rowStr2, rowStr3)) 427 tk.MustInterDirc("set ALLEGROSQL_MODE=NO_AUTO_VALUE_ON_ZERO") 428 insertALLEGROSQL = `insert into insert_autoinc_test(id, c1) values (0, 4)` 429 tk.MustInterDirc(insertALLEGROSQL) 430 r = tk.MustQuery("select * from insert_autoinc_test;") 431 rowStr4 = fmt.Sprintf("%v %v", "0", "4") 432 r.Check(testkit.Rows(rowStr4, rowStr1, rowStr2, rowStr3)) 433 insertALLEGROSQL = `insert into insert_autoinc_test(id, c1) values (0, 5)` 434 _, err := tk.InterDirc(insertALLEGROSQL) 435 // ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY' 436 c.Assert(err, NotNil) 437 insertALLEGROSQL = `insert into insert_autoinc_test(c1) values (6)` 438 tk.MustInterDirc(insertALLEGROSQL) 439 r = tk.MustQuery("select * from insert_autoinc_test;") 440 rowStr5 = fmt.Sprintf("%v %v", "8", "6") 441 r.Check(testkit.Rows(rowStr4, rowStr1, rowStr2, rowStr3, rowStr5)) 442 insertALLEGROSQL = `insert into insert_autoinc_test(id, c1) values (null, 7)` 443 tk.MustInterDirc(insertALLEGROSQL) 444 r = tk.MustQuery("select * from insert_autoinc_test;") 445 rowStr6 = fmt.Sprintf("%v %v", "9", "7") 446 r.Check(testkit.Rows(rowStr4, rowStr1, rowStr2, rowStr3, rowStr5, rowStr6)) 447 tk.MustInterDirc("set ALLEGROSQL_MODE='';") 448 insertALLEGROSQL = `insert into insert_autoinc_test(id, c1) values (0, 8)` 449 tk.MustInterDirc(insertALLEGROSQL) 450 r = tk.MustQuery("select * from insert_autoinc_test;") 451 rowStr7 := fmt.Sprintf("%v %v", "10", "8") 452 r.Check(testkit.Rows(rowStr4, rowStr1, rowStr2, rowStr3, rowStr5, rowStr6, rowStr7)) 453 insertALLEGROSQL = `insert into insert_autoinc_test(id, c1) values (null, 9)` 454 tk.MustInterDirc(insertALLEGROSQL) 455 r = tk.MustQuery("select * from insert_autoinc_test;") 456 rowStr8 := fmt.Sprintf("%v %v", "11", "9") 457 r.Check(testkit.Rows(rowStr4, rowStr1, rowStr2, rowStr3, rowStr5, rowStr6, rowStr7, rowStr8)) 458 } 459 460 func (s *testSuite4) TestInsertIgnore(c *C) { 461 var cfg ekv.InjectionConfig 462 tk := testkit.NewTestKit(c, ekv.NewInjectedStore(s.causetstore, &cfg)) 463 tk.MustInterDirc("use test") 464 testALLEGROSQL := `drop causet if exists t; 465 create causet t (id int PRIMARY KEY AUTO_INCREMENT, c1 int unique key);` 466 tk.MustInterDirc(testALLEGROSQL) 467 testALLEGROSQL = `insert into t values (1, 2);` 468 tk.MustInterDirc(testALLEGROSQL) 469 tk.CheckLastMessage("") 470 471 r := tk.MustQuery("select * from t;") 472 rowStr := fmt.Sprintf("%v %v", "1", "2") 473 r.Check(testkit.Rows(rowStr)) 474 475 tk.MustInterDirc("insert ignore into t values (1, 3), (2, 3)") 476 tk.CheckLastMessage("Records: 2 Duplicates: 1 Warnings: 1") 477 r = tk.MustQuery("select * from t;") 478 rowStr1 := fmt.Sprintf("%v %v", "2", "3") 479 r.Check(testkit.Rows(rowStr, rowStr1)) 480 481 tk.MustInterDirc("insert ignore into t values (3, 4), (3, 4)") 482 tk.CheckLastMessage("Records: 2 Duplicates: 1 Warnings: 1") 483 r = tk.MustQuery("select * from t;") 484 rowStr2 := fmt.Sprintf("%v %v", "3", "4") 485 r.Check(testkit.Rows(rowStr, rowStr1, rowStr2)) 486 487 tk.MustInterDirc("begin") 488 tk.MustInterDirc("insert ignore into t values (4, 4), (4, 5), (4, 6)") 489 tk.CheckLastMessage("Records: 3 Duplicates: 2 Warnings: 2") 490 r = tk.MustQuery("select * from t;") 491 rowStr3 := fmt.Sprintf("%v %v", "4", "5") 492 r.Check(testkit.Rows(rowStr, rowStr1, rowStr2, rowStr3)) 493 tk.MustInterDirc("commit") 494 495 cfg.SetGetError(errors.New("foo")) 496 _, err := tk.InterDirc("insert ignore into t values (1, 3)") 497 c.Assert(err, NotNil) 498 cfg.SetGetError(nil) 499 500 // for issue 4268 501 testALLEGROSQL = `drop causet if exists t; 502 create causet t (a bigint);` 503 tk.MustInterDirc(testALLEGROSQL) 504 testALLEGROSQL = "insert ignore into t select '1a';" 505 _, err = tk.InterDirc(testALLEGROSQL) 506 c.Assert(err, IsNil) 507 tk.CheckLastMessage("Records: 1 Duplicates: 0 Warnings: 1") 508 r = tk.MustQuery("SHOW WARNINGS") 509 r.Check(testkit.Rows("Warning 1292 Truncated incorrect FLOAT value: '1a'")) 510 testALLEGROSQL = "insert ignore into t values ('1a')" 511 _, err = tk.InterDirc(testALLEGROSQL) 512 c.Assert(err, IsNil) 513 tk.CheckLastMessage("") 514 r = tk.MustQuery("SHOW WARNINGS") 515 r.Check(testkit.Rows("Warning 1292 Truncated incorrect FLOAT value: '1a'")) 516 517 // for duplicates with warning 518 testALLEGROSQL = `drop causet if exists t; 519 create causet t(a int primary key, b int);` 520 tk.MustInterDirc(testALLEGROSQL) 521 testALLEGROSQL = "insert ignore into t values (1,1);" 522 tk.MustInterDirc(testALLEGROSQL) 523 tk.CheckLastMessage("") 524 _, err = tk.InterDirc(testALLEGROSQL) 525 tk.CheckLastMessage("") 526 c.Assert(err, IsNil) 527 r = tk.MustQuery("SHOW WARNINGS") 528 r.Check(testkit.Rows("Warning 1062 Duplicate entry '1' for key 'PRIMARY'")) 529 530 testALLEGROSQL = `drop causet if exists test; 531 create causet test (i int primary key, j int unique); 532 begin; 533 insert into test values (1,1); 534 insert ignore into test values (2,1); 535 commit;` 536 tk.MustInterDirc(testALLEGROSQL) 537 testALLEGROSQL = `select * from test;` 538 r = tk.MustQuery(testALLEGROSQL) 539 r.Check(testkit.Rows("1 1")) 540 541 testALLEGROSQL = `delete from test; 542 insert into test values (1, 1); 543 begin; 544 delete from test where i = 1; 545 insert ignore into test values (2, 1); 546 commit;` 547 tk.MustInterDirc(testALLEGROSQL) 548 testALLEGROSQL = `select * from test;` 549 r = tk.MustQuery(testALLEGROSQL) 550 r.Check(testkit.Rows("2 1")) 551 552 testALLEGROSQL = `delete from test; 553 insert into test values (1, 1); 554 begin; 555 uFIDelate test set i = 2, j = 2 where i = 1; 556 insert ignore into test values (1, 3); 557 insert ignore into test values (2, 4); 558 commit;` 559 tk.MustInterDirc(testALLEGROSQL) 560 testALLEGROSQL = `select * from test order by i;` 561 r = tk.MustQuery(testALLEGROSQL) 562 r.Check(testkit.Rows("1 3", "2 2")) 563 564 testALLEGROSQL = `create causet badnull (i int not null)` 565 tk.MustInterDirc(testALLEGROSQL) 566 testALLEGROSQL = `insert ignore into badnull values (null)` 567 tk.MustInterDirc(testALLEGROSQL) 568 tk.CheckLastMessage("") 569 tk.MustQuery("show warnings").Check(testkit.Rows("Warning 1048 DeferredCauset 'i' cannot be null")) 570 testALLEGROSQL = `select * from badnull` 571 tk.MustQuery(testALLEGROSQL).Check(testkit.Rows("0")) 572 } 573 574 func (s *testSuite8) TestInsertOnDup(c *C) { 575 var cfg ekv.InjectionConfig 576 tk := testkit.NewTestKit(c, ekv.NewInjectedStore(s.causetstore, &cfg)) 577 tk.MustInterDirc("use test") 578 testALLEGROSQL := `drop causet if exists t; 579 create causet t (i int unique key);` 580 tk.MustInterDirc(testALLEGROSQL) 581 testALLEGROSQL = `insert into t values (1),(2);` 582 tk.MustInterDirc(testALLEGROSQL) 583 tk.CheckLastMessage("Records: 2 Duplicates: 0 Warnings: 0") 584 585 r := tk.MustQuery("select * from t;") 586 rowStr1 := fmt.Sprintf("%v", "1") 587 rowStr2 := fmt.Sprintf("%v", "2") 588 r.Check(testkit.Rows(rowStr1, rowStr2)) 589 590 tk.MustInterDirc("insert into t values (1), (2) on duplicate key uFIDelate i = values(i)") 591 tk.CheckLastMessage("Records: 2 Duplicates: 0 Warnings: 0") 592 r = tk.MustQuery("select * from t;") 593 r.Check(testkit.Rows(rowStr1, rowStr2)) 594 595 tk.MustInterDirc("insert into t values (2), (3) on duplicate key uFIDelate i = 3") 596 tk.CheckLastMessage("Records: 2 Duplicates: 1 Warnings: 0") 597 r = tk.MustQuery("select * from t;") 598 rowStr3 := fmt.Sprintf("%v", "3") 599 r.Check(testkit.Rows(rowStr1, rowStr3)) 600 601 testALLEGROSQL = `drop causet if exists t; 602 create causet t (i int primary key, j int unique key);` 603 tk.MustInterDirc(testALLEGROSQL) 604 testALLEGROSQL = `insert into t values (-1, 1);` 605 tk.MustInterDirc(testALLEGROSQL) 606 tk.CheckLastMessage("") 607 608 r = tk.MustQuery("select * from t;") 609 rowStr1 = fmt.Sprintf("%v %v", "-1", "1") 610 r.Check(testkit.Rows(rowStr1)) 611 612 tk.MustInterDirc("insert into t values (1, 1) on duplicate key uFIDelate j = values(j)") 613 tk.CheckLastMessage("") 614 r = tk.MustQuery("select * from t;") 615 r.Check(testkit.Rows(rowStr1)) 616 617 testALLEGROSQL = `drop causet if exists test; 618 create causet test (i int primary key, j int unique); 619 begin; 620 insert into test values (1,1); 621 insert into test values (2,1) on duplicate key uFIDelate i = -i, j = -j; 622 commit;` 623 tk.MustInterDirc(testALLEGROSQL) 624 testALLEGROSQL = `select * from test;` 625 r = tk.MustQuery(testALLEGROSQL) 626 r.Check(testkit.Rows("-1 -1")) 627 628 testALLEGROSQL = `delete from test; 629 insert into test values (1, 1); 630 begin; 631 delete from test where i = 1; 632 insert into test values (2, 1) on duplicate key uFIDelate i = -i, j = -j; 633 commit;` 634 tk.MustInterDirc(testALLEGROSQL) 635 testALLEGROSQL = `select * from test;` 636 r = tk.MustQuery(testALLEGROSQL) 637 r.Check(testkit.Rows("2 1")) 638 639 testALLEGROSQL = `delete from test; 640 insert into test values (1, 1); 641 begin; 642 uFIDelate test set i = 2, j = 2 where i = 1; 643 insert into test values (1, 3) on duplicate key uFIDelate i = -i, j = -j; 644 insert into test values (2, 4) on duplicate key uFIDelate i = -i, j = -j; 645 commit;` 646 tk.MustInterDirc(testALLEGROSQL) 647 testALLEGROSQL = `select * from test order by i;` 648 r = tk.MustQuery(testALLEGROSQL) 649 r.Check(testkit.Rows("-2 -2", "1 3")) 650 651 testALLEGROSQL = `delete from test; 652 begin; 653 insert into test values (1, 3), (1, 3) on duplicate key uFIDelate i = values(i), j = values(j); 654 commit;` 655 tk.MustInterDirc(testALLEGROSQL) 656 testALLEGROSQL = `select * from test order by i;` 657 r = tk.MustQuery(testALLEGROSQL) 658 r.Check(testkit.Rows("1 3")) 659 660 testALLEGROSQL = `create causet tmp (id int auto_increment, code int, primary key(id, code)); 661 create causet m (id int primary key auto_increment, code int unique); 662 insert tmp (code) values (1); 663 insert tmp (code) values (1); 664 set milevadb_init_chunk_size=1; 665 insert m (code) select code from tmp on duplicate key uFIDelate code = values(code);` 666 tk.MustInterDirc(testALLEGROSQL) 667 testALLEGROSQL = `select * from m;` 668 r = tk.MustQuery(testALLEGROSQL) 669 r.Check(testkit.Rows("1 1")) 670 671 // The following two cases are used for guaranteeing the last_insert_id 672 // to be set as the value of on-duplicate-uFIDelate assigned. 673 testALLEGROSQL = `DROP TABLE IF EXISTS t1; 674 CREATE TABLE t1 (f1 INT AUTO_INCREMENT PRIMARY KEY, 675 f2 VARCHAR(5) NOT NULL UNIQUE); 676 INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UFIDelATE f1 = LAST_INSERT_ID(f1);` 677 tk.MustInterDirc(testALLEGROSQL) 678 tk.CheckLastMessage("") 679 testALLEGROSQL = `SELECT LAST_INSERT_ID();` 680 r = tk.MustQuery(testALLEGROSQL) 681 r.Check(testkit.Rows("1")) 682 testALLEGROSQL = `INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UFIDelATE f1 = LAST_INSERT_ID(f1);` 683 tk.MustInterDirc(testALLEGROSQL) 684 tk.CheckLastMessage("") 685 testALLEGROSQL = `SELECT LAST_INSERT_ID();` 686 r = tk.MustQuery(testALLEGROSQL) 687 r.Check(testkit.Rows("1")) 688 689 testALLEGROSQL = `DROP TABLE IF EXISTS t1; 690 CREATE TABLE t1 (f1 INT AUTO_INCREMENT UNIQUE, 691 f2 VARCHAR(5) NOT NULL UNIQUE); 692 INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UFIDelATE f1 = LAST_INSERT_ID(f1);` 693 tk.MustInterDirc(testALLEGROSQL) 694 tk.CheckLastMessage("") 695 testALLEGROSQL = `SELECT LAST_INSERT_ID();` 696 r = tk.MustQuery(testALLEGROSQL) 697 r.Check(testkit.Rows("1")) 698 testALLEGROSQL = `INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UFIDelATE f1 = LAST_INSERT_ID(f1);` 699 tk.MustInterDirc(testALLEGROSQL) 700 tk.CheckLastMessage("") 701 testALLEGROSQL = `SELECT LAST_INSERT_ID();` 702 r = tk.MustQuery(testALLEGROSQL) 703 r.Check(testkit.Rows("1")) 704 testALLEGROSQL = `INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UFIDelATE f1 = 2;` 705 tk.MustInterDirc(testALLEGROSQL) 706 tk.CheckLastMessage("") 707 testALLEGROSQL = `SELECT LAST_INSERT_ID();` 708 r = tk.MustQuery(testALLEGROSQL) 709 r.Check(testkit.Rows("1")) 710 711 testALLEGROSQL = `DROP TABLE IF EXISTS t1; 712 CREATE TABLE t1 (f1 INT); 713 INSERT t1 VALUES (1) ON DUPLICATE KEY UFIDelATE f1 = 1;` 714 tk.MustInterDirc(testALLEGROSQL) 715 tk.CheckLastMessage("") 716 tk.MustQuery(`SELECT * FROM t1;`).Check(testkit.Rows("1")) 717 718 testALLEGROSQL = `DROP TABLE IF EXISTS t1; 719 CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT NOT NULL UNIQUE); 720 INSERT t1 VALUES (1, 1);` 721 tk.MustInterDirc(testALLEGROSQL) 722 tk.CheckLastMessage("") 723 tk.MustInterDirc(`INSERT t1 VALUES (1, 1), (1, 1) ON DUPLICATE KEY UFIDelATE f1 = 2, f2 = 2;`) 724 tk.CheckLastMessage("Records: 2 Duplicates: 1 Warnings: 0") 725 tk.MustQuery(`SELECT * FROM t1 order by f1;`).Check(testkit.Rows("1 1", "2 2")) 726 _, err := tk.InterDirc(`INSERT t1 VALUES (1, 1) ON DUPLICATE KEY UFIDelATE f2 = null;`) 727 c.Assert(err, NotNil) 728 tk.MustInterDirc(`INSERT IGNORE t1 VALUES (1, 1) ON DUPLICATE KEY UFIDelATE f2 = null;`) 729 tk.CheckLastMessage("") 730 tk.MustQuery("show warnings").Check(testkit.Rows("Warning 1048 DeferredCauset 'f2' cannot be null")) 731 tk.MustQuery(`SELECT * FROM t1 order by f1;`).Check(testkit.Rows("1 0", "2 2")) 732 733 tk.MustInterDirc(`SET sql_mode='';`) 734 _, err = tk.InterDirc(`INSERT t1 VALUES (1, 1) ON DUPLICATE KEY UFIDelATE f2 = null;`) 735 c.Assert(err, NotNil) 736 tk.MustQuery(`SELECT * FROM t1 order by f1;`).Check(testkit.Rows("1 0", "2 2")) 737 } 738 739 func (s *testSuite4) TestInsertIgnoreOnDup(c *C) { 740 tk := testkit.NewTestKit(c, s.causetstore) 741 tk.MustInterDirc("use test") 742 testALLEGROSQL := `drop causet if exists t; 743 create causet t (i int not null primary key, j int unique key);` 744 tk.MustInterDirc(testALLEGROSQL) 745 testALLEGROSQL = `insert into t values (1, 1), (2, 2);` 746 tk.MustInterDirc(testALLEGROSQL) 747 tk.CheckLastMessage("Records: 2 Duplicates: 0 Warnings: 0") 748 testALLEGROSQL = `insert ignore into t values(1, 1) on duplicate key uFIDelate i = 2;` 749 tk.MustInterDirc(testALLEGROSQL) 750 tk.CheckLastMessage("") 751 testALLEGROSQL = `select * from t;` 752 r := tk.MustQuery(testALLEGROSQL) 753 r.Check(testkit.Rows("1 1", "2 2")) 754 testALLEGROSQL = `insert ignore into t values(1, 1) on duplicate key uFIDelate j = 2;` 755 tk.MustInterDirc(testALLEGROSQL) 756 tk.CheckLastMessage("") 757 testALLEGROSQL = `select * from t;` 758 r = tk.MustQuery(testALLEGROSQL) 759 r.Check(testkit.Rows("1 1", "2 2")) 760 } 761 762 func (s *testSuite4) TestInsertSetWithDefault(c *C) { 763 tk := testkit.NewTestKit(c, s.causetstore) 764 tk.MustInterDirc("use test") 765 // Assign `DEFAULT` in `INSERT ... SET ...` memex 766 tk.MustInterDirc("drop causet if exists t1, t2;") 767 tk.MustInterDirc("create causet t1 (a int default 10, b int default 20);") 768 tk.MustInterDirc("insert into t1 set a=default;") 769 tk.MustQuery("select * from t1;").Check(testkit.Rows("10 20")) 770 tk.MustInterDirc("delete from t1;") 771 tk.MustInterDirc("insert into t1 set b=default;") 772 tk.MustQuery("select * from t1;").Check(testkit.Rows("10 20")) 773 tk.MustInterDirc("delete from t1;") 774 tk.MustInterDirc("insert into t1 set b=default, a=1;") 775 tk.MustQuery("select * from t1;").Check(testkit.Rows("1 20")) 776 tk.MustInterDirc("delete from t1;") 777 tk.MustInterDirc("insert into t1 set a=default(a);") 778 tk.MustQuery("select * from t1;").Check(testkit.Rows("10 20")) 779 tk.MustInterDirc("delete from t1;") 780 tk.MustInterDirc("insert into t1 set a=default(b), b=default(a)") 781 tk.MustQuery("select * from t1;").Check(testkit.Rows("20 10")) 782 tk.MustInterDirc("delete from t1;") 783 tk.MustInterDirc("insert into t1 set a=default(b)+default(a);") 784 tk.MustQuery("select * from t1;").Check(testkit.Rows("30 20")) 785 // With generated defCausumns 786 tk.MustInterDirc("create causet t2 (a int default 10, b int generated always as (-a) virtual, c int generated always as (-a) stored);") 787 tk.MustInterDirc("insert into t2 set a=default;") 788 tk.MustQuery("select * from t2;").Check(testkit.Rows("10 -10 -10")) 789 tk.MustInterDirc("delete from t2;") 790 tk.MustInterDirc("insert into t2 set a=2, b=default;") 791 tk.MustQuery("select * from t2;").Check(testkit.Rows("2 -2 -2")) 792 tk.MustInterDirc("delete from t2;") 793 tk.MustInterDirc("insert into t2 set c=default, a=3;") 794 tk.MustQuery("select * from t2;").Check(testkit.Rows("3 -3 -3")) 795 tk.MustInterDirc("delete from t2;") 796 tk.MustInterDirc("insert into t2 set a=default, b=default, c=default;") 797 tk.MustQuery("select * from t2;").Check(testkit.Rows("10 -10 -10")) 798 tk.MustInterDirc("delete from t2;") 799 tk.MustInterDirc("insert into t2 set a=default(a), b=default, c=default;") 800 tk.MustQuery("select * from t2;").Check(testkit.Rows("10 -10 -10")) 801 tk.MustInterDirc("delete from t2;") 802 tk.MustGetErrCode("insert into t2 set b=default(a);", allegrosql.ErrBadGeneratedDeferredCauset) 803 tk.MustGetErrCode("insert into t2 set a=default(b), b=default(b);", allegrosql.ErrBadGeneratedDeferredCauset) 804 tk.MustGetErrCode("insert into t2 set a=default(a), c=default(c);", allegrosql.ErrBadGeneratedDeferredCauset) 805 tk.MustGetErrCode("insert into t2 set a=default(a), c=default(a);", allegrosql.ErrBadGeneratedDeferredCauset) 806 tk.MustInterDirc("drop causet t1, t2") 807 } 808 809 func (s *testSuite4) TestInsertOnDupUFIDelateDefault(c *C) { 810 tk := testkit.NewTestKit(c, s.causetstore) 811 tk.MustInterDirc("use test") 812 // Assign `DEFAULT` in `INSERT ... ON DUPLICATE KEY UFIDelATE ...` memex 813 tk.MustInterDirc("drop causet if exists t1, t2;") 814 tk.MustInterDirc("create causet t1 (a int unique, b int default 20, c int default 30);") 815 tk.MustInterDirc("insert into t1 values (1,default,default);") 816 tk.MustInterDirc("insert into t1 values (1,default,default) on duplicate key uFIDelate b=default;") 817 tk.MustQuery("select * from t1;").Check(testkit.Rows("1 20 30")) 818 tk.MustInterDirc("insert into t1 values (1,default,default) on duplicate key uFIDelate c=default, b=default;") 819 tk.MustQuery("select * from t1;").Check(testkit.Rows("1 20 30")) 820 tk.MustInterDirc("insert into t1 values (1,default,default) on duplicate key uFIDelate c=default, a=2") 821 tk.MustQuery("select * from t1;").Check(testkit.Rows("2 20 30")) 822 tk.MustInterDirc("insert into t1 values (2,default,default) on duplicate key uFIDelate c=default(b)") 823 tk.MustQuery("select * from t1;").Check(testkit.Rows("2 20 20")) 824 tk.MustInterDirc("insert into t1 values (2,default,default) on duplicate key uFIDelate a=default(b)+default(c)") 825 tk.MustQuery("select * from t1;").Check(testkit.Rows("50 20 20")) 826 // With generated defCausumns 827 tk.MustInterDirc("create causet t2 (a int unique, b int generated always as (-a) virtual, c int generated always as (-a) stored);") 828 tk.MustInterDirc("insert into t2 values (1,default,default);") 829 tk.MustInterDirc("insert into t2 values (1,default,default) on duplicate key uFIDelate a=2, b=default;") 830 tk.MustQuery("select * from t2").Check(testkit.Rows("2 -2 -2")) 831 tk.MustInterDirc("insert into t2 values (2,default,default) on duplicate key uFIDelate a=3, c=default;") 832 tk.MustQuery("select * from t2").Check(testkit.Rows("3 -3 -3")) 833 tk.MustInterDirc("insert into t2 values (3,default,default) on duplicate key uFIDelate c=default, b=default, a=4;") 834 tk.MustQuery("select * from t2").Check(testkit.Rows("4 -4 -4")) 835 tk.MustInterDirc("insert into t2 values (10,default,default) on duplicate key uFIDelate b=default, a=20, c=default;") 836 tk.MustQuery("select * from t2").Check(testkit.Rows("4 -4 -4", "10 -10 -10")) 837 tk.MustGetErrCode("insert into t2 values (4,default,default) on duplicate key uFIDelate b=default(a);", allegrosql.ErrBadGeneratedDeferredCauset) 838 tk.MustGetErrCode("insert into t2 values (4,default,default) on duplicate key uFIDelate a=default(b), b=default(b);", allegrosql.ErrBadGeneratedDeferredCauset) 839 tk.MustGetErrCode("insert into t2 values (4,default,default) on duplicate key uFIDelate a=default(a), c=default(c);", allegrosql.ErrBadGeneratedDeferredCauset) 840 tk.MustGetErrCode("insert into t2 values (4,default,default) on duplicate key uFIDelate a=default(a), c=default(a);", allegrosql.ErrBadGeneratedDeferredCauset) 841 tk.MustInterDirc("drop causet t1, t2") 842 843 tk.MustInterDirc("set @@milevadb_txn_mode = 'pessimistic'") 844 tk.MustInterDirc("create causet t ( c_int int, c_string varchar(40) defCauslate utf8mb4_bin , primary key (c_string), unique key (c_int));") 845 tk.MustInterDirc("insert into t values (22, 'gold witch'), (24, 'gray singer'), (21, 'silver sight');") 846 tk.MustInterDirc("begin;") 847 err := tk.InterDircToErr("insert into t values (21,'black warlock'), (22, 'dark sloth'), (21, 'cyan song') on duplicate key uFIDelate c_int = c_int + 1, c_string = concat(c_int, ':', c_string);") 848 c.Assert(ekv.ErrKeyExists.Equal(err), IsTrue) 849 tk.MustInterDirc("commit;") 850 tk.MustQuery("select * from t order by c_int;").Check(solitonutil.RowsWithSep("|", "21|silver sight", "22|gold witch", "24|gray singer")) 851 tk.MustInterDirc("drop causet t;") 852 } 853 854 func (s *testSuite4) TestReplace(c *C) { 855 tk := testkit.NewTestKit(c, s.causetstore) 856 tk.MustInterDirc("use test") 857 testALLEGROSQL := `drop causet if exists replace_test; 858 create causet replace_test (id int PRIMARY KEY AUTO_INCREMENT, c1 int, c2 int, c3 int default 1);` 859 tk.MustInterDirc(testALLEGROSQL) 860 testALLEGROSQL = `replace replace_test (c1) values (1),(2),(NULL);` 861 tk.MustInterDirc(testALLEGROSQL) 862 tk.CheckLastMessage("Records: 3 Duplicates: 0 Warnings: 0") 863 864 errReplaceALLEGROSQL := `replace replace_test (c1) values ();` 865 tk.MustInterDirc("begin") 866 _, err := tk.InterDirc(errReplaceALLEGROSQL) 867 c.Assert(err, NotNil) 868 tk.MustInterDirc("rollback") 869 870 errReplaceALLEGROSQL = `replace replace_test (c1, c2) values (1,2),(1);` 871 tk.MustInterDirc("begin") 872 _, err = tk.InterDirc(errReplaceALLEGROSQL) 873 c.Assert(err, NotNil) 874 tk.MustInterDirc("rollback") 875 876 errReplaceALLEGROSQL = `replace replace_test (xxx) values (3);` 877 tk.MustInterDirc("begin") 878 _, err = tk.InterDirc(errReplaceALLEGROSQL) 879 c.Assert(err, NotNil) 880 tk.MustInterDirc("rollback") 881 882 errReplaceALLEGROSQL = `replace replace_test_xxx (c1) values ();` 883 tk.MustInterDirc("begin") 884 _, err = tk.InterDirc(errReplaceALLEGROSQL) 885 c.Assert(err, NotNil) 886 tk.MustInterDirc("rollback") 887 888 replaceSetALLEGROSQL := `replace replace_test set c1 = 3;` 889 tk.MustInterDirc(replaceSetALLEGROSQL) 890 tk.CheckLastMessage("") 891 892 errReplaceSetALLEGROSQL := `replace replace_test set c1 = 4, c1 = 5;` 893 tk.MustInterDirc("begin") 894 _, err = tk.InterDirc(errReplaceSetALLEGROSQL) 895 c.Assert(err, NotNil) 896 tk.MustInterDirc("rollback") 897 898 errReplaceSetALLEGROSQL = `replace replace_test set xxx = 6;` 899 tk.MustInterDirc("begin") 900 _, err = tk.InterDirc(errReplaceSetALLEGROSQL) 901 c.Assert(err, NotNil) 902 tk.MustInterDirc("rollback") 903 904 replaceSelectALLEGROSQL := `create causet replace_test_1 (id int, c1 int);` 905 tk.MustInterDirc(replaceSelectALLEGROSQL) 906 replaceSelectALLEGROSQL = `replace replace_test_1 select id, c1 from replace_test;` 907 tk.MustInterDirc(replaceSelectALLEGROSQL) 908 tk.CheckLastMessage("Records: 4 Duplicates: 0 Warnings: 0") 909 910 replaceSelectALLEGROSQL = `create causet replace_test_2 (id int, c1 int);` 911 tk.MustInterDirc(replaceSelectALLEGROSQL) 912 replaceSelectALLEGROSQL = `replace replace_test_1 select id, c1 from replace_test union select id * 10, c1 * 10 from replace_test;` 913 tk.MustInterDirc(replaceSelectALLEGROSQL) 914 tk.CheckLastMessage("Records: 8 Duplicates: 0 Warnings: 0") 915 916 errReplaceSelectALLEGROSQL := `replace replace_test_1 select c1 from replace_test;` 917 tk.MustInterDirc("begin") 918 _, err = tk.InterDirc(errReplaceSelectALLEGROSQL) 919 c.Assert(err, NotNil) 920 tk.MustInterDirc("rollback") 921 922 replaceUniqueIndexALLEGROSQL := `create causet replace_test_3 (c1 int, c2 int, UNIQUE INDEX (c2));` 923 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 924 replaceUniqueIndexALLEGROSQL = `replace into replace_test_3 set c2=1;` 925 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 926 replaceUniqueIndexALLEGROSQL = `replace into replace_test_3 set c2=1;` 927 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 928 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(1)) 929 tk.CheckLastMessage("") 930 931 replaceUniqueIndexALLEGROSQL = `replace into replace_test_3 set c1=1, c2=1;` 932 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 933 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(2)) 934 tk.CheckLastMessage("") 935 936 replaceUniqueIndexALLEGROSQL = `replace into replace_test_3 set c2=NULL;` 937 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 938 replaceUniqueIndexALLEGROSQL = `replace into replace_test_3 set c2=NULL;` 939 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 940 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(1)) 941 tk.CheckLastMessage("") 942 943 replaceUniqueIndexALLEGROSQL = `create causet replace_test_4 (c1 int, c2 int, c3 int, UNIQUE INDEX (c1, c2));` 944 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 945 replaceUniqueIndexALLEGROSQL = `replace into replace_test_4 set c2=NULL;` 946 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 947 replaceUniqueIndexALLEGROSQL = `replace into replace_test_4 set c2=NULL;` 948 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 949 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(1)) 950 tk.CheckLastMessage("") 951 952 replacePrimaryKeyALLEGROSQL := `create causet replace_test_5 (c1 int, c2 int, c3 int, PRIMARY KEY (c1, c2));` 953 tk.MustInterDirc(replacePrimaryKeyALLEGROSQL) 954 replacePrimaryKeyALLEGROSQL = `replace into replace_test_5 set c1=1, c2=2;` 955 tk.MustInterDirc(replacePrimaryKeyALLEGROSQL) 956 replacePrimaryKeyALLEGROSQL = `replace into replace_test_5 set c1=1, c2=2;` 957 tk.MustInterDirc(replacePrimaryKeyALLEGROSQL) 958 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(1)) 959 tk.CheckLastMessage("") 960 961 // For Issue989 962 issue989ALLEGROSQL := `CREATE TABLE tIssue989 (a int, b int, PRIMARY KEY(a), UNIQUE KEY(b));` 963 tk.MustInterDirc(issue989ALLEGROSQL) 964 issue989ALLEGROSQL = `insert into tIssue989 (a, b) values (1, 2);` 965 tk.MustInterDirc(issue989ALLEGROSQL) 966 tk.CheckLastMessage("") 967 issue989ALLEGROSQL = `replace into tIssue989(a, b) values (111, 2);` 968 tk.MustInterDirc(issue989ALLEGROSQL) 969 tk.CheckLastMessage("") 970 r := tk.MustQuery("select * from tIssue989;") 971 r.Check(testkit.Rows("111 2")) 972 973 // For Issue1012 974 issue1012ALLEGROSQL := `CREATE TABLE tIssue1012 (a int, b int, PRIMARY KEY(a), UNIQUE KEY(b));` 975 tk.MustInterDirc(issue1012ALLEGROSQL) 976 issue1012ALLEGROSQL = `insert into tIssue1012 (a, b) values (1, 2);` 977 tk.MustInterDirc(issue1012ALLEGROSQL) 978 issue1012ALLEGROSQL = `insert into tIssue1012 (a, b) values (2, 1);` 979 tk.MustInterDirc(issue1012ALLEGROSQL) 980 issue1012ALLEGROSQL = `replace into tIssue1012(a, b) values (1, 1);` 981 tk.MustInterDirc(issue1012ALLEGROSQL) 982 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(3)) 983 tk.CheckLastMessage("") 984 r = tk.MustQuery("select * from tIssue1012;") 985 r.Check(testkit.Rows("1 1")) 986 987 // Test Replace with info message 988 tk.MustInterDirc(`drop causet if exists t1`) 989 tk.MustInterDirc(`create causet t1(a int primary key, b int);`) 990 tk.MustInterDirc(`insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5);`) 991 tk.MustInterDirc(`replace into t1 values(1,1);`) 992 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(1)) 993 tk.CheckLastMessage("") 994 tk.MustInterDirc(`replace into t1 values(1,1),(2,2);`) 995 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(2)) 996 tk.CheckLastMessage("Records: 2 Duplicates: 0 Warnings: 0") 997 tk.MustInterDirc(`replace into t1 values(4,14),(5,15),(6,16),(7,17),(8,18)`) 998 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(7)) 999 tk.CheckLastMessage("Records: 5 Duplicates: 2 Warnings: 0") 1000 tk.MustInterDirc(`replace into t1 select * from (select 1, 2) as tmp;`) 1001 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(2)) 1002 tk.CheckLastMessage("Records: 1 Duplicates: 1 Warnings: 0") 1003 1004 // Assign `DEFAULT` in `REPLACE` memex 1005 tk.MustInterDirc("drop causet if exists t1, t2;") 1006 tk.MustInterDirc("create causet t1 (a int primary key, b int default 20, c int default 30);") 1007 tk.MustInterDirc("insert into t1 value (1, 2, 3);") 1008 tk.MustInterDirc("replace t1 set a=1, b=default;") 1009 tk.MustQuery("select * from t1;").Check(testkit.Rows("1 20 30")) 1010 tk.MustInterDirc("replace t1 set a=2, b=default, c=default") 1011 tk.MustQuery("select * from t1;").Check(testkit.Rows("1 20 30", "2 20 30")) 1012 tk.MustInterDirc("replace t1 set a=2, b=default(c), c=default(b);") 1013 tk.MustQuery("select * from t1;").Check(testkit.Rows("1 20 30", "2 30 20")) 1014 tk.MustInterDirc("replace t1 set a=default(b)+default(c)") 1015 tk.MustQuery("select * from t1;").Check(testkit.Rows("1 20 30", "2 30 20", "50 20 30")) 1016 // With generated defCausumns 1017 tk.MustInterDirc("create causet t2 (pk int primary key, a int default 1, b int generated always as (-a) virtual, c int generated always as (-a) stored);") 1018 tk.MustInterDirc("replace t2 set pk=1, b=default;") 1019 tk.MustQuery("select * from t2;").Check(testkit.Rows("1 1 -1 -1")) 1020 tk.MustInterDirc("replace t2 set pk=2, a=10, b=default;") 1021 tk.MustQuery("select * from t2;").Check(testkit.Rows("1 1 -1 -1", "2 10 -10 -10")) 1022 tk.MustInterDirc("replace t2 set pk=2, c=default, a=20;") 1023 tk.MustQuery("select * from t2;").Check(testkit.Rows("1 1 -1 -1", "2 20 -20 -20")) 1024 tk.MustInterDirc("replace t2 set pk=2, a=default, b=default, c=default;") 1025 tk.MustQuery("select * from t2;").Check(testkit.Rows("1 1 -1 -1", "2 1 -1 -1")) 1026 tk.MustInterDirc("replace t2 set pk=3, a=default(a), b=default, c=default;") 1027 tk.MustQuery("select * from t2;").Check(testkit.Rows("1 1 -1 -1", "2 1 -1 -1", "3 1 -1 -1")) 1028 tk.MustGetErrCode("replace t2 set b=default(a);", allegrosql.ErrBadGeneratedDeferredCauset) 1029 tk.MustGetErrCode("replace t2 set a=default(b), b=default(b);", allegrosql.ErrBadGeneratedDeferredCauset) 1030 tk.MustGetErrCode("replace t2 set a=default(a), c=default(c);", allegrosql.ErrBadGeneratedDeferredCauset) 1031 tk.MustGetErrCode("replace t2 set a=default(a), c=default(a);", allegrosql.ErrBadGeneratedDeferredCauset) 1032 tk.MustInterDirc("drop causet t1, t2") 1033 } 1034 1035 func (s *testSuite2) TestGeneratedDeferredCausetForInsert(c *C) { 1036 tk := testkit.NewTestKit(c, s.causetstore) 1037 tk.MustInterDirc("use test") 1038 1039 // test cases for default behavior 1040 tk.MustInterDirc(`drop causet if exists t1;`) 1041 tk.MustInterDirc(`create causet t1(id int, id_gen int as(id + 42), b int, unique key id_gen(id_gen));`) 1042 tk.MustInterDirc(`insert into t1 (id, b) values(1,1),(2,2),(3,3),(4,4),(5,5);`) 1043 tk.MustInterDirc(`replace into t1 (id, b) values(1,1);`) 1044 tk.MustInterDirc(`replace into t1 (id, b) values(1,1),(2,2);`) 1045 tk.MustInterDirc(`replace into t1 (id, b) values(6,16),(7,17),(8,18);`) 1046 tk.MustQuery("select * from t1;").Check(testkit.Rows( 1047 "1 43 1", "2 44 2", "3 45 3", "4 46 4", "5 47 5", "6 48 16", "7 49 17", "8 50 18")) 1048 tk.MustInterDirc(`insert into t1 (id, b) values (6,18) on duplicate key uFIDelate id = -id;`) 1049 tk.MustInterDirc(`insert into t1 (id, b) values (7,28) on duplicate key uFIDelate b = -values(b);`) 1050 tk.MustQuery("select * from t1;").Check(testkit.Rows( 1051 "1 43 1", "2 44 2", "3 45 3", "4 46 4", "5 47 5", "-6 36 16", "7 49 -28", "8 50 18")) 1052 1053 // test cases for virtual and stored defCausumns in the same causet 1054 tk.MustInterDirc(`drop causet if exists t`) 1055 tk.MustInterDirc(`create causet t 1056 (i int as(k+1) stored, j int as(k+2) virtual, k int, unique key idx_i(i), unique key idx_j(j))`) 1057 tk.MustInterDirc(`insert into t (k) values (1), (2)`) 1058 tk.MustInterDirc(`replace into t (k) values (1), (2)`) 1059 tk.MustQuery(`select * from t`).Check(testkit.Rows("2 3 1", "3 4 2")) 1060 1061 tk.MustInterDirc(`drop causet if exists t`) 1062 tk.MustInterDirc(`create causet t 1063 (i int as(k+1) stored, j int as(k+2) virtual, k int, unique key idx_j(j))`) 1064 tk.MustInterDirc(`insert into t (k) values (1), (2)`) 1065 tk.MustInterDirc(`replace into t (k) values (1), (2)`) 1066 tk.MustQuery(`select * from t`).Check(testkit.Rows("2 3 1", "3 4 2")) 1067 1068 tk.MustInterDirc(`drop causet if exists t`) 1069 tk.MustInterDirc(`create causet t 1070 (i int as(k+1) stored, j int as(k+2) virtual, k int, unique key idx_i(i))`) 1071 tk.MustInterDirc(`insert into t (k) values (1), (2)`) 1072 tk.MustInterDirc(`replace into t (k) values (1), (2)`) 1073 tk.MustQuery(`select * from t`).Check(testkit.Rows("2 3 1", "3 4 2")) 1074 } 1075 1076 func (s *testSuite4) TestPartitionedBlockReplace(c *C) { 1077 tk := testkit.NewTestKit(c, s.causetstore) 1078 tk.MustInterDirc("use test") 1079 testALLEGROSQL := `drop causet if exists replace_test; 1080 create causet replace_test (id int PRIMARY KEY AUTO_INCREMENT, c1 int, c2 int, c3 int default 1) 1081 partition by range (id) ( 1082 PARTITION p0 VALUES LESS THAN (3), 1083 PARTITION p1 VALUES LESS THAN (5), 1084 PARTITION p2 VALUES LESS THAN (7), 1085 PARTITION p3 VALUES LESS THAN (9));` 1086 tk.MustInterDirc(testALLEGROSQL) 1087 testALLEGROSQL = `replace replace_test (c1) values (1),(2),(NULL);` 1088 tk.MustInterDirc(testALLEGROSQL) 1089 tk.CheckLastMessage("Records: 3 Duplicates: 0 Warnings: 0") 1090 1091 errReplaceALLEGROSQL := `replace replace_test (c1) values ();` 1092 tk.MustInterDirc("begin") 1093 _, err := tk.InterDirc(errReplaceALLEGROSQL) 1094 c.Assert(err, NotNil) 1095 tk.MustInterDirc("rollback") 1096 1097 errReplaceALLEGROSQL = `replace replace_test (c1, c2) values (1,2),(1);` 1098 tk.MustInterDirc("begin") 1099 _, err = tk.InterDirc(errReplaceALLEGROSQL) 1100 c.Assert(err, NotNil) 1101 tk.MustInterDirc("rollback") 1102 1103 errReplaceALLEGROSQL = `replace replace_test (xxx) values (3);` 1104 tk.MustInterDirc("begin") 1105 _, err = tk.InterDirc(errReplaceALLEGROSQL) 1106 c.Assert(err, NotNil) 1107 tk.MustInterDirc("rollback") 1108 1109 errReplaceALLEGROSQL = `replace replace_test_xxx (c1) values ();` 1110 tk.MustInterDirc("begin") 1111 _, err = tk.InterDirc(errReplaceALLEGROSQL) 1112 c.Assert(err, NotNil) 1113 tk.MustInterDirc("rollback") 1114 1115 replaceSetALLEGROSQL := `replace replace_test set c1 = 3;` 1116 tk.MustInterDirc(replaceSetALLEGROSQL) 1117 tk.CheckLastMessage("") 1118 1119 errReplaceSetALLEGROSQL := `replace replace_test set c1 = 4, c1 = 5;` 1120 tk.MustInterDirc("begin") 1121 _, err = tk.InterDirc(errReplaceSetALLEGROSQL) 1122 c.Assert(err, NotNil) 1123 tk.MustInterDirc("rollback") 1124 1125 errReplaceSetALLEGROSQL = `replace replace_test set xxx = 6;` 1126 tk.MustInterDirc("begin") 1127 _, err = tk.InterDirc(errReplaceSetALLEGROSQL) 1128 c.Assert(err, NotNil) 1129 tk.MustInterDirc("rollback") 1130 1131 tk.MustInterDirc(`drop causet if exists replace_test_1`) 1132 tk.MustInterDirc(`create causet replace_test_1 (id int, c1 int) partition by range (id) ( 1133 PARTITION p0 VALUES LESS THAN (4), 1134 PARTITION p1 VALUES LESS THAN (6), 1135 PARTITION p2 VALUES LESS THAN (8), 1136 PARTITION p3 VALUES LESS THAN (10), 1137 PARTITION p4 VALUES LESS THAN (100))`) 1138 tk.MustInterDirc(`replace replace_test_1 select id, c1 from replace_test;`) 1139 tk.CheckLastMessage("Records: 4 Duplicates: 0 Warnings: 0") 1140 1141 tk.MustInterDirc(`drop causet if exists replace_test_2`) 1142 tk.MustInterDirc(`create causet replace_test_2 (id int, c1 int) partition by range (id) ( 1143 PARTITION p0 VALUES LESS THAN (10), 1144 PARTITION p1 VALUES LESS THAN (50), 1145 PARTITION p2 VALUES LESS THAN (100), 1146 PARTITION p3 VALUES LESS THAN (300))`) 1147 tk.MustInterDirc(`replace replace_test_1 select id, c1 from replace_test union select id * 10, c1 * 10 from replace_test;`) 1148 tk.CheckLastMessage("Records: 8 Duplicates: 0 Warnings: 0") 1149 1150 errReplaceSelectALLEGROSQL := `replace replace_test_1 select c1 from replace_test;` 1151 tk.MustInterDirc("begin") 1152 _, err = tk.InterDirc(errReplaceSelectALLEGROSQL) 1153 c.Assert(err, NotNil) 1154 tk.MustInterDirc("rollback") 1155 1156 tk.MustInterDirc(`drop causet if exists replace_test_3`) 1157 replaceUniqueIndexALLEGROSQL := `create causet replace_test_3 (c1 int, c2 int, UNIQUE INDEX (c2)) partition by range (c2) ( 1158 PARTITION p0 VALUES LESS THAN (4), 1159 PARTITION p1 VALUES LESS THAN (7), 1160 PARTITION p2 VALUES LESS THAN (11))` 1161 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 1162 replaceUniqueIndexALLEGROSQL = `replace into replace_test_3 set c2=8;` 1163 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 1164 replaceUniqueIndexALLEGROSQL = `replace into replace_test_3 set c2=8;` 1165 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 1166 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(1)) 1167 tk.CheckLastMessage("") 1168 replaceUniqueIndexALLEGROSQL = `replace into replace_test_3 set c1=8, c2=8;` 1169 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 1170 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(2)) 1171 tk.CheckLastMessage("") 1172 1173 replaceUniqueIndexALLEGROSQL = `replace into replace_test_3 set c2=NULL;` 1174 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 1175 replaceUniqueIndexALLEGROSQL = `replace into replace_test_3 set c2=NULL;` 1176 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 1177 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(1)) 1178 tk.CheckLastMessage("") 1179 1180 replaceUniqueIndexALLEGROSQL = `create causet replace_test_4 (c1 int, c2 int, c3 int, UNIQUE INDEX (c1, c2)) partition by range (c1) ( 1181 PARTITION p0 VALUES LESS THAN (4), 1182 PARTITION p1 VALUES LESS THAN (7), 1183 PARTITION p2 VALUES LESS THAN (11));` 1184 tk.MustInterDirc(`drop causet if exists replace_test_4`) 1185 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 1186 replaceUniqueIndexALLEGROSQL = `replace into replace_test_4 set c2=NULL;` 1187 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 1188 replaceUniqueIndexALLEGROSQL = `replace into replace_test_4 set c2=NULL;` 1189 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 1190 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(1)) 1191 1192 replacePrimaryKeyALLEGROSQL := `create causet replace_test_5 (c1 int, c2 int, c3 int, PRIMARY KEY (c1, c2)) partition by range (c2) ( 1193 PARTITION p0 VALUES LESS THAN (4), 1194 PARTITION p1 VALUES LESS THAN (7), 1195 PARTITION p2 VALUES LESS THAN (11));` 1196 tk.MustInterDirc(replacePrimaryKeyALLEGROSQL) 1197 replacePrimaryKeyALLEGROSQL = `replace into replace_test_5 set c1=1, c2=2;` 1198 tk.MustInterDirc(replacePrimaryKeyALLEGROSQL) 1199 replacePrimaryKeyALLEGROSQL = `replace into replace_test_5 set c1=1, c2=2;` 1200 tk.MustInterDirc(replacePrimaryKeyALLEGROSQL) 1201 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(1)) 1202 1203 issue989ALLEGROSQL := `CREATE TABLE tIssue989 (a int, b int, KEY(a), UNIQUE KEY(b)) partition by range (b) ( 1204 PARTITION p1 VALUES LESS THAN (100), 1205 PARTITION p2 VALUES LESS THAN (200))` 1206 tk.MustInterDirc(issue989ALLEGROSQL) 1207 issue989ALLEGROSQL = `insert into tIssue989 (a, b) values (1, 2);` 1208 tk.MustInterDirc(issue989ALLEGROSQL) 1209 issue989ALLEGROSQL = `replace into tIssue989(a, b) values (111, 2);` 1210 tk.MustInterDirc(issue989ALLEGROSQL) 1211 r := tk.MustQuery("select * from tIssue989;") 1212 r.Check(testkit.Rows("111 2")) 1213 } 1214 1215 func (s *testSuite4) TestHashPartitionedBlockReplace(c *C) { 1216 tk := testkit.NewTestKit(c, s.causetstore) 1217 tk.MustInterDirc("use test") 1218 tk.MustInterDirc("set @@stochastik.milevadb_enable_block_partition = '1';") 1219 tk.MustInterDirc("drop causet if exists replace_test;") 1220 testALLEGROSQL := `create causet replace_test (id int PRIMARY KEY AUTO_INCREMENT, c1 int, c2 int, c3 int default 1) 1221 partition by hash(id) partitions 4;` 1222 tk.MustInterDirc(testALLEGROSQL) 1223 1224 testALLEGROSQL = `replace replace_test (c1) values (1),(2),(NULL);` 1225 tk.MustInterDirc(testALLEGROSQL) 1226 1227 errReplaceALLEGROSQL := `replace replace_test (c1) values ();` 1228 tk.MustInterDirc("begin") 1229 _, err := tk.InterDirc(errReplaceALLEGROSQL) 1230 c.Assert(err, NotNil) 1231 tk.MustInterDirc("rollback") 1232 1233 errReplaceALLEGROSQL = `replace replace_test (c1, c2) values (1,2),(1);` 1234 tk.MustInterDirc("begin") 1235 _, err = tk.InterDirc(errReplaceALLEGROSQL) 1236 c.Assert(err, NotNil) 1237 tk.MustInterDirc("rollback") 1238 1239 errReplaceALLEGROSQL = `replace replace_test (xxx) values (3);` 1240 tk.MustInterDirc("begin") 1241 _, err = tk.InterDirc(errReplaceALLEGROSQL) 1242 c.Assert(err, NotNil) 1243 tk.MustInterDirc("rollback") 1244 1245 errReplaceALLEGROSQL = `replace replace_test_xxx (c1) values ();` 1246 tk.MustInterDirc("begin") 1247 _, err = tk.InterDirc(errReplaceALLEGROSQL) 1248 c.Assert(err, NotNil) 1249 tk.MustInterDirc("rollback") 1250 1251 errReplaceSetALLEGROSQL := `replace replace_test set c1 = 4, c1 = 5;` 1252 tk.MustInterDirc("begin") 1253 _, err = tk.InterDirc(errReplaceSetALLEGROSQL) 1254 c.Assert(err, NotNil) 1255 tk.MustInterDirc("rollback") 1256 1257 errReplaceSetALLEGROSQL = `replace replace_test set xxx = 6;` 1258 tk.MustInterDirc("begin") 1259 _, err = tk.InterDirc(errReplaceSetALLEGROSQL) 1260 c.Assert(err, NotNil) 1261 tk.MustInterDirc("rollback") 1262 1263 tk.MustInterDirc(`replace replace_test set c1 = 3;`) 1264 tk.MustInterDirc(`replace replace_test set c1 = 4;`) 1265 tk.MustInterDirc(`replace replace_test set c1 = 5;`) 1266 tk.MustInterDirc(`replace replace_test set c1 = 6;`) 1267 tk.MustInterDirc(`replace replace_test set c1 = 7;`) 1268 1269 tk.MustInterDirc(`drop causet if exists replace_test_1`) 1270 tk.MustInterDirc(`create causet replace_test_1 (id int, c1 int) partition by hash(id) partitions 5;`) 1271 tk.MustInterDirc(`replace replace_test_1 select id, c1 from replace_test;`) 1272 1273 tk.MustInterDirc(`drop causet if exists replace_test_2`) 1274 tk.MustInterDirc(`create causet replace_test_2 (id int, c1 int) partition by hash(id) partitions 6;`) 1275 1276 tk.MustInterDirc(`replace replace_test_1 select id, c1 from replace_test union select id * 10, c1 * 10 from replace_test;`) 1277 1278 errReplaceSelectALLEGROSQL := `replace replace_test_1 select c1 from replace_test;` 1279 tk.MustInterDirc("begin") 1280 _, err = tk.InterDirc(errReplaceSelectALLEGROSQL) 1281 c.Assert(err, NotNil) 1282 tk.MustInterDirc("rollback") 1283 1284 tk.MustInterDirc(`drop causet if exists replace_test_3`) 1285 replaceUniqueIndexALLEGROSQL := `create causet replace_test_3 (c1 int, c2 int, UNIQUE INDEX (c2)) partition by hash(c2) partitions 7;` 1286 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 1287 1288 tk.MustInterDirc(`replace into replace_test_3 set c2=8;`) 1289 tk.MustInterDirc(`replace into replace_test_3 set c2=8;`) 1290 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(1)) 1291 tk.MustInterDirc(`replace into replace_test_3 set c1=8, c2=8;`) 1292 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(2)) 1293 1294 tk.MustInterDirc(`replace into replace_test_3 set c2=NULL;`) 1295 tk.MustInterDirc(`replace into replace_test_3 set c2=NULL;`) 1296 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(1)) 1297 1298 for i := 0; i < 100; i++ { 1299 allegrosql := fmt.Sprintf("replace into replace_test_3 set c2=%d;", i) 1300 tk.MustInterDirc(allegrosql) 1301 } 1302 result := tk.MustQuery("select count(*) from replace_test_3") 1303 result.Check(testkit.Rows("102")) 1304 1305 replaceUniqueIndexALLEGROSQL = `create causet replace_test_4 (c1 int, c2 int, c3 int, UNIQUE INDEX (c1, c2)) partition by hash(c1) partitions 8;` 1306 tk.MustInterDirc(`drop causet if exists replace_test_4`) 1307 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 1308 replaceUniqueIndexALLEGROSQL = `replace into replace_test_4 set c2=NULL;` 1309 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 1310 replaceUniqueIndexALLEGROSQL = `replace into replace_test_4 set c2=NULL;` 1311 tk.MustInterDirc(replaceUniqueIndexALLEGROSQL) 1312 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(1)) 1313 1314 replacePrimaryKeyALLEGROSQL := `create causet replace_test_5 (c1 int, c2 int, c3 int, PRIMARY KEY (c1, c2)) partition by hash (c2) partitions 9;` 1315 tk.MustInterDirc(replacePrimaryKeyALLEGROSQL) 1316 replacePrimaryKeyALLEGROSQL = `replace into replace_test_5 set c1=1, c2=2;` 1317 tk.MustInterDirc(replacePrimaryKeyALLEGROSQL) 1318 replacePrimaryKeyALLEGROSQL = `replace into replace_test_5 set c1=1, c2=2;` 1319 tk.MustInterDirc(replacePrimaryKeyALLEGROSQL) 1320 c.Assert(int64(tk.Se.AffectedRows()), Equals, int64(1)) 1321 1322 issue989ALLEGROSQL := `CREATE TABLE tIssue989 (a int, b int, KEY(a), UNIQUE KEY(b)) partition by hash (b) partitions 10;` 1323 tk.MustInterDirc(issue989ALLEGROSQL) 1324 issue989ALLEGROSQL = `insert into tIssue989 (a, b) values (1, 2);` 1325 tk.MustInterDirc(issue989ALLEGROSQL) 1326 issue989ALLEGROSQL = `replace into tIssue989(a, b) values (111, 2);` 1327 tk.MustInterDirc(issue989ALLEGROSQL) 1328 r := tk.MustQuery("select * from tIssue989;") 1329 r.Check(testkit.Rows("111 2")) 1330 } 1331 1332 func (s *testSuite8) TestUFIDelate(c *C) { 1333 tk := testkit.NewTestKit(c, s.causetstore) 1334 tk.MustInterDirc("use test") 1335 s.fillData(tk, "uFIDelate_test") 1336 1337 uFIDelateStr := `UFIDelATE uFIDelate_test SET name = "abc" where id > 0;` 1338 tk.MustInterDirc(uFIDelateStr) 1339 tk.CheckInterDircResult(2, 0) 1340 tk.CheckLastMessage("Rows matched: 2 Changed: 2 Warnings: 0") 1341 1342 // select data 1343 tk.MustInterDirc("begin") 1344 r := tk.MustQuery(`SELECT * from uFIDelate_test limit 2;`) 1345 r.Check(testkit.Rows("1 abc", "2 abc")) 1346 tk.MustInterDirc("commit") 1347 1348 tk.MustInterDirc(`UFIDelATE uFIDelate_test SET name = "foo"`) 1349 tk.CheckInterDircResult(2, 0) 1350 tk.CheckLastMessage("Rows matched: 2 Changed: 2 Warnings: 0") 1351 1352 // causet option is auto-increment 1353 tk.MustInterDirc("begin") 1354 tk.MustInterDirc("drop causet if exists uFIDelate_test;") 1355 tk.MustInterDirc("commit") 1356 tk.MustInterDirc("begin") 1357 tk.MustInterDirc("create causet uFIDelate_test(id int not null auto_increment, name varchar(255), primary key(id))") 1358 tk.MustInterDirc("insert into uFIDelate_test(name) values ('aa')") 1359 tk.MustInterDirc("uFIDelate uFIDelate_test set id = 8 where name = 'aa'") 1360 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 0") 1361 tk.MustInterDirc("insert into uFIDelate_test(name) values ('bb')") 1362 tk.MustInterDirc("commit") 1363 tk.MustInterDirc("begin") 1364 r = tk.MustQuery("select * from uFIDelate_test;") 1365 r.Check(testkit.Rows("8 aa", "9 bb")) 1366 tk.MustInterDirc("commit") 1367 1368 tk.MustInterDirc("begin") 1369 tk.MustInterDirc("drop causet if exists uFIDelate_test;") 1370 tk.MustInterDirc("commit") 1371 tk.MustInterDirc("begin") 1372 tk.MustInterDirc("create causet uFIDelate_test(id int not null auto_increment, name varchar(255), index(id))") 1373 tk.MustInterDirc("insert into uFIDelate_test(name) values ('aa')") 1374 _, err := tk.InterDirc("uFIDelate uFIDelate_test set id = null where name = 'aa'") 1375 c.Assert(err, NotNil) 1376 c.Assert(err.Error(), DeepEquals, "[causet:1048]DeferredCauset 'id' cannot be null") 1377 1378 tk.MustInterDirc("drop causet uFIDelate_test") 1379 tk.MustInterDirc("create causet uFIDelate_test(id int)") 1380 tk.MustInterDirc("begin") 1381 tk.MustInterDirc("insert into uFIDelate_test(id) values (1)") 1382 tk.MustInterDirc("uFIDelate uFIDelate_test set id = 2 where id = 1 limit 1") 1383 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 0") 1384 r = tk.MustQuery("select * from uFIDelate_test;") 1385 r.Check(testkit.Rows("2")) 1386 tk.MustInterDirc("commit") 1387 1388 // Test that in a transaction, when a constraint failed in an uFIDelate memex, the record is not inserted. 1389 tk.MustInterDirc("create causet uFIDelate_unique (id int primary key, name int unique)") 1390 tk.MustInterDirc("insert uFIDelate_unique values (1, 1), (2, 2);") 1391 tk.MustInterDirc("begin") 1392 _, err = tk.InterDirc("uFIDelate uFIDelate_unique set name = 1 where id = 2") 1393 c.Assert(err, NotNil) 1394 tk.MustInterDirc("commit") 1395 tk.MustQuery("select * from uFIDelate_unique").Check(testkit.Rows("1 1", "2 2")) 1396 1397 // test uFIDelate ignore for pimary key 1398 tk.MustInterDirc("drop causet if exists t;") 1399 tk.MustInterDirc("create causet t(a bigint, primary key (a));") 1400 tk.MustInterDirc("insert into t values (1)") 1401 tk.MustInterDirc("insert into t values (2)") 1402 _, err = tk.InterDirc("uFIDelate ignore t set a = 1 where a = 2;") 1403 c.Assert(err, IsNil) 1404 tk.CheckLastMessage("Rows matched: 1 Changed: 0 Warnings: 1") 1405 r = tk.MustQuery("SHOW WARNINGS;") 1406 r.Check(testkit.Rows("Warning 1062 Duplicate entry '1' for key 'PRIMARY'")) 1407 tk.MustQuery("select * from t").Check(testkit.Rows("1", "2")) 1408 1409 // test uFIDelate ignore for truncate as warning 1410 _, err = tk.InterDirc("uFIDelate ignore t set a = 1 where a = (select '2a')") 1411 c.Assert(err, IsNil) 1412 r = tk.MustQuery("SHOW WARNINGS;") 1413 r.Check(testkit.Rows("Warning 1292 Truncated incorrect FLOAT value: '2a'", "Warning 1292 Truncated incorrect FLOAT value: '2a'", "Warning 1062 Duplicate entry '1' for key 'PRIMARY'")) 1414 1415 tk.MustInterDirc("uFIDelate ignore t set a = 42 where a = 2;") 1416 tk.MustQuery("select * from t").Check(testkit.Rows("1", "42")) 1417 1418 // test uFIDelate ignore for unique key 1419 tk.MustInterDirc("drop causet if exists t;") 1420 tk.MustInterDirc("create causet t(a bigint, unique key I_uniq (a));") 1421 tk.MustInterDirc("insert into t values (1)") 1422 tk.MustInterDirc("insert into t values (2)") 1423 _, err = tk.InterDirc("uFIDelate ignore t set a = 1 where a = 2;") 1424 c.Assert(err, IsNil) 1425 tk.CheckLastMessage("Rows matched: 1 Changed: 0 Warnings: 1") 1426 r = tk.MustQuery("SHOW WARNINGS;") 1427 r.Check(testkit.Rows("Warning 1062 Duplicate entry '1' for key 'I_uniq'")) 1428 tk.MustQuery("select * from t").Check(testkit.Rows("1", "2")) 1429 1430 tk.MustInterDirc("drop causet if exists t") 1431 tk.MustInterDirc("create causet t(id integer auto_increment, t1 datetime, t2 datetime, primary key (id))") 1432 tk.MustInterDirc("insert into t(t1, t2) values('2000-10-01 01:01:01', '2020-01-01 10:10:10')") 1433 tk.MustQuery("select * from t").Check(testkit.Rows("1 2000-10-01 01:01:01 2020-01-01 10:10:10")) 1434 tk.MustInterDirc("uFIDelate t set t1 = '2020-10-01 10:10:11', t2 = date_add(t1, INTERVAL 10 MINUTE) where id = 1") 1435 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 0") 1436 tk.MustQuery("select * from t").Check(testkit.Rows("1 2020-10-01 10:10:11 2020-10-01 10:20:11")) 1437 1438 // for issue #5132 1439 tk.MustInterDirc("CREATE TABLE `tt1` (" + 1440 "`a` int(11) NOT NULL," + 1441 "`b` varchar(32) DEFAULT NULL," + 1442 "`c` varchar(32) DEFAULT NULL," + 1443 "PRIMARY KEY (`a`)," + 1444 "UNIQUE KEY `b_idx` (`b`)" + 1445 ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;") 1446 tk.MustInterDirc("insert into tt1 values(1, 'a', 'a');") 1447 tk.MustInterDirc("insert into tt1 values(2, 'd', 'b');") 1448 r = tk.MustQuery("select * from tt1;") 1449 r.Check(testkit.Rows("1 a a", "2 d b")) 1450 tk.MustInterDirc("uFIDelate tt1 set a=5 where c='b';") 1451 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 0") 1452 r = tk.MustQuery("select * from tt1;") 1453 r.Check(testkit.Rows("1 a a", "5 d b")) 1454 1455 // Automatic UFIDelating for TIMESTAMP 1456 tk.MustInterDirc("CREATE TABLE `tsup` (" + 1457 "`a` int," + 1458 "`ts` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UFIDelATE CURRENT_TIMESTAMP," + 1459 "KEY `idx` (`ts`)" + 1460 ");") 1461 tk.MustInterDirc("insert into tsup values(1, '0000-00-00 00:00:00');") 1462 tk.MustInterDirc("uFIDelate tsup set a=5;") 1463 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 0") 1464 r1 := tk.MustQuery("select ts from tsup use index (idx);") 1465 r2 := tk.MustQuery("select ts from tsup;") 1466 r1.Check(r2.Rows()) 1467 tk.MustInterDirc("uFIDelate tsup set ts='2020-01-01';") 1468 tk.MustQuery("select ts from tsup;").Check(testkit.Rows("2020-01-01 00:00:00")) 1469 1470 // issue 5532 1471 tk.MustInterDirc("create causet decimals (a decimal(20, 0) not null)") 1472 tk.MustInterDirc("insert into decimals values (201)") 1473 // A warning rather than data truncated error. 1474 tk.MustInterDirc("uFIDelate decimals set a = a + 1.23;") 1475 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 1") 1476 tk.MustQuery("show warnings").Check(testkit.Rows("Warning 1292 Truncated incorrect DECIMAL value: '202.23'")) 1477 r = tk.MustQuery("select * from decimals") 1478 r.Check(testkit.Rows("202")) 1479 1480 tk.MustInterDirc("drop causet t") 1481 tk.MustInterDirc("CREATE TABLE `t` ( `c1` year DEFAULT NULL, `c2` year DEFAULT NULL, `c3` date DEFAULT NULL, `c4` datetime DEFAULT NULL, KEY `idx` (`c1`,`c2`))") 1482 _, err = tk.InterDirc("UFIDelATE t SET c2=16777215 WHERE c1>= -8388608 AND c1 < -9 ORDER BY c1 LIMIT 2") 1483 c.Assert(err.Error(), Equals, "cannot convert causet from bigint to type year.") 1484 1485 tk.MustInterDirc("uFIDelate (select * from t) t set c1 = 1111111") 1486 1487 // test uFIDelate ignore for bad null error 1488 tk.MustInterDirc("drop causet if exists t;") 1489 tk.MustInterDirc(`create causet t (i int not null default 10)`) 1490 tk.MustInterDirc("insert into t values (1)") 1491 tk.MustInterDirc("uFIDelate ignore t set i = null;") 1492 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 1") 1493 r = tk.MustQuery("SHOW WARNINGS;") 1494 r.Check(testkit.Rows("Warning 1048 DeferredCauset 'i' cannot be null")) 1495 tk.MustQuery("select * from t").Check(testkit.Rows("0")) 1496 1497 // issue 7237, uFIDelate subquery causet should be forbidden 1498 tk.MustInterDirc("drop causet t") 1499 tk.MustInterDirc("create causet t (k int, v int)") 1500 _, err = tk.InterDirc("uFIDelate t, (select * from t) as b set b.k = t.k") 1501 c.Assert(err.Error(), Equals, "[causet:1288]The target causet b of the UFIDelATE is not uFIDelablock") 1502 tk.MustInterDirc("uFIDelate t, (select * from t) as b set t.k = b.k") 1503 1504 // issue 8045 1505 tk.MustInterDirc("drop causet if exists t1") 1506 tk.MustInterDirc(`CREATE TABLE t1 (c1 float)`) 1507 tk.MustInterDirc("INSERT INTO t1 SET c1 = 1") 1508 tk.MustInterDirc("UFIDelATE t1 SET c1 = 1.2 WHERE c1=1;") 1509 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 0") 1510 1511 // issue 8119 1512 tk.MustInterDirc("drop causet if exists t;") 1513 tk.MustInterDirc("create causet t (c1 float(1,1));") 1514 tk.MustInterDirc("insert into t values (0.0);") 1515 _, err = tk.InterDirc("uFIDelate t set c1 = 2.0;") 1516 c.Assert(types.ErrWarnDataOutOfRange.Equal(err), IsTrue) 1517 1518 tk.MustInterDirc("drop causet if exists t") 1519 tk.MustInterDirc("create causet t(a datetime not null, b datetime)") 1520 tk.MustInterDirc("insert into t value('1999-12-12', '1999-12-13')") 1521 tk.MustInterDirc(" set @orig_sql_mode=@@sql_mode; set @@sql_mode='';") 1522 tk.MustQuery("select * from t").Check(testkit.Rows("1999-12-12 00:00:00 1999-12-13 00:00:00")) 1523 tk.MustInterDirc("uFIDelate t set a = ''") 1524 tk.MustQuery("select * from t").Check(testkit.Rows("0000-00-00 00:00:00 1999-12-13 00:00:00")) 1525 tk.MustInterDirc("uFIDelate t set b = ''") 1526 tk.MustQuery("select * from t").Check(testkit.Rows("0000-00-00 00:00:00 0000-00-00 00:00:00")) 1527 tk.MustInterDirc("set @@sql_mode=@orig_sql_mode;") 1528 1529 tk.MustInterDirc("create view v as select * from t") 1530 _, err = tk.InterDirc("uFIDelate v set a = '2000-11-11'") 1531 c.Assert(err.Error(), Equals, embedded.ErrViewInvalid.GenWithStackByArgs("test", "v").Error()) 1532 tk.MustInterDirc("drop view v") 1533 1534 tk.MustInterDirc("create sequence seq") 1535 _, err = tk.InterDirc("uFIDelate seq set minvalue=1") 1536 c.Assert(err.Error(), Equals, "uFIDelate sequence seq is not supported now.") 1537 tk.MustInterDirc("drop sequence seq") 1538 1539 tk.MustInterDirc("drop causet if exists t1, t2") 1540 tk.MustInterDirc("create causet t1(a int, b int, c int, d int, e int, index idx(a))") 1541 tk.MustInterDirc("create causet t2(a int, b int, c int)") 1542 tk.MustInterDirc("uFIDelate t1 join t2 on t1.a=t2.a set t1.a=1 where t2.b=1 and t2.c=2") 1543 1544 // Assign `DEFAULT` in `UFIDelATE` memex 1545 tk.MustInterDirc("drop causet if exists t1, t2;") 1546 tk.MustInterDirc("create causet t1 (a int default 1, b int default 2);") 1547 tk.MustInterDirc("insert into t1 values (10, 10), (20, 20);") 1548 tk.MustInterDirc("uFIDelate t1 set a=default where b=10;") 1549 tk.MustQuery("select * from t1;").Check(testkit.Rows("1 10", "20 20")) 1550 tk.MustInterDirc("uFIDelate t1 set a=30, b=default where a=20;") 1551 tk.MustQuery("select * from t1;").Check(testkit.Rows("1 10", "30 2")) 1552 tk.MustInterDirc("uFIDelate t1 set a=default, b=default where a=30;") 1553 tk.MustQuery("select * from t1;").Check(testkit.Rows("1 10", "1 2")) 1554 tk.MustInterDirc("insert into t1 values (40, 40)") 1555 tk.MustInterDirc("uFIDelate t1 set a=default, b=default") 1556 tk.MustQuery("select * from t1;").Check(testkit.Rows("1 2", "1 2", "1 2")) 1557 tk.MustInterDirc("uFIDelate t1 set a=default(b), b=default(a)") 1558 tk.MustQuery("select * from t1;").Check(testkit.Rows("2 1", "2 1", "2 1")) 1559 // With generated defCausumns 1560 tk.MustInterDirc("create causet t2 (a int default 1, b int generated always as (-a) virtual, c int generated always as (-a) stored);") 1561 tk.MustInterDirc("insert into t2 values (10, default, default), (20, default, default)") 1562 tk.MustInterDirc("uFIDelate t2 set b=default;") 1563 tk.MustQuery("select * from t2;").Check(testkit.Rows("10 -10 -10", "20 -20 -20")) 1564 tk.MustInterDirc("uFIDelate t2 set a=30, b=default where a=10;") 1565 tk.MustQuery("select * from t2;").Check(testkit.Rows("30 -30 -30", "20 -20 -20")) 1566 tk.MustInterDirc("uFIDelate t2 set c=default, a=40 where c=-20;") 1567 tk.MustQuery("select * from t2;").Check(testkit.Rows("30 -30 -30", "40 -40 -40")) 1568 tk.MustInterDirc("uFIDelate t2 set a=default, b=default, c=default where b=-30;") 1569 tk.MustQuery("select * from t2;").Check(testkit.Rows("1 -1 -1", "40 -40 -40")) 1570 tk.MustInterDirc("uFIDelate t2 set a=default(a), b=default, c=default;") 1571 tk.MustQuery("select * from t2;").Check(testkit.Rows("1 -1 -1", "1 -1 -1")) 1572 tk.MustGetErrCode("uFIDelate t2 set b=default(a);", allegrosql.ErrBadGeneratedDeferredCauset) 1573 tk.MustGetErrCode("uFIDelate t2 set a=default(b), b=default(b);", allegrosql.ErrBadGeneratedDeferredCauset) 1574 tk.MustGetErrCode("uFIDelate t2 set a=default(a), c=default(c);", allegrosql.ErrBadGeneratedDeferredCauset) 1575 tk.MustGetErrCode("uFIDelate t2 set a=default(a), c=default(a);", allegrosql.ErrBadGeneratedDeferredCauset) 1576 tk.MustInterDirc("drop causet t1, t2") 1577 } 1578 1579 func (s *testSuite4) TestPartitionedBlockUFIDelate(c *C) { 1580 tk := testkit.NewTestKit(c, s.causetstore) 1581 tk.MustInterDirc("use test") 1582 tk.MustInterDirc("drop causet if exists t") 1583 tk.MustInterDirc(`create causet t (id int not null default 1, name varchar(255)) 1584 PARTITION BY RANGE ( id ) ( 1585 PARTITION p0 VALUES LESS THAN (6), 1586 PARTITION p1 VALUES LESS THAN (11), 1587 PARTITION p2 VALUES LESS THAN (16), 1588 PARTITION p3 VALUES LESS THAN (21))`) 1589 1590 tk.MustInterDirc(`insert INTO t VALUES (1, "hello");`) 1591 tk.CheckInterDircResult(1, 0) 1592 tk.MustInterDirc(`insert INTO t VALUES (7, "hello");`) 1593 tk.CheckInterDircResult(1, 0) 1594 1595 // uFIDelate non partition defCausumn 1596 tk.MustInterDirc(`UFIDelATE t SET name = "abc" where id > 0;`) 1597 tk.CheckInterDircResult(2, 0) 1598 tk.CheckLastMessage("Rows matched: 2 Changed: 2 Warnings: 0") 1599 r := tk.MustQuery(`SELECT * from t order by id limit 2;`) 1600 r.Check(testkit.Rows("1 abc", "7 abc")) 1601 1602 // uFIDelate partition defCausumn 1603 tk.MustInterDirc(`uFIDelate t set id = id + 1`) 1604 tk.CheckInterDircResult(2, 0) 1605 tk.CheckLastMessage("Rows matched: 2 Changed: 2 Warnings: 0") 1606 r = tk.MustQuery(`SELECT * from t order by id limit 2;`) 1607 r.Check(testkit.Rows("2 abc", "8 abc")) 1608 1609 // uFIDelate partition defCausumn, old and new record locates on different partitions 1610 tk.MustInterDirc(`uFIDelate t set id = 20 where id = 8`) 1611 tk.CheckInterDircResult(2, 0) 1612 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 0") 1613 r = tk.MustQuery(`SELECT * from t order by id limit 2;`) 1614 r.Check(testkit.Rows("2 abc", "20 abc")) 1615 1616 // causet option is auto-increment 1617 tk.MustInterDirc("drop causet if exists t;") 1618 tk.MustInterDirc(`create causet t (id int not null auto_increment, name varchar(255), primary key(id)) 1619 PARTITION BY RANGE ( id ) ( 1620 PARTITION p0 VALUES LESS THAN (6), 1621 PARTITION p1 VALUES LESS THAN (11), 1622 PARTITION p2 VALUES LESS THAN (16), 1623 PARTITION p3 VALUES LESS THAN (21))`) 1624 1625 tk.MustInterDirc("insert into t(name) values ('aa')") 1626 tk.MustInterDirc("uFIDelate t set id = 8 where name = 'aa'") 1627 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 0") 1628 tk.MustInterDirc("insert into t(name) values ('bb')") 1629 r = tk.MustQuery("select * from t;") 1630 r.Check(testkit.Rows("8 aa", "9 bb")) 1631 1632 _, err := tk.InterDirc("uFIDelate t set id = null where name = 'aa'") 1633 c.Assert(err, NotNil) 1634 c.Assert(err.Error(), DeepEquals, "[causet:1048]DeferredCauset 'id' cannot be null") 1635 1636 // Test that in a transaction, when a constraint failed in an uFIDelate memex, the record is not inserted. 1637 tk.MustInterDirc("drop causet if exists t;") 1638 tk.MustInterDirc(`create causet t (id int, name int unique) 1639 PARTITION BY RANGE ( name ) ( 1640 PARTITION p0 VALUES LESS THAN (6), 1641 PARTITION p1 VALUES LESS THAN (11), 1642 PARTITION p2 VALUES LESS THAN (16), 1643 PARTITION p3 VALUES LESS THAN (21))`) 1644 tk.MustInterDirc("insert t values (1, 1), (2, 2);") 1645 _, err = tk.InterDirc("uFIDelate t set name = 1 where id = 2") 1646 c.Assert(err, NotNil) 1647 tk.MustQuery("select * from t").Check(testkit.Rows("1 1", "2 2")) 1648 1649 // test uFIDelate ignore for pimary key 1650 tk.MustInterDirc("drop causet if exists t;") 1651 tk.MustInterDirc(`create causet t(a bigint, primary key (a)) 1652 PARTITION BY RANGE (a) ( 1653 PARTITION p0 VALUES LESS THAN (6), 1654 PARTITION p1 VALUES LESS THAN (11))`) 1655 tk.MustInterDirc("insert into t values (5)") 1656 tk.MustInterDirc("insert into t values (7)") 1657 _, err = tk.InterDirc("uFIDelate ignore t set a = 5 where a = 7;") 1658 c.Assert(err, IsNil) 1659 tk.CheckLastMessage("Rows matched: 1 Changed: 0 Warnings: 1") 1660 r = tk.MustQuery("SHOW WARNINGS;") 1661 r.Check(testkit.Rows("Warning 1062 Duplicate entry '5' for key 'PRIMARY'")) 1662 tk.MustQuery("select * from t order by a").Check(testkit.Rows("5", "7")) 1663 1664 // test uFIDelate ignore for truncate as warning 1665 _, err = tk.InterDirc("uFIDelate ignore t set a = 1 where a = (select '2a')") 1666 c.Assert(err, IsNil) 1667 r = tk.MustQuery("SHOW WARNINGS;") 1668 r.Check(testkit.Rows("Warning 1292 Truncated incorrect FLOAT value: '2a'", "Warning 1292 Truncated incorrect FLOAT value: '2a'")) 1669 1670 // test uFIDelate ignore for unique key 1671 tk.MustInterDirc("drop causet if exists t;") 1672 tk.MustInterDirc(`create causet t(a bigint, unique key I_uniq (a)) 1673 PARTITION BY RANGE (a) ( 1674 PARTITION p0 VALUES LESS THAN (6), 1675 PARTITION p1 VALUES LESS THAN (11))`) 1676 tk.MustInterDirc("insert into t values (5)") 1677 tk.MustInterDirc("insert into t values (7)") 1678 _, err = tk.InterDirc("uFIDelate ignore t set a = 5 where a = 7;") 1679 c.Assert(err, IsNil) 1680 tk.CheckLastMessage("Rows matched: 1 Changed: 0 Warnings: 1") 1681 r = tk.MustQuery("SHOW WARNINGS;") 1682 r.Check(testkit.Rows("Warning 1062 Duplicate entry '5' for key 'I_uniq'")) 1683 tk.MustQuery("select * from t order by a").Check(testkit.Rows("5", "7")) 1684 } 1685 1686 // TestUFIDelateCastOnlyModifiedValues for issue #4514. 1687 func (s *testSuite4) TestUFIDelateCastOnlyModifiedValues(c *C) { 1688 tk := testkit.NewTestKit(c, s.causetstore) 1689 tk.MustInterDirc("use test") 1690 tk.MustInterDirc("create causet uFIDelate_modified (defCaus_1 int, defCaus_2 enum('a', 'b'))") 1691 tk.MustInterDirc("set ALLEGROSQL_MODE=''") 1692 tk.MustInterDirc("insert into uFIDelate_modified values (0, 3)") 1693 r := tk.MustQuery("SELECT * FROM uFIDelate_modified") 1694 r.Check(testkit.Rows("0 ")) 1695 tk.MustInterDirc("set ALLEGROSQL_MODE=STRICT_ALL_TABLES") 1696 tk.MustInterDirc("uFIDelate uFIDelate_modified set defCaus_1 = 1") 1697 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 0") 1698 r = tk.MustQuery("SELECT * FROM uFIDelate_modified") 1699 r.Check(testkit.Rows("1 ")) 1700 _, err := tk.InterDirc("uFIDelate uFIDelate_modified set defCaus_1 = 2, defCaus_2 = 'c'") 1701 c.Assert(err, NotNil) 1702 r = tk.MustQuery("SELECT * FROM uFIDelate_modified") 1703 r.Check(testkit.Rows("1 ")) 1704 tk.MustInterDirc("uFIDelate uFIDelate_modified set defCaus_1 = 3, defCaus_2 = 'a'") 1705 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 0") 1706 r = tk.MustQuery("SELECT * FROM uFIDelate_modified") 1707 r.Check(testkit.Rows("3 a")) 1708 1709 // Test uFIDelate a field with different defCausumn type. 1710 tk.MustInterDirc(`CREATE TABLE uFIDelate_with_diff_type (a int, b JSON)`) 1711 tk.MustInterDirc(`INSERT INTO uFIDelate_with_diff_type VALUES(3, '{"a": "测试"}')`) 1712 tk.MustInterDirc(`UFIDelATE uFIDelate_with_diff_type SET a = '300'`) 1713 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 0") 1714 r = tk.MustQuery("SELECT a FROM uFIDelate_with_diff_type") 1715 r.Check(testkit.Rows("300")) 1716 tk.MustInterDirc(`UFIDelATE uFIDelate_with_diff_type SET b = '{"a": "\\u6d4b\\u8bd5"}'`) 1717 tk.CheckLastMessage("Rows matched: 1 Changed: 0 Warnings: 0") 1718 r = tk.MustQuery("SELECT b FROM uFIDelate_with_diff_type") 1719 r.Check(testkit.Rows(`{"a": "测试"}`)) 1720 } 1721 1722 func (s *testSuite4) fillMultiBlockForUFIDelate(tk *testkit.TestKit) { 1723 // Create and fill causet items 1724 tk.MustInterDirc("CREATE TABLE items (id int, price TEXT);") 1725 tk.MustInterDirc(`insert into items values (11, "items_price_11"), (12, "items_price_12"), (13, "items_price_13");`) 1726 tk.CheckInterDircResult(3, 0) 1727 // Create and fill causet month 1728 tk.MustInterDirc("CREATE TABLE month (mid int, mprice TEXT);") 1729 tk.MustInterDirc(`insert into month values (11, "month_price_11"), (22, "month_price_22"), (13, "month_price_13");`) 1730 tk.CheckInterDircResult(3, 0) 1731 } 1732 1733 func (s *testSuite4) TestMultipleBlockUFIDelate(c *C) { 1734 tk := testkit.NewTestKit(c, s.causetstore) 1735 tk.MustInterDirc("use test") 1736 s.fillMultiBlockForUFIDelate(tk) 1737 1738 tk.MustInterDirc(`UFIDelATE items, month SET items.price=month.mprice WHERE items.id=month.mid;`) 1739 tk.CheckLastMessage("Rows matched: 2 Changed: 2 Warnings: 0") 1740 tk.MustInterDirc("begin") 1741 r := tk.MustQuery("SELECT * FROM items") 1742 r.Check(testkit.Rows("11 month_price_11", "12 items_price_12", "13 month_price_13")) 1743 tk.MustInterDirc("commit") 1744 1745 // Single-causet syntax but with multiple blocks 1746 tk.MustInterDirc(`UFIDelATE items join month on items.id=month.mid SET items.price=month.mid;`) 1747 tk.CheckLastMessage("Rows matched: 2 Changed: 2 Warnings: 0") 1748 tk.MustInterDirc("begin") 1749 r = tk.MustQuery("SELECT * FROM items") 1750 r.Check(testkit.Rows("11 11", "12 items_price_12", "13 13")) 1751 tk.MustInterDirc("commit") 1752 1753 // JoinBlock with alias causet name. 1754 tk.MustInterDirc(`UFIDelATE items T0 join month T1 on T0.id=T1.mid SET T0.price=T1.mprice;`) 1755 tk.CheckLastMessage("Rows matched: 2 Changed: 2 Warnings: 0") 1756 tk.MustInterDirc("begin") 1757 r = tk.MustQuery("SELECT * FROM items") 1758 r.Check(testkit.Rows("11 month_price_11", "12 items_price_12", "13 month_price_13")) 1759 tk.MustInterDirc("commit") 1760 1761 // fix https://github.com/whtcorpsinc/milevadb/issues/369 1762 testALLEGROSQL := ` 1763 DROP TABLE IF EXISTS t1, t2; 1764 create causet t1 (c int); 1765 create causet t2 (c varchar(256)); 1766 insert into t1 values (1), (2); 1767 insert into t2 values ("a"), ("b"); 1768 uFIDelate t1, t2 set t1.c = 10, t2.c = "abc";` 1769 tk.MustInterDirc(testALLEGROSQL) 1770 tk.CheckLastMessage("Rows matched: 4 Changed: 4 Warnings: 0") 1771 1772 // fix https://github.com/whtcorpsinc/milevadb/issues/376 1773 testALLEGROSQL = `DROP TABLE IF EXISTS t1, t2; 1774 create causet t1 (c1 int); 1775 create causet t2 (c2 int); 1776 insert into t1 values (1), (2); 1777 insert into t2 values (1), (2); 1778 uFIDelate t1, t2 set t1.c1 = 10, t2.c2 = 2 where t2.c2 = 1;` 1779 tk.MustInterDirc(testALLEGROSQL) 1780 tk.CheckLastMessage("Rows matched: 3 Changed: 3 Warnings: 0") 1781 1782 r = tk.MustQuery("select * from t1") 1783 r.Check(testkit.Rows("10", "10")) 1784 1785 // test https://github.com/whtcorpsinc/milevadb/issues/3604 1786 tk.MustInterDirc("drop causet if exists t, t") 1787 tk.MustInterDirc("create causet t (a int, b int)") 1788 tk.MustInterDirc("insert into t values(1, 1), (2, 2), (3, 3)") 1789 tk.CheckLastMessage("Records: 3 Duplicates: 0 Warnings: 0") 1790 tk.MustInterDirc("uFIDelate t m, t n set m.a = m.a + 1") 1791 tk.CheckLastMessage("Rows matched: 3 Changed: 3 Warnings: 0") 1792 tk.MustQuery("select * from t").Check(testkit.Rows("2 1", "3 2", "4 3")) 1793 tk.MustInterDirc("uFIDelate t m, t n set n.a = n.a - 1, n.b = n.b + 1") 1794 tk.CheckLastMessage("Rows matched: 3 Changed: 3 Warnings: 0") 1795 tk.MustQuery("select * from t").Check(testkit.Rows("1 2", "2 3", "3 4")) 1796 } 1797 1798 func (s *testSuite) TestDelete(c *C) { 1799 tk := testkit.NewTestKit(c, s.causetstore) 1800 s.fillData(tk, "delete_test") 1801 1802 tk.MustInterDirc(`uFIDelate delete_test set name = "abc" where id = 2;`) 1803 tk.CheckInterDircResult(1, 0) 1804 1805 tk.MustInterDirc(`delete from delete_test where id = 2 limit 1;`) 1806 tk.CheckInterDircResult(1, 0) 1807 1808 // Test delete with false condition 1809 tk.MustInterDirc(`delete from delete_test where 0;`) 1810 tk.CheckInterDircResult(0, 0) 1811 1812 tk.MustInterDirc("insert into delete_test values (2, 'abc')") 1813 tk.MustInterDirc(`delete from delete_test where delete_test.id = 2 limit 1`) 1814 tk.CheckInterDircResult(1, 0) 1815 1816 // Select data 1817 tk.MustInterDirc("begin") 1818 rows := tk.MustQuery(`SELECT * from delete_test limit 2;`) 1819 rows.Check(testkit.Rows("1 hello")) 1820 tk.MustInterDirc("commit") 1821 1822 // Test delete ignore 1823 tk.MustInterDirc("insert into delete_test values (2, 'abc')") 1824 _, err := tk.InterDirc("delete from delete_test where id = (select '2a')") 1825 c.Assert(err, NotNil) 1826 _, err = tk.InterDirc("delete ignore from delete_test where id = (select '2a')") 1827 c.Assert(err, IsNil) 1828 tk.CheckInterDircResult(1, 0) 1829 r := tk.MustQuery("SHOW WARNINGS;") 1830 r.Check(testkit.Rows("Warning 1292 Truncated incorrect FLOAT value: '2a'", "Warning 1292 Truncated incorrect FLOAT value: '2a'")) 1831 1832 tk.MustInterDirc(`delete from delete_test ;`) 1833 tk.CheckInterDircResult(1, 0) 1834 1835 tk.MustInterDirc("create view v as select * from delete_test") 1836 _, err = tk.InterDirc("delete from v where name = 'aaa'") 1837 c.Assert(err.Error(), Equals, embedded.ErrViewInvalid.GenWithStackByArgs("test", "v").Error()) 1838 tk.MustInterDirc("drop view v") 1839 1840 tk.MustInterDirc("create sequence seq") 1841 _, err = tk.InterDirc("delete from seq") 1842 c.Assert(err.Error(), Equals, "delete sequence seq is not supported now.") 1843 tk.MustInterDirc("drop sequence seq") 1844 } 1845 1846 func (s *testSuite4) TestPartitionedBlockDelete(c *C) { 1847 createBlock := `CREATE TABLE test.t (id int not null default 1, name varchar(255), index(id)) 1848 PARTITION BY RANGE ( id ) ( 1849 PARTITION p0 VALUES LESS THAN (6), 1850 PARTITION p1 VALUES LESS THAN (11), 1851 PARTITION p2 VALUES LESS THAN (16), 1852 PARTITION p3 VALUES LESS THAN (21))` 1853 1854 tk := testkit.NewTestKit(c, s.causetstore) 1855 tk.MustInterDirc("use test") 1856 tk.MustInterDirc("drop causet if exists t") 1857 tk.MustInterDirc(createBlock) 1858 for i := 1; i < 21; i++ { 1859 tk.MustInterDirc(fmt.Sprintf(`insert into t values (%d, "hello")`, i)) 1860 } 1861 1862 tk.MustInterDirc(`delete from t where id = 2 limit 1;`) 1863 tk.CheckInterDircResult(1, 0) 1864 1865 // Test delete with false condition 1866 tk.MustInterDirc(`delete from t where 0;`) 1867 tk.CheckInterDircResult(0, 0) 1868 1869 tk.MustInterDirc("insert into t values (2, 'abc')") 1870 tk.MustInterDirc(`delete from t where t.id = 2 limit 1`) 1871 tk.CheckInterDircResult(1, 0) 1872 1873 // Test delete ignore 1874 tk.MustInterDirc("insert into t values (2, 'abc')") 1875 _, err := tk.InterDirc("delete from t where id = (select '2a')") 1876 c.Assert(err, NotNil) 1877 _, err = tk.InterDirc("delete ignore from t where id = (select '2a')") 1878 c.Assert(err, IsNil) 1879 tk.CheckInterDircResult(1, 0) 1880 r := tk.MustQuery("SHOW WARNINGS;") 1881 r.Check(testkit.Rows("Warning 1292 Truncated incorrect FLOAT value: '2a'", "Warning 1292 Truncated incorrect FLOAT value: '2a'")) 1882 1883 // Test delete without using index, involve multiple partitions. 1884 tk.MustInterDirc("delete from t ignore index(id) where id >= 13 and id <= 17") 1885 tk.CheckInterDircResult(5, 0) 1886 1887 tk.MustInterDirc("admin check causet t") 1888 tk.MustInterDirc(`delete from t;`) 1889 tk.CheckInterDircResult(14, 0) 1890 1891 // Fix that partitioned causet should not use PointGetCauset. 1892 tk.MustInterDirc(`create causet t1 (c1 bigint, c2 bigint, c3 bigint, primary key(c1)) partition by range (c1) (partition p0 values less than (3440))`) 1893 tk.MustInterDirc("insert into t1 values (379, 379, 379)") 1894 tk.MustInterDirc("delete from t1 where c1 = 379") 1895 tk.CheckInterDircResult(1, 0) 1896 tk.MustInterDirc(`drop causet t1;`) 1897 } 1898 1899 func (s *testSuite4) fillDataMultiBlock(tk *testkit.TestKit) { 1900 tk.MustInterDirc("use test") 1901 tk.MustInterDirc("drop causet if exists t1, t2, t3") 1902 // Create and fill causet t1 1903 tk.MustInterDirc("create causet t1 (id int, data int);") 1904 tk.MustInterDirc("insert into t1 values (11, 121), (12, 122), (13, 123);") 1905 tk.CheckInterDircResult(3, 0) 1906 // Create and fill causet t2 1907 tk.MustInterDirc("create causet t2 (id int, data int);") 1908 tk.MustInterDirc("insert into t2 values (11, 221), (22, 222), (23, 223);") 1909 tk.CheckInterDircResult(3, 0) 1910 // Create and fill causet t3 1911 tk.MustInterDirc("create causet t3 (id int, data int);") 1912 tk.MustInterDirc("insert into t3 values (11, 321), (22, 322), (23, 323);") 1913 tk.CheckInterDircResult(3, 0) 1914 } 1915 1916 func (s *testSuite4) TestMultiBlockDelete(c *C) { 1917 tk := testkit.NewTestKit(c, s.causetstore) 1918 s.fillDataMultiBlock(tk) 1919 1920 tk.MustInterDirc(`delete t1, t2 from t1 inner join t2 inner join t3 where t1.id=t2.id and t2.id=t3.id;`) 1921 tk.CheckInterDircResult(2, 0) 1922 1923 // Select data 1924 r := tk.MustQuery("select * from t3") 1925 c.Assert(r.Rows(), HasLen, 3) 1926 } 1927 1928 func (s *testSuite4) TestQualifiedDelete(c *C) { 1929 tk := testkit.NewTestKit(c, s.causetstore) 1930 tk.MustInterDirc("use test") 1931 tk.MustInterDirc("drop causet if exists t1") 1932 tk.MustInterDirc("drop causet if exists t2") 1933 tk.MustInterDirc("create causet t1 (c1 int, c2 int, index (c1))") 1934 tk.MustInterDirc("create causet t2 (c1 int, c2 int)") 1935 tk.MustInterDirc("insert into t1 values (1, 1), (2, 2)") 1936 1937 // delete with index 1938 tk.MustInterDirc("delete from t1 where t1.c1 = 1") 1939 tk.CheckInterDircResult(1, 0) 1940 1941 // delete with no index 1942 tk.MustInterDirc("delete from t1 where t1.c2 = 2") 1943 tk.CheckInterDircResult(1, 0) 1944 1945 r := tk.MustQuery("select * from t1") 1946 c.Assert(r.Rows(), HasLen, 0) 1947 1948 tk.MustInterDirc("insert into t1 values (1, 3)") 1949 tk.MustInterDirc("delete from t1 as a where a.c1 = 1") 1950 tk.CheckInterDircResult(1, 0) 1951 1952 tk.MustInterDirc("insert into t1 values (1, 1), (2, 2)") 1953 tk.MustInterDirc("insert into t2 values (2, 1), (3,1)") 1954 tk.MustInterDirc("delete t1, t2 from t1 join t2 where t1.c1 = t2.c2") 1955 tk.CheckInterDircResult(3, 0) 1956 1957 tk.MustInterDirc("insert into t2 values (2, 1), (3,1)") 1958 tk.MustInterDirc("delete a, b from t1 as a join t2 as b where a.c2 = b.c1") 1959 tk.CheckInterDircResult(2, 0) 1960 1961 _, err := tk.InterDirc("delete t1, t2 from t1 as a join t2 as b where a.c2 = b.c1") 1962 c.Assert(err, NotNil) 1963 } 1964 1965 func (s *testSuite8) TestLoadDataMissingDeferredCauset(c *C) { 1966 tk := testkit.NewTestKit(c, s.causetstore) 1967 tk.MustInterDirc("use test") 1968 createALLEGROSQL := `create causet load_data_missing (id int, t timestamp not null)` 1969 tk.MustInterDirc(createALLEGROSQL) 1970 tk.MustInterDirc("load data local infile '/tmp/nonexistence.csv' ignore into causet load_data_missing") 1971 ctx := tk.Se.(stochastikctx.Context) 1972 ld, ok := ctx.Value(interlock.LoadDataVarKey).(*interlock.LoadDataInfo) 1973 c.Assert(ok, IsTrue) 1974 defer ctx.SetValue(interlock.LoadDataVarKey, nil) 1975 c.Assert(ld, NotNil) 1976 1977 deleteALLEGROSQL := "delete from load_data_missing" 1978 selectALLEGROSQL := "select id, hour(t), minute(t) from load_data_missing;" 1979 _, reachLimit, err := ld.InsertData(context.Background(), nil, nil) 1980 c.Assert(err, IsNil) 1981 c.Assert(reachLimit, IsFalse) 1982 r := tk.MustQuery(selectALLEGROSQL) 1983 r.Check(nil) 1984 1985 curTime := types.CurrentTime(allegrosql.TypeTimestamp) 1986 timeHour := curTime.Hour() 1987 timeMinute := curTime.Minute() 1988 tests := []testCase{ 1989 {nil, []byte("12\n"), []string{fmt.Sprintf("12|%v|%v", timeHour, timeMinute)}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 0"}, 1990 } 1991 checkCases(tests, ld, c, tk, ctx, selectALLEGROSQL, deleteALLEGROSQL) 1992 1993 tk.MustInterDirc("alter causet load_data_missing add defCausumn t2 timestamp null") 1994 curTime = types.CurrentTime(allegrosql.TypeTimestamp) 1995 timeHour = curTime.Hour() 1996 timeMinute = curTime.Minute() 1997 selectALLEGROSQL = "select id, hour(t), minute(t), t2 from load_data_missing;" 1998 tests = []testCase{ 1999 {nil, []byte("12\n"), []string{fmt.Sprintf("12|%v|%v|<nil>", timeHour, timeMinute)}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 0"}, 2000 } 2001 checkCases(tests, ld, c, tk, ctx, selectALLEGROSQL, deleteALLEGROSQL) 2002 2003 } 2004 2005 func (s *testSuite4) TestLoadData(c *C) { 2006 trivialMsg := "Records: 1 Deleted: 0 Skipped: 0 Warnings: 0" 2007 tk := testkit.NewTestKit(c, s.causetstore) 2008 tk.MustInterDirc("use test") 2009 createALLEGROSQL := `drop causet if exists load_data_test; 2010 create causet load_data_test (id int PRIMARY KEY AUTO_INCREMENT, c1 int, c2 varchar(255) default "def", c3 int);` 2011 _, err := tk.InterDirc("load data local infile '/tmp/nonexistence.csv' into causet load_data_test") 2012 c.Assert(err, NotNil) 2013 tk.MustInterDirc(createALLEGROSQL) 2014 _, err = tk.InterDirc("load data infile '/tmp/nonexistence.csv' into causet load_data_test") 2015 c.Assert(err, NotNil) 2016 _, err = tk.InterDirc("load data local infile '/tmp/nonexistence.csv' replace into causet load_data_test") 2017 c.Assert(err, NotNil) 2018 tk.MustInterDirc("load data local infile '/tmp/nonexistence.csv' ignore into causet load_data_test") 2019 ctx := tk.Se.(stochastikctx.Context) 2020 ld, ok := ctx.Value(interlock.LoadDataVarKey).(*interlock.LoadDataInfo) 2021 c.Assert(ok, IsTrue) 2022 defer ctx.SetValue(interlock.LoadDataVarKey, nil) 2023 c.Assert(ld, NotNil) 2024 2025 deleteALLEGROSQL := "delete from load_data_test" 2026 selectALLEGROSQL := "select * from load_data_test;" 2027 // data1 = nil, data2 = nil, fields and lines is default 2028 ctx.GetStochastikVars().StmtCtx.DupKeyAsWarning = true 2029 ctx.GetStochastikVars().StmtCtx.BadNullAsWarning = true 2030 _, reachLimit, err := ld.InsertData(context.Background(), nil, nil) 2031 c.Assert(err, IsNil) 2032 c.Assert(reachLimit, IsFalse) 2033 err = ld.CheckAndInsertOneBatch(context.Background(), ld.GetRows(), ld.GetCurBatchCnt()) 2034 c.Assert(err, IsNil) 2035 ld.SetMaxRowsInBatch(20000) 2036 r := tk.MustQuery(selectALLEGROSQL) 2037 r.Check(nil) 2038 2039 sc := ctx.GetStochastikVars().StmtCtx 2040 originIgnoreTruncate := sc.IgnoreTruncate 2041 defer func() { 2042 sc.IgnoreTruncate = originIgnoreTruncate 2043 }() 2044 sc.IgnoreTruncate = false 2045 // fields and lines are default, InsertData returns data is nil 2046 tests := []testCase{ 2047 // data1 = nil, data2 != nil 2048 {nil, []byte("\n"), []string{"1|<nil>|<nil>|<nil>"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 1"}, 2049 {nil, []byte("\t\n"), []string{"2|0|<nil>|<nil>"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 2"}, 2050 {nil, []byte("3\t2\t3\t4\n"), []string{"3|2|3|4"}, nil, trivialMsg}, 2051 {nil, []byte("3*1\t2\t3\t4\n"), []string{"3|2|3|4"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 1"}, 2052 {nil, []byte("4\t2\t\t3\t4\n"), []string{"4|2||3"}, nil, trivialMsg}, 2053 {nil, []byte("\t1\t2\t3\t4\n"), []string{"5|1|2|3"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 1"}, 2054 {nil, []byte("6\t2\t3\n"), []string{"6|2|3|<nil>"}, nil, trivialMsg}, 2055 {nil, []byte("\t2\t3\t4\n\t22\t33\t44\n"), []string{"7|2|3|4", "8|22|33|44"}, nil, "Records: 2 Deleted: 0 Skipped: 0 Warnings: 2"}, 2056 {nil, []byte("7\t2\t3\t4\n7\t22\t33\t44\n"), []string{"7|2|3|4"}, nil, "Records: 2 Deleted: 0 Skipped: 1 Warnings: 1"}, 2057 2058 // data1 != nil, data2 = nil 2059 {[]byte("\t2\t3\t4"), nil, []string{"9|2|3|4"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 1"}, 2060 2061 // data1 != nil, data2 != nil 2062 {[]byte("\t2\t3"), []byte("\t4\t5\n"), []string{"10|2|3|4"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 1"}, 2063 {[]byte("\t2\t3"), []byte("4\t5\n"), []string{"11|2|34|5"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 1"}, 2064 2065 // data1 != nil, data2 != nil, InsertData returns data isn't nil 2066 {[]byte("\t2\t3"), []byte("\t4\t5"), nil, []byte("\t2\t3\t4\t5"), "Records: 0 Deleted: 0 Skipped: 0 Warnings: 0"}, 2067 } 2068 checkCases(tests, ld, c, tk, ctx, selectALLEGROSQL, deleteALLEGROSQL) 2069 c.Assert(sc.WarningCount(), Equals, uint16(1)) 2070 2071 // lines starting symbol is "" and terminated symbol length is 2, InsertData returns data is nil 2072 ld.LinesInfo.Terminated = "||" 2073 tests = []testCase{ 2074 // data1 != nil, data2 != nil 2075 {[]byte("0\t2\t3"), []byte("\t4\t5||"), []string{"12|2|3|4"}, nil, trivialMsg}, 2076 {[]byte("1\t2\t3\t4\t5|"), []byte("|"), []string{"1|2|3|4"}, nil, trivialMsg}, 2077 {[]byte("2\t2\t3\t4\t5|"), []byte("|3\t22\t33\t44\t55||"), 2078 []string{"2|2|3|4", "3|22|33|44"}, nil, "Records: 2 Deleted: 0 Skipped: 0 Warnings: 0"}, 2079 {[]byte("3\t2\t3\t4\t5|"), []byte("|4\t22\t33||"), []string{ 2080 "3|2|3|4", "4|22|33|<nil>"}, nil, "Records: 2 Deleted: 0 Skipped: 0 Warnings: 0"}, 2081 {[]byte("4\t2\t3\t4\t5|"), []byte("|5\t22\t33||6\t222||"), 2082 []string{"4|2|3|4", "5|22|33|<nil>", "6|222|<nil>|<nil>"}, nil, "Records: 3 Deleted: 0 Skipped: 0 Warnings: 0"}, 2083 {[]byte("6\t2\t3"), []byte("4\t5||"), []string{"6|2|34|5"}, nil, trivialMsg}, 2084 } 2085 checkCases(tests, ld, c, tk, ctx, selectALLEGROSQL, deleteALLEGROSQL) 2086 2087 // fields and lines aren't default, InsertData returns data is nil 2088 ld.FieldsInfo.Terminated = "\\" 2089 ld.LinesInfo.Starting = "xxx" 2090 ld.LinesInfo.Terminated = "|!#^" 2091 tests = []testCase{ 2092 // data1 = nil, data2 != nil 2093 {nil, []byte("xxx|!#^"), []string{"13|<nil>|<nil>|<nil>"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 1"}, 2094 {nil, []byte("xxx\\|!#^"), []string{"14|0|<nil>|<nil>"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 2"}, 2095 {nil, []byte("xxx3\\2\\3\\4|!#^"), []string{"3|2|3|4"}, nil, trivialMsg}, 2096 {nil, []byte("xxx4\\2\\\\3\\4|!#^"), []string{"4|2||3"}, nil, trivialMsg}, 2097 {nil, []byte("xxx\\1\\2\\3\\4|!#^"), []string{"15|1|2|3"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 1"}, 2098 {nil, []byte("xxx6\\2\\3|!#^"), []string{"6|2|3|<nil>"}, nil, trivialMsg}, 2099 {nil, []byte("xxx\\2\\3\\4|!#^xxx\\22\\33\\44|!#^"), []string{ 2100 "16|2|3|4", 2101 "17|22|33|44"}, nil, "Records: 2 Deleted: 0 Skipped: 0 Warnings: 2"}, 2102 {nil, []byte("\\2\\3\\4|!#^\\22\\33\\44|!#^xxx\\222\\333\\444|!#^"), []string{ 2103 "18|222|333|444"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 1"}, 2104 2105 // data1 != nil, data2 = nil 2106 {[]byte("xxx\\2\\3\\4"), nil, []string{"19|2|3|4"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 1"}, 2107 {[]byte("\\2\\3\\4|!#^"), nil, []string{}, nil, "Records: 0 Deleted: 0 Skipped: 0 Warnings: 0"}, 2108 {[]byte("\\2\\3\\4|!#^xxx18\\22\\33\\44|!#^"), nil, 2109 []string{"18|22|33|44"}, nil, trivialMsg}, 2110 2111 // data1 != nil, data2 != nil 2112 {[]byte("xxx10\\2\\3"), []byte("\\4|!#^"), 2113 []string{"10|2|3|4"}, nil, trivialMsg}, 2114 {[]byte("10\\2\\3xx"), []byte("x11\\4\\5|!#^"), 2115 []string{"11|4|5|<nil>"}, nil, trivialMsg}, 2116 {[]byte("xxx21\\2\\3\\4\\5|!"), []byte("#^"), 2117 []string{"21|2|3|4"}, nil, trivialMsg}, 2118 {[]byte("xxx22\\2\\3\\4\\5|!"), []byte("#^xxx23\\22\\33\\44\\55|!#^"), 2119 []string{"22|2|3|4", "23|22|33|44"}, nil, "Records: 2 Deleted: 0 Skipped: 0 Warnings: 0"}, 2120 {[]byte("xxx23\\2\\3\\4\\5|!"), []byte("#^xxx24\\22\\33|!#^"), 2121 []string{"23|2|3|4", "24|22|33|<nil>"}, nil, "Records: 2 Deleted: 0 Skipped: 0 Warnings: 0"}, 2122 {[]byte("xxx24\\2\\3\\4\\5|!"), []byte("#^xxx25\\22\\33|!#^xxx26\\222|!#^"), 2123 []string{"24|2|3|4", "25|22|33|<nil>", "26|222|<nil>|<nil>"}, nil, "Records: 3 Deleted: 0 Skipped: 0 Warnings: 0"}, 2124 {[]byte("xxx25\\2\\3\\4\\5|!"), []byte("#^26\\22\\33|!#^xxx27\\222|!#^"), 2125 []string{"25|2|3|4", "27|222|<nil>|<nil>"}, nil, "Records: 2 Deleted: 0 Skipped: 0 Warnings: 0"}, 2126 {[]byte("xxx\\2\\3"), []byte("4\\5|!#^"), []string{"28|2|34|5"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 1"}, 2127 2128 // InsertData returns data isn't nil 2129 {nil, []byte("\\2\\3\\4|!#^"), nil, []byte("#^"), "Records: 0 Deleted: 0 Skipped: 0 Warnings: 0"}, 2130 {nil, []byte("\\4\\5"), nil, []byte("\\5"), "Records: 0 Deleted: 0 Skipped: 0 Warnings: 0"}, 2131 {[]byte("\\2\\3"), []byte("\\4\\5"), nil, []byte("\\5"), "Records: 0 Deleted: 0 Skipped: 0 Warnings: 0"}, 2132 {[]byte("xxx1\\2\\3|"), []byte("!#^\\4\\5|!#"), 2133 []string{"1|2|3|<nil>"}, []byte("!#"), trivialMsg}, 2134 {[]byte("xxx1\\2\\3\\4\\5|!"), []byte("#^xxx2\\22\\33|!#^3\\222|!#^"), 2135 []string{"1|2|3|4", "2|22|33|<nil>"}, []byte("#^"), "Records: 2 Deleted: 0 Skipped: 0 Warnings: 0"}, 2136 {[]byte("xx1\\2\\3"), []byte("\\4\\5|!#^"), nil, []byte("#^"), "Records: 0 Deleted: 0 Skipped: 0 Warnings: 0"}, 2137 } 2138 checkCases(tests, ld, c, tk, ctx, selectALLEGROSQL, deleteALLEGROSQL) 2139 2140 // lines starting symbol is the same as terminated symbol, InsertData returns data is nil 2141 ld.LinesInfo.Terminated = "xxx" 2142 tests = []testCase{ 2143 // data1 = nil, data2 != nil 2144 {nil, []byte("xxxxxx"), []string{"29|<nil>|<nil>|<nil>"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 1"}, 2145 {nil, []byte("xxx3\\2\\3\\4xxx"), []string{"3|2|3|4"}, nil, trivialMsg}, 2146 {nil, []byte("xxx\\2\\3\\4xxxxxx\\22\\33\\44xxx"), 2147 []string{"30|2|3|4", "31|22|33|44"}, nil, "Records: 2 Deleted: 0 Skipped: 0 Warnings: 2"}, 2148 2149 // data1 != nil, data2 = nil 2150 {[]byte("xxx\\2\\3\\4"), nil, []string{"32|2|3|4"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 1"}, 2151 2152 // data1 != nil, data2 != nil 2153 {[]byte("xxx10\\2\\3"), []byte("\\4\\5xxx"), []string{"10|2|3|4"}, nil, trivialMsg}, 2154 {[]byte("xxxxx10\\2\\3"), []byte("\\4\\5xxx"), []string{"33|2|3|4"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 1"}, 2155 {[]byte("xxx21\\2\\3\\4\\5xx"), []byte("x"), []string{"21|2|3|4"}, nil, trivialMsg}, 2156 {[]byte("xxx32\\2\\3\\4\\5x"), []byte("xxxxx33\\22\\33\\44\\55xxx"), 2157 []string{"32|2|3|4", "33|22|33|44"}, nil, "Records: 2 Deleted: 0 Skipped: 0 Warnings: 0"}, 2158 {[]byte("xxx33\\2\\3\\4\\5xxx"), []byte("xxx34\\22\\33xxx"), 2159 []string{"33|2|3|4", "34|22|33|<nil>"}, nil, "Records: 2 Deleted: 0 Skipped: 0 Warnings: 0"}, 2160 {[]byte("xxx34\\2\\3\\4\\5xx"), []byte("xxxx35\\22\\33xxxxxx36\\222xxx"), 2161 []string{"34|2|3|4", "35|22|33|<nil>", "36|222|<nil>|<nil>"}, nil, "Records: 3 Deleted: 0 Skipped: 0 Warnings: 0"}, 2162 2163 // InsertData returns data isn't nil 2164 {nil, []byte("\\2\\3\\4xxxx"), nil, []byte("xxxx"), "Records: 0 Deleted: 0 Skipped: 0 Warnings: 0"}, 2165 {[]byte("\\2\\3\\4xxx"), nil, []string{"37|<nil>|<nil>|<nil>"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 1"}, 2166 {[]byte("\\2\\3\\4xxxxxx11\\22\\33\\44xxx"), nil, 2167 []string{"38|<nil>|<nil>|<nil>", "39|<nil>|<nil>|<nil>"}, nil, "Records: 2 Deleted: 0 Skipped: 0 Warnings: 2"}, 2168 {[]byte("xx10\\2\\3"), []byte("\\4\\5xxx"), nil, []byte("xxx"), "Records: 0 Deleted: 0 Skipped: 0 Warnings: 0"}, 2169 {[]byte("xxx10\\2\\3"), []byte("\\4xxxx"), []string{"10|2|3|4"}, []byte("x"), trivialMsg}, 2170 {[]byte("xxx10\\2\\3\\4\\5x"), []byte("xx11\\22\\33xxxxxx12\\222xxx"), 2171 []string{"10|2|3|4", "40|<nil>|<nil>|<nil>"}, []byte("xxx"), "Records: 2 Deleted: 0 Skipped: 0 Warnings: 1"}, 2172 } 2173 checkCases(tests, ld, c, tk, ctx, selectALLEGROSQL, deleteALLEGROSQL) 2174 } 2175 2176 func (s *testSuite4) TestLoadDataEscape(c *C) { 2177 trivialMsg := "Records: 1 Deleted: 0 Skipped: 0 Warnings: 0" 2178 tk := testkit.NewTestKit(c, s.causetstore) 2179 tk.MustInterDirc("use test; drop causet if exists load_data_test;") 2180 tk.MustInterDirc("CREATE TABLE load_data_test (id INT NOT NULL PRIMARY KEY, value TEXT NOT NULL) CHARACTER SET utf8") 2181 tk.MustInterDirc("load data local infile '/tmp/nonexistence.csv' into causet load_data_test") 2182 ctx := tk.Se.(stochastikctx.Context) 2183 ld, ok := ctx.Value(interlock.LoadDataVarKey).(*interlock.LoadDataInfo) 2184 c.Assert(ok, IsTrue) 2185 defer ctx.SetValue(interlock.LoadDataVarKey, nil) 2186 c.Assert(ld, NotNil) 2187 // test escape 2188 tests := []testCase{ 2189 // data1 = nil, data2 != nil 2190 {nil, []byte("1\ta string\n"), []string{"1|a string"}, nil, trivialMsg}, 2191 {nil, []byte("2\tstr \\t\n"), []string{"2|str \t"}, nil, trivialMsg}, 2192 {nil, []byte("3\tstr \\n\n"), []string{"3|str \n"}, nil, trivialMsg}, 2193 {nil, []byte("4\tboth \\t\\n\n"), []string{"4|both \t\n"}, nil, trivialMsg}, 2194 {nil, []byte("5\tstr \\\\\n"), []string{"5|str \\"}, nil, trivialMsg}, 2195 {nil, []byte("6\t\\r\\t\\n\\0\\Z\\b\n"), []string{"6|" + string([]byte{'\r', '\t', '\n', 0, 26, '\b'})}, nil, trivialMsg}, 2196 {nil, []byte("7\trtn0ZbN\n"), []string{"7|" + string([]byte{'r', 't', 'n', '0', 'Z', 'b', 'N'})}, nil, trivialMsg}, 2197 {nil, []byte("8\trtn0Zb\\N\n"), []string{"8|" + string([]byte{'r', 't', 'n', '0', 'Z', 'b', 'N'})}, nil, trivialMsg}, 2198 {nil, []byte("9\ttab\\ tab\n"), []string{"9|tab tab"}, nil, trivialMsg}, 2199 } 2200 deleteALLEGROSQL := "delete from load_data_test" 2201 selectALLEGROSQL := "select * from load_data_test;" 2202 checkCases(tests, ld, c, tk, ctx, selectALLEGROSQL, deleteALLEGROSQL) 2203 } 2204 2205 // TestLoadDataSpecifiedDeferredCausets reuse TestLoadDataEscape's test case :-) 2206 func (s *testSuite4) TestLoadDataSpecifiedDeferredCausets(c *C) { 2207 trivialMsg := "Records: 1 Deleted: 0 Skipped: 0 Warnings: 0" 2208 tk := testkit.NewTestKit(c, s.causetstore) 2209 tk.MustInterDirc("use test; drop causet if exists load_data_test;") 2210 tk.MustInterDirc(`create causet load_data_test (id int PRIMARY KEY AUTO_INCREMENT, c1 int, c2 varchar(255) default "def", c3 int default 0);`) 2211 tk.MustInterDirc("load data local infile '/tmp/nonexistence.csv' into causet load_data_test (c1, c2)") 2212 ctx := tk.Se.(stochastikctx.Context) 2213 ld, ok := ctx.Value(interlock.LoadDataVarKey).(*interlock.LoadDataInfo) 2214 c.Assert(ok, IsTrue) 2215 defer ctx.SetValue(interlock.LoadDataVarKey, nil) 2216 c.Assert(ld, NotNil) 2217 // test 2218 tests := []testCase{ 2219 // data1 = nil, data2 != nil 2220 {nil, []byte("7\ta string\n"), []string{"1|7|a string|0"}, nil, trivialMsg}, 2221 {nil, []byte("8\tstr \\t\n"), []string{"2|8|str \t|0"}, nil, trivialMsg}, 2222 {nil, []byte("9\tstr \\n\n"), []string{"3|9|str \n|0"}, nil, trivialMsg}, 2223 {nil, []byte("10\tboth \\t\\n\n"), []string{"4|10|both \t\n|0"}, nil, trivialMsg}, 2224 {nil, []byte("11\tstr \\\\\n"), []string{"5|11|str \\|0"}, nil, trivialMsg}, 2225 {nil, []byte("12\t\\r\\t\\n\\0\\Z\\b\n"), []string{"6|12|" + string([]byte{'\r', '\t', '\n', 0, 26, '\b'}) + "|0"}, nil, trivialMsg}, 2226 {nil, []byte("\\N\ta string\n"), []string{"7|<nil>|a string|0"}, nil, trivialMsg}, 2227 } 2228 deleteALLEGROSQL := "delete from load_data_test" 2229 selectALLEGROSQL := "select * from load_data_test;" 2230 checkCases(tests, ld, c, tk, ctx, selectALLEGROSQL, deleteALLEGROSQL) 2231 } 2232 2233 func (s *testSuite4) TestLoadDataIgnoreLines(c *C) { 2234 tk := testkit.NewTestKit(c, s.causetstore) 2235 tk.MustInterDirc("use test; drop causet if exists load_data_test;") 2236 tk.MustInterDirc("CREATE TABLE load_data_test (id INT NOT NULL PRIMARY KEY, value TEXT NOT NULL) CHARACTER SET utf8") 2237 tk.MustInterDirc("load data local infile '/tmp/nonexistence.csv' into causet load_data_test ignore 1 lines") 2238 ctx := tk.Se.(stochastikctx.Context) 2239 ld, ok := ctx.Value(interlock.LoadDataVarKey).(*interlock.LoadDataInfo) 2240 c.Assert(ok, IsTrue) 2241 defer ctx.SetValue(interlock.LoadDataVarKey, nil) 2242 c.Assert(ld, NotNil) 2243 tests := []testCase{ 2244 {nil, []byte("1\tline1\n2\tline2\n"), []string{"2|line2"}, nil, "Records: 1 Deleted: 0 Skipped: 0 Warnings: 0"}, 2245 {nil, []byte("1\tline1\n2\tline2\n3\tline3\n"), []string{"2|line2", "3|line3"}, nil, "Records: 2 Deleted: 0 Skipped: 0 Warnings: 0"}, 2246 } 2247 deleteALLEGROSQL := "delete from load_data_test" 2248 selectALLEGROSQL := "select * from load_data_test;" 2249 checkCases(tests, ld, c, tk, ctx, selectALLEGROSQL, deleteALLEGROSQL) 2250 } 2251 2252 // TestLoadDataOverflowBigintUnsigned related to issue 6360 2253 func (s *testSuite4) TestLoadDataOverflowBigintUnsigned(c *C) { 2254 tk := testkit.NewTestKit(c, s.causetstore) 2255 tk.MustInterDirc("use test; drop causet if exists load_data_test;") 2256 tk.MustInterDirc("CREATE TABLE load_data_test (a bigint unsigned);") 2257 tk.MustInterDirc("load data local infile '/tmp/nonexistence.csv' into causet load_data_test") 2258 ctx := tk.Se.(stochastikctx.Context) 2259 ld, ok := ctx.Value(interlock.LoadDataVarKey).(*interlock.LoadDataInfo) 2260 c.Assert(ok, IsTrue) 2261 defer ctx.SetValue(interlock.LoadDataVarKey, nil) 2262 c.Assert(ld, NotNil) 2263 tests := []testCase{ 2264 {nil, []byte("-1\n-18446744073709551615\n-18446744073709551616\n"), []string{"0", "0", "0"}, nil, "Records: 3 Deleted: 0 Skipped: 0 Warnings: 3"}, 2265 {nil, []byte("-9223372036854775809\n18446744073709551616\n"), []string{"0", "18446744073709551615"}, nil, "Records: 2 Deleted: 0 Skipped: 0 Warnings: 2"}, 2266 } 2267 deleteALLEGROSQL := "delete from load_data_test" 2268 selectALLEGROSQL := "select * from load_data_test;" 2269 checkCases(tests, ld, c, tk, ctx, selectALLEGROSQL, deleteALLEGROSQL) 2270 } 2271 2272 func (s *testSuite4) TestLoadDataIntoPartitionedBlock(c *C) { 2273 tk := testkit.NewTestKit(c, s.causetstore) 2274 tk.MustInterDirc("use test") 2275 tk.MustInterDirc("create causet range_t (a int, b int) partition by range (a) ( " + 2276 "partition p0 values less than (4)," + 2277 "partition p1 values less than (7)," + 2278 "partition p2 values less than (11))") 2279 tk.MustInterDirc("load data local infile '/tmp/nonexistence.csv' into causet range_t fields terminated by ','") 2280 ctx := tk.Se.(stochastikctx.Context) 2281 ld := ctx.Value(interlock.LoadDataVarKey).(*interlock.LoadDataInfo) 2282 c.Assert(ctx.NewTxn(context.Background()), IsNil) 2283 2284 _, _, err := ld.InsertData(context.Background(), nil, []byte("1,2\n3,4\n5,6\n7,8\n9,10\n")) 2285 c.Assert(err, IsNil) 2286 err = ld.CheckAndInsertOneBatch(context.Background(), ld.GetRows(), ld.GetCurBatchCnt()) 2287 c.Assert(err, IsNil) 2288 ld.SetMaxRowsInBatch(20000) 2289 ld.SetMessage() 2290 ctx.StmtCommit() 2291 txn, err := ctx.Txn(true) 2292 c.Assert(err, IsNil) 2293 err = txn.Commit(context.Background()) 2294 c.Assert(err, IsNil) 2295 } 2296 2297 func (s *testSuite4) TestNullDefault(c *C) { 2298 tk := testkit.NewTestKit(c, s.causetstore) 2299 tk.MustInterDirc("use test; drop causet if exists test_null_default;") 2300 tk.MustInterDirc("set timestamp = 1234") 2301 tk.MustInterDirc("set time_zone = '+08:00'") 2302 tk.MustInterDirc("create causet test_null_default (ts timestamp null default current_timestamp)") 2303 tk.MustInterDirc("insert into test_null_default values (null)") 2304 tk.MustQuery("select * from test_null_default").Check(testkit.Rows("<nil>")) 2305 tk.MustInterDirc("insert into test_null_default values ()") 2306 tk.MustQuery("select * from test_null_default").Check(testkit.Rows("<nil>", "1970-01-01 08:20:34")) 2307 } 2308 2309 func (s *testSuite4) TestNotNullDefault(c *C) { 2310 tk := testkit.NewTestKit(c, s.causetstore) 2311 tk.MustInterDirc("use test; drop causet if exists t1,t2;") 2312 defer tk.MustInterDirc("drop causet t1,t2") 2313 tk.MustInterDirc("create causet t1 (a int not null default null default 1);") 2314 tk.MustInterDirc("create causet t2 (a int);") 2315 tk.MustInterDirc("alter causet t2 change defCausumn a a int not null default null default 1;") 2316 } 2317 2318 func (s *testBypassSuite) TestLatch(c *C) { 2319 causetstore, err := mockstore.NewMockStore( 2320 // Small latch slot size to make conflicts. 2321 mockstore.WithTxnLocalLatches(64), 2322 ) 2323 c.Assert(err, IsNil) 2324 defer causetstore.Close() 2325 2326 dom, err1 := stochastik.BootstrapStochastik(causetstore) 2327 c.Assert(err1, IsNil) 2328 defer dom.Close() 2329 2330 tk1 := testkit.NewTestKit(c, causetstore) 2331 tk1.MustInterDirc("use test") 2332 tk1.MustInterDirc("drop causet if exists t") 2333 tk1.MustInterDirc("create causet t (id int)") 2334 tk1.MustInterDirc("set @@milevadb_disable_txn_auto_retry = true") 2335 2336 tk2 := testkit.NewTestKit(c, causetstore) 2337 tk2.MustInterDirc("use test") 2338 tk1.MustInterDirc("set @@milevadb_disable_txn_auto_retry = true") 2339 2340 fn := func() { 2341 tk1.MustInterDirc("begin") 2342 for i := 0; i < 100; i++ { 2343 tk1.MustInterDirc(fmt.Sprintf("insert into t values (%d)", i)) 2344 } 2345 tk2.MustInterDirc("begin") 2346 for i := 100; i < 200; i++ { 2347 tk1.MustInterDirc(fmt.Sprintf("insert into t values (%d)", i)) 2348 } 2349 tk2.MustInterDirc("commit") 2350 } 2351 2352 // txn1 and txn2 data range do not overlap, using latches should not 2353 // result in txn conflict. 2354 fn() 2355 tk1.MustInterDirc("commit") 2356 2357 tk1.MustInterDirc("truncate causet t") 2358 fn() 2359 tk1.MustInterDirc("commit") 2360 2361 // Test the error type of latch and it could be retry if MilevaDB enable the retry. 2362 tk1.MustInterDirc("begin") 2363 tk1.MustInterDirc("uFIDelate t set id = id + 1") 2364 tk2.MustInterDirc("uFIDelate t set id = id + 1") 2365 _, err = tk1.InterDirc("commit") 2366 c.Assert(ekv.ErrWriteConflictInMilevaDB.Equal(err), IsTrue) 2367 2368 tk1.MustInterDirc("set @@milevadb_disable_txn_auto_retry = 0") 2369 tk1.MustInterDirc("uFIDelate t set id = id + 1") 2370 tk2.MustInterDirc("uFIDelate t set id = id + 1") 2371 tk1.MustInterDirc("commit") 2372 } 2373 2374 // TestIssue4067 Test issue https://github.com/whtcorpsinc/milevadb/issues/4067 2375 func (s *testSuite7) TestIssue4067(c *C) { 2376 tk := testkit.NewTestKit(c, s.causetstore) 2377 tk.MustInterDirc("use test") 2378 tk.MustInterDirc("drop causet if exists t1, t2") 2379 tk.MustInterDirc("create causet t1(id int)") 2380 tk.MustInterDirc("create causet t2(id int)") 2381 tk.MustInterDirc("insert into t1 values(123)") 2382 tk.MustInterDirc("insert into t2 values(123)") 2383 tk.MustInterDirc("delete from t1 where id not in (select id from t2)") 2384 tk.MustQuery("select * from t1").Check(testkit.Rows("123")) 2385 tk.MustInterDirc("delete from t1 where id in (select id from t2)") 2386 tk.MustQuery("select * from t1").Check(nil) 2387 } 2388 2389 func (s *testSuite7) TestInsertCalculatedValue(c *C) { 2390 tk := testkit.NewTestKit(c, s.causetstore) 2391 tk.MustInterDirc("use test") 2392 2393 tk.MustInterDirc("drop causet if exists t") 2394 tk.MustInterDirc("create causet t(a int, b int)") 2395 tk.MustInterDirc("insert into t set a=1, b=a+1") 2396 tk.MustQuery("select a, b from t").Check(testkit.Rows("1 2")) 2397 2398 tk.MustInterDirc("drop causet if exists t") 2399 tk.MustInterDirc("create causet t(a int default 100, b int)") 2400 tk.MustInterDirc("insert into t set b=a+1, a=1") 2401 tk.MustQuery("select a, b from t").Check(testkit.Rows("1 101")) 2402 tk.MustInterDirc("insert into t (b) value (a)") 2403 tk.MustQuery("select * from t where b = 100").Check(testkit.Rows("100 100")) 2404 tk.MustInterDirc("insert into t set a=2, b=a+1") 2405 tk.MustQuery("select * from t where a = 2").Check(testkit.Rows("2 3")) 2406 2407 tk.MustInterDirc("drop causet if exists t") 2408 tk.MustInterDirc("create causet t (c int)") 2409 tk.MustInterDirc("insert into test.t set test.t.c = '1'") 2410 tk.MustQuery("select * from t").Check(testkit.Rows("1")) 2411 2412 tk.MustInterDirc("drop causet if exists t") 2413 tk.MustInterDirc("create causet t(a int default 1)") 2414 tk.MustInterDirc("insert into t values (a)") 2415 tk.MustQuery("select * from t").Check(testkit.Rows("1")) 2416 2417 tk.MustInterDirc("drop causet if exists t") 2418 tk.MustInterDirc("create causet t (a int, b int, c int, d int)") 2419 tk.MustInterDirc("insert into t value (1, 2, a+1, b+1)") 2420 tk.MustQuery("select * from t").Check(testkit.Rows("1 2 2 3")) 2421 2422 tk.MustInterDirc("drop causet if exists t") 2423 tk.MustInterDirc("create causet t (a int not null)") 2424 tk.MustInterDirc("insert into t values (a+2)") 2425 tk.MustInterDirc("insert into t values (a)") 2426 tk.MustQuery("select * from t order by a").Check(testkit.Rows("0", "2")) 2427 2428 tk.MustInterDirc("drop causet if exists t") 2429 tk.MustInterDirc("create causet t (a bigint not null, b bigint not null)") 2430 tk.MustInterDirc("insert into t value(b + 1, a)") 2431 tk.MustInterDirc("insert into t set a = b + a, b = a + 1") 2432 tk.MustInterDirc("insert into t value(1000, a)") 2433 tk.MustInterDirc("insert t set b = sqrt(a + 4), a = 10") 2434 tk.MustQuery("select * from t order by a").Check(testkit.Rows("0 1", "1 1", "10 2", "1000 1000")) 2435 2436 tk.MustInterDirc("drop causet if exists t") 2437 tk.MustInterDirc("create causet t(a int)") 2438 tk.MustInterDirc("insert into t values(a)") 2439 tk.MustQuery("select * from t").Check(testkit.Rows("<nil>")) 2440 2441 tk.MustInterDirc("drop causet if exists t") 2442 tk.MustInterDirc("create causet t(a enum('a', 'b'))") 2443 tk.MustInterDirc("insert into t values(a)") 2444 tk.MustQuery("select * from t").Check(testkit.Rows("<nil>")) 2445 tk.MustInterDirc("drop causet if exists t") 2446 tk.MustInterDirc("create causet t(a enum('a', 'b') default 'a')") 2447 tk.MustInterDirc("insert into t values(a)") 2448 tk.MustInterDirc("insert into t values(a+1)") 2449 tk.MustQuery("select * from t order by a").Check(testkit.Rows("a", "b")) 2450 2451 tk.MustInterDirc("drop causet if exists t") 2452 tk.MustInterDirc("create causet t(a blob)") 2453 tk.MustInterDirc("insert into t values(a)") 2454 tk.MustQuery("select * from t").Check(testkit.Rows("<nil>")) 2455 2456 tk.MustInterDirc("drop causet if exists t") 2457 tk.MustInterDirc("create causet t(a varchar(20) default 'a')") 2458 tk.MustInterDirc("insert into t values(a)") 2459 tk.MustInterDirc("insert into t values(upper(a))") 2460 tk.MustQuery("select * from t order by a").Check(testkit.Rows("A", "a")) 2461 tk.MustInterDirc("drop causet if exists t") 2462 tk.MustInterDirc("create causet t(a varchar(20) not null, b varchar(20))") 2463 tk.MustInterDirc("insert into t value (a, b)") 2464 tk.MustQuery("select * from t").Check(testkit.Rows(" <nil>")) 2465 2466 tk.MustInterDirc("drop causet if exists t") 2467 tk.MustInterDirc("create causet t(a int, b int)") 2468 tk.MustInterDirc("insert into t values(a*b, b*b)") 2469 tk.MustQuery("select * from t").Check(testkit.Rows("<nil> <nil>")) 2470 2471 tk.MustInterDirc("drop causet if exists t") 2472 tk.MustInterDirc("create causet t (a json not null, b int)") 2473 tk.MustInterDirc("insert into t value (a,a->'$')") 2474 tk.MustQuery("select * from t").Check(testkit.Rows("null 0")) 2475 2476 tk.MustInterDirc("drop causet if exists t") 2477 tk.MustInterDirc("create causet t(a json, b int, c int as (a->'$.a'))") 2478 tk.MustInterDirc("insert into t (a, b) value (a, a->'$.a'+1)") 2479 tk.MustInterDirc("insert into t (b) value (a->'$.a'+1)") 2480 tk.MustQuery("select * from t").Check(testkit.Rows("<nil> <nil> <nil>", "<nil> <nil> <nil>")) 2481 tk.MustInterDirc(`insert into t (a, b) value ('{"a": 1}', a->'$.a'+1)`) 2482 tk.MustQuery("select * from t where c = 1").Check(testkit.Rows(`{"a": 1} 2 1`)) 2483 tk.MustInterDirc("truncate causet t") 2484 tk.MustInterDirc("insert t set b = c + 1") 2485 tk.MustQuery("select * from t").Check(testkit.Rows("<nil> <nil> <nil>")) 2486 tk.MustInterDirc("truncate causet t") 2487 tk.MustInterDirc(`insert t set a = '{"a": 1}', b = c`) 2488 tk.MustQuery("select * from t").Check(testkit.Rows(`{"a": 1} <nil> 1`)) 2489 2490 tk.MustInterDirc("drop causet if exists t") 2491 tk.MustInterDirc("create causet t(a int auto_increment key, b int)") 2492 tk.MustInterDirc("insert into t (b) value (a)") 2493 tk.MustInterDirc("insert into t value (a, a+1)") 2494 tk.MustInterDirc("set ALLEGROSQL_MODE=NO_AUTO_VALUE_ON_ZERO") 2495 tk.MustInterDirc("insert into t (b) value (a+1)") 2496 tk.MustQuery("select * from t order by a").Check(testkit.Rows("1 0", "2 1", "3 1")) 2497 2498 tk.MustInterDirc("set ALLEGROSQL_MODE=STRICT_ALL_TABLES") 2499 tk.MustInterDirc("drop causet if exists t") 2500 tk.MustInterDirc("create causet t(a int not null, b int, c int as (sqrt(a)))") 2501 tk.MustInterDirc("insert t set b = a, a = 4") 2502 tk.MustQuery("select * from t").Check(testkit.Rows("4 0 2")) 2503 } 2504 2505 func (s *testSuite7) TestDataTooLongErrMsg(c *C) { 2506 tk := testkit.NewTestKit(c, s.causetstore) 2507 tk.MustInterDirc("use test") 2508 tk.MustInterDirc("create causet t(a varchar(2));") 2509 _, err := tk.InterDirc("insert into t values('123');") 2510 c.Assert(types.ErrDataTooLong.Equal(err), IsTrue) 2511 c.Assert(err.Error(), Equals, "[types:1406]Data too long for defCausumn 'a' at event 1") 2512 tk.MustInterDirc("insert into t values('12')") 2513 _, err = tk.InterDirc("uFIDelate t set a = '123' where a = '12';") 2514 c.Assert(types.ErrDataTooLong.Equal(err), IsTrue) 2515 c.Assert(err.Error(), Equals, "[types:1406]Data too long for defCausumn 'a' at event 1") 2516 } 2517 2518 func (s *testSuite7) TestUFIDelateSelect(c *C) { 2519 tk := testkit.NewTestKit(c, s.causetstore) 2520 tk.MustInterDirc("use test") 2521 tk.MustInterDirc("create causet msg (id varchar(8), b int, status int, primary key (id, b))") 2522 tk.MustInterDirc("insert msg values ('abc', 1, 1)") 2523 tk.MustInterDirc("create causet detail (id varchar(8), start varchar(8), status int, index idx_start(start))") 2524 tk.MustInterDirc("insert detail values ('abc', '123', 2)") 2525 tk.MustInterDirc("UFIDelATE msg SET msg.status = (SELECT detail.status FROM detail WHERE msg.id = detail.id)") 2526 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 0") 2527 tk.MustInterDirc("admin check causet msg") 2528 } 2529 2530 func (s *testSuite7) TestUFIDelateDelete(c *C) { 2531 tk := testkit.NewTestKit(c, s.causetstore) 2532 tk.MustInterDirc("use test") 2533 tk.MustInterDirc("CREATE TABLE ttt (id bigint(20) NOT NULL, host varchar(30) NOT NULL, PRIMARY KEY (id), UNIQUE KEY i_host (host));") 2534 tk.MustInterDirc("insert into ttt values (8,8),(9,9);") 2535 2536 tk.MustInterDirc("begin") 2537 tk.MustInterDirc("uFIDelate ttt set id = 0, host='9' where id = 9 limit 1;") 2538 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 0") 2539 tk.MustInterDirc("delete from ttt where id = 0 limit 1;") 2540 tk.MustQuery("select * from ttt use index (i_host) order by host;").Check(testkit.Rows("8 8")) 2541 tk.MustInterDirc("uFIDelate ttt set id = 0, host='8' where id = 8 limit 1;") 2542 tk.CheckLastMessage("Rows matched: 1 Changed: 1 Warnings: 0") 2543 tk.MustInterDirc("delete from ttt where id = 0 limit 1;") 2544 tk.MustQuery("select * from ttt use index (i_host) order by host;").Check(testkit.Rows()) 2545 tk.MustInterDirc("commit") 2546 tk.MustInterDirc("admin check causet ttt;") 2547 tk.MustInterDirc("drop causet ttt") 2548 } 2549 2550 func (s *testSuite7) TestUFIDelateAffectRowCnt(c *C) { 2551 tk := testkit.NewTestKit(c, s.causetstore) 2552 tk.MustInterDirc("use test") 2553 tk.MustInterDirc("create causet a(id int auto_increment, a int default null, primary key(id))") 2554 tk.MustInterDirc("insert into a values (1, 1001), (2, 1001), (10001, 1), (3, 1)") 2555 tk.MustInterDirc("uFIDelate a set id = id*10 where a = 1001") 2556 ctx := tk.Se.(stochastikctx.Context) 2557 c.Assert(ctx.GetStochastikVars().StmtCtx.AffectedRows(), Equals, uint64(2)) 2558 tk.CheckLastMessage("Rows matched: 2 Changed: 2 Warnings: 0") 2559 2560 tk.MustInterDirc("drop causet a") 2561 tk.MustInterDirc("create causet a ( a bigint, b bigint)") 2562 tk.MustInterDirc("insert into a values (1, 1001), (2, 1001), (10001, 1), (3, 1)") 2563 tk.MustInterDirc("uFIDelate a set a = a*10 where b = 1001") 2564 ctx = tk.Se.(stochastikctx.Context) 2565 c.Assert(ctx.GetStochastikVars().StmtCtx.AffectedRows(), Equals, uint64(2)) 2566 tk.CheckLastMessage("Rows matched: 2 Changed: 2 Warnings: 0") 2567 } 2568 2569 func (s *testSuite7) TestReplaceLog(c *C) { 2570 tk := testkit.NewTestKit(c, s.causetstore) 2571 tk.MustInterDirc("use test") 2572 tk.MustInterDirc(`create causet testLog (a int not null primary key, b int unique key);`) 2573 2574 // Make some dangling index. 2575 s.ctx = mock.NewContext() 2576 s.ctx.CausetStore = s.causetstore 2577 is := s.petri.SchemaReplicant() 2578 dbName := perceptron.NewCIStr("test") 2579 tblName := perceptron.NewCIStr("testLog") 2580 tbl, err := is.BlockByName(dbName, tblName) 2581 c.Assert(err, IsNil) 2582 tblInfo := tbl.Meta() 2583 idxInfo := tblInfo.FindIndexByName("b") 2584 indexOpr := blocks.NewIndex(tblInfo.ID, tblInfo, idxInfo) 2585 2586 txn, err := s.causetstore.Begin() 2587 c.Assert(err, IsNil) 2588 _, err = indexOpr.Create(s.ctx, txn.GetUnionStore(), types.MakeCausets(1), ekv.IntHandle(1)) 2589 c.Assert(err, IsNil) 2590 err = txn.Commit(context.Background()) 2591 c.Assert(err, IsNil) 2592 2593 _, err = tk.InterDirc(`replace into testLog values (0, 0), (1, 1);`) 2594 c.Assert(err, NotNil) 2595 expErr := errors.New(`can not be duplicated event, due to old event not found. handle 1 not found`) 2596 c.Assert(expErr.Error() == err.Error(), IsTrue, Commentf("obtained error: (%s)\nexpected error: (%s)", err.Error(), expErr.Error())) 2597 2598 tk.MustQuery(`admin cleanup index testLog b;`).Check(testkit.Rows("1")) 2599 } 2600 2601 // TestRebaseIfNeeded is for issue 7422. 2602 // There is no need to do the rebase when uFIDelating a record if the auto-increment ID not changed. 2603 // This could make the auto ID increasing speed slower. 2604 func (s *testSuite7) TestRebaseIfNeeded(c *C) { 2605 tk := testkit.NewTestKit(c, s.causetstore) 2606 tk.MustInterDirc("use test") 2607 tk.MustInterDirc(`create causet t (a int not null primary key auto_increment, b int unique key);`) 2608 tk.MustInterDirc(`insert into t (b) values (1);`) 2609 2610 s.ctx = mock.NewContext() 2611 s.ctx.CausetStore = s.causetstore 2612 tbl, err := s.petri.SchemaReplicant().BlockByName(perceptron.NewCIStr("test"), perceptron.NewCIStr("t")) 2613 c.Assert(err, IsNil) 2614 c.Assert(s.ctx.NewTxn(context.Background()), IsNil) 2615 // AddRecord directly here will skip to rebase the auto ID in the insert memex, 2616 // which could simulate another MilevaDB adds a large auto ID. 2617 _, err = tbl.AddRecord(s.ctx, types.MakeCausets(30001, 2)) 2618 c.Assert(err, IsNil) 2619 txn, err := s.ctx.Txn(true) 2620 c.Assert(err, IsNil) 2621 c.Assert(txn.Commit(context.Background()), IsNil) 2622 2623 tk.MustInterDirc(`uFIDelate t set b = 3 where a = 30001;`) 2624 tk.MustInterDirc(`insert into t (b) values (4);`) 2625 tk.MustQuery(`select a from t where b = 4;`).Check(testkit.Rows("2")) 2626 2627 tk.MustInterDirc(`insert into t set b = 3 on duplicate key uFIDelate a = a;`) 2628 tk.MustInterDirc(`insert into t (b) values (5);`) 2629 tk.MustQuery(`select a from t where b = 5;`).Check(testkit.Rows("4")) 2630 2631 tk.MustInterDirc(`insert into t set b = 3 on duplicate key uFIDelate a = a + 1;`) 2632 tk.MustInterDirc(`insert into t (b) values (6);`) 2633 tk.MustQuery(`select a from t where b = 6;`).Check(testkit.Rows("30003")) 2634 } 2635 2636 func (s *testSuite7) TestDeferConstraintCheckForDelete(c *C) { 2637 tk := testkit.NewTestKit(c, s.causetstore) 2638 tk.MustInterDirc("set milevadb_constraint_check_in_place = 0") 2639 tk.MustInterDirc("set @@milevadb_txn_mode = 'optimistic'") 2640 tk.MustInterDirc("use test") 2641 2642 tk.MustInterDirc("drop causet if exists t1, t2, t3, t4, t5") 2643 tk.MustInterDirc("create causet t1(i int primary key, j int)") 2644 tk.MustInterDirc("insert into t1 values(1, 2)") 2645 tk.MustInterDirc("begin") 2646 tk.MustInterDirc("insert into t1 values(1, 3)") 2647 tk.MustInterDirc("delete from t1 where j = 3") 2648 _, err := tk.InterDirc("commit") 2649 c.Assert(err.Error(), Equals, "previous memex: delete from t1 where j = 3: [ekv:1062]Duplicate entry '1' for key 'PRIMARY'") 2650 tk.MustInterDirc("rollback") 2651 2652 tk.MustInterDirc("create causet t2(i int, j int, unique index idx(i))") 2653 tk.MustInterDirc("insert into t2 values(1, 2)") 2654 tk.MustInterDirc("begin") 2655 tk.MustInterDirc("insert into t2 values(1, 3)") 2656 tk.MustInterDirc("delete from t2 where j = 3") 2657 _, err = tk.InterDirc("commit") 2658 c.Assert(err.Error(), Equals, "previous memex: delete from t2 where j = 3: [ekv:1062]Duplicate entry '1' for key 'idx'") 2659 tk.MustInterDirc("admin check causet t2") 2660 2661 tk.MustInterDirc("create causet t3(i int, j int, primary key(i))") 2662 tk.MustInterDirc("begin") 2663 tk.MustInterDirc("insert into t3 values(1, 3)") 2664 tk.MustInterDirc("delete from t3 where j = 3") 2665 tk.MustInterDirc("commit") 2666 2667 tk.MustInterDirc("create causet t4(i int, j int, primary key(i))") 2668 tk.MustInterDirc("begin") 2669 tk.MustInterDirc("insert into t4 values(1, 3)") 2670 tk.MustInterDirc("delete from t4 where j = 3") 2671 tk.MustInterDirc("insert into t4 values(2, 3)") 2672 tk.MustInterDirc("commit") 2673 tk.MustInterDirc("admin check causet t4") 2674 tk.MustQuery("select * from t4").Check(testkit.Rows("2 3")) 2675 2676 tk.MustInterDirc("create causet t5(i int, j int, primary key(i))") 2677 tk.MustInterDirc("begin") 2678 tk.MustInterDirc("insert into t5 values(1, 3)") 2679 tk.MustInterDirc("delete from t5 where j = 3") 2680 tk.MustInterDirc("insert into t5 values(1, 4)") 2681 tk.MustInterDirc("commit") 2682 tk.MustInterDirc("admin check causet t5") 2683 tk.MustQuery("select * from t5").Check(testkit.Rows("1 4")) 2684 } 2685 2686 func (s *testSuite7) TestDeferConstraintCheckForInsert(c *C) { 2687 tk := testkit.NewTestKit(c, s.causetstore) 2688 tk.MustInterDirc(`use test`) 2689 2690 tk.MustInterDirc(`drop causet if exists t;create causet t (a int primary key, b int);`) 2691 tk.MustInterDirc(`insert into t values (1,2),(2,2)`) 2692 _, err := tk.InterDirc("uFIDelate t set a=a+1 where b=2") 2693 c.Assert(err, NotNil) 2694 2695 tk.MustInterDirc(`drop causet if exists t;create causet t (i int key);`) 2696 tk.MustInterDirc(`insert t values (1);`) 2697 tk.MustInterDirc(`set milevadb_constraint_check_in_place = 1;`) 2698 tk.MustInterDirc(`begin;`) 2699 _, err = tk.InterDirc(`insert t values (1);`) 2700 c.Assert(err, NotNil) 2701 tk.MustInterDirc(`uFIDelate t set i = 2 where i = 1;`) 2702 tk.MustInterDirc(`commit;`) 2703 tk.MustQuery(`select * from t;`).Check(testkit.Rows("2")) 2704 2705 tk.MustInterDirc(`set milevadb_constraint_check_in_place = 0;`) 2706 tk.MustInterDirc("replace into t values (1),(2)") 2707 tk.MustInterDirc("begin") 2708 _, err = tk.InterDirc("uFIDelate t set i = 2 where i = 1") 2709 c.Assert(err, NotNil) 2710 _, err = tk.InterDirc("insert into t values (1) on duplicate key uFIDelate i = i + 1") 2711 c.Assert(err, NotNil) 2712 tk.MustInterDirc("rollback") 2713 2714 tk.MustInterDirc(`drop causet t; create causet t (id int primary key, v int unique);`) 2715 tk.MustInterDirc(`insert into t values (1, 1)`) 2716 tk.MustInterDirc(`set milevadb_constraint_check_in_place = 1;`) 2717 tk.MustInterDirc(`set @@autocommit = 0;`) 2718 2719 _, err = tk.InterDirc("insert into t values (3, 1)") 2720 c.Assert(err, NotNil) 2721 _, err = tk.InterDirc("insert into t values (1, 3)") 2722 c.Assert(err, NotNil) 2723 tk.MustInterDirc("commit") 2724 2725 tk.MustInterDirc(`set milevadb_constraint_check_in_place = 0;`) 2726 tk.MustInterDirc("insert into t values (3, 1)") 2727 tk.MustInterDirc("insert into t values (1, 3)") 2728 _, err = tk.InterDirc("commit") 2729 c.Assert(err, NotNil) 2730 } 2731 2732 func (s *testSuite7) TestPessimisticDeleteYourWrites(c *C) { 2733 stochastik1 := testkit.NewTestKitWithInit(c, s.causetstore) 2734 stochastik2 := testkit.NewTestKitWithInit(c, s.causetstore) 2735 2736 stochastik1.MustInterDirc("drop causet if exists x;") 2737 stochastik1.MustInterDirc("create causet x (id int primary key, c int);") 2738 2739 stochastik1.MustInterDirc("set milevadb_txn_mode = 'pessimistic'") 2740 stochastik2.MustInterDirc("set milevadb_txn_mode = 'pessimistic'") 2741 2742 stochastik1.MustInterDirc("begin;") 2743 stochastik1.MustInterDirc("insert into x select 1, 1") 2744 stochastik1.MustInterDirc("delete from x where id = 1") 2745 stochastik2.MustInterDirc("begin;") 2746 var wg sync.WaitGroup 2747 wg.Add(1) 2748 go func() { 2749 stochastik2.MustInterDirc("insert into x select 1, 2") 2750 wg.Done() 2751 }() 2752 stochastik1.MustInterDirc("commit;") 2753 wg.Wait() 2754 stochastik2.MustInterDirc("commit;") 2755 stochastik2.MustQuery("select * from x").Check(testkit.Rows("1 2")) 2756 } 2757 2758 func (s *testSuite7) TestDefEnumInsert(c *C) { 2759 tk := testkit.NewTestKit(c, s.causetstore) 2760 tk.MustInterDirc("use test") 2761 tk.MustInterDirc("create causet test (id int, prescription_type enum('a','b','c','d','e','f') NOT NULL, primary key(id));") 2762 tk.MustInterDirc("insert into test (id) values (1)") 2763 tk.MustQuery("select prescription_type from test").Check(testkit.Rows("a")) 2764 } 2765 2766 func (s *testSuite7) TestIssue11059(c *C) { 2767 tk := testkit.NewTestKitWithInit(c, s.causetstore) 2768 tk.MustInterDirc("create causet t (pk int primary key, uk int unique, v int)") 2769 tk.MustInterDirc("insert into t values (2, 11, 215)") 2770 tk.MustInterDirc("insert into t values (3, 7, 2111)") 2771 _, err := tk.InterDirc("uFIDelate t set pk = 2 where uk = 7") 2772 c.Assert(err, NotNil) 2773 } 2774 2775 func (s *testSuite7) TestSetWithRefGenDefCaus(c *C) { 2776 tk := testkit.NewTestKitWithInit(c, s.causetstore) 2777 tk.MustInterDirc("use test") 2778 tk.MustInterDirc(`create causet t (i int, j int as (i+1) not null);`) 2779 tk.MustInterDirc(`insert into t set i = j + 1;`) 2780 tk.MustQuery("select * from t").Check(testkit.Rows("1 2")) 2781 tk.MustInterDirc(`insert into t set i = j + 100;`) 2782 tk.MustQuery("select * from t").Check(testkit.Rows("1 2", "100 101")) 2783 2784 tk.MustInterDirc(`create causet te (i int)`) 2785 tk.MustInterDirc(`insert into te set i = i + 10;`) 2786 tk.MustQuery("select * from te").Check(testkit.Rows("<nil>")) 2787 tk.MustInterDirc(`insert into te set i = i;`) 2788 tk.MustQuery("select * from te").Check(testkit.Rows("<nil>", "<nil>")) 2789 2790 tk.MustInterDirc(`create causet tn (i int not null)`) 2791 tk.MustInterDirc(`insert into tn set i = i;`) 2792 tk.MustQuery("select * from tn").Check(testkit.Rows("0")) 2793 tk.MustInterDirc(`insert into tn set i = i + 10;`) 2794 tk.MustQuery("select * from tn").Check(testkit.Rows("0", "10")) 2795 2796 // 2797 tk.MustInterDirc(`create causet t1 (j int(11) GENERATED ALWAYS AS (i + 1) stored, i int(11) DEFAULT '10');`) 2798 tk.MustInterDirc(`insert into t1 values()`) 2799 tk.MustQuery("select * from t1").Check(testkit.Rows("11 10")) 2800 tk.MustInterDirc(`insert into t1 values()`) 2801 tk.MustQuery("select * from t1").Check(testkit.Rows("11 10", "11 10")) 2802 2803 tk.MustInterDirc(`create causet t2 (j int(11) GENERATED ALWAYS AS (i + 1) stored not null, i int(11) DEFAULT '5');`) 2804 tk.MustInterDirc(`insert into t2 set i = j + 9`) 2805 tk.MustQuery("select * from t2").Check(testkit.Rows("10 9")) 2806 _, err := tk.InterDirc(`insert into t2 set j = i + 1`) 2807 c.Assert(err, NotNil) 2808 tk.MustInterDirc(`insert into t2 set i = j + 100`) 2809 tk.MustQuery("select * from t2").Check(testkit.Rows("10 9", "101 100")) 2810 2811 tk.MustInterDirc(`create causet t3(j int(11) GENERATED ALWAYS AS (i + 1) stored, i int(11) DEFAULT '5');`) 2812 tk.MustInterDirc(`insert into t3 set i = j + 100`) 2813 tk.MustQuery("select * from t3").Check(testkit.Rows("<nil> <nil>")) 2814 _, err = tk.InterDirc(`insert into t3 set j = i + 1`) 2815 c.Assert(err, NotNil) 2816 } 2817 2818 func (s *testSuite7) TestSetWithCurrentTimestampAndNow(c *C) { 2819 tk := testkit.NewTestKitWithInit(c, s.causetstore) 2820 tk.MustInterDirc("use test") 2821 tk.MustInterDirc(`drop causet if exists tbl;`) 2822 tk.MustInterDirc(`create causet t1(c1 timestamp default current_timestamp, c2 int, c3 timestamp default current_timestamp);`) 2823 //c1 insert using now() function result, c3 using default value calculation, should be same 2824 tk.MustInterDirc(`insert into t1 set c1 = current_timestamp, c2 = sleep(2);`) 2825 tk.MustQuery("select c1 = c3 from t1").Check(testkit.Rows("1")) 2826 tk.MustInterDirc(`insert into t1 set c1 = current_timestamp, c2 = sleep(1);`) 2827 tk.MustQuery("select c1 = c3 from t1").Check(testkit.Rows("1", "1")) 2828 } 2829 2830 func (s *testSuite7) TestApplyWithPointAndBatchPointGet(c *C) { 2831 tk := testkit.NewTestKitWithInit(c, s.causetstore) 2832 tk.MustInterDirc("use test") 2833 tk.MustInterDirc(`drop causet if exists t;`) 2834 tk.MustInterDirc(`create causet t ( c_int int, c_str varchar(40),c_datetime datetime, c_timestamp timestamp, 2835 c_double double, c_decimal decimal(12, 6) , primary key(c_int, c_str) , unique key(c_int) , unique key(c_str) , 2836 unique key(c_decimal) , unique key(c_datetime) , key(c_timestamp) );`) 2837 tk.MustInterDirc(`insert into t values (1, 'zen ardinghelli', '2020-02-03 18:15:17', '2020-03-11 05:47:11', 36.226534, 3.763), 2838 (2, 'suspicious joliot', '2020-01-01 22:56:37', '2020-04-07 06:19:07', 62.756537, 5.567), 2839 (3, 'keen zhukovsky', '2020-01-21 04:09:20', '2020-06-06 08:32:14', 33.115065, 1.381), 2840 (4, 'crazy newton', '2020-02-14 21:37:56', '2020-04-28 08:33:48', 44.146318, 4.249), 2841 (5, 'happy black', '2020-03-12 16:04:14', '2020-01-18 09:17:37', 41.962653, 5.959);`) 2842 tk.MustInterDirc(`insert into t values (6, 'vigilant swartz', '2020-06-01 07:37:44', '2020-05-25 01:26:43', 56.352233, 2.202), 2843 (7, 'suspicious germain', '2020-04-16 23:25:23', '2020-03-17 05:06:57', 55.897698, 3.460), 2844 (8, 'festive chandrasekhar', '2020-02-11 23:40:29', '2020-04-08 10:13:04', 77.565691, 0.540), 2845 (9, 'vigorous meninsky', '2020-02-17 10:03:17', '2020-01-02 15:02:02', 6.484815, 6.292), 2846 (10, 'heuristic moser', '2020-04-20 12:18:49', '2020-06-20 20:20:18', 28.023822, 2.765);`) 2847 tk.MustInterDirc(`insert into t values (11, 'sharp carver', '2020-03-01 11:23:41', '2020-03-23 17:59:05', 40.842442, 6.345), 2848 (12, 'trusting noether', '2020-03-28 06:42:34', '2020-01-27 15:33:40', 49.544658, 4.811), 2849 (13, 'objective ishizaka', '2020-01-28 17:30:55', '2020-04-02 17:45:39', 59.523930, 5.015), 2850 (14, 'sad rhodes', '2020-03-30 21:43:37', '2020-06-09 06:53:53', 87.295753, 2.413), 2851 (15, 'wonderful shockley', '2020-04-29 09:17:11', '2020-03-14 04:36:51', 6.778588, 8.497);`) 2852 tk.MustInterDirc("begin pessimistic") 2853 tk.MustInterDirc(`insert into t values (13, 'vibrant yalow', '2020-05-15 06:59:05', '2020-05-03 05:58:45', 43.721929, 8.066), 2854 (14, 'xenodochial spence', '2020-02-13 17:28:07', '2020-04-01 12:18:30', 19.981331, 5.774), 2855 (22, 'eloquent neumann', '2020-02-10 16:00:20', '2020-03-28 00:24:42', 10.702532, 7.618) 2856 on duplicate key uFIDelate c_int=values(c_int), c_str=values(c_str), c_double=values(c_double), c_timestamp=values(c_timestamp);`) 2857 // Test pointGet. 2858 tk.MustQuery(`select sum((select t1.c_str from t t1 where t1.c_int = 11 and t1.c_str > t.c_str order by t1.c_decimal limit 1) is null) nulls 2859 from t order by c_str;`).Check(testkit.Rows("10")) 2860 // Test batchPointGet 2861 tk.MustQuery(`select sum((select t1.c_str from t t1 where t1.c_int in (11, 10086) and t1.c_str > t.c_str order by t1.c_decimal limit 1) is null) nulls 2862 from t order by c_str;`).Check(testkit.Rows("10")) 2863 tk.MustInterDirc("commit") 2864 tk.MustQuery(`select sum((select t1.c_str from t t1 where t1.c_int = 11 and t1.c_str > t.c_str order by t1.c_decimal limit 1) is null) nulls 2865 from t order by c_str;`).Check(testkit.Rows("10")) 2866 // Test batchPointGet 2867 tk.MustQuery(`select sum((select t1.c_str from t t1 where t1.c_int in (11, 10086) and t1.c_str > t.c_str order by t1.c_decimal limit 1) is null) nulls 2868 from t order by c_str;`).Check(testkit.Rows("10")) 2869 }