1
-- source include/have_ndb.inc
2
#--disable_abort_on_error
4
# Simple test for the partition storage engine
5
# Focuses on range partitioning tests
7
#-- source include/have_partition.inc
14
drop table if exists t1;
18
# Partition by range, basic
27
partition by range (a)
29
(partition x1 values less than (5),
30
partition x2 values less than (10),
31
partition x3 values less than (20));
33
# Simple insert and verify test
34
INSERT into t1 values (1, 1, 1);
35
INSERT into t1 values (6, 1, 1);
36
INSERT into t1 values (10, 1, 1);
37
INSERT into t1 values (15, 1, 1);
39
--replace_column 16 # 19 # 20 #
40
select * from information_schema.partitions where table_name= 't1';
42
select * from t1 order by a;
44
select * from t1 where a=1 order by a;
45
select * from t1 where a=15 and b=1 order by a;
46
select * from t1 where a=21 and b=1 order by a;
47
select * from t1 where a=21 order by a;
48
select * from t1 where a in (1,6,10,21) order by a;
49
select * from t1 where b=1 and a in (1,6,10,21) order by a;
51
# BUG#33061: ORDER BY DESC becomes ASC in NDB partition pruning to one partition
52
INSERT into t1 values (1, 2, 2);
53
select max(b) from t1 where a = 1;
54
select b from t1 where a = 1 order by b desc;
59
# Partition by range, basic
68
partition by range (b)
70
(partition x1 values less than (5),
71
partition x2 values less than (10),
72
partition x3 values less than (20));
74
# Simple insert and verify test
75
INSERT into t1 values (1, 1, 1);
76
INSERT into t1 values (2, 6, 1);
77
INSERT into t1 values (3, 10, 1);
78
INSERT into t1 values (4, 15, 1);
80
select * from t1 order by a;
81
UPDATE t1 set a = 5 WHERE b = 15;
82
select * from t1 order by a;
83
UPDATE t1 set a = 6 WHERE a = 5;
84
select * from t1 order by a;
86
select * from t1 where b=1 order by b;
87
select * from t1 where b=15 and a=1 order by b;
88
select * from t1 where b=21 and a=1 order by b;
89
select * from t1 where b=21 order by b;
90
select * from t1 where b in (1,6,10,21) order by b;
91
select * from t1 where a in (1,2,5,6) order by b;
92
select * from t1 where a=1 and b in (1,6,10,21) order by b;
94
DELETE from t1 WHERE b = 6;
95
DELETE from t1 WHERE a = 6;
98
# Test that explicit partition info _is_ shown in show create table
99
# result _should_ contain (PARTITION x1 ... etc)
101
show create table t1;
107
# Alter partitioned NDB table causes mysqld to core
111
(id MEDIUMINT NOT NULL,
115
d DECIMAL(10,4) DEFAULT 0,
117
total BIGINT UNSIGNED,
120
PARTITION BY RANGE (YEAR(t))
121
(PARTITION p0 VALUES LESS THAN (1901),
122
PARTITION p1 VALUES LESS THAN (1946),
123
PARTITION p2 VALUES LESS THAN (1966),
124
PARTITION p3 VALUES LESS THAN (1986),
125
PARTITION p4 VALUES LESS THAN (2005),
126
PARTITION p5 VALUES LESS THAN MAXVALUE);
128
INSERT INTO t1 VALUES (0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
130
ALTER TABLE t1 ENGINE=MYISAM;
134
CREATE LOGFILE GROUP lg1
135
ADD UNDOFILE 'undofile.dat'
140
CREATE TABLESPACE ts1
141
ADD DATAFILE 'datafile.dat'
142
USE LOGFILE GROUP lg1
146
CREATE TABLE test.t1 (
154
TABLESPACE ts1 STORAGE DISK ENGINE=NDB
155
PARTITION BY LIST (a1)
156
(PARTITION p0 VALUES IN (1,2,3,4,5),
157
PARTITION p1 VALUES IN (6,7,8,9, 10),
158
PARTITION p2 VALUES IN (11, 12, 13, 14, 15));
160
# Alter table directly without any statements inbetween
161
ALTER TABLE test.t1 DROP COLUMN a6;
162
ALTER TABLE test.t1 ADD COLUMN a6 VARCHAR(255);
168
eval INSERT INTO test.t1 VALUES ($j, "Tested Remotely from Texas, USA",
169
b'1',$j.00,$j+1,"By NIK $j");
173
SELECT COUNT(*) FROM test.t1;
175
ALTER TABLE test.t1 DROP COLUMN a4;
176
SELECT COUNT(*) FROM test.t1;
180
CREATE TABLE test.t1 (
188
TABLESPACE ts1 STORAGE DISK ENGINE=NDB
189
PARTITION BY HASH(a1)
196
eval INSERT INTO test.t1 VALUES ($j, "Tested Remotely from Texas, USA",
197
b'1',$j.00,$j+1,"By NIK $j");
201
SELECT COUNT(*) FROM test.t1;
203
ALTER TABLE test.t1 DROP COLUMN a4;
204
SELECT COUNT(*) FROM test.t1;
209
DROP DATAFILE 'datafile.dat'
211
DROP TABLESPACE ts1 ENGINE=NDB;
212
DROP LOGFILE GROUP lg1 ENGINE=NDB;
216
# Bug #17701 ALTER TABLE t1 ADD PARTITION for PARTITION BY LIST hangs test
220
(id MEDIUMINT NOT NULL,
224
d DECIMAL(10,4) DEFAULT 0,
226
total BIGINT UNSIGNED,
229
PARTITION BY LIST(id)
230
(PARTITION p0 VALUES IN (2, 4),
231
PARTITION p1 VALUES IN (42, 142));
233
INSERT INTO t1 VALUES (2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
235
ALTER TABLE t1 ADD PARTITION
236
(PARTITION p2 VALUES IN (412));
241
# Bug #17806 Update on NDB table with list partition causes mysqld to core
242
# Bug #16385 Partitions: crash when updating a range partitioned NDB table
250
(partition x123 values in (1,5,6),
251
partition x234 values in (4,7,8));
252
INSERT into t1 VALUES (5,1,1);
254
UPDATE t1 SET a=8 WHERE a=5 AND b=1;
258
CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) engine=ndb
259
PARTITION BY RANGE(f1)
260
( PARTITION part1 VALUES LESS THAN (2),
261
PARTITION part2 VALUES LESS THAN (1000));
262
INSERT INTO t1 VALUES(1, '---1---');
263
INSERT INTO t1 VALUES(2, '---2---');
264
select * from t1 order by f1;
265
UPDATE t1 SET f1 = f1 + 4 WHERE f1 = 2;
266
select * from t1 order by f1;
267
UPDATE t1 SET f1 = f1 + 4 WHERE f1 = 1;
268
select * from t1 order by f1;