<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="constant-folding-optimization">
      </a>
      10.2.1.14 Constant-Folding Optimization
     </h4>
    </div>
   </div>
  </div>
  <p>
   Comparisons between constants and column values in which the
          constant value is out of range or of the wrong type with
          respect to the column type are now handled once during query
          optimization rather row-by-row than during execution. The
          comparisons that can be treated in this manner are
   <code class="literal">
    &gt;
   </code>
   ,
   <code class="literal">
    &gt;=
   </code>
   ,
   <code class="literal">
    &lt;
   </code>
   ,
   <code class="literal">
    &lt;=
   </code>
   ,
   <code class="literal">
    &lt;&gt;
   </code>
   /
   <code class="literal">
    !=
   </code>
   ,
   <code class="literal">
    =
   </code>
   , and
   <code class="literal">
    &lt;=&gt;
   </code>
   .
  </p>
  <p>
   Consider the table created by the following statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa67899103"><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> t <span class="token punctuation">(</span>c <span class="token datatype">TINYINT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    WHERE
   </code>
   condition in the query
   <code class="literal">
    SELECT * FROM t WHERE c &lt; 256
   </code>
   contains
          the integral constant 256 which is out of range for a
   <code class="literal">
    TINYINT UNSIGNED
   </code>
   column. Previously, this
          was handled by treating both operands as the larger type, but
          now, since any allowed value for
   <code class="literal">
    c
   </code>
   is less
          than the constant, the
   <code class="literal">
    WHERE
   </code>
   expression can
          instead be folded as
   <code class="literal">
    WHERE 1
   </code>
   , so that the
          query is rewritten as
   <code class="literal">
    SELECT * FROM t WHERE
          1
   </code>
   .
  </p>
  <p>
   This makes it possible for the optimizer to remove the
   <code class="literal">
    WHERE
   </code>
   expression altogether. If the column
   <code class="literal">
    c
   </code>
   were nullable (that is, defined only as
   <code class="literal">
    TINYINT UNSIGNED
   </code>
   ) the query would be
          rewritten like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa84729493"><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">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t <span class="token keyword">WHERE</span> ti <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span></code></pre>
  </div>
  <p>
   Folding is performed for constants compared to supported MySQL
          column types as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <b>
       Integer column type.
      </b>
      Integer types are compared with constants of the
                following types as described here:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <b>
          Integer value.
         </b>
         If the constant is out of range for the column type,
                    the comparison is folded to
         <code class="literal">
          1
         </code>
         or
         <code class="literal">
          IS NOT NULL
         </code>
         , as already shown.
        </p>
        <p>
         If the constant is a range boundary, the comparison is
                  folded to
         <code class="literal">
          =
         </code>
         . For example (using the
                  same table as already defined):
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71432695"><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">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t <span class="token keyword">WHERE</span> c <span class="token operator">&gt;=</span> <span class="token number">255</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 class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <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 class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> SIMPLE
        table<span class="token punctuation">:</span> t
   partitions<span class="token punctuation">:</span> NULL
         type<span class="token punctuation">:</span> ALL
possible_keys<span class="token punctuation">:</span> NULL
          key<span class="token punctuation">:</span> NULL
      key_len<span class="token punctuation">:</span> NULL
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 5
     filtered<span class="token punctuation">:</span> 20.00
        Extra<span class="token punctuation">:</span> Using where
</span><span class="token output">1 row in set, 1 warning (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">WARNINGS</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 class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <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 class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
  Level<span class="token punctuation">:</span> Note
   Code<span class="token punctuation">:</span> 1003
Message<span class="token punctuation">:</span> /<span class="token punctuation">*</span> select#1 <span class="token punctuation">*</span>/ select `test`.`t`.`ti` AS `ti` from `test`.`t` where (`test`.`t`.`ti` = 255)
</span><span class="token output">1 row in set (0.00 sec)</span></code></pre>
        </div>
       </li>
       <li class="listitem">
        <p>
         <b>
          Floating- or fixed-point value.
         </b>
         If the constant is one of the decimal types (such as
         <code class="literal">
          DECIMAL
         </code>
         ,
         <code class="literal">
          REAL
         </code>
         ,
         <code class="literal">
          DOUBLE
         </code>
         , or
         <code class="literal">
          FLOAT
         </code>
         ) and has a nonzero decimal
                    portion, it cannot be equal; fold accordingly. For
                    other comparisons, round up or down to an integer
                    value according to the sign, then perform a range
                    check and handle as already described for
                    integer-integer comparisons.
        </p>
        <p>
         A
         <code class="literal">
          REAL
         </code>
         value that is too small to
                  be represented as
         <code class="literal">
          DECIMAL
         </code>
         is
                  rounded to .01 or -.01 depending on the sign, then
                  handled as a
         <code class="literal">
          DECIMAL
         </code>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         <b>
          String types.
         </b>
         Try to interpret the string value as an integer
                    type, then handle the comparison as between integer
                    values. If this fails, attempt to handle the value
                    as a
         <code class="literal">
          REAL
         </code>
         .
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      <b>
       DECIMAL or REAL column.
      </b>
      Decimal types are compared with constants of the
                following types as described here:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <b>
          Integer value.
         </b>
         Perform a range check against the column
                    value's integer part. If no folding results,
                    convert the constant to
         <code class="literal">
          DECIMAL
         </code>
         with the same number of decimal places as the column
                    value, then check it as a
         <code class="literal">
          DECIMAL
         </code>
         (see next).
        </p>
       </li>
       <li class="listitem">
        <p>
         <b>
          DECIMAL or REAL value.
         </b>
         Check for overflow (that is, whether the constant
                    has more digits in its integer part than allowed for
                    the column's decimal type). If so, fold.
        </p>
        <p>
         If the constant has more significant fractional digits
                  than column's type, truncate the constant. If the
                  comparison operator is
         <code class="literal">
          =
         </code>
         or
         <code class="literal">
          &lt;&gt;
         </code>
         , fold. If the operator is
         <code class="literal">
          &gt;=
         </code>
         or
         <code class="literal">
          &lt;=
         </code>
         ,
                  adjust the operator due to truncation. For example, if
                  column's type is
         <code class="literal">
          DECIMAL(3,1)
         </code>
         ,
         <code class="literal">
          SELECT * FROM t WHERE f &gt;= 10.13
         </code>
         becomes
         <code class="literal">
          SELECT * FROM t WHERE f &gt;
                  10.1
         </code>
         .
        </p>
        <p>
         If the constant has fewer decimal digits than the
                  column's type, convert it to a constant with same
                  number of digits. For underflow of a
         <code class="literal">
          REAL
         </code>
         value (that is, too few
                  fractional digits to represent it), convert the
                  constant to decimal 0.
        </p>
       </li>
       <li class="listitem">
        <p>
         <b>
          String value.
         </b>
         If the value can be interpreted as an integer type,
                    handle it as such. Otherwise, try to handle it as
         <code class="literal">
          REAL
         </code>
         .
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      <b>
       FLOAT or DOUBLE column.
      </b>
      <code class="literal">
       FLOAT(
       <em class="replaceable">
        <code>
         m
        </code>
       </em>
       ,
       <em class="replaceable">
        <code>
         n
        </code>
       </em>
       )
      </code>
      or
      <code class="literal">
       DOUBLE(
       <em class="replaceable">
        <code>
         m
        </code>
       </em>
       ,
       <em class="replaceable">
        <code>
         n
        </code>
       </em>
       )
      </code>
      values compared with constants are handled as follows:
     </p>
     <p>
      If the value overflows the range of the column, fold.
     </p>
     <p>
      If the value has more than
      <em class="replaceable">
       <code>
        n
       </code>
      </em>
      decimals, truncate, compensating during folding. For
      <code class="literal">
       =
      </code>
      and
      <code class="literal">
       &lt;&gt;
      </code>
      comparisons, fold to
      <code class="literal">
       TRUE
      </code>
      ,
      <code class="literal">
       FALSE
      </code>
      , or
      <code class="literal">
       IS [NOT]
              NULL
      </code>
      as described previously; for other
              operators, adjust the operator.
     </p>
     <p>
      If the value has more than
      <code class="literal">
       m
      </code>
      integer
              digits, fold.
     </p>
    </li>
   </ul>
  </div>
  <p>
   <b>
    Limitations.
   </b>
   This optimization cannot be used in the following cases:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      With comparisons using
      <code class="literal">
       BETWEEN
      </code>
      or
      <code class="literal">
       IN
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      With
      <code class="literal">
       BIT
      </code>
      columns or columns using date
              or time types.
     </p>
    </li>
    <li class="listitem">
     <p>
      During the preparation phase for a prepared statement,
              although it can be applied during the optimization phase
              when the prepared statement is actually executed. This due
              to the fact that, during statement preparation, the value
              of the constant is not yet known.
     </p>
    </li>
   </ol>
  </div>
 </div>
 <br/>
</div>
