OLD | NEW |
| (Empty) |
1 # 2002 May 24 | |
2 # | |
3 # The author disclaims copyright to this source code. In place of | |
4 # a legal notice, here is a blessing: | |
5 # | |
6 # May you do good and not evil. | |
7 # May you find forgiveness for yourself and forgive others. | |
8 # May you share freely, never taking more than you give. | |
9 # | |
10 #*********************************************************************** | |
11 # This file implements regression tests for SQLite library. | |
12 # | |
13 # This file implements tests for left outer joins containing WHERE | |
14 # clauses that restrict the scope of the left term of the join. | |
15 # | |
16 # $Id: join4.test,v 1.4 2005/03/29 03:11:00 danielk1977 Exp $ | |
17 | |
18 set testdir [file dirname $argv0] | |
19 source $testdir/tester.tcl | |
20 | |
21 ifcapable tempdb { | |
22 do_test join4-1.1 { | |
23 execsql { | |
24 create temp table t1(a integer, b varchar(10)); | |
25 insert into t1 values(1,'one'); | |
26 insert into t1 values(2,'two'); | |
27 insert into t1 values(3,'three'); | |
28 insert into t1 values(4,'four'); | |
29 | |
30 create temp table t2(x integer, y varchar(10), z varchar(10)); | |
31 insert into t2 values(2,'niban','ok'); | |
32 insert into t2 values(4,'yonban','err'); | |
33 } | |
34 execsql { | |
35 select * from t1 left outer join t2 on t1.a=t2.x where t2.z='ok' | |
36 } | |
37 } {2 two 2 niban ok} | |
38 } else { | |
39 do_test join4-1.1 { | |
40 execsql { | |
41 create table t1(a integer, b varchar(10)); | |
42 insert into t1 values(1,'one'); | |
43 insert into t1 values(2,'two'); | |
44 insert into t1 values(3,'three'); | |
45 insert into t1 values(4,'four'); | |
46 | |
47 create table t2(x integer, y varchar(10), z varchar(10)); | |
48 insert into t2 values(2,'niban','ok'); | |
49 insert into t2 values(4,'yonban','err'); | |
50 } | |
51 execsql { | |
52 select * from t1 left outer join t2 on t1.a=t2.x where t2.z='ok' | |
53 } | |
54 } {2 two 2 niban ok} | |
55 } | |
56 do_test join4-1.2 { | |
57 execsql { | |
58 select * from t1 left outer join t2 on t1.a=t2.x and t2.z='ok' | |
59 } | |
60 } {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}} | |
61 do_test join4-1.3 { | |
62 execsql { | |
63 create index i2 on t2(z); | |
64 } | |
65 execsql { | |
66 select * from t1 left outer join t2 on t1.a=t2.x where t2.z='ok' | |
67 } | |
68 } {2 two 2 niban ok} | |
69 do_test join4-1.4 { | |
70 execsql { | |
71 select * from t1 left outer join t2 on t1.a=t2.x and t2.z='ok' | |
72 } | |
73 } {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}} | |
74 do_test join4-1.5 { | |
75 execsql { | |
76 select * from t1 left outer join t2 on t1.a=t2.x where t2.z>='ok' | |
77 } | |
78 } {2 two 2 niban ok} | |
79 do_test join4-1.4 { | |
80 execsql { | |
81 select * from t1 left outer join t2 on t1.a=t2.x and t2.z>='ok' | |
82 } | |
83 } {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}} | |
84 ifcapable subquery { | |
85 do_test join4-1.6 { | |
86 execsql { | |
87 select * from t1 left outer join t2 on t1.a=t2.x where t2.z IN ('ok') | |
88 } | |
89 } {2 two 2 niban ok} | |
90 do_test join4-1.7 { | |
91 execsql { | |
92 select * from t1 left outer join t2 on t1.a=t2.x and t2.z IN ('ok') | |
93 } | |
94 } {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}} | |
95 } | |
96 | |
97 | |
98 finish_test | |
OLD | NEW |