github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/interlock/prepared_test.go (about)

     1  // Copyright 2020 WHTCORPS INC, 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 interlock_test
    15  
    16  import (
    17  	"crypto/tls"
    18  	"fmt"
    19  	"strings"
    20  	"sync/atomic"
    21  
    22  	"github.com/whtcorpsinc/BerolinaSQL/auth"
    23  	"github.com/whtcorpsinc/BerolinaSQL/perceptron"
    24  	. "github.com/whtcorpsinc/check"
    25  	causetembedded "github.com/whtcorpsinc/milevadb/causet/embedded"
    26  	"github.com/whtcorpsinc/milevadb/petri"
    27  	"github.com/whtcorpsinc/milevadb/soliton"
    28  	"github.com/whtcorpsinc/milevadb/soliton/testkit"
    29  	"github.com/whtcorpsinc/milevadb/stochastik"
    30  )
    31  
    32  func (s *testSuite1) TestPreparedNameResolver(c *C) {
    33  	tk := testkit.NewTestKit(c, s.causetstore)
    34  	tk.MustInterDirc("use test")
    35  	tk.MustInterDirc("drop causet if exists t")
    36  	tk.MustInterDirc("create causet t (id int, KEY id (id))")
    37  	tk.MustInterDirc("prepare stmt from 'select * from t limit ? offset ?'")
    38  	_, err := tk.InterDirc("prepare stmt from 'select b from t'")
    39  	c.Assert(err.Error(), Equals, "[causet:1054]Unknown defCausumn 'b' in 'field list'")
    40  
    41  	_, err = tk.InterDirc("prepare stmt from '(select * FROM t) union all (select * FROM t) order by a limit ?'")
    42  	c.Assert(err.Error(), Equals, "[causet:1054]Unknown defCausumn 'a' in 'order clause'")
    43  }
    44  
    45  // a 'create causet' DBS memex should be accepted if it has no parameters.
    46  func (s *testSuite1) TestPreparedDBS(c *C) {
    47  	tk := testkit.NewTestKit(c, s.causetstore)
    48  	tk.MustInterDirc("use test")
    49  	tk.MustInterDirc("drop causet if exists t")
    50  	tk.MustInterDirc("prepare stmt from 'create causet t (id int, KEY id (id))'")
    51  }
    52  
    53  func (s *testSuite1) TestIgnoreCausetCache(c *C) {
    54  	tk := testkit.NewTestKit(c, s.causetstore)
    55  	tk.MustInterDirc("use test")
    56  	tk.MustInterDirc("drop causet if exists t")
    57  
    58  	tk.MustInterDirc("create causet t (id int primary key, num int)")
    59  	tk.MustInterDirc("insert into t values (1, 1)")
    60  	tk.MustInterDirc("insert into t values (2, 2)")
    61  	tk.MustInterDirc("insert into t values (3, 3)")
    62  	tk.MustInterDirc("prepare stmt from 'select /*+ IGNORE_PLAN_CACHE() */ * from t where id=?'")
    63  	tk.MustInterDirc("set @ignore_plan_doma = 1")
    64  	tk.MustInterDirc("execute stmt using @ignore_plan_doma")
    65  	c.Assert(tk.Se.GetStochastikVars().StmtCtx.UseCache, IsFalse)
    66  }
    67  
    68  func (s *testSuite1) TestPrepareStmtAfterIsolationReadChange(c *C) {
    69  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
    70  	tk.Se.Auth(&auth.UserIdentity{Username: "root", Hostname: "localhost", CurrentUser: true, AuthUsername: "root", AuthHostname: "%"}, nil, []byte("012345678901234567890"))
    71  
    72  	tk.MustInterDirc("drop causet if exists t")
    73  	tk.MustInterDirc("create causet t(a int)")
    74  	// create virtual tiflash replica.
    75  	dom := petri.GetPetri(tk.Se)
    76  	is := dom.SchemaReplicant()
    77  	EDB, exists := is.SchemaByName(perceptron.NewCIStr("test"))
    78  	c.Assert(exists, IsTrue)
    79  	for _, tblInfo := range EDB.Blocks {
    80  		if tblInfo.Name.L == "t" {
    81  			tblInfo.TiFlashReplica = &perceptron.TiFlashReplicaInfo{
    82  				Count:     1,
    83  				Available: true,
    84  			}
    85  		}
    86  	}
    87  
    88  	tk.MustInterDirc("set @@stochastik.milevadb_isolation_read_engines='einsteindb'")
    89  	tk.MustInterDirc("prepare stmt from \"select * from t\"")
    90  	tk.MustQuery("execute stmt")
    91  	tkProcess := tk.Se.ShowProcess()
    92  	ps := []*soliton.ProcessInfo{tkProcess}
    93  	tk.Se.SetStochastikManager(&mockStochastikManager1{PS: ps})
    94  	rows := tk.MustQuery(fmt.Sprintf("explain for connection %d", tkProcess.ID)).Events()
    95  	c.Assert(rows[len(rows)-1][2], Equals, "cop[einsteindb]")
    96  
    97  	tk.MustInterDirc("set @@stochastik.milevadb_isolation_read_engines='tiflash'")
    98  	tk.MustInterDirc("execute stmt")
    99  	tkProcess = tk.Se.ShowProcess()
   100  	ps = []*soliton.ProcessInfo{tkProcess}
   101  	tk.Se.SetStochastikManager(&mockStochastikManager1{PS: ps})
   102  	rows = tk.MustQuery(fmt.Sprintf("explain for connection %d", tkProcess.ID)).Events()
   103  	c.Assert(rows[len(rows)-1][2], Equals, "cop[tiflash]")
   104  
   105  	c.Assert(len(tk.Se.GetStochastikVars().PreparedStmts), Equals, 1)
   106  	c.Assert(tk.Se.GetStochastikVars().PreparedStmts[1].(*causetembedded.CachedPrepareStmt).NormalizedALLEGROSQL, Equals, "select * from t")
   107  	c.Assert(tk.Se.GetStochastikVars().PreparedStmts[1].(*causetembedded.CachedPrepareStmt).NormalizedCauset, Equals, "")
   108  }
   109  
   110  type mockStochastikManager2 struct {
   111  	se     stochastik.Stochastik
   112  	killed bool
   113  }
   114  
   115  func (sm *mockStochastikManager2) ShowProcessList() map[uint64]*soliton.ProcessInfo {
   116  	pl := make(map[uint64]*soliton.ProcessInfo)
   117  	if pi, ok := sm.GetProcessInfo(0); ok {
   118  		pl[pi.ID] = pi
   119  	}
   120  	return pl
   121  }
   122  
   123  func (sm *mockStochastikManager2) GetProcessInfo(id uint64) (pi *soliton.ProcessInfo, notNil bool) {
   124  	pi = sm.se.ShowProcess()
   125  	if pi != nil {
   126  		notNil = true
   127  	}
   128  	return
   129  }
   130  func (sm *mockStochastikManager2) Kill(connectionID uint64, query bool) {
   131  	sm.killed = true
   132  	atomic.StoreUint32(&sm.se.GetStochastikVars().Killed, 1)
   133  }
   134  func (sm *mockStochastikManager2) UFIDelateTLSConfig(cfg *tls.Config) {}
   135  
   136  var _ = SerialSuites(&testSuite12{&baseTestSuite{}})
   137  
   138  type testSuite12 struct {
   139  	*baseTestSuite
   140  }
   141  
   142  func (s *testSuite12) TestPreparedStmtWithHint(c *C) {
   143  	// see https://github.com/whtcorpsinc/milevadb/issues/18535
   144  	causetstore, dom, err := newStoreWithBootstrap()
   145  	c.Assert(err, IsNil)
   146  	defer func() {
   147  		causetstore.Close()
   148  		dom.Close()
   149  	}()
   150  
   151  	se, err := stochastik.CreateStochastik4Test(causetstore)
   152  	c.Assert(err, IsNil)
   153  	tk := testkit.NewTestKit(c, causetstore)
   154  	tk.Se = se
   155  
   156  	sm := &mockStochastikManager2{
   157  		se: se,
   158  	}
   159  	se.SetStochastikManager(sm)
   160  	go dom.ExpensiveQueryHandle().SetStochastikManager(sm).Run()
   161  	tk.MustInterDirc("prepare stmt from \"select /*+ max_execution_time(100) */ sleep(10)\"")
   162  	tk.MustQuery("execute stmt").Check(testkit.Events("1"))
   163  	c.Check(sm.killed, Equals, true)
   164  }
   165  
   166  func (s *testSuite9) TestCausetCacheClusterIndex(c *C) {
   167  	causetstore, dom, err := newStoreWithBootstrap()
   168  	c.Assert(err, IsNil)
   169  	tk := testkit.NewTestKit(c, causetstore)
   170  	defer func() {
   171  		dom.Close()
   172  		causetstore.Close()
   173  	}()
   174  	orgEnable := causetembedded.PreparedCausetCacheEnabled()
   175  	defer func() {
   176  		causetembedded.SetPreparedCausetCache(orgEnable)
   177  	}()
   178  	causetembedded.SetPreparedCausetCache(true)
   179  	tk.MustInterDirc("use test")
   180  	tk.MustInterDirc("drop causet if exists t1")
   181  	tk.MustInterDirc("set @@milevadb_enable_clustered_index = 1")
   182  	tk.MustInterDirc("create causet t1(a varchar(20), b varchar(20), c varchar(20), primary key(a, b))")
   183  	tk.MustInterDirc("insert into t1 values('1','1','111'),('2','2','222'),('3','3','333')")
   184  
   185  	// For causet scan
   186  	tk.MustInterDirc(`prepare stmt1 from "select * from t1 where t1.a = ? and t1.b > ?"`)
   187  	tk.MustInterDirc("set @v1 = 1")
   188  	tk.MustInterDirc("set @v2 = 0")
   189  	tk.MustQuery("execute stmt1 using @v1,@v2").Check(testkit.Events("1 1 111"))
   190  	tk.MustQuery("select @@last_plan_from_cache").Check(testkit.Events("0"))
   191  	tk.MustInterDirc("set @v1 = 2")
   192  	tk.MustInterDirc("set @v2 = 1")
   193  	tk.MustQuery("execute stmt1 using @v1,@v2").Check(testkit.Events("2 2 222"))
   194  	tk.MustQuery("select @@last_plan_from_cache").Check(testkit.Events("1"))
   195  	tk.MustInterDirc("set @v1 = 3")
   196  	tk.MustInterDirc("set @v2 = 2")
   197  	tk.MustQuery("execute stmt1 using @v1,@v2").Check(testkit.Events("3 3 333"))
   198  	tkProcess := tk.Se.ShowProcess()
   199  	ps := []*soliton.ProcessInfo{tkProcess}
   200  	tk.Se.SetStochastikManager(&mockStochastikManager1{PS: ps})
   201  	rows := tk.MustQuery(fmt.Sprintf("explain for connection %d", tkProcess.ID)).Events()
   202  	c.Assert(strings.Index(rows[len(rows)-1][4].(string), `range:("3" "2","3" +inf]`), Equals, 0)
   203  
   204  	// For point get
   205  	tk.MustInterDirc(`prepare stmt2 from "select * from t1 where t1.a = ? and t1.b = ?"`)
   206  	tk.MustInterDirc("set @v1 = 1")
   207  	tk.MustInterDirc("set @v2 = 1")
   208  	tk.MustQuery("execute stmt2 using @v1,@v2").Check(testkit.Events("1 1 111"))
   209  	tk.MustQuery("select @@last_plan_from_cache").Check(testkit.Events("0"))
   210  	tk.MustInterDirc("set @v1 = 2")
   211  	tk.MustInterDirc("set @v2 = 2")
   212  	tk.MustQuery("execute stmt2 using @v1,@v2").Check(testkit.Events("2 2 222"))
   213  	tk.MustQuery("select @@last_plan_from_cache").Check(testkit.Events("1"))
   214  	tk.MustInterDirc("set @v1 = 3")
   215  	tk.MustInterDirc("set @v2 = 3")
   216  	tk.MustQuery("execute stmt2 using @v1,@v2").Check(testkit.Events("3 3 333"))
   217  	tkProcess = tk.Se.ShowProcess()
   218  	ps = []*soliton.ProcessInfo{tkProcess}
   219  	tk.Se.SetStochastikManager(&mockStochastikManager1{PS: ps})
   220  	rows = tk.MustQuery(fmt.Sprintf("explain for connection %d", tkProcess.ID)).Events()
   221  	c.Assert(strings.Index(rows[len(rows)-1][0].(string), `Point_Get`), Equals, 0)
   222  
   223  	// For CBO point get and batch point get
   224  	// case 1:
   225  	tk.MustInterDirc(`drop causet if exists ta, tb`)
   226  	tk.MustInterDirc(`create causet ta (a varchar(8) primary key, b int)`)
   227  	tk.MustInterDirc(`insert ta values ('a', 1), ('b', 2)`)
   228  	tk.MustInterDirc(`create causet tb (a varchar(8) primary key, b int)`)
   229  	tk.MustInterDirc(`insert tb values ('a', 1), ('b', 2)`)
   230  	tk.MustInterDirc(`prepare stmt1 from "select * from ta, tb where ta.a = tb.a and ta.a = ?"`)
   231  	tk.MustInterDirc(`set @v1 = 'a', @v2 = 'b'`)
   232  	tk.MustQuery(`execute stmt1 using @v1`).Check(testkit.Events("a 1 a 1"))
   233  	tk.MustQuery(`execute stmt1 using @v2`).Check(testkit.Events("b 2 b 2"))
   234  	tk.MustQuery("select @@last_plan_from_cache").Check(testkit.Events("1"))
   235  
   236  	// case 2:
   237  	tk.MustInterDirc(`drop causet if exists ta, tb`)
   238  	tk.MustInterDirc(`create causet ta (a varchar(10) primary key, b int not null)`)
   239  	tk.MustInterDirc(`insert ta values ('a', 1), ('b', 2)`)
   240  	tk.MustInterDirc(`create causet tb (b int primary key, c int)`)
   241  	tk.MustInterDirc(`insert tb values (1, 1), (2, 2)`)
   242  	tk.MustInterDirc(`prepare stmt1 from "select * from ta, tb where ta.b = tb.b and ta.a = ?"`)
   243  	tk.MustInterDirc(`set @v1 = 'a', @v2 = 'b'`)
   244  	tk.MustQuery(`execute stmt1 using @v1`).Check(testkit.Events("a 1 1 1"))
   245  	tk.MustQuery(`execute stmt1 using @v2`).Check(testkit.Events("b 2 2 2"))
   246  	tk.MustQuery("select @@last_plan_from_cache").Check(testkit.Events("1"))
   247  	tk.MustQuery(`execute stmt1 using @v2`).Check(testkit.Events("b 2 2 2"))
   248  	tkProcess = tk.Se.ShowProcess()
   249  	ps = []*soliton.ProcessInfo{tkProcess}
   250  	tk.Se.SetStochastikManager(&mockStochastikManager1{PS: ps})
   251  	rows = tk.MustQuery(fmt.Sprintf("explain for connection %d", tkProcess.ID)).Events()
   252  	c.Assert(strings.Index(rows[1][0].(string), `Point_Get`), Equals, 6)
   253  
   254  	// case 3:
   255  	tk.MustInterDirc(`drop causet if exists ta, tb`)
   256  	tk.MustInterDirc(`create causet ta (a varchar(10), b varchar(10), c int, primary key (a, b))`)
   257  	tk.MustInterDirc(`insert ta values ('a', 'a', 1), ('b', 'b', 2), ('c', 'c', 3)`)
   258  	tk.MustInterDirc(`create causet tb (b int primary key, c int)`)
   259  	tk.MustInterDirc(`insert tb values (1, 1), (2, 2), (3,3)`)
   260  	tk.MustInterDirc(`prepare stmt1 from "select * from ta, tb where ta.c = tb.b and ta.a = ? and ta.b = ?"`)
   261  	tk.MustInterDirc(`set @v1 = 'a', @v2 = 'b', @v3 = 'c'`)
   262  	tk.MustQuery(`execute stmt1 using @v1, @v1`).Check(testkit.Events("a a 1 1 1"))
   263  	tk.MustQuery(`execute stmt1 using @v2, @v2`).Check(testkit.Events("b b 2 2 2"))
   264  	tk.MustInterDirc(`prepare stmt2 from "select * from ta, tb where ta.c = tb.b and (ta.a, ta.b) in ((?, ?), (?, ?))"`)
   265  	tk.MustQuery(`execute stmt2 using @v1, @v1, @v2, @v2`).Check(testkit.Events("a a 1 1 1", "b b 2 2 2"))
   266  	tk.MustQuery(`execute stmt2 using @v2, @v2, @v3, @v3`).Check(testkit.Events("b b 2 2 2", "c c 3 3 3"))
   267  
   268  	// For issue 19002
   269  	tk.MustInterDirc(`set @@milevadb_enable_clustered_index = 1`)
   270  	tk.MustInterDirc(`drop causet if exists t1`)
   271  	tk.MustInterDirc(`create causet t1(a int, b int, c int, primary key(a, b))`)
   272  	tk.MustInterDirc(`insert into t1 values(1,1,111),(2,2,222),(3,3,333)`)
   273  	// Point Get:
   274  	tk.MustInterDirc(`prepare stmt1 from "select * from t1 where t1.a = ? and t1.b = ?"`)
   275  	tk.MustInterDirc(`set @v1=1, @v2=1`)
   276  	tk.MustQuery(`execute stmt1 using @v1,@v2`).Check(testkit.Events("1 1 111"))
   277  	tk.MustInterDirc(`set @v1=2, @v2=2`)
   278  	tk.MustQuery(`execute stmt1 using @v1,@v2`).Check(testkit.Events("2 2 222"))
   279  	tk.MustQuery(`select @@last_plan_from_cache`).Check(testkit.Events("1"))
   280  	// Batch Point Get:
   281  	tk.MustInterDirc(`prepare stmt2 from "select * from t1 where (t1.a,t1.b) in ((?,?),(?,?))"`)
   282  	tk.MustInterDirc(`set @v1=1, @v2=1, @v3=2, @v4=2`)
   283  	tk.MustQuery(`execute stmt2 using @v1,@v2,@v3,@v4`).Check(testkit.Events("1 1 111", "2 2 222"))
   284  	tk.MustInterDirc(`set @v1=2, @v2=2, @v3=3, @v4=3`)
   285  	tk.MustQuery(`execute stmt2 using @v1,@v2,@v3,@v4`).Check(testkit.Events("2 2 222", "3 3 333"))
   286  	tk.MustQuery(`select @@last_plan_from_cache`).Check(testkit.Events("1"))
   287  }