[ Avaa Bypassed ]




Upload:

Command:

hmhc3928@18.117.254.202: ~ $
SET @max_row = 20;
SET @@session.default_storage_engine = 'MyISAM';

#------------------------------------------------------------------------
#  0. Setting of auxiliary variables + Creation of an auxiliary tables
#     needed in many testcases
#------------------------------------------------------------------------
SELECT @max_row DIV 2 INTO @max_row_div2;
SELECT @max_row DIV 3 INTO @max_row_div3;
SELECT @max_row DIV 4 INTO @max_row_div4;
SET @max_int_4 = 2147483647;
DROP TABLE IF EXISTS t0_template;
CREATE TABLE t0_template (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000) ,
PRIMARY KEY(f_int1))
ENGINE = MEMORY;
#     Logging of <max_row> INSERTs into t0_template suppressed
DROP TABLE IF EXISTS t0_definition;
CREATE TABLE t0_definition (
state CHAR(3),
create_command VARBINARY(5000),
file_list      VARBINARY(10000),
PRIMARY KEY (state)
) ENGINE = MEMORY;
DROP TABLE IF EXISTS t0_aux;
CREATE TABLE t0_aux ( f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000) )
ENGINE = MEMORY;
SET AUTOCOMMIT= 1;
SET @@session.sql_mode= '';
# End of basic preparations needed for all tests
#-----------------------------------------------

#========================================================================
#  1.    Any PRIMARY KEYs or UNIQUE INDEXes must contain the columns used
#        within the partitioning functions
#========================================================================
DROP TABLE IF EXISTS t1;
#------------------------------------------------------------------------
#  1.1 column of partitioning function not included in PRIMARY KEY
#               PARTITION BY HASH/KEY/LIST/RANGE
#------------------------------------------------------------------------
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY (f_int2)
)
PARTITION BY HASH(f_int1) PARTITIONS 2;
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY (f_int2)
)
PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2;
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY (f_int2)
)
PARTITION BY KEY(f_int1) PARTITIONS 2;
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY (f_int2)
)
PARTITION BY KEY(f_int1,f_int2) PARTITIONS 2;
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY (f_int2)
)
PARTITION BY LIST(f_int1)
(PARTITION part1 VALUES IN (1));
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY (f_int2)
)
PARTITION BY LIST(f_int1 + f_int2)
(PARTITION part1 VALUES IN (1));
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY (f_int2)
)
PARTITION BY RANGE(f_int1)
(PARTITION part1 VALUES LESS THAN (1));
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY (f_int2)
)
PARTITION BY RANGE(f_int1 + f_int2)
(PARTITION part1 VALUES LESS THAN (1));
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
#------------------------------------------------------------------------
#  1.2 column of partitioning function not included in UNIQUE INDEX
#               PARTITION BY HASH/KEY/LIST/RANGE
#      Variant a) Without additional PRIMARY KEY        
#      Variant b) With correct additional PRIMARY KEY        
#      Variant 1) one column in partitioning function
#      Variant 2) two columns in partitioning function
#------------------------------------------------------------------------
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
UNIQUE INDEX (f_int2)
)
PARTITION BY HASH(f_int1) PARTITIONS 2;
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
)
PARTITION BY HASH(f_int1) PARTITIONS 2;
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
UNIQUE INDEX (f_int2)
)
PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2;
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
)
PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2;
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
UNIQUE INDEX (f_int2)
)
PARTITION BY KEY(f_int1) PARTITIONS 2;
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
)
PARTITION BY KEY(f_int1) PARTITIONS 2;
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
UNIQUE INDEX (f_int2)
)
PARTITION BY KEY(f_int1,f_int2) PARTITIONS 2;
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
)
PARTITION BY KEY(f_int1,f_int2) PARTITIONS 2;
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
UNIQUE INDEX (f_int2)
)
PARTITION BY LIST(MOD(f_int1,3))
(PARTITION partN VALUES IN (NULL),
PARTITION part0 VALUES IN (0),
PARTITION part1 VALUES IN (1),
PARTITION part2 VALUES IN (2));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
)
PARTITION BY LIST(MOD(f_int1,3))
(PARTITION partN VALUES IN (NULL),
PARTITION part0 VALUES IN (0),
PARTITION part1 VALUES IN (1),
PARTITION part2 VALUES IN (2));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
UNIQUE INDEX (f_int2)
)
PARTITION BY LIST(MOD(f_int1 + f_int2,3))
(PARTITION partN VALUES IN (NULL),
PARTITION part0 VALUES IN (0),
PARTITION part1 VALUES IN (1),
PARTITION part2 VALUES IN (2));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
)
PARTITION BY LIST(MOD(f_int1 + f_int2,3))
(PARTITION partN VALUES IN (NULL),
PARTITION part0 VALUES IN (0),
PARTITION part1 VALUES IN (1),
PARTITION part2 VALUES IN (2));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
UNIQUE INDEX (f_int2)
)
PARTITION BY RANGE(f_int1)
(PARTITION part1 VALUES LESS THAN (1),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
)
PARTITION BY RANGE(f_int1)
(PARTITION part1 VALUES LESS THAN (1),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
UNIQUE INDEX (f_int2)
)
PARTITION BY RANGE(f_int1 + f_int2)
(PARTITION part1 VALUES LESS THAN (1),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
)
PARTITION BY RANGE(f_int1 + f_int2)
(PARTITION part1 VALUES LESS THAN (1),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
#------------------------------------------------------------------------
#  1.3 column of subpartitioning function not included in PRIMARY KEY
#               PARTITION BY RANGE/LIST -- SUBPARTITION BY HASH/KEY
#------------------------------------------------------------------------
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY (f_int2)
)
PARTITION BY RANGE(f_int2) SUBPARTITION BY HASH(f_int1)
(PARTITION part1 VALUES LESS THAN (1)
(SUBPARTITION subpart1));
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY (f_int2)
)
PARTITION BY RANGE(f_int2) SUBPARTITION BY KEY(f_int1)
(PARTITION part1 VALUES LESS THAN (1)
(SUBPARTITION subpart1));
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY (f_int2)
)
PARTITION BY LIST(f_int2) SUBPARTITION BY HASH(f_int1)
(PARTITION part1 VALUES IN (1)
(SUBPARTITION subpart1));
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY (f_int2)
)
PARTITION BY LIST(f_int2) SUBPARTITION BY KEY(f_int1)
(PARTITION part1 VALUES IN (1)
(SUBPARTITION subpart1));
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
#------------------------------------------------------------------------
#  1.4 column of subpartitioning function not included in UNIQUE INDEX
#               PARTITION BY RANGE/LIST -- SUBPARTITION BY HASH/KEY
#      Variant a) Without additional PRIMARY KEY        
#      Variant b) With correct additional PRIMARY KEY        
#------------------------------------------------------------------------
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
UNIQUE INDEX (f_int2)
)
PARTITION BY RANGE(f_int2)
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 3
(PARTITION part1 VALUES LESS THAN (1),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
)
PARTITION BY RANGE(f_int2)
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 3
(PARTITION part1 VALUES LESS THAN (1),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
UNIQUE INDEX (f_int2)
)
PARTITION BY RANGE(f_int2)
SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 3
(PARTITION part1 VALUES LESS THAN (1),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
)
PARTITION BY RANGE(f_int2)
SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 3
(PARTITION part1 VALUES LESS THAN (1),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
UNIQUE INDEX (f_int2)
)
PARTITION BY LIST(MOD(f_int2,3))
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
(PARTITION partN VALUES IN (NULL),
PARTITION part0 VALUES IN (0),
PARTITION part1 VALUES IN (1),
PARTITION part2 VALUES IN (2));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
)
PARTITION BY LIST(MOD(f_int2,3))
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
(PARTITION partN VALUES IN (NULL),
PARTITION part0 VALUES IN (0),
PARTITION part1 VALUES IN (1),
PARTITION part2 VALUES IN (2));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
UNIQUE INDEX (f_int2)
)
PARTITION BY LIST(MOD(f_int2,3))
SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 2
(PARTITION partN VALUES IN (NULL),
PARTITION part0 VALUES IN (0),
PARTITION part1 VALUES IN (1),
PARTITION part2 VALUES IN (2));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
)
PARTITION BY LIST(MOD(f_int2,3))
SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 2
(PARTITION partN VALUES IN (NULL),
PARTITION part0 VALUES IN (0),
PARTITION part1 VALUES IN (1),
PARTITION part2 VALUES IN (2));
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function

#========================================================================
#  2   Some properties around subpartitioning
#========================================================================
#------------------------------------------------------------------------
#  2.1 Subpartioned table without subpartitioning rule must be rejected
#------------------------------------------------------------------------
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1)
( PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11));
ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
#------------------------------------------------------------------------
#  2.2 Every partition must have the same number of subpartitions.
#      This is a limitation of MySQL 5.1, which could be removed in
#      later releases.
#------------------------------------------------------------------------
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000),
PRIMARY KEY (f_int1)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY KEY(f_int1)
(
PARTITION part1 VALUES LESS THAN (0)
(SUBPARTITION subpart1),
PARTITION part2 VALUES LESS THAN (5)
(SUBPARTITION subpart1, SUBPARTITION subpart2));
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '))' at line 14

#========================================================================
#  3   VALUES clauses
#========================================================================
#------------------------------------------------------------------------
#  3.1 The constants in VALUES IN clauses must differ
#------------------------------------------------------------------------
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY LIST(MOD(f_int1,2))
( PARTITION part1 VALUES IN (-1),
PARTITION part2 VALUES IN (0),
PARTITION part3 VALUES IN (-1));
ERROR HY000: Multiple definition of same constant in list partitioning
CREATE TABLE t1 (f1 BIGINT, f2 BIGINT)
PARTITION BY RANGE(f1)
(PARTITION part1 VALUES LESS THAN (0),
PARTITION part2 VALUES LESS THAN (0),
PARTITION part3 VALUES LESS THAN (10000));
ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
#------------------------------------------------------------------------
#  3.2 The constants in VALUES LESS must be in increasing order
#------------------------------------------------------------------------
CREATE TABLE t1 (f1 BIGINT, f2 BIGINT)
PARTITION BY RANGE(f1)
(PARTITION part1 VALUES LESS THAN (0),
PARTITION part2 VALUES LESS THAN (-1),
PARTITION part3 VALUES LESS THAN (10000));
ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
#------------------------------------------------------------------------
#  3.3 LIST partitions must be defined with VALUES IN
#------------------------------------------------------------------------
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY LIST(MOD(f_int1,2))
( PARTITION part1 VALUES LESS THAN (-1),
PARTITION part2 VALUES LESS THAN (0),
PARTITION part3 VALUES LESS THAN (1000));
ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
#------------------------------------------------------------------------
#  3.4 RANGE partitions must be defined with VALUES LESS THAN
#------------------------------------------------------------------------
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1)
( PARTITION part1 VALUES IN (-1),
PARTITION part2 VALUES IN (0),
PARTITION part3 VALUES IN (1000));
ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
#------------------------------------------------------------------------
#  3.5 Use of NULL in VALUES clauses
#------------------------------------------------------------------------
#  3.5.1 NULL in RANGE partitioning clause
#  3.5.1.1 VALUE LESS THAN (NULL) is not allowed
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1)
( PARTITION part1 VALUES LESS THAN (NULL),
PARTITION part2 VALUES LESS THAN (1000));
ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN
#  3.5.1.2 VALUE LESS THAN (NULL) is not allowed
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1)
( PARTITION part1 VALUES LESS THAN (NULL),
PARTITION part2 VALUES LESS THAN (1000));
ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN
#  3.5.2 NULL in LIST partitioning clause
#  3.5.2.1 VALUE IN (NULL)
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY LIST(MOD(f_int1,2))
( PARTITION part1 VALUES IN (NULL),
PARTITION part2 VALUES IN (0),
PARTITION part3 VALUES IN (1));
DROP TABLE t1;
#  3.5.2.2 VALUE IN (NULL)
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY LIST(MOD(f_int1,2))
( PARTITION part1 VALUES IN (NULL),
PARTITION part3 VALUES IN (1));
create_command
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (MOD(f_int1,2))
(PARTITION part1 VALUES IN (NULL) ENGINE = MyISAM,
 PARTITION part3 VALUES IN (1) ENGINE = MyISAM) */

unified filelist
t1#P#part1.MYD
t1#P#part1.MYI
t1#P#part3.MYD
t1#P#part3.MYI
t1.frm
t1.par

DROP TABLE t1;
#  3.5.3 Reveal that IN (...NULL) is not mapped to IN(0)
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY LIST(MOD(f_int1,2))
( PARTITION part1 VALUES IN (NULL),
PARTITION part2 VALUES IN (0),
PARTITION part3 VALUES IN (1));
create_command
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (MOD(f_int1,2))
(PARTITION part1 VALUES IN (NULL) ENGINE = MyISAM,
 PARTITION part2 VALUES IN (0) ENGINE = MyISAM,
 PARTITION part3 VALUES IN (1) ENGINE = MyISAM) */

unified filelist
t1#P#part1.MYD
t1#P#part1.MYI
t1#P#part2.MYD
t1#P#part2.MYI
t1#P#part3.MYD
t1#P#part3.MYI
t1.frm
t1.par

DROP TABLE t1;

#========================================================================
#  4. Check assigning the number of partitions and subpartitions
#     with and without named partitions/subpartitions
#========================================================================
DROP TABLE IF EXISTS t1;
#------------------------------------------------------------------------
# 4.1 (positive) without partition/subpartition number assignment
#------------------------------------------------------------------------
# 4.1.1 no partition number, no named partitions
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1);
create_command
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (f_int1) */

unified filelist
t1#P#p0.MYD
t1#P#p0.MYI
t1.frm
t1.par

DROP TABLE t1;
# 4.1.2 no partition number, named partitions
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) (PARTITION part1, PARTITION part2);
create_command
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (f_int1)
(PARTITION part1 ENGINE = MyISAM,
 PARTITION part2 ENGINE = MyISAM) */

unified filelist
t1#P#part1.MYD
t1#P#part1.MYI
t1#P#part2.MYD
t1#P#part2.MYI
t1.frm
t1.par

DROP TABLE t1;
# 4.1.3 variations on no partition/subpartition number, named partitions,
#       different subpartitions are/are not named
CREATE TABLE t1 (  f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000) )
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646)) ;
DROP TABLE t1;
CREATE TABLE t1 (  f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000) )
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near 'SUBPARTITION subpart31 , SUBPARTITION subpart32 ))' at line 7
CREATE TABLE t1 (  f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000) )
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20)
(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)) ;
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near 'SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS T' at line 7
CREATE TABLE t1 (  f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000) )
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20)
(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near 'SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS T' at line 7
CREATE TABLE t1 (  f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000) )
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10)
(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646)) ;
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ' PARTITION part3 VALUES LESS THAN (2147483646))' at line 7
CREATE TABLE t1 (  f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000) )
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10)
(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ' PARTITION part3 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart31 , SUBPART' at line 7
CREATE TABLE t1 (  f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000) )
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10)
(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20)
(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)) ;
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')' at line 8
CREATE TABLE t1 (  f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000) )
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10)
(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20)
(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
create_command
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (f_int1)
SUBPARTITION BY HASH (f_int1)
(PARTITION part1 VALUES LESS THAN (10)
 (SUBPARTITION subpart11 ENGINE = MyISAM,
  SUBPARTITION subpart12 ENGINE = MyISAM),
 PARTITION part2 VALUES LESS THAN (20)
 (SUBPARTITION subpart21 ENGINE = MyISAM,
  SUBPARTITION subpart22 ENGINE = MyISAM),
 PARTITION part3 VALUES LESS THAN (2147483646)
 (SUBPARTITION subpart31 ENGINE = MyISAM,
  SUBPARTITION subpart32 ENGINE = MyISAM)) */

unified filelist
t1#P#part1#SP#subpart11.MYD
t1#P#part1#SP#subpart11.MYI
t1#P#part1#SP#subpart12.MYD
t1#P#part1#SP#subpart12.MYI
t1#P#part2#SP#subpart21.MYD
t1#P#part2#SP#subpart21.MYI
t1#P#part2#SP#subpart22.MYD
t1#P#part2#SP#subpart22.MYI
t1#P#part3#SP#subpart31.MYD
t1#P#part3#SP#subpart31.MYI
t1#P#part3#SP#subpart32.MYD
t1#P#part3#SP#subpart32.MYI
t1.frm
t1.par

DROP TABLE t1;
#------------------------------------------------------------------------
# 4.2 partition/subpartition numbers good and bad values and notations
#------------------------------------------------------------------------
DROP TABLE IF EXISTS t1;
# 4.2.1 partition/subpartition numbers INTEGER notation
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 2;
create_command
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (f_int1)
PARTITIONS 2 */

unified filelist
t1#P#p0.MYD
t1#P#p0.MYI
t1#P#p1.MYD
t1#P#p1.MYI
t1.frm
t1.par

DROP TABLE t1;
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 2
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
create_command
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (f_int1)
SUBPARTITION BY HASH (f_int1)
SUBPARTITIONS 2
(PARTITION part1 VALUES LESS THAN (10) ENGINE = MyISAM,
 PARTITION part2 VALUES LESS THAN (2147483646) ENGINE = MyISAM) */

unified filelist
t1#P#part1#SP#part1sp0.MYD
t1#P#part1#SP#part1sp0.MYI
t1#P#part1#SP#part1sp1.MYD
t1#P#part1#SP#part1sp1.MYI
t1#P#part2#SP#part2sp0.MYD
t1#P#part2#SP#part2sp0.MYI
t1#P#part2#SP#part2sp1.MYD
t1#P#part2#SP#part2sp1.MYI
t1.frm
t1.par

DROP TABLE t1;
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 1;
create_command
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (f_int1)
PARTITIONS 1 */

unified filelist
t1#P#p0.MYD
t1#P#p0.MYI
t1.frm
t1.par

DROP TABLE t1;
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 1
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
create_command
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (f_int1)
SUBPARTITION BY HASH (f_int1)
SUBPARTITIONS 1
(PARTITION part1 VALUES LESS THAN (10) ENGINE = MyISAM,
 PARTITION part2 VALUES LESS THAN (2147483646) ENGINE = MyISAM) */

unified filelist
t1#P#part1#SP#part1sp0.MYD
t1#P#part1#SP#part1sp0.MYI
t1#P#part2#SP#part2sp0.MYD
t1#P#part2#SP#part2sp0.MYI
t1.frm
t1.par

DROP TABLE t1;
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 0;
ERROR HY000: Number of partitions = 0 is not an allowed value
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 0
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR HY000: Number of subpartitions = 0 is not an allowed value
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS -1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS -1
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (214' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 1000000;
ERROR HY000: Too many partitions (including subpartitions) were defined
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 1000000
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR HY000: Too many partitions (including subpartitions) were defined
# 4.2.2 partition/subpartition numbers DECIMAL notation
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 2.0;
ERROR 42000: Only integers allowed as number here near '2.0' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 2.0
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: Only integers allowed as number here near '2.0
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (21' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS -2.0;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS -2.0
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 0.0;
ERROR 42000: Only integers allowed as number here near '0.0' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 0.0
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: Only integers allowed as number here near '0.0
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (21' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 1.6;
ERROR 42000: Only integers allowed as number here near '1.6' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 1.6
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: Only integers allowed as number here near '1.6
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (21' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 999999999999999999999999999999.999999999999999999999999999999;
ERROR 42000: Only integers allowed as number here near '999999999999999999999999999999.999999999999999999999999999999' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 999999999999999999999999999999.999999999999999999999999999999
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: Only integers allowed as number here near '999999999999999999999999999999.999999999999999999999999999999
(PARTITION part1 V' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 0.000000000000000000000000000001;
ERROR 42000: Only integers allowed as number here near '0.000000000000000000000000000001' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 0.000000000000000000000000000001
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: Only integers allowed as number here near '0.000000000000000000000000000001
(PARTITION part1 VALUES LESS THAN (10),
PARTITI' at line 9
# 4.2.3 partition/subpartition numbers FLOAT notation
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 2.0E+0;
ERROR 42000: Only integers allowed as number here near '2.0E+0' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 2.0E+0
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: Only integers allowed as number here near '2.0E+0
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN ' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 0.2E+1;
ERROR 42000: Only integers allowed as number here near '0.2E+1' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 0.2E+1
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: Only integers allowed as number here near '0.2E+1
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN ' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS -2.0E+0;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0E+0' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS -2.0E+0
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0E+0
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 0.16E+1;
ERROR 42000: Only integers allowed as number here near '0.16E+1' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 0.16E+1
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: Only integers allowed as number here near '0.16E+1
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 0.0E+300;
ERROR 42000: Only integers allowed as number here near '0.0E+300' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 0.0E+300
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: Only integers allowed as number here near '0.0E+300
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THA' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 1E+300;
ERROR 42000: Only integers allowed as number here near '1E+300' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 1E+300
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: Only integers allowed as number here near '1E+300
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN ' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 1E-300;
ERROR 42000: Only integers allowed as number here near '1E-300' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 1E-300
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: Only integers allowed as number here near '1E-300
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN ' at line 9
# 4.2.4 partition/subpartition numbers STRING notation
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS '2';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS '2'
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (21' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS '2.0';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0'' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS '2.0'
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0'
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS '0.2E+1';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1'' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS '0.2E+1'
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1'
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THA' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS '2A';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A'' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS '2A'
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A'
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 'A2';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2'' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 'A2'
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2'
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS '';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS ''
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (214' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 'GARBAGE';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE'' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 'GARBAGE'
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE'
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS TH' at line 9
# 4.2.5 partition/subpartition numbers other notations
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 2A;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS 2A
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (214' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS A2;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS A2
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (214' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS GARBAGE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS GARBAGE
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS "2";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2"' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS "2"
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2"
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (21' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS "2A";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A"' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS "2A"
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A"
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS "A2";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2"' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS "A2"
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2"
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2' at line 9
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS "GARBAGE";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE"' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS "GARBAGE"
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE"
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS TH' at line 9
# 4.2.6 (negative) partition/subpartition numbers per @variables
SET @aux = 5;
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS @aux;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@aux' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
SUBPARTITIONS @aux = 5
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (2147483646));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@aux = 5
(PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THA' at line 9
#------------------------------------------------------------------------
# 4.3 Mixups of assigned partition/subpartition numbers and names
#------------------------------------------------------------------------
# 4.3.1 (positive) number of partition/subpartition
#                         = number of named partition/subpartition
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 2 ( PARTITION part1, PARTITION part2 ) ;
create_command
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (f_int1)
(PARTITION part1 ENGINE = MyISAM,
 PARTITION part2 ENGINE = MyISAM) */

unified filelist
t1#P#part1.MYD
t1#P#part1.MYI
t1#P#part2.MYD
t1#P#part2.MYI
t1.frm
t1.par

DROP TABLE t1;
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) PARTITIONS 2
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart21, SUBPARTITION subpart22)
);
create_command
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (f_int1)
SUBPARTITION BY HASH (f_int1)
(PARTITION part1 VALUES LESS THAN (1000)
 (SUBPARTITION subpart11 ENGINE = MyISAM,
  SUBPARTITION subpart12 ENGINE = MyISAM),
 PARTITION part2 VALUES LESS THAN (2147483646)
 (SUBPARTITION subpart21 ENGINE = MyISAM,
  SUBPARTITION subpart22 ENGINE = MyISAM)) */

unified filelist
t1#P#part1#SP#subpart11.MYD
t1#P#part1#SP#subpart11.MYI
t1#P#part1#SP#subpart12.MYD
t1#P#part1#SP#subpart12.MYI
t1#P#part2#SP#subpart21.MYD
t1#P#part2#SP#subpart21.MYI
t1#P#part2#SP#subpart22.MYD
t1#P#part2#SP#subpart22.MYI
t1.frm
t1.par

DROP TABLE t1;
# 4.3.2 (positive) number of partition/subpartition ,
#                  0 (= no) named partition/subpartition
#                  already checked above
# 4.3.3 (negative) number of partitions/subpartitions
#                         > number of named partitions/subpartitions
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 2 ( PARTITION part1 ) ;
ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1)
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11 ),
PARTITION part2 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart21, SUBPARTITION subpart22)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
PARTITION part2 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart21, SUBPAR' at line 11
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1)
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2000)
(SUBPARTITION subpart21 ),
PARTITION part3 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart31, SUBPARTITION subpart32)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
PARTITION part3 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart31, SUBPAR' at line 13
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1) PARTITIONS 2
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart21                        )
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')
)' at line 13
# 4.3.4 (negative) number of partitions < number of named partitions
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) PARTITIONS 1 ( PARTITION part1, PARTITION part2 ) ;
ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 8
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1)
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart21, SUBPARTITION subpart22)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
PARTITION part2 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart21, SUBPAR' at line 11
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1)
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2000)
(SUBPARTITION subpart21                        ),
PARTITION part3 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart31, SUBPARTITION subpart32)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
PARTITION part2 VALUES LESS THAN (2000)
(SUBPARTITION subpart21              ' at line 11
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1)
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart21, SUBPARTITION subpart22)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
PARTITION part2 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart21, SUBPAR' at line 11

#========================================================================
#  5. Checks of logical partition/subpartition name
#     file name clashes during CREATE TABLE
#========================================================================
DROP TABLE IF EXISTS t1;
#------------------------------------------------------------------------
#  5.1 (negative) A partition/subpartition name used more than once
#------------------------------------------------------------------------
#  5.1.1 duplicate partition name
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY HASH(f_int1) (PARTITION part1, PARTITION part1);
ERROR HY000: Duplicate partition name part1
#  5.1.2 duplicate subpartition name
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1)
SUBPARTITION BY HASH(f_int1)
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart11)
);
ERROR HY000: Duplicate partition name subpart11
DROP VIEW  IF EXISTS v1;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t0_aux;
DROP TABLE IF EXISTS t0_definition;
DROP TABLE IF EXISTS t0_template;

Filemanager

Name Type Size Permission Actions
part_blocked_sql_func_innodb.result File 159.68 KB 0644
part_blocked_sql_func_myisam.result File 159.68 KB 0644
part_ctype_utf32.result File 355 B 0644
part_supported_sql_func_innodb.result File 238.66 KB 0644
part_supported_sql_func_myisam.result File 238.66 KB 0644
partition-dml-1-1-innodb-modes.result File 19.2 KB 0644
partition-dml-1-1-innodb.result File 8.38 KB 0644
partition-dml-1-1-myisam-modes.result File 19.2 KB 0644
partition-dml-1-1-myisam.result File 8.38 KB 0644
partition-dml-1-10-innodb.result File 5.37 KB 0644
partition-dml-1-10-myisam.result File 5.29 KB 0644
partition-dml-1-11-innodb.result File 7.08 KB 0644
partition-dml-1-11-myisam.result File 7.08 KB 0644
partition-dml-1-2-innodb.result File 6.11 KB 0644
partition-dml-1-2-myisam.result File 6.11 KB 0644
partition-dml-1-3-innodb.result File 7.27 KB 0644
partition-dml-1-4-innodb.result File 5.5 KB 0644
partition-dml-1-5-innodb.result File 5.52 KB 0644
partition-dml-1-6-innodb.result File 5.52 KB 0644
partition-dml-1-7-innodb.result File 6.96 KB 0644
partition-dml-1-8-innodb.result File 6.94 KB 0644
partition-dml-1-9-innodb.result File 18.38 KB 0644
partition-dml-1-9-myisam.result File 18.38 KB 0644
partition_alter1_1_2_innodb.result File 1020.49 KB 0644
partition_alter1_1_2_myisam.result File 303.96 KB 0644
partition_alter1_1_innodb.result File 596.87 KB 0644
partition_alter1_1_myisam.result File 309.05 KB 0644
partition_alter1_2_innodb.result File 1.26 MB 0644
partition_alter1_2_myisam.result File 539.78 KB 0644
partition_alter2_1_1_innodb.result File 718.67 KB 0644
partition_alter2_1_2_innodb.result File 719.61 KB 0644
partition_alter2_1_myisam.result File 875.52 KB 0644
partition_alter2_2_1_innodb.result File 720.91 KB 0644
partition_alter2_2_2_innodb.result File 725.47 KB 0644
partition_alter2_2_myisam.result File 880.39 KB 0644
partition_alter3_innodb.result File 27.19 KB 0644
partition_alter3_myisam.result File 28.01 KB 0644
partition_alter4_innodb.result File 3.12 MB 0644
partition_alter4_myisam.result File 3.18 MB 0644
partition_auto_increment_archive.result File 21.62 KB 0644
partition_auto_increment_blackhole.result File 24.09 KB 0644
partition_auto_increment_innodb.result File 23.72 KB 0644
partition_auto_increment_memory.result File 23.73 KB 0644
partition_auto_increment_myisam.result File 23.89 KB 0644
partition_basic_innodb.result File 1.11 MB 0644
partition_basic_myisam.result File 566.34 KB 0644
partition_basic_symlink_innodb.result File 4.11 KB 0644
partition_basic_symlink_myisam.result File 753.67 KB 0644
partition_bit_innodb.result File 6.45 KB 0644
partition_bit_myisam.result File 6.45 KB 0644
partition_char_innodb.result File 56.13 KB 0644
partition_char_myisam.result File 56.13 KB 0644
partition_datetime_innodb.result File 37.62 KB 0644
partition_datetime_myisam.result File 37.62 KB 0644
partition_debug.result File 57.71 KB 0644
partition_debug_innodb.result File 243.04 KB 0644
partition_debug_myisam.result File 192.25 KB 0644
partition_debug_sync_innodb.result File 2.56 KB 0644
partition_decimal_innodb.result File 5.53 KB 0644
partition_decimal_myisam.result File 5.54 KB 0644
partition_engine_innodb.result File 182.75 KB 0644
partition_engine_myisam.result File 184.74 KB 0644
partition_exch_innodb.result File 6.52 KB 0644
partition_exch_myisam.result File 6.52 KB 0644
partition_exch_myisam_innodb.result File 484 B 0644
partition_exch_qa.result File 6.52 KB 0644
partition_exch_qa_10.result File 1.67 KB 0644
partition_exch_qa_11.result File 1.36 KB 0644
partition_exch_qa_12.result File 2.4 KB 0644
partition_exch_qa_13.result File 3.87 KB 0644
partition_exch_qa_14.result File 1.32 KB 0644
partition_exch_qa_15.result File 624 B 0644
partition_exch_qa_1_innodb.result File 4.06 KB 0644
partition_exch_qa_1_myisam.result File 4.06 KB 0644
partition_exch_qa_2.result File 3.32 KB 0644
partition_exch_qa_3.result File 644 B 0644
partition_exch_qa_4_innodb.result File 1.48 KB 0644
partition_exch_qa_4_myisam.result File 1.48 KB 0644
partition_exch_qa_5_innodb.result File 3.09 KB 0644
partition_exch_qa_5_myisam.result File 3.09 KB 0644
partition_exch_qa_6.result File 2.93 KB 0644
partition_exch_qa_7_innodb.result File 1.28 KB 0644
partition_exch_qa_7_myisam.result File 1.28 KB 0644
partition_exch_qa_8_innodb.result File 1.88 KB 0644
partition_exch_qa_8_myisam.result File 1.88 KB 0644
partition_exchange_archive.result File 10.41 KB 0644
partition_exchange_blackhole.result File 440 B 0644
partition_exchange_innodb.result File 12.85 KB 0644
partition_exchange_memory.result File 12.85 KB 0644
partition_exchange_myisam.result File 12.85 KB 0644
partition_float_innodb.result File 3.64 KB 0644
partition_float_myisam.result File 3.64 KB 0644
partition_innodb_status_file.result File 439 B 0644
partition_int_innodb.result File 11.66 KB 0644
partition_int_myisam.result File 11.66 KB 0644
partition_max_parts_hash_innodb.result File 1.72 KB 0644
partition_max_parts_hash_myisam.result File 1.72 KB 0644
partition_max_parts_inv_innodb.result File 748.54 KB 0644
partition_max_parts_inv_myisam.result File 748.54 KB 0644
partition_max_parts_key_innodb.result File 1.64 KB 0644
partition_max_parts_key_myisam.result File 1.64 KB 0644
partition_max_parts_list_innodb.result File 413.37 KB 0644
partition_max_parts_list_myisam.result File 413.37 KB 0644
partition_max_parts_range_innodb.result File 335 KB 0644
partition_max_parts_range_myisam.result File 335 KB 0644
partition_max_sub_parts_key_list_innodb.result File 201.48 KB 0644
partition_max_sub_parts_key_list_myisam.result File 201.48 KB 0644
partition_max_sub_parts_key_range_innodb.result File 167.07 KB 0644
partition_max_sub_parts_key_range_myisam.result File 167.07 KB 0644
partition_max_sub_parts_list_innodb.result File 201.46 KB 0644
partition_max_sub_parts_list_myisam.result File 201.46 KB 0644
partition_max_sub_parts_range_innodb.result File 167.04 KB 0644
partition_max_sub_parts_range_myisam.result File 167.04 KB 0644
partition_mgm_lc0_archive.result File 19.6 KB 0644
partition_mgm_lc0_innodb.result File 24.07 KB 0644
partition_mgm_lc0_memory.result File 24.12 KB 0644
partition_mgm_lc0_myisam.result File 24.07 KB 0644
partition_mgm_lc1_archive.result File 19.99 KB 0644
partition_mgm_lc1_innodb.result File 24.47 KB 0644
partition_mgm_lc1_memory.result File 24.52 KB 0644
partition_mgm_lc1_myisam.result File 24.47 KB 0644
partition_mgm_lc2_archive.result File 19.99 KB 0644
partition_mgm_lc2_innodb.result File 24.47 KB 0644
partition_mgm_lc2_memory.result File 24.52 KB 0644
partition_mgm_lc2_myisam.result File 24.47 KB 0644
partition_recover_myisam.result File 1.71 KB 0644
partition_reorganize_innodb.result File 7.06 KB 0644
partition_reorganize_myisam.result File 5.72 KB 0644
partition_repair_myisam.result File 16.07 KB 0644
partition_special_innodb.result File 18.36 KB 0644
partition_special_myisam.result File 14.85 KB 0644
partition_syntax_innodb.result File 61.36 KB 0644
partition_syntax_myisam.result File 62.92 KB 0644
partition_t55.out File 4.5 KB 0644
partition_value_innodb.result File 7.36 KB 0644
partition_value_myisam.result File 7.36 KB 0644
rpl-partition-dml-1-1-innodb.result File 6.78 KB 0644
rpl-partition-dml-1-1-myisam.result File 6.78 KB 0644
rpl_partition.result File 5.51 KB 0644