**    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.120 2004/05/16 22:55:28 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>





























static char exprAffinity(Expr *pExpr){
  if( pExpr->op==TK_AS ){
    return exprAffinity(pExpr->pLeft);
  }
  if( pExpr->op==TK_SELECT ){
    return exprAffinity(pExpr->pSelect->pEList->a[0].pExpr);
  }
  return pExpr->affinity;
}

/*
** Return the P1 value that should be used for a binary comparison
** opcode (OP_Eq, OP_Ge etc.) used to compare pExpr1 and pExpr2.
** If jumpIfNull is true, then set the low byte of the returned
** P1 value to tell the opcode to jump if either expression
** evaluates to NULL.
*/
int binaryCompareP1(Expr *pExpr1, Expr *pExpr2, int jumpIfNull){

  char aff1 = exprAffinity(pExpr1);
  char aff2 = exprAffinity(pExpr2);

  if( aff1 && aff2 ){
    /* Both sides of the comparison are columns. If one has numeric or
    ** integer affinity, use that. Otherwise use no affinity.
    */
    if( aff1==SQLITE_AFF_INTEGER || aff2==SQLITE_AFF_INTEGER ){
      aff1 = SQLITE_AFF_INTEGER;
    }else
    if( aff1==SQLITE_AFF_NUMERIC || aff2==SQLITE_AFF_NUMERIC ){
      aff1 = SQLITE_AFF_NUMERIC;
    }else{
      aff1 = SQLITE_AFF_NONE;
    }
























  }else if( !aff1 ){


    aff1 = aff2;
  }

























  return (((int)aff1)<<8)+(jumpIfNull?1:0);
}

/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
** is responsible for making sure the node eventually gets freed.
*/
................................................................................
      if( lookupName(pParse, pDb, pTable, pColumn, pSrcList, 0, pExpr) ){
        return 1;
      }
      break;
    }

    case TK_IN: {

      Vdbe *v = sqlite3GetVdbe(pParse);
      if( v==0 ) return 1;
      if( sqlite3ExprResolveIds(pParse, pSrcList, pEList, pExpr->pLeft) ){
        return 1;
      }


















      if( pExpr->pSelect ){
        /* Case 1:     expr IN (SELECT ...)
        **
        ** Generate code to write the results of the select into a temporary
        ** table.  The cursor number of the temporary table has already
        ** been put in iTable by sqlite3ExprResolveInSelect().
        */

        pExpr->iTable = pParse->nTab++;
        sqlite3VdbeAddOp(v, OP_OpenTemp, pExpr->iTable, 1);
        sqlite3Select(pParse, pExpr->pSelect, SRT_Set, pExpr->iTable, 0,0,0);
      }else if( pExpr->pList ){
        /* Case 2:     expr IN (exprlist)
        **
        ** Create a set to put the exprlist values in.  The Set id is stored
        ** in iTable.


        */
        int i, iSet;







        for(i=0; i<pExpr->pList->nExpr; i++){
          Expr *pE2 = pExpr->pList->a[i].pExpr;


          if( !sqlite3ExprIsConstant(pE2) ){
            sqlite3ErrorMsg(pParse,
              "right-hand side of IN operator must be constant");
            return 1;
          }
          if( sqlite3ExprCheck(pParse, pE2, 0, 0) ){
            return 1;
          }
        }
        iSet = pExpr->iTable = pParse->nSet++;
        for(i=0; i<pExpr->pList->nExpr; i++){
          Expr *pE2 = pExpr->pList->a[i].pExpr;
          switch( pE2->op ){
            case TK_FLOAT:
            case TK_INTEGER:
            case TK_STRING: {
              int addr;
              assert( pE2->token.z );
              addr = sqlite3VdbeOp3(v, OP_SetInsert, iSet, 0,
                                  pE2->token.z, pE2->token.n);
              sqlite3VdbeDequoteP3(v, addr);
              break;
            }
            default: {
              sqlite3ExprCode(pParse, pE2);

              sqlite3VdbeAddOp(v, OP_SetInsert, iSet, 0);
              break;
            }
          }

        }
      }
      break;
    }

    case TK_SELECT: {
      /* This has to be a scalar SELECT.  Generate code to put the
................................................................................
    case TK_NE:
    case TK_EQ: {
      int p1 = binaryCompareP1(pExpr->pLeft, pExpr->pRight, 0);
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3ExprCode(pParse, pExpr->pRight);
      sqlite3VdbeAddOp(v, op, p1, 0);
      break;
#if 0
      if( sqlite3ExprType(pExpr)==SQLITE_SO_TEXT ){
        op += 6;  /* Convert numeric opcodes to text opcodes */
      }
      /* Fall through into the next case */
#endif
    }
    case TK_AND:
    case TK_OR:
    case TK_PLUS:
    case TK_STAR:
    case TK_MINUS:
    case TK_REM:
................................................................................
    }
    case TK_SELECT: {
      sqlite3VdbeAddOp(v, OP_MemLoad, pExpr->iColumn, 0);
      break;
    }
    case TK_IN: {
      int addr;








      sqlite3VdbeAddOp(v, OP_Integer, 1, 0);




      sqlite3ExprCode(pParse, pExpr->pLeft);
      addr = sqlite3VdbeCurrentAddr(v);
      sqlite3VdbeAddOp(v, OP_NotNull, -1, addr+4);
      sqlite3VdbeAddOp(v, OP_Pop, 2, 0);
      sqlite3VdbeAddOp(v, OP_String, 0, 0);
      sqlite3VdbeAddOp(v, OP_Goto, 0, addr+6);
      if( pExpr->pSelect ){

        sqlite3VdbeAddOp(v, OP_Found, pExpr->iTable, addr+6);
      }else{
        sqlite3VdbeAddOp(v, OP_SetFound, pExpr->iTable, addr+6);
      }
      sqlite3VdbeAddOp(v, OP_AddImm, -1, 0);
      break;
    }
    case TK_BETWEEN: {
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
      sqlite3ExprCode(pParse, pExpr->pList->a[0].pExpr);
      sqlite3VdbeAddOp(v, OP_Ge, 0, 0);
................................................................................
    }
    case TK_ISNULL:
    case TK_NOTNULL: {
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3VdbeAddOp(v, op, 1, dest);
      break;
    }

    case TK_IN: {
      int addr;
      sqlite3ExprCode(pParse, pExpr->pLeft);
      addr = sqlite3VdbeCurrentAddr(v);
      sqlite3VdbeAddOp(v, OP_NotNull, -1, addr+3);
      sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
      sqlite3VdbeAddOp(v, OP_Goto, 0, jumpIfNull ? dest : addr+4);
................................................................................
      if( pExpr->pSelect ){
        sqlite3VdbeAddOp(v, OP_Found, pExpr->iTable, dest);
      }else{
        sqlite3VdbeAddOp(v, OP_SetFound, pExpr->iTable, dest);
      }
      break;
    }

    case TK_BETWEEN: {
      int addr;
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
      sqlite3ExprCode(pParse, pExpr->pList->a[0].pExpr);
      addr = sqlite3VdbeAddOp(v, OP_Lt, !jumpIfNull, 0);
      sqlite3ExprCode(pParse, pExpr->pList->a[1].pExpr);
................................................................................
    }
    case TK_ISNULL:
    case TK_NOTNULL: {
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3VdbeAddOp(v, op, 1, dest);
      break;
    }

    case TK_IN: {
      int addr;
      sqlite3ExprCode(pParse, pExpr->pLeft);
      addr = sqlite3VdbeCurrentAddr(v);
      sqlite3VdbeAddOp(v, OP_NotNull, -1, addr+3);
      sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
      sqlite3VdbeAddOp(v, OP_Goto, 0, jumpIfNull ? dest : addr+4);
................................................................................
      if( pExpr->pSelect ){
        sqlite3VdbeAddOp(v, OP_NotFound, pExpr->iTable, dest);
      }else{
        sqlite3VdbeAddOp(v, OP_SetNotFound, pExpr->iTable, dest);
      }
      break;
    }

    case TK_BETWEEN: {
      int addr;
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
      sqlite3ExprCode(pParse, pExpr->pList->a[0].pExpr);
      addr = sqlite3VdbeCurrentAddr(v);
      sqlite3VdbeAddOp(v, OP_Ge, !jumpIfNull, addr+3);

**    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.121 2004/05/17 10:48:58 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

char const *sqlite3AffinityString(char affinity){
  switch( affinity ){
    case SQLITE_AFF_INTEGER: return "i";
    case SQLITE_AFF_NUMERIC: return "n";
    case SQLITE_AFF_TEXT:    return "t";
    case SQLITE_AFF_NONE:    return "o";
    default:
      assert(0);
  }
}


/*
** Return the 'affinity' of the expression pExpr if any.
**
** If pExpr is a column, a reference to a column via an 'AS' alias,
** or a sub-select with a column as the return value, then the 
** affinity of that column is returned. Otherwise, 0x00 is returned,
** indicating no affinity for the expression.
**
** i.e. the WHERE clause expresssions in the following statements all
** have an affinity:
**
** CREATE TABLE t1(a);
** SELECT * FROM t1 WHERE a;
** SELECT a AS b FROM t1 WHERE b;
** SELECT * FROM t1 WHERE (select a from t1);
*/
static char exprAffinity(Expr *pExpr){
  if( pExpr->op==TK_AS ){
    return exprAffinity(pExpr->pLeft);
  }
  if( pExpr->op==TK_SELECT ){
    return exprAffinity(pExpr->pSelect->pEList->a[0].pExpr);
  }
  return pExpr->affinity;
}









char sqlite3CompareAffinity(Expr *pExpr, char aff2){
  char aff1 = exprAffinity(pExpr);


  if( aff1 && aff2 ){
    /* Both sides of the comparison are columns. If one has numeric or
    ** integer affinity, use that. Otherwise use no affinity.
    */
    if( aff1==SQLITE_AFF_INTEGER || aff2==SQLITE_AFF_INTEGER ){
      return SQLITE_AFF_INTEGER;

    }else if( aff1==SQLITE_AFF_NUMERIC || aff2==SQLITE_AFF_NUMERIC ){
      return SQLITE_AFF_NUMERIC;
    }else{
      return SQLITE_AFF_NONE;
    }
  }else if( !aff1 && !aff2 ){
    /* Neither side of the comparison is a column. Use numeric affinity
    ** for the comparison.
    */
    return SQLITE_AFF_NUMERIC;
  }else{
    /* One side is a column, the other is not. Use the columns affinity. */
    return (aff1 + aff2);
  }
}

static char comparisonAffinity(Expr *pExpr){
  char aff;
  assert( pExpr->op==TK_EQ || pExpr->op==TK_IN || pExpr->op==TK_LT ||
          pExpr->op==TK_GT || pExpr->op==TK_GE || pExpr->op==TK_LE ||
          pExpr->op==TK_NE );
  assert( pExpr->pLeft );
  aff = exprAffinity(pExpr->pLeft);
  if( pExpr->pRight ){
    aff = sqlite3CompareAffinity(pExpr->pRight, aff);
  }
  else if( pExpr->pSelect ){
    aff = sqlite3CompareAffinity(pExpr->pSelect->pEList->a[0].pExpr, aff);
  }
  else if( !aff ){
    aff = SQLITE_AFF_NUMERIC;
  }
  return aff;
}

/*
** pExpr is a comparison expression, eg. '=', '<', IN(...) etc.
** idx_affinity is the affinity of an indexed column. Return true
** if the index with affinity idx_affinity may be used to implement
** the comparison in pExpr.
*/
int sqlite3IndexAffinityOk(Expr *pExpr, char idx_affinity){
  char aff = comparisonAffinity(pExpr);
  return 
    (aff==SQLITE_AFF_NONE) ||
    (aff==SQLITE_AFF_NUMERIC && idx_affinity==SQLITE_AFF_INTEGER) ||
    (aff==SQLITE_AFF_INTEGER && idx_affinity==SQLITE_AFF_NUMERIC) ||
    (aff==idx_affinity);
}

/*
** Return the P1 value that should be used for a binary comparison
** opcode (OP_Eq, OP_Ge etc.) used to compare pExpr1 and pExpr2.
** If jumpIfNull is true, then set the low byte of the returned
** P1 value to tell the opcode to jump if either expression
** evaluates to NULL.
*/
static int binaryCompareP1(Expr *pExpr1, Expr *pExpr2, int jumpIfNull){
  char aff = exprAffinity(pExpr2);
  return (((int)sqlite3CompareAffinity(pExpr1, aff))<<8)+(jumpIfNull?1:0);
}

/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
** is responsible for making sure the node eventually gets freed.
*/
................................................................................
      if( lookupName(pParse, pDb, pTable, pColumn, pSrcList, 0, pExpr) ){
        return 1;
      }
      break;
    }

    case TK_IN: {
      char affinity;
      Vdbe *v = sqlite3GetVdbe(pParse);
      if( v==0 ) return 1;
      if( sqlite3ExprResolveIds(pParse, pSrcList, pEList, pExpr->pLeft) ){
        return 1;
      }
      affinity = exprAffinity(pExpr->pLeft);

      /* Whether this is an 'x IN(SELECT...)' or an 'x IN(<exprlist>)'
      ** expression it is handled the same way. A temporary 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
      ** 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++;
      sqlite3VdbeAddOp(v, OP_OpenTemp, pExpr->iTable, 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.

        */
        int iParm = pExpr->iTable +  (((int)affinity)<<16);
        assert( (pExpr->iTable&0x0000FFFF)==pExpr->iTable );

        sqlite3Select(pParse, pExpr->pSelect, SRT_Set, iParm, 0, 0, 0);
      }else if( pExpr->pList ){
        /* Case 2:     expr IN (exprlist)
        **
	** 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;
        char const *affStr;
        if( !affinity ){
          affinity = SQLITE_AFF_NUMERIC;
        }
        affStr = sqlite3AffinityString(affinity);

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

          /* Check that the expression is constant and valid. */
          if( !sqlite3ExprIsConstant(pE2) ){
            sqlite3ErrorMsg(pParse,
              "right-hand side of IN operator must be constant");
            return 1;
          }
          if( sqlite3ExprCheck(pParse, pE2, 0, 0) ){
            return 1;
          }

          /* Evaluate the expression and insert it into the temp table */














          sqlite3ExprCode(pParse, pE2);
          sqlite3VdbeOp3(v, OP_MakeKey, 1, 0, affStr, P3_STATIC);
          sqlite3VdbeAddOp(v, OP_String, 0, 0);



          sqlite3VdbeAddOp(v, OP_PutStrKey, pExpr->iTable, 0);
        }
      }
      break;
    }

    case TK_SELECT: {
      /* This has to be a scalar SELECT.  Generate code to put the
................................................................................
    case TK_NE:
    case TK_EQ: {
      int p1 = binaryCompareP1(pExpr->pLeft, pExpr->pRight, 0);
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3ExprCode(pParse, pExpr->pRight);
      sqlite3VdbeAddOp(v, op, p1, 0);
      break;






    }
    case TK_AND:
    case TK_OR:
    case TK_PLUS:
    case TK_STAR:
    case TK_MINUS:
    case TK_REM:
................................................................................
    }
    case TK_SELECT: {
      sqlite3VdbeAddOp(v, OP_MemLoad, pExpr->iColumn, 0);
      break;
    }
    case TK_IN: {
      int addr;
      char const *affStr;

      /* Figure out the affinity to use to create a key from the results
      ** of the expression. affinityStr stores a static string suitable for
      ** P3 of OP_MakeKey.
      */
      affStr = sqlite3AffinityString(comparisonAffinity(pExpr));

      sqlite3VdbeAddOp(v, OP_Integer, 1, 0);

      /* Code the <expr> from "<expr> IN (...)". The temporary table
      ** pExpr->iTable contains the values that make up the (...) set.
      */
      sqlite3ExprCode(pParse, pExpr->pLeft);
      addr = sqlite3VdbeCurrentAddr(v);
      sqlite3VdbeAddOp(v, OP_NotNull, -1, addr+4);            /* addr + 0 */
      sqlite3VdbeAddOp(v, OP_Pop, 2, 0);
      sqlite3VdbeAddOp(v, OP_String, 0, 0);
      sqlite3VdbeAddOp(v, OP_Goto, 0, addr+7);

      sqlite3VdbeOp3(v, OP_MakeKey, 1, 0, affStr, P3_STATIC); /* addr + 4 */
      sqlite3VdbeAddOp(v, OP_Found, pExpr->iTable, addr+7);

      sqlite3VdbeAddOp(v, OP_AddImm, -1, 0);                  /* addr + 6 */


      break;
    }
    case TK_BETWEEN: {
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
      sqlite3ExprCode(pParse, pExpr->pList->a[0].pExpr);
      sqlite3VdbeAddOp(v, OP_Ge, 0, 0);
................................................................................
    }
    case TK_ISNULL:
    case TK_NOTNULL: {
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3VdbeAddOp(v, op, 1, dest);
      break;
    }
#if 0
    case TK_IN: {
      int addr;
      sqlite3ExprCode(pParse, pExpr->pLeft);
      addr = sqlite3VdbeCurrentAddr(v);
      sqlite3VdbeAddOp(v, OP_NotNull, -1, addr+3);
      sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
      sqlite3VdbeAddOp(v, OP_Goto, 0, jumpIfNull ? dest : addr+4);
................................................................................
      if( pExpr->pSelect ){
        sqlite3VdbeAddOp(v, OP_Found, pExpr->iTable, dest);
      }else{
        sqlite3VdbeAddOp(v, OP_SetFound, pExpr->iTable, dest);
      }
      break;
    }
#endif
    case TK_BETWEEN: {
      int addr;
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
      sqlite3ExprCode(pParse, pExpr->pList->a[0].pExpr);
      addr = sqlite3VdbeAddOp(v, OP_Lt, !jumpIfNull, 0);
      sqlite3ExprCode(pParse, pExpr->pList->a[1].pExpr);
................................................................................
    }
    case TK_ISNULL:
    case TK_NOTNULL: {
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3VdbeAddOp(v, op, 1, dest);
      break;
    }
#if 0
    case TK_IN: {
      int addr;
      sqlite3ExprCode(pParse, pExpr->pLeft);
      addr = sqlite3VdbeCurrentAddr(v);
      sqlite3VdbeAddOp(v, OP_NotNull, -1, addr+3);
      sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
      sqlite3VdbeAddOp(v, OP_Goto, 0, jumpIfNull ? dest : addr+4);
................................................................................
      if( pExpr->pSelect ){
        sqlite3VdbeAddOp(v, OP_NotFound, pExpr->iTable, dest);
      }else{
        sqlite3VdbeAddOp(v, OP_SetNotFound, pExpr->iTable, dest);
      }
      break;
    }
#endif
    case TK_BETWEEN: {
      int addr;
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
      sqlite3ExprCode(pParse, pExpr->pList->a[0].pExpr);
      addr = sqlite3VdbeCurrentAddr(v);
      sqlite3VdbeAddOp(v, OP_Ge, !jumpIfNull, addr+3);

**    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 INSERT statements in SQLite.
**
** $Id: insert.c,v 1.99 2004/05/16 11:15:38 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:
................................................................................
**  'n'            NUMERIC
**  'i'            INTEGER
**  't'            TEXT
**  'o'            NONE
*/
void sqlite3IndexAffinityStr(Vdbe *v, Index *pIdx){
  if( !pIdx->zColAff ){
    /* The first time a column affinity string for a particular table is
    ** required, it is allocated and populated here. It is then stored as
    ** a member of the Table structure for subsequent use.
    **
    ** The column affinity string will eventually be deleted by
    ** sqliteDeleteIndex() when the Table structure itself is cleaned
    ** up.
    */
    int n;
    Table *pTab = pIdx->pTable;
    pIdx->zColAff = (char *)sqliteMalloc(pIdx->nColumn+1);
    if( !pIdx->zColAff ){
      return;

**    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 INSERT statements in SQLite.
**
** $Id: insert.c,v 1.100 2004/05/17 10:48:58 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:
................................................................................
**  'n'            NUMERIC
**  'i'            INTEGER
**  't'            TEXT
**  'o'            NONE
*/
void sqlite3IndexAffinityStr(Vdbe *v, Index *pIdx){
  if( !pIdx->zColAff ){
    /* The first time a column affinity string for a particular index is
    ** required, it is allocated and populated here. It is then stored as
    ** a member of the Index structure for subsequent use.
    **
    ** The column affinity string will eventually be deleted by
    ** sqliteDeleteIndex() when the Index structure itself is cleaned
    ** up.
    */
    int n;
    Table *pTab = pIdx->pTable;
    pIdx->zColAff = (char *)sqliteMalloc(pIdx->nColumn+1);
    if( !pIdx->zColAff ){
      return;

**    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.164 2004/05/11 06:55:14 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
    /* 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 addr1 = sqlite3VdbeCurrentAddr(v);
      int addr2;

      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_NotNull, -1, addr1+3);
      sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
      addr2 = sqlite3VdbeAddOp(v, OP_Goto, 0, 0);
      if( pOrderBy ){
        pushOntoSorter(pParse, v, pOrderBy);
      }else{





        sqlite3VdbeAddOp(v, OP_String, 0, 0);
        sqlite3VdbeAddOp(v, OP_PutStrKey, iParm, 0);
      }
      sqlite3VdbeChangeP2(v, addr2, sqlite3VdbeCurrentAddr(v));
      break;
    }

    /* If this is a scalar select that is part of an expression, then
    ** store the results in the appropriate memory cell and break out
................................................................................
      break;
    }
    case SRT_Set: {
      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3);
      sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
      sqlite3VdbeAddOp(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+3);

      sqlite3VdbeAddOp(v, OP_String, 0, 0);
      sqlite3VdbeAddOp(v, OP_PutStrKey, iParm, 0);
      break;
    }
    case SRT_Mem: {
      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_MemStore, iParm, 1);
      sqlite3VdbeAddOp(v, OP_Goto, 0, end1);
      break;
................................................................................
      sqlite3SetNString(&aCol[i].zName, pR->token.z, pR->token.n, 0);
      for(j=cnt=0; j<i; j++){
        if( sqlite3StrICmp(aCol[j].zName, aCol[i].zName)==0 ){
          int n;
          char zBuf[30];
          sprintf(zBuf,"_%d",++cnt);
          n = strlen(zBuf);
          sqlite3SetNString(&aCol[i].zName, pR->token.z, pR->token.n, zBuf, n,0);
          j = -1;
        }
      }
    }else if( p->span.z && p->span.z[0] ){
      sqlite3SetNString(&pTab->aCol[i].zName, p->span.z, p->span.n, 0);
    }else{
      char zBuf[30];
      sprintf(zBuf, "column%d", i+1);
      pTab->aCol[i].zName = sqliteStrDup(zBuf);
    }



  }
  pTab->iPKey = -1;
  return pTab;
}

/*
** For the given SELECT statement, do three things.
................................................................................
**
**     eDest Value       Result
**     ------------    -------------------------------------------
**     SRT_Callback    Invoke the callback for each row of the result.
**
**     SRT_Mem         Store first result in memory cell iParm
**
**     SRT_Set         Store results as keys of a table with cursor iParm
**
**     SRT_Union       Store results as a key in a temporary table iParm
**
**     SRT_Except      Remove results from the temporary table iParm.
**
**     SRT_Table       Store results in temporary table iParm
**

**    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.165 2004/05/17 10:48:58 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
    /* 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 addr1 = sqlite3VdbeCurrentAddr(v);
      int addr2;

      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_NotNull, -1, addr1+3);
      sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
      addr2 = sqlite3VdbeAddOp(v, OP_Goto, 0, 0);
      if( pOrderBy ){
        pushOntoSorter(pParse, v, pOrderBy);
      }else{
        char const *affStr;
        char aff = (iParm>>16)&0xFF;
        aff = sqlite3CompareAffinity(pEList->a[0].pExpr, aff);
        affStr = sqlite3AffinityString(aff);
        sqlite3VdbeOp3(v, OP_MakeKey, 1, 1, affStr, P3_STATIC);
        sqlite3VdbeAddOp(v, OP_String, 0, 0);
        sqlite3VdbeAddOp(v, OP_PutStrKey, (iParm&0x0000FFFF), 0);
      }
      sqlite3VdbeChangeP2(v, addr2, sqlite3VdbeCurrentAddr(v));
      break;
    }

    /* If this is a scalar select that is part of an expression, then
    ** store the results in the appropriate memory cell and break out
................................................................................
      break;
    }
    case SRT_Set: {
      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3);
      sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
      sqlite3VdbeAddOp(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+3);
      sqlite3VdbeOp3(v, OP_MakeKey, 1, 1, "n", P3_STATIC);
      sqlite3VdbeAddOp(v, OP_String, 0, 0);
      sqlite3VdbeAddOp(v, OP_PutStrKey, (iParm&0x0000FFFF), 0);
      break;
    }
    case SRT_Mem: {
      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_MemStore, iParm, 1);
      sqlite3VdbeAddOp(v, OP_Goto, 0, end1);
      break;
................................................................................
      sqlite3SetNString(&aCol[i].zName, pR->token.z, pR->token.n, 0);
      for(j=cnt=0; j<i; j++){
        if( sqlite3StrICmp(aCol[j].zName, aCol[i].zName)==0 ){
          int n;
          char zBuf[30];
          sprintf(zBuf,"_%d",++cnt);
          n = strlen(zBuf);
          sqlite3SetNString(&aCol[i].zName, pR->token.z, pR->token.n, zBuf,n,0);
          j = -1;
        }
      }
    }else if( p->span.z && p->span.z[0] ){
      sqlite3SetNString(&pTab->aCol[i].zName, p->span.z, p->span.n, 0);
    }else{
      char zBuf[30];
      sprintf(zBuf, "column%d", i+1);
      pTab->aCol[i].zName = sqliteStrDup(zBuf);
    }
    
    /* Affinity is always NONE, as there is no type name. */
    pTab->aCol[i].affinity = SQLITE_AFF_NONE;
  }
  pTab->iPKey = -1;
  return pTab;
}

/*
** For the given SELECT statement, do three things.
................................................................................
**
**     eDest Value       Result
**     ------------    -------------------------------------------
**     SRT_Callback    Invoke the callback for each row of the result.
**
**     SRT_Mem         Store first result in memory cell iParm
**
**     SRT_Set         Store results as keys of table iParm.
**
**     SRT_Union       Store results as a key in a temporary table iParm
**
**     SRT_Except      Remove results from the temporary table iParm.
**
**     SRT_Table       Store results in temporary table iParm
**

**    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.234 2004/05/16 11:15:39 danielk1977 Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "parse.h"
#include <stdio.h>
#include <stdlib.h>
................................................................................
int sqlite3PutVarint(unsigned char *, u64);
int sqlite3GetVarint(const unsigned char *, u64 *);
int sqlite3GetVarint32(const unsigned char *, u32 *);
int sqlite3VarintLen(u64 v);
char sqlite3AffinityType(const char *, int);
void sqlite3IndexAffinityStr(Vdbe *, Index *);
void sqlite3TableAffinityStr(Vdbe *, Table *);

**    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.235 2004/05/17 10:48:58 danielk1977 Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "parse.h"
#include <stdio.h>
#include <stdlib.h>
................................................................................
int sqlite3PutVarint(unsigned char *, u64);
int sqlite3GetVarint(const unsigned char *, u64 *);
int sqlite3GetVarint32(const unsigned char *, u32 *);
int sqlite3VarintLen(u64 v);
char sqlite3AffinityType(const char *, int);
void sqlite3IndexAffinityStr(Vdbe *, Index *);
void sqlite3TableAffinityStr(Vdbe *, Table *);
char sqlite3CompareAffinity(Expr *pExpr, char aff2);
char const *sqlite3AffinityString(char affinity);
int sqlite3IndexAffinityOk(Expr *pExpr, char idx_affinity);

**
** 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.295 2004/05/16 11:57:28 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
** Pop the top two elements from the stack.  If they are equal, then
** jump to instruction P2.  Otherwise, continue to the next instruction.
**
** The least significant byte of P1 may be either 0x00 or 0x01. If either
** operand is NULL (and thus if the result is unknown) then take the jump
** only if the least significant byte of P1 is 0x01.
**
** The second least significant byte of P1 determines whether any
** conversions are applied to the two values before the comparison is made.
** If this byte is 0x00, and one of the values being compared is numeric
** and the other text, an attempt is made to convert the text value to 
** a numeric form.
**
** If the second least significant byte of P1 is not 0x00, then it must
** be an affinity character - 'n', 't', 'i' or 'o'. In this case an 
** attempt is made to coerce both values according to the affinity before
** the comparison is made.

**
** Once any conversions have taken place, and neither value is NULL, 
** the values are compared. If both values are blobs, or both are text,
** then memcmp() is used to determine the results of the comparison. If
** both values are numeric, then a numeric comparison is used. If the
** two values are of different types, then they are inequal.
**
................................................................................
      pTos++;
      pTos->flags = MEM_Null;
    }
    break;
  }

  affinity = (pOp->p1>>8)&0xFF;
  if( !affinity && (flags&(MEM_Real|MEM_Int)) ){
    affinity = SQLITE_AFF_NUMERIC;
  }
  if( affinity ){
    applyAffinity(pNos, affinity);
    applyAffinity(pTos, affinity);
  }

  res = sqlite3MemCompare(pNos, pTos);
  switch( pOp->opcode ){
    case OP_Eq:    res = res==0;     break;
    case OP_Ne:    res = res!=0;     break;
    case OP_Lt:    res = res<0;      break;
    case OP_Le:    res = res<=0;     break;
................................................................................
  ** quantity to nByte.
  **
  ** TODO: Figure out if the in-place coercion causes a problem for
  ** OP_MakeKey when P2 is 0 (used by DISTINCT).
  */
  for(pRec=pData0; pRec<=pTos; pRec++){
    u64 serial_type;
    if( zAffinity ){
      applyAffinity(pRec, zAffinity[pRec-pData0]);
    }else{
      applyAffinity(pRec, SQLITE_SO_NUM);
    }
    if( pRec->flags&MEM_Null ){
      containsNull = 1;
    }
    serial_type = sqlite3VdbeSerialType(pRec);
    nByte += sqlite3VarintLen(serial_type);
    nByte += sqlite3VdbeSerialTypeLen(serial_type);
  }
................................................................................
    ** automatically created table with root-page 1 (an INTKEY table).
    */
    if( pOp->p2 ){
      int pgno;
      rc = sqlite3BtreeCreateTable(pCx->pBt, &pgno, BTREE_ZERODATA); 
      if( rc==SQLITE_OK ){
        assert( pgno==MASTER_ROOT+1 );
        rc = sqlite3BtreeCursor(pCx->pBt, pgno, 1, 0, 0, &pCx->pCursor);

      }
    }else{
      rc = sqlite3BtreeCursor(pCx->pBt, MASTER_ROOT, 1, 0, 0, &pCx->pCursor);
      pCx->intKey = 1;
    }
  }
  break;
................................................................................
    sqlite3BtreeKeySize(pC->pCursor, (u64*)&v);
    v = keyToInt(v);
  }
  pTos->i = v;
  pTos->flags = MEM_Int;
  break;
}




















































/* Opcode: FullKey P1 * *
**
** Extract the complete key from the record that cursor P1 is currently
** pointing to and push the key onto the stack as a string.
**
** Compare this opcode to Recno.  The Recno opcode extracts the first

**
** 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.296 2004/05/17 10:48:58 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
** Pop the top two elements from the stack.  If they are equal, then
** jump to instruction P2.  Otherwise, continue to the next instruction.
**
** The least significant byte of P1 may be either 0x00 or 0x01. If either
** operand is NULL (and thus if the result is unknown) then take the jump
** only if the least significant byte of P1 is 0x01.
**
** The second least significant byte of P1 must be an affinity character -







** 'n', 't', 'i' or 'o' - or 0x00. An attempt is made to coerce both values
** according to the affinity before the comparison is made. If the byte is
** 0x00, then numeric affinity is used.
**
** Once any conversions have taken place, and neither value is NULL, 
** the values are compared. If both values are blobs, or both are text,
** then memcmp() is used to determine the results of the comparison. If
** both values are numeric, then a numeric comparison is used. If the
** two values are of different types, then they are inequal.
**
................................................................................
      pTos++;
      pTos->flags = MEM_Null;
    }
    break;
  }

  affinity = (pOp->p1>>8)&0xFF;



  if( affinity=='\0' ) affinity = 'n';
  applyAffinity(pNos, affinity);
  applyAffinity(pTos, affinity);


  res = sqlite3MemCompare(pNos, pTos);
  switch( pOp->opcode ){
    case OP_Eq:    res = res==0;     break;
    case OP_Ne:    res = res!=0;     break;
    case OP_Lt:    res = res<0;      break;
    case OP_Le:    res = res<=0;     break;
................................................................................
  ** quantity to nByte.
  **
  ** TODO: Figure out if the in-place coercion causes a problem for
  ** OP_MakeKey when P2 is 0 (used by DISTINCT).
  */
  for(pRec=pData0; pRec<=pTos; pRec++){
    u64 serial_type;

    applyAffinity(pRec, zAffinity[pRec-pData0]);



    if( pRec->flags&MEM_Null ){
      containsNull = 1;
    }
    serial_type = sqlite3VdbeSerialType(pRec);
    nByte += sqlite3VarintLen(serial_type);
    nByte += sqlite3VdbeSerialTypeLen(serial_type);
  }
................................................................................
    ** automatically created table with root-page 1 (an INTKEY table).
    */
    if( pOp->p2 ){
      int pgno;
      rc = sqlite3BtreeCreateTable(pCx->pBt, &pgno, BTREE_ZERODATA); 
      if( rc==SQLITE_OK ){
        assert( pgno==MASTER_ROOT+1 );
        rc = sqlite3BtreeCursor(pCx->pBt, pgno, 1, sqlite3VdbeKeyCompare,
            pCx, &pCx->pCursor);
      }
    }else{
      rc = sqlite3BtreeCursor(pCx->pBt, MASTER_ROOT, 1, 0, 0, &pCx->pCursor);
      pCx->intKey = 1;
    }
  }
  break;
................................................................................
    sqlite3BtreeKeySize(pC->pCursor, (u64*)&v);
    v = keyToInt(v);
  }
  pTos->i = v;
  pTos->flags = MEM_Int;
  break;
}

/* Opcode: IdxColumn P1 * *
**
** P1 is a cursor opened on an index. Push the first field from the
** current index key onto the stack.
*/
case OP_IdxColumn: {
  char *zData;
  i64 n;
  u64 serial_type;
  int len;
  int freeZData = 0;
  BtCursor *pCsr;

  assert( 0==p->apCsr[pOp->p1]->intKey );
  pCsr = p->apCsr[pOp->p1]->pCursor;
  rc = sqlite3BtreeKeySize(pCsr, &n);
  if( rc!=SQLITE_OK ){
    goto abort_due_to_error;
  }
  if( n>10 ) n = 10;

  zData = (char *)sqlite3BtreeKeyFetch(pCsr, n);
  assert( zData );

  len = sqlite3GetVarint(zData, &serial_type);
  n = sqlite3VdbeSerialTypeLen(serial_type);

  zData = (char *)sqlite3BtreeKeyFetch(pCsr, len+n);
  if( !zData ){
    zData = (char *)sqliteMalloc(n);
    if( !zData ){
      rc = SQLITE_NOMEM;
      goto abort_due_to_error;
    }
    rc = sqlite3BtreeKey(pCsr, len, n, zData);
    if( rc!=SQLITE_OK ){
      sqliteFree(zData);
      goto abort_due_to_error;
    }
    freeZData = 1;
    len = 0;
  }

  pTos++;
  sqlite3VdbeSerialGet(&zData[len], serial_type, pTos);
  if( freeZData ){
    sqliteFree(zData);
  }
  break;
}

/* Opcode: FullKey P1 * *
**
** Extract the complete key from the record that cursor P1 is currently
** pointing to and push the key onto the stack as a string.
**
** Compare this opcode to Recno.  The Recno opcode extracts the first

  if( offset1<nKey1 ){
    return 1;
  }
  if( offset2<nKey2 ){
    return -1;
  }

return_result:

  return 0;
}

/*
** pCur points at an index entry. Read the rowid (varint occuring at
** the end of the entry and store it in *rowid. Return SQLITE_OK if
** everything works, or an error code otherwise.

  if( offset1<nKey1 ){
    return 1;
  }
  if( offset2<nKey2 ){
    return -1;
  }



  return 0;
}

/*
** pCur points at an index entry. Read the rowid (varint occuring at
** the end of the entry and store it in *rowid. Return SQLITE_OK if
** everything works, or an error code otherwise.

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.
**
** $Id: where.c,v 1.94 2004/05/16 11:15:41 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................

      if( pIdx->nColumn>32 ) continue;  /* Ignore indices too many columns */
      for(j=0; j<nExpr; j++){
        if( aExpr[j].idxLeft==iCur 
             && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){
          int iColumn = aExpr[j].p->pLeft->iColumn;
          int k;

          for(k=0; k<pIdx->nColumn; k++){
            if( pIdx->aiColumn[k]==iColumn ){

              switch( aExpr[j].p->op ){
                case TK_IN: {
                  if( k==0 ) inMask |= 1;
                  break;
                }
                case TK_EQ: {
                  eqMask |= 1<<k;
................................................................................
            }
          }
        }
        if( aExpr[j].idxRight==iCur 
             && (aExpr[j].prereqLeft & loopMask)==aExpr[j].prereqLeft ){
          int iColumn = aExpr[j].p->pRight->iColumn;
          int k;

          for(k=0; k<pIdx->nColumn; k++){
            if( pIdx->aiColumn[k]==iColumn ){

              switch( aExpr[j].p->op ){
                case TK_EQ: {
                  eqMask |= 1<<k;
                  break;
                }
                case TK_LE:
                case TK_LT: {
................................................................................
      assert( aExpr[k].p!=0 );
      assert( aExpr[k].idxLeft==iCur || aExpr[k].idxRight==iCur );
      brk = pLevel->brk = sqlite3VdbeMakeLabel(v);
      if( aExpr[k].idxLeft==iCur ){
        Expr *pX = aExpr[k].p;
        if( pX->op!=TK_IN ){
          sqlite3ExprCode(pParse, aExpr[k].p->pRight);
        }else if( pX->pList ){
          sqlite3VdbeAddOp(v, OP_SetFirst, pX->iTable, brk);
          pLevel->inOp = OP_SetNext;
          pLevel->inP1 = pX->iTable;
          pLevel->inP2 = sqlite3VdbeCurrentAddr(v);
        }else{
          assert( pX->pSelect );
          sqlite3VdbeAddOp(v, OP_Rewind, pX->iTable, brk);
          sqlite3VdbeAddOp(v, OP_KeyAsData, pX->iTable, 1);
          pLevel->inP2 = sqlite3VdbeAddOp(v, OP_FullKey, pX->iTable, 0);
          pLevel->inOp = OP_Next;
          pLevel->inP1 = pX->iTable;
        }
      }else{
        sqlite3ExprCode(pParse, aExpr[k].p->pLeft);
      }
      aExpr[k].p = 0;
................................................................................
        for(k=0; k<nExpr; k++){
          Expr *pX = aExpr[k].p;
          if( pX==0 ) continue;
          if( aExpr[k].idxLeft==iCur
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pX->pLeft->iColumn==pIdx->aiColumn[j]
          ){


            if( pX->op==TK_EQ ){
              sqlite3ExprCode(pParse, pX->pRight);
              aExpr[k].p = 0;
              break;
            }
            if( pX->op==TK_IN && nColumn==1 ){
              if( pX->pList ){
                sqlite3VdbeAddOp(v, OP_SetFirst, pX->iTable, brk);
                pLevel->inOp = OP_SetNext;
                pLevel->inP1 = pX->iTable;
                pLevel->inP2 = sqlite3VdbeCurrentAddr(v);
              }else{
                assert( pX->pSelect );
                sqlite3VdbeAddOp(v, OP_Rewind, pX->iTable, brk);
                sqlite3VdbeAddOp(v, OP_KeyAsData, pX->iTable, 1);
                pLevel->inP2 = sqlite3VdbeAddOp(v, OP_FullKey, pX->iTable, 0);
                pLevel->inOp = OP_Next;
                pLevel->inP1 = pX->iTable;
              }
              aExpr[k].p = 0;
              break;

            }
          }
          if( aExpr[k].idxRight==iCur
             && aExpr[k].p->op==TK_EQ
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && aExpr[k].p->pRight->iColumn==pIdx->aiColumn[j]
          ){


            sqlite3ExprCode(pParse, aExpr[k].p->pLeft);
            aExpr[k].p = 0;
            break;

          }
        }
      }
      pLevel->iMem = pParse->nMem++;
      cont = pLevel->cont = sqlite3VdbeMakeLabel(v);
      sqlite3VdbeAddOp(v, OP_NotNull, -nColumn, sqlite3VdbeCurrentAddr(v)+3);
      sqlite3VdbeAddOp(v, OP_Pop, nColumn, 0);

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.
**
** $Id: where.c,v 1.95 2004/05/17 10:48:58 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................

      if( pIdx->nColumn>32 ) continue;  /* Ignore indices too many columns */
      for(j=0; j<nExpr; j++){
        if( aExpr[j].idxLeft==iCur 
             && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){
          int iColumn = aExpr[j].p->pLeft->iColumn;
          int k;
          char idxaff = pIdx->pTable->aCol[iColumn].affinity; 
          for(k=0; k<pIdx->nColumn; k++){
            if( pIdx->aiColumn[k]==iColumn 
                && sqlite3IndexAffinityOk(aExpr[j].p, idxaff) ){
              switch( aExpr[j].p->op ){
                case TK_IN: {
                  if( k==0 ) inMask |= 1;
                  break;
                }
                case TK_EQ: {
                  eqMask |= 1<<k;
................................................................................
            }
          }
        }
        if( aExpr[j].idxRight==iCur 
             && (aExpr[j].prereqLeft & loopMask)==aExpr[j].prereqLeft ){
          int iColumn = aExpr[j].p->pRight->iColumn;
          int k;
          char idxaff = pIdx->pTable->aCol[iColumn].affinity; 
          for(k=0; k<pIdx->nColumn; k++){
            if( pIdx->aiColumn[k]==iColumn 
                && sqlite3IndexAffinityOk(aExpr[j].p, idxaff) ){
              switch( aExpr[j].p->op ){
                case TK_EQ: {
                  eqMask |= 1<<k;
                  break;
                }
                case TK_LE:
                case TK_LT: {
................................................................................
      assert( aExpr[k].p!=0 );
      assert( aExpr[k].idxLeft==iCur || aExpr[k].idxRight==iCur );
      brk = pLevel->brk = sqlite3VdbeMakeLabel(v);
      if( aExpr[k].idxLeft==iCur ){
        Expr *pX = aExpr[k].p;
        if( pX->op!=TK_IN ){
          sqlite3ExprCode(pParse, aExpr[k].p->pRight);





        }else{

          sqlite3VdbeAddOp(v, OP_Rewind, pX->iTable, brk);
          sqlite3VdbeAddOp(v, OP_KeyAsData, pX->iTable, 1);
          pLevel->inP2 = sqlite3VdbeAddOp(v, OP_IdxColumn, pX->iTable, 0);
          pLevel->inOp = OP_Next;
          pLevel->inP1 = pX->iTable;
        }
      }else{
        sqlite3ExprCode(pParse, aExpr[k].p->pLeft);
      }
      aExpr[k].p = 0;
................................................................................
        for(k=0; k<nExpr; k++){
          Expr *pX = aExpr[k].p;
          if( pX==0 ) continue;
          if( aExpr[k].idxLeft==iCur
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pX->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            char idxaff = pIdx->pTable->aCol[pX->pLeft->iColumn].affinity;
            if( sqlite3IndexAffinityOk(aExpr[k].p, idxaff) ){
              if( pX->op==TK_EQ ){
                sqlite3ExprCode(pParse, pX->pRight);
                aExpr[k].p = 0;
                break;
              }
              if( pX->op==TK_IN && nColumn==1 ){







                sqlite3VdbeAddOp(v, OP_Rewind, pX->iTable, brk);
                sqlite3VdbeAddOp(v, OP_KeyAsData, pX->iTable, 1);
                pLevel->inP2 = sqlite3VdbeAddOp(v, OP_IdxColumn, pX->iTable, 0);
                pLevel->inOp = OP_Next;
                pLevel->inP1 = pX->iTable;

                aExpr[k].p = 0;
                break;
              }
            }
          }
          if( aExpr[k].idxRight==iCur
             && aExpr[k].p->op==TK_EQ
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && aExpr[k].p->pRight->iColumn==pIdx->aiColumn[j]
          ){
            char idxaff = pIdx->pTable->aCol[pX->pRight->iColumn].affinity;
            if( sqlite3IndexAffinityOk(aExpr[k].p, idxaff) ){
              sqlite3ExprCode(pParse, aExpr[k].p->pLeft);
              aExpr[k].p = 0;
              break;
            }
          }
        }
      }
      pLevel->iMem = pParse->nMem++;
      cont = pLevel->cont = sqlite3VdbeMakeLabel(v);
      sqlite3VdbeAddOp(v, OP_NotNull, -nColumn, sqlite3VdbeCurrentAddr(v)+3);
      sqlite3VdbeAddOp(v, OP_Pop, nColumn, 0);

#    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 interaction of manifest types, type affinity
# and comparison expressions.
#
# $Id: types2.test,v 1.1 2004/05/16 11:15:42 danielk1977 Exp $

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

# Tests in this file are organized roughly as follows:
#
# types2-1.*: The '=' operator in the absence of an index.
# types2-2.*: The '=' operator implemented using an index.
# types2-2.*: The '<' operator implemented using an index.
# types2-3.*: The '>' operator in the absense of an index.




#




execsql {
  CREATE TABLE t1(
    i1 INTEGER,
    i2 INTEGER,
    n1 NUMERIC,
    n2 NUMERIC,
................................................................................
  foreach {t e r} [list $testname $expr $res] {}

  do_test $t.1 "execsql {SELECT $e FROM t1}" $r
  do_test $t.2 "execsql {SELECT 1 FROM t1 WHERE $expr}" [expr $r?"1":""]
  do_test $t.3 "execsql {SELECT 1 FROM t1 WHERE NOT ($e)}" [expr $r?"":"1"]
}

# Compare literals against literals

test_bool types2-1.1 "" {500 = 500.0} 1
test_bool types2-1.2 "" {'500' = 500.0} 1
test_bool types2-1.3 "" {500 = '500.0'} 1
test_bool types2-1.4 "" {'500' = '500.0'} 0

# Compare literals against a column with TEXT affinity
test_bool types2-1.5 {t1=500} {500 = t1} 1
test_bool types2-1.6 {t1=500} {'500' = t1} 1
test_bool types2-1.7 {t1=500} {500.0 = t1} 0
test_bool types2-1.8 {t1=500} {'500.0' = t1} 0
test_bool types2-1.9 {t1='500'} {500 = t1} 1
................................................................................
test_boolset types2-3.4 {t < '20.0'} {1 2 3 4 5 7}

test_boolset types2-3.1 {o < 20} {1 2}
test_boolset types2-3.2 {o < 20.0} {1 2}
test_boolset types2-3.3 {o < '20'} {1 2 3 4 5 6 9 10}
test_boolset types2-3.3 {o < '20.0'} {1 2 3 4 5 6 7 9 10}

# Compare literals against literals
test_bool types2-4.1 "" {500 > 60.0} 1
test_bool types2-4.2 "" {'500' > 60.0} 1
test_bool types2-4.3 "" {500 > '60.0'} 1
test_bool types2-4.4 "" {'500' > '60.0'} 0

# Compare literals against a column with TEXT affinity
test_bool types2-4.5 {t1=500.0} {t1 > 500} 1
test_bool types2-4.6 {t1=500.0} {t1 > '500' } 1
test_bool types2-4.7 {t1=500.0} {t1 > 500.0 } 0
test_bool types2-4.8 {t1=500.0} {t1 > '500.0' } 0
test_bool types2-4.9 {t1='500.0'} {t1 > 500 } 1
................................................................................
test_bool types2-4.23 {o1=500} {500.0 > o1} 0
test_bool types2-4.24 {o1=500} {'500.0' > o1} 1
test_bool types2-4.25 {o1='500'} {500 > o1} 0
test_bool types2-4.26 {o1='500'} {'500' > o1} 0
test_bool types2-4.27 {o1='500'} {500.0 > o1} 0
test_bool types2-4.28 {o1='500'} {'500.0' > o1} 1











































































































finish_test

#    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 interaction of manifest types, type affinity
# and comparison expressions.
#
# $Id: types2.test,v 1.2 2004/05/17 10:48:58 danielk1977 Exp $

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

# Tests in this file are organized roughly as follows:
#
# types2-1.*: The '=' operator in the absence of an index.
# types2-2.*: The '=' operator implemented using an index.
# types2-3.*: The '<' operator implemented using an index.
# types2-4.*: The '>' operator in the absence of an index.
# types2-5.*: The 'IN(x, y...)' operator in the absence of an index.
# types2-6.*: The 'IN(x, y...)' operator with an index.
# types2-7.*: The 'IN(SELECT...)' operator in the absence of an index.
# types2-8.*: The 'IN(SELECT...)' operator with an index.
#
# All tests test the operators using literals and columns, but no
# other types of expressions. All expressions except columns are
# handled similarly in the implementation.

execsql {
  CREATE TABLE t1(
    i1 INTEGER,
    i2 INTEGER,
    n1 NUMERIC,
    n2 NUMERIC,
................................................................................
  foreach {t e r} [list $testname $expr $res] {}

  do_test $t.1 "execsql {SELECT $e FROM t1}" $r
  do_test $t.2 "execsql {SELECT 1 FROM t1 WHERE $expr}" [expr $r?"1":""]
  do_test $t.3 "execsql {SELECT 1 FROM t1 WHERE NOT ($e)}" [expr $r?"":"1"]
}

# Compare literals against literals. This should always use a numeric
# comparison.
test_bool types2-1.1 "" {500 = 500.0} 1
test_bool types2-1.2 "" {'500' = 500.0} 1
test_bool types2-1.3 "" {500 = '500.0'} 1
test_bool types2-1.4 "" {'500' = '500.0'} 1

# Compare literals against a column with TEXT affinity
test_bool types2-1.5 {t1=500} {500 = t1} 1
test_bool types2-1.6 {t1=500} {'500' = t1} 1
test_bool types2-1.7 {t1=500} {500.0 = t1} 0
test_bool types2-1.8 {t1=500} {'500.0' = t1} 0
test_bool types2-1.9 {t1='500'} {500 = t1} 1
................................................................................
test_boolset types2-3.4 {t < '20.0'} {1 2 3 4 5 7}

test_boolset types2-3.1 {o < 20} {1 2}
test_boolset types2-3.2 {o < 20.0} {1 2}
test_boolset types2-3.3 {o < '20'} {1 2 3 4 5 6 9 10}
test_boolset types2-3.3 {o < '20.0'} {1 2 3 4 5 6 7 9 10}

# Compare literals against literals (always a numeric comparison).
test_bool types2-4.1 "" {500 > 60.0} 1
test_bool types2-4.2 "" {'500' > 60.0} 1
test_bool types2-4.3 "" {500 > '60.0'} 1
test_bool types2-4.4 "" {'500' > '60.0'} 1

# Compare literals against a column with TEXT affinity
test_bool types2-4.5 {t1=500.0} {t1 > 500} 1
test_bool types2-4.6 {t1=500.0} {t1 > '500' } 1
test_bool types2-4.7 {t1=500.0} {t1 > 500.0 } 0
test_bool types2-4.8 {t1=500.0} {t1 > '500.0' } 0
test_bool types2-4.9 {t1='500.0'} {t1 > 500 } 1
................................................................................
test_bool types2-4.23 {o1=500} {500.0 > o1} 0
test_bool types2-4.24 {o1=500} {'500.0' > o1} 1
test_bool types2-4.25 {o1='500'} {500 > o1} 0
test_bool types2-4.26 {o1='500'} {'500' > o1} 0
test_bool types2-4.27 {o1='500'} {500.0 > o1} 0
test_bool types2-4.28 {o1='500'} {'500.0' > o1} 1

# types2-5.* - The 'IN (x, y....)' operator with no index.
# 
# Compare literals against literals (always a numeric comparison).
test_bool types2-5.1 {} {(NULL IN ('10.0', 20)) ISNULL} 1
test_bool types2-5.2 {} {10 IN ('10.0', 20)} 1
test_bool types2-5.3 {} {'10' IN ('10.0', 20)} 1
test_bool types2-5.4 {} {10 IN (10.0, 20)} 1
test_bool types2-5.5 {} {'10.0' IN (10, 20)} 1

# Compare literals against a column with TEXT affinity
test_bool types2-5.6 {t1='10.0'} {t1 IN (10.0, 20)} 1
test_bool types2-5.7 {t1='10.0'} {t1 IN (10, 20)} 0
test_bool types2-5.8 {t1='10'} {t1 IN (10.0, 20)} 0
test_bool types2-5.9 {t1='10'} {t1 IN (20, '10.0')} 0
test_bool types2-5.10 {t1=10} {t1 IN (20, '10')} 1

# Compare literals against a column with NUMERIC affinity
test_bool types2-5.11 {n1='10.0'} {n1 IN (10.0, 20)} 1
test_bool types2-5.12 {n1='10.0'} {n1 IN (10, 20)} 1
test_bool types2-5.13 {n1='10'} {n1 IN (10.0, 20)} 1
test_bool types2-5.14 {n1='10'} {n1 IN (20, '10.0')} 1
test_bool types2-5.15 {n1=10} {n1 IN (20, '10')} 1

# Compare literals against a column with affinity NONE
test_bool types2-5.16 {o1='10.0'} {o1 IN (10.0, 20)} 0
test_bool types2-5.17 {o1='10.0'} {o1 IN (10, 20)} 0
test_bool types2-5.18 {o1='10'} {o1 IN (10.0, 20)} 0
test_bool types2-5.19 {o1='10'} {o1 IN (20, '10.0')} 0
test_bool types2-5.20 {o1=10} {o1 IN (20, '10')} 0
test_bool types2-5.21 {o1='10.0'} {o1 IN (10, 20, '10.0')} 1
test_bool types2-5.22 {o1='10'} {o1 IN (10.0, 20, '10')} 1
test_bool types2-5.23 {o1=10} {n1 IN (20, '10', 10)} 1

# Tests named types2-6.* use the same infrastructure as the types2-2.*
# tests. The contents of the vals array is repeated here for easy 
# reference.
# 
# set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
#                1  2    3    4      5  6    7    8      9  10   11   12

test_boolset types2-6.1 {o IN ('10', 30)} {3 9 10}
test_boolset types2-6.2 {o IN (20.0, 30.0)} {5 6 9 10}
test_boolset types2-6.3 {t IN ('10', 30)} {1 3 9 11}
test_boolset types2-6.4 {t IN (20.0, 30.0)} {6 8 10 12}
test_boolset types2-6.5 {n IN ('10', 30)} {1 2 3 4 9 10 11 12}
test_boolset types2-6.6 {n IN (20.0, 30.0)} {5 6 7 8 9 10 11 12}
test_boolset types2-6.7 {i IN ('10', 30)} {1 2 3 4 9 10 11 12}
test_boolset types2-6.8 {i IN (20.0, 30.0)} {5 6 7 8 9 10 11 12}

# Also test than IN(x, y, z) works on a rowid:
test_boolset types2-6.9 {rowid IN (1, 6, 10)} {1 6 10}

# Tests types2-7.* concentrate on expressions of the form 
# "x IN (SELECT...)" with no index.
execsql {
  CREATE TABLE t3(i INTEGER, n NUMERIC, t TEXT, o);
  INSERT INTO t3 VALUES(1, 1, 1, 1);
  INSERT INTO t3 VALUES(2, 2, 2, 2);
  INSERT INTO t3 VALUES(3, 3, 3, 3);
  INSERT INTO t3 VALUES('1', '1', '1', '1');
  INSERT INTO t3 VALUES('1.0', '1.0', '1.0', '1.0');
}

test_bool types2-7.1 {i1=1} {i1 IN (SELECT i FROM t3)} 1
test_bool types2-7.2 {i1='2.0'} {i1 IN (SELECT i FROM t3)} 1
test_bool types2-7.3 {i1='2.0'} {i1 IN (SELECT n FROM t3)} 1
test_bool types2-7.4 {i1='2.0'} {i1 IN (SELECT t FROM t3)} 1
test_bool types2-7.5 {i1='2.0'} {i1 IN (SELECT o FROM t3)} 1

test_bool types2-7.6 {n1=1} {n1 IN (SELECT n FROM t3)} 1
test_bool types2-7.7 {n1='2.0'} {n1 IN (SELECT i FROM t3)} 1
test_bool types2-7.8 {n1='2.0'} {n1 IN (SELECT n FROM t3)} 1
test_bool types2-7.9 {n1='2.0'} {n1 IN (SELECT t FROM t3)} 1
test_bool types2-7.10 {n1='2.0'} {n1 IN (SELECT o FROM t3)} 1

test_bool types2-7.6 {t1=1} {t1 IN (SELECT t FROM t3)} 1
test_bool types2-7.7 {t1='2.0'} {t1 IN (SELECT t FROM t3)} 0
test_bool types2-7.8 {t1='2.0'} {t1 IN (SELECT n FROM t3)} 1
test_bool types2-7.9 {t1='2.0'} {t1 IN (SELECT i FROM t3)} 1
test_bool types2-7.10 {t1='2.0'} {t1 IN (SELECT o FROM t3)} 0
test_bool types2-7.11 {t1='1.0'} {t1 IN (SELECT t FROM t3)} 1
test_bool types2-7.12 {t1='1.0'} {t1 IN (SELECT o FROM t3)} 1

test_bool types2-7.13 {o1=2} {o1 IN (SELECT o FROM t3)} 1
test_bool types2-7.14 {o1='2'} {o1 IN (SELECT o FROM t3)} 0
test_bool types2-7.15 {o1='2'} {o1 IN (SELECT o||'' FROM t3)} 1

# set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
#                1  2    3    4      5  6    7    8      9  10   11   12
execsql {
  CREATE TABLE t4(i INTEGER, n NUMERIC, t TEXT, o);
  INSERT INTO t4 VALUES(10, 20, 20, 30);
}
test_boolset types2-8.1 {i IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.2 {n IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.3 {t IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.4 {o IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.5 {i IN (SELECT t FROM t4)} {5 6 7 8}
test_boolset types2-8.6 {n IN (SELECT t FROM t4)} {5 6 7 8}
test_boolset types2-8.7 {t IN (SELECT t FROM t4)} {5 7}
test_boolset types2-8.8 {o IN (SELECT t FROM t4)} {7}
test_boolset types2-8.9 {i IN (SELECT o FROM t4)} {9 10 11 12}
test_boolset types2-8.6 {n IN (SELECT o FROM t4)} {9 10 11 12}
test_boolset types2-8.7 {t IN (SELECT o FROM t4)} {9 11}
test_boolset types2-8.8 {o IN (SELECT o FROM t4)} {9 10}

finish_test