func (s *testSuite) TestInsertIgnore(c *C) {
defer func() {
s.cleanEnv(c)
testleak.AfterTest(c)()
}()
var cfg kv.InjectionConfig
tk := testkit.NewTestKit(c, kv.NewInjectedStore(s.store, &cfg))
tk.MustExec("use test")
testSQL := `drop table if exists t;
create table t (id int PRIMARY KEY AUTO_INCREMENT, c1 int);`
tk.MustExec(testSQL)
testSQL = `insert into t values (1, 2);`
tk.MustExec(testSQL)
r := tk.MustQuery("select * from t;")
rowStr := fmt.Sprintf("%v %v", "1", "2")
r.Check(testkit.Rows(rowStr))
tk.MustExec("insert ignore into t values (1, 3), (2, 3)")
r = tk.MustQuery("select * from t;")
rowStr = fmt.Sprintf("%v %v", "1", "2")
rowStr1 := fmt.Sprintf("%v %v", "2", "3")
r.Check(testkit.Rows(rowStr, rowStr1))
cfg.SetGetError(errors.New("foo"))
_, err := tk.Exec("insert ignore into t values (1, 3)")
c.Assert(err, NotNil)
cfg.SetGetError(nil)
}
func (s *testSuite) TestMultipleTableUpdate(c *C) {
defer func() {
s.cleanEnv(c)
testleak.AfterTest(c)()
}()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
s.fillMultiTableForUpdate(tk)
tk.MustExec(`UPDATE items, month SET items.price=month.mprice WHERE items.id=month.mid;`)
tk.MustExec("begin")
r := tk.MustQuery("SELECT * FROM items")
rowStr1 := fmt.Sprintf("%v %v", 11, []byte("month_price_11"))
rowStr2 := fmt.Sprintf("%v %v", 12, []byte("items_price_12"))
rowStr3 := fmt.Sprintf("%v %v", 13, []byte("month_price_13"))
r.Check(testkit.Rows(rowStr1, rowStr2, rowStr3))
tk.MustExec("commit")
// Single-table syntax but with multiple tables
tk.MustExec(`UPDATE items join month on items.id=month.mid SET items.price=month.mid;`)
tk.MustExec("begin")
r = tk.MustQuery("SELECT * FROM items")
rowStr1 = fmt.Sprintf("%v %v", 11, []byte("11"))
rowStr2 = fmt.Sprintf("%v %v", 12, []byte("items_price_12"))
rowStr3 = fmt.Sprintf("%v %v", 13, []byte("13"))
r.Check(testkit.Rows(rowStr1, rowStr2, rowStr3))
tk.MustExec("commit")
// JoinTable with alias table name.
tk.MustExec(`UPDATE items T0 join month T1 on T0.id=T1.mid SET T0.price=T1.mprice;`)
tk.MustExec("begin")
r = tk.MustQuery("SELECT * FROM items")
rowStr1 = fmt.Sprintf("%v %v", 11, []byte("month_price_11"))
rowStr2 = fmt.Sprintf("%v %v", 12, []byte("items_price_12"))
rowStr3 = fmt.Sprintf("%v %v", 13, []byte("month_price_13"))
r.Check(testkit.Rows(rowStr1, rowStr2, rowStr3))
tk.MustExec("commit")
// fix https://github.com/pingcap/tidb/issues/369
testSQL := `
DROP TABLE IF EXISTS t1, t2;
create table t1 (c int);
create table t2 (c varchar(256));
insert into t1 values (1), (2);
insert into t2 values ("a"), ("b");
update t1, t2 set t1.c = 10, t2.c = "abc";`
tk.MustExec(testSQL)
// fix https://github.com/pingcap/tidb/issues/376
testSQL = `DROP TABLE IF EXISTS t1, t2;
create table t1 (c1 int);
create table t2 (c2 int);
insert into t1 values (1), (2);
insert into t2 values (1), (2);
update t1, t2 set t1.c1 = 10, t2.c2 = 2 where t2.c2 = 1;`
tk.MustExec(testSQL)
r = tk.MustQuery("select * from t1")
r.Check(testkit.Rows("10", "10"))
}
func (s *testSuite) TestUpdate(c *C) {
defer func() {
s.cleanEnv(c)
testleak.AfterTest(c)()
}()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
s.fillData(tk, "update_test")
updateStr := `UPDATE update_test SET name = "abc" where id > 0;`
tk.MustExec(updateStr)
tk.CheckExecResult(2, 0)
// select data
tk.MustExec("begin")
r := tk.MustQuery(`SELECT * from update_test limit 2;`)
rowStr1 := fmt.Sprintf("%v %v", 1, []byte("abc"))
rowStr2 := fmt.Sprintf("%v %v", 2, []byte("abc"))
r.Check(testkit.Rows(rowStr1, rowStr2))
tk.MustExec("commit")
tk.MustExec(`UPDATE update_test SET name = "foo"`)
tk.CheckExecResult(2, 0)
// table option is auto-increment
tk.MustExec("begin")
tk.MustExec("drop table if exists update_test;")
tk.MustExec("commit")
tk.MustExec("begin")
tk.MustExec("create table update_test(id int not null auto_increment, name varchar(255), primary key(id))")
tk.MustExec("insert into update_test(name) values ('aa')")
tk.MustExec("update update_test set id = 8 where name = 'aa'")
tk.MustExec("insert into update_test(name) values ('bb')")
tk.MustExec("commit")
tk.MustExec("begin")
r = tk.MustQuery("select * from update_test;")
rowStr1 = fmt.Sprintf("%v %v", 8, []byte("aa"))
rowStr2 = fmt.Sprintf("%v %v", 9, []byte("bb"))
r.Check(testkit.Rows(rowStr1, rowStr2))
tk.MustExec("commit")
tk.MustExec("begin")
tk.MustExec("drop table if exists update_test;")
tk.MustExec("commit")
tk.MustExec("begin")
tk.MustExec("create table update_test(id int not null auto_increment, name varchar(255), index(id))")
tk.MustExec("insert into update_test(name) values ('aa')")
_, err := tk.Exec("update update_test set id = null where name = 'aa'")
c.Assert(err, NotNil)
c.Assert(err.Error(), DeepEquals, "Column 'id' cannot be null")
tk.MustExec("drop table update_test")
tk.MustExec("create table update_test(id int)")
tk.MustExec("begin")
tk.MustExec("insert into update_test(id) values (1)")
tk.MustExec("update update_test set id = 2 where id = 1 limit 1")
r = tk.MustQuery("select * from update_test;")
r.Check(testkit.Rows("2"))
tk.MustExec("commit")
}
func (s *testSuite) TestGrantGlobal(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
// Create a new user.
createUserSQL := `CREATE USER 'testGlobal'@'localhost' IDENTIFIED BY '123';`
tk.MustExec(createUserSQL)
// Make sure all the global privs for new user is "N".
for _, v := range mysql.AllDBPrivs {
sql := fmt.Sprintf("SELECT %s FROM mysql.User WHERE User=\"testGlobal\" and host=\"localhost\";", mysql.Priv2UserCol[v])
r := tk.MustQuery(sql)
r.Check(testkit.Rows("N"))
}
// Grant each priv to the user.
for _, v := range mysql.AllGlobalPrivs {
sql := fmt.Sprintf("GRANT %s ON *.* TO 'testGlobal'@'localhost';", mysql.Priv2Str[v])
tk.MustExec(sql)
sql = fmt.Sprintf("SELECT %s FROM mysql.User WHERE User=\"testGlobal\" and host=\"localhost\"", mysql.Priv2UserCol[v])
tk.MustQuery(sql).Check(testkit.Rows("Y"))
}
// Create a new user.
createUserSQL = `CREATE USER 'testGlobal1'@'localhost' IDENTIFIED BY '123';`
tk.MustExec(createUserSQL)
tk.MustExec("GRANT ALL ON *.* TO 'testGlobal1'@'localhost';")
// Make sure all the global privs for granted user is "Y".
for _, v := range mysql.AllGlobalPrivs {
sql := fmt.Sprintf("SELECT %s FROM mysql.User WHERE User=\"testGlobal1\" and host=\"localhost\"", mysql.Priv2UserCol[v])
tk.MustQuery(sql).Check(testkit.Rows("Y"))
}
}
func (s *testSuite) TestGrantDBScope(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
// Create a new user.
createUserSQL := `CREATE USER 'testDB'@'localhost' IDENTIFIED BY '123';`
tk.MustExec(createUserSQL)
// Make sure all the db privs for new user is empty.
sql := fmt.Sprintf("SELECT * FROM mysql.db WHERE User=\"testDB\" and host=\"localhost\"")
tk.MustQuery(sql).Check(testkit.Rows())
// Grant each priv to the user.
for _, v := range mysql.AllDBPrivs {
sql := fmt.Sprintf("GRANT %s ON test.* TO 'testDB'@'localhost';", mysql.Priv2Str[v])
tk.MustExec(sql)
sql = fmt.Sprintf("SELECT %s FROM mysql.DB WHERE User=\"testDB\" and host=\"localhost\" and db=\"test\"", mysql.Priv2UserCol[v])
tk.MustQuery(sql).Check(testkit.Rows("Y"))
}
// Create a new user.
createUserSQL = `CREATE USER 'testDB1'@'localhost' IDENTIFIED BY '123';`
tk.MustExec(createUserSQL)
tk.MustExec("USE test;")
tk.MustExec("GRANT ALL ON * TO 'testDB1'@'localhost';")
// Make sure all the db privs for granted user is "Y".
for _, v := range mysql.AllDBPrivs {
sql := fmt.Sprintf("SELECT %s FROM mysql.DB WHERE User=\"testDB1\" and host=\"localhost\" and db=\"test\";", mysql.Priv2UserCol[v])
tk.MustQuery(sql).Check(testkit.Rows("Y"))
}
}
func (s *testSuite) TestSetVar(c *C) {
plan.UseNewPlanner = true
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
testSQL := "SET @a = 1;"
tk.MustExec(testSQL)
testSQL = `SET @a = "1";`
tk.MustExec(testSQL)
testSQL = "SET @a = null;"
tk.MustExec(testSQL)
testSQL = "SET @@global.autocommit = 1;"
tk.MustExec(testSQL)
// TODO: this test case should returns error.
// testSQL = "SET @@global.autocommit = null;"
// _, err := tk.Exec(testSQL)
// c.Assert(err, NotNil)
testSQL = "SET @@autocommit = 1;"
tk.MustExec(testSQL)
testSQL = "SET @@autocommit = null;"
_, err := tk.Exec(testSQL)
c.Assert(err, NotNil)
errTestSql := "SET @@date_format = 1;"
_, err = tk.Exec(errTestSql)
c.Assert(err, NotNil)
errTestSql = "SET @@rewriter_enabled = 1;"
_, err = tk.Exec(errTestSql)
c.Assert(err, NotNil)
errTestSql = "SET xxx = abcd;"
_, err = tk.Exec(errTestSql)
c.Assert(err, NotNil)
errTestSql = "SET @@global.a = 1;"
_, err = tk.Exec(errTestSql)
c.Assert(err, NotNil)
errTestSql = "SET @@global.timestamp = 1;"
_, err = tk.Exec(errTestSql)
c.Assert(err, NotNil)
// For issue 998
testSQL = "SET @issue998a=1, @issue998b=5;"
tk.MustExec(testSQL)
tk.MustQuery(`select @issue998a, @issue998b;`).Check(testkit.Rows("1 5"))
testSQL = "SET @@autocommit=0, @issue998a=2;"
tk.MustExec(testSQL)
tk.MustQuery(`select @issue998a, @@autocommit;`).Check(testkit.Rows("2 0"))
testSQL = "SET @@global.autocommit=1, @issue998b=6;"
tk.MustExec(testSQL)
tk.MustQuery(`select @issue998b, @@global.autocommit;`).Check(testkit.Rows("6 1"))
plan.UseNewPlanner = false
}
func (s *testSuite) TestSetPwd(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
createUserSQL := `CREATE USER 'testpwd'@'localhost' IDENTIFIED BY '';`
tk.MustExec(createUserSQL)
result := tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr := fmt.Sprintf("%v", []byte(""))
result.Check(testkit.Rows(rowStr))
// set password for
tk.MustExec(`SET PASSWORD FOR 'testpwd'@'localhost' = 'password';`)
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr = fmt.Sprintf("%v", []byte(util.EncodePassword("password")))
result.Check(testkit.Rows(rowStr))
// set password
setPwdSQL := `SET PASSWORD = '******'`
// Session user is empty.
_, err := tk.Exec(setPwdSQL)
c.Check(err, NotNil)
tk.Se, err = tidb.CreateSession(s.store)
c.Check(err, IsNil)
ctx := tk.Se.(context.Context)
ctx.GetSessionVars().User = "******"
// Session user doesn't exist.
_, err = tk.Exec(setPwdSQL)
c.Check(terror.ErrorEqual(err, executor.ErrPasswordNoMatch), IsTrue)
// normal
ctx.GetSessionVars().User = "******"
tk.MustExec(setPwdSQL)
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr = fmt.Sprintf("%v", []byte(util.EncodePassword("pwd")))
result.Check(testkit.Rows(rowStr))
}
func (s *testDBSuite) TestUpdateMultipleTable(c *C) {
defer testleak.AfterTest(c)
store, err := tidb.NewStore("memory://update_multiple_table")
c.Assert(err, IsNil)
tk := testkit.NewTestKit(c, store)
tk.MustExec("use test")
tk.MustExec("create table t1 (c1 int, c2 int)")
tk.MustExec("insert t1 values (1, 1), (2, 2)")
tk.MustExec("create table t2 (c1 int, c2 int)")
tk.MustExec("insert t2 values (1, 3), (2, 5)")
ctx := tk.Se.(context.Context)
domain := sessionctx.GetDomain(ctx)
is := domain.InfoSchema()
db, ok := is.SchemaByName(model.NewCIStr("test"))
c.Assert(ok, IsTrue)
t1Tbl, err := is.TableByName(model.NewCIStr("test"), model.NewCIStr("t1"))
c.Assert(err, IsNil)
t1Info := t1Tbl.Meta()
// Add a new column in write only state.
newColumn := &model.ColumnInfo{
ID: 100,
Name: model.NewCIStr("c3"),
Offset: 2,
DefaultValue: 9,
FieldType: *types.NewFieldType(mysql.TypeLonglong),
State: model.StateWriteOnly,
}
t1Info.Columns = append(t1Info.Columns, newColumn)
kv.RunInNewTxn(store, false, func(txn kv.Transaction) error {
m := meta.NewMeta(txn)
_, err = m.GenSchemaVersion()
c.Assert(err, IsNil)
c.Assert(m.UpdateTable(db.ID, t1Info), IsNil)
return nil
})
err = domain.Reload()
c.Assert(err, IsNil)
tk.MustExec("update t1, t2 set t1.c1 = 8, t2.c2 = 10 where t1.c2 = t2.c1")
tk.MustQuery("select * from t1").Check(testkit.Rows("8 1", "8 2"))
tk.MustQuery("select * from t2").Check(testkit.Rows("1 10", "2 10"))
newColumn.State = model.StatePublic
kv.RunInNewTxn(store, false, func(txn kv.Transaction) error {
m := meta.NewMeta(txn)
_, err = m.GenSchemaVersion()
c.Assert(err, IsNil)
c.Assert(m.UpdateTable(db.ID, t1Info), IsNil)
return nil
})
err = domain.Reload()
c.Assert(err, IsNil)
tk.MustQuery("select * from t1").Check(testkit.Rows("8 1 9", "8 2 9"))
}
func (s *testSuite) TestCreateUser(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
// Make sure user test not in mysql.User.
result := tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
result.Check(nil)
// Create user test.
createUserSQL := `CREATE USER 'test'@'localhost' IDENTIFIED BY '123';`
tk.MustExec(createUserSQL)
// Make sure user test in mysql.User.
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr := fmt.Sprintf("%v", []byte(util.EncodePassword("123")))
result.Check(testkit.Rows(rowStr))
// Create duplicate user with IfNotExists will be success.
createUserSQL = `CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY '123';`
tk.MustExec(createUserSQL)
// Create duplicate user without IfNotExists will cause error.
createUserSQL = `CREATE USER 'test'@'localhost' IDENTIFIED BY '123';`
_, err := tk.Exec(createUserSQL)
c.Check(err, NotNil)
dropUserSQL := `DROP USER IF EXISTS 'test'@'localhost' ;`
tk.MustExec(dropUserSQL)
// Create user test.
createUserSQL = `CREATE USER 'test1'@'localhost';`
tk.MustExec(createUserSQL)
// Make sure user test in mysql.User.
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr = fmt.Sprintf("%v", []byte(util.EncodePassword("")))
result.Check(testkit.Rows(rowStr))
dropUserSQL = `DROP USER IF EXISTS 'test1'@'localhost' ;`
tk.MustExec(dropUserSQL)
// Test drop user if exists.
createUserSQL = `CREATE USER 'test1'@'localhost', 'test3'@'localhost';`
tk.MustExec(createUserSQL)
dropUserSQL = `DROP USER IF EXISTS 'test1'@'localhost', 'test2'@'localhost', 'test3'@'localhost' ;`
tk.MustExec(dropUserSQL)
// Test negative cases without IF EXISTS.
createUserSQL = `CREATE USER 'test1'@'localhost', 'test3'@'localhost';`
tk.MustExec(createUserSQL)
dropUserSQL = `DROP USER 'test1'@'localhost', 'test2'@'localhost', 'test3'@'localhost';`
_, err = tk.Exec(dropUserSQL)
c.Check(err, NotNil)
dropUserSQL = `DROP USER 'test3'@'localhost';`
_, err = tk.Exec(dropUserSQL)
c.Check(err, NotNil)
dropUserSQL = `DROP USER 'test1'@'localhost';`
_, err = tk.Exec(dropUserSQL)
c.Check(err, NotNil)
// Test positive cases without IF EXISTS.
createUserSQL = `CREATE USER 'test1'@'localhost', 'test3'@'localhost';`
tk.MustExec(createUserSQL)
dropUserSQL = `DROP USER 'test1'@'localhost', 'test3'@'localhost';`
tk.MustExec(dropUserSQL)
}
func (s *testSuite) TestShow(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
testSQL := `drop table if exists show_test`
tk.MustExec(testSQL)
testSQL = `create table show_test (id int PRIMARY KEY AUTO_INCREMENT, c1 int comment "c1_comment", c2 int, c3 int default 1) ENGINE=InnoDB AUTO_INCREMENT=28934 DEFAULT CHARSET=utf8 COMMENT "table_comment";`
tk.MustExec(testSQL)
testSQL = "show columns from show_test;"
result := tk.MustQuery(testSQL)
c.Check(result.Rows(), HasLen, 4)
testSQL = "show create table show_test;"
result = tk.MustQuery(testSQL)
c.Check(result.Rows(), HasLen, 1)
row := result.Rows()[0]
// For issue https://github.com/pingcap/tidb/issues/1061
expectedRow := []interface{}{
"show_test", "CREATE TABLE `show_test` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `c1` int(11) DEFAULT NULL COMMENT 'c1_comment',\n `c2` int(11) DEFAULT NULL,\n `c3` int(11) DEFAULT '1',\n PRIMARY KEY (`id`) \n) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=28934 COMMENT='table_comment'"}
for i, r := range row {
c.Check(r, Equals, expectedRow[i])
}
testSQL = "SHOW VARIABLES LIKE 'character_set_results';"
result = tk.MustQuery(testSQL)
c.Check(result.Rows(), HasLen, 1)
// Test case for index type and comment
tk.MustExec(`create table show_index (c int, index cIdx using hash (c) comment "index_comment_for_cIdx");`)
testSQL = "SHOW index from show_index;"
result = tk.MustQuery(testSQL)
c.Check(result.Rows(), HasLen, 1)
expectedRow = []interface{}{
"show_index", int64(1), "cIdx", int64(1), "c", "utf8_bin",
int64(0), nil, nil, "YES", "HASH", "", "index_comment_for_cIdx"}
row = result.Rows()[0]
c.Check(row, HasLen, len(expectedRow))
for i, r := range row {
c.Check(r, Equals, expectedRow[i])
}
// For show like with escape
testSQL = `show tables like 'show\_test'`
result = tk.MustQuery(testSQL)
c.Check(result.Rows(), HasLen, 1)
var ss stats
variable.RegisterStatistics(ss)
testSQL = "show status like 'character_set_results';"
result = tk.MustQuery(testSQL)
c.Check(result.Rows(), NotNil)
tk.MustQuery("SHOW PROCEDURE STATUS WHERE Db='test'").Check(testkit.Rows())
tk.MustQuery("SHOW TRIGGERS WHERE Trigger ='test'").Check(testkit.Rows())
}
func (s *testSuite) TestUnion(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
testSQL := `select 1 union select 0;`
tk.MustExec(testSQL)
testSQL = `drop table if exists union_test; create table union_test(id int);`
tk.MustExec(testSQL)
testSQL = `drop table if exists union_test;`
tk.MustExec(testSQL)
testSQL = `create table union_test(id int);`
tk.MustExec(testSQL)
testSQL = `insert union_test values (1),(2); select id from union_test union select 1;`
tk.MustExec(testSQL)
testSQL = `select id from union_test union select id from union_test;`
tk.MustExec("begin")
r := tk.MustQuery(testSQL)
r.Check(testkit.Rows("1", "2"))
testSQL = `select * from (select id from union_test union select id from union_test) t;`
tk.MustExec("begin")
r = tk.MustQuery(testSQL)
r.Check(testkit.Rows("1", "2"))
r = tk.MustQuery("select 1 union all select 1")
r.Check(testkit.Rows("1", "1"))
r = tk.MustQuery("select 1 union all select 1 union select 1")
r.Check(testkit.Rows("1"))
r = tk.MustQuery("select 1 union (select 2) limit 1")
r.Check(testkit.Rows("1"))
r = tk.MustQuery("select 1 union (select 2) limit 1, 1")
r.Check(testkit.Rows("2"))
r = tk.MustQuery("select id from union_test union all (select 1) order by id desc")
r.Check(testkit.Rows("2", "1", "1"))
r = tk.MustQuery("select id as a from union_test union (select 1) order by a desc")
r.Check(testkit.Rows("2", "1"))
r = tk.MustQuery(`select null union select "abc"`)
rowStr1 := fmt.Sprintf("%v", nil)
r.Check(testkit.Rows(rowStr1, "abc"))
r = tk.MustQuery(`select "abc" union select 1`)
r.Check(testkit.Rows("abc", "1"))
tk.MustExec("commit")
}
func (s *testSuite) TestSetVar(c *C) {
tk := testkit.NewTestKit(c, s.store)
testSQL := "SET @a = 1;"
tk.MustExec(testSQL)
testSQL = `SET @a = "1";`
tk.MustExec(testSQL)
testSQL = "SET @a = null;"
tk.MustExec(testSQL)
testSQL = "SET @@global.autocommit = 1;"
tk.MustExec(testSQL)
testSQL = "SET @@global.autocommit = null;"
tk.MustExec(testSQL)
testSQL = "SET @@autocommit = 1;"
tk.MustExec(testSQL)
testSQL = "SET @@autocommit = null;"
tk.MustExec(testSQL)
errTestSql := "SET @@date_format = 1;"
_, err := tk.Exec(errTestSql)
c.Assert(err, NotNil)
errTestSql = "SET @@rewriter_enabled = 1;"
_, err = tk.Exec(errTestSql)
c.Assert(err, NotNil)
errTestSql = "SET xxx = abcd;"
_, err = tk.Exec(errTestSql)
c.Assert(err, NotNil)
errTestSql = "SET @@global.a = 1;"
_, err = tk.Exec(errTestSql)
c.Assert(err, NotNil)
errTestSql = "SET @@global.timestamp = 1;"
_, err = tk.Exec(errTestSql)
c.Assert(err, NotNil)
// For issue 998
testSQL = "SET @issue998a=1, @issue998b=5;"
tk.MustExec(testSQL)
tk.MustQuery(`select @issue998a, @issue998b;`).Check(testkit.Rows("1 5"))
testSQL = "SET @@autocommit=0, @issue998a=2;"
tk.MustExec(testSQL)
tk.MustQuery(`select @issue998a, @@autocommit;`).Check(testkit.Rows("2 0"))
testSQL = "SET @@global.autocommit=1, @issue998b=6;"
tk.MustExec(testSQL)
tk.MustQuery(`select @issue998b, @@global.autocommit;`).Check(testkit.Rows("6 1"))
}
func (s *testDBSuite) testChangeColumn(c *C) {
s.mustExec(c, "create table t3 (a int, b varchar(10))")
s.mustExec(c, "insert into t3 values(1, 'a'), (2, 'b')")
s.tk.MustQuery("select a from t3").Check(testkit.Rows("1", "2"))
s.mustExec(c, "alter table t3 change a aa bigint")
s.tk.MustQuery("select aa from t3").Check(testkit.Rows("1", "2"))
sql := "alter table t3 change a testx.t3.aa bigint"
s.testErrorCode(c, sql, tmysql.ErrWrongDBName)
sql = "alter table t3 change t.a aa bigint"
s.testErrorCode(c, sql, tmysql.ErrWrongTableName)
}
func (s *testSuite) TestTableReverseOrder(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("drop table if exists t")
tk.MustExec("create table t (a int primary key auto_increment, b int)")
tk.MustExec("insert t (b) values (1), (2), (3), (4), (5), (6), (7), (8), (9)")
result := tk.MustQuery("select b from t order by a desc")
result.Check(testkit.Rows("9", "8", "7", "6", "5", "4", "3", "2", "1"))
result = tk.MustQuery("select a from t where a <3 or (a >=6 and a < 8) order by a desc")
result.Check(testkit.Rows("7", "6", "2", "1"))
}
func (s *testSuite) TestSubquerySameTable(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("drop table if exists t")
tk.MustExec("create table t (a int)")
tk.MustExec("insert t values (1), (2)")
result := tk.MustQuery("select a from t where exists(select 1 from t as x where x.a < t.a)")
result.Check(testkit.Rows("2"))
result = tk.MustQuery("select a from t where not exists(select 1 from t as x where x.a < t.a)")
result.Check(testkit.Rows("1"))
}
func (s *testSuite) TestSelectVar(c *C) {
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("drop table if exists t")
tk.MustExec("create table t (d int)")
tk.MustExec("insert into t values(1), (2), (1)")
result := tk.MustQuery("select @a, @a := d+1 from t")
result.Check(testkit.Rows("<nil> 2", "<nil> 3", "<nil> 2"))
result = tk.MustQuery("select @a, @a := d+1 from t")
result.Check(testkit.Rows("2 2", "2 3", "3 2"))
}
func (s *testSuite) TestNewTableDual(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
result := tk.MustQuery("Select 1")
result.Check(testkit.Rows("1"))
result = tk.MustQuery("Select 1 from dual")
result.Check(testkit.Rows("1"))
result = tk.MustQuery("Select count(*) from dual")
result.Check(testkit.Rows("1"))
result = tk.MustQuery("Select 1 from dual where 1")
result.Check(testkit.Rows("1"))
}
func (s *testSuite) TestUnsignedPKColumn(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("drop table if exists t")
tk.MustExec("create table t (a int unsigned primary key, b int, c int, key idx_ba (b, c, a));")
tk.MustExec("insert t values (1, 1, 1)")
result := tk.MustQuery("select * from t;")
result.Check(testkit.Rows("1 1 1"))
tk.MustExec("update t set c=2 where a=1;")
result = tk.MustQuery("select * from t where b=1;")
result.Check(testkit.Rows("1 1 2"))
}
func (s *testSuite) TestAddNotNullColumnNoDefault(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("create table nn (c1 int)")
tk.MustExec("insert nn values (1), (2)")
tk.MustExec("alter table nn add column c2 int not null")
tk.MustQuery("select * from nn").Check(testkit.Rows("1 0", "2 0"))
_, err := tk.Exec("insert nn (c1) values (3)")
c.Check(err, NotNil)
tk.MustExec("set sql_mode=''")
tk.MustExec("insert nn (c1) values (3)")
tk.MustQuery("select * from nn").Check(testkit.Rows("1 0", "2 0", "3 0"))
}
func (s *testSuite) TestSelectWithoutFrom(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("begin")
r := tk.MustQuery("select 1 + 2*3")
r.Check(testkit.Rows("7"))
tk.MustExec("commit")
tk.MustExec("begin")
r = tk.MustQuery(`select _utf8"string";`)
r.Check(testkit.Rows("string"))
tk.MustExec("commit")
}
func (s *testSuite) TestSetPwd(c *C) {
tk := testkit.NewTestKit(c, s.store)
createUserSQL := `CREATE USER 'testpwd'@'localhost' IDENTIFIED BY '';`
tk.MustExec(createUserSQL)
result := tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr := fmt.Sprintf("%v", []byte(""))
result.Check(testkit.Rows(rowStr))
tk.MustExec(`SET PASSWORD FOR 'testpwd'@'localhost' = 'password';`)
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr = fmt.Sprintf("%v", []byte(util.EncodePassword("password")))
result.Check(testkit.Rows(rowStr))
}
func (s *testSuite) TestDefaultNull(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("drop table if exists t")
tk.MustExec("create table t (a int primary key auto_increment, b int default 1, c int)")
tk.MustExec("insert t values ()")
tk.MustQuery("select * from t").Check(testkit.Rows("1 1 <nil>"))
tk.MustExec("update t set b = NULL where a = 1")
tk.MustQuery("select * from t").Check(testkit.Rows("1 <nil> <nil>"))
tk.MustExec("update t set c = 1")
tk.MustQuery("select * from t ").Check(testkit.Rows("1 <nil> 1"))
tk.MustExec("delete from t where a = 1")
tk.MustExec("insert t (a) values (1)")
tk.MustQuery("select * from t").Check(testkit.Rows("1 1 <nil>"))
}
func (s *testSuite) TestDelete(c *C) {
defer func() {
s.cleanEnv(c)
testleak.AfterTest(c)()
}()
tk := testkit.NewTestKit(c, s.store)
s.fillData(tk, "delete_test")
tk.MustExec(`update delete_test set name = "abc" where id = 2;`)
tk.CheckExecResult(1, 0)
tk.MustExec(`delete from delete_test where id = 2 limit 1;`)
tk.CheckExecResult(1, 0)
// Test delete with false condition
tk.MustExec(`delete from delete_test where 0;`)
tk.CheckExecResult(0, 0)
tk.MustExec("insert into delete_test values (2, 'abc')")
tk.MustExec(`delete from delete_test where delete_test.id = 2 limit 1`)
tk.CheckExecResult(1, 0)
// Select data
tk.MustExec("begin")
rows := tk.MustQuery(`SELECT * from delete_test limit 2;`)
rowStr := fmt.Sprintf("%v %v", "1", []byte("hello"))
rows.Check(testkit.Rows(rowStr))
tk.MustExec("commit")
tk.MustExec(`delete from delete_test ;`)
tk.CheckExecResult(1, 0)
}
func (s *testSuite) TestTableScan(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use information_schema")
result := tk.MustQuery("select * from schemata")
// There must be these tables: information_schema, mysql, preformance_schema and test.
c.Assert(len(result.Rows()), GreaterEqual, 4)
tk.MustExec("use test")
tk.MustExec("create database mytest")
rowStr1 := fmt.Sprintf("%s %s %s %s %v", "def", "mysql", "utf8", "utf8_general_ci", nil)
rowStr2 := fmt.Sprintf("%s %s %s %s %v", "def", "mytest", "utf8", "utf8_general_ci", nil)
tk.MustExec("use information_schema")
result = tk.MustQuery("select * from schemata where schema_name = 'mysql'")
result.Check(testkit.Rows(rowStr1))
result = tk.MustQuery("select * from schemata where schema_name like 'my%'")
result.Check(testkit.Rows(rowStr1, rowStr2))
}
func (s *testSuite) TestInSubquery(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("drop table if exists t")
tk.MustExec("create table t (a int, b int)")
tk.MustExec("insert t values (1, 1), (2, 1)")
result := tk.MustQuery("select m1.a from t as m1 where m1.a in (select m2.b from t as m2)")
result.Check(testkit.Rows("1"))
result = tk.MustQuery("select m1.a from t as m1 where (3, m1.b) not in (select * from t as m2)")
result.Check(testkit.Rows("1", "2"))
result = tk.MustQuery("select m1.a from t as m1 where m1.a in (select m2.b+? from t as m2)", 1)
result.Check(testkit.Rows("2"))
tk.MustExec(`prepare stmt1 from 'select m1.a from t as m1 where m1.a in (select m2.b+? from t as m2)'`)
tk.MustExec("set @a = 1")
result = tk.MustQuery(`execute stmt1 using @a;`)
result.Check(testkit.Rows("2"))
tk.MustExec("set @a = 0")
result = tk.MustQuery(`execute stmt1 using @a;`)
result.Check(testkit.Rows("1"))
result = tk.MustQuery("select m1.a from t as m1 where m1.a in (1, 3, 5)")
result.Check(testkit.Rows("1"))
tk.MustExec("drop table if exists t1")
tk.MustExec("create table t1 (a float)")
tk.MustExec("insert t1 values (281.37)")
tk.MustQuery("select a from t1 where (a in (select a from t1))").Check(testkit.Rows("281.37"))
}
func (s *testSuite) TestIndexReverseOrder(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("drop table if exists t")
tk.MustExec("create table t (a int primary key auto_increment, b int, index idx (b))")
tk.MustExec("insert t (b) values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)")
result := tk.MustQuery("select b from t order by b desc")
result.Check(testkit.Rows("9", "8", "7", "6", "5", "4", "3", "2", "1", "0"))
result = tk.MustQuery("select b from t where b <3 or (b >=6 and b < 8) order by b desc")
result.Check(testkit.Rows("7", "6", "2", "1", "0"))
tk.MustExec("drop table if exists t")
tk.MustExec("create table t (a int, b int, index idx (b, a))")
tk.MustExec("insert t values (0, 2), (1, 2), (2, 2), (0, 1), (1, 1), (2, 1), (0, 0), (1, 0), (2, 0)")
result = tk.MustQuery("select b, a from t order by b, a desc")
result.Check(testkit.Rows("0 2", "0 1", "0 0", "1 2", "1 1", "1 0", "2 2", "2 1", "2 0"))
}
func (s *testSuite) TestCharsetDatabase(c *C) {
tk := testkit.NewTestKit(c, s.store)
testSQL := `create database if not exists cd_test_utf8 CHARACTER SET utf8 COLLATE utf8_bin;`
tk.MustExec(testSQL)
testSQL = `create database if not exists cd_test_latin1 CHARACTER SET latin1 COLLATE latin1_swedish_ci;`
tk.MustExec(testSQL)
testSQL = `use cd_test_utf8;`
tk.MustExec(testSQL)
tk.MustQuery(`select @@character_set_database;`).Check(testkit.Rows("utf8"))
tk.MustQuery(`select @@collation_database;`).Check(testkit.Rows("utf8_bin"))
testSQL = `use cd_test_latin1;`
tk.MustExec(testSQL)
tk.MustQuery(`select @@character_set_database;`).Check(testkit.Rows("latin1"))
tk.MustQuery(`select @@collation_database;`).Check(testkit.Rows("latin1_swedish_ci"))
}
func (s *testSuite) TestSelectLimit(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
s.fillData(tk, "select_limit")
tk.MustExec("insert INTO select_limit VALUES (3, \"hello\");")
tk.CheckExecResult(1, 0)
tk.MustExec("insert INTO select_limit VALUES (4, \"hello\");")
tk.CheckExecResult(1, 0)
tk.MustExec("begin")
r := tk.MustQuery("select * from select_limit limit 1;")
rowStr1 := fmt.Sprintf("%v %v", 1, []byte("hello"))
r.Check(testkit.Rows(rowStr1))
tk.MustExec("commit")
r = tk.MustQuery("select id from (select * from select_limit limit 1) k where id != 1;")
r.Check(testkit.Rows())
tk.MustExec("begin")
r = tk.MustQuery("select * from select_limit limit 18446744073709551615 offset 0;")
rowStr2 := fmt.Sprintf("%v %v", 2, []byte("hello"))
rowStr3 := fmt.Sprintf("%v %v", 3, []byte("hello"))
rowStr4 := fmt.Sprintf("%v %v", 4, []byte("hello"))
r.Check(testkit.Rows(rowStr1, rowStr2, rowStr3, rowStr4))
tk.MustExec("commit")
tk.MustExec("begin")
r = tk.MustQuery("select * from select_limit limit 18446744073709551615 offset 1;")
r.Check(testkit.Rows(rowStr2, rowStr3, rowStr4))
tk.MustExec("commit")
tk.MustExec("begin")
r = tk.MustQuery("select * from select_limit limit 18446744073709551615 offset 3;")
r.Check(testkit.Rows(rowStr4))
tk.MustExec("commit")
tk.MustExec("begin")
_, err := tk.Exec("select * from select_limit limit 18446744073709551616 offset 3;")
c.Assert(err, NotNil)
tk.MustExec("rollback")
}
func (s *testSuite) TestRow(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("drop table if exists t")
tk.MustExec("create table t (c int, d int)")
tk.MustExec("insert t values (1, 1)")
tk.MustExec("insert t values (1, 3)")
tk.MustExec("insert t values (2, 1)")
tk.MustExec("insert t values (2, 3)")
result := tk.MustQuery("select * from t where (c, d) < (2,2)")
result.Check(testkit.Rows("1 1", "1 3", "2 1"))
result = tk.MustQuery("select * from t where (1,2,3) > (3,2,1)")
result.Check(testkit.Rows())
result = tk.MustQuery("select * from t where row(1,2,3) > (3,2,1)")
result.Check(testkit.Rows())
result = tk.MustQuery("select * from t where (c, d) = (select * from t where (c,d) = (1,1))")
result.Check(testkit.Rows("1 1"))
result = tk.MustQuery("select * from t where (c, d) = (select * from t k where (t.c,t.d) = (c,d))")
result.Check(testkit.Rows("1 1", "1 3", "2 1", "2 3"))
}
func (s *testSuite) TestTruncateTable(c *C) {
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec(`drop table if exists truncate_test;`)
tk.MustExec(`create table truncate_test (a int)`)
tk.MustExec(`insert truncate_test values (1),(2),(3)`)
result := tk.MustQuery("select * from truncate_test")
result.Check(testkit.Rows("1", "2", "3"))
tk.MustExec("truncate table truncate_test")
result = tk.MustQuery("select * from truncate_test")
result.Check(nil)
}