Bug #16370 Partitions: subpartitions names not mentioned in SHOW CREATE TABLE output
Submitted: 11 Jan 2006 12:18 Modified: 16 Mar 2006 8:07
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1 OS:
Assigned to: Mikael Ronström CPU Architecture:Any

[11 Jan 2006 12:18] Matthias Leich
Description:
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
(PARTITION part1 VALUES LESS THAN (100));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) 
(PARTITION part1 VALUES LESS THAN (100) )
var/master-data/test/t1#P#part1#SP#sp0.MYD
var/master-data/test/t1#P#part1#SP#sp0.MYI
var/master-data/test/t1.frm
var/master-data/test/t1.par
# Attention: The file listing shows that subpartition files are created, but
#                 the SHOW CREATE output does not mention the subpartition
#                 name "sp0".
ALTER TABLE t1 ADD PARTITION
(PARTITION part2 VALUES LESS THAN (200) (SUBPARTITION subpart21));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) 
(PARTITION part1 VALUES LESS THAN (100) , 
PARTITION part2 VALUES LESS THAN (200) )
var/master-data/test/t1#P#part1#SP#sp0.MYD
var/master-data/test/t1#P#part1#SP#sp0.MYI
var/master-data/test/t1#P#part2#SP#subpart21.MYD
var/master-data/test/t1#P#part2#SP#subpart21.MYI
var/master-data/test/t1.frm
var/master-data/test/t1.par
# Again the subpartitions are created but not mentioned in
# SHOW CREATE TABLE.
DROP TABLE t1;
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
(PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) 
(PARTITION part1 VALUES LESS THAN (100)  
       (SUBPARTITION subpart11 ENGINE = MyISAM))
var/master-data/test/t1#P#part1#SP#subpart11.MYD
var/master-data/test/t1#P#part1#SP#subpart11.MYI
var/master-data/test/t1.frm
var/master-data/test/t1.par
# If the subpartitions are explicit named, they are within
# the show create output.
# SHOW CREATE TABLE.

It is an inconsistent behaviour, if the names of implicit (the CREATE TABLE
statement executed does not contain subpartition names) created
subpartitions are not mentioned within the SHOW CREATE TABLE ouput.
But somebody might argue "Why should we show names which are created
implicit."  So this might be intended and no bug.
But the following ALTER TABLE ADD adds subpartitions with explicit
assigned names and at least these names have to be shown in the
SHOW CREATE TABLE output.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
           bk-internal.mysql.com:/home/bk/mysql-5.1-wl2604-new
            ChangeSet@1.2028, 2006-01-11

How to repeat:
Please use my attached testscript ml085.test
  copy it to mysql-test/t
  echo "Dummy" > r/ml085.result   # Produce a dummy file with 
                                                   # expected results
  ./mysql-test-run ml085

Suggested fix:
Please let SHOW CREATE TABLE output show all names of subpartitions,
even if the system itself choosed the name of the subpartition.
[11 Jan 2006 12:20] Matthias Leich
testcase

Attachment: ml085.test (application/test, text), 573 bytes.

[13 Mar 2006 18:03] Jim Winstead
Patch looks okay to push to me.
[14 Mar 2006 17:47] Mikael Ronström
Default handling of subpartitions was erroneous, creating wrong output from SHOW
CREATE TABLE
[16 Mar 2006 8:07] 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.8 changelog. Closed.