Index: ext/rtree/rtreeC.test ================================================================== --- ext/rtree/rtreeC.test +++ ext/rtree/rtreeC.test @@ -266,8 +266,91 @@ db2 close execsql { SELECT * FROM rt } } {1 2.0 3.0} db close } + +#-------------------------------------------------------------------- +# Test that queries featuring LEFT or CROSS JOINS are handled correctly. +# Handled correctly in this case means: +# +# * Terms with prereqs that appear to the left of a LEFT JOIN against +# the virtual table are always available to xBestIndex. +# +# * Terms with prereqs that appear to the right of a LEFT JOIN against +# the virtual table are never available to xBestIndex. +# +# And the same behaviour for CROSS joins. +# +reset_db +do_execsql_test 7.0 { + CREATE TABLE xdir(x1); + CREATE TABLE ydir(y1); + CREATE VIRTUAL TABLE rt USING rtree_i32(id, xmin, xmax, ymin, ymax); + + INSERT INTO xdir VALUES(5); + INSERT INTO ydir VALUES(10); + + INSERT INTO rt VALUES(1, 2, 7, 12, 14); -- Not a hit + INSERT INTO rt VALUES(2, 2, 7, 8, 12); -- A hit! + INSERT INTO rt VALUES(3, 7, 11, 8, 12); -- Not a hit! + INSERT INTO rt VALUES(4, 5, 5, 10, 10); -- A hit! + +} + +proc do_eqp_execsql_test {tn sql res} { + set query "EXPLAIN QUERY PLAN $sql ; $sql " + uplevel [list do_execsql_test $tn $query $res] +} + +do_eqp_execsql_test 7.1 { + SELECT id FROM xdir, rt, ydir + ON (y1 BETWEEN ymin AND ymax) + WHERE (x1 BETWEEN xmin AND xmax); +} { + 0 0 0 {SCAN TABLE xdir} + 0 1 2 {SCAN TABLE ydir} + 0 2 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 2:B2D3B0D1} + 2 4 +} + +do_eqp_execsql_test 7.2 { + SELECT * FROM xdir, rt LEFT JOIN ydir + ON (y1 BETWEEN ymin AND ymax) + WHERE (x1 BETWEEN xmin AND xmax); +} { + 0 0 0 {SCAN TABLE xdir} + 0 1 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 2:B0D1} + 0 2 2 {SCAN TABLE ydir} + + 5 1 2 7 12 14 {} + 5 2 2 7 8 12 10 + 5 4 5 5 10 10 10 +} + +do_eqp_execsql_test 7.3 { + SELECT id FROM xdir, rt CROSS JOIN ydir + ON (y1 BETWEEN ymin AND ymax) + WHERE (x1 BETWEEN xmin AND xmax); +} { + 0 0 0 {SCAN TABLE xdir} + 0 1 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 2:B0D1} + 0 2 2 {SCAN TABLE ydir} + 2 4 +} + +do_eqp_execsql_test 7.4 { + SELECT id FROM rt, xdir CROSS JOIN ydir + ON (y1 BETWEEN ymin AND ymax) + WHERE (x1 BETWEEN xmin AND xmax); +} { + 0 0 1 {SCAN TABLE xdir} + 0 1 0 {SCAN TABLE rt VIRTUAL TABLE INDEX 2:B0D1} + 0 2 2 {SCAN TABLE ydir} + 2 4 +} + +finish_test + finish_test Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -755,10 +755,11 @@ ** by passing the pointer returned by this function to sqlite3_free(). */ static sqlite3_index_info *allocateIndexInfo( Parse *pParse, WhereClause *pWC, + Bitmask mUnusable, /* Ignore terms with these prereqs */ struct SrcList_item *pSrc, ExprList *pOrderBy ){ int i, j; int nTerm; @@ -771,10 +772,11 @@ /* Count the number of possible WHERE clause constraints referring ** to this virtual table */ for(i=nTerm=0, pTerm=pWC->a; inTerm; i++, pTerm++){ if( pTerm->leftCursor != pSrc->iCursor ) continue; + if( pTerm->prereqRight & mUnusable ) continue; assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) ); testcase( pTerm->eOperator & WO_IN ); testcase( pTerm->eOperator & WO_ISNULL ); testcase( pTerm->eOperator & WO_IS ); testcase( pTerm->eOperator & WO_ALL ); @@ -825,10 +827,11 @@ pUsage; for(i=j=0, pTerm=pWC->a; inTerm; i++, pTerm++){ u8 op; if( pTerm->leftCursor != pSrc->iCursor ) continue; + if( pTerm->prereqRight & mUnusable ) continue; assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) ); testcase( pTerm->eOperator & WO_IN ); testcase( pTerm->eOperator & WO_IS ); testcase( pTerm->eOperator & WO_ISNULL ); testcase( pTerm->eOperator & WO_ALL ); @@ -2664,14 +2667,36 @@ #ifndef SQLITE_OMIT_VIRTUALTABLE /* ** Add all WhereLoop objects for a table of the join identified by ** pBuilder->pNew->iTab. That table is guaranteed to be a virtual table. +** +** If there are no LEFT or CROSS JOIN joins in the query, both mExtra and +** mUnusable are set to 0. Otherwise, mExtra is a mask of all FROM clause +** entries that occur before the virtual table in the FROM clause and are +** separated from it by at least one LEFT or CROSS JOIN. Similarly, the +** mUnusable mask contains all FROM clause entries that occur after the +** virtual table and are separated from it by at least one LEFT or +** CROSS JOIN. +** +** For example, if the query were: +** +** ... FROM t1, t2 LEFT JOIN t3, t4, vt CROSS JOIN t5, t6; +** +** then mExtra corresponds to (t1, t2) and mUnusable to (t5, t6). +** +** All the tables in mExtra must be scanned before the current virtual +** table. So any terms for which all prerequisites are satisfied by +** mExtra may be specified as "usable" in all calls to xBestIndex. +** Conversely, all tables in mUnusable must be scanned after the current +** virtual table, so any terms for which the prerequisites overlap with +** mUnusable should always be configured as "not-usable" for xBestIndex. */ static int whereLoopAddVirtual( WhereLoopBuilder *pBuilder, /* WHERE clause information */ - Bitmask mExtra + Bitmask mExtra, /* Tables that must be scanned before this one */ + Bitmask mUnusable /* Tables that must be scanned after this one */ ){ WhereInfo *pWInfo; /* WHERE analysis context */ Parse *pParse; /* The parsing context */ WhereClause *pWC; /* The WHERE clause */ struct SrcList_item *pSrc; /* The FROM clause term to search */ @@ -2688,19 +2713,20 @@ int seenVar = 0; /* True if a non-constant constraint is seen */ int iPhase; /* 0: const w/o IN, 1: const, 2: no IN, 2: IN */ WhereLoop *pNew; int rc = SQLITE_OK; + assert( (mExtra & mUnusable)==0 ); pWInfo = pBuilder->pWInfo; pParse = pWInfo->pParse; db = pParse->db; pWC = pBuilder->pWC; pNew = pBuilder->pNew; pSrc = &pWInfo->pTabList->a[pNew->iTab]; pTab = pSrc->pTab; assert( IsVirtual(pTab) ); - pIdxInfo = allocateIndexInfo(pParse, pWC, pSrc, pBuilder->pOrderBy); + pIdxInfo = allocateIndexInfo(pParse, pWC, mUnusable, pSrc,pBuilder->pOrderBy); if( pIdxInfo==0 ) return SQLITE_NOMEM; pNew->prereq = 0; pNew->rSetup = 0; pNew->wsFlags = WHERE_VIRTUALTABLE; pNew->nLTerm = 0; @@ -2726,19 +2752,19 @@ case 0: /* Constants without IN operator */ pIdxCons->usable = 0; if( (pTerm->eOperator & WO_IN)!=0 ){ seenIn = 1; } - if( pTerm->prereqRight!=0 ){ + if( (pTerm->prereqRight & ~mExtra)!=0 ){ seenVar = 1; }else if( (pTerm->eOperator & WO_IN)==0 ){ pIdxCons->usable = 1; } break; case 1: /* Constants with IN operators */ assert( seenIn ); - pIdxCons->usable = (pTerm->prereqRight==0); + pIdxCons->usable = (pTerm->prereqRight & ~mExtra)==0; break; case 2: /* Variables without IN */ assert( seenVar ); pIdxCons->usable = (pTerm->eOperator & WO_IN)==0; break; @@ -2833,11 +2859,15 @@ /* ** Add WhereLoop entries to handle OR terms. This works for either ** btrees or virtual tables. */ -static int whereLoopAddOr(WhereLoopBuilder *pBuilder, Bitmask mExtra){ +static int whereLoopAddOr( + WhereLoopBuilder *pBuilder, + Bitmask mExtra, + Bitmask mUnusable +){ WhereInfo *pWInfo = pBuilder->pWInfo; WhereClause *pWC; WhereLoop *pNew; WhereTerm *pTerm, *pWCEnd; int rc = SQLITE_OK; @@ -2892,18 +2922,18 @@ } } #endif #ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pItem->pTab) ){ - rc = whereLoopAddVirtual(&sSubBuild, mExtra); + rc = whereLoopAddVirtual(&sSubBuild, mExtra, mUnusable); }else #endif { rc = whereLoopAddBtree(&sSubBuild, mExtra); } if( rc==SQLITE_OK ){ - rc = whereLoopAddOr(&sSubBuild, mExtra); + rc = whereLoopAddOr(&sSubBuild, mExtra, mUnusable); } assert( rc==SQLITE_OK || sCur.n==0 ); if( sCur.n==0 ){ sSum.n = 0; break; @@ -2961,37 +2991,47 @@ Bitmask mExtra = 0; Bitmask mPrior = 0; int iTab; SrcList *pTabList = pWInfo->pTabList; struct SrcList_item *pItem; + struct SrcList_item *pEnd = &pTabList->a[pWInfo->nLevel]; sqlite3 *db = pWInfo->pParse->db; - int nTabList = pWInfo->nLevel; int rc = SQLITE_OK; - u8 priorJoinType = 0; WhereLoop *pNew; + u8 priorJointype = 0; /* Loop over the tables in the join, from left to right */ pNew = pBuilder->pNew; whereLoopInit(pNew); - for(iTab=0, pItem=pTabList->a; iTaba; pItemiTab = iTab; pNew->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, pItem->iCursor); - if( ((pItem->jointype|priorJoinType) & (JT_LEFT|JT_CROSS))!=0 ){ + if( ((pItem->jointype|priorJointype) & (JT_LEFT|JT_CROSS))!=0 ){ + /* This condition is true when pItem is the FROM clause term on the + ** right-hand-side of a LEFT or CROSS JOIN. */ mExtra = mPrior; } - priorJoinType = pItem->jointype; + priorJointype = pItem->jointype; if( IsVirtual(pItem->pTab) ){ - rc = whereLoopAddVirtual(pBuilder, mExtra); + struct SrcList_item *p; + for(p=&pItem[1]; pjointype & (JT_LEFT|JT_CROSS)) ){ + mUnusable |= sqlite3WhereGetMask(&pWInfo->sMaskSet, p->iCursor); + } + } + rc = whereLoopAddVirtual(pBuilder, mExtra, mUnusable); }else{ rc = whereLoopAddBtree(pBuilder, mExtra); } if( rc==SQLITE_OK ){ - rc = whereLoopAddOr(pBuilder, mExtra); + rc = whereLoopAddOr(pBuilder, mExtra, mUnusable); } mPrior |= pNew->maskSelf; if( rc || db->mallocFailed ) break; } + whereLoopClear(db, pNew); return rc; } /* Index: test/join.test ================================================================== --- test/join.test +++ test/join.test @@ -684,7 +684,33 @@ jointest join-12.11 65535 {1 {too many references to "t14": max 65535}} jointest join-12.12 65536 {1 {too many references to "t14": max 65535}} jointest join-12.13 65537 {1 {too many references to "t14": max 65535}} } } + + +#------------------------------------------------------------------------- +# Test a problem with reordering tables following a LEFT JOIN. +# +do_execsql_test join-13.0 { + CREATE TABLE aa(a); + CREATE TABLE bb(b); + CREATE TABLE cc(c); + + INSERT INTO aa VALUES(45); + INSERT INTO cc VALUES(45); + INSERT INTO cc VALUES(45); +} + +do_execsql_test join-13.1 { + SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a; +} {45 {} 45 45 {} 45} + +# In the following, the order of [cc] and [bb] must not be exchanged, even +# though this would be helpful if the query used an inner join. +do_execsql_test join-13.2 { + CREATE INDEX ccc ON cc(c); + SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a; +} {45 {} 45 45 {} 45} + finish_test