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.43.2.1 2004/06/23 21:16:52 drh Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"
#include "os.h"

  assert( argc==2 );
  if( argv[1][0]=='n' ){
    xCompare = sqliteCompare;
  }else{
    xCompare = strcmp;
  }
  mask = (int)sqlite_user_data(context);
  assert( mask==0 || mask==-1 );
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p==0 || argc<1 ) return;
  if( mask==0 && p->zBuf[0]>=2 ) return;
  if( argv[0]==0 ){
    if( mask==0 ){
      p->zBuf[0] |= 2;
    }
    return;
  }
  assert( p->zBuf[0]<2 );
  if( p->z==0 || (xCompare(argv[0],p->z)^mask)<0 ){
    int len;
    if( p->zBuf[0]!=0 ){
      sqliteFree(p->z);
    }
    len = strlen(argv[0]);
    if( len < sizeof(p->zBuf)-1 ){
      p->z = &p->zBuf[1];
      p->zBuf[0] = 0;
    }else{
      p->z = sqliteMalloc( len+1 );
      p->zBuf[0] = 1;
      if( p->z==0 ) return;
    }
    strcpy(p->z, argv[0]);
  }
}
static void minMaxFinalize(sqlite_func *context){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p && p->z && p->zBuf[0]<2 ){
    sqlite_set_result_string(context, p->z, strlen(p->z));
  }
  if( p && (p->zBuf[0]&1)!=0 ){
    sqliteFree(p->z);
  }
}

/*
** This function registered all of the above C functions as SQL
** functions.  This should be the only routine in this file with

    { "stddev", 1, SQLITE_NUMERIC, 0, stdDevStep,   stdDevFinalize },
#endif
  };
  static const char *azTypeFuncs[] = { "min", "max", "typeof" };
  int i;

  for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
    void *pArg;
    switch( aFuncs[i].argType ){
      case 0:  pArg = 0;           break;
      case 1:  pArg = db;          break;
      case 2:  pArg = (void*)(-1); break;
    }
    sqlite_create_function(db, aFuncs[i].zName,
           aFuncs[i].nArg, aFuncs[i].xFunc, pArg);
    if( aFuncs[i].xFunc ){
      sqlite_function_type(db, aFuncs[i].zName, aFuncs[i].dataType);
    }
  }
  for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
    void *pArg;
    switch( aAggs[i].argType ){
      case 0:  pArg = 0;           break;
      case 1:  pArg = db;          break;
      case 2:  pArg = (void*)(-1); break;
    }
    sqlite_create_aggregate(db, aAggs[i].zName,
           aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, pArg);
    sqlite_function_type(db, aAggs[i].zName, aAggs[i].dataType);
  }
  for(i=0; i<sizeof(azTypeFuncs)/sizeof(azTypeFuncs[0]); i++){
    int n = strlen(azTypeFuncs[i]);
    FuncDef *p = sqliteHashFind(&db->aFunc, azTypeFuncs[i], n);

# 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.16.2.1 2004/06/23 21:16:52 drh Exp $

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

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

# Tests for aggregate functions and how they handle NULLs.
#
do_test func-8.1 {
  execsql {
    SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
  }
} {68236 3 22745.33 {} 67890 5}
do_test func-8.2 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
do_test func-8.3 {
  execsql {
    CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
    SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
  }
} {{}}
do_test func-8.4 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

# How do you test the random() function in a meaningful, deterministic way?
#
do_test func-9.1 {
  execsql {
    SELECT random() is not null;
  }

#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.9.2.1 2004/06/23 21:16:52 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;

} {0}

do_test minmax-4.1 {
  execsql {
    SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
      (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
  }
} {-1 20}
do_test minmax-4.2 {
  execsql {
    SELECT y, sum(x) FROM
      (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
    GROUP BY y ORDER BY y;
  }
} {1 1 2 5 3 22 4 92 5 90 6 0}

do_test minmax-9.2 {
  execsql {
    SELECT max(rowid) FROM (
      SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
    )
  }
} {{}}

# If there is a NULL in an aggregate max(), ignore it.  If a NULL
# occurs in an aggregate min(), then the result will be NULL because
# NULL compares less than all other values.
#
do_test minmax-10.1 {
  execsql {
    CREATE TABLE t6(x);
    INSERT INTO t6 VALUES(1);
    INSERT INTO t6 VALUES(2);
    INSERT INTO t6 VALUES(NULL);
    SELECT coalesce(min(x),-1) FROM t6;
  }
} {-1}
do_test minmax-10.2 {
  execsql {
    SELECT max(x) FROM t6;
  }
} {2}
do_test minmax-10.3 {
  execsql {
    CREATE INDEX i6 ON t6(x);
    SELECT coalesce(min(x),-1) FROM t6;
  }
} {-1}
do_test minmax-10.4 {
  execsql {
    SELECT max(x) FROM t6;
  }
} {2}

finish_test

do_test null-2.8 {
  execsql {
    select ifnull(case c when b then 1 else 0 end, 99) from t1;
  }
} {1 0 0 1 0 0 0}

# Check to see that NULL values are ignored in aggregate functions.
# (except for min().)
#
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 {} 1}

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

# 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.30.2.1 2004/06/23 21:16:52 drh Exp $

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

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

} {0 11}
do_test select1-2.8 {
  set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg]
  lappend v [lsort $msg]
} {0 {11 33}}
do_test select1-2.8.1 {
  execsql {SELECT coalesce(min(a),'xyzzy') FROM t3}
} {xyzzy}
do_test select1-2.8.2 {
  execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3}
} {11}
do_test select1-2.8.3 {
  execsql {SELECT min(b), min(b) FROM t4}
} [list $long $long]
do_test select1-2.9 {