1# 2016 June 17 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 the SELECT statement. 13# 14 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set ::testprefix rowvalue 19 20do_execsql_test 0.0 { 21 CREATE TABLE one(o); 22 INSERT INTO one VALUES(1); 23} 24 25foreach {tn v1 v2 eq ne is isnot} { 26 1 "1, 2, 3" "1, 2, 3" 1 0 1 0 27 2 "1, 0, 3" "1, 2, 3" 0 1 0 1 28 3 "1, 2, NULL" "1, 2, 3" {} {} 0 1 29 4 "1, 2, NULL" "1, 2, NULL" {} {} 1 0 30 5 "NULL, NULL, NULL" "NULL, NULL, NULL" {} {} 1 0 31 32 6 "1, NULL, 1" "1, 1, 1" {} {} 0 1 33 7 "1, NULL, 1" "1, 1, 2" 0 1 0 1 34} { 35 do_execsql_test 1.$tn.eq "SELECT ($v1) == ($v2)" [list $eq] 36 do_execsql_test 1.$tn.ne "SELECT ($v1) != ($v2)" [list $ne] 37 38 do_execsql_test 1.$tn.is "SELECT ($v1) IS ($v2)" [list $is] 39 do_execsql_test 1.$tn.isnot "SELECT ($v1) IS NOT ($v2)" [list $isnot] 40 41 do_execsql_test 1.$tn.2.eq "SELECT (SELECT $v1) == (SELECT $v2)" [list $eq] 42 do_execsql_test 1.$tn.2.ne "SELECT (SELECT $v1) != (SELECT $v2)" [list $ne] 43} 44 45foreach {tn v1 v2 lt gt le ge} { 46 1 "(1, 1, 3)" "(1, 2, 3)" 1 0 1 0 47 2 "(1, 2, 3)" "(1, 2, 3)" 0 0 1 1 48 3 "(1, 3, 3)" "(1, 2, 3)" 0 1 0 1 49 50 4 "(1, NULL, 3)" "(1, 2, 3)" {} {} {} {} 51 5 "(1, 3, 3)" "(1, NULL, 3)" {} {} {} {} 52 6 "(1, NULL, 3)" "(1, NULL, 3)" {} {} {} {} 53} { 54 foreach {tn2 expr res} [list \ 55 2.$tn.lt "$v1 < $v2" $lt \ 56 2.$tn.gt "$v1 > $v2" $gt \ 57 2.$tn.le "$v1 <= $v2" $le \ 58 2.$tn.ge "$v1 >= $v2" $ge \ 59 ] { 60 do_execsql_test $tn2 "SELECT $expr" [list $res] 61 62 set map(0) [list] 63 set map() [list] 64 set map(1) [list 1] 65 do_execsql_test $tn2.where1 "SELECT * FROM one WHERE $expr" $map($res) 66 67 set map(0) [list 1] 68 set map() [list] 69 set map(1) [list] 70 do_execsql_test $tn2.where2 "SELECT * FROM one WHERE NOT $expr" $map($res) 71 } 72} 73 74do_execsql_test 3.0 { 75 CREATE TABLE t1(x, y); 76 INSERT INTO t1 VALUES(1, 1); 77 INSERT INTO t1 VALUES(1, 2); 78 INSERT INTO t1 VALUES(2, 3); 79 INSERT INTO t1 VALUES(2, 4); 80 INSERT INTO t1 VALUES(3, 5); 81 INSERT INTO t1 VALUES(3, 6); 82} 83 84foreach {tn r order} { 85 1 "(1, 1)" "ORDER BY y" 86 2 "(1, 1)" "ORDER BY x, y" 87 3 "(1, 2)" "ORDER BY x, y DESC" 88 4 "(3, 6)" "ORDER BY x DESC, y DESC" 89 5 "((3, 5))" "ORDER BY x DESC, y" 90 6 "(SELECT 3, 5)" "ORDER BY x DESC, y" 91} { 92 do_execsql_test 3.$tn.1 "SELECT $r == (SELECT x,y FROM t1 $order)" 1 93 do_execsql_test 3.$tn.2 "SELECT $r == (SELECT * FROM t1 $order)" 1 94 95 do_execsql_test 3.$tn.3 " 96 SELECT (SELECT * FROM t1 $order) == (SELECT * FROM t1 $order) 97 " 1 98 do_execsql_test 3.$tn.4 " 99 SELECT (SELECT 0, 0) == (SELECT * FROM t1 $order) 100 " 0 101} 102 103foreach {tn expr res} { 104 1 {(2, 2) BETWEEN (2, 2) AND (3, 3)} 1 105 2 {(2, 2) BETWEEN (2, NULL) AND (3, 3)} {} 106 3 {(2, 2) BETWEEN (3, NULL) AND (3, 3)} 0 107} { 108 do_execsql_test 4.$tn "SELECT $expr" [list $res] 109} 110 111foreach {tn expr res} { 112 1 {(2, 4) IN (SELECT * FROM t1)} 1 113 2 {(3, 4) IN (SELECT * FROM t1)} 0 114 115 3 {(NULL, 4) IN (SELECT * FROM t1)} {} 116 4 {(NULL, 0) IN (SELECT * FROM t1)} 0 117 118 5 {(NULL, 4) NOT IN (SELECT * FROM t1)} {} 119 6 {(NULL, 0) NOT IN (SELECT * FROM t1)} 1 120} { 121 do_execsql_test 5.$tn "SELECT $expr" [list $res] 122} 123 124do_execsql_test 6.0 { 125 CREATE TABLE hh(a, b, c); 126 INSERT INTO hh VALUES('abc', 1, 'i'); 127 INSERT INTO hh VALUES('ABC', 1, 'ii'); 128 INSERT INTO hh VALUES('def', 2, 'iii'); 129 INSERT INTO hh VALUES('DEF', 2, 'iv'); 130 INSERT INTO hh VALUES('GHI', 3, 'v'); 131 INSERT INTO hh VALUES('ghi', 3, 'vi'); 132 133 CREATE INDEX hh_ab ON hh(a, b); 134} 135 136do_execsql_test 6.1 { 137 SELECT c FROM hh WHERE (a, b) = (SELECT 'abc', 1); 138} {i} 139do_execsql_test 6.2 { 140 SELECT c FROM hh WHERE (a, b) = (SELECT 'abc' COLLATE nocase, 1); 141} {i} 142do_execsql_test 6.3 { 143 SELECT c FROM hh WHERE a = (SELECT 'abc' COLLATE nocase) AND b = (SELECT 1); 144} {i} 145do_execsql_test 6.4 { 146 SELECT c FROM hh WHERE +a = (SELECT 'abc' COLLATE nocase) AND b = (SELECT 1); 147} {i} 148do_execsql_test 6.5 { 149 SELECT c FROM hh WHERE a = (SELECT 'abc') COLLATE nocase AND b = (SELECT 1); 150} {i ii} 151do_catchsql_test 6.6 { 152 SELECT c FROM hh WHERE (a, b) = (SELECT 'abc', 1) COLLATE nocase; 153} {1 {row value misused}} 154do_catchsql_test 6.7 { 155 SELECT c FROM hh WHERE (a, b) = 1; 156} {1 {row value misused}} 157do_execsql_test 6.8 { 158 SELECT c FROM hh WHERE (a COLLATE nocase, b) = (SELECT 'def', 2); 159} {iii iv} 160do_execsql_test 6.9 { 161 SELECT c FROM hh WHERE (a COLLATE nocase, b) IS NOT (SELECT 'def', 2); 162} {i ii v vi} 163do_execsql_test 6.10 { 164 SELECT c FROM hh WHERE (b, a) = (SELECT 2, 'def'); 165} {iii} 166 167do_execsql_test 7.0 { 168 CREATE TABLE xy(i INTEGER PRIMARY KEY, j, k); 169 INSERT INTO xy VALUES(1, 1, 1); 170 INSERT INTO xy VALUES(2, 2, 2); 171 INSERT INTO xy VALUES(3, 3, 3); 172 INSERT INTO xy VALUES(4, 4, 4); 173} 174 175 176foreach {tn sql res eqp} { 177 1 "SELECT * FROM xy WHERE (i, j) IS (2, 2)" {2 2 2} 178 "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid=?)}" 179 180 2 "SELECT * FROM xy WHERE (k, j) < (2, 3)" {1 1 1 2 2 2} 181 "0 0 0 {SCAN TABLE xy}" 182 183 3 "SELECT * FROM xy WHERE (i, j) < (2, 3)" {1 1 1 2 2 2} 184 "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid<?)}" 185 186 4 "SELECT * FROM xy WHERE (i, j) > (2, 1)" {2 2 2 3 3 3 4 4 4} 187 "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)}" 188 189 5 "SELECT * FROM xy WHERE (i, j) > ('2', 1)" {2 2 2 3 3 3 4 4 4} 190 "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)}" 191 192} { 193 do_eqp_test 7.$tn.1 $sql $eqp 194 do_execsql_test 7.$tn.2 $sql $res 195} 196 197do_execsql_test 8.0 { 198 CREATE TABLE j1(a); 199} 200do_execsql_test 8.1 { 201 SELECT * FROM j1 WHERE (select min(a) FROM j1) IN (?, ?, ?) 202} 203 204do_execsql_test 9.0 { 205 CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c); 206 INSERT INTO t2 VALUES(1, 1, 1); 207 INSERT INTO t2 VALUES(2, 2, 2); 208 INSERT INTO t2 VALUES(3, 3, 3); 209 INSERT INTO t2 VALUES(4, 4, 4); 210 INSERT INTO t2 VALUES(5, 5, 5); 211} 212 213foreach {tn q res} { 214 1 "(a, b) > (2, 1)" {2 3 4 5} 215 2 "(a, b) > (2, 2)" {3 4 5} 216 3 "(a, b) < (4, 5)" {1 2 3 4} 217 4 "(a, b) < (4, 3)" {1 2 3} 218} { 219 do_execsql_test 9.$tn "SELECT c FROM t2 WHERE $q" $res 220} 221 222do_execsql_test 10.0 { 223 CREATE TABLE dual(dummy); INSERT INTO dual(dummy) VALUES('X'); 224 CREATE TABLE t3(a TEXT,b TEXT,c TEXT,d TEXT,e TEXT,f TEXT); 225 CREATE INDEX t3x ON t3(b,c,d,e,f); 226 227 SELECT a FROM t3 228 WHERE (c,d) IN (SELECT 'c','d' FROM dual) 229 AND (a,b,e) IN (SELECT 'a','b','d' FROM dual); 230} 231 232do_catchsql_test 11.1 { 233 CREATE TABLE t11(a); 234 SELECT * FROM t11 WHERE (a,a)<=1; 235} {1 {row value misused}} 236do_catchsql_test 11.2 { 237 SELECT * FROM t11 WHERE (a,a)<1; 238} {1 {row value misused}} 239do_catchsql_test 11.3 { 240 SELECT * FROM t11 WHERE (a,a)>=1; 241} {1 {row value misused}} 242do_catchsql_test 11.4 { 243 SELECT * FROM t11 WHERE (a,a)>1; 244} {1 {row value misused}} 245do_catchsql_test 11.5 { 246 SELECT * FROM t11 WHERE (a,a)==1; 247} {1 {row value misused}} 248do_catchsql_test 11.6 { 249 SELECT * FROM t11 WHERE (a,a)<>1; 250} {1 {row value misused}} 251do_catchsql_test 11.7 { 252 SELECT * FROM t11 WHERE (a,a) IS 1; 253} {1 {row value misused}} 254do_catchsql_test 11.8 { 255 SELECT * FROM t11 WHERE (a,a) IS NOT 1; 256} {1 {row value misused}} 257 258 259finish_test 260