Author: jstephens
Date: 2006-03-10 03:11:17 +0100 (Fri, 10 Mar 2006)
New Revision: 1545
Log:
Documenting fix for Partitioning Bug #15447:
- Noted incompatible change in 5.1.8 changelog.
- Rewrote section partitioning-handing-nulls.
Modified:
trunk/refman-5.1/partitioning.xml
trunk/refman-common/news-5.1.xml
Modified: trunk/refman-5.1/partitioning.xml
===================================================================
--- trunk/refman-5.1/partitioning.xml 2006-03-09 21:25:11 UTC (rev 1544)
+++ trunk/refman-5.1/partitioning.xml 2006-03-10 02:11:17 UTC (rev 1545)
@@ -1886,117 +1886,296 @@
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 behavior, you should design tables so as not to
- allow nulls; most likely you can do so by declaring columns
- <literal>NOT NULL</literal>.
+ 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. In keeping with the SQL
+ standard, MySQL Partitioning beginning with version 5.1.8 treats
+ <literal>NULL</literal> as being less than any number.
</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.
+ Because of this, this treatment of <literal>NULL</literal>
+ varies between partitioning of different types, and may produce
+ behavior which you do not expect if you are not prepared for it.
+ This being the case, we discuss in this section how each MySQL
+ partitioning types handles <literal>NULL</literal> values
+ when determining the partition in which a row should be stored,
+ and provide examples for each.
</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:
+ <literal>RANGE</literal> such that the column value used to
+ determine the partition is <literal>NULL</literal>, the row is
+ inserted into the lowest partition. 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>
+mysql> <userinput>CREATE TABLE t1 (</userinput>
+ -> <userinput>c1 INT,</userinput>
+ -> <userinput>c2 VARCHAR(20)</userinput>
+ -> <userinput>)</userinput>
+ -> <userinput>PARTITION BY RANGE(c1) (</userinput>
+ -> <userinput>PARTITION p0 VALUES LESS THAN (0),</userinput>
+ -> <userinput>PARTITION p1 VALUES LESS THAN (10),</userinput>
+ -> <userinput>PARTITION p2 VALUES LESS THAN MAXVALUE</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>
+
+mysql> <userinput>CREATE TABLE t1 (</userinput>
+ -> <userinput>c1 INT,</userinput>
+ -> <userinput>c2 VARCHAR(20)</userinput>
+ -> <userinput>)</userinput>
+ -> <userinput>PARTITION BY RANGE(c1) (</userinput>
+ -> <userinput>PARTITION p0 VALUES LESS THAN (-5),</userinput>
+ -> <userinput>PARTITION p1 VALUES LESS THAN (0),</userinput>
+ -> <userinput>PARTITION p1 VALUES LESS THAN (10),</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>
+mysql> <userinput>INSERT INTO t1 VALUES (NULL, 'mothra');</userinput>
Query OK, 1 row affected (0.00 sec)
-mysql> <userinput>INSERT INTO tnrange VALUES (NULL, 'jim');</userinput>
+mysql> <userinput>INSERT INTO t2 VALUES (NULL, 'mothra');</userinput>
Query OK, 1 row affected (0.00 sec)
-mysql> <userinput>SELECT * FROM tnlist;</userinput>
-+------+------+
-| id | name |
-+------+------+
-| NULL | bob |
-+------+------+
+mysql> <userinput>SELECT * FROM t1;</userinput>
++------+--------+
+| id | name |
++------+--------+
+| NULL | mothra |
++------+--------+
1 row in set (0.00 sec)
-mysql> <userinput>SELECT * FROM tnrange;</userinput>
-+------+------+
-| id | name |
-+------+------+
-| NULL | jim |
-+------+------+
+mysql> <userinput>SELECT * FROM t2;</userinput>
++------+--------+
+| id | name |
++------+--------+
+| NULL | mothra |
++------+--------+
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:
+ You can see which partitions the rows are stored in by
+ inspecting the filesystem and comparing the sizes of the
+ <filename>.MYD</filename> files correpsonding to the partitions:
</para>
+
+<programlisting>
+/var/lib/mysql/test> <userinput>ls -l *.MYD</userinput>
+<emphasis role="bold">-rw-rw---- 1 mysql mysql 20 2006-03-10 03:27 t1#P#p0.MYD</emphasis>
+-rw-rw---- 1 mysql mysql 0 2006-03-10 03:17 t1#P#p1.MYD
+-rw-rw---- 1 mysql mysql 0 2006-03-10 03:17 t1#P#p2.MYD
+<emphasis role="bold">-rw-rw---- 1 mysql mysql 20 2006-03-10 03:27 t2#P#p0.MYD</emphasis>
+-rw-rw---- 1 mysql mysql 0 2006-03-10 03:17 t2#P#p1.MYD
+-rw-rw---- 1 mysql mysql 0 2006-03-10 03:17 t2#P#p2.MYD
+-rw-rw---- 1 mysql mysql 0 2006-03-10 03:17 t2#P#p3.MYD
+</programlisting>
+
+ <para>
+ (Partition files are named according to the format
+ <literal><replaceable>table_name</replaceable>#P#<replaceable>partition_name</replaceable>.<replaceable>extension</replaceable></literal>,
+ so that <filename>t1#P#p0.MYD</filename> is the file in which
+ data belonging to partition <literal>p0</literal> of table
+ <literal>t1</literal> is stored.
+ <emphasis role="bold">Note</emphasis>: Prior to MySQL 5.1.5,
+ these files would have been named <filename>t1_p0.MYD</filename>
+ and <filename>t2_p0.MYD</filename>, respectively. See
+ <xref linkend="news-5-1-6"/> and Bug #13437 for information
+ regarding how this change impacts upgrades.)
+ </para>
+
+ <para>
+ You can also demonstrate that these rows were stored in the
+ lowest partition 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>
+mysql> <userinput>ALTER TABLE t1 DROP PARTITION p0;</userinput>
Query OK, 0 rows affected (0.16 sec)
-mysql> <userinput>ALTER TABLE tnrange DROP PARTITION p1;</userinput>
+mysql> <userinput>ALTER TABLE t2 DROP PARTITION p0;</userinput>
Query OK, 0 rows affected (0.16 sec)
-mysql> <userinput>SELECT * FROM tnlist;</userinput>
+mysql> <userinput>SELECT * FROM t1;</userinput>
Empty set (0.00 sec)
-mysql> <userinput>SELECT * FROM tnrange;</userinput>
+mysql> <userinput>SELECT * FROM t2;</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 behavior 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 that you have a table
- <literal>tnhash</literal>, created in the
- <literal>test</literal> database, using this statement:
+ Such treatment also holds true for partitioning expressions that
+ use SQL functions. Suppose that we have a table such as this one:
</para>
<programlisting>
-CREATE TABLE tnhash (
+CREATE TABLE tndate (
id INT,
- name VARCHAR(5)
+ dt DATE
)
-PARTITION BY HASH(id)
-PARTITIONS 2;
+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
+ a value less than any other value, and so is inserted into
+ partition <literal>p0</literal>.
+ </para>
+
+ <para>
+ A table that is partitioned by <literal>LIST</literal> admits
+ <literal>NULL</literal> values if and only if one of its
+ partitions is defined using that value-list that contains
+ <literal>NULL</literal>. The converse of this is that a table
+ partitioned by <literal>LIST</literal> which does not explicitly
+ use <literal>NULL</literal> in a value list rejects rows
+ resulting in a <literal>NULL</literal> value for the
+ partitioning expression, as shown in this example:
+ </para>
+
+<programlisting>
+mysql> <userinput>CREATE TABLE ts1 (</userinput>
+ -> <userinput>c1 INT,</userinput>
+ -> <userinput>c2 VARCHAR(20)</userinput>
+ -> <userinput>)</userinput>
+ -> <userinput>PARTITION BY LIST(c1) (</userinput>
+ -> <userinput>PARTITION p0 VALUES IN (0, 3, 6),</userinput>
+ -> <userinput>PARTITION p1 VALUES IN (1, 4, 7),</userinput>
+ -> <userinput>PARTITION p2 VALUES IN (2, 5, 8)</userinput>
+ -> <userinput>);</userinput>
+Query OK, 0 rows affected (0.01 sec)
+
+mysql> <userinput>INSERT INTO ts1 VALUES (9, 'mothra');</userinput>
+<errortext>ERROR 1504 (HY000): Table has no partition for value 9</errortext>
+
+mysql> <userinput>INSERT INTO ts1 VALUES (NULL, 'mothra');</userinput>
+<errortext>ERROR 1504 (HY000): Table has no partition for value NULL</errortext>
+</programlisting>
+
+ <para>
+ Only rows having a <literal>c1</literal> value between
+ <literal>0</literal> and <literal>8</literal> inclusive can be
+ inserted into <literal>ts1</literal>. <literal>NULL</literal>
+ falls outside this range, just like the number
+ <literal>9</literal>. We can create tables <literal>ts2</literal> and
+ <literal>ts3</literal> having value lists containing
+ <literal>NULL</literal>, as shown here:
+ </para>
+
+<programlisting>
+mysql> CREATE TABLE ts2 (
+ -> c1 INT,
+ -> c2 VARCHAR(20)
+ -> )
+ -> PARTITION BY LIST(c1) (
+ -> PARTITION p0 VALUES IN (0, 3, 6),
+ -> PARTITION p1 VALUES IN (1, 4, 7),
+ -> PARTITION p2 VALUES IN (2, 5, 8),
+ -> PARTITION p3 VALUES IN (NULL)
+ -> );
+Query OK, 0 rows affected (0.01 sec)
+
+mysql> CREATE TABLE ts3 (
+ -> c1 INT,
+ -> c2 VARCHAR(20)
+ -> )
+ -> PARTITION BY LIST(c1) (
+ -> PARTITION p0 VALUES IN (0, 3, 6),
+ -> PARTITION p1 VALUES IN (1, 4, 7, NULL),
+ -> PARTITION p2 VALUES IN (2, 5, 8)
+ -> );
+Query OK, 0 rows affected (0.01 sec)
+</programlisting>
+
+ <para>
+ When defining value lists for partitioning, you can treat
+ <literal>NULL</literal> just as you would any other value, and
+ so <literal>VALUES IN (NULL)</literal> and <literal>VALUES IN
+ (1, 4, 7, NULL)</literal> are both valid (as are
+ <literal>VALUES IN (1, NULL, 4, 7)</literal>, <literal>VALUES IN
+ (NULL, 1, 4, 7)</literal>, and so on). You can insert a
+ row having <literal>NULL</literal> for column
+ <literal>c1</literal> into each of the tables
+ <literal>ts2</literal> and <literal>ts3</literal>:
+ </para>
+
+<programlisting>
+mysql> <userinput>INSERT INTO ts2 VALUES (NULL, 'mothra');</userinput>
+Query OK, 1 row affected (0.00 sec)
+
+mysql> <userinput>INSERT INTO ts3 VALUES (NULL, 'mothra');</userinput>
+Query OK, 1 row affected (0.00 sec)
+</programlisting>
+
+ <para>
+ By inspecting the filesystem, you can verify that the first of
+ these statements inserted a new row into partition
+ <literal>p3</literal> of table <literal>ts2</literal>, and that
+ the second statement inserted a new row into partition
+ <literal>p1</literal> of table <literal>ts3</literal>:
+ </para>
+
+<programlisting>
+/var/lib/mysql/test> <userinput>ls -l ts2*.MYD</userinput>
+-rw-rw---- 1 mysql mysql 0 2006-03-10 10:35 ts2#P#p0.MYD
+-rw-rw---- 1 mysql mysql 0 2006-03-10 10:35 ts2#P#p1.MYD
+-rw-rw---- 1 mysql mysql 0 2006-03-10 10:35 ts2#P#p2.MYD
+<emphasis role="bold">-rw-rw---- 1 mysql mysql 20 2006-03-10 10:35 ts2#P#p3.MYD</emphasis>
+
+/var/lib/mysql/test> <userinput>ls -l ts3*.MYD</userinput>
+-rw-rw---- 1 mysql mysql 0 2006-03-10 10:36 ts3#P#p0.MYD
+<emphasis role="bold">-rw-rw---- 1 mysql mysql 20 2006-03-10 10:36 ts3#P#p1.MYD</emphasis>
+-rw-rw---- 1 mysql mysql 0 2006-03-10 10:36 ts3#P#p2.MYD
+</programlisting>
+
+ <para>
+ As in earlier examples, we assume the use of the
+ <command>bash</command> shell on a Unix operating system for
+ listing files; use whatever your platform provides in this
+ regard. For example, if you are using a DOS shell on a Windows
+ operating system, the equivalent for the last listing might be
+ obtained by running the command <literal>dir ts3*.MYD</literal>
+ in the directory <filename>C:\Program Files\MySQL\MySQL Server
+ 5.1\data\test</filename>.
+ </para>
+
+ <para>
+ <literal>NULL</literal> is handled some what differently for
+ tables partitioned by <literal>HASH</literal> or
+ <literal>KEY</literal>. In these cases, any partition expression
+ that yields a <literal>NULL</literal> value is treated as though
+ its return value were zero. We can verify this behavior 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 that you have a
+ table <literal>th</literal>, created in the
+ <literal>test</literal> database, using this statement:
+ </para>
+
+<programlisting>
+mysql> <userinput>CREATE TABLE th (</userinput>
+ -> <userinput>c1 INT,</userinput>
+ -> <userinput>c2 VARCHAR(20)</userinput>
+ -> <userinput>)</userinput>
+ -> <userinput>PARTITION BY HASH(c1)</userinput>
+ -> <userinput>PARTITIONS 2;</userinput>
+Query OK, 0 rows affected (0.00 sec)
+</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
@@ -2004,37 +2183,28 @@
</para>
<programlisting>
-/var/lib/mysql/test> <userinput>ls *.MYD -l</userinput>
--rw-rw---- 1 mysql mysql 0 2005-11-04 18:41 tnhash#P#p0.MYD
--rw-rw---- 1 mysql mysql 0 2005-11-04 18:41 tnhash#P#p1.MYD
+/var/lib/mysql/test> <userinput>ls th*.MYD -l</userinput>
+-rw-rw---- 1 mysql mysql 0 2005-11-04 18:41 th#P#p0.MYD
+-rw-rw---- 1 mysql mysql 0 2005-11-04 18:41 th#P#p1.MYD
</programlisting>
<para>
- (<emphasis role="bold">Note</emphasis>: Prior to MySQL 5.1.5,
- these files would have been named
- <filename>tnhash_p0.MYD</filename> and
- <filename>tnhash_p1.MYD</filename>. See
- <xref linkend="news-5-1-6"/> and Bug #13437 for information
- regarding how this change impacts upgrades.)
- </para>
-
- <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
+ into <literal>th</literal> whose <literal>c1</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>
+mysql> <userinput>INSERT INTO th VALUES (NULL, 'mothra');</userinput>
Query OK, 1 row affected (0.00 sec)
-mysql> <userinput>SELECT * FROM tnhash;</userinput>
-+------+------+
-| id | name |
-+------+------+
-| NULL | sam |
-+------+------+
+mysql> <userinput>SELECT * FROM th;</userinput>
++------+---------+
+| c1 | c2 |
++------+---------+
+| NULL | mothra |
++------+---------+
1 row in set (0.01 sec)
</programlisting>
@@ -2042,52 +2212,41 @@
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:
+ <literal>NULL</literal>. For tables that are partitioned by
+ <literal>HASH</literal> or <literal>KEY</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#P#p0.MYD
--rw-rw---- 1 mysql mysql 0 2005-11-04 18:41 tnhash#P#p1.MYD
+-rw-rw---- 1 mysql mysql 20 2005-11-04 18:44 th#P#p0.MYD
+-rw-rw---- 1 mysql mysql 0 2005-11-04 18:41 th#P#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
+ modified only the file <filename>th#P#p0.MYD</filename>
+ (increasing its size on disk), without affecting the other data
file.
</para>
-
+
<para>
- Suppose that we have a table such as this one:
+ <emphasis role="bold">Important</emphasis>: Prior to MySQL
+ 5.1.8, <literal>RANGE</literal> partitioning treated a
+ partitioning expression value of <literal>NULL</literal> as a
+ zero with respect to determining placement (the only way to
+ circumvent this was to design tables so as not to allow nulls,
+ usually by declaring columns <literal>NOT NULL</literal>). If you
+ have a <literal>RANGE</literal> partitioning scheme that depends
+ on this earlier behavior, you will need to re-implement it when
+ upgrading to MySQL 5.1.8 or later.
</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>
Modified: trunk/refman-common/news-5.1.xml
===================================================================
--- trunk/refman-common/news-5.1.xml 2006-03-09 21:25:11 UTC (rev 1544)
+++ trunk/refman-common/news-5.1.xml 2006-03-10 02:11:17 UTC (rev 1545)
@@ -181,6 +181,17 @@
be available in MySQL 5.2 or later.
</para>
</listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change</emphasis>: For
+ purposes of determining placement, <literal>RANGE</literal>
+ partitioning now treats <literal>NULL</literal> as less than
+ any other value. (Formerly, <literal>NULL</literal> was
+ treated as euqal to zero.) See <xref
+ linkend="partitioning-handling-nulls"/>. (Bug #15447)
+ </para>
+ </listitem>
<listitem>
<para>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1545 - in trunk: refman-5.1 refman-common | jon | 10 Mar |
