join5.test revision 5821806d5e7f356e8fa4b058a389a808ea183019 
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
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20
21
22do_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} {}
38do_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}
43do_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}
48do_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 {} {}}
54do_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#
63do_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 {} {}}
75do_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 {}}
78do_test join5-2.3 {
79  execsql {SELECT * FROM xy LEFT JOIN ab ON NULL}
80} {2 3 {} {} {} 1 {} {}}
81do_test join5-2.4 {
82  execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 0}
83} {}
84do_test join5-2.5 {
85  execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 0}
86} {}
87do_test join5-2.6 {
88  execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 0}
89} {}
90do_test join5-2.7 {
91  execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 1}
92} {2 3 {} {} {} 1 {} {}}
93do_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 {}}
96do_test join5-2.9 {
97  execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 1}
98} {2 3 {} {} {} 1 {} {}}
99do_test join5-2.10 {
100  execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE NULL}
101} {}
102do_test join5-2.11 {
103  execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE NULL}
104} {}
105do_test join5-2.12 {
106  execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE NULL}
107} {}
108
109
110finish_test
111