diff options
Diffstat (limited to 'mysql-test/main/partition_list.test')
| -rw-r--r-- | mysql-test/main/partition_list.test | 252 |
1 files changed, 252 insertions, 0 deletions
diff --git a/mysql-test/main/partition_list.test b/mysql-test/main/partition_list.test new file mode 100644 index 00000000..5eadb72a --- /dev/null +++ b/mysql-test/main/partition_list.test @@ -0,0 +1,252 @@ +#--disable_abort_on_error +# +# Simple test for the partition storage engine +# testing list partitioning +# +-- source include/have_partition.inc + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# Bug 20733: Zerofill columns gives wrong result with partitioned tables +# +create table t1 (a int unsigned) +partition by list (a) +(partition p0 values in (0), + partition p1 values in (1), + partition pnull values in (null), + partition p2 values in (2)); + +insert into t1 values (null),(0),(1),(2); +select * from t1 where a < 2; +select * from t1 where a <= 0; +select * from t1 where a < 1; +select * from t1 where a > 0; +select * from t1 where a > 1; +select * from t1 where a >= 0; +select * from t1 where a >= 1; +select * from t1 where a is null; +select * from t1 where a is not null; +select * from t1 where a is null or a > 0; +drop table t1; + +create table t1 (a int unsigned, b int) +partition by list (a) +subpartition by hash (b) +subpartitions 2 +(partition p0 values in (0), + partition p1 values in (1), + partition pnull values in (null, 2), + partition p3 values in (3)); +select partition_method, partition_expression, partition_description + from information_schema.partitions where table_name = "t1"; +insert into t1 values (0,0),(0,1),(1,0),(1,1),(null,0),(null,1); +insert into t1 values (2,0),(2,1),(3,0),(3,1); + +explain partitions select * from t1 where a is null; +select * from t1 where a is null; +explain partitions select * from t1 where a = 2; +select * from t1 where a = 2; +select * from t1 where a <= 0; +select * from t1 where a < 3; +select * from t1 where a >= 1 or a is null; +drop table t1; + +# +# Test ordinary list partitioning that it works ok +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null) +partition by list(a) +partitions 2 +(partition x123 values in (1,5,6), + partition x234 values in (4,7,8)); + +INSERT into t1 VALUES (1,1,1); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT into t1 VALUES (2,1,1); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT into t1 VALUES (3,1,1); +INSERT into t1 VALUES (4,1,1); +INSERT into t1 VALUES (5,1,1); +INSERT into t1 VALUES (6,1,1); +INSERT into t1 VALUES (7,1,1); +INSERT into t1 VALUES (8,1,1); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT into t1 VALUES (9,1,1); +INSERT into t1 VALUES (1,2,1); +INSERT into t1 VALUES (1,3,1); +INSERT into t1 VALUES (1,4,1); +INSERT into t1 VALUES (7,2,1); +INSERT into t1 VALUES (7,3,1); +INSERT into t1 VALUES (7,4,1); + +SELECT * from t1; +SELECT * from t1 WHERE a=1; +SELECT * from t1 WHERE a=7; +SELECT * from t1 WHERE b=2; + +UPDATE t1 SET a=8 WHERE a=7 AND b=3; +SELECT * from t1; +UPDATE t1 SET a=8 WHERE a=5 AND b=1; +SELECT * from t1; + +DELETE from t1 WHERE a=8; +SELECT * from t1; +DELETE from t1 WHERE a=2; +SELECT * from t1; +DELETE from t1 WHERE a=5 OR a=6; +SELECT * from t1; + +ALTER TABLE t1 +partition by list(a) +partitions 2 +(partition x123 values in (1,5,6), + partition x234 values in (4,7,8)); +SELECT * from t1; +INSERT into t1 VALUES (6,2,1); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT into t1 VALUES (2,2,1); + +drop table t1; +# +# Subpartition by hash, two partitions and two subpartitions +# Defined node group +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key (a,b)) +partition by list (a) +subpartition by hash (a+b) +( partition x1 values in (1,2,3) + ( subpartition x11 nodegroup 0, + subpartition x12 nodegroup 1), + partition x2 values in (4,5,6) + ( subpartition x21 nodegroup 0, + subpartition x22 nodegroup 1) +); + +INSERT into t1 VALUES (1,1,1); +INSERT into t1 VALUES (4,1,1); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT into t1 VALUES (7,1,1); +UPDATE t1 SET a=5 WHERE a=1; +SELECT * from t1; +UPDATE t1 SET a=6 WHERE a=4; +SELECT * from t1; +DELETE from t1 WHERE a=6; +SELECT * from t1; + +drop table t1; + +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key(a,b)) +partition by list (a) +(partition x1 values in (1,2,9,4) tablespace ts1); + +drop table t1; + +# +#Bug #17173 Partitions: less-than search fails +# +CREATE TABLE t1 (s1 int) PARTITION BY LIST (s1) +(PARTITION p1 VALUES IN (1), +PARTITION p2 VALUES IN (2), +PARTITION p3 VALUES IN (3), +PARTITION p4 VALUES IN (4), +PARTITION p5 VALUES IN (5)); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +SELECT COUNT(*) FROM t1 WHERE s1 < 3; +DROP TABLE t1; + +# +# Bug 19281 Partitions: Auto-increment value lost +# +create table t1 (a int auto_increment primary key) +auto_increment=100 +partition by list (a) +(partition p0 values in (1, 100)); +create index inx on t1 (a); +insert into t1 values (null); +select * from t1; +drop table t1; + +--error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED +create table t1 (a char(1)) +partition by list (ascii(ucase(a))) +(partition p1 values in (2)); + +--echo # +--echo # MDEV-11681: PARTITION BY LIST COLUMNS with default partition: +--echo # Assertion `part_info->num_list_values' failed in +--echo # get_part_iter_for_interval_cols_via_map +--echo # +CREATE TABLE t1 (f int) PARTITION BY LIST COLUMNS (f) (PARTITION pdef DEFAULT); +insert into t1 values (1),(2); +select * from t1 where f = 1; + +drop table t1; + +CREATE TABLE t1 (f int, d int) PARTITION BY LIST COLUMNS (f,d) (PARTITION pdef DEFAULT); +insert into t1 values (1,1),(2,2); +select * from t1 where f = 1 and d = 1 ; + +drop table t1; +CREATE TABLE t1 (f int) PARTITION BY LIST (f) (PARTITION pdef DEFAULT); +insert into t1 values (1),(2); +select * from t1 where f = 1; + +drop table t1; + +--echo #end of 10.2 tests + +--echo # +--echo # Bug MDEV-16101: More than MAX_REF_PARTS values in a list on ALTER TABLE. +--echo # Currently MAX_REF_PARTS = 32. +CREATE TABLE ts1 (a INT, PRIMARY KEY (`a`)) +PARTITION BY LIST (`a`) +(PARTITION `p ts_0` VALUES IN (101,102,103,104,105,106,107,108,109,110, + 111,112,113,114,115,116,117,118,119,120, + 121,122,123,124,125,126,127,128,129,130, + 131,132,133)); +INSERT INTO ts1 +VALUES (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), + (111), (112), (113), (114), (115), (116), (117), (118), (119), (120), + (121), (122), (123), (124), (125), (126), (127), (128), (129), (130), + (131), (132), (133); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT INTO ts1 VALUES (134); +SELECT * FROM ts1; +ALTER TABLE ts1 ADD PARTITION +(PARTITION `p ts_1` VALUES IN (1,2,3,4,5,6,7,8,9,10, + 11,12,13,14,15,16,17,18,19,20, + 21,22,23,24,25,26,27,28,29,30, + 31,32,33)); +INSERT INTO ts1 +VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), + (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), + (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), + (31), (32), (33); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT INTO ts1 VALUES(34); +SELECT * FROM ts1; +ALTER TABLE ts1 REORGANIZE PARTITION `p ts_1` INTO +(PARTITION `p ts_1` VALUES IN (1,2,3,4,5,6,7,8,9,10, + 11,12,13,14,15,16,17,18,19,20, + 21,22,23,24,25,26,27,28,29,30, + 31,32,33,34,35)); +INSERT INTO ts1 VALUES (34), (35); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT INTO ts1 VALUES (36); +SELECT * FROM ts1; +DROP TABLE ts1; |