title: Rounding issue in LOG and EXP functions
body: <p>I am trying to perform <em>cumulative multiplication</em>. I am trying two methods to do this </p>
  
  <h2>sample data:</h2>
  
  <pre><code>DECLARE @TEST TABLE
    (
       PAR_COLUMN INT,
       PERIOD     INT,
       VALUE      NUMERIC(22, 6)
    ) 
  INSERT INTO @TEST VALUES 
  (1,601,10 ),
  (1,602,20 ),
  (1,603,30 ),
  (1,604,40 ),
  (1,605,50 ),
  (1,606,60 ),
  (2,601,100),
  (2,602,200),
  (2,603,300),
  (2,604,400),
  (2,605,500),
  (2,606,600)
  </code></pre>
  
  <p><strong>Note:</strong>  The data in <code>value</code> column will never be integer and values will have decimal part. To show approximation problem I have kept example values as integers.</p>
  
  <hr>
  
  <h2>Method 1: EXP + LOG + SUM() Over(Order by)</h2>
  
  <p>In this method am using <code>EXP + LOG + SUM() Over(Order by)</code> technique to find cumulative multiplication. In this method values are not accurate; there is some rounding and approximation issue in the result.</p>
  
  <pre><code>SELECT *,
         Exp(Sum(Log(Abs(NULLIF(VALUE, 0))))
               OVER(
                 PARTITION BY PAR_COLUMN
                 ORDER BY PERIOD)) AS CUM_MUL
  FROM   @TEST;
  </code></pre>
  
  <h3>Result:</h3>
  
  <pre><code>PAR_COLUMN  PERIOD  VALUE       CUM_MUL
  ----------  ------  ---------   ----------------
  1           601     10.000000   10
  1           602     20.000000   200             -- 10 * 20 = 200(correct)
  1           603     30.000000   6000.00000000001 -- 200 * 30 = 6000.000000000 (not 6000.00000000001) incorrect
  1           604     40.000000   240000
  1           605     50.000000   12000000
  1           606     60.000000   720000000.000001  -- 12000000 * 60 = 720000000.000000 (not 720000000.000001) incorrect
  2           601     100.000000  100
  2           602     200.000000  20000
  2           603     300.000000  5999999.99999999 -- 20000.000000 *300.000000 = 6000000.000000 (not 5999999.99999999) incorrect
  2           604     400.000000  2399999999.99999  
  2           605     500.000000  1199999999999.99
  2           606     600.000000  719999999999998
  </code></pre>
  
  <hr>
  
  <h2>Method 2: Tradictional Multiplication (Recursive CTE)</h2>
  
  <p>This method works perfectly without any rounding or approximation problem.</p>
  
  <pre><code>;WITH CTE
       AS (SELECT TOP 1 WITH TIES PAR_COLUMN,
                                  PERIOD,
                                  VALUE,
                                  CUM_MUL = VALUE
           FROM   @TEST
           ORDER  BY PERIOD
           UNION ALL
           SELECT T.PAR_COLUMN,
                  T.PERIOD,
                  T.VALUE,
                  Cast(T.VALUE * C.CUM_MUL AS NUMERIC(22, 6))
           FROM   CTE C
                  INNER JOIN @TEST T
                          ON C.PAR_COLUMN = T.PAR_COLUMN
                             AND T.PERIOD = C.PERIOD + 1)
  SELECT *
  FROM   CTE 
  ORDER BY PAR_COLUMN,PERIOD
  </code></pre>
  
  <h3>Result</h3>
  
  <pre><code>PAR_COLUMN  PERIOD  VALUE       CUM_MUL
  ----------  ------  ---------   ----------------
  1           601     10.000000   10.000000
  1           602     20.000000   200.000000
  1           603     30.000000   6000.000000
  1           604     40.000000   240000.000000
  1           605     50.000000   12000000.000000
  1           606     60.000000   720000000.000000
  2           601     100.000000  100.000000
  2           602     200.000000  20000.000000
  2           603     300.000000  6000000.000000
  2           604     400.000000  2400000000.000000
  2           605     500.000000  1200000000000.000000
  2           606     600.000000  720000000000000.000000
  </code></pre>
  
  <hr>
  
  <p>Can anyone tell me <del>why in method 1 values are not accurate and</del> how to fix it?  I tried by changing the data types to <code>Float</code> and by increasing the <code>scale</code> in <code>numeric</code> but no use.</p>
  
  <p>I really want to use method 1 which is much faster than method 2.</p>
  
  <p><strong>Edit:</strong> Now I know the reason for approximation. Can anyone find a fix for this problem?</p>