<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="precision-math-expressions">
      </a>
      14.24.3 Expression Handling
     </h3>
    </div>
   </div>
  </div>
  <p>
   With precision math, exact-value numbers are used as given
      whenever possible. For example, numbers in comparisons are used
      exactly as given without a change in value. In strict SQL mode,
      for
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   into a column with an
      exact data type (
   <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>
   or
      integer), a number is inserted with its exact value if it is
      within the column range. When retrieved, the value should be the
      same as what was inserted. (If strict SQL mode is not enabled,
      truncation for
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   is
      permissible.)
  </p>
  <p>
   Handling of a numeric expression depends on what kind of values
      the expression contains:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If any approximate values are present, the expression is
          approximate and is evaluated using floating-point arithmetic.
     </p>
    </li>
    <li class="listitem">
     <p>
      If no approximate values are present, the expression contains
          only exact values. If any exact value contains a fractional
          part (a value following the decimal point), the expression is
          evaluated using
      <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>
      exact
          arithmetic and has a precision of 65 digits. The term
      <span class="quote">
       “
       <span class="quote">
        exact
       </span>
       ”
      </span>
      is subject to the limits of what can be
          represented in binary. For example,
      <code class="literal">
       1.0/3.0
      </code>
      can be approximated in decimal notation as
      <code class="literal">
       .333...
      </code>
      , but not written as an exact
          number, so
      <code class="literal">
       (1.0/3.0)*3.0
      </code>
      does not evaluate
          to exactly
      <code class="literal">
       1.0
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Otherwise, the expression contains only integer values. The
          expression is exact and is evaluated using integer arithmetic
          and has a precision the same as
      <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
       <code class="literal">
        BIGINT
       </code>
      </a>
      (64 bits).
     </p>
    </li>
   </ul>
  </div>
  <p>
   If a numeric expression contains any strings, they are converted
      to double-precision floating-point values and the expression is
      approximate.
  </p>
  <p>
   Inserts into numeric columns are affected by the SQL mode, which
      is controlled by the
   <a class="link" href="server-system-variables.html#sysvar_sql_mode">
    <code class="literal">
     sql_mode
    </code>
   </a>
   system variable. (See
   <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
    Section 7.1.11, “Server SQL Modes”
   </a>
   .) The following
      discussion mentions strict mode (selected by the
   <a class="link" href="sql-mode.html#sqlmode_strict_all_tables">
    <code class="literal">
     STRICT_ALL_TABLES
    </code>
   </a>
   or
   <a class="link" href="sql-mode.html#sqlmode_strict_trans_tables">
    <code class="literal">
     STRICT_TRANS_TABLES
    </code>
   </a>
   mode values)
      and
   <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
    <code class="literal">
     ERROR_FOR_DIVISION_BY_ZERO
    </code>
   </a>
   .
      To turn on all restrictions, you can simply use
   <a class="link" href="sql-mode.html#sqlmode_traditional">
    <code class="literal">
     TRADITIONAL
    </code>
   </a>
   mode, which includes
      both strict mode values and
   <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
    <code class="literal">
     ERROR_FOR_DIVISION_BY_ZERO
    </code>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa47308558"><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">SET</span> sql_mode<span class="token operator">=</span><span class="token string">'TRADITIONAL'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If a number is inserted into an exact type column
      (
   <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>
   or integer), it is
      inserted with its exact value if it is within the column range and
      precision.
  </p>
  <p>
   If the value has too many digits in the fractional part, rounding
      occurs and a note is generated. Rounding is done as described in
   <a class="xref" href="precision-math-rounding.html" title="14.24.4 Rounding Behavior">
    Section 14.24.4, “Rounding Behavior”
   </a>
   . Truncation due to
      rounding of the fractional part is not an error, even in strict
      mode.
  </p>
  <p>
   If the value has too many digits in the integer part, it is too
      large (out of range) and is handled as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If strict mode is not enabled, the value is truncated to the
          nearest legal value and a warning is generated.
     </p>
    </li>
    <li class="listitem">
     <p>
      If strict mode is enabled, an overflow error occurs.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Underflow is not detected, so underflow handling is undefined.
  </p>
  <p>
   For inserts of strings into numeric columns, conversion from
      string to number is handled as follows if the string has
      nonnumeric contents:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A string that does not begin with a number cannot be used as a
          number and produces an error in strict mode, or a warning
          otherwise. This includes the empty string.
     </p>
    </li>
    <li class="listitem">
     <p>
      A string that begins with a number can be converted, but the
          trailing nonnumeric portion is truncated. If the truncated
          portion contains anything other than spaces, this produces an
          error in strict mode, or a warning otherwise.
     </p>
    </li>
   </ul>
  </div>
  <p>
   By default, division by zero produces a result of
   <code class="literal">
    NULL
   </code>
   and no warning. By setting the SQL mode
      appropriately, division by zero can be restricted.
  </p>
  <p>
   With the
   <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
    <code class="literal">
     ERROR_FOR_DIVISION_BY_ZERO
    </code>
   </a>
   SQL
      mode enabled, MySQL handles division by zero differently:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If strict mode is not enabled, a warning occurs.
     </p>
    </li>
    <li class="listitem">
     <p>
      If strict mode is enabled, inserts and updates involving
          division by zero are prohibited, and an error occurs.
     </p>
    </li>
   </ul>
  </div>
  <p>
   In other words, inserts and updates involving expressions that
      perform division by zero can be treated as errors, but this
      requires
   <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
    <code class="literal">
     ERROR_FOR_DIVISION_BY_ZERO
    </code>
   </a>
   in
      addition to strict mode.
  </p>
  <p>
   Suppose that we have this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa60296509"><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">INSERT</span> <span class="token keyword">INTO</span> t <span class="token keyword">SET</span> i <span class="token operator">=</span> <span class="token number">1</span><span class="token operator">/</span><span class="token number">0</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This is what happens for combinations of strict and
   <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
    <code class="literal">
     ERROR_FOR_DIVISION_BY_ZERO
    </code>
   </a>
   modes.
  </p>
  <div class="informaltable">
   <table summary="What happens for combinations of strict and ERROR_FOR_DIVISION_BY_ZERO modes.">
    <colgroup>
     <col style="width: 50%"/>
     <col style="width: 50%"/>
    </colgroup>
    <thead>
     <tr>
      <th>
       <a class="link" href="server-system-variables.html#sysvar_sql_mode">
        <code class="literal">
         sql_mode
        </code>
       </a>
       Value
      </th>
      <th>
       Result
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <td>
       <code class="literal">
        ''
       </code>
       (Default)
      </td>
      <td>
       No warning, no error;
       <code class="literal">
        i
       </code>
       is set to
       <code class="literal">
        NULL
       </code>
       .
      </td>
     </tr>
     <tr>
      <td>
       strict
      </td>
      <td>
       No warning, no error;
       <code class="literal">
        i
       </code>
       is set to
       <code class="literal">
        NULL
       </code>
       .
      </td>
     </tr>
     <tr>
      <td>
       <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
        <code class="literal">
         ERROR_FOR_DIVISION_BY_ZERO
        </code>
       </a>
      </td>
      <td>
       Warning, no error;
       <code class="literal">
        i
       </code>
       is set to
       <code class="literal">
        NULL
       </code>
       .
      </td>
     </tr>
     <tr>
      <td>
       strict,
       <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
        <code class="literal">
         ERROR_FOR_DIVISION_BY_ZERO
        </code>
       </a>
      </td>
      <td>
       Error condition; no row is inserted.
      </td>
     </tr>
    </tbody>
   </table>
  </div>
 </div>
 <br/>
</div>
