diff options
Diffstat (limited to 'mysql-test/main/partition_hash.result')
| -rw-r--r-- | mysql-test/main/partition_hash.result | 202 |
1 files changed, 202 insertions, 0 deletions
diff --git a/mysql-test/main/partition_hash.result b/mysql-test/main/partition_hash.result new file mode 100644 index 00000000000..9a63db3f974 --- /dev/null +++ b/mysql-test/main/partition_hash.result @@ -0,0 +1,202 @@ +drop table if exists t1; +CREATE TABLE t1 (c1 INT) +PARTITION BY HASH (c1) +PARTITIONS 15; +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +ALTER TABLE t1 COALESCE PARTITION 13; +DROP TABLE t1; +CREATE TABLE t1 (c1 INT) +PARTITION BY LINEAR HASH (c1) +PARTITIONS 5; +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +ALTER TABLE t1 COALESCE PARTITION 3; +DROP TABLE t1; +create table t1 (a int unsigned) +partition by hash(a div 2) +partitions 4; +insert into t1 values (null),(0),(1),(2),(3),(4),(5),(6),(7); +select * from t1 where a < 0; +a +select * from t1 where a is null or (a >= 5 and a <= 7); +a +NULL +5 +6 +7 +select * from t1 where a is null; +a +NULL +select * from t1 where a is not null; +a +0 +1 +2 +3 +4 +5 +6 +7 +select * from t1 where a >= 1 and a < 3; +a +1 +2 +select * from t1 where a >= 3 and a <= 5; +a +3 +4 +5 +select * from t1 where a > 2 and a < 4; +a +3 +select * from t1 where a > 3 and a <= 6; +a +4 +5 +6 +select * from t1 where a > 5; +a +6 +7 +select * from t1 where a >= 1 and a <= 5; +a +1 +2 +3 +4 +5 +explain partitions select * from t1 where a < 0; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where +explain partitions select * from t1 where a is null or (a >= 5 and a <= 7); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p2,p3 ALL NULL NULL NULL NULL 7 Using where +explain partitions select * from t1 where a is null; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 Using where +explain partitions select * from t1 where a is not null; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where +explain partitions select * from t1 where a >= 1 and a < 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 5 Using where +explain partitions select * from t1 where a >= 3 and a <= 5; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1,p2 ALL NULL NULL NULL NULL 4 Using where +explain partitions select * from t1 where a > 2 and a < 4; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where +explain partitions select * from t1 where a > 3 and a <= 6; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 4 Using where +explain partitions select * from t1 where a > 5; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where +explain partitions select * from t1 where a >= 1 and a <= 5; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1,p2 ALL NULL NULL NULL NULL 7 Using where +drop table t1; +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key(a,b)) +partition by hash (a + 2) +partitions 3 +(partition x1 tablespace ts1, +partition x2 tablespace ts2, +partition x3 tablespace ts3); +insert into t1 values (1,1,1); +insert into t1 values (2,1,1); +insert into t1 values (3,1,1); +insert into t1 values (4,1,1); +insert into t1 values (5,1,1); +select * from t1; +a b c +1 1 1 +4 1 1 +2 1 1 +5 1 1 +3 1 1 +update t1 set c=3 where b=1; +select * from t1; +a b c +1 1 3 +4 1 3 +2 1 3 +5 1 3 +3 1 3 +select b from t1 where a=3; +b +1 +select b,c from t1 where a=1 AND b=1; +b c +1 3 +delete from t1 where a=1; +delete from t1 where c=3; +select * from t1; +a b c +ALTER TABLE t1 +partition by hash (a + 3) +partitions 3 +(partition x1 tablespace ts1, +partition x2 tablespace ts2, +partition x3 tablespace ts3); +select * from t1; +a b c +drop table t1; +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key(a,b)) +partition by hash (a) +(partition x1); +drop table t1; +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key(a,b)) +partition by key (a) +(partition x1); +drop table t1; +CREATE TABLE t1 (f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM' PARTITION BY HASH(f1) PARTITIONS 2; +INSERT INTO t1 SET f1 = 0 - 1, f2 = '#######'; +select * from t1; +f1 f2 +-1 ####### +drop table t1; +set sql_mode=""; +CREATE TABLE t1 (s1 int) ENGINE=BLACKHOLE PARTITION BY HASH (s1); +INSERT INTO t1 VALUES (0); +DROP TABLE t1; +set sql_mode=default; +create table t1 (c1 int DEFAULT NULL, +c2 varchar (30) DEFAULT NULL, +c3 date DEFAULT NULL) +engine = myisam +partition by hash (to_days(c3)) +partitions 12; +insert into t1 values +(136,'abc','2002-01-05'),(142,'abc','2002-02-14'),(162,'abc','2002-06-28'), +(182,'abc','2002-11-09'),(158,'abc','2002-06-01'),(184,'abc','2002-11-22'); +select * from t1; +c1 c2 c3 +136 abc 2002-01-05 +158 abc 2002-06-01 +142 abc 2002-02-14 +162 abc 2002-06-28 +182 abc 2002-11-09 +184 abc 2002-11-22 +select * from t1 where c3 between '2002-01-01' and '2002-12-31'; +c1 c2 c3 +136 abc 2002-01-05 +158 abc 2002-06-01 +142 abc 2002-02-14 +162 abc 2002-06-28 +182 abc 2002-11-09 +184 abc 2002-11-22 +drop table t1; +CREATE TABLE t1 (c1 INT) ENGINE=MyISAM PARTITION BY HASH(c1) PARTITIONS 1; +INSERT DELAYED INTO t1 VALUES (1); +DROP TABLE t1; |