/ Check-in [156a41f3]
Login

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

Overview
Comment:Fix some cases involving row values and virtual tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rowvalue
Files: files | file ages | folders
SHA1: 156a41f30a0afd9a70e6c26470dcc468a11bd402
User & Date: dan 2016-08-08 20:15:41
Context
2016-08-09
05:48
Add rowvalue5.test, which should have been part of the previous commit on this branch. check-in: ea03e219 user: dan tags: rowvalue
2016-08-08
20:15
Fix some cases involving row values and virtual tables. check-in: 156a41f3 user: dan tags: rowvalue
18:42
Fix the EXPLAIN QUERY PLAN output for row value range constaints that use an index. check-in: bb606511 user: dan tags: rowvalue
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

   822    822   ** by passing the pointer returned by this function to sqlite3_free().
   823    823   */
   824    824   static sqlite3_index_info *allocateIndexInfo(
   825    825     Parse *pParse,
   826    826     WhereClause *pWC,
   827    827     Bitmask mUnusable,              /* Ignore terms with these prereqs */
   828    828     struct SrcList_item *pSrc,
   829         -  ExprList *pOrderBy
          829  +  ExprList *pOrderBy,
          830  +  u16 *pmNoOmit                   /* Mask of terms not to omit */
   830    831   ){
   831    832     int i, j;
   832    833     int nTerm;
   833    834     struct sqlite3_index_constraint *pIdxCons;
   834    835     struct sqlite3_index_orderby *pIdxOrderBy;
   835    836     struct sqlite3_index_constraint_usage *pUsage;
   836    837     WhereTerm *pTerm;
   837    838     int nOrderBy;
   838    839     sqlite3_index_info *pIdxInfo;
          840  +  u16 mNoOmit = 0;
   839    841   
   840    842     /* Count the number of possible WHERE clause constraints referring
   841    843     ** to this virtual table */
   842    844     for(i=nTerm=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
   843    845       if( pTerm->leftCursor != pSrc->iCursor ) continue;
   844    846       if( pTerm->prereqRight & mUnusable ) continue;
   845    847       assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
................................................................................
   920    922       assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ );
   921    923       assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT );
   922    924       assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE );
   923    925       assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT );
   924    926       assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE );
   925    927       assert( WO_MATCH==SQLITE_INDEX_CONSTRAINT_MATCH );
   926    928       assert( pTerm->eOperator & (WO_IN|WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_MATCH) );
          929  +
          930  +    if( op & (WO_LT|WO_LE|WO_GT|WO_GE)
          931  +     && sqlite3ExprIsVector(pTerm->pExpr->pRight) 
          932  +    ){
          933  +      if( i<16 ) mNoOmit |= (1 << i);
          934  +      if( op==WO_LT ) pIdxCons[j].op = WO_LE;
          935  +      if( op==WO_GT ) pIdxCons[j].op = WO_GE;
          936  +    }
          937  +
   927    938       j++;
   928    939     }
   929    940     for(i=0; i<nOrderBy; i++){
   930    941       Expr *pExpr = pOrderBy->a[i].pExpr;
   931    942       pIdxOrderBy[i].iColumn = pExpr->iColumn;
   932    943       pIdxOrderBy[i].desc = pOrderBy->a[i].sortOrder;
   933    944     }
   934    945   
          946  +  *pmNoOmit = mNoOmit;
   935    947     return pIdxInfo;
   936    948   }
   937    949   
   938    950   /*
   939    951   ** The table object reference passed as the second argument to this function
   940    952   ** must represent a virtual table. This function invokes the xBestIndex()
   941    953   ** method of the virtual table with the sqlite3_index_info object that
................................................................................
  2939   2951   */
  2940   2952   static int whereLoopAddVirtualOne(
  2941   2953     WhereLoopBuilder *pBuilder,
  2942   2954     Bitmask mPrereq,                /* Mask of tables that must be used. */
  2943   2955     Bitmask mUsable,                /* Mask of usable tables */
  2944   2956     u16 mExclude,                   /* Exclude terms using these operators */
  2945   2957     sqlite3_index_info *pIdxInfo,   /* Populated object for xBestIndex */
         2958  +  u16 mNoOmit,                    /* Do not omit these constraints */
  2946   2959     int *pbIn                       /* OUT: True if plan uses an IN(...) op */
  2947   2960   ){
  2948   2961     WhereClause *pWC = pBuilder->pWC;
  2949   2962     struct sqlite3_index_constraint *pIdxCons;
  2950   2963     struct sqlite3_index_constraint_usage *pUsage = pIdxInfo->aConstraintUsage;
  2951   2964     int i;
  2952   2965     int mxTerm;
................................................................................
  3027   3040           ** together.  */
  3028   3041           pIdxInfo->orderByConsumed = 0;
  3029   3042           pIdxInfo->idxFlags &= ~SQLITE_INDEX_SCAN_UNIQUE;
  3030   3043           *pbIn = 1; assert( (mExclude & WO_IN)==0 );
  3031   3044         }
  3032   3045       }
  3033   3046     }
         3047  +  pNew->u.vtab.omitMask &= ~mNoOmit;
  3034   3048   
  3035   3049     pNew->nLTerm = mxTerm+1;
  3036   3050     assert( pNew->nLTerm<=pNew->nLSlot );
  3037   3051     pNew->u.vtab.idxNum = pIdxInfo->idxNum;
  3038   3052     pNew->u.vtab.needFree = pIdxInfo->needToFreeIdxStr;
  3039   3053     pIdxInfo->needToFreeIdxStr = 0;
  3040   3054     pNew->u.vtab.idxStr = pIdxInfo->idxStr;
................................................................................
  3100   3114     WhereClause *pWC;            /* The WHERE clause */
  3101   3115     struct SrcList_item *pSrc;   /* The FROM clause term to search */
  3102   3116     sqlite3_index_info *p;       /* Object to pass to xBestIndex() */
  3103   3117     int nConstraint;             /* Number of constraints in p */
  3104   3118     int bIn;                     /* True if plan uses IN(...) operator */
  3105   3119     WhereLoop *pNew;
  3106   3120     Bitmask mBest;               /* Tables used by best possible plan */
         3121  +  u16 mNoOmit;
  3107   3122   
  3108   3123     assert( (mPrereq & mUnusable)==0 );
  3109   3124     pWInfo = pBuilder->pWInfo;
  3110   3125     pParse = pWInfo->pParse;
  3111   3126     pWC = pBuilder->pWC;
  3112   3127     pNew = pBuilder->pNew;
  3113   3128     pSrc = &pWInfo->pTabList->a[pNew->iTab];
  3114   3129     assert( IsVirtual(pSrc->pTab) );
  3115         -  p = allocateIndexInfo(pParse, pWC, mUnusable, pSrc, pBuilder->pOrderBy);
         3130  +  p = allocateIndexInfo(pParse, pWC, mUnusable, pSrc, pBuilder->pOrderBy, 
         3131  +      &mNoOmit);
  3116   3132     if( p==0 ) return SQLITE_NOMEM_BKPT;
  3117   3133     pNew->rSetup = 0;
  3118   3134     pNew->wsFlags = WHERE_VIRTUALTABLE;
  3119   3135     pNew->nLTerm = 0;
  3120   3136     pNew->u.vtab.needFree = 0;
  3121   3137     nConstraint = p->nConstraint;
  3122   3138     if( whereLoopResize(pParse->db, pNew, nConstraint) ){
  3123   3139       sqlite3DbFree(pParse->db, p);
  3124   3140       return SQLITE_NOMEM_BKPT;
  3125   3141     }
  3126   3142   
  3127   3143     /* First call xBestIndex() with all constraints usable. */
  3128   3144     WHERETRACE(0x40, ("  VirtualOne: all usable\n"));
  3129         -  rc = whereLoopAddVirtualOne(pBuilder, mPrereq, ALLBITS, 0, p, &bIn);
         3145  +  rc = whereLoopAddVirtualOne(pBuilder, mPrereq, ALLBITS, 0, p, mNoOmit, &bIn);
  3130   3146   
  3131   3147     /* If the call to xBestIndex() with all terms enabled produced a plan
  3132   3148     ** that does not require any source tables (IOW: a plan with mBest==0),
  3133   3149     ** then there is no point in making any further calls to xBestIndex() 
  3134   3150     ** since they will all return the same result (if the xBestIndex()
  3135   3151     ** implementation is sane). */
  3136   3152     if( rc==SQLITE_OK && (mBest = (pNew->prereq & ~mPrereq))!=0 ){
................................................................................
  3139   3155       Bitmask mPrev = 0;
  3140   3156       Bitmask mBestNoIn = 0;
  3141   3157   
  3142   3158       /* If the plan produced by the earlier call uses an IN(...) term, call
  3143   3159       ** xBestIndex again, this time with IN(...) terms disabled. */
  3144   3160       if( bIn ){
  3145   3161         WHERETRACE(0x40, ("  VirtualOne: all usable w/o IN\n"));
  3146         -      rc = whereLoopAddVirtualOne(pBuilder, mPrereq, ALLBITS, WO_IN, p, &bIn);
         3162  +      rc = whereLoopAddVirtualOne(
         3163  +          pBuilder, mPrereq, ALLBITS, WO_IN, p, mNoOmit, &bIn);
  3147   3164         assert( bIn==0 );
  3148   3165         mBestNoIn = pNew->prereq & ~mPrereq;
  3149   3166         if( mBestNoIn==0 ){
  3150   3167           seenZero = 1;
  3151   3168           seenZeroNoIN = 1;
  3152   3169         }
  3153   3170       }
................................................................................
  3165   3182           if( mThis>mPrev && mThis<mNext ) mNext = mThis;
  3166   3183         }
  3167   3184         mPrev = mNext;
  3168   3185         if( mNext==ALLBITS ) break;
  3169   3186         if( mNext==mBest || mNext==mBestNoIn ) continue;
  3170   3187         WHERETRACE(0x40, ("  VirtualOne: mPrev=%04llx mNext=%04llx\n",
  3171   3188                          (sqlite3_uint64)mPrev, (sqlite3_uint64)mNext));
  3172         -      rc = whereLoopAddVirtualOne(pBuilder, mPrereq, mNext|mPrereq, 0, p, &bIn);
         3189  +      rc = whereLoopAddVirtualOne(
         3190  +          pBuilder, mPrereq, mNext|mPrereq, 0, p, mNoOmit, &bIn);
  3173   3191         if( pNew->prereq==mPrereq ){
  3174   3192           seenZero = 1;
  3175   3193           if( bIn==0 ) seenZeroNoIN = 1;
  3176   3194         }
  3177   3195       }
  3178   3196   
  3179   3197       /* If the calls to xBestIndex() in the above loop did not find a plan
  3180   3198       ** that requires no source tables at all (i.e. one guaranteed to be
  3181   3199       ** usable), make a call here with all source tables disabled */
  3182   3200       if( rc==SQLITE_OK && seenZero==0 ){
  3183   3201         WHERETRACE(0x40, ("  VirtualOne: all disabled\n"));
  3184         -      rc = whereLoopAddVirtualOne(pBuilder, mPrereq, mPrereq, 0, p, &bIn);
         3202  +      rc = whereLoopAddVirtualOne(
         3203  +          pBuilder, mPrereq, mPrereq, 0, p, mNoOmit, &bIn);
  3185   3204         if( bIn==0 ) seenZeroNoIN = 1;
  3186   3205       }
  3187   3206   
  3188   3207       /* If the calls to xBestIndex() have so far failed to find a plan
  3189   3208       ** that requires no source tables at all and does not use an IN(...)
  3190   3209       ** operator, make a final call to obtain one here.  */
  3191   3210       if( rc==SQLITE_OK && seenZeroNoIN==0 ){
  3192   3211         WHERETRACE(0x40, ("  VirtualOne: all disabled and w/o IN\n"));
  3193         -      rc = whereLoopAddVirtualOne(pBuilder, mPrereq, mPrereq, WO_IN, p, &bIn);
         3212  +      rc = whereLoopAddVirtualOne(
         3213  +          pBuilder, mPrereq, mPrereq, WO_IN, p, mNoOmit, &bIn);
  3194   3214       }
  3195   3215     }
  3196   3216   
  3197   3217     if( p->needToFreeIdxStr ) sqlite3_free(p->idxStr);
  3198   3218     sqlite3DbFree(pParse->db, p);
  3199   3219     return rc;
  3200   3220   }

Changes to src/wherecode.c.

  1095   1095         int iTarget = iReg+j+2;
  1096   1096         pTerm = pLoop->aLTerm[j];
  1097   1097         if( NEVER(pTerm==0) ) continue;
  1098   1098         if( pTerm->eOperator & WO_IN ){
  1099   1099           codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, iTarget);
  1100   1100           addrNotFound = pLevel->addrNxt;
  1101   1101         }else{
  1102         -        sqlite3ExprCode(pParse, pTerm->pExpr->pRight, iTarget);
         1102  +        Expr *pRight = pTerm->pExpr->pRight;
         1103  +        if( pRight->op==TK_SELECT_COLUMN ){
         1104  +          codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, iTarget);
         1105  +        }else{
         1106  +          codeExprOrVector(pParse, pRight, iTarget, 1);
         1107  +        }
  1103   1108         }
  1104   1109       }
  1105   1110       sqlite3VdbeAddOp2(v, OP_Integer, pLoop->u.vtab.idxNum, iReg);
  1106   1111       sqlite3VdbeAddOp2(v, OP_Integer, nConstraint, iReg+1);
  1107   1112       sqlite3VdbeAddOp4(v, OP_VFilter, iCur, addrNotFound, iReg,
  1108   1113                         pLoop->u.vtab.idxStr,
  1109   1114                         pLoop->u.vtab.needFree ? P4_MPRINTF : P4_STATIC);