diff options
Diffstat (limited to 'mysql-test/t/bdb.test')
| -rw-r--r-- | mysql-test/t/bdb.test | 1022 |
1 files changed, 0 insertions, 1022 deletions
diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test deleted file mode 100644 index d017d91bfb1..00000000000 --- a/mysql-test/t/bdb.test +++ /dev/null @@ -1,1022 +0,0 @@ --- source include/have_bdb.inc - -# -# Small basic test with ignore -# - ---disable_warnings -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8; ---enable_warnings -create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=bdb; - -insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt'); -select id, code, name from t1 order by id; - -update ignore t1 set id = 8, name = 'Sinisa' where id < 3; -select id, code, name from t1 order by id; -update ignore t1 set id = id + 10, name = 'Ralph' where id < 4; -select id, code, name from t1 order by id; - -drop table t1; - -# -# A bit bigger test -# - -CREATE TABLE t1 ( - id int(11) NOT NULL auto_increment, - parent_id int(11) DEFAULT '0' NOT NULL, - level tinyint(4) DEFAULT '0' NOT NULL, - PRIMARY KEY (id), - KEY parent_id (parent_id), - KEY level (level) -) engine=bdb; -INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2); -update t1 set parent_id=parent_id+100; -select * from t1 where parent_id=102; -update t1 set id=id+1000; --- error 1062 -update t1 set id=1024 where id=1009; -select * from t1; -update ignore t1 set id=id+1; # This will change all rows -select * from t1; -update ignore t1 set id=1023 where id=1010; -select * from t1 where parent_id=102 order by parent_id,id; -# Here and below the differences in result are caused by difference in -# floating point calculations performed in BDB handler. ---replace_result 5 X 6 X -explain select level from t1 where level=1; ---replace_result 5 X 6 X -explain select level,id from t1 where level=1; ---replace_result 5 X 6 X -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; - -# -# Test replace -# - -CREATE TABLE t1 ( - gesuchnr int(11) DEFAULT '0' NOT NULL, - benutzer_id int(11) DEFAULT '0' NOT NULL, - PRIMARY KEY (gesuchnr,benutzer_id) -) engine=BDB; - -replace into t1 (gesuchnr,benutzer_id) values (2,1); -replace into t1 (gesuchnr,benutzer_id) values (1,1); -replace into t1 (gesuchnr,benutzer_id) values (1,1); -select * from t1; -drop table t1; - -# test for bug in replace with secondary key -create table t1 (id int not null primary key, x int not null, key (x)) engine=bdb; -insert into t1 (id, x) values (1, 1); -replace into t1 (id, x) values (1, 2); -select * from t1; -drop table t1; - -# -# test delete using hidden_primary_key -# - -create table t1 (a int) engine=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)) engine=bdb; -insert into t1 values (1,""), (2,"testing"); -delete from t1 where a = 1; -select * from t1; -create index skr on t1 (a); -insert into t1 values (3,""), (4,"testing"); -analyze table t1; -show keys from t1; -drop table t1; - -# Test of reading on secondary key with may be null - -create table t1 (a int,b varchar(20),key(a)) engine=bdb; -insert into t1 values (1,""), (2,"testing"); -select * from t1 where a = 1; -drop table t1; - -# -# Test auto_increment on sub key -# - -create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b)) engine=BDB; -insert into t1 values ("a",1),("b",2),("a",2),("c",1); -insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL); -insert into t1 (a) values ("a"),("b"),("c"),("d"); -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; - -# -# Test rollback -# - -create table t1 (n int not null primary key) engine=bdb; -set autocommit=0; -insert into t1 values (4); -rollback; -select n, "after rollback" from t1; -insert into t1 values (4); -commit; -select n, "after commit" from t1; -commit; -insert into t1 values (5); --- error 1062 -insert into t1 values (4); -commit; -select n, "after commit" from t1; -set autocommit=1; -insert into t1 values (6); --- error 1062 -insert into t1 values (4); -select n from t1; -# nop -rollback; -drop table t1; - -# -# Testing transactions -# - -create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=BDB; -begin; -insert into t1 values(1,'hamdouni'); -select id as afterbegin_id,nom as afterbegin_nom from t1; -rollback; -select id as afterrollback_id,nom as afterrollback_nom from t1; -set autocommit=0; -insert into t1 values(2,'mysql'); -select id as afterautocommit0_id,nom as afterautocommit0_nom from t1; -rollback; -select id as afterrollback_id,nom as afterrollback_nom from t1; -set autocommit=1; -drop table t1; - -# -# Simple not autocommit test -# - -CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=bdb; -insert into t1 values ('pippo', 12); --- error 1062 -insert into t1 values ('pippo', 12); # Gives error -delete from t1; -delete from t1 where id = 'pippo'; -select * from t1; - -insert into t1 values ('pippo', 12); -set autocommit=0; -delete from t1; -rollback; -select * from t1; -delete from t1; -commit; -select * from t1; -drop table t1; -set autocommit=1; - -# -# The following simple tests failed at some point -# - -CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=BDB; -INSERT INTO t1 VALUES (1, 'Jochen'); -select * from t1; -drop table t1; - -CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=BDB; -set autocommit=0; -INSERT INTO t1 SET _userid='marc@anyware.co.uk'; -COMMIT; -SELECT * FROM t1; -SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk'; -drop table t1; -set autocommit=1; - -# -# Test when reading on part of unique key -# -CREATE TABLE t1 ( - user_id int(10) DEFAULT '0' NOT NULL, - name varchar(100), - phone varchar(100), - ref_email varchar(100) DEFAULT '' NOT NULL, - detail varchar(200), - PRIMARY KEY (user_id,ref_email) -)engine=bdb; - -INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar'); -select * from t1 where user_id=10292; -INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds'); -select * from t1 where user_id=10292; -select * from t1 where user_id>=10292; -select * from t1 where user_id>10292; -select * from t1 where user_id<10292; -drop table t1; - -# -# Test that keys are created in right order -# - -CREATE TABLE t1 (a int not null, b int not null,c int not null, -key(a),primary key(a,b), unique(c),key(a),unique(b)); -show index from t1; -drop table t1; - -# -# Test of ALTER TABLE and BDB tables -# - -create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)); -alter table t1 engine=BDB; -insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4'); -select * from t1; -update t1 set col2='7' where col1='4'; -select * from t1; -alter table t1 add co3 int not null; -select * from t1; -update t1 set col2='9' where col1='2'; -select * from t1; -drop table t1; - -# -# INSERT INTO BDB tables -# - -create table t1 (a int not null , b int, primary key (a)) engine = BDB; -create table t2 (a int not null , b int, primary key (a)) engine = myisam; -insert into t1 VALUES (1,3) , (2,3), (3,3); -select * from t1; -insert into t2 select * from t1; -select * from t2; -delete from t1 where b = 3; -select * from t1; -insert into t1 select * from t2; -select * from t1; -select * from t2; -drop table t1,t2; - -# -# Search on unique key -# - -CREATE TABLE t1 ( - id int(11) NOT NULL auto_increment, - ggid varchar(32) binary DEFAULT '' NOT NULL, - email varchar(64) DEFAULT '' NOT NULL, - passwd varchar(32) binary DEFAULT '' NOT NULL, - PRIMARY KEY (id), - UNIQUE ggid (ggid) -) ENGINE=BDB; - -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; - -# -# ORDER BY on not primary key -# - -CREATE TABLE t1 ( - user_name varchar(12), - password text, - subscribed char(1), - user_id int(11) DEFAULT '0' NOT NULL, - quota bigint(20), - weight double, - access_date date, - access_time time, - approved datetime, - dummy_primary_key int(11) NOT NULL auto_increment, - PRIMARY KEY (dummy_primary_key) -) ENGINE=BDB; -INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1); -INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2); -INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3); -INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4); -INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5); -select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name; -drop table t1; - -# -# Testing of tables without primary keys -# - -CREATE TABLE t1 ( - id int(11) NOT NULL auto_increment, - parent_id int(11) DEFAULT '0' NOT NULL, - level tinyint(4) DEFAULT '0' NOT NULL, - KEY (id), - KEY parent_id (parent_id), - KEY level (level) -) engine=bdb; -INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1); -INSERT INTO t1 values (179,5,2); -update t1 set parent_id=parent_id+100; -select * from t1 where parent_id=102; -update t1 set id=id+1000; -update t1 set id=1024 where id=1009; -select * from t1; -update ignore t1 set id=id+1; # This will change all rows -select * from t1; -update ignore t1 set id=1023 where id=1010; -select * from t1 where parent_id=102; ---replace_result 5 X 6 X -explain select level from t1 where level=1; -select level,id from t1 where level=1; -select level,id,parent_id from t1 where level=1; -select level,id from t1 where level=1 order by id; -delete from t1 where level=1; -select * from t1; -drop table t1; - -# -# Test of index only reads -# -CREATE TABLE t1 ( - sca_code char(6) NOT NULL, - cat_code char(6) NOT NULL, - sca_desc varchar(50), - lan_code char(2) NOT NULL, - sca_pic varchar(100), - sca_sdesc varchar(50), - sca_sch_desc varchar(16), - PRIMARY KEY (sca_code, cat_code, lan_code), - INDEX sca_pic (sca_pic) -) engine = 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'),( 'QQ', 'N', '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; -select count(*) from t1 where cat_code='E'; - -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 and timestamps -# -set @a:=now(); -CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=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; -select a from t1 natural join t1 as t2 where b >= @a order by 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))) engine=bdb; -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) engine=bdb; -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; - -# -# Test of bug in create index with NULL columns -# -create table t1 (i int, j int )ENGINE=BDB; -insert into t1 values (1,2); -select * from t1 where i=1 and j=2; -create index ax1 on t1 (i,j); -select * from t1 where i=1 and j=2; -drop table t1; - -# -# Test of with CONST tables and TEXT columns -# This gave a wrong result because the row information was freed too early -# - -create table t1 -( - branch_id int auto_increment primary key, - branch_name varchar(255) not null, - branch_active int not null default 1, - - unique branch_name(branch_name), - index branch_active(branch_active) -) engine=bdb; -create table t2 -( - target_id int auto_increment primary key, - target_name varchar(255) not null, - target_active int not null default 1, - - unique target_name(target_name), - index target_active(target_active) -) engine=bdb; -create table t3 -( - platform_id int auto_increment primary key, - platform_name varchar(255) not null, - platform_active int not null default 1, - - unique platform_name(platform_name), - index platform_active(platform_active) -) engine=bdb; -create table t4 -( - product_id int auto_increment primary key, - product_name varchar(255) not null, - version_file varchar(255) not null, - product_active int not null default 1, - - unique product_name(product_name), - index product_active(product_active) -) engine=bdb; -create table t5 -( - product_file_id int auto_increment primary key, - product_id int not null, - file_name varchar(255) not null, - /* cvs module used to find the file version */ - module_name varchar(255) not null, - /* flag whether the file is still included in the product */ - file_included int not null default 1, - - unique product_file(product_id,file_name), - index file_included(file_included) -) engine=bdb; -create table t6 -( - file_platform_id int auto_increment primary key, - product_file_id int not null, - platform_id int not null, - branch_id int not null, - /* filename in the build system */ - build_filename varchar(255) not null, - /* default filename in the build archive */ - archive_filename varchar(255) not null, - - unique file_platform(product_file_id,platform_id,branch_id) -) engine=bdb; -create table t8 -( - archive_id int auto_increment primary key, - branch_id int not null, - target_id int not null, - platform_id int not null, - product_id int not null, - status_id int not null default 1, - - unique archive(branch_id,target_id,platform_id,product_id), - index status_id(status_id) -) engine=bdb; -create table t7 -( - build_id int auto_increment primary key, - branch_id int not null, - target_id int not null, - build_number int not null, - build_date date not null, - /* build system tag, e.g. 'rmanight-022301-1779' */ - build_tag varchar(255) not null, - /* path relative to the build archive root, e.g. 'current' */ - build_path text not null, - - unique build(branch_id,target_id,build_number) -) engine=bdb; - -insert into t1 (branch_name) -values ('RealMedia'); -insert into t1 (branch_name) -values ('RP8REV'); -insert into t1 (branch_name) -values ('SERVER_8_0_GOLD'); - -insert into t2 (target_name) -values ('rmanight'); -insert into t2 (target_name) -values ('playerall'); -insert into t2 (target_name) -values ('servproxyall'); - -insert into t3 (platform_name) -values ('linux-2.0-libc6-i386'); -insert into t3 (platform_name) -values ('win32-i386'); - -insert into t4 (product_name, version_file) -values ('realserver', 'servinst'); -insert into t4 (product_name, version_file) -values ('realproxy', 'prxyinst'); -insert into t4 (product_name, version_file) -values ('realplayer', 'playinst'); -insert into t4 (product_name, version_file) -values ('plusplayer', 'plusinst'); - -create temporary table tmp1 - select branch_id, target_id, platform_id, product_id - from t1, t2, t3, t4 ; -create temporary table tmp2 - select tmp1.branch_id, tmp1.target_id, tmp1.platform_id, tmp1.product_id - from tmp1 left join t8 - using (branch_id,target_id,platform_id,product_id) - where t8.archive_id is null ; -insert into t8 - (branch_id, target_id, platform_id, product_id, status_id) - select branch_id, target_id, platform_id, product_id, 1 - from tmp2 ; -drop table tmp1 ; -drop table tmp2 ; - -insert into t5 (product_id, file_name, module_name) -values (1, 'servinst', 'server'); - -insert into t5 (product_id, file_name, module_name) -values (2, 'prxyinst', 'server'); - -insert into t5 (product_id, file_name, module_name) -values (3, 'playinst', 'rpapp'); - -insert into t5 (product_id, file_name, module_name) -values (4, 'plusinst', 'rpapp'); - -insert into t6 -(product_file_id,platform_id,branch_id,build_filename,archive_filename) -values (1, 2, 3, 'servinst.exe', 'win32-servinst.exe'); - -insert into t6 -(product_file_id,platform_id,branch_id,build_filename,archive_filename) -values (1, 1, 3, 'v80_linux-2.0-libc6-i386_servinst.bin', 'linux2-servinst.exe'); - -insert into t6 -(product_file_id,platform_id,branch_id,build_filename,archive_filename) -values (3, 2, 2, 'playinst.exe', 'win32-playinst.exe'); - -insert into t6 -(product_file_id,platform_id,branch_id,build_filename,archive_filename) -values (4, 2, 2, 'playinst.exe', 'win32-playinst.exe'); - -insert into t7 -(branch_id,target_id,build_number,build_tag,build_date,build_path) -values (2, 2, 1071, 'playerall-022101-1071', '2001-02-21', 'current'); - -insert into t7 -(branch_id,target_id,build_number,build_tag,build_date,build_path) -values (2, 2, 1072, 'playerall-022201-1072', '2001-02-22', 'current'); - -insert into t7 -(branch_id,target_id,build_number,build_tag,build_date,build_path) -values (3, 3, 388, 'servproxyall-022201-388', '2001-02-22', 'current'); - -insert into t7 -(branch_id,target_id,build_number,build_tag,build_date,build_path) -values (3, 3, 389, 'servproxyall-022301-389', '2001-02-23', 'current'); - -insert into t7 -(branch_id,target_id,build_number,build_tag,build_date,build_path) -values (4, 4, 100, 'foo target-010101-100', '2001-01-01', 'current'); - -update t8 -set status_id=2 -where branch_id=2 and target_id=2 and platform_id=2 and product_id=1; - -select t7.build_path -from - t1, - t7, - t2, - t3, - t4, - t5, - t6 -where - t7.branch_id = t1.branch_id and - t7.target_id = t2.target_id and - t5.product_id = t4.product_id and - t6.product_file_id = t5.product_file_id and - t6.platform_id = t3.platform_id and - t6.branch_id = t6.branch_id and - t7.build_id = 1 and - t4.product_id = 3 and - t5.file_name = 'playinst' and - t3.platform_id = 2; - -drop table t1, t2, t3, t4, t5, t6, t7, t8; - -# -# Test with blob + tinyint key -# - -CREATE TABLE t1 ( - a tinytext NOT NULL, - b tinyint(3) unsigned NOT NULL default '0', - PRIMARY KEY (a(32),b) -) ENGINE=BDB; -INSERT INTO t1 VALUES ('a',1),('a',2); -SELECT * FROM t1 WHERE a='a' AND b=2; -SELECT * FROM t1 WHERE a='a' AND b in (2); -SELECT * FROM t1 WHERE a='a' AND b in (1,2); -drop table t1; - -# -# Test min-max optimization -# - -CREATE TABLE t1 ( - a int3 unsigned NOT NULL, - b int1 unsigned NOT NULL, - UNIQUE (a, b) -) ENGINE = BDB; - -INSERT INTO t1 VALUES (1, 1); -SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1; -drop table t1; - -# -# Test problem with BDB and lock tables with duplicate write. -# - -create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=bdb; -insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); -LOCK TABLES t1 WRITE; ---error 1062 -insert into t1 values (99,1,2,'D'),(1,1,2,'D'); -select id from t1; -select id from t1; -UNLOCK TABLES; -DROP TABLE t1; - -create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=bdb; -insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); -LOCK TABLES t1 WRITE; -begin; ---error 1062 -insert into t1 values (99,1,2,'D'),(1,1,2,'D'); -select id from t1; -insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D'); -commit; -select id,id3 from t1; -UNLOCK TABLES; -DROP TABLE t1; - -# -# Test with empty tables (crashed with lock error) -# - -CREATE TABLE t1 (SYAIN_NO char(5) NOT NULL default '', KINMU_DATE char(6) NOT NULL default '', PRIMARY KEY (SYAIN_NO,KINMU_DATE)) ENGINE=BerkeleyDB; -CREATE TABLE t2 ( SYAIN_NO char(5) NOT NULL default '',STR_DATE char(8) NOT NULL default '',PRIMARY KEY (SYAIN_NO,STR_DATE) ) ENGINE=BerkeleyDB; -select T1.KINMU_DATE from t1 T1 ,t2 T2 where T1.SYAIN_NO = '12345' and T1.KINMU_DATE = '200106' and T2.SYAIN_NO = T1.SYAIN_NO; -select T1.KINMU_DATE from t1 T1 ,t2 T2 where T1.SYAIN_NO = '12345' and T1.KINMU_DATE = '200106' and T2.SYAIN_NO = T1.SYAIN_NO; -DROP TABLE t1,t2; - -# -# Test problem with joining table to itself on a multi-part unique key -# - -create table t1 (a int(11) not null, b int(11) not null, unique (a,b)) engine=bdb; -insert into t1 values (1,1), (1,2); -select * from t1 where a = 1; -select t1.*, t2.* from t1, t1 t2 where t1.a = t2.a and t2.a = 1; -select * from t1 where a = 1; -drop table t1; - -# -# This caused a deadlock in BDB internal locks -# - -create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=bdb; -insert into t1 values (0,0,0,'ABCDEFGHIJ'); -create table t2 (id int NOT NULL,primary key (id)) engine=bdb; -LOCK TABLES t1 WRITE, t2 WRITE; -insert into t2 values(1); -SELECT t1.* FROM t1 WHERE id IN (1); -SELECT t1.* FROM t2 left outer join t1 on (t1.id=t2.id); -delete from t1 where id3 >= 0 and id3 <= 0; -drop table t1,t2; - -# -# Test problems with NULL -# - -CREATE TABLE t1 (i varchar(48) NOT NULL default '', p varchar(255) default NULL,s varchar(48) NOT NULL default '', PRIMARY KEY (i), UNIQUE(p,s)) ENGINE=BDB; -INSERT INTO t1 VALUES ('00000000-e6c4ddeaa6-003b8-83458387','programs/xxxxxxxx.wmv','00000000-e6c4ddeb32-003bc-83458387'); -SELECT * FROM t1 WHERE p='programs/xxxxxxxx.wmv'; -drop table t1; - -# -# Test problem which gave error 'Can't find record in 't1'' -# - -CREATE TABLE t1 ( STR_DATE varchar(8) NOT NULL default '',INFO_NOTE varchar(200) default NULL,PRIMARY KEY (STR_DATE) ) ENGINE=BerkeleyDB; -select INFO_NOTE from t1 where STR_DATE = '20010610'; -select INFO_NOTE from t1 where STR_DATE < '20010610'; -select INFO_NOTE from t1 where STR_DATE > '20010610'; -drop table t1; - -# -# Test problem with multi table delete which quickly shows up with bdb tables. -# - -create table t1 (a int not null, b int, primary key (a)) engine =bdb; -create table t2 (a int not null, b int, primary key (a)) engine =bdb; -insert into t1 values (2, 3),(1, 7),(10, 7); -insert into t2 values (2, 3),(1, 7),(10, 7); -select * from t1; -select * from t2; -delete t1, t2 from t1, t2 where t1.a = t2.a; -select * from t1; -select * from t2; -select * from t2; -drop table t1,t2; - -# -# The bug #971 -# - -create table t1 (x int not null, index(x)) engine=bdb; -insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); -select * from t1 where x <= 10 and x >= 7; -select * from t1 where x <= 10 and x >= 7 order by x; -select * from t1 where x <= 10 and x >= 7 order by x desc; -select * from t1 where x <= 8 and x >= 5 order by x desc; -select * from t1 where x < 8 and x > 5 order by x desc; -drop table t1; - -# -# Test of multi-table-updates (bug #1980). -# - -create table t1 ( c char(8) not null ) engine=bdb; -insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); -insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); - -alter table t1 add b char(8) not null; -alter table t1 add a char(8) not null; -alter table t1 add primary key (a,b,c); -update t1 set a=c, b=c; - -create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=bdb; -insert into t2 select * from t1; - -delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; -drop table t1,t2; - -# -# Test index only read (Bug #2509) -# -create table t1 (a char(10), key(a), b int not null, key(b)) engine=bdb; -insert into t1 values ('a',1),('A',2); -explain select a from t1; -select a from t1; -explain select b from t1; -select b from t1; -alter table t1 modify a char(10) binary; -explain select a from t1; -select a from t1; -drop table t1; - -# -# bug#2686 - index_merge select on BerkeleyDB table with varchar PK crashes -# - -create table t1( - pk1 text not null, pk2 text not null, pk3 char(4), - key1 int, key2 int, - primary key(pk1(4), pk2(4), pk3), key(key1), key(key2) -) engine=bdb; -insert into t1 values (concat('aaa-', repeat('A', 4000)), - concat('eee-', repeat('e', 4000)), 'a++a', 1, 1); -insert into t1 values (concat('bbb-', repeat('B', 4000)), - concat('ggg-', repeat('G', 4000)), 'b++b', 1, 1); -select substring(pk1, 1, 4), substring(pk1, 4001), - substring(pk2, 1, 4), substring(pk2, 4001), pk3, key1, key2 - from t1 force index(key1, key2) where key1 < 3 or key2 < 3; -drop table t1; - -# -# bug#2688 - Wrong index_merge query results for BDB table with -# variable length primary key -# - -create table t1 ( - pk1 varchar(8) not null default '', - pk2 varchar(4) not null default '', - key1 int(11) default null, - key2 int(11) default null, - primary key (pk1,pk2), - key key1 (key1), - key key2 (key2)) engine=bdb; -insert into t1 values ('','empt',2,2), ('a','a--a',2,2), - ('bb','b--b',2,2), ('ccc','c--c',2,2), ('dddd','d--d',2,2); -select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; -drop table t1; - - -# -# Bug #4000: problem with active cursor. -# - -set autocommit=0; -create table t1(b varchar(30)) engine=bdb; -insert into t1 values ('one'); -commit; -select b FROM t1 outer_table where -exists (select 'two' from t1 where 'two' = outer_table.b); -drop table t1; -set autocommit=1; - -# -# Bug #4089: subselect and open cursor. -# - -create table t1(a int primary key, b varchar(30)) engine=bdb; -insert into t1 values (1,'one'), (2,'two'), (3,'three'), (4,'four'); -create table t2 like t1; -insert t2 select * from t1; -select a from t1 where a in (select a from t2); -delete from t2; -insert into t2 (a, b) - select a, b from t1 where (a, b) in (select a, b from t1); -select * from t2; -drop table t1, t2; - -# -# Bug #4304: TRUNCATE <table of type BDB> , wrong result -# - -create table t1 (a int, b varchar(30), primary key(a)) engine = bdb; -insert into t1 values (1,'one'); -commit; -truncate t1; -select * from t1; -drop table t1; - -# -# Check that BDB works fine with a string which is -# longer than 255 bytes for multibyte characters. -# -SET NAMES utf8; -create table t1 (a varchar(255) character set utf8) engine=bdb; -set @a:= convert(repeat(_latin1 0xFF, 255) using utf8); -insert into t1 values (@a); -select a, length(a), char_length(a) from t1; -drop table t1; -SET NAMES latin1; - -# -# Bug #5832 SELECT doesn't return records in some cases -# -CREATE TABLE t1 ( - id int unsigned NOT NULL auto_increment, - list_id smallint unsigned NOT NULL, - term TEXT NOT NULL, - PRIMARY KEY(id), - INDEX(list_id, term(4)) -) ENGINE=BDB CHARSET=utf8; -INSERT INTO t1 SET list_id = 1, term = "letterc"; -INSERT INTO t1 SET list_id = 1, term = "letterb"; -INSERT INTO t1 SET list_id = 1, term = "lettera"; -INSERT INTO t1 SET list_id = 1, term = "letterd"; -SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc"); -SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb"); -SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera"); -SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd"); -DROP TABLE t1; - -# -# Bug #15536: Crash when DELETE with subquery using BDB tables -# -create table t1 (a int, key(a)) engine=bdb; -create table t2 (b int, key(b)) engine=bdb; -insert into t1 values (1),(1),(2),(3),(4); -insert into t2 values (1),(5),(6),(7); -delete from t1 where (a in (select b from t2)); -select count(*) from t1; -# INSERT also blows up ---error 1242 -insert into t1 set a=(select b from t2); -select count(*) from t1; -# UPDATE also blows up -update t1 set a = a + 1 where (a in (select b from t2)); -select count(*) from t1; -drop table t1, t2; - ---echo End of 4.1 tests - -# -# alter temp table -# -create temporary table t1 (a int, primary key(a)) engine=bdb; -select * from t1; -alter table t1 add b int; -select * from t1; -drop table t1; - - -# -# Test varchar -# - -let $default=`select @@storage_engine`; -set storage_engine=bdb; -source include/varchar.inc; - -# -# Some errors/warnings on create -# - ---replace_result 1024 MAX_KEY_LENGTH 3072 MAX_KEY_LENGTH -create table t1 (v varchar(65530), key(v)); -drop table if exists t1; -create table t1 (v varchar(65536)); -show create table t1; -drop table t1; -create table t1 (v varchar(65530) character set utf8); -show create table t1; -drop table t1; - -# End varchar test -eval set storage_engine=$default; - -# -# Test that we can create a large key -# -create table t1 (a varchar(255) character set utf8, - b varchar(255) character set utf8, - c varchar(255) character set utf8, - d varchar(255) character set utf8, - key (a,b,c,d)) engine=bdb; -drop table t1; ---error ER_TOO_LONG_KEY -create table t1 (a varchar(255) character set utf8, - b varchar(255) character set utf8, - c varchar(255) character set utf8, - d varchar(255) character set utf8, - e varchar(255) character set utf8, - key (a,b,c,d,e)) engine=bdb; - -# -# Bug #14212: Server crash after COMMIT + ALTER TABLE -# -set autocommit=0; -create table t1 (a int) engine=bdb; -commit; -alter table t1 add primary key(a); -drop table t1; - ---echo End of 5.0 tests |