func (s *testStmtSuite) TestShow(c *C) {
testSQL := `drop table if exists show_test; create table show_test (id int PRIMARY KEY AUTO_INCREMENT, c1 int, c2 int, c3 int default 1);`
mustExec(c, s.testDB, testSQL)
testSQL = "show columns from show_test;"
stmtList, err := tidb.Compile(testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok := stmtList[0].(*stmts.ShowStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsFalse)
c.Assert(len(testStmt.OriginText()), Greater, 0)
testSQL = "show create table show_test;"
stmtList, err = tidb.Compile(testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok = stmtList[0].(*stmts.ShowStmt)
c.Assert(ok, IsTrue)
mf := newMockFormatter()
testStmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
testSQL = "SHOW VARIABLES LIKE 'character_set_results';"
stmtList, err = tidb.Compile(testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok = stmtList[0].(*stmts.ShowStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.Pattern, NotNil)
}
func (s *testTableRsetSuite) TestTableSourceString(c *C) {
tableIdent := table.Ident{Schema: model.NewCIStr(s.dbName), Name: model.NewCIStr(s.tableName)}
ts := &rsets.TableSource{Source: tableIdent, Name: s.tableName}
str := ts.String()
c.Assert(len(str), Greater, 0)
stmtList, err := tidb.Compile(s.querySql)
c.Assert(err, IsNil)
c.Assert(len(stmtList), Greater, 0)
ts = &rsets.TableSource{Source: stmtList[0], Name: s.tableName}
str = ts.String()
c.Assert(len(str), Greater, 0)
ts = &rsets.TableSource{Source: stmtList[0]}
str = ts.String()
c.Assert(len(str), Greater, 0)
// check panic
defer func() {
e := recover()
c.Assert(e, NotNil)
}()
ts = &rsets.TableSource{}
str = ts.String()
}
func (s *testStmtSuite) TestSelectHaving(c *C) {
s.fillData(s.testDB, c)
// Test compile
stmtList, err := tidb.Compile("select * from test where id = 2;")
c.Assert(err, IsNil)
str := stmtList[0].OriginText()
c.Assert(0, Less, len(str))
tx := mustBegin(c, s.testDB)
rows, err := tx.Query("select id, name from test where id in (1,3) having name like 'he%';")
c.Assert(err, IsNil)
for rows.Next() {
var id int
var name string
rows.Scan(&id, &name)
c.Assert(id, Equals, 1)
c.Assert(name, Equals, "hello")
}
rows.Close()
mustCommit(c, tx)
}
func (s *testStmtSuite) TestCreateTable(c *C) {
stmtList, err := tidb.Compile(s.createDBSql + " CREATE TABLE if not exists test(id INT NOT NULL DEFAULT 1, name varchar(255), PRIMARY KEY(id));")
c.Assert(err, IsNil)
for _, stmt := range stmtList {
c.Assert(len(stmt.OriginText()), Greater, 0)
mf := newMockFormatter()
stmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
}
// Test create an exist database
tx := mustBegin(c, s.testDB)
_, err = tx.Exec(fmt.Sprintf("CREATE database %s;", s.dbName))
c.Assert(err, NotNil)
tx.Rollback()
// Test create an exist table
mustExec(c, s.testDB, "CREATE TABLE test(id INT NOT NULL DEFAULT 1, name varchar(255), PRIMARY KEY(id));")
tx = mustBegin(c, s.testDB)
_, err = tx.Exec("CREATE TABLE test(id INT NOT NULL DEFAULT 1, name varchar(255), PRIMARY KEY(id));")
c.Assert(err, NotNil)
tx.Rollback()
// Test "if not exist"
mustExec(c, s.testDB, "CREATE TABLE if not exists test(id INT NOT NULL DEFAULT 1, name varchar(255), PRIMARY KEY(id));")
}
func (s *testStmtSuite) TestUse(c *C) {
testSQL := `create database if not exists use_test;`
mustExec(c, s.testDB, testSQL)
testSQL = `use test;`
stmtList, err := tidb.Compile(s.ctx, testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok := stmtList[0].(*stmts.UseStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsFalse)
c.Assert(len(testStmt.OriginText()), Greater, 0)
mf := newMockFormatter()
testStmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
errTestSQL := `use xxx;`
tx := mustBegin(c, s.testDB)
_, err = tx.Exec(errTestSQL)
c.Assert(err, NotNil)
tx.Rollback()
}
func (s *testTableRsetSuite) TestTableSourceString(c *C) {
tableIdent := table.Ident{Schema: model.NewCIStr(s.dbName), Name: model.NewCIStr(s.tableName)}
ts := &rsets.TableSource{Source: tableIdent, Name: s.tableName}
str := ts.String()
c.Assert(len(str), Greater, 0)
store := newStore(c)
se := newSession(c, store, s.dbName)
ctx, ok := se.(context.Context)
c.Assert(ok, IsTrue)
stmtList, err := tidb.Compile(ctx, s.querySql)
c.Assert(err, IsNil)
c.Assert(len(stmtList), Greater, 0)
ts = &rsets.TableSource{Source: stmtList[0], Name: s.tableName}
str = ts.String()
c.Assert(len(str), Greater, 0)
ts = &rsets.TableSource{Source: stmtList[0]}
str = ts.String()
c.Assert(len(str), Greater, 0)
// check panic
defer func() {
e := recover()
c.Assert(e, NotNil)
}()
ts = &rsets.TableSource{}
str = ts.String()
}
func (s *testStmtSuite) TestSetCharsetStmt(c *C) {
testSQL := `SET NAMES utf8;`
stmtList, err := tidb.Compile(testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok := stmtList[0].(*stmts.SetCharsetStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsFalse)
c.Assert(len(testStmt.OriginText()), Greater, 0)
ctx := mock.NewContext()
variable.BindSessionVars(ctx)
sessionVars := variable.GetSessionVars(ctx)
for _, v := range variable.SetNamesVariables {
c.Assert(sessionVars.Systems[v] != "utf8", IsTrue)
}
_, err = testStmt.Exec(ctx)
c.Assert(err, IsNil)
for _, v := range variable.SetNamesVariables {
c.Assert(sessionVars.Systems[v], Equals, "utf8")
}
c.Assert(sessionVars.Systems[variable.CollationConnection], Equals, "utf8_general_ci")
mf := newMockFormatter()
testStmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
}
func (s *testJoinRsetSuite) TestJoinRsetPlan(c *C) {
p, err := s.r.Plan(s.ctx)
c.Assert(err, IsNil)
_, ok := p.(*plans.JoinPlan)
c.Assert(ok, IsTrue)
// check join right is statement.
querySql := fmt.Sprintf("select 1")
stmtList, err := tidb.Compile(querySql)
c.Assert(err, IsNil)
c.Assert(len(stmtList), Greater, 0)
ts := &rsets.TableSource{Source: stmtList[0]}
s.r.Right = ts
_, err = s.r.Plan(s.ctx)
c.Assert(err, IsNil)
// check join right is join rset.
s.r.Right = &rsets.JoinRset{Left: ts}
_, err = s.r.Plan(s.ctx)
c.Assert(err, IsNil)
// check error.
s.r.Right = "xxx"
_, err = s.r.Plan(s.ctx)
c.Assert(err, NotNil)
s.r.Right = nil
}
func (s *testStmtSuite) TestExplain(c *C) {
testSQL := "explain select 1"
stmtList, err := tidb.Compile(s.ctx, testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok := stmtList[0].(*stmts.ExplainStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsFalse)
c.Assert(len(testStmt.OriginText()), Greater, 0)
newTestSql := "explain " + testSQL
newTestStmt := &stmts.ExplainStmt{S: testStmt, Text: newTestSql}
mf := newMockFormatter()
ctx := mock.NewContext()
variable.BindSessionVars(ctx)
newTestStmt.Explain(ctx, mf)
c.Assert(mf.Len(), Greater, 0)
_, err = testStmt.Exec(ctx)
c.Assert(err, IsNil)
showColumnSQL := "desc t;"
stmtList, err = tidb.Compile(s.ctx, showColumnSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok = stmtList[0].(*stmts.ExplainStmt)
c.Assert(ok, IsTrue)
showStmt, ok := testStmt.S.(*stmts.ShowStmt)
c.Assert(ok, IsTrue)
// Mock DBName for ShowStmt
showStmt.DBName = "test"
mf = newMockFormatter()
testStmt.Explain(ctx, mf)
c.Assert(mf.Len(), Greater, 0)
_, err = testStmt.Exec(ctx)
c.Assert(err, IsNil)
}
func (s *testStmtSuite) TestDo(c *C) {
testSQL := "do 1, 2"
stmtList, err := tidb.Compile(s.ctx, testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt := stmtList[0].(*stmts.DoStmt)
c.Assert(testStmt.IsDDL(), IsFalse)
c.Assert(len(testStmt.OriginText()), Greater, 0)
mf := newMockFormatter()
testStmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
mustExec(c, s.testDB, testSQL)
}
func (s *testStmtSuite) TestRollback(c *C) {
// Test RollbackStmt.
testSQL := `rollback;`
stmtList, err := tidb.Compile(s.ctx, testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok := stmtList[0].(*stmts.RollbackStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsFalse)
c.Assert(len(testStmt.OriginText()), Greater, 0)
mf := newMockFormatter()
testStmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
}
func (s *testStmtSuite) TestDropDatabase(c *C) {
testSQL := "drop database if exists drop_test;"
stmtList, err := tidb.Compile(testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok := stmtList[0].(*stmts.DropDatabaseStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsTrue)
c.Assert(len(testStmt.OriginText()), Greater, 0)
mf := newMockFormatter()
testStmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
mustExec(c, s.testDB, testSQL)
}
func (s *testStmtSuite) TestCreateIndex(c *C) {
mustExec(c, s.testDB, s.createTableSql)
stmtList, err := tidb.Compile("CREATE index name_idx on test (name)")
c.Assert(err, IsNil)
str := stmtList[0].OriginText()
c.Assert(0, Less, len(str))
mf := newMockFormatter()
stmtList[0].Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
tx := mustBegin(c, s.testDB)
_, err = tx.Exec("CREATE TABLE test(id INT NOT NULL DEFAULT 1, name varchar(255), PRIMARY KEY(id));")
c.Assert(err, NotNil)
tx.Rollback()
// Test not exist
mustExec(c, s.testDB, "CREATE index name_idx on test (name)")
}
func (s *testStmtSuite) TestSetCharsetStmt(c *C) {
testSQL := `SET NAMES utf8;`
stmtList, err := tidb.Compile(testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok := stmtList[0].(*stmts.SetCharsetStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsFalse)
c.Assert(len(testStmt.OriginText()), Greater, 0)
_, err = testStmt.Exec(nil)
c.Assert(err, IsNil)
mf := newMockFormatter()
testStmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
}
func (s *testStmtSuite) TestAlterTable(c *C) {
testSQL := "drop table if exists t; create table t (c1 int); alter table t add column c2 int;"
stmtList, err := tidb.Compile(s.ctx, testSQL)
c.Assert(err, IsNil)
stmtLen := len(stmtList)
c.Assert(stmtLen, Greater, 0)
testStmt, ok := stmtList[stmtLen-1].(*stmts.AlterTableStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsTrue)
c.Assert(len(testStmt.OriginText()), Greater, 0)
mf := newMockFormatter()
testStmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
mustExec(c, s.testDB, testSQL)
}
func (s *testStmtSuite) TestDropTable(c *C) {
testSQL := "drop table if exists drop_table;"
stmtList, err := tidb.Compile(s.ctx, testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok := stmtList[0].(*stmts.DropTableStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsTrue)
c.Assert(len(testStmt.OriginText()), Greater, 0)
mf := newMockFormatter()
testStmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
mustExec(c, s.testDB, testSQL)
mustExec(c, s.testDB, "create table if not exists t (c int)")
mustExec(c, s.testDB, "drop table t")
}
func (s *testStmtSuite) TestTruncate(c *C) {
testSQL := `drop table if exists truncate_test; create table truncate_test(id int);`
mustExec(c, s.testDB, testSQL)
testSQL = "truncate table truncate_test;"
stmtList, err := tidb.Compile(s.ctx, testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok := stmtList[0].(*stmts.TruncateTableStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsFalse)
c.Assert(len(testStmt.OriginText()), Greater, 0)
mf := newMockFormatter()
testStmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
mustExec(c, s.testDB, testSQL)
}
func (s *testStmtSuite) TestUnion(c *C) {
testSQL := `select 1 union select 0;`
mustExec(c, s.testDB, testSQL)
stmtList, err := tidb.Compile(testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok := stmtList[0].(*stmts.UnionStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsFalse)
c.Assert(len(testStmt.OriginText()), Greater, 0)
mf := newMockFormatter()
testStmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
testSQL = `drop table if exists union_test; create table union_test(id int);
insert union_test values (1),(2); select id from union_test union select 1;`
mustExec(c, s.testDB, testSQL)
testSQL = `select id from union_test union select id from union_test;`
tx := mustBegin(c, s.testDB)
rows, err := tx.Query(testSQL)
c.Assert(err, IsNil)
i := 1
for rows.Next() {
var id int
rows.Scan(&id)
c.Assert(id, Equals, i)
i++
}
rows.Close()
mustCommit(c, tx)
}
func (s *testStmtSuite) TestMultiTableDelete(c *C) {
s.fillDataMultiTable(s.testDB, c)
// Test compile
stmtList, err := tidb.Compile("DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;")
c.Assert(err, IsNil)
str := stmtList[0].OriginText()
c.Assert(0, Less, len(str))
r := mustExec(c, s.testDB, `DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;`)
checkResult(c, r, 2, 0)
// Select data
tx := mustBegin(c, s.testDB)
rows, err := tx.Query("select * from t3")
c.Assert(err, IsNil)
cnt := 0
for rows.Next() {
cnt++
}
c.Assert(cnt, Equals, 3)
rows.Close()
}
func (s *testStmtSuite) TestInsert(c *C) {
testSQL := `drop table if exists insert_test;
create table insert_test (id int PRIMARY KEY AUTO_INCREMENT, c1 int, c2 int, c3 int default 1);
insert insert_test (c1) values (1),(2),(NULL);`
mustExec(c, s.testDB, testSQL)
errInsertSelectSQL := `insert insert_test (c1) values ();`
tx := mustBegin(c, s.testDB)
_, err := tx.Exec(errInsertSelectSQL)
c.Assert(err, NotNil)
tx.Rollback()
errInsertSelectSQL = `insert insert_test (c1, c2) values (1,2),(1);`
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errInsertSelectSQL)
c.Assert(err, NotNil)
tx.Rollback()
errInsertSelectSQL = `insert insert_test (xxx) values (3);`
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errInsertSelectSQL)
c.Assert(err, NotNil)
tx.Rollback()
errInsertSelectSQL = `insert insert_test_xxx (c1) values ();`
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errInsertSelectSQL)
c.Assert(err, NotNil)
tx.Rollback()
insertSetSQL := `insert insert_test set c1 = 3;`
mustExec(c, s.testDB, insertSetSQL)
stmtList, err := tidb.Compile(insertSetSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok := stmtList[0].(*stmts.InsertIntoStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsFalse)
c.Assert(len(testStmt.OriginText()), Greater, 0)
errInsertSelectSQL = `insert insert_test set c1 = 4, c1 = 5;`
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errInsertSelectSQL)
c.Assert(err, NotNil)
tx.Rollback()
errInsertSelectSQL = `insert insert_test set xxx = 6;`
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errInsertSelectSQL)
c.Assert(err, NotNil)
tx.Rollback()
insertSelectSQL := `create table insert_test_1 (id int, c1 int); insert insert_test_1 select id, c1 from insert_test;`
mustExec(c, s.testDB, insertSelectSQL)
insertSelectSQL = `create table insert_test_2 (id int, c1 int);
insert insert_test_1 select id, c1 from insert_test union select id * 10, c1 * 10 from insert_test;`
mustExec(c, s.testDB, insertSelectSQL)
errInsertSelectSQL = `insert insert_test_1 select c1 from insert_test;`
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errInsertSelectSQL)
c.Assert(err, NotNil)
tx.Rollback()
insertSQL := `insert into insert_test (id, c2) values (1, 1) on duplicate key update c2=10;`
mustExec(c, s.testDB, insertSQL)
insertSQL = `insert into insert_test (id, c2) values (1, 1) on duplicate key update insert_test.c2=10;`
mustExec(c, s.testDB, insertSQL)
_, err = s.testDB.Exec(`insert into insert_test (id, c2) values(1, 1) on duplicate key update t.c2 = 10`)
c.Assert(err, NotNil)
}
func (s *testStmtSuite) TestReplace(c *C) {
testSQL := `drop table if exists replace_test;
create table replace_test (id int PRIMARY KEY AUTO_INCREMENT, c1 int, c2 int, c3 int default 1);
replace replace_test (c1) values (1),(2),(NULL);`
mustExec(c, s.testDB, testSQL)
errReplaceSQL := `replace replace_test (c1) values ();`
tx := mustBegin(c, s.testDB)
_, err := tx.Exec(errReplaceSQL)
c.Assert(err, NotNil)
tx.Rollback()
errReplaceSQL = `replace replace_test (c1, c2) values (1,2),(1);`
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errReplaceSQL)
c.Assert(err, NotNil)
tx.Rollback()
errReplaceSQL = `replace replace_test (xxx) values (3);`
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errReplaceSQL)
c.Assert(err, NotNil)
tx.Rollback()
errReplaceSQL = `replace replace_test_xxx (c1) values ();`
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errReplaceSQL)
c.Assert(err, NotNil)
tx.Rollback()
replaceSetSQL := `replace replace_test set c1 = 3;`
mustExec(c, s.testDB, replaceSetSQL)
stmtList, err := tidb.Compile(s.ctx, replaceSetSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok := stmtList[0].(*stmts.ReplaceIntoStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsFalse)
c.Assert(len(testStmt.OriginText()), Greater, 0)
errReplaceSetSQL := `replace replace_test set c1 = 4, c1 = 5;`
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errReplaceSetSQL)
c.Assert(err, NotNil)
tx.Rollback()
errReplaceSetSQL = `replace replace_test set xxx = 6;`
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errReplaceSetSQL)
c.Assert(err, NotNil)
tx.Rollback()
replaceSelectSQL := `create table replace_test_1 (id int, c1 int);
replace replace_test_1 select id, c1 from replace_test;`
mustExec(c, s.testDB, replaceSelectSQL)
replaceSelectSQL = `create table replace_test_2 (id int, c1 int);
replace replace_test_1 select id, c1 from replace_test union select id * 10, c1 * 10 from replace_test;`
mustExec(c, s.testDB, replaceSelectSQL)
errReplaceSelectSQL := `replace replace_test_1 select c1 from replace_test;`
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errReplaceSelectSQL)
c.Assert(err, NotNil)
tx.Rollback()
replaceUniqueIndexSQL := `create table replace_test_3 (c1 int, c2 int, UNIQUE INDEX (c2));
replace into replace_test_3 set c2=1;`
mustExec(c, s.testDB, replaceUniqueIndexSQL)
replaceUniqueIndexSQL = `replace into replace_test_3 set c2=1;`
ret := mustExec(c, s.testDB, replaceUniqueIndexSQL)
rows, err := ret.RowsAffected()
c.Assert(err, IsNil)
c.Assert(rows, Equals, int64(1))
replaceUniqueIndexSQL = `replace into replace_test_3 set c1=1, c2=1;`
ret = mustExec(c, s.testDB, replaceUniqueIndexSQL)
rows, err = ret.RowsAffected()
c.Assert(err, IsNil)
c.Assert(rows, Equals, int64(2))
replaceUniqueIndexSQL = `replace into replace_test_3 set c2=NULL;`
mustExec(c, s.testDB, replaceUniqueIndexSQL)
replaceUniqueIndexSQL = `replace into replace_test_3 set c2=NULL;`
ret = mustExec(c, s.testDB, replaceUniqueIndexSQL)
rows, err = ret.RowsAffected()
c.Assert(err, IsNil)
c.Assert(rows, Equals, int64(1))
replaceUniqueIndexSQL = `create table replace_test_4 (c1 int, c2 int, c3 int, UNIQUE INDEX (c1, c2));
replace into replace_test_4 set c2=NULL;`
mustExec(c, s.testDB, replaceUniqueIndexSQL)
replaceUniqueIndexSQL = `replace into replace_test_4 set c2=NULL;`
ret = mustExec(c, s.testDB, replaceUniqueIndexSQL)
rows, err = ret.RowsAffected()
c.Assert(err, IsNil)
c.Assert(rows, Equals, int64(1))
replacePrimaryKeySQL := `create table replace_test_5 (c1 int, c2 int, c3 int, PRIMARY KEY (c1, c2));
replace into replace_test_5 set c1=1, c2=2;`
mustExec(c, s.testDB, replacePrimaryKeySQL)
replacePrimaryKeySQL = `replace into replace_test_5 set c1=1, c2=2;`
ret = mustExec(c, s.testDB, replacePrimaryKeySQL)
rows, err = ret.RowsAffected()
c.Assert(err, IsNil)
c.Assert(rows, Equals, int64(1))
}
func (s *testStmtSuite) TestCreateTable(c *C) {
stmtList, err := tidb.Compile(s.ctx, s.createDBSql+" CREATE TABLE if not exists test(id INT NOT NULL DEFAULT 1, name varchar(255), PRIMARY KEY(id));")
c.Assert(err, IsNil)
for _, stmt := range stmtList {
c.Assert(len(stmt.OriginText()), Greater, 0)
mf := newMockFormatter()
stmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
}
// Test create an exist database
tx := mustBegin(c, s.testDB)
_, err = tx.Exec(fmt.Sprintf("CREATE database %s;", s.dbName))
c.Assert(err, NotNil)
tx.Rollback()
// Test create an exist table
mustExec(c, s.testDB, "CREATE TABLE test(id INT NOT NULL DEFAULT 1, name varchar(255), PRIMARY KEY(id));")
tx = mustBegin(c, s.testDB)
_, err = tx.Exec("CREATE TABLE test(id INT NOT NULL DEFAULT 1, name varchar(255), PRIMARY KEY(id));")
c.Assert(err, NotNil)
tx.Rollback()
// Test "if not exist"
mustExec(c, s.testDB, "CREATE TABLE if not exists test(id INT NOT NULL DEFAULT 1, name varchar(255), PRIMARY KEY(id));")
// Testcase for https://github.com/pingcap/tidb/issues/312
mustExec(c, s.testDB, `create table issue312_1 (c float(24));`)
mustExec(c, s.testDB, `create table issue312_2 (c float(25));`)
tx = mustBegin(c, s.testDB)
rows, err := tx.Query(`desc issue312_1`)
c.Assert(err, IsNil)
for rows.Next() {
var (
c1 string
c2 string
c3 string
c4 string
c5 string
c6 string
)
rows.Scan(&c1, &c2, &c3, &c4, &c5, &c6)
c.Assert(c2, Equals, "float")
}
rows.Close()
mustCommit(c, tx)
tx = mustBegin(c, s.testDB)
rows, err = tx.Query(`desc issue312_2`)
c.Assert(err, IsNil)
for rows.Next() {
var (
c1 string
c2 string
c3 string
c4 string
c5 string
c6 string
)
rows.Scan(&c1, &c2, &c3, &c4, &c5, &c6)
c.Assert(c2, Equals, "double")
}
rows.Close()
mustCommit(c, tx)
}
func (s *testStmtSuite) TestDelete(c *C) {
s.fillData(s.testDB, c)
// Test compile
stmtList, err := tidb.Compile("DELETE from test where id = 2;")
c.Assert(err, IsNil)
str := stmtList[0].OriginText()
c.Assert(0, Less, len(str))
r := mustExec(c, s.testDB, `UPDATE test SET name = "abc" where id = 2;`)
checkResult(c, r, 1, 0)
r = mustExec(c, s.testDB, `DELETE from test where id = 2 limit 1;`)
checkResult(c, r, 1, 0)
// Test delete with false condition
r = mustExec(c, s.testDB, `DELETE from test where 0;`)
checkResult(c, r, 0, 0)
mustExec(c, s.testDB, "insert into test values (2, 'abc')")
r = mustExec(c, s.testDB, `delete from test where test.id = 2 limit 1`)
checkResult(c, r, 1, 0)
// Select data
tx := mustBegin(c, s.testDB)
rows, err := tx.Query(s.selectSql)
c.Assert(err, IsNil)
for rows.Next() {
var id int
var name string
rows.Scan(&id, &name)
c.Assert(id, Equals, 1)
c.Assert(name, Equals, "hello")
}
rows.Close()
mustCommit(c, tx)
r = mustExec(c, s.testDB, `DELETE from test;`)
checkResult(c, r, 1, 0)
// Should use index
strs := s.queryStrings(s.testDB, `explain DELETE from test where id = 2;`, c)
var useIndex bool
for _, str := range strs {
if strings.Index(str, "index") > 0 {
useIndex = true
}
}
if !useIndex {
c.Fatal(strs)
}
// Should not use index
strs = s.queryStrings(s.testDB, `explain DELETE from test;`, c)
useIndex = false
for _, str := range strs {
if strings.Index(str, "index") > 0 {
useIndex = true
}
}
if useIndex {
c.Fatal(strs)
}
}
func (s *testStmtSuite) TestUpdate(c *C) {
testDB, err := sql.Open(tidb.DriverName, tidb.EngineGoLevelDBMemory+"tmp/"+s.dbName)
c.Assert(err, IsNil)
s.fillData(testDB, c)
updateStr := `UPDATE test SET name = "abc" where id > 0;`
// Test compile
stmtList, err := tidb.Compile(updateStr)
c.Assert(err, IsNil)
str := stmtList[0].OriginText()
c.Assert(0, Less, len(str))
r := mustExec(c, testDB, updateStr)
checkResult(c, r, 2, 0)
// select data
tx := mustBegin(c, testDB)
rows, err := tx.Query(s.selectSql)
c.Assert(err, IsNil)
for rows.Next() {
var id int
var name string
rows.Scan(&id, &name)
c.Assert(name, Equals, "abc")
}
rows.Close()
mustCommit(c, tx)
// Should use index
strs := s.queryStrings(testDB, `explain `+updateStr, c)
var useIndex bool
for _, str := range strs {
if strings.Index(str, "index") > 0 {
useIndex = true
}
}
if !useIndex {
c.Fatal(strs)
}
// Should not use index
strs = s.queryStrings(testDB, `explain UPDATE test SET name = "abc"`, c)
useIndex = false
for _, str := range strs {
if strings.Index(str, "index") > 0 {
useIndex = true
}
}
if useIndex {
c.Fatal(strs)
}
// Test update without index
r = mustExec(c, testDB, `explain UPDATE test SET name = "abc"`)
checkResult(c, r, 0, 0)
r = mustExec(c, testDB, `UPDATE test SET name = "foo"`)
checkResult(c, r, 2, 0)
}
func (s *testStmtSuite) TestSet(c *C) {
testSQL := "SET @a = 1;"
mustExec(c, s.testDB, testSQL)
stmtList, err := tidb.Compile(testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok := stmtList[0].(*stmts.SetStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsFalse)
c.Assert(len(testStmt.Variables[0].String()), Greater, 0)
c.Assert(len(testStmt.OriginText()), Greater, 0)
mf := newMockFormatter()
testStmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
testSQL = `SET @a = "1";`
mustExec(c, s.testDB, testSQL)
testSQL = "SET @a = null;"
mustExec(c, s.testDB, testSQL)
testSQL = "SET @@global.autocommit = 1;"
mustExec(c, s.testDB, testSQL)
stmtList, err = tidb.Compile(testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok = stmtList[0].(*stmts.SetStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsFalse)
c.Assert(len(testStmt.Variables[0].String()), Greater, 0)
c.Assert(len(testStmt.OriginText()), Greater, 0)
mf = newMockFormatter()
testStmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
testSQL = "SET @@global.autocommit = null;"
mustExec(c, s.testDB, testSQL)
testSQL = "SET @@autocommit = 1;"
mustExec(c, s.testDB, testSQL)
stmtList, err = tidb.Compile(testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok = stmtList[0].(*stmts.SetStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsFalse)
c.Assert(len(testStmt.Variables[0].String()), Greater, 0)
c.Assert(len(testStmt.OriginText()), Greater, 0)
mf = newMockFormatter()
testStmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
testSQL = "SET @@autocommit = null;"
mustExec(c, s.testDB, testSQL)
errTestSql := "SET @@date_format = 1;"
tx := mustBegin(c, s.testDB)
_, err = tx.Exec(errTestSql)
c.Assert(err, NotNil)
tx.Rollback()
errTestSql = "SET @@rewriter_enabled = 1;"
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errTestSql)
c.Assert(err, NotNil)
tx.Rollback()
errTestSql = "SET xxx = abcd;"
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errTestSql)
c.Assert(err, NotNil)
tx.Rollback()
errTestSql = "SET @@global.a = 1;"
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errTestSql)
c.Assert(err, NotNil)
tx.Rollback()
errTestSql = "SET @@global.timestamp = 1;"
tx = mustBegin(c, s.testDB)
_, err = tx.Exec(errTestSql)
c.Assert(err, NotNil)
tx.Rollback()
}
func (s *testStmtSuite) TestUnion(c *C) {
testSQL := `select 1 union select 0;`
mustExec(c, s.testDB, testSQL)
stmtList, err := tidb.Compile(s.ctx, testSQL)
c.Assert(err, IsNil)
c.Assert(stmtList, HasLen, 1)
testStmt, ok := stmtList[0].(*stmts.UnionStmt)
c.Assert(ok, IsTrue)
c.Assert(testStmt.IsDDL(), IsFalse)
c.Assert(len(testStmt.OriginText()), Greater, 0)
mf := newMockFormatter()
testStmt.Explain(nil, mf)
c.Assert(mf.Len(), Greater, 0)
testSQL = `drop table if exists union_test; create table union_test(id int);
insert union_test values (1),(2); select id from union_test union select 1;`
mustExec(c, s.testDB, testSQL)
testSQL = `select id from union_test union select id from union_test;`
tx := mustBegin(c, s.testDB)
rows, err := tx.Query(testSQL)
c.Assert(err, IsNil)
matchRows(c, rows, [][]interface{}{{1}, {2}})
rows, err = tx.Query("select 1 union all select 1")
c.Assert(err, IsNil)
matchRows(c, rows, [][]interface{}{{1}, {1}})
rows, err = tx.Query("select 1 union all select 1 union select 1")
c.Assert(err, IsNil)
matchRows(c, rows, [][]interface{}{{1}})
rows, err = tx.Query("select 1 union (select 2) limit 1")
c.Assert(err, IsNil)
matchRows(c, rows, [][]interface{}{{1}})
rows, err = tx.Query("select 1 union (select 2) limit 1, 1")
c.Assert(err, IsNil)
matchRows(c, rows, [][]interface{}{{2}})
rows, err = tx.Query("select id from union_test union all (select 1) order by id desc")
c.Assert(err, IsNil)
matchRows(c, rows, [][]interface{}{{2}, {1}, {1}})
rows, err = tx.Query("select id as a from union_test union (select 1) order by a desc")
c.Assert(err, IsNil)
matchRows(c, rows, [][]interface{}{{2}, {1}})
rows, err = tx.Query(`select null union select "abc"`)
c.Assert(err, IsNil)
matchRows(c, rows, [][]interface{}{{nil}, {"abc"}})
rows, err = tx.Query(`select "abc" union select 1`)
c.Assert(err, IsNil)
matchRows(c, rows, [][]interface{}{{"abc"}, {"1"}})
mustCommit(c, tx)
}