/ Check-in [465fd853]
Login

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

Overview
Comment:Add experimental tointeger() and todouble() SQL functions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | toTypeFuncs
Files: files | file ages | folders
SHA1: 465fd853d3e3544cb06b15ffa32ce25774d816c7
User & Date: mistachkin 2013-03-11 01:23:37
Context
2013-03-11
06:24
Add more tests. check-in: f9468e33 user: mistachkin tags: toTypeFuncs
01:23
Add experimental tointeger() and todouble() SQL functions. check-in: 465fd853 user: mistachkin tags: toTypeFuncs
2013-03-09
14:49
Add a test case for the problem fixed by the previous commit. check-in: e899b058 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/func.c.

   958    958         assert( sqlite3_value_type(argv[0])==SQLITE_NULL );
   959    959         sqlite3_result_text(context, "NULL", 4, SQLITE_STATIC);
   960    960         break;
   961    961       }
   962    962     }
   963    963   }
   964    964   
          965  +/*
          966  +** EXPERIMENTAL - This is not an official function.  The interface may
          967  +** change.  This function may disappear.  Do not write code that depends
          968  +** on this function.
          969  +**
          970  +** Implementation of the TOINTEGER() function.  This function takes a
          971  +** single argument.  If the argument is an integer or is a double that
          972  +** can be losslessly converted to an integer, the return value is the
          973  +** same as the argument.  If the argument is a double that cannot be
          974  +** losslessly represented as an integer, the return value is undefined.
          975  +** If the argument is NULL, the return value is NULL.  Otherwise, an
          976  +** attempt is made to convert the argument to an integer.  If the
          977  +** conversion is successful, the integer value is returned; otherwise,
          978  +** NULL is returned.
          979  +*/
          980  +static void tointegerFunc(
          981  +  sqlite3_context *context,
          982  +  int argc,
          983  +  sqlite3_value **argv
          984  +){
          985  +  assert( argc==1 );
          986  +  UNUSED_PARAMETER(argc);
          987  +  switch( sqlite3_value_type(argv[0]) ){
          988  +    case SQLITE_FLOAT:
          989  +    case SQLITE_INTEGER: {
          990  +      sqlite3_result_int64(context, sqlite3_value_int64(argv[0]));
          991  +      break;
          992  +    }
          993  +    case SQLITE_BLOB:
          994  +    case SQLITE_TEXT: {
          995  +      const unsigned char *zStr = sqlite3_value_text(argv[0]);
          996  +      if( zStr ){
          997  +        int nStr = sqlite3_value_bytes(argv[0]);
          998  +        if( nStr ){
          999  +          i64 iVal;
         1000  +          if( !sqlite3Atoi64(zStr, &iVal, nStr, SQLITE_UTF8) ){
         1001  +            sqlite3_result_int64(context, iVal);
         1002  +            return;
         1003  +          }
         1004  +        }
         1005  +      }
         1006  +      sqlite3_result_null(context);
         1007  +      break;
         1008  +    }
         1009  +    default: {
         1010  +      assert( sqlite3_value_type(argv[0])==SQLITE_NULL );
         1011  +      sqlite3_result_null(context);
         1012  +      break;
         1013  +    }
         1014  +  }
         1015  +}
         1016  +
         1017  +/*
         1018  +** EXPERIMENTAL - This is not an official function.  The interface may
         1019  +** change.  This function may disappear.  Do not write code that depends
         1020  +** on this function.
         1021  +**
         1022  +** Implementation of the TODOUBLE() function.  This function takes a
         1023  +** single argument.  If the argument is a double or is an integer that
         1024  +** can be losslessly converted to a double, the return value is the
         1025  +** same as the argument.  If the argument is an integer that cannot be
         1026  +** losslessly represented as a double, the return value is undefined.
         1027  +** If the argument is NULL, the return value is NULL.  Otherwise, an
         1028  +** attempt is made to convert the argument to a double.  If the
         1029  +** conversion is successful, the double value is returned; otherwise,
         1030  +** NULL is returned.
         1031  +*/
         1032  +#ifndef SQLITE_OMIT_FLOATING_POINT
         1033  +static void todoubleFunc(
         1034  +  sqlite3_context *context,
         1035  +  int argc,
         1036  +  sqlite3_value **argv
         1037  +){
         1038  +  assert( argc==1 );
         1039  +  UNUSED_PARAMETER(argc);
         1040  +  switch( sqlite3_value_type(argv[0]) ){
         1041  +    case SQLITE_FLOAT:
         1042  +    case SQLITE_INTEGER: {
         1043  +      sqlite3_result_double(context, sqlite3_value_double(argv[0]));
         1044  +      break;
         1045  +    }
         1046  +    case SQLITE_BLOB:
         1047  +    case SQLITE_TEXT: {
         1048  +      const unsigned char *zStr = sqlite3_value_text(argv[0]);
         1049  +      if( zStr ){
         1050  +        int nStr = sqlite3_value_bytes(argv[0]);
         1051  +        if( nStr ){
         1052  +          double rVal;
         1053  +          if( sqlite3AtoF(zStr, &rVal, nStr, SQLITE_UTF8) ){
         1054  +            sqlite3_result_double(context, rVal);
         1055  +            return;
         1056  +          }
         1057  +        }
         1058  +      }
         1059  +      sqlite3_result_null(context);
         1060  +      break;
         1061  +    }
         1062  +    default: {
         1063  +      assert( sqlite3_value_type(argv[0])==SQLITE_NULL );
         1064  +      sqlite3_result_null(context);
         1065  +      break;
         1066  +    }
         1067  +  }
         1068  +}
         1069  +#endif
         1070  +
   965   1071   /*
   966   1072   ** The unicode() function.  Return the integer unicode code-point value
   967   1073   ** for the first character of the input string. 
   968   1074   */
   969   1075   static void unicodeFunc(
   970   1076     sqlite3_context *context,
   971   1077     int argc,
................................................................................
  1666   1772       FUNCTION(sqlite_source_id,   0, 0, 0, sourceidFunc     ),
  1667   1773       FUNCTION(sqlite_log,         2, 0, 0, errlogFunc       ),
  1668   1774   #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS
  1669   1775       FUNCTION(sqlite_compileoption_used,1, 0, 0, compileoptionusedFunc  ),
  1670   1776       FUNCTION(sqlite_compileoption_get, 1, 0, 0, compileoptiongetFunc  ),
  1671   1777   #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */
  1672   1778       FUNCTION(quote,              1, 0, 0, quoteFunc        ),
         1779  +    FUNCTION(tointeger,          1, 0, 0, tointegerFunc    ),
         1780  +#ifndef SQLITE_OMIT_FLOATING_POINT
         1781  +    FUNCTION(todouble,           1, 0, 0, todoubleFunc     ),
         1782  +#endif
  1673   1783       FUNCTION(last_insert_rowid,  0, 0, 0, last_insert_rowid),
  1674   1784       FUNCTION(changes,            0, 0, 0, changes          ),
  1675   1785       FUNCTION(total_changes,      0, 0, 0, total_changes    ),
  1676   1786       FUNCTION(replace,            3, 0, 0, replaceFunc      ),
  1677   1787       FUNCTION(zeroblob,           1, 0, 0, zeroblobFunc     ),
  1678   1788     #ifdef SQLITE_SOUNDEX
  1679   1789       FUNCTION(soundex,            1, 0, 0, soundexFunc      ),

Added test/func4.test.

            1  +# 2013 March 10
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library. The
           12  +# focus of this file is testing the TOINTEGER() and TODOUBLE()
           13  +# functions.
           14  +#
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +
           18  +set i 0
           19  +do_execsql_test func4-1.[incr i] {
           20  +  SELECT tointeger(NULL);
           21  +} {{}}
           22  +do_execsql_test func4-1.[incr i] {
           23  +  SELECT tointeger('');
           24  +} {{}}
           25  +do_execsql_test func4-1.[incr i] {
           26  +  SELECT tointeger('   ');
           27  +} {{}}
           28  +do_execsql_test func4-1.[incr i] {
           29  +  SELECT tointeger('1234');
           30  +} {1234}
           31  +do_execsql_test func4-1.[incr i] {
           32  +  SELECT tointeger('   1234');
           33  +} {1234}
           34  +do_execsql_test func4-1.[incr i] {
           35  +  SELECT tointeger('bad');
           36  +} {{}}
           37  +do_execsql_test func4-1.[incr i] {
           38  +  SELECT tointeger('0xBAD');
           39  +} {{}}
           40  +do_execsql_test func4-1.[incr i] {
           41  +  SELECT tointeger('123BAD');
           42  +} {{}}
           43  +do_execsql_test func4-1.[incr i] {
           44  +  SELECT tointeger('0x123BAD');
           45  +} {{}}
           46  +do_execsql_test func4-1.[incr i] {
           47  +  SELECT tointeger('123NO');
           48  +} {{}}
           49  +do_execsql_test func4-1.[incr i] {
           50  +  SELECT tointeger('0x123NO');
           51  +} {{}}
           52  +do_execsql_test func4-1.[incr i] {
           53  +  SELECT tointeger('-0x1');
           54  +} {{}}
           55  +do_execsql_test func4-1.[incr i] {
           56  +  SELECT tointeger('-0x0');
           57  +} {{}}
           58  +do_execsql_test func4-1.[incr i] {
           59  +  SELECT tointeger('0x0');
           60  +} {{}}
           61  +do_execsql_test func4-1.[incr i] {
           62  +  SELECT tointeger('0x1');
           63  +} {{}}
           64  +do_execsql_test func4-1.[incr i] {
           65  +  SELECT tointeger(-1);
           66  +} {-1}
           67  +do_execsql_test func4-1.[incr i] {
           68  +  SELECT tointeger(-0);
           69  +} {0}
           70  +do_execsql_test func4-1.[incr i] {
           71  +  SELECT tointeger(0);
           72  +} {0}
           73  +do_execsql_test func4-1.[incr i] {
           74  +  SELECT tointeger(1);
           75  +} {1}
           76  +do_execsql_test func4-1.[incr i] {
           77  +  SELECT tointeger(-1.79769313486232e308 - 1);
           78  +} {-9223372036854775808}
           79  +do_execsql_test func4-1.[incr i] {
           80  +  SELECT tointeger(-1.79769313486232e308);
           81  +} {-9223372036854775808}
           82  +do_execsql_test func4-1.[incr i] {
           83  +  SELECT tointeger(-1.79769313486232e308 + 1);
           84  +} {-9223372036854775808}
           85  +do_execsql_test func4-1.[incr i] {
           86  +  SELECT tointeger(-9223372036854775808 - 1);
           87  +} {-9223372036854775808}
           88  +do_execsql_test func4-1.[incr i] {
           89  +  SELECT tointeger(-9223372036854775808);
           90  +} {-9223372036854775808}
           91  +do_execsql_test func4-1.[incr i] {
           92  +  SELECT tointeger(-9223372036854775808 + 1);
           93  +} {-9223372036854775807}
           94  +do_execsql_test func4-1.[incr i] {
           95  +  SELECT tointeger(-2147483648 - 1);
           96  +} {-2147483649}
           97  +do_execsql_test func4-1.[incr i] {
           98  +  SELECT tointeger(-2147483648);
           99  +} {-2147483648}
          100  +do_execsql_test func4-1.[incr i] {
          101  +  SELECT tointeger(-2147483648 + 1);
          102  +} {-2147483647}
          103  +do_execsql_test func4-1.[incr i] {
          104  +  SELECT tointeger(2147483647 - 1);
          105  +} {2147483646}
          106  +do_execsql_test func4-1.[incr i] {
          107  +  SELECT tointeger(2147483647);
          108  +} {2147483647}
          109  +do_execsql_test func4-1.[incr i] {
          110  +  SELECT tointeger(2147483647 + 1);
          111  +} {2147483648}
          112  +do_execsql_test func4-1.[incr i] {
          113  +  SELECT tointeger(9223372036854775807 - 1);
          114  +} {9223372036854775806}
          115  +do_execsql_test func4-1.[incr i] {
          116  +  SELECT tointeger(9223372036854775807);
          117  +} {9223372036854775807}
          118  +do_execsql_test func4-1.[incr i] {
          119  +  SELECT tointeger(9223372036854775807 + 1);
          120  +} {-9223372036854775808}
          121  +do_execsql_test func4-1.[incr i] {
          122  +  SELECT tointeger(1.79769313486232e308 - 1);
          123  +} {-9223372036854775808}
          124  +do_execsql_test func4-1.[incr i] {
          125  +  SELECT tointeger(1.79769313486232e308);
          126  +} {-9223372036854775808}
          127  +do_execsql_test func4-1.[incr i] {
          128  +  SELECT tointeger(1.79769313486232e308 + 1);
          129  +} {-9223372036854775808}
          130  +do_execsql_test func4-1.[incr i] {
          131  +  SELECT tointeger(4503599627370496 - 1);
          132  +} {4503599627370495}
          133  +do_execsql_test func4-1.[incr i] {
          134  +  SELECT tointeger(4503599627370496);
          135  +} {4503599627370496}
          136  +do_execsql_test func4-1.[incr i] {
          137  +  SELECT tointeger(4503599627370496 + 1);
          138  +} {4503599627370497}
          139  +do_execsql_test func4-1.[incr i] {
          140  +  SELECT tointeger(9007199254740992 - 1);
          141  +} {9007199254740991}
          142  +do_execsql_test func4-1.[incr i] {
          143  +  SELECT tointeger(9007199254740992);
          144  +} {9007199254740992}
          145  +do_execsql_test func4-1.[incr i] {
          146  +  SELECT tointeger(9007199254740992 + 1);
          147  +} {9007199254740993}
          148  +do_execsql_test func4-1.[incr i] {
          149  +  SELECT tointeger(9223372036854775808 - 1);
          150  +} {-9223372036854775808}
          151  +do_execsql_test func4-1.[incr i] {
          152  +  SELECT tointeger(9223372036854775808);
          153  +} {-9223372036854775808}
          154  +do_execsql_test func4-1.[incr i] {
          155  +  SELECT tointeger(9223372036854775808 + 1);
          156  +} {-9223372036854775808}
          157  +do_execsql_test func4-1.[incr i] {
          158  +  SELECT tointeger(18446744073709551616 - 1);
          159  +} {-9223372036854775808}
          160  +do_execsql_test func4-1.[incr i] {
          161  +  SELECT tointeger(18446744073709551616);
          162  +} {-9223372036854775808}
          163  +do_execsql_test func4-1.[incr i] {
          164  +  SELECT tointeger(18446744073709551616 + 1);
          165  +} {-9223372036854775808}
          166  +
          167  +ifcapable floatingpoint {
          168  +  set i 0
          169  +  do_execsql_test func4-2.[incr i] {
          170  +    SELECT todouble(NULL);
          171  +  } {{}}
          172  +  do_execsql_test func4-2.[incr i] {
          173  +    SELECT todouble('');
          174  +  } {{}}
          175  +  do_execsql_test func4-2.[incr i] {
          176  +    SELECT todouble('   ');
          177  +  } {{}}
          178  +  do_execsql_test func4-2.[incr i] {
          179  +    SELECT todouble('1234');
          180  +  } {1234.0}
          181  +  do_execsql_test func4-2.[incr i] {
          182  +    SELECT todouble('   1234');
          183  +  } {1234.0}
          184  +  do_execsql_test func4-2.[incr i] {
          185  +    SELECT todouble('bad');
          186  +  } {{}}
          187  +  do_execsql_test func4-2.[incr i] {
          188  +    SELECT todouble('0xBAD');
          189  +  } {{}}
          190  +  do_execsql_test func4-2.[incr i] {
          191  +    SELECT todouble('123BAD');
          192  +  } {{}}
          193  +  do_execsql_test func4-2.[incr i] {
          194  +    SELECT todouble('0x123BAD');
          195  +  } {{}}
          196  +  do_execsql_test func4-2.[incr i] {
          197  +    SELECT todouble('123NO');
          198  +  } {{}}
          199  +  do_execsql_test func4-2.[incr i] {
          200  +    SELECT todouble('0x123NO');
          201  +  } {{}}
          202  +  do_execsql_test func4-2.[incr i] {
          203  +    SELECT todouble('-0x1');
          204  +  } {{}}
          205  +  do_execsql_test func4-2.[incr i] {
          206  +    SELECT todouble('-0x0');
          207  +  } {{}}
          208  +  do_execsql_test func4-2.[incr i] {
          209  +    SELECT todouble('0x0');
          210  +  } {{}}
          211  +  do_execsql_test func4-2.[incr i] {
          212  +    SELECT todouble('0x1');
          213  +  } {{}}
          214  +  do_execsql_test func4-2.[incr i] {
          215  +    SELECT todouble(-1);
          216  +  } {-1.0}
          217  +  do_execsql_test func4-2.[incr i] {
          218  +    SELECT todouble(-0);
          219  +  } {0.0}
          220  +  do_execsql_test func4-2.[incr i] {
          221  +    SELECT todouble(0);
          222  +  } {0.0}
          223  +  do_execsql_test func4-2.[incr i] {
          224  +    SELECT todouble(1);
          225  +  } {1.0}
          226  +  do_execsql_test func4-2.[incr i] {
          227  +    SELECT todouble(-1.79769313486232e308 - 1);
          228  +  } {-Inf}
          229  +  do_execsql_test func4-2.[incr i] {
          230  +    SELECT todouble(-1.79769313486232e308);
          231  +  } {-Inf}
          232  +  do_execsql_test func4-2.[incr i] {
          233  +    SELECT todouble(-1.79769313486232e308 + 1);
          234  +  } {-Inf}
          235  +  do_execsql_test func4-2.[incr i] {
          236  +    SELECT todouble(-9223372036854775808 - 1);
          237  +  } {-9.22337203685478e+18}
          238  +  do_execsql_test func4-2.[incr i] {
          239  +    SELECT todouble(-9223372036854775808);
          240  +  } {-9.22337203685478e+18}
          241  +  do_execsql_test func4-2.[incr i] {
          242  +    SELECT todouble(-9223372036854775808 + 1);
          243  +  } {-9.22337203685478e+18}
          244  +  do_execsql_test func4-2.[incr i] {
          245  +    SELECT todouble(-2147483648 - 1);
          246  +  } {-2147483649.0}
          247  +  do_execsql_test func4-2.[incr i] {
          248  +    SELECT todouble(-2147483648);
          249  +  } {-2147483648.0}
          250  +  do_execsql_test func4-2.[incr i] {
          251  +    SELECT todouble(-2147483648 + 1);
          252  +  } {-2147483647.0}
          253  +  do_execsql_test func4-2.[incr i] {
          254  +    SELECT todouble(2147483647 - 1);
          255  +  } {2147483646.0}
          256  +  do_execsql_test func4-2.[incr i] {
          257  +    SELECT todouble(2147483647);
          258  +  } {2147483647.0}
          259  +  do_execsql_test func4-2.[incr i] {
          260  +    SELECT todouble(2147483647 + 1);
          261  +  } {2147483648.0}
          262  +  do_execsql_test func4-2.[incr i] {
          263  +    SELECT todouble(9223372036854775807 - 1);
          264  +  } {9.22337203685478e+18}
          265  +  do_execsql_test func4-2.[incr i] {
          266  +    SELECT todouble(9223372036854775807);
          267  +  } {9.22337203685478e+18}
          268  +  do_execsql_test func4-2.[incr i] {
          269  +    SELECT todouble(9223372036854775807 + 1);
          270  +  } {9.22337203685478e+18}
          271  +  do_execsql_test func4-2.[incr i] {
          272  +    SELECT todouble(1.79769313486232e308 - 1);
          273  +  } {Inf}
          274  +  do_execsql_test func4-2.[incr i] {
          275  +    SELECT todouble(1.79769313486232e308);
          276  +  } {Inf}
          277  +  do_execsql_test func4-2.[incr i] {
          278  +    SELECT todouble(1.79769313486232e308 + 1);
          279  +  } {Inf}
          280  +  do_execsql_test func4-2.[incr i] {
          281  +    SELECT todouble(4503599627370496 - 1);
          282  +  } {4503599627370500.0}
          283  +  do_execsql_test func4-2.[incr i] {
          284  +    SELECT todouble(4503599627370496);
          285  +  } {4503599627370500.0}
          286  +  do_execsql_test func4-2.[incr i] {
          287  +    SELECT todouble(4503599627370496 + 1);
          288  +  } {4503599627370500.0}
          289  +  do_execsql_test func4-2.[incr i] {
          290  +    SELECT todouble(9007199254740992 - 1);
          291  +  } {9007199254740990.0}
          292  +  do_execsql_test func4-2.[incr i] {
          293  +    SELECT todouble(9007199254740992);
          294  +  } {9007199254740990.0}
          295  +  do_execsql_test func4-2.[incr i] {
          296  +    SELECT todouble(9007199254740992 + 1);
          297  +  } {9007199254740990.0}
          298  +  do_execsql_test func4-2.[incr i] {
          299  +    SELECT todouble(9223372036854775808 - 1);
          300  +  } {9.22337203685478e+18}
          301  +  do_execsql_test func4-2.[incr i] {
          302  +    SELECT todouble(9223372036854775808);
          303  +  } {9.22337203685478e+18}
          304  +  do_execsql_test func4-2.[incr i] {
          305  +    SELECT todouble(9223372036854775808 + 1);
          306  +  } {9.22337203685478e+18}
          307  +  do_execsql_test func4-2.[incr i] {
          308  +    SELECT todouble(18446744073709551616 - 1);
          309  +  } {1.84467440737096e+19}
          310  +  do_execsql_test func4-2.[incr i] {
          311  +    SELECT todouble(18446744073709551616);
          312  +  } {1.84467440737096e+19}
          313  +  do_execsql_test func4-2.[incr i] {
          314  +    SELECT todouble(18446744073709551616 + 1);
          315  +  } {1.84467440737096e+19}
          316  +}
          317  +
          318  +finish_test