| 1 |
# 2020-12-07 |
| 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 |
# |
| 12 |
# Test cases for SQL functions based off the standard math library |
| 13 |
# |
| 14 |
set testdir [file dirname $argv0] |
| 15 |
source $testdir/tester.tcl |
| 16 |
ifcapable !mathlib { |
| 17 |
finish_test |
| 18 |
return |
| 19 |
} |
| 20 |
|
| 21 |
do_execsql_test func7-100 { |
| 22 |
SELECT ceil(99.9), ceiling(-99.01), floor(17), floor(-17.99); |
| 23 |
} {100.0 -99.0 17 -18.0} |
| 24 |
do_execsql_test func7-110 { |
| 25 |
SELECT quote(ceil(NULL)), ceil('-99.99'); |
| 26 |
} {NULL -99.0} |
| 27 |
do_execsql_test func7-200 { |
| 28 |
SELECT round(ln(5),2), log(100.0), log(100), log(2,'256'); |
| 29 |
} {1.61 2.0 2.0 8.0} |
| 30 |
do_execsql_test func7-210 { |
| 31 |
SELECT ln(-5), log(-5,100.0); |
| 32 |
} {{} {}} |
| 33 |
|
| 34 |
# Test cases derived from PostgreSQL documentation |
| 35 |
# |
| 36 |
do_execsql_test func7-pg-100 { |
| 37 |
SELECT abs(-17.4) |
| 38 |
} {17.4} |
| 39 |
do_execsql_test func7-pg-110 { |
| 40 |
SELECT ceil(42.2) |
| 41 |
} {43.0} |
| 42 |
do_execsql_test func7-pg-120 { |
| 43 |
SELECT ceil(-42.2) |
| 44 |
} {-42.0} |
| 45 |
do_execsql_test func7-pg-130 { |
| 46 |
SELECT round(exp(1.0),7) |
| 47 |
} {2.7182818} |
| 48 |
do_execsql_test func7-pg-140 { |
| 49 |
SELECT floor(42.8) |
| 50 |
} {42.0} |
| 51 |
do_execsql_test func7-pg-150 { |
| 52 |
SELECT floor(-42.8) |
| 53 |
} {-43.0} |
| 54 |
do_execsql_test func7-pg-160 { |
| 55 |
SELECT round(ln(2.0),7) |
| 56 |
} {0.6931472} |
| 57 |
do_execsql_test func7-pg-170 { |
| 58 |
SELECT log(100.0) |
| 59 |
} {2.0} |
| 60 |
do_execsql_test func7-pg-180 { |
| 61 |
SELECT log10(1000.0) |
| 62 |
} {3.0} |
| 63 |
do_execsql_test func7-pg-181 { |
| 64 |
SELECT format('%.30f', log10(100.0) ); |
| 65 |
} {2.000000000000000000000000000000} |
| 66 |
do_execsql_test func7-pg-182 { |
| 67 |
SELECT format('%.30f', ln(exp(2.0)) ); |
| 68 |
} {2.000000000000000000000000000000} |
| 69 |
do_execsql_test func7-pg-190 { |
| 70 |
SELECT log(2.0, 64.0) |
| 71 |
} {6.0} |
| 72 |
do_execsql_test func7-pg-200 { |
| 73 |
SELECT mod(9,4); |
| 74 |
} {1.0} |
| 75 |
do_execsql_test func7-pg-210 { |
| 76 |
SELECT round(pi(),7); |
| 77 |
} {3.1415927} |
| 78 |
do_execsql_test func7-pg-220 { |
| 79 |
SELECT power(9,3); |
| 80 |
} {729.0} |
| 81 |
do_execsql_test func7-pg-230 { |
| 82 |
SELECT round(radians(45.0),7); |
| 83 |
} {0.7853982} |
| 84 |
do_execsql_test func7-pg-240 { |
| 85 |
SELECT round(42.4); |
| 86 |
} {42.0} |
| 87 |
do_execsql_test func7-pg-250 { |
| 88 |
SELECT round(42.4382,2); |
| 89 |
} {42.44} |
| 90 |
do_execsql_test func7-pg-260 { |
| 91 |
SELECT sign(-8.4); |
| 92 |
} {-1} |
| 93 |
do_execsql_test func7-pg-270 { |
| 94 |
SELECT round( sqrt(2), 7); |
| 95 |
} {1.4142136} |
| 96 |
do_execsql_test func7-pg-280 { |
| 97 |
SELECT trunc(42.8), trunc(-42.8); |
| 98 |
} {42.0 -42.0} |
| 99 |
do_execsql_test func7-pg-300 { |
| 100 |
SELECT acos(1); |
| 101 |
} {0.0} |
| 102 |
do_execsql_test func7-pg-301 { |
| 103 |
SELECT format('%f',degrees(acos(0.5))); |
| 104 |
} {60.0} |
| 105 |
do_execsql_test func7-pg-310 { |
| 106 |
SELECT round( asin(1), 7); |
| 107 |
} {1.5707963} |
| 108 |
do_execsql_test func7-pg-311 { |
| 109 |
SELECT format('%f',degrees( asin(0.5) )); |
| 110 |
} {30.0} |
| 111 |
do_execsql_test func7-pg-320 { |
| 112 |
SELECT round( atan(1), 7); |
| 113 |
} {0.7853982} |
| 114 |
do_execsql_test func7-pg-321 { |
| 115 |
SELECT degrees( atan(1) ); |
| 116 |
} {45.0} |
| 117 |
do_execsql_test func7-pg-330 { |
| 118 |
SELECT round( atan2(1,0), 7); |
| 119 |
} {1.5707963} |
| 120 |
do_execsql_test func7-pg-331 { |
| 121 |
SELECT degrees( atan2(1,0) ); |
| 122 |
} {90.0} |
| 123 |
do_execsql_test func7-pg-400 { |
| 124 |
SELECT cos(0); |
| 125 |
} {1.0} |
| 126 |
do_execsql_test func7-pg-401 { |
| 127 |
SELECT cos( radians(60.0) ); |
| 128 |
} {0.5} |
| 129 |
do_execsql_test func7-pg-400 { |
| 130 |
SELECT cos(0); |
| 131 |
} {1.0} |
| 132 |
do_execsql_test func7-pg-410 { |
| 133 |
SELECT round( sin(1), 7); |
| 134 |
} {0.841471} |
| 135 |
do_execsql_test func7-pg-411 { |
| 136 |
SELECT sin( radians(30) ); |
| 137 |
} {0.5} |
| 138 |
do_execsql_test func7-pg-420 { |
| 139 |
SELECT round( tan(1), 7); |
| 140 |
} {1.5574077} |
| 141 |
do_execsql_test func7-pg-421 { |
| 142 |
SELECT round(tan( radians(45) ),10); |
| 143 |
} {1.0} |
| 144 |
do_execsql_test func7-pg-500 { |
| 145 |
SELECT round( sinh(1), 7); |
| 146 |
} {1.1752012} |
| 147 |
do_execsql_test func7-pg-510 { |
| 148 |
SELECT round( cosh(0), 7); |
| 149 |
} {1.0} |
| 150 |
do_execsql_test func7-pg-520 { |
| 151 |
SELECT round( tanh(1), 7); |
| 152 |
} {0.7615942} |
| 153 |
do_execsql_test func7-pg-530 { |
| 154 |
SELECT round( asinh(1), 7); |
| 155 |
} {0.8813736} |
| 156 |
do_execsql_test func7-pg-540 { |
| 157 |
SELECT round( acosh(1), 7); |
| 158 |
} {0.0} |
| 159 |
do_execsql_test func7-pg-550 { |
| 160 |
SELECT round( atanh(0.5), 7); |
| 161 |
} {0.5493061} |
| 162 |
|
| 163 |
# Test cases derived from MySQL documentation |
| 164 |
# |
| 165 |
do_execsql_test func7-mysql-100 { |
| 166 |
SELECT acos(1); |
| 167 |
} {0.0} |
| 168 |
do_execsql_test func7-mysql-110 { |
| 169 |
SELECT acos(1.0001); |
| 170 |
} {{}} |
| 171 |
do_execsql_test func7-mysql-120 { |
| 172 |
SELECT round( acos(0.0), 7); |
| 173 |
} {1.5707963} |
| 174 |
do_execsql_test func7-mysql-130 { |
| 175 |
SELECT round( asin(0.2), 7); |
| 176 |
} {0.2013579} |
| 177 |
do_execsql_test func7-mysql-140 { |
| 178 |
SELECT asin('foo'); |
| 179 |
} {{}} ;# Note: MySQL returns 0 here, not NULL. |
| 180 |
# SQLite deliberately returns NULL. |
| 181 |
# SQLServer and Oracle throw an error. |
| 182 |
do_execsql_test func7-mysql-150 { |
| 183 |
SELECT round( atan(2), 7), round( atan(-2), 7); |
| 184 |
} {1.1071487 -1.1071487} |
| 185 |
do_execsql_test func7-mysql-160 { |
| 186 |
SELECT round( atan2(-2,2), 7), round( atan2(pi(),0), 7); |
| 187 |
} {-0.7853982 1.5707963} |
| 188 |
do_execsql_test func7-mysql-170 { |
| 189 |
SELECT ceiling(1.23), ceiling(-1.23); |
| 190 |
} {2.0 -1.0} |
| 191 |
do_execsql_test func7-mysql-180 { |
| 192 |
SELECT cos(pi()); |
| 193 |
} {-1.0} |
| 194 |
do_execsql_test func7-mysql-190 { |
| 195 |
SELECT degrees(pi()), degrees(pi()/2); |
| 196 |
} {180.0 90.0} |
| 197 |
do_execsql_test func7-mysql-190 { |
| 198 |
SELECT round( exp(2), 7), round( exp(-2), 7), exp(0); |
| 199 |
} {7.3890561 0.1353353 1.0} |
| 200 |
do_execsql_test func7-mysql-200 { |
| 201 |
SELECT floor(1.23), floor(-1.23); |
| 202 |
} {1.0 -2.0} |
| 203 |
do_execsql_test func7-mysql-210 { |
| 204 |
SELECT round(ln(2),7), quote(ln(-2)); |
| 205 |
} {0.6931472 NULL} |
| 206 |
#do_execsql_test func7-mysql-220 { |
| 207 |
# SELECT round(log(2),7), log(-2); |
| 208 |
#} {0.6931472 NULL} |
| 209 |
# log() means natural logarithm in MySQL |
| 210 |
do_execsql_test func7-mysql-230 { |
| 211 |
SELECT log(2,65536), log(10,100), quote(log(1,100)), quote(log(0,100)); |
| 212 |
} {16.0 2.0 NULL NULL} |
| 213 |
do_execsql_test func7-mysql-240 { |
| 214 |
SELECT log2(65536), quote(log2(-100)), quote(log2(0)); |
| 215 |
} {16.0 NULL NULL} |
| 216 |
do_execsql_test func7-mysql-250 { |
| 217 |
SELECT round(log10(2),7), log10(100), quote(log10(-100)); |
| 218 |
} {0.30103 2.0 NULL} |
| 219 |
do_execsql_test func7-mysql-260 { |
| 220 |
SELECT mod(234,10), 253%7, mod(29,9), 29%9; |
| 221 |
} {4.0 1 2.0 2} |
| 222 |
do_execsql_test func7-mysql-270 { |
| 223 |
SELECT mod(34.5,3); |
| 224 |
} {1.5} |
| 225 |
do_execsql_test func7-mysql-280 { |
| 226 |
SELECT pow(2,2), pow(2,-2); |
| 227 |
} {4.0 0.25} |
| 228 |
do_execsql_test func7-mysql-281 { |
| 229 |
SELECT power(2,2), power(2,-2); |
| 230 |
} {4.0 0.25} |
| 231 |
do_execsql_test func7-mysql-290 { |
| 232 |
SELECT round(radians(90),7); |
| 233 |
} {1.5707963} |
| 234 |
do_execsql_test func7-mysql-300 { |
| 235 |
SELECT sign(-32), sign(0), sign(234); |
| 236 |
} {-1 0 1} |
| 237 |
do_execsql_test func7-mysql-310 { |
| 238 |
SELECT sin(pi()) BETWEEN -1.0e-15 AND 1.0e-15; |
| 239 |
} {1} |
| 240 |
do_execsql_test func7-mysql-320 { |
| 241 |
SELECT sqrt(4), round(sqrt(20),7), quote(sqrt(-16)); |
| 242 |
} {2.0 4.472136 NULL} |
| 243 |
do_execsql_test func7-mysql-330 { |
| 244 |
SELECT tan(pi()) BETWEEN -1.0e-15 AND 1.0e-15; |
| 245 |
} {1} |
| 246 |
do_execsql_test func7-mysql-331 { |
| 247 |
SELECT round(tan(pi()+1),7); |
| 248 |
} {1.5574077} |
| 249 |
|
| 250 |
|
| 251 |
finish_test |