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.122 2006/02/11 17:34:00 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
/* #include <math.h> */
#include <stdlib.h>
#include <assert.h>
#include "vdbeInt.h"

/*
** An instance of the following structure holds the context of a
** sum() or avg() aggregate computation.
*/
typedef struct SumCtx SumCtx;
struct SumCtx {
  double rSum;      /* Floating point sum */
  i64 iSum;         /* Integer sum */   
  i64 cnt;          /* Number of elements summed */
  u8 overflow;      /* True if integer overflow seen */
  u8 approx;        /* True if non-integer value was input to the sum */
};

/*
** 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

  int type;
  assert( argc==1 );
  p = sqlite3_aggregate_context(context, sizeof(*p));
  type = sqlite3_value_numeric_type(argv[0]);
  if( p && type!=SQLITE_NULL ){
    p->cnt++;
    if( type==SQLITE_INTEGER ){
      i64 v = sqlite3_value_int64(argv[0]);
      p->rSum += v;
      if( (p->approx|p->overflow)==0 ){
        i64 iNewSum = p->iSum + v;
        int s1 = p->iSum >> (sizeof(i64)*8-1);
        int s2 = v       >> (sizeof(i64)*8-1);
        int s3 = iNewSum >> (sizeof(i64)*8-1);
        p->overflow = (s1&s2&~s3) | (~s1&~s2&s3);
        p->iSum = iNewSum;
      }
    }else{
      p->rSum += sqlite3_value_double(argv[0]);
      p->approx = 1;
    }
  }
}
static void sumFinalize(sqlite3_context *context){
  SumCtx *p;
  p = sqlite3_aggregate_context(context, 0);
  if( p && p->cnt>0 ){
    if( p->overflow ){
      sqlite3_result_error(context,"integer overflow",-1);
    }else if( p->approx ){
      sqlite3_result_double(context, p->rSum);
    }else{
      sqlite3_result_int64(context, p->iSum);
    }
  }
}
static void avgFinalize(sqlite3_context *context){
  SumCtx *p;
  p = sqlite3_aggregate_context(context, 0);
  if( p && p->cnt>0 ){
    sqlite3_result_double(context, p->rSum/(double)p->cnt);
  }
}
static void totalFinalize(sqlite3_context *context){
  SumCtx *p;
  p = sqlite3_aggregate_context(context, 0);
  sqlite3_result_double(context, p ? p->rSum : 0.0);
}

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

# 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.48 2006/02/11 17:34:01 drh Exp $

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

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

do_test func-18.6 {
  execsql {
    INSERT INTO t5 VALUES(123);
    SELECT sum(x), total(x) FROM t5
  }
} {123 123.0}

# Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes
# an error. The non-standard TOTAL() function continues to give a helpful
# result.
#
do_test func-18.10 {
  execsql {
    CREATE TABLE t6(x INTEGER);
    INSERT INTO t6 VALUES(1);
    INSERT INTO t6 VALUES(1<<62);
    SELECT sum(x) - ((1<<62)+1) from t6;
  }
} 0









do_test func-18.11 {
  execsql {
    SELECT typeof(sum(x)) FROM t6
  }
} integer
do_test func-18.12 {
  catchsql {
    INSERT INTO t6 VALUES(1<<62);
    SELECT sum(x) - ((1<<62)*2.0+1) from t6;
  }
} {1 {integer overflow}}
do_test func-18.13 {
  execsql {
    SELECT total(x) - ((1<<62)*2.0+1) FROM t6
  }
} 0.0
do_test func-18.14 {
  execsql {
    SELECT sum(-9223372036854775805);
  }
} -9223372036854775805
do_test func-18.15 {
  catchsql {
    SELECT sum(x) FROM 
       (SELECT 9223372036854775807 AS x UNION ALL
        SELECT 10 AS x);
  }
} {1 {integer overflow}}
do_test func-18.16 {
  catchsql {
    SELECT sum(x) FROM 
       (SELECT 9223372036854775807 AS x UNION ALL
        SELECT -10 AS x);
  }
} {0 9223372036854775797}
do_test func-18.17 {
  catchsql {
    SELECT sum(x) FROM 
       (SELECT -9223372036854775807 AS x UNION ALL
        SELECT 10 AS x);
  }
} {0 -9223372036854775797}
do_test func-18.18 {
  catchsql {
    SELECT sum(x) FROM 
       (SELECT -9223372036854775807 AS x UNION ALL
        SELECT -10 AS x);
  }
} {1 {integer overflow}}
do_test func-18.19 {
  catchsql {
    SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x);
  }
} {0 -1}
do_test func-18.20 {
  catchsql {
    SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x);
  }
} {0 1}
do_test func-18.21 {
  catchsql {
    SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x);
  }
} {0 -1}
do_test func-18.22 {
  catchsql {
    SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x);
  }
} {0 1}


finish_test

#
# Run this Tcl script to generate the lang-*.html files.
#
set rcsid {$Id: lang.tcl,v 1.109 2006/02/11 17:34:02 drh Exp $}
source common.tcl

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

   but the SQL standard requires it and most other
   SQL database engines implement sum() that way so SQLite does it in the
   same 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.</p>

   <p>The result of total() is always a floating point value.
   The result of sum() is an integer value if all non-NULL inputs are integers.
   If any input to sum() is neither an integer or a NULL


   then sum() returns a floating point value
   which might be an approximation to the true sum.</p>

   <p>Sum() will throw an "integer overflow" exception if all inputs
   are integers or NULL
   and an integer overflow occurs at any point during the computation.
   Total() never throws an exception.</p>
</tr>
</table>
}


Section INSERT insert