#--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 # # BUG 18198: Partition functions handling # create table t1 (a varchar(10) charset latin1 collate latin1_bin) partition by hash(length(a)) partitions 10; insert into t1 values (''),(' '),('a'),('a '),('a '); explain partitions select * from t1 where a='a '; explain partitions select * from t1 where a='a'; explain partitions select * from t1 where a='a ' OR a='a'; drop table t1; # # 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; # # 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; # # 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; # # BUG# 14524 Partitions: crash if blackhole # CREATE TABLE t1 (s1 int) ENGINE=BLACKHOLE PARTITION BY HASH (s1); --error 0,ER_BINLOG_ROW_FORMAT_FORBIDDEN INSERT INTO t1 VALUES (0); DROP TABLE t1; # # 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;