wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2005 September 19
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 ON
14 # clauses that restrict the scope of the left term of the join.
15 #
16 # $Id: join5.test,v 1.2 2007/06/08 00:20:48 drh Exp $
17  
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20  
21  
22 do_test join5-1.1 {
23 execsql {
24 BEGIN;
25 CREATE TABLE t1(a integer primary key, b integer, c integer);
26 CREATE TABLE t2(x integer primary key, y);
27 CREATE TABLE t3(p integer primary key, q);
28 INSERT INTO t3 VALUES(11,'t3-11');
29 INSERT INTO t3 VALUES(12,'t3-12');
30 INSERT INTO t2 VALUES(11,'t2-11');
31 INSERT INTO t2 VALUES(12,'t2-12');
32 INSERT INTO t1 VALUES(1, 5, 0);
33 INSERT INTO t1 VALUES(2, 11, 2);
34 INSERT INTO t1 VALUES(3, 12, 1);
35 COMMIT;
36 }
37 } {}
38 do_test join5-1.2 {
39 execsql {
40 select * from t1 left join t2 on t1.b=t2.x and t1.c=1
41 }
42 } {1 5 0 {} {} 2 11 2 {} {} 3 12 1 12 t2-12}
43 do_test join5-1.3 {
44 execsql {
45 select * from t1 left join t2 on t1.b=t2.x where t1.c=1
46 }
47 } {3 12 1 12 t2-12}
48 do_test join5-1.4 {
49 execsql {
50 select * from t1 left join t2 on t1.b=t2.x and t1.c=1
51 left join t3 on t1.b=t3.p and t1.c=2
52 }
53 } {1 5 0 {} {} {} {} 2 11 2 {} {} 11 t3-11 3 12 1 12 t2-12 {} {}}
54 do_test join5-1.5 {
55 execsql {
56 select * from t1 left join t2 on t1.b=t2.x and t1.c=1
57 left join t3 on t1.b=t3.p where t1.c=2
58 }
59 } {2 11 2 {} {} 11 t3-11}
60  
61 # Ticket #2403
62 #
63 do_test join5-2.1 {
64 execsql {
65 CREATE TABLE ab(a,b);
66 INSERT INTO "ab" VALUES(1,2);
67 INSERT INTO "ab" VALUES(3,NULL);
68  
69 CREATE TABLE xy(x,y);
70 INSERT INTO "xy" VALUES(2,3);
71 INSERT INTO "xy" VALUES(NULL,1);
72 }
73 execsql {SELECT * FROM xy LEFT JOIN ab ON 0}
74 } {2 3 {} {} {} 1 {} {}}
75 do_test join5-2.2 {
76 execsql {SELECT * FROM xy LEFT JOIN ab ON 1}
77 } {2 3 1 2 2 3 3 {} {} 1 1 2 {} 1 3 {}}
78 do_test join5-2.3 {
79 execsql {SELECT * FROM xy LEFT JOIN ab ON NULL}
80 } {2 3 {} {} {} 1 {} {}}
81 do_test join5-2.4 {
82 execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 0}
83 } {}
84 do_test join5-2.5 {
85 execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 0}
86 } {}
87 do_test join5-2.6 {
88 execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 0}
89 } {}
90 do_test join5-2.7 {
91 execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 1}
92 } {2 3 {} {} {} 1 {} {}}
93 do_test join5-2.8 {
94 execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 1}
95 } {2 3 1 2 2 3 3 {} {} 1 1 2 {} 1 3 {}}
96 do_test join5-2.9 {
97 execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 1}
98 } {2 3 {} {} {} 1 {} {}}
99 do_test join5-2.10 {
100 execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE NULL}
101 } {}
102 do_test join5-2.11 {
103 execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE NULL}
104 } {}
105 do_test join5-2.12 {
106 execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE NULL}
107 } {}
108  
109  
110 finish_test