SQLite

** This file contains the C functions that implement date and time
** functions for SQLite.  
**
** There is only one exported symbol in this file - the function
** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: date.c,v 1.103 2009/02/04 03:59:25 shane Exp $
**
** SQLite processes all times and dates as Julian Day numbers.  The
** dates and times are stored as the number of days since noon
** in Greenwich on November 24, 4714 B.C. according to the Gregorian
** calendar system. 
**
** 1970-01-01 00:00:00 is JD 2440587.5

  sqlite3_value **argv
){
  time_t t;
  char *zFormat = (char *)sqlite3_user_data(context);
  sqlite3 *db;
  double rT;
  char zBuf[20];

  UNUSED_PARAMETER(argc);
  UNUSED_PARAMETER(argv);

  db = sqlite3_context_db_handle(context);
  sqlite3OsCurrentTime(db->pVfs, &rT);
#ifndef SQLITE_OMIT_FLOATING_POINT
  t = 86400.0*(rT - 2440587.5) + 0.5;
#else
  /* without floating point support, rT will have
  ** already lost fractional day precision.
  */
  t = 86400 * (rT - 2440587) - 43200;
#endif
#ifdef HAVE_GMTIME_R
  {
    struct tm sNow;
    gmtime_r(&t, &sNow);
    strftime(zBuf, 20, zFormat, &sNow);
  }
#else

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.411 2009/02/04 03:59:25 shane Exp $
*/
#include "sqliteInt.h"

/*
** Return the 'affinity' of the expression pExpr if any.
**
** If pExpr is a column, a reference to a column via an 'AS' alias,

      testcase( regFree1==0 );
      testcase( regFree2==0 );
      break;
    }
    case TK_UMINUS: {
      Expr *pLeft = pExpr->pLeft;
      assert( pLeft );

      if( pLeft->op==TK_FLOAT ){
        codeReal(v, (char*)pLeft->token.z, pLeft->token.n, 1, target);
      }else if( pLeft->op==TK_INTEGER ){
        codeInteger(v, pLeft, 1, target);

      }else{
        regFree1 = r1 = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp2(v, OP_Integer, 0, r1);
        r2 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, &regFree2);
        sqlite3VdbeAddOp3(v, OP_Subtract, r2, r1, target);
        testcase( regFree2==0 );
      }

** 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.222 2009/02/04 03:59:25 shane Exp $
*/
#include "sqliteInt.h"
#include <stdlib.h>
#include <assert.h>
#include "vdbeInt.h"

/*

    sqlite3_result_blob(context, (char*)&z[p1], (int)p2, SQLITE_TRANSIENT);
  }
}

/*
** Implementation of the round() function
*/
#ifndef SQLITE_OMIT_FLOATING_POINT
static void roundFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
  int n = 0;
  double r;
  char zBuf[500];  /* larger than the %f representation of the largest double */
  assert( argc==1 || argc==2 );
  if( argc==2 ){
    if( SQLITE_NULL==sqlite3_value_type(argv[1]) ) return;
    n = sqlite3_value_int(argv[1]);
    if( n>30 ) n = 30;
    if( n<0 ) n = 0;
  }
  if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
  r = sqlite3_value_double(argv[0]);
  sqlite3_snprintf(sizeof(zBuf),zBuf,"%.*f",n,r);
  sqlite3AtoF(zBuf, &r);
  sqlite3_result_double(context, r);
}
#endif

/*
** Allocate nByte bytes of space using sqlite3_malloc(). If the
** allocation fails, call sqlite3_result_error_nomem() to notify
** the database handle that malloc() has failed.
*/
static void *contextMalloc(sqlite3_context *context, i64 nByte){

  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);
  /* (double)0 In case of SQLITE_OMIT_FLOATING_POINT... */
  sqlite3_result_double(context, p ? p->rSum : (double)0);
}

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

    FUNCTION(max,                0, 1, 1, 0                ),
    AGGREGATE(max,               1, 1, 1, minmaxStep,      minMaxFinalize ),
    FUNCTION(typeof,             1, 0, 0, typeofFunc       ),
    FUNCTION(length,             1, 0, 0, lengthFunc       ),
    FUNCTION(substr,             2, 0, 0, substrFunc       ),
    FUNCTION(substr,             3, 0, 0, substrFunc       ),
    FUNCTION(abs,                1, 0, 0, absFunc          ),
#ifndef SQLITE_OMIT_FLOATING_POINT
    FUNCTION(round,              1, 0, 0, roundFunc        ),
    FUNCTION(round,              2, 0, 0, roundFunc        ),
#endif
    FUNCTION(upper,              1, 0, 0, upperFunc        ),
    FUNCTION(lower,              1, 0, 0, lowerFunc        ),
    FUNCTION(coalesce,           1, 0, 0, 0                ),
    FUNCTION(coalesce,          -1, 0, 0, ifnullFunc       ),
    FUNCTION(coalesce,           0, 0, 0, 0                ),
    FUNCTION(hex,                1, 0, 0, hexFunc          ),
    FUNCTION(ifnull,             2, 0, 1, ifnullFunc       ),

**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.526 2009/02/04 03:59:25 shane Exp $
*/
#include "sqliteInt.h"

#ifdef SQLITE_ENABLE_FTS3
# include "fts3.h"
#endif
#ifdef SQLITE_ENABLE_RTREE

  /* The following is just a sanity check to make sure SQLite has
  ** been compiled correctly.  It is important to run this code, but
  ** we don't want to run it too often and soak up CPU cycles for no
  ** reason.  So we run it once during initialization.
  */
#ifndef NDEBUG
#ifndef SQLITE_OMIT_FLOATING_POINT
  /* This section of code's only "output" is via assert() statements. */
  if ( rc==SQLITE_OK ){
    u64 x = (((u64)1)<<63)-1;
    double y;
    assert(sizeof(x)==8);
    assert(sizeof(x)==sizeof(y));
    memcpy(&y, &x, 8);
    assert( sqlite3IsNaN(y) );
  }
#endif
#endif

  return rc;
}

/*
** Undo the effects of sqlite3_initialize().  Must not be called while

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
******************************************************************************
**
** This file contains code that is specific to windows.
**
** $Id: os_win.c,v 1.147 2009/02/04 03:59:25 shane Exp $
*/
#include "sqliteInt.h"
#if SQLITE_OS_WIN               /* This file is used for windows only */


/*
** A Note About Memory Allocation:

/*
** Make sure all writes to a particular file are committed to disk.
*/
static int winSync(sqlite3_file *id, int flags){
#ifndef SQLITE_NO_SYNC
  winFile *pFile = (winFile*)id;
  OSTRACE3("SYNC %d lock=%d\n", pFile->h, pFile->locktype);
#else
  UNUSED_PARAMETER(id);
#endif

#ifndef SQLITE_TEST
  UNUSED_PARAMETER(flags);
#else
  if( flags & SQLITE_SYNC_FULL ){
    sqlite3_fullsync_count++;
  }
  sqlite3_sync_count++;

** return 0.  Return 1 if the time and date cannot be found.
*/
int winCurrentTime(sqlite3_vfs *pVfs, double *prNow){
  FILETIME ft;
  /* FILETIME structure is a 64-bit value representing the number of 
     100-nanosecond intervals since January 1, 1601 (= JD 2305813.5). 
  */
  sqlite3_int64 timeW, timeF;
#if SQLITE_OS_WINCE
  SYSTEMTIME time;
  GetSystemTime(&time);
  /* if SystemTimeToFileTime() fails, it returns zero. */
  if (!SystemTimeToFileTime(&time,&ft)){
    return 1;
  }
#else
  GetSystemTimeAsFileTime( &ft );
#endif
  UNUSED_PARAMETER(pVfs);
#if defined(_MSC_VER)
  timeW = (((sqlite3_int64)ft.dwHighDateTime)*4294967296) + ft.dwLowDateTime;
  timeF = timeW % 864000000000;           /* fractional days (100-nanoseconds) */
  timeW = timeW / 864000000000;           /* whole days */
  timeW = timeW + 2305813;                /* add whole days (from 2305813.5) */
  timeF = timeF + 432000000000;           /* add half a day (from 2305813.5) */
  timeW = timeW + (timeF / 864000000000); /* add whole day if half day made one */
  timeF = timeF % 864000000000;           /* compute new fractional days */
  *prNow = (double)timeW + ((double)timeF / (double)864000000000);
#else
  timeW = (((sqlite3_int64)ft.dwHighDateTime)*4294967296LL) + ft.dwLowDateTime;
  timeF = timeW % 864000000000LL;           /* fractional days (100-nanoseconds) */
  timeW = timeW / 864000000000LL;           /* whole days */
  timeW = timeW + 2305813;                  /* add whole days (from 2305813.5) */
  timeF = timeF + 432000000000LL;           /* add half a day (from 2305813.5) */
  timeW = timeW + (timeF / 864000000000LL); /* add whole day if half day made one */
  timeF = timeF % 864000000000LL;           /* compute new fractional days */
  *prNow = (double)timeW + ((double)timeF / (double)864000000000LL);
#endif
#ifdef SQLITE_TEST
  if( sqlite3_current_time ){
    *prNow = ((double)sqlite3_current_time + (double)43200) / (double)86400 + (double)2440587;
  }
#endif
  return 0;
}

/*
** The idea is that this function works like a combination of

**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.248 2009/02/04 03:59:25 shane Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>


/*
** Routine needed to support the testcase() macro.
*/
#ifdef SQLITE_COVERAGE_TEST
void sqlite3Coverage(int x){
  static int dummy = 0;

**
** 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.814 2009/02/04 03:59:25 shane Exp $
*/
#include "sqliteInt.h"
#include "vdbeInt.h"

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_SeekXX, OP_Next, or OP_Prev opcodes.  The test

    a = sqlite3VdbeRealValue(pIn1);
    b = sqlite3VdbeRealValue(pIn2);
    switch( pOp->opcode ){
      case OP_Add:         b += a;       break;
      case OP_Subtract:    b -= a;       break;
      case OP_Multiply:    b *= a;       break;
      case OP_Divide: {
        /* (double)0 In case of SQLITE_OMIT_FLOATING_POINT... */
        if( a==(double)0 ) goto arithmetic_result_is_null;
        b /= a;
        break;
      }
      default: {
        i64 ia = (i64)a;
        i64 ib = (i64)b;
        if( ia==0 ) goto arithmetic_result_is_null;

case OP_If:                 /* jump, in1 */
case OP_IfNot: {            /* jump, in1 */
  int c;
  if( pIn1->flags & MEM_Null ){
    c = pOp->p3;
  }else{
#ifdef SQLITE_OMIT_FLOATING_POINT
    c = sqlite3VdbeIntValue(pIn1)!=0;
#else
    c = sqlite3VdbeRealValue(pIn1)!=0.0;
#endif
    if( pOp->opcode==OP_IfNot ) c = !c;
  }
  if( c ){
    pc = pOp->p2-1;

**    May you share freely, never taking more than you give.
**
*************************************************************************
**
** This file contains code use to implement APIs that are part of the
** VDBE.
**
** $Id: vdbeapi.c,v 1.151 2009/02/04 03:59:25 shane Exp $
*/
#include "sqliteInt.h"
#include "vdbeInt.h"

#if 0 && defined(SQLITE_ENABLE_MEMORY_MANAGEMENT)
/*
** The following structure contains pointers to the end points of a

  pVm = (Vdbe *)pStmt;
  if( pVm && pVm->pResultSet!=0 && i<pVm->nResColumn && i>=0 ){
    sqlite3_mutex_enter(pVm->db->mutex);
    vals = sqlite3_data_count(pStmt);
    pOut = &pVm->pResultSet[i];
  }else{
    /* ((double)0) In case of SQLITE_OMIT_FLOATING_POINT... */
    static const Mem nullMem = {{0}, (double)0, 0, "", 0, MEM_Null, SQLITE_NULL, 0, 0, 0 };
    if( pVm->db ){
      sqlite3_mutex_enter(pVm->db->mutex);
      sqlite3Error(pVm->db, SQLITE_RANGE, 0);
    }
    pOut = (Mem*)&nullMem;
  }
  return pOut;

*************************************************************************
**
** This file contains code use to manipulate "Mem" structure.  A "Mem"
** stores a single value in the VDBE.  Mem is an opaque structure visible
** only within the VDBE.  Interface routines refer to a Mem using the
** name sqlite_value
**
** $Id: vdbemem.c,v 1.137 2009/02/04 03:59:25 shane Exp $
*/
#include "sqliteInt.h"
#include "vdbeInt.h"

/*
** Call sqlite3VdbeMemExpandBlob() on the supplied value (type Mem*)
** P if required.

double sqlite3VdbeRealValue(Mem *pMem){
  assert( pMem->db==0 || sqlite3_mutex_held(pMem->db->mutex) );
  if( pMem->flags & MEM_Real ){
    return pMem->r;
  }else if( pMem->flags & MEM_Int ){
    return (double)pMem->u.i;
  }else if( pMem->flags & (MEM_Str|MEM_Blob) ){
    /* (double)0 In case of SQLITE_OMIT_FLOATING_POINT... */
    double val = (double)0;
    pMem->flags |= MEM_Str;
    if( sqlite3VdbeChangeEncoding(pMem, SQLITE_UTF8)
       || sqlite3VdbeMemNulTerminate(pMem) ){
      /* (double)0 In case of SQLITE_OMIT_FLOATING_POINT... */
      return (double)0;
    }
    assert( pMem->z );
    sqlite3AtoF(pMem->z, &val);
    return val;
  }else{
    /* (double)0 In case of SQLITE_OMIT_FLOATING_POINT... */
    return (double)0;
  }
}

/*
** The MEM structure is already a MEM_Real.  Try to also make it a
** MEM_Int if we can.
*/

      sqlite3ValueApplyAffinity(pVal, SQLITE_AFF_NUMERIC, enc);
    }else{
      sqlite3ValueApplyAffinity(pVal, affinity, enc);
    }
  }else if( op==TK_UMINUS ) {
    if( SQLITE_OK==sqlite3ValueFromExpr(db,pExpr->pLeft,enc,affinity,&pVal) ){
      pVal->u.i = -1 * pVal->u.i;
      /* (double)-1 In case of SQLITE_OMIT_FLOATING_POINT... */
      pVal->r = (double)-1 * pVal->r;
    }
  }
#ifndef SQLITE_OMIT_BLOB_LITERAL
  else if( op==TK_BLOB ){
    int nVal;
    assert( pExpr->token.n>=3 );
    assert( pExpr->token.z[0]=='x' || pExpr->token.z[0]=='X' );

** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is responsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.368 2009/02/04 03:59:25 shane Exp $
*/
#include "sqliteInt.h"

/*
** Trace output macros
*/
#if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)

    /* Allocate the sqlite3_index_info structure
    */
    pIdxInfo = sqlite3DbMallocZero(pParse->db, sizeof(*pIdxInfo)
                             + (sizeof(*pIdxCons) + sizeof(*pUsage))*nTerm
                             + sizeof(*pIdxOrderBy)*nOrderBy );
    if( pIdxInfo==0 ){
      sqlite3ErrorMsg(pParse, "out of memory");
      /* (double)0 In case of SQLITE_OMIT_FLOATING_POINT... */
      return (double)0;
    }
    *ppIdxInfo = pIdxInfo;

    /* Initialize the structure.  The sqlite3_index_info structure contains
    ** many fields that are declared "const" to prevent xBestIndex from
    ** changing them.  We have to do some funky casting in order to
    ** initialize those fields.

  if( pIdxInfo->needToFreeIdxStr ){
    sqlite3_free(pIdxInfo->idxStr);
  }
  pIdxInfo->idxStr = 0;
  pIdxInfo->idxNum = 0;
  pIdxInfo->needToFreeIdxStr = 0;
  pIdxInfo->orderByConsumed = 0;
  /* ((double)2) In case of SQLITE_OMIT_FLOATING_POINT... */
  pIdxInfo->estimatedCost = SQLITE_BIG_DBL / ((double)2);
  nOrderBy = pIdxInfo->nOrderBy;
  if( pIdxInfo->nOrderBy && !orderByUsable ){
    *(int*)&pIdxInfo->nOrderBy = 0;
  }

  (void)sqlite3SafetyOff(pParse->db);
  WHERETRACE(("xBestIndex for %s\n", pTab->zName));

  sqlite3DbFree(pParse->db, pVtab->zErrMsg);
  pVtab->zErrMsg = 0;

  for(i=0; i<pIdxInfo->nConstraint; i++){
    if( !pIdxInfo->aConstraint[i].usable && pUsage[i].argvIndex>0 ){
      sqlite3ErrorMsg(pParse, 
          "table %s: xBestIndex returned an invalid plan", pTab->zName);
      /* (double)0 In case of SQLITE_OMIT_FLOATING_POINT... */
      return (double)0;
    }
  }

  *(int*)&pIdxInfo->nOrderBy = nOrderBy;
  return pIdxInfo->estimatedCost;
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */

                                       ppIdxInfo);
        sCost.plan.wsFlags = WHERE_VIRTUALTABLE;
        sCost.plan.u.pVtabIdx = pVtabIdx = *ppIdxInfo;
        if( pVtabIdx && pVtabIdx->orderByConsumed ){
          sCost.plan.wsFlags = WHERE_VIRTUALTABLE | WHERE_ORDERBY;
        }
        sCost.plan.nEq = 0;
        /* (double)2 In case of SQLITE_OMIT_FLOATING_POINT... */
        if( (SQLITE_BIG_DBL/((double)2))<sCost.rCost ){
          /* The cost is not allowed to be larger than SQLITE_BIG_DBL (the
          ** inital value of lowestCost in this loop. If it is, then
          ** the (cost<lowestCost) test below will never be true.
          */ 
          /* (double)2 In case of SQLITE_OMIT_FLOATING_POINT... */
          sCost.rCost = (SQLITE_BIG_DBL/((double)2));
        }
      }else 
#endif
      {
        bestIndex(pParse, pWC, pTabItem, notReady,
                  (i==0 && ppOrderBy) ? *ppOrderBy : 0, &sCost);
      }

# 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 expressions.
#
# $Id: expr.test,v 1.67 2009/02/04 03:59:25 shane Exp $

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

# Create a table to work with.
#
ifcapable floatingpoint {
  execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)}
  execsql {INSERT INTO test1 VALUES(1,2,1.1,2.2,'hello','world')}
}
ifcapable !floatingpoint {
  execsql {CREATE TABLE test1(i1 int, i2 int, t1 text, t2 text)}
  execsql {INSERT INTO test1 VALUES(1,2,'hello','world')}
}

proc test_expr {name settings expr result} {
  do_test $name [format {
    execsql {BEGIN; UPDATE test1 SET %s; SELECT %s FROM test1; ROLLBACK;}
  } $settings $expr] $result
}

test_expr expr-1.1 {i1=10, i2=20} {i1+i2} 30

test_expr expr-1.15 {i1=20, i2=20} {i2<=i1} 1
test_expr expr-1.16 {i1=20, i2=20} {i2>i1} 0
test_expr expr-1.17 {i1=20, i2=20} {i2>=i1} 1
test_expr expr-1.18 {i1=20, i2=20} {i2!=i1} 0
test_expr expr-1.19 {i1=20, i2=20} {i2=i1} 1
test_expr expr-1.20 {i1=20, i2=20} {i2<>i1} 0
test_expr expr-1.21 {i1=20, i2=20} {i2==i1} 1
ifcapable floatingpoint {
  test_expr expr-1.22 {i1=1, i2=2, r1=3.0} {i1+i2*r1} {7.0}
  test_expr expr-1.23 {i1=1, i2=2, r1=3.0} {(i1+i2)*r1} {9.0}
}
test_expr expr-1.24 {i1=1, i2=2} {min(i1,i2,i1+i2,i1-i2)} {-1}
test_expr expr-1.25 {i1=1, i2=2} {max(i1,i2,i1+i2,i1-i2)} {3}
test_expr expr-1.26 {i1=1, i2=2} {max(i1,i2,i1+i2,i1-i2)} {3}
test_expr expr-1.27 {i1=1, i2=2} {i1==1 AND i2=2} {1}
test_expr expr-1.28 {i1=1, i2=2} {i1=2 AND i2=1} {0}
test_expr expr-1.29 {i1=1, i2=2} {i1=1 AND i2=1} {0}
test_expr expr-1.30 {i1=1, i2=2} {i1=2 AND i2=2} {0}

test_expr expr-1.98 {i1=NULL, i2=NULL} {coalesce(i1|i2,99)} 99
test_expr expr-1.99 {i1=32, i2=NULL} {coalesce(i1&i2,99)} 99
test_expr expr-1.100 {i1=1, i2=''} {i1=i2} 0
test_expr expr-1.101 {i1=0, i2=''} {i1=i2} 0

# Check for proper handling of 64-bit integer values.
#
if {[working_64bit_int]} {
  test_expr expr-1.102 {i1=40, i2=1} {i2<<i1} 1099511627776
}

ifcapable floatingpoint {
  test_expr expr-1.103 {i1=0} {(-2147483648.0 % -1)} 0.0
  test_expr expr-1.104 {i1=0} {(-9223372036854775808.0 % -1)} 0.0
  test_expr expr-1.105 {i1=0} {(-9223372036854775808.0 / -1)>1} 1
}

if {[working_64bit_int]} {
  test_expr expr-1.106 {i1=0} {(1<<63)/-1} -9223372036854775808
}

test_expr expr-1.107 {i1=0} {(1<<63)%-1} 0
test_expr expr-1.108 {i1=0} {1%0} {{}}
test_expr expr-1.109 {i1=0} {1/0} {{}}

if {[working_64bit_int]} {
  test_expr expr-1.110 {i1=0} {-9223372036854775807/-1} 9223372036854775807
}

ifcapable floatingpoint {
  test_expr expr-2.1 {r1=1.23, r2=2.34} {r1+r2} 3.57
  test_expr expr-2.2 {r1=1.23, r2=2.34} {r1-r2} -1.11
  test_expr expr-2.3 {r1=1.23, r2=2.34} {r1*r2} 2.8782
}
set tcl_precision 15
ifcapable floatingpoint {
  test_expr expr-2.4 {r1=1.23, r2=2.34} {r1/r2} 0.525641025641026
  test_expr expr-2.5 {r1=1.23, r2=2.34} {r2/r1} 1.90243902439024
  test_expr expr-2.6 {r1=1.23, r2=2.34} {r2<r1} 0
  test_expr expr-2.7 {r1=1.23, r2=2.34} {r2<=r1} 0
  test_expr expr-2.8 {r1=1.23, r2=2.34} {r2>r1} 1
  test_expr expr-2.9 {r1=1.23, r2=2.34} {r2>=r1} 1
  test_expr expr-2.10 {r1=1.23, r2=2.34} {r2!=r1} 1
  test_expr expr-2.11 {r1=1.23, r2=2.34} {r2=r1} 0
  test_expr expr-2.12 {r1=1.23, r2=2.34} {r2<>r1} 1
  test_expr expr-2.13 {r1=1.23, r2=2.34} {r2==r1} 0
  test_expr expr-2.14 {r1=2.34, r2=2.34} {r2<r1} 0
  test_expr expr-2.15 {r1=2.34, r2=2.34} {r2<=r1} 1
  test_expr expr-2.16 {r1=2.34, r2=2.34} {r2>r1} 0
  test_expr expr-2.17 {r1=2.34, r2=2.34} {r2>=r1} 1
  test_expr expr-2.18 {r1=2.34, r2=2.34} {r2!=r1} 0
  test_expr expr-2.19 {r1=2.34, r2=2.34} {r2=r1} 1
  test_expr expr-2.20 {r1=2.34, r2=2.34} {r2<>r1} 0
  test_expr expr-2.21 {r1=2.34, r2=2.34} {r2==r1} 1
  test_expr expr-2.22 {r1=1.23, r2=2.34} {min(r1,r2,r1+r2,r1-r2)} {-1.11}
  test_expr expr-2.23 {r1=1.23, r2=2.34} {max(r1,r2,r1+r2,r1-r2)} {3.57}
  test_expr expr-2.24 {r1=25.0, r2=11.0} {r1%r2} 3.0
  test_expr expr-2.25 {r1=1.23, r2=NULL} {coalesce(r1+r2,99.0)} 99.0
  test_expr expr-2.26 {r1=1e300, r2=1e300} {coalesce((r1*r2)*0.0,99.0)} 99.0
  test_expr expr-2.26b {r1=1e300, r2=-1e300} {coalesce((r1*r2)*0.0,99.0)} 99.0
  test_expr expr-2.27 {r1=1.1, r2=0.0} {r1/r2} {{}}
  test_expr expr-2.28 {r1=1.1, r2=0.0} {r1%r2} {{}}
}

test_expr expr-3.1 {t1='abc', t2='xyz'} {t1<t2} 1
test_expr expr-3.2 {t1='xyz', t2='abc'} {t1<t2} 0
test_expr expr-3.3 {t1='abc', t2='abc'} {t1<t2} 0
test_expr expr-3.4 {t1='abc', t2='xyz'} {t1<=t2} 1
test_expr expr-3.5 {t1='xyz', t2='abc'} {t1<=t2} 0
test_expr expr-3.6 {t1='abc', t2='abc'} {t1<=t2} 1

test_expr expr-4.3 {t1='abc', t2='Bbc'} {t1<t2} 0
test_expr expr-4.4 {t1='abc', t2='Bbc'} {t1>t2} 1
test_expr expr-4.5 {t1='0', t2='0.0'} {t1==t2} 0
test_expr expr-4.6 {t1='0.000', t2='0.0'} {t1==t2} 0
test_expr expr-4.7 {t1=' 0.000', t2=' 0.0'} {t1==t2} 0
test_expr expr-4.8 {t1='0.0', t2='abc'} {t1<t2} 1
test_expr expr-4.9 {t1='0.0', t2='abc'} {t1==t2} 0

ifcapable floatingpoint {
  test_expr expr-4.10 {r1='0.0', r2='abc'} {r1>r2} 0
  test_expr expr-4.11 {r1='abc', r2='Abc'} {r1<r2} 0
  test_expr expr-4.12 {r1='abc', r2='Abc'} {r1>r2} 1
  test_expr expr-4.13 {r1='abc', r2='Bbc'} {r1<r2} 0
  test_expr expr-4.14 {r1='abc', r2='Bbc'} {r1>r2} 1
  test_expr expr-4.15 {r1='0', r2='0.0'} {r1==r2} 1
  test_expr expr-4.16 {r1='0.000', r2='0.0'} {r1==r2} 1
  test_expr expr-4.17 {r1=' 0.000', r2=' 0.0'} {r1==r2} 0
  test_expr expr-4.18 {r1='0.0', r2='abc'} {r1<r2} 1
  test_expr expr-4.19 {r1='0.0', r2='abc'} {r1==r2} 0
  test_expr expr-4.20 {r1='0.0', r2='abc'} {r1>r2} 0
}

# CSL is true if LIKE is case sensitive and false if not.
# NCSL is the opposite.  Use these variables as the result
# on operations where case makes a difference.
set CSL $sqlite_options(casesensitivelike)
set NCSL [expr {!$CSL}]

test_expr2 expr-7.33 {(b=0 OR a<0) AND a IS NULL} {{}}
test_expr2 expr-7.34 {(a<0 AND b=0) AND a IS NULL} {}
test_expr2 expr-7.35 {(b=0 AND a<0) AND a IS NULL} {}
test_expr2 expr-7.36 {a<2 OR (a<0 OR b=0)} {{} 1}
test_expr2 expr-7.37 {a<2 OR (b=0 OR a<0)} {{} 1}
test_expr2 expr-7.38 {a<2 OR (a<0 AND b=0)} {1}
test_expr2 expr-7.39 {a<2 OR (b=0 AND a<0)} {1}
ifcapable floatingpoint {
  test_expr2 expr-7.40 {((a<2 OR a IS NULL) AND b<3) OR b>1e10} {{} 1}
}
test_expr2 expr-7.41 {a BETWEEN -1 AND 1} {1}
test_expr2 expr-7.42 {a NOT BETWEEN 2 AND 100} {1}
test_expr2 expr-7.43 {(b+1234)||'this is a string that is at least 32 characters long' BETWEEN 1 AND 2} {}
test_expr2 expr-7.44 {123||'xabcdefghijklmnopqrstuvwyxz01234567890'||a BETWEEN '123a' AND '123b'} {}
test_expr2 expr-7.45 {((123||'xabcdefghijklmnopqrstuvwyxz01234567890'||a) BETWEEN '123a' AND '123b')<0} {}
test_expr2 expr-7.46 {((123||'xabcdefghijklmnopqrstuvwyxz01234567890'||a) BETWEEN '123a' AND '123z')>0} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20}

test_expr2 expr-7.65 {b = abs(+-2)}                   {1}
test_expr2 expr-7.66 {b = abs(++-2)}                  {1}
test_expr2 expr-7.67 {b = abs(+-+-2)}                 {1}
test_expr2 expr-7.68 {b = abs(+-++-2)}                {1}
test_expr2 expr-7.69 {b = abs(++++-2)}                {1}
test_expr2 expr-7.70 {b = 5 - abs(+3)}                {1}
test_expr2 expr-7.71 {b = 5 - abs(-3)}                {1}
ifcapable floatingpoint {
  test_expr2 expr-7.72 {b = abs(-2.0)}                  {1}
}
test_expr2 expr-7.73 {b = 6 - abs(-a)}                {2}
ifcapable floatingpoint {
  test_expr2 expr-7.74 {b = abs(8.0)}                   {3}
}

# Test the CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP expressions.
#
ifcapable {floatingpoint} {
  set sqlite_current_time 1157124849
  do_test expr-8.1 {
    execsql {SELECT CURRENT_TIME}
  } {15:34:09}
  do_test expr-8.2 {
    execsql {SELECT CURRENT_DATE}
  } {2006-09-01}
  do_test expr-8.3 {
    execsql {SELECT CURRENT_TIMESTAMP}
  } {{2006-09-01 15:34:09}}
}
ifcapable datetime {
  do_test expr-8.4 {
    execsql {SELECT CURRENT_TIME==time('now');}
  } 1
  do_test expr-8.5 {
    execsql {SELECT CURRENT_DATE==date('now');}
  } 1
  do_test expr-8.6 {
    execsql {SELECT CURRENT_TIMESTAMP==datetime('now');}
  } 1
}
set sqlite_current_time 0

ifcapable floatingpoint {
  do_test expr-9.1 {
    execsql {SELECT round(-('-'||'123'))}
  } 123.0
}

# Test an error message that can be generated by the LIKE expression
do_test expr-10.1 {
  catchsql {SELECT 'abc' LIKE 'abc' ESCAPE ''}
} {1 {ESCAPE expression must be a single character}}
do_test expr-10.2 {
  catchsql {SELECT 'abc' LIKE 'abc' ESCAPE 'ab'}

} {real}
do_test expr-11.11 {
  execsql {SELECT typeof(-9223372036854775808)}
} {integer}
do_test expr-11.12 {
  execsql {SELECT typeof(-00000009223372036854775808)}
} {integer}
ifcapable floatingpoint {
  do_test expr-11.13 {
    execsql {SELECT typeof(-9223372036854775809)}
  } {real}
  do_test expr-11.14 {
    execsql {SELECT typeof(-00000009223372036854775809)}
  } {real}
}

# These two statements used to leak memory (because of missing %destructor
# directives in parse.y).
do_test expr-12.1 {
  catchsql {
    SELECT (CASE a>4 THEN 1 ELSE 0 END) FROM test1;
  }
} {1 {near "THEN": syntax error}}
do_test expr-12.2 {
  catchsql {
    SELECT (CASE WHEN a>4 THEN 1 ELSE 0) FROM test1;
  }
} {1 {near ")": syntax error}}

ifcapable floatingpoint {
  do_test expr-13.1 {
    execsql {
      SELECT 12345678901234567890;
    }
  } {1.23456789012346e+19}
}

# Implicit String->Integer conversion is used when possible.
#
if {[working_64bit_int]} {
  do_test expr-13.2 {
    execsql {
      SELECT 0+'9223372036854775807'
    }
  } {9223372036854775807}
  do_test expr-13.3 {
    execsql {
      SELECT '9223372036854775807'+0
    }
  } {9223372036854775807}
}

# If the value is too large, use String->Float conversion.
#
ifcapable floatingpoint {
  do_test expr-13.4 {
    execsql {
      SELECT 0+'9223372036854775808'
    }
  } {9.22337203685478e+18}
  do_test expr-13.5 {
    execsql {
      SELECT '9223372036854775808'+0
    }
  } {9.22337203685478e+18}
}

# Use String->float conversion if the value is explicitly a floating
# point value.
#
do_test expr-13.6 {
  execsql {
    SELECT 0+'9223372036854775807.0'

# 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.91 2009/02/04 03:59:25 shane Exp $

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

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

  execsql {SELECT t1 FROM tbl1}
} {this program is free software}

} ;# End \u1234!=u1234

# Test the abs() and round() functions.
#
ifcapable !floatingpoint {
  do_test func-4.1 {
    execsql {
      CREATE TABLE t1(a,b,c);
      INSERT INTO t1 VALUES(1,2,3);
      INSERT INTO t1 VALUES(2,12345678901234,-1234567890);
      INSERT INTO t1 VALUES(3,-2,-5);
    }
    catchsql {SELECT abs(a,b) FROM t1}
  } {1 {wrong number of arguments to function abs()}}
}
ifcapable floatingpoint {
  do_test func-4.1 {
    execsql {
      CREATE TABLE t1(a,b,c);
      INSERT INTO t1 VALUES(1,2,3);
      INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890);
      INSERT INTO t1 VALUES(3,-2,-5);
    }
    catchsql {SELECT abs(a,b) FROM t1}
  } {1 {wrong number of arguments to function abs()}}
}
do_test func-4.2 {
  catchsql {SELECT abs() FROM t1}
} {1 {wrong number of arguments to function abs()}}
ifcapable floatingpoint {
  do_test func-4.3 {
    catchsql {SELECT abs(b) FROM t1 ORDER BY a}
  } {0 {2 1.2345678901234 2}}
  do_test func-4.4 {
    catchsql {SELECT abs(c) FROM t1 ORDER BY a}
  } {0 {3 12345.6789 5}}
}
ifcapable !floatingpoint {
  if {[working_64bit_int]} {
    do_test func-4.3 {
      catchsql {SELECT abs(b) FROM t1 ORDER BY a}
    } {0 {2 12345678901234 2}}
  }
  do_test func-4.4 {
    catchsql {SELECT abs(c) FROM t1 ORDER BY a}
  } {0 {3 1234567890 5}}
}
do_test func-4.4.1 {
  execsql {SELECT abs(a) FROM t2}
} {1 {} 345 {} 67890}
do_test func-4.4.2 {
  execsql {SELECT abs(t1) FROM tbl1}
} {0.0 0.0 0.0 0.0 0.0}

ifcapable floatingpoint {
  do_test func-4.5 {
    catchsql {SELECT round(a,b,c) FROM t1}
  } {1 {wrong number of arguments to function round()}}
  do_test func-4.6 {
    catchsql {SELECT round(b,2) FROM t1 ORDER BY b}
  } {0 {-2.0 1.23 2.0}}
  do_test func-4.7 {
    catchsql {SELECT round(b,0) FROM t1 ORDER BY a}
  } {0 {2.0 1.0 -2.0}}
  do_test func-4.8 {
    catchsql {SELECT round(c) FROM t1 ORDER BY a}
  } {0 {3.0 -12346.0 -5.0}}
  do_test func-4.9 {
    catchsql {SELECT round(c,a) FROM t1 ORDER BY a}
  } {0 {3.0 -12345.68 -5.0}}
  do_test func-4.10 {
    catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a}
  } {0 {x3.0y x-12345.68y x-5.0y}}
  do_test func-4.11 {
    catchsql {SELECT round() FROM t1 ORDER BY a}
  } {1 {wrong number of arguments to function round()}}
  do_test func-4.12 {
    execsql {SELECT coalesce(round(a,2),'nil') FROM t2}
  } {1.0 nil 345.0 nil 67890.0}
  do_test func-4.13 {
    execsql {SELECT round(t1,2) FROM tbl1}
  } {0.0 0.0 0.0 0.0 0.0}
  do_test func-4.14 {
    execsql {SELECT typeof(round(5.1,1));}
  } {real}
  do_test func-4.15 {
    execsql {SELECT typeof(round(5.1));}
  } {real}
  do_test func-4.16 {
    catchsql {SELECT round(b,2.0) FROM t1 ORDER BY b}
  } {0 {-2.0 1.23 2.0}}
}

# Test the upper() and lower() functions
#
do_test func-5.1 {
  execsql {SELECT upper(t1) FROM tbl1}
} {THIS PROGRAM IS FREE SOFTWARE}
do_test func-5.2 {

#
do_test func-7.1 {
  execsql {SELECT last_insert_rowid()}
} [db last_insert_rowid]

# Tests for aggregate functions and how they handle NULLs.
#
ifcapable floatingpoint {
  do_test func-8.1 {
    ifcapable explain {
      execsql {EXPLAIN SELECT sum(a) FROM t2;}
    }
    execsql {
      SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
    }
  } {68236 3 22745.33 1 67890 5}
}
ifcapable !floatingpoint {
  do_test func-8.1 {
    ifcapable explain {
      execsql {EXPLAIN SELECT sum(a) FROM t2;}
    }
    execsql {
      SELECT sum(a), count(a), avg(a), min(a), max(a), count(*) FROM t2;
    }
  } {68236 3 22745.0 1 67890 5}
}
do_test func-8.2 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

ifcapable tempdb {

  } {integer}
  do_test func-8.7 {
    execsql {
      SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x
                          UNION ALL SELECT -9223372036854775807)
    }
  } {real}
ifcapable floatingpoint {
  do_test func-8.8 {
    execsql {
      SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x
                          UNION ALL SELECT -9223372036850000000)
    }
  } {1}
}
ifcapable !floatingpoint {
  do_test func-8.8 {
    execsql {
      SELECT sum(x)>0 FROM (SELECT '9223372036' || '854775808' AS x
                          UNION ALL SELECT -9223372036850000000)
    }
  } {1}
}
}

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

  execsql {
    SELECT testfunc(
     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
     'string', NULL
    );
  }
} {{}}

ifcapable floatingpoint {
  do_test func-10.4 {
    execsql {
      SELECT testfunc(
       'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
       'double', 1.234
      );
    }
  } {1.234}
  do_test func-10.5 {
    execsql {
      SELECT testfunc(
       'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
       'int', 1234,
       'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
       'string', NULL,
       'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
       'double', 1.234,
       'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
       'int', 1234,
       'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
       'string', NULL,
       'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
       'double', 1.234
      );
    }
  } {1.234}
}

# Test the built-in sqlite_version(*) SQL function.
#
do_test func-11.1 {
  execsql {
    SELECT sqlite_version(*);
  }

    CREATE TABLE t5(x);
    INSERT INTO t5 VALUES(1);
    INSERT INTO t5 VALUES(-99);
    INSERT INTO t5 VALUES(10000);
    SELECT sum(x) FROM t5;
  }
} {9902}
ifcapable floatingpoint {
  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.
#
# The TOTAL of nothing is 0.0.
#
do_test func-18.3 {
  execsql {

  }
} 0
do_test func-18.11 {
  execsql {
    SELECT typeof(sum(x)) FROM t6
  }
} integer
ifcapable floatingpoint {
  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
}
ifcapable !floatingpoint {
  do_test func-18.12 {
    catchsql {
      INSERT INTO t6 VALUES(1<<62);
      SELECT sum(x) - ((1<<62)*2+1) from t6;
    }
  } {1 {integer overflow}}
  do_test func-18.13 {
    execsql {
      SELECT total(x) - ((1<<62)*2+1) FROM t6
    }
  } 0.0
}
if {[working_64bit_int]} {
  do_test func-18.14 {
    execsql {
      SELECT sum(-9223372036854775805);
    }
  } -9223372036854775805
}
ifcapable compound&&subquery {

do_test func-18.15 {
  catchsql {
    SELECT sum(x) FROM 
       (SELECT 9223372036854775807 AS x UNION ALL
        SELECT 10 AS x);
  }
} {1 {integer overflow}}
if {[working_64bit_int]} {
  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}}

  }
} {0 1}

} ;# ifcapable compound&&subquery

# Integer overflow on abs()
#
if {[working_64bit_int]} {
  do_test func-18.31 {
    catchsql {
      SELECT abs(-9223372036854775807);
    }
  } {0 9223372036854775807}
}
do_test func-18.32 {
  catchsql {
    SELECT abs(-9223372036854775807-1);
  }
} {1 {integer overflow}}

# The MATCH function exists but is only a stub and always throws an error.