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 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# Build some test data 21# 22do_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 {}} 33do_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 {}} 38do_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 {}} 43do_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 49ifcapable 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 58do_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} 63do_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} 68do_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 76do_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 {}} 83do_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 {}} 88do_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 {}} 93do_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 99ifcapable 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 109do_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} 114do_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} 119do_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} 124do_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# Ticket [ebdbadade5b]: 131# If the ON close on a LEFT JOIN is of the form x=y where both x and y 132# are indexed columns on tables to left of the join, then do not use that 133# term with indices to either table. 134# 135do_test where6-3.1 { 136 db eval { 137 CREATE TABLE t4(x UNIQUE); 138 INSERT INTO t4 VALUES('abc'); 139 INSERT INTO t4 VALUES('def'); 140 INSERT INTO t4 VALUES('ghi'); 141 CREATE TABLE t5(a, b, c, PRIMARY KEY(a,b)); 142 INSERT INTO t5 VALUES('abc','def',123); 143 INSERT INTO t5 VALUES('def','ghi',456); 144 145 SELECT t4a.x, t4b.x, t5.c, t6.v 146 FROM t4 AS t4a 147 INNER JOIN t4 AS t4b 148 LEFT JOIN t5 ON t5.a=t4a.x AND t5.b=t4b.x 149 LEFT JOIN (SELECT 1 AS v) AS t6 ON t4a.x=t4b.x 150 ORDER BY 1, 2, 3; 151 } 152} {abc abc {} 1 abc def 123 {} abc ghi {} {} def abc {} {} def def {} 1 def ghi 456 {} ghi abc {} {} ghi def {} {} ghi ghi {} 1} 153 154finish_test 155