Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -4675,39 +4675,77 @@ for(ii=0; iinLevel; ii++){ whereLoopPrint(pWInfo->a[ii].pWLoop, sWLB.pWC); } } #endif - /* Attempt to omit tables from the join that do not effect the result */ + + /* Attempt to omit tables from the join that do not affect the result. + ** For a table to not affect the result, the following must be true: + ** + ** 1) The query must not be an aggregate. + ** 2) The table must be the RHS of a LEFT JOIN. + ** 3) Either the query must be DISTINCT, or else the ON or USING clause + ** must contain a constraint that limits the scan of the table to + ** at most a single row. + ** 4) The table must not be referenced by any part of the query apart + ** from its own USING or ON clause. + ** + ** For example, given: + ** + ** CREATE TABLE t1(ipk INTEGER PRIMARY KEY, v1); + ** CREATE TABLE t2(ipk INTEGER PRIMARY KEY, v2); + ** CREATE TABLE t3(ipk INTEGER PRIMARY KEY, v3); + ** + ** then table t2 can be omitted from the following: + ** + ** SELECT v1, v3 FROM t1 + ** LEFT JOIN t2 USING (t1.ipk=t2.ipk) + ** LEFT JOIN t3 USING (t1.ipk=t3.ipk) + ** + ** or from: + ** + ** SELECT DISTINCT v1, v3 FROM t1 + ** LEFT JOIN t2 + ** LEFT JOIN t3 USING (t1.ipk=t3.ipk) + */ if( pWInfo->nLevel>=2 - && pResultSet!=0 + && pResultSet!=0 /* guarantees condition (1) above */ && OptimizationEnabled(db, SQLITE_OmitNoopJoin) ){ + int i; Bitmask tabUsed = sqlite3WhereExprListUsage(pMaskSet, pResultSet); if( sWLB.pOrderBy ){ tabUsed |= sqlite3WhereExprListUsage(pMaskSet, sWLB.pOrderBy); } - while( pWInfo->nLevel>=2 ){ + for(i=pWInfo->nLevel-1; i>=1; i--){ WhereTerm *pTerm, *pEnd; - pLoop = pWInfo->a[pWInfo->nLevel-1].pWLoop; - if( (pWInfo->pTabList->a[pLoop->iTab].fg.jointype & JT_LEFT)==0 ) break; + struct SrcList_item *pItem; + pLoop = pWInfo->a[i].pWLoop; + pItem = &pWInfo->pTabList->a[pLoop->iTab]; + if( (pItem->fg.jointype & JT_LEFT)==0 ) continue; if( (wctrlFlags & WHERE_WANT_DISTINCT)==0 && (pLoop->wsFlags & WHERE_ONEROW)==0 ){ - break; + continue; } - if( (tabUsed & pLoop->maskSelf)!=0 ) break; + if( (tabUsed & pLoop->maskSelf)!=0 ) continue; pEnd = sWLB.pWC->a + sWLB.pWC->nTerm; for(pTerm=sWLB.pWC->a; pTermprereqAll & pLoop->maskSelf)!=0 - && !ExprHasProperty(pTerm->pExpr, EP_FromJoin) - ){ - break; + if( (pTerm->prereqAll & pLoop->maskSelf)!=0 ){ + if( !ExprHasProperty(pTerm->pExpr, EP_FromJoin) + || pTerm->pExpr->iRightJoinTable!=pItem->iCursor + ){ + break; + } } } - if( pTerm drop loop %c not used\n", pLoop->cId)); + if( i!=pWInfo->nLevel-1 ){ + int nByte = (pWInfo->nLevel-1-i) * sizeof(WhereLevel); + memmove(&pWInfo->a[i], &pWInfo->a[i+1], nByte); + } pWInfo->nLevel--; nTabList--; } } WHERETRACE(0xffff,("*** Optimizer Finished ***\n")); @@ -4988,11 +5026,12 @@ if( pLevel->iLeftJoin ){ int ws = pLoop->wsFlags; addr = sqlite3VdbeAddOp1(v, OP_IfPos, pLevel->iLeftJoin); VdbeCoverage(v); assert( (ws & WHERE_IDX_ONLY)==0 || (ws & WHERE_INDEXED)!=0 ); if( (ws & WHERE_IDX_ONLY)==0 ){ - sqlite3VdbeAddOp1(v, OP_NullRow, pTabList->a[i].iCursor); + assert( pLevel->iTabCur==pTabList->a[pLevel->iFrom].iCursor ); + sqlite3VdbeAddOp1(v, OP_NullRow, pLevel->iTabCur); } if( (ws & WHERE_INDEXED) || ((ws & WHERE_MULTI_OR) && pLevel->u.pCovidx) ){ sqlite3VdbeAddOp1(v, OP_NullRow, pLevel->iIdxCur); Index: test/join2.test ================================================================== --- test/join2.test +++ test/join2.test @@ -120,7 +120,55 @@ SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_2 USING (k3); } { 0 0 0 {SCAN TABLE t1} 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)} } + +#------------------------------------------------------------------------- +# Test that tables other than the rightmost can be omitted from a +# LEFT JOIN query. +# +do_execsql_test 4.0 { + CREATE TABLE c1(k INTEGER PRIMARY KEY, v1); + CREATE TABLE c2(k INTEGER PRIMARY KEY, v2); + CREATE TABLE c3(k INTEGER PRIMARY KEY, v3); + + INSERT INTO c1 VALUES(1, 2); + INSERT INTO c2 VALUES(2, 3); + INSERT INTO c3 VALUES(3, 'v3'); + + INSERT INTO c1 VALUES(111, 1112); + INSERT INTO c2 VALUES(112, 1113); + INSERT INTO c3 VALUES(113, 'v1113'); +} +do_execsql_test 4.1.1 { + SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2); +} {2 v3 1112 {}} +do_execsql_test 4.1.2 { + SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1); +} {2 v3 1112 {}} + +do_execsql_test 4.1.3 { + SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1); +} {2 v3 1112 {}} + +do_execsql_test 4.1.4 { + SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1); +} {2 v3 2 v3 1112 {} 1112 {}} + +do_eqp_test 4.2.1 { + SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2); +} { + 0 0 0 {SCAN TABLE c1} + 0 1 1 {SEARCH TABLE c2 USING INTEGER PRIMARY KEY (rowid=?)} + 0 2 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)} +} +do_eqp_test 4.2.2 { + SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1); +} { + 0 0 0 {SCAN TABLE c1} + 0 1 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)} +} + + finish_test