/ Check-in [eed754fe]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:The optimization of check-in [b67a6e33f2] does not work (it generates incorrect VDBE code) if an OR term is AND-ed with a constant expression. So back that optimization out and add a test case to make sure it does not get added back in.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: eed754fe93644f4df082eac0c0a7ffc5c78ccd10
User & Date: drh 2014-07-22 19:14:42
Context
2014-07-22
20:02
Add the OP_ReopenIdx opcode that works like OP_OpenRead except that it becomes a no-op if the cursor is already open on the same index. Update the OR-optimization logic to make use of OP_ReopenIdx in order to avoid unnecessary cursor open requests sent to the B-Tree layer. check-in: 77f412ca user: drh tags: trunk
19:14
The optimization of check-in [b67a6e33f2] does not work (it generates incorrect VDBE code) if an OR term is AND-ed with a constant expression. So back that optimization out and add a test case to make sure it does not get added back in. check-in: eed754fe user: drh tags: trunk
16:00
Fix the index name for the shadow tables in the spellfix1 extension so that multiple instances of the spellfix1 virtual table can each have their own index. check-in: 438c348a user: drh tags: trunk
00:40
For the OR-optimization, avoid generating OP_OpenRead opcodes that reopen exactly the same index. check-in: b67a6e33 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqliteInt.h.

  2132   2132   #define WHERE_FORCE_TABLE      0x0020 /* Do not use an index-only search */
  2133   2133   #define WHERE_ONETABLE_ONLY    0x0040 /* Only code the 1st table in pTabList */
  2134   2134   #define WHERE_AND_ONLY         0x0080 /* Don't use indices for OR terms */
  2135   2135   #define WHERE_GROUPBY          0x0100 /* pOrderBy is really a GROUP BY */
  2136   2136   #define WHERE_DISTINCTBY       0x0200 /* pOrderby is really a DISTINCT clause */
  2137   2137   #define WHERE_WANT_DISTINCT    0x0400 /* All output needs to be distinct */
  2138   2138   #define WHERE_SORTBYGROUP      0x0800 /* Support sqlite3WhereIsSorted() */
  2139         -#define WHERE_OR_INDEX_OPEN    0x1000 /* OP_OpenRead for the OR index exists */
  2140   2139   
  2141   2140   /* Allowed return values from sqlite3WhereIsDistinct()
  2142   2141   */
  2143   2142   #define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */
  2144   2143   #define WHERE_DISTINCT_UNIQUE    1  /* No duplicates */
  2145   2144   #define WHERE_DISTINCT_ORDERED   2  /* All duplicates are adjacent */
  2146   2145   #define WHERE_DISTINCT_UNORDERED 3  /* Duplicates are scattered */

Changes to src/where.c.

  3418   3418       int regReturn = ++pParse->nMem;           /* Register used with OP_Gosub */
  3419   3419       int regRowset = 0;                        /* Register for RowSet object */
  3420   3420       int regRowid = 0;                         /* Register holding rowid */
  3421   3421       int iLoopBody = sqlite3VdbeMakeLabel(v);  /* Start of loop body */
  3422   3422       int iRetInit;                             /* Address of regReturn init */
  3423   3423       int untestedTerms = 0;             /* Some terms not completely tested */
  3424   3424       int ii;                            /* Loop counter */
  3425         -    int subWctrlFlags;                 /* wctrlFlags for sub-queries */
  3426   3425       Expr *pAndExpr = 0;                /* An ".. AND (...)" expression */
  3427   3426       Table *pTab = pTabItem->pTab;
  3428   3427      
  3429   3428       pTerm = pLoop->aLTerm[0];
  3430   3429       assert( pTerm!=0 );
  3431   3430       assert( pTerm->eOperator & WO_OR );
  3432   3431       assert( (pTerm->wtFlags & TERM_ORINFO)!=0 );
................................................................................
  3514   3513         }
  3515   3514       }
  3516   3515   
  3517   3516       /* Run a separate WHERE clause for each term of the OR clause.  After
  3518   3517       ** eliminating duplicates from other WHERE clauses, the action for each
  3519   3518       ** sub-WHERE clause is to to invoke the main loop body as a subroutine.
  3520   3519       */
  3521         -    subWctrlFlags = WHERE_OMIT_OPEN_CLOSE | WHERE_AND_ONLY |
  3522         -                    WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY;
  3523   3520       for(ii=0; ii<pOrWc->nTerm; ii++){
  3524   3521         WhereTerm *pOrTerm = &pOrWc->a[ii];
  3525   3522         if( pOrTerm->leftCursor==iCur || (pOrTerm->eOperator & WO_AND)!=0 ){
  3526   3523           WhereInfo *pSubWInfo;           /* Info for single OR-term scan */
  3527   3524           Expr *pOrExpr = pOrTerm->pExpr; /* Current OR clause term */
  3528   3525           int j1 = 0;                     /* Address of jump operation */
  3529   3526           if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){
  3530   3527             pAndExpr->pLeft = pOrExpr;
  3531   3528             pOrExpr = pAndExpr;
  3532   3529           }
  3533   3530           /* Loop through table entries that match term pOrTerm. */
  3534   3531           pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0,
  3535         -                        subWctrlFlags, iCovCur);
         3532  +                        WHERE_OMIT_OPEN_CLOSE | WHERE_AND_ONLY |
         3533  +                        WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY, iCovCur);
  3536   3534           assert( pSubWInfo || pParse->nErr || db->mallocFailed );
  3537   3535           if( pSubWInfo ){
  3538   3536             WhereLoop *pSubLoop;
  3539   3537             explainOneScan(
  3540   3538                 pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
  3541   3539             );
  3542   3540             /* This is the sub-WHERE clause body.  First skip over
................................................................................
  3619   3617             assert( (pSubLoop->wsFlags & WHERE_AUTO_INDEX)==0 );
  3620   3618             if( (pSubLoop->wsFlags & WHERE_INDEXED)!=0
  3621   3619              && (ii==0 || pSubLoop->u.btree.pIndex==pCov)
  3622   3620              && (HasRowid(pTab) || !IsPrimaryKeyIndex(pSubLoop->u.btree.pIndex))
  3623   3621             ){
  3624   3622               assert( pSubWInfo->a[0].iIdxCur==iCovCur );
  3625   3623               pCov = pSubLoop->u.btree.pIndex;
  3626         -            subWctrlFlags |= WHERE_OR_INDEX_OPEN;
  3627   3624             }else{
  3628   3625               pCov = 0;
  3629   3626             }
  3630   3627   
  3631   3628             /* Finish the loop through table entries that match term pOrTerm. */
  3632   3629             sqlite3WhereEnd(pSubWInfo);
  3633   3630           }
................................................................................
  6218   6215             iIndexCur++;
  6219   6216             pJ = pJ->pNext;
  6220   6217           }
  6221   6218           op = OP_OpenWrite;
  6222   6219           pWInfo->aiCurOnePass[1] = iIndexCur;
  6223   6220         }else if( iIdxCur && (wctrlFlags & WHERE_ONETABLE_ONLY)!=0 ){
  6224   6221           iIndexCur = iIdxCur;
  6225         -        if( (wctrlFlags & WHERE_OR_INDEX_OPEN)!=0 ){
  6226         -          /* For 2nd and subsequent subqueries for processing OR terms,
  6227         -          ** try to reuse the previous OP_OpenRead, if there is one.  The
  6228         -          ** WHERE_OR_INDEX_OPEN bit will only be set if there is a prior
  6229         -          ** OP_OpenRead opcode on cursor iIndexCur, so the while() loop
  6230         -          ** below is guaranteed to terminate.
  6231         -          */
  6232         -          VdbeOp *pOp = sqlite3VdbeGetOp(v, -1);
  6233         -          while( pOp->opcode!=OP_OpenRead || pOp->p1!=iIndexCur ) pOp--;
  6234         -          assert( pOp->p3==iDb );
  6235         -          if( pOp->p2==pIx->tnum ) op = 0;
  6236         -        }
  6237   6222         }else{
  6238   6223           iIndexCur = pParse->nTab++;
  6239   6224         }
  6240   6225         pLevel->iIdxCur = iIndexCur;
  6241   6226         assert( pIx->pSchema==pTab->pSchema );
  6242   6227         assert( iIndexCur>=0 );
  6243   6228         if( op ){

Changes to test/where2.test.

   747    747     EXPLAIN QUERY PLAN
   748    748       SELECT a.x, b.x
   749    749         FROM t12 AS a JOIN t12 AS b ON a.y=b.x
   750    750        WHERE (b.x=$abc OR b.y=$abc);
   751    751   } {/.*SEARCH TABLE t12 AS b .*SEARCH TABLE t12 AS b .*/}
   752    752   }
   753    753   
          754  +# Verify that all necessary OP_OpenRead opcodes occur in the OR optimization.
          755  +#
          756  +do_execsql_test where2-13.1 {
          757  +  CREATE TABLE t13(a,b);
          758  +  CREATE INDEX t13a ON t13(a);
          759  +  INSERT INTO t13 VALUES(4,5);
          760  +  SELECT * FROM t13 WHERE (1=2 AND a=3) OR a=4;
          761  +} {4 5}
   754    762   
   755    763   finish_test