/ Check-in [a3e26038]
Login

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

Overview
Comment:Fix a problem to do with multi-or queries and automatic indexes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | multi-or-covering-index
Files: files | file ages | folders
SHA1: a3e26038a1d9f3331239f777c67d1920d904aab0
User & Date: dan 2012-08-24 18:44:56
Context
2012-08-24
19:52
Add extra tests to whereD.test. check-in: 7e961eef user: dan tags: multi-or-covering-index
18:44
Fix a problem to do with multi-or queries and automatic indexes. check-in: a3e26038 user: dan tags: multi-or-covering-index
17:52
More test cases for OR-optimization covering indices. check-in: e5c3190c user: drh tags: multi-or-covering-index
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/delete.c.

   367    367       int iRowid = ++pParse->nMem;    /* Used for storing rowid values. */
   368    368       int regRowid;                   /* Actual register containing rowids */
   369    369   
   370    370       /* Collect rowids of every row to be deleted.
   371    371       */
   372    372       sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet);
   373    373       pWInfo = sqlite3WhereBegin(
   374         -        pParse, pTabList, pWhere, 0, 0, WHERE_DUPLICATES_OK
          374  +        pParse, pTabList, pWhere, 0, 0, WHERE_DUPLICATES_OK, 0
   375    375       );
   376    376       if( pWInfo==0 ) goto delete_from_cleanup;
   377    377       regRowid = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iCur, iRowid, 0);
   378    378       sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, regRowid);
   379    379       if( db->flags & SQLITE_CountRows ){
   380    380         sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
   381    381       }

Changes to src/fkey.c.

   556    556     sNameContext.pParse = pParse;
   557    557     sqlite3ResolveExprNames(&sNameContext, pWhere);
   558    558   
   559    559     /* Create VDBE to loop through the entries in pSrc that match the WHERE
   560    560     ** clause. If the constraint is not deferred, throw an exception for
   561    561     ** each row found. Otherwise, for deferred constraints, increment the
   562    562     ** deferred constraint counter by nIncr for each row selected.  */
   563         -  pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0);
          563  +  pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0);
   564    564     if( nIncr>0 && pFKey->isDeferred==0 ){
   565    565       sqlite3ParseToplevel(pParse)->mayAbort = 1;
   566    566     }
   567    567     sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
   568    568     if( pWInfo ){
   569    569       sqlite3WhereEnd(pWInfo);
   570    570     }

Changes to src/select.c.

  4020   4020     }
  4021   4021   
  4022   4022     /* Aggregate and non-aggregate queries are handled differently */
  4023   4023     if( !isAgg && pGroupBy==0 ){
  4024   4024       ExprList *pDist = (isDistinct ? p->pEList : 0);
  4025   4025   
  4026   4026       /* Begin the database scan. */
  4027         -    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy, pDist, 0);
         4027  +    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy, pDist, 0,0);
  4028   4028       if( pWInfo==0 ) goto select_end;
  4029   4029       if( pWInfo->nRowOut < p->nSelectRow ) p->nSelectRow = pWInfo->nRowOut;
  4030   4030   
  4031   4031       /* If sorting index that was created by a prior OP_OpenEphemeral 
  4032   4032       ** instruction ended up not being needed, then change the OP_OpenEphemeral
  4033   4033       ** into an OP_Noop.
  4034   4034       */
................................................................................
  4193   4193   
  4194   4194         /* Begin a loop that will extract all source rows in GROUP BY order.
  4195   4195         ** This might involve two separate loops with an OP_Sort in between, or
  4196   4196         ** it might be a single loop that uses an index to extract information
  4197   4197         ** in the right order to begin with.
  4198   4198         */
  4199   4199         sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset);
  4200         -      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pGroupBy, 0, 0);
         4200  +      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pGroupBy, 0, 0, 0);
  4201   4201         if( pWInfo==0 ) goto select_end;
  4202   4202         if( pGroupBy==0 ){
  4203   4203           /* The optimizer is able to deliver rows in group by order so
  4204   4204           ** we do not have to sort.  The OP_OpenEphemeral table will be
  4205   4205           ** cancelled later because we still need to use the pKeyInfo
  4206   4206           */
  4207   4207           pGroupBy = p->pGroupBy;
................................................................................
  4462   4462           }
  4463   4463     
  4464   4464           /* This case runs if the aggregate has no GROUP BY clause.  The
  4465   4465           ** processing is much simpler since there is only a single row
  4466   4466           ** of output.
  4467   4467           */
  4468   4468           resetAccumulator(pParse, &sAggInfo);
  4469         -        pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pMinMax, 0, flag);
         4469  +        pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pMinMax,0,flag,0);
  4470   4470           if( pWInfo==0 ){
  4471   4471             sqlite3ExprListDelete(db, pDel);
  4472   4472             goto select_end;
  4473   4473           }
  4474   4474           updateAccumulator(pParse, &sAggInfo);
  4475   4475           if( !pMinMax && flag ){
  4476   4476             sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iBreak);

Changes to src/sqliteInt.h.

  2803   2803   int sqlite3IsReadOnly(Parse*, Table*, int);
  2804   2804   void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
  2805   2805   #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
  2806   2806   Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *, char *);
  2807   2807   #endif
  2808   2808   void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
  2809   2809   void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
  2810         -WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**,ExprList*,u16);
         2810  +WhereInfo *sqlite3WhereBegin(
         2811  +    Parse*,SrcList*,Expr*,ExprList**,ExprList*,u16,int);
  2811   2812   void sqlite3WhereEnd(WhereInfo*);
  2812   2813   int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, u8);
  2813   2814   void sqlite3ExprCodeGetColumnOfTable(Vdbe*, Table*, int, int, int);
  2814   2815   void sqlite3ExprCodeMove(Parse*, int, int, int);
  2815   2816   void sqlite3ExprCodeCopy(Parse*, int, int, int);
  2816   2817   void sqlite3ExprCacheStore(Parse*, int, int, int);
  2817   2818   void sqlite3ExprCachePush(Parse*);

Changes to src/update.c.

   309    309       goto update_cleanup;
   310    310     }
   311    311   
   312    312     /* Begin the database scan
   313    313     */
   314    314     sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid);
   315    315     pWInfo = sqlite3WhereBegin(
   316         -      pParse, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED
          316  +      pParse, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED, 0
   317    317     );
   318    318     if( pWInfo==0 ) goto update_cleanup;
   319    319     okOnePass = pWInfo->okOnePass;
   320    320   
   321    321     /* Remember the rowid of every item to be updated.
   322    322     */
   323    323     sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regOldRowid);

Changes to src/where.c.

  4293   4293       **          Return     2                # Jump back to the Gosub
  4294   4294       **
  4295   4295       **       B: <after the loop>
  4296   4296       **
  4297   4297       */
  4298   4298       WhereClause *pOrWc;    /* The OR-clause broken out into subterms */
  4299   4299       SrcList *pOrTab;       /* Shortened table list or OR-clause generation */
  4300         -    Index *pCov = 0;       /* Potential covering index (or NULL) */
  4301         -    int iCovCur = 0;       /* Cursor used to open pCov */
         4300  +    Index *pCov = 0;             /* Potential covering index (or NULL) */
         4301  +    int iCovCur = pParse->nTab++;  /* Cursor used for index scans (if any) */
  4302   4302   
  4303   4303       int regReturn = ++pParse->nMem;           /* Register used with OP_Gosub */
  4304   4304       int regRowset = 0;                        /* Register for RowSet object */
  4305   4305       int regRowid = 0;                         /* Register holding rowid */
  4306   4306       int iLoopBody = sqlite3VdbeMakeLabel(v);  /* Start of loop body */
  4307   4307       int iRetInit;                             /* Address of regReturn init */
  4308   4308       int untestedTerms = 0;             /* Some terms not completely tested */
................................................................................
  4390   4390           if( pAndExpr ){
  4391   4391             pAndExpr->pLeft = pOrExpr;
  4392   4392             pOrExpr = pAndExpr;
  4393   4393           }
  4394   4394           /* Loop through table entries that match term pOrTerm. */
  4395   4395           pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0,
  4396   4396                           WHERE_OMIT_OPEN_CLOSE | WHERE_AND_ONLY |
  4397         -                        WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY);
         4397  +                        WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY, iCovCur);
  4398   4398           assert( pSubWInfo || pParse->nErr || pParse->db->mallocFailed );
  4399   4399           if( pSubWInfo ){
  4400   4400             WhereLevel *pLvl;
  4401   4401             explainOneScan(
  4402   4402                 pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
  4403   4403             );
  4404   4404             if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
................................................................................
  4422   4422             ** index, and the index is opened using the same cursor number
  4423   4423             ** by each call to sqlite3WhereBegin() made by this loop, it may
  4424   4424             ** be possible to use that index as a covering index.
  4425   4425             **
  4426   4426             ** If the call to sqlite3WhereBegin() above resulted in a scan that
  4427   4427             ** uses an index, and this is either the first OR-connected term
  4428   4428             ** processed or the index is the same as that used by all previous
  4429         -          ** terms, set pCov to the candidate covering index and iCovCur to
  4430         -          ** the cursor number used to open it. Otherwise, set pCov to NULL
  4431         -          ** to indicate that no candidate covering index will be available.
         4429  +          ** terms, set pCov to the candidate covering index. Otherwise, set 
         4430  +          ** pCov to NULL to indicate that no candidate covering index will 
         4431  +          ** be available.
  4432   4432             */
  4433   4433             pLvl = &pSubWInfo->a[0];
  4434   4434             if( (pLvl->plan.wsFlags & WHERE_INDEXED)!=0
  4435   4435              && (pLvl->plan.wsFlags & WHERE_TEMP_INDEX)==0
  4436         -           && (pLvl->iIdxCur==pParse->nTab-1)
  4437         -           && (ii==0 || (pLvl->plan.u.pIdx==pCov && pLvl->iIdxCur==iCovCur))
         4436  +           && (ii==0 || pLvl->plan.u.pIdx==pCov)
  4438   4437             ){
         4438  +            assert( pLvl->iIdxCur==iCovCur );
  4439   4439               pCov = pLvl->plan.u.pIdx;
  4440         -            iCovCur = pLvl->iIdxCur;
  4441         -            /* Decrement pParse->nTab in order to make cursor number iCovCur
  4442         -            ** available to the next OR-connected term.  */
  4443         -            pParse->nTab--;
  4444   4440             }else{
  4445   4441               pCov = 0;
  4446   4442             }
  4447   4443   
  4448   4444             /* Finish the loop through table entries that match term pOrTerm. */
  4449   4445             sqlite3WhereEnd(pSubWInfo);
  4450   4446           }
  4451   4447         }
  4452   4448       }
  4453         -    /* Increment pParse->nTab here to ensure that it really is larger than
  4454         -    ** the largest cursor number used by the VM (this may not be the case
  4455         -    ** if pParse->nTab was decremented by the final iteration of the loop
  4456         -    ** above).  */
  4457         -    pParse->nTab++;
  4458   4449       pLevel->pCovidx = pCov;
  4459   4450       pLevel->iIdxCur = iCovCur;
  4460   4451       if( pAndExpr ){
  4461   4452         pAndExpr->pLeft = 0;
  4462   4453         sqlite3ExprDelete(pParse->db, pAndExpr);
  4463   4454       }
  4464   4455       sqlite3VdbeChangeP1(v, iRetInit, sqlite3VdbeCurrentAddr(v));
................................................................................
  4670   4661   */
  4671   4662   WhereInfo *sqlite3WhereBegin(
  4672   4663     Parse *pParse,        /* The parser context */
  4673   4664     SrcList *pTabList,    /* A list of all tables to be scanned */
  4674   4665     Expr *pWhere,         /* The WHERE clause */
  4675   4666     ExprList **ppOrderBy, /* An ORDER BY clause, or NULL */
  4676   4667     ExprList *pDistinct,  /* The select-list for DISTINCT queries - or NULL */
  4677         -  u16 wctrlFlags        /* One of the WHERE_* flags defined in sqliteInt.h */
         4668  +  u16 wctrlFlags,       /* One of the WHERE_* flags defined in sqliteInt.h */
         4669  +  int iIdxCur           /* If WHERE_ONETABLE_ONLY is set, index cursor number */
  4678   4670   ){
  4679   4671     int i;                     /* Loop counter */
  4680   4672     int nByteWInfo;            /* Num. bytes allocated for WhereInfo struct */
  4681   4673     int nTabList;              /* Number of elements in pTabList */
  4682   4674     WhereInfo *pWInfo;         /* Will become the return value of this function */
  4683   4675     Vdbe *v = pParse->pVdbe;   /* The virtual database engine */
  4684   4676     Bitmask notReady;          /* Cursors that are not yet positioned */
................................................................................
  4990   4982         pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
  4991   4983       }
  4992   4984       andFlags &= bestPlan.plan.wsFlags;
  4993   4985       pLevel->plan = bestPlan.plan;
  4994   4986       testcase( bestPlan.plan.wsFlags & WHERE_INDEXED );
  4995   4987       testcase( bestPlan.plan.wsFlags & WHERE_TEMP_INDEX );
  4996   4988       if( bestPlan.plan.wsFlags & (WHERE_INDEXED|WHERE_TEMP_INDEX) ){
  4997         -      pLevel->iIdxCur = pParse->nTab++;
         4989  +      if( (wctrlFlags & WHERE_ONETABLE_ONLY) 
         4990  +       && (bestPlan.plan.wsFlags & WHERE_TEMP_INDEX)==0 
         4991  +      ){
         4992  +        pLevel->iIdxCur = iIdxCur;
         4993  +      }else{
         4994  +        pLevel->iIdxCur = pParse->nTab++;
         4995  +      }
  4998   4996       }else{
  4999   4997         pLevel->iIdxCur = -1;
  5000   4998       }
  5001   4999       notReady &= ~getMask(pMaskSet, pTabList->a[bestJ].iCursor);
  5002   5000       pLevel->iFrom = (u8)bestJ;
  5003   5001       if( bestPlan.plan.nRow>=(double)1 ){
  5004   5002         pParse->nQueryLoop *= bestPlan.plan.nRow;