Пример #1
0
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)
}
Пример #2
0
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()
}
Пример #3
0
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)
}
Пример #4
0
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));")
}
Пример #5
0
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()
}
Пример #6
0
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()
}
Пример #7
0
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)
}
Пример #8
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
}
Пример #9
0
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)
}
Пример #10
0
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)
}
Пример #11
0
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)
}
Пример #12
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)
}
Пример #13
0
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)")
}
Пример #14
0
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)
}
Пример #15
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)
}
Пример #16
0
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")
}
Пример #17
0
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)
}
Пример #18
0
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)
}
Пример #19
0
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()
}
Пример #20
0
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)
}
Пример #21
0
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))
}
Пример #22
0
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)
}
Пример #23
0
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)
	}
}
Пример #24
0
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)
}
Пример #25
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()
}
Пример #26
0
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)
}