• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
1# 2005 November 26
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 to verify that ticket #1537 is
14# fixed.
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20do_test tkt1537-1.1 {
21  execsql {
22    CREATE TABLE t1(id, a1, a2);
23    INSERT INTO t1 VALUES(1, NULL, NULL);
24    INSERT INTO t1 VALUES(2, 1, 3);
25    CREATE TABLE t2(id, b);
26    INSERT INTO t2 VALUES(3, 1);
27    INSERT INTO t2 VALUES(4, NULL);
28    SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=+b;
29  }
30} {1 {} {} {} {} 2 1 3 3 1}
31do_test tkt1537-1.2 {
32  execsql {
33    SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=b;
34  }
35} {1 {} {} {} {} 2 1 3 3 1}
36do_test tkt1537-1.3 {
37  execsql {
38    SELECT * FROM t2 LEFT JOIN t1 ON a1=b OR a2=b;
39  }
40} {3 1 2 1 3 4 {} {} {} {}}
41ifcapable subquery {
42  do_test tkt1537-1.4 {
43    execsql {
44      SELECT * FROM t1 LEFT JOIN t2 ON b IN (a1,a2);
45    }
46  } {1 {} {} {} {} 2 1 3 3 1}
47  do_test tkt1537-1.5 {
48    execsql {
49      SELECT * FROM t2 LEFT JOIN t1 ON b IN (a2,a1);
50    }
51  } {3 1 2 1 3 4 {} {} {} {}}
52}
53do_test tkt1537-1.6 {
54  execsql {
55    CREATE INDEX t1a1 ON t1(a1);
56    CREATE INDEX t1a2 ON t1(a2);
57    CREATE INDEX t2b ON t2(b);
58    SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=b;
59  }
60} {1 {} {} {} {} 2 1 3 3 1}
61do_test tkt1537-1.7 {
62  execsql {
63    SELECT * FROM t2 LEFT JOIN t1 ON a1=b OR a2=b;
64  }
65} {3 1 2 1 3 4 {} {} {} {}}
66
67ifcapable subquery {
68  do_test tkt1537-1.8 {
69    execsql {
70      SELECT * FROM t1 LEFT JOIN t2 ON b IN (a1,a2);
71    }
72  } {1 {} {} {} {} 2 1 3 3 1}
73  do_test tkt1537-1.9 {
74    execsql {
75      SELECT * FROM t2 LEFT JOIN t1 ON b IN (a2,a1);
76    }
77  } {3 1 2 1 3 4 {} {} {} {}}
78}
79
80execsql {
81  DROP INDEX t1a1;
82  DROP INDEX t1a2;
83  DROP INDEX t2b;
84}
85
86do_test tkt1537-2.1 {
87  execsql {
88    SELECT * FROM t1 LEFT JOIN t2 ON b BETWEEN a1 AND a2;
89  }
90} {1 {} {} {} {} 2 1 3 3 1}
91do_test tkt1537-2.2 {
92  execsql {
93    CREATE INDEX t2b ON t2(b);
94    SELECT * FROM t1 LEFT JOIN t2 ON b BETWEEN a1 AND a2;
95  }
96} {1 {} {} {} {} 2 1 3 3 1}
97do_test tkt1537-2.3 {
98  execsql {
99    SELECT * FROM t2 LEFT JOIN t1 ON b BETWEEN a1 AND a2;
100  }
101} {3 1 2 1 3 4 {} {} {} {}}
102do_test tkt1537-2.4 {
103  execsql {
104    CREATE INDEX t1a1 ON t1(a1);
105    CREATE INDEX t1a2 ON t1(a2);
106    SELECT * FROM t2 LEFT JOIN t1 ON b BETWEEN a1 AND a2;
107  }
108} {3 1 2 1 3 4 {} {} {} {}}
109
110do_test tkt1537-3.1 {
111  execsql {
112    SELECT * FROM t1 LEFT JOIN t2 ON b GLOB 'abc*' WHERE t1.id=1;
113  }
114} {1 {} {} {} {}}
115do_test tkt1537-3.2 {
116  execsql {
117    SELECT * FROM t2 LEFT JOIN t1 ON a1 GLOB 'abc*' WHERE t2.id=3;
118  }
119} {3 1 {} {} {}}
120
121
122finish_test
123