numeric.out

PostgreSQL7.4.6 for Linux

    FROM num_result t1, num_exp_mul t2
    WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
    AND t1.result != t2.expected;
 id1 | id2 | result | expected 
-----+-----+--------+----------
(0 rows)

DELETE FROM num_result;
INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val * t2.val, 30)
    FROM num_data t1, num_data t2;
SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 30) as expected
    FROM num_result t1, num_exp_mul t2
    WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
    AND t1.result != round(t2.expected, 30);
 id1 | id2 | result | expected 
-----+-----+--------+----------
(0 rows)

-- ******************************
-- * Division check
-- ******************************
DELETE FROM num_result;
INSERT INTO num_result SELECT t1.id, t2.id, t1.val / t2.val
    FROM num_data t1, num_data t2
    WHERE t2.val != '0.0';
SELECT t1.id1, t1.id2, t1.result, t2.expected
    FROM num_result t1, num_exp_div t2
    WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
    AND t1.result != t2.expected;
 id1 | id2 | result | expected 
-----+-----+--------+----------
(0 rows)

DELETE FROM num_result;
INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val / t2.val, 80)
    FROM num_data t1, num_data t2
    WHERE t2.val != '0.0';
SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 80) as expected
    FROM num_result t1, num_exp_div t2
    WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
    AND t1.result != round(t2.expected, 80);
 id1 | id2 | result | expected 
-----+-----+--------+----------
(0 rows)

-- ******************************
-- * Square root check
-- ******************************
DELETE FROM num_result;
INSERT INTO num_result SELECT id, 0, SQRT(ABS(val))
    FROM num_data;
SELECT t1.id1, t1.result, t2.expected
    FROM num_result t1, num_exp_sqrt t2
    WHERE t1.id1 = t2.id
    AND t1.result != t2.expected;
 id1 | result | expected 
-----+--------+----------
(0 rows)

-- ******************************
-- * Natural logarithm check
-- ******************************
DELETE FROM num_result;
INSERT INTO num_result SELECT id, 0, LN(ABS(val))
    FROM num_data
    WHERE val != '0.0';
SELECT t1.id1, t1.result, t2.expected
    FROM num_result t1, num_exp_ln t2
    WHERE t1.id1 = t2.id
    AND t1.result != t2.expected;
 id1 | result | expected 
-----+--------+----------
(0 rows)

-- ******************************
-- * Logarithm base 10 check
-- ******************************
DELETE FROM num_result;
INSERT INTO num_result SELECT id, 0, LOG(numeric '10', ABS(val))
    FROM num_data
    WHERE val != '0.0';
SELECT t1.id1, t1.result, t2.expected
    FROM num_result t1, num_exp_log10 t2
    WHERE t1.id1 = t2.id
    AND t1.result != t2.expected;
 id1 | result | expected 
-----+--------+----------
(0 rows)

-- ******************************
-- * POW(10, LN(value)) check
-- ******************************
DELETE FROM num_result;
INSERT INTO num_result SELECT id, 0, POW(numeric '10', LN(ABS(round(val,200))))
    FROM num_data
    WHERE val != '0.0';
SELECT t1.id1, t1.result, t2.expected
    FROM num_result t1, num_exp_power_10_ln t2
    WHERE t1.id1 = t2.id
    AND t1.result != t2.expected;
 id1 | result | expected 
-----+--------+----------
(0 rows)

-- ******************************
-- * miscellaneous checks for things that have been broken in the past...
-- ******************************
-- numeric AVG used to fail on some platforms
SELECT AVG(val) FROM num_data;
          avg           
------------------------
 -13430913.592242320700
(1 row)

-- Check for appropriate rounding and overflow
CREATE TABLE fract_only (id int, val numeric(4,4));
INSERT INTO fract_only VALUES (1, '0.0');
INSERT INTO fract_only VALUES (2, '0.1');
INSERT INTO fract_only VALUES (3, '1.0');	-- should fail
ERROR:  numeric field overflow
DETAIL:  The absolute value is greater than or equal to 10^0 for field with precision 4, scale 4.
INSERT INTO fract_only VALUES (4, '-0.9999');
INSERT INTO fract_only VALUES (5, '0.99994');
INSERT INTO fract_only VALUES (6, '0.99995');  -- should fail
ERROR:  numeric field overflow
DETAIL:  The absolute value is greater than or equal to 10^0 for field with precision 4, scale 4.
INSERT INTO fract_only VALUES (7, '0.00001');
INSERT INTO fract_only VALUES (8, '0.00017');
SELECT * FROM fract_only;
 id |   val   
----+---------
  1 |  0.0000
  2 |  0.1000
  4 | -0.9999
  5 |  0.9999
  7 |  0.0000
  8 |  0.0002
(6 rows)

DROP TABLE fract_only;
-- TO_CHAR()
--
SELECT '' AS to_char_1, to_char(val, '9G999G999G999G999G999') 
	FROM num_data;
 to_char_1 |        to_char         
-----------+------------------------
           |                      0
           |                      0
           |            -34,338,492
           |                      4
           |              7,799,461
           |                 16,397
           |                 93,902
           |            -83,028,485
           |                 74,881
           |            -24,926,804
(10 rows)

SELECT '' AS to_char_2, to_char(val, '9G999G999G999G999G999D999G999G999G999G999')
	FROM num_data;	
 to_char_2 |                  to_char                   
-----------+--------------------------------------------
           |                       .000,000,000,000,000
           |                       .000,000,000,000,000
           |            -34,338,492.215,397,047,000,000
           |                      4.310,000,000,000,000
           |              7,799,461.411,900,000,000,000
           |                 16,397.038,491,000,000,000
           |                 93,901.577,630,260,000,000
           |            -83,028,485.000,000,000,000,000
           |                 74,881.000,000,000,000,000
           |            -24,926,804.045,047,420,000,000
(10 rows)

SELECT '' AS to_char_3, to_char(val, '9999999999999999.999999999999999PR')
	FROM num_data;
 to_char_3 |              to_char               
-----------+------------------------------------
           |                  .000000000000000 
           |                  .000000000000000 
           |         <34338492.215397047000000>
           |                 4.310000000000000 
           |           7799461.411900000000000 
           |             16397.038491000000000 
           |             93901.577630260000000 
           |         <83028485.000000000000000>
           |             74881.000000000000000 
           |         <24926804.045047420000000>
(10 rows)

SELECT '' AS to_char_4, to_char(val, '9999999999999999.999999999999999S')
	FROM num_data;
 to_char_4 |              to_char              
-----------+-----------------------------------
           |                 .000000000000000+
           |                 .000000000000000+
           |         34338492.215397047000000-
           |                4.310000000000000+
           |          7799461.411900000000000+
           |            16397.038491000000000+
           |            93901.577630260000000+
           |         83028485.000000000000000-
           |            74881.000000000000000+
           |         24926804.045047420000000-
(10 rows)

SELECT '' AS to_char_5,  to_char(val, 'MI9999999999999999.999999999999999')     FROM num_data;	
 to_char_5 |              to_char              
-----------+-----------------------------------
           |                  .000000000000000
           |                  .000000000000000
           | -        34338492.215397047000000
           |                 4.310000000000000
           |           7799461.411900000000000
           |             16397.038491000000000
           |             93901.577630260000000
           | -        83028485.000000000000000
           |             74881.000000000000000
           | -        24926804.045047420000000
(10 rows)

SELECT '' AS to_char_6,  to_char(val, 'FMS9999999999999999.999999999999999')    FROM num_data;
 to_char_6 |       to_char       
-----------+---------------------
           | +0.
           | +0.
           | -34338492.215397047
           | +4.31
           | +7799461.4119
           | +16397.038491
           | +93901.57763026
           | -83028485.
           | +74881.
           | -24926804.04504742
(10 rows)

SELECT '' AS to_char_7,  to_char(val, 'FM9999999999999999.999999999999999THPR') FROM num_data;
 to_char_7 |       to_char        
-----------+----------------------
           | 0.
           | 0.
           | <34338492.215397047>
           | 4.31
           | 7799461.4119
           | 16397.038491
           | 93901.57763026
           | <83028485.>
           | 74881.
           | <24926804.04504742>
(10 rows)

SELECT '' AS to_char_8,  to_char(val, 'SG9999999999999999.999999999999999th')   FROM num_data;	
 to_char_8 |              to_char              
-----------+-----------------------------------
           | +                .000000000000000
           | +                .000000000000000
           | -        34338492.215397047000000
           | +               4.310000000000000
           | +         7799461.411900000000000
           | +           16397.038491000000000
           | +           93901.577630260000000
           | -        83028485.000000000000000
           | +           74881.000000000000000
           | -        24926804.045047420000000
(10 rows)

SELECT '' AS to_char_9,  to_char(val, '0999999999999999.999999999999999')       FROM num_data;	
 to_char_9 |              to_char              
-----------+-----------------------------------
           |  0000000000000000.000000000000000
           |  0000000000000000.000000000000000
           | -0000000034338492.215397047000000
           |  0000000000000004.310000000000000
           |  0000000007799461.411900000000000
           |  0000000000016397.038491000000000
           |  0000000000093901.577630260000000
           | -0000000083028485.000000000000000
           |  0000000000074881.000000000000000
           | -0000000024926804.045047420000000