Bug #13644 DROP PARTITION truncates DATE columns
Submitted: 30 Sep 2005 9:47 Modified: 20 Jan 2006 10:45
Reporter: Jon Stephens Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.2-a_drop5p4-log OS:Linux (SuSE Linux 10.0)
Assigned to: Mikael Ronström CPU Architecture:Any

[30 Sep 2005 9:47] Jon Stephens
Description:
A DATE column in a range-partitioned table has all its values set to NULL upon execution of ALTER TABLE ... DROP PARTITION.

It does not appear to make any difference which column is used as the basis for the partitioning range, nor which partition is dropped.

How to repeat:
Copied and pasted from MySQL Monitor:

mysql> CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
    ->     PARTITION BY RANGE(id)
    ->     (
    ->         PARTITION p0 VALUES LESS THAN (3),
    ->         PARTITION p1 VALUES LESS THAN (7),
    ->         PARTITION p2 VALUES LESS THAN (9),
    ->         PARTITION p3 VALUES LESS THAN (11)
    ->     );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO trb1 VALUES
    ->     (1, 'desk organiser', '2003-10-15'),
    ->     (2, 'CD player', '1993-11-05'),
    ->     (3, 'TV set', '1996-03-10'),
    ->     (4, 'bookcase', '1982-01-10'),
    ->     (5, 'exercise bike', '2004-05-09'),
    ->     (6, 'sofa', '1987-06-05'),
    ->     (7, 'popcorn maker', '2001-11-22'),
    ->     (8, 'aquarium', '1992-08-04'),
    ->     (9, 'study desk', '1984-09-16'),
    ->     (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM trb1 ORDER BY id;
+------+----------------+------------+
| id   | name           | purchased  |
+------+----------------+------------+
|    1 | desk organiser | 2003-10-15 |
|    2 | CD player      | 1993-11-05 |
|    3 | TV set         | 1996-03-10 |
|    4 | bookcase       | 1982-01-10 |
|    5 | exercise bike  | 2004-05-09 |
|    6 | sofa           | 1987-06-05 |
|    7 | popcorn maker  | 2001-11-22 |
|    8 | aquarium       | 1992-08-04 |
|    9 | study desk     | 1984-09-16 |
|   10 | lava lamp      | 1998-12-25 |
+------+----------------+------------+
10 rows in set (0.00 sec)

mysql> ALTER TABLE trb1 DROP PARTITION p0;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM trb1 ORDER BY id;
+------+---------------+-----------+
| id   | name          | purchased |
+------+---------------+-----------+
|    3 | TV set        | NULL      |
|    4 | bookcase      | NULL      |
|    5 | exercise bike | NULL      |
|    6 | sofa          | NULL      |
|    7 | popcorn maker | NULL      |
|    8 | aquarium      | NULL      |
|    9 | study desk    | NULL      |
|   10 | lava lamp     | NULL      |
+------+---------------+-----------+
8 rows in set (0.00 sec)

Suggested fix:
Don't truncate DATE column values when dropping a RANGE partition from a table containing a DATE column.
[19 Oct 2005 10:40] Jon Stephens
Changed category to Server/Partitioning.
[19 Oct 2005 16:43] Mikael Ronström
table options were screwed up when creating new frm file which lead to that the position of the
null bits became one away so all null bits used the wrong the bit after the ALTER TABLE
[19 Jan 2006 9:00] Mikael Ronström
Patch now pushed and will appear in 5.1.6
[20 Jan 2006 10:45] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.1.6 changelog. Closed.