OLD | NEW |
(Empty) | |
| 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 |
| 16 set testdir [file dirname $argv0] |
| 17 source $testdir/tester.tcl |
| 18 set ::testprefix rowvalue |
| 19 |
| 20 do_execsql_test 0.0 { |
| 21 CREATE TABLE one(o); |
| 22 INSERT INTO one VALUES(1); |
| 23 } |
| 24 |
| 25 foreach {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 |
| 45 foreach {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 |
| 74 do_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 |
| 84 foreach {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 |
| 103 foreach {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 |
| 111 foreach {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 |
| 124 do_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 |
| 136 do_execsql_test 6.1 { |
| 137 SELECT c FROM hh WHERE (a, b) = (SELECT 'abc', 1); |
| 138 } {i} |
| 139 do_execsql_test 6.2 { |
| 140 SELECT c FROM hh WHERE (a, b) = (SELECT 'abc' COLLATE nocase, 1); |
| 141 } {i} |
| 142 do_execsql_test 6.3 { |
| 143 SELECT c FROM hh WHERE a = (SELECT 'abc' COLLATE nocase) AND b = (SELECT 1); |
| 144 } {i} |
| 145 do_execsql_test 6.4 { |
| 146 SELECT c FROM hh WHERE +a = (SELECT 'abc' COLLATE nocase) AND b = (SELECT 1); |
| 147 } {i} |
| 148 do_execsql_test 6.5 { |
| 149 SELECT c FROM hh WHERE a = (SELECT 'abc') COLLATE nocase AND b = (SELECT 1); |
| 150 } {i ii} |
| 151 do_catchsql_test 6.6 { |
| 152 SELECT c FROM hh WHERE (a, b) = (SELECT 'abc', 1) COLLATE nocase; |
| 153 } {1 {row value misused}} |
| 154 do_catchsql_test 6.7 { |
| 155 SELECT c FROM hh WHERE (a, b) = 1; |
| 156 } {1 {row value misused}} |
| 157 do_execsql_test 6.8 { |
| 158 SELECT c FROM hh WHERE (a COLLATE nocase, b) = (SELECT 'def', 2); |
| 159 } {iii iv} |
| 160 do_execsql_test 6.9 { |
| 161 SELECT c FROM hh WHERE (a COLLATE nocase, b) IS NOT (SELECT 'def', 2); |
| 162 } {i ii v vi} |
| 163 do_execsql_test 6.10 { |
| 164 SELECT c FROM hh WHERE (b, a) = (SELECT 2, 'def'); |
| 165 } {iii} |
| 166 |
| 167 do_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 |
| 176 foreach {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 |
| 197 do_execsql_test 8.0 { |
| 198 CREATE TABLE j1(a); |
| 199 } |
| 200 do_execsql_test 8.1 { |
| 201 SELECT * FROM j1 WHERE (select min(a) FROM j1) IN (?, ?, ?) |
| 202 } |
| 203 |
| 204 do_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 |
| 213 foreach {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 |
| 222 do_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 |
| 232 do_catchsql_test 11.1 { |
| 233 CREATE TABLE t11(a); |
| 234 SELECT * FROM t11 WHERE (a,a)<=1; |
| 235 } {1 {row value misused}} |
| 236 do_catchsql_test 11.2 { |
| 237 SELECT * FROM t11 WHERE (a,a)<1; |
| 238 } {1 {row value misused}} |
| 239 do_catchsql_test 11.3 { |
| 240 SELECT * FROM t11 WHERE (a,a)>=1; |
| 241 } {1 {row value misused}} |
| 242 do_catchsql_test 11.4 { |
| 243 SELECT * FROM t11 WHERE (a,a)>1; |
| 244 } {1 {row value misused}} |
| 245 do_catchsql_test 11.5 { |
| 246 SELECT * FROM t11 WHERE (a,a)==1; |
| 247 } {1 {row value misused}} |
| 248 do_catchsql_test 11.6 { |
| 249 SELECT * FROM t11 WHERE (a,a)<>1; |
| 250 } {1 {row value misused}} |
| 251 do_catchsql_test 11.7 { |
| 252 SELECT * FROM t11 WHERE (a,a) IS 1; |
| 253 } {1 {row value misused}} |
| 254 do_catchsql_test 11.8 { |
| 255 SELECT * FROM t11 WHERE (a,a) IS NOT 1; |
| 256 } {1 {row value misused}} |
| 257 |
| 258 # 2016-10-27: https://www.sqlite.org/src/tktview/fef4bb4bd9185ec8f |
| 259 # Incorrect result from a LEFT JOIN with a row-value constraint |
| 260 # |
| 261 do_execsql_test 12.1 { |
| 262 DROP TABLE IF EXISTS t1; |
| 263 CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,2); |
| 264 DROP TABLE IF EXISTS t2; |
| 265 CREATE TABLE t2(x,y); INSERT INTO t2 VALUES(3,4); |
| 266 SELECT *,'x' FROM t1 LEFT JOIN t2 ON (a,b)=(x,y); |
| 267 } {1 2 {} {} x} |
| 268 |
| 269 |
| 270 foreach {tn sql} { |
| 271 0 "SELECT (1,2) AS x WHERE x=3" |
| 272 1 "SELECT (1,2) BETWEEN 1 AND 2" |
| 273 2 "SELECT 1 BETWEEN (1,2) AND 2" |
| 274 3 "SELECT 2 BETWEEN 1 AND (1,2)" |
| 275 4 "SELECT (1,2) FROM (SELECT 1) ORDER BY 1" |
| 276 5 "SELECT (1,2) FROM (SELECT 1) GROUP BY 1" |
| 277 } { |
| 278 do_catchsql_test 13.$tn $sql {1 {row value misused}} |
| 279 } |
| 280 |
| 281 do_execsql_test 14.0 { |
| 282 CREATE TABLE t12(x); |
| 283 INSERT INTO t12 VALUES(2), (4); |
| 284 } |
| 285 do_execsql_test 14.1 "SELECT 1 WHERE (2,2) BETWEEN (1,1) AND (3,3)" 1 |
| 286 do_execsql_test 14.2 "SELECT CASE (2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1 |
| 287 do_execsql_test 14.3 "SELECT CASE (SELECT 2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1 |
| 288 do_execsql_test 14.4 "SELECT 1 WHERE (SELECT 2,2) BETWEEN (1,1) AND (3,3)" 1 |
| 289 do_execsql_test 14.5 "SELECT 1 FROM t12 WHERE (x,1) BETWEEN (1,1) AND (3,3)" 1 |
| 290 do_execsql_test 14.6 { |
| 291 SELECT 1 FROM t12 WHERE (1,x) BETWEEN (1,1) AND (3,3) |
| 292 } {1 1} |
| 293 |
| 294 #------------------------------------------------------------------------- |
| 295 # Test that errors are not concealed by the SELECT flattening or |
| 296 # WHERE-clause push-down optimizations. |
| 297 do_execsql_test 14.1 { |
| 298 CREATE TABLE x1(a PRIMARY KEY, b); |
| 299 CREATE TABLE x2(a INTEGER PRIMARY KEY, b); |
| 300 } |
| 301 |
| 302 foreach {tn n sql} { |
| 303 1 0 "SELECT * FROM (SELECT (1, 1) AS c FROM x1) WHERE c=1" |
| 304 2 2 "SELECT * FROM (SELECT 1 AS x, (SELECT 8,9) AS y) WHERE y<1" |
| 305 3 3 "SELECT * FROM (SELECT 1 AS x, (SELECT 8,9,10) AS y) WHERE y<1" |
| 306 4 0 "SELECT * FROM (SELECT (a, b) AS c FROM x1), x2 WHERE c=a" |
| 307 5 0 "SELECT * FROM (SELECT a AS c, (1, 2, 3) FROM x1), x2 WHERE c=a" |
| 308 6 0 "SELECT * FROM (SELECT 1 AS c, (1, 2, 3) FROM x1) WHERE c=1" |
| 309 } { |
| 310 if {$n==0} { |
| 311 set err "row value misused" |
| 312 } else { |
| 313 set err "sub-select returns $n columns - expected 1" |
| 314 } |
| 315 do_catchsql_test 14.2.$tn $sql [list 1 $err] |
| 316 } |
| 317 |
| 318 #-------------------------------------------------------------------------- |
| 319 # Test for vector size mismatches concealed by unexpanded subqueries. |
| 320 # |
| 321 do_catchsql_test 15.1 { |
| 322 DETACH (SELECT * FROM (SELECT 1,2))<3; |
| 323 } {1 {row value misused}} |
| 324 do_catchsql_test 15.2 { |
| 325 UPDATE x1 SET a=(SELECT * FROM (SELECT b,2))<3; |
| 326 } {1 {row value misused}} |
| 327 do_catchsql_test 15.3 { |
| 328 UPDATE x1 SET a=NULL WHERE a<(SELECT * FROM (SELECT b,2)); |
| 329 } {1 {sub-select returns 2 columns - expected 1}} |
| 330 do_catchsql_test 15.4 { |
| 331 DELETE FROM x1 WHERE a<(SELECT * FROM (SELECT b,2)); |
| 332 } {1 {sub-select returns 2 columns - expected 1}} |
| 333 do_catchsql_test 15.5 { |
| 334 INSERT INTO x1(a,b) VALUES(1,(SELECT * FROM (SELECT 1,2))<3); |
| 335 } {1 {row value misused}} |
| 336 |
| 337 #------------------------------------------------------------------------- |
| 338 # Row-values used in UPDATE statements within TRIGGERs |
| 339 # |
| 340 # Ticket https://www.sqlite.org/src/info/8c9458e703666e1a |
| 341 # |
| 342 do_execsql_test 16.1 { |
| 343 CREATE TABLE t16a(a,b,c); |
| 344 INSERT INTO t16a VALUES(1,2,3); |
| 345 CREATE TABLE t16b(x); |
| 346 INSERT INTO t16b(x) VALUES(1); |
| 347 CREATE TRIGGER t16r AFTER UPDATE ON t16b BEGIN |
| 348 UPDATE t16a SET (a,b,c)=(SELECT new.x,new.x+1,new.x+2); |
| 349 END; |
| 350 UPDATE t16b SET x=7; |
| 351 SELECT * FROM t16a; |
| 352 } {7 8 9} |
| 353 do_execsql_test 16.2 { |
| 354 UPDATE t16b SET x=97; |
| 355 SELECT * FROM t16a; |
| 356 } {97 98 99} |
| 357 |
| 358 do_execsql_test 16.3 { |
| 359 CREATE TABLE t16c(a, b, c, d, e); |
| 360 INSERT INTO t16c VALUES(1, 'a', 'b', 'c', 'd'); |
| 361 CREATE TRIGGER t16c1 AFTER INSERT ON t16c BEGIN |
| 362 UPDATE t16c SET (c, d) = (SELECT 'A', 'B'), (e, b) = (SELECT 'C', 'D') |
| 363 WHERE a = new.a-1; |
| 364 END; |
| 365 |
| 366 SELECT * FROM t16c; |
| 367 } {1 a b c d} |
| 368 |
| 369 do_execsql_test 16.4 { |
| 370 INSERT INTO t16c VALUES(2, 'w', 'x', 'y', 'z'); |
| 371 SELECT * FROM t16c; |
| 372 } { |
| 373 1 D A B C |
| 374 2 w x y z |
| 375 } |
| 376 |
| 377 do_execsql_test 16.5 { |
| 378 DROP TRIGGER t16c1; |
| 379 PRAGMA recursive_triggers = 1; |
| 380 INSERT INTO t16c VALUES(3, 'i', 'ii', 'iii', 'iv'); |
| 381 CREATE TRIGGER t16c1 AFTER UPDATE ON t16c WHEN new.a>1 BEGIN |
| 382 UPDATE t16c SET (e, d) = ( |
| 383 SELECT b, c FROM t16c WHERE a = new.a-1 |
| 384 ), (c, b) = ( |
| 385 SELECT d, e FROM t16c WHERE a = new.a-1 |
| 386 ) WHERE a = new.a-1; |
| 387 END; |
| 388 |
| 389 UPDATE t16c SET a=a WHERE a=3; |
| 390 SELECT * FROM t16c; |
| 391 } { |
| 392 1 C B A D |
| 393 2 z y x w |
| 394 3 i ii iii iv |
| 395 } |
| 396 |
| 397 finish_test |
OLD | NEW |