1 #--disable_abort_on_error
3 # Simple test for the partition storage engine
4 # Taken fromm the select test
6 -- source include/have_partition.inc
9 drop table if exists t1;
13 # Bug#30822: crash when COALESCE
15 CREATE TABLE t1 (c1 INT)
16 PARTITION BY HASH (c1)
18 INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
19 ALTER TABLE t1 COALESCE PARTITION 13;
21 CREATE TABLE t1 (c1 INT)
22 PARTITION BY LINEAR HASH (c1)
24 INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
25 ALTER TABLE t1 COALESCE PARTITION 3;
29 # More partition pruning tests, especially on interval walking
31 create table t1 (a int unsigned)
32 partition by hash(a div 2)
34 insert into t1 values (null),(0),(1),(2),(3),(4),(5),(6),(7);
35 select * from t1 where a < 0;
36 select * from t1 where a is null or (a >= 5 and a <= 7);
37 select * from t1 where a is null;
38 select * from t1 where a is not null;
39 select * from t1 where a >= 1 and a < 3;
40 select * from t1 where a >= 3 and a <= 5;
41 select * from t1 where a > 2 and a < 4;
42 select * from t1 where a > 3 and a <= 6;
43 select * from t1 where a > 5;
44 select * from t1 where a >= 1 and a <= 5;
45 explain partitions select * from t1 where a < 0;
46 explain partitions select * from t1 where a is null or (a >= 5 and a <= 7);
47 explain partitions select * from t1 where a is null;
48 explain partitions select * from t1 where a is not null;
49 explain partitions select * from t1 where a >= 1 and a < 3;
50 explain partitions select * from t1 where a >= 3 and a <= 5;
51 explain partitions select * from t1 where a > 2 and a < 4;
52 explain partitions select * from t1 where a > 3 and a <= 6;
53 explain partitions select * from t1 where a > 5;
54 explain partitions select * from t1 where a >= 1 and a <= 5;
59 # Partition by hash, basic
66 partition by hash (a + 2)
68 (partition x1 tablespace ts1,
69 partition x2 tablespace ts2,
70 partition x3 tablespace ts3);
72 insert into t1 values (1,1,1);
73 insert into t1 values (2,1,1);
74 insert into t1 values (3,1,1);
75 insert into t1 values (4,1,1);
76 insert into t1 values (5,1,1);
80 update t1 set c=3 where b=1;
83 select b from t1 where a=3;
84 select b,c from t1 where a=1 AND b=1;
86 delete from t1 where a=1;
87 delete from t1 where c=3;
92 partition by hash (a + 3)
94 (partition x1 tablespace ts1,
95 partition x2 tablespace ts2,
96 partition x3 tablespace ts3);
101 # Partition by hash, only one partition
108 partition by hash (a)
113 # Partition by key, only one partition
126 # Bug# 15968 - crash when INSERT with f1 = -1 into partition by hash(f1)
128 CREATE TABLE t1 (f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM' PARTITION BY HASH(f1) PARTITIONS 2;
129 INSERT INTO t1 SET f1 = 0 - 1, f2 = '#######';
134 # BUG# 14524 Partitions: crash if blackhole
136 # Disable warnings to allow this test case to run without
137 # the Blackhole storage engine.
139 CREATE TABLE t1 (s1 int) ENGINE=BLACKHOLE PARTITION BY HASH (s1);
141 --error 0,ER_BINLOG_LOGGING_IMPOSSIBLE
142 INSERT INTO t1 VALUES (0);
146 # BUG 18423 Hash partitioning can lose rows in some queries
148 create table t1 (c1 int DEFAULT NULL,
149 c2 varchar (30) DEFAULT NULL,
150 c3 date DEFAULT NULL)
152 partition by hash (to_days(c3))
155 insert into t1 values
156 (136,'abc','2002-01-05'),(142,'abc','2002-02-14'),(162,'abc','2002-06-28'),
157 (182,'abc','2002-11-09'),(158,'abc','2002-06-01'),(184,'abc','2002-11-22');
159 select * from t1 where c3 between '2002-01-01' and '2002-12-31';
164 # Bug#31210 - INSERT DELAYED crashes server when used on partitioned table
166 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM PARTITION BY HASH(c1) PARTITIONS 1;
167 # The test succeeds in an embedded server because normal insert is done.
168 # The test fails in a normal server with
169 # "DELAYED option not supported by table".
170 --error 0, ER_DELAYED_NOT_SUPPORTED
171 INSERT DELAYED INTO t1 VALUES (1);