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.18 2002/05/29 23:22:23 drh Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"

/*
** An instance of the following structure holds the context of a
** sum() or avg() aggregate computation.
*/
typedef struct SumCtx SumCtx;
struct SumCtx {
  double sum;     /* Sum of terms */
  int cnt;        /* Number of elements summed */
};

/*
** Routines used to compute the sum or average.
*/
static void sumStep(sqlite_func *context, int argc, const char **argv){
  SumCtx *p;

  if( argc<1 ) return;
  p = sqlite_aggregate_context(context, sizeof(*p));

  if( p && argv[0] ){
    p->sum += atof(argv[0]);
    p->cnt++;
  }
}
static void sumFinalize(sqlite_func *context){
  SumCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  sqlite_set_result_double(context, p ? p->sum : 0.0);
}
static void avgFinalize(sqlite_func *context){
  SumCtx *p;

  p = sqlite_aggregate_context(context, sizeof(*p));

  if( p && p->cnt>0 ){
    sqlite_set_result_double(context, p->sum/(double)p->cnt);
  }
}

/*
** An instance of the following structure holds the context of a
** variance or standard deviation computation.
*/
typedef struct StdDevCtx StdDevCtx;
struct StdDevCtx {
  double sum;     /* Sum of terms */
  double sum2;    /* Sum of the squares of terms */
  int cnt;        /* Number of terms counted */
};

#if 0   /* Omit because math library is required */
/*
** Routines used to compute the standard deviation as an aggregate.
*/
static void stdDevStep(sqlite_func *context, int argc, const char **argv){
  StdDevCtx *p;
  double x;
  if( argc<1 ) return;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p && argv[0] ){
    x = atof(argv[0]);
    p->sum += x;
    p->sum2 += x*x;
    p->cnt++;
  }
}
static void stdDevFinalize(sqlite_func *context){
  double rN = sqlite_aggregate_count(context);
  StdDevCtx *p = sqlite_aggregate_context(context, sizeof(*p));
  if( p && p->cnt>1 ){
    double rCnt = cnt;
    sqlite_set_result_double(context, 
       sqrt((p->sum2 - p->sum*p->sum/rCnt)/(rCnt-1.0)));
  }
}
#endif

/*
** The following structure keeps track of state information for the
** count() aggregate function.

/*
** This function tracks state information for the min() and max()
** aggregate functions.
*/
typedef struct MinMaxCtx MinMaxCtx;
struct MinMaxCtx {

  char *z;         /* The best so far */
  char zBuf[28];   /* Space that can be used for storage */
};

/*
** Routines to implement min() and max() aggregate functions.
*/
static void minStep(sqlite_func *context, int argc, const char **argv){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p==0 || argc<1 || argv[0]==0 ) return;




  if( sqlite_aggregate_count(context)==1 || sqliteCompare(argv[0],p->z)<0 ){
    int len;
    if( p->z && p->z!=p->zBuf ){
      sqliteFree(p->z);
    }
    len = strlen(argv[0]);
    if( len < sizeof(p->zBuf) ){
      p->z = p->zBuf;
    }else{
      p->z = sqliteMalloc( len+1 );
      if( p->z==0 ) return;
    }
    strcpy(p->z, argv[0]);
  }
}
static void maxStep(sqlite_func *context, int argc, const char **argv){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p==0 || argc<1 || argv[0]==0 ) return;




  if( sqlite_aggregate_count(context)==1 || sqliteCompare(argv[0],p->z)>0 ){
    int len;
    if( p->z && p->z!=p->zBuf ){
      sqliteFree(p->z);
    }
    len = strlen(argv[0]);
    if( len < sizeof(p->zBuf) ){
      p->z = p->zBuf;
    }else{
      p->z = sqliteMalloc( len+1 );
      if( p->z==0 ) return;
    }
    strcpy(p->z, argv[0]);
  }
}
static void minMaxFinalize(sqlite_func *context){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p && p->z ){
    sqlite_set_result_string(context, p->z, strlen(p->z));
  }
  if( p && p->z && p->z!=p->zBuf ){
    sqliteFree(p->z);
  }
}

    { "round",      1, roundFunc  },
    { "round",      2, roundFunc  },
    { "upper",      1, upperFunc  },
    { "lower",      1, lowerFunc  },
    { "coalesce",  -1, ifnullFunc },
    { "coalesce",   0, 0          },
    { "coalesce",   1, 0          },
    { "ifnull",     2, ifnullFunc },
    { "random",    -1, randomFunc },
    { "like",       2, likeFunc   },
    { "glob",       2, globFunc   },
    { "nullif",     2, nullifFunc },
  };
  static struct {
    char *zName;

# 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.12 2002/05/29 23:22:23 drh Exp $

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

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

} {1 nil 345 nil 67890}

# Test the last_insert_rowid() function
#
do_test func-7.1 {
  execsql {SELECT last_insert_rowid()}
} [db last_insert_rowid]

# 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 1 67890 5}


finish_test

#
#***********************************************************************
# 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.3 2002/05/29 23:22:23 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),-1), coalesce(max(x),-1) FROM
      (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
  }
} {1 20}

finish_test

# 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.29 2002/05/29 23:22:23 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}
} {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 {

} {0 {23 45}}
do_test select1-2.13 {
  set v [catch {execsql {SELECT MAX(f1)+1 FROM test1}} msg]
  lappend v $msg
} {0 34}
do_test select1-2.13.1 {
  execsql {SELECT coalesce(max(a),'xyzzy') FROM t3}
} {abc}
do_test select1-2.13.2 {
  execsql {SELECT max(coalesce(a,'xyzzy')) FROM t3}
} {xyzzy}
do_test select1-2.14 {
  set v [catch {execsql {SELECT SUM(*) FROM test1}} msg]
  lappend v $msg
} {1 {wrong number of arguments to function SUM()}}