SQLite

/*
** Insert or replace a WhereLoop entry using the template supplied.
**
** An existing WhereLoop entry might be overwritten if the new template
** is better and has fewer dependencies.  Or the template will be ignored
** and no insert will occur if an existing WhereLoop is faster and has
** fewer dependencies than the template.  Otherwise a new WhereLoop is
** added based on the template.
**
** If pBuilder->pBest is not NULL then we only care about the very
** best template and that template should be stored in pBuilder->pBest.
** If pBuilder->pBest is NULL then a list of the best templates are stored
** in pBuilder->pWInfo->pLoops.
**
** When accumulating multiple loops (when pBuilder->pBest is NULL) we
** still might overwrite similar loops with the new template if the
** template is better.  Loops may be overwritten if the following 
** conditions are met:
**
**    (1)  They have the same iTab.
**    (2)  They have the same iSortIdx.
**    (3)  The template has same or fewer dependencies than the current loop
**    (4)  The template has the same or lower cost than the current loop
**    (5)  The template uses more terms of the same index but has no additional
**         dependencies          
*/
static int whereLoopInsert(WhereLoopBuilder *pBuilder, WhereLoop *pTemplate){
  WhereLoop **ppPrev, *p, *pNext = 0, *pToFree = 0;
  WhereTerm **paTerm = 0;
  sqlite3 *db = pBuilder->db;
  WhereInfo *pWInfo = pBuilder->pWInfo;

  Index *pProbe;              /* An index we are evaluating */
  Index sPk;                  /* A fake index object for the primary key */
  tRowcnt aiRowEstPk[2];      /* The aiRowEst[] value for the sPk index */
  int aiColumnPk = -1;        /* The aColumn[] value for the sPk index */
  struct SrcList_item *pSrc;  /* The FROM clause btree term to add */
  WhereLoop *pNew;            /* Template WhereLoop object */
  int rc = SQLITE_OK;         /* Return code */
  int iSortIdx = 1;           /* Index number */
  int b;                      /* A boolean value */
  double rSize;               /* number of rows in the table */
  double rLogSize;            /* Logarithm of the number of rows in the table */
  
  pNew = pBuilder->pNew;
  pSrc = pBuilder->pTabList->a + pNew->iTab;
  assert( !IsVirtual(pSrc->pTab) );

    pNew->u.btree.pIndex = pProbe;
    b = indexMightHelpWithOrderBy(pBuilder, pProbe, pSrc->iCursor);
    if( pProbe->tnum<=0 ){
      /* Integer primary key index */
      pNew->wsFlags = WHERE_IPK;

      /* Full table scan */
      pNew->iSortIdx = b ? iSortIdx : 0;
      pNew->nOut = rSize;
      pNew->rRun = (rSize + rLogSize)*(3+b); /* 4x penalty for a full-scan */
      rc = whereLoopInsert(pBuilder, pNew);
      if( rc ) break;
    }else{
      Bitmask m = pSrc->colUsed;
      int j;

  int nLoop,            /* Number of entries in pPath->aLoop[] */
  WhereLoop *pLast,     /* Add this WhereLoop to the end of pPath->aLoop[] */
  Bitmask *pRevMask     /* Mask of WhereLoops to run in reverse order */
){
  u8 revSet;            /* True if rev is known */
  u8 rev;               /* Composite sort order */
  u8 revIdx;            /* Index sort order */
  u8 isOneRow;          /* Current WhereLoop is a one-row loop */
  u8 requireOneRow = 0; /* All subsequent loops must be one-row */
  u8 isUniqueIdx;       /* Current WhereLoop uses a unique index */

  u16 nColumn;
  u16 nOrderBy;
  int i, j;
  int nUsed = 0;
  int iCur;
  int iColumn;
  WhereLoop *pLoop;

  **  (2) If the current WhereLoop is not one-row, then all subsequent
  **      WhereLoops must be one-row.
  **
  **  (3) Optionally match any ORDER BY terms against the first nEq columns
  **      of the index.
  **
  **  (4) Index columns past nEq must match ORDER BY terms one-for-one.
  **
  **  (5) If all columns of a UNIQUE index have been matched against ORDER BY
  **      terms, then any subsequent entries in the ORDER BY clause against the
  **      same table can be skipped.
  */

  assert( pOrderBy!=0 );

  /* Sortability of virtual tables is determined by the xBestIndex method
  ** of the virtual table itself */
  if( pLast->wsFlags & WHERE_VIRTUALTABLE ){

    pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
    if( pOBExpr->op!=TK_COLUMN ) return 0;
  }
    
  for(i=0; i<=nLoop && nUsed<nOrderBy; i++){
    pLoop = i<nLoop ? pPath->aLoop[i] : pLast;
    assert( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 );
    isOneRow = isUniqueIdx = 1;
    if( pLoop->wsFlags & WHERE_IPK ){
      if( (pLoop->wsFlags & WHERE_COLUMN_IN)!=0 ) isOneRow = 0;
      if( pLoop->u.btree.nEq!=1 ) isOneRow = 0;
      pIndex = 0;
      nColumn = 0;
    }else if( (pIndex = pLoop->u.btree.pIndex)==0 || pIndex->bUnordered ){
      return 0;
    }else{
      nColumn = pIndex->nColumn;
      if( pIndex->onError==OE_None ){
        isOneRow = isUniqueIdx = 0;
      }else if( (pLoop->wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_RANGE
                                   |WHERE_COLUMN_NULL))!=0 ){
        isOneRow = 0;
      }else if( pLoop->u.btree.nEq < pIndex->nColumn ){
        isOneRow = 0;
      }
    }
    if( !isOneRow && requireOneRow ) return 0;
    requireOneRow = !isOneRow;
    iCur = pWInfo->pTabList->a[pLoop->iTab].iCursor;
    j = 0;
    revSet = rev = 0;
    for(j=0; j<=nColumn && nUsed<nOrderBy; j++, nUsed++){
      int skipable;
      pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[nUsed].pExpr);
      assert( pOBExpr->op==TK_COLUMN );
      if( pOBExpr->iTable!=iCur ) break;
      if( isOneRow ){ j--; continue; }
      if( j<nColumn ){
        /* Normal index columns */
        iColumn = pIndex->aiColumn[j];
        revIdx = pIndex->aSortOrder[j];
        if( iColumn==pIndex->pTable->iPKey ) iColumn = -1;
      }else{
        /* The ROWID column at the end */

        if( revSet ){
          if( (rev ^ revIdx)!=pOrderBy->a[nUsed].sortOrder ) return 0;
        }else{
          rev = revIdx ^ pOrderBy->a[nUsed].sortOrder;
          revSet = 1;
        }
      }
      if( j>=nColumn-1 && isUniqueIdx ){ j--; isOneRow = 1; }
    }
    if( rev ) revMask |= ((Bitmask)1)<<i;
  }
  if( nUsed==nOrderBy ){
    *pRevMask = revMask;
    return 1;
  }