#ifndef SQLITE4_OMIT_TRUNCATE_OPTIMIZATION
  /* Special case: A DELETE without a WHERE clause deletes everything.
  ** It is easier just to erase the whole table. Prior to version 3.6.5,
  ** this optimization caused the row change count (the value returned by 
  ** API function sqlite4_count_changes) to be set incorrectly.  */
  if( rcauth==SQLITE4_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) 
   && 0==sqlite4FkRequired(pParse, pTab, 0)

  ){
    Index *pIdx;                  /* For looping over indices of the table */
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->pSchema==pTab->pSchema );
      sqlite4VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
      if( pIdx->eIndexType==SQLITE4_INDEX_PRIMARYKEY ){
        sqlite4VdbeChangeP5(v, OPFLAG_NCHANGE);

#ifndef SQLITE4_OMIT_TRUNCATE_OPTIMIZATION
  /* Special case: A DELETE without a WHERE clause deletes everything.
  ** It is easier just to erase the whole table. Prior to version 3.6.5,
  ** this optimization caused the row change count (the value returned by 
  ** API function sqlite4_count_changes) to be set incorrectly.  */
  if( rcauth==SQLITE4_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) 
   && 0==sqlite4FkRequired(pParse, pTab, 0)
   && !IsKvstore(pTab)
  ){
    Index *pIdx;                  /* For looping over indices of the table */
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->pSchema==pTab->pSchema );
      sqlite4VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
      if( pIdx->eIndexType==SQLITE4_INDEX_PRIMARYKEY ){
        sqlite4VdbeChangeP5(v, OPFLAG_NCHANGE);

    /* If regKey is 0, pIdx will not be updated. */
    if( regKey==0 ) continue;

    /* Create an index key. Primary key indexes consists of just the primary
    ** key values. Other indexes consists of the indexed columns followed by
    ** the primary key values.  */







    nTmpReg = 1 + pIdx->nColumn + (pIdx==pPk ? 0 : pPk->nColumn);
    regTmp = sqlite4GetTempRange(pParse, nTmpReg);
    regPk = regTmp + nTmpReg - 1;

    for(i=0; i<pIdx->nColumn; i++){
      int idx = pIdx->aiColumn[i];
      sqlite4VdbeAddOp2(v, OP_SCopy, regContent+idx, regTmp+i);
    }
    if( pIdx!=pPk ){
      for(i=0; i<pPk->nColumn; i++){
        int idx = pPk->aiColumn[i];
        sqlite4VdbeAddOp2(v, OP_SCopy, regContent+idx, regTmp+i+pIdx->nColumn);
      }
    }
    sqlite4VdbeAddOp4Int(v, OP_MakeKey, regTmp, nTmpReg-1, regKey, iIdx);

    VdbeComment((v, "key for %s", pIdx->zName));

    /* If Index.onError==OE_None, then pIdx is not a UNIQUE or PRIMARY KEY 
    ** index. In this case there is no need to test the index for uniqueness
    ** - all that is required is to populate the regKey register. Jump 
    ** to the next iteration of the loop if this is the case.  */
    onError = pIdx->onError;
................................................................................
        if( onError==OE_Ignore ) onError = OE_Replace;
        else if( onError==OE_Fail ) onError = OE_Abort;
      }

      iLabel = sqlite4VdbeMakeLabel(v);
      if( pIdx!=pPk ){
        sqlite4VdbeAddOp3(v, OP_IsNull, regTmp, iLabel, pIdx->nColumn);

      }
      if( regOldKey && pIdx==pPk ){
        sqlite4VdbeAddOp3(v, OP_Eq, regOldKey, iLabel, regKey);
      }
      sqlite4VdbeAddOp4(v, OP_Blob, nPkRoot, regPk, 0, (char*)aPkRoot, nPkRoot);
      sqlite4VdbeAddOp4Int(v, OP_IsUnique, iIdx, iLabel, regKey, regPk);
      if( regOldKey && pIdx!=pPk ){
        sqlite4VdbeAddOp3(v, OP_Eq, regOldKey, iLabel, regPk);
      }
      
      switch( onError ){
        case OE_Rollback:
................................................................................

  /* Generate code to serialize array of registers into a database record. 
  ** This OP_MakeRecord also serves to apply affinities to the array of
  ** input registers at regContent. For this reason it must be executed 
  ** before any MakeRecord instructions used to create covering index
  ** records.  */
  regRec = sqlite4GetTempReg(pParse);




  sqlite4VdbeAddOp3(v, OP_MakeRecord, regContent, pTab->nCol, regRec);
  sqlite4TableAffinityStr(v, pTab);
  sqlite4ExprCacheAffinityChange(pParse, regContent, pTab->nCol);

  regCover = sqlite4GetTempReg(pParse);

  /* Write the entry to each index. */
  for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
    assert( pIdx->eIndexType!=SQLITE4_INDEX_PRIMARYKEY || aRegIdx[i] );
    if( pIdx->eIndexType==SQLITE4_INDEX_FTS5 ){
      int iPK;

    /* If regKey is 0, pIdx will not be updated. */
    if( regKey==0 ) continue;

    /* Create an index key. Primary key indexes consists of just the primary
    ** key values. Other indexes consists of the indexed columns followed by
    ** the primary key values.  */
    if( pIdx->tnum==KVSTORE_ROOT ){
      /* If this is the sqlite_kvstore PK index, interpret the value
      ** specified for column "key" as a blob and use it as the index key. */
      sqlite4VdbeAddOp2(v, OP_SCopy, regContent, regKey);
      sqlite4VdbeAddOp1(v, OP_ToBlob, regKey);
      regPk = regKey;
    }else{
      nTmpReg = 1 + pIdx->nColumn + (pIdx==pPk ? 0 : pPk->nColumn);
      regTmp = sqlite4GetTempRange(pParse, nTmpReg);
      regPk = regTmp + nTmpReg - 1;

      for(i=0; i<pIdx->nColumn; i++){
        int idx = pIdx->aiColumn[i];
        sqlite4VdbeAddOp2(v, OP_SCopy, regContent+idx, regTmp+i);
      }
      if( pIdx!=pPk ){
        for(i=0; i<pPk->nColumn; i++){
          int idx = pPk->aiColumn[i];
          sqlite4VdbeAddOp2(v, OP_SCopy, regContent+idx,regTmp+i+pIdx->nColumn);
        }
      }
      sqlite4VdbeAddOp4Int(v, OP_MakeKey, regTmp, nTmpReg-1, regKey, iIdx);
    }
    VdbeComment((v, "key for %s", pIdx->zName));

    /* If Index.onError==OE_None, then pIdx is not a UNIQUE or PRIMARY KEY 
    ** index. In this case there is no need to test the index for uniqueness
    ** - all that is required is to populate the regKey register. Jump 
    ** to the next iteration of the loop if this is the case.  */
    onError = pIdx->onError;
................................................................................
        if( onError==OE_Ignore ) onError = OE_Replace;
        else if( onError==OE_Fail ) onError = OE_Abort;
      }

      iLabel = sqlite4VdbeMakeLabel(v);
      if( pIdx!=pPk ){
        sqlite4VdbeAddOp3(v, OP_IsNull, regTmp, iLabel, pIdx->nColumn);
        sqlite4VdbeAddOp4(v, OP_Blob, nPkRoot, regPk, 0,(char*)aPkRoot,nPkRoot);
      }
      if( regOldKey && pIdx==pPk ){
        sqlite4VdbeAddOp3(v, OP_Eq, regOldKey, iLabel, regKey);
      }

      sqlite4VdbeAddOp4Int(v, OP_IsUnique, iIdx, iLabel, regKey, regPk);
      if( regOldKey && pIdx!=pPk ){
        sqlite4VdbeAddOp3(v, OP_Eq, regOldKey, iLabel, regPk);
      }
      
      switch( onError ){
        case OE_Rollback:
................................................................................

  /* Generate code to serialize array of registers into a database record. 
  ** This OP_MakeRecord also serves to apply affinities to the array of
  ** input registers at regContent. For this reason it must be executed 
  ** before any MakeRecord instructions used to create covering index
  ** records.  */
  regRec = sqlite4GetTempReg(pParse);
  if( IsKvstore(pTab) ){
    sqlite4VdbeAddOp2(v, OP_SCopy, regContent+1, regRec);
    sqlite4VdbeAddOp1(v, OP_ToBlob, regRec);
  }else{
    sqlite4VdbeAddOp3(v, OP_MakeRecord, regContent, pTab->nCol, regRec);
    sqlite4TableAffinityStr(v, pTab);
    sqlite4ExprCacheAffinityChange(pParse, regContent, pTab->nCol);
  }
  regCover = sqlite4GetTempReg(pParse);

  /* Write the entry to each index. */
  for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
    assert( pIdx->eIndexType!=SQLITE4_INDEX_PRIMARYKEY || aRegIdx[i] );
    if( pIdx->eIndexType==SQLITE4_INDEX_FTS5 ){
      int iPK;

  sqlite4VdbeAddOp3(v, OP_Null, 0, regNew, regNew+pTab->nCol-1);
  for(i=0; i<pTab->nCol; i++){
    j = aXRef[i];
    if( j>=0 ){
      sqlite4ExprCode(pParse, pChanges->a[j].pExpr, regNew+i);
    }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask&(1<<i)) ){
      /* This branch loads the value of a column that will not be changed 
       ** into a register. This is done if there are no BEFORE triggers, or
       ** if there are one or more BEFORE triggers that use this value via
       ** a new.* reference in a trigger program.
       */
      testcase( i==31 );
      testcase( i==32 );
      sqlite4VdbeAddOp3(v, OP_Column, iCur+iPk, i, regNew+i);
      sqlite4ColumnDefault(v, pTab, i, regNew+i);
    }
  }
  if( bImplicitPk ){
    sqlite4VdbeAddOp2(v, OP_Rowid, iCur+iPk, regNew-1);
  }

  /* Fire any BEFORE UPDATE triggers. This happens before constraints are
................................................................................
    /* If it did not delete it, the row-trigger may still have modified 
    ** some of the columns of the row being updated. Load the values for 
    ** all columns not modified by the update statement into their 
    ** registers in case this has happened.
    */
    for(i=0; i<pTab->nCol; i++){
      if( aXRef[i]<0 ){
        sqlite4VdbeAddOp3(v, OP_Column, iCur+iPk, i, regNew+i);
        sqlite4ColumnDefault(v, pTab, i, regNew+i);
      }
    }
  }

  if( !isView ){
    int j1;                       /* Address of jump instruction */

  sqlite4VdbeAddOp3(v, OP_Null, 0, regNew, regNew+pTab->nCol-1);
  for(i=0; i<pTab->nCol; i++){
    j = aXRef[i];
    if( j>=0 ){
      sqlite4ExprCode(pParse, pChanges->a[j].pExpr, regNew+i);
    }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask&(1<<i)) ){
      /* This branch loads the value of a column that will not be changed 
      ** into a register. This is done if there are no BEFORE triggers, or
      ** if there are one or more BEFORE triggers that use this value via
      ** a new.* reference in a trigger program.
      */
      testcase( i==31 );
      testcase( i==32 );
      sqlite4ExprCodeGetColumnOfTable(v, pTab, iCur+iPk, i, regNew+i);

    }
  }
  if( bImplicitPk ){
    sqlite4VdbeAddOp2(v, OP_Rowid, iCur+iPk, regNew-1);
  }

  /* Fire any BEFORE UPDATE triggers. This happens before constraints are
................................................................................
    /* If it did not delete it, the row-trigger may still have modified 
    ** some of the columns of the row being updated. Load the values for 
    ** all columns not modified by the update statement into their 
    ** registers in case this has happened.
    */
    for(i=0; i<pTab->nCol; i++){
      if( aXRef[i]<0 ){
        sqlite4ExprCodeGetColumnOfTable(v, pTab, iCur+iPk, i, regNew+i);

      }
    }
  }

  if( !isView ){
    int j1;                       /* Address of jump instruction */

case OP_IsUnique: {        /* jump, in3 */
  VdbeCursor *pC;
  Mem *pProbe;
  Mem *pOut;
  int iOut;
  int nShort;
  int dir;

  u64 dummy;

  KVByteArray const *aKey;        /* Key read from cursor */
  KVSize nKey;                    /* Size of aKey in bytes */

  assert( pOp->p4type==P4_INT32 );

  pProbe = &aMem[pOp->p3];
  pC = p->apCsr[pOp->p1];
  pC->rowChnged = 1;
  pOut = (pOp->p4.i==0 ? 0 : &aMem[pOp->p4.i]);

  assert( pOut==0 || (pOut->flags & MEM_Blob) );


  nShort = sqlite4VdbeShortKey((u8 *)pProbe->z, pProbe->n, 
      pC->pKeyInfo->nField - pC->pKeyInfo->nPK, 0
  );
  assert( nShort<=pProbe->n );
  assert( (nShort==pProbe->n)==(pC->pKeyInfo->nPK==0) );



  dir = (pC->pKeyInfo->nPK==0 ? 0 : 1);


  rc = sqlite4KVCursorSeek(pC->pKVCur, (u8 *)pProbe->z, nShort, dir);

  if( rc==SQLITE4_OK && pOut ){
    sqlite4VdbeMemCopy(pOut, pProbe);
  }else if( rc==SQLITE4_NOTFOUND ){
    rc = SQLITE4_OK;
    pc = pOp->p2-1;
  }else if( rc==SQLITE4_INEXACT ){
    assert( nShort<pProbe->n );

    rc = sqlite4KVCursorKey(pC->pKVCur, &aKey, &nKey);
    if( rc==SQLITE4_OK ){
      if( nKey<nShort || memcmp(pProbe->z, aKey, nShort) ){
        pc = pOp->p2-1;
      }else if( pOut ){
        iOut = sqlite4GetVarint64((u8 *)pOut->z, pOut->n, &dummy);
        rc = sqlite4VdbeMemGrow(pOut, iOut+(nKey - nShort), 1);

case OP_IsUnique: {        /* jump, in3 */
  VdbeCursor *pC;
  Mem *pProbe;
  Mem *pOut;
  int iOut;
  int nShort;
  int dir;
  int bPk;
  u64 dummy;

  KVByteArray const *aKey;        /* Key read from cursor */
  KVSize nKey;                    /* Size of aKey in bytes */

  assert( pOp->p4type==P4_INT32 );

  pProbe = &aMem[pOp->p3];
  pC = p->apCsr[pOp->p1];
  pC->rowChnged = 1;
  pOut = (pOp->p4.i==0 ? 0 : &aMem[pOp->p4.i]);
  bPk = (pC->pKeyInfo->nPK==0);
  assert( pOut==0 || (pOut->flags & MEM_Blob) || bPk );

  if( bPk==0 ){
    nShort = sqlite4VdbeShortKey((u8 *)pProbe->z, pProbe->n, 
        pC->pKeyInfo->nField - pC->pKeyInfo->nPK, 0
        );
    assert( nShort<=pProbe->n );
    assert( (nShort==pProbe->n)==(pC->pKeyInfo->nPK==0) );
  }else{
    nShort = pProbe->n;
  }


  dir = !bPk;      /* "dir = (bPk ? 0 : 1);" */
  rc = sqlite4KVCursorSeek(pC->pKVCur, (u8 *)pProbe->z, nShort, dir);

  if( rc==SQLITE4_OK && pOut ){
    sqlite4VdbeMemCopy(pOut, pProbe);
  }else if( rc==SQLITE4_NOTFOUND ){
    rc = SQLITE4_OK;
    pc = pOp->p2-1;
  }else if( rc==SQLITE4_INEXACT ){
    assert( nShort<pProbe->n );
    assert( bPk==0 );
    rc = sqlite4KVCursorKey(pC->pKVCur, &aKey, &nKey);
    if( rc==SQLITE4_OK ){
      if( nKey<nShort || memcmp(pProbe->z, aKey, nShort) ){
        pc = pOp->p2-1;
      }else if( pOut ){
        iOut = sqlite4GetVarint64((u8 *)pOut->z, pOut->n, &dummy);
        rc = sqlite4VdbeMemGrow(pOut, iOut+(nKey - nShort), 1);

# 2013 Jul 31
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix kvstore2


do_execsql_test 1.0 { PRAGMA writable_schema = 1; } {}

do_catchsql_test 1.1 {
  INSERT INTO sqlite_kvstore VALUES(NULL, 'helloworld');
} {1 {sqlite_kvstore.key may not be NULL}}

do_execsql_test 1.2 {
  INSERT INTO sqlite_kvstore VALUES('abc', 'helloworld');
  SELECT count(*) FROM sqlite_kvstore;
} {1}

do_execsql_test 1.3 {
  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
} {abc helloworld}

do_execsql_test 1.4 {
  SELECT quote(key), quote(value) FROM sqlite_kvstore;
} {x'616263' x'68656c6c6f776f726c64'}

do_catchsql_test 1.5 {
  INSERT INTO sqlite_kvstore VALUES('abc', 'def');
} {1 {PRIMARY KEY must be unique}}

do_execsql_test 1.6 {
  REPLACE INTO sqlite_kvstore VALUES('abc', 'def');
  SELECT cast(key AS text), cast(value AS text) FROM sqlite_kvstore;
} {abc def}

do_execsql_test 1.7 {
  INSERT INTO sqlite_kvstore VALUES(123, 456.789);
  SELECT cast(key AS text), cast(value AS text) FROM sqlite_kvstore;
} {123 456.789 abc def}

do_execsql_test 1.8 {
  SELECT quote(key), quote(value) FROM sqlite_kvstore;
} {x'313233' x'3435362e373839' x'616263' x'646566'}

do_execsql_test 1.9 {
  UPDATE sqlite_kvstore SET value = x'AABBCC' WHERE key = x'616263';
  SELECT quote(key), quote(value) FROM sqlite_kvstore;
} {x'313233' x'3435362e373839' x'616263' x'aabbcc'}

do_execsql_test 1.10 {
  UPDATE sqlite_kvstore SET value = 111;
  SELECT quote(key), quote(value) FROM sqlite_kvstore;
} {x'313233' x'313131' x'616263' x'313131'}

do_execsql_test 1.11 {
  DELETE FROM sqlite_kvstore WHERE key = x'313233';
  SELECT quote(key), quote(value) FROM sqlite_kvstore;
} {x'616263' x'313131'}

do_execsql_test 1.12 {
  DELETE FROM sqlite_kvstore;
  SELECT quote(key), quote(value) FROM sqlite_kvstore;
} {}


finish_test

  func.test func2.test func3.test 
  fuzz.test fuzz2.test 
  in.test in2.test in3.test in4.test
  index.test index2.test index3.test index4.test 
  insert.test insert2.test insert3.test insert5.test
  join.test join2.test join3.test join4.test join5.test join6.test
  keyword1.test
  kvstore.test
  laststmtchanges.test
  limit.test
  like.test like2.test
  main.test
  manydb.test
  misc5.test misc6.test
  misuse.test

  func.test func2.test func3.test 
  fuzz.test fuzz2.test 
  in.test in2.test in3.test in4.test
  index.test index2.test index3.test index4.test 
  insert.test insert2.test insert3.test insert5.test
  join.test join2.test join3.test join4.test join5.test join6.test
  keyword1.test
  kvstore.test kvstore2.test
  laststmtchanges.test
  limit.test
  like.test like2.test
  main.test
  manydb.test
  misc5.test misc6.test
  misuse.test