<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="partitioning-limitations-functions">
      </a>
      26.6.3 Partitioning Limitations Relating to Functions
     </h3>
    </div>
   </div>
  </div>
  <p>
   This section discusses limitations in MySQL Partitioning
        relating specifically to functions used in partitioning
        expressions.
  </p>
  <a class="indexterm" name="idm46045082721744">
  </a>
  <p>
   Only the MySQL functions shown in the following list are allowed
        in partitioning expressions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="mathematical-functions.html#function_abs">
       <code class="literal">
        ABS()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="mathematical-functions.html#function_ceiling">
       <code class="literal">
        CEILING()
       </code>
      </a>
      (see
      <a class="xref" href="partitioning-limitations-functions.html#partitioning-limitations-ceiling-floor" title="CEILING() and FLOOR()">
       CEILING() and FLOOR()
      </a>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_datediff">
       <code class="literal">
        DATEDIFF()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_day">
       <code class="literal">
        DAY()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_dayofmonth">
       <code class="literal">
        DAYOFMONTH()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_dayofweek">
       <code class="literal">
        DAYOFWEEK()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_dayofyear">
       <code class="literal">
        DAYOFYEAR()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_extract">
       <code class="literal">
        EXTRACT()
       </code>
      </a>
      (see
      <a class="xref" href="partitioning-limitations-functions.html#partitioning-limitations-extract" title="EXTRACT() function with WEEK specifier">
       EXTRACT() function with WEEK specifier
      </a>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="mathematical-functions.html#function_floor">
       <code class="literal">
        FLOOR()
       </code>
      </a>
      (see
      <a class="xref" href="partitioning-limitations-functions.html#partitioning-limitations-ceiling-floor" title="CEILING() and FLOOR()">
       CEILING() and FLOOR()
      </a>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_hour">
       <code class="literal">
        HOUR()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_microsecond">
       <code class="literal">
        MICROSECOND()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_minute">
       <code class="literal">
        MINUTE()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="mathematical-functions.html#function_mod">
       <code class="literal">
        MOD()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_month">
       <code class="literal">
        MONTH()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_quarter">
       <code class="literal">
        QUARTER()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_second">
       <code class="literal">
        SECOND()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_time-to-sec">
       <code class="literal">
        TIME_TO_SEC()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_to-days">
       <code class="literal">
        TO_DAYS()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_to-seconds">
       <code class="literal">
        TO_SECONDS()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_unix-timestamp">
       <code class="literal">
        UNIX_TIMESTAMP()
       </code>
      </a>
      (with
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      columns)
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_weekday">
       <code class="literal">
        WEEKDAY()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_year">
       <code class="literal">
        YEAR()
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_yearweek">
       <code class="literal">
        YEARWEEK()
       </code>
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   In MySQL 8.4, partition pruning is supported for
        the
   <a class="link" href="date-and-time-functions.html#function_to-days">
    <code class="literal">
     TO_DAYS()
    </code>
   </a>
   ,
   <a class="link" href="date-and-time-functions.html#function_to-seconds">
    <code class="literal">
     TO_SECONDS()
    </code>
   </a>
   ,
   <a class="link" href="date-and-time-functions.html#function_year">
    <code class="literal">
     YEAR()
    </code>
   </a>
   , and
   <a class="link" href="date-and-time-functions.html#function_unix-timestamp">
    <code class="literal">
     UNIX_TIMESTAMP()
    </code>
   </a>
   functions. See
   <a class="xref" href="partitioning-pruning.html" title="26.4 Partition Pruning">
    Section 26.4, “Partition Pruning”
   </a>
   , for more information.
  </p>
  <p>
   <a name="partitioning-limitations-ceiling-floor">
   </a>
   <b>
    CEILING() and FLOOR().
   </b>
   Each of these functions returns an integer only if it is
          passed an argument of an exact numeric type, such as one of
          the
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     INT
    </code>
   </a>
   types or
   <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>
   . This means, for
          example, that the following
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE
          TABLE
    </code>
   </a>
   statement fails with an error, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28732232"><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> t <span class="token punctuation">(</span>c <span class="token datatype">FLOAT</span><span class="token punctuation">)</span> <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">LIST</span><span class="token punctuation">(</span> <span class="token function">FLOOR</span><span class="token punctuation">(</span>c<span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">PARTITION</span> p0 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1490 (HY000)<span class="token punctuation">:</span> The PARTITION function returns the wrong type</span></code></pre>
  </div>
  <p>
   <a name="partitioning-limitations-extract">
   </a>
   <b>
    EXTRACT() function with WEEK specifier.
   </b>
   The value returned by the
   <a class="link" href="date-and-time-functions.html#function_extract">
    <code class="literal">
     EXTRACT()
    </code>
   </a>
   function, when used
          as
   <a class="link" href="date-and-time-functions.html#function_extract">
    <code class="literal">
     EXTRACT(WEEK FROM
     <em class="replaceable">
      <code>
       col
      </code>
     </em>
     )
    </code>
   </a>
   , depends on the
          value of the
   <a class="link" href="server-system-variables.html#sysvar_default_week_format">
    <code class="literal">
     default_week_format
    </code>
   </a>
   system
          variable. For this reason,
   <a class="link" href="date-and-time-functions.html#function_extract">
    <code class="literal">
     EXTRACT()
    </code>
   </a>
   is not permitted as a
          partitioning function when it specifies the unit as
   <code class="literal">
    WEEK
   </code>
   . (Bug #54483)
  </p>
  <p>
   See
   <a class="xref" href="mathematical-functions.html" title="14.6.2 Mathematical Functions">
    Section 14.6.2, “Mathematical Functions”
   </a>
   , for more
        information about the return types of these functions, as well
        as
   <a class="xref" href="numeric-types.html" title="13.1 Numeric Data Types">
    Section 13.1, “Numeric Data Types”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
