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
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.449 2008/07/08 18:05:26 drh Exp $
*/
#include "sqliteInt.h"


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

      **     satisfying the 'ORDER BY' clause than it does in other cases.
      **     Refer to code and comments in where.c for details.
      */
      flag = minMaxQuery(pParse, p);
      if( flag ){
        pDel = pMinMax = sqlite3ExprListDup(db, p->pEList->a[0].pExpr->pList);
        if( pMinMax && !db->mallocFailed ){
          pMinMax->a[0].sortOrder = flag!=WHERE_ORDERBY_MIN;
          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.

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

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

      if( IsVirtual(pTabItem->pTab) ){
        sqlite3_index_info **ppIdxInfo = &pWInfo->a[j].pIdxInfo;
        cost = bestVirtualIndex(pParse, &wc, pTabItem, notReady,
                                ppOrderBy ? *ppOrderBy : 0, i==0,
                                ppIdxInfo);
        flags = WHERE_VIRTUALTABLE;
        pIndex = *ppIdxInfo;
        assert( pIndex!=0 );
        if( pIndex->orderByConsumed ){
          flags = WHERE_VIRTUALTABLE | WHERE_ORDERBY;
        }
        pIdx = 0;
        nEq = 0;
        if( (SQLITE_BIG_DBL/2.0)<cost ){
          /* The cost is not allowed to be larger than SQLITE_BIG_DBL (the
          ** inital value of lowestCost in this loop. If it is, then

      ** 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( (wflags&WHERE_ORDERBY_MIN)!=0
       && (pLevel->flags&WHERE_ORDERBY)
       && (pIdx->nColumn>nEq)

      ){
        assert( pOrderBy->nExpr==1 );
        assert( pOrderBy->a[0].pExpr->iColumn==pIdx->aiColumn[nEq] );
        isMinQuery = 1;
      }

      /* Find any inequality constraint terms for the start and end 
      ** of the range. 
      */
      if( pLevel->flags & WHERE_TOP_LIMIT ){

#
#***********************************************************************
# 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.21 2008/07/08 18:05:26 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;

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}

do_test minmax-1.21 {
  execsql {SELECT min(x) FROM t1 WHERE x=5}
} {5}
do_test minmax-1.22 {
  execsql {SELECT min(x) FROM t1 WHERE x>=5}
} {5}
do_test minmax-1.23 {
  execsql {SELECT min(x) FROM t1 WHERE x>=4.5}
} {5}
do_test minmax-1.24 {
  execsql {SELECT min(x) FROM t1 WHERE x<4.5}
} {1}

do_test minmax-2.0 {
  execsql {
    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
    INSERT INTO t2 SELECT * FROM t1;
  }
  set sqlite_search_count 0

  } {{} {}}
}
do_test minmax-10.12 {
  execsql {
    SELECT min(x), max(x) FROM t6;
  }
} {{} {}}


do_test minmax-11.1 {
  execsql {
    CREATE INDEX t1i2 ON t1(y,x);
    SELECT min(x) FROM t1 WHERE y=5;
  }
} {16}
do_test minmax-11.2 {
  execsql {
    SELECT max(x) FROM t1 WHERE y=5;
  }
} {20}
do_test minmax-11.3 {
  execsql {
    SELECT min(x) FROM t1 WHERE y=6;
  }
} {{}}
do_test minmax-11.4 {
  execsql {
    SELECT max(x) FROM t1 WHERE y=6;
  }
} {{}}
do_test minmax-11.5 {
  execsql {
    SELECT min(x) FROM t1 WHERE y=1;
  }
} {1}
do_test minmax-11.6 {
  execsql {
    SELECT max(x) FROM t1 WHERE y=1;
  }
} {1}
do_test minmax-11.7 {
  execsql {
    SELECT min(x) FROM t1 WHERE y=0;
  }
} {{}}
do_test minmax-11.8 {
  execsql {
    SELECT max(x) FROM t1 WHERE y=0;
  }
} {{}}
do_test minmax-11.9 {
  execsql {
    SELECT min(x) FROM t1 WHERE y=5 AND x>=17.5;
  }
} {18}
do_test minmax-11.10 {
  execsql {
    SELECT max(x) FROM t1 WHERE y=5 AND x>=17.5;
  }
} {20}

do_test minmax-12.1 {
  execsql {
    CREATE TABLE t7(a,b,c);
    INSERT INTO t7 SELECT y, x, x*y FROM t1;
    INSERT INTO t7 SELECT y, x, x*y+1000 FROM t1;
    CREATE INDEX t7i1 ON t7(a,b,c);
    SELECT min(a) FROM t7;
  }
} {1}
do_test minmax-12.2 {
  execsql {
    SELECT max(a) FROM t7;
  }
} {5}
do_test minmax-12.3 {
  execsql {
    SELECT max(a) FROM t7 WHERE a=5;
  }
} {5}
do_test minmax-12.4 {
  execsql {
    SELECT min(b) FROM t7 WHERE a=5;
  }
} {16}
do_test minmax-12.5 {
  execsql {
    SELECT max(b) FROM t7 WHERE a=5;
  }
} {20}
do_test minmax-12.6 {
  execsql {
    SELECT min(b) FROM t7 WHERE a=4;
  }
} {8}
do_test minmax-12.7 {
  execsql {
    SELECT max(b) FROM t7 WHERE a=4;
  }
} {15}
do_test minmax-12.8 {
  execsql {
    SELECT min(c) FROM t7 WHERE a=4 AND b=10;
  }
} {40}
do_test minmax-12.9 {
  execsql {
    SELECT max(c) FROM t7 WHERE a=4 AND b=10;
  }
} {1040}
do_test minmax-12.10 {
  execsql {
    SELECT min(rowid) FROM t7;
  }
} {1}
do_test minmax-12.11 {
  execsql {
    SELECT max(rowid) FROM t7;
  }
} {40}
do_test minmax-12.12 {
  execsql {
    SELECT min(rowid) FROM t7 WHERE a=3;
  }
} {4}
do_test minmax-12.13 {
  execsql {
    SELECT max(rowid) FROM t7 WHERE a=3;
  }
} {27}
do_test minmax-12.14 {
  execsql {
    SELECT min(rowid) FROM t7 WHERE a=3 AND b=5;
  }
} {5}
do_test minmax-12.15 {
  execsql {
    SELECT max(rowid) FROM t7 WHERE a=3 AND b=5;
  }
} {25}
do_test minmax-12.16 {
  execsql {
    SELECT min(rowid) FROM t7 WHERE a=3 AND b=5 AND c=1015;
  }
} {25}
do_test minmax-12.17 {
  execsql {
    SELECT max(rowid) FROM t7 WHERE a=3 AND b=5 AND c=15;
  }
} {5}




finish_test