SQLite4  Check-in [23ded9b859]

/*
** An instance of the following structure holds the context of a
** sum() or avg() aggregate computation.
*/
typedef struct SumCtx SumCtx;
struct SumCtx {

  sqlite4_num sum;  /* Sum so far */
  i64 cnt;          /* Number of elements summed */

  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

  int type;
  assert( argc==1 );
  UNUSED_PARAMETER(argc);
  p = sqlite4_aggregate_context(context, sizeof(*p));
  type = sqlite4_value_numeric_type(argv[0]);
  if( p && type!=SQLITE4_NULL ){
    p->cnt++;
    p->sum = sqlite4_num_add(p->sum, sqlite4_value_num(argv[0]));
    if( type==SQLITE4_FLOAT ){







      p->approx = 1;
    }
  }
}
static void sumFinalize(sqlite4_context *context){
  SumCtx *p;
  p = sqlite4_aggregate_context(context, 0);
  if( p && p->cnt>0 ){
    if( p->approx==0 ){
      int bLossy;
      i64 iVal = sqlite4_num_to_int64(p->sum, &bLossy);
      if( bLossy ){
        sqlite4_result_error(context, "integer overflow", -1);
      }else{
        sqlite4_result_int64(context, iVal);
      }
    }else{
      sqlite4_result_num(context, p->sum);
    }
  }
}
static void avgFinalize(sqlite4_context *context){
  SumCtx *p;
  p = sqlite4_aggregate_context(context, 0);
  if( p && p->cnt>0 ){
    sqlite4_result_num(context, 
        sqlite4_num_div(p->sum, sqlite4_num_from_int64(p->cnt))
    );
  }
}
static void totalFinalize(sqlite4_context *context){
  SumCtx *p;
  p = sqlite4_aggregate_context(context, 0);

  sqlite4_result_num(context, (p ? p->sum : sqlite4_num_from_int64(0)));
}

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

*/
sqlite4_int64 sqlite4_num_to_int64(sqlite4_num num, int *pbLossy){
  static const i64 L10 = (LARGEST_INT64 / 10);
  i64 iRet;
  int i;
  iRet = num.m;

  if( pbLossy ){

    *pbLossy = (num.m==LARGEST_UINT64 || (num.m+num.sign)>(u64)LARGEST_INT64);

  }
  for(i=num.e; i<0; i++){
    if( pbLossy && (iRet % 10) ) *pbLossy = 1;
    iRet = iRet / 10;
  }
  for(i=0; i<num.e; i++){
    if( pbLossy && iRet>L10 ) *pbLossy = 1;
    iRet = iRet * 10;
  }


#define LARGEST_INT64  (0xffffffff|(((i64)0x7fffffff)<<32))
#define SMALLEST_INT64 (((i64)-1) - LARGEST_INT64)
#define LARGEST_UINT64  (0xffffffff|(((u64)0xffffffff)<<32))

  if( num.sign ) iRet = iRet*-1;
  return iRet;
}


/*

** CAPIREF: String length type
**
** A type for measuring the length of the string.  Like size_t but
** does not require <stddef.h>
*/
typedef int sqlite4_size_t;

/*
** CAPI4REF: Representation Of Numbers
**
** Every number in SQLite is represented in memory by an instance of
** the following object.
*/
typedef struct sqlite4_num sqlite4_num;
struct sqlite4_num {
  unsigned char sign;     /* Sign of the overall value */
  unsigned char approx;   /* True if the value is approximate */
  short e;                /* The exponent. */
  sqlite4_uint64 m;       /* The significant */
};

/*
** Available memory allocator object subtypes:
*/
typedef enum {
  SQLITE4_MM_SYSTEM = 1,     /* Use the system malloc() */
  SQLITE4_MM_ONESIZE = 2,    /* All allocations map to a fixed size */
  SQLITE4_MM_OVERFLOW = 3,   /* Two allocators. Use A first; failover to B */

sqlite4_int64 sqlite4_value_int64(sqlite4_value*);
const char *sqlite4_value_text(sqlite4_value*, int *pnByte);
const void *sqlite4_value_text16(sqlite4_value*, int *pnByte);
const void *sqlite4_value_text16le(sqlite4_value*, int *pnByte);
const void *sqlite4_value_text16be(sqlite4_value*, int *pnByte);
int sqlite4_value_type(sqlite4_value*);
int sqlite4_value_numeric_type(sqlite4_value*);
sqlite4_num sqlite4_value_num(sqlite4_value*);

/*
** CAPIREF: Obtain Aggregate Function Context
**
** Implementations of aggregate SQL functions use this
** routine to allocate memory for storing their state.
**

void sqlite4_result_error16(sqlite4_context*, const void*, int);
void sqlite4_result_error_toobig(sqlite4_context*);
void sqlite4_result_error_nomem(sqlite4_context*);
void sqlite4_result_error_code(sqlite4_context*, int);
void sqlite4_result_int(sqlite4_context*, int);
void sqlite4_result_int64(sqlite4_context*, sqlite4_int64);
void sqlite4_result_null(sqlite4_context*);
void sqlite4_result_num(sqlite4_context*, sqlite4_num);
void sqlite4_result_text(sqlite4_context*, const char*, int,
                         void(*)(void*,void*),void*);
void sqlite4_result_text16(sqlite4_context*, const void*, int,
                           void(*)(void*,void*),void*);
void sqlite4_result_text16le(sqlite4_context*, const void*, int,
                             void(*)(void*,void*),void*);
void sqlite4_result_text16be(sqlite4_context*, const void*, int,

typedef int (*sqlite4_kvfactory)(
  sqlite4_env *pEnv,             /* The environment to use */
  sqlite4_kvstore **ppKVStore,   /* OUT: New KV store returned here */
  const char *zFilename,         /* Name of database file to open */
  unsigned flags                 /* Bit flags */
);















/*
** CAPI4REF: Operations On SQLite Number Objects
*/
sqlite4_num sqlite4_num_add(sqlite4_num, sqlite4_num);
sqlite4_num sqlite4_num_sub(sqlite4_num, sqlite4_num);
sqlite4_num sqlite4_num_mul(sqlite4_num, sqlite4_num);
sqlite4_num sqlite4_num_div(sqlite4_num, sqlite4_num);

  Mem *pMem = (Mem*)pVal;
  if( pMem->type==SQLITE4_TEXT ){
    applyNumericAffinity(pMem);
    sqlite4VdbeMemStoreType(pMem);
  }
  return pMem->type;
}

sqlite4_num sqlite4_value_num(sqlite4_value *pVal){
  return sqlite4VdbeNumValue((Mem*)pVal);
}

/*
** Exported version of applyAffinity(). This one works on sqlite4_value*, 
** not the internal Mem* type.
*/
void sqlite4ValueApplyAffinity(
  sqlite4_value *pVal, 

void sqlite4VdbeMemSetInt64(Mem*, i64);
#ifdef SQLITE4_OMIT_FLOATING_POINT
# define sqlite4VdbeMemSetDouble sqlite4VdbeMemSetInt64
#else
  void sqlite4VdbeMemSetDouble(Mem*, double);
#endif
void sqlite4VdbeMemSetNull(Mem*);
void sqlite4VdbeMemSetNum(Mem *, sqlite4_num, int);
int sqlite4VdbeMemMakeWriteable(Mem*);
int sqlite4VdbeMemStringify(Mem*, int);
i64 sqlite4VdbeIntValue(Mem*);
int sqlite4VdbeMemIntegerify(Mem*);
double sqlite4VdbeRealValue(Mem*);
sqlite4_num sqlite4VdbeNumValue(Mem *);
void sqlite4VdbeIntegerAffinity(Mem*);

void sqlite4_result_int(sqlite4_context *pCtx, int iVal){
  assert( sqlite4_mutex_held(pCtx->s.db->mutex) );
  sqlite4VdbeMemSetInt64(&pCtx->s, (i64)iVal);
}
void sqlite4_result_int64(sqlite4_context *pCtx, i64 iVal){
  assert( sqlite4_mutex_held(pCtx->s.db->mutex) );
  sqlite4VdbeMemSetInt64(&pCtx->s, iVal);
}
void sqlite4_result_num(sqlite4_context *pCtx, sqlite4_num val){
  assert( sqlite4_mutex_held(pCtx->s.db->mutex) );
  sqlite4VdbeMemSetNum(&pCtx->s, val, MEM_Real);
}
void sqlite4_result_null(sqlite4_context *pCtx){
  assert( sqlite4_mutex_held(pCtx->s.db->mutex) );
  sqlite4VdbeMemSetNull(&pCtx->s);
}
void sqlite4_result_text(
  sqlite4_context *pCtx, 

*/
void sqlite4VdbeMemSetInt64(Mem *pMem, i64 val){
  sqlite4VdbeMemRelease(pMem);
  pMem->u.num = sqlite4_num_from_int64(val);
  pMem->flags = MEM_Int;
  pMem->type = SQLITE4_INTEGER;
}

void sqlite4VdbeMemSetNum(Mem *pMem, sqlite4_num val, int flag){
  assert( flag==MEM_Int || flag==MEM_Real );
  sqlite4VdbeMemRelease(pMem);
  pMem->u.num = val;
  pMem->flags = flag;
  sqlite4VdbeMemStoreType(pMem);
}

#ifndef SQLITE4_OMIT_FLOATING_POINT
/*
** Delete any previous value and set the value stored in *pMem to val,
** manifest type REAL.
*/
void sqlite4VdbeMemSetDouble(Mem *pMem, double val){

  execsql {SELECT coalesce(nullif(1,2),'nil')}
} {1}
do_test func-6.5 {
  execsql {SELECT coalesce(nullif(1,NULL),'nil')}
} {1}








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

# The "hex()" function was added in order to be able to render blobs
# generated by randomblob().  So this seems like a good place to test
# hex().
#
ifcapable bloblit {
  do_test func-9.10 {
    execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')}
  } {00112233445566778899aabbccddeeff}
}
set encoding [db one {PRAGMA encoding}]
if {$encoding=="UTF-16le"} {
  do_test func-9.11-utf16le {
    execsql {SELECT hex(replace('abcdefg','ef','12'))}
  } {6100620063006400310032006700}
  do_test func-9.12-utf16le {

  set STMT [sqlite4_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL]
  sqlite4_bind_blob $::STMT 1 abc 3
  sqlite4_step $::STMT
  sqlite4_finalize $::STMT
  execsql {
    SELECT quote(a), quote(b) FROM tbl2;
  }
} {x'616263' NULL}

# Correctly handle function error messages that include %.  Ticket #1354
#
do_test func-17.1 {
  proc testfunc1 args {error "Error %d with %s percents %p"}
  db function testfunc1 ::testfunc1
  catchsql {

}

# 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 {
breakpoint
  execsql {
    DELETE FROM t5;
    SELECT sum(x), total(x) FROM t5;
  }
} {{} 0.0}
do_test func-18.4 {
  execsql {

do_test func-18.11 {
  execsql {
    SELECT typeof(sum(x)) FROM t6
  }
} integer
ifcapable floatingpoint {
  do_test func-18.12 {
  breakpoint
    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 {

} {{xyxzy  hi  }}
do_test func-22.13 {
  execsql {SELECT trim('  hi  ','');}
} {{  hi  }}
if {[db one {PRAGMA encoding}]=="UTF-8"} {
  do_test func-22.14 {
    execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))}
  } {f48fbfbf6869}
  do_test func-22.15 {
    execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61',
                             x'6162e1bfbfc280f48fbfbf'))}
  } {6869}
  do_test func-22.16 {
    execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));}
  } {ceb2ceb3}
}
do_test func-22.20 {
  execsql {SELECT typeof(trim(NULL));}
} {null}
do_test func-22.21 {
  execsql {SELECT typeof(trim(NULL,'xyz'));}
} {null}
do_test func-22.22 {
  execsql {SELECT typeof(trim('hello',NULL));}
} {null}












# The group_concat() function.
#
do_test func-24.1 {
  execsql {
    SELECT group_concat(t1) FROM tbl1
  }
} {this,program,is,free,software}

    INSERT INTO t1 VALUES(42, 3);
  }
} {}

do_execsql_test 78.2 {
    SELECT id, v FROM t1 WHERE id>1.5;
} {42 3}

#-------------------------------------------------------------------------
# Ticket f3d7aab1e5
#
reset_db
do_test 79.1 {
  set r1 [expr 0.684377705997032]
  execsql "CREATE TABLE t1(x UNIQUE)"
  execsql "INSERT INTO t1 VALUES($r1)"
  execsql "PRAGMA integrity_check"
} {ok}

finish_test