**    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.394 2008/09/17 00:13:12 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

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

  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

................................................................................
**     (SELECT a FROM b)          -- subquery
**     EXISTS (SELECT a FROM b)   -- EXISTS subquery
**     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>.
      **
      ** If the 'x' expression is a column value, or the SELECT...
................................................................................
      ** column is used to build the index keys. If both 'x' and the
      ** SELECT... statement are columns, then numeric affinity is used
      ** if either column has NUMERIC or INTEGER affinity. If neither
      ** 'x' nor the SELECT... statement are columns, then numeric affinity
      ** is used.
      */
      pExpr->iTable = pParse->nTab++;
      addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pExpr->iTable, 1);
      memset(&keyInfo, 0, sizeof(keyInfo));
      keyInfo.nField = 1;

      if( pExpr->pSelect ){
        /* Case 1:     expr IN (SELECT ...)
        **
        ** Generate code to write the results of the select into the temporary
        ** table allocated and opened above.
        */
        SelectDest dest;
        ExprList *pEList;


        sqlite3SelectDestInit(&dest, SRT_Set, pExpr->iTable);
        dest.affinity = (int)affinity;
        assert( (pExpr->iTable&0x0000FFFF)==pExpr->iTable );
        if( sqlite3Select(pParse, pExpr->pSelect, &dest) ){
          return;
        }
        pEList = pExpr->pSelect->pEList;
................................................................................
          }

          /* Evaluate the expression and insert it into the temp table */
          pParse->disableColCache++;
          r3 = sqlite3ExprCodeTarget(pParse, pE2, r1);
          assert( pParse->disableColCache>0 );
          pParse->disableColCache--;






          sqlite3VdbeAddOp4(v, OP_MakeRecord, r3, 1, r2, &affinity, 1);
          sqlite3ExprCacheAffinityChange(pParse, r3, 1);
          sqlite3VdbeAddOp2(v, OP_IdxInsert, pExpr->iTable, r2);

        }
        sqlite3ReleaseTempReg(pParse, r1);
        sqlite3ReleaseTempReg(pParse, r2);
      }

      sqlite3VdbeChangeP4(v, addr, (void *)&keyInfo, P4_KEYINFO);

      break;
    }

    case TK_EXISTS:
    case TK_SELECT: {
      /* This has to be a scalar SELECT.  Generate code to put the
      ** value of this select in a memory cell and record the number
................................................................................
    }
#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;
................................................................................
                             nullRecord, P4_STATIC);
          j4 = sqlite3VdbeAddOp3(v, OP_Found, pExpr->iTable, 0, rMayHaveNull);
          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);

**    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.395 2008/10/02 13:50:56 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

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

  if( eType==0 ){
    int rMayHaveNull = 0;
    eType = IN_INDEX_EPH;
    if( prNotFound ){
      *prNotFound = rMayHaveNull = ++pParse->nMem;
    }else if( pX->pLeft->iColumn<0 && pX->pSelect==0 ){
      eType = IN_INDEX_ROWID;
    }
    sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);

  }else{
    pX->iTable = iTab;
  }
  return eType;
}
#endif

................................................................................
**     (SELECT a FROM b)          -- subquery
**     EXISTS (SELECT a FROM b)   -- EXISTS subquery
**     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.
**
** If parameter isRowid is non-zero, then expression pExpr is guaranteed
** to be of the form "<rowid> IN (?, ?, ?)", where <rowid> is a reference
** to some integer key column of a table B-Tree. In this case, use an
** intkey B-Tree to store the set of IN(...) values instead of the usual
** (slower) variable length keys B-Tree.
*/
#ifndef SQLITE_OMIT_SUBQUERY
void sqlite3CodeSubselect(
  Parse *pParse, 
  Expr *pExpr, 
  int rMayHaveNull,
  int isRowid
){
  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 */
      Expr *pLeft = pExpr->pLeft;

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

      affinity = sqlite3ExprAffinity(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>.
      **
      ** If the 'x' expression is a column value, or the SELECT...
................................................................................
      ** column is used to build the index keys. If both 'x' and the
      ** SELECT... statement are columns, then numeric affinity is used
      ** if either column has NUMERIC or INTEGER affinity. If neither
      ** 'x' nor the SELECT... statement are columns, then numeric affinity
      ** is used.
      */
      pExpr->iTable = pParse->nTab++;
      addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pExpr->iTable, !isRowid);
      memset(&keyInfo, 0, sizeof(keyInfo));
      keyInfo.nField = 1;

      if( pExpr->pSelect ){
        /* Case 1:     expr IN (SELECT ...)
        **
        ** Generate code to write the results of the select into the temporary
        ** table allocated and opened above.
        */
        SelectDest dest;
        ExprList *pEList;

        assert( !isRowid );
        sqlite3SelectDestInit(&dest, SRT_Set, pExpr->iTable);
        dest.affinity = (int)affinity;
        assert( (pExpr->iTable&0x0000FFFF)==pExpr->iTable );
        if( sqlite3Select(pParse, pExpr->pSelect, &dest) ){
          return;
        }
        pEList = pExpr->pSelect->pEList;
................................................................................
          }

          /* Evaluate the expression and insert it into the temp table */
          pParse->disableColCache++;
          r3 = sqlite3ExprCodeTarget(pParse, pE2, r1);
          assert( pParse->disableColCache>0 );
          pParse->disableColCache--;

          if( isRowid ){
            sqlite3VdbeAddOp2(v, OP_Null, 0, r2);
            sqlite3VdbeAddOp2(v, OP_MustBeInt, r3, sqlite3VdbeCurrentAddr(v)+2);
            sqlite3VdbeAddOp3(v, OP_Insert, pExpr->iTable, r2, r3);
          }else{
            sqlite3VdbeAddOp4(v, OP_MakeRecord, r3, 1, r2, &affinity, 1);
            sqlite3ExprCacheAffinityChange(pParse, r3, 1);
            sqlite3VdbeAddOp2(v, OP_IdxInsert, pExpr->iTable, r2);
          }
        }
        sqlite3ReleaseTempReg(pParse, r1);
        sqlite3ReleaseTempReg(pParse, r2);
      }
      if( !isRowid ){
        sqlite3VdbeChangeP4(v, addr, (void *)&keyInfo, P4_KEYINFO);
      }
      break;
    }

    case TK_EXISTS:
    case TK_SELECT: {
      /* This has to be a scalar SELECT.  Generate code to put the
      ** value of this select in a memory cell and record the number
................................................................................
    }
#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, 0);
      }
      inReg = pExpr->iColumn;
      break;
    }
    case TK_IN: {
      int rNotFound = 0;
      int rMayHaveNull = 0;
................................................................................
                             nullRecord, P4_STATIC);
          j4 = sqlite3VdbeAddOp3(v, OP_Found, pExpr->iTable, 0, rMayHaveNull);
          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);

**    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.772 2008/09/12 16:03:48 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build
................................................................................
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);
void sqlite3SelectPrep(Parse*, Select*, NameContext*);
int sqlite3ResolveExprNames(NameContext*, Expr*);
void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
void sqlite3ColumnDefault(Vdbe *, Table *, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);

**    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.773 2008/10/02 13:50:56 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build
................................................................................
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);
void sqlite3SelectPrep(Parse*, Select*, NameContext*);
int sqlite3ResolveExprNames(NameContext*, Expr*);
void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
void sqlite3ColumnDefault(Vdbe *, Table *, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);

# 2008 September 1
#
# 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.
#
#***********************************************************************
#
# $Id: in4.test,v 1.1 2008/10/02 13:50:56 danielk1977 Exp $

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

do_test in4-1.1 {
  execsql {
    CREATE TABLE t1(a, b);
    CREATE INDEX i1 ON t1(a);
  }
} {}
do_test in4-1.2 {
  execsql {
    SELECT * FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc');
  }
} {}
do_test in4-1.3 {
  execsql {
    INSERT INTO t1 VALUES('aaa', 1);
    INSERT INTO t1 VALUES('ddd', 2);
    INSERT INTO t1 VALUES('ccc', 3);
    INSERT INTO t1 VALUES('eee', 4);
    SELECT b FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc');
  }
} {1 3}
do_test in4-1.4 {
  execsql {
    SELECT a FROM t1 WHERE rowid IN (1, 3);
  }
} {aaa ccc}
do_test in4-1.5 {
  execsql {
    SELECT a FROM t1 WHERE rowid IN ();
  }
} {}
do_test in4-1.6 {
  execsql {
    SELECT a FROM t1 WHERE a IN ('ddd');
  }
} {ddd}

do_test in4-2.1 {
  execsql {
    CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT);
    INSERT INTO t2 VALUES(-1, '-one');
    INSERT INTO t2 VALUES(0, 'zero');
    INSERT INTO t2 VALUES(1, 'one');
    INSERT INTO t2 VALUES(2, 'two');
    INSERT INTO t2 VALUES(3, 'three');
  }
} {}

do_test in4-2.2 {
  execsql { SELECT b FROM t2 WHERE a IN (0, 2) }
} {zero two}

do_test in4-2.3 {
  execsql { SELECT b FROM t2 WHERE a IN (2, 0) }
} {zero two}

do_test in4-2.4 {
  execsql { SELECT b FROM t2 WHERE a IN (2, -1) }
} {-one two}

do_test in4-2.5 {
  execsql { SELECT b FROM t2 WHERE a IN (NULL, 3) }
} {three}

do_test in4-2.6 {
  execsql { SELECT b FROM t2 WHERE a IN (1.0, 2.1) }
} {one}

do_test in4-2.7 {
  execsql { SELECT b FROM t2 WHERE a IN ('1', '2') }
} {one two}

do_test in4-2.8 {
  execsql { SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2') }
} {two}

finish_test