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.110 2005/09/08 20:37:43 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "vdbeInt.h"

      p->seenFloat = 1;
    }
  }
}
static void sumFinalize(sqlite3_context *context){
  SumCtx *p;
  p = sqlite3_aggregate_context(context, 0);
  if( p && p->cnt>0 ){

    if( p->seenFloat ){
      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);

# 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.40 2005/09/08 20:37:44 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.2 {
  execsql {
    INSERT INTO t5 VALUES(0.0);
    SELECT sum(x) FROM t5;
  }
} {9902.0}

# The sum of nothing is NULL.  But the sum of all NULLs is NULL.
#
do_test func-18.3 {
  execsql {
    DELETE FROM t5;
    SELECT sum(x) FROM t5;
  }
} {{}}
do_test func-18.4 {
  execsql {
    INSERT INTO t5 VALUES(NULL);
    SELECT sum(x) FROM t5
  }
} {{}}
do_test func-18.5 {

#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.16 2005/09/08 20:37:44 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {

    SELECT a, sum(b) FROM t2 WHERE b=5 GROUP BY a;
  }
} {}
do_test select3-7.2 {
  execsql {
    SELECT a, sum(b) FROM t2 WHERE b=5;
  }
} {{} {}}


finish_test

#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select5.test,v 1.13 2005/09/08 20:37:44 drh Exp $

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

# Build some test data
#
execsql {

    SELECT max(x) FROM t1 WHERE x>100
  }
} {{}}
do_test select5-4.5 {
  execsql {
    SELECT sum(x) FROM t1 WHERE x>100
  }
} {{}}

# Some tests for queries with a GROUP BY clause but no aggregate functions.
#
# Note: The query in test case 5-5.5 are not legal SQL. So if the 
# implementation changes in the future and it returns different results,
# this is not such a big deal.
#

      CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
      CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
  
      CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW 
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  old.a, old.b, 
  	  (SELECT coalesce(sum(a),0) FROM tbl),
          (SELECT coalesce(sum(b),0) FROM tbl), 
  	  new.a, new.b);
      END;
  
      CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW 
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  old.a, old.b, 
  	  (SELECT coalesce(sum(a),0) FROM tbl),
          (SELECT coalesce(sum(b),0) FROM tbl), 
  	  new.a, new.b);
      END;
  
      CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW
        WHEN old.a = 1
        BEGIN
        INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog), 
  	  old.a, old.b, 
  	  (SELECT coalesce(sum(a),0) FROM tbl),
          (SELECT coalesce(sum(b),0) FROM tbl), 
  	  new.a, new.b);
      END;
    }
  
    do_test trigger2-1.$ii.1 {
      set r {}
      foreach v [execsql { 

      DELETE FROM tbl;
      INSERT INTO tbl VALUES (100, 100);
      INSERT INTO tbl VALUES (300, 200);
      CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  old.a, old.b, 
  	  (SELECT coalesce(sum(a),0) FROM tbl),
          (SELECT coalesce(sum(b),0) FROM tbl), 
  	  0, 0);
      END;
  
      CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  old.a, old.b, 
  	  (SELECT coalesce(sum(a),0) FROM tbl),
          (SELECT coalesce(sum(b),0) FROM tbl), 
  	  0, 0);
      END;
    }
    do_test trigger2-1.$ii.2 {
      set r {}
      foreach v [execsql {
        DELETE FROM tbl;

  
    execsql {
      DELETE FROM rlog;
      CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  0, 0,
  	  (SELECT coalesce(sum(a),0) FROM tbl),
          (SELECT coalesce(sum(b),0) FROM tbl), 
  	  new.a, new.b);
      END;
  
      CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  0, 0,
  	  (SELECT coalesce(sum(a),0) FROM tbl),
          (SELECT coalesce(sum(b),0) FROM tbl), 
  	  new.a, new.b);
      END;
    }
    do_test trigger2-1.$ii.3 {
      execsql {
  
        CREATE TABLE other_tbl(a, b);

#
# Run this Tcl script to generate the lang-*.html files.
#
set rcsid {$Id: lang.tcl,v 1.99 2005/09/08 20:37:44 drh Exp $}
source common.tcl

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

aggregate functions written in C may be added using the 
<a href="capi3ref.html#sqlite3_create_function">sqlite3_create_function()</a>
API.</p>

<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120>avg(<i>X</i>)</td>
<td valign="top">Return the average value of all non-NULL <i>X</i> within a
group.  Non-numeric values are interpreted as 0.</td>
</tr>

<tr>
<td valign="top" align="right">count(<i>X</i>)<br>count(*)</td>
<td valign="top">The first form return a count of the number of times
that <i>X</i> is not NULL in a group.  The second form (with no argument)
returns the total number of rows in the group.</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 numeric values in the group.
   If there are no input rows or all values are NULL, then NULL is returned.
   NULL is not a helpful result in that case (the correct answer should be
   zero) but it is what the SQL standard requires and how 
   most other SQL database engines operate so SQLite does it that way
   in order to be compatible. 
   You will probably want to use
   "<b>coalesce(sum(</b>X<b>),0)</b>" instead of just "<b>sum(</b>X<b>)</b>"
   to work around this design problem in the SQL language.</td>
</tr>
</table>
}


Section INSERT insert