Author: jstephens
Date: 2005-11-04 12:53:10 +0100 (Fri, 04 Nov 2005)
New Revision: 244
Log:
refman-5.1/partitioning.xml
- New section, discusses how partitioning handles NULL in partitioning
expressions, with examples; response to Bug#13443 (intended behaviour, but
needed to be explained in docs)
refman-common/titles.en.ent - Added new section title
Modified:
trunk/refman-5.1/partitioning.xml
trunk/refman-common/titles.en.ent
Modified: trunk/refman-5.1/partitioning.xml
===================================================================
--- trunk/refman-5.1/partitioning.xml 2005-11-04 09:59:17 UTC (rev 243)
+++ trunk/refman-5.1/partitioning.xml 2005-11-04 11:53:10 UTC (rev 244)
@@ -482,10 +482,10 @@
</programlisting>
<para>
- This is because MySQL see no difference between the partition
+ This is because MySQL sees no difference between the partition
names <literal>mypart</literal> and <literal>MyPart</literal>.
</para>
-
+
<para>
<emphasis role="bold">Note</emphasis>: In the sections that
follow, we do not necessarily provide all possible forms for the
@@ -1760,7 +1760,209 @@
</itemizedlist>
</section>
+
+ <section id="partitioning-handling-nulls">
+ <title id="title-partitioning-handling-nulls">&title-partitioning-handling-nulls;</title>
+
+ <para>
+ Partitioning in MySQL does nothing to disallow
+ <literal>NULL</literal> as the value of a partitioning
+ expression, whether it is a column value or the value of a
+ user-supplied expression. In general, MySQL will treat a
+ <literal>NULL</literal> as a zero in such cases. If you wish to
+ circumvent this behaviour, you should design tables so as not to
+ allow nulls; most likely you can do so by declaring columns
+ <literal>NOT NULL</literal>.
+ </para>
+
+ <para>
+ In this section, we provide some examples illustrating how MySQL
+ handles <literal>NULL</literal> values when determining the
+ partition in which a row should be stored.
+ </para>
+
+ <para>
+ If you insert a row into a table partitioned by
+ <literal>RANGE</literal> or <literal>LIST</literal> such that
+ the column value used to determine the partition is
+ <literal>NULL</literal>, it is treated as <literal>0</literal>.
+ For example, consider these two tables, created and populated as
+ follows:
+ </para>
+
+<programlisting>
+mysql> <userinput>CREATE TABLE tnlist (</userinput>
+ -> <userinput>id INT,</userinput>
+ -> <userinput>name VARCHAR(5)</userinput>
+ -> <userinput>)</userinput>
+ -> <userinput>PARTITION BY LIST(id) (</userinput>
+ -> <userinput>PARTITION p1 VALUES IN (0),</userinput>
+ -> <userinput>PARTITION p2 VALUES IN (1)</userinput>
+ -> <userinput>);</userinput>
+Query OK, 0 rows affected (0.09 sec)
+mysql> <userinput>CREATE TABLE tnrange (</userinput>
+ -> <userinput>id INT,</userinput>
+ -> <userinput>name VARCHAR(5)</userinput>
+ -> <userinput>)</userinput>
+ -> <userinput>PARTITION BY RANGE(id) (</userinput>
+ -> <userinput>PARTITION p1 VALUES LESS THAN (1),</userinput>
+ -> <userinput>PARTITION p2 VALUES LESS THAN MAXVALUE</userinput>
+ -> <userinput>);</userinput>
+Query OK, 0 rows affected (0.09 sec)
+
+mysql> <userinput>INSERT INTO tnlist VALUES (NULL, 'bob');</userinput>
+Query OK, 1 row affected (0.00 sec)
+
+mysql> <userinput>INSERT INTO tnrange VALUES (NULL, 'jim');</userinput>
+Query OK, 1 row affected (0.00 sec)
+
+mysql> <userinput>SELECT * FROM tnlist;</userinput>
++------+------+
+| id | name |
++------+------+
+| NULL | bob |
++------+------+
+1 row in set (0.00 sec)
+
+mysql> <userinput>SELECT * FROM tnrange;</userinput>
++------+------+
+| id | name |
++------+------+
+| NULL | jim |
++------+------+
+1 row in set (0.00 sec)
+</programlisting>
+
+ <para>
+ In both tables, the <literal>id</literal> column was not
+ declared as <literal>NOT NULL</literal>, which means that it
+ admits <literal>NULL</literal> values. You can verify that the
+ rows were stored in the partitions <literal>p1</literal> of the
+ each table by dropping these partitions, and then re-running the
+ <literal>SELECT</literal> statements:
+ </para>
+
+<programlisting>
+mysql> <userinput>ALTER TABLE tnlist DROP PARTITION p1;</userinput>
+Query OK, 0 rows affected (0.16 sec)
+
+mysql> <userinput>ALTER TABLE tnrange DROP PARTITION p1;</userinput>
+Query OK, 0 rows affected (0.16 sec)
+
+mysql> <userinput>SELECT * FROM tnlist;</userinput>
+Empty set (0.00 sec)
+
+mysql> <userinput>SELECT * FROM tnrange;</userinput>
+Empty set (0.00 sec)
+</programlisting>
+
+ <para>
+ In the case of partitioning by <literal>HASH</literal> or
+ <literal>KEY</literal>, any partition expression that yields a
+ <literal>NULL</literal> value is treated as though its return
+ value were zero. We can verify this behaviour by examining the
+ effects on the filesystem of creating a table partitioned by
+ <literal>HASH</literal> and populating it with a record
+ containing appropriate values. Suppose you have a table
+ <literal>tnhash</literal>, created in the
+ <literal>test</literal> database, using this statement:
+ </para>
+
+<programlisting>
+CREATE TABLE tnhash (
+ id INT,
+ name VARCHAR(5)
+)
+PARTITION BY HASH(id)
+PARTITIONS 2;
+</programlisting>
+
+ <para>
+ Assuming an RPM installation of MySQL on Linux, this statement
+ creates two <filename>.MYD</filename> files in
+ <filename>/var/lib/mysql/test</filename>, which can be viewed in
+ the <command>bash</command> shell as follows:
+ </para>
+
+<programlisting>
+/var/lib/mysql/test> <userinput>ls *.MYD -l</userinput>
+-rw-rw---- 1 mysql mysql 0 2005-11-04 18:41 tnhash_p0.MYD
+-rw-rw---- 1 mysql mysql 0 2005-11-04 18:41 tnhash_p1.MYD
+</programlisting>
+
+ <para>
+ Note that the size of each file is 0 bytes. Now insert a row
+ into <literal>tnhash</literal> whose <literal>id</literal>
+ column value is <literal>NULL</literal> and verify that this row
+ was inserted:
+ </para>
+
+<programlisting>
+mysql> <userinput>INSERT INTO tnhash VALUES (NULL, 'sam');</userinput>
+Query OK, 1 row affected (0.00 sec)
+
+mysql> <userinput>SELECT * FROM tnhash;</userinput>
++------+------+
+| id | name |
++------+------+
+| NULL | sam |
++------+------+
+1 row in set (0.01 sec)
+</programlisting>
+
+ <para>
+ Recall that for any integer <replaceable>N</replaceable>, the
+ value of <literal>NULL MOD
+ <replaceable>N</replaceable></literal> is always
+ <literal>NULL</literal>. This result is treated for determining
+ the correct partition as <literal>0</literal>. Returning to the
+ system shell (still assuming <command>bash</command> for this
+ purpose), we can see that the value was inserted into the first
+ partition (named <literal>p0</literal> by default) by listing
+ the data files once again:
+ </para>
+
+<programlisting>
+var/lib/mysql/test> <userinput>ls *.MYD -l</userinput>
+-rw-rw---- 1 mysql mysql 20 2005-11-04 18:44 tnhash_p0.MYD
+-rw-rw---- 1 mysql mysql 0 2005-11-04 18:41 tnhash_p1.MYD
+</programlisting>
+
+ <para>
+ You can see that the <literal>INSERT</literal> statement
+ modified only the file <filename>tnhash_p0.MYD</filename>,
+ increasing its size on disk, without affecting the other data
+ file.
+ </para>
+
+ <para>
+ Suppose we have a table such as this one:
+ </para>
+
+<programlisting>
+CREATE TABLE tndate (
+ id INT,
+ dt DATE
+)
+PARTITION BY RANGE( YEAR(dt) ) (
+ PARTITION p0 VALUES LESS THAN (1990),
+ PARTITION p1 VALUES LESS THAN (2000),
+ PARTITION p2 VALUES LESS THAN MAXVALUE
+);
+</programlisting>
+
+ <para>
+ As with other MySQL functions, <literal>YEAR(NULL)</literal>
+ returns <literal>NULL</literal>. A row with a
+ <literal>dt</literal> column value of <literal>NULL</literal> is
+ treated as though the partitioning expression evaluated to
+ <literal>0</literal>, and is inserted into partition
+ <literal>p0</literal>.
+ </para>
+
+ </section>
+
</section>
<section id="partitioning-management">
@@ -2132,7 +2334,7 @@
partitioned by range, you can use <literal>ADD
PARTITION</literal> to add new partitions to the high end of the
partitions list only. Trying to add a new partition in this
- manner between or before existing partitions will result n an
+ manner between or before existing partitions will result in an
error as shown here:
</para>
Modified: trunk/refman-common/titles.en.ent
===================================================================
--- trunk/refman-common/titles.en.ent 2005-11-04 09:59:17 UTC (rev 243)
+++ trunk/refman-common/titles.en.ent 2005-11-04 11:53:10 UTC (rev 244)
@@ -1242,6 +1242,7 @@
<!ENTITY title-partitioning-range "<literal>RANGE</literal> Partitioning">
<!ENTITY title-partitioning-list "<literal>LIST</literal> Partitioning">
<!ENTITY title-partitioning-subpartitions "Subpartitioning">
+<!ENTITY title-partitioning-handling-nulls "How MySQL Partitioning Handles <literal>NULL</literal> Values">
<!ENTITY title-partitioning-management "Partition Management">
<!ENTITY title-partitioning-management-range-list "Management of <literal>RANGE</literal> and <literal>LIST</literal> Partitions">
<!ENTITY title-partitioning-management-hash-key "Management of <literal>HASH</literal> and <literal>KEY</literal> Partitions">
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r244 - in trunk: refman-5.1 refman-common | jstephens | 4 Nov |
