SQLite4  Check-in [34138a088b]

  p = (ExprHasProperty(pX, EP_xIsSelect) ? pX->x.pSelect : 0);
  if( ALWAYS(pParse->nErr==0) && isCandidateForInOpt(p) ){
    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 */
    Index *pIdx;
    CollSeq *pReq;
    char aff;
    int affinity_ok;

    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 and OP_TableLock for <table>. */
    iDb = sqlite4SchemaToIndex(db, pTab->pSchema);
    sqlite4CodeVerifySchema(pParse, iDb);
    sqlite4TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);

    /* 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);












    /* 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.  */
    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.
    */
    aff = comparisonAffinity(pX);
    affinity_ok = (pTab->aCol[iCol].affinity==aff||aff==SQLITE_AFF_NONE);

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

        pKey = (char *)sqlite4IndexKeyinfo(pParse, pIdx);
        iAddr = sqlite4CodeOnce(pParse);

        sqlite4VdbeAddOp4(v, OP_OpenRead, iTab, pIdx->tnum, iDb,
            pKey,P4_KEYINFO_HANDOFF);
        VdbeComment((v, "%s", pIdx->zName));
        eType = IN_INDEX_INDEX;

        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.

}

/*
** Insert code into "v" that will push the record on the top of the
** stack into the sorter.
*/
static void pushOntoSorter(
  Parse *pParse,                  /* Parser context */
  ExprList *pOrderBy,             /* The ORDER BY clause */
  Select *pSelect,                /* The whole SELECT statement */
  int regData                     /* Register holding data to be sorted */
){
  Vdbe *v = pParse->pVdbe;
  int nExpr = pOrderBy->nExpr;
  int regBase = sqlite4GetTempRange(pParse, nExpr+1);

  int regKey = sqlite4GetTempReg(pParse);
  int op;

  /* Assemble the sort-key values in a contiguous array of registers
  ** starting at regBase. The sort-key consists of the result of each 
  ** expression in the ORDER BY clause followed by a unique sequence 
  ** number. The sequence number allows more than one row with the same
  ** sort-key.  */
  sqlite4ExprCacheClear(pParse);
  sqlite4ExprCodeExprList(pParse, pOrderBy, regBase, 0);
  sqlite4VdbeAddOp2(v, OP_Sequence, pOrderBy->iECursor, regBase+nExpr);

  /* Encode the sort-key. */
  sqlite4VdbeAddOp3(v, OP_MakeIdxKey, pOrderBy->iECursor, regBase, regKey);

  /* Insert an entry into the sorter. The key inserted is the encoded key
  ** created by the OP_MakeIdxKey coded above. The value is the record
  ** currently stored in register regData.  */
  if( pSelect->selFlags & SF_UseSorter ){
    op = OP_SorterInsert;
  }else{
    op = OP_IdxInsert;
  }
  sqlite4VdbeAddOp3(v, op, pOrderBy->iECursor, regData, regKey);

  /* Release the temporary registers */
  sqlite4ReleaseTempReg(pParse, regKey);
  sqlite4ReleaseTempRange(pParse, regBase, nExpr+1);

  if( pSelect->iLimit ){
    int addr1, addr2;
    int iLimit;
    if( pSelect->iOffset ){
      iLimit = pSelect->iOffset+1;
    }else{
      iLimit = pSelect->iLimit;

** index to implement a DISTINCT test.
**
** Space to hold the KeyInfo structure is obtain from malloc.  The calling
** function is responsible for seeing that this structure is eventually
** freed.  Add the KeyInfo structure to the P4 field of an opcode using
** P4_KEYINFO_HANDOFF is the usual way of dealing with this.
*/
static KeyInfo *keyInfoFromExprList(
  Parse *pParse, 
  ExprList *pList,
  int bOrderBy
){
  sqlite4 *db = pParse->db;       /* Database handle */
  int nField;                     /* Number of fields in keys */
  KeyInfo *pInfo;                 /* Object to return */
  int nByte;                      /* Bytes of space to allocate */


  assert( bOrderBy==0 || bOrderBy==1 );

  nField = pList->nExpr + bOrderBy;
  nByte = sizeof(KeyInfo) + nField * sizeof(CollSeq *) + nField;
  pInfo = (KeyInfo *)sqlite4DbMallocZero(db, nByte);

  if( pInfo ){
    int i;                        /* Used to iterate through pList */

    pInfo->aSortOrder = (u8*)&pInfo->aColl[nField];
    pInfo->nField = (u16)nField;
    pInfo->enc = ENC(db);
    pInfo->db = db;

    for(i=0; i<pList->nExpr; i++){
      CollSeq *pColl;
      pColl = sqlite4ExprCollSeq(pParse, pList->a[i].pExpr);

      if( !pColl ) pColl = db->pDfltColl;

      pInfo->aColl[i] = pColl;
      pInfo->aSortOrder[i] = pList->a[i].sortOrder;
    }
  }
  return pInfo;
}

#ifndef SQLITE_OMIT_COMPOUND_SELECT
/*

#else
/* No-op versions of the explainXXX() functions and macros. */
# define explainComposite(v,w,x,y,z)
#endif

/*
** If the inner loop was generated using a non-null pOrderBy argument,
** then the results were placed in a sorter. After the loop is terminated
** we need to loop through the contents of the sorter and output the 
** results. The following routine generates the code needed to do that.
*/
static void generateSortTail(
  Parse *pParse,                  /* Parsing context */
  Select *p,                      /* The SELECT statement */
  Vdbe *v,                        /* Generate code into this VDBE */
  int nColumn,                    /* Number of columns of data */
  SelectDest *pDest               /* Write the sorted results here */
){
  int addrBreak = sqlite4VdbeMakeLabel(v);     /* Jump here to exit loop */
  int addrContinue = sqlite4VdbeMakeLabel(v);  /* Jump here for next cycle */
  int addr;
  int iTab;                       /* Sorter object cursor */

  ExprList *pOrderBy = p->pOrderBy;

  int eDest = pDest->eDest;
  int iParm = pDest->iParm;

  int regRow;
  int regRowid = 0;

  iTab = pOrderBy->iECursor;
  regRow = sqlite4GetTempReg(pParse);
  if( eDest!=SRT_Output && eDest!=SRT_Coroutine ){




    regRowid = sqlite4GetTempReg(pParse);
  }

  if( p->selFlags & SF_UseSorter ){
    int regSortOut = ++pParse->nMem;
    int ptab2 = pParse->nTab++;
    sqlite4VdbeAddOp3(v, OP_OpenPseudo, ptab2, regSortOut, pOrderBy->nExpr+2);
    addr = 1 + sqlite4VdbeAddOp2(v, OP_SorterSort, iTab, addrBreak);
    codeOffset(v, p, addrContinue);
    sqlite4VdbeAddOp2(v, OP_SorterData, iTab, regSortOut);

    }
#endif
    default: {
      int i;
      assert( eDest==SRT_Output || eDest==SRT_Coroutine ); 
      testcase( eDest==SRT_Output );
      testcase( eDest==SRT_Coroutine );

      /* Read the data out of the sorter and into the array of nColumn
      ** contiguous registers starting at pDest->iMem.  */
      for(i=0; i<nColumn; i++){

        sqlite4VdbeAddOp3(v, OP_Column, iTab, i, pDest->iMem+i);


      }

      if( eDest==SRT_Output ){
        sqlite4VdbeAddOp2(v, OP_ResultRow, pDest->iMem, nColumn);
        sqlite4ExprCacheAffinityChange(pParse, pDest->iMem, nColumn);
      }else{
        sqlite4VdbeAddOp1(v, OP_Yield, pDest->iParm);
      }
      break;

  sqlite4VdbeResolveLabel(v, addrContinue);
  if( p->selFlags & SF_UseSorter ){
    sqlite4VdbeAddOp2(v, OP_SorterNext, iTab, addr);
  }else{
    sqlite4VdbeAddOp2(v, OP_Next, iTab, addr);
  }
  sqlite4VdbeResolveLabel(v, addrBreak);



}

/*
** Return a pointer to a string containing the 'declaration type' of the
** expression pExpr. The string may be treated as static by the caller.
**
** The declaration type is the exact datatype definition extracted from the

      Expr *pE = pFunc->pExpr;
      assert( !ExprHasProperty(pE, EP_xIsSelect) );
      if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){
        sqlite4ErrorMsg(pParse, "DISTINCT aggregates must have exactly one "
           "argument");
        pFunc->iDistinct = -1;
      }else{
        KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pE->x.pList, 0);
        sqlite4VdbeAddOp4(v, OP_OpenEphemeral, pFunc->iDistinct, 0, 0,
                          (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
      }
    }
  }
}

  ** extracted in pre-sorted order.  If that is the case, then the
  ** OP_OpenEphemeral instruction will be changed to an OP_Noop once
  ** we figure out that the sorting index is not needed.  The addrSortIndex
  ** variable is used to facilitate that change.
  */
  if( pOrderBy ){
    KeyInfo *pKeyInfo;
    pKeyInfo = keyInfoFromExprList(pParse, pOrderBy, 1);
    if( pKeyInfo ) pKeyInfo->nData = pEList->nExpr;

    pOrderBy->iECursor = pParse->nTab++;
    p->addrOpenEphm[2] = addrSortIndex =
      sqlite4VdbeAddOp4(v, OP_OpenEphemeral,
                           pOrderBy->iECursor, pOrderBy->nExpr+2, 0,
                           (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
  }else{
    addrSortIndex = -1;

  }

  /* Open a virtual index to use for the distinct set.
  */
  if( p->selFlags & SF_Distinct ){
    KeyInfo *pKeyInfo;
    distinct = pParse->nTab++;
    pKeyInfo = keyInfoFromExprList(pParse, p->pEList, 0);
    addrDistinctIndex = sqlite4VdbeAddOp4(v, OP_OpenEphemeral, distinct, 0, 0,
        (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
  }else{
    distinct = addrDistinctIndex = -1;
  }

  /* Aggregate and non-aggregate queries are handled differently */

      /* If there is a GROUP BY clause we might need a sorting index to
      ** implement it.  Allocate that sorting index now.  If it turns out
      ** that we do not need it after all, the OP_SorterOpen instruction
      ** will be converted into a Noop.  
      */
      sAggInfo.sortingIdx = pParse->nTab++;
      pKeyInfo = keyInfoFromExprList(pParse, pGroupBy, 0);
      addrSortingIdx = sqlite4VdbeAddOp4(v, OP_SorterOpen, 
          sAggInfo.sortingIdx, sAggInfo.nSortingColumn, 
          0, (char*)pKeyInfo, P4_KEYINFO_HANDOFF);

      /* Initialize memory locations used by GROUP BY aggregate processing
      */
      iUseFlag = ++pParse->nMem;

    aData = (const KVByteArray*)pReg->z;
    nData = pReg->n;
  }else{
    aData = 0;
    MemSetTypeFlag(pDest, MEM_Null);
  }
  if( rc==SQLITE_OK && aData ){
    /* TODO: Fix this somehow... */
    int nField = pC->nField;
    if( pC->pKeyInfo && pC->pKeyInfo->nData ) nField = pC->pKeyInfo->nData;

    rc = sqlite4VdbeCreateDecoder(db, aData, nData, nField, &pCodec);
    if( rc==0 ){
      pDefault = (pOp->p4type==P4_MEM) ? pOp->p4.pMem : 0;
      rc = sqlite4VdbeDecodeValue(pCodec, pOp->p2, pDefault, pDest);
      assert( rc==SQLITE_OK );
      sqlite4VdbeDestroyDecoder(pCodec);
    }

  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  assert( pC->pseudoTableReg==0 );
  assert( OP_SeekLe == OP_SeekLt+1 );
  assert( OP_SeekGe == OP_SeekLt+2 );
  assert( OP_SeekGt == OP_SeekLt+3 );
  assert( pC->isOrdered );

  oc = pOp->opcode;
  pC->nullRow = 0;



  nField = pOp->p4.i;

  pIn3 = &aMem[pOp->p3];
  rc = sqlite4VdbeEncodeKey(db, pIn3, nField, pC->iRoot, pC->pKeyInfo,
                            &aProbe, &nProbe, 0);
  if( rc ){
    sqlite4DbFree(db, aProbe);
    break;
  }
  rc = sqlite4KVCursorSeek(pC->pKVCur, aProbe, nProbe, oc<=OP_SeekLe ? -1 : 1);

  sqlite4DbFree(db, aProbe);
  if( rc==SQLITE_OK ){
    if( oc==OP_SeekLt ){
      rc = sqlite4KVCursorPrev(pC->pKVCur);
    }else if( oc==OP_SeekGt ){
      rc = sqlite4KVCursorNext(pC->pKVCur);
    }
  }else if( rc==SQLITE_INEXACT ){
    rc = SQLITE_OK;
  }

  if( rc==SQLITE_OK ){
    rc = sqlite4KVCursorKey(pC->pKVCur, &aKey, &nKey);
    if( rc==SQLITE_OK ){
      iRoot = 0;
      n = sqlite4GetVarint64(aKey, nKey, &iRoot);
      if( iRoot!=pC->iRoot ) rc = SQLITE_NOTFOUND;
      c = aKey[n];
      if( c<0x05 || c>0xfa ) rc = SQLITE_NOTFOUND;
    }
  }
  if( rc==SQLITE_NOTFOUND ){
    rc = SQLITE_OK;
    pc = pOp->p2 - 1;
  }
  break;
}

/* Opcode: Seek P1 P2 * * *

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  rc = sqlite4VdbeSeekEnd(pC, -1);
  if( rc==SQLITE_NOTFOUND ){  
    rc = SQLITE_OK;
    if( pOp->p2 ) pc = pOp->p2 - 1;
  }else{
    pC->nullRow = 0;
  }
  break;
}


/* Opcode: Sort P1 P2 * * *
**

#   quick
#   full
#
lappend ::testsuitelist xxx

test_suite "src4" -prefix "" -description {
} -files [
  test_set simple.test fkey1.test conflict.test trigger2.test select1.test
]

test_suite "veryquick" -prefix "" -description {
  "Very" quick test suite. Runs in less than 5 minutes on a workstation. 
  This test suite is the same as the "quick" tests, except that some files
  that test malloc and IO errors are omitted.
} -files [

      GROUP BY f1
     HAVING max(m+5)<10
  }
} {1 {misuse of aliased aggregate m}}
do_test select1-2.23 {
  execsql {
    CREATE TABLE tkt2526(a,b,c PRIMARY KEY);
    INSERT INTO tkt2526 VALUES('x','y',1);
    INSERT INTO tkt2526 VALUES('x','z',2);
  }
  catchsql {
    SELECT count(a) AS cn FROM tkt2526 GROUP BY a HAVING cn<max(cn)
  }
} {1 {misuse of aliased aggregate cn}}

# WHERE clause expressions

    SELECT * FROM sqlite_master WHERE rowid=10;
    SELECT * FROM sqlite_master WHERE rowid<10;
    SELECT * FROM sqlite_master WHERE rowid<=10;
    SELECT * FROM sqlite_master WHERE rowid>=10;
    SELECT * FROM sqlite_master;
  }
} {}

do_test select1-14.2 {
  execsql { 
    SELECT 10 IN (SELECT rowid FROM sqlite_master);
  }
} {0}

if {[db one {PRAGMA locking_mode}]=="normal"} {

} {4 3}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 39.1 {
  CREATE TABLE t1(a PRIMARY KEY, b);
}

do_catchsql_test 39.2 {
  INSERT INTO t1 VALUES(NULL, 'xyz');
} {1 {t1.a may not be NULL}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 40.1 {
    CREATE TABLE abc(a, b, c, PRIMARY KEY(a, b));
    INSERT INTO abc VALUES(1, 1, 1);
    SELECT * FROM abc;
} {1 1 1}
do_execsql_test 40.2 { SELECT max(a) FROM abc } {1}
do_execsql_test 40.3 {
  SELECT a+(select max(a) FROM abc), 
         b+(select max(a) FROM abc), 
         c+(select max(a) FROM abc) 
  FROM abc
} {2 2 2}
do_execsql_test 40.4 {
  INSERT INTO abc SELECT 
      a+(select max(a) FROM abc), 
      b+(select max(a) FROM abc), 
      c+(select max(a) FROM abc) 
  FROM abc;
}
do_execsql_test 40.5 { SELECT * FROM abc } {1 1 1 2 2 2}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 41.1 {
  CREATE TABLE x(a, b);
  INSERT INTO x VALUES(1, 'one');
  INSERT INTO x VALUES(2, 'two');
  INSERT INTO x VALUES(1, 'three');
} 

do_execsql_test 41.2 {
  SELECT * FROM x ORDER BY a;
} {1 one 1 three 2 two}

#proc populate_t1 {} {
#  db eval {
#    INSERT INTO t1(a, b) VALUES(4, 'four');
#    INSERT INTO t1(a, b) VALUES(9, 'nine');
#    INSERT INTO t1(a, b) VALUES(5, 'five');
#    INSERT INTO t1(a, b) VALUES(1, 'one');