Bug #33287 Can't retrieve Partition Comment, if the Table has Subpartitioning
Submitted: 17 Dec 2007 10:46 Modified: 17 Dec 2007 18:22
Reporter: Santo Leto Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.1.22-rc OS:Any
Assigned to: CPU Architecture:Any
Tags: information_schema.partitions, partition comment
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[17 Dec 2007 10:46] Santo Leto
Description:
The following contradiction occurs: if you have a partition which has one or more subpartitions, you can set a partition comment but you can't retrieve it.

How to repeat:
Test 1) - USING SUBPARTITIONING

DROP DATABASE IF EXISTS `partition_contradiction`;
CREATE DATABASE `partition_contradiction`;
USE `partition_contradiction`;

CREATE TABLE `ts` (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) COMMENT = 'comment for p0'(
            SUBPARTITION s0 COMMENT = 'comment for s0',
            SUBPARTITION s1 COMMENT = 'comment for s1'
        ),
        PARTITION p1 VALUES LESS THAN (2000) COMMENT = 'comment for p1'(
            SUBPARTITION s2 COMMENT = 'comment for s2',
            SUBPARTITION s3 COMMENT = 'comment for s3'
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE COMMENT = 'comment for p2' (
            SUBPARTITION s4 COMMENT = 'comment for s4',
            SUBPARTITION s5 COMMENT = 'comment for s5'
        )
    );

SELECT `PARTITION_NAME`,`PARTITION_COMMENT`, `PARTITION_ORDINAL_POSITION`, `PARTITION_METHOD`, `PARTITION_EXPRESSION`, `PARTITION_DESCRIPTION`, SUM(`TABLE_ROWS`) AS `Total Partition Rows`, `AVG_ROW_LENGTH`, SUM(`DATA_LENGTH`) AS `Total Data Length`,`MAX_DATA_LENGTH`, SUM(`DATA_FREE`) AS `Total Data Free`, `NODEGROUP`, `TABLESPACE_NAME` FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCHEMA` = 'partition_contradiction' AND `TABLE_NAME` = 'ts' AND `PARTITION_NAME` = 'p1' GROUP BY `PARTITION_DESCRIPTION`\G

SELECT * FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCHEMA` = 'partition_contradiction' AND `TABLE_NAME` = 'ts' AND `SUBPARTITION_NAME` = 's5'\G

Retrieving information about a partition:

mysql> SELECT `PARTITION_NAME`,`PARTITION_COMMENT`, `PARTITION_ORDINAL_POSITION`
, `PARTITION_METHOD`, `PARTITION_EXPRESSION`, `PARTITION_DESCRIPTION`, SUM(`TABL
E_ROWS`) AS `Total Partition Rows`, `AVG_ROW_LENGTH`, SUM(`DATA_LENGTH`) AS `Tot
al Data Length`,`MAX_DATA_LENGTH`, SUM(`DATA_FREE`) AS `Total Data Free`, `NODEG
ROUP`, `TABLESPACE_NAME` FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCH
EMA` = 'partition_contradiction' AND `TABLE_NAME` = 'ts' AND `PARTITION_NAME` =
'p1' GROUP BY `PARTITION_DESCRIPTION`\G
*************************** 1. row ***************************
            PARTITION_NAME: p1
         PARTITION_COMMENT: comment for s2
PARTITION_ORDINAL_POSITION: 2
          PARTITION_METHOD: RANGE
      PARTITION_EXPRESSION:  YEAR(purchased)
     PARTITION_DESCRIPTION: 2000
      Total Partition Rows: 0
            AVG_ROW_LENGTH: 0
         Total Data Length: 0
           MAX_DATA_LENGTH: 2251799813685247
           Total Data Free: 0
                 NODEGROUP: default
           TABLESPACE_NAME: NULL
1 row in set (0.02 sec)

Retrieving information about a subpartition:

SELECT * FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCHEMA` = 'partition_contradiction' AND `TABLE_NAME` = 'ts' AND `SUBPARTITION_NAME` = 's5'\G

mysql> SELECT * FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCHEMA` = 'p
artition_contradiction' AND `TABLE_NAME` = 'ts' AND `SUBPARTITION_NAME` = 's5'\G

*************************** 1. row ***************************
                TABLE_CATALOG: NULL
                 TABLE_SCHEMA: partition_contradiction
                   TABLE_NAME: ts
               PARTITION_NAME: p2
            SUBPARTITION_NAME: s5
   PARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: 2
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: HASH
         PARTITION_EXPRESSION:  YEAR(purchased)
      SUBPARTITION_EXPRESSION:  TO_DAYS(purchased)
        PARTITION_DESCRIPTION: MAXVALUE
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 0
              MAX_DATA_LENGTH: 2251799813685247
                 INDEX_LENGTH: 1024
                    DATA_FREE: 0
                  CREATE_TIME: 2007-12-17 10:59:32
                  UPDATE_TIME: 2007-12-17 10:59:34
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: comment for s5
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
1 row in set (0.01 sec)

Test 2) - SIMPLE PARTITIONING

DROP DATABASE IF EXISTS `partition_contradiction_simple`;
CREATE DATABASE `partition_contradiction_simple`;
USE `partition_contradiction_simple`;

CREATE TABLE `ts_simple` (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) COMMENT = 'comment for p0',
        PARTITION p1 VALUES LESS THAN (2000) COMMENT = 'comment for p1',
        PARTITION p2 VALUES LESS THAN MAXVALUE COMMENT = 'comment for p2'
    );

SELECT `PARTITION_NAME`, `PARTITION_COMMENT`, `PARTITION_ORDINAL_POSITION`, `PARTITION_METHOD`, `PARTITION_EXPRESSION`, `PARTITION_DESCRIPTION`, `TABLE_ROWS`, `AVG_ROW_LENGTH`, `DATA_LENGTH`, `MAX_DATA_LENGTH`, `INDEX_LENGTH`, `DATA_FREE`, `CREATE_TIME`, `UPDATE_TIME`, `CHECK_TIME`, `CHECKSUM`, `NODEGROUP`, `TABLESPACE_NAME` FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCHEMA` = 'partition_contradiction_simple' AND `TABLE_NAME` = 'ts_simple' AND `PARTITION_NAME` = 'p1'\G

Retrieving information about a partition:

mysql> SELECT `PARTITION_NAME`, `PARTITION_COMMENT`, `PARTITION_ORDINAL_POSITION
`, `PARTITION_METHOD`, `PARTITION_EXPRESSION`, `PARTITION_DESCRIPTION`, `TABLE_R
OWS`, `AVG_ROW_LENGTH`, `DATA_LENGTH`, `MAX_DATA_LENGTH`, `INDEX_LENGTH`, `DATA_
FREE`, `CREATE_TIME`, `UPDATE_TIME`, `CHECK_TIME`, `CHECKSUM`, `NODEGROUP`, `TAB
LESPACE_NAME` FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCHEMA` = 'par
tition_contradiction_simple' AND `TABLE_NAME` = 'ts_simple' AND `PARTITION_NAME`
 = 'p1'\G
*************************** 1. row ***************************
            PARTITION_NAME: p1
         PARTITION_COMMENT: comment for p1
PARTITION_ORDINAL_POSITION: 2
          PARTITION_METHOD: RANGE
      PARTITION_EXPRESSION:  YEAR(purchased)
     PARTITION_DESCRIPTION: 2000
                TABLE_ROWS: 0
            AVG_ROW_LENGTH: 0
               DATA_LENGTH: 0
           MAX_DATA_LENGTH: 2251799813685247
              INDEX_LENGTH: 1024
                 DATA_FREE: 0
               CREATE_TIME: 2007-12-17 11:07:10
               UPDATE_TIME: 2007-12-17 11:07:12
                CHECK_TIME: NULL
                  CHECKSUM: NULL
                 NODEGROUP: default
           TABLESPACE_NAME: NULL
1 row in set (0.00 sec)

Suggested fix:
In the case of simple partitioning, the server adds a row in information_schema.partitions for each partition and you can retrieve metadata.
In the case of partitioning with subpartitioning, the server adds a row in information_schema.partitions for each subpartition.
Thus, if you have a partition with 2 subpartitions, the server adds just 2 rows, relating to the subpartitions and it doesn't store the partition comment (and of course it can't). 

A simple solution (and probably an elegant way) is adding 3 rows: one for the partition and 2 for the subpartitions. But you shoud consider that, in this way, you do NOT preserve backward compatibility.

Maybe, you should find a solution that preserve backward compatibility. For example (but probably that's not an elegant way), you could add and extra-column in information_schema.partitions.

Suppose you add a `PARENT_PARTITION_COMMENT` column. Thus you could have something like:

mysql> SELECT `PARTITION_NAME`,`PARTITION_COMMENT`, `PARENT_PARTITION_COMMENT`, `PARTITION_ORDINAL_POSITION`, `PARTITION_METHOD`, `PARTITION_EXPRESSION`, `PARTITION_DESCRIPTION`, SUM(`TABLE_ROWS`) AS `Total Partition Rows`, `AVG_ROW_LENGTH`, SUM(`DATA_LENGTH`) AS `Total Data Length`,`MAX_DATA_LENGTH`, SUM(`DATA_FREE`) AS `Total Data Free`, `NODEGROUP`, `TABLESPACE_NAME` FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCHEMA` = 'partition_contradiction' AND `TABLE_NAME` = 'ts' AND `PARTITION_NAME` =
'p1' GROUP BY `PARTITION_DESCRIPTION`\G
*************************** 1. row ***************************
            PARTITION_NAME: p1
         PARTITION_COMMENT: comment for s2
  PARENT_PARTITION_COMMENT: comment for p1
PARTITION_ORDINAL_POSITION: 2
          PARTITION_METHOD: RANGE
      PARTITION_EXPRESSION:  YEAR(purchased)
     PARTITION_DESCRIPTION: 2000
      Total Partition Rows: 0
            AVG_ROW_LENGTH: 0
         Total Data Length: 0
           MAX_DATA_LENGTH: 2251799813685247
           Total Data Free: 0
                 NODEGROUP: default
           TABLESPACE_NAME: NULL
1 row in set (0.02 sec)
[17 Dec 2007 18:22] Susanne Ebrecht
Many thanks for writing a bug report.

Verified as described. I only get the subpartion comment and not the partition comment.
[12 Jan 2012 20:52] Mattias Jonsson
There is also a bug in that when specifying subpartitions with comments, the comment for the partitions is never recorded.

easy to verify by:
SHOW CREATE TABLE ts;
Table	Create Table
ts	CREATE TABLE `ts` (
  `id` int(11) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
SUBPARTITION BY HASH ( TO_DAYS(purchased))
(PARTITION p0 VALUES LESS THAN (1990)
 (SUBPARTITION s0 COMMENT = 'comment for s0' ENGINE = MyISAM,
  SUBPARTITION s1 COMMENT = 'comment for s1' ENGINE = MyISAM),
 PARTITION p1 VALUES LESS THAN (2000)
 (SUBPARTITION s2 COMMENT = 'comment for s2' ENGINE = MyISAM,
  SUBPARTITION s3 COMMENT = 'comment for s3' ENGINE = MyISAM),
 PARTITION p2 VALUES LESS THAN MAXVALUE
 (SUBPARTITION s4 COMMENT = 'comment for s4' ENGINE = MyISAM,
  SUBPARTITION s5 COMMENT = 'comment for s5' ENGINE = MyISAM)) */

No comments on p0, p1 or p2.

The solution to fix the I_S.PARTITIONS will be to add a new field SUBPARTITOIN_COMMENT.
[12 Jan 2012 22:14] Mattias Jonsson
It can be discussed if this is 'Not a bug':

Let us look at the syntax of partition_definition (from the manual):
http://dev.mysql.com/doc/refman/5.6/en/create-table.html

And call all the below options for partition_storage_options:
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]

If specified on partition level, then it should be used as defaults for all subpartitions within that partition (I have not verified this!).

If one specifies subpartitions, then all subpartitions needs to be specified. (i.e. not using SUBPARTITIONS N only but adds SUBPARTITION subpart_name [options])
Then the options from the partition will only be used if they are not specified for the subpartition. Which results in all given options on the partition level will be used if not overridden by the subpartition. So there are no meaning with recording them for the partitioning level, as they are only used as defaults for the subpartitions, and when the subpartitions are configured, they will have the correct options.

I understand that the comment can be useful on both levels independently, but since it is handled just like the other options it is natural that it is not stored on partition level, but only as default for subpartitions.

The diff for also storing the options given on partitioning level, when using named subpartitions is:
=== modified file 'sql/sql_partition.cc'
--- sql/sql_partition.cc        revid:tor.didriksen@oracle.com-20120111143530-5s
zscbo4fo5ba3vj
+++ sql/sql_partition.cc        2012-01-12 21:16:28 +0000
@@ -2602,6 +2602,8 @@ char *generate_partition_syntax(partitio
         }
         else
         {
+          if (show_partition_options)
+            err+= add_partition_options(fptr, part_elem);
           err+= add_string(fptr, "\n");
           err+= add_space(fptr);
           err+= add_begin_parenthesis(fptr);
[12 Jan 2012 22:24] Mattias Jonsson
And the diff for implementing SUBPARTITION_COMMENT is:
=== modified file 'sql/sql_show.cc'
--- sql/sql_show.cc     revid:tor.didriksen@oracle.com-20120111143530-5szscbo4fo
5ba3vj
+++ sql/sql_show.cc     2012-01-12 15:44:20 +0000
@@ -5894,27 +5894,22 @@ static void store_schema_partitions_reco
   }
   if (part_elem)
   {
-    if (part_elem->part_comment)
-      table->field[22]->store(part_elem->part_comment,
-                              strlen(part_elem->part_comment), cs);
-    else
-      table->field[22]->store(STRING_WITH_LEN(""), cs);
     if (part_elem->nodegroup_id != UNDEF_NODEGROUP)
-      table->field[23]->store((longlong) part_elem->nodegroup_id, TRUE);
+      table->field[24]->store((longlong) part_elem->nodegroup_id, TRUE);
     else
-      table->field[23]->store(STRING_WITH_LEN("default"), cs);
+      table->field[24]->store(STRING_WITH_LEN("default"), cs);

-    table->field[24]->set_notnull();
+    table->field[25]->set_notnull();
     if (part_elem->tablespace_name)
-      table->field[24]->store(part_elem->tablespace_name,
+      table->field[25]->store(part_elem->tablespace_name,
                               strlen(part_elem->tablespace_name), cs);
     else
     {
       char *ts= showing_table->s->tablespace;
       if(ts)
-        table->field[24]->store(ts, strlen(ts), cs);
+        table->field[25]->store(ts, strlen(ts), cs);
       else
-        table->field[24]->set_null();
+        table->field[25]->set_null();
     }
   }
   return;
@@ -6164,6 +6159,13 @@ static int get_schema_partitions_record(
         table->field[11]->set_notnull();
       }

+      /* PARTITION_COMMENT */
+      if (part_elem->part_comment)
+        table->field[22]->store(part_elem->part_comment,
+                                strlen(part_elem->part_comment), cs);
+      else
+        table->field[22]->store(STRING_WITH_LEN(""), cs);
+
       if (part_elem->subpartitions.elements)
       {
         List_iterator<partition_element> sub_it(part_elem->subpartitions);
@@ -6178,7 +6180,14 @@ static int get_schema_partitions_record(
           /* SUBPARTITION_ORDINAL_POSITION */
           table->field[6]->store((longlong) ++subpart_pos, TRUE);
           table->field[6]->set_notnull();
-
+
+          /* SUBPARTITION_COMMENT */
+          if (subpart_elem->part_comment)
+            table->field[23]->store(subpart_elem->part_comment,
+                                    strlen(subpart_elem->part_comment), cs);
+          else
+            table->field[23]->store(STRING_WITH_LEN(""), cs);
+
           store_schema_partitions_record(thd, table, show_table, subpart_elem,
                                          file, part_id);
           part_id++;
@@ -6188,6 +6197,9 @@ static int get_schema_partitions_record(
       }
       else
       {
+        /* SUBPARTITION_COMMENT */
+        table->field[23]->set_notnull();
+
         store_schema_partitions_record(thd, table, show_table, part_elem,
                                        file, part_id);
         part_id++;
@@ -7740,6 +7752,7 @@ ST_FIELD_INFO partitions_fields_info[]=
   {"CHECKSUM", 21 , MYSQL_TYPE_LONGLONG, 0,
    (MY_I_S_MAYBE_NULL | MY_I_S_UNSIGNED), 0, OPEN_FULL_TABLE},
   {"PARTITION_COMMENT", 80, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FULL_TABLE},
+  {"SUBPARTITION_COMMENT", 80, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FULL_TABLE},
   {"NODEGROUP", 12 , MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FULL_TABLE},
   {"TABLESPACE_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 1, 0,
    OPEN_FULL_TABLE},