1# 2012 August 24 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 an index may be used as a covering 13# index when there are OR expressions in the WHERE clause. 14# 15 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19set ::testprefix whereD 20 21do_execsql_test 1.1 { 22 CREATE TABLE t(i,j,k,m,n); 23 CREATE INDEX ijk ON t(i,j,k); 24 CREATE INDEX jmn ON t(j,m,n); 25 26 INSERT INTO t VALUES(3, 3, 'three', 3, 'tres'); 27 INSERT INTO t VALUES(2, 2, 'two', 2, 'dos'); 28 INSERT INTO t VALUES(1, 1, 'one', 1, 'uno'); 29 INSERT INTO t VALUES(4, 4, 'four', 4, 'cuatro'); 30} 31 32do_execsql_test 1.2 { 33 SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2); 34} {one two} 35do_execsql_test 1.3 { 36 SELECT k FROM t WHERE (i=1 AND j=1) OR (+i=2 AND j=2); 37} {one two} 38do_execsql_test 1.4 { 39 SELECT n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2); 40} {uno dos} 41do_execsql_test 1.5 { 42 SELECT k, n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2); 43} {one uno two dos} 44do_execsql_test 1.6 { 45 SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (i=3 AND j=3); 46} {one two three} 47do_execsql_test 1.7 { 48 SELECT n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (i=3 AND j=3); 49} {uno dos tres} 50do_execsql_test 1.8 { 51 SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2); 52} {one two} 53do_execsql_test 1.9 { 54 SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (j=3 AND m=3); 55} {one two three} 56do_execsql_test 1.10 { 57 SELECT n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (j=3 AND m=3); 58} {uno dos tres} 59do_execsql_test 1.11 { 60 SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2) OR (i=3 AND j=3); 61} {one two three} 62do_execsql_test 1.12 { 63 SELECT n FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2) OR (i=3 AND j=3); 64} {uno dos tres} 65do_execsql_test 1.13 { 66 SELECT k FROM t WHERE (j=1 AND m=1) OR (i=2 AND j=2) OR (i=3 AND j=3); 67} {one two three} 68do_execsql_test 1.14 { 69 SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND i=2) OR (i=3 AND j=3); 70} {one two three} 71do_execsql_test 1.15 { 72 SELECT k FROM t WHERE (i=1 AND j=2) OR (i=2 AND j=1) OR (i=3 AND j=4); 73} {} 74do_execsql_test 1.16 { 75 SELECT k FROM t WHERE (i=1 AND (j=1 or j=2)) OR (i=3 AND j=3); 76} {one three} 77 78do_execsql_test 2.0 { 79 CREATE TABLE t1(a,b,c,d); 80 CREATE INDEX t1b ON t1(b); 81 CREATE INDEX t1c ON t1(c); 82 CREATE INDEX t1d ON t1(d); 83 CREATE TABLE t2(x,y); 84 CREATE INDEX t2y ON t2(y); 85 86 INSERT INTO t1 VALUES(1,2,3,4); 87 INSERT INTO t1 VALUES(5,6,7,8); 88 INSERT INTO t2 VALUES(1,2); 89 INSERT INTO t2 VALUES(2,7); 90 INSERT INTO t2 VALUES(3,4); 91} {} 92do_execsql_test 2.1 { 93 SELECT a, x FROM t1 JOIN t2 ON +y=d OR x=7 ORDER BY a, x; 94} {1 3} 95do_execsql_test 2.2 { 96 SELECT a, x FROM t1 JOIN t2 ON y=d OR x=7 ORDER BY a, x; 97} {1 3} 98 99 100# Similar to [do_execsql_test], except that two elements are appended 101# to the result - the string "search" and the number of times test variable 102# sqlite3_search_count is incremented by running the supplied SQL. e.g. 103# 104# do_searchcount_test 1.0 { SELECT * FROM t1 } {x y search 2} 105# 106proc do_searchcount_test {tn sql res} { 107 uplevel [subst -nocommands { 108 do_test $tn { 109 set ::sqlite_search_count 0 110 concat [db eval {$sql}] search [set ::sqlite_search_count] 111 } [list $res] 112 }] 113} 114 115do_execsql_test 3.0 { 116 CREATE TABLE t3(a, b, c); 117 CREATE UNIQUE INDEX i3 ON t3(a, b); 118 INSERT INTO t3 VALUES(1, 'one', 'i'); 119 INSERT INTO t3 VALUES(3, 'three', 'iii'); 120 INSERT INTO t3 VALUES(6, 'six', 'vi'); 121 INSERT INTO t3 VALUES(2, 'two', 'ii'); 122 INSERT INTO t3 VALUES(4, 'four', 'iv'); 123 INSERT INTO t3 VALUES(5, 'five', 'v'); 124 125 CREATE TABLE t4(x PRIMARY KEY, y); 126 INSERT INTO t4 VALUES('a', 'one'); 127 INSERT INTO t4 VALUES('b', 'two'); 128} 129 130do_searchcount_test 3.1 { 131 SELECT a, b FROM t3 WHERE (a=1 AND b='one') OR (a=2 AND b='two') 132} {1 one 2 two search 2} 133 134do_searchcount_test 3.2 { 135 SELECT a, c FROM t3 WHERE (a=1 AND b='one') OR (a=2 AND b='two') 136} {1 i 2 ii search 4} 137 138do_searchcount_test 3.4.1 { 139 SELECT y FROM t4 WHERE x='a' 140} {one search 2} 141do_searchcount_test 3.4.2 { 142 SELECT a, b FROM t3 WHERE 143 (a=1 AND b=(SELECT y FROM t4 WHERE x='a')) 144 OR (a=2 AND b='two') 145} {1 one 2 two search 4} 146do_searchcount_test 3.4.3 { 147 SELECT a, b FROM t3 WHERE 148 (a=2 AND b='two') 149 OR (a=1 AND b=(SELECT y FROM t4 WHERE x='a')) 150} {2 two 1 one search 4} 151do_searchcount_test 3.4.4 { 152 SELECT a, b FROM t3 WHERE 153 (a=2 AND b=(SELECT y FROM t4 WHERE x='b')) 154 OR (a=1 AND b=(SELECT y FROM t4 WHERE x='a')) 155} {2 two 1 one search 6} 156 157do_searchcount_test 3.5.1 { 158 SELECT a, b FROM t3 WHERE (a=1 AND b='one') OR rowid=4 159} {1 one 2 two search 2} 160do_searchcount_test 3.5.2 { 161 SELECT a, c FROM t3 WHERE (a=1 AND b='one') OR rowid=4 162} {1 i 2 ii search 2} 163 164# Ticket [d02e1406a58ea02d] (2012-10-04) 165# LEFT JOIN with an OR in the ON clause causes segfault 166# 167do_test 4.1 { 168 db eval { 169 CREATE TABLE t41(a,b,c); 170 INSERT INTO t41 VALUES(1,2,3), (4,5,6); 171 CREATE TABLE t42(d,e,f); 172 INSERT INTO t42 VALUES(3,6,9), (4,8,12); 173 SELECT * FROM t41 AS x LEFT JOIN t42 AS y ON (y.d=x.c) OR (y.e=x.b); 174 } 175} {1 2 3 3 6 9 4 5 6 {} {} {}} 176do_test 4.2 { 177 db eval { 178 CREATE INDEX t42d ON t42(d); 179 CREATE INDEX t42e ON t42(e); 180 SELECT * FROM t41 AS x LEFT JOIN t42 AS y ON (y.d=x.c) OR (y.e=x.b); 181 } 182} {1 2 3 3 6 9 4 5 6 {} {} {}} 183do_test 4.3 { 184 db eval { 185 SELECT * FROM t41 AS x LEFT JOIN t42 AS y ON (y.d=x.c) OR (y.d=x.b); 186 } 187} {1 2 3 3 6 9 4 5 6 {} {} {}} 188 189# Ticket [bc1aea7b725f276177] 190# Incorrect result on LEFT JOIN with OR constraints and an ORDER BY clause. 191# 192do_execsql_test 4.4 { 193 CREATE TABLE t44(a INTEGER, b INTEGER); 194 INSERT INTO t44 VALUES(1,2); 195 INSERT INTO t44 VALUES(3,4); 196 SELECT * 197 FROM t44 AS x 198 LEFT JOIN (SELECT a AS c, b AS d FROM t44) AS y ON a=c 199 WHERE d=4 OR d IS NULL; 200} {3 4 3 4} 201do_execsql_test 4.5 { 202 SELECT * 203 FROM t44 AS x 204 LEFT JOIN (SELECT a AS c, b AS d FROM t44) AS y ON a=c 205 WHERE d=4 OR d IS NULL 206 ORDER BY a; 207} {3 4 3 4} 208do_execsql_test 4.6 { 209 CREATE TABLE t46(c INTEGER, d INTEGER); 210 INSERT INTO t46 SELECT a, b FROM t44; 211 SELECT * FROM t44 LEFT JOIN t46 ON a=c 212 WHERE d=4 OR d IS NULL; 213} {3 4 3 4} 214do_execsql_test 4.7 { 215 SELECT * FROM t44 LEFT JOIN t46 ON a=c 216 WHERE d=4 OR d IS NULL 217 ORDER BY a; 218} {3 4 3 4} 219 220 221 222finish_test 223