diff options
Diffstat (limited to 'mysql-test/suite/parts/r/partition_syntax_ndb.result')
| -rw-r--r-- | mysql-test/suite/parts/r/partition_syntax_ndb.result | 1820 |
1 files changed, 0 insertions, 1820 deletions
diff --git a/mysql-test/suite/parts/r/partition_syntax_ndb.result b/mysql-test/suite/parts/r/partition_syntax_ndb.result deleted file mode 100644 index 2b0899fe53a..00000000000 --- a/mysql-test/suite/parts/r/partition_syntax_ndb.result +++ /dev/null @@ -1,1820 +0,0 @@ -SET @max_row = 20; -SET @@session.storage_engine = 'ndbcluster'; - -#------------------------------------------------------------------------ -# There are several testcases disabled because of the open bugs -# #15890 -# #18730, Bug#18735 -# The expected results suffer from the following bugs -# harmless #17455, #19305 -# which cannot be suppressed because of technical reasons. -#------------------------------------------------------------------------ - -#------------------------------------------------------------------------ -# 0. Setting of auxiliary variables + Creation of an auxiliary tables -# needed in many testcases -#------------------------------------------------------------------------ -SELECT @max_row DIV 2 INTO @max_row_div2; -SELECT @max_row DIV 3 INTO @max_row_div3; -SELECT @max_row DIV 4 INTO @max_row_div4; -SET @max_int_4 = 2147483647; -DROP TABLE IF EXISTS t0_template; -CREATE TABLE t0_template ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) , -PRIMARY KEY(f_int1)) -ENGINE = MEMORY; -# Logging of <max_row> INSERTs into t0_template suppressed -DROP TABLE IF EXISTS t0_definition; -CREATE TABLE t0_definition ( -state CHAR(3), -create_command VARBINARY(5000), -file_list VARBINARY(5000), -PRIMARY KEY (state) -) ENGINE = MEMORY; -DROP TABLE IF EXISTS t0_aux; -CREATE TABLE t0_aux ( f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) ) -ENGINE = MEMORY; -SET AUTOCOMMIT= 1; -SET @@session.sql_mode= ''; -# End of basic preparations needed for all tests -#----------------------------------------------- - -#======================================================================== -# 1. Any PRIMARY KEYs or UNIQUE INDEXes must contain the columns used -# within the partitioning functions -#======================================================================== -DROP TABLE IF EXISTS t1; -#------------------------------------------------------------------------ -# 1.1 column of partitioning function not included in PRIMARY KEY -# PARTITION BY HASH/KEY/LIST/RANGE -#------------------------------------------------------------------------ -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY (f_int2) -) -PARTITION BY HASH(f_int1) PARTITIONS 2; -ERROR HY000: A PRIMARY KEY need to include all fields in the partition function -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY (f_int2) -) -PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2; -ERROR HY000: A PRIMARY KEY need to include all fields in the partition function -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY (f_int2) -) -PARTITION BY KEY(f_int1) PARTITIONS 2; -ERROR HY000: A PRIMARY KEY need to include all fields in the partition function -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY (f_int2) -) -PARTITION BY KEY(f_int1,f_int2) PARTITIONS 2; -ERROR HY000: A PRIMARY KEY need to include all fields in the partition function -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY (f_int2) -) -PARTITION BY LIST(f_int1) -(PARTITION part1 VALUES IN (1)); -ERROR HY000: A PRIMARY KEY need to include all fields in the partition function -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY (f_int2) -) -PARTITION BY LIST(f_int1 + f_int2) -(PARTITION part1 VALUES IN (1)); -ERROR HY000: A PRIMARY KEY need to include all fields in the partition function -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY (f_int2) -) -PARTITION BY RANGE(f_int1) -(PARTITION part1 VALUES LESS THAN (1)); -ERROR HY000: A PRIMARY KEY need to include all fields in the partition function -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY (f_int2) -) -PARTITION BY RANGE(f_int1 + f_int2) -(PARTITION part1 VALUES LESS THAN (1)); -ERROR HY000: A PRIMARY KEY need to include all fields in the partition function -#------------------------------------------------------------------------ -# 1.2 column of partitioning function not included in UNIQUE INDEX -# PARTITION BY HASH/KEY/LIST/RANGE -# Variant a) Without additional PRIMARY KEY -# Variant b) With correct additional PRIMARY KEY -# Variant 1) one column in partitioning function -# Variant 2) two columns in partitioning function -#------------------------------------------------------------------------ -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -UNIQUE INDEX (f_int2) -) -PARTITION BY HASH(f_int1) PARTITIONS 2; -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2) -) -PARTITION BY HASH(f_int1) PARTITIONS 2; -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -UNIQUE INDEX (f_int2) -) -PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2; -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2) -) -PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2; -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -UNIQUE INDEX (f_int2) -) -PARTITION BY KEY(f_int1) PARTITIONS 2; -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2) -) -PARTITION BY KEY(f_int1) PARTITIONS 2; -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -UNIQUE INDEX (f_int2) -) -PARTITION BY KEY(f_int1,f_int2) PARTITIONS 2; -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2) -) -PARTITION BY KEY(f_int1,f_int2) PARTITIONS 2; -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -UNIQUE INDEX (f_int2) -) -PARTITION BY LIST(MOD(f_int1,3)) -(PARTITION partN VALUES IN (NULL), -PARTITION part0 VALUES IN (0), -PARTITION part1 VALUES IN (1), -PARTITION part2 VALUES IN (2)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2) -) -PARTITION BY LIST(MOD(f_int1,3)) -(PARTITION partN VALUES IN (NULL), -PARTITION part0 VALUES IN (0), -PARTITION part1 VALUES IN (1), -PARTITION part2 VALUES IN (2)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -UNIQUE INDEX (f_int2) -) -PARTITION BY LIST(MOD(f_int1 + f_int2,3)) -(PARTITION partN VALUES IN (NULL), -PARTITION part0 VALUES IN (0), -PARTITION part1 VALUES IN (1), -PARTITION part2 VALUES IN (2)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2) -) -PARTITION BY LIST(MOD(f_int1 + f_int2,3)) -(PARTITION partN VALUES IN (NULL), -PARTITION part0 VALUES IN (0), -PARTITION part1 VALUES IN (1), -PARTITION part2 VALUES IN (2)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -UNIQUE INDEX (f_int2) -) -PARTITION BY RANGE(f_int1) -(PARTITION part1 VALUES LESS THAN (1), -PARTITION part2 VALUES LESS THAN (2147483646)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2) -) -PARTITION BY RANGE(f_int1) -(PARTITION part1 VALUES LESS THAN (1), -PARTITION part2 VALUES LESS THAN (2147483646)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -UNIQUE INDEX (f_int2) -) -PARTITION BY RANGE(f_int1 + f_int2) -(PARTITION part1 VALUES LESS THAN (1), -PARTITION part2 VALUES LESS THAN (2147483646)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2) -) -PARTITION BY RANGE(f_int1 + f_int2) -(PARTITION part1 VALUES LESS THAN (1), -PARTITION part2 VALUES LESS THAN (2147483646)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -#------------------------------------------------------------------------ -# 1.3 column of subpartitioning function not included in PRIMARY KEY -# PARTITION BY RANGE/LIST -- SUBPARTITION BY HASH/KEY -#------------------------------------------------------------------------ -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY (f_int2) -) -PARTITION BY RANGE(f_int2) SUBPARTITION BY HASH(f_int1) -(PARTITION part1 VALUES LESS THAN (1) -(SUBPARTITION subpart1)); -ERROR HY000: A PRIMARY KEY need to include all fields in the partition function -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY (f_int2) -) -PARTITION BY RANGE(f_int2) SUBPARTITION BY KEY(f_int1) -(PARTITION part1 VALUES LESS THAN (1) -(SUBPARTITION subpart1)); -ERROR HY000: A PRIMARY KEY need to include all fields in the partition function -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY (f_int2) -) -PARTITION BY LIST(f_int2) SUBPARTITION BY HASH(f_int1) -(PARTITION part1 VALUES IN (1) -(SUBPARTITION subpart1)); -ERROR HY000: A PRIMARY KEY need to include all fields in the partition function -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY (f_int2) -) -PARTITION BY LIST(f_int2) SUBPARTITION BY KEY(f_int1) -(PARTITION part1 VALUES IN (1) -(SUBPARTITION subpart1)); -ERROR HY000: A PRIMARY KEY need to include all fields in the partition function -#------------------------------------------------------------------------ -# 1.4 column of subpartitioning function not included in UNIQUE INDEX -# PARTITION BY RANGE/LIST -- SUBPARTITION BY HASH/KEY -# Variant a) Without additional PRIMARY KEY -# Variant b) With correct additional PRIMARY KEY -#------------------------------------------------------------------------ -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -UNIQUE INDEX (f_int2) -) -PARTITION BY RANGE(f_int2) -SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 3 -(PARTITION part1 VALUES LESS THAN (1), -PARTITION part2 VALUES LESS THAN (2147483646)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2) -) -PARTITION BY RANGE(f_int2) -SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 3 -(PARTITION part1 VALUES LESS THAN (1), -PARTITION part2 VALUES LESS THAN (2147483646)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -UNIQUE INDEX (f_int2) -) -PARTITION BY RANGE(f_int2) -SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 3 -(PARTITION part1 VALUES LESS THAN (1), -PARTITION part2 VALUES LESS THAN (2147483646)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2) -) -PARTITION BY RANGE(f_int2) -SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 3 -(PARTITION part1 VALUES LESS THAN (1), -PARTITION part2 VALUES LESS THAN (2147483646)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -UNIQUE INDEX (f_int2) -) -PARTITION BY LIST(MOD(f_int2,3)) -SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2 -(PARTITION partN VALUES IN (NULL), -PARTITION part0 VALUES IN (0), -PARTITION part1 VALUES IN (1), -PARTITION part2 VALUES IN (2)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2) -) -PARTITION BY LIST(MOD(f_int2,3)) -SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2 -(PARTITION partN VALUES IN (NULL), -PARTITION part0 VALUES IN (0), -PARTITION part1 VALUES IN (1), -PARTITION part2 VALUES IN (2)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -UNIQUE INDEX (f_int2) -) -PARTITION BY LIST(MOD(f_int2,3)) -SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 2 -(PARTITION partN VALUES IN (NULL), -PARTITION part0 VALUES IN (0), -PARTITION part1 VALUES IN (1), -PARTITION part2 VALUES IN (2)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2) -) -PARTITION BY LIST(MOD(f_int2,3)) -SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 2 -(PARTITION partN VALUES IN (NULL), -PARTITION part0 VALUES IN (0), -PARTITION part1 VALUES IN (1), -PARTITION part2 VALUES IN (2)); -INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) -SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; -DROP TABLE t1; - -#======================================================================== -# 2 Some properties around subpartitioning -#======================================================================== -#------------------------------------------------------------------------ -# 2.1 Subpartioned table without subpartitioning rule must be rejected -#------------------------------------------------------------------------ -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) -( PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11)); -ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning -#------------------------------------------------------------------------ -# 2.2 Every partition must have the same number of subpartitions. -# This is a limitation of MySQL 5.1, which could be removed in -# later releases. -#------------------------------------------------------------------------ -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000), -PRIMARY KEY (f_int1) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY KEY(f_int1) -( -PARTITION part1 VALUES LESS THAN (0) -(SUBPARTITION subpart1), -PARTITION part2 VALUES LESS THAN (5) -(SUBPARTITION subpart1, SUBPARTITION subpart2)); -ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '))' at line 14 - -#======================================================================== -# 3 VALUES clauses -#======================================================================== -#------------------------------------------------------------------------ -# 3.1 The constants in VALUES IN clauses must differ -#------------------------------------------------------------------------ -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY LIST(MOD(f_int1,2)) -( PARTITION part1 VALUES IN (-1), -PARTITION part2 VALUES IN (0), -PARTITION part3 VALUES IN (-1)); -ERROR HY000: Multiple definition of same constant in list partitioning -CREATE TABLE t1 (f1 BIGINT, f2 BIGINT) -PARTITION BY RANGE(f1) -(PARTITION part1 VALUES LESS THAN (0), -PARTITION part2 VALUES LESS THAN (0), -PARTITION part3 VALUES LESS THAN (10000)); -ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition -#------------------------------------------------------------------------ -# 3.2 The constants in VALUES LESS must be in increasing order -#------------------------------------------------------------------------ -CREATE TABLE t1 (f1 BIGINT, f2 BIGINT) -PARTITION BY RANGE(f1) -(PARTITION part1 VALUES LESS THAN (0), -PARTITION part2 VALUES LESS THAN (-1), -PARTITION part3 VALUES LESS THAN (10000)); -ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition -#------------------------------------------------------------------------ -# 3.3 LIST partitions must be defined with VALUES IN -#------------------------------------------------------------------------ -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY LIST(MOD(f_int1,2)) -( PARTITION part1 VALUES LESS THAN (-1), -PARTITION part2 VALUES LESS THAN (0), -PARTITION part3 VALUES LESS THAN (1000)); -ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition -#------------------------------------------------------------------------ -# 3.4 RANGE partitions must be defined with VALUES LESS THAN -#------------------------------------------------------------------------ -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) -( PARTITION part1 VALUES IN (-1), -PARTITION part2 VALUES IN (0), -PARTITION part3 VALUES IN (1000)); -ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition -#------------------------------------------------------------------------ -# 3.5 Use of NULL in VALUES clauses -#------------------------------------------------------------------------ -# 3.5.1 NULL in RANGE partitioning clause -# 3.5.1.1 VALUE LESS THAN (NULL) is not allowed -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) -( PARTITION part1 VALUES LESS THAN (NULL), -PARTITION part2 VALUES LESS THAN (1000)); -ERROR 42000: Not allowed to use NULL value in VALUES LESS THAN near '), -PARTITION part2 VALUES LESS THAN (1000))' at line 9 -# 3.5.1.2 VALUE LESS THAN (CAST(NULL AS SIGNED INTEGER)) is not allowed -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) -( PARTITION part1 VALUES LESS THAN (CAST(NULL AS SIGNED INTEGER)), -PARTITION part2 VALUES LESS THAN (1000)); -ERROR 42000: Not allowed to use NULL value in VALUES LESS THAN near '), -PARTITION part2 VALUES LESS THAN (1000))' at line 9 -# 3.5.2 NULL in LIST partitioning clause -# 3.5.2.1 VALUE IN (NULL) -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY LIST(MOD(f_int1,2)) -( PARTITION part1 VALUES IN (NULL), -PARTITION part2 VALUES IN (0), -PARTITION part3 VALUES IN (1)); -DROP TABLE t1; -# 3.5.2.2 VALUE IN (CAST(NULL AS SIGNED INTEGER)) -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY LIST(MOD(f_int1,2)) -( PARTITION part1 VALUES IN (CAST(NULL AS SIGNED INTEGER)), -PARTITION part3 VALUES IN (1)); -create_command -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f_int1` int(11) DEFAULT NULL, - `f_int2` int(11) DEFAULT NULL, - `f_char1` char(20) DEFAULT NULL, - `f_char2` char(20) DEFAULT NULL, - `f_charbig` varchar(1000) DEFAULT NULL -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY LIST (MOD(f_int1,2)) (PARTITION part1 VALUES IN (NULL) ENGINE = ndbcluster, PARTITION part3 VALUES IN (1) ENGINE = ndbcluster) - -DROP TABLE t1; -# 3.5.3 Reveal that IN (...NULL) is not mapped to IN(0) -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY LIST(MOD(f_int1,2)) -( PARTITION part1 VALUES IN (CAST(NULL AS SIGNED INTEGER)), -PARTITION part2 VALUES IN (0), -PARTITION part3 VALUES IN (1)); -create_command -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f_int1` int(11) DEFAULT NULL, - `f_int2` int(11) DEFAULT NULL, - `f_char1` char(20) DEFAULT NULL, - `f_char2` char(20) DEFAULT NULL, - `f_charbig` varchar(1000) DEFAULT NULL -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY LIST (MOD(f_int1,2)) (PARTITION part1 VALUES IN (NULL) ENGINE = ndbcluster, PARTITION part2 VALUES IN (0) ENGINE = ndbcluster, PARTITION part3 VALUES IN (1) ENGINE = ndbcluster) - -DROP TABLE t1; - -#======================================================================== -# 4. Check assigning the number of partitions and subpartitions -# with and without named partitions/subpartitions -#======================================================================== -DROP TABLE IF EXISTS t1; -#------------------------------------------------------------------------ -# 4.1 (positive) without partition/subpartition number assignment -#------------------------------------------------------------------------ -# 4.1.1 no partition number, no named partitions -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1); -create_command -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f_int1` int(11) DEFAULT NULL, - `f_int2` int(11) DEFAULT NULL, - `f_char1` char(20) DEFAULT NULL, - `f_char2` char(20) DEFAULT NULL, - `f_charbig` varchar(1000) DEFAULT NULL -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f_int1) - -DROP TABLE t1; -# 4.1.2 no partition number, named partitions -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) (PARTITION part1, PARTITION part2); -create_command -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f_int1` int(11) DEFAULT NULL, - `f_int2` int(11) DEFAULT NULL, - `f_char1` char(20) DEFAULT NULL, - `f_char2` char(20) DEFAULT NULL, - `f_charbig` varchar(1000) DEFAULT NULL -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f_int1) (PARTITION part1 ENGINE = ndbcluster, PARTITION part2 ENGINE = ndbcluster) - -DROP TABLE t1; -# 4.1.3 variations on no partition/subpartition number, named partitions, -# different subpartitions are/are not named -CREATE TABLE t1 ( f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) ) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646)) ; -DROP TABLE t1; -CREATE TABLE t1 ( f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) ) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ; -ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '))' at line 7 -CREATE TABLE t1 ( f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) ) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20) -(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)) ; -ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '), PARTITION part3 VALUES LESS THAN (2147483646))' at line 7 -CREATE TABLE t1 ( f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) ) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), 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 '), PARTITION part3 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart31 , SUBPA' at line 7 -CREATE TABLE t1 ( f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) ) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10) -(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646)) ; -ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ' PARTITION part3 VALUES LESS THAN (2147483646))' at line 7 -CREATE TABLE t1 ( f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) ) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10) -(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ; -ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ' PARTITION part3 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart31 , SUBPART' at line 7 -CREATE TABLE t1 ( f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) ) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10) -(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20) -(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)) ; -ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')' at line 8 -CREATE TABLE t1 ( f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) ) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10) -(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20) -(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ; -create_command -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f_int1` int(11) DEFAULT NULL, - `f_int2` int(11) DEFAULT NULL, - `f_char1` char(20) DEFAULT NULL, - `f_char2` char(20) DEFAULT NULL, - `f_charbig` varchar(1000) DEFAULT NULL -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = ndbcluster, SUBPARTITION subpart12 ENGINE = ndbcluster), PARTITION part2 VALUES LESS THAN (20) (SUBPARTITION subpart21 ENGINE = ndbcluster, SUBPARTITION subpart22 ENGINE = ndbcluster), PARTITION part3 VALUES LESS THAN (2147483646) (SUBPARTITION subpart31 ENGINE = ndbcluster, SUBPARTITION subpart32 ENGINE = ndbcluster)) - -DROP TABLE t1; -#------------------------------------------------------------------------ -# 4.2 partition/subpartition numbers good and bad values and notations -#------------------------------------------------------------------------ -DROP TABLE IF EXISTS t1; -# 4.2.1 partition/subpartition numbers INTEGER notation -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 2; -create_command -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f_int1` int(11) DEFAULT NULL, - `f_int2` int(11) DEFAULT NULL, - `f_char1` char(20) DEFAULT NULL, - `f_char2` char(20) DEFAULT NULL, - `f_charbig` varchar(1000) DEFAULT NULL -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f_int1) PARTITIONS 2 - -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS 2 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -create_command -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f_int1` int(11) DEFAULT NULL, - `f_int2` int(11) DEFAULT NULL, - `f_char1` char(20) DEFAULT NULL, - `f_char2` char(20) DEFAULT NULL, - `f_charbig` varchar(1000) DEFAULT NULL -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) SUBPARTITIONS 2 (PARTITION part1 VALUES LESS THAN (10) , PARTITION part2 VALUES LESS THAN (2147483646) ) - -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 1; -create_command -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f_int1` int(11) DEFAULT NULL, - `f_int2` int(11) DEFAULT NULL, - `f_char1` char(20) DEFAULT NULL, - `f_char2` char(20) DEFAULT NULL, - `f_charbig` varchar(1000) DEFAULT NULL -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f_int1) PARTITIONS 1 - -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS 1 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -create_command -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f_int1` int(11) DEFAULT NULL, - `f_int2` int(11) DEFAULT NULL, - `f_char1` char(20) DEFAULT NULL, - `f_char2` char(20) DEFAULT NULL, - `f_charbig` varchar(1000) DEFAULT NULL -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) SUBPARTITIONS 1 (PARTITION part1 VALUES LESS THAN (10) , PARTITION part2 VALUES LESS THAN (2147483646) ) - -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 0; -ERROR HY000: Number of partitions = 0 is not an allowed value -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS 0 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR HY000: Number of subpartitions = 0 is not an allowed value -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS -1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS -1 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (214' at line 9 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 1000000; -ERROR HY000: Too many partitions (including subpartitions) were defined -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS 1000000 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR HY000: Too many partitions (including subpartitions) were defined -# 4.2.2 partition/subpartition numbers DECIMAL notation -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 2.0; -create_command -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f_int1` int(11) DEFAULT NULL, - `f_int2` int(11) DEFAULT NULL, - `f_char1` char(20) DEFAULT NULL, - `f_char2` char(20) DEFAULT NULL, - `f_charbig` varchar(1000) DEFAULT NULL -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f_int1) PARTITIONS 2 - -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS 2.0 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -create_command -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f_int1` int(11) DEFAULT NULL, - `f_int2` int(11) DEFAULT NULL, - `f_char1` char(20) DEFAULT NULL, - `f_char2` char(20) DEFAULT NULL, - `f_charbig` varchar(1000) DEFAULT NULL -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) SUBPARTITIONS 2 (PARTITION part1 VALUES LESS THAN (10) , PARTITION part2 VALUES LESS THAN (2147483646) ) - -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS -2.0; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS -2.0 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2' at line 9 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 0.0; -ERROR HY000: Number of partitions = 0 is not an allowed value -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS 0.0 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR HY000: Number of subpartitions = 0 is not an allowed value -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 999999999999999999999999999999.999999999999999999999999999999; -ERROR HY000: Too many partitions (including subpartitions) were defined -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS 999999999999999999999999999999.999999999999999999999999999999 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR HY000: Too many partitions (including subpartitions) were defined -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 0.000000000000000000000000000001; -ERROR HY000: Number of partitions = 0 is not an allowed value -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS 0.000000000000000000000000000001 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR HY000: Number of subpartitions = 0 is not an allowed value -# 4.2.3 partition/subpartition numbers FLOAT notation -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 2.0E+0; -create_command -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f_int1` int(11) DEFAULT NULL, - `f_int2` int(11) DEFAULT NULL, - `f_char1` char(20) DEFAULT NULL, - `f_char2` char(20) DEFAULT NULL, - `f_charbig` varchar(1000) DEFAULT NULL -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f_int1) PARTITIONS 2 - -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS 2.0E+0 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -create_command -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f_int1` int(11) DEFAULT NULL, - `f_int2` int(11) DEFAULT NULL, - `f_char1` char(20) DEFAULT NULL, - `f_char2` char(20) DEFAULT NULL, - `f_charbig` varchar(1000) DEFAULT NULL -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) SUBPARTITIONS 2 (PARTITION part1 VALUES LESS THAN (10) , PARTITION part2 VALUES LESS THAN (2147483646) ) - -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS -2.0E+0; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0E+0' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS -2.0E+0 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0E+0 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN' at line 9 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 0.0E+300; -ERROR HY000: Number of partitions = 0 is not an allowed value -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS 0.0E+300 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR HY000: Number of subpartitions = 0 is not an allowed value -# 4.2.4 partition/subpartition numbers STRING notation -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS '2'; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS '2' -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2' -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (21' at line 9 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS '2.0'; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0'' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS '2.0' -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0' -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (' at line 9 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS '0.2E+1'; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1'' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS '0.2E+1' -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1' -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THA' at line 9 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS '2A'; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A'' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS '2A' -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A' -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2' at line 9 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 'A2'; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2'' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS 'A2' -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2' -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2' at line 9 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS ''; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS '' -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (214' at line 9 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 'GARBAGE'; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE'' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS 'GARBAGE' -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE' -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS TH' at line 9 -# 4.2.5 partition/subpartition numbers other notations -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 2A; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS 2A -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (214' at line 9 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS A2; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS A2 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (214' at line 9 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS GARBAGE; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS GARBAGE -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN' at line 9 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS "2"; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2"' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS "2" -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2" -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (21' at line 9 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS "2A"; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A"' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS "2A" -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A" -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2' at line 9 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS "A2"; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2"' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS "A2" -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2" -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2' at line 9 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS "GARBAGE"; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE"' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS "GARBAGE" -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE" -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS TH' at line 9 -# 4.2.6 (negative) partition/subpartition numbers per @variables -SET @aux = 5; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS @aux; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@aux' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) -SUBPARTITIONS @aux = 5 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THAN (2147483646)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@aux = 5 -(PARTITION part1 VALUES LESS THAN (10), -PARTITION part2 VALUES LESS THA' at line 9 -#------------------------------------------------------------------------ -# 4.3 Mixups of assigned partition/subpartition numbers and names -#------------------------------------------------------------------------ -# 4.3.1 (positive) number of partition/subpartition -# = number of named partition/subpartition -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 2 ( PARTITION part1, PARTITION part2 ) ; -create_command -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f_int1` int(11) DEFAULT NULL, - `f_int2` int(11) DEFAULT NULL, - `f_char1` char(20) DEFAULT NULL, - `f_char2` char(20) DEFAULT NULL, - `f_charbig` varchar(1000) DEFAULT NULL -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f_int1) (PARTITION part1 ENGINE = ndbcluster, PARTITION part2 ENGINE = ndbcluster) - -DROP TABLE t1; -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) PARTITIONS 2 -SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2 -( PARTITION part1 VALUES LESS THAN (1000) -(SUBPARTITION subpart11, SUBPARTITION subpart12), -PARTITION part2 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart21, SUBPARTITION subpart22) -); -create_command -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f_int1` int(11) DEFAULT NULL, - `f_int2` int(11) DEFAULT NULL, - `f_char1` char(20) DEFAULT NULL, - `f_char2` char(20) DEFAULT NULL, - `f_charbig` varchar(1000) DEFAULT NULL -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = ndbcluster, SUBPARTITION subpart12 ENGINE = ndbcluster), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = ndbcluster, SUBPARTITION subpart22 ENGINE = ndbcluster)) - -DROP TABLE t1; -# 4.3.2 (positive) number of partition/subpartition , -# 0 (= no) named partition/subpartition -# already checked above -# 4.3.3 (negative) number of partitions/subpartitions -# > number of named partitions/subpartitions -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 2 ( PARTITION part1 ) ; -ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) -SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2 -( PARTITION part1 VALUES LESS THAN (1000) -(SUBPARTITION subpart11 ), -PARTITION part2 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart21, SUBPARTITION subpart22) -); -ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '), -PARTITION part2 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart21, SUBPAR' at line 11 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) -SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2 -( PARTITION part1 VALUES LESS THAN (1000) -(SUBPARTITION subpart11, SUBPARTITION subpart12), -PARTITION part2 VALUES LESS THAN (2000) -(SUBPARTITION subpart21 ), -PARTITION part3 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart31, SUBPARTITION subpart32) -); -ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '), -PARTITION part3 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart31, SUBPAR' at line 13 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) PARTITIONS 2 -SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2 -( PARTITION part1 VALUES LESS THAN (1000) -(SUBPARTITION subpart11, SUBPARTITION subpart12), -PARTITION part2 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart21 ) -); -ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ') -)' at line 13 -# 4.3.4 (negative) number of partitions < number of named partitions -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) PARTITIONS 1 ( PARTITION part1, PARTITION part2 ) ; -ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 8 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) -SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1 -( PARTITION part1 VALUES LESS THAN (1000) -(SUBPARTITION subpart11, SUBPARTITION subpart12), -PARTITION part2 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart21, SUBPARTITION subpart22) -); -ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '), -PARTITION part2 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart21, SUBPAR' at line 11 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) -SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1 -( PARTITION part1 VALUES LESS THAN (1000) -(SUBPARTITION subpart11, SUBPARTITION subpart12), -PARTITION part2 VALUES LESS THAN (2000) -(SUBPARTITION subpart21 ), -PARTITION part3 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart31, SUBPARTITION subpart32) -); -ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '), -PARTITION part2 VALUES LESS THAN (2000) -(SUBPARTITION subpart21 ' at line 11 -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) -SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1 -( PARTITION part1 VALUES LESS THAN (1000) -(SUBPARTITION subpart11, SUBPARTITION subpart12), -PARTITION part2 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart21, SUBPARTITION subpart22) -); -ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '), -PARTITION part2 VALUES LESS THAN (2147483646) -(SUBPARTITION subpart21, SUBPAR' at line 11 - -#======================================================================== -# 5. Checks of logical partition/subpartition name -# file name clashes during CREATE TABLE -#======================================================================== -DROP TABLE IF EXISTS t1; -#------------------------------------------------------------------------ -# 5.1 (negative) A partition/subpartition name used more than once -#------------------------------------------------------------------------ -# 5.1.1 duplicate partition name -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY HASH(f_int1) (PARTITION part1, PARTITION part1); -ERROR HY000: Duplicate partition name part1 -# 5.1.2 duplicate subpartition name -CREATE TABLE t1 ( -f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000) -) -PARTITION BY RANGE(f_int1) -SUBPARTITION BY HASH(f_int1) -( PARTITION part1 VALUES LESS THAN (1000) -(SUBPARTITION subpart11, SUBPARTITION subpart11) -); -ERROR HY000: Duplicate partition name subpart11 -DROP VIEW IF EXISTS v1; -DROP TABLE IF EXISTS t1; -DROP TABLE IF EXISTS t0_aux; -DROP TABLE IF EXISTS t0_definition; -DROP TABLE IF EXISTS t0_template; |