github.com/whtcorpsinc/MilevaDB-Prod@v0.0.0-20211104133533-f57f4be3b597/causetstore/petri/acyclic/causet/embedded/preprocess_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 embedded_test
    15  
    16  import (
    17  	"context"
    18  
    19  	"github.com/whtcorpsinc/BerolinaSQL"
    20  	"github.com/whtcorpsinc/BerolinaSQL/perceptron"
    21  	"github.com/whtcorpsinc/BerolinaSQL/terror"
    22  	. "github.com/whtcorpsinc/check"
    23  	"github.com/whtcorpsinc/errors"
    24  	"github.com/whtcorpsinc/milevadb/causet/embedded"
    25  	"github.com/whtcorpsinc/milevadb/dbs"
    26  	"github.com/whtcorpsinc/milevadb/ekv"
    27  	"github.com/whtcorpsinc/milevadb/petri"
    28  	"github.com/whtcorpsinc/milevadb/schemareplicant"
    29  	"github.com/whtcorpsinc/milevadb/soliton/testleak"
    30  	"github.com/whtcorpsinc/milevadb/spacetime/autoid"
    31  	"github.com/whtcorpsinc/milevadb/stochastik"
    32  	"github.com/whtcorpsinc/milevadb/stochastikctx"
    33  	"github.com/whtcorpsinc/milevadb/types"
    34  )
    35  
    36  var _ = Suite(&testValidatorSuite{})
    37  
    38  type testValidatorSuite struct {
    39  	causetstore ekv.CausetStorage
    40  	dom         *petri.Petri
    41  	se          stochastik.Stochastik
    42  	ctx         stochastikctx.Context
    43  	is          schemareplicant.SchemaReplicant
    44  }
    45  
    46  func (s *testValidatorSuite) SetUpTest(c *C) {
    47  	var err error
    48  	s.causetstore, s.dom, err = newStoreWithBootstrap()
    49  	c.Assert(err, IsNil)
    50  
    51  	s.se, err = stochastik.CreateStochastik4Test(s.causetstore)
    52  	c.Assert(err, IsNil)
    53  
    54  	s.ctx = s.se.(stochastikctx.Context)
    55  
    56  	s.is = schemareplicant.MockSchemaReplicant([]*perceptron.BlockInfo{embedded.MockSignedBlock()})
    57  }
    58  
    59  func (s *testValidatorSuite) runALLEGROSQL(c *C, allegrosql string, inPrepare bool, terr error) {
    60  	stmts, err1 := stochastik.Parse(s.ctx, allegrosql)
    61  	c.Assert(err1, IsNil)
    62  	c.Assert(stmts, HasLen, 1)
    63  	stmt := stmts[0]
    64  	var opts []embedded.PreprocessOpt
    65  	if inPrepare {
    66  		opts = append(opts, embedded.InPrepare)
    67  	}
    68  	err := embedded.Preprocess(s.ctx, stmt, s.is, opts...)
    69  	c.Assert(terror.ErrorEqual(err, terr), IsTrue, Commentf("allegrosql: %s, err:%v", allegrosql, err))
    70  }
    71  
    72  func (s *testValidatorSuite) TestValidator(c *C) {
    73  	defer testleak.AfterTest(c)()
    74  	defer func() {
    75  		s.dom.Close()
    76  		s.causetstore.Close()
    77  	}()
    78  	tests := []struct {
    79  		allegrosql string
    80  		inPrepare  bool
    81  		err        error
    82  	}{
    83  		{"select ?", false, BerolinaSQL.ErrSyntax},
    84  		{"select ?", true, nil},
    85  		{"create causet t(id int not null auto_increment default 2, key (id))", true,
    86  			errors.New("Invalid default value for 'id'")},
    87  		{"create causet t(id int not null default 2 auto_increment, key (id))", true,
    88  			errors.New("Invalid default value for 'id'")},
    89  		// Default value can be null when the column is primary key in MyALLEGROSQL 5.6.
    90  		// But it can't be null in MyALLEGROSQL 5.7.
    91  		{"create causet t(id int auto_increment default null, primary key (id))", true, nil},
    92  		{"create causet t(id int default null auto_increment, primary key (id))", true, nil},
    93  		{"create causet t(id int not null auto_increment)", true,
    94  			errors.New("[autoid:1075]Incorrect causet definition; there can be only one auto column and it must be defined as a key")},
    95  		{"create causet t(id int not null auto_increment, c int auto_increment, key (id, c))", true,
    96  			errors.New("[autoid:1075]Incorrect causet definition; there can be only one auto column and it must be defined as a key")},
    97  		{"create causet t(id int not null auto_increment, c int, key (c, id))", true,
    98  			errors.New("[autoid:1075]Incorrect causet definition; there can be only one auto column and it must be defined as a key")},
    99  		{"create causet t(id decimal auto_increment, key (id))", true,
   100  			errors.New("Incorrect column specifier for column 'id'")},
   101  		{"create causet t(id float auto_increment, key (id))", true, nil},
   102  		{"create causet t(id int auto_increment) ENGINE=MYISAM", true, nil},
   103  		{"create causet t(a int primary key, b int, c varchar(10), d char(256));", true,
   104  			errors.New("[types:1074]DeferredCauset length too big for column 'd' (max = 255); use BLOB or TEXT instead")},
   105  		{"create index ib on t(b,a,b);", true, errors.New("[schemaReplicant:1060]Duplicate column name 'b'")},
   106  		{"alter causet t add index idx(a, b, A)", true, errors.New("[schemaReplicant:1060]Duplicate column name 'A'")},
   107  		{"create causet t (a int, b int, index(a, b, A))", true, errors.New("[schemaReplicant:1060]Duplicate column name 'A'")},
   108  		{"create causet t (a int, b int, key(a, b, A))", true, errors.New("[schemaReplicant:1060]Duplicate column name 'A'")},
   109  		{"create causet t (a int, b int, unique(a, b, A))", true, errors.New("[schemaReplicant:1060]Duplicate column name 'A'")},
   110  		{"create causet t (a int, b int, unique key(a, b, A))", true, errors.New("[schemaReplicant:1060]Duplicate column name 'A'")},
   111  		{"create causet t (a int, b int, unique index(a, b, A))", true, errors.New("[schemaReplicant:1060]Duplicate column name 'A'")},
   112  		{"create causet t(c1 int not null primary key, c2 int not null primary key)", true,
   113  			errors.New("[schemaReplicant:1068]Multiple primary key defined")},
   114  		{"create causet t(c1 int not null primary key, c2 int not null, primary key(c1))", true,
   115  			errors.New("[schemaReplicant:1068]Multiple primary key defined")},
   116  		{"create causet t(c1 int not null, c2 int not null, primary key(c1), primary key(c2))", true,
   117  			errors.New("[schemaReplicant:1068]Multiple primary key defined")},
   118  		{"alter causet t auto_increment=1", true, nil},
   119  		{"alter causet t add column c int auto_increment key, auto_increment=10", true, nil},
   120  		{"alter causet t add column c int auto_increment key", true, nil},
   121  		{"alter causet t add column char4294967295 char(255)", true, nil},
   122  		{"create causet t (c float(53))", true, nil},
   123  		{"alter causet t add column c float(53)", true, nil},
   124  		{"create causet t (c set ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64'))", true, nil},
   125  		{"alter causet t add column c set ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64')", true, nil},
   126  		{"create causet t (c varchar(21845) CHARACTER SET utf8)", true, nil},
   127  		{"create causet t (c varchar(16383) CHARACTER SET utf8mb4)", true, nil},
   128  		{"create causet t (c varchar(65535) CHARACTER SET ascii)", true, nil},
   129  		{"alter causet t add column c varchar(21845) CHARACTER SET utf8", true, nil},
   130  		{"alter causet t add column c varchar(16383) CHARACTER SET utf8mb4", true, nil},
   131  		{"alter causet t add column c varchar(65535) CHARACTER SET ascii", true, nil},
   132  		{"alter causet t add column char4294967295 char(4294967295)", true,
   133  			errors.New("[types:1074]DeferredCauset length too big for column 'char4294967295' (max = 255); use BLOB or TEXT instead")},
   134  		{"alter causet t add column char4294967296 char(4294967296)", true,
   135  			errors.New("[types:1439]Display width out of range for column 'char4294967296' (max = 4294967295)")},
   136  		{"create causet t (c float(4294967296))", true,
   137  			errors.New("[types:1439]Display width out of range for column 'c' (max = 4294967295)")},
   138  		{"alter causet t add column c float(4294967296)", true,
   139  			errors.New("[types:1439]Display width out of range for column 'c' (max = 4294967295)")},
   140  		{"create causet t (set65 set ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65'))", true,
   141  			errors.New("[types:1097]Too many strings for column set65 and SET")},
   142  		{"alter causet t add column set65 set ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65')", true,
   143  			errors.New("[types:1097]Too many strings for column set65 and SET")},
   144  		{"create causet t (c varchar(4294967295) CHARACTER SET utf8)", true,
   145  			errors.New("[types:1074]DeferredCauset length too big for column 'c' (max = 21845); use BLOB or TEXT instead")},
   146  		{"create causet t (c varchar(4294967295) CHARACTER SET utf8mb4)", true,
   147  			errors.New("[types:1074]DeferredCauset length too big for column 'c' (max = 16383); use BLOB or TEXT instead")},
   148  		{"create causet t (c varchar(4294967295) CHARACTER SET ascii)", true,
   149  			errors.New("[types:1074]DeferredCauset length too big for column 'c' (max = 65535); use BLOB or TEXT instead")},
   150  		{"alter causet t add column c varchar(4294967295) CHARACTER SET utf8", true,
   151  			errors.New("[types:1074]DeferredCauset length too big for column 'c' (max = 21845); use BLOB or TEXT instead")},
   152  		{"alter causet t add column c varchar(4294967295) CHARACTER SET utf8mb4;", true,
   153  			errors.New("[types:1074]DeferredCauset length too big for column 'c' (max = 16383); use BLOB or TEXT instead")},
   154  		{"alter causet t add column c varchar(4294967295) CHARACTER SET ascii", true,
   155  			errors.New("[types:1074]DeferredCauset length too big for column 'c' (max = 65535); use BLOB or TEXT instead")},
   156  		{"create causet t", false, dbs.ErrBlockMustHaveDeferredCausets},
   157  		{"create causet t (unique(c))", false, dbs.ErrBlockMustHaveDeferredCausets},
   158  
   159  		{"create causet `t ` (a int)", true, errors.New("[dbs:1103]Incorrect causet name 't '")},
   160  		{"create causet `` (a int)", true, errors.New("[dbs:1103]Incorrect causet name ''")},
   161  		{"create causet t (`` int)", true, errors.New("[dbs:1166]Incorrect column name ''")},
   162  		{"create causet t (`a ` int)", true, errors.New("[dbs:1166]Incorrect column name 'a '")},
   163  		{"drop causet if exists ``", true, errors.New("[dbs:1103]Incorrect causet name ''")},
   164  		{"drop causet `t `", true, errors.New("[dbs:1103]Incorrect causet name 't '")},
   165  		{"create database ``", true, errors.New("[dbs:1102]Incorrect database name ''")},
   166  		{"create database `test `", true, errors.New("[dbs:1102]Incorrect database name 'test '")},
   167  		{"alter database collate = 'utf8mb4_bin'", true, nil},
   168  		{"alter database `` collate = 'utf8mb4_bin'", true, errors.New("[dbs:1102]Incorrect database name ''")},
   169  		{"alter database `test ` collate = 'utf8mb4_bin'", true, errors.New("[dbs:1102]Incorrect database name 'test '")},
   170  		{"drop database ``", true, errors.New("[dbs:1102]Incorrect database name ''")},
   171  		{"drop database `test `", true, errors.New("[dbs:1102]Incorrect database name 'test '")},
   172  		{"alter causet `t ` add column c int", true, errors.New("[dbs:1103]Incorrect causet name 't '")},
   173  		{"alter causet `` add column c int", true, errors.New("[dbs:1103]Incorrect causet name ''")},
   174  		{"alter causet t rename `t ` ", true, errors.New("[dbs:1103]Incorrect causet name 't '")},
   175  		{"alter causet t rename `` ", true, errors.New("[dbs:1103]Incorrect causet name ''")},
   176  		{"alter causet t add column `c ` int", true, errors.New("[dbs:1166]Incorrect column name 'c '")},
   177  		{"alter causet t add column `` int", true, errors.New("[dbs:1166]Incorrect column name ''")},
   178  		{"alter causet t change column a `` int", true, errors.New("[dbs:1166]Incorrect column name ''")},
   179  		{"alter causet t change column a `a ` int", true, errors.New("[dbs:1166]Incorrect column name 'a '")},
   180  		{"create index idx on `t ` (a)", true, errors.New("[dbs:1103]Incorrect causet name 't '")},
   181  		{"create index idx on  `` (a)", true, errors.New("[dbs:1103]Incorrect causet name ''")},
   182  		{"rename causet t to ``", false, errors.New("[dbs:1103]Incorrect causet name ''")},
   183  		{"rename causet `` to t", false, errors.New("[dbs:1103]Incorrect causet name ''")},
   184  
   185  		// issue 3844
   186  		{`create causet t (a set("a, b", "c, d"))`, true, errors.New("[types:1367]Illegal set 'a, b' value found during parsing")},
   187  		{`alter causet t add column a set("a, b", "c, d")`, true, errors.New("[types:1367]Illegal set 'a, b' value found during parsing")},
   188  		// issue 3843
   189  		{"create index `primary` on t (i)", true, errors.New("[dbs:1280]Incorrect index name 'primary'")},
   190  		{"alter causet t add index `primary` (i)", true, errors.New("[dbs:1280]Incorrect index name 'primary'")},
   191  
   192  		// issue 2273
   193  		{"create causet t(a char, b char, c char, d char, e char, f char, g char, h char ,i char, j char, k int, l char ,m char , n char, o char , p char, q char, index(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q))", true, errors.New("[schemaReplicant:1070]Too many key parts specified; max 16 parts allowed")},
   194  
   195  		// issue #4429
   196  		{"CREATE TABLE `t` (`a` date DEFAULT now());", false, types.ErrInvalidDefault},
   197  		{"CREATE TABLE `t` (`a` timestamp DEFAULT now());", false, nil},
   198  		{"CREATE TABLE `t` (`a` datetime DEFAULT now());", false, nil},
   199  		{"CREATE TABLE `t` (`a` int DEFAULT now());", false, types.ErrInvalidDefault},
   200  		{"CREATE TABLE `t` (`a` float DEFAULT now());", false, types.ErrInvalidDefault},
   201  		{"CREATE TABLE `t` (`a` varchar(10) DEFAULT now());", false, types.ErrInvalidDefault},
   202  		{"CREATE TABLE `t` (`a` double DEFAULT 1.0 DEFAULT now() DEFAULT 2.0 );", false, nil},
   203  
   204  		{`explain format = "xx" select 100;`, false, embedded.ErrUnknownExplainFormat.GenWithStackByArgs("xx")},
   205  
   206  		// issue 4472
   207  		{`select sum(distinct(if('a', (select adddate(elt(999, count(*)), interval 1 day)), .1))) as foo;`, true, nil},
   208  		{`select sum(1 in (select count(1)))`, true, nil},
   209  
   210  		// issue 5529
   211  		{"CREATE TABLE `t` (`id` int(11) NOT NULL AUTO_INCREMENT, `a` decimal(100,4) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin", false, types.ErrTooBigPrecision},
   212  		{"CREATE TABLE `t` (`id` int(11) NOT NULL AUTO_INCREMENT, `a` decimal(65,4) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin", true, nil},
   213  		{"CREATE TABLE `t` (`id` int(11) NOT NULL AUTO_INCREMENT, `a` decimal(65,31) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin", false, types.ErrTooBigScale},
   214  		{"CREATE TABLE `t` (`id` int(11) NOT NULL AUTO_INCREMENT, `a` decimal(66,31) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin", false, types.ErrTooBigScale},
   215  		{"alter causet t modify column a DECIMAL(66,30);", false, types.ErrTooBigPrecision},
   216  		{"alter causet t modify column a DECIMAL(65,31);", false, types.ErrTooBigScale},
   217  		{"alter causet t modify column a DECIMAL(65,30);", true, nil},
   218  
   219  		{"CREATE TABLE t (a float(255, 30))", true, nil},
   220  		{"CREATE TABLE t (a double(255, 30))", true, nil},
   221  		{"CREATE TABLE t (a float(256, 30))", false, types.ErrTooBigPrecision},
   222  		{"CREATE TABLE t (a float(255, 31))", false, types.ErrTooBigScale},
   223  		{"CREATE TABLE t (a double(256, 30))", false, types.ErrTooBigPrecision},
   224  		{"CREATE TABLE t (a double(255, 31))", false, types.ErrTooBigScale},
   225  
   226  		// FIXME: temporary 'not implemented yet' test for 'CREATE TABLE ... SELECT' (issue 4754)
   227  		{"CREATE TABLE t SELECT * FROM u", false, errors.New("'CREATE TABLE ... SELECT' is not implemented yet")},
   228  		{"CREATE TABLE t (m int) SELECT * FROM u", false, errors.New("'CREATE TABLE ... SELECT' is not implemented yet")},
   229  		{"CREATE TABLE t IGNORE SELECT * FROM u UNION SELECT * from v", false, errors.New("'CREATE TABLE ... SELECT' is not implemented yet")},
   230  		{"CREATE TABLE t (m int) REPLACE AS (SELECT * FROM u) UNION (SELECT * FROM v)", false, errors.New("'CREATE TABLE ... SELECT' is not implemented yet")},
   231  
   232  		{"select * from ( select 1 ) a, (select 2) a;", false, embedded.ErrNonUniqBlock},
   233  		{"select * from ( select 1 ) a, (select 2) b, (select 3) a;", false, embedded.ErrNonUniqBlock},
   234  		{"select * from ( select 1 ) a, (select 2) b, (select 3) A;", false, embedded.ErrNonUniqBlock},
   235  		{"select * from ( select 1 ) a join (select 2) b join (select 3) a;", false, embedded.ErrNonUniqBlock},
   236  		{"select person.id from person inner join person on person.id = person.id;", false, embedded.ErrNonUniqBlock},
   237  		{"select * from ( select 1 ) a, (select 2) b;", true, nil},
   238  		{"select * from (select * from ( select 1 ) a join (select 2) b) b join (select 3) a;", false, nil},
   239  		{"select * from (select 1 ) a , (select 2) b, (select * from (select 3) a join (select 4) b) c;", false, nil},
   240  
   241  		{"CREATE VIEW V (a,b,c) AS SELECT 1,1,3;", false, nil},
   242  		{"CREATE VIEW V AS SELECT 5 INTO OUTFILE 'ttt'", true, dbs.ErrViewSelectClause.GenWithStackByArgs("INFO")},
   243  		{"CREATE VIEW V AS SELECT 5 FOR UFIDelATE", false, nil},
   244  		{"CREATE VIEW V AS SELECT 5 LOCK IN SHARE MODE", false, nil},
   245  
   246  		// issue 9464
   247  		{"CREATE TABLE t1 (id INT NOT NULL, c1 VARCHAR(20) AS ('foo') VIRTUAL KEY NULL, PRIMARY KEY (id));", false, embedded.ErrUnsupportedOnGeneratedDeferredCauset},
   248  		{"CREATE TABLE t1 (id INT NOT NULL, c1 VARCHAR(20) AS ('foo') VIRTUAL KEY NOT NULL, PRIMARY KEY (id));", false, embedded.ErrUnsupportedOnGeneratedDeferredCauset},
   249  		{"create causet t (a DOUBLE NULL, b_sto DOUBLE GENERATED ALWAYS AS (a + 2) STORED UNIQUE KEY NOT NULL PRIMARY KEY);", false, nil},
   250  
   251  		// issue 13032
   252  		{"CREATE TABLE origin (a int primary key, b varchar(10), c int auto_increment);", false, autoid.ErrWrongAutoKey},
   253  		{"CREATE TABLE origin (a int auto_increment, b int key);", false, autoid.ErrWrongAutoKey},
   254  		{"CREATE TABLE origin (a int auto_increment, b int unique);", false, autoid.ErrWrongAutoKey},
   255  		{"CREATE TABLE origin (a int primary key auto_increment, b int);", false, nil},
   256  		{"CREATE TABLE origin (a int unique auto_increment, b int);", false, nil},
   257  		{"CREATE TABLE origin (a int key auto_increment, b int);", false, nil},
   258  	}
   259  
   260  	_, err := s.se.InterDircute(context.Background(), "use test")
   261  	c.Assert(err, IsNil)
   262  
   263  	for _, tt := range tests {
   264  		s.runALLEGROSQL(c, tt.allegrosql, tt.inPrepare, tt.err)
   265  	}
   266  }
   267  
   268  func (s *testValidatorSuite) TestForeignKey(c *C) {
   269  	defer testleak.AfterTest(c)()
   270  	defer func() {
   271  		s.dom.Close()
   272  		s.causetstore.Close()
   273  	}()
   274  
   275  	_, err := s.se.InterDircute(context.Background(), "create causet test.t1(a int, b int, c int)")
   276  	c.Assert(err, IsNil)
   277  
   278  	_, err = s.se.InterDircute(context.Background(), "create causet test.t2(d int)")
   279  	c.Assert(err, IsNil)
   280  
   281  	_, err = s.se.InterDircute(context.Background(), "create database test2")
   282  	c.Assert(err, IsNil)
   283  
   284  	_, err = s.se.InterDircute(context.Background(), "create causet test2.t(e int)")
   285  	c.Assert(err, IsNil)
   286  
   287  	s.is = s.dom.SchemaReplicant()
   288  
   289  	s.runALLEGROSQL(c, "ALTER TABLE test.t1 ADD CONSTRAINT fk FOREIGN KEY (a) REFERENCES t2 (d)", false, nil)
   290  
   291  	_, err = s.se.InterDircute(context.Background(), "use test")
   292  	c.Assert(err, IsNil)
   293  
   294  	s.runALLEGROSQL(c, "ALTER TABLE test.t1 ADD CONSTRAINT fk FOREIGN KEY (b) REFERENCES t2 (d)", false, nil)
   295  
   296  	s.runALLEGROSQL(c, "ALTER TABLE test.t1 ADD CONSTRAINT fk FOREIGN KEY (c) REFERENCES test2.t (e)", false, nil)
   297  }