AryaWu/sqlite
0
1# 2016 June 172#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is testing the SELECT statement.13#14 15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18set ::testprefix rowvalue19 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 027 2 "1, 0, 3" "1, 2, 3" 0 1 0 128 3 "1, 2, NULL" "1, 2, 3" {} {} 0 129 4 "1, 2, NULL" "1, 2, NULL" {} {} 1 030 5 "NULL, NULL, NULL" "NULL, NULL, NULL" {} {} 1 031 32 6 "1, NULL, 1" "1, 1, 1" {} {} 0 133 7 "1, NULL, 1" "1, 1, 2" 0 1 0 134} {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 047 2 "(1, 2, 3)" "(1, 2, 3)" 0 0 1 148 3 "(1, 3, 3)" "(1, 2, 3)" 0 1 0 149 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)" 193 do_execsql_test 3.$tn.2 "SELECT $r == (SELECT * FROM t1 $order)" 194 95 do_execsql_test 3.$tn.3 "96 SELECT (SELECT * FROM t1 $order) == (SELECT * FROM t1 $order)97 " 198 do_execsql_test 3.$tn.4 "99 SELECT (SELECT 0, 0) == (SELECT * FROM t1 $order)100 " 0101}102 103foreach {tn expr res} {104 1 {(2, 2) BETWEEN (2, 2) AND (3, 3)} 1105 2 {(2, 2) BETWEEN (2, NULL) AND (3, 3)} {}106 3 {(2, 2) BETWEEN (3, NULL) AND (3, 3)} 0107} {108 do_execsql_test 4.$tn "SELECT $expr" [list $res]109}110 111foreach {tn expr res} {112 1 {(2, 4) IN (SELECT * FROM t1)} 1113 2 {(3, 4) IN (SELECT * FROM t1)} 0114 115 3 {(NULL, 4) IN (SELECT * FROM t1)} {}116 4 {(NULL, 0) IN (SELECT * FROM t1)} 0117 118 5 {(NULL, 4) NOT IN (SELECT * FROM t1)} {}119 6 {(NULL, 0) NOT IN (SELECT * FROM t1)} 1120} {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 $eqp194 do_execsql_test 7.$tn.2 $sql $res195}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" $res220} 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 t3228 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://sqlite.org/src/tktview/fef4bb4bd9185ec8f259# Incorrect result from a LEFT JOIN with a row-value constraint260#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 4278}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)" 1298do_execsql_test 14.2 "SELECT CASE (2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1299do_execsql_test 14.3 "SELECT CASE (SELECT 2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1300do_execsql_test 14.4 "SELECT 1 WHERE (SELECT 2,2) BETWEEN (1,1) AND (3,3)" 1301do_execsql_test 14.5 "SELECT 1 FROM t12 WHERE (x,1) BETWEEN (1,1) AND (3,3)" 1302do_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 or308# 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 TRIGGERs351#352# Ticket https://sqlite.org/src/info/8c9458e703666e1a353#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 BEGIN360 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 BEGIN374 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 z387}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 BEGIN394 UPDATE t16c SET (e, d) = (395 SELECT b, c FROM t16c WHERE a = new.a-1396 ), (c, b) = (397 SELECT d, e FROM t16c WHERE a = new.a-1398 ) 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 D405 2 z y x w406 3 i ii iii iv407}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.a440 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.a444 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.a448 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://sqlite.org/src/tktview/f484b65f3d6230593c3453# 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://sqlite.org/src/info/1a84668dcfdebaf1562# Assertion fault when doing row-value operations on a primary key563# 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 b47e3627ecaadbde586#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 6ef984af8972c2eb602do_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 135c9da7513e5a97611do_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 02aa2bd02f97d0f2632# The TK_VECTOR operator messes up sqlite3ExprImpliesNonNull() which633# causes incorrect LEFT JOIN strength reduction. TK_VECTOR should be634# treated the same as TK_OR.635#636db close637sqlite3 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 892575cdba4e1e36658#659reset_db660do_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-03666# https://bugs.chromium.org/p/chromium/issues/detail?id=1173511667# Faulty assert() statement.668#669reset_db670do_catchsql_test 28.10 {671 CREATE TABLE t0(c0 PRIMARY KEY, c1);672 CREATE TRIGGER trigger0 BEFORE DELETE ON t0 BEGIN673 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-19679# 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_db686do_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) ) ), 2697 )698 FROM t1;699} {}700 701do_execsql_test 30.2 {702 SELECT * FROM t2703} {1000 2}704 705reset_db706do_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/ab95010d410a0a55714reset_db715do_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 80a9fade844b4fb43564efc972bcb2c68270f5d1736reset_db737do_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.c750 ) AS t3751 WHERE a=1234 OR a<=567;752} {500 502}753 754# 2022-07-15755# https://sqlite.org/forum/forumpost/3607259d3c756#757reset_db758do_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 785# 2025-04-15 https://sqlite.org/forum/forumpost/b9647a113b465950786# Incorrect result when the schema includes a table with a UNIQUE787# constraint and one of the columns in the UNIQUE constraint is the788# INTEGER PRIMARY KEY, and the columns that UNIQUE constraint are789# used in a rowvalue-IN operator constraint.790#791# 2025-07-07 Discovered that the original fix was incomplete and792# new tests added. See tag-20250707-01 in the code.793#794reset_db795do_execsql_test 34.1 {796 CREATE TABLE items (797 Id INTEGER /* rowid alias */,798 Item INTEGER /* any type */,799 Test TEXT /* TEXT or BLOB */,800 Filler, /* any type */801 PRIMARY KEY(Id),802 UNIQUE(Item, Id)803 );804 INSERT INTO items (Id, Item)805 VALUES (1, 2), (2, 2), (3, 3), (4, 5);806 UPDATE items SET test='ok'807 WHERE (Id, Item) IN (SELECT Id, Item FROM items);808 SELECT Id, Item, test FROM items ORDER BY id;809} {1 2 ok 2 2 ok 3 3 ok 4 5 ok}810db null NULL811do_execsql_test 34.2 {812 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);813 CREATE INDEX idx ON t1(b,a);814 INSERT INTO t1(a,b) VALUES (1, 22);815 SELECT * FROM t1 INDEXED BY idx WHERE (b,a) IN (SELECT b,a FROM t1);816} {1 22 NULL NULL}817do_execsql_test 34.3 {818 DROP TABLE t1;819 CREATE TABLE t1(a, b, c, d);820 CREATE INDEX idx ON t1(b,a,a);821 INSERT INTO t1(a,b) VALUES (1, 22);822 SELECT * FROM t1 INDEXED BY idx WHERE (b,a) IN (SELECT b,a FROM t1);823} {1 22 NULL NULL}824do_execsql_test 34.4 {825 DROP TABLE t1;826 CREATE TABLE t1(id INTEGER PRIMARY KEY, a INT);827 CREATE INDEX t1a ON t1(a,id); -- index includes PRIMARY KEY828 CREATE TABLE t2(id INTEGER PRIMARY KEY);829 WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<100)830 INSERT INTO t1(id,a) SELECT n, 777 FROM c;831 INSERT INTO t2 SELECT id FROM t1;832 SELECT *833 FROM t1 JOIN t2 USING(id)834 WHERE t1.a=777 AND t2.id>999835 ORDER BY t1.id;836} {}837do_execsql_test 34.5 {838 EXPLAIN QUERY PLAN839 SELECT *840 FROM t1 JOIN t2 USING(id)841 WHERE t1.a=777 AND t2.id>999842 ORDER BY t1.id;843} {/SEARCH t1 USING COVERING INDEX t1a .a=. AND id>../}844 845# 2025-08-04 forum post eab63506cf846#847do_execsql_test 35.0 {848 DROP TABLE IF EXISTS t1;849 CREATE TABLE t1(c1 TEXT, c2 INTEGER, PRIMARY KEY(c1, c2));850 INSERT INTO t1(c1, c2) VALUES ('a', 1);851 SELECT ('a', 1) IN (SELECT c1, c2 from t1);852} 1853do_execsql_test 35.1 {854 SELECT (1, 'a') IN (SELECT c2, c1 from t1);855} 1856 857 858finish_test859