diff options
Diffstat (limited to 'mysql-test/suite/parts/r/partition_syntax_innodb.result')
| -rw-r--r-- | mysql-test/suite/parts/r/partition_syntax_innodb.result | 1889 |
1 files changed, 1889 insertions, 0 deletions
diff --git a/mysql-test/suite/parts/r/partition_syntax_innodb.result b/mysql-test/suite/parts/r/partition_syntax_innodb.result new file mode 100644 index 00000000..eb0f9de5 --- /dev/null +++ b/mysql-test/suite/parts/r/partition_syntax_innodb.result @@ -0,0 +1,1889 @@ +SET @max_row = 20; +SET @@session.default_storage_engine = 'InnoDB'; + +#------------------------------------------------------------------------ +# 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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY LIST (`f_int1` MOD 2) +(PARTITION `part1` VALUES IN (NULL) ENGINE = InnoDB, + PARTITION `part3` VALUES IN (1) ENGINE = InnoDB) + +DROP TABLE t1; +# 3.5.3 Reveal that IN (...NULL) is not mapped to IN(0) +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY LIST (`f_int1` MOD 2) +(PARTITION `part1` VALUES IN (NULL) ENGINE = InnoDB, + PARTITION `part2` VALUES IN (0) ENGINE = InnoDB, + PARTITION `part3` VALUES IN (1) ENGINE = InnoDB) + +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (`f_int1`) + +DROP TABLE t1; +# 4.1.2 no partition number, named partitions +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (`f_int1`) +(PARTITION `part1` ENGINE = InnoDB, + PARTITION `part2` ENGINE = InnoDB) + +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 LES...' at line 7 +CREATE TABLE t1 ( f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 LES...' at line 7 +CREATE TABLE t1 ( f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 , SUBP...' at line 7 +CREATE TABLE t1 ( f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY RANGE (`f_int1`) +SUBPARTITION BY HASH (`f_int1`) +(PARTITION `part1` VALUES LESS THAN (10) + (SUBPARTITION `subpart11` ENGINE = InnoDB, + SUBPARTITION `subpart12` ENGINE = InnoDB), + PARTITION `part2` VALUES LESS THAN (20) + (SUBPARTITION `subpart21` ENGINE = InnoDB, + SUBPARTITION `subpart22` ENGINE = InnoDB), + PARTITION `part3` VALUES LESS THAN (2147483646) + (SUBPARTITION `subpart31` ENGINE = InnoDB, + SUBPARTITION `subpart32` ENGINE = InnoDB)) + +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (`f_int1`) +PARTITIONS 2 + +DROP TABLE t1; +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY RANGE (`f_int1`) +SUBPARTITION BY HASH (`f_int1`) +SUBPARTITIONS 2 +(PARTITION `part1` VALUES LESS THAN (10) ENGINE = InnoDB, + PARTITION `part2` VALUES LESS THAN (2147483646) ENGINE = InnoDB) + +DROP TABLE t1; +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (`f_int1`) +PARTITIONS 1 + +DROP TABLE t1; +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY RANGE (`f_int1`) +SUBPARTITION BY HASH (`f_int1`) +SUBPARTITIONS 1 +(PARTITION `part1` VALUES LESS THAN (10) ENGINE = InnoDB, + PARTITION `part2` VALUES LESS THAN (2147483646) ENGINE = InnoDB) + +DROP TABLE t1; +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '-1' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '-1 +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS THAN (...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 ...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '-2.0' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB 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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 ...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 ...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 part...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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), +PART...' at line 9 +# 4.2.3 partition/subpartition numbers FLOAT notation +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 TH...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 TH...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '-2.0E+0' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '-2.0E+0 +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS T...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 T...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 ...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 TH...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 TH...' at line 9 +# 4.2.4 partition/subpartition numbers STRING notation +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near ''2'' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near ''2' +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS THAN ...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near ''2.0'' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near ''2.0' +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS THA...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near ''0.2E+1'' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near ''0.2E+1' +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS ...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near ''2A'' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near ''2A' +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS THAN...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near ''A2'' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near ''A2' +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS THAN...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '''' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near ''' +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS THAN (...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near ''GARBAGE'' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near ''GARBAGE' +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS...' at line 9 +# 4.2.5 partition/subpartition numbers other notations +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '2A' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '2A +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS THAN (...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near 'A2' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near 'A2 +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS THAN (...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near 'GARBAGE' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near 'GARBAGE +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS T...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '"2"' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '"2" +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS THAN ...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '"2A"' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '"2A" +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS THAN...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '"A2"' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '"A2" +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS THAN...' at line 9 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '"GARBAGE"' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '"GARBAGE" +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS...' at line 9 +# 4.2.6 (negative) partition/subpartition numbers per @variables +SET @aux = 5; +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '@aux' at line 8 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 MariaDB server version for the right syntax to use near '@aux = 5 +(PARTITION part1 VALUES LESS THAN (10), +PARTITION part2 VALUES LESS ...' 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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (`f_int1`) +(PARTITION `part1` ENGINE = InnoDB, + PARTITION `part2` ENGINE = InnoDB) + +DROP TABLE t1; +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY RANGE (`f_int1`) +SUBPARTITION BY HASH (`f_int1`) +(PARTITION `part1` VALUES LESS THAN (1000) + (SUBPARTITION `subpart11` ENGINE = InnoDB, + SUBPARTITION `subpart12` ENGINE = InnoDB), + PARTITION `part2` VALUES LESS THAN (2147483646) + (SUBPARTITION `subpart21` ENGINE = InnoDB, + SUBPARTITION `subpart22` ENGINE = InnoDB)) + +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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, SUB...' at line 11 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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, SUB...' at line 13 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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, SUB...' at line 11 +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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, SUB...' 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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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 DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +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; |