/ Check-in [2cef8b68]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Allow an index to be used for sorting even if prior terms of the index are constrained by IN operators.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2cef8b68f0e1216cf68bb7dd45a5a9a330748070
User & Date: drh 2013-02-13 01:00:35
References
2013-03-12
17:34 New ticket [4dd95f69] Inverted result order with ORDER BY DESC and a DESC index.. artifact: 8f409867 user: drh
Context
2013-02-13
14:04
Add recent API additions to the extension mechanism. check-in: 7e10a62d user: drh tags: trunk
13:42
Enhancements to the query planner to make use of indices for ORDER BY even when IN constraints are in the WHERE clause. Add extended error codes for all SQLITE_CONSTRAINT errors. check-in: 7e14dc73 user: drh tags: sessions
01:00
Allow an index to be used for sorting even if prior terms of the index are constrained by IN operators. check-in: 2cef8b68 user: drh tags: trunk
2013-02-12
22:20
Improve memory allocation error handling on WinCE. check-in: cdbca259 user: drh tags: trunk
2013-02-08
23:18
Fix a potential NULL-pointer dereference following an OOM error in the query planner logic for virtual tables with OR-connected terms. Closed-Leaf check-in: 71b6c260 user: drh tags: IN-with-ORDERBY
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/sqliteInt.h.

1961
1962
1963
1964
1965
1966
1967

1968
1969
1970
1971
1972
1973
1974
  int p1, p2;           /* Operands of the opcode used to ends the loop */
  union {               /* Information that depends on plan.wsFlags */
    struct {
      int nIn;              /* Number of entries in aInLoop[] */
      struct InLoop {
        int iCur;              /* The VDBE cursor used by this IN operator */
        int addrInTop;         /* Top of the IN loop */

      } *aInLoop;           /* Information about each nested IN operator */
    } in;                 /* Used when plan.wsFlags&WHERE_IN_ABLE */
    Index *pCovidx;       /* Possible covering index for WHERE_MULTI_OR */
  } u;
  double rOptCost;      /* "Optimal" cost for this level */

  /* The following field is really not part of the current level.  But







>







1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
  int p1, p2;           /* Operands of the opcode used to ends the loop */
  union {               /* Information that depends on plan.wsFlags */
    struct {
      int nIn;              /* Number of entries in aInLoop[] */
      struct InLoop {
        int iCur;              /* The VDBE cursor used by this IN operator */
        int addrInTop;         /* Top of the IN loop */
        u8 eEndLoopOp;         /* IN Loop terminator. OP_Next or OP_Prev */
      } *aInLoop;           /* Information about each nested IN operator */
    } in;                 /* Used when plan.wsFlags&WHERE_IN_ABLE */
    Index *pCovidx;       /* Possible covering index for WHERE_MULTI_OR */
  } u;
  double rOptCost;      /* "Optimal" cost for this level */

  /* The following field is really not part of the current level.  But

Changes to src/where.c.

136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
...
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
...
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
....
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
....
2268
2269
2270
2271
2272
2273
2274
2275
2276

2277
2278
2279
2280
2281
2282
2283
....
2368
2369
2370
2371
2372
2373
2374

2375
2376
2377
2378
2379
2380
2381

2382
2383
2384
2385
2386







2387
2388
2389
2390
2391
2392
2393
....
2409
2410
2411
2412
2413
2414
2415

2416
2417
2418
2419
2420
2421
2422
2423
....
3006
3007
3008
3009
3010
3011
3012
3013
3014
3015
3016
3017
3018
3019
3020
3021
3022
3023
....
3313
3314
3315
3316
3317
3318
3319
3320
3321
3322
3323
3324
3325
3326
3327
3328
....
3655
3656
3657
3658
3659
3660
3661

3662
3663
3664
3665
3666
3667
3668
3669
....
3779
3780
3781
3782
3783
3784
3785

3786
3787
3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
....
3802
3803
3804
3805
3806
3807
3808

3809
3810
3811
3812
3813
3814
3815
....
5053
5054
5055
5056
5057
5058
5059
5060
5061
5062
5063
5064
5065
5066
5067
5068
5069
5070
5071
5072
5073
5074
5075
5076
5077
5078
5079
5080
5081
5082
5083
5084
....
5554
5555
5556
5557
5558
5559
5560
5561
5562
5563
5564
5565
5566
5567
5568
** There are separate WhereClause objects for the whole clause and for
** the subclauses "(b AND c)" and "(d AND e)".  The pOuter field of the
** subclauses points to the WhereClause object for the whole clause.
*/
struct WhereClause {
  Parse *pParse;           /* The parser context */
  WhereMaskSet *pMaskSet;  /* Mapping of table cursor numbers to bitmasks */
  Bitmask vmask;           /* Bitmask identifying virtual table cursors */
  WhereClause *pOuter;     /* Outer conjunction */
  u8 op;                   /* Split operator.  TK_AND or TK_OR */
  u16 wctrlFlags;          /* Might include WHERE_AND_ONLY */
  int nTerm;               /* Number of terms */
  int nSlot;               /* Number of entries in a[] */
  WhereTerm *a;            /* Each a[] describes a term of the WHERE cluase */
#if defined(SQLITE_SMALL_STACK)
................................................................................
){
  pWC->pParse = pParse;
  pWC->pMaskSet = pMaskSet;
  pWC->pOuter = 0;
  pWC->nTerm = 0;
  pWC->nSlot = ArraySize(pWC->aStatic);
  pWC->a = pWC->aStatic;
  pWC->vmask = 0;
  pWC->wctrlFlags = wctrlFlags;
}

/* Forward reference */
static void whereClauseClear(WhereClause*);

/*
................................................................................
**     (B)     x=expr1 OR expr2=x OR x=expr3
**     (C)     t1.x=t2.y OR (t1.x=t2.z AND t1.y=15)
**     (D)     x=expr1 OR (y>11 AND y<22 AND z LIKE '*hello*')
**     (E)     (p.a=1 AND q.b=2 AND r.c=3) OR (p.x=4 AND q.y=5 AND r.z=6)
**
** CASE 1:
**
** If all subterms are of the form T.C=expr for some single column of C
** a single table T (as shown in example B above) then create a new virtual
** term that is an equivalent IN expression.  In other words, if the term
** being analyzed is:
**
**      x = expr1  OR  expr2 = x  OR  x = expr3
**
** then create a new virtual term like this:
................................................................................
  if( db->mallocFailed ) return;
  assert( pOrWc->nTerm>=2 );

  /*
  ** Compute the set of tables that might satisfy cases 1 or 2.
  */
  indexable = ~(Bitmask)0;
  chngToIN = ~(pWC->vmask);
  for(i=pOrWc->nTerm-1, pOrTerm=pOrWc->a; i>=0 && indexable; i--, pOrTerm++){
    if( (pOrTerm->eOperator & WO_SINGLE)==0 ){
      WhereAndInfo *pAndInfo;
      assert( (pOrTerm->wtFlags & (TERM_ANDINFO|TERM_ORINFO))==0 );
      chngToIN = 0;
      pAndInfo = sqlite3DbMallocRaw(db, sizeof(*pAndInfo));
      if( pAndInfo ){
................................................................................
  WhereClause *pWC = p->pWC;      /* The WHERE clause */
  struct SrcList_item *pSrc = p->pSrc; /* The FROM clause term to search */
  Table *pTab = pSrc->pTab;
  sqlite3_index_info *pIdxInfo;
  struct sqlite3_index_constraint *pIdxCons;
  struct sqlite3_index_constraint_usage *pUsage;
  WhereTerm *pTerm;
  int i, j;
  int nOrderBy;

  int bAllowIN;                   /* Allow IN optimizations */
  double rCost;

  /* Make sure wsFlags is initialized to some sane value. Otherwise, if the 
  ** malloc in allocateIndexInfo() fails and this function returns leaving
  ** wsFlags in an uninitialized state, the caller may behave unpredictably.
  */
................................................................................
      pIdxInfo->nOrderBy = 0;
    }
  
    if( vtabBestIndex(pParse, pTab, pIdxInfo) ){
      return;
    }
  

    pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
    for(i=0; i<pIdxInfo->nConstraint; i++, pIdxCons++){
      if( pUsage[i].argvIndex>0 ){
        j = pIdxCons->iTermOffset;
        pTerm = &pWC->a[j];
        p->cost.used |= pTerm->prereqRight;
        if( (pTerm->eOperator & WO_IN)!=0 && pUsage[i].omit==0 ){

          /* Do not attempt to use an IN constraint if the virtual table
          ** says that the equivalent EQ constraint cannot be safely omitted.
          ** If we do attempt to use such a constraint, some rows might be
          ** repeated in the output. */
          break;







        }
      }
    }
    if( i>=pIdxInfo->nConstraint ) break;
  }
  
  /* If there is an ORDER BY clause, and the selected virtual table index
................................................................................
  if( (SQLITE_BIG_DBL/((double)2))<rCost ){
    p->cost.rCost = (SQLITE_BIG_DBL/((double)2));
  }else{
    p->cost.rCost = rCost;
  }
  p->cost.plan.u.pVtabIdx = pIdxInfo;
  if( pIdxInfo->orderByConsumed ){

    p->cost.plan.wsFlags |= WHERE_ORDERED;
    p->cost.plan.nOBSat = nOrderBy;
  }else{
    p->cost.plan.nOBSat = p->i ? p->aLevel[p->i-1].plan.nOBSat : 0;
  }
  p->cost.plan.nEq = 0;
  pIdxInfo->nOrderBy = nOrderBy;

................................................................................
    /* If X is the column in the index and ORDER BY clause, check to see
    ** if there are any X= or X IS NULL constraints in the WHERE clause. */
    pConstraint = findTerm(p->pWC, base, iColumn, p->notReady,
                           WO_EQ|WO_ISNULL|WO_IN, pIdx);
    if( pConstraint==0 ){
      isEq = 0;
    }else if( (pConstraint->eOperator & WO_IN)!=0 ){
      /* Constraints of the form: "X IN ..." cannot be used with an ORDER BY
      ** because we do not know in what order the values on the RHS of the IN
      ** operator will occur. */
      break;
    }else if( (pConstraint->eOperator & WO_ISNULL)!=0 ){
      uniqueNotNull = 0;
      isEq = 1;  /* "X IS NULL" means X has only a single value */
    }else if( pConstraint->prereqRight==0 ){
      isEq = 1;  /* Constraint "X=constant" means X has only a single value */
    }else{
      Expr *pRight = pConstraint->pExpr->pRight;
................................................................................
 
    /* If the index being considered is UNIQUE, and there is an equality 
    ** constraint for all columns in the index, then this search will find
    ** at most a single row. In this case set the WHERE_UNIQUE flag to 
    ** indicate this to the caller.
    **
    ** Otherwise, if the search may find more than one row, test to see if
    ** there is a range constraint on indexed column (pc.plan.nEq+1) that can be 
    ** optimized using the index. 
    */
    if( pc.plan.nEq==pProbe->nColumn && pProbe->onError!=OE_None ){
      testcase( pc.plan.wsFlags & WHERE_COLUMN_IN );
      testcase( pc.plan.wsFlags & WHERE_COLUMN_NULL );
      if( (pc.plan.wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_NULL))==0 ){
        pc.plan.wsFlags |= WHERE_UNIQUE;
        if( p->i==0 || (p->aLevel[p->i-1].plan.wsFlags & WHERE_ALL_UNIQUE)!=0 ){
................................................................................
*/
static void bestIndex(WhereBestIdx *p){
#ifndef SQLITE_OMIT_VIRTUALTABLE
  if( IsVirtual(p->pSrc->pTab) ){
    sqlite3_index_info *pIdxInfo = 0;
    p->ppIdxInfo = &pIdxInfo;
    bestVirtualIndex(p);

    if( pIdxInfo->needToFreeIdxStr ){
      sqlite3_free(pIdxInfo->idxStr);
    }
    sqlite3DbFree(p->pParse->db, pIdxInfo);
  }else
#endif
  {
    bestBtreeIndex(p);
................................................................................
    iReg = iTarget;
    sqlite3VdbeAddOp2(v, OP_Null, 0, iReg);
#ifndef SQLITE_OMIT_SUBQUERY
  }else{
    int eType;
    int iTab;
    struct InLoop *pIn;


    assert( pX->op==TK_IN );
    iReg = iTarget;
    eType = sqlite3FindInIndex(pParse, pX, 0);
    iTab = pX->iTable;
    sqlite3VdbeAddOp2(v, OP_Rewind, iTab, 0);
    assert( pLevel->plan.wsFlags & WHERE_IN_ABLE );
    if( pLevel->u.in.nIn==0 ){
      pLevel->addrNxt = sqlite3VdbeMakeLabel(v);
    }
    pLevel->u.in.nIn++;
    pLevel->u.in.aInLoop =
       sqlite3DbReallocOrFree(pParse->db, pLevel->u.in.aInLoop,
................................................................................
      pIn += pLevel->u.in.nIn - 1;
      pIn->iCur = iTab;
      if( eType==IN_INDEX_ROWID ){
        pIn->addrInTop = sqlite3VdbeAddOp2(v, OP_Rowid, iTab, iReg);
      }else{
        pIn->addrInTop = sqlite3VdbeAddOp3(v, OP_Column, iTab, 0, iReg);
      }

      sqlite3VdbeAddOp1(v, OP_IsNull, iReg);
    }else{
      pLevel->u.in.nIn = 0;
    }
#endif
  }
  disableTerm(pLevel, pTerm);
................................................................................
  ** the bitmask for all FROM clause terms to the left of the N-th term
  ** is (X-1).   An expression from the ON clause of a LEFT JOIN can use
  ** its Expr.iRightJoinTable value to find the bitmask of the right table
  ** of the join.  Subtracting one from the right table bitmask gives a
  ** bitmask for all tables to the left of the join.  Knowing the bitmask
  ** for all tables to the left of a left join is important.  Ticket #3015.
  **
  ** Configure the WhereClause.vmask variable so that bits that correspond
  ** to virtual table cursors are set. This is used to selectively disable 
  ** the OR-to-IN transformation in exprAnalyzeOrTerm(). It is not helpful 
  ** with virtual tables.
  **
  ** Note that bitmasks are created for all pTabList->nSrc tables in
  ** pTabList, not just the first nTabList tables.  nTabList is normally
  ** equal to pTabList->nSrc but might be shortened to 1 if the
  ** WHERE_ONETABLE_ONLY flag is set.
  */
  assert( sWBI.pWC->vmask==0 && pMaskSet->n==0 );
  for(ii=0; ii<pTabList->nSrc; ii++){
    createMask(pMaskSet, pTabList->a[ii].iCursor);
#ifndef SQLITE_OMIT_VIRTUALTABLE
    if( ALWAYS(pTabList->a[ii].pTab) && IsVirtual(pTabList->a[ii].pTab) ){
      sWBI.pWC->vmask |= ((Bitmask)1 << ii);
    }
#endif
  }
#ifndef NDEBUG
  {
    Bitmask toTheLeft = 0;
    for(ii=0; ii<pTabList->nSrc; ii++){
      Bitmask m = getMask(pMaskSet, pTabList->a[ii].iCursor);
      assert( (m-1)==toTheLeft );
................................................................................
    }
    if( pLevel->plan.wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0 ){
      struct InLoop *pIn;
      int j;
      sqlite3VdbeResolveLabel(v, pLevel->addrNxt);
      for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){
        sqlite3VdbeJumpHere(v, pIn->addrInTop+1);
        sqlite3VdbeAddOp2(v, OP_Next, pIn->iCur, pIn->addrInTop);
        sqlite3VdbeJumpHere(v, pIn->addrInTop-1);
      }
      sqlite3DbFree(db, pLevel->u.in.aInLoop);
    }
    sqlite3VdbeResolveLabel(v, pLevel->addrBrk);
    if( pLevel->iLeftJoin ){
      int addr;







<







 







<







 







|







 







|







 







|

>







 







>






|
>
|
|
|
|
|
>
>
>
>
>
>
>







 







>
|







 







|
<
<
<







 







|
|







 







>
|







 







>





|







 







>







 







<
<
<
<
<





<


<
<
<
<
<







 







|







136
137
138
139
140
141
142

143
144
145
146
147
148
149
...
312
313
314
315
316
317
318

319
320
321
322
323
324
325
...
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
...
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
....
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
....
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
....
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
....
3015
3016
3017
3018
3019
3020
3021
3022



3023
3024
3025
3026
3027
3028
3029
....
3319
3320
3321
3322
3323
3324
3325
3326
3327
3328
3329
3330
3331
3332
3333
3334
....
3661
3662
3663
3664
3665
3666
3667
3668
3669
3670
3671
3672
3673
3674
3675
3676
....
3786
3787
3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
....
3810
3811
3812
3813
3814
3815
3816
3817
3818
3819
3820
3821
3822
3823
3824
....
5062
5063
5064
5065
5066
5067
5068





5069
5070
5071
5072
5073

5074
5075





5076
5077
5078
5079
5080
5081
5082
....
5552
5553
5554
5555
5556
5557
5558
5559
5560
5561
5562
5563
5564
5565
5566
** There are separate WhereClause objects for the whole clause and for
** the subclauses "(b AND c)" and "(d AND e)".  The pOuter field of the
** subclauses points to the WhereClause object for the whole clause.
*/
struct WhereClause {
  Parse *pParse;           /* The parser context */
  WhereMaskSet *pMaskSet;  /* Mapping of table cursor numbers to bitmasks */

  WhereClause *pOuter;     /* Outer conjunction */
  u8 op;                   /* Split operator.  TK_AND or TK_OR */
  u16 wctrlFlags;          /* Might include WHERE_AND_ONLY */
  int nTerm;               /* Number of terms */
  int nSlot;               /* Number of entries in a[] */
  WhereTerm *a;            /* Each a[] describes a term of the WHERE cluase */
#if defined(SQLITE_SMALL_STACK)
................................................................................
){
  pWC->pParse = pParse;
  pWC->pMaskSet = pMaskSet;
  pWC->pOuter = 0;
  pWC->nTerm = 0;
  pWC->nSlot = ArraySize(pWC->aStatic);
  pWC->a = pWC->aStatic;

  pWC->wctrlFlags = wctrlFlags;
}

/* Forward reference */
static void whereClauseClear(WhereClause*);

/*
................................................................................
**     (B)     x=expr1 OR expr2=x OR x=expr3
**     (C)     t1.x=t2.y OR (t1.x=t2.z AND t1.y=15)
**     (D)     x=expr1 OR (y>11 AND y<22 AND z LIKE '*hello*')
**     (E)     (p.a=1 AND q.b=2 AND r.c=3) OR (p.x=4 AND q.y=5 AND r.z=6)
**
** CASE 1:
**
** If all subterms are of the form T.C=expr for some single column of C and
** a single table T (as shown in example B above) then create a new virtual
** term that is an equivalent IN expression.  In other words, if the term
** being analyzed is:
**
**      x = expr1  OR  expr2 = x  OR  x = expr3
**
** then create a new virtual term like this:
................................................................................
  if( db->mallocFailed ) return;
  assert( pOrWc->nTerm>=2 );

  /*
  ** Compute the set of tables that might satisfy cases 1 or 2.
  */
  indexable = ~(Bitmask)0;
  chngToIN = ~(Bitmask)0;
  for(i=pOrWc->nTerm-1, pOrTerm=pOrWc->a; i>=0 && indexable; i--, pOrTerm++){
    if( (pOrTerm->eOperator & WO_SINGLE)==0 ){
      WhereAndInfo *pAndInfo;
      assert( (pOrTerm->wtFlags & (TERM_ANDINFO|TERM_ORINFO))==0 );
      chngToIN = 0;
      pAndInfo = sqlite3DbMallocRaw(db, sizeof(*pAndInfo));
      if( pAndInfo ){
................................................................................
  WhereClause *pWC = p->pWC;      /* The WHERE clause */
  struct SrcList_item *pSrc = p->pSrc; /* The FROM clause term to search */
  Table *pTab = pSrc->pTab;
  sqlite3_index_info *pIdxInfo;
  struct sqlite3_index_constraint *pIdxCons;
  struct sqlite3_index_constraint_usage *pUsage;
  WhereTerm *pTerm;
  int i, j, k;
  int nOrderBy;
  int sortOrder;                  /* Sort order for IN clauses */
  int bAllowIN;                   /* Allow IN optimizations */
  double rCost;

  /* Make sure wsFlags is initialized to some sane value. Otherwise, if the 
  ** malloc in allocateIndexInfo() fails and this function returns leaving
  ** wsFlags in an uninitialized state, the caller may behave unpredictably.
  */
................................................................................
      pIdxInfo->nOrderBy = 0;
    }
  
    if( vtabBestIndex(pParse, pTab, pIdxInfo) ){
      return;
    }
  
    sortOrder = SQLITE_SO_ASC;
    pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
    for(i=0; i<pIdxInfo->nConstraint; i++, pIdxCons++){
      if( pUsage[i].argvIndex>0 ){
        j = pIdxCons->iTermOffset;
        pTerm = &pWC->a[j];
        p->cost.used |= pTerm->prereqRight;
        if( (pTerm->eOperator & WO_IN)!=0 ){
          if( pUsage[i].omit==0 ){
            /* Do not attempt to use an IN constraint if the virtual table
            ** says that the equivalent EQ constraint cannot be safely omitted.
            ** If we do attempt to use such a constraint, some rows might be
            ** repeated in the output. */
            break;
          }
          for(k=0; k<pIdxInfo->nOrderBy; k++){
            if( pIdxInfo->aOrderBy[k].iColumn==pIdxCons->iColumn ){
              sortOrder = pIdxInfo->aOrderBy[k].desc;
              break;
            }
          }
        }
      }
    }
    if( i>=pIdxInfo->nConstraint ) break;
  }
  
  /* If there is an ORDER BY clause, and the selected virtual table index
................................................................................
  if( (SQLITE_BIG_DBL/((double)2))<rCost ){
    p->cost.rCost = (SQLITE_BIG_DBL/((double)2));
  }else{
    p->cost.rCost = rCost;
  }
  p->cost.plan.u.pVtabIdx = pIdxInfo;
  if( pIdxInfo->orderByConsumed ){
    assert( sortOrder==0 || sortOrder==1 );
    p->cost.plan.wsFlags |= WHERE_ORDERED + sortOrder*WHERE_REVERSE;
    p->cost.plan.nOBSat = nOrderBy;
  }else{
    p->cost.plan.nOBSat = p->i ? p->aLevel[p->i-1].plan.nOBSat : 0;
  }
  p->cost.plan.nEq = 0;
  pIdxInfo->nOrderBy = nOrderBy;

................................................................................
    /* If X is the column in the index and ORDER BY clause, check to see
    ** if there are any X= or X IS NULL constraints in the WHERE clause. */
    pConstraint = findTerm(p->pWC, base, iColumn, p->notReady,
                           WO_EQ|WO_ISNULL|WO_IN, pIdx);
    if( pConstraint==0 ){
      isEq = 0;
    }else if( (pConstraint->eOperator & WO_IN)!=0 ){
      isEq = 0;



    }else if( (pConstraint->eOperator & WO_ISNULL)!=0 ){
      uniqueNotNull = 0;
      isEq = 1;  /* "X IS NULL" means X has only a single value */
    }else if( pConstraint->prereqRight==0 ){
      isEq = 1;  /* Constraint "X=constant" means X has only a single value */
    }else{
      Expr *pRight = pConstraint->pExpr->pRight;
................................................................................
 
    /* If the index being considered is UNIQUE, and there is an equality 
    ** constraint for all columns in the index, then this search will find
    ** at most a single row. In this case set the WHERE_UNIQUE flag to 
    ** indicate this to the caller.
    **
    ** Otherwise, if the search may find more than one row, test to see if
    ** there is a range constraint on indexed column (pc.plan.nEq+1) that
    ** can be optimized using the index. 
    */
    if( pc.plan.nEq==pProbe->nColumn && pProbe->onError!=OE_None ){
      testcase( pc.plan.wsFlags & WHERE_COLUMN_IN );
      testcase( pc.plan.wsFlags & WHERE_COLUMN_NULL );
      if( (pc.plan.wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_NULL))==0 ){
        pc.plan.wsFlags |= WHERE_UNIQUE;
        if( p->i==0 || (p->aLevel[p->i-1].plan.wsFlags & WHERE_ALL_UNIQUE)!=0 ){
................................................................................
*/
static void bestIndex(WhereBestIdx *p){
#ifndef SQLITE_OMIT_VIRTUALTABLE
  if( IsVirtual(p->pSrc->pTab) ){
    sqlite3_index_info *pIdxInfo = 0;
    p->ppIdxInfo = &pIdxInfo;
    bestVirtualIndex(p);
    assert( pIdxInfo!=0 || p->pParse->db->mallocFailed );
    if( pIdxInfo && pIdxInfo->needToFreeIdxStr ){
      sqlite3_free(pIdxInfo->idxStr);
    }
    sqlite3DbFree(p->pParse->db, pIdxInfo);
  }else
#endif
  {
    bestBtreeIndex(p);
................................................................................
    iReg = iTarget;
    sqlite3VdbeAddOp2(v, OP_Null, 0, iReg);
#ifndef SQLITE_OMIT_SUBQUERY
  }else{
    int eType;
    int iTab;
    struct InLoop *pIn;
    u8 bRev = (pLevel->plan.wsFlags & WHERE_REVERSE)!=0;

    assert( pX->op==TK_IN );
    iReg = iTarget;
    eType = sqlite3FindInIndex(pParse, pX, 0);
    iTab = pX->iTable;
    sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
    assert( pLevel->plan.wsFlags & WHERE_IN_ABLE );
    if( pLevel->u.in.nIn==0 ){
      pLevel->addrNxt = sqlite3VdbeMakeLabel(v);
    }
    pLevel->u.in.nIn++;
    pLevel->u.in.aInLoop =
       sqlite3DbReallocOrFree(pParse->db, pLevel->u.in.aInLoop,
................................................................................
      pIn += pLevel->u.in.nIn - 1;
      pIn->iCur = iTab;
      if( eType==IN_INDEX_ROWID ){
        pIn->addrInTop = sqlite3VdbeAddOp2(v, OP_Rowid, iTab, iReg);
      }else{
        pIn->addrInTop = sqlite3VdbeAddOp3(v, OP_Column, iTab, 0, iReg);
      }
      pIn->eEndLoopOp = bRev ? OP_Prev : OP_Next;
      sqlite3VdbeAddOp1(v, OP_IsNull, iReg);
    }else{
      pLevel->u.in.nIn = 0;
    }
#endif
  }
  disableTerm(pLevel, pTerm);
................................................................................
  ** the bitmask for all FROM clause terms to the left of the N-th term
  ** is (X-1).   An expression from the ON clause of a LEFT JOIN can use
  ** its Expr.iRightJoinTable value to find the bitmask of the right table
  ** of the join.  Subtracting one from the right table bitmask gives a
  ** bitmask for all tables to the left of the join.  Knowing the bitmask
  ** for all tables to the left of a left join is important.  Ticket #3015.
  **





  ** Note that bitmasks are created for all pTabList->nSrc tables in
  ** pTabList, not just the first nTabList tables.  nTabList is normally
  ** equal to pTabList->nSrc but might be shortened to 1 if the
  ** WHERE_ONETABLE_ONLY flag is set.
  */

  for(ii=0; ii<pTabList->nSrc; ii++){
    createMask(pMaskSet, pTabList->a[ii].iCursor);





  }
#ifndef NDEBUG
  {
    Bitmask toTheLeft = 0;
    for(ii=0; ii<pTabList->nSrc; ii++){
      Bitmask m = getMask(pMaskSet, pTabList->a[ii].iCursor);
      assert( (m-1)==toTheLeft );
................................................................................
    }
    if( pLevel->plan.wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0 ){
      struct InLoop *pIn;
      int j;
      sqlite3VdbeResolveLabel(v, pLevel->addrNxt);
      for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){
        sqlite3VdbeJumpHere(v, pIn->addrInTop+1);
        sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop);
        sqlite3VdbeJumpHere(v, pIn->addrInTop-1);
      }
      sqlite3DbFree(db, pLevel->u.in.aInLoop);
    }
    sqlite3VdbeResolveLabel(v, pLevel->addrBrk);
    if( pLevel->iLeftJoin ){
      int addr;

Changes to test/where.test.

375
376
377
378
379
380
381
382
383
384
385
386















387
388
389
390
391
392
393
...
448
449
450
451
452
453
454
























455
456
457
458
459
460
461
...
507
508
509
510
511
512
513
514
515
516
517
518





519
520
521
522
523
524
525
    }
  } {1 0 4 2 1 9 3 1 16 4}
  do_test where-5.2 {
    count {
      SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 102}
  do_test where-5.3 {
    count {
      SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 14}















  do_test where-5.4 {
    count {
      SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 102}
  do_test where-5.5 {
    count {
................................................................................
    }
  } {2 1 9 8}
  do_test where-5.15 {
    count {
      SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1;
    }
  } {2 1 9 3 1 16 11}
























}

# This procedure executes the SQL.  Then it checks to see if the OP_Sort
# opcode was executed.  If an OP_Sort did occur, then "sort" is appended
# to the result.  If no OP_Sort happened, then "nosort" is appended.
#
# This procedure is used to check to make sure sorting is or is not
................................................................................
} {1 100 4 2 99 9 3 98 16 nosort}
do_test where-6.7 {
  cksort {
    SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 nosort}
ifcapable subquery {
  do_test where-6.8 {
    cksort {
      SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
    }
  } {1 100 4 2 99 9 3 98 16 sort}





}
do_test where-6.9.1 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
  }
} {1 100 4 nosort}
do_test where-6.9.1.1 {







|



|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|



|
>
>
>
>
>







375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
...
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
...
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
    }
  } {1 0 4 2 1 9 3 1 16 4}
  do_test where-5.2 {
    count {
      SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 102}
  do_test where-5.3a {
    count {
      SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 13}
  do_test where-5.3b {
    count {
      SELECT * FROM t1 WHERE w IN (3,-1,1,2) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 13}
  do_test where-5.3c {
    count {
      SELECT * FROM t1 WHERE w IN (3,2,-1,1,2) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 13}
  do_test where-5.3d {
    count {
      SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1 DESC;
    }
  } {3 1 16 2 1 9 1 0 4 12}
  do_test where-5.4 {
    count {
      SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 102}
  do_test where-5.5 {
    count {
................................................................................
    }
  } {2 1 9 8}
  do_test where-5.15 {
    count {
      SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1;
    }
  } {2 1 9 3 1 16 11}
  do_test where-5.100 {
    db eval {
      SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
       ORDER BY x, y
    }
  } {2 1 9 54 5 3025 62 5 3969}
  do_test where-5.101 {
    db eval {
      SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
       ORDER BY x DESC, y DESC
    }
  } {62 5 3969 54 5 3025 2 1 9}
  do_test where-5.102 {
    db eval {
      SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
       ORDER BY x DESC, y
    }
  } {54 5 3025 62 5 3969 2 1 9}
  do_test where-5.103 {
    db eval {
      SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
       ORDER BY x, y DESC
    }
  } {2 1 9 62 5 3969 54 5 3025}
}

# This procedure executes the SQL.  Then it checks to see if the OP_Sort
# opcode was executed.  If an OP_Sort did occur, then "sort" is appended
# to the result.  If no OP_Sort happened, then "nosort" is appended.
#
# This procedure is used to check to make sure sorting is or is not
................................................................................
} {1 100 4 2 99 9 3 98 16 nosort}
do_test where-6.7 {
  cksort {
    SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 nosort}
ifcapable subquery {
  do_test where-6.8a {
    cksort {
      SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
    }
  } {1 100 4 2 99 9 3 98 16 nosort}
  do_test where-6.8b {
    cksort {
      SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a DESC LIMIT 3
    }
  } {9 92 100 7 94 64 5 96 36 nosort}
}
do_test where-6.9.1 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
  }
} {1 100 4 nosort}
do_test where-6.9.1.1 {

Changes to test/where2.test.

163
164
165
166
167
168
169
170
























171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187






188
189
190
191
192
193
194
...
203
204
205
206
207
208
209
210
211
212
213
214





215
216
217
218
219
220
221
        SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
                         AND y IN (SELECT 10000 UNION SELECT 10201)
                         AND x>0 AND x<10
        ORDER BY w
      }
    } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
  }
  do_test where2-4.6 {
























    queryplan {
      SELECT * FROM t1
       WHERE x IN (1,2,3,4,5,6,7,8)
         AND y IN (10000,10001,10002,10003,10004,10005)
       ORDER BY 2
    }
  } {99 6 10000 10006 sort t1 i1xy}

  # Duplicate entires on the RHS of an IN operator do not cause duplicate
  # output rows.
  #
  do_test where2-4.6 {
    queryplan {
      SELECT * FROM t1 WHERE z IN (10207,10006,10006,10207)
      ORDER BY w
    }
  } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}






  ifcapable compound {
    do_test where2-4.7 {
      queryplan {
        SELECT * FROM t1 WHERE z IN (
           SELECT 10207 UNION ALL SELECT 10006
           UNION ALL SELECT 10006 UNION ALL SELECT 10207)
        ORDER BY w
................................................................................
do_test where2-5.1 {
  queryplan {
    SELECT * FROM t1 WHERE w=99 ORDER BY w
  }
} {99 6 10000 10006 nosort t1 i1w}

ifcapable subquery {
  do_test where2-5.2 {
    queryplan {
      SELECT * FROM t1 WHERE w IN (99) ORDER BY w
    }
  } {99 6 10000 10006 sort t1 i1w}





}

# Verify that OR clauses get translated into IN operators.
#
set ::idx {}
ifcapable subquery {set ::idx i1w}
do_test where2-6.1.1 {







|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




|






|





>
>
>
>
>
>







 







|



|
>
>
>
>
>







163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
...
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
        SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
                         AND y IN (SELECT 10000 UNION SELECT 10201)
                         AND x>0 AND x<10
        ORDER BY w
      }
    } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
  }
  do_test where2-4.6a {
    queryplan {
      SELECT * FROM t1
       WHERE x IN (1,2,3,4,5,6,7,8)
         AND y IN (10000,10001,10002,10003,10004,10005)
       ORDER BY x
    }
  } {99 6 10000 10006 nosort t1 i1xy}
  do_test where2-4.6b {
    queryplan {
      SELECT * FROM t1
       WHERE x IN (1,2,3,4,5,6,7,8)
         AND y IN (10000,10001,10002,10003,10004,10005)
       ORDER BY x DESC
    }
  } {99 6 10000 10006 nosort t1 i1xy}
  do_test where2-4.6c {
    queryplan {
      SELECT * FROM t1
       WHERE x IN (1,2,3,4,5,6,7,8)
         AND y IN (10000,10001,10002,10003,10004,10005)
       ORDER BY x, y
    }
  } {99 6 10000 10006 nosort t1 i1xy}
  do_test where2-4.6d {
    queryplan {
      SELECT * FROM t1
       WHERE x IN (1,2,3,4,5,6,7,8)
         AND y IN (10000,10001,10002,10003,10004,10005)
       ORDER BY x, y DESC
    }
  } {99 6 10000 10006 sort t1 i1xy}

  # Duplicate entires on the RHS of an IN operator do not cause duplicate
  # output rows.
  #
  do_test where2-4.6x {
    queryplan {
      SELECT * FROM t1 WHERE z IN (10207,10006,10006,10207)
      ORDER BY w
    }
  } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
  do_test where2-4.6y {
    queryplan {
      SELECT * FROM t1 WHERE z IN (10207,10006,10006,10207)
      ORDER BY w DESC
    }
  } {100 6 10201 10207 99 6 10000 10006 sort t1 i1zyx}
  ifcapable compound {
    do_test where2-4.7 {
      queryplan {
        SELECT * FROM t1 WHERE z IN (
           SELECT 10207 UNION ALL SELECT 10006
           UNION ALL SELECT 10006 UNION ALL SELECT 10207)
        ORDER BY w
................................................................................
do_test where2-5.1 {
  queryplan {
    SELECT * FROM t1 WHERE w=99 ORDER BY w
  }
} {99 6 10000 10006 nosort t1 i1w}

ifcapable subquery {
  do_test where2-5.2a {
    queryplan {
      SELECT * FROM t1 WHERE w IN (99) ORDER BY w
    }
  } {99 6 10000 10006 nosort t1 i1w}
  do_test where2-5.2b {
    queryplan {
      SELECT * FROM t1 WHERE w IN (99) ORDER BY w DESC
    }
  } {99 6 10000 10006 nosort t1 i1w}
}

# Verify that OR clauses get translated into IN operators.
#
set ::idx {}
ifcapable subquery {set ::idx i1w}
do_test where2-6.1.1 {