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.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
** 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.
**
** I am told that SUM() should raise an exception if it encounters
** a integer overflow.  But after pondering this, I decided that 
** behavior leads to brittle programs.  So instead, I have coded
** SUM() to revert to using floating point if it encounters an
** integer overflow.  The answer may not be exact, but it will be
** close.  If the SUM() function returns an integer, the value is
** exact.  If SUM() returns a floating point value, it means the
** value might be approximated.
*/
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_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 ){

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This header file defines the interface that the SQLite library
** presents to client programs.
**
** @(#) $Id: sqlite.h.in,v 1.160 2006/02/09 22:13:42 drh Exp $
*/
#ifndef _SQLITE3_H_
#define _SQLITE3_H_
#include <stdarg.h>     /* Needed for the definition of va_list */

/*
** Make sure we can call this stuff from C++.

int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
double sqlite3_column_double(sqlite3_stmt*, int iCol);
int sqlite3_column_int(sqlite3_stmt*, int iCol);
sqlite_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
int sqlite3_column_type(sqlite3_stmt*, int iCol);
int sqlite3_column_numeric_type(sqlite3_stmt*, int iCol);

/*
** The sqlite3_finalize() function is called to delete a compiled
** SQL statement obtained by a previous call to sqlite3_prepare()
** or sqlite3_prepare16(). If the statement was executed successfully, or
** not executed at all, then SQLITE_OK is returned. If execution of the
** statement failed then an error code is returned. 

int sqlite3_value_int(sqlite3_value*);
sqlite_int64 sqlite3_value_int64(sqlite3_value*);
const unsigned char *sqlite3_value_text(sqlite3_value*);
const void *sqlite3_value_text16(sqlite3_value*);
const void *sqlite3_value_text16le(sqlite3_value*);
const void *sqlite3_value_text16be(sqlite3_value*);
int sqlite3_value_type(sqlite3_value*);
int sqlite3_value_numeric_type(sqlite3_value*);

/*
** Aggregate functions use the following routine to allocate
** a structure for storing their state.  The first time this routine
** is called for a particular aggregate, a new structure of size nBytes
** is allocated, zeroed, and returned.  On subsequent calls (for the
** same aggregate instance) the same buffer is returned.  The implementation

*/
int Md5_Init(Tcl_Interp *interp){
  Tcl_CreateCommand(interp, "md5", (Tcl_CmdProc*)md5_cmd, 0, 0);
  Tcl_CreateCommand(interp, "md5file", (Tcl_CmdProc*)md5file_cmd, 0, 0);
  return TCL_OK;
}




/*
** During testing, the special md5sum() aggregate function is available.
** inside SQLite.  The following routines implement that function.
*/
static void md5step(sqlite3_context *context, int argc, sqlite3_value **argv){
  MD5Context *p;
  int i;

**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.542 2006/02/09 22:13:42 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*

  }
  p->apCsr[iCur] = pCx = sqliteMalloc( sizeof(Cursor) );
  if( pCx ){
    pCx->iDb = iDb;
  }
  return pCx;
}

/*
** Try to convert a value into a numeric representation if we can
** do so without loss of information.  In other words, if the string
** looks like a number, convert it into a number.  If it does not
** look like a number, leave it alone.
*/
static void applyNumericAffinity(Mem *pRec){
  if( (pRec->flags & (MEM_Real|MEM_Int))==0 ){
    int realnum;
    sqlite3VdbeMemNulTerminate(pRec);
    if( (pRec->flags&MEM_Str)
         && sqlite3IsNumber(pRec->z, &realnum, pRec->enc) ){
      i64 value;
      sqlite3VdbeChangeEncoding(pRec, SQLITE_UTF8);
      if( !realnum && sqlite3atoi64(pRec->z, &value) ){
        sqlite3VdbeMemRelease(pRec);
        pRec->i = value;
        pRec->flags = MEM_Int;
      }else{
        sqlite3VdbeMemRealify(pRec);
      }
    }
  }
}

/*
** Processing is determine by the affinity parameter:
**
** SQLITE_AFF_INTEGER:
** SQLITE_AFF_REAL:
** SQLITE_AFF_NUMERIC:

    if( 0==(pRec->flags&MEM_Str) && (pRec->flags&(MEM_Real|MEM_Int)) ){
      sqlite3VdbeMemStringify(pRec, enc);
    }
    pRec->flags &= ~(MEM_Real|MEM_Int);
  }else if( affinity!=SQLITE_AFF_NONE ){
    assert( affinity==SQLITE_AFF_INTEGER || affinity==SQLITE_AFF_REAL
             || affinity==SQLITE_AFF_NUMERIC );












    applyNumericAffinity(pRec);

    if( pRec->flags & MEM_Real ){

      sqlite3VdbeIntegerAffinity(pRec);
    }
  }


}

/*
** Try to convert the type of a function argument or a result column
** into a numeric representation.  Use either INTEGER or REAL whichever
** is appropriate.  But only do the conversion if it is possible without
** loss of information and return the revised type of the argument.
**
** This is an EXPERIMENTAL api and is subject to change or removal.
*/
int sqlite3_value_numeric_type(sqlite3_value *pVal){
  Mem *pMem = (Mem*)pVal;
  applyNumericAffinity(pMem);
  storeTypeInfo(pMem, 0);
  return pMem->type;
}

/*
** Exported version of applyAffinity(). This one works on sqlite3_value*, 
** not the internal Mem* type.
*/
void sqlite3ValueApplyAffinity(sqlite3_value *pVal, u8 affinity, u8 enc){
  applyAffinity((Mem *)pVal, affinity, enc);

const void *sqlite3_value_text16le(sqlite3_value *pVal){
  return sqlite3ValueText(pVal, SQLITE_UTF16LE);
}
#endif /* SQLITE_OMIT_UTF16 */
int sqlite3_value_type(sqlite3_value* pVal){
  return pVal->type;
}
/* sqlite3_value_numeric_type() defined in vdbe.c */

/**************************** sqlite3_result_  *******************************
** The following routines are used by user-defined functions to specify
** the function result.
*/
void sqlite3_result_blob(
  sqlite3_context *pCtx, 

  columnMallocFailure(pStmt);
  return val;
}
#endif /* SQLITE_OMIT_UTF16 */
int sqlite3_column_type(sqlite3_stmt *pStmt, int i){
  return sqlite3_value_type( columnMem(pStmt,i) );
}

/* The following function is experimental and subject to change or
** removal */
/*int sqlite3_column_numeric_type(sqlite3_stmt *pStmt, int i){
**  return sqlite3_value_numeric_type( columnMem(pStmt,i) );
**}
*/

/*
** Convert the N-th element of pStmt->pColName[] into a string using
** xFunc() then return that string.  If N is out of range, return 0.
**
** There are up to 5 names for each column.  useType determines which
** name is returned.  Here are the names:

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

    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

# 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.48 2006/02/09 22:13:42 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {

} {1 {wrong number of arguments to function sum()}}
do_test select1-2.17 {
  set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg]
  lappend v $msg
} {0 45}
do_test select1-2.17.1 {
  execsql {SELECT sum(a) FROM t3}
} {44.0}
do_test select1-2.18 {
  set v [catch {execsql {SELECT XYZZY(f1) FROM test1}} msg]
  lappend v $msg
} {1 {no such function: XYZZY}}
do_test select1-2.19 {
  set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg]
  lappend v $msg

        (SELECT a FROM abc WHERE a = NULL AND b >= upper.c) 
      ) FROM abc AS upper;
    }
  } {0}
}

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 ""
}

values in column X.
</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.  String and BLOB values that do not look like numbers are
interpreted as 0.
The result of avg() is always a floating point value even if all
inputs are integers. </p></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>)<br>total(<i>X</i>)</td>
<td valign="top">Return the numeric sum of all non-NULL values in the group.
   If there are no non-NULL input rows then sum() returns
   NULL but total() returns 0.0.
   NULL is not normally a helpful result for the sum of no rows
   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