SQLite

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.377 2008/06/26 18:04:03 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**

**                    populated epheremal table.
**
** An existing structure may only be used if the SELECT is of the simple
** form:
**
**     SELECT <column> FROM <table>
**
** If prNotFound parameter is 0, then the structure 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
** is unique by virtue of a constraint or implicit index.
**
** If the prNotFound parameter is not 0, then the structure 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 structure is being used for set membership tests, the user



** 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 a chance that the structure may 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 structure 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 structure does not remain constant
** for the duration of the query (i.e. the set is a correlated sub-select), 
** the value of the allocated register is reset to NULL each time the 
** structure is repopulated. 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 SQLITE_OMIT_SUBQUERY
int sqlite3FindInIndex(Parse *pParse, Expr *pX, int *prNotFound){
  Select *p;
  int eType = 0;
  int iTab = pParse->nTab++;
  int mustBeUnique = !prNotFound;

  /* The follwing if(...) expression is true if the SELECT is of the 
  ** simple form:
  **
  **     SELECT <column> FROM <table>
  **
  ** If this is the case, it may be possible to use an existing table

          sqlite3VdbeAddOp2(v, OP_SetNumColumns, 0, pIdx->nColumn);
          sqlite3VdbeAddOp4(v, OP_OpenRead, iTab, pIdx->tnum, iDb,
                               pKey,P4_KEYINFO_HANDOFF);
          VdbeComment((v, "%s", pIdx->zName));
          eType = IN_INDEX_INDEX;

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

  if( eType==0 ){
    int rMayHaveNull = 0;
    if( prNotFound ){
      *prNotFound = rMayHaveNull = ++pParse->nMem;
    }
    sqlite3CodeSubselect(pParse, pX, rMayHaveNull);
    eType = IN_INDEX_EPH;
  }else{
    pX->iTable = iTab;
  }
  return eType;
}
#endif

**     x IN (4,5,11)              -- IN operator with list on right-hand side
**     x IN (SELECT a FROM b)     -- IN operator with subquery on the right
**
** The pExpr parameter describes the expression that contains the IN
** operator or subquery.
*/
#ifndef SQLITE_OMIT_SUBQUERY
void sqlite3CodeSubselect(Parse *pParse, Expr *pExpr, int rMayHaveNull){
  int testAddr = 0;                       /* One-time test address */
  Vdbe *v = sqlite3GetVdbe(pParse);
  if( v==0 ) return;


  /* This code must be run in its entirety every time it is encountered
  ** if any of the following is true:

  }

  switch( pExpr->op ){
    case TK_IN: {
      char affinity;
      KeyInfo keyInfo;
      int addr;        /* Address of OP_OpenEphemeral instruction */

      if( rMayHaveNull ){
        sqlite3VdbeAddOp2(v, OP_Null, 0, rMayHaveNull);
      }

      affinity = sqlite3ExprAffinity(pExpr->pLeft);

      /* Whether this is an 'x IN(SELECT...)' or an 'x IN(<exprlist>)'
      ** expression it is handled the same way. A virtual table is 
      ** filled with single-field index keys representing the results
      ** from the SELECT or the <exprlist>.

    }
#ifndef SQLITE_OMIT_SUBQUERY
    case TK_EXISTS:
    case TK_SELECT: {
      testcase( op==TK_EXISTS );
      testcase( op==TK_SELECT );
      if( pExpr->iColumn==0 ){
        sqlite3CodeSubselect(pParse, pExpr, 0);
      }
      inReg = pExpr->iColumn;
      break;
    }
    case TK_IN: {
      int rNotFound = 0;
      int rMayHaveNull = 0;
      int j1, j2, j3, j4, j5;
      char affinity;
      int eType;

      eType = sqlite3FindInIndex(pParse, pExpr, &rMayHaveNull);
      if( rMayHaveNull ){
        rNotFound = ++pParse->nMem;
      }

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

      sqlite3VdbeJumpHere(v, j1);
      if( eType==IN_INDEX_ROWID ){
        j3 = sqlite3VdbeAddOp1(v, OP_MustBeInt, r1);
        j4 = sqlite3VdbeAddOp3(v, OP_NotExists, pExpr->iTable, 0, r1);
        j5 = sqlite3VdbeAddOp0(v, OP_Goto);
        sqlite3VdbeJumpHere(v, j3);
        sqlite3VdbeJumpHere(v, j4);
        sqlite3VdbeAddOp2(v, OP_Integer, 0, target);
      }else{
        r2 = regFree2 = sqlite3GetTempReg(pParse);

        /* Create a record and test for set membership. If the set contains
        ** the value, then jump to the end of the test code. The target
        ** register still contains the true (1) value written to it earlier.
        */
        sqlite3VdbeAddOp4(v, OP_MakeRecord, r1, 1, r2, &affinity, 1);
        sqlite3ExprCacheAffinityChange(pParse, r1, 1);
        j5 = sqlite3VdbeAddOp3(v, OP_Found, pExpr->iTable, 0, r2);

        /* If the set membership test fails, then the result of the 
        ** "x IN (...)" expression must be either 0 or NULL. If the set
        ** contains no NULL values, then the result is 0. If the set 
        ** contains one or more NULL values, then the result of the
        ** expression is also NULL.
        */
        if( rNotFound==0 ){
          /* This branch runs if it is known at compile time (now) that 
          ** the set contains no NULL values. This happens as the result
          ** of a "NOT NULL" constraint in the database schema. No need
          ** to test the data structure at runtime in this case.
          */
          sqlite3VdbeAddOp2(v, OP_Integer, 0, target);
        }else{
          /* This block populates the rNotFound register with either NULL
          ** or 0 (an integer value). If the data structure contains one
          ** or more NULLs, then set rNotFound to NULL. Otherwise, set it
          ** to 0. If register rMayHaveNull is already set to some value
          ** other than NULL, then the test has already been run and 
          ** rNotFound is already populated.
          */
          j3 = sqlite3VdbeAddOp1(v, OP_NotNull, rMayHaveNull);
          sqlite3VdbeAddOp2(v, OP_Null, 0, rNotFound);
          sqlite3VdbeAddOp2(v, OP_Integer, 1, rMayHaveNull);
	  sqlite3VdbeAddOp4(v, OP_MakeRecord, rNotFound, 1, r2, 0, 1);
          j4 = sqlite3VdbeAddOp3(v, OP_Found, pExpr->iTable, 0, r2);
          sqlite3VdbeAddOp2(v, OP_Integer, 0, rNotFound);
          sqlite3VdbeJumpHere(v, j4);
          sqlite3VdbeJumpHere(v, j3);

          /* Copy the value of register rNotFound (which is either NULL or 0)
	  ** into the target register. This will be the result of the
          ** expression.
          */
          sqlite3VdbeAddOp2(v, OP_Copy, rNotFound, target);
        }
      }
      sqlite3VdbeJumpHere(v, j2);
      sqlite3VdbeJumpHere(v, j5);
      break;
    }
#endif
    /*
    **    x BETWEEN y AND z

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.437 2008/06/26 18:04:03 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.

    ** then there should be a single item on the stack.  Write this
    ** item into the set table with bogus data.
    */
    case SRT_Set: {
      int addr2;

      assert( nColumn==1 );

      p->affinity = sqlite3CompareAffinity(pEList->a[0].pExpr, pDest->affinity);
      if( pOrderBy ){
        /* At first glance you would think we could optimize out the
        ** ORDER BY in this case since the order of entries in the set
        ** does not matter.  But there might be a LIMIT clause, in which
        ** case the order does matter */
        pushOntoSorter(pParse, pOrderBy, p, regResult);
      }else{
        int r1 = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult, 1, r1, &p->affinity, 1);
        sqlite3ExprCacheAffinityChange(pParse, regResult, 1);
        sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1);
        sqlite3ReleaseTempReg(pParse, r1);
      }

      break;
    }

    /* If any row exist in the result set, record that fact and abort.
    */
    case SRT_Exists: {
      sqlite3VdbeAddOp2(v, OP_Integer, 1, iParm);

      sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, regRowid);
      sqlite3VdbeAddOp3(v, OP_Insert, iParm, regRow, regRowid);
      sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
      break;
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case SRT_Set: {

      assert( nColumn==1 );

      sqlite3VdbeAddOp4(v, OP_MakeRecord, regRow, 1, regRowid, &p->affinity, 1);
      sqlite3ExprCacheAffinityChange(pParse, regRow, 1);
      sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, regRowid);

      break;
    }
    case SRT_Mem: {
      assert( nColumn==1 );
      sqlite3ExprCodeMove(pParse, regRow, iParm, 1);
      /* The LIMIT clause will terminate the loop for us */
      break;

    /* If we are creating a set for an "expr IN (SELECT ...)" construct,
    ** then there should be a single item on the stack.  Write this
    ** item into the set table with bogus data.
    */
    case SRT_Set: {
      int addr2, r1;
      assert( pIn->nMem==1 );

      p->affinity = 
         sqlite3CompareAffinity(p->pEList->a[0].pExpr, pDest->affinity);
      r1 = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp4(v, OP_MakeRecord, pIn->iMem, 1, r1, &p->affinity, 1);
      sqlite3ExprCacheAffinityChange(pParse, pIn->iMem, 1);
      sqlite3VdbeAddOp2(v, OP_IdxInsert, pDest->iParm, r1);
      sqlite3ReleaseTempReg(pParse, r1);

      break;
    }

    /* If any row exist in the result set, record that fact and abort.
    */
    case SRT_Exists: {
      sqlite3VdbeAddOp2(v, OP_Integer, 1, pDest->iParm);

**
**     pDest->eDest    Result
**     ------------    -------------------------------------------
**     SRT_Callback    Invoke the callback for each row of the result.
**
**     SRT_Mem         Store first result in memory cell pDest->iParm
**
**     SRT_Set         Store results as keys of table pDest->iParm. 
**                     Apply the affinity pDest->affinity before storing them.
**
**     SRT_Union       Store results as a key in a temporary table pDest->iParm.
**
**     SRT_Except      Remove results from the temporary table pDest->iParm.
**
**     SRT_Table       Store results in temporary table pDest->iParm

/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.732 2008/06/26 18:04:03 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build

#define SRT_Discard      4  /* Do not save the results anywhere */

/* The ORDER BY clause is ignored for all of the above */
#define IgnorableOrderby(X) ((X->eDest)<=SRT_Discard)

#define SRT_Callback     5  /* Invoke a callback with each row of result */
#define SRT_Mem          6  /* Store result in a memory cell */
#define SRT_Set          7  /* Store results as keys in an index */
#define SRT_Table        8  /* Store result as data with an automatic rowid */
#define SRT_EphemTab     9  /* Create transient tab and store like SRT_Table */
#define SRT_Coroutine   10  /* Generate a single row of result */

/*
** A structure used to customize the behaviour of sqlite3Select(). See
** comments above sqlite3Select() for details.

void sqlite3RootPageMoved(Db*, int, int);
void sqlite3Reindex(Parse*, Token*, Token*);
void sqlite3AlterFunctions(sqlite3*);
void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);
int sqlite3GetToken(const unsigned char *, int *);
void sqlite3NestedParse(Parse*, const char*, ...);
void sqlite3ExpirePreparedStatements(sqlite3*);
void sqlite3CodeSubselect(Parse *, Expr *, int);
int sqlite3SelectResolve(Parse *, Select *, NameContext *);
void sqlite3ColumnDefault(Vdbe *, Table *, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
CollSeq *sqlite3GetCollSeq(sqlite3*, CollSeq *, const char *, int);
char sqlite3AffinityType(const Token*);
void sqlite3Analyze(Parse*, Token*, Token*);

  #define sqlite3BeginBenignMalloc()
  #define sqlite3EndBenignMalloc()
#endif

#define IN_INDEX_ROWID           1
#define IN_INDEX_EPH             2
#define IN_INDEX_INDEX           3
int sqlite3FindInIndex(Parse *, Expr *, int*);

#ifdef SQLITE_ENABLE_ATOMIC_WRITE
  int sqlite3JournalOpen(sqlite3_vfs *, const char *, sqlite3_file *, int, int);
  int sqlite3JournalSize(sqlite3_vfs *);
  int sqlite3JournalCreate(sqlite3_file *);
#else
  #define sqlite3JournalSize(pVfs) ((pVfs)->szOsFile)

**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.756 2008/06/26 18:04:03 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
** The following global variable is incremented every time a cursor

  pOut->u.i = a;
  MemSetTypeFlag(pOut, MEM_Int);
  break;
}

/* Opcode: AddImm  P1 P2 * * *
** 
** Add the constant P2 to the value in register P1.
** The result is always an integer.
**
** To force any register to be an integer, just add 0.
*/
case OP_AddImm: {            /* in1 */
  sqlite3VdbeMemIntegerify(pIn1);
  pIn1->u.i += pOp->p2;

** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is responsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.311 2008/06/26 18:04:03 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)

  }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);
    VdbeComment((v, "%.*s", pX->span.n, pX->span.z));
    if( pLevel->nIn==0 ){
      pLevel->nxt = sqlite3VdbeMakeLabel(v);
    }
    pLevel->nIn++;

# 2001 September 15
#
# 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 IN and BETWEEN operator.
#
# $Id: in.test,v 1.21 2008/06/26 18:04:03 danielk1977 Exp $

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

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {

  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
    );
  }
} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}


#------------------------------------------------------------------------
# The following tests check that NULL is handled correctly when it 
# appears as part of a set of values on the right-hand side of an
# IN or NOT IN operator.
#
# When it appears in such a set, NULL is handled as an "unknown value".
# If, because of the unknown value in the set, the result of the expression 
# cannot be determined, then it itself evaluates to NULL.
#

# Warm body test to demonstrate the principles being tested:
#
do_test in-13.1 {
  db nullvalue "null"
  execsql { SELECT 
    1 IN (NULL, 1, 2),     -- The value 1 is a member of the set, return true.
    3 IN (NULL, 1, 2),     -- Ambiguous, return NULL.
    1 NOT IN (NULL, 1, 2), -- The value 1 is a member of the set, return false.
    3 NOT IN (NULL, 1, 2)  -- Ambiguous, return NULL.
  }
} {1 null 0 null}

do_test in-13.2 {
  execsql { 
    CREATE TABLE t7(a, b, c NOT NULL);
    INSERT INTO t7 VALUES(1,    1, 1);
    INSERT INTO t7 VALUES(2,    2, 2);
    INSERT INTO t7 VALUES(3,    3, 3);
    INSERT INTO t7 VALUES(NULL, 4, 4);
    INSERT INTO t7 VALUES(NULL, 5, 5);
  }
} {}

do_test in-13.3 {
  execsql { SELECT 2 IN (SELECT a FROM t7) }
} {1}
do_test in-13.4 {
  execsql { SELECT 6 IN (SELECT a FROM t7) }
} {null}

do_test in-13.5 {
  execsql { SELECT 2 IN (SELECT b FROM t7) }
} {1}
do_test in-13.6 {
  execsql { SELECT 6 IN (SELECT b FROM t7) }
} {0}

do_test in-13.7 {
  execsql { SELECT 2 IN (SELECT c FROM t7) }
} {1}
do_test in-13.8 {
  execsql { SELECT 6 IN (SELECT c FROM t7) }
} {0}

do_test in-13.9 {
  execsql {
    SELECT
      2 NOT IN (SELECT a FROM t7),
      6 NOT IN (SELECT a FROM t7),
      2 NOT IN (SELECT b FROM t7),
      6 NOT IN (SELECT b FROM t7),
      2 NOT IN (SELECT c FROM t7),
      6 NOT IN (SELECT c FROM t7)
  } 
} {0 null 0 1 0 1}

do_test in-13.10 {
  execsql { 
    SELECT b IN (
      SELECT inside.a 
      FROM t7 AS inside 
      WHERE inside.b BETWEEN outside.b+1 AND outside.b+2
    )
    FROM t7 AS outside ORDER BY b;
  }
} {0 null null null 0}

do_test in-13.11 {
  execsql {
    SELECT b NOT IN (
      SELECT inside.a 
      FROM t7 AS inside 
      WHERE inside.b BETWEEN outside.b+1 AND outside.b+2
    )
    FROM t7 AS outside ORDER BY b;
  }
} {1 null null null 1}

do_test in-13.12 {
  execsql {
    CREATE INDEX i1 ON t7(a);
    CREATE INDEX i2 ON t7(b);
    CREATE INDEX i3 ON t7(c);
  }
  execsql {
    SELECT
      2 IN (SELECT a FROM t7),
      6 IN (SELECT a FROM t7),
      2 IN (SELECT b FROM t7),
      6 IN (SELECT b FROM t7),
      2 IN (SELECT c FROM t7),
      6 IN (SELECT c FROM t7)
  } 
} {1 null 1 0 1 0}

do_test in-13.13 {
  execsql {
    SELECT
      2 NOT IN (SELECT a FROM t7),
      6 NOT IN (SELECT a FROM t7),
      2 NOT IN (SELECT b FROM t7),
      6 NOT IN (SELECT b FROM t7),
      2 NOT IN (SELECT c FROM t7),
      6 NOT IN (SELECT c FROM t7)
  } 
} {0 null 0 1 0 1}

do_test in-13.14 {
  execsql {
    BEGIN TRANSACTION;
    CREATE TABLE a(id INTEGER);
    INSERT INTO a VALUES(1);
    INSERT INTO a VALUES(2);
    INSERT INTO a VALUES(3);
    CREATE TABLE b(id INTEGER);
    INSERT INTO b VALUES(NULL);
    INSERT INTO b VALUES(3);
    INSERT INTO b VALUES(4);
    INSERT INTO b VALUES(5);
    COMMIT;
    SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
  }
} {}
do_test in-13.14 {
  execsql {
    CREATE INDEX i5 ON b(id);
    SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
  }
} {}


do_test in-13.X {
  db nullvalue ""
} {}

finish_test

#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test that it is possible to have two open blob handles on a single
# blob object.
#
# $Id: incrblob2.test,v 1.7 2008/06/26 18:04:03 danielk1977 Exp $
#

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

ifcapable {!autovacuum || !pragma || !incrblob} {
  finish_test

do_test incrblob2-7.4 {
  expr {[sqlite3_memory_highwater]<(5 * 1024 * 1024)}
} {1}

do_test incrblob2-7.5 {
  close $h
} {}

#do_test incrblob2-8.5 {
  #execsql BEGIN
  #db eval {SELECT * FROM t2} {
  #execsql "DROP TABLE t2"
  #}
#} {}

finish_test