diff options
Diffstat (limited to 'mysql-test/t/bdb.test')
| -rw-r--r-- | mysql-test/t/bdb.test | 71 |
1 files changed, 64 insertions, 7 deletions
diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test index ae341aa5155..cf6d28c47b0 100644 --- a/mysql-test/t/bdb.test +++ b/mysql-test/t/bdb.test @@ -45,6 +45,8 @@ explain select level,id from t1 where level=1; explain select level,id,parent_id from t1 where level=1; select level,id from t1 where level=1; select level,id,parent_id from t1 where level=1; +optimize table t1; +show keys from t1; drop table t1; # @@ -69,6 +71,14 @@ drop table t1; create table t1 (a int) type=bdb; insert into t1 values (1), (2); +optimize table t1; +delete from t1 where a = 1; +select * from t1; +check table t1; +drop table t1; + +create table t1 (a int,b varchar(20)) type=bdb; +insert into t1 values (1,""), (2,"testing"); delete from t1 where a = 1; select * from t1; drop table t1; @@ -85,6 +95,8 @@ insert into t1 (a) values ('k'),('d'); insert into t1 (a) values ("a"); insert into t1 values ("d",last_insert_id()); select * from t1; +flush tables; +select count(*) from t1; drop table t1; # @@ -251,10 +263,22 @@ CREATE TABLE t1 ( insert into t1 (ggid,passwd) values ('test1','xxx'); insert into t1 (ggid,passwd) values ('test2','yyy'); +-- error 1062 +insert into t1 (ggid,passwd) values ('test2','this will fail'); +-- error 1062 +insert into t1 (ggid,id) values ('this will fail',1); select * from t1 where ggid='test1'; select * from t1 where passwd='xxx'; select * from t1 where id=2; + +replace into t1 (ggid,id) values ('this will work',1); +replace into t1 (ggid,passwd) values ('test2','this will work'); +-- error 1062 +update t1 set id=100,ggid='test2' where id=1; +select * from t1; +select * from t1 where id=1; +select * from t1 where id=999; drop table t1; # @@ -324,27 +348,60 @@ CREATE TABLE t1 ( sca_pic varchar(100), sca_sdesc varchar(50), sca_sch_desc varchar(16), - PRIMARY KEY (sca_code, cat_code, lan_code) + PRIMARY KEY (sca_code, cat_code, lan_code), + INDEX sca_pic (sca_pic) ) type = bdb ; -INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'); +INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'J', 'RING', 'EN', 'not null', NULL, 'RING'); select count(*) from t1 where sca_code = 'PD'; +select count(*) from t1 where sca_code <= 'PD'; +select count(*) from t1 where sca_pic is null; +alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic); +select count(*) from t1 where sca_code='PD' and sca_pic is null; +alter table t1 drop index sca_pic, add index (sca_pic, cat_code); +select count(*) from t1 where sca_code='PD' and sca_pic is null; +select count(*) from t1 where sca_pic >= 'n'; +select sca_pic from t1 where sca_pic is null; +update t1 set sca_pic="test" where sca_pic is null; +delete from t1 where sca_code='pd'; drop table t1; # -# Test of opening table twice +# Test of opening table twice and timestamps # -CREATE TABLE t1 (a int not null, primary key (a)) type=bdb; -insert into t1 values(1),(2),(3); -select t1.a from t1 natural join t1 as t2 order by t1.a; +set @a:=now(); +CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) type=bdb; +insert into t1 (a) values(1),(2),(3); +select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; +update t1 set a=5 where a=1; +select a from t1; drop table t1; # +# Test flushing of berkeley DB logs +# +flush logs; + +# # Test key on blob with null values # create table t1 (b blob, i int, key (b(100)), key (i), key (i, b(20))); -insert into t1 values ('this is a blob', 1), (null, -1), (null, null); +insert into t1 values ('this is a blob', 1), (null, -1), (null, null),("",1),("",2),("",3); select b from t1 where b = 'this is a blob'; select * from t1 where b like 't%'; select b, i from t1 where b is not null; select * from t1 where b is null and i > 0; +select * from t1 where i is NULL; +update t1 set b='updated' where i=1; +select * from t1; +drop table t1; + +# +# Test with variable length primary key +# +create table t1 (a varchar(100) not null, primary key(a), b int not null); +insert into t1 values("hello",1),("world",2); +select * from t1 order by b desc; +optimize table t1; +show keys from t1; +drop table t1; |