SQLite4
Check-in [23ded9b859]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Use decimal arithmetic in the sum(), total() and avg() functions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 23ded9b859ec2104e0eb361d0c63c4bfca79a447
User & Date: dan 2013-06-01 20:34:15
Context
2013-06-03
06:45
Fix handling of text and blob values in the sum() function. check-in: a43972e1eb user: dan tags: trunk
2013-06-01
22:58
Fix build error from redundant typedefs in test_mem.c introduced by 75b8ccc0a8. check-in: 3ae7214267 user: peterreid tags: trunk
20:34
Use decimal arithmetic in the sum(), total() and avg() functions. check-in: 23ded9b859 user: dan tags: trunk
06:08
Add missing file test_num.c. check-in: 5ab129f59e user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/func.c.

1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
....
1190
1191
1192
1193
1194
1195
1196

1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213



1214
1215
1216
1217
1218



1219
1220
1221
1222
1223
1224
1225
1226


1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240

/*
** An instance of the following structure holds the context of a
** sum() or avg() aggregate computation.
*/
typedef struct SumCtx SumCtx;
struct SumCtx {
  double rSum;      /* Floating point sum */
  i64 iSum;         /* Integer sum */   
  i64 cnt;          /* Number of elements summed */
  u8 overflow;      /* True if integer overflow seen */
  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++;

    if( type==SQLITE4_INTEGER ){
      i64 v = sqlite4_value_int64(argv[0]);
      p->rSum += v;
      if( (p->approx|p->overflow)==0 && sqlite4AddInt64(&p->iSum, v) ){
        p->overflow = 1;
      }
    }else{
      p->rSum += sqlite4_value_double(argv[0]);
      p->approx = 1;
    }
  }
}
static void sumFinalize(sqlite4_context *context){
  SumCtx *p;
  p = sqlite4_aggregate_context(context, 0);
  if( p && p->cnt>0 ){
    if( p->overflow ){



      sqlite4_result_error(context,"integer overflow",-1);
    }else if( p->approx ){
      sqlite4_result_double(context, p->rSum);
    }else{
      sqlite4_result_int64(context, p->iSum);



    }
  }
}
static void avgFinalize(sqlite4_context *context){
  SumCtx *p;
  p = sqlite4_aggregate_context(context, 0);
  if( p && p->cnt>0 ){
    sqlite4_result_double(context, p->rSum/(double)p->cnt);


  }
}
static void totalFinalize(sqlite4_context *context){
  SumCtx *p;
  p = sqlite4_aggregate_context(context, 0);
  /* (double)0 In case of SQLITE4_OMIT_FLOATING_POINT... */
  sqlite4_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;







|
<

<







 







>
|
<
<
<
<
<
<
<








|
>
>
>
|
<
<
|
|
>
>
>







|
>
>





|
<







1164
1165
1166
1167
1168
1169
1170
1171

1172

1173
1174
1175
1176
1177
1178
1179
....
1188
1189
1190
1191
1192
1193
1194
1195
1196







1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209


1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230

1231
1232
1233
1234
1235
1236
1237

/*
** 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;

Changes to src/math.c.

534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549









550
551
552
553
554
555
556
*/
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 = 0;
  for(i=0; i<num.e; i++){
    if( pbLossy && iRet>L10 ) *pbLossy = 1;
    iRet = iRet * 10;
  }
  for(i=num.e; i<0; i++){
    if( pbLossy && (iRet % 10) ) *pbLossy = 1;
    iRet = iRet / 10;
  }










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


/*







|
|
<
<





>
>
>
>
>
>
>
>
>







534
535
536
537
538
539
540
541
542


543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
*/
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;
}


/*

Changes to src/sqlite.h.in.

79
80
81
82
83
84
85














86
87
88
89
90
91
92
....
2412
2413
2414
2415
2416
2417
2418

2419
2420
2421
2422
2423
2424
2425
....
2665
2666
2667
2668
2669
2670
2671

2672
2673
2674
2675
2676
2677
2678
....
4083
4084
4085
4086
4087
4088
4089
4090
4091
4092
4093
4094
4095
4096
4097
4098
4099
4100
4101
4102
4103
4104
4105
4106
4107
4108
4109
4110
** CAPIREF: String length type
**
** A type for measuring the length of the string.  Like size_t but
** does not require &lt;stddef.h&gt;
*/
typedef int sqlite4_size_t;















/*
** 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*);


/*
** 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_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: 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 */
};

/*
** 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);







>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>







 







>







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<







79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
....
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
....
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
....
4099
4100
4101
4102
4103
4104
4105














4106
4107
4108
4109
4110
4111
4112
** CAPIREF: String length type
**
** A type for measuring the length of the string.  Like size_t but
** does not require &lt;stddef.h&gt;
*/
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);

Changes to src/vdbe.c.

294
295
296
297
298
299
300




301
302
303
304
305
306
307
  Mem *pMem = (Mem*)pVal;
  if( pMem->type==SQLITE4_TEXT ){
    applyNumericAffinity(pMem);
    sqlite4VdbeMemStoreType(pMem);
  }
  return pMem->type;
}





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







>
>
>
>







294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
  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, 

Changes to src/vdbeInt.h.

400
401
402
403
404
405
406

407
408
409
410
411
412
413
void sqlite4VdbeMemSetInt64(Mem*, i64);
#ifdef SQLITE4_OMIT_FLOATING_POINT
# define sqlite4VdbeMemSetDouble sqlite4VdbeMemSetInt64
#else
  void sqlite4VdbeMemSetDouble(Mem*, double);
#endif
void sqlite4VdbeMemSetNull(Mem*);

int sqlite4VdbeMemMakeWriteable(Mem*);
int sqlite4VdbeMemStringify(Mem*, int);
i64 sqlite4VdbeIntValue(Mem*);
int sqlite4VdbeMemIntegerify(Mem*);
double sqlite4VdbeRealValue(Mem*);
sqlite4_num sqlite4VdbeNumValue(Mem *);
void sqlite4VdbeIntegerAffinity(Mem*);







>







400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
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*);

Changes to src/vdbeapi.c.

228
229
230
231
232
233
234




235
236
237
238
239
240
241
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_null(sqlite4_context *pCtx){
  assert( sqlite4_mutex_held(pCtx->s.db->mutex) );
  sqlite4VdbeMemSetNull(&pCtx->s);
}
void sqlite4_result_text(
  sqlite4_context *pCtx, 







>
>
>
>







228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
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, 

Changes to src/vdbemem.c.

426
427
428
429
430
431
432








433
434
435
436
437
438
439
*/
void sqlite4VdbeMemSetInt64(Mem *pMem, i64 val){
  sqlite4VdbeMemRelease(pMem);
  pMem->u.num = sqlite4_num_from_int64(val);
  pMem->flags = MEM_Int;
  pMem->type = SQLITE4_INTEGER;
}









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







>
>
>
>
>
>
>
>







426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
*/
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){

Changes to test/func.test.

351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
...
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
...
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
...
752
753
754
755
756
757
758

759
760
761
762
763
764
765
...
795
796
797
798
799
800
801

802
803
804
805
806
807
808
....
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
  execsql {SELECT coalesce(nullif(1,2),'nil')}
} {1}
do_test func-6.5 {
  execsql {SELECT coalesce(nullif(1,NULL),'nil')}
} {1}


# Test the last_insert_rowid() function
#
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;}
    }
................................................................................
# 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 {

  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 {

    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}

# This is to test the deprecated sqlite4_aggregate_count() API.
#
ifcapable deprecated {
  do_test func-23.1 {
    sqlite4_create_aggregate db
    execsql {
      SELECT legacy_count() FROM t6;
    }
  } {3}
}

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







<
<
<
<
<
<







 







|







 







|







 







>







 







>







 







|






|











<
<
<
<
<
<
<
<
<
<
<







351
352
353
354
355
356
357






358
359
360
361
362
363
364
...
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
...
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
...
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
...
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
....
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068











1069
1070
1071
1072
1073
1074
1075
  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}

Changes to test/simple.test.

1527
1528
1529
1530
1531
1532
1533











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

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












finish_test








>
>
>
>
>
>
>
>
>
>
>



1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
    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