-- source include/have_bdb.inc # # Small basic test with ignore # drop table if exists t1,t2,t3,t4,t5,t6,t7,t8; 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)) type=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) ) type=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; explain select level from t1 where level=1; 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; # # 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) ) type=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)) type=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) 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; 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)) type=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)) type=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) type=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)) type=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) type=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)) TYPE=BDB; INSERT INTO t1 VALUES (1, 'Jochen'); select * from t1; drop table t1; CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) TYPE=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) )type=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 type=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)) type = BDB; create table t2 (a int not null , b int, primary key (a)) type = 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) ) TYPE=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) ) TYPE=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) ) type=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; 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) ) 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'),( '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)) 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))) type=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) type=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 )TYPE=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 # drop table if exists t1, t2, t3, t4, t5, t6, t7; 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) ) type=bdb; drop table if exists t2 ; 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) ) type=bdb; drop table if exists t3 ; 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) ) type=bdb; drop table if exists t4 ; 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) ) type=bdb; drop table if exists t5 ; 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) ) type=bdb; drop table if exists t6 ; 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) ) type=bdb; drop table if exists t8 ; 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) ) type=bdb; drop table if exists t7 ; 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) ) type=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) ) TYPE=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) ) TYPE = 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)) type=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)) type=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)) TYPE=BerkeleyDB; CREATE TABLE t2 ( SYAIN_NO char(5) NOT NULL default '',STR_DATE char(8) NOT NULL default '',PRIMARY KEY (SYAIN_NO,STR_DATE) ) TYPE=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 # drop table if exists t1; create table t1 (a int(11) not null, b int(11) not null, unique (a,b)) type=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)) type=bdb; insert into t1 values (0,0,0,'ABCDEFGHIJ'); create table t2 (id int NOT NULL,primary key (id)) type=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)) TYPE=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) ) TYPE=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)) type =bdb; create table t2 (a int not null, b int, primary key (a)) type =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)) type=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 ) type=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)) type=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;