~drizzle-trunk/drizzle/development

# Test Routine for Indexing in BlitzDB

--disable_warnings
drop table if exists t1, t2;
--enable_warnings

# +------------------+
# | PRIMARY KEY: INT |
# +------------------+ 
create table t1 (id int primary key, a int, b int) engine = blitzdb;
select * from t1;
insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
insert into t1 values (5, 5, 5), (6, 6, 6), (7, 7, 7), (8, 8, 8);
insert into t1 values (9, 9, 9), (10, 10, 10), (11, 11, 11), (12, 12, 12);
insert into t1 values (13, 13, 13), (14, 14, 14), (15, 15, 15), (16, 16, 16);

# this statement should be 'optimized away'.
explain select count (*) from t1;

# overflow
--error ER_WARN_DATA_OUT_OF_RANGE
insert into t1 values (2147483648, 1, 1);

# duplicates 
--error ER_DUP_ENTRY
insert into t1 values (1, 0, 0);
--error ER_DUP_ENTRY
insert into t1 values (2, 0, 0);
--error ER_DUP_ENTRY
insert into t1 values (3, 0, 0);
--error ER_DUP_ENTRY
insert into t1 values (4, 0, 0);

# needle in a haystack 
select * from t1 where id = 2;
select * from t1 where id = 4;
select * from t1 where id = 6;

# update value by key
update t1 set a = 70 where id = 7;
update t1 set a = 80 where id = 8;
select * from t1 order by id;

# update the key
--error ER_DUP_ENTRY
update t1 set id = 2 where id = 1;

update t1 set id = 17 where id = 1;
update t1 set id = 18 where id = 2;
update t1 set id = 19 where id = 3;
update t1 set id = 20 where id = 4;
update t1 set id = 21 where id = 5;
update t1 set id = 22 where id = 6;
update t1 set id = 23 where id = 7;

--error ER_DUP_ENTRY
update t1 set id = 20 where id = 8;

# TODO: This is using filesort. Investigate why.
select * from t1 order by id;

# delete by pk
select count (*) from t1;
delete from t1 where id = 8;
select count (*) from t1;
delete from t1 where id = 9;
select count (*) from t1;
delete from t1 where id = 18;
select count (*) from t1;
delete from t1 where id = 19;
select count (*) from t1;
select count (id) from t1;
select count (a) from t1;
select count (b) from t1;
select * from t1 order by id;

# delete by pk range
delete from t1 where id > 10 and id < 14;
select * from t1 order by id;
select count (*) from t1;
delete from t1 where id >= 20 and id <= 23;
select * from t1 order by id;
select count (*) from t1;

# impossible range
delete from t1 where a > 20;
select count(*) from t1;

drop table t1;

# +---------------------+
# | PRIMARY KEY: BIGINT |
# +---------------------+
create table t1 (id bigint primary key, a int) engine = blitzdb;

# 32bit max
insert into t1 values (2147483647, 1);

# 64bit max
insert into t1 values (1.8e+18, 2);

# overflow
--error ER_WARN_DATA_OUT_OF_RANGE
insert into t1 values (1.8e+19, 3);

insert into t1 values (1,1), (2,2), (3,3), (4,4);

--error ER_DUP_ENTRY
update t1 set id = 4 where id = 1;

update t1 set id = 10 where id = 1;
update t1 set id = 20 where id = 2;
update t1 set id = 30 where id = 3;
update t1 set id = 40 where id = 4;

select count(*) from t1 where id < 10;
explain select id from t1 where id in (10, 20, 30, 40);
select id from t1 where id in (10, 20, 30, 40);
select * from t1;

delete from t1 where id = 10;
delete from t1 where id = 20;
delete from t1 where id in (30, 40);
select * from t1;
drop table t1;

# +--------------------------------------+
# | PRIMARY KEY: INT with Auto Increment |
# +--------------------------------------+
create table t1 (id int primary key auto_increment, num int) engine = blitzdb;
insert into t1 (num) values (1);
select * from t1;
insert into t1 (num) values (1);
insert into t1 (num) values (1);
insert into t1 (num) values (1);
select count(*) from t1;
flush table t1;
select count(*) from t1;
insert into t1 (num) values (1);
insert into t1 (num) values (1);
insert into t1 (num) values (1);
insert into t1 (num) values (1);
select count(*) from t1;
select count(num) from t1;
select * from t1;
drop table t1;

create table t1 (id int primary key auto_increment) engine = blitzdb;
insert into t1 values (1), (2), (3), (4);
insert into t1 values (8), (9), (10), (11);
insert into t1 values (5), (7);
select * from t1;
insert into t1 values (), (), (); # 12, 13, 14
select * from t1;
drop table t1;

# COUNT on a single auto column table
create table t1 (id int primary key auto_increment) engine = blitzdb;
insert into t1 values ();
insert into t1 values ();
insert into t1 values ();
insert into t1 values ();
select count(*) from t1;
delete from t1 where id = 1;
delete from t1 where id = 2;
select count(*) from t1;
insert into t1 values ();
insert into t1 values ();
select count(*) from t1;
drop table t1;

# +---------------------+
# | PRIMARY KEY: DOUBLE |
# +---------------------+
create table t1 (id double primary key, a int) engine = blitzdb;
insert into t1 values (1.1, 1);
insert into t1 values (1.11, 2);
insert into t1 values (1.111, 3);
insert into t1 values (1.1111, 4);
insert into t1 values (2.2, 5);
insert into t1 values (2.22, 6);
insert into t1 values (2.222, 7);
insert into t1 values (2.2222, 8);
select * from t1;

--error ER_DUP_ENTRY
update t1 set id = 1.11 where id = 1.1;
--error ER_DUP_ENTRY
update t1 set id = 2.22 where id = 2.2;

update t1 set id = 3.3 where id = 1.1;
update t1 set id = 3.33 where id = 1.11;
update t1 set id = 3.333 where id = 1.111;
update t1 set id = 3.3333 where id = 1.1111;
select * from t1 order by id;

--error ER_DUP_ENTRY
update t1 set id = 2.2 where id = 3.3;

delete from t1 where id = 3.3;
delete from t1 where id = 3.33;
select * from t1 order by id;
drop table t1;

# +----------------------+
# | PRIMARY KEY: VARCHAR |
# +----------------------+
create table t1 (id varchar(64) primary key, country varchar(64)) engine = blitzdb;
insert into t1 values ('amsterdam', 'netherlands');
insert into t1 values ('budapest', 'hungary');
insert into t1 values ('copenhagen', 'denmark');
insert into t1 values ('dublin', 'ireland');
insert into t1 values ('edinburgh', 'scotland');
insert into t1 values ('fukuoka', 'japan');
insert into t1 values ('geneva', 'switzerland');

select * from t1 order by id;
select country from t1 where id = 'dublin';
select country from t1 where id = 'geneva';
select country from t1 where id = 'amsterdam';
select country from t1 where id = 'non existent key';
select country from t1 where id = 'edinburgh';
select country from t1 where id = 'copenhagen';
select country from t1 where id = 'fukuoka';
select country from t1 where id = 'budapest';
select count (id) from t1;

--error ER_DUP_ENTRY
update t1 set id = 'dublin' where id = 'geneva';
update t1 set id = 'berlin', country = 'germany' where id = 'budapest';
update t1 set id = 'london', country = 'england' where id = 'copenhagen';
update t1 set id = 'paris', country = 'france' where id = 'dublin';

explain select * from t1 where id = 'berlin';
select * from t1 where id = 'berlin';
select * from t1 where id = 'london';
select * from t1 where id = 'paris';

delete from t1 where id = 'geneva';
delete from t1 where id = 'fukuoka';
select count (id) from t1;
select count (*) from t1;
select * from t1 order by id;

# Delete by Range
delete from t1 where id < 'london';
select count (*) from t1;
select * from t1 order by id;
drop table t1;

# Test HA_KEYTYPE_VARTEXT1 as a PRIMARY KEY
create table t1 (a varchar(10) primary key, b int) engine = blitzdb;
insert into t1 values ('aaa', 1), ('bbb', 2), ('ccc', 3), ('ddd', 4);
insert into t1 values ('eee', 5), ('fff', 6), ('ggg', 7), ('hhh', 8);

select * from t1 where a = 'aaa';
select * from t1 where a = 'bbb';
select * from t1 where a = 'ccc';
select * from t1 where a = 'ddd';
select * from t1;

delete from t1 where a = 'ggg';
delete from t1 where a = 'hhh';
select * from t1;

--error ER_DUP_ENTRY
update t1 set a = 'ddd' where a = 'aaa';
--error ER_DUP_ENTRY
update t1 set a = 'ccc' where a = 'bbb';

update t1 set a = 'zzz' where a = 'fff';
select count(*) from t1 where a = 'fff';
select * from t1 where a = 'zzz';
select * from t1;
drop table t1;

# +------------------+
# |PRIMARY KEY: DATE |
# +------------------+
create table t1 (a date primary key, b int, c varchar(32)) engine = blitzdb;
insert into t1 values ('1984-09-22', 22, 'twenty two');
insert into t1 values ('1984-09-23', 23, 'twenty three');
insert into t1 values ('1984-09-24', 24, 'twenty four');
insert into t1 values ('1984-09-25', 23, 'twenty five');
select * from t1;
explain select * from t1 where a = '1984-09-22';
select * from t1 where a = '1984-09-22';
select * from t1 where a = '1984-09-23';
select * from t1 where a = '1984-09-24';
select * from t1 where a = '1984-09-25';

--error ER_DUP_ENTRY
update t1 set a = '1984-09-22' where a = '1984-09-25';
--error ER_DUP_ENTRY
update t1 set a = '19840922' where a = '1984-09-25';

update t1 set a = '2010-03-10' where a = '1984-09-22';
update t1 set a = '2010-03-11', b = 777, c = 'triple seven' where a = '1984-09-23';
select * from t1 order by a;
drop table t1;

# Test basic index scan on INT primary keys.
create table t1 (a int primary key) engine = blitzdb;
insert into t1 values (1), (2), (3), (4), (5), (6);

# forward scan
explain select * from t1 order by a desc;
select * from t1;

# reverse scan
explain select * from t1 order by a desc;
select * from t1 order by a desc;

# write in random order
delete from t1;
insert into t1 values (6), (1), (5), (3), (2), (4);

# forward scan
explain select * from t1;
--sorted_result
select * from t1;

# reverse scan
explain select * from t1 order by a desc;
select * from t1 order by a desc;

# test aggregate functions
explain select max(a) from t1;
select max(a) from t1;
explain select min(a) from t1;
select min(a) from t1;
explain select sum(a) from t1;
select sum(a) from t1;

drop table t1;

# Test basic forward index scan on BIGINT primary keys.
create table t1 (a bigint primary key) engine = blitzdb;
insert into t1 values (10), (20), (30), (40), (50), (60);

explain select * from t1;
select * from t1;
select * from t1 order by a desc;
delete from t1;

insert into t1 values (60), (10), (50), (30), (20), (40);
select * from t1;
select * from t1 order by a desc;
drop table t1;

# Test basic index scan on VARTEXT1 primary keys.
create table t1 (a varchar(10) primary key) engine = blitzdb;
insert into t1 values ('a'), ('b'), ('c'), ('d'), ('e');
select * from t1;
explain select * from t1 order by a desc;
select * from t1 order by a desc;
delete from t1;
insert into t1 values ('c'), ('a'), ('e'), ('b'), ('d');
select * from t1;
delete from t1;

# Test it with multi byte characters (Japanese).
insert into t1 values ('う'), ('お'), ('い'), ('あ'), ('え');
insert into t1 values ('こ'), ('け'), ('か'), ('く'), ('き');
explain select * from t1;
select * from t1;
explain select * from t1 order by a desc;
select * from t1 order by a desc;
drop table t1;

# Test basic index scan on VARTEXT2 primary keys.
create table t1 (a varchar(255) primary key) engine = blitzdb;
insert into t1 values ('a'), ('b'), ('c'), ('d'), ('e');
explain select * from t1;
select * from t1;
select * from t1 order by a desc;
delete from t1;
insert into t1 values ('c'), ('a'), ('e'), ('b'), ('d');
select * from t1;
delete from t1;
drop table t1;

# Test UNIQUE index insertion 
create table t1 (a int, unique index(a)) engine = blitzdb;
insert into t1 values (1), (2);

--error ER_DUP_ENTRY
insert into t1 values (1);
--error ER_DUP_ENTRY
insert into t1 values (2);

insert into t1 values (NULL);
insert into t1 values (3);
insert into t1 values (4);

select * from t1;
select * from t1 where a = 1;
select * from t1 where a = 2;
select * from t1 where a = 3;
select * from t1 where a = 4;
select * from t1 where a is NULL;
drop table t1;

create table t1 (a varchar(32), unique index(a)) engine = blitzdb;
insert into t1 values ('a'), ('b'), ('c');

--error ER_DUP_ENTRY
insert into t1 values ('a');
--error ER_DUP_ENTRY
insert into t1 values ('b');
--error ER_DUP_ENTRY
insert into t1 values ('c');

insert into t1 values ('f'), ('e'), ('d');
--sorted_result
select * from t1;
select count(*) from t1;
explain select * from t1 where a = 'a';
select * from t1 where a = 'a';
select * from t1 where a = 'b';
select * from t1 where a = 'c';
select * from t1 where a = 'd';
select * from t1 where a = 'e';
select * from t1 where a = 'f';

# deletion test
delete from t1 where a = 'a';
delete from t1 where a = 'b';
select count(*) from t1;
select * from t1;
delete from t1 where a = 'c';
delete from t1 where a = 'd';
delete from t1 where a = 'e';
delete from t1 where a = 'f';
select count(*) from t1;
select * from t1;
drop table t1;

# +------------+
# | INDEX: INT |
# +------------+
create table t1 (a int, index(a)) engine = blitzdb;
insert into t1 values (1), (2), (3), (4);
insert into t1 values (1), (2), (3), (4);

--sorted_result
select * from t1;

insert into t1 values (NULL), (NULL);
insert into t1 values (NULL), (NULL);

select * from t1;
select count(*) from t1 where a is NULL;
select * from t1 where a is NULL;
select * from t1 where a is not NULL;

# indexed needle in a haystack query
explain select * from t1 where a = 1;
select * from t1 where a = 1;
select * from t1 where a = 2;
select * from t1 where a = 3;
select * from t1 where a = 4;

# index based deletion
delete from t1 where a = 3;
select * from t1 where a = 3;
delete from t1 where a = 1;
select * from t1 where a = 1;
select * from t1;
delete from t1 where a is NULL;
select * from t1 where a is NULL;
delete from t1 where a = 2;
select * from t1 where a = 2;
delete from t1 where a = 4;
select * from t1 where a = 4;
select count(*) from t1;
drop table t1;

# +-----------------+
# | INDEX: VARTEXT1 |
# +-----------------+
create table t1 (a varchar(32), index(a)) engine = blitzdb;
insert into t1 values ('ccc'), ('bbb'), ('aaa');
insert into t1 values ('eee'), ('ddd'), ('fff');
--sorted_result
select * from t1;
select * from t1 where a = 'aaa';
select * from t1 where a = 'bbb';
select * from t1 where a = 'ccc';
select * from t1 where a = 'ddd';
select * from t1 where a = 'eee';
delete from t1 where a = 'ddd';
delete from t1 where a = 'eee';
select * from t1;
select count(*) from t1;
drop table t1;

# +------------------------------+
# | UNIQUE INDEX: DUPLICATE NULL |
# +------------------------------+
create table t1 (a int, b int, unique index(a)) engine = blitzdb;
insert into t1 values (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4);
select * from t1;
select * from t1 where a is NULL;
insert into t1 values (1, 5), (2, 6);
--error ER_DUP_ENTRY
insert into t1 values (1, 7), (1, 8);
select * from t1 where a is not NULL;
delete from t1 where a is NULL;
select * from t1;
select count(*) from t1;
drop table t1;

# +-----------------+
# | INDEX: GROUP BY |
# +-----------------+
create table t1 (
  id int primary key,
  lastname varchar(64),
  description varchar(255), 
  price int,
  INDEX(price)
) engine = blitzdb;

insert into t1 values (1, "Schwartz", "Flight", 1500);
insert into t1 values (2, "Hayes", "Computer Equipment", 400);
insert into t1 values (3, "Lawrence", "Text Books", 220);
insert into t1 values (4, "Smith", "Weaponry", 45500);
insert into t1 values (5, "Yamada", "Dinner", 120);
insert into t1 values (6, "Smith", "Lunch", 30);
insert into t1 values (7, "Hayes", "Lunch", 30);
insert into t1 values (8, "Kinoshita", "Computer Equipment", 3740);

select * from t1;
select sum(price) from t1;
select lastname, sum(price) from t1 group by lastname;

drop table t1;

# +-----------------------+
# | COMPOSITE INDEX CHECK |
# +-----------------------+

--error ER_CANT_CREATE_TABLE
create table t1 (a int, b int, c int, d int, primary key(a, b)) engine = blitzdb;
--error ER_CANT_CREATE_TABLE
create table t1 (a int, b int, c int, d int, primary key(a, b, c)) engine = blitzdb;
--error ER_CANT_CREATE_TABLE
create table t1 (a int, b int, c int, d int, index(a, b)) engine = blitzdb;
--error ER_CANT_CREATE_TABLE
create table t1 (a int, b int, c int, d int, index(a, b, c)) engine = blitzdb;
--error ER_CANT_CREATE_TABLE
create table t1 (a int, b int, c int, d int, unique(a, b)) engine = blitzdb;
--error ER_CANT_CREATE_TABLE
create table t1 (a int, b int, c int, d int, unique(a, b, c)) engine = blitzdb;

# +------------------------------------------+
# | LARGE KEYS : Testcase from Patrick Crews |
# +------------------------------------------+

create table `t1` (
  `col_varchar_10_key` varchar(10),
  `col_int_key` int,
  `col_varchar_1024_key` varchar(1024),
  pk integer auto_increment,
  `col_varchar_10` varchar(10),
  `col_int` int,
  `col_varchar_1024` varchar(1024),
  key (`col_varchar_10_key`),
  key (`col_int_key`),
  key (`col_varchar_1024_key`),
  primary key (pk)
) engine = blitzdb;

insert /*! IGNORE */ into t1 values ('x', NULL, 'keyone', NULL, 'could', 1322188800, 'I\'m');
insert /*! IGNORE */ into t1 values ('y', NULL, 'keytwo', NULL, 'could', 1322188800, 'I\'m');
insert /*! IGNORE */ into t1 values ('z', NULL, 'keythree', NULL, 'could', 1322188800, 'I\'m');
select * from t1;

drop table t1;