diff options
| -rw-r--r-- | manifest | 14 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | src/func.c | 190 | ||||
| -rw-r--r-- | test/func7.test | 214 |
4 files changed, 386 insertions, 34 deletions
@@ -1,5 +1,5 @@ -C Begin\sadding\snew\sSQL\sfunctions\sthat\sdepend\son\s-lm:\s\sceil(),\sceiling(),\nfloor(),\sln(),\slog(),\sand\slog10()\sso\sfar.\s\sMore\sto\sfollow. -D 2020-12-07T17:15:32.150 +C Many\smore\smath\sfunctions.\s\sSemantics\sfollows\sPG\swherever\spossible. +D 2020-12-07T21:13:06.475 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -495,7 +495,7 @@ F src/delete.c 927cf8f900583e79aca8f1a321979e0a8f053babd9a690b44b38f79de2cc09fe F src/expr.c 0d196ed5a2ebf96be7e8df88add4fabfad0dce16c0fed81a4b8f6a26e259797f F src/fault.c 460f3e55994363812d9d60844b2a6de88826e007 F src/fkey.c 83372403298e6a7dd989a47aaacdbaa5b4307b5199dbd56e07d4896066b3de72 -F src/func.c d3113a23625daeb54331752421442af772abd851123550459dec96fedbe068e7 +F src/func.c 7c3a41213179cce249d46283fc2237bc3424a041343866dbdb31914fed0edddc F src/global.c ed55af196a9b66e198aaeda3f5454c3aa7d7d050c6c938181fd044b70d180a81 F src/hash.c 8d7dda241d0ebdafb6ffdeda3149a412d7df75102cecfc1021c98d6219823b19 F src/hash.h 9d56a9079d523b648774c1784b74b89bd93fac7b365210157482e4319a468f38 @@ -1026,7 +1026,7 @@ F test/func3.test 2bb0f31ab7baaed690b962a88544d7be6b34fa389364bc36a44e441ed3e3f1 F test/func4.test 2285fb5792d593fef442358763f0fd9de806eda47dbc7a5934df57ffdc484c31 F test/func5.test 863e6d1bd0013d09c17236f8a13ea34008dd857d87d85a13a673960e4c25d82a F test/func6.test 90e42b64c4f9fb6f04f44cb8a1da586c8542502e926b19c76504fe74ff2a9b7c -F test/func7.test 7414f1ffb7aba86b6bcfdf0c3739a4e4717026ff583acd8b43ab21189300bcdc +F test/func7.test bb05a77daedf0e3f8764f323a49bc3b8d98f280a0bc6a370387117f4596bde05 F test/fuzz-oss1.test e58330d01cbbd8215ee636b17a03fe220b37dbfa F test/fuzz.test 96083052bf5765e4518c1ba686ce2bab785670d1 F test/fuzz2.test 76dc35b32b6d6f965259508508abce75a6c4d7e1 @@ -1888,7 +1888,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 1db7c751912beb57a697ac8e85b9c29e30da7b6c89207e9828bf08e56c58242f -R 6662031cc76e6cb57f52d5a4ee0696ea +P 4db5f2f7875f6df78630a7816fc018141a6eee2e295b44fc7627eb66d07881ea +R 5f3694d6742f321a9a8686c5ca8182de U drh -Z 99632da1aa8e30b2d9d751e33b863567 +Z 1fdfc343d2601875f5f91cade0f6a5bf diff --git a/manifest.uuid b/manifest.uuid index 7ffa11f57..acf82a7c3 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -4db5f2f7875f6df78630a7816fc018141a6eee2e295b44fc7627eb66d07881ea
\ No newline at end of file +6b93627b5d9819abf179a3e4a82e7afe17cbcafdabbd5f058de9ed114c9d477f
\ No newline at end of file diff --git a/src/func.c b/src/func.c index 09e44345d..7bfd5bd6a 100644 --- a/src/func.c +++ b/src/func.c @@ -1904,6 +1904,18 @@ int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char *aWc){ return 1; } +/* Mathematical Constants */ +#ifndef M_PI +# define M_PI 3.141592653589793238462643383279502884 +#endif +#ifndef M_LN10 +# define M_LN10 2.302585092994045684017991454684364208 +#endif +#ifndef M_LN2 +# define M_LN2 0.693147180559945309417232121458176568 +#endif + + /* Extra math functions that require linking with -lm */ #ifdef SQLITE_ENABLE_MATH_FUNCTIONS @@ -1923,17 +1935,19 @@ static void ceilingFunc( sqlite3_value **argv ){ assert( argc==1 ); - switch( sqlite3_value_type(argv[0]) ){ - case SQLITE_INTEGER: + switch( sqlite3_value_numeric_type(argv[0]) ){ + case SQLITE_INTEGER: { sqlite3_result_int64(context, sqlite3_value_int64(argv[0])); break; - case SQLITE_NULL: - break; - default: { + } + case SQLITE_FLOAT: { double (*x)(double) = (double(*)(double))sqlite3_user_data(context); sqlite3_result_double(context, x(sqlite3_value_double(argv[0]))); break; } + default: { + break; + } } } @@ -1941,39 +1955,143 @@ static void ceilingFunc( ** Implementation of SQL functions: ** ** ln(X) - natural logarithm -** log(X) - log base 10 -** log10(X) - log base 10 -** log(X,Y) - log base Y +** log(X) - log X base 10 +** log10(X) - log X base 10 +** log(B,X) - log X base B */ static void logFunc( sqlite3_context *context, int argc, sqlite3_value **argv ){ - double x, y, ans; + double x, b, ans; assert( argc==1 || argc==2 ); - if( sqlite3_value_type(argv[0])==SQLITE_NULL - || (x = sqlite3_value_double(argv[0]))<0.0 - ){ - return; /* Return NULL for a domain error */ + switch( sqlite3_value_numeric_type(argv[0]) ){ + case SQLITE_INTEGER: + case SQLITE_FLOAT: + x = sqlite3_value_double(argv[0]); + if( x<0.0 ) return; + break; + default: + return; } - ans = log(x); if( argc==2 ){ - if( sqlite3_value_type(argv[1])==SQLITE_NULL - || (y = sqlite3_value_double(argv[1]))<0.0 - ){ - return; /* Return NULL for a domain error */ + switch( sqlite3_value_numeric_type(argv[0]) ){ + case SQLITE_INTEGER: + case SQLITE_FLOAT: + b = x; + x = sqlite3_value_double(argv[1]); + if( x<0.0 ) return; + break; + default: + return; + } + ans = log(x)/log(b); + }else{ + ans = log(x); + switch( SQLITE_PTR_TO_INT(sqlite3_user_data(context)) ){ + case 1: + /* Convert from natural logarithm to log base 10 */ + ans *= 1.0/M_LN10; + break; + case 2: + /* Convert from natural logarithm to log base 2 */ + ans *= 1.0/M_LN2; + break; + default: + break; } - ans /= log(y); - }else if( sqlite3_user_data(context)!=0 ){ - /* Convert from natural logarithm to log base 10 */ - ans *= 0.43429448190325178672; } sqlite3_result_double(context, ans); } + +/* +** Functions to converts degrees to radians and radians to degrees. +*/ +static double degToRad(double x){ return x*(M_PI/180.0); } +static double radToDeg(double x){ return x*(180.0/M_PI); } + +/* +** Implementation of 1-argument SQL math functions: +** +** exp(X) - Compute e to the X-th power +*/ +static void math1Func( + sqlite3_context *context, + int argc, + sqlite3_value **argv +){ + assert( argc==2 ); + int type0; + double v0, ans; + double (*x)(double); + type0 = sqlite3_value_numeric_type(argv[0]); + if( type0!=SQLITE_INTEGER && type0!=SQLITE_FLOAT ) return; + v0 = sqlite3_value_double(argv[0]); + x = (double(*)(double))sqlite3_user_data(context); + ans = x(v0); + sqlite3_result_double(context, ans); +} + +/* +** Implementation of 2-argument SQL math functions: +** +** power(X,Y) - Compute X to the Y-th power +*/ +static void math2Func( + sqlite3_context *context, + int argc, + sqlite3_value **argv +){ + assert( argc==2 ); + int type0, type1; + double v0, v1, ans; + double (*x)(double,double); + type0 = sqlite3_value_numeric_type(argv[0]); + if( type0!=SQLITE_INTEGER && type0!=SQLITE_FLOAT ) return; + type1 = sqlite3_value_numeric_type(argv[1]); + if( type1!=SQLITE_INTEGER && type1!=SQLITE_FLOAT ) return; + v0 = sqlite3_value_double(argv[0]); + v1 = sqlite3_value_double(argv[1]); + x = (double(*)(double,double))sqlite3_user_data(context); + ans = x(v0, v1); + sqlite3_result_double(context, ans); +} + +/* +** Implementation of 2-argument SQL math functions: +** +** power(X,Y) - Compute X to the Y-th power +*/ +static void piFunc( + sqlite3_context *context, + int argc, + sqlite3_value **argv +){ + assert( argc==0 ); + sqlite3_result_double(context, M_PI); +} + #endif /* SQLITE_ENABLE_MATH_FUNCTIONS */ /* +** Implementation of sign(X) function. +*/ +static void signFunc( + sqlite3_context *context, + int argc, + sqlite3_value **argv +){ + assert( argc==1 ); + int type0; + double x; + type0 = sqlite3_value_numeric_type(argv[0]); + if( type0!=SQLITE_INTEGER && type0!=SQLITE_FLOAT ) return; + x = sqlite3_value_double(argv[0]); + sqlite3_result_int(context, x<0.0 ? -1 : x>0.0 ? +1 : 0); +} + +/* ** All of the FuncDef structures in the aBuiltinFunc[] array above ** to the global function hash table. This occurs at start-time (as ** a consequence of calling sqlite3_initialize()). @@ -2091,17 +2209,41 @@ void sqlite3RegisterBuiltinFunctions(void){ #endif FUNCTION(coalesce, 1, 0, 0, 0 ), FUNCTION(coalesce, 0, 0, 0, 0 ), - INLINE_FUNC(coalesce, -1, INLINEFUNC_coalesce, 0 ), - INLINE_FUNC(iif, 3, INLINEFUNC_iif, 0 ), #ifdef SQLITE_ENABLE_MATH_FUNCTIONS MFUNCTION(ceil, 1, ceil, ceilingFunc ), MFUNCTION(ceiling, 1, ceil, ceilingFunc ), MFUNCTION(floor, 1, floor, ceilingFunc ), + MFUNCTION(trunc, 1, trunc, ceilingFunc ), FUNCTION(ln, 1, 0, 0, logFunc ), FUNCTION(log, 1, 1, 0, logFunc ), FUNCTION(log10, 1, 1, 0, logFunc ), + FUNCTION(log2, 1, 2, 0, logFunc ), FUNCTION(log, 2, 0, 0, logFunc ), + MFUNCTION(exp, 1, exp, math1Func ), + MFUNCTION(pow, 2, pow, math2Func ), + MFUNCTION(power, 2, pow, math2Func ), + MFUNCTION(mod, 2, fmod, math2Func ), + MFUNCTION(acos, 1, acos, math1Func ), + MFUNCTION(asin, 1, asin, math1Func ), + MFUNCTION(atan, 1, atan, math1Func ), + MFUNCTION(atan2, 2, atan2, math2Func ), + MFUNCTION(cos, 1, cos, math1Func ), + MFUNCTION(sin, 1, sin, math1Func ), + MFUNCTION(tan, 1, tan, math1Func ), + MFUNCTION(cosh, 1, cosh, math1Func ), + MFUNCTION(sinh, 1, sinh, math1Func ), + MFUNCTION(tanh, 1, tanh, math1Func ), + MFUNCTION(acosh, 1, acosh, math1Func ), + MFUNCTION(asinh, 1, asinh, math1Func ), + MFUNCTION(atanh, 1, atanh, math1Func ), + MFUNCTION(sqrt, 1, sqrt, math1Func ), + MFUNCTION(radians, 1, degToRad, math1Func ), + MFUNCTION(degrees, 1, radToDeg, math1Func ), + FUNCTION(pi, 0, 0, 0, piFunc ), #endif /* SQLITE_ENABLE_MATH_FUNCTIONS */ + FUNCTION(sign, 1, 0, 0, signFunc ), + INLINE_FUNC(coalesce, -1, INLINEFUNC_coalesce, 0 ), + INLINE_FUNC(iif, 3, INLINEFUNC_iif, 0 ), }; #ifndef SQLITE_OMIT_ALTERTABLE sqlite3AlterFunctions(); diff --git a/test/func7.test b/test/func7.test index c7fce79dd..536f7eb41 100644 --- a/test/func7.test +++ b/test/func7.test @@ -25,11 +25,221 @@ do_execsql_test func7-110 { SELECT quote(ceil(NULL)), ceil('-99.99'); } {NULL -99.0} do_execsql_test func7-200 { - SELECT round(ln(5),2), log(100.0), log(100), log('256',2); + SELECT round(ln(5),2), log(100.0), log(100), log(2,'256'); } {1.61 2.0 2.0 8.0} do_execsql_test func7-210 { - SELECT ln(-5), log(100.0,-5); + SELECT ln(-5), log(-5,100.0); } {{} {}} +# Test cases derived from PostgreSQL documentation +# +do_execsql_test func7-pg-100 { + SELECT abs(-17.4) +} {17.4} +do_execsql_test func7-pg-110 { + SELECT ceil(42.2) +} {43.0} +do_execsql_test func7-pg-120 { + SELECT ceil(-42.2) +} {-42.0} +do_execsql_test func7-pg-130 { + SELECT round(exp(1.0),7) +} {2.7182818} +do_execsql_test func7-pg-140 { + SELECT floor(42.8) +} {42.0} +do_execsql_test func7-pg-150 { + SELECT floor(-42.8) +} {-43.0} +do_execsql_test func7-pg-160 { + SELECT round(ln(2.0),7) +} {0.6931472} +do_execsql_test func7-pg-170 { + SELECT log(100.0) +} {2.0} +do_execsql_test func7-pg-180 { + SELECT log10(1000.0) +} {3.0} +do_execsql_test func7-pg-190 { + SELECT log(2.0, 64.0) +} {6.0} +do_execsql_test func7-pg-200 { + SELECT mod(9,4); +} {1.0} +do_execsql_test func7-pg-210 { + SELECT round(pi(),7); +} {3.1415927} +do_execsql_test func7-pg-220 { + SELECT power(9,3); +} {729.0} +do_execsql_test func7-pg-230 { + SELECT round(radians(45.0),7); +} {0.7853982} +do_execsql_test func7-pg-240 { + SELECT round(42.4); +} {42.0} +do_execsql_test func7-pg-250 { + SELECT round(42.4382,2); +} {42.44} +do_execsql_test func7-pg-260 { + SELECT sign(-8.4); +} {-1} +do_execsql_test func7-pg-270 { + SELECT round( sqrt(2), 7); +} {1.4142136} +do_execsql_test func7-pg-280 { + SELECT trunc(42.8), trunc(-42.8); +} {42.0 -42.0} +do_execsql_test func7-pg-300 { + SELECT acos(1); +} {0.0} +do_execsql_test func7-pg-301 { + SELECT degrees(acos(0.5)); +} {60.0} +do_execsql_test func7-pg-310 { + SELECT round( asin(1), 7); +} {1.5707963} +do_execsql_test func7-pg-311 { + SELECT degrees( asin(0.5) ); +} {30.0} +do_execsql_test func7-pg-320 { + SELECT round( atan(1), 7); +} {0.7853982} +do_execsql_test func7-pg-321 { + SELECT degrees( atan(1) ); +} {45.0} +do_execsql_test func7-pg-330 { + SELECT round( atan2(1,0), 7); +} {1.5707963} +do_execsql_test func7-pg-331 { + SELECT degrees( atan2(1,0) ); +} {90.0} +do_execsql_test func7-pg-400 { + SELECT cos(0); +} {1.0} +do_execsql_test func7-pg-401 { + SELECT cos( radians(60.0) ); +} {0.5} +do_execsql_test func7-pg-400 { + SELECT cos(0); +} {1.0} +do_execsql_test func7-pg-410 { + SELECT round( sin(1), 7); +} {0.841471} +do_execsql_test func7-pg-411 { + SELECT sin( radians(30) ); +} {0.5} +do_execsql_test func7-pg-420 { + SELECT round( tan(1), 7); +} {1.5574077} +do_execsql_test func7-pg-421 { + SELECT tan( radians(45) ); +} {1.0} +do_execsql_test func7-pg-500 { + SELECT round( sinh(1), 7); +} {1.1752012} +do_execsql_test func7-pg-510 { + SELECT round( cosh(0), 7); +} {1.0} +do_execsql_test func7-pg-520 { + SELECT round( tanh(1), 7); +} {0.7615942} +do_execsql_test func7-pg-530 { + SELECT round( asinh(1), 7); +} {0.8813736} +do_execsql_test func7-pg-540 { + SELECT round( acosh(1), 7); +} {0.0} +do_execsql_test func7-pg-550 { + SELECT round( atanh(0.5), 7); +} {0.5493061} + +# Test cases derived from MySQL documentation +# +do_execsql_test func7-mysql-100 { + SELECT acos(1); +} {0.0} +do_execsql_test func7-mysql-110 { + SELECT acos(1.0001); +} {{}} +do_execsql_test func7-mysql-120 { + SELECT round( acos(0.0), 7); +} {1.5707963} +do_execsql_test func7-mysql-130 { + SELECT round( asin(0.2), 7); +} {0.2013579} +do_execsql_test func7-mysql-140 { + SELECT asin('foo'); +} {{}} ;# Note: MySQL returns 0 here, not NULL. + # SQLite deliberately returns NULL. + # SQLServer and Oracle throw an error. +do_execsql_test func7-mysql-150 { + SELECT round( atan(2), 7), round( atan(-2), 7); +} {1.1071487 -1.1071487} +do_execsql_test func7-mysql-160 { + SELECT round( atan2(-2,2), 7), round( atan2(pi(),0), 7); +} {-0.7853982 1.5707963} +do_execsql_test func7-mysql-170 { + SELECT ceiling(1.23), ceiling(-1.23); +} {2.0 -1.0} +do_execsql_test func7-mysql-180 { + SELECT cos(pi()); +} {-1.0} +do_execsql_test func7-mysql-190 { + SELECT degrees(pi()), degrees(pi()/2); +} {180.0 90.0} +do_execsql_test func7-mysql-190 { + SELECT round( exp(2), 7), round( exp(-2), 7), exp(0); +} {7.3890561 0.1353353 1.0} +do_execsql_test func7-mysql-200 { + SELECT floor(1.23), floor(-1.23); +} {1.0 -2.0} +do_execsql_test func7-mysql-210 { + SELECT round(ln(2),7), quote(ln(-2)); +} {0.6931472 NULL} +#do_execsql_test func7-mysql-220 { +# SELECT round(log(2),7), log(-2); +#} {0.6931472 NULL} +# log() means natural logarithm in MySQL +do_execsql_test func7-mysql-230 { + SELECT log(2,65536), log(10,100), quote(log(1,100)); +} {16.0 2.0 Inf} +do_execsql_test func7-mysql-240 { + SELECT log2(65536), quote(log2(-100)); +} {16.0 NULL} +do_execsql_test func7-mysql-250 { + SELECT round(log10(2),7), log10(100), quote(log10(-100)); +} {0.30103 2.0 NULL} +do_execsql_test func7-mysql-260 { + SELECT mod(234,10), 253%7, mod(29,9), 29%9; +} {4.0 1 2.0 2} +do_execsql_test func7-mysql-270 { + SELECT mod(34.5,3); +} {1.5} +do_execsql_test func7-mysql-280 { + SELECT pow(2,2), pow(2,-2); +} {4.0 0.25} +do_execsql_test func7-mysql-281 { + SELECT power(2,2), power(2,-2); +} {4.0 0.25} +do_execsql_test func7-mysql-290 { + SELECT round(radians(90),7); +} {1.5707963} +do_execsql_test func7-mysql-300 { + SELECT sign(-32), sign(0), sign(234); +} {-1 0 1} +do_execsql_test func7-mysql-310 { + SELECT sin(pi()) BETWEEN -1.0e-15 AND 1.0e-15; +} {1} +do_execsql_test func7-mysql-320 { + SELECT sqrt(4), round(sqrt(20),7), quote(sqrt(-16)); +} {2.0 4.472136 NULL} +do_execsql_test func7-mysql-330 { + SELECT tan(pi()) BETWEEN -1.0e-15 AND 1.0e-15; +} {1} +do_execsql_test func7-mysql-331 { + SELECT round(tan(pi()+1),7); +} {1.5574077} + finish_test |