List:Commits« Previous MessageNext Message »
From:jon.stephens Date:June 7 2010 8:16pm
Subject:svn commit - mysqldoc@docsrva: r21094 - in trunk: dynamic-docs/opsfunctions refman-5.1 refman-5.4 refman-5.5 refman-6.0
View as plain text  
Author: jstephens
Date: 2010-06-07 22:16:09 +0200 (Mon, 07 Jun 2010)
New Revision: 21094

Log:

COLUMNS partitioning, TO_SECONDS(), TRUNCATE PARTITION now in 6.0 tree

Harmonised unneeded differences in Partitioning chapter

Wording fixes



Modified:
   trunk/dynamic-docs/opsfunctions/opfunctions.xml
   trunk/refman-5.1/partitioning.xml
   trunk/refman-5.4/partitioning.xml
   trunk/refman-5.5/partitioning.xml
   trunk/refman-5.5/sql-syntax-data-definition.xml
   trunk/refman-6.0/functions-core.xml
   trunk/refman-6.0/partitioning.xml
   trunk/refman-6.0/sql-syntax-data-definition.xml


Modified: trunk/dynamic-docs/opsfunctions/opfunctions.xml
===================================================================
--- trunk/dynamic-docs/opsfunctions/opfunctions.xml	2010-06-07 19:55:03 UTC (rev 21093)
+++ trunk/dynamic-docs/opsfunctions/opfunctions.xml	2010-06-07 20:16:09 UTC (rev 21094)
Changed blocks: 1, Lines Added: 2, Lines Deleted: 0; 465 bytes

@@ -2709,6 +2709,8 @@
 
     <versions>
       <manual version="5.5"/>
+      <manual version="6.0"/>
+      <introduced version="6.0.14"/>
     </versions>
 
   </opfunction>


Modified: trunk/refman-5.1/partitioning.xml
===================================================================
--- trunk/refman-5.1/partitioning.xml	2010-06-07 19:55:03 UTC (rev 21093)
+++ trunk/refman-5.1/partitioning.xml	2010-06-07 20:16:09 UTC (rev 21094)
Changed blocks: 1, Lines Added: 4, Lines Deleted: 4; 892 bytes

@@ -1364,10 +1364,10 @@
 
       <para>
         You can cause this type of error to be ignored by using the
-        <literal>IGNORE</literal> key word. If you do so, rows
-        containing unmatched partitioning column values are not
-        inserted, but any rows with matching values
-        <emphasis>are</emphasis> inserted, and no errors are reported:
+        <literal>IGNORE</literal> keyword. If you do so, rows containing
+        unmatched partitioning column values are not inserted, but any
+        rows with matching values <emphasis>are</emphasis> inserted, and
+        no errors are reported:
       </para>
 
 <programlisting>


Modified: trunk/refman-5.4/partitioning.xml
===================================================================
--- trunk/refman-5.4/partitioning.xml	2010-06-07 19:55:03 UTC (rev 21093)
+++ trunk/refman-5.4/partitioning.xml	2010-06-07 20:16:09 UTC (rev 21094)
Changed blocks: 11, Lines Added: 27, Lines Deleted: 92; 8959 bytes

@@ -290,14 +290,19 @@
       use an SQL expression that is valid in MySQL, as long as that
       expression returns either <literal>NULL</literal> or an integer
       <replaceable>intval</replaceable> such that
+    </para>
 
 <programlisting>
 -MAXVALUE &lt;= <replaceable>intval</replaceable> &lt;= MAXVALUE
 </programlisting>
 
+    <para>
       (<literal>MAXVALUE</literal> is used to represent the least upper
       bound for the type of integer in question.
       <literal>-MAXVALUE</literal> represents the greatest lower bound.)
+    </para>
+
+    <para>
       There are some additional restrictions on partitioning functions;
       see <xref linkend="partitioning-limitations"/>, for more
       information about these.

@@ -345,11 +350,7 @@
       <para>
         MySQL partitioning cannot be used with the
         <literal>MERGE</literal>, <literal>CSV</literal>, or
-        <literal>FEDERATED</literal> storage engines. Partitioning by
-        <literal>KEY</literal> is possible with
-        <literal role="se">NDBCLUSTER</literal>, but other types of
-        user-defined partitioning are not supported for tables using
-        this storage engine.
+        <literal>FEDERATED</literal> storage engines.
       </para>
     </note>
 

@@ -868,7 +869,7 @@
         using a <quote>catchall</quote> <literal>VALUES LESS
         THAN</literal> clause in the <literal role="stmt">CREATE
         TABLE</literal> statement that provides for all values greater
-        than highest value explicitly named:
+        than the highest value explicitly named:
       </para>
 
 <programlisting>

@@ -922,8 +923,8 @@
         example&mdash;assuming that two-digit job codes are used for
         regular (in-store) workers, three-digit codes are used for
         office and support personnel, and four-digit codes are used for
-        management positions&mdash;you could create the partitioned
-        table using the following:
+        management positions&mdash;you could create this partitioned
+        table using the following statement:
       </para>
 
 <programlisting>

@@ -1258,10 +1259,10 @@
 
       <para>
         You can cause this type of error to be ignored by using the
-        <literal>IGNORE</literal> key word. If you do so, rows
-        containing unmatched partitioning column values are not
-        inserted, but any rows with matching values
-        <emphasis>are</emphasis> inserted, and no errors are reported:
+        <literal>IGNORE</literal> keyword. If you do so, rows containing
+        unmatched partitioning column values are not inserted, but any
+        rows with matching values <emphasis>are</emphasis> inserted, and
+        no errors are reported:
       </para>
 
 <programlisting>

@@ -1713,11 +1714,8 @@
         Partitioning by key is similar to partitioning by hash, except
         that where hash partitioning employs a user-defined expression,
         the hashing function for key partitioning is supplied by the
-        MySQL server. MySQL Cluster uses
-        <literal role="func">MD5()</literal> for this purpose; for
-        tables using other storage engines, the server employs its own
-        internal hashing function which is based on the same algorithm
-        as <literal role="func">PASSWORD()</literal>.
+        MySQL server. This internal hashing function is based on the
+        same algorithm as <literal role="func">PASSWORD()</literal>.
       </para>
 
       <para>

@@ -1827,32 +1825,13 @@
             <xref linkend="partitioning-limitations"/>.
           </para>
 
-          <note>
-            <para>
-              Tables using the <literal role="se">NDBCLUSTER</literal>
-              storage engine are implicitly partitioned by
-              <literal>KEY</literal>, again using the table&apos;s
-              primary key as the partitioning key. In the event that the
-              Cluster table has no explicit primary key, the
-              <quote>hidden</quote> primary key generated by the
-              <literal role="se">NDBCLUSTER</literal> storage engine for
-              each MySQL Cluster table is used as the partitioning key.
-            </para>
-          </note>
-
           <important>
             <para>
-              For a key-partitioned table using any MySQL storage engine
-              other than <literal role="se">NDBCLUSTER</literal>, you
-              cannot execute an <literal>ALTER TABLE DROP PRIMARY
-              KEY</literal>, as doing so generates the error
-              <errortext>ERROR 1466 (HY000): Field in list of fields for
-              partition function not found in table</errortext>. This is
-              not an issue for MySQL Cluster tables which are
-              partitioned by <literal>KEY</literal>; in such cases, the
-              table is reorganized using the <quote>hidden</quote>
-              primary key as the table's new partitioning key. See
-              <xref linkend="mysql-cluster"/>.
+              For a key-partitioned table, you cannot execute an
+              <literal>ALTER TABLE DROP PRIMARY KEY</literal>, as doing
+              so generates the error <errortext>ERROR 1466 (HY000):
+              Field in list of fields for partition function not found
+              in table</errortext>.
             </para>
           </important>
         </listitem>

@@ -2278,10 +2257,10 @@
         user-supplied expression. Even though it is permitted to use
         <literal>NULL</literal> as the value of an expression that must
         otherwise yield an integer, it is important to keep in mind that
-        <literal>NULL</literal> is not a number. The partitioning
-        implementation treats <literal>NULL</literal> as being less than
-        any non-<literal>NULL</literal> value, just as <literal>ORDER
-        BY</literal> does.
+        <literal>NULL</literal> is not a number. MySQL&apos;s
+        partitioning implementation treats <literal>NULL</literal> as
+        being less than any non-<literal>NULL</literal> value, just as
+        <literal>ORDER BY</literal> does.
       </para>
 
       <para>

@@ -2924,16 +2903,6 @@
 Query OK, 0 rows affected (0.03 sec)
 </programlisting>
 
-      <note>
-        <para>
-          The <literal role="se">NDBCLUSTER</literal> storage engine
-          does not support <literal>ALTER TABLE ... DROP
-          PARTITION</literal>. It does, however, support the other
-          partitioning-related extensions to <literal role="stmt">ALTER
-          TABLE</literal> that are described in this chapter.
-        </para>
-      </note>
-
       <para>
         It is very important to remember that, <emphasis>when you drop a
         partition, you also delete all the data that was stored in that

@@ -4154,17 +4123,9 @@
       partitioning expression uses the
       <literal role="func">YEAR()</literal> or
       <literal role="func">TO_DAYS()</literal> function.
+    </para>
 
-      <note>
-        <para>
-          We plan to add pruning support in a future MySQL release for
-          additional functions that act on a
-          <literal role="type">DATE</literal> or
-          <literal role="type">DATETIME</literal> value, return an
-          integer, and are increasing or decreasing.
-        </para>
-      </note>
-
+    <para>
       Suppose that table <literal>t2</literal>, defined as shown here,
       is partitioned on a <literal role="type">DATE</literal> column:
     </para>

@@ -5670,37 +5631,11 @@
 
       <formalpara>
 
-        <title><literal role="se">NDBCLUSTER</literal> storage engine (MySQL Cluster)</title>
-
-        <para>
-          Partitioning by <literal>KEY</literal> (or <literal>LINEAR
-          KEY</literal>) is the only type of partitioning supported for
-          the <literal role="se">NDBCLUSTER</literal> storage engine. It
-          is not possible to create a MySQL Cluster table using any
-          partitioning type other than [<literal>LINEAR</literal>]
-          <literal>KEY</literal>, and attempting to do so fails with an
-          error.
-        </para>
-
-      </formalpara>
-
-      <para>
-        In addition, the maximum number of partitions that can be
-        defined for an <literal role="se">NDBCLUSTER</literal> table is
-        8 times the number of node groups in the cluster. (See
-        <xref linkend="mysql-cluster-nodes-groups"/>, for more
-        information about node groups in MySQL Cluster.)
-      </para>
-
-      <formalpara>
-
         <title>Upgrading partitioned tables</title>
 
         <para>
           When performing an upgrade, tables which are partitioned by
-          <literal>KEY</literal> and which use any storage engine other
-          than <literal role="se">NDBCLUSTER</literal> must be dumped
-          and reloaded.
+          <literal>KEY</literal> must be dumped and reloaded.
         </para>
 
       </formalpara>


Modified: trunk/refman-5.5/partitioning.xml
===================================================================
--- trunk/refman-5.5/partitioning.xml	2010-06-07 19:55:03 UTC (rev 21093)
+++ trunk/refman-5.5/partitioning.xml	2010-06-07 20:16:09 UTC (rev 21094)
Changed blocks: 13, Lines Added: 77, Lines Deleted: 99; 11947 bytes

@@ -350,11 +350,7 @@
       <para>
         MySQL partitioning cannot be used with the
         <literal>MERGE</literal>, <literal>CSV</literal>, or
-        <literal>FEDERATED</literal> storage engines. Partitioning by
-        <literal>KEY</literal> is possible with
-        <literal role="se">NDBCLUSTER</literal>, but other types of
-        user-defined partitioning are not supported for tables using
-        this storage engine.
+        <literal>FEDERATED</literal> storage engines.
       </para>
     </note>
 

@@ -535,9 +531,10 @@
 
           <para>
             This type of partitioning assigns rows to partitions based
-            on column values falling within a given range. MySQL 5.5
-            adds an extension, <literal>RANGE COLUMNS</literal>, to this
-            type. See <xref linkend="partitioning-columns-range"/>.
+            on column values falling within a given range. See
+            <xref linkend="partitioning-range"/>. MySQL 5.5 adds an
+            extension, <literal>RANGE COLUMNS</literal>, to this type.
+            See <xref linkend="partitioning-columns-range"/>.
           </para>
 
         </formalpara>

@@ -551,9 +548,10 @@
           <para>
             Similar to partitioning by <literal>RANGE</literal>, except
             that the partition is selected based on columns matching one
-            of a set of discrete values. MySQL 5.5 adds an extension,
-            <literal>LIST COLUMNS</literal>, to this type. See
-            <xref linkend="partitioning-columns-list"/>.
+            of a set of discrete values. See
+            <xref linkend="partitioning-list"/>. MySQL 5.5 adds an
+            extension, <literal>LIST COLUMNS</literal>, to this type.
+            See <xref linkend="partitioning-columns-list"/>.
           </para>
 
         </formalpara>

@@ -611,8 +609,10 @@
 
     <para>
       A very common use of database partitioning is to segregate data by
-      date. It is not difficult in MySQL to create partitioning schemes
-      based on <literal role="type">DATE</literal>,
+      date. Some database systems support explicit date partitioning,
+      which MySQL does not implement in &current-series;. However, it is
+      not difficult in MySQL to create partitioning schemes based on
+      <literal role="type">DATE</literal>,
       <literal role="type">TIME</literal>, or
       <literal role="type">DATETIME</literal> columns, or based on
       expressions making use of such columns.

@@ -703,8 +703,8 @@
     <para>
       MySQL partitioning is optimized for use with the
       <literal role="func">TO_DAYS()</literal>,
-      <literal role="func">YEAR()</literal>, and (in MySQL 5.5.0 and
-      later) <literal role="func">TO_SECONDS()</literal> functions.
+      <literal role="func">YEAR()</literal>, and (beginning with MySQL
+      5.5.0) <literal role="func">TO_SECONDS()</literal> functions.
       However, you can use other date and time functions that return an
       integer or <literal>NULL</literal>, such as
       <literal role="func">WEEKDAY()</literal>,

@@ -991,6 +991,50 @@
       </para>
 
       <para>
+        It is also possible to partition a table by
+        <literal>RANGE</literal>, based on the value of a
+        <literal role="type">TIMESTAMP</literal> column, using the
+        <literal role="func">UNIX_TIMESTAMP()</literal> function, as
+        shown in this example:
+      </para>
+
+<programlisting>
+CREATE TABLE quarterly_report_status (
+    report_id INT NOT NULL,
+    report_status VARCHAR(20) NOT NULL,
+    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+)
+PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
+    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
+    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
+    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
+    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
+    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
+    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
+    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
+    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
+    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
+    PARTITION p9 VALUES LESS THAN (MAXVALUE)
+);
+</programlisting>
+
+      <para>
+        Any other expressions involving
+        <literal role="type">TIMESTAMP</literal> values are disallowed.
+        (See Bug#42849.)
+      </para>
+
+      <note>
+        <para>
+          It is also possible in MySQL 6.0.14 and later to use
+          <literal role="func">UNIX_TIMESTAMP(timestamp_column)</literal>
+          as a partitioning expression for tables that are partitioned
+          by <literal>LIST</literal>. However, it is usually not
+          practical to do so.
+        </para>
+      </note>
+
+      <para>
         Range partitioning is particularly useful when:
       </para>
 

@@ -1391,10 +1435,10 @@
 
       <para>
         You can cause this type of error to be ignored by using the
-        <literal>IGNORE</literal> key word. If you do so, rows
-        containing unmatched partitioning column values are not
-        inserted, but any rows with matching values
-        <emphasis>are</emphasis> inserted, and no errors are reported:
+        <literal>IGNORE</literal> keyword. If you do so, rows containing
+        unmatched partitioning column values are not inserted, but any
+        rows with matching values <emphasis>are</emphasis> inserted, and
+        no errors are reported:
       </para>
 
 <programlisting>

@@ -2732,11 +2776,8 @@
         Partitioning by key is similar to partitioning by hash, except
         that where hash partitioning employs a user-defined expression,
         the hashing function for key partitioning is supplied by the
-        MySQL server. MySQL Cluster uses
-        <literal role="func">MD5()</literal> for this purpose; for
-        tables using other storage engines, the server employs its own
-        internal hashing function which is based on the same algorithm
-        as <literal role="func">PASSWORD()</literal>.
+        MySQL server. This internal hashing function is based on the
+        same algorithm as <literal role="func">PASSWORD()</literal>.
       </para>
 
       <para>

@@ -2846,32 +2887,13 @@
             <xref linkend="partitioning-limitations"/>.
           </para>
 
-          <note>
-            <para>
-              Tables using the <literal role="se">NDBCLUSTER</literal>
-              storage engine are implicitly partitioned by
-              <literal>KEY</literal>, again using the table&apos;s
-              primary key as the partitioning key. In the event that the
-              Cluster table has no explicit primary key, the
-              <quote>hidden</quote> primary key generated by the
-              <literal role="se">NDBCLUSTER</literal> storage engine for
-              each MySQL Cluster table is used as the partitioning key.
-            </para>
-          </note>
-
           <important>
             <para>
-              For a key-partitioned table using any MySQL storage engine
-              other than <literal role="se">NDBCLUSTER</literal>, you
-              cannot execute an <literal>ALTER TABLE DROP PRIMARY
-              KEY</literal>, as doing so generates the error
-              <errortext>ERROR 1466 (HY000): Field in list of fields for
-              partition function not found in table</errortext>. This is
-              not an issue for MySQL Cluster tables which are
-              partitioned by <literal>KEY</literal>; in such cases, the
-              table is reorganized using the <quote>hidden</quote>
-              primary key as the table's new partitioning key. See
-              <xref linkend="mysql-cluster"/>.
+              For a key-partitioned table, you cannot execute an
+              <literal>ALTER TABLE DROP PRIMARY KEY</literal>, as doing
+              so generates the error <errortext>ERROR 1466 (HY000):
+              Field in list of fields for partition function not found
+              in table</errortext>.
             </para>
           </important>
         </listitem>

@@ -3297,10 +3319,10 @@
         user-supplied expression. Even though it is permitted to use
         <literal>NULL</literal> as the value of an expression that must
         otherwise yield an integer, it is important to keep in mind that
-        <literal>NULL</literal> is not a number. The partitioning
-        implementation treats <literal>NULL</literal> as being less than
-        any non-<literal>NULL</literal> value, just as <literal>ORDER
-        BY</literal> does.
+        <literal>NULL</literal> is not a number. MySQL&apos;s
+        partitioning implementation treats <literal>NULL</literal> as
+        being less than any non-<literal>NULL</literal> value, just as
+        <literal>ORDER BY</literal> does.
       </para>
 
       <para>

@@ -3955,16 +3977,6 @@
 Query OK, 0 rows affected (0.03 sec)
 </programlisting>
 
-      <note>
-        <para>
-          The <literal role="se">NDBCLUSTER</literal> storage engine
-          does not support <literal>ALTER TABLE ... DROP
-          PARTITION</literal>. It does, however, support the other
-          partitioning-related extensions to <literal role="stmt">ALTER
-          TABLE</literal> that are described in this chapter.
-        </para>
-      </note>
-
       <para>
         It is very important to remember that, <emphasis>when you drop a
         partition, you also delete all the data that was stored in that

@@ -5210,17 +5222,9 @@
       beginning with MySQL 5.5.0, pruning can be applied for such tables
       when the partitioning expression uses the
       <literal role="func">TO_SECONDS()</literal> function.
+    </para>
 
-      <note>
-        <para>
-          We plan to add pruning support in future MySQL releases for
-          additional functions that act on a
-          <literal role="type">DATE</literal> or
-          <literal role="type">DATETIME</literal> value, return an
-          integer, and are increasing or decreasing.
-        </para>
-      </note>
-
+    <para>
       Suppose that table <literal>t2</literal>, defined as shown here,
       is partitioned on a <literal role="type">DATE</literal> column:
     </para>

@@ -6764,37 +6768,11 @@
 
       <formalpara>
 
-        <title><literal role="se">NDBCLUSTER</literal> storage engine (MySQL Cluster)</title>
-
-        <para>
-          Partitioning by <literal>KEY</literal> (or <literal>LINEAR
-          KEY</literal>) is the only type of partitioning supported for
-          the <literal role="se">NDBCLUSTER</literal> storage engine. It
-          is not possible to create a MySQL Cluster table using any
-          partitioning type other than [<literal>LINEAR</literal>]
-          <literal>KEY</literal>, and attempting to do so fails with an
-          error.
-        </para>
-
-      </formalpara>
-
-      <para>
-        In addition, the maximum number of partitions that can be
-        defined for an <literal role="se">NDBCLUSTER</literal> table is
-        8 times the number of node groups in the cluster. (See
-        <xref linkend="mysql-cluster-nodes-groups"/>, for more
-        information about node groups in MySQL Cluster.)
-      </para>
-
-      <formalpara>
-
         <title>Upgrading partitioned tables</title>
 
         <para>
           When performing an upgrade, tables which are partitioned by
-          <literal>KEY</literal> and which use any storage engine other
-          than <literal role="se">NDBCLUSTER</literal> must be dumped
-          and reloaded.
+          <literal>KEY</literal> must be dumped and reloaded.
         </para>
 
       </formalpara>


Modified: trunk/refman-5.5/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.5/sql-syntax-data-definition.xml	2010-06-07 19:55:03 UTC (rev 21093)
+++ trunk/refman-5.5/sql-syntax-data-definition.xml	2010-06-07 20:16:09 UTC (rev 21094)
Changed blocks: 1, Lines Added: 12, Lines Deleted: 13; 2009 bytes

@@ -755,19 +755,18 @@
       <listitem>
         <para>
           <literal>ALTER TABLE ... ADD PARTITION</literal> creates no
-          temporary table except for MySQL Cluster.
-          <literal>ADD</literal> or <literal>DROP</literal> operations
-          for <literal>RANGE</literal> or <literal>LIST</literal>
-          partitions are immediate operations or nearly so.
-          <literal>ADD</literal> or <literal>COALESCE</literal>
-          operations for <literal>HASH</literal> or
-          <literal>KEY</literal> partitions copy data between changed
-          partitions; unless <literal>LINEAR HASH</literal> or
-          <literal>LINEAR KEY</literal> was used, this is much the same
-          as creating a new table (although the operation is done
-          partition by partition). <literal>REORGANIZE</literal>
-          operations copy only changed partitions and do not touch
-          unchanged ones.
+          temporary table. <literal>ADD</literal> or
+          <literal>DROP</literal> operations for
+          <literal>RANGE</literal> or <literal>LIST</literal> partitions
+          are immediate operations or nearly so. <literal>ADD</literal>
+          or <literal>COALESCE</literal> operations for
+          <literal>HASH</literal> or <literal>KEY</literal> partitions
+          copy data between changed partitions; unless <literal>LINEAR
+          HASH</literal> or <literal>LINEAR KEY</literal> was used, this
+          is much the same as creating a new table (although the
+          operation is done partition by partition).
+          <literal>REORGANIZE</literal> operations copy only changed
+          partitions and do not touch unchanged ones.
         </para>
       </listitem>
 


Modified: trunk/refman-6.0/functions-core.xml
===================================================================
--- trunk/refman-6.0/functions-core.xml	2010-06-07 19:55:03 UTC (rev 21093)
+++ trunk/refman-6.0/functions-core.xml	2010-06-07 20:16:09 UTC (rev 21094)
Changed blocks: 1, Lines Added: 106, Lines Deleted: 0; 4062 bytes

@@ -10841,6 +10841,112 @@
       </listitem>
 
       <listitem>
+        <remark role="help-topic" condition="TO_SECONDS"/>
+
+        <remark role="help-syntax-begin"/>
+
+        <para id="function_to-seconds">
+          <indexterm>
+            <primary>TO_SECONDS()</primary>
+          </indexterm>
+
+          <literal role="func">TO_SECONDS(<replaceable>expr</replaceable>)</literal>
+        </para>
+
+        <remark role="help-syntax-end"/>
+
+        <remark role="help-description-begin"/>
+
+        <para>
+          Given a date or datetime <replaceable>expr</replaceable>,
+          returns a the number of seconds since the year 0. If
+          <replaceable>expr</replaceable> is not a valid date or
+          datetime value, returns <literal>NULL</literal>.
+        </para>
+
+        <remark role="help-description-end"/>
+
+        <remark role="help-example"/>
+
+<programlisting>
+mysql&gt; <userinput>SELECT TO_SECONDS(950501);</userinput>
+        -&gt; 62966505600
+mysql&gt; <userinput>SELECT TO_SECONDS('2009-11-29');</userinput>
+        -&gt; 63426672000
+mysql&gt; <userinput>SELECT TO_SECONDS('2009-11-29 13:43:32');</userinput>
+        -&gt; 63426721412
+mysql&gt; <userinput>SELECT TO_SECONDS( NOW() );</userinput>
+        -&gt; 63426721458
+</programlisting>
+
+        <para>
+          Like <literal role="func">TO_DAYS()</literal>,
+          <literal>TO_SECONDS()</literal> is not intended for use with
+          values that precede the advent of the Gregorian calendar
+          (1582), because it does not take into account the days that
+          were lost when the calendar was changed. For dates before 1582
+          (and possibly a later year in other locales), results from
+          this function are not reliable. See
+          <xref linkend="mysql-calendar"/>, for details.
+        </para>
+
+        <para>
+          Like <literal role="func">TO_DAYS()</literal>,
+          <literal>TO_SECONDS()</literal>, converts two-digit year
+          values in dates to four-digit form using the rules in
+          <xref linkend="date-and-time-types"/>.
+        </para>
+
+        <para>
+          <literal>TO_SECONDS()</literal> is available beginning with
+          MySQL 6.0.14.
+        </para>
+
+        <para>
+          In MySQL, the zero date is defined as
+          <literal>'0000-00-00'</literal>, even though this date is
+          itself considered invalid. This means that, for
+          <literal>'0000-00-00'</literal> and
+          <literal>'0000-01-01'</literal>,
+          <literal role="func">TO_SECONDS()</literal> returns the values
+          shown here:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT TO_SECONDS('0000-00-00');</userinput>
++--------------------------+
+| TO_SECONDS('0000-00-00') |
++--------------------------+
+|                     NULL |
++--------------------------+
+1 row in set, 1 warning (0.00 sec)
+
+mysql&gt; <userinput>SHOW WARNINGS;</userinput>
++---------+------+----------------------------------------+
+| Level   | Code | Message                                |
++---------+------+----------------------------------------+
+| Warning | 1292 | Incorrect datetime value: '0000-00-00' |
++---------+------+----------------------------------------+
+1 row in set (0.00 sec)
+
+
+mysql&gt; <userinput>SELECT TO_SECONDS('0000-01-01');</userinput>
++--------------------------+
+| TO_SECONDS('0000-01-01') |
++--------------------------+
+|                    86400 |
++--------------------------+
+1 row in set (0.00 sec)
+</programlisting>
+
+        <para>
+          This is true whether or not the
+          <literal role="sqlmode">ALLOW_INVALID_DATES</literal> SQL
+          server mode is enabled.
+        </para>
+      </listitem>
+
+      <listitem>
         <remark role="help-topic" condition="UNIX_TIMESTAMP"/>
 
         <remark role="help-syntax-begin"/>


Modified: trunk/refman-6.0/partitioning.xml
===================================================================
--- trunk/refman-6.0/partitioning.xml	2010-06-07 19:55:03 UTC (rev 21093)
+++ trunk/refman-6.0/partitioning.xml	2010-06-07 20:16:09 UTC (rev 21094)
Changed blocks: 27, Lines Added: 1217, Lines Deleted: 110; 58923 bytes

@@ -82,7 +82,7 @@
   </para>
 
   <para>
-    Community binaries provided by Oracle Corporation include
+    MySQL Community binaries provided by Oracle Corporation include
     partitioning support. For information about partitioning support
     offered in commercial MySQL Server binaries, see
     <ulink url="&base-url-enterprise;server.html"><citetitle>MySQL

@@ -290,14 +290,19 @@
       use an SQL expression that is valid in MySQL, as long as that
       expression returns either <literal>NULL</literal> or an integer
       <replaceable>intval</replaceable> such that
+    </para>
 
 <programlisting>
 -MAXVALUE &lt;= <replaceable>intval</replaceable> &lt;= MAXVALUE
 </programlisting>
 
+    <para>
       (<literal>MAXVALUE</literal> is used to represent the least upper
       bound for the type of integer in question.
       <literal>-MAXVALUE</literal> represents the greatest lower bound.)
+    </para>
+
+    <para>
       There are some additional restrictions on partitioning functions;
       see <xref linkend="partitioning-limitations"/>, for more
       information about these.

@@ -323,6 +328,12 @@
     </para>
 
     <para>
+      For information about determining whether your MySQL Server binary
+      supports user-defined partitioning, see
+      <xref linkend="partitioning"/>.
+    </para>
+
+    <para>
       For creating partitioned tables, you can use most storage engines
       that are supported by your MySQL server; the MySQL partitioning
       engine runs in a separate layer and can interact with any of

@@ -338,8 +349,8 @@
     <note>
       <para>
         MySQL partitioning cannot be used with the
-        <literal>MERGE</literal> or <literal>CSV</literal> storage
-        engines.
+        <literal>MERGE</literal>, <literal>CSV</literal>, or
+        <literal>FEDERATED</literal> storage engines.
       </para>
     </note>
 

@@ -522,7 +533,10 @@
           <para>
             This type of partitioning assigns rows to partitions based
             on column values falling within a given range. See
-            <xref linkend="partitioning-range"/>.
+            <xref linkend="partitioning-range"/>. Beginning with MySQL
+            6.0.14, an extension to this type, <literal>RANGE
+            COLUMNS</literal>, is also supported. See
+            <xref linkend="partitioning-columns-range"/>.
           </para>
 
         </formalpara>

@@ -537,7 +551,10 @@
             Similar to partitioning by <literal>RANGE</literal>, except
             that the partition is selected based on columns matching one
             of a set of discrete values. See
-            <xref linkend="partitioning-list"/>.
+            <xref linkend="partitioning-list"/>. Beginning with MySQL
+            6.0.14, an extension to this type, <literal>LIST
+            COLUMNS</literal>, is also supported. See
+            <xref linkend="partitioning-columns-list"/>.
           </para>
 
         </formalpara>

@@ -595,10 +612,8 @@
 
     <para>
       A very common use of database partitioning is to segregate data by
-      date. Some database systems support explicit date partitioning,
-      which MySQL does not implement in &current-series;. However, it is
-      not difficult in MySQL to create partitioning schemes based on
-      <literal role="type">DATE</literal>,
+      date. It is not difficult in MySQL to create partitioning schemes
+      based on <literal role="type">DATE</literal>,
       <literal role="type">TIME</literal>, or
       <literal role="type">DATETIME</literal> columns, or based on
       expressions making use of such columns.

@@ -627,35 +642,19 @@
 </programlisting>
 
     <para>
+      Beginning with MySQL 6.0.14, it is also possible to use a
+      <literal role="type">DATE</literal> or
+      <literal role="type">DATETIME</literal> column as the partitioning
+      column using <literal>RANGE COLUMNS</literal> and <literal>LIST
+      COLUMNS</literal> partitioning.
+    </para>
+
+    <para>
       MySQL's other partitioning types, however, require a partitioning
       expression that yields an integer value or
-      <literal>NULL</literal>. If you wish to use date-based
-      partitioning by <literal>RANGE</literal>, <literal>LIST</literal>,
-      <literal>HASH</literal>, or <literal>LINEAR HASH</literal>, you
-      can simply employ a function that operates on a
-      <literal role="type">DATE</literal>,
-      <literal role="type">TIME</literal>, or
-      <literal role="type">DATETIME</literal> column and returns such a
-      value, as shown here:
+      <literal>NULL</literal>.
     </para>
 
-<programlisting>
-CREATE TABLE members (
-    firstname VARCHAR(25) NOT NULL,
-    lastname VARCHAR(25) NOT NULL,
-    username VARCHAR(16) NOT NULL,
-    email VARCHAR(35),
-    joined DATE NOT NULL
-)
-PARTITION BY RANGE( YEAR(joined) ) (
-    PARTITION p0 VALUES LESS THAN (1960),
-    PARTITION p1 VALUES LESS THAN (1970),
-    PARTITION p2 VALUES LESS THAN (1980),
-    PARTITION p3 VALUES LESS THAN (1990),
-    PARTITION p4 VALUES LESS THAN MAXVALUE
-);
-</programlisting>
-
     <para>
       Additional examples of partitioning using dates may be found here:
     </para>

@@ -704,10 +703,11 @@
 
     <para>
       MySQL partitioning is optimized for use with the
-      <literal role="func">TO_DAYS()</literal> and
-      <literal role="func">YEAR()</literal> functions. However, you can
-      use other date and time functions that return an integer or
-      <literal>NULL</literal>, such as
+      <literal role="func">TO_DAYS()</literal>,
+      <literal role="func">YEAR()</literal>, and (beginning with MySQL
+      6.0.14) <literal role="func">TO_SECONDS()</literal> functions.
+      However, you can use other date and time functions that return an
+      integer or <literal>NULL</literal>, such as
       <literal role="func">WEEKDAY()</literal>,
       <literal role="func">DAYOFYEAR()</literal>, or
       <literal role="func">MONTH()</literal>. See

@@ -859,7 +859,7 @@
         using a <quote>catchall</quote> <literal>VALUES LESS
         THAN</literal> clause in the <literal role="stmt">CREATE
         TABLE</literal> statement that provides for all values greater
-        than highest value explicitly named:
+        than the highest value explicitly named:
       </para>
 
 <programlisting>

@@ -913,8 +913,8 @@
         example&mdash;assuming that two-digit job codes are used for
         regular (in-store) workers, three-digit codes are used for
         office and support personnel, and four-digit codes are used for
-        management positions&mdash;you could create the partitioned
-        table using the following:
+        management positions&mdash;you could create this partitioned
+        table using the following statement:
       </para>
 
 <programlisting>

@@ -1082,6 +1082,144 @@
 
       </itemizedlist>
 
+      <para>
+        A variant on this type of partitioning, <literal>RANGE
+        COLUMNS</literal> partitioning, was introduced in MySQL 6.0.14.
+        Partitioning by <literal>RANGE COLUMNS</literal> makes it
+        possible to employ multiple columns for defining partitioning
+        ranges that apply both to placement of rows in partitions and
+        for determining the inclusion or exclusion of specific
+        partitions when performing partition pruning. See
+        <xref linkend="partitioning-columns-range"/>, for more
+        information.
+      </para>
+
+      <formalpara id="partitioning-time-intervals">
+
+        <title>Partitioning schemes based on time intervals</title>
+
+        <para>
+          If you wish to implement a partitioning scheme based on ranges
+          or intervals of time in MySQL &current-series;, you have two
+          options:
+        </para>
+
+      </formalpara>
+
+      <orderedlist>
+
+        <listitem>
+          <para>
+            Partition the table by <literal>RANGE</literal>, and for the
+            partitioning expression, employ a function operating on a
+            <literal role="type">DATE</literal>,
+            <literal role="type">TIME</literal>, or
+            <literal role="type">DATETIME</literal> column and returning
+            an integer value, as shown here:
+          </para>
+
+<programlisting>
+CREATE TABLE members (
+    firstname VARCHAR(25) NOT NULL,
+    lastname VARCHAR(25) NOT NULL,
+    username VARCHAR(16) NOT NULL,
+    email VARCHAR(35),
+    joined DATE NOT NULL
+)
+PARTITION BY RANGE( YEAR(joined) ) (
+    PARTITION p0 VALUES LESS THAN (1960),
+    PARTITION p1 VALUES LESS THAN (1970),
+    PARTITION p2 VALUES LESS THAN (1980),
+    PARTITION p3 VALUES LESS THAN (1990),
+    PARTITION p4 VALUES LESS THAN MAXVALUE
+);
+</programlisting>
+
+          <para>
+            Beginning with MySQL 5.5.1, it is also possible to partition
+            a table by <literal>RANGE</literal> based on the value of a
+            <literal role="type">TIMESTAMP</literal> column, using the
+            <literal role="func">UNIX_TIMESTAMP()</literal> function, as
+            shown in this example:
+          </para>
+
+<programlisting>
+CREATE TABLE quarterly_report_status (
+    report_id INT NOT NULL,
+    report_status VARCHAR(20) NOT NULL,
+    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+)
+PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
+    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
+    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
+    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
+    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
+    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
+    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
+    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
+    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
+    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
+    PARTITION p9 VALUES LESS THAN (MAXVALUE)
+);
+</programlisting>
+
+          <para>
+            Also beginning with MySQL 6.0.14, any other expressions
+            involving <literal role="type">TIMESTAMP</literal> values
+            are disallowed. (See Bug#42849.)
+          </para>
+
+          <note>
+            <para>
+              It is also possible in MySQL 6.0.14 and later to use
+              <literal role="func">UNIX_TIMESTAMP(timestamp_column)</literal>
+              as a partitioning expression for tables that are
+              partitioned by <literal>LIST</literal>. However, it is
+              usually not practical to do so.
+            </para>
+          </note>
+        </listitem>
+
+        <listitem>
+          <para>
+            Partition the table by <literal>RANGE COLUMNS</literal>,
+            using a <literal role="type">DATE</literal> or
+            <literal role="type">DATETIME</literal> column as the
+            partitioning column (available starting with MySQL 6.0.14).
+            For example, the <literal>members</literal> table could be
+            defined using the <literal>joined</literal> column directly,
+            as shown here:
+          </para>
+
+<programlisting>
+CREATE TABLE members (
+    firstname VARCHAR(25) NOT NULL,
+    lastname VARCHAR(25) NOT NULL,
+    username VARCHAR(16) NOT NULL,
+    email VARCHAR(35),
+    joined DATE NOT NULL
+)
+PARTITION BY RANGE COLUMNS(joined) (
+    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
+    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
+    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
+    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
+    PARTITION p4 VALUES LESS THAN MAXVALUE
+);
+</programlisting>
+        </listitem>
+
+      </orderedlist>
+
+      <note>
+        <para>
+          The use of partitioning columns employing date or time types
+          other than <literal role="type">DATE</literal> or
+          <literal role="type">DATETIME</literal> is not supported with
+          <literal>RANGE COLUMNS</literal>.
+        </para>
+      </note>
+
     </section>
 
     <section id="partitioning-list">

@@ -1123,6 +1261,13 @@
           <xref linkend="partitioning-handling-nulls"/>) when
           partitioning by <literal>LIST</literal>.
         </para>
+
+        <para>
+          However, beginning with MySQL 6.0.14, other column types may
+          be used in value lists when employing <literal>LIST
+          COLUMN</literal> partitioning, which is described later in
+          this section.
+        </para>
       </note>
 
       <para>

@@ -1219,12 +1364,19 @@
         This makes it easy to add or drop employee records relating to
         specific regions to or from the table. For instance, suppose
         that all stores in the West region are sold to another company.
-        All rows relating to employees working at stores in that region
-        can be deleted with the query <literal>ALTER TABLE employees
-        DROP PARTITION pWest;</literal>, which can be executed much more
-        efficiently than the equivalent
-        <literal role="stmt">DELETE</literal> statement <literal>DELETE
-        FROM employees WHERE store_id IN (4,12,13,14,18);</literal>.
+        Beginning with MySQL 6.0.14, all rows relating to employees
+        working at stores in that region can be deleted with the query
+        <literal>ALTER TABLE employees TRUNCATE PARTITION
+        pWest</literal>, which can be executed much more efficiently
+        than the equivalent <literal role="stmt">DELETE</literal>
+        statement <literal>DELETE FROM employees WHERE store_id IN
+        (4,12,13,14,18);</literal>. (Using <literal>ALTER TABLE
+        employees DROP PARTITION pWest</literal> would also delete all
+        of these rows, but would also remove the partition
+        <literal>pWest</literal> from the definition of the table; you
+        would need to use an <literal>ALTER TABLE ... ADD
+        PARTITION</literal> statement to restore the table&apos;s
+        original partitioning scheme.)
       </para>
 
       <para>

@@ -1285,10 +1437,10 @@
 
       <para>
         You can cause this type of error to be ignored by using the
-        <literal>IGNORE</literal> key word. If you do so, rows
-        containing unmatched partitioning column values are not
-        inserted, but any rows with matching values
-        <emphasis>are</emphasis> inserted, and no errors are reported:
+        <literal>IGNORE</literal> keyword. If you do so, rows containing
+        unmatched partitioning column values are not inserted, but any
+        rows with matching values <emphasis>are</emphasis> inserted, and
+        no errors are reported:
       </para>
 
 <programlisting>

@@ -1313,8 +1465,894 @@
 3 rows in set (0.00 sec)
 </programlisting>
 
+      <para>
+        Beginning with MySQL 6.0.14, MySQL provides support for
+        <literal>LIST COLUMNS</literal> partitioning. This is a variant
+        of <literal>LIST</literal> partitioning that allows you to use
+        columns of types other than integer types for partitioning
+        columns, as well as to use multiple columns as partitioning
+        keys. For more information, see
+        <xref linkend="partitioning-columns-list"/>.
+      </para>
+
     </section>
 
+    <section id="partitioning-columns">
+
+      <title><literal>COLUMNS</literal> Partitioning</title>
+
+      <indexterm>
+        <primary>partitioning</primary>
+        <secondary>COLUMNS</secondary>
+      </indexterm>
+
+      <indexterm>
+        <primary>columns partitioning</primary>
+      </indexterm>
+
+      <indexterm>
+        <primary>PARTITION BY RANGE COLUMNS</primary>
+      </indexterm>
+
+      <indexterm>
+        <primary>PARTITION BY LIST COLUMNS</primary>
+      </indexterm>
+
+      <indexterm>
+        <seealso>range partitioning</seealso>
+      </indexterm>
+
+      <indexterm>
+        <seealso>list partitioning</seealso>
+      </indexterm>
+
+      <para>
+        The next two sections discuss
+        <firstterm><literal>COLUMNS</literal> partitioning</firstterm>,
+        which are variants on <literal>RANGE</literal> and
+        <literal>LIST</literal> partitioning that were introduced in
+        MySQL 6.0.14. <literal>COLUMNS</literal> partitioning allows the
+        use of multiple columns in partitioning keys. The multiple
+        columns are taken into account both for the purpose of placing
+        rows in partitions and for the determination of which partitions
+        are to be checked for matching rows in partition pruning.
+      </para>
+
+      <para>
+        In addition, both <literal>RANGE COLUMNS</literal> partitioning
+        and <literal>LIST COLUMNS</literal> partitioning support the use
+        of non-integer columns for defining value ranges or list
+        members. The permitted data types are shown in the following
+        list:
+      </para>
+
+      <itemizedlist>
+
+        <listitem>
+          <para>
+            All integer types: <literal role="type">TINYINT</literal>,
+            <literal role="type">SMALLINT</literal>,
+            <literal role="type">MEDIUMINT</literal>,
+            <literal role="type">INT</literal>
+            (<literal role="type">INTEGER</literal>), and
+            <literal role="type">BIGINT</literal>. (This is the same as
+            with partitioning by <literal>RANGE</literal> and
+            <literal>LIST</literal>.)
+          </para>
+
+          <para>
+            Other numeric data types (such as
+            <literal role="type">DECIMAL</literal> or
+            <literal role="type">FLOAT</literal>) are not supported as
+            partitioning columns.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            <literal role="type">DATE</literal> and
+            <literal role="type">DATETIME</literal>.
+          </para>
+
+          <para>
+            Columns using other data types relating to dates or times
+            are not supported as partitioning columns.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            The following string types:
+            <literal role="type">CHAR</literal>,
+            <literal role="type">VARCHAR</literal>,
+            <literal role="type">BINARY</literal>, and
+            <literal role="type">VARBINARY</literal>.
+          </para>
+
+          <para>
+            <literal role="type">TEXT</literal> and
+            <literal role="type">BLOB</literal> columns are not
+            supported as partitioning columns.
+          </para>
+        </listitem>
+
+      </itemizedlist>
+
+      <para>
+        The discussions of <literal>RANGE COLUMNS</literal> and
+        <literal>LIST COLUMNS</literal> partitioning in the next two
+        sections assume that you are already familiar with partitioning
+        based on ranges and lists as supported in MySQL 5.1 and later;
+        for more information about these, see
+        <xref linkend="partitioning-range"/>, and
+        <xref linkend="partitioning-list"/>, respectively.
+      </para>
+
+      <section id="partitioning-columns-range">
+
+        <title>Range columns partitioning</title>
+
+        <para>
+          Range columns partitioning is similar to range partitioning,
+          but allows you to define partitions using ranges based on
+          multiple column values. In addition, you can define the ranges
+          using columns of types other than integer types.
+        </para>
+
+        <para>
+          <literal>RANGE COLUMNS</literal> partitioning differs
+          significantly from <literal>RANGE</literal> partitioning in
+          the following ways:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              <literal>RANGE COLUMNS</literal> does not accept
+              expressions, only names of columns.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>RANGE COLUMNS</literal> accepts a list of one or
+              more columns.
+            </para>
+
+            <para>
+              <literal>RANGE COLUMNS</literal> partitions are based on
+              comparisons between <firstterm>tuples</firstterm> (lists
+              of column values) rather than comparisons between scalar
+              values. Placement of rows in <literal>RANGE
+              COLUMNS</literal> partitions is also based on comparisons
+              between tuples; this is discussed further later in this
+              section.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>RANGE COLUMNS</literal> partitioning columns are
+              not restricted to integer columns; string,
+              <literal role="type">DATE</literal> and
+              <literal role="type">DATETIME</literal> columns can also
+              be used as partitioning columns. (See
+              <xref linkend="partitioning-columns"/>, for details.)
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          The basic syntax for creating a table partitioned by
+          <literal>RANGE COLUMNS</literal> is shown here:
+        </para>
+
+<programlisting>
+CREATE TABLE <replaceable>table_name</replaceable>
+PARTITIONED BY RANGE COLUMNS(<replaceable>column_list</replaceable>) (
+    PARTITION <replaceable>partition_name</replaceable> VALUES LESS THAN (<replaceable>value_list</replaceable>)[,
+    PARTITION <replaceable>partition_name</replaceable> VALUES LESS THAN (<replaceable>value_list</replaceable>)][,
+    ...]
+) 
+
+<replaceable>column_list</replaceable>:
+    <replaceable>column_name</replaceable>[, <replaceable>column_name</replaceable>][, ...]
+
+<replaceable>value_list</replaceable>:
+    <replaceable>value</replaceable>[, <replaceable>value</replaceable>][, ...]
+</programlisting>
+
+        <note>
+          <para>
+            Not all <literal role="stmt">CREATE TABLE</literal> options
+            that can be used when creating partitioned tables are shown
+            here. For complete information, see
+            <xref linkend="create-table"/>.
+          </para>
+        </note>
+
+        <para>
+          In the syntax just shown,
+          <replaceable>column_list</replaceable> is a list of one or
+          more columns (sometimes called a <firstterm>partitioning
+          column list</firstterm>), and
+          <replaceable>value_list</replaceable> is a list of values
+          (that is, it is a <firstterm>partition definition value
+          list</firstterm>). A <replaceable>value_list</replaceable>
+          must be supplied for each partition definition, and each
+          <replaceable>value_list</replaceable> must have the same
+          number of values as the <replaceable>column_list</replaceable>
+          has columns. Generally speaking, if you use
+          <replaceable>N</replaceable> columns in the
+          <literal>COLUMNS</literal> clause, then each <literal>VALUES
+          LESS THAN</literal> clause must also be supplied with a list
+          of <replaceable>N</replaceable> values.
+        </para>
+
+        <para>
+          The elements in the partitioning column list and in the value
+          list defining each partition must occur in the same order. In
+          addition, each element in the value list must be of the same
+          data type as the corresponding element in the column list.
+          However, the order of the column names in the partitioning
+          column list and the value lists does not have to be the same
+          as the order of the table column definitions in the main part
+          of the <literal role="stmt">CREATE TABLE</literal> statement.
+          As with table partitioned by <literal>RANGE</literal>, you can
+          use <literal>MAXVALUE</literal> to represent a value such that
+          any legal value inserted into a given column is always less
+          than this value. Here is an example of a
+          <literal role="stmt">CREATE TABLE</literal> statement that
+          helps to illustrate all of these points:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>CREATE TABLE rcx (</userinput>
+    -&gt;     <userinput>a INT,</userinput>
+    -&gt;     <userinput>b INT,</userinput>
+    -&gt;     <userinput>c CHAR(3),</userinput>
+    -&gt;     <userinput>d INT</userinput>
+    -&gt; <userinput>)</userinput>
+    -&gt; <userinput>PARTITION BY RANGE COLUMNS(a,d,c) (</userinput>
+    -&gt;     <userinput>PARTITION p0 VALUES LESS THAN (5,10,'ggg'),</userinput>
+    -&gt;     <userinput>PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),</userinput>
+    -&gt;     <userinput>PARTITION p2 VALUES LESS THAN (15,30,'sss'),</userinput>
+    -&gt;     <userinput>PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)</userinput>
+    -&gt; <userinput>);</userinput>
+Query OK, 0 rows affected (0.15 sec)
+</programlisting>
+
+        <para>
+          Table <literal>rcx</literal> contains the columns
+          <literal>a</literal>, <literal>b</literal>,
+          <literal>c</literal>, <literal>d</literal>. The partitioning
+          column list supplied to the <literal>COLUMNS</literal> clause
+          uses 3 of these columns, in the order <literal>a</literal>,
+          <literal>d</literal>, <literal>c</literal>. Each value list
+          used to define a partition contains 3 values in the same
+          order; that is, each value list tuple has the form
+          (<literal>INT</literal>, <literal>INT</literal>,
+          <literal>CHAR(3)</literal>), which corresponds to the data
+          types used by columns <literal>a</literal>,
+          <literal>d</literal>, and <literal>c</literal> (in that
+          order).
+        </para>
+
+        <para>
+          Placement of rows into partitions is determined by comparing
+          the tuple from a row to be inserted that matches the column
+          list in the <literal>COLUMNS</literal> clause with the tuples
+          used in the <literal>VALUES LESS THAN</literal> clauses to
+          define partitions of the table. Because we are comparing
+          tuples (that is, lists or sets of values) rather than scalar
+          values, the semantics of <literal>VALUES LESS THAN</literal>
+          as used with <literal>RANGE COLUMNS</literal> partitions
+          differs somewhat from the case with simple
+          <literal>RANGE</literal> partitions. In
+          <literal>RANGE</literal> partitioning, a row generating an
+          expression value that is equal to a limiting value in a
+          <literal>VALUES LESS THAN</literal> is never placed in the
+          corresponding partition; however, when using <literal>RANGE
+          COLUMNS</literal> partitioning, it is sometimes possible for a
+          row whose partitioning partitioning column list&apos;s first
+          element is equal in value to the that of the first element in
+          a <literal>VALUES LESS THAN</literal> value list to be placed
+          in the corresponding partition.
+        </para>
+
+        <para>
+          For example, consider the <literal>RANGE</literal> partitioned
+          table defined by this <literal>CREATE TABLE</literal>
+          statement:
+        </para>
+
+<programlisting>
+CREATE TABLE r1 (
+    a INT,
+    b INT
+)
+PARTITION BY RANGE (a)  (
+    PARTITION p0 VALUES LESS THAN (5),
+    PARTITION p1 VALUES LESS THAN (MAXVALUE)
+);
+</programlisting>
+
+        <para>
+          If we insert 3 rows into this table such that the column value
+          for <literal>a</literal> is <literal>5</literal> for each row,
+          all 3 rows are stored in partition <literal>p1</literal>
+          because the <literal>a</literal> column value is in each case
+          not less than 5, as we can see by executing the proper query
+          against the
+          <literal role="is">INFORMATION_SCHEMA.PARTITIONS</literal>
+          table:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>INSERT INTO r1 VALUES (5,10), (5,11), (5,12);</userinput>
+Query OK, 3 rows affected (0.00 sec)
+Records: 3  Duplicates: 0  Warnings: 0
+
+mysql&gt; <userinput>SELECT PARTITION_NAME,TABLE_ROWS</userinput>
+    -&gt;     <userinput>FROM INFORMATION_SCHEMA.PARTITIONS</userinput>
+    -&gt;     <userinput>WHERE TABLE_NAME = 'r1';</userinput>
++----------------+------------+
+| PARTITION_NAME | TABLE_ROWS |
++----------------+------------+
+| p0             |          0 |
+| p1             |          3 |
++----------------+------------+
+2 rows in set (0.00 sec)
+</programlisting>
+
+        <para>
+          Now consider a similar table <literal>rc1</literal> that uses
+          <literal>RANGE COLUMNS</literal> partitioning with both
+          columns <literal>a</literal> and <literal>b</literal>
+          referenced in the <literal>COLUMNS</literal> clause, created
+          as shown here:
+        </para>
+
+<programlisting>
+CREATE TABLE rc1 (
+    a INT, 
+    b INT
+) 
+PARTITION BY RANGE COLUMNS(a, b) (
+    PARTITION p0 VALUES LESS THAN (5, 12),
+    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
+);
+</programlisting>
+
+        <para>
+          If we insert exactly the same rows into <literal>rc1</literal>
+          as we just inserted into <literal>r1</literal>, the
+          distribution of the rows is quite different:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);</userinput>
+Query OK, 3 rows affected (0.00 sec)
+Records: 3  Duplicates: 0  Warnings: 0
+
+mysql&gt; <userinput>SELECT PARTITION_NAME,TABLE_ROWS</userinput>
+    -&gt;     <userinput>FROM INFORMATION_SCHEMA.PARTITIONS</userinput>
+    -&gt;     <userinput>WHERE TABLE_NAME = 'rc1';</userinput>
++--------------+----------------+------------+
+| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |
++--------------+----------------+------------+
+| p            | p0             |          2 |
+| p            | p1             |          1 |
++--------------+----------------+------------+
+2 rows in set (0.00 sec)
+</programlisting>
+
+        <para>
+          This is because we are comparing rows rather than scalar
+          values. We can compare the row values inserted with the
+          limiting row value from the <literal>VALUES THAN LESS
+          THAN</literal> clause used to define partition
+          <literal>p0</literal> in table <literal>rc1</literal>, like
+          this:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT (5,10) &lt; (5,12), (5,11) &lt; (5,12), (5,12) &lt; (5,12);</userinput>
++-----------------+-----------------+-----------------+
+| (5,10) &lt; (5,12) | (5,11) &lt; (5,12) | (5,12) &lt; (5,12) |
++-----------------+-----------------+-----------------+
+|               1 |               1 |               0 |
++-----------------+-----------------+-----------------+
+1 row in set (0.00 sec)
+</programlisting>
+
+        <para>
+          The 2 tuples <literal>(5,10)</literal> and
+          <literal>(5,11)</literal> evaluate as less than
+          <literal>(5,12)</literal>, so they are stored in partition
+          <literal>p0</literal>. Since 5 is not less than 5 and 12 is
+          not less than 12, <literal>(5,12)</literal> is considered not
+          less than <literal>(5,12)</literal>, and is stored in
+          partition <literal>p1</literal>.
+        </para>
+
+        <note>
+          <para>
+            The <literal role="stmt">SELECT</literal> statement in the
+            preceding example could also have been written using
+            explicit row constructors, like this:
+          </para>
+
+<programlisting>
+SELECT ROW(5,10) &lt; ROW(5,12), ROW(5,11) &lt; ROW(5,12), ROW(5,12) &lt; ROW(5,12);
+</programlisting>
+
+          <para>
+            For more information about the use of row constructors in
+            MySQL, see <xref linkend="row-subqueries"/>.
+          </para>
+        </note>
+
+        <para>
+          For a table partitioned by <literal>RANGE COLUMNS</literal>
+          using only a single partitioning column, the storing of rows
+          in partitions is the same as that of an equivalent table that
+          is partitioned by <literal>RANGE</literal>. The following
+          <literal>CREATE TABLE</literal> statement creates a table
+          partitioned by <literal>RANGE COLUMNS</literal> using 1
+          partitioning column:
+        </para>
+
+<programlisting>
+CREATE TABLE rx (
+    a INT,
+    b INT
+)
+PARTITION BY RANGE COLUMNS (a)  (
+    PARTITION p0 VALUES LESS THAN (5),
+    PARTITION p1 VALUES LESS THAN (MAXVALUE)
+); 
+</programlisting>
+
+        <para>
+          If we insert the rows <literal>(5,10)</literal>,
+          <literal>(5,11)</literal>, and <literal>(5,12)</literal> into
+          this table, we can see that their placement is the same as it
+          is for the table <literal>r</literal> we created and populated
+          earlier:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>INSERT INTO rx VALUES (5,10), (5,11), (5,12);</userinput>
+Query OK, 3 rows affected (0.00 sec)
+Records: 3  Duplicates: 0  Warnings: 0
+
+mysql&gt; <userinput>SELECT PARTITION_NAME,TABLE_ROWS</userinput>
+    -&gt;     <userinput>FROM INFORMATION_SCHEMA.PARTITIONS</userinput>
+    -&gt;     <userinput>WHERE TABLE_NAME = 'rx';</userinput>
++--------------+----------------+------------+
+| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |
++--------------+----------------+------------+
+| p            | p0             |          0 |
+| p            | p1             |          3 |
++--------------+----------------+------------+
+2 rows in set (0.00 sec)
+</programlisting>
+
+        <para>
+          It is also possible to create tables partitioned by
+          <literal>RANGE COLUMNS</literal> where limiting values for one
+          or more columns are repeated in successive partition
+          definitions. You can do this as long as the tuples of column
+          values used to define the partitions are strictly increasing.
+          For example, each of the following <literal role="stmt">CREATE
+          TABLE</literal> statements is valid:
+        </para>
+
+<programlisting>
+CREATE TABLE rc2 (
+    a INT,
+    b INT
+)
+PARTITION BY RANGE COLUMNS(a,b) (
+    PARTITION p0 VALUES LESS THAN (0,10),
+    PARTITION p1 VALUES LESS THAN (10,20),
+    PARTITION p2 VALUES LESS THAN (10,30),
+    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
+ );
+ 
+CREATE TABLE rc3 (
+    a INT,
+    b INT
+)
+PARTITION BY RANGE COLUMNS(a,b) (
+    PARTITION p0 VALUES LESS THAN (0,10),
+    PARTITION p1 VALUES LESS THAN (10,20),
+    PARTITION p2 VALUES LESS THAN (10,30),
+    PARTITION p3 VALUES LESS THAN (10,35),
+    PARTITION p4 VALUES LESS THAN (20,40),
+    PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
+ );
+</programlisting>
+
+        <para>
+          The following statement also succeeds, even though it might
+          appear at first glance that it would not, since the limiting
+          value of column <literal>b</literal> is 25 for partition
+          <literal>p0</literal> and 20 for partition
+          <literal>p1</literal>, and the limiting value of column
+          <literal>c</literal> is 100 for partition
+          <literal>p1</literal> and 50 for partition
+          <literal>p2</literal>:
+        </para>
+
+<programlisting>
+CREATE TABLE rc4 (
+    a INT,
+    b INT,
+    c INT
+)
+PARTITION BY RANGE COLUMNS(a,b,c) (
+    PARTITION p0 VALUES LESS THAN (0,25,50),
+    PARTITION p1 VALUES LESS THAN (10,20,100),
+    PARTITION p2 VALUES LESS THAN (10,30,50)
+    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE) 
+ );
+</programlisting>
+
+        <para>
+          When designing tables partitioned by <literal>RANGE
+          COLUMNS</literal>, you can always test successive partition
+          definitions by comparing the desired tuples using the
+          <command>mysql</command> client, like this:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT (0,25,50) &lt; (10,20,100), (10,20,100) &lt; (10,30,50);</userinput>
++-------------------------+--------------------------+
+| (0,25,50) &lt; (10,20,100) | (10,20,100) &lt; (10,30,50) |
++-------------------------+--------------------------+
+|                       1 |                        1 |
++-------------------------+--------------------------+
+1 row in set (0.00 sec)
+</programlisting>
+
+        <para>
+          The following <literal role="stmt">CREATE TABLE</literal>
+          statement fails with an error:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>CREATE TABLE rcf (</userinput>
+    -&gt;     <userinput>a INT,</userinput>
+    -&gt;     <userinput>b INT,</userinput>
+    -&gt;     <userinput>c INT</userinput>
+    -&gt; <userinput>)</userinput>
+    -&gt; <userinput>PARTITION BY RANGE COLUMNS(a,b,c) (</userinput>
+    -&gt;     <userinput>PARTITION p0 VALUES LESS THAN (0,25,50),</userinput>
+    -&gt;     <userinput>PARTITION p1 VALUES LESS THAN (20,20,100),</userinput>
+    -&gt;     <userinput>PARTITION p2 VALUES LESS THAN (10,30,50),</userinput>
+    -&gt;     <userinput>PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)</userinput>
+    -&gt;  <userinput>);</userinput>
+<errortext>ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition</errortext>
+</programlisting>
+
+        <para>
+          When you get this error, you can deduce which partition
+          definitions are invalid by making <quote>less than</quote>
+          comparisons between their column lists. In this case, the
+          problem is with the definition of partition
+          <literal>p2</literal> because the tuple used to define it is
+          not less than the tuple used to define partition
+          <literal>p3</literal>, as shown here:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT (0,25,50) &lt; (20,20,100), (20,20,100) &lt; (10,30,50);</userinput>
++-------------------------+--------------------------+
+| (0,25,50) &lt; (20,20,100) | (20,20,100) &lt; (10,30,50) |
++-------------------------+--------------------------+
+|                       1 |                        0 |
++-------------------------+--------------------------+
+1 row in set (0.00 sec)
+</programlisting>
+
+        <para>
+          It is also possible for <literal>MAXVALUE</literal> to appear
+          for the same column in more than one <literal>VALUES LESS
+          THAN</literal> clause when using <literal>RANGE
+          COLUMNS</literal>. However, the limiting values for individual
+          columns in successive partition definitions should otherwise
+          be increasing, there should be no more than one partition
+          defined where <literal>MAXVALUE</literal> is used as the upper
+          limit for all column values, and this partition definition
+          should appear last in the list of <literal>PARTITION ...
+          VALUES LESS THAN</literal> clauses. In addition, you cannot
+          use <literal>MAXVALUE</literal> as the limiting value for the
+          first column in more than one partition definition.
+        </para>
+
+        <para>
+          As stated previously, it is also possible with <literal>RANGE
+          COLUMNS</literal> partitioning to use non-integer columns as
+          partitioning columns. (See
+          <xref linkend="partitioning-columns"/>, for a complete listing
+          of these.) For example, consider a table named
+          <literal>employees</literal> (which is not partitioned),
+          defined using the following <literal role="stmt">CREATE
+          TABLE</literal> statement:
+        </para>
+
+<programlisting>
+CREATE TABLE employees (
+    id INT NOT NULL,
+    fname VARCHAR(30),
+    lname VARCHAR(30),
+    hired DATE NOT NULL DEFAULT '1970-01-01',
+    separated DATE NOT NULL DEFAULT '9999-12-31',
+    job_code INT NOT NULL,
+    store_id INT NOT NULL
+);
+</programlisting>
+
+        <para>
+          Using <literal>RANGE COLUMNS</literal> partitioning, you can
+          create a version of this table that stores each row in one of
+          four partitions based on the employye&apos;s last name, like
+          this:
+        </para>
+
+<programlisting>
+CREATE TABLE employees_by_lname (
+    id INT NOT NULL,
+    fname VARCHAR(30),
+    lname VARCHAR(30),
+    hired DATE NOT NULL DEFAULT '1970-01-01',
+    separated DATE NOT NULL DEFAULT '9999-12-31',
+    job_code INT NOT NULL,
+    store_id INT NOT NULL
+)
+PARTITION BY RANGE COLUMNS (lname)  (
+    PARTITION p0 VALUES LESS THAN ('g'),
+    PARTITION p1 VALUES LESS THAN ('m'),
+    PARTITION p2 VALUES LESS THAN ('t'),
+    PARTITION p3 VALUES LESS THAN (MAXVALUE)
+);
+</programlisting>
+
+        <para>
+          Alternatively, you could cause the
+          <literal>employees</literal> table as created previously to be
+          partitioned using this scheme by executing the following
+          <literal role="stmt">ALTER TABLE</literal> statement:
+        </para>
+
+<programlisting>
+ALTER TABLE employees PARTITION BY RANGE COLUMNS (lname)  (
+    PARTITION p0 VALUES LESS THAN ('g'),
+    PARTITION p1 VALUES LESS THAN ('m'),
+    PARTITION p2 VALUES LESS THAN ('t'),
+    PARTITION p3 VALUES LESS THAN (MAXVALUE)
+);
+</programlisting>
+
+        <note>
+          <para>
+            Because different character sets and collations have
+            different sort orders, the character sets and collations in
+            use may effect which partition of a table partitioned by
+            <literal>RANGE COLUMNS</literal> a given row is stored in
+            when using string columns as partitioning columns. In
+            addition, changing the character set or collation for a
+            given database, table, or column after such a table is
+            created may cause changes in how rows are distributed. For
+            example, when using a case-sensitive collation,
+            <literal>'and'</literal> sorts before
+            <literal>'Andersen'</literal>, but when using a collation
+            that is case insensitive, the reverse is true.
+          </para>
+
+          <para>
+            For information about how MySQL handles character sets and
+            collations, see <xref linkend="charset"/>.
+          </para>
+        </note>
+
+        <para>
+          Similarly, you can cause the <literal>employees</literal>
+          table to be partitioned in such a way that each row is stored
+          in one of several partitions based on the decade in which the
+          corresponding employee was hired using the
+          <literal role="stmt">ALTER TABLE</literal> statement shown
+          here:
+        </para>
+
+<programlisting>
+ALTER TABLE employees PARTITION BY RANGE COLUMNS (hired)  (
+    PARTITION p0 VALUES LESS THAN ('1970-01-01'),
+    PARTITION p1 VALUES LESS THAN ('1980-01-01'),
+    PARTITION p2 VALUES LESS THAN ('1990-01-01'),
+    PARTITION p3 VALUES LESS THAN ('2000-01-01'),
+    PARTITION p4 VALUES LESS THAN ('2010-01-01'),
+    PARTITION p5 VALUES LESS THAN (MAXVALUE)
+);
+</programlisting>
+
+        <para>
+          See <xref linkend="create-table"/>, for additional information
+          about <literal>PARTITION BY RANGE COLUMNS</literal> syntax.
+        </para>
+
+      </section>
+
+      <section id="partitioning-columns-list">
+
+        <title>List columns partitioning</title>
+
+        <para>
+          Beginning with MySQL 6.0.14, MySQL provides support for
+          <literal>LIST COLUMNS</literal> partitioning. This is a
+          variant of <literal>LIST</literal> partitioning that allows
+          the use of multiple columns as partition keys, and for columns
+          of data types other than integer types to be used as
+          partitioning columns; you can use string types,
+          <literal role="type">DATE</literal>, and
+          <literal role="type">DATETIME</literal> columns. (For more
+          information about allowed data types for
+          <literal>COLUMNS</literal> partitioning columns, see
+          <xref linkend="partitioning-columns"/>.)
+        </para>
+
+        <para>
+          Suppose that you have a business that has customers in 12
+          cities which, for sales and marketing purposes, you organize
+          into 4 regions of 3 cities each as shown in the following
+          table:
+        </para>
+
+        <informaltable>
+          <tgroup cols="2">
+            <colspec colwidth="50*"/>
+            <colspec colwidth="50*"/>
+            <thead>
+              <row>
+                <entry>Region</entry>
+                <entry>Cities</entry>
+              </row>
+            </thead>
+            <tbody>
+              <row>
+                <entry>1</entry>
+                <entry>Oskarshamn, Högsby, Mönsterås</entry>
+              </row>
+              <row>
+                <entry>2</entry>
+                <entry>Vimmerby, Hultsfred, Västervik</entry>
+              </row>
+              <row>
+                <entry>3</entry>
+                <entry>Nässjö, Eksjö, Vetlanda</entry>
+              </row>
+              <row>
+                <entry>4</entry>
+                <entry>Uppvidinge, Alvesta, Växjo</entry>
+              </row>
+            </tbody>
+          </tgroup>
+        </informaltable>
+
+        <para>
+          With <literal>LIST COLUMNS</literal> partitioning, you can
+          create a table for customer data that assigns a row to any of
+          4 partitions corresponding to these regions based on the name
+          of the city where a customer resides, as shown here:
+        </para>
+
+<programlisting>
+CREATE TABLE customers_1 (
+    first_name VARCHAR(25),
+    last_name VARCHAR(25),
+    street_1 VARCHAR(30),
+    street_2 VARCHAR(30),
+    city VARCHAR(15),
+    renewal DATE
+)
+PARTITION BY LIST COLUMNS(city) (
+    PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
+    PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
+    PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
+    PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
+);
+</programlisting>
+
+        <para>
+          As with partitioning by <literal>RANGE COLUMNS</literal>, you
+          do not need to use expressions in the
+          <literal>COLUMNS()</literal> clause to convert column values
+          into integers. (In fact, the use of expressions other than
+          column names is not allowed with
+          <literal>COLUMNS()</literal>.)
+        </para>
+
+        <para>
+          It is also possible to use <literal role="type">DATE</literal>
+          and <literal role="type">DATETIME</literal> columns, as shown
+          in the following example that uses the same name and columns
+          as the <literal>customers_1</literal> table shown previously,
+          but employs <literal>LIST COLUMNS</literal> partitioning based
+          on the <literal>renewal</literal> column to store rows in one
+          of 4 partitions depending on the week in February 2010 the
+          customer&apos;s account is scheduled to renew:
+        </para>
+
+<programlisting>
+CREATE TABLE customers_2 (
+    first_name VARCHAR(25),
+    last_name VARCHAR(25),
+    street_1 VARCHAR(30),
+    street_2 VARCHAR(30),
+    city VARCHAR(15),
+    renewal DATE
+)
+PARTITION BY LIST COLUMNS(renewal) (
+    PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
+        '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
+    PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
+        '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
+    PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
+        '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
+    PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
+        '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
+);
+</programlisting>
+
+        <para>
+          This works, but becomes cumbersome to define and maintain if
+          the number of dates involved grows very large; in such cases,
+          it is usually more practical to employ
+          <literal>RANGE</literal> or <literal>RANGE COLUMNS</literal>
+          partitioning instead:
+        </para>
+
+<programlisting>
+CREATE TABLE customers_3 (
+    first_name VARCHAR(25),
+    last_name VARCHAR(25),
+    street_1 VARCHAR(30),
+    street_2 VARCHAR(30),
+    city VARCHAR(15),
+    renewal DATE
+)
+PARTITION BY RANGE COLUMNS(renewal) (
+    PARTITION pWeek_1 VALUES LESS THAN('2010-02-09'),
+    PARTITION pWeek_2 VALUES LESS THAN('2010-02-15'),
+    PARTITION pWeek_3 VALUES LESS THAN('2010-02-22'),
+    PARTITION pWeek_4 VALUES LESS THAN('2010-03-01')
+);
+</programlisting>
+
+        <para>
+          See <xref linkend="partitioning-columns-range"/>, for more
+          information.
+        </para>
+
+        <para>
+          In addition (as with <literal>RANGE COLUMNS</literal>
+          partitioning), you can use multiple columns in the
+          <literal>COLUMNS()</literal> clause.
+        </para>
+
+        <para>
+          See <xref linkend="create-table"/>, for additional information
+          about <literal>PARTITION BY LIST COLUMNS()</literal> syntax.
+        </para>
+
+      </section>
+
+    </section>
+
     <section id="partitioning-hash">
 
       <title><literal>HASH</literal> Partitioning</title>

@@ -1740,8 +2778,8 @@
         Partitioning by key is similar to partitioning by hash, except
         that where hash partitioning employs a user-defined expression,
         the hashing function for key partitioning is supplied by the
-        MySQL server. This function is based on the same algorithm as
-        <literal role="func">PASSWORD()</literal>.
+        MySQL server. This internal hashing function is based on the
+        same algorithm as <literal role="func">PASSWORD()</literal>.
       </para>
 
       <para>

@@ -2713,6 +3751,11 @@
       <secondary>splitting and merging</secondary>
     </indexterm>
 
+    <indexterm>
+      <primary>partitions</primary>
+      <secondary>truncating</secondary>
+    </indexterm>
+
     <para>
       MySQL &current-series; provides a number of ways to modify
       partitioned tables. It is possible to add, drop, redefine, merge,

@@ -2830,6 +3873,13 @@
       </para>
     </important>
 
+    <para>
+      Beginning with MySQL 6.0.14, it is possible to delete rows from
+      one or more selected partitions using
+      <literal role="stmt" condition="alter-table">ALTER TABLE ...
+      TRUNCATE PARTITION</literal>.
+    </para>
+
     <section id="partitioning-management-range-list">
 
       <title>Management of <literal>RANGE</literal> and <literal>LIST</literal>

@@ -3758,6 +4808,21 @@
       </para>
 -->
 
+      <para>
+        Beginning with MySQL 6.0.14, you can also truncate partitions
+        using <literal role="stmt" condition="alter-table">ALTER TABLE
+        ... TRUNCATE PARTITION</literal>. This statement can be used to
+        delete all rows from one or more partitions in much the same way
+        that <literal role="stmt">TRUNCATE TABLE</literal> deletes all
+        rows from a table.
+      </para>
+
+      <para>
+        <literal role="stmt" condition="alter-table">ALTER TABLE ...
+        TRUNCATE PARTITION ALL</literal> truncates all partitions in the
+        table.
+      </para>
+
     </section>
 
     <section id="partitioning-info">

@@ -4151,6 +5216,13 @@
       rows.
     </para>
 
+    <para>
+      Beginning with MySQL 6.0.14, the optimizer can also perform
+      pruning for queries that that involve comparisons of the preceding
+      types on multiple columns for tables that use <literal>RANGE
+      COLUMNS</literal> or <literal>LIST COLUMNS</literal> partitioning.
+    </para>
+
     <indexterm>
       <primary>dates</primary>
       <secondary>used with partitioning (examples)</secondary>

@@ -4166,18 +5238,13 @@
       <literal role="type">DATETIME</literal> column when the
       partitioning expression uses the
       <literal role="func">YEAR()</literal> or
-      <literal role="func">TO_DAYS()</literal> function.
+      <literal role="func">TO_DAYS()</literal> function. In addition,
+      beginning with MySQL 6.0.14, pruning can be applied for such
+      tables when the partitioning expression uses the
+      <literal role="func">TO_SECONDS()</literal> function.
+    </para>
 
-      <note>
-        <para>
-          We plan to add pruning support in a future MySQL release for
-          additional functions that act on a
-          <literal role="type">DATE</literal> or
-          <literal role="type">DATETIME</literal> value, return an
-          integer, and are increasing or decreasing.
-        </para>
-      </note>
-
+    <para>
       Suppose that table <literal>t2</literal>, defined as shown here,
       is partitioned on a <literal role="type">DATE</literal> column:
     </para>

@@ -4862,6 +5929,39 @@
       <listitem>
         <formalpara>
 
+          <title>Per-partition key caches</title>
+
+          <indexterm>
+            <primary>partitioning</primary>
+            <secondary>and key cache</secondary>
+          </indexterm>
+
+          <indexterm>
+            <primary>CACHE INDEX</primary>
+            <secondary>and partitioning</secondary>
+          </indexterm>
+
+          <indexterm>
+            <primary>LOAD INDEX INTO CACHE</primary>
+            <secondary>and partitioning</secondary>
+          </indexterm>
+
+          <para>
+            Beginning with MySQL 6.0.14, key caches are supported for
+            partitioned <literal role="se">MyISAM</literal> tables,
+            using the <literal role="stmt">CACHE INDEX</literal> and
+            <literal role="stmt" condition="load-index">LOAD INDEX INTO
+            CACHE</literal> statements. Key caches may be defined for
+            one, several, or all partitions, and indexes for one,
+            several, or all partitions may be preloaded into key caches.
+          </para>
+
+        </formalpara>
+      </listitem>
+
+      <listitem>
+        <formalpara>
+
           <title>Foreign keys not supported</title>
 
           <indexterm>

@@ -4999,23 +6099,63 @@
         </formalpara>
 
         <para>
-          The lone exception to this restriction occurs when
-          partitioning by [<literal>LINEAR</literal>]
-          <literal>KEY</literal>, where it is possible to use columns of
-          other types as partitioning keys, because MySQL's internal
-          key-hashing functions produce the correct data type from these
-          types. For example, the following <literal role="stmt">CREATE
-          TABLE</literal> statement is valid:
+          There are two exceptions to this restriction:
         </para>
 
+        <orderedlist>
+
+          <listitem>
+            <para>
+              When partitioning by [<literal>LINEAR</literal>]
+              <literal>KEY</literal>, it is possible to use columns of
+              other types as partitioning keys, because MySQL's internal
+              key-hashing functions produce the correct data type from
+              these types. For example, the following
+              <literal role="stmt">CREATE TABLE</literal> statement is
+              valid:
+            </para>
+
 <programlisting>
 CREATE TABLE tkc (c1 CHAR)
 PARTITION BY KEY(c1)
 PARTITIONS 4;
 </programlisting>
+          </listitem>
 
+          <listitem>
+            <para>
+              When partitioning by <literal>RANGE COLUMNS</literal> or
+              <literal>LIST COLUMNS</literal> (MySQL 6.0.14 and later),
+              it is possible to use string,
+              <literal role="type">DATE</literal>, and
+              <literal role="type">DATETIME</literal> columns. For
+              example, each of the following <literal role="stmt">CREATE
+              TABLE</literal> statements is valid:
+            </para>
+
+<programlisting>
+CREATE TABLE rc (c1 INT, c2 DATE)
+PARTITION BY RANGE COLUMNS(c2) (
+    PARTITION p0 VALUES LESS THAN('1990-01-01'),
+    PARTITION p1 VALUES LESS THAN('1995-01-01'),
+    PARTITION p2 VALUES LESS THAN('2000-01-01'),
+    PARTITION p3 VALUES LESS THAN('2005-01-01'),
+    PARTITION p4 VALUES LESS THAN(MAXVALUE)
+);
+
+CREATE TABLE lc (c1 INT, c2 CHAR(1))
+PARTITION BY LIST COLUMNS(c2) (
+    PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'),
+    PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'),
+    PARTITION p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL)
+);
+</programlisting>
+          </listitem>
+
+        </orderedlist>
+
         <para>
-          This exception does <emphasis>not</emphasis> apply to
+          Neither of the preceding exceptions applies to
           <literal role="type">BLOB</literal> or
           <literal role="type">TEXT</literal> column types.
         </para>

@@ -5161,41 +6301,6 @@
       <listitem>
         <formalpara>
 
-          <title>Key caches not supported</title>
-
-          <indexterm>
-            <primary>partitioning</primary>
-            <secondary>and key cache</secondary>
-          </indexterm>
-
-          <indexterm>
-            <primary>CACHE INDEX</primary>
-            <secondary>and partitioning</secondary>
-          </indexterm>
-
-          <indexterm>
-            <primary>LOAD INDEX INTO CACHE</primary>
-            <secondary>and partitioning</secondary>
-          </indexterm>
-
-          <para>
-            Key caches are not supported for partitioned tables. The
-            <literal role="stmt">CACHE INDEX</literal> and
-            <literal role="stmt" condition="load-index">LOAD INDEX INTO
-            CACHE</literal> statements, when you attempt to use them on
-            tables having user-defined partitioning, fail with the
-            errors <errortext>The storage engine for the table doesn't
-            support assign_to_keycache</errortext> and <errortext>The
-            storage engine for the table doesn't support
-            preload_keys</errortext>, respectively.
-          </para>
-
-        </formalpara>
-      </listitem>
-
-      <listitem>
-        <formalpara>
-
           <title><literal>DELAYED</literal> option not supported</title>
 
           <para>

@@ -5790,16 +6895,17 @@
               <entry><literal role="func">TO_DAYS()</literal></entry>
             </row>
             <row>
+              <entry><literal role="func">TO_SECONDS()</literal> (implemented in MySQL
+                6.0.14)</entry>
               <entry><literal role="func">UNIX_TIMESTAMP()</literal> (permitted in MySQL
                 6.0.14 and later, with
                 <literal role="type">TIMESTAMP</literal> columns)</entry>
               <entry><literal role="func">WEEKDAY()</literal></entry>
-              <entry><literal role="func">YEAR()</literal></entry>
             </row>
             <row>
+              <entry><literal role="func">YEAR()</literal></entry>
               <entry></entry>
               <entry><literal role="func">YEARWEEK()</literal></entry>
-              <entry></entry>
             </row>
           </tbody>
         </tgroup>

@@ -5808,7 +6914,8 @@
       <note>
         <para>
           In MySQL &current-series;, partition pruning is supported only
-          for the <literal role="func">TO_DAYS()</literal> and
+          for the <literal role="func">TO_DAYS()</literal>,
+          <literal role="func">TO_SECONDS()</literal>, and
           <literal role="func">YEAR()</literal> functions. See
           <xref linkend="partitioning-pruning"/>, for more information.
         </para>


Modified: trunk/refman-6.0/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-6.0/sql-syntax-data-definition.xml	2010-06-07 19:55:03 UTC (rev 21093)
+++ trunk/refman-6.0/sql-syntax-data-definition.xml	2010-06-07 20:16:09 UTC (rev 21094)
Changed blocks: 6, Lines Added: 122, Lines Deleted: 11; 6788 bytes

@@ -627,6 +627,7 @@
   | <replaceable>partition_options</replaceable>
   | ADD PARTITION (<replaceable>partition_definition</replaceable>)
   | DROP PARTITION <replaceable>partition_names</replaceable>
+  | TRUNCATE PARTITION {<replaceable>partition_names</replaceable> | ALL }
   | COALESCE PARTITION <replaceable>number</replaceable>
   | REORGANIZE PARTITION <replaceable>partition_names</replaceable> INTO (<replaceable>partition_definitions</replaceable>)
   | ANALYZE PARTITION  {<replaceable>partition_names</replaceable> | ALL }

@@ -1677,6 +1678,115 @@
         </para>
 
         <para>
+          Beginning with MySQL 6.0.14, it is possible to delete rows
+          from selected partitions using the <literal>TRUNCATE
+          PARTITION</literal> option. This option takes a
+          comma-separated list of one or more partition names. For
+          example, consider the table <literal>t1</literal> as defined
+          here:
+        </para>
+
+<programlisting>
+CREATE TABLE t1 (
+    id INT,
+    year_col INT
+)
+PARTITION BY RANGE (year_col) (
+    PARTITION p0 VALUES LESS THAN (1991),
+    PARTITION p1 VALUES LESS THAN (1995),
+    PARTITION p2 VALUES LESS THAN (1999),
+    PARTITION p3 VALUES LESS THAN (2003),
+    PARTITION p4 VALUES LESS THAN (2007)
+);
+</programlisting>
+
+        <para>
+          To delete all rows from partition <literal>p0</literal>, you
+          can use the following statement:
+        </para>
+
+<programlisting>
+ALTER TABLE t1 TRUNCATE PARTITION p0;
+</programlisting>
+
+        <para>
+          The statement just shown has the same effect as the following
+          <literal role="stmt">DELETE</literal> statement:
+        </para>
+
+<programlisting>
+DELETE FROM t1 WHERE year_col &lt; 1991;
+</programlisting>
+
+        <para>
+          When truncating multiple partitions, the partitions do not
+          have to be contiguous: This can greatly simplify delete
+          operations on partitioned tables that would otherwise require
+          very complex <literal>WHERE</literal> conditions if done with
+          <literal role="stmt">DELETE</literal> statements. For example,
+          this statement deletes all rows from partitions
+          <literal>p1</literal> and <literal>p3</literal>:
+        </para>
+
+<programlisting>
+ALTER TABLE t1 TRUNCATE PARTITION p1, p3;
+</programlisting>
+
+        <para>
+          An equivalent <literal role="stmt">DELETE</literal> statement
+          is shown here:
+        </para>
+
+<programlisting>
+DELETE FROM t1 WHERE 
+    (year_col &gt;= 1991 AND year_col &lt; 1995)
+    OR
+    (year_col &gt;= 2003 AND year_col &lt; 2007);
+</programlisting>
+
+        <para>
+          You can also use the <literal>ALL</literal> keyword in place
+          of the list of partition names; in this case, the statement
+          acts on all partitions in the table.
+        </para>
+
+        <para>
+          <literal>TRUNCATE PARTITION</literal> merely deletes rows; it
+          does not alter the definition of the table itself, or of any
+          of its partitions.
+        </para>
+
+        <note>
+          <para>
+            <literal>TRUNCATE PARTITION</literal> does not work with
+            subpartitions.
+          </para>
+        </note>
+
+        <para>
+          You can verify that the rows were dropped by checking the
+          <literal>INFORMATION_SCHEMA.PARTITIONS</literal> table, using
+          a query such as this one:
+        </para>
+
+<programlisting>
+SELECT PARTITION_NAME, TABLE_ROWS 
+    FROM INFORMATION_SCHEMA.PARTITIONS 
+    WHERE TABLE_NAME = 't1';
+</programlisting>
+
+        <para>
+          <literal>TRUNCATE PARTITION</literal> is supported only for
+          partitioned tables that use the
+          <literal role="se">MyISAM</literal>,
+          <literal role="se">InnoDB</literal>, or
+          <literal role="se">MEMORY</literal> storage engine. It also
+          works on <literal role="se">BLACKHOLE</literal> tables (but
+          has no effect). It is not supported for
+          <literal role="se">ARCHIVE</literal> tables.
+        </para>
+
+        <para>
           <literal>COALESCE PARTITION</literal> can be used with a table
           that is partitioned by <literal>HASH</literal> or
           <literal>KEY</literal> to reduce the number of partitions by

@@ -1771,9 +1881,9 @@
 
       <listitem>
         <para>
-          Several additional options available for providing partition
-          maintenance and repair functionality analogous to that
-          implemented for nonpartitioned tables by statements such as
+          Several additional options provide partition maintenance and
+          repair functionality analogous to that implemented for
+          nonpartitioned tables by statements such as
           <literal role="stmt">CHECK TABLE</literal> and
           <literal role="stmt">REPAIR TABLE</literal> (which are also
           supported for partitioned tables, beginning with MySQL

@@ -1842,11 +1952,12 @@
         a given <literal role="stmt">ALTER TABLE</literal> statement:
         <literal>PARTITION BY</literal>, <literal>ADD
         PARTITION</literal>, <literal>DROP PARTITION</literal>,
-        <literal>REORGANIZE PARTITION</literal>, or <literal>COALESCE
-        PARTITION</literal>, <literal>ANALYZE PARTITION</literal>,
-        <literal>CHECK PARTITION</literal>, <literal>OPTIMIZE
-        PARTITION</literal>, <literal>REBUILD PARTITION</literal>,
-        <literal>REMOVE PARTITIONING</literal>.
+        <literal>TRUNCATE PARTITION</literal>, <literal>REORGANIZE
+        PARTITION</literal>, or <literal>COALESCE PARTITION</literal>,
+        <literal>ANALYZE PARTITION</literal>, <literal>CHECK
+        PARTITION</literal>, <literal>OPTIMIZE PARTITION</literal>,
+        <literal>REBUILD PARTITION</literal>, <literal>REMOVE
+        PARTITIONING</literal>.
       </para>
 
       <para>

@@ -1868,7 +1979,7 @@
       </para>
 
 <programlisting>
-ALTER TABLE t1 ANALYZE PARTITION p1, ANALYZE PARTITION p2;
+ALTER TABLE t1 ANALYZE PARTITION p1, p2;
 </programlisting>
 
       <para>

@@ -1966,9 +2077,9 @@
 </programlisting>
 
     <para>
-      Note that we indexed <literal>c</literal> (as a <literal>PRIMARY
+      We indexed <literal>c</literal> (as a <literal>PRIMARY
       KEY</literal>) because <literal>AUTO_INCREMENT</literal> columns
-      must be indexed, and also that we declare <literal>c</literal> as
+      must be indexed, and we declare <literal>c</literal> as
       <literal>NOT NULL</literal> because primary key columns cannot be
       <literal>NULL</literal>.
     </para>


Thread
svn commit - mysqldoc@docsrva: r21094 - in trunk: dynamic-docs/opsfunctions refman-5.1 refman-5.4 refman-5.5 refman-6.0jon.stephens7 Jun