SQLite4
Check-in [7b3bb63808]
Not logged in

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

Overview
Comment:Modify the round() and abs() SQL functions to use decimal arithmetic.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7b3bb638088da175d59351a48fbd1c0b04f743c0
User & Date: dan 2013-06-03 11:47:31
Context
2013-06-03
15:23
Merge overflow and rounding fixes for sqlite4_num_to_int64(). check-in: e8db1e81a4 user: dan tags: trunk
11:47
Modify the round() and abs() SQL functions to use decimal arithmetic. check-in: 7b3bb63808 user: dan tags: trunk
06:45
Fix handling of text and blob values in the sum() function. check-in: a43972e1eb user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/func.c.

123
124
125
126
127
128
129

130
131
132
133
134
135
136
...
145
146
147
148
149
150
151
152
153
154
155
156
157
158

159
160
161
162
163
164
165
166
...
253
254
255
256
257
258
259
260
261

262
263
264
265
266
267
268
269
270
271
272
273

274
275
276
277
278

279
280
281
282
283
284
285
286
287

288
289
290
291

292
293
294
295
296
297
298
299
300
301
....
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
**
** IMP: R-23979-26855 The abs(X) function returns the absolute value of
** the numeric argument X. 
*/
static void absFunc(sqlite4_context *context, int argc, sqlite4_value **argv){
  assert( argc==1 );
  UNUSED_PARAMETER(argc);

  switch( sqlite4_value_type(argv[0]) ){
    case SQLITE4_INTEGER: {
      i64 iVal = sqlite4_value_int64(argv[0]);
      if( iVal<0 ){
        if( (iVal<<1)==0 ){
          /* IMP: R-35460-15084 If X is the integer -9223372036854775807 then
          ** abs(X) throws an integer overflow error since there is no
................................................................................
    }
    case SQLITE4_NULL: {
      /* IMP: R-37434-19929 Abs(X) returns NULL if X is NULL. */
      sqlite4_result_null(context);
      break;
    }
    default: {
      /* Because sqlite4_value_double() returns 0.0 if the argument is not
      ** something that can be converted into a number, we have:
      ** IMP: R-57326-31541 Abs(X) return 0.0 if X is a string or blob that
      ** cannot be converted to a numeric value. 
      */
      double rVal = sqlite4_value_double(argv[0]);
      if( rVal<0 ) rVal = -rVal;

      sqlite4_result_double(context, rVal);
      break;
    }
  }
}

/*
** Implementation of the substr() function.
................................................................................
    sqlite4_result_blob(context, (char*)&z[p1], (int)p2, SQLITE4_TRANSIENT, 0);
  }
}

/*
** Implementation of the round() function
*/
#ifndef SQLITE4_OMIT_FLOATING_POINT
static void roundFunc(sqlite4_context *context, int argc, sqlite4_value **argv){

  int n = 0;
  double r;
  char *zBuf;
  sqlite4_env *pEnv = sqlite4_context_env(context);
  assert( argc==1 || argc==2 );
  if( argc==2 ){
    if( SQLITE4_NULL==sqlite4_value_type(argv[1]) ) return;
    n = sqlite4_value_int(argv[1]);
    if( n>30 ) n = 30;
    if( n<0 ) n = 0;
  }
  if( sqlite4_value_type(argv[0])==SQLITE4_NULL ) return;

  r = sqlite4_value_double(argv[0]);
  /* If Y==0 and X will fit in a 64-bit int,
  ** handle the rounding directly,
  ** otherwise use printf.
  */

  if( n==0 && r>=0 && r<LARGEST_INT64-1 ){
    r = (double)((sqlite4_int64)(r+0.5));
  }else if( n==0 && r<0 && (-r)<LARGEST_INT64-1 ){
    r = -(double)((sqlite4_int64)((-r)+0.5));
  }else{
    zBuf = sqlite4_mprintf(pEnv,"%.*f",n,r);
    if( zBuf==0 ){
      sqlite4_result_error_nomem(context);
      return;

    }
    sqlite4AtoF(zBuf, &r, sqlite4Strlen30(zBuf), SQLITE4_UTF8);
    sqlite4_free(pEnv, zBuf);
  }

  sqlite4_result_double(context, r);
}
#endif

/*
** Allocate nByte bytes of space using sqlite4_malloc(). If the
** allocation fails, call sqlite4_result_error_nomem() to notify
** the database handle that malloc() has failed and return NULL.
** If nByte is larger than the maximum string or blob length, then
** raise an SQLITE4_TOOBIG exception and return NULL.
................................................................................
    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 SQLITE4_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,           0, 0, 0, 0                ),
/*  FUNCTION(coalesce,          -1, 0, 0, ifnullFunc       ), */
    {-1,SQLITE4_UTF8,SQLITE4_FUNC_COALESCE,0,0,ifnullFunc,0,0,"coalesce",0,0},
    FUNCTION(hex,                1, 0, 0, hexFunc          ),







>







 







|


|
<
|
<
>
|







 







<

>
|
|
<
<








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

<







 







<


<







123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
...
146
147
148
149
150
151
152
153
154
155
156

157

158
159
160
161
162
163
164
165
166
...
253
254
255
256
257
258
259

260
261
262
263


264
265
266
267
268
269
270
271
272
273

274
275

276
277
278
279
280





281
282



283
284
285

286
287
288
289
290
291
292
....
1454
1455
1456
1457
1458
1459
1460

1461
1462

1463
1464
1465
1466
1467
1468
1469
**
** IMP: R-23979-26855 The abs(X) function returns the absolute value of
** the numeric argument X. 
*/
static void absFunc(sqlite4_context *context, int argc, sqlite4_value **argv){
  assert( argc==1 );
  UNUSED_PARAMETER(argc);

  switch( sqlite4_value_type(argv[0]) ){
    case SQLITE4_INTEGER: {
      i64 iVal = sqlite4_value_int64(argv[0]);
      if( iVal<0 ){
        if( (iVal<<1)==0 ){
          /* IMP: R-35460-15084 If X is the integer -9223372036854775807 then
          ** abs(X) throws an integer overflow error since there is no
................................................................................
    }
    case SQLITE4_NULL: {
      /* IMP: R-37434-19929 Abs(X) returns NULL if X is NULL. */
      sqlite4_result_null(context);
      break;
    }
    default: {
      /* Because sqlite4_value_num() returns 0.0 if the argument is not
      ** something that can be converted into a number, we have:
      ** IMP: R-57326-31541 Abs(X) return 0.0 if X is a string or blob that
      ** cannot be converted to a numeric value.  */

      sqlite4_num num = sqlite4_value_num(argv[0]);

      num.sign = 0;
      sqlite4_result_num(context, num);
      break;
    }
  }
}

/*
** Implementation of the substr() function.
................................................................................
    sqlite4_result_blob(context, (char*)&z[p1], (int)p2, SQLITE4_TRANSIENT, 0);
  }
}

/*
** Implementation of the round() function
*/

static void roundFunc(sqlite4_context *context, int argc, sqlite4_value **argv){
  int n = 0;                      /* Second argument to this function */
  int p10;
  sqlite4_num num;


  assert( argc==1 || argc==2 );
  if( argc==2 ){
    if( SQLITE4_NULL==sqlite4_value_type(argv[1]) ) return;
    n = sqlite4_value_int(argv[1]);
    if( n>30 ) n = 30;
    if( n<0 ) n = 0;
  }
  if( sqlite4_value_type(argv[0])==SQLITE4_NULL ) return;

  num = sqlite4_value_num(argv[0]);

  p10 = (num.e*-1) - n;
  if( p10>0 ){

    int rnd;
    int i;
    u64 div = 1;
    for(i=0; i<p10; i++) div = div * 10;
    rnd = (num.m % div) >= (div/2);





    num.m = ((num.m / div) + rnd) * div;
  }



  num.approx = 0;
  sqlite4_result_num(context, num);
}


/*
** Allocate nByte bytes of space using sqlite4_malloc(). If the
** allocation fails, call sqlite4_result_error_nomem() to notify
** the database handle that malloc() has failed and return NULL.
** If nByte is larger than the maximum string or blob length, then
** raise an SQLITE4_TOOBIG exception and return NULL.
................................................................................
    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          ),

    FUNCTION(round,              1, 0, 0, roundFunc        ),
    FUNCTION(round,              2, 0, 0, roundFunc        ),

    FUNCTION(upper,              1, 0, 0, upperFunc        ),
    FUNCTION(lower,              1, 0, 0, lowerFunc        ),
    FUNCTION(coalesce,           1, 0, 0, 0                ),
    FUNCTION(coalesce,           0, 0, 0, 0                ),
/*  FUNCTION(coalesce,          -1, 0, 0, ifnullFunc       ), */
    {-1,SQLITE4_UTF8,SQLITE4_FUNC_COALESCE,0,0,ifnullFunc,0,0,"coalesce",0,0},
    FUNCTION(hex,                1, 0, 0, hexFunc          ),

Changes to test/func.test.

134
135
136
137
138
139
140

141
142
143
144
145
146
147
...
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
...
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
  execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}
} "F- er in \u1234h"
do_test func-3.10 {
  execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}
} "TF- ter ain i\u1234h"
do_test func-3.99 {
  execsql {DELETE FROM tbl1}

  foreach word {this program is free software} {
    execsql "INSERT INTO tbl1 VALUES('$word')"
  }
  execsql {SELECT t1 FROM tbl1}
} {this program is free software}

} ;# End \u1234!=u1234
................................................................................
    SELECT match(a,b) FROM t1 WHERE 0;
  }
} {}
do_test func-19.2 {
  catchsql {
    SELECT 'abc' MATCH 'xyz';
  }
} {1 {unable to use function MATCH in the requested context}}
do_test func-19.3 {
  catchsql {
    SELECT 'abc' NOT MATCH 'xyz';
  }
} {1 {unable to use function MATCH in the requested context}}
do_test func-19.4 {
  catchsql {
................................................................................
do_test func-21.2 {
  catchsql {
    SELECT replace(1,2,3,4);
  }
} {1 {wrong number of arguments to function replace()}}
do_test func-21.3 {
  execsql {
    SELECT typeof(replace("This is the main test string", NULL, "ALT"));
  }
} {null}
do_test func-21.4 {
  execsql {
    SELECT typeof(replace(NULL, "main", "ALT"));
  }
} {null}
do_test func-21.5 {
  execsql {
    SELECT typeof(replace("This is the main test string", "main", NULL));
  }
} {null}
do_test func-21.6 {
  execsql {
    SELECT replace("This is the main test string", "main", "ALT");
  }
} {{This is the ALT test string}}
do_test func-21.7 {
  execsql {
    SELECT replace("This is the main test string", "main", "larger-main");
  }
} {{This is the larger-main test string}}
do_test func-21.8 {
  execsql {
    SELECT replace("aaaaaaa", "a", "0123456789");
  }
} {0123456789012345678901234567890123456789012345678901234567890123456789}

ifcapable tclvar {
  do_test func-21.9 {
    # Attempt to exploit a buffer-overflow that at one time existed 
    # in the REPLACE function. 







>







 







|







 







|




|




|




|




|




|







134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
...
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
...
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
  execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}
} "F- er in \u1234h"
do_test func-3.10 {
  execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}
} "TF- ter ain i\u1234h"
do_test func-3.99 {
  execsql {DELETE FROM tbl1}
  optimize_db
  foreach word {this program is free software} {
    execsql "INSERT INTO tbl1 VALUES('$word')"
  }
  execsql {SELECT t1 FROM tbl1}
} {this program is free software}

} ;# End \u1234!=u1234
................................................................................
    SELECT match(a,b) FROM t1 WHERE 0;
  }
} {}
do_test func-19.2 {
  catchsql {
    SELECT 'abc' MATCH 'xyz';
  }
} {1 {lhs of MATCH operator must be a table name}}
do_test func-19.3 {
  catchsql {
    SELECT 'abc' NOT MATCH 'xyz';
  }
} {1 {unable to use function MATCH in the requested context}}
do_test func-19.4 {
  catchsql {
................................................................................
do_test func-21.2 {
  catchsql {
    SELECT replace(1,2,3,4);
  }
} {1 {wrong number of arguments to function replace()}}
do_test func-21.3 {
  execsql {
    SELECT typeof(replace('This is the main test string', NULL, 'ALT'));
  }
} {null}
do_test func-21.4 {
  execsql {
    SELECT typeof(replace(NULL, 'main', 'ALT'));
  }
} {null}
do_test func-21.5 {
  execsql {
    SELECT typeof(replace('This is the main test string', 'main', NULL));
  }
} {null}
do_test func-21.6 {
  execsql {
    SELECT replace('This is the main test string', 'main', 'ALT');
  }
} {{This is the ALT test string}}
do_test func-21.7 {
  execsql {
    SELECT replace('This is the main test string', 'main', 'larger-main');
  }
} {{This is the larger-main test string}}
do_test func-21.8 {
  execsql {
    SELECT replace('aaaaaaa', 'a', '0123456789');
  }
} {0123456789012345678901234567890123456789012345678901234567890123456789}

ifcapable tclvar {
  do_test func-21.9 {
    # Attempt to exploit a buffer-overflow that at one time existed 
    # in the REPLACE function. 

Changes to test/permutations.test.

164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
  distinct.test distinctagg.test
  exists.test
  e_droptrigger.test e_dropview.test
  e_resolve.test e_dropview.test
  e_select2.test
  enc4.test
  fkey1.test fkey2.test fkey3.test fkey4.test
  func2.test func3.test 
  fuzz2.test 
  in.test in4.test
  index2.test index3.test index4.test 
  insert.test insert2.test insert3.test insert5.test
  join.test join2.test join3.test join4.test join5.test join6.test
  keyword1.test
  limit.test







|







164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
  distinct.test distinctagg.test
  exists.test
  e_droptrigger.test e_dropview.test
  e_resolve.test e_dropview.test
  e_select2.test
  enc4.test
  fkey1.test fkey2.test fkey3.test fkey4.test
  func.test func2.test func3.test 
  fuzz2.test 
  in.test in4.test
  index2.test index3.test index4.test 
  insert.test insert2.test insert3.test insert5.test
  join.test join2.test join3.test join4.test join5.test join6.test
  keyword1.test
  limit.test

Changes to test/testInt.h.

15
16
17
18
19
20
21




22
23
24
25
26
27
28

#include <tcl.h>


/* test_auth.c */
int Sqlitetest_auth_init(Tcl_Interp *interp);





/* test_main.c */
void sqlite4TestInit(Tcl_Interp*);
void *sqlite4TestTextToPtr(const char *z);
int sqlite4TestDbHandle(Tcl_Interp *, Tcl_Obj *, sqlite4 **);
int sqlite4TestSetResult(Tcl_Interp *interp, int rc);

/* test_mem.c */







>
>
>
>







15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

#include <tcl.h>


/* test_auth.c */
int Sqlitetest_auth_init(Tcl_Interp *interp);


/* test_func.c */
int Sqlitetest_func_Init(Tcl_Interp *);

/* test_main.c */
void sqlite4TestInit(Tcl_Interp*);
void *sqlite4TestTextToPtr(const char *z);
int sqlite4TestDbHandle(Tcl_Interp *, Tcl_Obj *, sqlite4 **);
int sqlite4TestSetResult(Tcl_Interp *interp, int rc);

/* test_mem.c */

Changes to test/test_main.c.

4139
4140
4141
4142
4143
4144
4145

4146
4147
4148
4149
4150
4151
4152
  sqlite4_test_control(SQLITE4_TESTCTRL_OPTIMIZATIONS, db, mask);
  return TCL_OK;
}

void sqlite4TestInit(Tcl_Interp *interp){
  Sqlitetest_auth_init(interp);
  Sqlitetest_num_init(interp);

}

/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest1_Init(Tcl_Interp *interp){
  extern int sqlite4_search_count;







>







4139
4140
4141
4142
4143
4144
4145
4146
4147
4148
4149
4150
4151
4152
4153
  sqlite4_test_control(SQLITE4_TESTCTRL_OPTIMIZATIONS, db, mask);
  return TCL_OK;
}

void sqlite4TestInit(Tcl_Interp *interp){
  Sqlitetest_auth_init(interp);
  Sqlitetest_num_init(interp);
  Sqlitetest_func_Init(interp);
}

/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest1_Init(Tcl_Interp *interp){
  extern int sqlite4_search_count;