SQLite4  Check-in [856ed13e06]

  Table *pTab;
  FKey *pFKey;
  if( (pTab = pParse->pNewTable)==0 || (pFKey = pTab->pFKey)==0 ) return;
  assert( isDeferred==0 || isDeferred==1 ); /* EV: R-30323-21917 */
  pFKey->isDeferred = (u8)isDeferred;
#endif
}


























/*
** Generate code that will erase and refill index *pIdx.  This is
** used to initialize a newly created index or to recompute the
** content of an index in response to a REINDEX command.
*/
static void sqlite4RefillIndex(Parse *pParse, Index *pIdx, int bCreate){

    sqlite4VdbeAddOp2(v, OP_RowKey, iTab, regKey);
    for(i=0; i<pTab->nCol; i++){
      sqlite4VdbeAddOp3(v, OP_Column, iTab, i, regData+i);
    }
    sqlite4Fts5CodeUpdate(pParse, pIdx, pParse->iNewidxReg, regKey, regData, 0);
  }else{
    sqlite4GetTempRange(pParse,2);
    regKey = sqlite4GetTempReg(pParse);
    sqlite4EncodeIndexKey(pParse, pPk, iTab, pIdx, iIdx, 0, regKey);
    if( pIdx->onError!=OE_None ){
      const char *zErr = "indexed columns are not unique";
      int addrTest;

      addrTest = sqlite4VdbeAddOp4Int(v, OP_IsUnique, iIdx, 0, regKey, 0);
      sqlite4HaltConstraint(pParse, OE_Abort, (char *)zErr, P4_STATIC);
      sqlite4VdbeJumpHere(v, addrTest);
    }




    sqlite4VdbeAddOp3(v, OP_IdxInsert, iIdx, 0, regKey);  

  }

  sqlite4VdbeAddOp2(v, OP_Next, iTab, addr1+1);
  sqlite4VdbeJumpHere(v, addr1);
  sqlite4ReleaseTempReg(pParse, regKey);

  sqlite4VdbeAddOp1(v, OP_Close, iTab);
  sqlite4VdbeAddOp1(v, OP_Close, iIdx);
}

/*
** The CreateIndex structure indicated by the first argument contains the

    }
  }

  return pIdx;
}
#endif /* SQLITE4_OMIT_SUBQUERY */

/*
** This function is used by the implementation of the IN (...) operator.
** It's job is to find or create a b-tree structure that may be used
** either to test for membership of the (...) set or to iterate through
** its members, skipping duplicates.
**
** The index of the cursor opened on the b-tree (database table, database index 
** or ephermal table) is stored in pX->iTable before this function returns.
** The returned value of this function indicates the b-tree type, as follows:
**
**   IN_INDEX_ROWID - The cursor was opened on a database table.
**   IN_INDEX_INDEX - The cursor was opened on a database index.
**   IN_INDEX_EPH -   The cursor was opened on a specially created and
**                    populated epheremal table.
**
** An existing b-tree may only be used if the SELECT is of the simple
** form:
**
**     SELECT <column> FROM <table>
**
** If the prNotFound parameter is 0, then the b-tree will be used to iterate
** through the set members, skipping any duplicates. In this case an
** epheremal table must be used unless the selected <column> is guaranteed
** to be unique - either because it is an INTEGER PRIMARY KEY or it
** has a UNIQUE constraint or UNIQUE index.
**
** If the prNotFound parameter is not 0, then the b-tree will be used 
** for fast set membership tests. In this case an epheremal table must 
** be used unless <column> is an INTEGER PRIMARY KEY or an index can 
** be found with <column> as its left-most column.
**
** When the b-tree is being used for membership tests, the calling function
** needs to know whether or not the structure contains an SQL NULL 
** value in order to correctly evaluate expressions like "X IN (Y, Z)".
** If there is any chance that the (...) might contain a NULL value at
** runtime, then a register is allocated and the register number written
** to *prNotFound. If there is no chance that the (...) contains a
** NULL value, then *prNotFound is left unchanged.
**
** If a register is allocated and its location stored in *prNotFound, then
** its initial value is NULL.  If the (...) does not remain constant
** for the duration of the query (i.e. the SELECT within the (...)
** is a correlated subquery) then the value of the allocated register is
** reset to NULL each time the subquery is rerun. This allows the
** caller to use vdbe code equivalent to the following:
**
**   if( register==NULL ){
**     has_null = <test if data structure contains null>
**     register = 1
**   }
**
** in order to avoid running the <test if data structure contains null>
** test more often than is necessary.
*/
#ifndef SQLITE4_OMIT_SUBQUERY
#if 0
int sqlite4FindInIndex(Parse *pParse, Expr *pX, int *prNotFound){
  Index *pIdx;
  int eType = 0;                        /* Type of RHS table. IN_INDEX_* */
  int iTab = pParse->nTab++;            /* Cursor of the RHS table */
  Vdbe *v = sqlite4GetVdbe(pParse);     /* Virtual machine being coded */

  assert( pX->op==TK_IN );
  assert( prNotFound );

  /* Check to see if an existing table or index can be used to
  ** satisfy the query. This is preferable to generating a new 
  ** ephemeral table.  */
  pIdx = sqlite4FindExistingInIndex(pParse, pX, 0);
  if( pIdx ){
    int iAddr;
    char *pKey;
    int iDb;                      /* aDb[] Index of database containing pIdx */

    iDb = sqlite4SchemaToIndex(pParse->db, pIdx->pSchema);
    pKey = (char *)sqlite4IndexKeyinfo(pParse, pIdx);
    iAddr = sqlite4CodeOnce(pParse);
    sqlite4VdbeAddOp3(v, OP_OpenRead, iTab, pIdx->tnum, iDb);
    sqlite4VdbeChangeP4(v, -1 , pKey, P4_KEYINFO_HANDOFF);
    VdbeComment((v, "%s", pIdx->zName));
    sqlite4VdbeJumpHere(v, iAddr);

    *prNotFound = ++pParse->nMem;
    sqlite4VdbeAddOp2(v, OP_Null, 0, *prNotFound);
    pX->iTable = iTab;
  }else{
    /* Could not find an existing table or index to use as the RHS b-tree.
    ** We will have to generate an ephemeral table to do the job.  */
    double savedNQueryLoop = pParse->nQueryLoop;
    int rMayHaveNull = 0;
    eType = IN_INDEX_EPH;
    *prNotFound = rMayHaveNull = ++pParse->nMem;
    sqlite4VdbeAddOp2(v, OP_Null, 0, *prNotFound);
    sqlite4CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);
    pParse->nQueryLoop = savedNQueryLoop;
  }
  
  return eType;
}
#endif

/*
** This function is used by the implementation of the IN (...) operator.
** The pX parameter is the expression on the RHS of the IN operator, which
** might be either a list of expressions or a subquery.
**
** The job of this routine is to find or create a b-tree object that can
** be used either to test for membership in the RHS set or to iterate through

**     has_null = <test if data structure contains null>
**     register = 1
**   }
**
** in order to avoid running the <test if data structure contains null>
** test more often than is necessary.
*/
int sqlite4FindInIndex(Parse *pParse, Expr *pX, int *prNotFound){
  Select *p;                            /* SELECT to the right of IN operator */
  int eType = 0;                        /* Type of RHS table. IN_INDEX_* */
  int iTab = pParse->nTab++;            /* Cursor of the RHS table */
  int mustBeUnique = (prNotFound==0);   /* True if RHS must be unique */
  Vdbe *v = sqlite4GetVdbe(pParse);     /* Virtual machine being coded */

  assert( pX->op==TK_IN );







  /* Check to see if an existing table or index can be used to
  ** satisfy the query.  This is preferable to generating a new 
  ** ephemeral table.
  */
  p = (ExprHasProperty(pX, EP_xIsSelect) ? pX->x.pSelect : 0);
  if( ALWAYS(pParse->nErr==0) && isCandidateForInOpt(p) && prNotFound ){
    sqlite4 *db = pParse->db;              /* Database connection */
    Table *pTab;                           /* Table <table>. */
    Expr *pExpr;                           /* Expression <column> */
    int iCol;                              /* Index of column <column> */
    int iDb;                               /* Database idx for pTab */





    assert( p );                        /* Because of isCandidateForInOpt(p) */
    assert( p->pEList!=0 );             /* Because of isCandidateForInOpt(p) */
    assert( p->pEList->a[0].pExpr!=0 ); /* Because of isCandidateForInOpt(p) */
    assert( p->pSrc!=0 );               /* Because of isCandidateForInOpt(p) */
    pTab = p->pSrc->a[0].pTab;
    pExpr = p->pEList->a[0].pExpr;
    iCol = pExpr->iColumn;
   
    /* Code an OP_VerifyCookie for <table>. */
    iDb = sqlite4SchemaToIndex(db, pTab->pSchema);
    sqlite4CodeVerifySchema(pParse, iDb);

    /* This function is only called from two places. In both cases the vdbe
    ** has already been allocated. So assume sqlite4GetVdbe() is always
    ** successful here.
    */
    assert(v);
#if 0
    if( iCol<0 ){
      int iAddr;

      iAddr = sqlite4CodeOnce(pParse);

      sqlite4OpenTable(pParse, iTab, iDb, pTab, OP_OpenRead);
      eType = IN_INDEX_ROWID;

      sqlite4VdbeJumpHere(v, iAddr);
    }else
#endif
    {
      Index *pIdx;                         /* Iterator variable */

      /* The collation sequence used by the comparison. If an index is to
      ** be used in place of a temp-table, it must be ordered according
      ** to this collation sequence.  */
      CollSeq *pReq = sqlite4BinaryCompareCollSeq(pParse, pX->pLeft, pExpr);

      /* Check that the affinity that will be used to perform the 
      ** comparison is the same as the affinity of the column. If
      ** it is not, it is not possible to use any index.
      */

      int affinity_ok = sqlite4IndexAffinityOk(pX, pTab->aCol[iCol].affinity);

      for(pIdx=pTab->pIndex; pIdx && eType==0 && affinity_ok; pIdx=pIdx->pNext){
        if( (pIdx->aiColumn[0]==iCol)
         && sqlite4FindCollSeq(db, pIdx->azColl[0], 0)==pReq
         && (!mustBeUnique || (pIdx->nColumn==1 && pIdx->onError!=OE_None))
        ){
          int iAddr;
  













          iAddr = sqlite4CodeOnce(pParse);
          sqlite4OpenIndex(pParse, iTab, iDb, pIdx, OP_OpenRead);
          assert( IN_INDEX_INDEX_DESC == IN_INDEX_INDEX_ASC+1 );



          eType = IN_INDEX_INDEX_ASC + pIdx->aSortOrder[0];


          sqlite4VdbeJumpHere(v, iAddr);
          if( prNotFound && !pTab->aCol[iCol].notNull ){
            *prNotFound = ++pParse->nMem;
            sqlite4VdbeAddOp2(v, OP_Null, 0, *prNotFound);
          }
        }
      }
    }
  }

  if( eType==0 ){
    /* Could not found an existing table or index to use as the RHS b-tree.
    ** We will have to generate an ephemeral table to do the job.
    */
    u32 savedNQueryLoop = pParse->nQueryLoop;
    int rMayHaveNull = 0;
    eType = IN_INDEX_EPH;
    if( prNotFound ){
      *prNotFound = rMayHaveNull = ++pParse->nMem;
      sqlite4VdbeAddOp2(v, OP_Null, 0, *prNotFound);
    }else{
      testcase( pParse->nQueryLoop>0 );
      pParse->nQueryLoop = 0;

    }
    sqlite4CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);
    pParse->nQueryLoop = savedNQueryLoop;
  }else{
    pX->iTable = iTab;
  }
  return eType;

  /* Compute the RHS.   After this step, the table with cursor
  ** pExpr->iTable will contains the values that make up the RHS.
  */
  v = pParse->pVdbe;
  assert( v!=0 );       /* OOM detected prior to this routine */
  VdbeNoopComment((v, "begin IN expr"));
  eType = sqlite4FindInIndex(pParse, pExpr, &rRhsHasNull);

  /* Figure out the affinity to use to create a key from the results
  ** of the expression. affinityStr stores a static string suitable for
  ** P4 of OP_MakeRecord.
  */
  affinity = comparisonAffinity(pExpr);

  #define sqlite4EndBenignMalloc(X)
#endif

#define IN_INDEX_ROWID           1
#define IN_INDEX_EPH             2
#define IN_INDEX_INDEX_ASC       3
#define IN_INDEX_INDEX_DESC      4
int sqlite4FindInIndex(Parse *, Expr *, int*);
Index *sqlite4FindExistingInIndex(Parse *, Expr *, int);


#if SQLITE4_MAX_EXPR_DEPTH>0
  void sqlite4ExprSetHeight(Parse *pParse, Expr *p);
  int sqlite4SelectExprHeight(Select *);
  int sqlite4ExprCheckHeight(Parse*, int);

  }else if( pX->op==TK_ISNULL ){
    iReg = iTarget;
    sqlite4VdbeAddOp2(v, OP_Null, 0, iReg);
#ifndef SQLITE4_OMIT_SUBQUERY
  }else{
    int eType;
    int iTab;

    struct InLoop *pIn;
    WhereLoop *pLoop = pLevel->pWLoop;

    if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0
      && pLoop->u.btree.pIndex!=0
      && pLoop->u.btree.pIndex->aSortOrder[iEq]
    ){
      testcase( iEq==0 );
      testcase( bRev );
      bRev = !bRev;
    }
    assert( pX->op==TK_IN );
    iReg = iTarget;
    eType = sqlite4FindInIndex(pParse, pX, 0);
    if( eType==IN_INDEX_INDEX_DESC ){
      testcase( bRev );
      bRev = !bRev;
    }
    iTab = pX->iTable;
    sqlite4VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
    assert( (pLoop->wsFlags & WHERE_MULTI_OR)==0 );

    pIn = pLevel->u.in.aInLoop;
    if( pIn ){
      pIn += pLevel->u.in.nIn - 1;
      pIn->iCur = iTab;
      if( eType==IN_INDEX_ROWID ){
        pIn->addrInTop = sqlite4VdbeAddOp2(v, OP_Rowid, iTab, iReg);
      }else{
        pIn->addrInTop = sqlite4VdbeAddOp3(v, OP_Column, iTab, 0, iReg);
      }
      pIn->eEndLoopOp = bRev ? OP_Prev : OP_Next;
      sqlite4VdbeAddOp1(v, OP_IsNull, iReg);
    }else{
      pLevel->u.in.nIn = 0;
    }
#endif

  exec_neph { SELECT 'TEXT' COLLATE nocase IN (SELECT b FROM t3) }
} {1 1}
do_test in3-4.4 {
  # A temp table must be used because t3_i.b is not guaranteed to be unique.
  exec_neph { SELECT b FROM t3 WHERE b IN (SELECT b FROM t3) }
} {1 none numeric real text}
do_test in3-4.5 {
  execsql { CREATE UNIQUE INDEX t3_i2 ON t3(b) }
  exec_neph { SELECT b FROM t3 WHERE b IN (SELECT b FROM t3) ORDER BY b}
} {0 none numeric real text}
do_test in3-4.6 {
  execsql { DROP INDEX t3_i2 }
} {}

# The following two test cases verify that ticket #2991 has been fixed.

do_execsql_test 89.1 {
  CREATE TABLE t1(a COLLATE NOCASE);
  CREATE INDEX i1 ON t1(a);
}
do_eqp_test 89.2 {
  SELECT * FROM t1 ORDER BY a;
} {0 0 0 {SCAN TABLE t1 USING INDEX i1}}


























































finish_test