diff options
| -rw-r--r-- | manifest | 23 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | src/build.c | 9 | ||||
| -rw-r--r-- | src/resolve.c | 1 | ||||
| -rw-r--r-- | src/select.c | 164 | ||||
| -rw-r--r-- | src/sqliteInt.h | 3 | ||||
| -rw-r--r-- | test/existsexpr.test | 428 | ||||
| -rw-r--r-- | test/existsexpr2.test | 96 | ||||
| -rw-r--r-- | test/existsfault.test | 49 |
9 files changed, 761 insertions, 14 deletions
@@ -1,5 +1,5 @@ -C Fix\san\sincorrect\stcl\scomment\sthat\sappeared\sin\smany\sfts5\stest\sfiles. -D 2024-06-24T18:06:15.031 +C Merge\sthe\slatest\strunk\senhancements\sinto\sthe\sexists-to-join\sbranch. +D 2024-06-27T14:54:15.037 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -697,7 +697,7 @@ F src/btmutex.c 79a43670447eacc651519a429f6ece9fd638563cf95b469d6891185ddae2b522 F src/btree.c 8b42fc7d9efdb2df05c30e8f91ff6cfbd979724ae24bf90269028468b7a13333 F src/btree.h 55066f513eb095db935169dab1dc2f7c7a747ef223c533f5d4ad4dfed346cbd0 F src/btreeInt.h 98aadb6dcb77b012cab2574d6a728fad56b337fc946839b9898c4b4c969e30b6 -F src/build.c 237ccc0290d131d646be722f418e92ee0a38043aee25e7dfdc75f8ce5b3abe4e +F src/build.c 9e6a971156db6285f726fb03ddd9d47cb0a3648198b611f462021ac96fa24135 F src/callback.c db3a45e376deff6a16c0058163fe0ae2b73a2945f3f408ca32cf74960b28d490 F src/complete.c a3634ab1e687055cd002e11b8f43eb75c17da23e F src/ctime.c 64e4b1227b4ed123146f0aa2989131d1fbd9b927b11e80c9d58c6a68f9cd5ce3 @@ -753,14 +753,14 @@ F src/pragma.h e690a356c18e98414d2e870ea791c1be1545a714ba623719deb63f7f226d8bb7 F src/prepare.c d99931f45416652895e502328ca49fe782cfc4e1ebdcda13b3736d991ebf42ce F src/printf.c 8b250972305e14b365561be5117ed0fd364e4fd58968776df1ce64c6280b90f9 F src/random.c 606b00941a1d7dd09c381d3279a058d771f406c5213c9932bbd93d5587be4b9c -F src/resolve.c 7e8d23ce7cdbfedf351a47e759f2722e8182ca10fd7580be43f4ce1f1a228145 +F src/resolve.c 9c7786f032dea81487e7d94cb17849936f0e9b8891bfc91a6ac24ab193762804 F src/rowset.c 8432130e6c344b3401a8874c3cb49fefe6873fec593294de077afea2dce5ec97 -F src/select.c d26ac0a13b4154cbd71a9a57ca024350cd73f0f6fcf0ca0cbb4537e2d5b3e257 +F src/select.c d92f158185cf5aedc058bbe1eefba070b5dc57a5d71cb66625c3d2a906037829 F src/shell.c.in 2ccbed6a9fd451399b0f378aafa323ad2286fa9de54ae0cd28f32907cd94d18d F src/sqlite.h.in 6c884a87bbf8828562b49272025a1e66e3801a196a58b0bdec87edcd2c9c8fc1 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8 F src/sqlite3ext.h 3f046c04ea3595d6bfda99b781926b17e672fd6d27da2ba6d8d8fc39981dcb54 -F src/sqliteInt.h 76f4bf5adce98a989bf9b792b962bc8f739e79636a63d841e567017370acd607 +F src/sqliteInt.h 36aaff552ecdf273ab6d14bd87d01175d8d5c88267b6d8b04e8f513c5d27efc9 F src/sqliteLimit.h 6878ab64bdeb8c24a1d762d45635e34b96da21132179023338c93f820eee6728 F src/status.c cb11f8589a6912af2da3bb1ec509a94dd8ef27df4d4c1a97e0bcf2309ece972b F src/table.c 0f141b58a16de7e2fbe81c308379e7279f4c6b50eb08efeec5892794a0ba30d1 @@ -1114,6 +1114,9 @@ F test/exclusive.test 7ff63be7503990921838d5c9f77f6e33e68e48ed1a9d48cd28745bf650 F test/exclusive2.test 984090e8e9d1b331d2e8111daf6e5d61dda0bef7 F test/exec.test e949714dc127eaa5ecc7d723efec1ec27118fdd7 F test/exists.test 79a75323c78f02bbe9c251ea502a092f9ef63dac +F test/existsexpr.test bf1201621070e79c1060c1a8cf7d65d81fc6b336d94a371c63ecb08d357af2fd +F test/existsexpr2.test dc23e76389eff3d29f6488ff733012a3560cd67ec8cfaecbecd52cced5d5af11 +F test/existsfault.test ff41c11f3052c1bbd4f8dd557802310026253d67d7c4e3a180c16d2f0862973e F test/expr.test 5c06696478212e5a04e04b043f993373f6f8e5ce5a80f5548a84703b123b6caa F test/expr2.test c27327ae9c017a7ff6280123f67aff496f912da74d78c888926d68b46ec75fd8 F test/exprfault.test da33606d799718e2f8e34efd0e5858884a1ad87f608774c552a7f5517cc27181 @@ -2195,8 +2198,8 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 56d265f956fe6433b625c6d732e55f387af3f643e705316f5a6f709d63731669 -R 5ad9950c3d8fa87fe57068f06313a6c9 -U dan -Z 3992b6a75558611778f9237da672a1e0 +P 8e3a1d2850337a902ab36b1d6a0dad4ae35030b71d1e15547f6e7487c1f86d18 d07085e2035b52a7edd27980523225e59c5bf851fb4a6de975f03e653b937c9c +R 4bccf9946bb839298bd611ecb98c6563 +U drh +Z fabaa312611792ffca3e18aa282f1001 # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index 73edec88b..499a42453 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -d07085e2035b52a7edd27980523225e59c5bf851fb4a6de975f03e653b937c9c +fc643f8a12e9b7448136b281f798e18dfebe0a3df5115d930b965c8a33933e2d diff --git a/src/build.c b/src/build.c index 9747810e8..e6de79e77 100644 --- a/src/build.c +++ b/src/build.c @@ -5049,14 +5049,17 @@ void sqlite3SrcListIndexedBy(Parse *pParse, SrcList *p, Token *pIndexedBy){ ** are deleted by this function. */ SrcList *sqlite3SrcListAppendList(Parse *pParse, SrcList *p1, SrcList *p2){ - assert( p1 && p1->nSrc==1 ); + assert( p1 ); if( p2 ){ - SrcList *pNew = sqlite3SrcListEnlarge(pParse, p1, p2->nSrc, 1); + int nOld = p1->nSrc; + SrcList *pNew = sqlite3SrcListEnlarge(pParse, p1, p2->nSrc, nOld); if( pNew==0 ){ sqlite3SrcListDelete(pParse->db, p2); }else{ p1 = pNew; - memcpy(&p1->a[1], p2->a, p2->nSrc*sizeof(SrcItem)); + memcpy(&p1->a[nOld], p2->a, p2->nSrc*sizeof(SrcItem)); + assert( nOld==1 || (p2->nSrc==1 && (p2->a[0].fg.jointype&JT_LTORJ)==0) ); + assert( p1->nSrc>=2 ); sqlite3DbFree(pParse->db, p2); p1->a[0].fg.jointype |= (JT_LTORJ & p1->a[1].fg.jointype); } diff --git a/src/resolve.c b/src/resolve.c index d5c1515a7..9cb366262 100644 --- a/src/resolve.c +++ b/src/resolve.c @@ -1367,6 +1367,7 @@ static int resolveExprStep(Walker *pWalker, Expr *pExpr){ if( nRef!=pNC->nRef ){ ExprSetProperty(pExpr, EP_VarSelect); pExpr->x.pSelect->selFlags |= SF_Correlated; + if( pExpr->op==TK_EXISTS ) pParse->bHasExists = 1; } pNC->ncFlags |= NC_Subquery; } diff --git a/src/select.c b/src/select.c index 3bc5858f6..f2d44bf36 100644 --- a/src/select.c +++ b/src/select.c @@ -7284,6 +7284,163 @@ static int fromClauseTermCanBeCoroutine( } /* +** sqlite3WalkExpr() callback used by exprReferencesTable(). +*/ +static int exprReferencesTableExprCb(Walker *pWalker, Expr *pExpr){ + if( pExpr->op==TK_COLUMN && pExpr->iTable==pWalker->u.iCur ){ + pWalker->eCode = 1; + } + return WRC_Continue; +} + +/* +** Return true if the expression passed as the first argument refers +** to cursor number iCur. Otherwise return false. +*/ +static int exprReferencesTable(Expr *pExpr, int iCur){ + Walker w; + memset(&w, 0, sizeof(w)); + w.u.iCur = iCur; + w.xExprCallback = exprReferencesTableExprCb; + w.xSelectCallback = sqlite3SelectWalkNoop; + sqlite3WalkExpr(&w, pExpr); + return w.eCode; +} + +/* +** Index pIdx is a UNIQUE index on the table accessed by cursor number +** iCsr. This function returns a mask of the index columns that are +** constrained to match a single, non-NULL value by the WHERE clause +** passed as the 4th argument. For example, if the index is: +** +** CREATE UNIQUE INDEX idx ON tbl(a, b, c); +** +** and pWhere: +** +** WHERE a=? AND c=? +** +** then this function returns 5. +*/ +static u64 findConstIdxTerms( + Parse *pParse, + int iCsr, + Index *pIdx, + Expr *pWhere +){ + u64 m = 0; + if( pWhere->op==TK_AND ){ + m = findConstIdxTerms(pParse, iCsr, pIdx, pWhere->pLeft); + m |= findConstIdxTerms(pParse, iCsr, pIdx, pWhere->pRight); + }else if( pWhere->op==TK_EQ ){ + Expr *pLeft = sqlite3ExprSkipCollateAndLikely(pWhere->pLeft); + Expr *pRight = sqlite3ExprSkipCollateAndLikely(pWhere->pRight); + if( pRight->op==TK_COLUMN && pRight->iTable==iCsr ){ + SWAP(Expr*, pLeft, pRight); + } + if( pLeft->op==TK_COLUMN + && pLeft->iTable==iCsr + && exprReferencesTable(pRight, iCsr)==0 + ){ + if( pIdx ){ + int ii; + for(ii=0; ii<pIdx->nKeyCol; ii++){ + assert( pIdx->azColl[ii] ); + if( pLeft->iColumn==pIdx->aiColumn[ii] ){ + CollSeq *pColl = sqlite3ExprCompareCollSeq(pParse, pWhere); + if( pColl && sqlite3StrICmp(pColl->zName, pIdx->azColl[ii])==0 ){ + m |= ((u64)1 << ii); + break; + } + } + } + }else{ + if( pLeft->iColumn<0 ) m = 1; + } + } + } + return m; +} + +/* +** Argument pWhere is the WHERE clause belonging to SELECT statement p. This +** function attempts to transform expressions of the form: +** +** EXISTS (SELECT ...) +** +** into joins. For example, given +** +** CREATE TABLE sailors(sid INTEGER PRIMARY KEY, name TEXT); +** CREATE TABLE reserves(sid INT, day DATE, PRIMARY KEY(sid, day)); +** +** SELECT name FROM sailors AS S WHERE EXISTS ( +** SELECT * FROM reserves AS R WHERE S.sid = R.sid AND R.day = '2022-10-25' +** ); +** +** the SELECT statement may be transformed as follows: +** +** SELECT name FROM sailors AS S, reserves AS R +** WHERE S.sid = R.sid AND R.day = '2022-10-25'; +*/ +static void existsToJoin(Parse *pParse, Select *p, Expr *pWhere){ + if( pWhere + && !ExprHasProperty(pWhere, EP_OuterON|EP_InnerON) + && p->pSrc->nSrc>0 + && p->pSrc->nSrc<BMS + && pParse->db->mallocFailed==0 + ){ + if( pWhere->op==TK_AND ){ + Expr *pRight = pWhere->pRight; + existsToJoin(pParse, p, pWhere->pLeft); + existsToJoin(pParse, p, pRight); + } + else if( pWhere->op==TK_EXISTS ){ + Select *pSub = pWhere->x.pSelect; + if( pSub->pSrc->nSrc==1 + && (pSub->selFlags & (SF_Aggregate|SF_Correlated))==SF_Correlated + && pSub->pWhere + ){ + int bTransform = 0; /* True if EXISTS can be made into join */ + Table *pTab = pSub->pSrc->a[0].pTab; + int iCsr = pSub->pSrc->a[0].iCursor; + Index *pIdx; + if( HasRowid(pTab) && findConstIdxTerms(pParse, iCsr, 0,pSub->pWhere) ){ + bTransform = 1; + } + for(pIdx=pTab->pIndex; pIdx && bTransform==0; pIdx=pIdx->pNext){ + if( pIdx->onError && pIdx->nKeyCol<=63 ){ + u64 c = findConstIdxTerms(pParse, iCsr, pIdx, pSub->pWhere); + if( c==((u64)1 << pIdx->nKeyCol)-1 ){ + bTransform = 1; + } + } + } + if( bTransform ){ + memset(pWhere, 0, sizeof(*pWhere)); + pWhere->op = TK_INTEGER; + pWhere->u.iValue = 1; + ExprSetProperty(pWhere, EP_IntValue); + + assert( p->pWhere!=0 ); + p->pSrc = sqlite3SrcListAppendList(pParse, p->pSrc, pSub->pSrc); + p->pWhere = sqlite3PExpr(pParse, TK_AND, p->pWhere, pSub->pWhere); + + pSub->pWhere = 0; + pSub->pSrc = 0; + sqlite3ParserAddCleanup(pParse, sqlite3SelectDeleteGeneric, pSub); +#if TREETRACE_ENABLED + if( sqlite3TreeTrace & 0x100000 ){ + TREETRACE(0x100000,pParse,p, + ("After EXISTS-to-JOIN optimization:\n")); + sqlite3TreeViewSelect(0, p, 0); + } +#endif + } + } + } + } +} + +/* ** Generate code for the SELECT statement given in the p argument. ** ** The results are returned according to the SelectDest structure. @@ -7612,6 +7769,13 @@ int sqlite3Select( } #endif + /* If there may be an "EXISTS (SELECT ...)" in the WHERE clause, attempt + ** to change it into a join. */ + if( pParse->bHasExists && OptimizationEnabled(db,SQLITE_ExistsToJoin) ){ + existsToJoin(pParse, p, p->pWhere); + pTabList = p->pSrc; + } + /* Do the WHERE-clause constant propagation optimization if this is ** a join. No need to speed time on this operation for non-join queries ** as the equivalent optimization will be handled by query planner in diff --git a/src/sqliteInt.h b/src/sqliteInt.h index 736889868..44914065c 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -1126,6 +1126,7 @@ extern u32 sqlite3TreeTrace; ** 0x00020000 Transform DISTINCT into GROUP BY ** 0x00040000 SELECT tree dump after all code has been generated ** 0x00080000 NOT NULL strength reduction +** 0x00100000 EXISTS-to-JOIN optimization */ /* @@ -1924,6 +1925,7 @@ struct sqlite3 { #define SQLITE_Coroutines 0x02000000 /* Co-routines for subqueries */ #define SQLITE_NullUnusedCols 0x04000000 /* NULL unused columns in subqueries */ #define SQLITE_OnePass 0x08000000 /* Single-pass DELETE and UPDATE */ +#define SQLITE_ExistsToJoin 0x10000000 /* The EXISTS-to-JOIN optimization */ #define SQLITE_AllOpts 0xffffffff /* All optimizations */ /* @@ -3827,6 +3829,7 @@ struct Parse { u8 prepFlags; /* SQLITE_PREPARE_* flags */ u8 withinRJSubrtn; /* Nesting level for RIGHT JOIN body subroutines */ u8 bHasWith; /* True if statement contains WITH */ + u8 bHasExists; /* Has a correlated "EXISTS (SELECT ....)" expression */ #if defined(SQLITE_DEBUG) || defined(SQLITE_COVERAGE_TEST) u8 earlyCleanup; /* OOM inside sqlite3ParserAddCleanup() */ #endif diff --git a/test/existsexpr.test b/test/existsexpr.test new file mode 100644 index 000000000..81aa5799f --- /dev/null +++ b/test/existsexpr.test @@ -0,0 +1,428 @@ +# 2024 May 25 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +source $testdir/lock_common.tcl +set testprefix existsexpr + + +do_execsql_test 1.0 { + CREATE TABLE x1(a, b, PRIMARY KEY(a)) WITHOUT ROWID; + INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6); + CREATE INDEX x1b ON x1(b); + + CREATE TABLE x2(x, y); + INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6); +} + +do_execsql_test 1.1 { + SELECT 1 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=5) +} {1} + +do_execsql_test 1.2 { + SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x) +} {1 2 3 4 5 6} + +# With "a=x", the UNIQUE index means the EXIST can be transformed to a join. +# So no "SUBQUERY". With "b=x", the index is not UNIQUE and so there is a +# "SUBQUERY". +do_execsql_test 1.3.1 { + EXPLAIN QUERY PLAN + SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x) +} {~/SUBQUERY/} +do_execsql_test 1.3.2 { + EXPLAIN QUERY PLAN + SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE b=x) +} {/SUBQUERY/} + +do_execsql_test 1.4.1 { + EXPLAIN QUERY PLAN + SELECT * FROM x2 WHERE x=1 AND EXISTS (SELECT 1 FROM x1 WHERE a=x) +} {~/SUBQUERY/} +do_execsql_test 1.4.2 { + EXPLAIN QUERY PLAN + SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x) AND y=2 +} {~/SUBQUERY/} + +do_execsql_test 1.5 { + SELECT count(*) FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x) +} {3} + +#------------------------------------------------------------------------- +do_execsql_test 2.0 { + CREATE TABLE t1(a, b); + WITH s(i) AS ( + SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000 + ) INSERT INTO t1 SELECT i, i FROM s; + + CREATE TABLE t2(c, d); + WITH s(i) AS ( + SELECT 10 UNION ALL SELECT i+10 FROM s WHERE i<1000 + ) INSERT INTO t2 SELECT i, i FROM s; +} + +do_execsql_test 2.1 { + SELECT count(*) FROM t1; + SELECT count(*) FROM t2; +} {1000 100} + +do_execsql_test 2.2 { + SELECT count(*) FROM t1, t2 WHERE a=c; +} {100} + +do_execsql_test 2.3 { + SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a) +} {100} +do_eqp_test 2.4 { + SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a) +} {SCAN t1} + +do_execsql_test 2.5 { + CREATE UNIQUE INDEX t2c ON t2(c); + CREATE UNIQUE INDEX t1a ON t1(a); +} + +do_eqp_test 2.4.1 { + SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a); +} {SCAN t1*SEARCH t2} +do_execsql_test 2.4.2 { + ANALYZE; +} +do_eqp_test 2.4.3 { + SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a); +} {SCAN t2*SEARCH t1} +do_execsql_test 2.4.4 { + SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a); +} {100} + +do_execsql_test 2.5.1 { + EXPLAIN QUERY PLAN + SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.rowid=a); +} {~/SUBQUERY/} + +#------------------------------------------------------------------------- +proc do_subquery_test {tn bSub sql res} { + set r1(0) ~/SUBQUERY/ + set r1(1) /SUBQUERY/ + do_execsql_test $tn.1 "explain query plan $sql" $r1($bSub) + do_execsql_test $tn.2 $sql $res +} + +do_execsql_test 3.0 { + CREATE TABLE y1(a, b, c); + CREATE TABLE y2(x, y, z); + CREATE UNIQUE INDEX y2zy ON y2(z, y); + + INSERT INTO y1 VALUES(1, 1, 1); + INSERT INTO y1 VALUES(2, 2, 2); + INSERT INTO y1 VALUES(3, 3, 3); + INSERT INTO y1 VALUES(4, 4, 4); + + INSERT INTO y2 VALUES(1, 1, 1); + INSERT INTO y2 VALUES(3, 3, 3); +} + +do_subquery_test 3.1 0 { + SELECT * FROM y1 WHERE EXISTS ( + SELECT 1 FROM y2 WHERE z=a AND y=b AND x=z + ) +} { + 1 1 1 3 3 3 +} + +do_subquery_test 3.2 0 { + SELECT * FROM y1 WHERE EXISTS ( + SELECT 1 FROM y2 WHERE z=max(a,b) AND y=min(b,a) AND x=z + ) +} { + 1 1 1 3 3 3 +} + +do_subquery_test 3.3 0 { + SELECT * FROM y1 WHERE EXISTS ( + SELECT 1 FROM y2 WHERE z=max(a,b) AND y=min(b,a) AND c!=3 + ) +} { + 1 1 1 +} + +do_subquery_test 3.4 1 { + SELECT * FROM y1 WHERE EXISTS ( + SELECT 1 FROM y2 WHERE z=max(a,b) AND b=3 + ) +} { + 3 3 3 +} + +do_subquery_test 3.5 0 { + SELECT * FROM y1 WHERE EXISTS ( + SELECT 1 FROM y2 WHERE z=a-1 AND y=a-1 + ) +} { + 2 2 2 + 4 4 4 +} + +do_subquery_test 3.6 1 { + SELECT * FROM y1 WHERE EXISTS ( + SELECT 1 FROM y2 WHERE z=a-1 AND y+1=a + ) +} { + 2 2 2 + 4 4 4 +} + +do_subquery_test 3.7 1 { + SELECT * FROM y1 WHERE EXISTS ( + SELECT count(*) FROM y2 WHERE z=a-1 AND y=a-1 + ) +} { + 1 1 1 + 2 2 2 + 3 3 3 + 4 4 4 +} + +do_subquery_test 3.8 1 { + SELECT * FROM y1 WHERE EXISTS ( SELECT a+1 FROM y2 ) +} { + 1 1 1 + 2 2 2 + 3 3 3 + 4 4 4 +} + +do_subquery_test 3.9 1 { + SELECT * FROM y1 WHERE EXISTS ( + SELECT 1 FROM y2 one, y2 two WHERE one.z=a-1 AND one.y=a-1 + ) +} { + 2 2 2 + 4 4 4 +} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 4.0 { + CREATE TABLE tx1(a TEXT COLLATE nocase, b TEXT); + CREATE UNIQUE INDEX tx1ab ON tx1(a, b); + + INSERT INTO tx1 VALUES('a', 'a'); + INSERT INTO tx1 VALUES('B', 'b'); + INSERT INTO tx1 VALUES('c', 'c'); + INSERT INTO tx1 VALUES('D', 'd'); + INSERT INTO tx1 VALUES('e', 'e'); + + CREATE TABLE tx2(x, y); + INSERT INTO tx2 VALUES('A', 'a'); + INSERT INTO tx2 VALUES('b', 'b'); + INSERT INTO tx2 VALUES('C', 'c'); + INSERT INTO tx2 VALUES('D', 'd'); +} + +do_subquery_test 4.1 0 { + SELECT * FROM tx2 WHERE EXISTS ( + SELECT 1 FROM tx1 WHERE a=x AND b=y + ) +} { + A a + b b + C c + D d +} + +do_subquery_test 4.1.1 0 { + SELECT * FROM tx2 WHERE EXISTS ( + SELECT 1 FROM tx1 WHERE (a COLLATE nocase)=x AND b=y + ) +} { + A a b b C c D d +} +do_subquery_test 4.1.2 0 { + SELECT * FROM tx2 WHERE EXISTS ( + SELECT 1 FROM tx1 WHERE a=x AND (b COLLATE binary)=y + ) +} { + A a b b C c D d +} +do_subquery_test 4.1.1 0 { + SELECT * FROM tx2 WHERE EXISTS ( + SELECT 1 FROM tx1 WHERE x=(a COLLATE nocase) AND b=y + ) +} { + A a b b C c D d +} +do_subquery_test 4.1.2 0 { + SELECT * FROM tx2 WHERE EXISTS ( + SELECT 1 FROM tx1 WHERE a=x AND y=(b COLLATE binary) + ) +} { + A a b b C c D d +} + +do_subquery_test 4.2 1 { + SELECT * FROM tx2 WHERE EXISTS ( + SELECT 1 FROM tx1 WHERE a=x AND b=y COLLATE nocase + ) +} { + A a + b b + C c + D d +} + +do_execsql_test 4.3 { + DROP INDEX tx1ab; + CREATE UNIQUE INDEX tx1ab ON tx1(a COLLATE binary, b); +} + +do_subquery_test 4.4 1 { + SELECT * FROM tx2 WHERE EXISTS ( + SELECT 1 FROM tx1 WHERE a=x AND b=y + ) +} { + A a + b b + C c + D d +} + +do_subquery_test 4.4 0 { + SELECT * FROM tx2 WHERE EXISTS ( + SELECT 1 FROM tx1 WHERE a=x COLLATE binary AND b=y + ) +} { + D d +} + +do_subquery_test 4.4 1 { + SELECT EXISTS ( SELECT x FROM tx1 ) FROM tx2 +} { + 1 1 1 1 +} + +do_subquery_test 4.4 1 { + SELECT (SELECT EXISTS ( SELECT x FROM tx1 ) WHERE 1) FROM tx2 +} { + 1 1 1 1 +} + +#------------------------------------------------------------------------- +proc cols {s f} { + set lCols [list] + for {set i $s} {$i<=$f} {incr i} { + lappend lCols [format "c%02d" $i] + } + join $lCols ", " +} +proc vals {n val} { + set lVal [list] + for {set i 0} {$i<$n} {incr i} { + lappend lVal $val + } + join $lVal ", " +} +proc exprs {s f} { + set lExpr [list] + for {set i $s} {$i<=$f} {incr i} { + lappend lExpr [format "c%02d = o" $i] + } + join $lExpr " AND " +} + + +do_execsql_test 5.0 " + CREATE TABLE a1( [cols 0 99] ); +" +do_execsql_test 5.1 " + -- 63 column index + CREATE UNIQUE INDEX a1idx1 ON a1( [cols 0 62] ); +" +do_execsql_test 5.2 " + -- 64 column index + CREATE UNIQUE INDEX a1idx2 ON a1( [cols 10 73] ); +" +do_execsql_test 5.2 " + -- 65 column index + CREATE UNIQUE INDEX a1idx3 ON a1( [cols 20 84] ); +" + +do_test 5.3 { + foreach v {1 2 3 4 5 6} { + execsql "INSERT INTO a1 VALUES( [vals 100 $v] )" + } +} {} + +do_execsql_test 5.4 { + CREATE TABLE a2(o); + INSERT INTO a2 VALUES(2), (5); +} + +do_subquery_test 5.5 0 " + SELECT o FROM a2 WHERE EXISTS ( + SELECT 1 FROM a1 WHERE [exprs 0 62] + ) +" { + 2 5 +} + +do_subquery_test 5.6 1 " + SELECT o FROM a2 WHERE EXISTS ( + SELECT 1 FROM a1 WHERE [exprs 10 73] + ) +" { + 2 5 +} + +do_subquery_test 5.7 1 " + SELECT o FROM a2 WHERE EXISTS ( + SELECT 1 FROM a1 WHERE [exprs 20 84] + ) +" { + 2 5 +} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 6.0 { + CREATE TABLE t1(a, b UNIQUE, c UNIQUE); + CREATE TABLE t2(a INfEGER PRIMARY KEY, b); + CREATE UNIQUE INDEX t2b ON t2(b); +} + +do_catchsql_test 6.1 { + SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c COLLATE f = a) +} {1 {no such collation sequence: f}} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 7.0 { + CREATE TABLE t1(x); + CREATE TABLE t2(y UNIQUE); + + INSERT INTO t1 VALUES(1), (2); + INSERT INTO t2 VALUES(1), (3); + + SELECT * FROM t1 one LEFT JOIN t1 two ON (one.x=two.x AND EXISTS ( + SELECT 1 FROM t2 WHERE y=one.x + )); +} { + 1 1 + 2 {} +} + + + +finish_test + + diff --git a/test/existsexpr2.test b/test/existsexpr2.test new file mode 100644 index 000000000..f7644bf80 --- /dev/null +++ b/test/existsexpr2.test @@ -0,0 +1,96 @@ +# 2024 June 14 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +source $testdir/lock_common.tcl +set testprefix existsexpr2 + + +do_execsql_test 1.0 { + CREATE TABLE x1(a, b, PRIMARY KEY(a)) WITHOUT ROWID; + INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6); + CREATE INDEX x1b ON x1(b); + + CREATE TABLE x2(x, y); + INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6); +} + +do_execsql_test 1.1 { + SELECT * FROM x1 WHERE EXISTS (SELECT 1 FROM x2 WHERE a!=123) +} {1 2 3 4 5 6} + +do_execsql_test 1.2 { + CREATE TABLE x3(u, v); + CREATE INDEX x3u ON x3(u); + INSERT INTO x3 VALUES + (1, 1), (1, 2), (1, 3), + (2, 1), (2, 2), (2, 3); +} + +do_execsql_test 1.3 { + SELECT * FROM x1 WHERE EXISTS ( + SELECT 1 FROM x3 WHERE u IN (1, 2, 3, 4) AND v=b + ); +} { + 1 2 +} + +#------------------------------------------------------------------------- +# +reset_db +do_execsql_test 2.0 { + CREATE TABLE t1(a, b, c); + CREATE INDEX t1ab ON t1(a,b); + + INSERT INTO t1 VALUES + ('abc', 1, 1), + ('abc', 2, 2), + ('abc', 2, 3), + + ('def', 1, 1), + ('def', 2, 2), + ('def', 2, 3); + + CREATE TABLE t2(x, y); + INSERT INTO t2 VALUES(1, 1), (2, 2), (3, 3); + + ANALYZE; + DELETE FROM sqlite_stat1; + INSERT INTO sqlite_stat1 VALUES('t1','t1ab','10000 5000 2'); + ANALYZE sqlite_master; +} + + +do_execsql_test 2.1 { + SELECT a,b,c FROM t1 WHERE b=2 ORDER BY a +} { + abc 2 2 + abc 2 3 + def 2 2 + def 2 3 +} + +do_execsql_test 2.2 { + SELECT x, y FROM t2 WHERE EXISTS ( + SELECT 1 FROM t1 WHERE b=x + ) +} { + 1 1 + 2 2 +} + + + +finish_test + + diff --git a/test/existsfault.test b/test/existsfault.test new file mode 100644 index 000000000..4b335d84c --- /dev/null +++ b/test/existsfault.test @@ -0,0 +1,49 @@ +# 2024 May 25 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +source $testdir/lock_common.tcl +source $testdir/malloc_common.tcl +set testprefix existsfault + +db close +sqlite3_shutdown +sqlite3_config_lookaside 0 0 +sqlite3_initialize +autoinstall_test_functions +sqlite3 db test.db + +do_execsql_test 1.0 { + CREATE TABLE x1(a, b); + INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6); + CREATE UNIQUE INDEX x1a ON x1(a); + CREATE INDEX x1b ON x1(b); + + CREATE TABLE x2(x, y); + INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6); +} + +do_faultsim_test 1 -faults oom* -prep { + sqlite3 db test.db + execsql { SELECT * FROM sqlite_schema } +} -body { + execsql { + SELECT count(*) FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x) AND y!=11 + } +} -test { + faultsim_test_result {0 3} +} + +finish_test + + |