/ Check-in [a917c1f0]
Login

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

Overview
Comment:Allow the "a=?1 OR a=?2" to "a IN (?1,?2)" transformation to work on virtual tables again. This was formerly restricted because virtual tables could not optimize IN terms. (See check-in [fad88e71cf195e].) But IN terms are now used by virtual tables (as of check-in [3d65c70343]) so the restriction can now be removed.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | IN-with-ORDERBY
Files: files | file ages | folders
SHA1: a917c1f09254b54e03e31b119cc49c551269d82e
User & Date: drh 2013-02-08 16:04:19
Context
2013-02-08
18:48
Loop through the elements on the RHS of an IN operator in reverse order when the ORDER BY clauses specifies DESC. check-in: f78395c8 user: drh tags: IN-with-ORDERBY
16:04
Allow the "a=?1 OR a=?2" to "a IN (?1,?2)" transformation to work on virtual tables again. This was formerly restricted because virtual tables could not optimize IN terms. (See check-in [fad88e71cf195e].) But IN terms are now used by virtual tables (as of check-in [3d65c70343]) so the restriction can now be removed. check-in: a917c1f0 user: drh tags: IN-with-ORDERBY
2013-02-07
21:15
Allow an index to be used for sorting even if prior terms of the index are constrained by IN operators. check-in: 98bf668a user: drh tags: IN-with-ORDERBY
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

   136    136   ** There are separate WhereClause objects for the whole clause and for
   137    137   ** the subclauses "(b AND c)" and "(d AND e)".  The pOuter field of the
   138    138   ** subclauses points to the WhereClause object for the whole clause.
   139    139   */
   140    140   struct WhereClause {
   141    141     Parse *pParse;           /* The parser context */
   142    142     WhereMaskSet *pMaskSet;  /* Mapping of table cursor numbers to bitmasks */
   143         -  Bitmask vmask;           /* Bitmask identifying virtual table cursors */
   144    143     WhereClause *pOuter;     /* Outer conjunction */
   145    144     u8 op;                   /* Split operator.  TK_AND or TK_OR */
   146    145     u16 wctrlFlags;          /* Might include WHERE_AND_ONLY */
   147    146     int nTerm;               /* Number of terms */
   148    147     int nSlot;               /* Number of entries in a[] */
   149    148     WhereTerm *a;            /* Each a[] describes a term of the WHERE cluase */
   150    149   #if defined(SQLITE_SMALL_STACK)
................................................................................
   313    312   ){
   314    313     pWC->pParse = pParse;
   315    314     pWC->pMaskSet = pMaskSet;
   316    315     pWC->pOuter = 0;
   317    316     pWC->nTerm = 0;
   318    317     pWC->nSlot = ArraySize(pWC->aStatic);
   319    318     pWC->a = pWC->aStatic;
   320         -  pWC->vmask = 0;
   321    319     pWC->wctrlFlags = wctrlFlags;
   322    320   }
   323    321   
   324    322   /* Forward reference */
   325    323   static void whereClauseClear(WhereClause*);
   326    324   
   327    325   /*
................................................................................
   913    911   **     (B)     x=expr1 OR expr2=x OR x=expr3
   914    912   **     (C)     t1.x=t2.y OR (t1.x=t2.z AND t1.y=15)
   915    913   **     (D)     x=expr1 OR (y>11 AND y<22 AND z LIKE '*hello*')
   916    914   **     (E)     (p.a=1 AND q.b=2 AND r.c=3) OR (p.x=4 AND q.y=5 AND r.z=6)
   917    915   **
   918    916   ** CASE 1:
   919    917   **
   920         -** If all subterms are of the form T.C=expr for some single column of C
          918  +** If all subterms are of the form T.C=expr for some single column of C and
   921    919   ** a single table T (as shown in example B above) then create a new virtual
   922    920   ** term that is an equivalent IN expression.  In other words, if the term
   923    921   ** being analyzed is:
   924    922   **
   925    923   **      x = expr1  OR  expr2 = x  OR  x = expr3
   926    924   **
   927    925   ** then create a new virtual term like this:
................................................................................
  1001    999     if( db->mallocFailed ) return;
  1002   1000     assert( pOrWc->nTerm>=2 );
  1003   1001   
  1004   1002     /*
  1005   1003     ** Compute the set of tables that might satisfy cases 1 or 2.
  1006   1004     */
  1007   1005     indexable = ~(Bitmask)0;
  1008         -  chngToIN = ~(pWC->vmask);
         1006  +  chngToIN = ~(Bitmask)0;
  1009   1007     for(i=pOrWc->nTerm-1, pOrTerm=pOrWc->a; i>=0 && indexable; i--, pOrTerm++){
  1010   1008       if( (pOrTerm->eOperator & WO_SINGLE)==0 ){
  1011   1009         WhereAndInfo *pAndInfo;
  1012   1010         assert( (pOrTerm->wtFlags & (TERM_ANDINFO|TERM_ORINFO))==0 );
  1013   1011         chngToIN = 0;
  1014   1012         pAndInfo = sqlite3DbMallocRaw(db, sizeof(*pAndInfo));
  1015   1013         if( pAndInfo ){
................................................................................
  5058   5056     ** the bitmask for all FROM clause terms to the left of the N-th term
  5059   5057     ** is (X-1).   An expression from the ON clause of a LEFT JOIN can use
  5060   5058     ** its Expr.iRightJoinTable value to find the bitmask of the right table
  5061   5059     ** of the join.  Subtracting one from the right table bitmask gives a
  5062   5060     ** bitmask for all tables to the left of the join.  Knowing the bitmask
  5063   5061     ** for all tables to the left of a left join is important.  Ticket #3015.
  5064   5062     **
  5065         -  ** Configure the WhereClause.vmask variable so that bits that correspond
  5066         -  ** to virtual table cursors are set. This is used to selectively disable 
  5067         -  ** the OR-to-IN transformation in exprAnalyzeOrTerm(). It is not helpful 
  5068         -  ** with virtual tables.
  5069         -  **
  5070   5063     ** Note that bitmasks are created for all pTabList->nSrc tables in
  5071   5064     ** pTabList, not just the first nTabList tables.  nTabList is normally
  5072   5065     ** equal to pTabList->nSrc but might be shortened to 1 if the
  5073   5066     ** WHERE_ONETABLE_ONLY flag is set.
  5074   5067     */
  5075         -  assert( sWBI.pWC->vmask==0 && pMaskSet->n==0 );
  5076   5068     for(ii=0; ii<pTabList->nSrc; ii++){
  5077   5069       createMask(pMaskSet, pTabList->a[ii].iCursor);
  5078         -#ifndef SQLITE_OMIT_VIRTUALTABLE
  5079         -    if( ALWAYS(pTabList->a[ii].pTab) && IsVirtual(pTabList->a[ii].pTab) ){
  5080         -      sWBI.pWC->vmask |= ((Bitmask)1 << ii);
  5081         -    }
  5082         -#endif
  5083   5070     }
  5084   5071   #ifndef NDEBUG
  5085   5072     {
  5086   5073       Bitmask toTheLeft = 0;
  5087   5074       for(ii=0; ii<pTabList->nSrc; ii++){
  5088   5075         Bitmask m = getMask(pMaskSet, pTabList->a[ii].iCursor);
  5089   5076         assert( (m-1)==toTheLeft );