1-- 2-- FLOAT8 3-- 4 5CREATE TABLE FLOAT8_TBL(f1 float8); 6 7INSERT INTO FLOAT8_TBL(f1) VALUES (' 0.0 '); 8INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30 '); 9INSERT INTO FLOAT8_TBL(f1) VALUES (' -34.84'); 10INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200'); 11INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200'); 12 13-- test for underflow and overflow handling 14SELECT '10e400'::float8; 15SELECT '-10e400'::float8; 16SELECT '10e-400'::float8; 17SELECT '-10e-400'::float8; 18 19-- test smallest normalized input 20SELECT float8send('2.2250738585072014E-308'::float8); 21 22-- bad input 23INSERT INTO FLOAT8_TBL(f1) VALUES (''); 24INSERT INTO FLOAT8_TBL(f1) VALUES (' '); 25INSERT INTO FLOAT8_TBL(f1) VALUES ('xyz'); 26INSERT INTO FLOAT8_TBL(f1) VALUES ('5.0.0'); 27INSERT INTO FLOAT8_TBL(f1) VALUES ('5 . 0'); 28INSERT INTO FLOAT8_TBL(f1) VALUES ('5. 0'); 29INSERT INTO FLOAT8_TBL(f1) VALUES (' - 3'); 30INSERT INTO FLOAT8_TBL(f1) VALUES ('123 5'); 31 32-- special inputs 33SELECT 'NaN'::float8; 34SELECT 'nan'::float8; 35SELECT ' NAN '::float8; 36SELECT 'infinity'::float8; 37SELECT ' -INFINiTY '::float8; 38-- bad special inputs 39SELECT 'N A N'::float8; 40SELECT 'NaN x'::float8; 41SELECT ' INFINITY x'::float8; 42 43SELECT 'Infinity'::float8 + 100.0; 44SELECT 'Infinity'::float8 / 'Infinity'::float8; 45SELECT 'nan'::float8 / 'nan'::float8; 46SELECT 'nan'::numeric::float8; 47 48SELECT '' AS five, * FROM FLOAT8_TBL; 49 50SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3'; 51 52SELECT '' AS one, f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3'; 53 54SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE '1004.3' > f.f1; 55 56SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE f.f1 < '1004.3'; 57 58SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1; 59 60SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <= '1004.3'; 61 62SELECT '' AS three, f.f1, f.f1 * '-10' AS x 63 FROM FLOAT8_TBL f 64 WHERE f.f1 > '0.0'; 65 66SELECT '' AS three, f.f1, f.f1 + '-10' AS x 67 FROM FLOAT8_TBL f 68 WHERE f.f1 > '0.0'; 69 70SELECT '' AS three, f.f1, f.f1 / '-10' AS x 71 FROM FLOAT8_TBL f 72 WHERE f.f1 > '0.0'; 73 74SELECT '' AS three, f.f1, f.f1 - '-10' AS x 75 FROM FLOAT8_TBL f 76 WHERE f.f1 > '0.0'; 77 78SELECT '' AS one, f.f1 ^ '2.0' AS square_f1 79 FROM FLOAT8_TBL f where f.f1 = '1004.3'; 80 81-- absolute value 82SELECT '' AS five, f.f1, @f.f1 AS abs_f1 83 FROM FLOAT8_TBL f; 84 85-- truncate 86SELECT '' AS five, f.f1, trunc(f.f1) AS trunc_f1 87 FROM FLOAT8_TBL f; 88 89-- round 90SELECT '' AS five, f.f1, round(f.f1) AS round_f1 91 FROM FLOAT8_TBL f; 92 93-- ceil / ceiling 94select ceil(f1) as ceil_f1 from float8_tbl f; 95select ceiling(f1) as ceiling_f1 from float8_tbl f; 96 97-- floor 98select floor(f1) as floor_f1 from float8_tbl f; 99 100-- sign 101select sign(f1) as sign_f1 from float8_tbl f; 102 103-- avoid bit-exact output here because operations may not be bit-exact. 104SET extra_float_digits = 0; 105 106-- square root 107SELECT sqrt(float8 '64') AS eight; 108 109SELECT |/ float8 '64' AS eight; 110 111SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1 112 FROM FLOAT8_TBL f 113 WHERE f.f1 > '0.0'; 114 115-- power 116SELECT power(float8 '144', float8 '0.5'); 117SELECT power(float8 'NaN', float8 '0.5'); 118SELECT power(float8 '144', float8 'NaN'); 119SELECT power(float8 'NaN', float8 'NaN'); 120SELECT power(float8 '-1', float8 'NaN'); 121SELECT power(float8 '1', float8 'NaN'); 122SELECT power(float8 'NaN', float8 '0'); 123 124-- take exp of ln(f.f1) 125SELECT '' AS three, f.f1, exp(ln(f.f1)) AS exp_ln_f1 126 FROM FLOAT8_TBL f 127 WHERE f.f1 > '0.0'; 128 129-- cube root 130SELECT ||/ float8 '27' AS three; 131 132SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f; 133 134 135SELECT '' AS five, * FROM FLOAT8_TBL; 136 137UPDATE FLOAT8_TBL 138 SET f1 = FLOAT8_TBL.f1 * '-1' 139 WHERE FLOAT8_TBL.f1 > '0.0'; 140 141SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; 142 143SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; 144 145SELECT 0 ^ 0 + 0 ^ 1 + 0 ^ 0.0 + 0 ^ 0.5; 146 147SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 = '0.0' ; 148 149SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 < '0.0' ; 150 151SELECT '' AS bad, exp(f.f1) from FLOAT8_TBL f; 152 153SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f; 154 155SELECT '' AS five, * FROM FLOAT8_TBL; 156 157-- hyperbolic functions 158-- we run these with extra_float_digits = 0 too, since different platforms 159-- tend to produce results that vary in the last place. 160SELECT sinh(float8 '1'); 161SELECT cosh(float8 '1'); 162SELECT tanh(float8 '1'); 163SELECT asinh(float8 '1'); 164SELECT acosh(float8 '2'); 165SELECT atanh(float8 '0.5'); 166-- test Inf/NaN cases for hyperbolic functions 167SELECT sinh(float8 'infinity'); 168SELECT sinh(float8 '-infinity'); 169SELECT sinh(float8 'nan'); 170SELECT cosh(float8 'infinity'); 171SELECT cosh(float8 '-infinity'); 172SELECT cosh(float8 'nan'); 173SELECT tanh(float8 'infinity'); 174SELECT tanh(float8 '-infinity'); 175SELECT tanh(float8 'nan'); 176SELECT asinh(float8 'infinity'); 177SELECT asinh(float8 '-infinity'); 178SELECT asinh(float8 'nan'); 179-- acosh(Inf) should be Inf, but some mingw versions produce NaN, so skip test 180-- SELECT acosh(float8 'infinity'); 181SELECT acosh(float8 '-infinity'); 182SELECT acosh(float8 'nan'); 183SELECT atanh(float8 'infinity'); 184SELECT atanh(float8 '-infinity'); 185SELECT atanh(float8 'nan'); 186 187RESET extra_float_digits; 188 189-- test for over- and underflow 190INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400'); 191 192INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400'); 193 194INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'); 195 196INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400'); 197 198-- maintain external table consistency across platforms 199-- delete all values and reinsert well-behaved ones 200 201DELETE FROM FLOAT8_TBL; 202 203INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0'); 204 205INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84'); 206 207INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30'); 208 209INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200'); 210 211INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200'); 212 213SELECT '' AS five, * FROM FLOAT8_TBL; 214 215-- test edge-case coercions to integer 216SELECT '32767.4'::float8::int2; 217SELECT '32767.6'::float8::int2; 218SELECT '-32768.4'::float8::int2; 219SELECT '-32768.6'::float8::int2; 220SELECT '2147483647.4'::float8::int4; 221SELECT '2147483647.6'::float8::int4; 222SELECT '-2147483648.4'::float8::int4; 223SELECT '-2147483648.6'::float8::int4; 224SELECT '9223372036854773760'::float8::int8; 225SELECT '9223372036854775807'::float8::int8; 226SELECT '-9223372036854775808.5'::float8::int8; 227SELECT '-9223372036854780000'::float8::int8; 228 229-- test exact cases for trigonometric functions in degrees 230 231SELECT x, 232 sind(x), 233 sind(x) IN (-1,-0.5,0,0.5,1) AS sind_exact 234FROM (VALUES (0), (30), (90), (150), (180), 235 (210), (270), (330), (360)) AS t(x); 236 237SELECT x, 238 cosd(x), 239 cosd(x) IN (-1,-0.5,0,0.5,1) AS cosd_exact 240FROM (VALUES (0), (60), (90), (120), (180), 241 (240), (270), (300), (360)) AS t(x); 242 243SELECT x, 244 tand(x), 245 tand(x) IN ('-Infinity'::float8,-1,0, 246 1,'Infinity'::float8) AS tand_exact, 247 cotd(x), 248 cotd(x) IN ('-Infinity'::float8,-1,0, 249 1,'Infinity'::float8) AS cotd_exact 250FROM (VALUES (0), (45), (90), (135), (180), 251 (225), (270), (315), (360)) AS t(x); 252 253SELECT x, 254 asind(x), 255 asind(x) IN (-90,-30,0,30,90) AS asind_exact, 256 acosd(x), 257 acosd(x) IN (0,60,90,120,180) AS acosd_exact 258FROM (VALUES (-1), (-0.5), (0), (0.5), (1)) AS t(x); 259 260SELECT x, 261 atand(x), 262 atand(x) IN (-90,-45,0,45,90) AS atand_exact 263FROM (VALUES ('-Infinity'::float8), (-1), (0), (1), 264 ('Infinity'::float8)) AS t(x); 265 266SELECT x, y, 267 atan2d(y, x), 268 atan2d(y, x) IN (-90,0,90,180) AS atan2d_exact 269FROM (SELECT 10*cosd(a), 10*sind(a) 270 FROM generate_series(0, 360, 90) AS t(a)) AS t(x,y); 271 272-- 273-- test output (and round-trip safety) of various values. 274-- To ensure we're testing what we think we're testing, start with 275-- float values specified by bit patterns (as a useful side effect, 276-- this means we'll fail on non-IEEE platforms). 277 278create type xfloat8; 279create function xfloat8in(cstring) returns xfloat8 immutable strict 280 language internal as 'int8in'; 281create function xfloat8out(xfloat8) returns cstring immutable strict 282 language internal as 'int8out'; 283create type xfloat8 (input = xfloat8in, output = xfloat8out, like = float8); 284create cast (xfloat8 as float8) without function; 285create cast (float8 as xfloat8) without function; 286create cast (xfloat8 as bigint) without function; 287create cast (bigint as xfloat8) without function; 288 289-- float8: seeeeeee eeeeeeee eeeeeeee mmmmmmmm mmmmmmmm(x4) 290 291-- we don't care to assume the platform's strtod() handles subnormals 292-- correctly; those are "use at your own risk". However we do test 293-- subnormal outputs, since those are under our control. 294 295with testdata(bits) as (values 296 -- small subnormals 297 (x'0000000000000001'), 298 (x'0000000000000002'), (x'0000000000000003'), 299 (x'0000000000001000'), (x'0000000100000000'), 300 (x'0000010000000000'), (x'0000010100000000'), 301 (x'0000400000000000'), (x'0000400100000000'), 302 (x'0000800000000000'), (x'0000800000000001'), 303 -- these values taken from upstream testsuite 304 (x'00000000000f4240'), 305 (x'00000000016e3600'), 306 (x'0000008cdcdea440'), 307 -- borderline between subnormal and normal 308 (x'000ffffffffffff0'), (x'000ffffffffffff1'), 309 (x'000ffffffffffffe'), (x'000fffffffffffff')) 310select float8send(flt) as ibits, 311 flt 312 from (select bits::bigint::xfloat8::float8 as flt 313 from testdata 314 offset 0) s; 315 316-- round-trip tests 317 318with testdata(bits) as (values 319 (x'0000000000000000'), 320 -- smallest normal values 321 (x'0010000000000000'), (x'0010000000000001'), 322 (x'0010000000000002'), (x'0018000000000000'), 323 -- 324 (x'3ddb7cdfd9d7bdba'), (x'3ddb7cdfd9d7bdbb'), (x'3ddb7cdfd9d7bdbc'), 325 (x'3e112e0be826d694'), (x'3e112e0be826d695'), (x'3e112e0be826d696'), 326 (x'3e45798ee2308c39'), (x'3e45798ee2308c3a'), (x'3e45798ee2308c3b'), 327 (x'3e7ad7f29abcaf47'), (x'3e7ad7f29abcaf48'), (x'3e7ad7f29abcaf49'), 328 (x'3eb0c6f7a0b5ed8c'), (x'3eb0c6f7a0b5ed8d'), (x'3eb0c6f7a0b5ed8e'), 329 (x'3ee4f8b588e368ef'), (x'3ee4f8b588e368f0'), (x'3ee4f8b588e368f1'), 330 (x'3f1a36e2eb1c432c'), (x'3f1a36e2eb1c432d'), (x'3f1a36e2eb1c432e'), 331 (x'3f50624dd2f1a9fb'), (x'3f50624dd2f1a9fc'), (x'3f50624dd2f1a9fd'), 332 (x'3f847ae147ae147a'), (x'3f847ae147ae147b'), (x'3f847ae147ae147c'), 333 (x'3fb9999999999999'), (x'3fb999999999999a'), (x'3fb999999999999b'), 334 -- values very close to 1 335 (x'3feffffffffffff0'), (x'3feffffffffffff1'), (x'3feffffffffffff2'), 336 (x'3feffffffffffff3'), (x'3feffffffffffff4'), (x'3feffffffffffff5'), 337 (x'3feffffffffffff6'), (x'3feffffffffffff7'), (x'3feffffffffffff8'), 338 (x'3feffffffffffff9'), (x'3feffffffffffffa'), (x'3feffffffffffffb'), 339 (x'3feffffffffffffc'), (x'3feffffffffffffd'), (x'3feffffffffffffe'), 340 (x'3fefffffffffffff'), 341 (x'3ff0000000000000'), 342 (x'3ff0000000000001'), (x'3ff0000000000002'), (x'3ff0000000000003'), 343 (x'3ff0000000000004'), (x'3ff0000000000005'), (x'3ff0000000000006'), 344 (x'3ff0000000000007'), (x'3ff0000000000008'), (x'3ff0000000000009'), 345 -- 346 (x'3ff921fb54442d18'), 347 (x'4005bf0a8b14576a'), 348 (x'400921fb54442d18'), 349 -- 350 (x'4023ffffffffffff'), (x'4024000000000000'), (x'4024000000000001'), 351 (x'4058ffffffffffff'), (x'4059000000000000'), (x'4059000000000001'), 352 (x'408f3fffffffffff'), (x'408f400000000000'), (x'408f400000000001'), 353 (x'40c387ffffffffff'), (x'40c3880000000000'), (x'40c3880000000001'), 354 (x'40f869ffffffffff'), (x'40f86a0000000000'), (x'40f86a0000000001'), 355 (x'412e847fffffffff'), (x'412e848000000000'), (x'412e848000000001'), 356 (x'416312cfffffffff'), (x'416312d000000000'), (x'416312d000000001'), 357 (x'4197d783ffffffff'), (x'4197d78400000000'), (x'4197d78400000001'), 358 (x'41cdcd64ffffffff'), (x'41cdcd6500000000'), (x'41cdcd6500000001'), 359 (x'4202a05f1fffffff'), (x'4202a05f20000000'), (x'4202a05f20000001'), 360 (x'42374876e7ffffff'), (x'42374876e8000000'), (x'42374876e8000001'), 361 (x'426d1a94a1ffffff'), (x'426d1a94a2000000'), (x'426d1a94a2000001'), 362 (x'42a2309ce53fffff'), (x'42a2309ce5400000'), (x'42a2309ce5400001'), 363 (x'42d6bcc41e8fffff'), (x'42d6bcc41e900000'), (x'42d6bcc41e900001'), 364 (x'430c6bf52633ffff'), (x'430c6bf526340000'), (x'430c6bf526340001'), 365 (x'4341c37937e07fff'), (x'4341c37937e08000'), (x'4341c37937e08001'), 366 (x'4376345785d89fff'), (x'4376345785d8a000'), (x'4376345785d8a001'), 367 (x'43abc16d674ec7ff'), (x'43abc16d674ec800'), (x'43abc16d674ec801'), 368 (x'43e158e460913cff'), (x'43e158e460913d00'), (x'43e158e460913d01'), 369 (x'4415af1d78b58c3f'), (x'4415af1d78b58c40'), (x'4415af1d78b58c41'), 370 (x'444b1ae4d6e2ef4f'), (x'444b1ae4d6e2ef50'), (x'444b1ae4d6e2ef51'), 371 (x'4480f0cf064dd591'), (x'4480f0cf064dd592'), (x'4480f0cf064dd593'), 372 (x'44b52d02c7e14af5'), (x'44b52d02c7e14af6'), (x'44b52d02c7e14af7'), 373 (x'44ea784379d99db3'), (x'44ea784379d99db4'), (x'44ea784379d99db5'), 374 (x'45208b2a2c280290'), (x'45208b2a2c280291'), (x'45208b2a2c280292'), 375 -- 376 (x'7feffffffffffffe'), (x'7fefffffffffffff'), 377 -- round to even tests (+ve) 378 (x'4350000000000002'), 379 (x'4350000000002e06'), 380 (x'4352000000000003'), 381 (x'4352000000000004'), 382 (x'4358000000000003'), 383 (x'4358000000000004'), 384 (x'435f000000000020'), 385 -- round to even tests (-ve) 386 (x'c350000000000002'), 387 (x'c350000000002e06'), 388 (x'c352000000000003'), 389 (x'c352000000000004'), 390 (x'c358000000000003'), 391 (x'c358000000000004'), 392 (x'c35f000000000020'), 393 -- exercise fixed-point memmoves 394 (x'42dc12218377de66'), 395 (x'42a674e79c5fe51f'), 396 (x'4271f71fb04cb74c'), 397 (x'423cbe991a145879'), 398 (x'4206fee0e1a9e061'), 399 (x'41d26580b487e6b4'), 400 (x'419d6f34540ca453'), 401 (x'41678c29dcd6e9dc'), 402 (x'4132d687e3df217d'), 403 (x'40fe240c9fcb68c8'), 404 (x'40c81cd6e63c53d3'), 405 (x'40934a4584fd0fdc'), 406 (x'405edd3c07fb4c93'), 407 (x'4028b0fcd32f7076'), 408 (x'3ff3c0ca428c59f8'), 409 -- these cases come from the upstream's testsuite 410 -- LotsOfTrailingZeros) 411 (x'3e60000000000000'), 412 -- Regression 413 (x'c352bd2668e077c4'), 414 (x'434018601510c000'), 415 (x'43d055dc36f24000'), 416 (x'43e052961c6f8000'), 417 (x'3ff3c0ca2a5b1d5d'), 418 -- LooksLikePow5 419 (x'4830f0cf064dd592'), 420 (x'4840f0cf064dd592'), 421 (x'4850f0cf064dd592'), 422 -- OutputLength 423 (x'3ff3333333333333'), 424 (x'3ff3ae147ae147ae'), 425 (x'3ff3be76c8b43958'), 426 (x'3ff3c083126e978d'), 427 (x'3ff3c0c1fc8f3238'), 428 (x'3ff3c0c9539b8887'), 429 (x'3ff3c0ca2a5b1d5d'), 430 (x'3ff3c0ca4283de1b'), 431 (x'3ff3c0ca43db770a'), 432 (x'3ff3c0ca428abd53'), 433 (x'3ff3c0ca428c1d2b'), 434 (x'3ff3c0ca428c51f2'), 435 (x'3ff3c0ca428c58fc'), 436 (x'3ff3c0ca428c59dd'), 437 (x'3ff3c0ca428c59f8'), 438 (x'3ff3c0ca428c59fb'), 439 -- 32-bit chunking 440 (x'40112e0be8047a7d'), 441 (x'40112e0be815a889'), 442 (x'40112e0be826d695'), 443 (x'40112e0be83804a1'), 444 (x'40112e0be84932ad'), 445 -- MinMaxShift 446 (x'0040000000000000'), 447 (x'007fffffffffffff'), 448 (x'0290000000000000'), 449 (x'029fffffffffffff'), 450 (x'4350000000000000'), 451 (x'435fffffffffffff'), 452 (x'1330000000000000'), 453 (x'133fffffffffffff'), 454 (x'3a6fa7161a4d6e0c') 455) 456select float8send(flt) as ibits, 457 flt, 458 flt::text::float8 as r_flt, 459 float8send(flt::text::float8) as obits, 460 float8send(flt::text::float8) = float8send(flt) as correct 461 from (select bits::bigint::xfloat8::float8 as flt 462 from testdata 463 offset 0) s; 464 465-- clean up, lest opr_sanity complain 466drop type xfloat8 cascade; 467