test/func7.test

# 2020-12-07
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
#
# Test cases for SQL functions based off the standard math library
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable !mathlib {
  finish_test
  return
}

do_execsql_test func7-100 {
  SELECT ceil(99.9), ceiling(-99.01), floor(17), floor(-17.99);
} {100.0 -99.0 17 -18.0}
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(2,'256');
} {1.61 2.0 2.0 8.0}
do_execsql_test func7-210 {
  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-181 {
  SELECT format('%.30f', log10(100.0) );
} {2.000000000000000000000000000000}
do_execsql_test func7-pg-182 {
  SELECT format('%.30f', ln(exp(2.0)) );
} {2.000000000000000000000000000000}
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)), quote(log(0,100));
} {16.0 2.0 NULL NULL}
do_execsql_test func7-mysql-240 {
   SELECT log2(65536), quote(log2(-100)), quote(log2(0));
} {16.0 NULL 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