SQLite

** This file contains the C functions that implement various SQL
** functions of SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: func.c,v 1.77 2004/06/28 13:09:11 danielk1977 Exp $
** $Id: func.c,v 1.78 2004/07/18 20:52:32 drh Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"
#include "vdbeInt.h"

  char zBuf[28];   /* Space that can be used for storage */
};

/*
** Routines to implement min() and max() aggregate functions.
*/
static void minmaxStep(sqlite3_context *context, int argc, sqlite3_value **argv){
  int max = 0;
  int cmp = 0;
  Mem *pArg  = (Mem *)argv[0];
  Mem *pBest;

  if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
  Mem *pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest));
  pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest));
  if( !pBest ) return;

  if( pBest->flags ){
    int max;
    int cmp;
    CollSeq *pColl = sqlite3GetFuncCollSeq(context);
    /* This step function is used for both the min() and max() aggregates,
    ** the only difference between the two being that the sense of the
    ** comparison is inverted. For the max() aggregate, the
    ** sqlite3_user_data() function returns (void *)-1. For min() it
    ** returns (void *)db, where db is the sqlite3* database pointer.
    ** Therefore the next statement sets variable 'max' to 1 for the max()

**    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.195 2004/06/21 10:45:09 danielk1977 Exp $
** $Id: select.c,v 1.196 2004/07/18 20:52:32 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.

  if( pIdx==0 ){
    sqlite3VdbeAddOp(v, seekOp, base, 0);
  }else{
    sqlite3VdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
    sqlite3VdbeOp3(v, OP_OpenRead, base+1, pIdx->tnum,
                   (char*)&pIdx->keyInfo, P3_KEYINFO);
    sqlite3VdbeAddOp(v, seekOp, base+1, 0);
    if( seekOp==OP_Rewind ){
      int addr;
      sqlite3VdbeAddOp(v, OP_SetNumColumns, base+1, pIdx->nColumn+1);
      sqlite3VdbeAddOp(v, OP_KeyAsData, base+1, 1);
      addr = sqlite3VdbeAddOp(v, OP_IdxColumn, base+1, 0);
      sqlite3VdbeAddOp(v, OP_NotNull, 1, addr+3);
      sqlite3VdbeAddOp(v, OP_Next, 1, addr);
    }
    sqlite3VdbeAddOp(v, OP_IdxRecno, base+1, 0);
    sqlite3VdbeAddOp(v, OP_Close, base+1, 0);
    sqlite3VdbeAddOp(v, OP_MoveGe, base, 0);
  }
  eList.nExpr = 1;
  memset(&eListItem, 0, sizeof(eListItem));
  eList.a = &eListItem;

# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing built-in functions.
#
# $Id: func.test,v 1.27 2004/06/29 13:18:24 danielk1977 Exp $
# $Id: func.test,v 1.28 2004/07/18 20:52:32 drh Exp $

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

# Create a table to work with.
#
do_test func-0.0 {

# Tests for aggregate functions and how they handle NULLs.
#
do_test func-8.1 {
  execsql {
    SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
  }
} {68236 3 22745.33 {} 67890 5}
} {68236 3 22745.33 1 67890 5}
do_test func-8.2 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
do_test func-8.3 {
  execsql {
    CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
    SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
  }
} {{}}
} {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
do_test func-8.4 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

# How do you test the random() function in a meaningful, deterministic way?

#
#***********************************************************************
# 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.10 2004/06/24 00:20:05 danielk1977 Exp $
# $Id: minmax.test,v 1.11 2004/07/18 20:52:32 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;

} {0}

do_test minmax-4.1 {
  execsql {
    SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
      (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
  }
} {-1 20}
} {1 20}
do_test minmax-4.2 {
  execsql {
    SELECT y, sum(x) FROM
      (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
    GROUP BY y ORDER BY y;
  }
} {1 1 2 5 3 22 4 92 5 90 6 0}

  execsql {
    SELECT max(rowid) FROM (
      SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
    )
  }
} {{}}

# If there is a NULL in an aggregate max(), ignore it.  If a NULL
# occurs in an aggregate min(), then the result will be NULL because
# If there is a NULL in an aggregate max() or min(), ignore it.  An
# aggregate min() or max() will only return NULL if all values are NULL.
# NULL compares less than all other values.
#
do_test minmax-10.1 {
  execsql {
    CREATE TABLE t6(x);
    INSERT INTO t6 VALUES(1);
    INSERT INTO t6 VALUES(2);
    INSERT INTO t6 VALUES(NULL);
    SELECT coalesce(min(x),-1) FROM t6;
  }
} {-1}
} {1}
do_test minmax-10.2 {
  execsql {
    SELECT max(x) FROM t6;
  }
} {2}
do_test minmax-10.3 {
  execsql {
    CREATE INDEX i6 ON t6(x);
    SELECT coalesce(min(x),-1) FROM t6;
  }
} {-1}
} {1}
do_test minmax-10.4 {
  execsql {
    SELECT max(x) FROM t6;
  }
} {2}
do_test minmax-10.5 {
  execsql {
    DELETE FROM t6 WHERE x NOT NULL;
    SELECT count(*) FROM t6;

  }
} 1
do_test minmax-10.6 {
  execsql {
    SELECT count(x) FROM t6;
  }
} 0
do_test minmax-10.7 {
  execsql {
    SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
  }
} {{} {}}
do_test minmax-10.8 {
  execsql {
    SELECT min(x), max(x) FROM t6;
  }
} {{} {}}
do_test minmax-10.9 {
  execsql {
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    SELECT count(*) FROM t6;
  }
} 1024
do_test minmax-10.10 {
  execsql {
    SELECT count(x) FROM t6;
  }
} 0
do_test minmax-10.11 {
  execsql {
    SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
  }
} {{} {}}
do_test minmax-10.12 {
  execsql {
    SELECT min(x), max(x) FROM t6;
  }
} {{} {}}


finish_test

# Check to see that NULL values are ignored in aggregate functions.
#
do_test null-3.1 {
  execsql {
    select count(*), count(b), count(c), sum(b), sum(c), 
           avg(b), avg(c), min(b), max(b) from t1;
  }
} {7 4 6 2 3 0.5 0.5 {} 1}
} {7 4 6 2 3 0.5 0.5 0 1}

# Check to see how WHERE clauses handle NULL values.  A NULL value
# is the same as UNKNOWN.  The WHERE clause should only select those
# rows that are TRUE.  FALSE and UNKNOWN rows are rejected.
#
do_test null-4.1 {
  execsql {

# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.33 2004/06/24 00:20:05 danielk1977 Exp $
# $Id: select1.test,v 1.34 2004/07/18 20:52:32 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {

} {0 11}
do_test select1-2.8 {
  set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg]
  lappend v [lsort $msg]
} {0 {11 33}}
do_test select1-2.8.1 {
  execsql {SELECT coalesce(min(a),'xyzzy') FROM t3}
} {xyzzy}
} {11}
do_test select1-2.8.2 {
  execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3}
} {11}
do_test select1-2.8.3 {
  execsql {SELECT min(b), min(b) FROM t4}
} [list $long $long]
do_test select1-2.9 {

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.70 2004/06/18 11:25:21 danielk1977 Exp $}
set rcsid {$Id: lang.tcl,v 1.71 2004/07/18 20:52:32 drh Exp $}
source common.tcl
header {Query Language Understood by SQLite}
puts {
<h2>SQL As Understood By SQLite</h2>

<p>The SQLite library understands most of the standard SQL
language.  But it does <a href="omitted.html">omit some features</a>

<td valign="top" align="right">max(<i>X</i>)</td>
<td valign="top">Return the maximum value of all values in the group.
The usual sort order is used to determine the maximum.</td>
</tr>

<tr>
<td valign="top" align="right">min(<i>X</i>)</td>
<td valign="top">Return the minimum value of all values in the group.
The usual sort order is used to determine the minimum.</td>
<td valign="top">Return the minimum non-NULL value of all values in the group.
The usual sort order is used to determine the minimum.  NULL is only returned
if all values in the group are NULL.</td>
</tr>

<tr>
<td valign="top" align="right">sum(<i>X</i>)</td>
<td valign="top">Return the numeric sum of all values in the group.</td>
</tr>
</table>