github.com/whtcorpsinc/MilevaDB-Prod@v0.0.0-20211104133533-f57f4be3b597/interlock/insert_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  	"fmt"
    18  	"math"
    19  	"strconv"
    20  	"strings"
    21  	"sync"
    22  
    23  	"github.com/whtcorpsinc/BerolinaSQL/terror"
    24  	. "github.com/whtcorpsinc/check"
    25  	"github.com/whtcorpsinc/milevadb/causet"
    26  	"github.com/whtcorpsinc/milevadb/errno"
    27  	"github.com/whtcorpsinc/milevadb/soliton/solitonutil"
    28  	"github.com/whtcorpsinc/milevadb/soliton/testkit"
    29  	"github.com/whtcorpsinc/milevadb/spacetime/autoid"
    30  	"github.com/whtcorpsinc/milevadb/stochastikctx/variable"
    31  	"github.com/whtcorpsinc/milevadb/types"
    32  )
    33  
    34  func (s *testSuite8) TestInsertOnDuplicateKey(c *C) {
    35  	tk := testkit.NewTestKit(c, s.causetstore)
    36  	tk.MustInterDirc("use test")
    37  
    38  	tk.MustInterDirc(`drop causet if exists t1, t2;`)
    39  	tk.MustInterDirc(`create causet t1(a1 bigint primary key, b1 bigint);`)
    40  	tk.MustInterDirc(`create causet t2(a2 bigint primary key, b2 bigint);`)
    41  	tk.MustInterDirc(`insert into t1 values(1, 100);`)
    42  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(1))
    43  	tk.CheckLastMessage("")
    44  	tk.MustInterDirc(`insert into t2 values(1, 200);`)
    45  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(1))
    46  	tk.CheckLastMessage("")
    47  
    48  	tk.MustInterDirc(`insert into t1 select a2, b2 from t2 on duplicate key uFIDelate b1 = a2;`)
    49  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(2))
    50  	tk.CheckLastMessage("Records: 1  Duplicates: 1  Warnings: 0")
    51  	tk.MustQuery(`select * from t1;`).Check(testkit.Events("1 1"))
    52  
    53  	tk.MustInterDirc(`insert into t1 select a2, b2 from t2 on duplicate key uFIDelate b1 = b2;`)
    54  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(2))
    55  	tk.CheckLastMessage("Records: 1  Duplicates: 1  Warnings: 0")
    56  	tk.MustQuery(`select * from t1;`).Check(testkit.Events("1 200"))
    57  
    58  	tk.MustInterDirc(`insert into t1 select a2, b2 from t2 on duplicate key uFIDelate a1 = a2;`)
    59  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(0))
    60  	tk.CheckLastMessage("Records: 1  Duplicates: 0  Warnings: 0")
    61  	tk.MustQuery(`select * from t1;`).Check(testkit.Events("1 200"))
    62  
    63  	tk.MustInterDirc(`insert into t1 select a2, b2 from t2 on duplicate key uFIDelate b1 = 300;`)
    64  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(2))
    65  	tk.CheckLastMessage("Records: 1  Duplicates: 1  Warnings: 0")
    66  	tk.MustQuery(`select * from t1;`).Check(testkit.Events("1 300"))
    67  
    68  	tk.MustInterDirc(`insert into t1 values(1, 1) on duplicate key uFIDelate b1 = 400;`)
    69  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(2))
    70  	tk.CheckLastMessage("")
    71  	tk.MustQuery(`select * from t1;`).Check(testkit.Events("1 400"))
    72  
    73  	tk.MustInterDirc(`insert into t1 select 1, 500 from t2 on duplicate key uFIDelate b1 = 400;`)
    74  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(0))
    75  	tk.CheckLastMessage("Records: 1  Duplicates: 0  Warnings: 0")
    76  	tk.MustQuery(`select * from t1;`).Check(testkit.Events("1 400"))
    77  
    78  	tk.MustInterDirc(`drop causet if exists t1, t2;`)
    79  	tk.MustInterDirc(`create causet t1(a bigint primary key, b bigint);`)
    80  	tk.MustInterDirc(`create causet t2(a bigint primary key, b bigint);`)
    81  	_, err := tk.InterDirc(`insert into t1 select * from t2 on duplicate key uFIDelate c = t2.b;`)
    82  	c.Assert(err.Error(), Equals, `[causet:1054]Unknown defCausumn 'c' in 'field list'`)
    83  
    84  	tk.MustInterDirc(`drop causet if exists t1, t2;`)
    85  	tk.MustInterDirc(`create causet t1(a bigint primary key, b bigint);`)
    86  	tk.MustInterDirc(`create causet t2(a bigint primary key, b bigint);`)
    87  	_, err = tk.InterDirc(`insert into t1 select * from t2 on duplicate key uFIDelate a = b;`)
    88  	c.Assert(err.Error(), Equals, `[causet:1052]DeferredCauset 'b' in field list is ambiguous`)
    89  
    90  	tk.MustInterDirc(`drop causet if exists t1, t2;`)
    91  	tk.MustInterDirc(`create causet t1(a bigint primary key, b bigint);`)
    92  	tk.MustInterDirc(`create causet t2(a bigint primary key, b bigint);`)
    93  	_, err = tk.InterDirc(`insert into t1 select * from t2 on duplicate key uFIDelate c = b;`)
    94  	c.Assert(err.Error(), Equals, `[causet:1054]Unknown defCausumn 'c' in 'field list'`)
    95  
    96  	tk.MustInterDirc(`drop causet if exists t1, t2;`)
    97  	tk.MustInterDirc(`create causet t1(a1 bigint primary key, b1 bigint);`)
    98  	tk.MustInterDirc(`create causet t2(a2 bigint primary key, b2 bigint);`)
    99  	_, err = tk.InterDirc(`insert into t1 select * from t2 on duplicate key uFIDelate a1 = values(b2);`)
   100  	c.Assert(err.Error(), Equals, `[causet:1054]Unknown defCausumn 'b2' in 'field list'`)
   101  
   102  	tk.MustInterDirc(`drop causet if exists t1, t2;`)
   103  	tk.MustInterDirc(`create causet t1(a1 bigint primary key, b1 bigint);`)
   104  	tk.MustInterDirc(`create causet t2(a2 bigint primary key, b2 bigint);`)
   105  	tk.MustInterDirc(`insert into t1 values(1, 100);`)
   106  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(1))
   107  	tk.CheckLastMessage("")
   108  	tk.MustInterDirc(`insert into t2 values(1, 200);`)
   109  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(1))
   110  	tk.CheckLastMessage("")
   111  	tk.MustInterDirc(`insert into t1 select * from t2 on duplicate key uFIDelate b1 = values(b1) + b2;`)
   112  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(2))
   113  	tk.CheckLastMessage("Records: 1  Duplicates: 1  Warnings: 0")
   114  	tk.MustQuery(`select * from t1`).Check(testkit.Events("1 400"))
   115  	tk.MustInterDirc(`insert into t1 select * from t2 on duplicate key uFIDelate b1 = values(b1) + b2;`)
   116  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(0))
   117  	tk.CheckLastMessage("Records: 1  Duplicates: 0  Warnings: 0")
   118  	tk.MustQuery(`select * from t1`).Check(testkit.Events("1 400"))
   119  
   120  	tk.MustInterDirc(`drop causet if exists t;`)
   121  	tk.MustInterDirc(`create causet t(k1 bigint, k2 bigint, val bigint, primary key(k1, k2));`)
   122  	tk.MustInterDirc(`insert into t (val, k1, k2) values (3, 1, 2);`)
   123  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(1))
   124  	tk.CheckLastMessage("")
   125  	tk.MustQuery(`select * from t;`).Check(testkit.Events(`1 2 3`))
   126  	tk.MustInterDirc(`insert into t (val, k1, k2) select c, a, b from (select 1 as a, 2 as b, 4 as c) tmp on duplicate key uFIDelate val = tmp.c;`)
   127  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(2))
   128  	tk.CheckLastMessage("Records: 1  Duplicates: 1  Warnings: 0")
   129  	tk.MustQuery(`select * from t;`).Check(testkit.Events(`1 2 4`))
   130  
   131  	tk.MustInterDirc(`drop causet if exists t;`)
   132  	tk.MustInterDirc(`create causet t(k1 double, k2 double, v double, primary key(k1, k2));`)
   133  	tk.MustInterDirc(`insert into t (v, k1, k2) select c, a, b from (select "3" c, "1" a, "2" b) tmp on duplicate key uFIDelate v=c;`)
   134  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(1))
   135  	tk.CheckLastMessage("Records: 1  Duplicates: 0  Warnings: 0")
   136  	tk.MustQuery(`select * from t;`).Check(testkit.Events(`1 2 3`))
   137  	tk.MustInterDirc(`insert into t (v, k1, k2) select c, a, b from (select "3" c, "1" a, "2" b) tmp on duplicate key uFIDelate v=c;`)
   138  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(0))
   139  	tk.CheckLastMessage("Records: 1  Duplicates: 0  Warnings: 0")
   140  	tk.MustQuery(`select * from t;`).Check(testkit.Events(`1 2 3`))
   141  
   142  	tk.MustInterDirc(`drop causet if exists t1, t2;`)
   143  	tk.MustInterDirc(`create causet t1(id int, a int, b int);`)
   144  	tk.MustInterDirc(`insert into t1 values (1, 1, 1);`)
   145  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(1))
   146  	tk.CheckLastMessage("")
   147  	tk.MustInterDirc(`insert into t1 values (2, 2, 1);`)
   148  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(1))
   149  	tk.CheckLastMessage("")
   150  	tk.MustInterDirc(`insert into t1 values (3, 3, 1);`)
   151  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(1))
   152  	tk.CheckLastMessage("")
   153  	tk.MustInterDirc(`create causet t2(a int primary key, b int, unique(b));`)
   154  	tk.MustInterDirc(`insert into t2 select a, b from t1 order by id on duplicate key uFIDelate a=t1.a, b=t1.b;`)
   155  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(5))
   156  	tk.CheckLastMessage("Records: 3  Duplicates: 2  Warnings: 0")
   157  	tk.MustQuery(`select * from t2 order by a;`).Check(testkit.Events(`3 1`))
   158  
   159  	tk.MustInterDirc(`drop causet if exists t1, t2;`)
   160  	tk.MustInterDirc(`create causet t1(id int, a int, b int);`)
   161  	tk.MustInterDirc(`insert into t1 values (1, 1, 1);`)
   162  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(1))
   163  	tk.CheckLastMessage("")
   164  	tk.MustInterDirc(`insert into t1 values (2, 1, 2);`)
   165  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(1))
   166  	tk.CheckLastMessage("")
   167  	tk.MustInterDirc(`insert into t1 values (3, 3, 1);`)
   168  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(1))
   169  	tk.CheckLastMessage("")
   170  	tk.MustInterDirc(`create causet t2(a int primary key, b int, unique(b));`)
   171  	tk.MustInterDirc(`insert into t2 select a, b from t1 order by id on duplicate key uFIDelate a=t1.a, b=t1.b;`)
   172  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(4))
   173  	tk.CheckLastMessage("Records: 3  Duplicates: 1  Warnings: 0")
   174  	tk.MustQuery(`select * from t2 order by a;`).Check(testkit.Events(`1 2`, `3 1`))
   175  
   176  	tk.MustInterDirc(`drop causet if exists t1, t2;`)
   177  	tk.MustInterDirc(`create causet t1(id int, a int, b int, c int);`)
   178  	tk.MustInterDirc(`insert into t1 values (1, 1, 1, 1);`)
   179  	tk.MustInterDirc(`insert into t1 values (2, 2, 1, 2);`)
   180  	tk.MustInterDirc(`insert into t1 values (3, 3, 2, 2);`)
   181  	tk.MustInterDirc(`insert into t1 values (4, 4, 2, 2);`)
   182  	tk.MustInterDirc(`create causet t2(a int primary key, b int, c int, unique(b), unique(c));`)
   183  	tk.MustInterDirc(`insert into t2 select a, b, c from t1 order by id on duplicate key uFIDelate b=t2.b, c=t2.c;`)
   184  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(2))
   185  	tk.CheckLastMessage("Records: 4  Duplicates: 0  Warnings: 0")
   186  	tk.MustQuery(`select * from t2 order by a;`).Check(testkit.Events(`1 1 1`, `3 2 2`))
   187  
   188  	tk.MustInterDirc(`drop causet if exists t1`)
   189  	tk.MustInterDirc(`create causet t1(a int primary key, b int);`)
   190  	tk.MustInterDirc(`insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5);`)
   191  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(5))
   192  	tk.CheckLastMessage("Records: 5  Duplicates: 0  Warnings: 0")
   193  	tk.MustInterDirc(`insert into t1 values(4,14),(5,15),(6,16),(7,17),(8,18) on duplicate key uFIDelate b=b+10`)
   194  	c.Assert(tk.Se.AffectedEvents(), Equals, uint64(7))
   195  	tk.CheckLastMessage("Records: 5  Duplicates: 2  Warnings: 0")
   196  
   197  	// reproduce insert on duplicate key uFIDelate bug under new event format.
   198  	tk.MustInterDirc(`drop causet if exists t1`)
   199  	tk.MustInterDirc(`create causet t1(c1 decimal(6,4), primary key(c1))`)
   200  	tk.MustInterDirc(`insert into t1 set c1 = 0.1`)
   201  	tk.MustInterDirc(`insert into t1 set c1 = 0.1 on duplicate key uFIDelate c1 = 1`)
   202  	tk.MustQuery(`select * from t1 use index(primary)`).Check(testkit.Events(`1.0000`))
   203  }
   204  
   205  func (s *testSuite8) TestClusterIndexInsertOnDuplicateKey(c *C) {
   206  	tk := testkit.NewTestKit(c, s.causetstore)
   207  	tk.MustInterDirc("drop database if exists cluster_index_duplicate_entry_error;")
   208  	tk.MustInterDirc("create database cluster_index_duplicate_entry_error;")
   209  	tk.MustInterDirc("use cluster_index_duplicate_entry_error;")
   210  	tk.MustInterDirc("set @@milevadb_enable_clustered_index = 1")
   211  
   212  	tk.MustInterDirc("create causet t(a char(20), b int, primary key(a));")
   213  	tk.MustInterDirc("insert into t values('aa', 1), ('bb', 1);")
   214  	_, err := tk.InterDirc("insert into t values('aa', 2);")
   215  	c.Assert(err, ErrorMatches, ".*Duplicate entry 'aa' for.*")
   216  
   217  	tk.MustInterDirc("drop causet t;")
   218  	tk.MustInterDirc("create causet t(a char(20), b varchar(30), c varchar(10), primary key(a, b, c));")
   219  	tk.MustInterDirc("insert into t values ('a', 'b', 'c'), ('b', 'a', 'c');")
   220  	_, err = tk.InterDirc("insert into t values ('a', 'b', 'c');")
   221  	c.Assert(err, ErrorMatches, ".*Duplicate entry 'a-b-c' for.*")
   222  }
   223  
   224  func (s *testSuite10) TestPaddingCommonHandle(c *C) {
   225  	tk := testkit.NewTestKit(c, s.causetstore)
   226  	tk.MustInterDirc("use test")
   227  	tk.MustInterDirc("set @@milevadb_enable_clustered_index = 1")
   228  	tk.MustInterDirc(`create causet t1(c1 decimal(6,4), primary key(c1))`)
   229  	tk.MustInterDirc(`insert into t1 set c1 = 0.1`)
   230  	tk.MustInterDirc(`insert into t1 set c1 = 0.1 on duplicate key uFIDelate c1 = 1`)
   231  	tk.MustQuery(`select * from t1`).Check(testkit.Events(`1.0000`))
   232  }
   233  
   234  func (s *testSuite2) TestInsertReorgDelete(c *C) {
   235  	tk := testkit.NewTestKit(c, s.causetstore)
   236  	tk.MustInterDirc("use test")
   237  
   238  	inputs := []struct {
   239  		typ string
   240  		dat string
   241  	}{
   242  		{"year", "'2004'"},
   243  		{"year", "2004"},
   244  		{"bit", "1"},
   245  		{"smallint unsigned", "1"},
   246  		{"int unsigned", "1"},
   247  		{"smallint", "-1"},
   248  		{"int", "-1"},
   249  		{"decimal(6,4)", "'1.1'"},
   250  		{"decimal", "1.1"},
   251  		{"numeric", "-1"},
   252  		{"float", "1.2"},
   253  		{"double", "1.2"},
   254  		{"double", "1.3"},
   255  		{"real", "1.4"},
   256  		{"date", "'2020-01-01'"},
   257  		{"time", "'20:00:00'"},
   258  		{"datetime", "'2020-01-01 22:22:22'"},
   259  		{"timestamp", "'2020-01-01 22:22:22'"},
   260  		{"year", "'2020'"},
   261  		{"char(15)", "'test'"},
   262  		{"varchar(15)", "'test'"},
   263  		{"binary(3)", "'a'"},
   264  		{"varbinary(3)", "'b'"},
   265  		{"blob", "'test'"},
   266  		{"text", "'test'"},
   267  		{"enum('a', 'b')", "'a'"},
   268  		{"set('a', 'b')", "'a,b'"},
   269  	}
   270  
   271  	for _, i := range inputs {
   272  		tk.MustInterDirc(`drop causet if exists t1`)
   273  		tk.MustInterDirc(fmt.Sprintf(`create causet t1(c1 %s)`, i.typ))
   274  		tk.MustInterDirc(fmt.Sprintf(`insert into t1 set c1 = %s`, i.dat))
   275  		switch i.typ {
   276  		case "blob", "text":
   277  			tk.MustInterDirc(`alter causet t1 add index idx(c1(3))`)
   278  		default:
   279  			tk.MustInterDirc(`alter causet t1 add index idx(c1)`)
   280  		}
   281  		tk.MustInterDirc(`delete from t1`)
   282  		tk.MustInterDirc(`admin check causet t1`)
   283  	}
   284  }
   285  
   286  func (s *testSuite3) TestUFIDelateDuplicateKey(c *C) {
   287  	tk := testkit.NewTestKit(c, s.causetstore)
   288  	tk.MustInterDirc("use test")
   289  
   290  	tk.MustInterDirc(`drop causet if exists t;`)
   291  	tk.MustInterDirc(`create causet c(i int,j int,k int,primary key(i,j,k));`)
   292  	tk.MustInterDirc(`insert into c values(1,2,3);`)
   293  	tk.MustInterDirc(`insert into c values(1,2,4);`)
   294  	_, err := tk.InterDirc(`uFIDelate c set i=1,j=2,k=4 where i=1 and j=2 and k=3;`)
   295  	c.Assert(err.Error(), Equals, "[ekv:1062]Duplicate entry '1-2-4' for key 'PRIMARY'")
   296  }
   297  
   298  func (s *testSuite3) TestInsertWrongValueForField(c *C) {
   299  	tk := testkit.NewTestKit(c, s.causetstore)
   300  	tk.MustInterDirc("use test")
   301  	tk.MustInterDirc(`drop causet if exists t1;`)
   302  	tk.MustInterDirc(`create causet t1(a bigint);`)
   303  	_, err := tk.InterDirc(`insert into t1 values("asfasdfsajhlkhlksdaf");`)
   304  	c.Assert(terror.ErrorEqual(err, causet.ErrTruncatedWrongValueForField), IsTrue)
   305  
   306  	tk.MustInterDirc(`drop causet if exists t1;`)
   307  	tk.MustInterDirc(`create causet t1(a varchar(10)) charset ascii;`)
   308  	_, err = tk.InterDirc(`insert into t1 values('我');`)
   309  	c.Assert(terror.ErrorEqual(err, causet.ErrTruncatedWrongValueForField), IsTrue)
   310  
   311  	tk.MustInterDirc(`drop causet if exists t1;`)
   312  	tk.MustInterDirc(`create causet t1(a char(10) charset utf8);`)
   313  	tk.MustInterDirc(`insert into t1 values('我');`)
   314  	tk.MustInterDirc(`alter causet t1 add defCausumn b char(10) charset ascii as ((a));`)
   315  	tk.MustQuery(`select * from t1;`).Check(testkit.Events(`我 `))
   316  }
   317  
   318  func (s *testSuite3) TestInsertDateTimeWithTimeZone(c *C) {
   319  	tk := testkit.NewTestKit(c, s.causetstore)
   320  
   321  	tk.MustInterDirc(`use test;`)
   322  	tk.MustInterDirc(`set time_zone="+09:00";`)
   323  	tk.MustInterDirc(`drop causet if exists t;`)
   324  	tk.MustInterDirc(`create causet t (id int, c1 datetime not null default CURRENT_TIMESTAMP);`)
   325  	tk.MustInterDirc(`set TIMESTAMP = 1234;`)
   326  	tk.MustInterDirc(`insert t (id) values (1);`)
   327  
   328  	tk.MustQuery(`select * from t;`).Check(testkit.Events(
   329  		`1 1970-01-01 09:20:34`,
   330  	))
   331  }
   332  
   333  func (s *testSuite3) TestInsertZeroYear(c *C) {
   334  	tk := testkit.NewTestKit(c, s.causetstore)
   335  	tk.MustInterDirc("use test")
   336  	tk.MustInterDirc(`drop causet if exists t1;`)
   337  	tk.MustInterDirc(`create causet t1(a year(4));`)
   338  	tk.MustInterDirc(`insert into t1 values(0000),(00),("0000"),("000"), ("00"), ("0"), (79), ("79");`)
   339  	tk.MustQuery(`select * from t1;`).Check(testkit.Events(
   340  		`0`,
   341  		`0`,
   342  		`0`,
   343  		`2000`,
   344  		`2000`,
   345  		`2000`,
   346  		`1979`,
   347  		`1979`,
   348  	))
   349  
   350  	tk.MustInterDirc(`drop causet if exists t;`)
   351  	tk.MustInterDirc(`create causet t(f_year year NOT NULL DEFAULT '0000')ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;`)
   352  	tk.MustInterDirc(`insert into t values();`)
   353  	tk.MustQuery(`select * from t;`).Check(testkit.Events(
   354  		`0`,
   355  	))
   356  	tk.MustInterDirc(`insert into t values('0000');`)
   357  	tk.MustQuery(`select * from t;`).Check(testkit.Events(
   358  		`0`,
   359  		`0`,
   360  	))
   361  }
   362  
   363  func (s *testSuiteP1) TestAllowInvalidDates(c *C) {
   364  	tk := testkit.NewTestKit(c, s.causetstore)
   365  	tk.MustInterDirc(`use test`)
   366  	tk.MustInterDirc(`drop causet if exists t1, t2, t3, t4;`)
   367  	tk.MustInterDirc(`create causet t1(d date);`)
   368  	tk.MustInterDirc(`create causet t2(d datetime);`)
   369  	tk.MustInterDirc(`create causet t3(d date);`)
   370  	tk.MustInterDirc(`create causet t4(d datetime);`)
   371  
   372  	runWithMode := func(mode string) {
   373  		inputs := []string{"0000-00-00", "2020-00-00", "2020-01-00", "2020-00-01", "2020-02-31"}
   374  		results := testkit.Events(`0 0 0`, `2020 0 0`, `2020 1 0`, `2020 0 1`, `2020 2 31`)
   375  		oldMode := tk.MustQuery(`select @@sql_mode`).Events()[0][0]
   376  		defer func() {
   377  			tk.MustInterDirc(fmt.Sprintf(`set sql_mode='%s'`, oldMode))
   378  		}()
   379  
   380  		tk.MustInterDirc(`truncate t1;truncate t2;truncate t3;truncate t4;`)
   381  		tk.MustInterDirc(fmt.Sprintf(`set sql_mode='%s';`, mode))
   382  		for _, input := range inputs {
   383  			tk.MustInterDirc(fmt.Sprintf(`insert into t1 values ('%s')`, input))
   384  			tk.MustInterDirc(fmt.Sprintf(`insert into t2 values ('%s')`, input))
   385  		}
   386  		tk.MustQuery(`select year(d), month(d), day(d) from t1;`).Check(results)
   387  		tk.MustQuery(`select year(d), month(d), day(d) from t2;`).Check(results)
   388  		tk.MustInterDirc(`insert t3 select d from t1;`)
   389  		tk.MustQuery(`select year(d), month(d), day(d) from t3;`).Check(results)
   390  		tk.MustInterDirc(`insert t4 select d from t2;`)
   391  		tk.MustQuery(`select year(d), month(d), day(d) from t4;`).Check(results)
   392  	}
   393  
   394  	runWithMode("STRICT_TRANS_TABLES,ALLOW_INVALID_DATES")
   395  	runWithMode("ALLOW_INVALID_DATES")
   396  }
   397  
   398  func (s *testSuite3) TestInsertWithAutoidSchema(c *C) {
   399  	tk := testkit.NewTestKit(c, s.causetstore)
   400  	tk.MustInterDirc(`use test`)
   401  	tk.MustInterDirc(`create causet t1(id int primary key auto_increment, n int);`)
   402  	tk.MustInterDirc(`create causet t2(id int unsigned primary key auto_increment, n int);`)
   403  	tk.MustInterDirc(`create causet t3(id tinyint primary key auto_increment, n int);`)
   404  	tk.MustInterDirc(`create causet t4(id int primary key, n float auto_increment, key I_n(n));`)
   405  	tk.MustInterDirc(`create causet t5(id int primary key, n float unsigned auto_increment, key I_n(n));`)
   406  	tk.MustInterDirc(`create causet t6(id int primary key, n double auto_increment, key I_n(n));`)
   407  	tk.MustInterDirc(`create causet t7(id int primary key, n double unsigned auto_increment, key I_n(n));`)
   408  	// test for inserting multiple values
   409  	tk.MustInterDirc(`create causet t8(id int primary key auto_increment, n int);`)
   410  
   411  	tests := []struct {
   412  		insert string
   413  		query  string
   414  		result [][]interface{}
   415  	}{
   416  		{
   417  			`insert into t1(id, n) values(1, 1)`,
   418  			`select * from t1 where id = 1`,
   419  			testkit.Events(`1 1`),
   420  		},
   421  		{
   422  			`insert into t1(n) values(2)`,
   423  			`select * from t1 where id = 2`,
   424  			testkit.Events(`2 2`),
   425  		},
   426  		{
   427  			`insert into t1(n) values(3)`,
   428  			`select * from t1 where id = 3`,
   429  			testkit.Events(`3 3`),
   430  		},
   431  		{
   432  			`insert into t1(id, n) values(-1, 4)`,
   433  			`select * from t1 where id = -1`,
   434  			testkit.Events(`-1 4`),
   435  		},
   436  		{
   437  			`insert into t1(n) values(5)`,
   438  			`select * from t1 where id = 4`,
   439  			testkit.Events(`4 5`),
   440  		},
   441  		{
   442  			`insert into t1(id, n) values('5', 6)`,
   443  			`select * from t1 where id = 5`,
   444  			testkit.Events(`5 6`),
   445  		},
   446  		{
   447  			`insert into t1(n) values(7)`,
   448  			`select * from t1 where id = 6`,
   449  			testkit.Events(`6 7`),
   450  		},
   451  		{
   452  			`insert into t1(id, n) values(7.4, 8)`,
   453  			`select * from t1 where id = 7`,
   454  			testkit.Events(`7 8`),
   455  		},
   456  		{
   457  			`insert into t1(id, n) values(7.5, 9)`,
   458  			`select * from t1 where id = 8`,
   459  			testkit.Events(`8 9`),
   460  		},
   461  		{
   462  			`insert into t1(n) values(9)`,
   463  			`select * from t1 where id = 9`,
   464  			testkit.Events(`9 9`),
   465  		},
   466  		// test last insert id
   467  		{
   468  			`insert into t1 values(3000, -1), (null, -2)`,
   469  			`select * from t1 where id = 3000`,
   470  			testkit.Events(`3000 -1`),
   471  		},
   472  		{
   473  			`;`,
   474  			`select * from t1 where id = 3001`,
   475  			testkit.Events(`3001 -2`),
   476  		},
   477  		{
   478  			`;`,
   479  			`select last_insert_id()`,
   480  			testkit.Events(`3001`),
   481  		},
   482  		{
   483  			`insert into t2(id, n) values(1, 1)`,
   484  			`select * from t2 where id = 1`,
   485  			testkit.Events(`1 1`),
   486  		},
   487  		{
   488  			`insert into t2(n) values(2)`,
   489  			`select * from t2 where id = 2`,
   490  			testkit.Events(`2 2`),
   491  		},
   492  		{
   493  			`insert into t2(n) values(3)`,
   494  			`select * from t2 where id = 3`,
   495  			testkit.Events(`3 3`),
   496  		},
   497  		{
   498  			`insert into t3(id, n) values(1, 1)`,
   499  			`select * from t3 where id = 1`,
   500  			testkit.Events(`1 1`),
   501  		},
   502  		{
   503  			`insert into t3(n) values(2)`,
   504  			`select * from t3 where id = 2`,
   505  			testkit.Events(`2 2`),
   506  		},
   507  		{
   508  			`insert into t3(n) values(3)`,
   509  			`select * from t3 where id = 3`,
   510  			testkit.Events(`3 3`),
   511  		},
   512  		{
   513  			`insert into t3(id, n) values(-1, 4)`,
   514  			`select * from t3 where id = -1`,
   515  			testkit.Events(`-1 4`),
   516  		},
   517  		{
   518  			`insert into t3(n) values(5)`,
   519  			`select * from t3 where id = 4`,
   520  			testkit.Events(`4 5`),
   521  		},
   522  		{
   523  			`insert into t4(id, n) values(1, 1)`,
   524  			`select * from t4 where id = 1`,
   525  			testkit.Events(`1 1`),
   526  		},
   527  		{
   528  			`insert into t4(id) values(2)`,
   529  			`select * from t4 where id = 2`,
   530  			testkit.Events(`2 2`),
   531  		},
   532  		{
   533  			`insert into t4(id, n) values(3, -1)`,
   534  			`select * from t4 where id = 3`,
   535  			testkit.Events(`3 -1`),
   536  		},
   537  		{
   538  			`insert into t4(id) values(4)`,
   539  			`select * from t4 where id = 4`,
   540  			testkit.Events(`4 3`),
   541  		},
   542  		{
   543  			`insert into t4(id, n) values(5, 5.5)`,
   544  			`select * from t4 where id = 5`,
   545  			testkit.Events(`5 5.5`),
   546  		},
   547  		{
   548  			`insert into t4(id) values(6)`,
   549  			`select * from t4 where id = 6`,
   550  			testkit.Events(`6 7`),
   551  		},
   552  		{
   553  			`insert into t4(id, n) values(7, '7.7')`,
   554  			`select * from t4 where id = 7`,
   555  			testkit.Events(`7 7.7`),
   556  		},
   557  		{
   558  			`insert into t4(id) values(8)`,
   559  			`select * from t4 where id = 8`,
   560  			testkit.Events(`8 9`),
   561  		},
   562  		{
   563  			`insert into t4(id, n) values(9, 10.4)`,
   564  			`select * from t4 where id = 9`,
   565  			testkit.Events(`9 10.4`),
   566  		},
   567  		{
   568  			`insert into t4(id) values(10)`,
   569  			`select * from t4 where id = 10`,
   570  			testkit.Events(`10 11`),
   571  		},
   572  		{
   573  			`insert into t5(id, n) values(1, 1)`,
   574  			`select * from t5 where id = 1`,
   575  			testkit.Events(`1 1`),
   576  		},
   577  		{
   578  			`insert into t5(id) values(2)`,
   579  			`select * from t5 where id = 2`,
   580  			testkit.Events(`2 2`),
   581  		},
   582  		{
   583  			`insert into t5(id) values(3)`,
   584  			`select * from t5 where id = 3`,
   585  			testkit.Events(`3 3`),
   586  		},
   587  		{
   588  			`insert into t6(id, n) values(1, 1)`,
   589  			`select * from t6 where id = 1`,
   590  			testkit.Events(`1 1`),
   591  		},
   592  		{
   593  			`insert into t6(id) values(2)`,
   594  			`select * from t6 where id = 2`,
   595  			testkit.Events(`2 2`),
   596  		},
   597  		{
   598  			`insert into t6(id, n) values(3, -1)`,
   599  			`select * from t6 where id = 3`,
   600  			testkit.Events(`3 -1`),
   601  		},
   602  		{
   603  			`insert into t6(id) values(4)`,
   604  			`select * from t6 where id = 4`,
   605  			testkit.Events(`4 3`),
   606  		},
   607  		{
   608  			`insert into t6(id, n) values(5, 5.5)`,
   609  			`select * from t6 where id = 5`,
   610  			testkit.Events(`5 5.5`),
   611  		},
   612  		{
   613  			`insert into t6(id) values(6)`,
   614  			`select * from t6 where id = 6`,
   615  			testkit.Events(`6 7`),
   616  		},
   617  		{
   618  			`insert into t6(id, n) values(7, '7.7')`,
   619  			`select * from t4 where id = 7`,
   620  			testkit.Events(`7 7.7`),
   621  		},
   622  		{
   623  			`insert into t6(id) values(8)`,
   624  			`select * from t4 where id = 8`,
   625  			testkit.Events(`8 9`),
   626  		},
   627  		{
   628  			`insert into t6(id, n) values(9, 10.4)`,
   629  			`select * from t6 where id = 9`,
   630  			testkit.Events(`9 10.4`),
   631  		},
   632  		{
   633  			`insert into t6(id) values(10)`,
   634  			`select * from t6 where id = 10`,
   635  			testkit.Events(`10 11`),
   636  		},
   637  		{
   638  			`insert into t7(id, n) values(1, 1)`,
   639  			`select * from t7 where id = 1`,
   640  			testkit.Events(`1 1`),
   641  		},
   642  		{
   643  			`insert into t7(id) values(2)`,
   644  			`select * from t7 where id = 2`,
   645  			testkit.Events(`2 2`),
   646  		},
   647  		{
   648  			`insert into t7(id) values(3)`,
   649  			`select * from t7 where id = 3`,
   650  			testkit.Events(`3 3`),
   651  		},
   652  
   653  		// the following is test for insert multiple values.
   654  		{
   655  			`insert into t8(n) values(1),(2)`,
   656  			`select * from t8 where id = 1`,
   657  			testkit.Events(`1 1`),
   658  		},
   659  		{
   660  			`;`,
   661  			`select * from t8 where id = 2`,
   662  			testkit.Events(`2 2`),
   663  		},
   664  		{
   665  			`;`,
   666  			`select last_insert_id();`,
   667  			testkit.Events(`1`),
   668  		},
   669  		// test user rebase and auto alloc mixture.
   670  		{
   671  			`insert into t8 values(null, 3),(-1, -1),(null,4),(null, 5)`,
   672  			`select * from t8 where id = 3`,
   673  			testkit.Events(`3 3`),
   674  		},
   675  		// -1 won't rebase allocator here cause -1 < base.
   676  		{
   677  			`;`,
   678  			`select * from t8 where id = -1`,
   679  			testkit.Events(`-1 -1`),
   680  		},
   681  		{
   682  			`;`,
   683  			`select * from t8 where id = 4`,
   684  			testkit.Events(`4 4`),
   685  		},
   686  		{
   687  			`;`,
   688  			`select * from t8 where id = 5`,
   689  			testkit.Events(`5 5`),
   690  		},
   691  		{
   692  			`;`,
   693  			`select last_insert_id();`,
   694  			testkit.Events(`3`),
   695  		},
   696  		{
   697  			`insert into t8 values(null, 6),(10, 7),(null, 8)`,
   698  			`select * from t8 where id = 6`,
   699  			testkit.Events(`6 6`),
   700  		},
   701  		// 10 will rebase allocator here.
   702  		{
   703  			`;`,
   704  			`select * from t8 where id = 10`,
   705  			testkit.Events(`10 7`),
   706  		},
   707  		{
   708  			`;`,
   709  			`select * from t8 where id = 11`,
   710  			testkit.Events(`11 8`),
   711  		},
   712  		{
   713  			`;`,
   714  			`select last_insert_id()`,
   715  			testkit.Events(`6`),
   716  		},
   717  		// fix bug for last_insert_id should be first allocated id in insert rows (skip the rebase id).
   718  		{
   719  			`insert into t8 values(100, 9),(null,10),(null,11)`,
   720  			`select * from t8 where id = 100`,
   721  			testkit.Events(`100 9`),
   722  		},
   723  		{
   724  			`;`,
   725  			`select * from t8 where id = 101`,
   726  			testkit.Events(`101 10`),
   727  		},
   728  		{
   729  			`;`,
   730  			`select * from t8 where id = 102`,
   731  			testkit.Events(`102 11`),
   732  		},
   733  		{
   734  			`;`,
   735  			`select last_insert_id()`,
   736  			testkit.Events(`101`),
   737  		},
   738  		// test with sql_mode: NO_AUTO_VALUE_ON_ZERO.
   739  		{
   740  			`;`,
   741  			`select @@sql_mode`,
   742  			testkit.Events(`ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION`),
   743  		},
   744  		{
   745  			`;`,
   746  			"set stochastik sql_mode = `ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO`",
   747  			nil,
   748  		},
   749  		{
   750  			`insert into t8 values (0, 12), (null, 13)`,
   751  			`select * from t8 where id = 0`,
   752  			testkit.Events(`0 12`),
   753  		},
   754  		{
   755  			`;`,
   756  			`select * from t8 where id = 103`,
   757  			testkit.Events(`103 13`),
   758  		},
   759  		{
   760  			`;`,
   761  			`select last_insert_id()`,
   762  			testkit.Events(`103`),
   763  		},
   764  		// test without sql_mode: NO_AUTO_VALUE_ON_ZERO.
   765  		{
   766  			`;`,
   767  			"set stochastik sql_mode = `ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION`",
   768  			nil,
   769  		},
   770  		// value 0 will be substitute by autoid.
   771  		{
   772  			`insert into t8 values (0, 14), (null, 15)`,
   773  			`select * from t8 where id = 104`,
   774  			testkit.Events(`104 14`),
   775  		},
   776  		{
   777  			`;`,
   778  			`select * from t8 where id = 105`,
   779  			testkit.Events(`105 15`),
   780  		},
   781  		{
   782  			`;`,
   783  			`select last_insert_id()`,
   784  			testkit.Events(`104`),
   785  		},
   786  		// last test : auto increment allocation can find in retryInfo.
   787  		{
   788  			`retry : insert into t8 values (null, 16), (null, 17)`,
   789  			`select * from t8 where id = 1000`,
   790  			testkit.Events(`1000 16`),
   791  		},
   792  		{
   793  			`;`,
   794  			`select * from t8 where id = 1001`,
   795  			testkit.Events(`1001 17`),
   796  		},
   797  		{
   798  			`;`,
   799  			`select last_insert_id()`,
   800  			// this insert doesn't has the last_insert_id, should be same as the last insert case.
   801  			testkit.Events(`104`),
   802  		},
   803  	}
   804  
   805  	for _, tt := range tests {
   806  		if strings.HasPrefix(tt.insert, "retry : ") {
   807  			// it's the last retry insert case, change the stochastikVars.
   808  			retryInfo := &variable.RetryInfo{Retrying: true}
   809  			retryInfo.AddAutoIncrementID(1000)
   810  			retryInfo.AddAutoIncrementID(1001)
   811  			tk.Se.GetStochastikVars().RetryInfo = retryInfo
   812  			tk.MustInterDirc(tt.insert[8:])
   813  			tk.Se.GetStochastikVars().RetryInfo = &variable.RetryInfo{}
   814  		} else {
   815  			tk.MustInterDirc(tt.insert)
   816  		}
   817  		if tt.query == "set stochastik sql_mode = `ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO`" ||
   818  			tt.query == "set stochastik sql_mode = `ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION`" {
   819  			tk.MustInterDirc(tt.query)
   820  		} else {
   821  			tk.MustQuery(tt.query).Check(tt.result)
   822  		}
   823  	}
   824  
   825  }
   826  
   827  func (s *testSuite3) TestPartitionInsertOnDuplicate(c *C) {
   828  	tk := testkit.NewTestKit(c, s.causetstore)
   829  	tk.MustInterDirc(`use test`)
   830  	tk.MustInterDirc(`create causet t1 (a int,b int,primary key(a,b)) partition by range(a) (partition p0 values less than (100),partition p1 values less than (1000))`)
   831  	tk.MustInterDirc(`insert into t1 set a=1, b=1`)
   832  	tk.MustInterDirc(`insert into t1 set a=1,b=1 on duplicate key uFIDelate a=1,b=1`)
   833  	tk.MustQuery(`select * from t1`).Check(testkit.Events("1 1"))
   834  
   835  	tk.MustInterDirc(`create causet t2 (a int,b int,primary key(a,b)) partition by hash(a) partitions 4`)
   836  	tk.MustInterDirc(`insert into t2 set a=1,b=1;`)
   837  	tk.MustInterDirc(`insert into t2 set a=1,b=1 on duplicate key uFIDelate a=1,b=1`)
   838  	tk.MustQuery(`select * from t2`).Check(testkit.Events("1 1"))
   839  
   840  	tk.MustInterDirc(`CREATE TABLE t3 (a int, b int, c int, d int, e int,
   841    PRIMARY KEY (a,b),
   842    UNIQUE KEY (b,c,d)
   843  ) PARTITION BY RANGE ( b ) (
   844    PARTITION p0 VALUES LESS THAN (4),
   845    PARTITION p1 VALUES LESS THAN (7),
   846    PARTITION p2 VALUES LESS THAN (11)
   847  )`)
   848  	tk.MustInterDirc("insert into t3 values (1,2,3,4,5)")
   849  	tk.MustInterDirc("insert into t3 values (1,2,3,4,5),(6,2,3,4,6) on duplicate key uFIDelate e = e + values(e)")
   850  	tk.MustQuery("select * from t3").Check(testkit.Events("1 2 3 4 16"))
   851  }
   852  
   853  func (s *testSuite3) TestBit(c *C) {
   854  	tk := testkit.NewTestKit(c, s.causetstore)
   855  	tk.MustInterDirc(`use test`)
   856  	tk.MustInterDirc(`create causet t1 (a bit(3))`)
   857  	_, err := tk.InterDirc("insert into t1 values(-1)")
   858  	c.Assert(types.ErrDataTooLong.Equal(err), IsTrue)
   859  	c.Assert(err.Error(), Matches, ".*Data too long for defCausumn 'a' at.*")
   860  	_, err = tk.InterDirc("insert into t1 values(9)")
   861  	c.Assert(err.Error(), Matches, ".*Data too long for defCausumn 'a' at.*")
   862  
   863  	tk.MustInterDirc(`create causet t64 (a bit(64))`)
   864  	tk.MustInterDirc("insert into t64 values(-1)")
   865  	tk.MustInterDirc("insert into t64 values(18446744073709551615)")      // 2^64 - 1
   866  	_, err = tk.InterDirc("insert into t64 values(18446744073709551616)") // z^64
   867  	c.Assert(err.Error(), Matches, ".*Out of range value for defCausumn 'a' at.*")
   868  
   869  }
   870  
   871  func (s *testSuiteP1) TestAllocateContinuousEventID(c *C) {
   872  	tk := testkit.NewTestKit(c, s.causetstore)
   873  	tk.MustInterDirc(`use test`)
   874  	tk.MustInterDirc(`create causet t1 (a int,b int, key I_a(a));`)
   875  	wg := sync.WaitGroup{}
   876  	for i := 0; i < 5; i++ {
   877  		wg.Add(1)
   878  		go func(idx int) {
   879  			defer wg.Done()
   880  			tk := testkit.NewTestKitWithInit(c, s.causetstore)
   881  			for j := 0; j < 10; j++ {
   882  				k := strconv.Itoa(idx*100 + j)
   883  				allegrosql := "insert into t1(a,b) values (" + k + ", 2)"
   884  				for t := 0; t < 20; t++ {
   885  					allegrosql += ",(" + k + ",2)"
   886  				}
   887  				tk.MustInterDirc(allegrosql)
   888  				q := "select _milevadb_rowid from t1 where a=" + k
   889  				rows := tk.MustQuery(q).Events()
   890  				c.Assert(len(rows), Equals, 21)
   891  				last := 0
   892  				for _, r := range rows {
   893  					c.Assert(len(r), Equals, 1)
   894  					v, err := strconv.Atoi(r[0].(string))
   895  					c.Assert(err, Equals, nil)
   896  					if last > 0 {
   897  						c.Assert(last+1, Equals, v)
   898  					}
   899  					last = v
   900  				}
   901  			}
   902  		}(i)
   903  	}
   904  	wg.Wait()
   905  }
   906  
   907  func (s *testSuite3) TestJiraIssue5366(c *C) {
   908  	tk := testkit.NewTestKit(c, s.causetstore)
   909  	tk.MustInterDirc(`use test`)
   910  	tk.MustInterDirc(`create causet bug (a varchar(100))`)
   911  	tk.MustInterDirc(` insert into bug select  ifnull(JSON_UNQUOTE(JSON_EXTRACT('[{"amount":2000,"feeAmount":0,"merchantNo":"20190430140319679394","shareBizCode":"20160311162_SECOND"}]', '$[0].merchantNo')),'') merchant_no union SELECT '20180531557' merchant_no;`)
   912  	tk.MustQuery(`select * from bug`).Sort().Check(testkit.Events("20180531557", "20190430140319679394"))
   913  }
   914  
   915  func (s *testSuite3) TestDMLCast(c *C) {
   916  	tk := testkit.NewTestKit(c, s.causetstore)
   917  	tk.MustInterDirc(`use test`)
   918  	tk.MustInterDirc(`create causet t (a int, b double)`)
   919  	tk.MustInterDirc(`insert into t values (ifnull('',0)+0, 0)`)
   920  	tk.MustInterDirc(`insert into t values (0, ifnull('',0)+0)`)
   921  	tk.MustQuery(`select * from t`).Check(testkit.Events("0 0", "0 0"))
   922  	_, err := tk.InterDirc(`insert into t values ('', 0)`)
   923  	c.Assert(err, NotNil)
   924  	_, err = tk.InterDirc(`insert into t values (0, '')`)
   925  	c.Assert(err, NotNil)
   926  	_, err = tk.InterDirc(`uFIDelate t set a = ''`)
   927  	c.Assert(err, NotNil)
   928  	_, err = tk.InterDirc(`uFIDelate t set b = ''`)
   929  	c.Assert(err, NotNil)
   930  	tk.MustInterDirc("uFIDelate t set a = ifnull('',0)+0")
   931  	tk.MustInterDirc("uFIDelate t set b = ifnull('',0)+0")
   932  	tk.MustInterDirc("delete from t where a = ''")
   933  	tk.MustQuery(`select * from t`).Check(testkit.Events())
   934  }
   935  
   936  // There is a potential issue in MyALLEGROSQL: when the value of auto_increment_offset is greater
   937  // than that of auto_increment_increment, the value of auto_increment_offset is ignored
   938  // (https://dev.allegrosql.com/doc/refman/8.0/en/replication-options-master.html#sysvar_auto_increment_increment),
   939  // This issue is a flaw of the implementation of MyALLEGROSQL and it doesn't exist in MilevaDB.
   940  func (s *testSuite3) TestAutoIDIncrementAndOffset(c *C) {
   941  	tk := testkit.NewTestKit(c, s.causetstore)
   942  	tk.MustInterDirc(`use test`)
   943  	// Test for offset is larger than increment.
   944  	tk.Se.GetStochastikVars().AutoIncrementIncrement = 5
   945  	tk.Se.GetStochastikVars().AutoIncrementOffset = 10
   946  	tk.MustInterDirc(`create causet io (a int key auto_increment)`)
   947  	tk.MustInterDirc(`insert into io values (null),(null),(null)`)
   948  	tk.MustQuery(`select * from io`).Check(testkit.Events("10", "15", "20"))
   949  	tk.MustInterDirc(`drop causet io`)
   950  
   951  	// Test handle is PK.
   952  	tk.MustInterDirc(`create causet io (a int key auto_increment)`)
   953  	tk.Se.GetStochastikVars().AutoIncrementOffset = 10
   954  	tk.Se.GetStochastikVars().AutoIncrementIncrement = 2
   955  	tk.MustInterDirc(`insert into io values (),(),()`)
   956  	tk.MustQuery(`select * from io`).Check(testkit.Events("10", "12", "14"))
   957  	tk.MustInterDirc(`delete from io`)
   958  
   959  	// Test reset the increment.
   960  	tk.Se.GetStochastikVars().AutoIncrementIncrement = 5
   961  	tk.MustInterDirc(`insert into io values (),(),()`)
   962  	tk.MustQuery(`select * from io`).Check(testkit.Events("15", "20", "25"))
   963  	tk.MustInterDirc(`delete from io`)
   964  
   965  	tk.Se.GetStochastikVars().AutoIncrementIncrement = 10
   966  	tk.MustInterDirc(`insert into io values (),(),()`)
   967  	tk.MustQuery(`select * from io`).Check(testkit.Events("30", "40", "50"))
   968  	tk.MustInterDirc(`delete from io`)
   969  
   970  	tk.Se.GetStochastikVars().AutoIncrementIncrement = 5
   971  	tk.MustInterDirc(`insert into io values (),(),()`)
   972  	tk.MustQuery(`select * from io`).Check(testkit.Events("55", "60", "65"))
   973  	tk.MustInterDirc(`drop causet io`)
   974  
   975  	// Test handle is not PK.
   976  	tk.Se.GetStochastikVars().AutoIncrementIncrement = 2
   977  	tk.Se.GetStochastikVars().AutoIncrementOffset = 10
   978  	tk.MustInterDirc(`create causet io (a int, b int auto_increment, key(b))`)
   979  	tk.MustInterDirc(`insert into io(b) values (null),(null),(null)`)
   980  	// AutoID allocation will take increment and offset into consideration.
   981  	tk.MustQuery(`select b from io`).Check(testkit.Events("10", "12", "14"))
   982  	// HandleID allocation will ignore the increment and offset.
   983  	tk.MustQuery(`select _milevadb_rowid from io`).Check(testkit.Events("15", "16", "17"))
   984  	tk.MustInterDirc(`delete from io`)
   985  
   986  	tk.Se.GetStochastikVars().AutoIncrementIncrement = 10
   987  	tk.MustInterDirc(`insert into io(b) values (null),(null),(null)`)
   988  	tk.MustQuery(`select b from io`).Check(testkit.Events("20", "30", "40"))
   989  	tk.MustQuery(`select _milevadb_rowid from io`).Check(testkit.Events("41", "42", "43"))
   990  
   991  	// Test invalid value.
   992  	tk.Se.GetStochastikVars().AutoIncrementIncrement = -1
   993  	tk.Se.GetStochastikVars().AutoIncrementOffset = -2
   994  	_, err := tk.InterDirc(`insert into io(b) values (null),(null),(null)`)
   995  	c.Assert(err, NotNil)
   996  	c.Assert(err.Error(), Equals, "[autoid:8060]Invalid auto_increment settings: auto_increment_increment: -1, auto_increment_offset: -2, both of them must be in range [1..65535]")
   997  	tk.MustInterDirc(`delete from io`)
   998  
   999  	tk.Se.GetStochastikVars().AutoIncrementIncrement = 65536
  1000  	tk.Se.GetStochastikVars().AutoIncrementOffset = 65536
  1001  	_, err = tk.InterDirc(`insert into io(b) values (null),(null),(null)`)
  1002  	c.Assert(err, NotNil)
  1003  	c.Assert(err.Error(), Equals, "[autoid:8060]Invalid auto_increment settings: auto_increment_increment: 65536, auto_increment_offset: 65536, both of them must be in range [1..65535]")
  1004  }
  1005  
  1006  var _ = SerialSuites(&testSuite9{&baseTestSuite{}})
  1007  
  1008  type testSuite9 struct {
  1009  	*baseTestSuite
  1010  }
  1011  
  1012  func (s *testSuite9) TestAutoRandomID(c *C) {
  1013  	solitonutil.ConfigTestUtils.SetupAutoRandomTestConfig()
  1014  	defer solitonutil.ConfigTestUtils.RestoreAutoRandomTestConfig()
  1015  
  1016  	tk := testkit.NewTestKit(c, s.causetstore)
  1017  	tk.MustInterDirc(`use test`)
  1018  	tk.MustInterDirc(`drop causet if exists ar`)
  1019  	tk.MustInterDirc(`create causet ar (id bigint key auto_random, name char(10))`)
  1020  
  1021  	tk.MustInterDirc(`insert into ar(id) values (null)`)
  1022  	rs := tk.MustQuery(`select id from ar`)
  1023  	c.Assert(len(rs.Events()), Equals, 1)
  1024  	firstValue, err := strconv.Atoi(rs.Events()[0][0].(string))
  1025  	c.Assert(err, IsNil)
  1026  	c.Assert(firstValue, Greater, 0)
  1027  	tk.MustQuery(`select last_insert_id()`).Check(testkit.Events(fmt.Sprintf("%d", firstValue)))
  1028  	tk.MustInterDirc(`delete from ar`)
  1029  
  1030  	tk.MustInterDirc(`insert into ar(id) values (0)`)
  1031  	rs = tk.MustQuery(`select id from ar`)
  1032  	c.Assert(len(rs.Events()), Equals, 1)
  1033  	firstValue, err = strconv.Atoi(rs.Events()[0][0].(string))
  1034  	c.Assert(err, IsNil)
  1035  	c.Assert(firstValue, Greater, 0)
  1036  	tk.MustQuery(`select last_insert_id()`).Check(testkit.Events(fmt.Sprintf("%d", firstValue)))
  1037  	tk.MustInterDirc(`delete from ar`)
  1038  
  1039  	tk.MustInterDirc(`insert into ar(name) values ('a')`)
  1040  	rs = tk.MustQuery(`select id from ar`)
  1041  	c.Assert(len(rs.Events()), Equals, 1)
  1042  	firstValue, err = strconv.Atoi(rs.Events()[0][0].(string))
  1043  	c.Assert(err, IsNil)
  1044  	c.Assert(firstValue, Greater, 0)
  1045  	tk.MustQuery(`select last_insert_id()`).Check(testkit.Events(fmt.Sprintf("%d", firstValue)))
  1046  
  1047  	tk.MustInterDirc(`drop causet ar`)
  1048  	tk.MustInterDirc(`create causet ar (id bigint key auto_random(15), name char(10))`)
  1049  	overflowVal := 1 << (64 - 5)
  1050  	errMsg := fmt.Sprintf(autoid.AutoRandomRebaseOverflow, overflowVal, 1<<(64-16)-1)
  1051  	_, err = tk.InterDirc(fmt.Sprintf("alter causet ar auto_random_base = %d", overflowVal))
  1052  	c.Assert(err, NotNil)
  1053  	c.Assert(strings.Contains(err.Error(), errMsg), IsTrue)
  1054  }
  1055  
  1056  func (s *testSuite9) TestMultiAutoRandomID(c *C) {
  1057  	solitonutil.ConfigTestUtils.SetupAutoRandomTestConfig()
  1058  	defer solitonutil.ConfigTestUtils.RestoreAutoRandomTestConfig()
  1059  
  1060  	tk := testkit.NewTestKit(c, s.causetstore)
  1061  	tk.MustInterDirc(`use test`)
  1062  	tk.MustInterDirc(`drop causet if exists ar`)
  1063  	tk.MustInterDirc(`create causet ar (id bigint key auto_random, name char(10))`)
  1064  
  1065  	tk.MustInterDirc(`insert into ar(id) values (null),(null),(null)`)
  1066  	rs := tk.MustQuery(`select id from ar order by id`)
  1067  	c.Assert(len(rs.Events()), Equals, 3)
  1068  	firstValue, err := strconv.Atoi(rs.Events()[0][0].(string))
  1069  	c.Assert(err, IsNil)
  1070  	c.Assert(firstValue, Greater, 0)
  1071  	c.Assert(rs.Events()[1][0].(string), Equals, fmt.Sprintf("%d", firstValue+1))
  1072  	c.Assert(rs.Events()[2][0].(string), Equals, fmt.Sprintf("%d", firstValue+2))
  1073  	tk.MustQuery(`select last_insert_id()`).Check(testkit.Events(fmt.Sprintf("%d", firstValue)))
  1074  	tk.MustInterDirc(`delete from ar`)
  1075  
  1076  	tk.MustInterDirc(`insert into ar(id) values (0),(0),(0)`)
  1077  	rs = tk.MustQuery(`select id from ar order by id`)
  1078  	c.Assert(len(rs.Events()), Equals, 3)
  1079  	firstValue, err = strconv.Atoi(rs.Events()[0][0].(string))
  1080  	c.Assert(err, IsNil)
  1081  	c.Assert(firstValue, Greater, 0)
  1082  	c.Assert(rs.Events()[1][0].(string), Equals, fmt.Sprintf("%d", firstValue+1))
  1083  	c.Assert(rs.Events()[2][0].(string), Equals, fmt.Sprintf("%d", firstValue+2))
  1084  	tk.MustQuery(`select last_insert_id()`).Check(testkit.Events(fmt.Sprintf("%d", firstValue)))
  1085  	tk.MustInterDirc(`delete from ar`)
  1086  
  1087  	tk.MustInterDirc(`insert into ar(name) values ('a'),('a'),('a')`)
  1088  	rs = tk.MustQuery(`select id from ar order by id`)
  1089  	c.Assert(len(rs.Events()), Equals, 3)
  1090  	firstValue, err = strconv.Atoi(rs.Events()[0][0].(string))
  1091  	c.Assert(err, IsNil)
  1092  	c.Assert(firstValue, Greater, 0)
  1093  	c.Assert(rs.Events()[1][0].(string), Equals, fmt.Sprintf("%d", firstValue+1))
  1094  	c.Assert(rs.Events()[2][0].(string), Equals, fmt.Sprintf("%d", firstValue+2))
  1095  	tk.MustQuery(`select last_insert_id()`).Check(testkit.Events(fmt.Sprintf("%d", firstValue)))
  1096  
  1097  	tk.MustInterDirc(`drop causet ar`)
  1098  }
  1099  
  1100  func (s *testSuite9) TestAutoRandomIDAllowZero(c *C) {
  1101  	solitonutil.ConfigTestUtils.SetupAutoRandomTestConfig()
  1102  	defer solitonutil.ConfigTestUtils.RestoreAutoRandomTestConfig()
  1103  
  1104  	tk := testkit.NewTestKit(c, s.causetstore)
  1105  	tk.MustInterDirc(`use test`)
  1106  	tk.MustInterDirc(`drop causet if exists ar`)
  1107  	tk.MustInterDirc(`create causet ar (id bigint key auto_random, name char(10))`)
  1108  
  1109  	rs := tk.MustQuery(`select @@stochastik.sql_mode`)
  1110  	sqlMode := rs.Events()[0][0].(string)
  1111  	tk.MustInterDirc(fmt.Sprintf(`set stochastik sql_mode="%s,%s"`, sqlMode, "NO_AUTO_VALUE_ON_ZERO"))
  1112  
  1113  	tk.MustInterDirc(`insert into ar(id) values (0)`)
  1114  	rs = tk.MustQuery(`select id from ar`)
  1115  	c.Assert(len(rs.Events()), Equals, 1)
  1116  	firstValue, err := strconv.Atoi(rs.Events()[0][0].(string))
  1117  	c.Assert(err, IsNil)
  1118  	c.Assert(firstValue, Equals, 0)
  1119  	tk.MustQuery(`select last_insert_id()`).Check(testkit.Events(fmt.Sprintf("%d", firstValue)))
  1120  	tk.MustInterDirc(`delete from ar`)
  1121  
  1122  	tk.MustInterDirc(`insert into ar(id) values (null)`)
  1123  	rs = tk.MustQuery(`select id from ar`)
  1124  	c.Assert(len(rs.Events()), Equals, 1)
  1125  	firstValue, err = strconv.Atoi(rs.Events()[0][0].(string))
  1126  	c.Assert(err, IsNil)
  1127  	c.Assert(firstValue, Greater, 0)
  1128  	tk.MustQuery(`select last_insert_id()`).Check(testkit.Events(fmt.Sprintf("%d", firstValue)))
  1129  
  1130  	tk.MustInterDirc(`drop causet ar`)
  1131  }
  1132  
  1133  func (s *testSuite9) TestAutoRandomIDExplicit(c *C) {
  1134  	solitonutil.ConfigTestUtils.SetupAutoRandomTestConfig()
  1135  	defer solitonutil.ConfigTestUtils.RestoreAutoRandomTestConfig()
  1136  
  1137  	tk := testkit.NewTestKit(c, s.causetstore)
  1138  	tk.MustInterDirc("set @@allow_auto_random_explicit_insert = true")
  1139  
  1140  	tk.MustInterDirc(`use test`)
  1141  	tk.MustInterDirc(`drop causet if exists ar`)
  1142  	tk.MustInterDirc(`create causet ar (id bigint key auto_random, name char(10))`)
  1143  
  1144  	tk.MustInterDirc(`insert into ar(id) values (1)`)
  1145  	tk.MustQuery(`select id from ar`).Check(testkit.Events("1"))
  1146  	tk.MustQuery(`select last_insert_id()`).Check(testkit.Events("0"))
  1147  	tk.MustInterDirc(`delete from ar`)
  1148  
  1149  	tk.MustInterDirc(`insert into ar(id) values (1), (2)`)
  1150  	tk.MustQuery(`select id from ar`).Check(testkit.Events("1", "2"))
  1151  	tk.MustQuery(`select last_insert_id()`).Check(testkit.Events("0"))
  1152  	tk.MustInterDirc(`delete from ar`)
  1153  
  1154  	tk.MustInterDirc(`drop causet ar`)
  1155  }
  1156  
  1157  func (s *testSuite9) TestInsertErrorMsg(c *C) {
  1158  	tk := testkit.NewTestKit(c, s.causetstore)
  1159  	tk.MustInterDirc(`use test`)
  1160  	tk.MustInterDirc(`drop causet if exists t`)
  1161  	tk.MustInterDirc(`create causet t (a int primary key, b datetime, d date)`)
  1162  	_, err := tk.InterDirc(`insert into t values (1, '2020-02-11 30:00:00', '2020-01-31')`)
  1163  	c.Assert(err, NotNil)
  1164  	c.Assert(strings.Contains(err.Error(), "Incorrect datetime value: '2020-02-11 30:00:00' for defCausumn 'b' at event 1"), IsTrue, Commentf("%v", err))
  1165  }
  1166  
  1167  func (s *testSuite9) TestIssue16366(c *C) {
  1168  	tk := testkit.NewTestKit(c, s.causetstore)
  1169  	tk.MustInterDirc(`use test;`)
  1170  	tk.MustInterDirc(`drop causet if exists t;`)
  1171  	tk.MustInterDirc(`create causet t(c numeric primary key);`)
  1172  	tk.MustInterDirc("insert ignore into t values(null);")
  1173  	_, err := tk.InterDirc(`insert into t values(0);`)
  1174  	c.Assert(err, NotNil)
  1175  	c.Assert(strings.Contains(err.Error(), "Duplicate entry '0' for key 'PRIMARY'"), IsTrue, Commentf("%v", err))
  1176  }
  1177  
  1178  var _ = SerialSuites(&testSuite10{&baseTestSuite{}})
  1179  
  1180  type testSuite10 struct {
  1181  	*baseTestSuite
  1182  }
  1183  
  1184  func (s *testSuite10) TestClusterPrimaryBlockPlainInsert(c *C) {
  1185  	tk := testkit.NewTestKit(c, s.causetstore)
  1186  	tk.MustInterDirc(`use test`)
  1187  	tk.MustInterDirc(`set @@milevadb_enable_clustered_index=true`)
  1188  
  1189  	tk.MustInterDirc(`drop causet if exists t1pk`)
  1190  	tk.MustInterDirc(`create causet t1pk(id varchar(200) primary key, v int)`)
  1191  	tk.MustInterDirc(`insert into t1pk(id, v) values('abc', 1)`)
  1192  	tk.MustQuery(`select * from t1pk`).Check(testkit.Events("abc 1"))
  1193  	tk.MustInterDirc(`set @@milevadb_constraint_check_in_place=true`)
  1194  	tk.MustGetErrCode(`insert into t1pk(id, v) values('abc', 2)`, errno.ErrDupEntry)
  1195  	tk.MustInterDirc(`set @@milevadb_constraint_check_in_place=false`)
  1196  	tk.MustGetErrCode(`insert into t1pk(id, v) values('abc', 3)`, errno.ErrDupEntry)
  1197  	tk.MustQuery(`select v, id from t1pk`).Check(testkit.Events("1 abc"))
  1198  	tk.MustQuery(`select id from t1pk where id = 'abc'`).Check(testkit.Events("abc"))
  1199  	tk.MustQuery(`select v, id from t1pk where id = 'abc'`).Check(testkit.Events("1 abc"))
  1200  
  1201  	tk.MustInterDirc(`drop causet if exists t3pk`)
  1202  	tk.MustInterDirc(`create causet t3pk(id1 varchar(200), id2 varchar(200), v int, id3 int, primary key(id1, id2, id3))`)
  1203  	tk.MustInterDirc(`insert into t3pk(id1, id2, id3, v) values('abc', 'xyz', 100, 1)`)
  1204  	tk.MustQuery(`select * from t3pk`).Check(testkit.Events("abc xyz 1 100"))
  1205  	tk.MustInterDirc(`set @@milevadb_constraint_check_in_place=true`)
  1206  	tk.MustGetErrCode(`insert into t3pk(id1, id2, id3, v) values('abc', 'xyz', 100, 2)`, errno.ErrDupEntry)
  1207  	tk.MustInterDirc(`set @@milevadb_constraint_check_in_place=false`)
  1208  	tk.MustGetErrCode(`insert into t3pk(id1, id2, id3, v) values('abc', 'xyz', 100, 3)`, errno.ErrDupEntry)
  1209  	tk.MustQuery(`select v, id3, id2, id1 from t3pk`).Check(testkit.Events("1 100 xyz abc"))
  1210  	tk.MustQuery(`select id3, id2, id1 from t3pk where id3 = 100 and id2 = 'xyz' and id1 = 'abc'`).Check(testkit.Events("100 xyz abc"))
  1211  	tk.MustQuery(`select id3, id2, id1, v from t3pk where id3 = 100 and id2 = 'xyz' and id1 = 'abc'`).Check(testkit.Events("100 xyz abc 1"))
  1212  	tk.MustInterDirc(`insert into t3pk(id1, id2, id3, v) values('abc', 'xyz', 101, 1)`)
  1213  	tk.MustInterDirc(`insert into t3pk(id1, id2, id3, v) values('abc', 'zzz', 101, 1)`)
  1214  
  1215  	tk.MustInterDirc(`drop causet if exists t1pku`)
  1216  	tk.MustInterDirc(`create causet t1pku(id varchar(200) primary key, uk int, v int, unique key ukk(uk))`)
  1217  	tk.MustInterDirc(`insert into t1pku(id, uk, v) values('abc', 1, 2)`)
  1218  	tk.MustQuery(`select * from t1pku where id = 'abc'`).Check(testkit.Events("abc 1 2"))
  1219  	tk.MustGetErrCode(`insert into t1pku(id, uk, v) values('aaa', 1, 3)`, errno.ErrDupEntry)
  1220  	tk.MustQuery(`select * from t1pku`).Check(testkit.Events("abc 1 2"))
  1221  
  1222  	tk.MustQuery(`select * from t3pk where (id1, id2, id3) in (('abc', 'xyz', 100), ('abc', 'xyz', 101), ('abc', 'zzz', 101))`).
  1223  		Check(testkit.Events("abc xyz 1 100", "abc xyz 1 101", "abc zzz 1 101"))
  1224  }
  1225  
  1226  func (s *testSuite10) TestClusterPrimaryBlockInsertIgnore(c *C) {
  1227  	tk := testkit.NewTestKit(c, s.causetstore)
  1228  	tk.MustInterDirc(`use test`)
  1229  	tk.MustInterDirc(`set @@milevadb_enable_clustered_index=true`)
  1230  
  1231  	tk.MustInterDirc(`drop causet if exists it1pk`)
  1232  	tk.MustInterDirc(`create causet it1pk(id varchar(200) primary key, v int)`)
  1233  	tk.MustInterDirc(`insert into it1pk(id, v) values('abc', 1)`)
  1234  	tk.MustInterDirc(`insert ignore into it1pk(id, v) values('abc', 2)`)
  1235  	tk.MustQuery(`select * from it1pk where id = 'abc'`).Check(testkit.Events("abc 1"))
  1236  
  1237  	tk.MustInterDirc(`drop causet if exists it2pk`)
  1238  	tk.MustInterDirc(`create causet it2pk(id1 varchar(200), id2 varchar(200), v int, primary key(id1, id2))`)
  1239  	tk.MustInterDirc(`insert into it2pk(id1, id2, v) values('abc', 'cba', 1)`)
  1240  	tk.MustQuery(`select * from it2pk where id1 = 'abc' and id2 = 'cba'`).Check(testkit.Events("abc cba 1"))
  1241  	tk.MustInterDirc(`insert ignore into it2pk(id1, id2, v) values('abc', 'cba', 2)`)
  1242  	tk.MustQuery(`select * from it2pk where id1 = 'abc' and id2 = 'cba'`).Check(testkit.Events("abc cba 1"))
  1243  
  1244  	tk.MustInterDirc(`drop causet if exists it1pku`)
  1245  	tk.MustInterDirc(`create causet it1pku(id varchar(200) primary key, uk int, v int, unique key ukk(uk))`)
  1246  	tk.MustInterDirc(`insert into it1pku(id, uk, v) values('abc', 1, 2)`)
  1247  	tk.MustQuery(`select * from it1pku where id = 'abc'`).Check(testkit.Events("abc 1 2"))
  1248  	tk.MustInterDirc(`insert ignore into it1pku(id, uk, v) values('aaa', 1, 3), ('bbb', 2, 1)`)
  1249  	tk.MustQuery(`select * from it1pku`).Check(testkit.Events("abc 1 2", "bbb 2 1"))
  1250  }
  1251  
  1252  func (s *testSuite10) TestClusterPrimaryBlockInsertDuplicate(c *C) {
  1253  	tk := testkit.NewTestKit(c, s.causetstore)
  1254  	tk.MustInterDirc(`use test`)
  1255  	tk.MustInterDirc(`set @@milevadb_enable_clustered_index=true`)
  1256  
  1257  	tk.MustInterDirc(`drop causet if exists dt1pi`)
  1258  	tk.MustInterDirc(`create causet dt1pi(id varchar(200) primary key, v int)`)
  1259  	tk.MustInterDirc(`insert into dt1pi(id, v) values('abb', 1),('acc', 2)`)
  1260  	tk.MustInterDirc(`insert into dt1pi(id, v) values('abb', 2) on duplicate key uFIDelate v = v + 1`)
  1261  	tk.MustQuery(`select * from dt1pi`).Check(testkit.Events("abb 2", "acc 2"))
  1262  	tk.MustInterDirc(`insert into dt1pi(id, v) values('abb', 2) on duplicate key uFIDelate v = v + 1, id = 'xxx'`)
  1263  	tk.MustQuery(`select * from dt1pi`).Check(testkit.Events("acc 2", "xxx 3"))
  1264  
  1265  	tk.MustInterDirc(`drop causet if exists dt1piu`)
  1266  	tk.MustInterDirc(`create causet dt1piu(id varchar(200) primary key, uk int, v int, unique key uuk(uk))`)
  1267  	tk.MustInterDirc(`insert into dt1piu(id, uk, v) values('abb', 1, 10),('acc', 2, 20)`)
  1268  	tk.MustInterDirc(`insert into dt1piu(id, uk, v) values('xyz', 1, 100) on duplicate key uFIDelate v = v + 1`)
  1269  	tk.MustQuery(`select * from dt1piu`).Check(testkit.Events("abb 1 11", "acc 2 20"))
  1270  	tk.MustInterDirc(`insert into dt1piu(id, uk, v) values('abb', 1, 2) on duplicate key uFIDelate v = v + 1, id = 'xxx'`)
  1271  	tk.MustQuery(`select * from dt1piu`).Check(testkit.Events("acc 2 20", "xxx 1 12"))
  1272  
  1273  	tk.MustInterDirc(`drop causet if exists ts1pk`)
  1274  	tk.MustInterDirc(`create causet ts1pk(id1 timestamp, id2 timestamp, v int, primary key(id1, id2))`)
  1275  	ts := "2020-01-01 11:11:11"
  1276  	tk.MustInterDirc(`insert into ts1pk (id1, id2, v) values(?, ?, ?)`, ts, ts, 1)
  1277  	tk.MustQuery(`select id1, id2, v from ts1pk`).Check(testkit.Events("2020-01-01 11:11:11 2020-01-01 11:11:11 1"))
  1278  	tk.MustInterDirc(`insert into ts1pk (id1, id2, v) values(?, ?, ?) on duplicate key uFIDelate v = values(v)`, ts, ts, 2)
  1279  	tk.MustQuery(`select id1, id2, v from ts1pk`).Check(testkit.Events("2020-01-01 11:11:11 2020-01-01 11:11:11 2"))
  1280  	tk.MustInterDirc(`insert into ts1pk (id1, id2, v) values(?, ?, ?) on duplicate key uFIDelate v = values(v), id1 = ?`, ts, ts, 2, "2020-01-01 11:11:12")
  1281  	tk.MustQuery(`select id1, id2, v from ts1pk`).Check(testkit.Events("2020-01-01 11:11:12 2020-01-01 11:11:11 2"))
  1282  }
  1283  
  1284  func (s *testSuite10) TestClusterPrimaryKeyForIndexScan(c *C) {
  1285  	tk := testkit.NewTestKit(c, s.causetstore)
  1286  	tk.MustInterDirc(`use test`)
  1287  	tk.MustInterDirc(`set @@milevadb_enable_clustered_index=true`)
  1288  
  1289  	tk.MustInterDirc("drop causet if exists pkt1;")
  1290  	tk.MustInterDirc("CREATE TABLE pkt1 (a varchar(255), b int, index idx(b), primary key(a,b));")
  1291  	tk.MustInterDirc("insert into pkt1 values ('aaa',1);")
  1292  	tk.MustQuery(`select b from pkt1 where b = 1;`).Check(testkit.Events("1"))
  1293  
  1294  	tk.MustInterDirc("drop causet if exists pkt2;")
  1295  	tk.MustInterDirc("CREATE TABLE pkt2 (a varchar(255), b int, unique index idx(b), primary key(a,b));")
  1296  	tk.MustInterDirc("insert into pkt2 values ('aaa',1);")
  1297  	tk.MustQuery(`select b from pkt2 where b = 1;`).Check(testkit.Events("1"))
  1298  
  1299  	tk.MustInterDirc("drop causet if exists issue_18232;")
  1300  	tk.MustInterDirc("create causet issue_18232 (a int, b int, c int, d int, primary key (a, b), index idx(c));")
  1301  
  1302  	iter, cnt := combination([]string{"a", "b", "c", "d"}), 0
  1303  	for {
  1304  		comb := iter()
  1305  		if comb == nil {
  1306  			break
  1307  		}
  1308  		selField := strings.Join(comb, ",")
  1309  		allegrosql := fmt.Sprintf("select %s from issue_18232 use index (idx);", selField)
  1310  		tk.MustInterDirc(allegrosql)
  1311  		cnt++
  1312  	}
  1313  	c.Assert(cnt, Equals, 15)
  1314  }
  1315  
  1316  func combination(items []string) func() []string {
  1317  	current := 1
  1318  	buf := make([]string, len(items))
  1319  	return func() []string {
  1320  		if current >= int(math.Pow(2, float64(len(items)))) {
  1321  			return nil
  1322  		}
  1323  		buf = buf[:0]
  1324  		for i, e := range items {
  1325  			if (1<<i)&current != 0 {
  1326  				buf = append(buf, e)
  1327  			}
  1328  		}
  1329  		current++
  1330  		return buf
  1331  	}
  1332  }