~drizzle-trunk/drizzle/development

1239.3.44 by Toru Maesaka
Added basic JOIN tests, optimized B+Tree usage and added slotted lock to write_row().
1
# Test Routine for JOIN operations in BlitzDB
2
3
--disable_warnings
4
drop table if exists t1, t2;
5
--enable_warnings
6
1239.3.52 by Toru Maesaka
Added more tests for textual data, DATE type, join syntax and boundary.
7
# Simple JOIN syntax - Unindexed 
1239.3.44 by Toru Maesaka
Added basic JOIN tests, optimized B+Tree usage and added slotted lock to write_row().
8
create table t1 (a int) engine = blitzdb;
9
create table t2 (a int) engine = blitzdb;
10
11
insert into t1 values (1), (2), (3);
12
insert into t2 values (1), (4), (8);
13
14
select * from t1 JOIN t2 where t1.a = t2.a;
15
select * from t1 LEFT JOIN t2 on t1.a = t2.a;
16
select * from t1 RIGHT JOIN t2 on t1.a = t2.a;
17
select * from t1 UNION select * from t2;
18
drop table t1, t2;
1239.3.52 by Toru Maesaka
Added more tests for textual data, DATE type, join syntax and boundary.
19
20
# Simple Expensive Multi Table JOIN - Unindexed
1239.3.74 by Toru Maesaka
Added and fixed some tests.
21
create table t1 (id int, name varchar(32)) engine = blitzdb; 
22
create table t2 (id int, job_id int) engine = blitzdb; 
23
create table t3 (job_id int, job_name varchar(32)) engine = blitzdb; 
1239.3.52 by Toru Maesaka
Added more tests for textual data, DATE type, join syntax and boundary.
24
25
insert into t1 values (1, 'Alex'); 
26
insert into t1 values (2, 'Bob'); 
27
insert into t1 values (3, 'Curt'); 
28
insert into t1 values (4, 'Dan'); 
29
insert into t1 values (5, 'Edgar'); 
30
31
insert into t2 values (1, 1);
32
insert into t2 values (2, 1);
33
insert into t2 values (3, 3);
34
insert into t2 values (4, 2);
35
insert into t2 values (5, 4);
36
37
insert into t3 values (1, 'Software Engineer');
38
insert into t3 values (2, 'Civil Engineer');
39
insert into t3 values (3, 'Electrical Engineer');
40
insert into t3 values (4, 'Mechanical Engineer');
41
42
select
43
  t1.name, t3.job_name
44
from
45
  t1 join (t2, t3)
46
on
47
  (t1.id = t2.id and t2.job_id = t3.job_id);
48
49
# right join
50
select
51
  t1.name, t3.job_name
52
from
53
  t1 right join (t2, t3)
54
on
55
  (t1.id = t2.id and t2.job_id = t3.job_id);
56
57
# remove a job
58
delete from t3 where job_id = 4;
59
60
select
61
  t1.name, t3.job_name
62
from
63
  t1 join (t2, t3)
64
on
65
  (t1.id = t2.id and t2.job_id = t3.job_id);
66
67
# left join
68
select
69
  t1.name, t3.job_name
70
from
71
  t1 left join (t2, t3)
72
on
73
  (t1.id = t2.id and t2.job_id = t3.job_id);
74
75
drop table t1, t2, t3;