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) TestAlterTable(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("create table if not exists alter_test (c1 int)")
tk.MustExec("alter table alter_test add column c2 int")
}
func (s *testSuite) TestShow(c *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, c2 int, c3 int default 1);`
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)
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])
}
}
func (ts *testInfoBinderSuite) TestInfoBinder(c *C) {
store, err := tidb.NewStore(tidb.EngineGoLevelDBMemory)
c.Assert(err, IsNil)
defer store.Close()
testKit := testkit.NewTestKit(c, store)
testKit.MustExec("use test")
testKit.MustExec("create table t (c1 int, c2 int)")
domain := sessionctx.GetDomain(testKit.Se.(context.Context))
src := "SELECT c1 from t"
l := parser.NewLexer(src)
c.Assert(parser.YYParse(l), Equals, 0)
stmts := l.Stmts()
c.Assert(len(stmts), Equals, 1)
v := &optimizer.InfoBinder{
Info: domain.InfoSchema(),
DefaultSchema: model.NewCIStr("test"),
}
selectStmt := stmts[0].(*ast.SelectStmt)
selectStmt.Accept(v)
verifier := &binderVerifier{
c: c,
}
selectStmt.Accept(verifier)
}
func (s *testSuite) TestLoadDataEscape(c *C) {
defer func() {
s.cleanEnv(c)
testleak.AfterTest(c)()
}()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test; drop table if exists load_data_test;")
tk.MustExec("CREATE TABLE load_data_test (id INT NOT NULL PRIMARY KEY, value TEXT NOT NULL) CHARACTER SET utf8")
tk.MustExec("load data local infile '/tmp/nonexistence.csv' into table load_data_test")
ctx := tk.Se.(context.Context)
ld := makeLoadDataInfo(2, ctx, c)
// test escape
cases := []testCase{
// data1 = nil, data2 != nil
{nil, []byte("1\ta string\n"), []string{fmt.Sprintf("%v %v", 1, []byte("a string"))}, nil},
{nil, []byte("2\tstr \\t\n"), []string{fmt.Sprintf("%v %v", 2, []byte("str \t"))}, nil},
{nil, []byte("3\tstr \\n\n"), []string{fmt.Sprintf("%v %v", 3, []byte("str \n"))}, nil},
{nil, []byte("4\tboth \\t\\n\n"), []string{fmt.Sprintf("%v %v", 4, []byte("both \t\n"))}, nil},
{nil, []byte("5\tstr \\\\\n"), []string{fmt.Sprintf("%v %v", 5, []byte("str \\"))}, nil},
{nil, []byte("6\t\\r\\t\\n\\0\\Z\\b\n"), []string{fmt.Sprintf("%v %v", 6, []byte{'\r', '\t', '\n', 0, 26, '\b'})}, nil},
}
deleteSQL := "delete from load_data_test"
selectSQL := "select * from load_data_test;"
checkCases(cases, ld, c, tk, ctx, selectSQL, deleteSQL)
}
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) 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 (ts *testNameResolverSuite) TestNameResolver(c *C) {
store, err := tidb.NewStore(tidb.EngineGoLevelDBMemory)
c.Assert(err, IsNil)
defer store.Close()
testKit := testkit.NewTestKit(c, store)
testKit.MustExec("use test")
testKit.MustExec("create table t1 (c1 int, c2 int)")
testKit.MustExec("create table t2 (c1 int, c2 int)")
testKit.MustExec("create table t3 (c1 int, c2 int)")
ctx := testKit.Se.(context.Context)
domain := sessionctx.GetDomain(ctx)
db.BindCurrentSchema(ctx, "test")
for _, tc := range resolverTestCases {
node, err := parser.ParseOneStmt(tc.src, "", "")
c.Assert(err, IsNil)
resolveErr := plan.ResolveName(node, domain.InfoSchema(), ctx)
if tc.valid {
c.Assert(resolveErr, IsNil)
verifier := &resolverVerifier{c: c, src: tc.src}
node.Accept(verifier)
} else {
c.Assert(resolveErr, NotNil, Commentf("%s", tc.src))
}
}
}
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) 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) TestCreateDropDatabase(c *C) {
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("create database if not exists drop_test;")
tk.MustExec("drop database if exists drop_test;")
tk.MustExec("create database drop_test;")
tk.MustExec("drop database drop_test;")
}
func (s *testBinlogSuite) SetUpSuite(c *C) {
logLevel := os.Getenv("log_level")
log.SetLevelByString(logLevel)
store, err := tikv.NewMockTikvStore()
c.Assert(err, IsNil)
s.store = store
tidb.SetSchemaLease(0)
s.unixFile = "/tmp/mock-binlog-pump"
os.Remove(s.unixFile)
l, err := net.Listen("unix", s.unixFile)
c.Assert(err, IsNil)
s.serv = grpc.NewServer()
s.pump = new(mockBinlogPump)
binlog.RegisterPumpServer(s.serv, s.pump)
go s.serv.Serve(l)
opt := grpc.WithDialer(func(addr string, timeout time.Duration) (net.Conn, error) {
return net.DialTimeout("unix", addr, timeout)
})
clientCon, err := grpc.Dial(s.unixFile, opt, grpc.WithInsecure())
c.Assert(err, IsNil)
c.Assert(clientCon, NotNil)
binloginfo.PumpClient = binlog.NewPumpClient(clientCon)
s.tk = testkit.NewTestKit(c, s.store)
s.tk.MustExec("use test")
domain := sessionctx.GetDomain(s.tk.Se.(context.Context))
s.ddl = domain.DDL()
}
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 *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) 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) TestSelectErrorRow(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("begin")
_, err := tk.Exec("select row(1, 1) from test")
c.Assert(err, NotNil)
_, err = tk.Exec("select * from test group by row(1, 1);")
c.Assert(err, NotNil)
_, err = tk.Exec("select * from test order by row(1, 1);")
c.Assert(err, NotNil)
_, err = tk.Exec("select * from test having row(1, 1);")
c.Assert(err, NotNil)
_, err = tk.Exec("select (select 1, 1) from test;")
c.Assert(err, NotNil)
_, err = tk.Exec("select * from test group by (select 1, 1);")
c.Assert(err, NotNil)
_, err = tk.Exec("select * from test order by (select 1, 1);")
c.Assert(err, NotNil)
_, err = tk.Exec("select * from test having (select 1, 1);")
c.Assert(err, NotNil)
tk.MustExec("commit")
}
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) TestCreateDropIndex(c *C) {
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("create table if not exists drop_test (a int)")
tk.MustExec("create index idx_a on drop_test (a)")
tk.MustExec("drop index idx_a on drop_test")
tk.MustExec("drop table drop_test")
}
func (s *testDBSuite) TestColumn(c *C) {
defer testleak.AfterTest(c)()
s.tk = testkit.NewTestKit(c, s.store)
s.tk.MustExec("use " + s.schemaName)
s.testAddColumn(c)
s.testDropColumn(c)
s.testChangeColumn(c)
}
func (s *testSuite) TestJoinPanic(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("drop table if exists events")
tk.MustExec("create table events (clock int, source int)")
tk.MustQuery("SELECT * FROM events e JOIN (SELECT MAX(clock) AS clock FROM events e2 GROUP BY e2.source) e3 ON e3.clock=e.clock")
}
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) TestNewSubquery(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 (2, 2)")
tk.MustExec("insert t values (3, 4)")
tk.MustExec("commit")
result := tk.MustQuery("select * from t where exists(select * from t k where t.c = k.c having sum(c) = 1)")
result.Check(testkit.Rows("1 1"))
result = tk.MustQuery("select * from t where exists(select k.c, k.d from t k, t p where t.c = k.d)")
result.Check(testkit.Rows("1 1", "2 2"))
result = tk.MustQuery("select 1 = (select count(*) from t where t.c = k.d) from t k")
result.Check(testkit.Rows("1", "1", "0"))
result = tk.MustQuery("select 1 = (select count(*) from t where exists( select * from t m where t.c = k.d)) from t k")
result.Check(testkit.Rows("1", "1", "0"))
result = tk.MustQuery("select t.c = any (select count(*) from t) from t")
result.Check(testkit.Rows("0", "0", "1"))
result = tk.MustQuery("select * from t where (t.c, 6) = any (select count(*), sum(t.c) from t)")
result.Check(testkit.Rows("3 4"))
result = tk.MustQuery("select t.c from t where (t.c) < all (select count(*) from t)")
result.Check(testkit.Rows("1", "2"))
result = tk.MustQuery("select t.c from t where (t.c, t.d) = any (select * from t)")
result.Check(testkit.Rows("1", "2", "3"))
result = tk.MustQuery("select t.c from t where (t.c, t.d) != all (select * from t)")
result.Check(testkit.Rows())
result = tk.MustQuery("select (select count(*) from t where t.c = k.d) from t k")
result.Check(testkit.Rows("1", "1", "0"))
result = tk.MustQuery("select t.c from t where (t.c, t.d) in (select * from t)")
result.Check(testkit.Rows("1", "2", "3"))
result = tk.MustQuery("select t.c from t where (t.c, t.d) not in (select * from t)")
result.Check(testkit.Rows())
// = all empty set is true
result = tk.MustQuery("select t.c from t where (t.c, t.d) != all (select * from t where d > 1000)")
result.Check(testkit.Rows("1", "2", "3"))
result = tk.MustQuery("select t.c from t where (t.c) < any (select c from t where d > 1000)")
result.Check(testkit.Rows())
tk.MustExec("insert t values (NULL, NULL)")
result = tk.MustQuery("select (t.c) < any (select c from t) from t")
result.Check(testkit.Rows("1", "1", "<nil>", "<nil>"))
result = tk.MustQuery("select (10) > all (select c from t) from t")
result.Check(testkit.Rows("<nil>", "<nil>", "<nil>", "<nil>"))
result = tk.MustQuery("select (c) > all (select c from t) from t")
result.Check(testkit.Rows("0", "0", "0", "<nil>"))
tk.MustExec("drop table if exists a")
tk.MustExec("create table a (c int, d int)")
tk.MustExec("insert a values (1, 2)")
tk.MustExec("drop table if exists b")
tk.MustExec("create table b (c int, d int)")
tk.MustExec("insert b values (2, 1)")
result = tk.MustQuery("select * from a b where c = (select d from b a where a.c = 2 and b.c = 1)")
result.Check(testkit.Rows("1 2"))
}
func (s *testSuite) TestPrepared(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("drop table if exists prepare_test")
tk.MustExec("create table prepare_test (id int PRIMARY KEY AUTO_INCREMENT, c1 int, c2 int, c3 int default 1)")
tk.MustExec("insert prepare_test (c1) values (1),(2),(NULL)")
tk.MustExec(`prepare stmt_test_1 from 'select id from prepare_test where id > ?'; set @a = 1; execute stmt_test_1 using @a;`)
tk.MustExec(`prepare stmt_test_2 from 'select 1'`)
// Prepare multiple statement is not allowed.
_, err := tk.Exec(`prepare stmt_test_3 from 'select id from prepare_test where id > ?;select id from prepare_test where id > ?;'`)
c.Assert(executor.ErrPrepareMulti.Equal(err), IsTrue)
// The variable count does not match.
_, err = tk.Exec(`prepare stmt_test_4 from 'select id from prepare_test where id > ? and id < ?'; set @a = 1; execute stmt_test_4 using @a;`)
c.Assert(executor.ErrWrongParamCount.Equal(err), IsTrue)
// Prepare and deallocate prepared statement immediately.
tk.MustExec(`prepare stmt_test_5 from 'select id from prepare_test where id > ?'; deallocate prepare stmt_test_5;`)
// Statement not found.
_, err = tk.Exec("deallocate prepare stmt_test_5")
c.Assert(executor.ErrStmtNotFound.Equal(err), IsTrue)
// The `stmt_test5` should not be found.
_, err = tk.Exec(`set @a = 1; execute stmt_test_5 using @a;`)
c.Assert(executor.ErrStmtNotFound.Equal(err), IsTrue)
// Use parameter marker with argument will run prepared statement.
result := tk.MustQuery("select distinct c1, c2 from prepare_test where c1 = ?", 1)
result.Check([][]interface{}{{1, nil}})
// Call Session PrepareStmt directly to get stmtId.
stmtId, _, _, err := tk.Se.PrepareStmt("select c1, c2 from prepare_test where c1 = ?")
c.Assert(err, IsNil)
_, err = tk.Se.ExecutePreparedStmt(stmtId, 1)
c.Assert(err, IsNil)
// Make schema change.
tk.Exec("create table prepare2 (a int)")
// Should success as the changed schema do not affect the prepared statement.
_, err = tk.Se.ExecutePreparedStmt(stmtId, 1)
c.Assert(err, IsNil)
// Drop a column so the prepared statement become invalid.
tk.MustExec("alter table prepare_test drop column c2")
// There should be schema changed error.
_, err = tk.Se.ExecutePreparedStmt(stmtId, 1)
c.Assert(executor.ErrSchemaChanged.Equal(err), IsTrue)
// Coverage.
exec := &executor.ExecuteExec{}
exec.Fields()
exec.Next()
exec.Close()
}
func (ts *testTypeInferrerSuite) TestInterType(c *C) {
store, err := tidb.NewStore(tidb.EngineGoLevelDBMemory)
c.Assert(err, IsNil)
defer store.Close()
testKit := testkit.NewTestKit(c, store)
testKit.MustExec("use test")
testKit.MustExec("create table t (c1 int, c2 double, c3 text)")
cases := []struct {
expr string
tp byte
}{
{"c1", mysql.TypeLong},
{"+1", mysql.TypeLonglong},
{"-1", mysql.TypeLonglong},
{"-'1'", mysql.TypeDouble},
{"~1", mysql.TypeLonglong},
{"!true", mysql.TypeLonglong},
{"c1 is true", mysql.TypeLonglong},
{"c2 is null", mysql.TypeLonglong},
{"cast(1 as decimal)", mysql.TypeNewDecimal},
{"1 and 1", mysql.TypeLonglong},
{"1 or 1", mysql.TypeLonglong},
{"1 xor 1", mysql.TypeLonglong},
{"'1' & 2", mysql.TypeLonglong},
{"'1' | 2", mysql.TypeLonglong},
{"'1' ^ 2", mysql.TypeLonglong},
{"'1' << 1", mysql.TypeLonglong},
{"'1' >> 1", mysql.TypeLonglong},
{"1 + '1'", mysql.TypeDouble},
{"1 + 1.1", mysql.TypeNewDecimal},
{"1 div 2", mysql.TypeLonglong},
{"1 > any (select 1)", mysql.TypeLonglong},
{"exists (select 1)", mysql.TypeLonglong},
{"1 in (2, 3)", mysql.TypeLonglong},
{"'abc' like 'abc'", mysql.TypeLonglong},
{"'abc' rlike 'abc'", mysql.TypeLonglong},
{"(1+1)", mysql.TypeLonglong},
}
for _, ca := range cases {
ctx := testKit.Se.(context.Context)
stmts, err := tidb.Parse(ctx, "select "+ca.expr+" from t")
c.Assert(err, IsNil)
c.Assert(stmts, HasLen, 1)
stmt := stmts[0].(*ast.SelectStmt)
is := sessionctx.GetDomain(ctx).InfoSchema()
err = optimizer.ResolveName(stmt, is, ctx)
c.Assert(err, IsNil)
optimizer.InferType(stmt)
tp := stmt.GetResultFields()[0].Column.Tp
c.Assert(tp, Equals, ca.tp, Commentf("for %s", ca.expr))
}
}
func (s *testSuite) TestCreateDropTable(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("create table if not exists drop_test (a int)")
tk.MustExec("drop table if exists drop_test")
tk.MustExec("create table drop_test (a int)")
tk.MustExec("drop table drop_test")
}
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) TestAdmin(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
tk.MustExec("use test")
tk.MustExec("drop table if exists admin_test")
tk.MustExec("create table admin_test (c1 int, c2 int, c3 int default 1, index (c1))")
tk.MustExec("insert admin_test (c1) values (1),(2),(NULL)")
r, err := tk.Exec("admin show ddl")
c.Assert(err, IsNil)
row, err := r.Next()
c.Assert(err, IsNil)
c.Assert(row.Data, HasLen, 6)
txn, err := s.store.Begin()
c.Assert(err, IsNil)
ddlInfo, err := inspectkv.GetDDLInfo(txn)
c.Assert(err, IsNil)
c.Assert(row.Data[0].GetInt64(), Equals, ddlInfo.SchemaVer)
rowOwnerInfos := strings.Split(row.Data[1].GetString(), ",")
ownerInfos := strings.Split(ddlInfo.Owner.String(), ",")
c.Assert(rowOwnerInfos[0], Equals, ownerInfos[0])
c.Assert(row.Data[2].GetString(), Equals, "")
bgInfo, err := inspectkv.GetBgDDLInfo(txn)
c.Assert(err, IsNil)
c.Assert(row.Data[3].GetInt64(), Equals, bgInfo.SchemaVer)
rowOwnerInfos = strings.Split(row.Data[4].GetString(), ",")
ownerInfos = strings.Split(bgInfo.Owner.String(), ",")
c.Assert(rowOwnerInfos[0], Equals, ownerInfos[0])
c.Assert(row.Data[5].GetString(), Equals, "")
row, err = r.Next()
c.Assert(err, IsNil)
c.Assert(row, IsNil)
// check table test
tk.MustExec("create table admin_test1 (c1 int, c2 int default 1, index (c1))")
tk.MustExec("insert admin_test1 (c1) values (21),(22)")
r, err = tk.Exec("admin check table admin_test, admin_test1")
c.Assert(err, IsNil)
c.Assert(r, IsNil)
// error table name
r, err = tk.Exec("admin check table admin_test_error")
c.Assert(err, NotNil)
// different index values
ctx := tk.Se.(context.Context)
domain := sessionctx.GetDomain(ctx)
is := domain.InfoSchema()
c.Assert(is, NotNil)
tb, err := is.TableByName(model.NewCIStr("test"), model.NewCIStr("admin_test"))
c.Assert(err, IsNil)
c.Assert(tb.Indices(), HasLen, 1)
_, err = tb.Indices()[0].Create(txn, types.MakeDatums(int64(10)), 1)
c.Assert(err, IsNil)
err = txn.Commit()
c.Assert(err, IsNil)
r, err = tk.Exec("admin check table admin_test")
c.Assert(err, NotNil)
}
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 *testDBSuite) TestIndex(c *C) {
defer testleak.AfterTest(c)()
s.tk = testkit.NewTestKit(c, s.store)
s.tk.MustExec("use " + s.schemaName)
s.testAddIndex(c)
s.testAddAnonymousIndex(c)
s.testDropIndex(c)
s.testAddUniqueIndexRollback(c)
s.testAddIndexWithDupCols(c)
}
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"))
}