--
-- FLOAT8
--
CREATE EXTENSION sqlite_fdw;
CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw
OPTIONS (database '/tmp/sqlitefdw_test_core.db');
CREATE FOREIGN TABLE FLOAT8_TBL(f1 float8 OPTIONS (key 'true')) SERVER sqlite_svr;
INSERT INTO FLOAT8_TBL(f1) VALUES ('    0.0   ');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30  ');
INSERT INTO FLOAT8_TBL(f1) VALUES ('   -34.84');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
-- test for underflow and overflow handling
INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400'::float8);
ERROR:  "10e400" is out of range for type double precision
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400'::float8);
                                           ^
INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400'::float8);
ERROR:  "-10e400" is out of range for type double precision
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400'::float8);
                                           ^
INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'::float8);
ERROR:  "10e-400" is out of range for type double precision
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'::float8);
                                           ^
INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400'::float8);
ERROR:  "-10e-400" is out of range for type double precision
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400'::float8);
                                           ^
-- bad input
INSERT INTO FLOAT8_TBL(f1) VALUES ('');
ERROR:  invalid input syntax for type double precision: ""
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('');
                                           ^
INSERT INTO FLOAT8_TBL(f1) VALUES ('     ');
ERROR:  invalid input syntax for type double precision: "     "
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('     ');
                                           ^
INSERT INTO FLOAT8_TBL(f1) VALUES ('xyz');
ERROR:  invalid input syntax for type double precision: "xyz"
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('xyz');
                                           ^
INSERT INTO FLOAT8_TBL(f1) VALUES ('5.0.0');
ERROR:  invalid input syntax for type double precision: "5.0.0"
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('5.0.0');
                                           ^
INSERT INTO FLOAT8_TBL(f1) VALUES ('5 . 0');
ERROR:  invalid input syntax for type double precision: "5 . 0"
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('5 . 0');
                                           ^
INSERT INTO FLOAT8_TBL(f1) VALUES ('5.   0');
ERROR:  invalid input syntax for type double precision: "5.   0"
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('5.   0');
                                           ^
INSERT INTO FLOAT8_TBL(f1) VALUES ('    - 3');
ERROR:  invalid input syntax for type double precision: "    - 3"
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('    - 3');
                                           ^
INSERT INTO FLOAT8_TBL(f1) VALUES ('123           5');
ERROR:  invalid input syntax for type double precision: "123           5"
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('123           5');
                                           ^
-- special inputs
BEGIN;
DELETE FROM FLOAT8_TBL;
INSERT INTO FLOAT8_TBL VALUES ('NaN'::float8);
INSERT INTO FLOAT8_TBL VALUES ('nan'::float8);
INSERT INTO FLOAT8_TBL VALUES ('   NAN  '::float8);
INSERT INTO FLOAT8_TBL VALUES ('infinity'::float8);
INSERT INTO FLOAT8_TBL VALUES ('          -INFINiTY   '::float8);
SELECT * FROM FLOAT8_TBL;
    f1     
-----------
          
          
          
  Infinity
 -Infinity
(5 rows)

ROLLBACK;
-- bad special inputs
INSERT INTO FLOAT8_TBL VALUES ('N A N'::float8);
ERROR:  invalid input syntax for type double precision: "N A N"
LINE 1: INSERT INTO FLOAT8_TBL VALUES ('N A N'::float8);
                                       ^
INSERT INTO FLOAT8_TBL VALUES ('NaN x'::float8);
ERROR:  invalid input syntax for type double precision: "NaN x"
LINE 1: INSERT INTO FLOAT8_TBL VALUES ('NaN x'::float8);
                                       ^
INSERT INTO FLOAT8_TBL VALUES (' INFINITY    x'::float8);
ERROR:  invalid input syntax for type double precision: " INFINITY    x"
LINE 1: INSERT INTO FLOAT8_TBL VALUES (' INFINITY    x'::float8);
                                       ^
BEGIN;
DELETE FROM FLOAT8_TBL;
INSERT INTO FLOAT8_TBL VALUES ('Infinity'::float8 + 100.0);
INSERT INTO FLOAT8_TBL VALUES ('Infinity'::float8 / 'Infinity'::float8);
INSERT INTO FLOAT8_TBL VALUES ('nan'::float8 / 'nan'::float8);
INSERT INTO FLOAT8_TBL VALUES ('nan'::numeric::float8);
SELECT * FROM FLOAT8_TBL;
    f1    
----------
 Infinity
         
         
         
(4 rows)

ROLLBACK;
SELECT '' AS five, * FROM FLOAT8_TBL;
 five |          f1          
------+----------------------
      |                    0
      |               1004.3
      |               -34.84
      | 1.2345678901234e+200
      | 1.2345678901234e-200
(5 rows)

SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3';
 four |          f1          
------+----------------------
      |                    0
      |               -34.84
      | 1.2345678901234e+200
      | 1.2345678901234e-200
(4 rows)

SELECT '' AS one, f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3';
 one |   f1   
-----+--------
     | 1004.3
(1 row)

SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE '1004.3' > f.f1;
 three |          f1          
-------+----------------------
       |                    0
       |               -34.84
       | 1.2345678901234e-200
(3 rows)

SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE  f.f1 < '1004.3';
 three |          f1          
-------+----------------------
       |                    0
       |               -34.84
       | 1.2345678901234e-200
(3 rows)

SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1;
 four |          f1          
------+----------------------
      |                    0
      |               1004.3
      |               -34.84
      | 1.2345678901234e-200
(4 rows)

SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE  f.f1 <= '1004.3';
 four |          f1          
------+----------------------
      |                    0
      |               1004.3
      |               -34.84
      | 1.2345678901234e-200
(4 rows)

SELECT '' AS three, f.f1, f.f1 * '-10' AS x
   FROM FLOAT8_TBL f
   WHERE f.f1 > '0.0';
 three |          f1          |           x           
-------+----------------------+-----------------------
       |               1004.3 |                -10043
       | 1.2345678901234e+200 | -1.2345678901234e+201
       | 1.2345678901234e-200 | -1.2345678901234e-199
(3 rows)

SELECT '' AS three, f.f1, f.f1 + '-10' AS x
   FROM FLOAT8_TBL f
   WHERE f.f1 > '0.0';
 three |          f1          |          x           
-------+----------------------+----------------------
       |               1004.3 |                994.3
       | 1.2345678901234e+200 | 1.2345678901234e+200
       | 1.2345678901234e-200 |                  -10
(3 rows)

SELECT '' AS three, f.f1, f.f1 / '-10' AS x
   FROM FLOAT8_TBL f
   WHERE f.f1 > '0.0';
 three |          f1          |           x           
-------+----------------------+-----------------------
       |               1004.3 |               -100.43
       | 1.2345678901234e+200 | -1.2345678901234e+199
       | 1.2345678901234e-200 | -1.2345678901234e-201
(3 rows)

SELECT '' AS three, f.f1, f.f1 - '-10' AS x
   FROM FLOAT8_TBL f
   WHERE f.f1 > '0.0';
 three |          f1          |          x           
-------+----------------------+----------------------
       |               1004.3 |               1014.3
       | 1.2345678901234e+200 | 1.2345678901234e+200
       | 1.2345678901234e-200 |                   10
(3 rows)

SELECT '' AS one, f.f1 ^ '2.0' AS square_f1
   FROM FLOAT8_TBL f where f.f1 = '1004.3';
 one | square_f1  
-----+------------
     | 1008618.49
(1 row)

-- absolute value
SELECT '' AS five, f.f1, @f.f1 AS abs_f1
   FROM FLOAT8_TBL f;
 five |          f1          |        abs_f1        
------+----------------------+----------------------
      |                    0 |                    0
      |               1004.3 |               1004.3
      |               -34.84 |                34.84
      | 1.2345678901234e+200 | 1.2345678901234e+200
      | 1.2345678901234e-200 | 1.2345678901234e-200
(5 rows)

-- truncate
SELECT '' AS five, f.f1, trunc(f.f1) AS trunc_f1
   FROM FLOAT8_TBL f;
 five |          f1          |       trunc_f1       
------+----------------------+----------------------
      |                    0 |                    0
      |               1004.3 |                 1004
      |               -34.84 |                  -34
      | 1.2345678901234e+200 | 1.2345678901234e+200
      | 1.2345678901234e-200 |                    0
(5 rows)

-- round
SELECT '' AS five, f.f1, round(f.f1) AS round_f1
   FROM FLOAT8_TBL f;
 five |          f1          |       round_f1       
------+----------------------+----------------------
      |                    0 |                    0
      |               1004.3 |                 1004
      |               -34.84 |                  -35
      | 1.2345678901234e+200 | 1.2345678901234e+200
      | 1.2345678901234e-200 |                    0
(5 rows)

-- ceil / ceiling
select ceil(f1) as ceil_f1 from float8_tbl f;
       ceil_f1        
----------------------
                    0
                 1005
                  -34
 1.2345678901234e+200
                    1
(5 rows)

select ceiling(f1) as ceiling_f1 from float8_tbl f;
      ceiling_f1      
----------------------
                    0
                 1005
                  -34
 1.2345678901234e+200
                    1
(5 rows)

-- floor
select floor(f1) as floor_f1 from float8_tbl f;
       floor_f1       
----------------------
                    0
                 1004
                  -35
 1.2345678901234e+200
                    0
(5 rows)

-- sign
select sign(f1) as sign_f1 from float8_tbl f;
 sign_f1 
---------
       0
       1
      -1
       1
       1
(5 rows)

-- square root
BEGIN;
DELETE FROM FLOAT8_TBL;
INSERT INTO FLOAT8_TBL VALUES (sqrt(float8 '64'));
INSERT INTO FLOAT8_TBL VALUES (|/ float8 '64');
SELECT * FROM FLOAT8_TBL;
 f1 
----
  8
  8
(2 rows)

ROLLBACK;
SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1
   FROM FLOAT8_TBL f
   WHERE f.f1 > '0.0';
 three |          f1          |        sqrt_f1        
-------+----------------------+-----------------------
       |               1004.3 |      31.6906926399535
       | 1.2345678901234e+200 | 1.11111110611109e+100
       | 1.2345678901234e-200 | 1.11111110611109e-100
(3 rows)

-- power
BEGIN;
DELETE FROM FLOAT8_TBL;
INSERT INTO FLOAT8_TBL VALUES (power(float8 '144', float8 '0.5'));
INSERT INTO FLOAT8_TBL VALUES (power(float8 'NaN', float8 '0.5'));
INSERT INTO FLOAT8_TBL VALUES (power(float8 '144', float8 'NaN'));
INSERT INTO FLOAT8_TBL VALUES (power(float8 'NaN', float8 'NaN'));
INSERT INTO FLOAT8_TBL VALUES (power(float8 '-1', float8 'NaN'));
INSERT INTO FLOAT8_TBL VALUES (power(float8 '1', float8 'NaN'));
INSERT INTO FLOAT8_TBL VALUES (power(float8 'NaN', float8 '0'));
SELECT * FROM FLOAT8_TBL;
 f1 
----
 12
   
   
   
   
  1
  1
(7 rows)

ROLLBACK;
-- take exp of ln(f.f1)
SELECT '' AS three, f.f1, exp(ln(f.f1)) AS exp_ln_f1
   FROM FLOAT8_TBL f
   WHERE f.f1 > '0.0';
 three |          f1          |       exp_ln_f1       
-------+----------------------+-----------------------
       |               1004.3 |                1004.3
       | 1.2345678901234e+200 | 1.23456789012338e+200
       | 1.2345678901234e-200 | 1.23456789012339e-200
(3 rows)

-- cube root
BEGIN;
DELETE FROM FLOAT8_TBL;
INSERT INTO FLOAT8_TBL VALUES (||/ float8 '27');
SELECT * FROM FLOAT8_TBL;
 f1 
----
  3
(1 row)

ROLLBACK;
SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f;
 five |          f1          |       cbrt_f1        
------+----------------------+----------------------
      |                    0 |                    0
      |               1004.3 |      10.014312837827
      |               -34.84 |    -3.26607421344208
      | 1.2345678901234e+200 | 4.97933859234765e+66
      | 1.2345678901234e-200 |  2.3112042409018e-67
(5 rows)

SELECT '' AS five, * FROM FLOAT8_TBL;
 five |          f1          
------+----------------------
      |                    0
      |               1004.3
      |               -34.84
      | 1.2345678901234e+200
      | 1.2345678901234e-200
(5 rows)

UPDATE FLOAT8_TBL
   SET f1 = FLOAT8_TBL.f1 * '-1'
   WHERE FLOAT8_TBL.f1 > '0.0';
SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f;
ERROR:  value out of range: overflow
SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f;
ERROR:  value out of range: overflow
BEGIN;
DELETE FROM FLOAT8_TBL;
INSERT INTO FLOAT8_TBL VALUES (0 ^ 0 + 0 ^ 1 + 0 ^ 0.0 + 0 ^ 0.5);
SELECT * FROM FLOAT8_TBL;
 f1 
----
  2
(1 row)

ROLLBACK;
SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 = '0.0' ;
ERROR:  cannot take logarithm of zero
SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 < '0.0' ;
ERROR:  cannot take logarithm of a negative number
SELECT '' AS bad, exp(f.f1) from FLOAT8_TBL f;
ERROR:  value out of range: underflow
SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f;
ERROR:  division by zero
SELECT '' AS five, * FROM FLOAT8_TBL;
 five |          f1           
------+-----------------------
      |                     0
      |               -1004.3
      |                -34.84
      | -1.2345678901234e+200
      | -1.2345678901234e-200
(5 rows)

-- test for over- and underflow
INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
ERROR:  "10e400" is out of range for type double precision
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
                                           ^
INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
ERROR:  "-10e400" is out of range for type double precision
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
                                           ^
INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
ERROR:  "10e-400" is out of range for type double precision
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
                                           ^
INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
ERROR:  "-10e-400" is out of range for type double precision
LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
                                           ^
-- maintain external table consistency across platforms
-- delete all values and reinsert well-behaved ones
DELETE FROM FLOAT8_TBL;
INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30');
INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200');
INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200');
SELECT '' AS five, * FROM FLOAT8_TBL;
 five |          f1           
------+-----------------------
      |                     0
      |                -34.84
      |               -1004.3
      | -1.2345678901234e+200
      | -1.2345678901234e-200
(5 rows)

-- test exact cases for trigonometric functions in degrees
SET extra_float_digits = 3;
BEGIN;
DELETE FROM FLOAT8_TBL;
INSERT INTO FLOAT8_TBL VALUES (0), (30), (90), (150), (180),
      (210), (270), (330), (360);
SELECT f1,
       sind(f1),
       sind(f1) IN (-1,-0.5,0,0.5,1) AS sind_exact
       FROM FLOAT8_TBL;
 f1  | sind | sind_exact 
-----+------+------------
   0 |    0 | t
  30 |  0.5 | t
  90 |    1 | t
 150 |  0.5 | t
 180 |    0 | t
 210 | -0.5 | t
 270 |   -1 | t
 330 | -0.5 | t
 360 |    0 | t
(9 rows)

DELETE FROM FLOAT8_TBL;
INSERT INTO FLOAT8_TBL VALUES (0), (60), (90), (120), (180),
      (240), (270), (300), (360);
SELECT f1,
       cosd(f1),
       cosd(f1) IN (-1,-0.5,0,0.5,1) AS cosd_exact
       FROM FLOAT8_TBL;
 f1  | cosd | cosd_exact 
-----+------+------------
   0 |    1 | t
  60 |  0.5 | t
  90 |    0 | t
 120 | -0.5 | t
 180 |   -1 | t
 240 | -0.5 | t
 270 |    0 | t
 300 |  0.5 | t
 360 |    1 | t
(9 rows)

DELETE FROM FLOAT8_TBL;
INSERT INTO FLOAT8_TBL VALUES (0), (45), (90), (135), (180),
      (225), (270), (315), (360);
SELECT f1,
       tand(f1),
       tand(f1) IN ('-Infinity'::float8,-1,0,
                   1,'Infinity'::float8) AS tand_exact,
       cotd(f1),
       cotd(f1) IN ('-Infinity'::float8,-1,0,
                   1,'Infinity'::float8) AS cotd_exact
          FROM FLOAT8_TBL;
 f1  |   tand    | tand_exact |   cotd    | cotd_exact 
-----+-----------+------------+-----------+------------
   0 |         0 | t          |  Infinity | t
  45 |         1 | t          |         1 | t
  90 |  Infinity | t          |         0 | t
 135 |        -1 | t          |        -1 | t
 180 |         0 | t          | -Infinity | t
 225 |         1 | t          |         1 | t
 270 | -Infinity | t          |         0 | t
 315 |        -1 | t          |        -1 | t
 360 |         0 | t          |  Infinity | t
(9 rows)

DELETE FROM FLOAT8_TBL;
INSERT INTO FLOAT8_TBL VALUES (-1), (-0.5), (0), (0.5), (1);
SELECT f1,
       asind(f1),
       asind(f1) IN (-90,-30,0,30,90) AS asind_exact,
       acosd(f1),
       acosd(f1) IN (0,60,90,120,180) AS acosd_exact
          FROM FLOAT8_TBL;
  f1  | asind | asind_exact | acosd | acosd_exact 
------+-------+-------------+-------+-------------
   -1 |   -90 | t           |   180 | t
 -0.5 |   -30 | t           |   120 | t
    0 |     0 | t           |    90 | t
  0.5 |    30 | t           |    60 | t
    1 |    90 | t           |     0 | t
(5 rows)

DELETE FROM FLOAT8_TBL;
INSERT INTO FLOAT8_TBL VALUES ('-Infinity'::float8), (-1), (0), (1),
      ('Infinity'::float8);
SELECT f1,
       atand(f1),
       atand(f1) IN (-90,-45,0,45,90) AS atand_exact
          FROM FLOAT8_TBL;
    f1     | atand | atand_exact 
-----------+-------+-------------
 -Infinity |   -90 | t
        -1 |   -45 | t
         0 |     0 | t
         1 |    45 | t
  Infinity |    90 | t
(5 rows)

DELETE FROM FLOAT8_TBL;
INSERT INTO FLOAT8_TBL SELECT * FROM generate_series(0, 360, 90);
SELECT x, y,
       atan2d(y, x),
       atan2d(y, x) IN (-90,0,90,180) AS atan2d_exact
FROM (SELECT 10*cosd(f1), 10*sind(f1)
          FROM FLOAT8_TBL) AS t(x,y);
  x  |  y  | atan2d | atan2d_exact 
-----+-----+--------+--------------
  10 |   0 |      0 | t
   0 |  10 |     90 | t
 -10 |   0 |    180 | t
   0 | -10 |    -90 | t
  10 |   0 |      0 | t
(5 rows)

ROLLBACK;
RESET extra_float_digits;
DROP FOREIGN TABLE FLOAT8_TBL;
DROP SERVER sqlite_svr;
DROP EXTENSION sqlite_fdw CASCADE;