AryaWu/sqlite
0
1# 2024 May 252#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#12 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15source $testdir/lock_common.tcl16set testprefix existsexpr17 18 19do_execsql_test 1.0 {20 CREATE TABLE x1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;21 INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6);22 CREATE INDEX x1b ON x1(b);23 24 CREATE TABLE x2(x, y);25 INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6);26}27 28do_execsql_test 1.1 {29 SELECT 1 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=5)30} {1}31 32do_execsql_test 1.2 {33 SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)34} {1 2 3 4 5 6}35 36# With "a=x", the UNIQUE index means the EXIST can be transformed to a join.37# So no "SUBQUERY". With "b=x", the index is not UNIQUE and so there is a38# "SUBQUERY".39do_execsql_test 1.3.1 {40 EXPLAIN QUERY PLAN41 SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)42} {~/SUBQUERY/}43do_execsql_test 1.3.2 {44 EXPLAIN QUERY PLAN45 SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE b=x)46} {~/SUBQUERY/}47 48do_execsql_test 1.4.1 {49 EXPLAIN QUERY PLAN50 SELECT * FROM x2 WHERE x=1 AND EXISTS (SELECT 1 FROM x1 WHERE a=x)51} {~/SUBQUERY/}52do_execsql_test 1.4.2 {53 EXPLAIN QUERY PLAN54 SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x) AND y=255} {~/SUBQUERY/}56 57do_execsql_test 1.5 {58 SELECT count(*) FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)59} {3}60 61#-------------------------------------------------------------------------62do_execsql_test 2.0 {63 CREATE TABLE t1(a, b);64 WITH s(i) AS (65 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<100066 ) INSERT INTO t1 SELECT i, i FROM s;67 68 CREATE TABLE t2(c, d);69 WITH s(i) AS (70 SELECT 10 UNION ALL SELECT i+10 FROM s WHERE i<100071 ) INSERT INTO t2 SELECT i, i FROM s;72}73 74do_execsql_test 2.1 {75 SELECT count(*) FROM t1;76 SELECT count(*) FROM t2;77} {1000 100}78 79do_execsql_test 2.2 {80 SELECT count(*) FROM t1, t2 WHERE a=c;81} {100}82 83do_execsql_test 2.3 {84 SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a)85} {100}86do_eqp_test 2.4 {87 SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a)88} {SCAN t1}89 90do_execsql_test 2.4.0 {91 CREATE UNIQUE INDEX t2c ON t2(c);92 CREATE UNIQUE INDEX t1a ON t1(a);93}94 95do_eqp_test 2.4.1 {96 SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);97} {SCAN t1*t2 EXISTS}98do_execsql_test 2.4.2 {99 ANALYZE;100}101do_eqp_test 2.4.3 {102 SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);103} {SCAN t1*t2 EXISTS}104do_execsql_test 2.4.4 {105 SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);106} {100}107 108do_execsql_test 2.5.1 {109 EXPLAIN QUERY PLAN110 SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.rowid=a);111} {~/SUBQUERY/}112 113#-------------------------------------------------------------------------114proc do_subquery_test {tn bSub sql res} {115 set r1(0) ~/SUBQUERY/116 set r1(1) /SUBQUERY/117 do_execsql_test $tn.1 "explain query plan $sql" $r1($bSub)118 do_execsql_test $tn.2 $sql $res119}120 121do_execsql_test 3.0 {122 CREATE TABLE y1(a, b, c);123 CREATE TABLE y2(x, y, z);124 CREATE UNIQUE INDEX y2zy ON y2(z, y);125 126 INSERT INTO y1 VALUES(1, 1, 1);127 INSERT INTO y1 VALUES(2, 2, 2);128 INSERT INTO y1 VALUES(3, 3, 3);129 INSERT INTO y1 VALUES(4, 4, 4);130 131 INSERT INTO y2 VALUES(1, 1, 1);132 INSERT INTO y2 VALUES(3, 3, 3);133}134 135do_subquery_test 3.1 0 {136 SELECT * FROM y1 WHERE EXISTS ( 137 SELECT 1 FROM y2 WHERE z=a AND y=b AND x=z138 )139} {140 1 1 1 3 3 3141} 142 143do_subquery_test 3.2 0 {144 SELECT * FROM y1 WHERE EXISTS ( 145 SELECT 1 FROM y2 WHERE z=max(a,b) AND y=min(b,a) AND x=z146 )147} {148 1 1 1 3 3 3149} 150 151do_subquery_test 3.3 0 {152 SELECT * FROM y1 WHERE EXISTS ( 153 SELECT 1 FROM y2 WHERE z=max(a,b) AND y=min(b,a) AND c!=3154 )155} {156 1 1 1157} 158 159do_subquery_test 3.4 0 {160 SELECT * FROM y1 WHERE EXISTS ( 161 SELECT 1 FROM y2 WHERE z=max(a,b) AND b=3162 )163} {164 3 3 3165} 166 167do_subquery_test 3.5 0 {168 SELECT * FROM y1 WHERE EXISTS ( 169 SELECT 1 FROM y2 WHERE z=a-1 AND y=a-1170 )171} {172 2 2 2173 4 4 4174} 175 176do_subquery_test 3.6 0 {177 SELECT * FROM y1 WHERE EXISTS ( 178 SELECT 1 FROM y2 WHERE z=a-1 AND y+1=a179 )180} {181 2 2 2182 4 4 4183} 184 185do_subquery_test 3.7 1 {186 SELECT * FROM y1 WHERE EXISTS ( 187 SELECT count(*) FROM y2 WHERE z=a-1 AND y=a-1188 )189} {190 1 1 1191 2 2 2192 3 3 3193 4 4 4194} 195 196do_subquery_test 3.8 0 {197 SELECT * FROM y1 WHERE EXISTS ( SELECT a+1 FROM y2 )198} {199 1 1 1200 2 2 2201 3 3 3202 4 4 4203} 204 205do_subquery_test 3.9 1 {206 SELECT * FROM y1 WHERE EXISTS ( 207 SELECT 1 FROM y2 one, y2 two WHERE one.z=a-1 AND one.y=a-1208 )209} {210 2 2 2211 4 4 4212} 213 214#-------------------------------------------------------------------------215reset_db216do_execsql_test 4.0 {217 CREATE TABLE tx1(a TEXT COLLATE nocase, b TEXT);218 CREATE UNIQUE INDEX tx1ab ON tx1(a, b);219 220 INSERT INTO tx1 VALUES('a', 'a');221 INSERT INTO tx1 VALUES('B', 'b');222 INSERT INTO tx1 VALUES('c', 'c');223 INSERT INTO tx1 VALUES('D', 'd');224 INSERT INTO tx1 VALUES('e', 'e');225 226 CREATE TABLE tx2(x, y);227 INSERT INTO tx2 VALUES('A', 'a');228 INSERT INTO tx2 VALUES('b', 'b');229 INSERT INTO tx2 VALUES('C', 'c');230 INSERT INTO tx2 VALUES('D', 'd');231}232 233do_subquery_test 4.1 0 {234 SELECT * FROM tx2 WHERE EXISTS (235 SELECT 1 FROM tx1 WHERE a=x AND b=y236 )237} {238 A a239 b b240 C c241 D d242}243 244do_subquery_test 4.1.1 0 {245 SELECT * FROM tx2 WHERE EXISTS (246 SELECT 1 FROM tx1 WHERE (a COLLATE nocase)=x AND b=y247 )248} {249 A a b b C c D d250}251do_subquery_test 4.1.2 0 {252 SELECT * FROM tx2 WHERE EXISTS (253 SELECT 1 FROM tx1 WHERE a=x AND (b COLLATE binary)=y254 )255} {256 A a b b C c D d257}258do_subquery_test 4.1.1 0 {259 SELECT * FROM tx2 WHERE EXISTS (260 SELECT 1 FROM tx1 WHERE x=(a COLLATE nocase) AND b=y261 )262} {263 A a b b C c D d264}265do_subquery_test 4.1.2 0 {266 SELECT * FROM tx2 WHERE EXISTS (267 SELECT 1 FROM tx1 WHERE a=x AND y=(b COLLATE binary)268 )269} {270 A a b b C c D d271}272 273do_subquery_test 4.2 0 {274 SELECT * FROM tx2 WHERE EXISTS (275 SELECT 1 FROM tx1 WHERE a=x AND b=y COLLATE nocase276 )277} {278 A a279 b b280 C c281 D d282}283 284do_execsql_test 4.3 {285 DROP INDEX tx1ab;286 CREATE UNIQUE INDEX tx1ab ON tx1(a COLLATE binary, b);287}288 289do_subquery_test 4.4 0 {290 SELECT * FROM tx2 WHERE EXISTS (291 SELECT 1 FROM tx1 WHERE a=x AND b=y292 )293} {294 A a295 b b296 C c297 D d298}299 300do_subquery_test 4.4 0 {301 SELECT * FROM tx2 WHERE EXISTS (302 SELECT 1 FROM tx1 WHERE a=x COLLATE binary AND b=y303 )304} {305 D d306}307 308do_subquery_test 4.4 1 {309 SELECT EXISTS ( SELECT x FROM tx1 ) FROM tx2310} {311 1 1 1 1312}313 314do_subquery_test 4.4 1 {315 SELECT (SELECT EXISTS ( SELECT x FROM tx1 ) WHERE 1) FROM tx2316} {317 1 1 1 1318}319 320#-------------------------------------------------------------------------321proc cols {s f} {322 set lCols [list]323 for {set i $s} {$i<=$f} {incr i} {324 lappend lCols [format "c%02d" $i]325 }326 join $lCols ", "327}328proc vals {n val} {329 set lVal [list]330 for {set i 0} {$i<$n} {incr i} {331 lappend lVal $val332 }333 join $lVal ", "334}335proc exprs {s f} {336 set lExpr [list]337 for {set i $s} {$i<=$f} {incr i} {338 lappend lExpr [format "c%02d = o" $i]339 }340 join $lExpr " AND "341}342 343 344do_execsql_test 5.0 "345 CREATE TABLE a1( [cols 0 99] );346"347do_execsql_test 5.1 "348 -- 63 column index349 CREATE UNIQUE INDEX a1idx1 ON a1( [cols 0 62] );350"351do_execsql_test 5.2 "352 -- 64 column index353 CREATE UNIQUE INDEX a1idx2 ON a1( [cols 10 73] );354"355do_execsql_test 5.2 "356 -- 65 column index357 CREATE UNIQUE INDEX a1idx3 ON a1( [cols 20 84] );358"359 360do_test 5.3 {361 foreach v {1 2 3 4 5 6} {362 execsql "INSERT INTO a1 VALUES( [vals 100 $v] )"363 }364} {}365 366do_execsql_test 5.4 {367 CREATE TABLE a2(o);368 INSERT INTO a2 VALUES(2), (5); 369}370 371do_subquery_test 5.5 0 "372 SELECT o FROM a2 WHERE EXISTS (373 SELECT 1 FROM a1 WHERE [exprs 0 62]374 )375" {376 2 5377}378 379do_subquery_test 5.6 0 "380 SELECT o FROM a2 WHERE EXISTS (381 SELECT 1 FROM a1 WHERE [exprs 10 73]382 )383" {384 2 5385}386 387do_subquery_test 5.7 0 "388 SELECT o FROM a2 WHERE EXISTS (389 SELECT 1 FROM a1 WHERE [exprs 20 84]390 )391" {392 2 5393}394 395#-------------------------------------------------------------------------396reset_db397do_execsql_test 6.0 {398 CREATE TABLE t1(a, b UNIQUE, c UNIQUE);399 CREATE TABLE t2(a INfEGER PRIMARY KEY, b);400 CREATE UNIQUE INDEX t2b ON t2(b);401}402 403do_catchsql_test 6.1 {404 SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c COLLATE f = a)405} {1 {no such collation sequence: f}}406 407#-------------------------------------------------------------------------408reset_db409do_execsql_test 7.0 {410 CREATE TABLE t1(x);411 CREATE TABLE t2(y UNIQUE);412 413 INSERT INTO t1 VALUES(1), (2);414 INSERT INTO t2 VALUES(1), (3);415 416 SELECT * FROM t1 one LEFT JOIN t1 two ON (one.x=two.x AND EXISTS (417 SELECT 1 FROM t2 WHERE y=one.x418 ));419} {420 1 1421 2 {}422}423 424# https://sqlite.org/forum/forumpost/2025-07-23T10:59:14z425reset_db426do_execsql_test 8.0 {427 CREATE TABLE t0 (c0 INT); INSERT INTO t0(c0) VALUES (1);428 CREATE TABLE t1(c0 INT); INSERT INTO t1(c0) VALUES (2);429 SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 LIMIT 0);430} {}431 432#-------------------------------------------------------------------------433reset_db434 435do_execsql_test 9.0 {436 CREATE TABLE t1(xx);437 INSERT INTO t1 VALUES('big string value');438} {}439 440do_execsql_test 9.1 {441 PRAGMA automatic_index = off;442 CREATE TABLE t2(ii);443 INSERT INTO t2 VALUES(100);444 INSERT INTO t2 VALUES(200);445}446 447do_execsql_test 9.2 {448 CREATE TABLE t3(yy);449 INSERT INTO t3 VALUES(200);450}451 452do_execsql_test 9.3 {453 SELECT 1 FROM t2 WHERE EXISTS ( SELECT 1 FROM t3 WHERE yy==t2.ii )454} {1}455 456do_execsql_test 9.4 {457 SELECT EXISTS (458 SELECT 1 FROM t2 WHERE EXISTS ( SELECT 1 FROM t3 WHERE yy==t2.ii )459 )460} {1}461 462do_execsql_test 9.5 {463 SELECT 1234 WHERE EXISTS (464 SELECT 1 FROM t2 WHERE EXISTS ( SELECT 1 FROM t3 WHERE yy==t2.ii )465 )466} {1234}467 468set Q {469 SELECT * FROM t1 WHERE 470 EXISTS (471 SELECT 1 FROM t2 WHERE EXISTS ( SELECT 1 FROM t3 WHERE yy==t2.ii ) 472 )473}474 475do_execsql_test 9.5 $Q {{big string value}}476catch { optimization_control db exists-to-join 0 }477db cache flush478do_execsql_test 9.6 $Q {{big string value}}479 480finish_test481