SQLite

**    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
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.151 2008/01/05 17:39:30 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.

  ** the table and pick which records to delete.
  */
  else{
    int iRowid = ++pParse->nMem;    /* Used for storing rowid values. */

    /* Begin the database scan
    */
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0);
    if( pWInfo==0 ) goto delete_from_cleanup;

    /* Remember the rowid of every item to be deleted.
    */
    sqlite3VdbeAddOp2(v, IsVirtual(pTab) ? OP_VRowid : OP_Rowid, iCur, iRowid);
    sqlite3VdbeAddOp1(v, OP_FifoWrite, iRowid);
    if( db->flags & SQLITE_CountRows ){

**    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.387 2008/01/05 17:39:30 danielk1977 Exp $
*/
#include "sqliteInt.h"


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

  /* Finially, delete what is left of the subquery and return
  ** success.
  */
  sqlite3SelectDelete(pSub);
  return 1;
}
#endif /* SQLITE_OMIT_VIEW */

/*
** Analyze the SELECT statement passed as an argument to see if it
** is a min() or max() query. Return ORDERBY_MIN or ORDERBY_MAX if 
** it is, or 0 otherwise. At present, a query is considered to be
** a min()/max() query if:
**
**   1. The result set contains exactly one element, either 
**      min(x) or max(x), where x is a column identifier.
*/
static int minMaxQuery(Parse *pParse, Select *p){
  Expr *pExpr;
  ExprList *pEList = p->pEList;

  if( pEList->nExpr!=1 ) return ORDERBY_NORMAL;
  pExpr = pEList->a[0].pExpr;
  pEList = pExpr->pList;
  if( pExpr->op!=TK_AGG_FUNCTION || pEList==0 || pEList->nExpr!=1 ) return 0;
  if( pEList->a[0].pExpr->op!=TK_AGG_COLUMN ) return ORDERBY_NORMAL;
  if( pExpr->token.n!=3 ) return ORDERBY_NORMAL;
  if( sqlite3StrNICmp((char*)pExpr->token.z,"min",3)==0 ){
    return ORDERBY_MIN;
  }else if( sqlite3StrNICmp((char*)pExpr->token.z,"max",3)==0 ){
    return ORDERBY_MAX;
  }
  return ORDERBY_NORMAL;
}

/*
** Analyze the SELECT statement passed in as an argument to see if it
** is a simple min() or max() query.  If it is and this query can be
** satisfied using a single seek to the beginning or end of an index,
** then generate the code for this SELECT and return 1.  If this is not a 
** simple min() or max() query, then return 0;

** can be no GROUP BY or HAVING or WHERE clauses.  The result set must
** be the min() or max() of a single column of the table.  The column
** in the min() or max() function must be indexed.
**
** The parameters to this routine are the same as for sqlite3Select().
** See the header comment on that routine for additional information.
*/
#if 0
static int simpleMinMaxQuery(Parse *pParse, Select *p, SelectDest *pDest){
  Expr *pExpr;
  int iCol;
  Table *pTab;
  Index *pIdx;
  int base;
  Vdbe *v;

  eList.a[0].pExpr = pExpr;
  selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, pDest, brk, brk, 0);
  sqlite3VdbeResolveLabel(v, brk);
  sqlite3VdbeAddOp2(v, OP_Close, base, 0);
  
  return 1;
}
#endif 

/*
** This routine resolves any names used in the result set of the
** supplied SELECT statement. If the SELECT statement being resolved
** is a sub-select, then pOuterNC is a pointer to the NameContext 
** of the parent SELECT.
*/

    isDistinct = p->isDistinct;
  }
#endif

  /* Check for the special case of a min() or max() function by itself
  ** in the result set.
  */
#if 0
  if( simpleMinMaxQuery(pParse, p, pDest) ){
    rc = 0;
    goto select_end;
  }
#endif

  /* Check to see if this is a subquery that can be "flattened" into its parent.
  ** If flattening is a possiblity, do so and return immediately.  
  */
#ifndef SQLITE_OMIT_VIEW
  if( pParent && pParentAgg &&
      flattenSubquery(db, pParent, parentTab, *pParentAgg, isAgg) ){

  }

  /* Aggregate and non-aggregate queries are handled differently */
  if( !isAgg && pGroupBy==0 ){
    /* This case is for non-aggregate queries
    ** Begin the database scan
    */
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy, 0);
    if( pWInfo==0 ) goto select_end;

    /* If sorting index that was created by a prior OP_OpenEphemeral 
    ** instruction ended up not being needed, then change the OP_OpenEphemeral
    ** into an OP_Noop.
    */
    if( addrSortIndex>=0 && pOrderBy==0 ){

      /* Begin a loop that will extract all source rows in GROUP BY order.
      ** This might involve two separate loops with an OP_Sort in between, or
      ** it might be a single loop that uses an index to extract information
      ** in the right order to begin with.
      */
      sqlite3VdbeResolveLabel(v, addrInitializeLoop);
      sqlite3VdbeAddOp2(v, OP_Gosub, 0, addrReset);
      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pGroupBy, 0);
      if( pWInfo==0 ) goto select_end;
      if( pGroupBy==0 ){
        /* The optimizer is able to deliver rows in group by order so
        ** we do not have to sort.  The OP_OpenEphemeral table will be
        ** cancelled later because we still need to use the pKeyInfo
        */
        pGroupBy = p->pGroupBy;

      /* Output the final row of result
      */
      sqlite3VdbeAddOp2(v, OP_Gosub, 0, addrOutputRow);
      VdbeComment((v, "output final row"));
      
    } /* endif pGroupBy */
    else {
      ExprList *pMinMax = 0;
      u8 flag;

      flag = minMaxQuery(pParse, p);
      if( flag ){
        pMinMax = sqlite3ExprListDup(db, p->pEList->a[0].pExpr->pList);
        if( pMinMax ){
          pMinMax->a[0].sortOrder = ((flag==ORDERBY_MIN)?0:1);
          pMinMax->a[0].pExpr->op = TK_COLUMN;
        }
      }

      /* This case runs if the aggregate has no GROUP BY clause.  The
      ** processing is much simpler since there is only a single row
      ** of output.
      */
      resetAccumulator(pParse, &sAggInfo);
      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pMinMax, flag);
      if( pWInfo==0 ) goto select_end;
      updateAccumulator(pParse, &sAggInfo);
      if( !pMinMax && flag ){
        sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iBreak);
        VdbeComment((v, "%s() by index", (flag==ORDERBY_MIN?"min":"max")));
      }
      sqlite3WhereEnd(pWInfo);
      finalizeAggFunctions(pParse, &sAggInfo);
      pOrderBy = 0;
      if( pHaving ){
        sqlite3ExprIfFalse(pParse, pHaving, addrEnd, 1);
      }
      selectInnerLoop(pParse, p, p->pEList, 0, 0, 0, -1, 
                      pDest, addrEnd, addrEnd, aff);

      sqlite3ExprListDelete(pMinMax);
    }
    sqlite3VdbeResolveLabel(v, addrEnd);
    
  } /* endif aggregate query */

  /* If there is an ORDER BY clause, then we need to sort the results
  ** and send them to the callback one by one.

/*
** 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.639 2008/01/05 17:39:30 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** The macro unlikely() is a hint that surrounds a boolean
** expression that is usually false.  Macro likely() surrounds

  /* The following field is really not part of the current level.  But
  ** we need a place to cache index information for each table in the
  ** FROM clause and the WhereLevel structure is a convenient place.
  */
  sqlite3_index_info *pIdxInfo;  /* Index info for n-th source table */
};

#define ORDERBY_NORMAL 0
#define ORDERBY_MIN    1
#define ORDERBY_MAX    2

/*
** The WHERE clause processing routine has two halves.  The
** first part does the start of the WHERE loop and the second
** half does the tail of the WHERE loop.  An instance of
** this structure is returned by the first half and passed
** into the second half to give some continuity.
*/

void sqlite3SelectDelete(Select*);
int sqlite3SelectMask(Parse *, Select *, u32);
Table *sqlite3SrcListLookup(Parse*, SrcList*);
int sqlite3IsReadOnly(Parse*, Table*, int);
void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**, u8);
void sqlite3WhereEnd(WhereInfo*);
void sqlite3ExprCodeGetColumn(Vdbe*, Table*, int, int, int);
int sqlite3ExprCode(Parse*, Expr*, int);
void sqlite3ExprCodeAndCache(Parse*, Expr*);
int sqlite3ExprCodeExprList(Parse*, ExprList*, int);
void sqlite3ExprIfTrue(Parse*, Expr*, int, int);
void sqlite3ExprIfFalse(Parse*, Expr*, int, int);

**    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 UPDATE statements.
**
** $Id: update.c,v 1.161 2008/01/05 17:39:30 danielk1977 Exp $
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Forward declaration */
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */

    dest.iParm = iCur;
    sqlite3Select(pParse, pView, &dest, 0, 0, 0, 0);
    sqlite3SelectDelete(pView);
  }

  /* Begin the database scan
  */
  pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0);
  if( pWInfo==0 ) goto update_cleanup;

  /* Remember the rowid of every item to be updated.
  */
  iRowid = ++pParse->nMem;
  sqlite3VdbeAddOp2(v, IsVirtual(pTab) ? OP_VRowid : OP_Rowid, iCur, iRowid);
  sqlite3VdbeAddOp2(v, OP_FifoWrite, iRowid, 0);

** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible 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.276 2008/01/05 17:39:30 danielk1977 Exp $
*/
#include "sqliteInt.h"

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

** If the where clause loops cannot be arranged to provide the correct
** output order, then the *ppOrderBy is unchanged.
*/
WhereInfo *sqlite3WhereBegin(
  Parse *pParse,        /* The parser context */
  SrcList *pTabList,    /* A list of all tables to be scanned */
  Expr *pWhere,         /* The WHERE clause */
  ExprList **ppOrderBy, /* An ORDER BY clause, or NULL */
  u8 obflag             /* One of ORDERBY_MIN, ORDERBY_MAX or ORDERBY_NORMAL */
){
  int i;                     /* Loop counter */
  WhereInfo *pWInfo;         /* Will become the return value of this function */
  Vdbe *v = pParse->pVdbe;   /* The virtual database engine */
  int brk, cont = 0;         /* Addresses used during code generation */
  Bitmask notReady;          /* Cursors that are not yet positioned */
  WhereTerm *pTerm;          /* A single term in the WHERE clause */
  ExprMaskSet maskSet;       /* The expression mask set */
  WhereClause wc;            /* The WHERE clause is divided into these terms */
  struct SrcList_item *pTabItem;  /* A single entry from pTabList */
  WhereLevel *pLevel;             /* A single level in the pWInfo list */
  int iFrom;                      /* First unused FROM clause element */
  int andFlags;              /* AND-ed combination of all wc.a[].flags */
  sqlite3 *db;               /* Database connection */
  ExprList *pOrderBy = 0;

  /* The number of tables in the FROM clause is limited by the number of
  ** bits in a Bitmask 
  */
  if( pTabList->nSrc>BMS ){
    sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
    return 0;
  }

  if( ppOrderBy ){
    pOrderBy = *ppOrderBy;
  }

  /* Split the WHERE clause into separate subexpressions where each
  ** subexpression is separated by an AND operator.
  */
  initMaskSet(&maskSet);
  whereClauseInit(&wc, pParse, &maskSet);
  whereSplit(&wc, pWhere, TK_AND);

      int nEq = pLevel->nEq;
      int topEq=0;        /* True if top limit uses ==. False is strictly < */
      int btmEq=0;        /* True if btm limit uses ==. False if strictly > */
      int topOp, btmOp;   /* Operators for the top and bottom search bounds */
      int testOp;
      int topLimit = (pLevel->flags & WHERE_TOP_LIMIT)!=0;
      int btmLimit = (pLevel->flags & WHERE_BTM_LIMIT)!=0;
      int isMinQuery = 0;      /* If this is an optimized SELECT min(x) ... */

      /* Generate code to evaluate all constraint terms using == or IN
      ** and level the values of those terms on the stack.
      */
      codeAllEqualityTerms(pParse, pLevel, &wc, notReady);

      /* Duplicate the equality term values because they will all be

        topOp = WO_LT|WO_LE;
        btmOp = WO_GT|WO_GE;
      }else{
        topOp = WO_GT|WO_GE;
        btmOp = WO_LT|WO_LE;
        SWAP(int, topLimit, btmLimit);
      }

      /* If this loop satisfies a sort order (pOrderBy) request that 
      ** was passed to this function to implement a "SELECT min(x) ..." 
      ** query, then the caller will only allow the loop to run for
      ** a single iteration. This means that the first row returned
      ** should not have a NULL value stored in 'x'. If column 'x' is
      ** the first one after the nEq equality constraints in the index,
      ** this requires some special handling.
      */
      if( (obflag==ORDERBY_MIN)
       && (pLevel->flags&WHERE_ORDERBY)
       && (pIdx->nColumn>nEq)
       && (pOrderBy->a[0].pExpr->iColumn==pIdx->aiColumn[nEq])
      ){
        isMinQuery = 1;
      }

      /* Generate the termination key.  This is the key value that
      ** will end the search.  There is no termination key if there
      ** are no equality terms and no "X<..." term.
      **
      ** 2002-Dec-04: On a reverse-order scan, the so-called "termination"
      ** key computed here really ends up being the start key.

        topEq = pTerm->eOperator & (WO_LE|WO_GE);
        disableTerm(pLevel, pTerm);
        testOp = OP_IdxGE;
      }else{
        testOp = nEq>0 ? OP_IdxGE : OP_Noop;
        topEq = 1;
      }
      if( testOp!=OP_Noop || (isMinQuery&&bRev) ){
        int nCol = nEq + topLimit;
        pLevel->iMem = ++pParse->nMem;
        if( isMinQuery && !topLimit ){
          nCol++;
          sqlite3VdbeAddOp2(v, OP_Null, 0, 0);
          topEq = 0;
        }
        buildIndexProbe(v, nCol, pIdx);
        if( bRev ){
          int op = topEq ? OP_MoveLe : OP_MoveLt;
          sqlite3VdbeAddOp2(v, op, iIdxCur, nxt);
        }else{
          sqlite3VdbeAddOp2(v, OP_Move, 0, pLevel->iMem);
        }
      }else if( bRev ){
        sqlite3VdbeAddOp2(v, OP_Last, iIdxCur, brk);
      }
   
      /* Generate the start key.  This is the key that defines the lower
      ** bound on the search.  There is no start key if there are no
      ** equality terms and if there is no "X>..." term.  In
      ** that case, generate a "Rewind" instruction in place of the
      ** start key search.
      **
      ** 2002-Dec-04: In the case of a reverse-order search, the so-called

        sqlite3ExprCode(pParse, pX->pRight, 0);
        sqlite3VdbeAddOp2(v, OP_IsNull, -(nEq+1), nxt);
        btmEq = pTerm->eOperator & (WO_LE|WO_GE);
        disableTerm(pLevel, pTerm);
      }else{
        btmEq = 1;
      }
      if( nEq>0 || btmLimit || (isMinQuery&&!bRev) ){
        int nCol = nEq + btmLimit;
        if( isMinQuery && !btmLimit ){
          nCol++;
          sqlite3VdbeAddOp2(v, OP_Null, 0, 0);
          btmEq = 0;
        }
        buildIndexProbe(v, nCol, pIdx);
        if( bRev ){
          pLevel->iMem = ++pParse->nMem;
          sqlite3VdbeAddOp2(v, OP_Move, 0, pLevel->iMem);
          testOp = OP_IdxLT;
        }else{
          int op = btmEq ? OP_MoveGe : OP_MoveGt;

      pLevel->p2 = start;
    }else if( pLevel->flags & WHERE_COLUMN_EQ ){
      /* Case 4:  There is an index and all terms of the WHERE clause that
      **          refer to the index using the "==" or "IN" operators.
      */
      int start;
      int nEq = pLevel->nEq;
      int isMinQuery = 0;      /* If this is an optimized SELECT min(x) ... */

      /* Generate code to evaluate all constraint terms using == or IN
      ** and leave the values of those terms on the stack.
      */
      codeAllEqualityTerms(pParse, pLevel, &wc, notReady);
      nxt = pLevel->nxt;

      if( (obflag==ORDERBY_MIN)
       && (pLevel->flags&WHERE_ORDERBY) 
       && (pIdx->nColumn>nEq)
       && (pOrderBy->a[0].pExpr->iColumn==pIdx->aiColumn[nEq])
      ){
        int h;
        isMinQuery = 1;
        for(h=0; h<nEq; h++){
          sqlite3VdbeAddOp1(v, OP_Copy, 1-nEq);
        }
        buildIndexProbe(v, nEq, pIdx);
        sqlite3VdbeAddOp2(v, OP_Copy, 0, pLevel->iMem);
        sqlite3VdbeAddOp2(v, OP_Pop, 1, 0);
        sqlite3VdbeAddOp2(v, OP_Null, 0, 0);
        buildIndexProbe(v, nEq+1, pIdx);
      }else{
        /* Generate a single key that will be used to both start and 
        ** terminate the search
        */
        buildIndexProbe(v, nEq, pIdx);
        sqlite3VdbeAddOp2(v, OP_Copy, 0, pLevel->iMem);
      }

      /* Generate code (1) to move to the first matching element of the table.
      ** Then generate code (2) that jumps to "nxt" after the cursor is past
      ** the last matching element of the table.  The code (1) is executed
      ** once to initialize the search, the code (2) is executed before each
      ** iteration of the scan to see if the scan has finished. */
      if( bRev ){
        /* Scan in reverse order */
        sqlite3VdbeAddOp2(v, (isMinQuery?OP_MoveLt:OP_MoveLe), iIdxCur, nxt);
        start = sqlite3VdbeAddOp2(v, OP_SCopy, pLevel->iMem, 0);
        sqlite3VdbeAddOp2(v, OP_IdxLT, iIdxCur, nxt);
        pLevel->op = OP_Prev;
      }else{
        /* Scan in the forward order */
        sqlite3VdbeAddOp2(v, (isMinQuery?OP_MoveGt:OP_MoveGe), iIdxCur, nxt);
        start = sqlite3VdbeAddOp2(v, OP_SCopy, pLevel->iMem, 0);
        sqlite3VdbeAddOp4(v, OP_IdxGE, iIdxCur, nxt, 0, "+", P4_STATIC);
        pLevel->op = OP_Next;
      }
      if( !omitTable ){
        sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, 0);
        sqlite3VdbeAddOp2(v, OP_MoveGe, iCur, 0);

#    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 script is page cache subsystem.
#
# $Id: collate4.test,v 1.9 2008/01/05 17:39:30 danielk1977 Exp $

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

db collate TEXT text_collate
proc text_collate {a b} {
  return [string compare $a $b]

  # Test that the index with collation type TEXT is used.
  execsql {
    CREATE INDEX collate4i1 ON collate4t1(a);
  }
  count {
    SELECT min(a) FROM collate4t1;
  }
} {10 1}
do_test collate4-4.4 {
  count {
    SELECT max(a) FROM collate4t1;
  }
} {20 0}
do_test collate4-4.5 {
  # Test that the index with collation type NUMERIC is not used.
  execsql {
    DROP INDEX collate4i1;
    CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC);
  }
  count {

#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.20 2008/01/05 17:39:30 danielk1977 Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;

do_test minmax-1.5 {
  execsql {CREATE INDEX t1i1 ON t1(x)}
  set sqlite_search_count 0
  execsql {SELECT min(x) FROM t1}
} {1}
do_test minmax-1.6 {
  set sqlite_search_count
} {1}
do_test minmax-1.7 {
  set sqlite_search_count 0
  execsql {SELECT max(x) FROM t1}
} {20}
do_test minmax-1.8 {
  set sqlite_search_count
} {0}
do_test minmax-1.9 {
  set sqlite_search_count 0
  execsql {SELECT max(y) FROM t1}
} {5}
do_test minmax-1.10 {
  set sqlite_search_count
} {19}

# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.  This file makes sure that the min/max
# optimization works right in the presence of descending
# indices.  Ticket #2514.
#
# $Id: minmax2.test,v 1.2 2008/01/05 17:39:30 danielk1977 Exp $

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

do_test minmax2-1.0 {
  execsql {
    PRAGMA legacy_file_format=0;

do_test minmax2-1.5 {
  execsql {CREATE INDEX t1i1 ON t1(x DESC)}
  set sqlite_search_count 0
  execsql {SELECT min(x) FROM t1}
} {1}
do_test minmax2-1.6 {
  set sqlite_search_count
} {1}
do_test minmax2-1.7 {
  set sqlite_search_count 0
  execsql {SELECT max(x) FROM t1}
} {20}
do_test minmax2-1.8 {
  set sqlite_search_count
} {0}
do_test minmax2-1.9 {
  set sqlite_search_count 0
  execsql {SELECT max(y) FROM t1}
} {5}
do_test minmax2-1.10 {
  set sqlite_search_count
} {19}

# 2008 January 5
#
# 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: minmax3.test,v 1.1 2008/01/05 17:39:30 danielk1977 Exp $

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

# Do an SQL statement.  Append the search count to the end of the result.
#
proc count sql {
  set ::sqlite_search_count 0
  return [concat [execsql $sql] $::sqlite_search_count]
}

# This procedure sets the value of the file-format in file 'test.db'
# to $newval. Also, the schema cookie is incremented.
# 
proc set_file_format {newval} {
  set bt [btree_open test.db 10 0]
  btree_begin_transaction $bt
  set meta [btree_get_meta $bt]
  lset meta 2 $newval                    ;# File format
  lset meta 1 [expr [lindex $meta 1]+1]  ;# Schema cookie
  eval "btree_update_meta $bt $meta"
  btree_commit $bt
  btree_close $bt
}

# Create the file as file-format 4 (DESC index support). This is 
# required to exercise a few cases in where.c.
#
execsql { select * from sqlite_master }
set_file_format 4

do_test minmax3-1.0 {
  execsql {
    BEGIN;
    CREATE TABLE t1(x, y, z);
    INSERT INTO t1 VALUES('1', 'I',   'one');
    INSERT INTO t1 VALUES('2', 'IV',  'four');
    INSERT INTO t1 VALUES('2', NULL,  'three');
    INSERT INTO t1 VALUES('2', 'II',  'two');
    INSERT INTO t1 VALUES('2', 'V',   'five');
    INSERT INTO t1 VALUES('3', 'VI',  'six');
    COMMIT;
  }
} {}
do_test minmax3-1.1.1 {
  # Linear scan.
  count { SELECT max(y) FROM t1 WHERE x = '2'; }
} {V 5}
do_test minmax3-1.1.2 {
  # Index optimizes the WHERE x='2' constraint.
  execsql { CREATE INDEX i1 ON t1(x) }
  count   { SELECT max(y) FROM t1 WHERE x = '2'; }
} {V 9}
do_test minmax3-1.1.3 {
  # Index optimizes the WHERE x='2' constraint and the MAX(y).
  execsql { CREATE INDEX i2 ON t1(x,y) }
  count   { SELECT max(y) FROM t1 WHERE x = '2'; }
} {V 1}
do_test minmax3-1.1.4 {
  # Index optimizes the WHERE x='2' constraint and the MAX(y).
  execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) }
  count   { SELECT max(y) FROM t1 WHERE x = '2'; }
} {V 1}
do_test minmax3-1.1.5 {
  count   { SELECT max(y) FROM t1 WHERE x = '2' AND y != 'V'; }
} {IV 2}
do_test minmax3-1.1.6 {
  count   { SELECT max(y) FROM t1 WHERE x = '2' AND y < 'V'; }
} {IV 1}
do_test minmax3-1.1.6 {
  count   { SELECT max(y) FROM t1 WHERE x = '2' AND z != 'five'; }
} {IV 4}

do_test minmax3-1.2.1 {
  # Linear scan of t1.
  execsql { DROP INDEX i1 ; DROP INDEX i2 }
  count { SELECT min(y) FROM t1 WHERE x = '2'; }
} {II 5}
do_test minmax3-1.2.2 {
  # Index i1 optimizes the WHERE x='2' constraint.
  execsql { CREATE INDEX i1 ON t1(x) }
  count   { SELECT min(y) FROM t1 WHERE x = '2'; }
} {II 9}
do_test minmax3-1.2.3 {
  # Index i2 optimizes the WHERE x='2' constraint and the min(y).
  execsql { CREATE INDEX i2 ON t1(x,y) }
  count   { SELECT min(y) FROM t1 WHERE x = '2'; }
} {II 1}
do_test minmax3-1.2.4 {
  # Index optimizes the WHERE x='2' constraint and the MAX(y).
  execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) }
  count   { SELECT min(y) FROM t1 WHERE x = '2'; }
} {II 1}

do_test minmax3-1.3.1 {
  # Linear scan
  execsql { DROP INDEX i1 ; DROP INDEX i2 }
  count   { SELECT min(y) FROM t1; }
} {I 5}
do_test minmax3-1.3.2 {
  # Index i1 optimizes the min(y)
  execsql { CREATE INDEX i1 ON t1(y) }
  count   { SELECT min(y) FROM t1; }
} {I 1}
do_test minmax3-1.3.3 {
  # Index i1 optimizes the min(y)
  execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) }
  count   { SELECT min(y) FROM t1; }
} {I 1}

do_test minmax3-1.4.1 {
  # Linear scan
  execsql { DROP INDEX i1 }
  count   { SELECT max(y) FROM t1; }
} {VI 5}
do_test minmax3-1.4.2 {
  # Index i1 optimizes the max(y)
  execsql { CREATE INDEX i1 ON t1(y) }
  count   { SELECT max(y) FROM t1; }
} {VI 0}
do_test minmax3-1.4.3 {
  # Index i1 optimizes the max(y)
  execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) }
  execsql   { SELECT y from t1}
  count   { SELECT max(y) FROM t1; }
} {VI 0}
do_test minmax3-1.4.4 {
  execsql { DROP INDEX i1 }
} {}


finish_test