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