** 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.121 2006/02/09 22:13:42 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 {
  LONGDOUBLE_TYPE sum;    /* Sum of terms */

  i64 cnt;                /* Number of elements summed */
  u8 approx;              /* True if sum is approximate */


};

/*
** 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 ){
      p->sum += sqlite3_value_int64(argv[0]);
      if( !p->approx ){
        i64 iVal;
        p->approx = p->sum!=(LONGDOUBLE_TYPE)(iVal = (i64)p->sum);





      }
    }else{
      p->sum += 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->approx ){
      sqlite3_result_double(context, p->sum);
    }else{
      sqlite3_result_int64(context, (i64)p->sum);
    }
  }
}
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);
}

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

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

#    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.47 2006/02/09 22:13:42 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: 64-bit overflow in sum()


#
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

# Ticket #1669, #1670: I am told that if an integer overflow occurs
# during a sum that the result should be an error.  This strikes me
# as being brittle.  So I'm not doing it that way.
# making the SQL-standard version SUM() even more useless than it
# was before.  
#
# The non-standard TOTAL() function continues to give a helpful result.
#
do_test func-18.11 {
  execsql {
    SELECT typeof(sum(x)) FROM t6
  }
} integer
do_test func-18.12 {
  execsql {
    INSERT INTO t6 VALUES(1<<62);
    SELECT sum(x) - ((1<<62)*2.0+1) from t6;
  }
} {0.0}
do_test func-18.13 {
  execsql {
    SELECT total(x) - ((1<<62)*2.0+1) FROM t6
  }
} 0.0























































finish_test

#    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.108 2006/02/09 22:13:42 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
   and the sum is exact.  If any input to sum() is neither an integer or
   a NULL or if the
   an integer overflow occurs at any point during the computation,
   then sum() returns a floating point value
   which might be an approximation to the true sum.</td>





</tr>
</table>
}


Section INSERT insert

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