<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="problems-with-float">
      </a>
      B.3.4.8 Problems with Floating-Point Values
     </h4>
    </div>
   </div>
  </div>
  <p>
   Floating-point numbers sometimes cause confusion because they
          are approximate and not stored as exact values. A
          floating-point value as written in an SQL statement may not be
          the same as the value represented internally. Attempts to
          treat floating-point values as exact in comparisons may lead
          to problems. They are also subject to platform or
          implementation dependencies. The
   <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
    <code class="literal">
     FLOAT
    </code>
   </a>
   and
   <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
    <code class="literal">
     DOUBLE
    </code>
   </a>
   data types are subject
          to these issues. For
   <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
    <code class="literal">
     DECIMAL
    </code>
   </a>
   columns, MySQL performs operations with a precision of 65
          decimal digits, which should solve most common inaccuracy
          problems.
  </p>
  <p>
   The following example uses
   <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
    <code class="literal">
     DOUBLE
    </code>
   </a>
   to demonstrate how
          calculations that are done using floating-point operations are
          subject to floating-point error.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17237125"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>i <span class="token datatype">INT</span><span class="token punctuation">,</span> d1 <span class="token datatype">DOUBLE</span><span class="token punctuation">,</span> d2 <span class="token datatype">DOUBLE</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">101.40</span><span class="token punctuation">,</span> <span class="token number">21.40</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token operator">-</span><span class="token number">80.00</span><span class="token punctuation">,</span> <span class="token number">0.00</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">0.00</span><span class="token punctuation">,</span> <span class="token number">0.00</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token operator">-</span><span class="token number">13.20</span><span class="token punctuation">,</span> <span class="token number">0.00</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">59.60</span><span class="token punctuation">,</span> <span class="token number">46.40</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">30.40</span><span class="token punctuation">,</span> <span class="token number">30.40</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">37.00</span><span class="token punctuation">,</span> <span class="token number">7.40</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token operator">-</span><span class="token number">29.60</span><span class="token punctuation">,</span> <span class="token number">0.00</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token number">60.00</span><span class="token punctuation">,</span> <span class="token number">15.40</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token operator">-</span><span class="token number">10.60</span><span class="token punctuation">,</span> <span class="token number">0.00</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token operator">-</span><span class="token number">34.00</span><span class="token punctuation">,</span> <span class="token number">0.00</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span> <span class="token number">33.00</span><span class="token punctuation">,</span> <span class="token number">0.00</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span> <span class="token operator">-</span><span class="token number">25.80</span><span class="token punctuation">,</span> <span class="token number">0.00</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span> <span class="token number">0.00</span><span class="token punctuation">,</span> <span class="token number">7.20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">,</span> <span class="token number">0.00</span><span class="token punctuation">,</span> <span class="token number">0.00</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">,</span> <span class="token operator">-</span><span class="token number">51.40</span><span class="token punctuation">,</span> <span class="token number">0.00</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> i<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>d1<span class="token punctuation">)</span> <span class="token keyword">AS</span> a<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>d2<span class="token punctuation">)</span> <span class="token keyword">AS</span> b
<span class="token prompt">    -&gt;</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> i <span class="token keyword">HAVING</span> a <span class="token operator">&lt;&gt;</span> b<span class="token punctuation">;</span>

<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> i    <span class="token punctuation">|</span> a     <span class="token punctuation">|</span> b    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>  21.4 <span class="token punctuation">|</span> 21.4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span>  76.8 <span class="token punctuation">|</span> 76.8 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>   7.4 <span class="token punctuation">|</span>  7.4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    4 <span class="token punctuation">|</span>  15.4 <span class="token punctuation">|</span> 15.4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>   7.2 <span class="token punctuation">|</span>  7.2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    6 <span class="token punctuation">|</span> <span class="token punctuation">-</span>51.4 <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   The result is correct. Although the first five records look
          like they should not satisfy the comparison (the values of
   <code class="literal">
    a
   </code>
   and
   <code class="literal">
    b
   </code>
   do not appear to
          be different), they may do so because the difference between
          the numbers shows up around the tenth decimal or so, depending
          on factors such as computer architecture or the compiler
          version or optimization level. For example, different CPUs may
          evaluate floating-point numbers differently.
  </p>
  <p>
   If columns
   <code class="literal">
    d1
   </code>
   and
   <code class="literal">
    d2
   </code>
   had
          been defined as
   <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
    <code class="literal">
     DECIMAL
    </code>
   </a>
   rather
          than
   <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
    <code class="literal">
     DOUBLE
    </code>
   </a>
   , the result of the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   query would have
          contained only one row—the last one shown above.
  </p>
  <p>
   The correct way to do floating-point number comparison is to
          first decide on an acceptable tolerance for differences
          between the numbers and then do the comparison against the
          tolerance value. For example, if we agree that floating-point
          numbers should be regarded the same if they are same within a
          precision of one in ten thousand (0.0001), the comparison
          should be written to find differences larger than the
          tolerance value:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa37469620"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> i<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>d1<span class="token punctuation">)</span> <span class="token keyword">AS</span> a<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>d2<span class="token punctuation">)</span> <span class="token keyword">AS</span> b <span class="token keyword">FROM</span> t1
<span class="token prompt">    -&gt;</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> i <span class="token keyword">HAVING</span> <span class="token function">ABS</span><span class="token punctuation">(</span>a <span class="token operator">-</span> b<span class="token punctuation">)</span> <span class="token operator">&gt;</span> <span class="token number">0.0001</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> i    <span class="token punctuation">|</span> a     <span class="token punctuation">|</span> b    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    6 <span class="token punctuation">|</span> <span class="token punctuation">-</span>51.4 <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   Conversely, to get rows where the numbers are the same, the
          test should find differences within the tolerance value:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51891303"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> i<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>d1<span class="token punctuation">)</span> <span class="token keyword">AS</span> a<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>d2<span class="token punctuation">)</span> <span class="token keyword">AS</span> b <span class="token keyword">FROM</span> t1
<span class="token prompt">    -&gt;</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> i <span class="token keyword">HAVING</span> <span class="token function">ABS</span><span class="token punctuation">(</span>a <span class="token operator">-</span> b<span class="token punctuation">)</span> <span class="token operator">&lt;=</span> <span class="token number">0.0001</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> i    <span class="token punctuation">|</span> a    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span> 21.4 <span class="token punctuation">|</span> 21.4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> 76.8 <span class="token punctuation">|</span> 76.8 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>  7.4 <span class="token punctuation">|</span>  7.4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    4 <span class="token punctuation">|</span> 15.4 <span class="token punctuation">|</span> 15.4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>  7.2 <span class="token punctuation">|</span>  7.2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">5 rows in set (0.03 sec)</span></code></pre>
  </div>
  <p>
   Floating-point values are subject to platform or
          implementation dependencies. Suppose that you execute the
          following statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15144527"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1<span class="token punctuation">(</span>c1 <span class="token datatype">FLOAT</span><span class="token punctuation">(</span><span class="token number">53</span><span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span> c2 <span class="token datatype">FLOAT</span><span class="token punctuation">(</span><span class="token number">53</span><span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'1e+52'</span><span class="token punctuation">,</span><span class="token string">'-1e+52'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   On some platforms, the
   <code class="literal">
    SELECT
   </code>
   statement
          returns
   <code class="literal">
    inf
   </code>
   and
   <code class="literal">
    -inf
   </code>
   . On
          others, it returns
   <code class="literal">
    0
   </code>
   and
   <code class="literal">
    -0
   </code>
   .
  </p>
  <p>
   An implication of the preceding issues is that if you attempt
          to create a replica by dumping table contents with
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   on the source and reloading the
          dump file into the replica, tables containing floating-point
          columns might differ between the two hosts.
  </p>
 </div>
 <br/>
</div>
