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.116 2006/01/12 22:17:50 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
/* #include <math.h> */
#include <stdlib.h>
#include <assert.h>
#include "vdbeInt.h"

struct SumCtx {
  double sum;     /* Sum of terms */
  int cnt;        /* Number of elements summed */
  u8 seenFloat;   /* True if there has been any floating point value */
};

/*
** Routines used to compute the sum, average, and total.
**
** The SUM() function follows the (broken) SQL standard which means
** that it returns NULL if it sums over no inputs.  TOTAL returns
** 0.0 in that case.  In addition, TOTAL always returns a float where
** SUM might return an integer if it never encounters a floating point
** value.
*/
static void sumStep(sqlite3_context *context, int argc, sqlite3_value **argv){
  SumCtx *p;
  int type;
  assert( argc==1 );
  p = sqlite3_aggregate_context(context, sizeof(*p));
  type = sqlite3_value_type(argv[0]);

}
static void avgFinalize(sqlite3_context *context){
  SumCtx *p;
  p = sqlite3_aggregate_context(context, 0);
  if( p && p->cnt>0 ){
    sqlite3_result_double(context, p->sum/(double)p->cnt);
  }
}
static void totalFinalize(sqlite3_context *context){
  SumCtx *p;
  p = sqlite3_aggregate_context(context, 0);
  sqlite3_result_double(context, p ? p->sum : 0.0);
}

/*
** An instance of the following structure holds the context of a
** variance or standard deviation computation.
*/
typedef struct StdDevCtx StdDevCtx;

    u8 needCollSeq;
    void (*xStep)(sqlite3_context*,int,sqlite3_value**);
    void (*xFinalize)(sqlite3_context*);
  } aAggs[] = {
    { "min",    1, 0, 1, minmaxStep,   minMaxFinalize },
    { "max",    1, 2, 1, minmaxStep,   minMaxFinalize },
    { "sum",    1, 0, 0, sumStep,      sumFinalize    },
    { "total",  1, 0, 0, sumStep,      totalFinalize    },
    { "avg",    1, 0, 0, sumStep,      avgFinalize    },
    { "count",  0, 0, 0, countStep,    countFinalize  },
    { "count",  1, 0, 0, countStep,    countFinalize  },
  };
  int i;

  for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){

#
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 0 1}

# The sum of zero entries is a NULL, but the total of zero entries is 0.
#
do_test null-3.2 {
  execsql {
    SELECT sum(b), total(b) FROM t1 WHERE b<0
  }
} {{} 0.0}

# 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 {

#
# Run this Tcl script to generate the lang-*.html files.
#
set rcsid {$Id: lang.tcl,v 1.105 2006/01/12 22:17:50 drh Exp $}
source common.tcl

if {[llength $argv]>0} {
  set outputdir [lindex $argv 0]
} else {
  set outputdir ""
}

<td valign="top" align="right">min(<i>X</i>)</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>)<br>total(<i>X</i>)</td>
<td valign="top">Return the numeric sum of all numeric values in the group.
   If there are no input rows or all values are NULL, then sum() returns
   NULL but total() returns zero.
   NULL is not a helpful result in that case (the correct answer should be
   zero) but the SQL standard requires that behavior from sum() and that is how 
   most other SQL database engines implement sum() so SQLite does it that way
   in order to be compatible.   The non-standard total() function is provided

   as a convenient way
   to work around this design problem in the SQL language.</td>
</tr>
</table>
}


Section INSERT insert