github.com/insionng/yougam@v0.0.0-20170714101924-2bc18d833463/libraries/pingcap/tidb/session_test.go (about) 1 // Copyright 2015 PingCAP, 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 tidb 15 16 import ( 17 "fmt" 18 "strings" 19 "sync" 20 "sync/atomic" 21 "time" 22 23 "github.com/insionng/yougam/libraries/ngaut/log" 24 . "github.com/insionng/yougam/libraries/pingcap/check" 25 "github.com/insionng/yougam/libraries/pingcap/tidb/context" 26 "github.com/insionng/yougam/libraries/pingcap/tidb/executor" 27 "github.com/insionng/yougam/libraries/pingcap/tidb/kv" 28 "github.com/insionng/yougam/libraries/pingcap/tidb/mysql" 29 "github.com/insionng/yougam/libraries/pingcap/tidb/optimizer" 30 "github.com/insionng/yougam/libraries/pingcap/tidb/optimizer/plan" 31 "github.com/insionng/yougam/libraries/pingcap/tidb/sessionctx" 32 "github.com/insionng/yougam/libraries/pingcap/tidb/sessionctx/autocommit" 33 "github.com/insionng/yougam/libraries/pingcap/tidb/sessionctx/variable" 34 "github.com/insionng/yougam/libraries/pingcap/tidb/terror" 35 "github.com/insionng/yougam/libraries/pingcap/tidb/util/testleak" 36 ) 37 38 var _ = Suite(&testSessionSuite{}) 39 40 type testSessionSuite struct { 41 dbName string 42 dbNameBootstrap string 43 44 createDBSQL string 45 dropDBSQL string 46 useDBSQL string 47 createTableSQL string 48 dropTableSQL string 49 selectSQL string 50 } 51 52 func (s *testSessionSuite) SetUpSuite(c *C) { 53 s.dbName = "test_session_db" 54 s.dbNameBootstrap = "test_main_db_bootstrap" 55 s.createDBSQL = fmt.Sprintf("create database if not exists %s;", s.dbName) 56 s.dropDBSQL = fmt.Sprintf("drop database %s;", s.dbName) 57 s.useDBSQL = fmt.Sprintf("use %s;", s.dbName) 58 s.dropTableSQL = `Drop TABLE if exists t;` 59 s.createTableSQL = `CREATE TABLE t(id TEXT);` 60 s.selectSQL = `SELECT * from t;` 61 log.SetLevelByString("error") 62 } 63 64 func (s *testSessionSuite) TearDownSuite(c *C) { 65 removeStore(c, s.dbName) 66 } 67 68 func (s *testSessionSuite) TestPrepare(c *C) { 69 defer testleak.AfterTest(c)() 70 store := newStore(c, s.dbName) 71 se := newSession(c, store, s.dbName) 72 // create table 73 mustExecSQL(c, se, s.dropTableSQL) 74 mustExecSQL(c, se, s.createTableSQL) 75 // insert data 76 mustExecSQL(c, se, `INSERT INTO t VALUES ("id");`) 77 id, ps, _, err := se.PrepareStmt("select id+? from t") 78 c.Assert(err, IsNil) 79 c.Assert(id, Equals, uint32(1)) 80 c.Assert(ps, Equals, 1) 81 mustExecSQL(c, se, `set @a=1`) 82 _, err = se.ExecutePreparedStmt(id, "1") 83 c.Assert(err, IsNil) 84 err = se.DropPreparedStmt(id) 85 c.Assert(err, IsNil) 86 mustExecSQL(c, se, s.dropDBSQL) 87 88 mustExecSQL(c, se, "prepare stmt from 'select 1+?'") 89 mustExecSQL(c, se, "set @v1=100") 90 rs := mustExecSQL(c, se, "execute stmt using @v1") 91 r, err := rs.Next() 92 c.Assert(err, IsNil) 93 c.Assert(r.Data[0].GetFloat64(), Equals, float64(101)) 94 95 mustExecSQL(c, se, "set @v2=200") 96 rs = mustExecSQL(c, se, "execute stmt using @v2") 97 r, err = rs.Next() 98 c.Assert(err, IsNil) 99 c.Assert(r.Data[0].GetFloat64(), Equals, float64(201)) 100 101 mustExecSQL(c, se, "set @v3=300") 102 rs = mustExecSQL(c, se, "execute stmt using @v3") 103 r, err = rs.Next() 104 c.Assert(err, IsNil) 105 c.Assert(r.Data[0].GetFloat64(), Equals, float64(301)) 106 mustExecSQL(c, se, "deallocate prepare stmt") 107 108 err = store.Close() 109 c.Assert(err, IsNil) 110 } 111 112 func (s *testSessionSuite) TestAffectedRows(c *C) { 113 defer testleak.AfterTest(c)() 114 store := newStore(c, s.dbName) 115 se := newSession(c, store, s.dbName) 116 mustExecSQL(c, se, s.dropTableSQL) 117 mustExecSQL(c, se, s.createTableSQL) 118 mustExecSQL(c, se, `INSERT INTO t VALUES ("a");`) 119 c.Assert(int(se.AffectedRows()), Equals, 1) 120 mustExecSQL(c, se, `INSERT INTO t VALUES ("b");`) 121 c.Assert(int(se.AffectedRows()), Equals, 1) 122 mustExecSQL(c, se, `UPDATE t set id = 'c' where id = 'a';`) 123 c.Assert(int(se.AffectedRows()), Equals, 1) 124 mustExecSQL(c, se, `UPDATE t set id = 'a' where id = 'a';`) 125 c.Assert(int(se.AffectedRows()), Equals, 0) 126 mustExecSQL(c, se, `SELECT * from t;`) 127 c.Assert(int(se.AffectedRows()), Equals, 0) 128 129 mustExecSQL(c, se, s.dropTableSQL) 130 mustExecSQL(c, se, "create table t (id int, data int)") 131 mustExecSQL(c, se, `INSERT INTO t VALUES (1, 0), (0, 0), (1, 1);`) 132 mustExecSQL(c, se, `UPDATE t set id = 1 where data = 0;`) 133 c.Assert(int(se.AffectedRows()), Equals, 1) 134 135 mustExecSQL(c, se, s.dropTableSQL) 136 mustExecSQL(c, se, "create table t (id int, c1 timestamp);") 137 mustExecSQL(c, se, `insert t values(1, 0);`) 138 mustExecSQL(c, se, `UPDATE t set id = 1 where id = 1;`) 139 c.Assert(int(se.AffectedRows()), Equals, 0) 140 141 // With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 142 // 2 if an existing row is updated, and 0 if an existing row is set to its current values. 143 mustExecSQL(c, se, s.dropTableSQL) 144 mustExecSQL(c, se, "create table t (c1 int PRIMARY KEY, c2 int);") 145 mustExecSQL(c, se, `insert t values(1, 1);`) 146 mustExecSQL(c, se, `insert into t values (1, 1) on duplicate key update c2=2;`) 147 c.Assert(int(se.AffectedRows()), Equals, 2) 148 mustExecSQL(c, se, `insert into t values (1, 1) on duplicate key update c2=2;`) 149 c.Assert(int(se.AffectedRows()), Equals, 0) 150 151 se.SetClientCapability(mysql.ClientFoundRows) 152 mustExecSQL(c, se, s.dropTableSQL) 153 mustExecSQL(c, se, "create table t (id int, data int)") 154 mustExecSQL(c, se, `INSERT INTO t VALUES (1, 0), (0, 0), (1, 1);`) 155 mustExecSQL(c, se, `UPDATE t set id = 1 where data = 0;`) 156 c.Assert(int(se.AffectedRows()), Equals, 2) 157 158 sessionExec(c, se, s.dropDBSQL) 159 err := store.Close() 160 c.Assert(err, IsNil) 161 } 162 163 func (s *testSessionSuite) TestString(c *C) { 164 defer testleak.AfterTest(c)() 165 store := newStore(c, s.dbName) 166 se := newSession(c, store, s.dbName) 167 sessionExec(c, se, "select 1") 168 // here to check the panic bug in String() when txn is nil after committed. 169 c.Log(se.String()) 170 171 err := store.Close() 172 c.Assert(err, IsNil) 173 } 174 175 func (s *testSessionSuite) TestResultField(c *C) { 176 defer testleak.AfterTest(c)() 177 store := newStore(c, s.dbName) 178 se := newSession(c, store, s.dbName) 179 // create table 180 mustExecSQL(c, se, s.dropTableSQL) 181 mustExecSQL(c, se, "create table t (id int);") 182 183 mustExecSQL(c, se, `INSERT INTO t VALUES (1);`) 184 mustExecSQL(c, se, `INSERT INTO t VALUES (2);`) 185 r := mustExecSQL(c, se, `SELECT count(*) from t;`) 186 c.Assert(r, NotNil) 187 _, err := GetRows(r) 188 c.Assert(err, IsNil) 189 fields, err := r.Fields() 190 c.Assert(err, IsNil) 191 c.Assert(len(fields), Equals, 1) 192 field := fields[0].Column 193 c.Assert(field.Tp, Equals, mysql.TypeLonglong) 194 c.Assert(field.Flen, Equals, 21) 195 mustExecSQL(c, se, s.dropDBSQL) 196 197 err = store.Close() 198 c.Assert(err, IsNil) 199 } 200 201 func (s *testSessionSuite) TestPrimaryKeyAutoincrement(c *C) { 202 defer testleak.AfterTest(c)() 203 store := newStore(c, s.dbName) 204 se := newSession(c, store, s.dbName) 205 mustExecSQL(c, se, "drop table if exists t") 206 mustExecSQL(c, se, "create table t (id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL, name varchar(255) UNIQUE NOT NULL, status int)") 207 mustExecSQL(c, se, "insert t (name) values (?)", "abc") 208 id := se.LastInsertID() 209 c.Check(id != 0, IsTrue) 210 211 se2 := newSession(c, store, s.dbName) 212 rs := mustExecSQL(c, se2, "select * from t") 213 c.Assert(rs, NotNil) 214 row, err := rs.Next() 215 c.Assert(err, IsNil) 216 match(c, row.Data, id, []byte("abc"), nil) 217 218 mustExecSQL(c, se, "update t set name = 'abc', status = 1 where id = ?", id) 219 rs = mustExecSQL(c, se2, "select * from t") 220 c.Assert(rs, NotNil) 221 row, err = rs.Next() 222 c.Assert(err, IsNil) 223 match(c, row.Data, id, []byte("abc"), 1) 224 // Check for pass bool param to tidb prepared statement 225 mustExecSQL(c, se, "drop table if exists t") 226 mustExecSQL(c, se, "create table t (id tiny)") 227 mustExecSQL(c, se, "insert t values (?)", true) 228 rs = mustExecSQL(c, se, "select * from t") 229 c.Assert(rs, NotNil) 230 row, err = rs.Next() 231 c.Assert(err, IsNil) 232 match(c, row.Data, int8(1)) 233 234 mustExecSQL(c, se, s.dropDBSQL) 235 err = store.Close() 236 c.Assert(err, IsNil) 237 } 238 239 func (s *testSessionSuite) TestAutoincrementID(c *C) { 240 defer testleak.AfterTest(c)() 241 store := newStore(c, s.dbName) 242 se := newSession(c, store, s.dbName) 243 mustExecSQL(c, se, "drop table if exists t") 244 mustExecSQL(c, se, "create table t (id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL)") 245 mustExecSQL(c, se, "insert t values ()") 246 mustExecSQL(c, se, "insert t values ()") 247 mustExecSQL(c, se, "insert t values ()") 248 se.Execute("drop table if exists t;") 249 mustExecSQL(c, se, "create table t (id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL)") 250 mustExecSQL(c, se, "insert t values ()") 251 lastID := se.LastInsertID() 252 c.Assert(lastID, Less, uint64(4)) 253 mustExecSQL(c, se, "insert t () values ()") 254 c.Assert(se.LastInsertID(), Greater, lastID) 255 mustExecSQL(c, se, "insert t () select 100") 256 mustExecSQL(c, se, s.dropDBSQL) 257 258 err := store.Close() 259 c.Assert(err, IsNil) 260 } 261 262 func checkTxn(c *C, se Session, stmt string, expect uint16) { 263 mustExecSQL(c, se, stmt) 264 if expect == 0 { 265 c.Assert(se.(*session).txn, IsNil) 266 return 267 } 268 c.Assert(se.(*session).txn, NotNil) 269 } 270 271 func checkAutocommit(c *C, se Session, expect uint16) { 272 ret := variable.GetSessionVars(se.(*session)).Status & mysql.ServerStatusAutocommit 273 c.Assert(ret, Equals, expect) 274 } 275 276 // See: https://dev.mysql.com/doc/internals/en/status-flags.html 277 func (s *testSessionSuite) TestAutocommit(c *C) { 278 defer testleak.AfterTest(c)() 279 store := newStore(c, s.dbName) 280 se := newSession(c, store, s.dbName) 281 checkTxn(c, se, "drop table if exists t;", 0) 282 checkAutocommit(c, se, 2) 283 checkTxn(c, se, "create table t (id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL)", 0) 284 checkAutocommit(c, se, 2) 285 checkTxn(c, se, "insert t values ()", 0) 286 checkAutocommit(c, se, 2) 287 checkTxn(c, se, "begin", 1) 288 checkAutocommit(c, se, 2) 289 checkTxn(c, se, "insert t values ()", 1) 290 checkAutocommit(c, se, 2) 291 checkTxn(c, se, "drop table if exists t;", 0) 292 checkAutocommit(c, se, 2) 293 294 checkTxn(c, se, "create table t (id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL)", 0) 295 checkAutocommit(c, se, 2) 296 checkTxn(c, se, "set autocommit=0;", 0) 297 checkAutocommit(c, se, 0) 298 checkTxn(c, se, "insert t values ()", 1) 299 checkAutocommit(c, se, 0) 300 checkTxn(c, se, "commit", 0) 301 checkAutocommit(c, se, 0) 302 checkTxn(c, se, "drop table if exists t;", 0) 303 checkAutocommit(c, se, 0) 304 checkTxn(c, se, "set autocommit=1;", 0) 305 checkAutocommit(c, se, 2) 306 307 mustExecSQL(c, se, s.dropDBSQL) 308 err := store.Close() 309 c.Assert(err, IsNil) 310 } 311 312 func checkInTrans(c *C, se Session, stmt string, expect uint16) { 313 checkTxn(c, se, stmt, expect) 314 ret := variable.GetSessionVars(se.(*session)).Status & mysql.ServerStatusInTrans 315 c.Assert(ret, Equals, expect) 316 } 317 318 // See: https://dev.mysql.com/doc/internals/en/status-flags.html 319 func (s *testSessionSuite) TestInTrans(c *C) { 320 defer testleak.AfterTest(c)() 321 store := newStore(c, s.dbName) 322 se := newSession(c, store, s.dbName) 323 checkInTrans(c, se, "drop table if exists t;", 0) 324 checkInTrans(c, se, "create table t (id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL)", 0) 325 checkInTrans(c, se, "insert t values ()", 0) 326 checkInTrans(c, se, "begin", 1) 327 checkInTrans(c, se, "insert t values ()", 1) 328 checkInTrans(c, se, "drop table if exists t;", 0) 329 checkInTrans(c, se, "create table t (id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL)", 0) 330 checkInTrans(c, se, "insert t values ()", 0) 331 checkInTrans(c, se, "commit", 0) 332 checkInTrans(c, se, "insert t values ()", 0) 333 334 checkInTrans(c, se, "set autocommit=0;", 0) 335 checkInTrans(c, se, "begin", 1) 336 checkInTrans(c, se, "insert t values ()", 1) 337 checkInTrans(c, se, "commit", 0) 338 checkInTrans(c, se, "insert t values ()", 1) 339 checkInTrans(c, se, "commit", 0) 340 341 checkInTrans(c, se, "set autocommit=1;", 0) 342 checkInTrans(c, se, "drop table if exists t;", 0) 343 checkInTrans(c, se, "create table t (id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL)", 0) 344 checkInTrans(c, se, "begin", 1) 345 checkInTrans(c, se, "insert t values ()", 1) 346 checkInTrans(c, se, "rollback", 0) 347 348 mustExecSQL(c, se, s.dropDBSQL) 349 err := store.Close() 350 c.Assert(err, IsNil) 351 } 352 353 // See: http://dev.mysql.com/doc/refman/5.7/en/commit.html 354 func (s *testSessionSuite) TestRowLock(c *C) { 355 defer testleak.AfterTest(c)() 356 store := newStore(c, s.dbName) 357 se := newSession(c, store, s.dbName) 358 se1 := newSession(c, store, s.dbName) 359 se2 := newSession(c, store, s.dbName) 360 361 mustExecSQL(c, se, "drop table if exists t") 362 c.Assert(se.(*session).txn, IsNil) 363 mustExecSQL(c, se, "create table t (c1 int, c2 int, c3 int)") 364 mustExecSQL(c, se, "insert t values (11, 2, 3)") 365 mustExecSQL(c, se, "insert t values (12, 2, 3)") 366 mustExecSQL(c, se, "insert t values (13, 2, 3)") 367 368 mustExecSQL(c, se1, "begin") 369 mustExecSQL(c, se1, "update t set c2=21 where c1=11") 370 371 mustExecSQL(c, se2, "begin") 372 mustExecSQL(c, se2, "update t set c2=211 where c1=11") 373 mustExecSQL(c, se2, "commit") 374 375 _, err := exec(c, se1, "commit") 376 // se1 will retry and the final value is 21 377 c.Assert(err, IsNil) 378 // Check the result is correct 379 se3 := newSession(c, store, s.dbName) 380 r := mustExecSQL(c, se3, "select c2 from t where c1=11") 381 rows, err := GetRows(r) 382 fmt.Println(rows) 383 matches(c, rows, [][]interface{}{{21}}) 384 385 mustExecSQL(c, se1, "begin") 386 mustExecSQL(c, se1, "update t set c2=21 where c1=11") 387 388 mustExecSQL(c, se2, "begin") 389 mustExecSQL(c, se2, "update t set c2=22 where c1=12") 390 mustExecSQL(c, se2, "commit") 391 392 mustExecSQL(c, se1, "commit") 393 394 mustExecSQL(c, se, s.dropDBSQL) 395 err = store.Close() 396 c.Assert(err, IsNil) 397 } 398 399 func (s *testSessionSuite) TestIssue827(c *C) { 400 defer testleak.AfterTest(c)() 401 store := newStore(c, s.dbName) 402 se := newSession(c, store, s.dbName) 403 se1 := newSession(c, store, s.dbName) 404 405 mustExecSQL(c, se, "drop table if exists t1") 406 c.Assert(se.(*session).txn, IsNil) 407 mustExecSQL(c, se, "create table t1 (c2 int, c3 int, c1 int not null auto_increment, PRIMARY KEY (c1))") 408 mustExecSQL(c, se, "insert into t1 set c2 = 30") 409 410 mustExecSQL(c, se, "drop table if exists t") 411 c.Assert(se.(*session).txn, IsNil) 412 mustExecSQL(c, se, "create table t (c2 int, c1 int not null auto_increment, PRIMARY KEY (c1))") 413 mustExecSQL(c, se, "insert into t (c2) values (1), (2), (3), (4), (5)") 414 415 // insert values 416 lastInsertID := se.LastInsertID() 417 mustExecSQL(c, se, "begin") 418 mustExecSQL(c, se, "insert into t (c2) values (11), (12), (13)") 419 rs, err := exec(c, se, "select c1 from t where c2 = 11") 420 c.Assert(err, IsNil) 421 expect, err := GetRows(rs) 422 c.Assert(err, IsNil) 423 _, err = exec(c, se, "update t set c2 = 33 where c2 = 1") 424 c.Assert(err, IsNil) 425 426 mustExecSQL(c, se1, "begin") 427 mustExecSQL(c, se1, "update t set c2 = 22 where c2 = 1") 428 mustExecSQL(c, se1, "commit") 429 430 _, err = exec(c, se, "commit") 431 c.Assert(err, IsNil) 432 433 rs, err = exec(c, se, "select c1 from t where c2 = 11") 434 c.Assert(err, IsNil) 435 r, err := GetRows(rs) 436 c.Assert(err, IsNil) 437 c.Assert(r, DeepEquals, expect) 438 currLastInsertID := se.LastInsertID() 439 c.Assert(lastInsertID+3, Equals, currLastInsertID) 440 441 // insert set 442 lastInsertID = se.LastInsertID() 443 mustExecSQL(c, se, "begin") 444 mustExecSQL(c, se, "insert into t set c2 = 31") 445 rs, err = exec(c, se, "select c1 from t where c2 = 31") 446 c.Assert(err, IsNil) 447 expect, err = GetRows(rs) 448 c.Assert(err, IsNil) 449 _, err = exec(c, se, "update t set c2 = 44 where c2 = 2") 450 c.Assert(err, IsNil) 451 452 mustExecSQL(c, se1, "begin") 453 mustExecSQL(c, se1, "update t set c2 = 55 where c2 = 2") 454 mustExecSQL(c, se1, "commit") 455 456 _, err = exec(c, se, "commit") 457 c.Assert(err, IsNil) 458 459 rs, err = exec(c, se, "select c1 from t where c2 = 31") 460 c.Assert(err, IsNil) 461 r, err = GetRows(rs) 462 c.Assert(err, IsNil) 463 c.Assert(r, DeepEquals, expect) 464 currLastInsertID = se.LastInsertID() 465 c.Assert(lastInsertID+1, Equals, currLastInsertID) 466 467 // replace 468 lastInsertID = se.LastInsertID() 469 mustExecSQL(c, se, "begin") 470 mustExecSQL(c, se, "insert into t (c2) values (21), (22), (23)") 471 rs, err = exec(c, se, "select c1 from t where c2 = 21") 472 c.Assert(err, IsNil) 473 expect, err = GetRows(rs) 474 c.Assert(err, IsNil) 475 _, err = exec(c, se, "update t set c2 = 66 where c2 = 3") 476 c.Assert(err, IsNil) 477 478 mustExecSQL(c, se1, "begin") 479 mustExecSQL(c, se1, "update t set c2 = 77 where c2 = 3") 480 mustExecSQL(c, se1, "commit") 481 482 _, err = exec(c, se, "commit") 483 c.Assert(err, IsNil) 484 485 rs, err = exec(c, se, "select c1 from t where c2 = 21") 486 c.Assert(err, IsNil) 487 r, err = GetRows(rs) 488 c.Assert(err, IsNil) 489 c.Assert(r, DeepEquals, expect) 490 currLastInsertID = se.LastInsertID() 491 c.Assert(lastInsertID+3, Equals, currLastInsertID) 492 493 // update 494 lastInsertID = se.LastInsertID() 495 mustExecSQL(c, se, "begin") 496 mustExecSQL(c, se, "insert into t set c2 = 41") 497 mustExecSQL(c, se, "update t set c1 = 0 where c2 = 41") 498 rs, err = exec(c, se, "select c1 from t where c2 = 41") 499 c.Assert(err, IsNil) 500 expect, err = GetRows(rs) 501 c.Assert(err, IsNil) 502 _, err = exec(c, se, "update t set c2 = 88 where c2 = 4") 503 c.Assert(err, IsNil) 504 505 mustExecSQL(c, se1, "begin") 506 mustExecSQL(c, se1, "update t set c2 = 99 where c2 = 4") 507 mustExecSQL(c, se1, "commit") 508 509 _, err = exec(c, se, "commit") 510 c.Assert(err, IsNil) 511 512 rs, err = exec(c, se, "select c1 from t where c2 = 41") 513 c.Assert(err, IsNil) 514 r, err = GetRows(rs) 515 c.Assert(err, IsNil) 516 c.Assert(r, DeepEquals, expect) 517 currLastInsertID = se.LastInsertID() 518 c.Assert(lastInsertID+1, Equals, currLastInsertID) 519 520 // prepare 521 lastInsertID = se.LastInsertID() 522 mustExecSQL(c, se, "begin") 523 mustExecSQL(c, se, "prepare stmt from 'insert into t (c2) values (?)'") 524 mustExecSQL(c, se, "set @v1=100") 525 mustExecSQL(c, se, "set @v2=200") 526 mustExecSQL(c, se, "set @v3=300") 527 mustExecSQL(c, se, "execute stmt using @v1") 528 mustExecSQL(c, se, "execute stmt using @v2") 529 mustExecSQL(c, se, "execute stmt using @v3") 530 mustExecSQL(c, se, "deallocate prepare stmt") 531 rs, err = exec(c, se, "select c1 from t where c2 = 12") 532 c.Assert(err, IsNil) 533 expect, err = GetRows(rs) 534 c.Assert(err, IsNil) 535 _, err = exec(c, se, "update t set c2 = 111 where c2 = 5") 536 c.Assert(err, IsNil) 537 538 mustExecSQL(c, se1, "begin") 539 mustExecSQL(c, se1, "update t set c2 = 222 where c2 = 5") 540 mustExecSQL(c, se1, "commit") 541 542 _, err = exec(c, se, "commit") 543 c.Assert(err, IsNil) 544 545 rs, err = exec(c, se, "select c1 from t where c2 = 12") 546 c.Assert(err, IsNil) 547 r, err = GetRows(rs) 548 c.Assert(err, IsNil) 549 c.Assert(r, DeepEquals, expect) 550 currLastInsertID = se.LastInsertID() 551 c.Assert(lastInsertID+3, Equals, currLastInsertID) 552 553 mustExecSQL(c, se, s.dropDBSQL) 554 err = se.Close() 555 c.Assert(err, IsNil) 556 err = se1.Close() 557 c.Assert(err, IsNil) 558 err = store.Close() 559 c.Assert(err, IsNil) 560 } 561 562 func (s *testSessionSuite) TestIssue996(c *C) { 563 defer testleak.AfterTest(c)() 564 store := newStore(c, s.dbName) 565 se := newSession(c, store, s.dbName) 566 567 mustExecSQL(c, se, "drop table if exists t") 568 c.Assert(se.(*session).txn, IsNil) 569 mustExecSQL(c, se, "create table t (c2 int, c3 int, c1 int not null auto_increment, PRIMARY KEY (c1))") 570 mustExecSQL(c, se, "insert into t set c2 = 30") 571 572 // insert values 573 lastInsertID := se.LastInsertID() 574 mustExecSQL(c, se, "prepare stmt1 from 'insert into t (c2) values (?)'") 575 mustExecSQL(c, se, "set @v1=10") 576 mustExecSQL(c, se, "set @v2=20") 577 mustExecSQL(c, se, "execute stmt1 using @v1") 578 mustExecSQL(c, se, "execute stmt1 using @v2") 579 mustExecSQL(c, se, "deallocate prepare stmt1") 580 rs, err := exec(c, se, "select c1 from t where c2 = 20") 581 c.Assert(err, IsNil) 582 r, err := GetRows(rs) 583 c.Assert(err, IsNil) 584 c.Assert(r, NotNil) 585 currLastInsertID := se.LastInsertID() 586 c.Assert(r[0][0].GetValue(), DeepEquals, int64(currLastInsertID)) 587 c.Assert(lastInsertID+2, Equals, currLastInsertID) 588 589 err = store.Close() 590 c.Assert(err, IsNil) 591 } 592 593 func (s *testSessionSuite) TestIssue986(c *C) { 594 defer testleak.AfterTest(c)() 595 sqlText := `CREATE TABLE address ( 596 id bigint(20) NOT NULL AUTO_INCREMENT, 597 PRIMARY KEY (id));` 598 store := newStore(c, s.dbName) 599 se := newSession(c, store, s.dbName) 600 mustExecSQL(c, se, sqlText) 601 sqlText = `insert into address values ('10')` 602 mustExecSQL(c, se, sqlText) 603 604 err := store.Close() 605 c.Assert(err, IsNil) 606 } 607 608 func (s *testSessionSuite) TestIssue1089(c *C) { 609 defer testleak.AfterTest(c)() 610 store := newStore(c, s.dbName) 611 se := newSession(c, store, s.dbName) 612 613 r := mustExecSQL(c, se, "select cast(0.5 as unsigned)") 614 row, err := r.Next() 615 c.Assert(err, IsNil) 616 match(c, row.Data, 1) 617 r = mustExecSQL(c, se, "select cast(-0.5 as signed)") 618 row, err = r.Next() 619 c.Assert(err, IsNil) 620 match(c, row.Data, -1) 621 622 err = store.Close() 623 c.Assert(err, IsNil) 624 } 625 626 func (s *testSessionSuite) TestIssue1135(c *C) { 627 defer testleak.AfterTest(c)() 628 store := newStore(c, s.dbName) 629 se := newSession(c, store, s.dbName) 630 se1 := newSession(c, store, s.dbName+"1") 631 632 mustExecSQL(c, se1, "drop table if exists t") 633 mustExecSQL(c, se1, "create table t (F1 VARCHAR(30));") 634 mustExecSQL(c, se1, "insert into t (F1) values ('1'), ('4');") 635 636 mustExecSQL(c, se, "drop table if exists t") 637 mustExecSQL(c, se, "create table t (F1 VARCHAR(30));") 638 mustExecSQL(c, se, "insert into t (F1) values ('1'), ('2');") 639 mustExecSQL(c, se, "delete m1 from t m2,t m1 where m1.F1>1;") 640 r := mustExecSQL(c, se, "select * from t;") 641 row, err := r.Next() 642 c.Assert(err, IsNil) 643 match(c, row.Data, []interface{}{'1'}) 644 645 mustExecSQL(c, se, "drop table if exists t") 646 mustExecSQL(c, se, "create table t (F1 VARCHAR(30));") 647 mustExecSQL(c, se, "insert into t (F1) values ('1'), ('2');") 648 mustExecSQL(c, se, "delete m1 from t m1,t m2 where true and m1.F1<2;") 649 r = mustExecSQL(c, se, "select * from t;") 650 row, err = r.Next() 651 c.Assert(err, IsNil) 652 match(c, row.Data, []interface{}{'2'}) 653 654 mustExecSQL(c, se, "drop table if exists t") 655 mustExecSQL(c, se, "create table t (F1 VARCHAR(30));") 656 mustExecSQL(c, se, "insert into t (F1) values ('1'), ('2');") 657 mustExecSQL(c, se, "delete m1 from t m1,t m2 where false;") 658 r = mustExecSQL(c, se, "select * from t;") 659 row, err = r.Next() 660 c.Assert(err, IsNil) 661 match(c, row.Data, []interface{}{'1'}) 662 row, err = r.Next() 663 c.Assert(err, IsNil) 664 match(c, row.Data, []interface{}{'2'}) 665 666 mustExecSQL(c, se, "drop table if exists t") 667 mustExecSQL(c, se, "create table t (F1 VARCHAR(30));") 668 mustExecSQL(c, se, "insert into t (F1) values ('1'), ('2');") 669 mustExecSQL(c, se, "delete m1, m2 from t m1,t m2 where m1.F1>m2.F1;") 670 r = mustExecSQL(c, se, "select * from t;") 671 row, err = r.Next() 672 c.Assert(err, IsNil) 673 c.Assert(row, IsNil) 674 675 mustExecSQL(c, se, "drop table if exists t") 676 mustExecSQL(c, se, "create table t (F1 VARCHAR(30));") 677 mustExecSQL(c, se, "insert into t (F1) values ('1'), ('2');") 678 sql := fmt.Sprintf("delete %s.t from %s.t inner join %s.t where %s.t.F1 > %s.t.F1", 679 s.dbName+"1", s.dbName+"1", s.dbName, s.dbName+"1", s.dbName) 680 mustExecSQL(c, se1, sql) 681 r = mustExecSQL(c, se1, "select * from t;") 682 row, err = r.Next() 683 c.Assert(err, IsNil) 684 match(c, row.Data, []interface{}{'1'}) 685 686 err = store.Close() 687 c.Assert(err, IsNil) 688 } 689 690 func (s *testSessionSuite) TestIssue1114(c *C) { 691 defer testleak.AfterTest(c)() 692 store := newStore(c, s.dbName) 693 se := newSession(c, store, s.dbName) 694 695 mustExecSQL(c, se, "set @tmp = 0") 696 mustExecSQL(c, se, "set @tmp := @tmp + 1") 697 r := mustExecSQL(c, se, "select @tmp") 698 row, err := r.Next() 699 c.Assert(err, IsNil) 700 match(c, row.Data, 1) 701 702 r = mustExecSQL(c, se, "select @tmp1 = 1, @tmp2 := 2") 703 row, err = r.Next() 704 c.Assert(err, IsNil) 705 match(c, row.Data, nil, 2) 706 707 r = mustExecSQL(c, se, "select @tmp1 := 11, @tmp2") 708 row, err = r.Next() 709 c.Assert(err, IsNil) 710 match(c, row.Data, 11, 2) 711 712 mustExecSQL(c, se, "drop table if exists t") 713 mustExecSQL(c, se, "create table t (c int);") 714 mustExecSQL(c, se, "insert into t values (1),(2);") 715 mustExecSQL(c, se, "update t set c = 3 WHERE c = @var:= 1") 716 r = mustExecSQL(c, se, "select * from t") 717 row, err = r.Next() 718 c.Assert(err, IsNil) 719 match(c, row.Data, 3) 720 row, err = r.Next() 721 c.Assert(err, IsNil) 722 match(c, row.Data, 2) 723 724 r = mustExecSQL(c, se, "select @tmp := count(*) from t") 725 row, err = r.Next() 726 c.Assert(err, IsNil) 727 match(c, row.Data, 2) 728 729 r = mustExecSQL(c, se, "select @tmp := c-2 from t where c=3") 730 row, err = r.Next() 731 c.Assert(err, IsNil) 732 match(c, row.Data, 1) 733 734 err = store.Close() 735 c.Assert(err, IsNil) 736 } 737 738 func (s *testSessionSuite) TestSelectForUpdate(c *C) { 739 defer testleak.AfterTest(c)() 740 store := newStore(c, s.dbName) 741 se := newSession(c, store, s.dbName) 742 se1 := newSession(c, store, s.dbName) 743 se2 := newSession(c, store, s.dbName) 744 745 mustExecSQL(c, se, "drop table if exists t") 746 c.Assert(se.(*session).txn, IsNil) 747 mustExecSQL(c, se, "create table t (c1 int, c2 int, c3 int)") 748 mustExecSQL(c, se, "insert t values (11, 2, 3)") 749 mustExecSQL(c, se, "insert t values (12, 2, 3)") 750 mustExecSQL(c, se, "insert t values (13, 2, 3)") 751 752 // conflict 753 mustExecSQL(c, se1, "begin") 754 rs, err := exec(c, se1, "select * from t where c1=11 for update") 755 c.Assert(err, IsNil) 756 _, err = GetRows(rs) 757 758 mustExecSQL(c, se2, "begin") 759 mustExecSQL(c, se2, "update t set c2=211 where c1=11") 760 mustExecSQL(c, se2, "commit") 761 762 _, err = exec(c, se1, "commit") 763 c.Assert(err, NotNil) 764 err = se1.Retry() 765 // retry should fail 766 c.Assert(err, NotNil) 767 768 // not conflict 769 mustExecSQL(c, se1, "begin") 770 rs, err = exec(c, se1, "select * from t where c1=11 for update") 771 _, err = GetRows(rs) 772 773 mustExecSQL(c, se2, "begin") 774 mustExecSQL(c, se2, "update t set c2=22 where c1=12") 775 mustExecSQL(c, se2, "commit") 776 777 mustExecSQL(c, se1, "commit") 778 779 // not conflict, auto commit 780 mustExecSQL(c, se1, "set @@autocommit=1;") 781 rs, err = exec(c, se1, "select * from t where c1=11 for update") 782 _, err = GetRows(rs) 783 784 mustExecSQL(c, se2, "begin") 785 mustExecSQL(c, se2, "update t set c2=211 where c1=11") 786 mustExecSQL(c, se2, "commit") 787 788 _, err = exec(c, se1, "commit") 789 c.Assert(err, IsNil) 790 791 mustExecSQL(c, se, s.dropDBSQL) 792 err = se.Close() 793 c.Assert(err, IsNil) 794 err = se1.Close() 795 c.Assert(err, IsNil) 796 err = se2.Close() 797 c.Assert(err, IsNil) 798 err = store.Close() 799 c.Assert(err, IsNil) 800 } 801 802 func (s *testSessionSuite) TestRow(c *C) { 803 defer testleak.AfterTest(c)() 804 store := newStore(c, s.dbName) 805 se := newSession(c, store, s.dbName) 806 807 r := mustExecSQL(c, se, "select row(1, 1) in (row(1, 1))") 808 row, err := r.Next() 809 c.Assert(err, IsNil) 810 match(c, row.Data, 1) 811 812 r = mustExecSQL(c, se, "select row(1, 1) in (row(1, 0))") 813 row, err = r.Next() 814 c.Assert(err, IsNil) 815 match(c, row.Data, 0) 816 817 r = mustExecSQL(c, se, "select row(1, 1) in (select 1, 1)") 818 row, err = r.Next() 819 c.Assert(err, IsNil) 820 match(c, row.Data, 1) 821 822 r = mustExecSQL(c, se, "select row(1, 1) > row(1, 0)") 823 row, err = r.Next() 824 c.Assert(err, IsNil) 825 match(c, row.Data, 1) 826 827 r = mustExecSQL(c, se, "select row(1, 1) > (select 1, 0)") 828 row, err = r.Next() 829 c.Assert(err, IsNil) 830 match(c, row.Data, 1) 831 832 r = mustExecSQL(c, se, "select 1 > (select 1)") 833 row, err = r.Next() 834 c.Assert(err, IsNil) 835 match(c, row.Data, 0) 836 837 r = mustExecSQL(c, se, "select (select 1)") 838 row, err = r.Next() 839 c.Assert(err, IsNil) 840 match(c, row.Data, 1) 841 842 err = store.Close() 843 c.Assert(err, IsNil) 844 } 845 846 func (s *testSessionSuite) TestIndex(c *C) { 847 defer testleak.AfterTest(c)() 848 store := newStore(c, s.dbName) 849 se := newSession(c, store, s.dbName) 850 851 mustExecSQL(c, se, "create table if not exists test_index (c1 int, c double, index(c1), index(c))") 852 mustExecSQL(c, se, "insert into test_index values (1, 2), (3, null)") 853 r := mustExecSQL(c, se, "select c1 from test_index where c > 0") 854 rows, err := GetRows(r) 855 c.Assert(err, IsNil) 856 c.Assert(rows, HasLen, 1) 857 match(c, rows[0], 1) 858 859 mustExecSQL(c, se, "drop table if exists t1, t2") 860 mustExecSQL(c, se, ` 861 create table t1 (c1 int, primary key(c1)); 862 create table t2 (c2 int, primary key(c2));`) 863 mustExecSQL(c, se, ` 864 insert into t1 values (1), (2); 865 insert into t2 values (2);`) 866 867 r = mustExecSQL(c, se, "select * from t1 left join t2 on t1.c1 = t2.c2 order by t1.c1") 868 rows, err = GetRows(r) 869 c.Assert(err, IsNil) 870 matches(c, rows, [][]interface{}{{1, nil}, {2, 2}}) 871 872 r = mustExecSQL(c, se, "select * from t1 left join t2 on t1.c1 = t2.c2 where t2.c2 < 10") 873 rows, err = GetRows(r) 874 c.Assert(err, IsNil) 875 matches(c, rows, [][]interface{}{{2, 2}}) 876 877 err = store.Close() 878 c.Assert(err, IsNil) 879 } 880 881 func (s *testSessionSuite) TestMySQLTypes(c *C) { 882 defer testleak.AfterTest(c)() 883 store := newStore(c, s.dbName) 884 se := newSession(c, store, s.dbName) 885 886 r := mustExecSQL(c, se, `select 0x01 + 1, x'4D7953514C' = "MySQL"`) 887 row, err := r.Next() 888 c.Assert(err, IsNil) 889 match(c, row.Data, 2, 1) 890 r.Close() 891 892 r = mustExecSQL(c, se, `select 0b01 + 1, 0b01000001 = "A"`) 893 row, err = r.Next() 894 c.Assert(err, IsNil) 895 match(c, row.Data, 2, 1) 896 r.Close() 897 898 err = store.Close() 899 c.Assert(err, IsNil) 900 } 901 902 func (s *testSessionSuite) TestExpression(c *C) { 903 defer testleak.AfterTest(c)() 904 store := newStore(c, s.dbName) 905 se := newSession(c, store, s.dbName) 906 907 r := mustExecSQL(c, se, `select + (1 > 0), -(1 >0), + (1 < 0), - (1 < 0)`) 908 row, err := r.Next() 909 c.Assert(err, IsNil) 910 match(c, row.Data, 1, -1, 0, 0) 911 r.Close() 912 913 r = mustExecSQL(c, se, "select 1 <=> 1, 1 <=> null, null <=> null, null <=> (select null)") 914 row, err = r.Next() 915 c.Assert(err, IsNil) 916 match(c, row.Data, 1, 0, 1, 1) 917 r.Close() 918 919 err = store.Close() 920 c.Assert(err, IsNil) 921 } 922 923 func (s *testSessionSuite) TestSelect(c *C) { 924 defer testleak.AfterTest(c)() 925 store := newStore(c, s.dbName) 926 se := newSession(c, store, s.dbName) 927 928 mustExecSQL(c, se, "create table if not exists t (c1 int, c2 int)") 929 mustExecSQL(c, se, "create table if not exists t1 (c1 int, c2 int)") 930 931 _, err := se.Execute("select * from t as a join t as a") 932 c.Assert(err, NotNil) 933 934 _, err = se.Execute("select * from t join t1 as t") 935 c.Assert(err, NotNil) 936 937 _, err = se.Execute("select * from t join test.t") 938 c.Assert(err, NotNil) 939 940 _, err = se.Execute("select * from t as a join (select 1) as a") 941 c.Assert(err, IsNil) 942 943 r := mustExecSQL(c, se, "select 1, 2 from dual") 944 row, err := r.Next() 945 c.Assert(err, IsNil) 946 match(c, row.Data, 1, 2) 947 948 // Testcase For https://yougam/libraries/pingcap/tidb/issues/1071 949 r = mustExecSQL(c, se, `select 1 from dual where "0.1"`) 950 row, err = r.Next() 951 c.Assert(err, IsNil) 952 c.Assert(row, IsNil) 953 r = mustExecSQL(c, se, "select 1 from dual where 0.8") 954 row, err = r.Next() 955 c.Assert(err, IsNil) 956 match(c, row.Data, 1) 957 r = mustExecSQL(c, se, "select 1, count(*) from dual where 0.1") 958 row, err = r.Next() 959 c.Assert(err, IsNil) 960 match(c, row.Data, 1, 0) 961 r = mustExecSQL(c, se, "select count(*), 1 from dual where 0.8") 962 row, err = r.Next() 963 c.Assert(err, IsNil) 964 match(c, row.Data, 1, 1) 965 r = mustExecSQL(c, se, "select 1, 2 from dual where 0.1") 966 row, err = r.Next() 967 c.Assert(err, IsNil) 968 c.Assert(row, IsNil) 969 mustExecSQL(c, se, "create table if not exists t2 (c1 int, c2 int)") 970 mustExecSQL(c, se, "insert into t2 (c1, c2) values(1, 1), (2, 2), (3, 3)") 971 r = mustExecSQL(c, se, "select 1 from t2 where 0.1") 972 row, err = r.Next() 973 c.Assert(err, IsNil) 974 c.Assert(row, IsNil) 975 r = mustExecSQL(c, se, "select 1 from t2 where 0.9") 976 row, err = r.Next() 977 c.Assert(err, IsNil) 978 match(c, row.Data, 1) 979 row, err = r.Next() 980 c.Assert(err, IsNil) 981 match(c, row.Data, 1) 982 row, err = r.Next() 983 c.Assert(err, IsNil) 984 match(c, row.Data, 1) 985 r = mustExecSQL(c, se, "select sum(c1) from t2 where 0.1") 986 row, err = r.Next() 987 c.Assert(err, IsNil) 988 match(c, row.Data, nil) 989 r = mustExecSQL(c, se, "select sum(c1), c2 from t2 where 0.1") 990 row, err = r.Next() 991 c.Assert(err, IsNil) 992 match(c, row.Data, nil, nil) 993 r = mustExecSQL(c, se, "select 1+2, count(c1) from t2 where 0.1") 994 row, err = r.Next() 995 c.Assert(err, IsNil) 996 match(c, row.Data, 3, 0) 997 998 r = mustExecSQL(c, se, "select 1, 2 from dual where not exists (select * from t where c1=2)") 999 row, err = r.Next() 1000 c.Assert(err, IsNil) 1001 match(c, row.Data, 1, 2) 1002 1003 r = mustExecSQL(c, se, "select 1, 2") 1004 row, err = r.Next() 1005 c.Assert(err, IsNil) 1006 match(c, row.Data, 1, 2) 1007 1008 r = mustExecSQL(c, se, `select '''a''', """a""", 'pingcap ''-->'' tidb'`) 1009 row, err = r.Next() 1010 c.Assert(err, IsNil) 1011 match(c, row.Data, `'a'`, `"a"`, `pingcap '-->' tidb`) 1012 1013 r = mustExecSQL(c, se, `select '\'a\'', "\"a\"";`) 1014 row, err = r.Next() 1015 c.Assert(err, IsNil) 1016 match(c, row.Data, `'a'`, `"a"`) 1017 1018 mustExecSQL(c, se, "drop table if exists t") 1019 mustExecSQL(c, se, "create table t (c varchar(20))") 1020 mustExecSQL(c, se, `insert t values("pingcap '-->' tidb")`) 1021 1022 r = mustExecSQL(c, se, `select * from t where c like 'pingcap ''-->'' tidb'`) 1023 row, err = r.Next() 1024 c.Assert(err, IsNil) 1025 match(c, row.Data, []byte(`pingcap '-->' tidb`)) 1026 1027 mustExecSQL(c, se, "drop table if exists t1") 1028 mustExecSQL(c, se, "drop table if exists t2") 1029 mustExecSQL(c, se, "drop table if exists t3") 1030 mustExecSQL(c, se, "create table t1 (c1 int, c11 int)") 1031 mustExecSQL(c, se, "create table t2 (c2 int)") 1032 mustExecSQL(c, se, "create table t3 (c3 int)") 1033 mustExecSQL(c, se, "insert into t1 values (1, 1), (2, 2), (3, 3)") 1034 mustExecSQL(c, se, "insert into t2 values (1), (1), (2)") 1035 mustExecSQL(c, se, "insert into t3 values (1), (3)") 1036 1037 r = mustExecSQL(c, se, "select * from t1 left join t2 on t1.c1 = t2.c2 left join t3 on t1.c1 = t3.c3 order by t1.c1, t2.c2, t3.c3") 1038 rows, err := GetRows(r) 1039 c.Assert(err, IsNil) 1040 c.Assert(rows, HasLen, 4) 1041 match(c, rows[0], 1, 1, 1, 1) 1042 match(c, rows[1], 1, 1, 1, 1) 1043 match(c, rows[2], 2, 2, 2, nil) 1044 match(c, rows[3], 3, 3, nil, 3) 1045 1046 mustExecSQL(c, se, "drop table if exists t") 1047 mustExecSQL(c, se, "create table t (c float(8))") 1048 mustExecSQL(c, se, "insert into t values (3.12)") 1049 r = mustExecSQL(c, se, "select * from t") 1050 row, err = r.Next() 1051 c.Assert(err, IsNil) 1052 match(c, row.Data, 3.12) 1053 1054 mustExecSQL(c, se, `drop table if exists t;create table t (c int);insert into t values (1);`) 1055 r = mustExecSQL(c, se, "select a.c from t as a where c between null and 2") 1056 row, err = r.Next() 1057 c.Assert(err, IsNil) 1058 c.Assert(row, IsNil) 1059 1060 mustExecSQL(c, se, "drop table if exists t1, t2, t3") 1061 mustExecSQL(c, se, ` 1062 create table t1 (c1 int); 1063 create table t2 (c2 int); 1064 create table t3 (c3 int);`) 1065 mustExecSQL(c, se, ` 1066 insert into t1 values (1), (2); 1067 insert into t2 values (2); 1068 insert into t3 values (3);`) 1069 r = mustExecSQL(c, se, "select * from t1 left join t2 on t1.c1 = t2.c2 left join t3 on t1.c1 = t3.c3 order by t1.c1") 1070 rows, err = GetRows(r) 1071 c.Assert(err, IsNil) 1072 matches(c, rows, [][]interface{}{{1, nil, nil}, {2, 2, nil}}) 1073 1074 mustExecFailed(c, se, "select * from t1 left join t2 on t1.c1 = t3.c3 left join on t3 on t1.c1 = t2.c2") 1075 1076 // For issue 393 1077 mustExecSQL(c, se, "drop table if exists t") 1078 mustExecSQL(c, se, "create table t (b blob)") 1079 mustExecSQL(c, se, `insert t values('\x01')`) 1080 1081 r = mustExecSQL(c, se, `select length(b) from t`) 1082 row, err = r.Next() 1083 c.Assert(err, IsNil) 1084 match(c, row.Data, 3) 1085 1086 mustExecSQL(c, se, `select * from t1, t2 where t1.c1 is null`) 1087 err = store.Close() 1088 c.Assert(err, IsNil) 1089 } 1090 1091 func (s *testSessionSuite) TestSubQuery(c *C) { 1092 defer testleak.AfterTest(c)() 1093 store := newStore(c, s.dbName) 1094 se := newSession(c, store, s.dbName) 1095 1096 mustExecSQL(c, se, "create table if not exists t1 (c1 int, c2 int)") 1097 mustExecSQL(c, se, "create table if not exists t2 (c1 int, c2 int)") 1098 mustExecSQL(c, se, "insert into t1 values (1, 1), (2, 2)") 1099 mustExecSQL(c, se, "insert into t2 values (1, 1), (1, 2)") 1100 1101 r := mustExecSQL(c, se, `select c1 from t1 where c1 = (select c2 from t2 where t1.c2 = t2.c2)`) 1102 row, err := r.Next() 1103 c.Assert(err, IsNil) 1104 match(c, row.Data, 1) 1105 1106 r = mustExecSQL(c, se, `select (select count(c1) from t2 where t2.c1 != t1.c2) from t1`) 1107 rows, err := GetRows(r) 1108 c.Assert(err, IsNil) 1109 c.Assert(rows, HasLen, 2) 1110 match(c, rows[0], 0) 1111 match(c, rows[1], 2) 1112 1113 mustExecMatch(c, se, "select a.c1, a.c2 from (select c1 as c1, c1 as c2 from t1) as a", [][]interface{}{{1, 1}, {2, 2}}) 1114 1115 err = store.Close() 1116 c.Assert(err, IsNil) 1117 } 1118 1119 func (s *testSessionSuite) TestShow(c *C) { 1120 defer testleak.AfterTest(c)() 1121 store := newStore(c, s.dbName) 1122 se := newSession(c, store, s.dbName) 1123 1124 mustExecSQL(c, se, "set global autocommit=1") 1125 r := mustExecSQL(c, se, "show global variables where variable_name = 'autocommit'") 1126 row, err := r.Next() 1127 c.Assert(err, IsNil) 1128 match(c, row.Data, "autocommit", "ON") 1129 1130 mustExecSQL(c, se, "drop table if exists t") 1131 mustExecSQL(c, se, "create table if not exists t (c int)") 1132 r = mustExecSQL(c, se, `show columns from t`) 1133 rows, err := GetRows(r) 1134 c.Assert(err, IsNil) 1135 c.Assert(rows, HasLen, 1) 1136 match(c, rows[0], "c", "int(11)", "YES", "", nil, "") 1137 1138 r = mustExecSQL(c, se, "show collation where Charset = 'utf8' and Collation = 'utf8_bin'") 1139 row, err = r.Next() 1140 c.Assert(err, IsNil) 1141 match(c, row.Data, "utf8_bin", "utf8", 83, "", "Yes", 1) 1142 1143 r = mustExecSQL(c, se, "show tables") 1144 row, err = r.Next() 1145 c.Assert(err, IsNil) 1146 c.Assert(row.Data, HasLen, 1) 1147 1148 r = mustExecSQL(c, se, "show full tables") 1149 row, err = r.Next() 1150 c.Assert(err, IsNil) 1151 c.Assert(row.Data, HasLen, 2) 1152 1153 r = mustExecSQL(c, se, "show create table t") 1154 row, err = r.Next() 1155 c.Assert(err, IsNil) 1156 c.Assert(row.Data, HasLen, 2) 1157 c.Assert(row.Data[0].GetString(), Equals, "t") 1158 1159 r = mustExecSQL(c, se, "show databases like 'test'") 1160 row, err = r.Next() 1161 c.Assert(err, IsNil) 1162 c.Assert(row.Data, HasLen, 1) 1163 c.Assert(row.Data[0].GetString(), Equals, "test") 1164 1165 r = mustExecSQL(c, se, "grant all on *.* to 'root'@'%'") 1166 r = mustExecSQL(c, se, "show grants") 1167 row, err = r.Next() 1168 c.Assert(err, IsNil) 1169 c.Assert(row.Data, HasLen, 1) 1170 1171 err = store.Close() 1172 c.Assert(err, IsNil) 1173 } 1174 1175 func (s *testSessionSuite) TestTimeFunc(c *C) { 1176 defer testleak.AfterTest(c)() 1177 store := newStore(c, s.dbName) 1178 se := newSession(c, store, s.dbName) 1179 1180 last := time.Now().Format(mysql.TimeFormat) 1181 r := mustExecSQL(c, se, "select now(), now(6), current_timestamp, current_timestamp(), current_timestamp(6), sysdate(), sysdate(6)") 1182 row, err := r.Next() 1183 c.Assert(err, IsNil) 1184 for _, t := range row.Data { 1185 n := t.GetMysqlTime() 1186 c.Assert(n.String(), GreaterEqual, last) 1187 } 1188 1189 last = time.Now().Format(mysql.DateFormat) 1190 r = mustExecSQL(c, se, "select current_date, current_date(), curdate()") 1191 row, err = r.Next() 1192 c.Assert(err, IsNil) 1193 for _, t := range row.Data { 1194 n := t.GetMysqlTime() 1195 c.Assert(n.String(), GreaterEqual, last) 1196 } 1197 1198 err = store.Close() 1199 c.Assert(err, IsNil) 1200 } 1201 1202 func (s *testSessionSuite) TestBit(c *C) { 1203 defer testleak.AfterTest(c)() 1204 store := newStore(c, s.dbName) 1205 se := newSession(c, store, s.dbName) 1206 1207 mustExecSQL(c, se, "drop table if exists t") 1208 mustExecSQL(c, se, "create table t (c1 bit(2))") 1209 mustExecSQL(c, se, "insert into t values (0), (1), (2), (3)") 1210 _, err := exec(c, se, "insert into t values (4)") 1211 c.Assert(err, NotNil) 1212 r := mustExecSQL(c, se, "select * from t where c1 = 2") 1213 row, err := r.Next() 1214 c.Assert(err, IsNil) 1215 c.Assert(row.Data[0].GetMysqlBit(), Equals, mysql.Bit{Value: 2, Width: 2}) 1216 1217 err = store.Close() 1218 c.Assert(err, IsNil) 1219 } 1220 1221 func (s *testSessionSuite) TestBootstrap(c *C) { 1222 defer testleak.AfterTest(c)() 1223 store := newStore(c, s.dbName) 1224 se := newSession(c, store, s.dbName) 1225 mustExecSQL(c, se, "USE mysql;") 1226 r := mustExecSQL(c, se, `select * from user;`) 1227 c.Assert(r, NotNil) 1228 row, err := r.Next() 1229 c.Assert(err, IsNil) 1230 c.Assert(row, NotNil) 1231 match(c, row.Data, []byte("%"), []byte("root"), []byte(""), "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y") 1232 1233 c.Assert(se.Auth("root@anyhost", []byte(""), []byte("")), IsTrue) 1234 mustExecSQL(c, se, "USE test;") 1235 // Check privilege tables. 1236 mustExecSQL(c, se, "SELECT * from mysql.db;") 1237 mustExecSQL(c, se, "SELECT * from mysql.tables_priv;") 1238 mustExecSQL(c, se, "SELECT * from mysql.columns_priv;") 1239 // Check privilege tables. 1240 r = mustExecSQL(c, se, "SELECT COUNT(*) from mysql.global_variables;") 1241 c.Assert(r, NotNil) 1242 v, err := r.Next() 1243 c.Assert(err, IsNil) 1244 c.Assert(v.Data[0].GetInt64(), Equals, int64(len(variable.SysVars))) 1245 1246 // Check a storage operations are default autocommit after the second start. 1247 mustExecSQL(c, se, "USE test;") 1248 mustExecSQL(c, se, "drop table if exists t") 1249 mustExecSQL(c, se, "create table t (id int)") 1250 delete(storeBootstrapped, store.UUID()) 1251 se.Close() 1252 se, err = CreateSession(store) 1253 c.Assert(err, IsNil) 1254 mustExecSQL(c, se, "USE test;") 1255 mustExecSQL(c, se, "insert t values (?)", 3) 1256 se, err = CreateSession(store) 1257 c.Assert(err, IsNil) 1258 mustExecSQL(c, se, "USE test;") 1259 r = mustExecSQL(c, se, "select * from t") 1260 c.Assert(r, NotNil) 1261 v, err = r.Next() 1262 c.Assert(err, IsNil) 1263 match(c, v.Data, 3) 1264 mustExecSQL(c, se, "drop table if exists t") 1265 se.Close() 1266 1267 // Try do bootstrap dml jobs on an already bootstraped TiDB system will not cause fatal. 1268 // For https://yougam/libraries/pingcap/tidb/issues/1096 1269 store = newStore(c, s.dbName) 1270 se, err = CreateSession(store) 1271 c.Assert(err, IsNil) 1272 doDMLWorks(se) 1273 1274 err = store.Close() 1275 c.Assert(err, IsNil) 1276 } 1277 1278 // Create a new session on store but only do ddl works. 1279 func (s *testSessionSuite) bootstrapWithError(store kv.Storage, c *C) { 1280 ss := &session{ 1281 values: make(map[fmt.Stringer]interface{}), 1282 store: store, 1283 sid: atomic.AddInt64(&sessionID, 1), 1284 } 1285 domain, err := domap.Get(store) 1286 c.Assert(err, IsNil) 1287 sessionctx.BindDomain(ss, domain) 1288 variable.BindSessionVars(ss) 1289 variable.GetSessionVars(ss).SetStatusFlag(mysql.ServerStatusAutocommit, true) 1290 // session implements autocommit.Checker. Bind it to ctx 1291 autocommit.BindAutocommitChecker(ss, ss) 1292 sessionMu.Lock() 1293 defer sessionMu.Unlock() 1294 b, err := checkBootstrapped(ss) 1295 c.Assert(b, IsFalse) 1296 c.Assert(err, IsNil) 1297 doDDLWorks(ss) 1298 // Leave dml unfinished. 1299 } 1300 1301 func (s *testSessionSuite) TestBootstrapWithError(c *C) { 1302 defer testleak.AfterTest(c)() 1303 store := newStore(c, s.dbNameBootstrap) 1304 s.bootstrapWithError(store, c) 1305 se := newSession(c, store, s.dbNameBootstrap) 1306 mustExecSQL(c, se, "USE mysql;") 1307 r := mustExecSQL(c, se, `select * from user;`) 1308 row, err := r.Next() 1309 c.Assert(err, IsNil) 1310 c.Assert(row, NotNil) 1311 match(c, row.Data, []byte("%"), []byte("root"), []byte(""), "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y") 1312 mustExecSQL(c, se, "USE test;") 1313 // Check privilege tables. 1314 mustExecSQL(c, se, "SELECT * from mysql.db;") 1315 mustExecSQL(c, se, "SELECT * from mysql.tables_priv;") 1316 mustExecSQL(c, se, "SELECT * from mysql.columns_priv;") 1317 // Check global variables. 1318 r = mustExecSQL(c, se, "SELECT COUNT(*) from mysql.global_variables;") 1319 v, err := r.Next() 1320 c.Assert(err, IsNil) 1321 c.Assert(v.Data[0].GetInt64(), Equals, int64(len(variable.SysVars))) 1322 1323 r = mustExecSQL(c, se, `SELECT VARIABLE_VALUE from mysql.TiDB where VARIABLE_NAME="bootstrapped";`) 1324 row, err = r.Next() 1325 c.Assert(err, IsNil) 1326 c.Assert(row, NotNil) 1327 c.Assert(row.Data, HasLen, 1) 1328 c.Assert(row.Data[0].GetBytes(), BytesEquals, []byte("True")) 1329 1330 err = store.Close() 1331 c.Assert(err, IsNil) 1332 } 1333 1334 func (s *testSessionSuite) TestEnum(c *C) { 1335 defer testleak.AfterTest(c)() 1336 store := newStore(c, s.dbName) 1337 se := newSession(c, store, s.dbName) 1338 1339 mustExecSQL(c, se, "drop table if exists t") 1340 mustExecSQL(c, se, "create table t (c enum('a', 'b', 'c'))") 1341 mustExecSQL(c, se, "insert into t values ('a'), (2), ('c')") 1342 r := mustExecSQL(c, se, "select * from t where c = 'a'") 1343 row, err := r.Next() 1344 c.Assert(err, IsNil) 1345 match(c, row.Data, "a") 1346 1347 r = mustExecSQL(c, se, "select c + 1 from t where c = 2") 1348 row, err = r.Next() 1349 c.Assert(err, IsNil) 1350 match(c, row.Data, "3") 1351 1352 mustExecSQL(c, se, "delete from t") 1353 mustExecSQL(c, se, "insert into t values ()") 1354 mustExecSQL(c, se, "insert into t values (null), ('1')") 1355 r = mustExecSQL(c, se, "select c + 1 from t where c = 1") 1356 row, err = r.Next() 1357 c.Assert(err, IsNil) 1358 match(c, row.Data, "2") 1359 1360 err = store.Close() 1361 c.Assert(err, IsNil) 1362 } 1363 1364 func (s *testSessionSuite) TestSet(c *C) { 1365 defer testleak.AfterTest(c)() 1366 store := newStore(c, s.dbName) 1367 se := newSession(c, store, s.dbName) 1368 1369 mustExecSQL(c, se, "drop table if exists t") 1370 mustExecSQL(c, se, "create table t (c set('a', 'b', 'c'))") 1371 mustExecSQL(c, se, "insert into t values ('a'), (2), ('c'), ('a,b'), ('b,a')") 1372 r := mustExecSQL(c, se, "select * from t where c = 'a'") 1373 row, err := r.Next() 1374 c.Assert(err, IsNil) 1375 match(c, row.Data, "a") 1376 1377 r = mustExecSQL(c, se, "select * from t where c = 'a,b'") 1378 rows, err := GetRows(r) 1379 c.Assert(err, IsNil) 1380 c.Assert(rows, HasLen, 2) 1381 1382 r = mustExecSQL(c, se, "select c + 1 from t where c = 2") 1383 row, err = r.Next() 1384 c.Assert(err, IsNil) 1385 match(c, row.Data, "3") 1386 1387 mustExecSQL(c, se, "delete from t") 1388 mustExecSQL(c, se, "insert into t values ()") 1389 mustExecSQL(c, se, "insert into t values (null), ('1')") 1390 r = mustExecSQL(c, se, "select c + 1 from t where c = 1") 1391 row, err = r.Next() 1392 c.Assert(err, IsNil) 1393 match(c, row.Data, "2") 1394 1395 err = store.Close() 1396 c.Assert(err, IsNil) 1397 } 1398 1399 func (s *testSessionSuite) TestDatabase(c *C) { 1400 defer testleak.AfterTest(c)() 1401 store := newStore(c, s.dbName) 1402 se := newSession(c, store, s.dbName) 1403 1404 // Test database. 1405 mustExecSQL(c, se, "create database xxx") 1406 mustExecSQL(c, se, "drop database xxx") 1407 1408 mustExecSQL(c, se, "drop database if exists xxx") 1409 mustExecSQL(c, se, "create database xxx") 1410 mustExecSQL(c, se, "create database if not exists xxx") 1411 mustExecSQL(c, se, "drop database if exists xxx") 1412 1413 // Test schema. 1414 mustExecSQL(c, se, "create schema xxx") 1415 mustExecSQL(c, se, "drop schema xxx") 1416 1417 mustExecSQL(c, se, "drop schema if exists xxx") 1418 mustExecSQL(c, se, "create schema xxx") 1419 mustExecSQL(c, se, "create schema if not exists xxx") 1420 mustExecSQL(c, se, "drop schema if exists xxx") 1421 1422 err := store.Close() 1423 c.Assert(err, IsNil) 1424 } 1425 1426 func (s *testSessionSuite) TestWhereLike(c *C) { 1427 defer testleak.AfterTest(c)() 1428 store := newStore(c, s.dbName) 1429 se := newSession(c, store, s.dbName) 1430 1431 mustExecSQL(c, se, "drop table if exists t") 1432 mustExecSQL(c, se, "create table t(c int, index(c))") 1433 mustExecSQL(c, se, "insert into t values (1),(2),(3),(-11),(11),(123),(211),(210)") 1434 mustExecSQL(c, se, "insert into t values ()") 1435 1436 r := mustExecSQL(c, se, "select c from t where c like '%1%'") 1437 rows, err := GetRows(r) 1438 c.Assert(err, IsNil) 1439 c.Assert(rows, HasLen, 6) 1440 1441 mustExecSQL(c, se, "select c from t where c like binary('abc')") 1442 1443 err = store.Close() 1444 c.Assert(err, IsNil) 1445 } 1446 1447 func (s *testSessionSuite) TestDefaultFlenBug(c *C) { 1448 defer testleak.AfterTest(c)() 1449 // If set unspecified column flen to 0, it will cause bug in union. 1450 // This test is used to prevent the bug reappear. 1451 store := newStore(c, s.dbName) 1452 se := newSession(c, store, s.dbName) 1453 1454 mustExecSQL(c, se, "create table t1 (c double);") 1455 mustExecSQL(c, se, "create table t2 (c double);") 1456 mustExecSQL(c, se, "insert into t1 value (73);") 1457 mustExecSQL(c, se, "insert into t2 value (930);") 1458 // The data in the second src will be casted as the type of the first src. 1459 // If use flen=0, it will be truncated. 1460 r := mustExecSQL(c, se, "select c from t1 union select c from t2;") 1461 rows, err := GetRows(r) 1462 c.Assert(err, IsNil) 1463 c.Assert(rows, HasLen, 2) 1464 c.Assert(rows[1][0].GetFloat64(), Equals, float64(930)) 1465 1466 err = store.Close() 1467 c.Assert(err, IsNil) 1468 } 1469 1470 func (s *testSessionSuite) TestExecRestrictedSQL(c *C) { 1471 defer testleak.AfterTest(c)() 1472 store := newStore(c, s.dbName) 1473 se := newSession(c, store, s.dbName).(*session) 1474 r, err := se.ExecRestrictedSQL(se, "select 1;") 1475 c.Assert(r, NotNil) 1476 c.Assert(err, IsNil) 1477 _, err = se.ExecRestrictedSQL(se, "select 1; select 2;") 1478 c.Assert(err, NotNil) 1479 _, err = se.ExecRestrictedSQL(se, "") 1480 c.Assert(err, NotNil) 1481 1482 err = store.Close() 1483 c.Assert(err, IsNil) 1484 } 1485 1486 func (s *testSessionSuite) TestGroupBy(c *C) { 1487 defer testleak.AfterTest(c)() 1488 store := newStore(c, s.dbName) 1489 se := newSession(c, store, s.dbName) 1490 mustExecSQL(c, se, "drop table if exists t") 1491 mustExecSQL(c, se, "create table t (c1 int, c2 int)") 1492 mustExecSQL(c, se, "insert into t values (1,1), (2,2), (1,2), (1,3)") 1493 mustExecMatch(c, se, "select nullif (count(*), 2);", [][]interface{}{{1}}) 1494 mustExecMatch(c, se, "select 1 as a, sum(c1) as a from t group by a", [][]interface{}{{1, 5}}) 1495 mustExecMatch(c, se, "select c1 as a, 1 as a, sum(c1) as a from t group by a", [][]interface{}{{1, 1, 5}}) 1496 mustExecMatch(c, se, "select c1 as a, 1 as a, c2 as a from t group by a;", [][]interface{}{{1, 1, 1}}) 1497 mustExecMatch(c, se, "select c1 as c2, sum(c1) as c2 from t group by c2;", [][]interface{}{{1, 1}, {2, 3}, {1, 1}}) 1498 1499 mustExecMatch(c, se, "select c1 as c2, c2 from t group by c2 + 1", [][]interface{}{{1, 1}, {2, 2}, {1, 3}}) 1500 mustExecMatch(c, se, "select c1 as c2, count(c1) from t group by c2", [][]interface{}{{1, 1}, {2, 2}, {1, 1}}) 1501 mustExecMatch(c, se, "select t.c1, c1 from t group by c1", [][]interface{}{{1, 1}, {2, 2}}) 1502 mustExecMatch(c, se, "select t.c1 as a, c1 as a from t group by a", [][]interface{}{{1, 1}, {2, 2}}) 1503 1504 mustExecFailed(c, se, "select c1 as a, c2 as a from t group by a") 1505 mustExecFailed(c, se, "select c1 as c2, c2 from t group by c2") 1506 mustExecFailed(c, se, "select sum(c1) as a from t group by a") 1507 mustExecFailed(c, se, "select sum(c1) as a from t group by a + 1") 1508 1509 err := store.Close() 1510 c.Assert(err, IsNil) 1511 } 1512 1513 func (s *testSessionSuite) TestOrderBy(c *C) { 1514 defer testleak.AfterTest(c)() 1515 store := newStore(c, s.dbName) 1516 se := newSession(c, store, s.dbName) 1517 mustExecSQL(c, se, "drop table if exists t") 1518 mustExecSQL(c, se, "create table t (c1 int, c2 int, c3 varchar(20))") 1519 mustExecSQL(c, se, "insert into t values (1, 2, 'abc'), (2, 1, 'bcd')") 1520 1521 // Fix issue https://yougam/libraries/pingcap/tidb/issues/337 1522 mustExecMatch(c, se, "select c1 as a, c1 as b from t order by c1", [][]interface{}{{1, 1}, {2, 2}}) 1523 1524 mustExecMatch(c, se, "select c1 as a, t.c1 as a from t order by a desc", [][]interface{}{{2, 2}, {1, 1}}) 1525 mustExecMatch(c, se, "select c1 as c2 from t order by c2", [][]interface{}{{1}, {2}}) 1526 mustExecMatch(c, se, "select sum(c1) from t order by sum(c1)", [][]interface{}{{3}}) 1527 mustExecMatch(c, se, "select c1 as c2 from t order by c2 + 1", [][]interface{}{{2}, {1}}) 1528 1529 // Order by position 1530 mustExecMatch(c, se, "select * from t order by 1", [][]interface{}{{1, 2, []byte("abc")}, {2, 1, []byte("bcd")}}) 1531 mustExecMatch(c, se, "select * from t order by 2", [][]interface{}{{2, 1, []byte("bcd")}, {1, 2, []byte("abc")}}) 1532 mustExecFailed(c, se, "select * from t order by 0") 1533 mustExecFailed(c, se, "select * from t order by 4") 1534 1535 mustExecFailed(c, se, "select c1 as a, c2 as a from t order by a") 1536 1537 mustExecFailed(c, se, "(select c1 as c2, c2 from t) union (select c1, c2 from t) order by c2") 1538 mustExecFailed(c, se, "(select c1 as c2, c2 from t) union (select c1, c2 from t) order by c1") 1539 1540 // Ordery by binary 1541 mustExecMatch(c, se, "select c1, c3 from t order by binary c1 desc", [][]interface{}{{2, []byte("bcd")}, {1, []byte("abc")}}) 1542 mustExecMatch(c, se, "select c1, c2 from t order by binary c3", [][]interface{}{{1, 2}, {2, 1}}) 1543 1544 err := store.Close() 1545 c.Assert(err, IsNil) 1546 } 1547 1548 func (s *testSessionSuite) TestHaving(c *C) { 1549 defer testleak.AfterTest(c)() 1550 store := newStore(c, s.dbName) 1551 se := newSession(c, store, s.dbName) 1552 mustExecSQL(c, se, "drop table if exists t") 1553 mustExecSQL(c, se, "create table t (c1 int, c2 int, c3 int)") 1554 mustExecSQL(c, se, "insert into t values (1,2,3), (2, 3, 1), (3, 1, 2)") 1555 1556 mustExecMatch(c, se, "select c1 as c2, c3 from t having c2 = 2", [][]interface{}{{2, 1}}) 1557 mustExecMatch(c, se, "select c1 as c2, c3 from t group by c2 having c2 = 2;", [][]interface{}{{1, 3}}) 1558 mustExecMatch(c, se, "select c1 as c2, c3 from t group by c2 having sum(c2) = 2;", [][]interface{}{{1, 3}}) 1559 mustExecMatch(c, se, "select c1 as c2, c3 from t group by c3 having sum(c2) = 2;", [][]interface{}{{1, 3}}) 1560 mustExecMatch(c, se, "select c1 as c2, c3 from t group by c3 having sum(0) + c2 = 2;", [][]interface{}{{2, 1}}) 1561 mustExecMatch(c, se, "select c1 as a from t having c1 = 1;", [][]interface{}{{1}}) 1562 mustExecMatch(c, se, "select t.c1 from t having c1 = 1;", [][]interface{}{{1}}) 1563 mustExecMatch(c, se, "select a.c1 from t as a having c1 = 1;", [][]interface{}{{1}}) 1564 mustExecMatch(c, se, "select c1 as a from t group by c3 having sum(a) = 1;", [][]interface{}{{1}}) 1565 mustExecMatch(c, se, "select c1 as a from t group by c3 having sum(a) + a = 2;", [][]interface{}{{1}}) 1566 mustExecMatch(c, se, "select a.c1 as c, a.c1 as d from t as a, t as b having c1 = 1 limit 1;", [][]interface{}{{1, 1}}) 1567 1568 mustExecMatch(c, se, "select sum(c1) from t group by c1 having sum(c1)", [][]interface{}{{1}, {2}, {3}}) 1569 mustExecMatch(c, se, "select sum(c1) - 1 from t group by c1 having sum(c1) - 1", [][]interface{}{{1}, {2}}) 1570 mustExecMatch(c, se, "select 1 from t group by c1 having sum(abs(c2 + c3)) = c1", [][]interface{}{{1}}) 1571 1572 mustExecFailed(c, se, "select c1 from t having c2") 1573 mustExecFailed(c, se, "select c1 from t having c2 + 1") 1574 mustExecFailed(c, se, "select c1 from t group by c2 + 1 having c2") 1575 mustExecFailed(c, se, "select c1 from t group by c2 + 1 having c2 + 1") 1576 mustExecFailed(c, se, "select c1 as c2, c2 from t having c2") 1577 mustExecFailed(c, se, "select c1 as c2, c2 from t having c2 + 1") 1578 mustExecFailed(c, se, "select c1 as a, c2 as a from t having a") 1579 mustExecFailed(c, se, "select c1 as a, c2 as a from t having a + 1") 1580 mustExecFailed(c, se, "select c1 + 1 from t having c1") 1581 mustExecFailed(c, se, "select c1 + 1 from t having c1 + 1") 1582 mustExecFailed(c, se, "select a.c1 as c, b.c1 as d from t as a, t as b having c1") 1583 mustExecFailed(c, se, "select 1 from t having sum(avg(c1))") 1584 1585 err := store.Close() 1586 c.Assert(err, IsNil) 1587 } 1588 1589 func (s *testSessionSuite) TestResultType(c *C) { 1590 defer testleak.AfterTest(c)() 1591 // Testcase for https://yougam/libraries/pingcap/tidb/issues/325 1592 store := newStore(c, s.dbName) 1593 se := newSession(c, store, s.dbName) 1594 rs := mustExecSQL(c, se, `select cast(null as char(30))`) 1595 c.Assert(rs, NotNil) 1596 row, err := rs.Next() 1597 c.Assert(err, IsNil) 1598 c.Assert(row.Data[0].GetValue(), IsNil) 1599 fs, err := rs.Fields() 1600 c.Assert(err, IsNil) 1601 c.Assert(fs[0].Column.FieldType.Tp, Equals, mysql.TypeString) 1602 1603 err = store.Close() 1604 c.Assert(err, IsNil) 1605 } 1606 1607 func (s *testSessionSuite) TestIssue461(c *C) { 1608 defer testleak.AfterTest(c)() 1609 store := newStore(c, s.dbName) 1610 se1 := newSession(c, store, s.dbName) 1611 mustExecSQL(c, se1, 1612 `CREATE TABLE test ( id int(11) UNSIGNED NOT NULL AUTO_INCREMENT, val int UNIQUE, PRIMARY KEY (id)); `) 1613 mustExecSQL(c, se1, "begin;") 1614 mustExecSQL(c, se1, "insert into test(id, val) values(1, 1);") 1615 se2 := newSession(c, store, s.dbName) 1616 mustExecSQL(c, se2, "begin;") 1617 mustExecSQL(c, se2, "insert into test(id, val) values(2, 2);") 1618 se3 := newSession(c, store, s.dbName) 1619 mustExecSQL(c, se3, "begin;") 1620 mustExecSQL(c, se3, "insert into test(id, val) values(1, 2);") 1621 mustExecSQL(c, se3, "commit;") 1622 _, err := se1.Execute("commit") 1623 c.Assert(err, NotNil) 1624 // Check error type and error message 1625 c.Assert(terror.ErrorEqual(err, kv.ErrKeyExists), IsTrue) 1626 c.Assert(err.Error(), Equals, "[kv:1062]Duplicate entry '1' for key 'PRIMARY'") 1627 1628 _, err = se2.Execute("commit") 1629 c.Assert(err, NotNil) 1630 c.Assert(terror.ErrorEqual(err, kv.ErrKeyExists), IsTrue) 1631 c.Assert(err.Error(), Equals, "[kv:1062]Duplicate entry '2' for key 'val'") 1632 1633 se := newSession(c, store, s.dbName) 1634 mustExecSQL(c, se, "drop table test;") 1635 err = store.Close() 1636 c.Assert(err, IsNil) 1637 } 1638 1639 func (s *testSessionSuite) TestIssue463(c *C) { 1640 defer testleak.AfterTest(c)() 1641 // Testcase for https://yougam/libraries/pingcap/tidb/issues/463 1642 store := newStore(c, s.dbName) 1643 se := newSession(c, store, s.dbName) 1644 mustExecSQL(c, se, "DROP TABLE IF EXISTS test") 1645 mustExecSQL(c, se, 1646 `CREATE TABLE test ( 1647 id int(11) UNSIGNED NOT NULL AUTO_INCREMENT, 1648 val int UNIQUE, 1649 PRIMARY KEY (id) 1650 );`) 1651 mustExecSQL(c, se, "insert into test(id, val) values(1, 1);") 1652 mustExecFailed(c, se, "insert into test(id, val) values(2, 1);") 1653 mustExecSQL(c, se, "insert into test(id, val) values(2, 2);") 1654 1655 mustExecSQL(c, se, "begin;") 1656 mustExecSQL(c, se, "insert into test(id, val) values(3, 3);") 1657 mustExecFailed(c, se, "insert into test(id, val) values(4, 3);") 1658 mustExecSQL(c, se, "insert into test(id, val) values(4, 4);") 1659 mustExecSQL(c, se, "commit;") 1660 se1 := newSession(c, store, s.dbName) 1661 mustExecSQL(c, se1, "begin;") 1662 mustExecSQL(c, se1, "insert into test(id, val) values(5, 6);") 1663 mustExecSQL(c, se, "begin;") 1664 mustExecSQL(c, se, "insert into test(id, val) values(20, 6);") 1665 mustExecSQL(c, se, "commit;") 1666 mustExecFailed(c, se1, "commit;") 1667 mustExecSQL(c, se1, "insert into test(id, val) values(5, 5);") 1668 1669 mustExecSQL(c, se, "drop table test;") 1670 1671 mustExecSQL(c, se, 1672 `CREATE TABLE test ( 1673 id int(11) UNSIGNED NOT NULL AUTO_INCREMENT, 1674 val1 int UNIQUE, 1675 val2 int UNIQUE, 1676 PRIMARY KEY (id) 1677 );`) 1678 mustExecSQL(c, se, "insert into test(id, val1, val2) values(1, 1, 1);") 1679 mustExecSQL(c, se, "insert into test(id, val1, val2) values(2, 2, 2);") 1680 mustExecFailed(c, se, "update test set val1 = 3, val2 = 2 where id = 1;") 1681 mustExecSQL(c, se, "insert into test(id, val1, val2) values(3, 3, 3);") 1682 mustExecSQL(c, se, "drop table test;") 1683 1684 err := store.Close() 1685 c.Assert(err, IsNil) 1686 } 1687 1688 func (s *testSessionSuite) TestIssue177(c *C) { 1689 defer testleak.AfterTest(c)() 1690 store := newStore(c, s.dbName) 1691 se := newSession(c, store, s.dbName) 1692 mustExecSQL(c, se, `drop table if exists t1;`) 1693 mustExecSQL(c, se, `drop table if exists t2;`) 1694 mustExecSQL(c, se, "CREATE TABLE `t1` ( `a` char(3) NOT NULL default '', `b` char(3) NOT NULL default '', `c` char(3) NOT NULL default '', PRIMARY KEY (`a`,`b`,`c`)) ENGINE=InnoDB;") 1695 mustExecSQL(c, se, "CREATE TABLE `t2` ( `a` char(3) NOT NULL default '', `b` char(3) NOT NULL default '', `c` char(3) NOT NULL default '', PRIMARY KEY (`a`,`b`,`c`)) ENGINE=InnoDB;") 1696 mustExecSQL(c, se, `INSERT INTO t1 VALUES (1,1,1);`) 1697 mustExecSQL(c, se, `INSERT INTO t2 VALUES (1,1,1);`) 1698 mustExecSQL(c, se, `PREPARE my_stmt FROM "SELECT t1.b, count(*) FROM t1 group by t1.b having count(*) > ALL (SELECT COUNT(*) FROM t2 WHERE t2.a=1 GROUP By t2.b)";`) 1699 mustExecSQL(c, se, `EXECUTE my_stmt;`) 1700 mustExecSQL(c, se, `EXECUTE my_stmt;`) 1701 mustExecSQL(c, se, `deallocate prepare my_stmt;`) 1702 mustExecSQL(c, se, `drop table t1,t2;`) 1703 1704 err := store.Close() 1705 c.Assert(err, IsNil) 1706 } 1707 1708 func (s *testSessionSuite) TestBuiltin(c *C) { 1709 defer testleak.AfterTest(c)() 1710 store := newStore(c, s.dbName) 1711 se := newSession(c, store, s.dbName) 1712 1713 // Testcase for https://yougam/libraries/pingcap/tidb/issues/382 1714 mustExecFailed(c, se, `select cast("xxx 10:10:10" as datetime)`) 1715 mustExecMatch(c, se, "select locate('bar', 'foobarbar')", [][]interface{}{{4}}) 1716 1717 err := store.Close() 1718 c.Assert(err, IsNil) 1719 } 1720 1721 func (s *testSessionSuite) TestFieldText(c *C) { 1722 defer testleak.AfterTest(c)() 1723 store := newStore(c, s.dbName) 1724 se := newSession(c, store, s.dbName) 1725 mustExecSQL(c, se, "drop table if exists t") 1726 mustExecSQL(c, se, "create table t (a int)") 1727 cases := []struct { 1728 sql string 1729 field string 1730 }{ 1731 {"select distinct(a) from t", "a"}, 1732 {"select (1)", "1"}, 1733 {"select (1+1)", "(1+1)"}, 1734 {"select a from t", "a"}, 1735 {"select ((a+1)) from t", "((a+1))"}, 1736 } 1737 for _, v := range cases { 1738 results, err := se.Execute(v.sql) 1739 c.Assert(err, IsNil) 1740 result := results[0] 1741 fields, err := result.Fields() 1742 c.Assert(err, IsNil) 1743 c.Assert(fields[0].ColumnAsName.O, Equals, v.field) 1744 } 1745 1746 err := store.Close() 1747 c.Assert(err, IsNil) 1748 } 1749 1750 func (s *testSessionSuite) TestIndexPointLookup(c *C) { 1751 defer testleak.AfterTest(c)() 1752 store := newStore(c, s.dbName) 1753 se := newSession(c, store, s.dbName) 1754 mustExecSQL(c, se, "drop table if exists t") 1755 mustExecSQL(c, se, "create table t (a int)") 1756 mustExecSQL(c, se, "insert t values (1), (2), (3)") 1757 mustExecMatch(c, se, "select * from t where a = '1.1';", [][]interface{}{}) 1758 mustExecMatch(c, se, "select * from t where a > '1.1';", [][]interface{}{{2}, {3}}) 1759 mustExecMatch(c, se, "select * from t where a = '2';", [][]interface{}{{2}}) 1760 mustExecMatch(c, se, "select * from t where a = 3;", [][]interface{}{{3}}) 1761 mustExecMatch(c, se, "select * from t where a = 4;", [][]interface{}{}) 1762 mustExecSQL(c, se, "drop table t") 1763 1764 err := store.Close() 1765 c.Assert(err, IsNil) 1766 } 1767 1768 func (s *testSessionSuite) TestIssue454(c *C) { 1769 defer testleak.AfterTest(c)() 1770 store := newStore(c, s.dbName) 1771 se := newSession(c, store, s.dbName) 1772 1773 mustExecSQL(c, se, "drop table if exists t") 1774 mustExecSQL(c, se, "drop table if exists t1") 1775 mustExecSQL(c, se, "create table t1 (c1 int, c2 int, c3 int);") 1776 mustExecSQL(c, se, "insert into t1 set c1=1, c2=2, c3=1;") 1777 mustExecSQL(c, se, "create table t (c1 int, c2 int, c3 int, primary key (c1));") 1778 mustExecSQL(c, se, "insert into t set c1=1, c2=4;") 1779 mustExecSQL(c, se, "insert into t select * from t1 limit 1 on duplicate key update c3=3333;") 1780 1781 err := store.Close() 1782 c.Assert(err, IsNil) 1783 } 1784 1785 func (s *testSessionSuite) TestIssue456(c *C) { 1786 defer testleak.AfterTest(c)() 1787 store := newStore(c, s.dbName) 1788 se := newSession(c, store, s.dbName) 1789 1790 mustExecSQL(c, se, "drop table if exists t") 1791 mustExecSQL(c, se, "drop table if exists t1") 1792 mustExecSQL(c, se, "create table t1 (c1 int, c2 int, c3 int);") 1793 mustExecSQL(c, se, "replace into t1 set c1=1, c2=2, c3=1;") 1794 mustExecSQL(c, se, "create table t (c1 int, c2 int, c3 int, primary key (c1));") 1795 mustExecSQL(c, se, "replace into t set c1=1, c2=4;") 1796 mustExecSQL(c, se, "replace into t select * from t1 limit 1;") 1797 1798 err := store.Close() 1799 c.Assert(err, IsNil) 1800 } 1801 1802 // For https://yougam/libraries/pingcap/tidb/issues/571 1803 func (s *testSessionSuite) TestIssue571(c *C) { 1804 defer testleak.AfterTest(c)() 1805 store := newStore(c, s.dbName) 1806 se := newSession(c, store, s.dbName) 1807 1808 mustExecSQL(c, se, "begin") 1809 mustExecSQL(c, se, "drop table if exists t") 1810 mustExecSQL(c, se, "create table t (c int)") 1811 mustExecSQL(c, se, "insert t values (1), (2), (3)") 1812 mustExecSQL(c, se, "commit") 1813 1814 se1 := newSession(c, store, s.dbName) 1815 se1.(*session).maxRetryCnt = unlimitedRetryCnt 1816 mustExecSQL(c, se1, "SET SESSION autocommit=1;") 1817 se2 := newSession(c, store, s.dbName) 1818 se2.(*session).maxRetryCnt = unlimitedRetryCnt 1819 mustExecSQL(c, se2, "SET SESSION autocommit=1;") 1820 se3 := newSession(c, store, s.dbName) 1821 se3.(*session).maxRetryCnt = unlimitedRetryCnt 1822 mustExecSQL(c, se3, "SET SESSION autocommit=0;") 1823 1824 var wg sync.WaitGroup 1825 wg.Add(3) 1826 f1 := func() { 1827 defer wg.Done() 1828 for i := 0; i < 30; i++ { 1829 mustExecSQL(c, se1, "update t set c = 1;") 1830 } 1831 } 1832 f2 := func() { 1833 defer wg.Done() 1834 for i := 0; i < 30; i++ { 1835 mustExecSQL(c, se2, "update t set c = ?;", 1) 1836 } 1837 } 1838 f3 := func() { 1839 defer wg.Done() 1840 for i := 0; i < 30; i++ { 1841 mustExecSQL(c, se3, "begin") 1842 mustExecSQL(c, se3, "update t set c = 1;") 1843 mustExecSQL(c, se3, "commit") 1844 } 1845 } 1846 go f1() 1847 go f2() 1848 go f3() 1849 wg.Wait() 1850 1851 err := store.Close() 1852 c.Assert(err, IsNil) 1853 } 1854 1855 func (s *testSessionSuite) TestIssue620(c *C) { 1856 defer testleak.AfterTest(c)() 1857 store := newStore(c, s.dbName) 1858 se := newSession(c, store, s.dbName) 1859 1860 mustExecSQL(c, se, "drop table if exists t1") 1861 mustExecSQL(c, se, "drop table if exists t2") 1862 mustExecSQL(c, se, "drop table if exists t3") 1863 mustExecSQL(c, se, "create table t1(id int primary key auto_increment, c int);") 1864 mustExecSQL(c, se, "create table t2(c int);") 1865 mustExecSQL(c, se, "insert into t2 values (1);") 1866 mustExecSQL(c, se, "create table t3(id int, c int);") 1867 mustExecSQL(c, se, "insert into t3 values (2,2);") 1868 mustExecSQL(c, se, "insert into t1(c) select * from t2; insert into t1 select * from t3;") 1869 mustExecMatch(c, se, "select * from t1;", [][]interface{}{{1, 1}, {2, 2}}) 1870 1871 err := store.Close() 1872 c.Assert(err, IsNil) 1873 } 1874 1875 func (s *testSessionSuite) TestRetryPreparedStmt(c *C) { 1876 defer testleak.AfterTest(c)() 1877 store := newStore(c, s.dbName) 1878 se := newSession(c, store, s.dbName) 1879 se1 := newSession(c, store, s.dbName) 1880 se2 := newSession(c, store, s.dbName) 1881 1882 mustExecSQL(c, se, "drop table if exists t") 1883 c.Assert(se.(*session).txn, IsNil) 1884 mustExecSQL(c, se, "create table t (c1 int, c2 int, c3 int)") 1885 mustExecSQL(c, se, "insert t values (11, 2, 3)") 1886 1887 mustExecSQL(c, se1, "begin") 1888 mustExecSQL(c, se1, "update t set c2=? where c1=11;", 21) 1889 1890 mustExecSQL(c, se2, "begin") 1891 mustExecSQL(c, se2, "update t set c2=? where c1=11", 22) 1892 mustExecSQL(c, se2, "commit") 1893 1894 mustExecSQL(c, se1, "commit") 1895 1896 se3 := newSession(c, store, s.dbName) 1897 r := mustExecSQL(c, se3, "select c2 from t where c1=11") 1898 row, err := r.Next() 1899 c.Assert(err, IsNil) 1900 match(c, row.Data, 21) 1901 1902 err = store.Close() 1903 c.Assert(err, IsNil) 1904 } 1905 1906 func (s *testSessionSuite) TestIssue893(c *C) { 1907 defer testleak.AfterTest(c)() 1908 store := newStore(c, s.dbName) 1909 se := newSession(c, store, s.dbName) 1910 mustExecSQL(c, se, "drop table if exists t1; create table t1(id int ); insert into t1 values (1);") 1911 mustExecMatch(c, se, "select * from t1;", [][]interface{}{{1}}) 1912 1913 err := store.Close() 1914 c.Assert(err, IsNil) 1915 } 1916 1917 // Testcase for session 1918 func (s *testSessionSuite) TestSession(c *C) { 1919 defer testleak.AfterTest(c)() 1920 store := newStore(c, s.dbName) 1921 se := newSession(c, store, s.dbName) 1922 mustExecSQL(c, se, "ROLLBACK;") 1923 1924 err := se.Close() 1925 c.Assert(err, IsNil) 1926 err = store.Close() 1927 c.Assert(err, IsNil) 1928 } 1929 1930 func (s *testSessionSuite) TestSessionAuth(c *C) { 1931 defer testleak.AfterTest(c)() 1932 store := newStore(c, s.dbName) 1933 se := newSession(c, store, s.dbName) 1934 defer se.Close() 1935 c.Assert(se.Auth("Any not exist username with zero password! @anyhost", []byte(""), []byte("")), IsFalse) 1936 1937 err := store.Close() 1938 c.Assert(err, IsNil) 1939 } 1940 1941 func (s *testSessionSuite) TestErrorRollback(c *C) { 1942 defer testleak.AfterTest(c)() 1943 store := newStore(c, s.dbName) 1944 s1 := newSession(c, store, s.dbName) 1945 1946 defer s1.Close() 1947 1948 mustExecSQL(c, s1, "drop table if exists t_rollback") 1949 mustExecSQL(c, s1, "create table t_rollback (c1 int, c2 int, primary key(c1))") 1950 1951 _, err := s1.Execute("insert into t_rollback values (0, 0)") 1952 c.Assert(err, IsNil) 1953 1954 var wg sync.WaitGroup 1955 cnt := 4 1956 wg.Add(cnt) 1957 num := 100 1958 1959 for i := 0; i < cnt; i++ { 1960 go func() { 1961 defer wg.Done() 1962 se := newSession(c, store, s.dbName) 1963 // retry forever 1964 se.(*session).maxRetryCnt = unlimitedRetryCnt 1965 defer se.Close() 1966 1967 for j := 0; j < num; j++ { 1968 // force generate a txn in session for later insert use. 1969 se.(*session).GetTxn(false) 1970 1971 se.Execute("insert into t_rollback values (1, 1)") 1972 1973 _, err1 := se.Execute("update t_rollback set c2 = c2 + 1 where c1 = 0") 1974 c.Assert(err1, IsNil) 1975 } 1976 }() 1977 } 1978 1979 wg.Wait() 1980 1981 mustExecMatch(c, s1, "select c2 from t_rollback where c1 = 0", [][]interface{}{{cnt * num}}) 1982 1983 err = store.Close() 1984 c.Assert(err, IsNil) 1985 } 1986 1987 func (s *testSessionSuite) TestMultiColumnIndex(c *C) { 1988 defer testleak.AfterTest(c)() 1989 store := newStore(c, s.dbName) 1990 se := newSession(c, store, s.dbName) 1991 mustExecSQL(c, se, "drop table if exists t;") 1992 mustExecSQL(c, se, "create table t (c1 int, c2 int);") 1993 mustExecSQL(c, se, "create index idx_c1_c2 on t (c1, c2)") 1994 mustExecSQL(c, se, "insert into t values (1, 5)") 1995 1996 sql := "select c1 from t where c1 in (1) and c2 < 10" 1997 expectedExplain := "Index(t.idx_c1_c2)->Fields" 1998 checkPlan(c, se, sql, expectedExplain) 1999 mustExecMatch(c, se, sql, [][]interface{}{{1}}) 2000 2001 sql = "select c1 from t where c1 in (1) and c2 > 3" 2002 checkPlan(c, se, sql, expectedExplain) 2003 mustExecMatch(c, se, sql, [][]interface{}{{1}}) 2004 2005 sql = "select c1 from t where c1 in (1.1) and c2 > 3" 2006 checkPlan(c, se, sql, expectedExplain) 2007 mustExecMatch(c, se, sql, [][]interface{}{}) 2008 2009 sql = "select c1 from t where c1 in (1) and c2 < 5.1" 2010 checkPlan(c, se, sql, expectedExplain) 2011 mustExecMatch(c, se, sql, [][]interface{}{{1}}) 2012 2013 // Test varchar type. 2014 mustExecSQL(c, se, "drop table t;") 2015 mustExecSQL(c, se, "create table t (c1 varchar(64), c2 varchar(64), index c1_c2 (c1, c2));") 2016 mustExecSQL(c, se, "insert into t values ('abc', 'def')") 2017 sql = "select c1 from t where c1 = 'abc'" 2018 mustExecMatch(c, se, sql, [][]interface{}{{[]byte("abc")}}) 2019 2020 err := se.Close() 2021 c.Assert(err, IsNil) 2022 err = store.Close() 2023 c.Assert(err, IsNil) 2024 } 2025 2026 func (s *testSessionSuite) TestSubstringIndexExpr(c *C) { 2027 defer testleak.AfterTest(c)() 2028 store := newStore(c, s.dbName) 2029 se := newSession(c, store, s.dbName) 2030 mustExecSQL(c, se, "drop table if exists t;") 2031 mustExecSQL(c, se, "create table t (c varchar(128));") 2032 mustExecSQL(c, se, `insert into t values ("www.pingcap.com");`) 2033 mustExecMatch(c, se, "SELECT DISTINCT SUBSTRING_INDEX(c, '.', 2) from t;", [][]interface{}{{"www.pingcap"}}) 2034 2035 err := store.Close() 2036 c.Assert(err, IsNil) 2037 } 2038 2039 func (s *testSessionSuite) TestIgnoreForeignKey(c *C) { 2040 c.Skip("skip panic") 2041 defer testleak.AfterTest(c)() 2042 sqlText := `CREATE TABLE address ( 2043 id bigint(20) NOT NULL AUTO_INCREMENT, 2044 user_id bigint(20) NOT NULL, 2045 PRIMARY KEY (id), 2046 CONSTRAINT FK_7rod8a71yep5vxasb0ms3osbg FOREIGN KEY (user_id) REFERENCES waimaiqa.user (id), 2047 INDEX FK_7rod8a71yep5vxasb0ms3osbg (user_id) comment '' 2048 ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPACT COMMENT='' CHECKSUM=0 DELAY_KEY_WRITE=0;` 2049 store := newStore(c, s.dbName) 2050 se := newSession(c, store, s.dbName) 2051 mustExecSQL(c, se, sqlText) 2052 2053 err := store.Close() 2054 c.Assert(err, IsNil) 2055 } 2056 2057 func (s *testSessionSuite) TestJoinSubquery(c *C) { 2058 defer testleak.AfterTest(c)() 2059 store := newStore(c, s.dbName) 2060 se := newSession(c, store, s.dbName) 2061 mustExecSQL(c, se, "CREATE TABLE table1 (id INTEGER key AUTO_INCREMENT, data VARCHAR(30))") 2062 mustExecSQL(c, se, "CREATE TABLE table2 (id INTEGER key AUTO_INCREMENT, data VARCHAR(30), t1id INTEGER)") 2063 sqlTxt := `SELECT table1.id AS table1_id, table1.data AS table1_data FROM 2064 table1 INNER JOIN ( 2065 SELECT table2.id AS id, table2.data AS data, table2.t1id AS t1id FROM table2 2066 ) AS anon_1 ON table1.id = anon_1.t1id;` 2067 mustExecSQL(c, se, sqlTxt) 2068 2069 err := store.Close() 2070 c.Assert(err, IsNil) 2071 } 2072 2073 func (s *testSessionSuite) TestGlobalVarAccessor(c *C) { 2074 defer testleak.AfterTest(c)() 2075 2076 varName := "max_allowed_packet" 2077 varValue := "4194304" // This is the default value for max_allowed_packet 2078 varValue1 := "4194305" 2079 varValue2 := "4194306" 2080 2081 store := newStore(c, s.dbName) 2082 se := newSession(c, store, s.dbName).(*session) 2083 // Get globalSysVar twice and get the same value 2084 v, err := se.GetGlobalSysVar(se, varName) 2085 c.Assert(err, IsNil) 2086 c.Assert(v, Equals, varValue) 2087 v, err = se.GetGlobalSysVar(se, varName) 2088 c.Assert(err, IsNil) 2089 c.Assert(v, Equals, varValue) 2090 // Set global var to another value 2091 err = se.SetGlobalSysVar(se, varName, varValue1) 2092 c.Assert(err, IsNil) 2093 v, err = se.GetGlobalSysVar(se, varName) 2094 c.Assert(err, IsNil) 2095 c.Assert(v, Equals, varValue1) 2096 c.Assert(se.FinishTxn(false), IsNil) 2097 2098 // Change global variable value in another session 2099 se1 := newSession(c, store, s.dbName).(*session) 2100 v, err = se1.GetGlobalSysVar(se1, varName) 2101 c.Assert(err, IsNil) 2102 c.Assert(v, Equals, varValue1) 2103 err = se1.SetGlobalSysVar(se1, varName, varValue2) 2104 c.Assert(err, IsNil) 2105 v, err = se1.GetGlobalSysVar(se1, varName) 2106 c.Assert(err, IsNil) 2107 c.Assert(v, Equals, varValue2) 2108 c.Assert(se1.FinishTxn(false), IsNil) 2109 2110 // Make sure the change is visible to any client that accesses that global variable. 2111 v, err = se.GetGlobalSysVar(se, varName) 2112 c.Assert(err, IsNil) 2113 c.Assert(v, Equals, varValue2) 2114 2115 err = store.Close() 2116 c.Assert(err, IsNil) 2117 } 2118 2119 func checkPlan(c *C, se Session, sql, explain string) { 2120 ctx := se.(context.Context) 2121 stmts, err := Parse(ctx, sql) 2122 c.Assert(err, IsNil) 2123 stmt := stmts[0] 2124 is := sessionctx.GetDomain(ctx).InfoSchema() 2125 err = optimizer.Prepare(is, ctx, stmt) 2126 c.Assert(err, IsNil) 2127 p, err := optimizer.Optimize(ctx, stmt, executor.NewSubQueryBuilder(is)) 2128 c.Assert(err, IsNil) 2129 c.Assert(plan.ToString(p), Equals, explain) 2130 } 2131 2132 func mustExecMultiSQL(c *C, se Session, sql string) { 2133 ss := strings.Split(sql, "\n") 2134 for _, s := range ss { 2135 s = strings.TrimSpace(s) 2136 if len(s) == 0 { 2137 continue 2138 } 2139 mustExecSQL(c, se, s) 2140 } 2141 } 2142 2143 func (s *testSessionSuite) TestSqlLogicTestCase(c *C) { 2144 initSQL := ` 2145 CREATE TABLE tab1(pk INTEGER PRIMARY KEY, col0 INTEGER, col1 FLOAT) 2146 INSERT INTO tab1 VALUES(0,26,690.51) 2147 CREATE INDEX idx_tab1_1 on tab1 (col1)` 2148 store := newStore(c, s.dbName) 2149 se := newSession(c, store, s.dbName) 2150 mustExecMultiSQL(c, se, initSQL) 2151 2152 sql := "SELECT col0 FROM tab1 WHERE 71*22 >= col1" 2153 mustExecMatch(c, se, sql, [][]interface{}{{"26"}}) 2154 }