github.com/insionng/yougam@v0.0.0-20170714101924-2bc18d833463/libraries/pingcap/tidb/session_test.go (about)

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