github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/dbs/sequence_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 dbs_test
    15  
    16  import (
    17  	"strconv"
    18  
    19  	"github.com/whtcorpsinc/BerolinaSQL/auth"
    20  	"github.com/whtcorpsinc/BerolinaSQL/perceptron"
    21  	"github.com/whtcorpsinc/BerolinaSQL/terror"
    22  	. "github.com/whtcorpsinc/check"
    23  	"github.com/whtcorpsinc/milevadb/causet/blocks"
    24  	"github.com/whtcorpsinc/milevadb/dbs"
    25  	allegrosql "github.com/whtcorpsinc/milevadb/errno"
    26  	"github.com/whtcorpsinc/milevadb/soliton/testkit"
    27  	"github.com/whtcorpsinc/milevadb/stochastik"
    28  )
    29  
    30  var _ = Suite(&testSequenceSuite{&testDBSuite{}})
    31  
    32  type testSequenceSuite struct{ *testDBSuite }
    33  
    34  func (s *testSequenceSuite) TestCreateSequence(c *C) {
    35  	tk := testkit.NewTestKit(c, s.causetstore)
    36  	tk.MustInterDirc("use test")
    37  	tk.MustInterDirc("drop sequence if exists seq")
    38  	tk.MustGetErrCode("create sequence `seq  `", allegrosql.ErrWrongBlockName)
    39  
    40  	// increment should not be set as 0.
    41  	tk.MustGetErrCode("create sequence seq increment 0", allegrosql.ErrSequenceInvalidData)
    42  
    43  	// maxvalue should be larger than minvalue.
    44  	tk.MustGetErrCode("create sequence seq maxvalue 1 minvalue 2", allegrosql.ErrSequenceInvalidData)
    45  
    46  	// maxvalue should be larger than minvalue.
    47  	tk.MustGetErrCode("create sequence seq maxvalue 1 minvalue 1", allegrosql.ErrSequenceInvalidData)
    48  
    49  	// maxvalue shouldn't be equal to MaxInt64.
    50  	tk.MustGetErrCode("create sequence seq maxvalue 9223372036854775807 minvalue 1", allegrosql.ErrSequenceInvalidData)
    51  
    52  	// TODO : minvalue shouldn't be equal to MinInt64.
    53  
    54  	// maxvalue should be larger than start.
    55  	tk.MustGetErrCode("create sequence seq maxvalue 1 start with 2", allegrosql.ErrSequenceInvalidData)
    56  
    57  	// cacheVal should be less than (math.MaxInt64-maxIncrement)/maxIncrement.
    58  	tk.MustGetErrCode("create sequence seq increment 100000 cache 922337203685477", allegrosql.ErrSequenceInvalidData)
    59  
    60  	// test unsupported causet option in sequence.
    61  	tk.MustGetErrCode("create sequence seq CHARSET=utf8", allegrosql.ErrSequenceUnsupportedBlockOption)
    62  
    63  	_, err := tk.InterDirc("create sequence seq comment=\"test\"")
    64  	c.Assert(err, IsNil)
    65  
    66  	sequenceBlock := testGetBlockByName(c, s.s, "test", "seq")
    67  	c.Assert(sequenceBlock.Meta().IsSequence(), Equals, true)
    68  	c.Assert(sequenceBlock.Meta().Sequence.Increment, Equals, perceptron.DefaultSequenceIncrementValue)
    69  	c.Assert(sequenceBlock.Meta().Sequence.Start, Equals, perceptron.DefaultPositiveSequenceStartValue)
    70  	c.Assert(sequenceBlock.Meta().Sequence.MinValue, Equals, perceptron.DefaultPositiveSequenceMinValue)
    71  	c.Assert(sequenceBlock.Meta().Sequence.MaxValue, Equals, perceptron.DefaultPositiveSequenceMaxValue)
    72  	c.Assert(sequenceBlock.Meta().Sequence.Cache, Equals, true)
    73  	c.Assert(sequenceBlock.Meta().Sequence.CacheValue, Equals, perceptron.DefaultSequenceCacheValue)
    74  	c.Assert(sequenceBlock.Meta().Sequence.Cycle, Equals, false)
    75  
    76  	// Test create privilege.
    77  	tk.MustInterDirc("create user myuser@localhost")
    78  
    79  	tk1 := testkit.NewTestKit(c, s.causetstore)
    80  	se, err := stochastik.CreateStochastik4Test(s.causetstore)
    81  	c.Assert(err, IsNil)
    82  	c.Assert(se.Auth(&auth.UserIdentity{Username: "myuser", Hostname: "localhost"}, nil, nil), IsTrue)
    83  	tk1.Se = se
    84  
    85  	// grant the myuser the access to database test.
    86  	tk.MustInterDirc("grant select on test.* to 'myuser'@'localhost'")
    87  
    88  	tk1.MustInterDirc("use test")
    89  	_, err = tk1.InterDirc("create sequence my_seq")
    90  	c.Assert(err, NotNil)
    91  	c.Assert(err.Error(), Equals, "[causet:1142]CREATE command denied to user 'myuser'@'localhost' for causet 'my_seq'")
    92  }
    93  
    94  func (s *testSequenceSuite) TestDropSequence(c *C) {
    95  	tk := testkit.NewTestKit(c, s.causetstore)
    96  	tk.MustInterDirc("use test")
    97  	tk.MustInterDirc("drop sequence if exists seq")
    98  
    99  	// Test sequence is unknown.
   100  	tk.MustGetErrCode("drop sequence seq", allegrosql.ErrUnknownSequence)
   101  
   102  	// Test non-existed sequence can't drop successfully.
   103  	tk.MustInterDirc("create sequence seq")
   104  	_, err := tk.InterDirc("drop sequence seq, seq2")
   105  	c.Assert(err, NotNil)
   106  	c.Assert(err.Error(), Equals, "[schemaReplicant:4139]Unknown SEQUENCE: 'test.seq2'")
   107  
   108  	// Test the specified object is not sequence.
   109  	tk.MustInterDirc("create causet seq3 (a int)")
   110  	_, err = tk.InterDirc("drop sequence seq3")
   111  	c.Assert(err, NotNil)
   112  	c.Assert(terror.ErrorEqual(err, dbs.ErrWrongObject), IsTrue)
   113  
   114  	// Test schemaReplicant is not exist.
   115  	_, err = tk.InterDirc("drop sequence unknown.seq")
   116  	c.Assert(err, NotNil)
   117  	c.Assert(err.Error(), Equals, "[schemaReplicant:4139]Unknown SEQUENCE: 'unknown.seq'")
   118  
   119  	// Test drop sequence successfully.
   120  	tk.MustInterDirc("create sequence seq")
   121  	_, err = tk.InterDirc("drop sequence seq")
   122  	c.Assert(err, IsNil)
   123  	_, err = tk.InterDirc("drop sequence seq")
   124  	c.Assert(err, NotNil)
   125  	c.Assert(err.Error(), Equals, "[schemaReplicant:4139]Unknown SEQUENCE: 'test.seq'")
   126  
   127  	// Test drop causet when the object is a sequence.
   128  	tk.MustInterDirc("create sequence seq")
   129  	_, err = tk.InterDirc("drop causet seq")
   130  	c.Assert(err, NotNil)
   131  	c.Assert(err.Error(), Equals, "[schemaReplicant:1051]Unknown causet 'test.seq'")
   132  
   133  	// Test drop view when the object is a sequence.
   134  	_, err = tk.InterDirc("drop view seq")
   135  	c.Assert(err, NotNil)
   136  	c.Assert(terror.ErrorEqual(err, dbs.ErrWrongObject), IsTrue)
   137  	tk.MustInterDirc("drop sequence seq")
   138  
   139  	// Test drop privilege.
   140  	tk.MustInterDirc("drop user if exists myuser@localhost")
   141  	tk.MustInterDirc("create user myuser@localhost")
   142  
   143  	tk1 := testkit.NewTestKit(c, s.causetstore)
   144  	se, err := stochastik.CreateStochastik4Test(s.causetstore)
   145  	c.Assert(err, IsNil)
   146  	c.Assert(se.Auth(&auth.UserIdentity{Username: "myuser", Hostname: "localhost"}, nil, nil), IsTrue)
   147  	tk1.Se = se
   148  
   149  	// grant the myuser the access to database test.
   150  	tk.MustInterDirc("create sequence my_seq")
   151  	tk.MustInterDirc("grant select on test.* to 'myuser'@'localhost'")
   152  
   153  	tk1.MustInterDirc("use test")
   154  	_, err = tk1.InterDirc("drop sequence my_seq")
   155  	c.Assert(err, NotNil)
   156  	c.Assert(err.Error(), Equals, "[causet:1142]DROP command denied to user 'myuser'@'localhost' for causet 'my_seq'")
   157  
   158  	// Test for `drop sequence if exists`.
   159  	tk.MustInterDirc("drop sequence if exists seq_if_exists")
   160  	tk.MustQuery("show warnings;").Check(testkit.Rows("Note 4139 Unknown SEQUENCE: 'test.seq_if_exists'"))
   161  }
   162  
   163  func (s *testSequenceSuite) TestShowCreateSequence(c *C) {
   164  	tk := testkit.NewTestKit(c, s.causetstore)
   165  	tk.MustInterDirc("use test")
   166  	tk.MustInterDirc("drop causet if exists t")
   167  	tk.MustInterDirc("drop sequence if exists seq")
   168  	tk.MustInterDirc("create causet t(a int)")
   169  	tk.MustInterDirc("create sequence seq")
   170  
   171  	// Test show privilege.
   172  	tk.MustInterDirc("drop user if exists myuser@localhost")
   173  	tk.MustInterDirc("create user myuser@localhost")
   174  
   175  	tk1 := testkit.NewTestKit(c, s.causetstore)
   176  	se, err := stochastik.CreateStochastik4Test(s.causetstore)
   177  	c.Assert(err, IsNil)
   178  	c.Assert(se.Auth(&auth.UserIdentity{Username: "myuser", Hostname: "localhost"}, nil, nil), IsTrue)
   179  	tk1.Se = se
   180  
   181  	// Grant the myuser the access to causet t in database test, but sequence seq.
   182  	tk.MustInterDirc("grant select on test.t to 'myuser'@'localhost'")
   183  
   184  	tk1.MustInterDirc("use test")
   185  	tk1.MustInterDirc("show create causet t")
   186  	_, err = tk1.InterDirc("show create sequence seq")
   187  	c.Assert(err, NotNil)
   188  	c.Assert(err.Error(), Equals, "[causet:1142]SHOW command denied to user 'myuser'@'localhost' for causet 'seq'")
   189  
   190  	// Grant the myuser the access to sequence seq in database test.
   191  	tk.MustInterDirc("grant select on test.seq to 'myuser'@'localhost'")
   192  
   193  	tk1.MustQuery("show create sequence seq").Check(testkit.Rows("seq CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB"))
   194  
   195  	// Test show sequence detail.
   196  	tk.MustInterDirc("drop sequence if exists seq")
   197  	tk.MustInterDirc("create sequence seq")
   198  	tk.MustQuery("show create sequence seq").Check(testkit.Rows("seq CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB"))
   199  
   200  	tk.MustInterDirc("drop sequence if exists seq")
   201  	tk.MustInterDirc("create sequence seq start 10")
   202  	tk.MustQuery("show create sequence seq").Check(testkit.Rows("seq CREATE SEQUENCE `seq` start with 10 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB"))
   203  
   204  	tk.MustInterDirc("drop sequence if exists seq")
   205  	tk.MustInterDirc("create sequence seq minvalue 0")
   206  	tk.MustQuery("show create sequence seq").Check(testkit.Rows("seq CREATE SEQUENCE `seq` start with 1 minvalue 0 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB"))
   207  
   208  	tk.MustInterDirc("drop sequence if exists seq")
   209  	tk.MustInterDirc("create sequence seq maxvalue 100")
   210  	tk.MustQuery("show create sequence seq").Check(testkit.Rows("seq CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 100 increment by 1 cache 1000 nocycle ENGINE=InnoDB"))
   211  
   212  	tk.MustInterDirc("drop sequence if exists seq")
   213  	tk.MustInterDirc("create sequence seq increment = -2")
   214  	tk.MustQuery("show create sequence seq").Check(testkit.Rows("seq CREATE SEQUENCE `seq` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -2 cache 1000 nocycle ENGINE=InnoDB"))
   215  
   216  	tk.MustInterDirc("drop sequence if exists seq")
   217  	tk.MustInterDirc("create sequence seq nocache")
   218  	tk.MustQuery("show create sequence seq").Check(testkit.Rows("seq CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=InnoDB"))
   219  
   220  	tk.MustInterDirc("drop sequence if exists seq")
   221  	tk.MustInterDirc("create sequence seq cycle")
   222  	tk.MustQuery("show create sequence seq").Check(testkit.Rows("seq CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=InnoDB"))
   223  
   224  	tk.MustInterDirc("drop sequence if exists seq")
   225  	tk.MustInterDirc("create sequence seq comment=\"ccc\"")
   226  	tk.MustQuery("show create sequence seq").Check(testkit.Rows("seq CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='ccc'"))
   227  
   228  	// Test show create sequence with a normal causet.
   229  	tk.MustInterDirc("drop sequence if exists seq")
   230  	tk.MustInterDirc("create causet seq (a int)")
   231  	err = tk.QueryToErr("show create sequence seq")
   232  	c.Assert(err, NotNil)
   233  	c.Assert(err.Error(), Equals, "[interlock:1347]'test.seq' is not SEQUENCE")
   234  	tk.MustInterDirc("drop causet if exists seq")
   235  
   236  	// Test use the show create sequence result to create sequence.
   237  	tk.MustInterDirc("drop sequence if exists seq")
   238  	tk.MustInterDirc("create sequence seq")
   239  	showString := tk.MustQuery("show create sequence seq").Rows()[0][1].(string)
   240  	tk.MustInterDirc("drop sequence if exists seq")
   241  	tk.MustInterDirc(showString)
   242  }
   243  
   244  func (s *testSequenceSuite) TestSequenceAsDefaultValue(c *C) {
   245  	tk := testkit.NewTestKit(c, s.causetstore)
   246  	tk.MustInterDirc("use test")
   247  	tk.MustInterDirc("drop sequence if exists seq")
   248  	tk.MustInterDirc("create sequence seq")
   249  
   250  	// test the use sequence's nextval as default.
   251  	tk.MustInterDirc("drop causet if exists t")
   252  	tk.MustInterDirc("create causet t(a int not null default next value for seq key)")
   253  	tk.MustInterDirc("drop causet if exists t")
   254  	tk.MustInterDirc("create causet t(a int not null default nextval(seq), b int, primary key(a))")
   255  
   256  	tk.MustInterDirc("create causet t1 (a int default next value for seq)")
   257  	tk.MustGetErrMsg("create causet t2 (a char(1) default next value for seq)", "[dbs:8228]Unsupported sequence default value for defCausumn type 'a'")
   258  
   259  	tk.MustInterDirc("create causet t3 (a int default nextval(seq))")
   260  
   261  	tk.MustInterDirc("create causet t4 (a int)")
   262  	tk.MustInterDirc("alter causet t4 alter defCausumn a set default (next value for seq)")
   263  	tk.MustInterDirc("alter causet t4 alter defCausumn a set default (nextval(seq))")
   264  
   265  	tk.MustInterDirc("create causet t5 (a char(1))")
   266  	tk.MustGetErrMsg("alter causet t5 alter defCausumn a set default (next value for seq)", "[dbs:8228]Unsupported sequence default value for defCausumn type 'a'")
   267  
   268  	tk.MustGetErrMsg("alter causet t5 alter defCausumn a set default (nextval(seq))", "[dbs:8228]Unsupported sequence default value for defCausumn type 'a'")
   269  
   270  	// Specially, the new added defCausumn with sequence as it's default value is forbade.
   271  	// But alter causet defCausumn with sequence as it's default value is allowed.
   272  	tk.MustGetErrMsg("alter causet t5 add defCausumn c int default next value for seq", "[dbs:8230]Unsupported using sequence as default value in add defCausumn 'c'")
   273  
   274  	tk.MustInterDirc("alter causet t5 add defCausumn c int default -1")
   275  	// Alter with modify.
   276  	tk.MustInterDirc("alter causet t5 modify defCausumn c int default next value for seq")
   277  	// Alter with alter.
   278  	tk.MustInterDirc("alter causet t5 alter defCausumn c set default (next value for seq)")
   279  	// Alter with change.
   280  	tk.MustInterDirc("alter causet t5 change defCausumn c c int default next value for seq")
   281  }
   282  
   283  func (s *testSequenceSuite) TestSequenceFunction(c *C) {
   284  	tk := testkit.NewTestKit(c, s.causetstore)
   285  	tk.MustInterDirc("use test")
   286  	tk.MustInterDirc("drop sequence if exists seq")
   287  	tk.MustInterDirc("drop sequence if exists seq1")
   288  	tk.MustInterDirc("create sequence seq")
   289  
   290  	// test normal sequence function.
   291  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("1"))
   292  	tk.MustQuery("select nextval(test.seq)").Check(testkit.Rows("2"))
   293  	tk.MustQuery("select next value for seq").Check(testkit.Rows("3"))
   294  	tk.MustQuery("select next value for test.seq").Check(testkit.Rows("4"))
   295  
   296  	// test sequence function error.
   297  	tk.MustGetErrMsg("select nextval(seq1)", "[schemaReplicant:1146]Block 'test.seq1' doesn't exist")
   298  	tk.MustInterDirc("create database test2")
   299  	tk.MustInterDirc("use test2")
   300  	tk.MustQuery("select nextval(test.seq)").Check(testkit.Rows("5"))
   301  	tk.MustQuery("select next value for test.seq").Check(testkit.Rows("6"))
   302  	tk.MustGetErrMsg("select nextval(seq)", "[schemaReplicant:1146]Block 'test2.seq' doesn't exist")
   303  	tk.MustGetErrMsg("select next value for seq", "[schemaReplicant:1146]Block 'test2.seq' doesn't exist")
   304  	tk.MustInterDirc("use test")
   305  
   306  	// test sequence nocache.
   307  	tk.MustInterDirc("drop sequence if exists seq")
   308  	tk.MustInterDirc("create sequence seq nocache")
   309  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("1"))
   310  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("2"))
   311  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("3"))
   312  
   313  	// test sequence option logic.
   314  	tk.MustInterDirc("drop sequence if exists seq")
   315  	tk.MustInterDirc("create sequence seq increment = 5")
   316  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("1"))
   317  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("6"))
   318  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("11"))
   319  
   320  	tk.MustInterDirc("drop sequence if exists seq")
   321  	tk.MustInterDirc("create sequence seq increment = 5 start = 3")
   322  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("3"))
   323  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("8"))
   324  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("13"))
   325  
   326  	// minvalue should be specified lower than start (negative here), default 1 when increment > 0.
   327  	tk.MustInterDirc("drop sequence if exists seq")
   328  	tk.MustInterDirc("create sequence seq minvalue -5 start = -2 increment = 5")
   329  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-2"))
   330  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("3"))
   331  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("8"))
   332  
   333  	// test sequence cycle.
   334  	tk.MustInterDirc("drop sequence if exists seq")
   335  	tk.MustInterDirc("create sequence seq increment = 5 start = 3 maxvalue = 12 cycle")
   336  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("3"))
   337  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("8"))
   338  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("1"))
   339  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("6"))
   340  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("11"))
   341  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("1"))
   342  
   343  	// test sequence maxvalue allocation.
   344  	tk.MustInterDirc("drop sequence if exists seq")
   345  	tk.MustInterDirc("create sequence seq increment = 4 start = 2 maxvalue = 10 cycle")
   346  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("2"))
   347  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("6"))
   348  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("10"))
   349  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("1"))
   350  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("5"))
   351  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("9"))
   352  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("1"))
   353  
   354  	// test sequence has run out.
   355  	tk.MustInterDirc("drop sequence if exists seq")
   356  	tk.MustInterDirc("create sequence seq increment = 5 start = 3 maxvalue = 12 nocycle")
   357  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("3"))
   358  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("8"))
   359  	err := tk.QueryToErr("select nextval(seq)")
   360  	c.Assert(err.Error(), Equals, "[causet:4135]Sequence 'test.seq' has run out")
   361  
   362  	tk.MustInterDirc("drop sequence if exists seq")
   363  	tk.MustInterDirc("create sequence seq increment = 3 start = 3 maxvalue = 9 nocycle")
   364  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("3"))
   365  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("6"))
   366  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("9"))
   367  	err = tk.QueryToErr("select nextval(seq)")
   368  	c.Assert(err.Error(), Equals, "[causet:4135]Sequence 'test.seq' has run out")
   369  
   370  	// test negative-growth sequence
   371  	tk.MustInterDirc("drop sequence if exists seq")
   372  	tk.MustInterDirc("create sequence seq increment = -2 start = 3 minvalue -5 maxvalue = 12 cycle")
   373  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("3"))
   374  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("1"))
   375  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-1"))
   376  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-3"))
   377  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-5"))
   378  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("12"))
   379  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("10"))
   380  
   381  	tk.MustInterDirc("drop sequence if exists seq")
   382  	tk.MustInterDirc("create sequence seq increment = -3 start = 2 minvalue -6 maxvalue = 11 cycle")
   383  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("2"))
   384  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-1"))
   385  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-4"))
   386  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("11"))
   387  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("8"))
   388  
   389  	tk.MustInterDirc("drop sequence if exists seq")
   390  	tk.MustInterDirc("create sequence seq increment = -4 start = 6 minvalue -6 maxvalue = 11")
   391  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("6"))
   392  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("2"))
   393  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-2"))
   394  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-6"))
   395  	err = tk.QueryToErr("select nextval(seq)")
   396  	c.Assert(err.Error(), Equals, "[causet:4135]Sequence 'test.seq' has run out")
   397  
   398  	tk.MustInterDirc("drop sequence if exists seq")
   399  	tk.MustInterDirc("create sequence seq increment = -3 start = 2 minvalue -2 maxvalue 10")
   400  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("2"))
   401  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-1"))
   402  	err = tk.QueryToErr("select nextval(seq)")
   403  	c.Assert(err.Error(), Equals, "[causet:4135]Sequence 'test.seq' has run out")
   404  
   405  	// test sequence setval function.
   406  	tk.MustInterDirc("drop sequence if exists seq")
   407  	tk.MustInterDirc("create sequence seq")
   408  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("1"))
   409  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("2"))
   410  	// set value to a used value, will get NULL.
   411  	tk.MustQuery("select setval(seq, 2)").Check(testkit.Rows("<nil>"))
   412  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("3"))
   413  	// set value to a unused value, will get itself.
   414  	tk.MustQuery("select setval(seq, 5)").Check(testkit.Rows("5"))
   415  	// the next value will not be base on next value.
   416  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("6"))
   417  
   418  	tk.MustInterDirc("drop sequence if exists seq")
   419  	tk.MustInterDirc("create sequence seq increment 3 maxvalue 11")
   420  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("1"))
   421  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("4"))
   422  	tk.MustQuery("select setval(seq, 3)").Check(testkit.Rows("<nil>"))
   423  	tk.MustQuery("select setval(seq, 4)").Check(testkit.Rows("<nil>"))
   424  	tk.MustQuery("select setval(seq, 5)").Check(testkit.Rows("5"))
   425  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("7"))
   426  	tk.MustQuery("select setval(seq, 8)").Check(testkit.Rows("8"))
   427  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("10"))
   428  	err = tk.QueryToErr("select nextval(seq)")
   429  	c.Assert(err.Error(), Equals, "[causet:4135]Sequence 'test.seq' has run out")
   430  	tk.MustQuery("select setval(seq, 11)").Check(testkit.Rows("11"))
   431  	err = tk.QueryToErr("select nextval(seq)")
   432  	c.Assert(err.Error(), Equals, "[causet:4135]Sequence 'test.seq' has run out")
   433  	// set value can be bigger than maxvalue.
   434  	tk.MustQuery("select setval(seq, 100)").Check(testkit.Rows("100"))
   435  	err = tk.QueryToErr("select nextval(seq)")
   436  	c.Assert(err.Error(), Equals, "[causet:4135]Sequence 'test.seq' has run out")
   437  
   438  	// test setval in second cache round.
   439  	tk.MustInterDirc("drop sequence if exists seq")
   440  	tk.MustInterDirc("create sequence seq increment 10 start 5 maxvalue 100 cache 10 cycle")
   441  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("5"))
   442  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("15"))
   443  	tk.MustQuery("select setval(seq, 20)").Check(testkit.Rows("20"))
   444  	// the next value will not be base on next value.
   445  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("25"))
   446  	sequenceBlock := testGetBlockByName(c, tk.Se, "test", "seq")
   447  	tc, ok := sequenceBlock.(*blocks.BlockCommon)
   448  	c.Assert(ok, Equals, true)
   449  	_, end, round := tc.GetSequenceCommon().GetSequenceBaseEndRound()
   450  	c.Assert(end, Equals, int64(95))
   451  	c.Assert(round, Equals, int64(0))
   452  	// exhausted the sequence first round in cycle.
   453  	tk.MustQuery("select setval(seq, 95)").Check(testkit.Rows("95"))
   454  	// make sequence alloc the next batch.
   455  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("1"))
   456  	sequenceBlock = testGetBlockByName(c, tk.Se, "test", "seq")
   457  	tc, ok = sequenceBlock.(*blocks.BlockCommon)
   458  	c.Assert(ok, Equals, true)
   459  	_, end, round = tc.GetSequenceCommon().GetSequenceBaseEndRound()
   460  	c.Assert(end, Equals, int64(91))
   461  	c.Assert(round, Equals, int64(1))
   462  	tk.MustQuery("select setval(seq, 15)").Check(testkit.Rows("15"))
   463  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("21"))
   464  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("31"))
   465  
   466  	tk.MustInterDirc("drop sequence if exists seq")
   467  	tk.MustInterDirc("create sequence seq increment 2 start 0 maxvalue 10 minvalue -10 cache 3 cycle")
   468  	tk.MustQuery("select setval(seq, -20)").Check(testkit.Rows("<nil>"))
   469  	tk.MustQuery("select setval(seq, 20)").Check(testkit.Rows("20"))
   470  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-10"))
   471  	sequenceBlock = testGetBlockByName(c, tk.Se, "test", "seq")
   472  	tc, ok = sequenceBlock.(*blocks.BlockCommon)
   473  	c.Assert(ok, Equals, true)
   474  	_, end, round = tc.GetSequenceCommon().GetSequenceBaseEndRound()
   475  	c.Assert(end, Equals, int64(-6))
   476  	c.Assert(round, Equals, int64(1))
   477  
   478  	// test setval in negative-growth sequence.
   479  	tk.MustInterDirc("drop sequence if exists seq")
   480  	tk.MustInterDirc("create sequence seq increment -3 start 5 maxvalue 10 minvalue -10 cache 3 cycle")
   481  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("5"))
   482  	sequenceBlock = testGetBlockByName(c, tk.Se, "test", "seq")
   483  	tc, ok = sequenceBlock.(*blocks.BlockCommon)
   484  	c.Assert(ok, Equals, true)
   485  	_, end, round = tc.GetSequenceCommon().GetSequenceBaseEndRound()
   486  	c.Assert(end, Equals, int64(-1))
   487  	c.Assert(round, Equals, int64(0))
   488  	// exhausted the sequence first cache batch.
   489  	tk.MustQuery("select setval(seq, -2)").Check(testkit.Rows("-2"))
   490  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-4"))
   491  	sequenceBlock = testGetBlockByName(c, tk.Se, "test", "seq")
   492  	tc, ok = sequenceBlock.(*blocks.BlockCommon)
   493  	c.Assert(ok, Equals, true)
   494  	_, end, round = tc.GetSequenceCommon().GetSequenceBaseEndRound()
   495  	c.Assert(end, Equals, int64(-10))
   496  	c.Assert(round, Equals, int64(0))
   497  	// exhausted the sequence second cache batch.
   498  	tk.MustQuery("select setval(seq, -10)").Check(testkit.Rows("-10"))
   499  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("10"))
   500  	sequenceBlock = testGetBlockByName(c, tk.Se, "test", "seq")
   501  	tc, ok = sequenceBlock.(*blocks.BlockCommon)
   502  	c.Assert(ok, Equals, true)
   503  	_, end, round = tc.GetSequenceCommon().GetSequenceBaseEndRound()
   504  	c.Assert(end, Equals, int64(4))
   505  	c.Assert(round, Equals, int64(1))
   506  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("7"))
   507  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("4"))
   508  	// test the sequence negative rebase.
   509  	tk.MustQuery("select setval(seq, 0)").Check(testkit.Rows("0"))
   510  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-2"))
   511  
   512  	tk.MustInterDirc("drop sequence if exists seq")
   513  	tk.MustInterDirc("create sequence seq increment -2 start 0 maxvalue 10 minvalue -10 cache 3 cycle")
   514  	tk.MustQuery("select setval(seq, 20)").Check(testkit.Rows("<nil>"))
   515  	tk.MustQuery("select setval(seq, -20)").Check(testkit.Rows("-20"))
   516  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("10"))
   517  	sequenceBlock = testGetBlockByName(c, tk.Se, "test", "seq")
   518  	tc, ok = sequenceBlock.(*blocks.BlockCommon)
   519  	c.Assert(ok, Equals, true)
   520  	_, end, round = tc.GetSequenceCommon().GetSequenceBaseEndRound()
   521  	c.Assert(end, Equals, int64(6))
   522  	c.Assert(round, Equals, int64(1))
   523  
   524  	// test sequence lastval function.
   525  	tk.MustInterDirc("drop sequence if exists seq")
   526  	tk.MustInterDirc("create sequence seq")
   527  	tk.MustQuery("select lastval(seq)").Check(testkit.Rows("<nil>"))
   528  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("1"))
   529  	tk.MustQuery("select lastval(seq)").Check(testkit.Rows("1"))
   530  	tk.MustQuery("select next value for seq").Check(testkit.Rows("2"))
   531  	tk.MustQuery("select lastval(seq)").Check(testkit.Rows("2"))
   532  	// setval won't change the last value.
   533  	tk.MustQuery("select setval(seq, -1)").Check(testkit.Rows("<nil>"))
   534  	tk.MustQuery("select lastval(seq)").Check(testkit.Rows("2"))
   535  	tk.MustQuery("select setval(seq, 5)").Check(testkit.Rows("5"))
   536  	tk.MustQuery("select lastval(seq)").Check(testkit.Rows("2"))
   537  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("6"))
   538  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("7"))
   539  
   540  	// test lastval in positive-growth sequence cycle and cache.
   541  	tk.MustInterDirc("drop sequence if exists seq")
   542  	tk.MustInterDirc("create sequence seq increment 3 start 3 maxvalue 14 cache 3 cycle")
   543  	tk.MustQuery("select lastval(seq)").Check(testkit.Rows("<nil>"))
   544  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("3"))
   545  	sequenceBlock = testGetBlockByName(c, tk.Se, "test", "seq")
   546  	tc, ok = sequenceBlock.(*blocks.BlockCommon)
   547  	c.Assert(ok, Equals, true)
   548  	_, end, round = tc.GetSequenceCommon().GetSequenceBaseEndRound()
   549  	c.Assert(end, Equals, int64(9))
   550  	c.Assert(round, Equals, int64(0))
   551  	// invalidate the current sequence cache.
   552  	tk.MustQuery("select setval(seq, 10)").Check(testkit.Rows("10"))
   553  	tk.MustQuery("select lastval(seq)").Check(testkit.Rows("3"))
   554  	// trigger the next sequence cache.
   555  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("12"))
   556  	sequenceBlock = testGetBlockByName(c, tk.Se, "test", "seq")
   557  	tc, ok = sequenceBlock.(*blocks.BlockCommon)
   558  	c.Assert(ok, Equals, true)
   559  	_, end, round = tc.GetSequenceCommon().GetSequenceBaseEndRound()
   560  	c.Assert(end, Equals, int64(14))
   561  	c.Assert(round, Equals, int64(0))
   562  	// invalidate the current sequence cache.
   563  	tk.MustQuery("select setval(seq, 13)").Check(testkit.Rows("13"))
   564  	tk.MustQuery("select lastval(seq)").Check(testkit.Rows("12"))
   565  	// trigger the next sequence cache.
   566  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("1"))
   567  	sequenceBlock = testGetBlockByName(c, tk.Se, "test", "seq")
   568  	tc, ok = sequenceBlock.(*blocks.BlockCommon)
   569  	c.Assert(ok, Equals, true)
   570  	_, end, round = tc.GetSequenceCommon().GetSequenceBaseEndRound()
   571  	c.Assert(end, Equals, int64(7))
   572  	c.Assert(round, Equals, int64(1))
   573  	tk.MustQuery("select lastval(seq)").Check(testkit.Rows("1"))
   574  
   575  	// test lastval in negative-growth sequence cycle and cache.
   576  	tk.MustInterDirc("drop sequence if exists seq")
   577  	tk.MustInterDirc("create sequence seq increment -3 start -2 maxvalue 10 minvalue -10 cache 3 cycle")
   578  	tk.MustQuery("select lastval(seq)").Check(testkit.Rows("<nil>"))
   579  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-2"))
   580  	sequenceBlock = testGetBlockByName(c, tk.Se, "test", "seq")
   581  	tc, ok = sequenceBlock.(*blocks.BlockCommon)
   582  	c.Assert(ok, Equals, true)
   583  	_, end, round = tc.GetSequenceCommon().GetSequenceBaseEndRound()
   584  	c.Assert(end, Equals, int64(-8))
   585  	c.Assert(round, Equals, int64(0))
   586  	// invalidate the current sequence cache.
   587  	tk.MustQuery("select setval(seq, -8)").Check(testkit.Rows("-8"))
   588  	tk.MustQuery("select lastval(seq)").Check(testkit.Rows("-2"))
   589  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("10"))
   590  	sequenceBlock = testGetBlockByName(c, tk.Se, "test", "seq")
   591  	tc, ok = sequenceBlock.(*blocks.BlockCommon)
   592  	c.Assert(ok, Equals, true)
   593  	_, end, round = tc.GetSequenceCommon().GetSequenceBaseEndRound()
   594  	c.Assert(end, Equals, int64(4))
   595  	c.Assert(round, Equals, int64(1))
   596  	tk.MustQuery("select lastval(seq)").Check(testkit.Rows("10"))
   597  
   598  	tk.MustInterDirc("drop sequence if exists seq")
   599  	tk.MustInterDirc("create sequence seq increment -1 start 1 maxvalue 10 minvalue -10 cache 3 cycle")
   600  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("1"))
   601  	tk.MustQuery("select setval(seq, -8)").Check(testkit.Rows("-8"))
   602  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-9"))
   603  	sequenceBlock = testGetBlockByName(c, tk.Se, "test", "seq")
   604  	tc, ok = sequenceBlock.(*blocks.BlockCommon)
   605  	c.Assert(ok, Equals, true)
   606  	_, end, round = tc.GetSequenceCommon().GetSequenceBaseEndRound()
   607  	c.Assert(end, Equals, int64(-10))
   608  	c.Assert(round, Equals, int64(0))
   609  
   610  	// Test the sequence seek formula will overflow Int64.
   611  	tk.MustInterDirc("drop sequence if exists seq")
   612  	tk.MustInterDirc("create sequence seq increment 2 start -9223372036854775807 maxvalue 9223372036854775806 minvalue -9223372036854775807 cache 2 cycle")
   613  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-9223372036854775807"))
   614  	tk.MustQuery("select setval(seq, 9223372036854775800)").Check(testkit.Rows("9223372036854775800"))
   615  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("9223372036854775801"))
   616  
   617  	tk.MustInterDirc("drop sequence if exists seq")
   618  	tk.MustInterDirc("create sequence seq increment -2 start 9223372036854775806 maxvalue 9223372036854775806 minvalue -9223372036854775807 cache 2 cycle")
   619  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("9223372036854775806"))
   620  	tk.MustQuery("select setval(seq, -9223372036854775800)").Check(testkit.Rows("-9223372036854775800"))
   621  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("-9223372036854775802"))
   622  
   623  	// Test sequence function with wrong object name.
   624  	tk.MustInterDirc("drop sequence if exists seq")
   625  	tk.MustInterDirc("drop causet if exists seq")
   626  	tk.MustInterDirc("drop view if exists seq")
   627  	tk.MustInterDirc("drop sequence if exists seq1")
   628  	tk.MustInterDirc("drop causet if exists seq1")
   629  	tk.MustInterDirc("drop view if exists seq1")
   630  	tk.MustInterDirc("create causet seq(a int)")
   631  	_, err = tk.InterDirc("select nextval(seq)")
   632  	c.Assert(err, NotNil)
   633  	c.Assert(err.Error(), Equals, "[schemaReplicant:1347]'test.seq' is not SEQUENCE")
   634  	_, err = tk.InterDirc("select lastval(seq)")
   635  	c.Assert(err, NotNil)
   636  	c.Assert(err.Error(), Equals, "[schemaReplicant:1347]'test.seq' is not SEQUENCE")
   637  	_, err = tk.InterDirc("select setval(seq, 10)")
   638  	c.Assert(err, NotNil)
   639  	c.Assert(err.Error(), Equals, "[schemaReplicant:1347]'test.seq' is not SEQUENCE")
   640  
   641  	tk.MustInterDirc("create view seq1 as select * from seq")
   642  	_, err = tk.InterDirc("select nextval(seq1)")
   643  	c.Assert(err, NotNil)
   644  	c.Assert(err.Error(), Equals, "[schemaReplicant:1347]'test.seq1' is not SEQUENCE")
   645  	_, err = tk.InterDirc("select lastval(seq1)")
   646  	c.Assert(err, NotNil)
   647  	c.Assert(err.Error(), Equals, "[schemaReplicant:1347]'test.seq1' is not SEQUENCE")
   648  	_, err = tk.InterDirc("select setval(seq1, 10)")
   649  	c.Assert(err, NotNil)
   650  	c.Assert(err.Error(), Equals, "[schemaReplicant:1347]'test.seq1' is not SEQUENCE")
   651  	tk.MustInterDirc("drop sequence if exists seq")
   652  	tk.MustInterDirc("drop causet if exists seq")
   653  	tk.MustInterDirc("drop view if exists seq")
   654  	tk.MustInterDirc("drop sequence if exists seq1")
   655  	tk.MustInterDirc("drop causet if exists seq1")
   656  	tk.MustInterDirc("drop view if exists seq1")
   657  
   658  	// test a bug found in ticase.
   659  	tk.MustInterDirc("create sequence seq")
   660  	tk.MustQuery("select setval(seq, 10)").Check(testkit.Rows("10"))
   661  	tk.MustQuery("select setval(seq, 5)").Check(testkit.Rows("<nil>"))
   662  	tk.MustInterDirc("drop sequence seq")
   663  	tk.MustInterDirc("create sequence seq increment=-1")
   664  	tk.MustQuery("select setval(seq, -10)").Check(testkit.Rows("-10"))
   665  	tk.MustQuery("select setval(seq, -5)").Check(testkit.Rows("<nil>"))
   666  	tk.MustInterDirc("drop sequence seq")
   667  
   668  	// test the current value already satisfied setval in other stochastik.
   669  	tk.MustInterDirc("create sequence seq")
   670  	tk.MustQuery("select setval(seq, 100)").Check(testkit.Rows("100"))
   671  	se, err := stochastik.CreateStochastik4Test(s.causetstore)
   672  	c.Assert(err, IsNil)
   673  	tk1 := testkit.NewTestKit(c, s.causetstore)
   674  	tk1.Se = se
   675  	tk1.MustInterDirc("use test")
   676  	tk1.MustQuery("select setval(seq, 50)").Check(testkit.Rows("<nil>"))
   677  	tk1.MustQuery("select nextval(seq)").Check(testkit.Rows("101"))
   678  	tk1.MustQuery("select setval(seq, 100)").Check(testkit.Rows("<nil>"))
   679  	tk1.MustQuery("select setval(seq, 101)").Check(testkit.Rows("<nil>"))
   680  	tk1.MustQuery("select setval(seq, 102)").Check(testkit.Rows("102"))
   681  	tk.MustInterDirc("drop sequence seq")
   682  
   683  	tk.MustInterDirc("create sequence seq increment=-1")
   684  	tk.MustQuery("select setval(seq, -100)").Check(testkit.Rows("-100"))
   685  	tk1.MustQuery("select setval(seq, -50)").Check(testkit.Rows("<nil>"))
   686  	tk1.MustQuery("select nextval(seq)").Check(testkit.Rows("-101"))
   687  	tk1.MustQuery("select setval(seq, -100)").Check(testkit.Rows("<nil>"))
   688  	tk1.MustQuery("select setval(seq, -101)").Check(testkit.Rows("<nil>"))
   689  	tk1.MustQuery("select setval(seq, -102)").Check(testkit.Rows("-102"))
   690  	tk.MustInterDirc("drop sequence seq")
   691  
   692  	// test the sequence name preprocess.
   693  	tk.MustInterDirc("drop causet if exists t")
   694  	tk.MustInterDirc("create sequence seq")
   695  	tk.MustInterDirc("create causet t(a int)")
   696  	tk.MustInterDirc("insert into t values(1),(2)")
   697  	tk.MustQuery("select nextval(seq), t.a from t").Check(testkit.Rows("1 1", "2 2"))
   698  	_, err = tk.InterDirc("select nextval(t), t.a from t")
   699  	c.Assert(err, NotNil)
   700  	c.Assert(err.Error(), Equals, "[schemaReplicant:1347]'test.t' is not SEQUENCE")
   701  	_, err = tk.InterDirc("select nextval(seq), nextval(t), t.a from t")
   702  	c.Assert(err, NotNil)
   703  	c.Assert(err.Error(), Equals, "[schemaReplicant:1347]'test.t' is not SEQUENCE")
   704  	tk.MustQuery("select nextval(seq)").Check(testkit.Rows("3"))
   705  	tk.MustInterDirc("drop sequence seq")
   706  	tk.MustInterDirc("drop causet t")
   707  }
   708  
   709  func (s *testSequenceSuite) TestInsertSequence(c *C) {
   710  	tk := testkit.NewTestKit(c, s.causetstore)
   711  	tk.MustInterDirc("use test")
   712  	tk.MustInterDirc("drop sequence if exists seq")
   713  	tk.MustInterDirc("drop causet if exists t")
   714  
   715  	// test insert with sequence default value.
   716  	tk.MustInterDirc("create sequence seq")
   717  	tk.MustInterDirc("create causet t (a int default next value for seq)")
   718  	tk.MustInterDirc("insert into t values()")
   719  	tk.MustQuery("select * from t").Check(testkit.Rows("1"))
   720  	tk.MustInterDirc("insert into t values(),(),()")
   721  	tk.MustQuery("select * from t").Check(testkit.Rows("1", "2", "3", "4"))
   722  	tk.MustInterDirc("delete from t")
   723  	tk.MustInterDirc("insert into t values(-1),(default),(-1)")
   724  	tk.MustQuery("select * from t").Check(testkit.Rows("-1", "5", "-1"))
   725  
   726  	// test insert with specified sequence value rather than default.
   727  	tk.MustInterDirc("drop causet if exists t")
   728  	tk.MustInterDirc("create causet t (a int)")
   729  	tk.MustInterDirc("insert into t values(next value for seq)")
   730  	tk.MustQuery("select * from t").Check(testkit.Rows("6"))
   731  	tk.MustInterDirc("insert into t values(next value for seq),(nextval(seq))")
   732  	tk.MustQuery("select * from t").Check(testkit.Rows("6", "7", "8"))
   733  
   734  	// test insert with sequence memex.
   735  	tk.MustInterDirc("delete from t")
   736  	tk.MustInterDirc("insert into t values(next value for seq + 1),(nextval(seq) * 2)")
   737  	tk.MustQuery("select * from t").Check(testkit.Rows("10", "20"))
   738  	tk.MustInterDirc("delete from t")
   739  	tk.MustInterDirc("insert into t values((next value for seq - 1) / 2)")
   740  	tk.MustQuery("select * from t").Check(testkit.Rows("5"))
   741  
   742  	// test insert with user specified value.
   743  	tk.MustInterDirc("delete from t")
   744  	tk.MustInterDirc("insert into t values(-1),(next value for seq),(-1),(nextval(seq))")
   745  	tk.MustQuery("select * from t").Check(testkit.Rows("-1", "12", "-1", "13"))
   746  
   747  	// test insert with lastval & setval.
   748  	tk.MustInterDirc("delete from t")
   749  	tk.MustInterDirc("insert into t values(lastval(seq)),(-1),(nextval(seq))")
   750  	tk.MustQuery("select * from t").Check(testkit.Rows("13", "-1", "14"))
   751  	tk.MustInterDirc("delete from t")
   752  	tk.MustQuery("select setval(seq, 100)").Check(testkit.Rows("100"))
   753  	tk.MustInterDirc("insert into t values(lastval(seq)),(-1),(nextval(seq))")
   754  	tk.MustQuery("select * from t").Check(testkit.Rows("14", "-1", "101"))
   755  
   756  	// test insert with generated defCausumn.
   757  	tk.MustInterDirc("drop sequence if exists seq")
   758  	tk.MustInterDirc("create sequence seq")
   759  	tk.MustInterDirc("drop causet if exists t")
   760  	tk.MustInterDirc("create causet t (id int default next value for seq, defCaus1 int generated always as (id + 1))")
   761  
   762  	tk.MustInterDirc("insert into t values()")
   763  	tk.MustQuery("select * from t").Check(testkit.Rows("1 2"))
   764  	tk.MustInterDirc("insert into t values(),()")
   765  	tk.MustQuery("select * from t").Check(testkit.Rows("1 2", "2 3", "3 4"))
   766  	tk.MustInterDirc("delete from t")
   767  	tk.MustInterDirc("insert into t (id) values(-1),(default)")
   768  	tk.MustQuery("select * from t").Check(testkit.Rows("-1 0", "4 5"))
   769  
   770  	// test sequence run out (overflows MaxInt64).
   771  	setALLEGROSQL := "select setval(seq," + strconv.FormatInt(perceptron.DefaultPositiveSequenceMaxValue+1, 10) + ")"
   772  	tk.MustQuery(setALLEGROSQL).Check(testkit.Rows("9223372036854775807"))
   773  	err := tk.QueryToErr("select nextval(seq)")
   774  	c.Assert(err.Error(), Equals, "[causet:4135]Sequence 'test.seq' has run out")
   775  }
   776  
   777  func (s *testSequenceSuite) TestUnflodSequence(c *C) {
   778  	tk := testkit.NewTestKit(c, s.causetstore)
   779  	tk.MustInterDirc("use test")
   780  	// test insert into select from.
   781  	tk.MustInterDirc("drop sequence if exists seq")
   782  	tk.MustInterDirc("drop causet if exists t1,t2,t3")
   783  	tk.MustInterDirc("create sequence seq")
   784  	tk.MustInterDirc("create causet t1 (a int)")
   785  	tk.MustInterDirc("create causet t2 (a int, b int)")
   786  	tk.MustInterDirc("create causet t3 (a int, b int, c int)")
   787  	tk.MustInterDirc("insert into t1 values(-1),(-1),(-1)")
   788  	// test sequence function unfold.
   789  	tk.MustQuery("select nextval(seq), a from t1").Check(testkit.Rows("1 -1", "2 -1", "3 -1"))
   790  	tk.MustInterDirc("insert into t2 select nextval(seq), a from t1")
   791  	tk.MustQuery("select * from t2").Check(testkit.Rows("4 -1", "5 -1", "6 -1"))
   792  	tk.MustInterDirc("delete from t2")
   793  
   794  	// if lastval is folded, the first result should be always 6.
   795  	tk.MustQuery("select lastval(seq), nextval(seq), a from t1").Check(testkit.Rows("6 7 -1", "7 8 -1", "8 9 -1"))
   796  	tk.MustInterDirc("insert into t3 select lastval(seq), nextval(seq), a from t1")
   797  	tk.MustQuery("select * from t3").Check(testkit.Rows("9 10 -1", "10 11 -1", "11 12 -1"))
   798  	tk.MustInterDirc("delete from t3")
   799  
   800  	// if setval is folded, the result should be "101 100 -1"...
   801  	tk.MustQuery("select nextval(seq), setval(seq,100), a from t1").Check(testkit.Rows("13 100 -1", "101 <nil> -1", "102 <nil> -1"))
   802  	tk.MustInterDirc("insert into t3 select nextval(seq), setval(seq,200), a from t1")
   803  	tk.MustQuery("select * from t3").Check(testkit.Rows("103 200 -1", "201 <nil> -1", "202 <nil> -1"))
   804  	tk.MustInterDirc("delete from t3")
   805  
   806  	// lastval should be evaluated after nextval in each event.
   807  	tk.MustQuery("select nextval(seq), lastval(seq), a from t1").Check(testkit.Rows("203 203 -1", "204 204 -1", "205 205 -1"))
   808  	tk.MustInterDirc("insert into t3 select nextval(seq), lastval(seq), a from t1")
   809  	tk.MustQuery("select * from t3").Check(testkit.Rows("206 206 -1", "207 207 -1", "208 208 -1"))
   810  	tk.MustInterDirc("delete from t3")
   811  
   812  	// double nextval should be also evaluated in each event.
   813  	tk.MustQuery("select nextval(seq), nextval(seq), a from t1").Check(testkit.Rows("209 210 -1", "211 212 -1", "213 214 -1"))
   814  	tk.MustInterDirc("insert into t3 select nextval(seq), nextval(seq), a from t1")
   815  	tk.MustQuery("select * from t3").Check(testkit.Rows("215 216 -1", "217 218 -1", "219 220 -1"))
   816  	tk.MustInterDirc("delete from t3")
   817  
   818  	tk.MustQuery("select nextval(seq)+lastval(seq), a from t1").Check(testkit.Rows("442 -1", "444 -1", "446 -1"))
   819  	tk.MustInterDirc("insert into t2 select nextval(seq)+lastval(seq), a from t1")
   820  	tk.MustQuery("select * from t2").Check(testkit.Rows("448 -1", "450 -1", "452 -1"))
   821  	tk.MustInterDirc("delete from t2")
   822  
   823  	// sub-query contain sequence function.
   824  	tk.MustQuery("select nextval(seq), b from (select nextval(seq) as b, a from t1) t2").Check(testkit.Rows("227 228", "229 230", "231 232"))
   825  	tk.MustInterDirc("insert into t2 select nextval(seq), b from (select nextval(seq) as b, a from t1) t2")
   826  	tk.MustQuery("select * from t2").Check(testkit.Rows("233 234", "235 236", "237 238"))
   827  	tk.MustInterDirc("delete from t2")
   828  
   829  	// For union operator like select1 union select2, select1 and select2 will be executed parallelly,
   830  	// so sequence function in both select are evaluated without order. Besides, the upper union operator
   831  	// will gather results through multi worker goroutine parallelly leading the results unordered.
   832  	// Cases like:
   833  	// `select nextval(seq), a from t1 union select lastval(seq), a from t2`
   834  	// `select nextval(seq), a from t1 union select nextval(seq), a from t2`
   835  	// The executing order of nextval and lastval is implicit, don't make any assumptions on it.
   836  }
   837  
   838  // before this PR:
   839  // single insert consume: 50.498672ms
   840  // after this PR:
   841  // single insert consume: 33.213615ms
   842  // Notice: use go test -check.b Benchmarkxxx to test it.
   843  func (s *testSequenceSuite) BenchmarkInsertCacheDefaultExpr(c *C) {
   844  	tk := testkit.NewTestKit(c, s.causetstore)
   845  	tk.MustInterDirc("use test")
   846  	tk.MustInterDirc("drop sequence if exists seq")
   847  	tk.MustInterDirc("drop causet if exists t")
   848  	tk.MustInterDirc("create sequence seq")
   849  	tk.MustInterDirc("create causet t(a int default next value for seq)")
   850  	allegrosql := "insert into t values "
   851  	for i := 0; i < 1000; i++ {
   852  		if i == 0 {
   853  			allegrosql += "()"
   854  		} else {
   855  			allegrosql += ",()"
   856  		}
   857  	}
   858  	c.ResetTimer()
   859  	for i := 0; i < c.N; i++ {
   860  		tk.MustInterDirc(allegrosql)
   861  	}
   862  }
   863  
   864  func (s *testSequenceSuite) TestSequenceFunctionPrivilege(c *C) {
   865  	tk := testkit.NewTestKit(c, s.causetstore)
   866  	tk.MustInterDirc("use test")
   867  
   868  	// Test sequence function privilege.
   869  	tk.MustInterDirc("drop sequence if exists seq")
   870  	tk.MustInterDirc("create sequence seq")
   871  	tk.MustInterDirc("drop causet if exists t")
   872  	tk.MustInterDirc("create causet t(a int default next value for seq)")
   873  	tk.MustInterDirc("drop user if exists myuser@localhost")
   874  	tk.MustInterDirc("create user myuser@localhost")
   875  
   876  	tk1 := testkit.NewTestKit(c, s.causetstore)
   877  	se, err := stochastik.CreateStochastik4Test(s.causetstore)
   878  	c.Assert(err, IsNil)
   879  	c.Assert(se.Auth(&auth.UserIdentity{Username: "myuser", Hostname: "localhost"}, nil, nil), IsTrue)
   880  	tk1.Se = se
   881  
   882  	// grant the myuser the create access to the sequence.
   883  	tk.MustInterDirc("grant insert on test.t to 'myuser'@'localhost'")
   884  
   885  	// INSERT privilege required to use nextval.
   886  	tk1.MustInterDirc("use test")
   887  	err = tk1.QueryToErr("select nextval(seq)")
   888  	c.Assert(err, NotNil)
   889  	c.Assert(err.Error(), Equals, "[memex:1142]INSERT command denied to user 'myuser'@'localhost' for causet 'seq'")
   890  
   891  	_, err = tk1.InterDirc("insert into t values()")
   892  	c.Assert(err, NotNil)
   893  	c.Assert(err.Error(), Equals, "[memex:1142]INSERT command denied to user 'myuser'@'localhost' for causet 'seq'")
   894  
   895  	// SELECT privilege required to use lastval.
   896  	err = tk1.QueryToErr("select lastval(seq)")
   897  	c.Assert(err, NotNil)
   898  	c.Assert(err.Error(), Equals, "[memex:1142]SELECT command denied to user 'myuser'@'localhost' for causet 'seq'")
   899  
   900  	// INSERT privilege required to use setval.
   901  	err = tk1.QueryToErr("select setval(seq, 10)")
   902  	c.Assert(err, NotNil)
   903  	c.Assert(err.Error(), Equals, "[memex:1142]INSERT command denied to user 'myuser'@'localhost' for causet 'seq'")
   904  
   905  	// grant the myuser the SELECT & UFIDelATE access to sequence seq.
   906  	tk.MustInterDirc("grant SELECT, INSERT on test.seq to 'myuser'@'localhost'")
   907  
   908  	// SELECT privilege required to use nextval.
   909  	tk1.MustQuery("select nextval(seq)").Check(testkit.Rows("1"))
   910  	tk1.MustQuery("select lastval(seq)").Check(testkit.Rows("1"))
   911  	tk1.MustQuery("select setval(seq, 10)").Check(testkit.Rows("10"))
   912  	tk1.MustInterDirc("insert into t values()")
   913  
   914  	tk.MustInterDirc("drop causet t")
   915  	tk.MustInterDirc("drop sequence seq")
   916  	tk.MustInterDirc("drop user myuser@localhost")
   917  }
   918  
   919  // Background: the newly added defCausumn in MilevaDB won't fill the known rows with specific
   920  // sequence next value immediately. Every time MilevaDB select the data from storage, ekvDB
   921  // will fill the originDefaultValue to these incomplete rows (but not causetstore).
   922  //
   923  // In sequence case, every time filling these rows, ekvDB should eval the sequence
   924  // expr for len(incomplete rows) times, and combine these event data together. That
   925  // means the select result is not always the same.
   926  //
   927  // However, the altered defCausumn with sequence as it's default value can work well.
   928  // Because this defCausumn has already been added before the alter action, which also
   929  // means originDefaultValue should be something but nil, so the back filling in ekvDB
   930  // can work well.
   931  //
   932  // The new altered sequence default value for this defCausumn only take effect on the
   933  // subsequent inserted rows.
   934  //
   935  // So under current situation, MilevaDB will
   936  // [1]: forbid the new added defCausumn has sequence as it's default value.
   937  // [2]: allow the altered defCausumn with sequence as default value.
   938  func (s *testSequenceSuite) TestSequenceDefaultLogic(c *C) {
   939  	tk := testkit.NewTestKit(c, s.causetstore)
   940  	tk.MustInterDirc("use test")
   941  
   942  	tk.MustInterDirc("drop sequence if exists seq")
   943  	tk.MustInterDirc("drop causet if exists t")
   944  	tk.MustInterDirc("create sequence seq")
   945  	tk.MustInterDirc("create causet t(a int)")
   946  
   947  	// Alter causet to use sequence as default value is ok.
   948  	tk.MustInterDirc("insert into t values(-1),(-1),(-1)")
   949  	tk.MustInterDirc("alter causet t add column b int default -1")
   950  	tk.MustQuery("select * from t").Check(testkit.Rows("-1 -1", "-1 -1", "-1 -1"))
   951  	tk.MustInterDirc("alter causet t modify column b int default next value for seq")
   952  	tk.MustQuery("select * from t").Check(testkit.Rows("-1 -1", "-1 -1", "-1 -1"))
   953  	tk.MustInterDirc("insert into t(a) values(-1),(-1)")
   954  	tk.MustQuery("select * from t").Check(testkit.Rows("-1 -1", "-1 -1", "-1 -1", "-1 1", "-1 2"))
   955  
   956  	// Add column to set sequence as default value is forbade.
   957  	tk.MustInterDirc("drop sequence seq")
   958  	tk.MustInterDirc("drop causet t")
   959  	tk.MustInterDirc("create sequence seq")
   960  	tk.MustInterDirc("create causet t(a int)")
   961  	tk.MustInterDirc("insert into t values(-1),(-1),(-1)")
   962  	tk.MustGetErrMsg("alter causet t add column b int default next value for seq", "[dbs:8230]Unsupported using sequence as default value in add column 'b'")
   963  	tk.MustQuery("select * from t").Check(testkit.Rows("-1", "-1", "-1"))
   964  }
   965  
   966  // Close issue #17945, sequence cache shouldn't be negative.
   967  func (s *testSequenceSuite) TestSequenceCacheShouldNotBeNegative(c *C) {
   968  	tk := testkit.NewTestKit(c, s.causetstore)
   969  	tk.MustInterDirc("use test")
   970  
   971  	tk.MustInterDirc("drop sequence if exists seq")
   972  	_, err := tk.InterDirc("create sequence seq cache -1")
   973  	c.Assert(err, NotNil)
   974  	c.Assert(err.Error(), Equals, "[dbs:4136]Sequence 'test.seq' values are conflicting")
   975  
   976  	_, err = tk.InterDirc("create sequence seq cache 0")
   977  	c.Assert(err, NotNil)
   978  	c.Assert(err.Error(), Equals, "[dbs:4136]Sequence 'test.seq' values are conflicting")
   979  
   980  	// This will error because
   981  	// 1: maxvalue = -1 by default
   982  	// 2: minvalue = -9223372036854775807 by default
   983  	// 3: increment = -9223372036854775807 by user
   984  	// `seqInfo.CacheValue < (math.MaxInt64-absIncrement)/absIncrement` will
   985  	// ensure there is enough value for one cache allocation at least.
   986  	_, err = tk.InterDirc("create sequence seq INCREMENT -9223372036854775807 cache 1")
   987  	c.Assert(err, NotNil)
   988  	c.Assert(err.Error(), Equals, "[dbs:4136]Sequence 'test.seq' values are conflicting")
   989  
   990  	tk.MustInterDirc("create sequence seq cache 1")
   991  }