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.
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.