mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-10-31 10:56:12 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/parts/r/partition_syntax_myisam.result
Alexander Barkov 36eba98817 MDEV-19123 Change default charset from latin1 to utf8mb4 
Changing the default server character set from latin1 to utf8mb4.
2024-07-11 10:21:07 +04:00

1991 lines
66 KiB
Text
Raw Permalink Blame History

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 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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY LIST (`f_int1` MOD 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 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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY LIST (`f_int1` MOD 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 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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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 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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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 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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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 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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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 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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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 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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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 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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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 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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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 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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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 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;
Reference in a new issue View git blame Copy permalink