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 }