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 "SEARCH xy USING INTEGER PRIMARY KEY (rowid=?)" 179 180 2 "SELECT * FROM xy WHERE (k, j) < (2, 3)" {1 1 1 2 2 2} 181 "SCAN xy" 182 183 3 "SELECT * FROM xy WHERE (i, j) < (2, 3)" {1 1 1 2 2 2} 184 "SEARCH 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 "SEARCH 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 "SEARCH 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# 2016-10-27: https://www.sqlite.org/src/tktview/fef4bb4bd9185ec8f 259# Incorrect result from a LEFT JOIN with a row-value constraint 260# 261do_execsql_test 12.1 { 262 DROP TABLE IF EXISTS t1; 263 CREATE TABLE t1(a INT,b INT); INSERT INTO t1 VALUES(1,2); 264 DROP TABLE IF EXISTS t2; 265 CREATE TABLE t2(x INT,y INT); INSERT INTO t2 VALUES(3,4); 266 SELECT *,'x' FROM t1 LEFT JOIN t2 ON (a,b)=(x,y); 267} {1 2 {} {} x} 268db null - 269do_execsql_test 12.2 { 270 SELECT t1.*, t2.* FROM t2 RIGHT JOIN t1 ON (a,b)=(x,y); 271} {1 2 - -} 272do_execsql_test 12.3 { 273 SELECT t1.*, t2.* FROM t1 FULL JOIN t2 ON (a,b)=(x,y) 274 ORDER BY coalesce(a,x); 275} { 276 1 2 - - 277 - - 3 4 278} 279db null {} 280 281 282foreach {tn sql} { 283 0 "SELECT (1,2) AS x WHERE x=3" 284 1 "SELECT (1,2) BETWEEN 1 AND 2" 285 2 "SELECT 1 BETWEEN (1,2) AND 2" 286 3 "SELECT 2 BETWEEN 1 AND (1,2)" 287 4 "SELECT (1,2) FROM (SELECT 1) ORDER BY 1" 288 5 "SELECT (1,2) FROM (SELECT 1) GROUP BY 1" 289} { 290 do_catchsql_test 13.$tn $sql {1 {row value misused}} 291} 292 293do_execsql_test 14.0 { 294 CREATE TABLE t12(x); 295 INSERT INTO t12 VALUES(2), (4); 296} 297do_execsql_test 14.1 "SELECT 1 WHERE (2,2) BETWEEN (1,1) AND (3,3)" 1 298do_execsql_test 14.2 "SELECT CASE (2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1 299do_execsql_test 14.3 "SELECT CASE (SELECT 2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1 300do_execsql_test 14.4 "SELECT 1 WHERE (SELECT 2,2) BETWEEN (1,1) AND (3,3)" 1 301do_execsql_test 14.5 "SELECT 1 FROM t12 WHERE (x,1) BETWEEN (1,1) AND (3,3)" 1 302do_execsql_test 14.6 { 303 SELECT 1 FROM t12 WHERE (1,x) BETWEEN (1,1) AND (3,3) 304} {1 1} 305 306#------------------------------------------------------------------------- 307# Test that errors are not concealed by the SELECT flattening or 308# WHERE-clause push-down optimizations. 309do_execsql_test 14.1 { 310 CREATE TABLE x1(a PRIMARY KEY, b); 311 CREATE TABLE x2(a INTEGER PRIMARY KEY, b); 312} 313 314foreach {tn n sql} { 315 1 0 "SELECT * FROM (SELECT (1, 1) AS c FROM x1) WHERE c=1" 316 2 2 "SELECT * FROM (SELECT 1 AS x, (SELECT 8,9) AS y) WHERE y<1" 317 3 3 "SELECT * FROM (SELECT 1 AS x, (SELECT 8,9,10) AS y) WHERE y<1" 318 4 0 "SELECT * FROM (SELECT (a, b) AS c FROM x1), x2 WHERE c=a" 319 5 0 "SELECT * FROM (SELECT a AS c, (1, 2, 3) FROM x1), x2 WHERE c=a" 320 6 0 "SELECT * FROM (SELECT 1 AS c, (1, 2, 3) FROM x1) WHERE c=1" 321} { 322 if {$n==0} { 323 set err "row value misused" 324 } else { 325 set err "sub-select returns $n columns - expected 1" 326 } 327 do_catchsql_test 14.2.$tn $sql [list 1 $err] 328} 329 330#-------------------------------------------------------------------------- 331# Test for vector size mismatches concealed by unexpanded subqueries. 332# 333do_catchsql_test 15.1 { 334 DETACH (SELECT * FROM (SELECT 1,2))<3; 335} {1 {row value misused}} 336do_catchsql_test 15.2 { 337 UPDATE x1 SET a=(SELECT * FROM (SELECT b,2))<3; 338} {1 {row value misused}} 339do_catchsql_test 15.3 { 340 UPDATE x1 SET a=NULL WHERE a<(SELECT * FROM (SELECT b,2)); 341} {1 {sub-select returns 2 columns - expected 1}} 342do_catchsql_test 15.4 { 343 DELETE FROM x1 WHERE a<(SELECT * FROM (SELECT b,2)); 344} {1 {sub-select returns 2 columns - expected 1}} 345do_catchsql_test 15.5 { 346 INSERT INTO x1(a,b) VALUES(1,(SELECT * FROM (SELECT 1,2))<3); 347} {1 {row value misused}} 348 349#------------------------------------------------------------------------- 350# Row-values used in UPDATE statements within TRIGGERs 351# 352# Ticket https://www.sqlite.org/src/info/8c9458e703666e1a 353# 354do_execsql_test 16.1 { 355 CREATE TABLE t16a(a,b,c); 356 INSERT INTO t16a VALUES(1,2,3); 357 CREATE TABLE t16b(x); 358 INSERT INTO t16b(x) VALUES(1); 359 CREATE TRIGGER t16r AFTER UPDATE ON t16b BEGIN 360 UPDATE t16a SET (a,b,c)=(SELECT new.x,new.x+1,new.x+2); 361 END; 362 UPDATE t16b SET x=7; 363 SELECT * FROM t16a; 364} {7 8 9} 365do_execsql_test 16.2 { 366 UPDATE t16b SET x=97; 367 SELECT * FROM t16a; 368} {97 98 99} 369 370do_execsql_test 16.3 { 371 CREATE TABLE t16c(a, b, c, d, e); 372 INSERT INTO t16c VALUES(1, 'a', 'b', 'c', 'd'); 373 CREATE TRIGGER t16c1 AFTER INSERT ON t16c BEGIN 374 UPDATE t16c SET (c, d) = (SELECT 'A', 'B'), (e, b) = (SELECT 'C', 'D') 375 WHERE a = new.a-1; 376 END; 377 378 SELECT * FROM t16c; 379} {1 a b c d} 380 381do_execsql_test 16.4 { 382 INSERT INTO t16c VALUES(2, 'w', 'x', 'y', 'z'); 383 SELECT * FROM t16c; 384} { 385 1 D A B C 386 2 w x y z 387} 388 389do_execsql_test 16.5 { 390 DROP TRIGGER t16c1; 391 PRAGMA recursive_triggers = 1; 392 INSERT INTO t16c VALUES(3, 'i', 'ii', 'iii', 'iv'); 393 CREATE TRIGGER t16c1 AFTER UPDATE ON t16c WHEN new.a>1 BEGIN 394 UPDATE t16c SET (e, d) = ( 395 SELECT b, c FROM t16c WHERE a = new.a-1 396 ), (c, b) = ( 397 SELECT d, e FROM t16c WHERE a = new.a-1 398 ) WHERE a = new.a-1; 399 END; 400 401 UPDATE t16c SET a=a WHERE a=3; 402 SELECT * FROM t16c; 403} { 404 1 C B A D 405 2 z y x w 406 3 i ii iii iv 407} 408 409do_execsql_test 17.0 { 410 CREATE TABLE b1(a, b); 411 CREATE TABLE b2(x); 412} 413 414do_execsql_test 17.1 { 415 SELECT * FROM b2 CROSS JOIN b1 416 WHERE b2.x=b1.a AND (b1.a, 2) 417 IN (VALUES(1, 2)); 418} {} 419 420do_execsql_test 18.0 { 421 CREATE TABLE b3 ( a, b, PRIMARY KEY (a, b) ); 422 CREATE TABLE b4 ( a ); 423 CREATE TABLE b5 ( a, b ); 424 INSERT INTO b3 VALUES (1, 1), (1, 2); 425 INSERT INTO b4 VALUES (1); 426 INSERT INTO b5 VALUES (1, 1), (1, 2); 427} 428 429do_execsql_test 18.1 { 430 SELECT * FROM b3 WHERE (SELECT b3.a, b3.b) IN ( SELECT a, b FROM b5 ) 431} {1 1 1 2} 432do_execsql_test 18.2 { 433 SELECT * FROM b3 WHERE (VALUES(b3.a, b3.b)) IN ( SELECT a, b FROM b5 ); 434} {1 1 1 2} 435do_execsql_test 18.3 { 436 SELECT * FROM b3 WHERE (b3.a, b3.b) IN ( SELECT a, b FROM b5 ); 437} {1 1 1 2} 438do_execsql_test 18.4 { 439 SELECT * FROM b3 JOIN b4 ON b4.a = b3.a 440 WHERE (SELECT b3.a, b3.b) IN ( SELECT a, b FROM b5 ); 441} {1 1 1 1 2 1} 442do_execsql_test 18.5 { 443 SELECT * FROM b3 JOIN b4 ON b4.a = b3.a 444 WHERE (VALUES(b3.a, b3.b)) IN ( SELECT a, b FROM b5 ); 445} {1 1 1 1 2 1} 446do_execsql_test 18.6 { 447 SELECT * FROM b3 JOIN b4 ON b4.a = b3.a 448 WHERE (b3.a, b3.b) IN ( SELECT a, b FROM b5 ); 449} {1 1 1 1 2 1} 450 451 452# 2018-02-13 Ticket https://www.sqlite.org/src/tktview/f484b65f3d6230593c3 453# Incorrect result from a row-value comparison in the WHERE clause. 454# 455do_execsql_test 19.1 { 456 DROP TABLE IF EXISTS t1; 457 CREATE TABLE t1(a INTEGER PRIMARY KEY,b); 458 INSERT INTO t1(a,b) VALUES(1,11),(2,22),(3,33),(4,44); 459 SELECT * FROM t1 WHERE (a,b)>(0,0) ORDER BY a; 460} {1 11 2 22 3 33 4 44} 461do_execsql_test 19.2 { 462 SELECT * FROM t1 WHERE (a,b)>=(0,0) ORDER BY a; 463} {1 11 2 22 3 33 4 44} 464do_execsql_test 19.3 { 465 SELECT * FROM t1 WHERE (a,b)<(5,0) ORDER BY a DESC; 466} {4 44 3 33 2 22 1 11} 467do_execsql_test 19.4 { 468 SELECT * FROM t1 WHERE (a,b)<=(5,0) ORDER BY a DESC; 469} {4 44 3 33 2 22 1 11} 470do_execsql_test 19.5 { 471 SELECT * FROM t1 WHERE (a,b)>(3,0) ORDER BY a; 472} {3 33 4 44} 473do_execsql_test 19.6 { 474 SELECT * FROM t1 WHERE (a,b)>=(3,0) ORDER BY a; 475} {3 33 4 44} 476do_execsql_test 19.7 { 477 SELECT * FROM t1 WHERE (a,b)<(3,0) ORDER BY a DESC; 478} {2 22 1 11} 479do_execsql_test 19.8 { 480 SELECT * FROM t1 WHERE (a,b)<=(3,0) ORDER BY a DESC; 481} {2 22 1 11} 482do_execsql_test 19.9 { 483 SELECT * FROM t1 WHERE (a,b)>(3,32) ORDER BY a; 484} {3 33 4 44} 485do_execsql_test 19.10 { 486 SELECT * FROM t1 WHERE (a,b)>(3,33) ORDER BY a; 487} {4 44} 488do_execsql_test 19.11 { 489 SELECT * FROM t1 WHERE (a,b)>=(3,33) ORDER BY a; 490} {3 33 4 44} 491do_execsql_test 19.12 { 492 SELECT * FROM t1 WHERE (a,b)>=(3,34) ORDER BY a; 493} {4 44} 494do_execsql_test 19.13 { 495 SELECT * FROM t1 WHERE (a,b)<(3,34) ORDER BY a DESC; 496} {3 33 2 22 1 11} 497do_execsql_test 19.14 { 498 SELECT * FROM t1 WHERE (a,b)<(3,33) ORDER BY a DESC; 499} {2 22 1 11} 500do_execsql_test 19.15 { 501 SELECT * FROM t1 WHERE (a,b)<=(3,33) ORDER BY a DESC; 502} {3 33 2 22 1 11} 503do_execsql_test 19.16 { 504 SELECT * FROM t1 WHERE (a,b)<=(3,32) ORDER BY a DESC; 505} {2 22 1 11} 506do_execsql_test 19.21 { 507 SELECT * FROM t1 WHERE (0,0)<(a,b) ORDER BY a; 508} {1 11 2 22 3 33 4 44} 509do_execsql_test 19.22 { 510 SELECT * FROM t1 WHERE (0,0)<=(a,b) ORDER BY a; 511} {1 11 2 22 3 33 4 44} 512do_execsql_test 19.23 { 513 SELECT * FROM t1 WHERE (5,0)>(a,b) ORDER BY a DESC; 514} {4 44 3 33 2 22 1 11} 515do_execsql_test 19.24 { 516 SELECT * FROM t1 WHERE (5,0)>=(a,b) ORDER BY a DESC; 517} {4 44 3 33 2 22 1 11} 518do_execsql_test 19.25 { 519 SELECT * FROM t1 WHERE (3,0)<(a,b) ORDER BY a; 520} {3 33 4 44} 521do_execsql_test 19.26 { 522 SELECT * FROM t1 WHERE (3,0)<=(a,b) ORDER BY a; 523} {3 33 4 44} 524do_execsql_test 19.27 { 525 SELECT * FROM t1 WHERE (3,0)>(a,b) ORDER BY a DESC; 526} {2 22 1 11} 527do_execsql_test 19.28 { 528 SELECT * FROM t1 WHERE (3,0)>=(a,b) ORDER BY a DESC; 529} {2 22 1 11} 530do_execsql_test 19.29 { 531 SELECT * FROM t1 WHERE (3,32)<(a,b) ORDER BY a; 532} {3 33 4 44} 533do_execsql_test 19.30 { 534 SELECT * FROM t1 WHERE (3,33)<(a,b) ORDER BY a; 535} {4 44} 536do_execsql_test 19.31 { 537 SELECT * FROM t1 WHERE (3,33)<=(a,b) ORDER BY a; 538} {3 33 4 44} 539do_execsql_test 19.32 { 540 SELECT * FROM t1 WHERE (3,34)<=(a,b) ORDER BY a; 541} {4 44} 542do_execsql_test 19.33 { 543 SELECT * FROM t1 WHERE (3,34)>(a,b) ORDER BY a DESC; 544} {3 33 2 22 1 11} 545do_execsql_test 19.34 { 546 SELECT * FROM t1 WHERE (3,33)>(a,b) ORDER BY a DESC; 547} {2 22 1 11} 548do_execsql_test 19.35 { 549 SELECT * FROM t1 WHERE (3,33)>=(a,b) ORDER BY a DESC; 550} {3 33 2 22 1 11} 551do_execsql_test 19.36 { 552 SELECT * FROM t1 WHERE (3,32)>=(a,b) ORDER BY a DESC; 553} {2 22 1 11} 554 555# 2018-02-18: Memory leak nested row-value. Detected by OSSFuzz. 556# 557do_catchsql_test 20.1 { 558 SELECT 1 WHERE (2,(2,0)) IS (2,(2,0)); 559} {0 1} 560 561# 2018-11-03: Ticket https://www.sqlite.org/src/info/1a84668dcfdebaf1 562# Assertion fault when doing row-value operations on a primary key 563# containing duplicate columns. 564# 565do_execsql_test 21.0 { 566 DROP TABLE IF EXISTS t1; 567 CREATE TABLE t1(a,b,PRIMARY KEY(b,b)); 568 INSERT INTO t1 VALUES(1,2),(3,4),(5,6); 569 SELECT * FROM t1 WHERE (a,b) IN (VALUES(1,2)); 570} {1 2} 571 572# 2019-08-09: Multi-column subquery on the RHS of an IN operator. 573# 574do_execsql_test 22.100 { 575 SELECT (SELECT 3,4 UNION SELECT 5,6 ORDER BY 1) IN (SELECT 3,4); 576 SELECT (SELECT 3,4 UNION SELECT 5,6 ORDER BY 1) IN (SELECT 5,6); 577 SELECT (SELECT 5,6 UNION SELECT 3,4 ORDER BY 1) IN (SELECT 3,4); 578 SELECT (SELECT 5,6 UNION SELECT 3,4 ORDER BY 1) IN (SELECT 5,6); 579 SELECT (SELECT 3,4 UNION SELECT 5,6 ORDER BY 1 DESC) IN (SELECT 3,4); 580 SELECT (SELECT 3,4 UNION SELECT 5,6 ORDER BY 1 DESC) IN (SELECT 5,6); 581 SELECT (SELECT 5,6 UNION SELECT 3,4 ORDER BY 1 DESC) IN (SELECT 3,4); 582 SELECT (SELECT 5,6 UNION SELECT 3,4 ORDER BY 1 DESC) IN (SELECT 5,6); 583} {1 0 1 0 0 1 0 1} 584 585# 2019-10-21 Ticket b47e3627ecaadbde 586# 587do_execsql_test 23.100 { 588 DROP TABLE IF EXISTS t0; 589 CREATE TABLE t0(aa COLLATE NOCASE, bb); 590 INSERT INTO t0 VALUES('a', 'A'); 591 SELECT (+bb,1) >= (aa, 1), (aa,1)<=(+bb,1) FROM t0; 592 SELECT 2 FROM t0 WHERE (+bb,1) >= (aa,1); 593 SELECT 3 FROM t0 WHERE (aa,1) <= (+bb,1); 594} {0 1 3} 595do_execsql_test 23.110 { 596 SELECT (SELECT +bb,1) >= (aa, 1), (aa,1)<=(SELECT +bb,1) FROM t0; 597 SELECT 2 FROM t0 WHERE (SELECT +bb,1) >= (aa,1); 598 SELECT 3 FROM t0 WHERE (aa,1) <= (SELECT +bb,1); 599} {0 1 3} 600 601# 2019-10-22 Ticket 6ef984af8972c2eb 602do_execsql_test 24.100 { 603 DROP TABLE t0; 604 CREATE TABLE t0(c0 TEXT PRIMARY KEY); 605 INSERT INTO t0(c0) VALUES (''); 606 SELECT (t0.c0, TRUE) > (CAST(0 AS REAL), FALSE) FROM t0; 607 SELECT 2 FROM t0 WHERE (t0.c0, TRUE) > (CAST('' AS REAL), FALSE); 608} {1 2} 609 610# 2019-10-23 Ticket 135c9da7513e5a97 611do_execsql_test 25.10 { 612 DROP TABLE t0; 613 CREATE TABLE t0(c0 UNIQUE); 614 INSERT INTO t0(c0) VALUES('a'); 615 SELECT (t0.c0, 0) < ('B' COLLATE NOCASE, 0) FROM t0; 616 SELECT 2 FROM t0 WHERE (t0.c0, 0) < ('B' COLLATE NOCASE, 0); 617} {1 2} 618do_execsql_test 25.20 { 619 SELECT ('B' COLLATE NOCASE, 0)> (t0.c0, 0) FROM t0; 620 SELECT 2 FROM t0 WHERE ('B' COLLATE NOCASE, 0)> (t0.c0, 0); 621} {1 2} 622do_execsql_test 25.30 { 623 SELECT ('B', 0)> (t0.c0 COLLATE nocase, 0) FROM t0; 624 SELECT 2 FROM t0 WHERE ('B', 0)> (t0.c0 COLLATE nocase, 0); 625} {1 2} 626do_execsql_test 25.40 { 627 SELECT (t0.c0 COLLATE nocase, 0) < ('B', 0) FROM t0; 628 SELECT 2 FROM t0 WHERE (t0.c0 COLLATE nocase, 0) < ('B', 0); 629} {1 2} 630 631# 2019-11-04 Ticket 02aa2bd02f97d0f2 632# The TK_VECTOR operator messes up sqlite3ExprImpliesNonNull() which 633# causes incorrect LEFT JOIN strength reduction. TK_VECTOR should be 634# treated the same as TK_OR. 635# 636db close 637sqlite3 db :memory: 638do_execsql_test 26.10 { 639 CREATE TABLE t0(c0); 640 CREATE TABLE t1(c1); 641 INSERT INTO t1(c1) VALUES (0); 642 SELECT (c0, x'') != (NULL, 0) FROM t1 LEFT JOIN t0; 643} {1} 644do_execsql_test 26.20 { 645 SELECT 2 FROM t1 LEFT JOIN t0 ON (c0, x'') != (NULL, 0); 646} {2} 647do_execsql_test 26.21 { 648 SELECT 21 FROM t0 RIGHT JOIN t1 ON (c0, x'') != (NULL, 0); 649} {21} 650do_execsql_test 26.30 { 651 SELECT 3 FROM t1 LEFT JOIN t0 WHERE (c0, x'') != (NULL, 0); 652} {3} 653do_execsql_test 26.31 { 654 SELECT 31 FROM t0 RIGHT JOIN t1 WHERE (c0, x'') != (NULL, 0); 655} {31} 656 657# 2019-12-30 ticket 892575cdba4e1e36 658# 659reset_db 660do_catchsql_test 27.10 { 661 CREATE TABLE t0(c0 CHECK(((0, 0) > (0, c0)))); 662 INSERT INTO t0(c0) VALUES(0) ON CONFLICT(c0) DO UPDATE SET c0 = 3; 663} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} 664 665# 2021-02-03 666# https://bugs.chromium.org/p/chromium/issues/detail?id=1173511 667# Faulty assert() statement. 668# 669reset_db 670do_catchsql_test 28.10 { 671 CREATE TABLE t0(c0 PRIMARY KEY, c1); 672 CREATE TRIGGER trigger0 BEFORE DELETE ON t0 BEGIN 673 SELECT (SELECT c0,c1 FROM t0) FROM t0; 674 END ; 675 DELETE FROM t0; 676} {1 {sub-select returns 2 columns - expected 1}} 677 678# 2021-03-19 679# dbsqlfuzz find of a NEVER(). 680do_catchsql_test 29.1 { 681 SELECT (SELECT 1 WHERE ((SELECT 1 WHERE (2,(2,0)) IS (2,(20))),(2,0)) IS (2,(20))) WHERE (2,(2,0)) IS (2 IN(SELECT 1 WHERE (2,(2,2,0)) IS (2,(20))),(20)); 682} {1 {row value misused}} 683 684#------------------------------------------------------------------------- 685reset_db 686do_execsql_test 30.0 { 687 CREATE TABLE t1(x, y, z); 688 CREATE TABLE t2(a, b); 689 690 INSERT INTO t1 VALUES(1000, 2000, 3000); 691 INSERT INTO t2 VALUES(NULL, NULL); 692} 693 694do_execsql_test 30.1 { 695 UPDATE t2 SET (a,b)=( 696 SELECT max( t1.x ) OVER( PARTITION BY sum( (SELECT t1.y) ) ), 2 697 ) 698 FROM t1; 699} {} 700 701do_execsql_test 30.2 { 702 SELECT * FROM t2 703} {1000 2} 704 705reset_db 706do_execsql_test 30.3 { 707 CREATE TABLE t1(x INT PRIMARY KEY, y, z); 708 CREATE TABLE t2(a,b,c,d,e,PRIMARY KEY(a,b))WITHOUT ROWID; 709 710 UPDATE t2 SET (d,d,a)=(SELECT EXISTS(SELECT 1 IN(SELECT max( 1 IN(SELECT x ORDER BY 1)) OVER(PARTITION BY sum((SELECT y FROM t1 UNION SELECT x ORDER BY 1)))INTERSECT SELECT EXISTS(SELECT 1 FROM t1 UNION SELECT x ORDER BY 1) ORDER BY 1) ORDERa)|9 AS blob, 2, 3) FROM t1 WHERE x<a; 711} 712 713# 2022-01-21 https://sqlite.org/forum/forumpost/ab95010d410a0a55 714reset_db 715do_execsql_test 31.1 { 716 CREATE TABLE a(a1 PRIMARY KEY,a2); 717 INSERT INTO a VALUES(1,5); 718 CREATE TABLE b(b1 UNIQUE,b2); 719 SELECT * FROM a LEFT JOIN b ON b2=NULL AND b2=5 WHERE (b1,substr(b.b1,1,1))==(SELECT 1024,'b'); 720} {} 721do_execsql_test 31.1b { 722 SELECT * FROM b RIGHT JOIN a ON b2=NULL AND b2=5 WHERE (b1,substr(b.b1,1,1))==(SELECT 1024,'b'); 723} {} 724do_execsql_test 31.2 { 725 CREATE TABLE t1(a); 726 INSERT INTO t1 VALUES(0); 727 CREATE TABLE t2(b,c,d); 728 INSERT INTO t2 VALUES(NULL,123,456); 729 SELECT * FROM t1 LEFT JOIN t2 ON b=NULL WHERE (c,d)==(SELECT 123, 456+a); 730} {} 731do_execsql_test 31.2b { 732 SELECT * FROM t2 RIGHT JOIN t1 ON b=NULL WHERE (c,d)==(SELECT 123, 456+a); 733} {} 734 735# 2022-02-03 dbsqlfuzz 80a9fade844b4fb43564efc972bcb2c68270f5d1 736reset_db 737do_execsql_test 32.1 { 738 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT); 739 CREATE TABLE t2(d INTEGER PRIMARY KEY); 740 INSERT INTO t1(a,b,c) VALUES(500,654,456); 741 INSERT INTO t1(a,b,c) VALUES(501,655,456); 742 INSERT INTO t1(a,b,c) VALUES(502,654,122); 743 INSERT INTO t1(a,b,c) VALUES(503,654,221); 744 INSERT INTO t1(a,b,c) VALUES(601,654,122); 745 INSERT INTO t2(d) VALUES(456); 746 INSERT INTO t2(d) VALUES(122); 747 SELECT a FROM ( 748 SELECT t1.a FROM t2, t1 749 WHERE (987, t1.b) = ( SELECT 987, 654 ) AND t2.d=t1.c 750 ) AS t3 751 WHERE a=1234 OR a<=567; 752} {500 502} 753 754# 2022-07-15 755# https://sqlite.org/forum/forumpost/3607259d3c 756# 757reset_db 758do_execsql_test 33.1 { 759 CREATE TABLE t1(a INT, b INT PRIMARY KEY) WITHOUT ROWID; 760 INSERT INTO t1(a, b) VALUES (0, 1),(15,-7),(3,100); 761 ANALYZE; 762} {} 763do_execsql_test 33.2 { 764 SELECT * FROM t1 WHERE (b,a) BETWEEN (0,5) AND (99,-2); 765} {0 1} 766do_execsql_test 33.3 { 767 SELECT * FROM t1 WHERE (b,a) BETWEEN (-8,5) AND (0,-2); 768} {15 -7} 769do_execsql_test 33.3 { 770 SELECT * FROM t1 WHERE (b,a) BETWEEN (3,5) AND (100,4); 771} {3 100} 772do_execsql_test 33.3 { 773 SELECT * FROM t1 WHERE (b,a) BETWEEN (3,5) AND (100,2); 774} {} 775do_execsql_test 33.3 { 776 SELECT * FROM t1 WHERE (a,b) BETWEEN (-2,99) AND (1,0); 777} {0 1} 778do_execsql_test 33.3 { 779 SELECT * FROM t1 WHERE (a,b) BETWEEN (14,99) AND (16,0); 780} {15 -7} 781do_execsql_test 33.3 { 782 SELECT * FROM t1 WHERE (a,b) BETWEEN (2,99) AND (4,0); 783} {3 100} 784 785finish_test 786