|
1 # 2007 June 8 |
|
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. The |
|
12 # focus of this file is testing that terms in the ON clause of |
|
13 # a LEFT OUTER JOIN are not used with indices. See ticket #3015. |
|
14 # |
|
15 # $Id: where6.test,v 1.2 2008/04/17 19:14:02 drh Exp $ |
|
16 |
|
17 set testdir [file dirname $argv0] |
|
18 source $testdir/tester.tcl |
|
19 |
|
20 # Build some test data |
|
21 # |
|
22 do_test where6-1.1 { |
|
23 execsql { |
|
24 CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c); |
|
25 INSERT INTO t1 VALUES(1,3,1); |
|
26 INSERT INTO t1 VALUES(2,4,2); |
|
27 CREATE TABLE t2(x INTEGER PRIMARY KEY); |
|
28 INSERT INTO t2 VALUES(3); |
|
29 |
|
30 SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1; |
|
31 } |
|
32 } {1 3 1 3 2 4 2 {}} |
|
33 do_test where6-1.2 { |
|
34 execsql { |
|
35 SELECT * FROM t1 LEFT JOIN t2 ON x=b AND c=1; |
|
36 } |
|
37 } {1 3 1 3 2 4 2 {}} |
|
38 do_test where6-1.3 { |
|
39 execsql { |
|
40 SELECT * FROM t1 LEFT JOIN t2 ON x=b AND 1=c; |
|
41 } |
|
42 } {1 3 1 3 2 4 2 {}} |
|
43 do_test where6-1.4 { |
|
44 execsql { |
|
45 SELECT * FROM t1 LEFT JOIN t2 ON b=x AND 1=c; |
|
46 } |
|
47 } {1 3 1 3 2 4 2 {}} |
|
48 |
|
49 ifcapable explain { |
|
50 do_test where6-1.5 { |
|
51 explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON x=b AND 1=c} |
|
52 } [explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1}] |
|
53 do_test where6-1.6 { |
|
54 explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON x=b WHERE 1=c} |
|
55 } [explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1}] |
|
56 } |
|
57 |
|
58 do_test where6-1.11 { |
|
59 execsql { |
|
60 SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1; |
|
61 } |
|
62 } {1 3 1 3} |
|
63 do_test where6-1.12 { |
|
64 execsql { |
|
65 SELECT * FROM t1 LEFT JOIN t2 ON x=b WHERE c=1; |
|
66 } |
|
67 } {1 3 1 3} |
|
68 do_test where6-1.13 { |
|
69 execsql { |
|
70 SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE 1=c; |
|
71 } |
|
72 } {1 3 1 3} |
|
73 |
|
74 |
|
75 |
|
76 do_test where6-2.1 { |
|
77 execsql { |
|
78 CREATE INDEX i1 ON t1(c); |
|
79 |
|
80 SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1; |
|
81 } |
|
82 } {1 3 1 3 2 4 2 {}} |
|
83 do_test where6-2.2 { |
|
84 execsql { |
|
85 SELECT * FROM t1 LEFT JOIN t2 ON x=b AND c=1; |
|
86 } |
|
87 } {1 3 1 3 2 4 2 {}} |
|
88 do_test where6-2.3 { |
|
89 execsql { |
|
90 SELECT * FROM t1 LEFT JOIN t2 ON x=b AND 1=c; |
|
91 } |
|
92 } {1 3 1 3 2 4 2 {}} |
|
93 do_test where6-2.4 { |
|
94 execsql { |
|
95 SELECT * FROM t1 LEFT JOIN t2 ON b=x AND 1=c; |
|
96 } |
|
97 } {1 3 1 3 2 4 2 {}} |
|
98 |
|
99 ifcapable explain { |
|
100 do_test where6-2.5 { |
|
101 explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON x=b AND 1=c} |
|
102 } [explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1}] |
|
103 do_test where6-2.6 { |
|
104 explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON x=b WHERE 1=c} |
|
105 } [explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1}] |
|
106 } |
|
107 |
|
108 |
|
109 do_test where6-2.11 { |
|
110 execsql { |
|
111 SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1; |
|
112 } |
|
113 } {1 3 1 3} |
|
114 do_test where6-2.12 { |
|
115 execsql { |
|
116 SELECT * FROM t1 LEFT JOIN t2 ON x=b WHERE c=1; |
|
117 } |
|
118 } {1 3 1 3} |
|
119 do_test where6-2.13 { |
|
120 execsql { |
|
121 SELECT * FROM t1 LEFT JOIN t2 ON x=b WHERE 1=c; |
|
122 } |
|
123 } {1 3 1 3} |
|
124 do_test where6-2.14 { |
|
125 execsql { |
|
126 SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE 1=c; |
|
127 } |
|
128 } {1 3 1 3} |
|
129 |
|
130 finish_test |