partition_hash.test 3.41 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11
#--disable_abort_on_error
#
# Simple test for the partition storage engine
# Taken fromm the select test
#
-- source include/have_partition.inc

--disable_warnings
drop table if exists t1;
--enable_warnings

12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
#
# More partition pruning tests, especially on interval walking
#
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;
select * from t1 where a is null or (a >= 5 and a <= 7);
select * from t1 where a is null;
select * from t1 where a is not null;
select * from t1 where a >= 1 and a < 3;
select * from t1 where a >= 3 and a <= 5;
select * from t1 where a > 2 and a < 4;
select * from t1 where a > 3 and a <= 6;
select * from t1 where a > 5;
select * from t1 where a >= 1 and a <= 5;
explain partitions select * from t1 where a < 0;
explain partitions select * from t1 where a is null or (a >= 5 and a <= 7); 
explain partitions select * from t1 where a is null;
explain partitions select * from t1 where a is not null;
explain partitions select * from t1 where a >= 1 and a < 3;
explain partitions select * from t1 where a >= 3 and a <= 5;
explain partitions select * from t1 where a > 2 and a < 4;
explain partitions select * from t1 where a > 3 and a <= 6;
explain partitions select * from t1 where a > 5;
explain partitions select * from t1 where a >= 1 and a <= 5;

drop table t1;

42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107
#
# Partition by hash, basic
#
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;

update t1 set c=3 where b=1;
select * from t1;

select b from t1 where a=3;
select b,c from t1 where a=1 AND b=1;

delete from t1 where a=1;
delete from t1 where c=3;

select * from t1;

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;
drop table t1;

#
# Partition by hash, only one partition
#
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;
#
# Partition by key, only one partition
#
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;
108 109 110 111 112 113 114 115 116

#
# Bug# 15968 - crash when INSERT with f1 = -1 into partition by hash(f1)
#
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;
drop table t1;

117 118 119 120 121 122
#
# BUG# 14524 Partitions: crash if blackhole
#
CREATE TABLE t1 (s1 int) ENGINE=BLACKHOLE PARTITION BY HASH (s1);
INSERT INTO t1 VALUES (0);
DROP TABLE t1;
123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141

#
# BUG 18423 Hash partitioning can lose rows in some queries
#
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;
select * from t1 where c3 between '2002-01-01' and '2002-12-31';

drop table t1;