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.211 2005/07/08 18:25:26 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

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

**
** The first form is handled by creating a set holding the list
** of allowed values.  The second form causes the SELECT to generate 
** a temporary table.
*/
#ifndef SQLITE_OMIT_SUBQUERY
void sqlite3CodeSubselect(Parse *pParse, Expr *pExpr){
  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:
  **
  **    *  The right-hand side is a correlated subquery
  **    *  The right-hand side is an expression list containing variables
  **    *  We are inside a trigger

  **
  ** If all of the above are false, then we can run this code just once
  ** save the results, and reuse the same result on subsequent invocations.
  */
  if( !ExprHasAnyProperty(pExpr, EP_VarSelect) && !pParse->trigStack ){
    int mem = pParse->nMem++;
    sqlite3VdbeAddOp(v, OP_MemLoad, mem, 0);

    testAddr = sqlite3VdbeAddOp(v, OP_If, 0, 0);
    assert( testAddr>0 );
    sqlite3VdbeAddOp(v, OP_Integer, 1, 0);
    sqlite3VdbeAddOp(v, OP_MemStore, mem, 1);
  }

  if( pExpr->pSelect ){
    sqlite3VdbeAddOp(v, OP_AggContextPush, 0, 0);
  }

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

      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...
      ** statement returns a column value, then the affinity of that
      ** column is used to build the index keys. If both 'x' and the
      ** SELECT... statement are columns, then numeric affinity is used

        **
	** For each expression, build an index key from the evaluation and
        ** store it in the temporary table. If <expr> is a column, then use
        ** that columns affinity when building index keys. If <expr> is not
        ** a column, use numeric affinity.
        */
        int i;
        ExprList *pList = pExpr->pList;
        struct ExprList_item *pItem;

        if( !affinity ){
          affinity = SQLITE_AFF_NUMERIC;
        }
        keyInfo.aColl[0] = pExpr->pLeft->pColl;

        /* Loop through each expression in <exprlist>. */
        for(i=pList->nExpr, pItem=pList->a; i>0; i--, pItem++){
          Expr *pE2 = pItem->pExpr;

          /* If the expression is not constant then we will need to
          ** disable the test that was generated above that makes sure
          ** this code only executes once.  Because for a non-constant
          ** expression we need to rerun this code each time.
          */
          if( testAddr>=0 && !sqlite3ExprIsConstant(pE2) ){
            VdbeOp *aOp = sqlite3VdbeGetOp(v, testAddr-1);

            int i;
            for(i=0; i<4; i++){
              aOp[i].opcode = OP_Noop;
            }
            testAddr = 0;
          }

          /* Evaluate the expression and insert it into the temp table */
          sqlite3ExprCode(pParse, pE2);
          sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, &affinity, 1);
          sqlite3VdbeAddOp(v, OP_IdxInsert, pExpr->iTable, 0);
        }

      break;
    }
  }

  if( pExpr->pSelect ){
    sqlite3VdbeAddOp(v, OP_AggContextPop, 0, 0);
  }
  if( testAddr ){
    sqlite3VdbeChangeP2(v, testAddr, sqlite3VdbeCurrentAddr(v));
  }
  return;
}
#endif /* SQLITE_OMIT_SUBQUERY */

/*
** Generate an instruction that will put the integer describe by

# 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.14 2005/07/08 18:25:26 drh 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 {

  execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) OR b=512 ORDER BY a}
} {1 2 6 7 8 9 10}
do_test in-2.5 {
  execsql {SELECT a+100*(b IN (8,16,24)) FROM t1 ORDER BY b}
} {1 2 103 104 5 6 7 8 9 10}

do_test in-2.6 {
  execsql {SELECT a FROM t1 WHERE b IN (b+8,64)}
} {6}

do_test in-2.7 {
  execsql {SELECT a FROM t1 WHERE b IN (max(5,10,b),20)}


} {4 5 6 7 8 9 10}
do_test in-2.8 {
  execsql {SELECT a FROM t1 WHERE b IN (8*2,64/2) ORDER BY b}
} {4 5}
do_test in-2.9 {
  execsql {SELECT a FROM t1 WHERE b IN (max(5,10),20)}
} {}

do_test in-2.10 {
  execsql {SELECT a FROM t1 WHERE min(0,b IN (a,30))}
} {}

do_test in-2.11 {
  set v [catch {execsql {SELECT a FROM t1 WHERE c IN (10,20)}} msg]
  lappend v $msg
} {1 {no such column: c}}

# Testing the IN operator where the right-hand side is a SELECT
#