Debian Codegraph @

mysql-test/suite/ndb/t/ndb_partition_range.test

raw
1 -- source include/have_ndb.inc
2 #
3 # Simple test for the partition storage engine
4 # Focuses on range partitioning tests
5 #
6 #-- source include/have_partition.inc
7
8 --disable_query_log
9 # This test uses "partition by range" which is a feature only available
10 # when turning on MySQL's system variable "new", this functionality
11 # is certainly not new but still only enabled when "new" is on.
12 set new=on;
13 --enable_query_log
14
15 #
16 # Partition by range, basic
17 #
18 CREATE TABLE t1 (
19 a int not null,
20 b int not null,
21 c int not null,
22 primary key(a,b),
23 index (a))
24 engine = ndb
25 partition by range (a)
26 partitions 3
27 (partition x1 values less than (5),
28 partition x2 values less than (10),
29 partition x3 values less than (20));
30
31 # Simple insert and verify test
32 INSERT into t1 values (1, 1, 1);
33 INSERT into t1 values (6, 1, 1);
34 INSERT into t1 values (10, 1, 1);
35 INSERT into t1 values (15, 1, 1);
36
37 --replace_column 16 # 19 # 20 #
38 --sorted_result
39 select * from information_schema.partitions where table_name= 't1';
40
41 select * from t1 order by a;
42
43 select * from t1 where a=1 order by a;
44 select * from t1 where a=15 and b=1 order by a;
45 select * from t1 where a=21 and b=1 order by a;
46 select * from t1 where a=21 order by a;
47 select * from t1 where a in (1,6,10,21) order by a;
48 select * from t1 where b=1 and a in (1,6,10,21) order by a;
49
50 # BUG#33061: ORDER BY DESC becomes ASC in NDB partition pruning to one partition
51 INSERT into t1 values (1, 2, 2);
52 select max(b) from t1 where a = 1;
53 select b from t1 where a = 1 order by b desc;
54
55 drop table t1;
56
57 #
58 # Partition by range, basic
59 #
60 CREATE TABLE t1 (
61 a int not null,
62 b int not null,
63 c int not null,
64 primary key(b),
65 unique (a))
66 engine = ndb
67 partition by range (b)
68 partitions 3
69 (partition x1 values less than (5),
70 partition x2 values less than (10),
71 partition x3 values less than (20));
72
73 # Simple insert and verify test
74 INSERT into t1 values (1, 1, 1);
75 INSERT into t1 values (2, 6, 1);
76 INSERT into t1 values (3, 10, 1);
77 INSERT into t1 values (4, 15, 1);
78
79 select * from t1 order by a;
80 UPDATE t1 set a = 5 WHERE b = 15;
81 select * from t1 order by a;
82 UPDATE t1 set a = 6 WHERE a = 5;
83 select * from t1 order by a;
84
85 select * from t1 where b=1 order by b;
86 select * from t1 where b=15 and a=1 order by b;
87 select * from t1 where b=21 and a=1 order by b;
88 select * from t1 where b=21 order by b;
89 select * from t1 where b in (1,6,10,21) order by b;
90 select * from t1 where a in (1,2,5,6) order by b;
91 select * from t1 where a=1 and b in (1,6,10,21) order by b;
92
93 # test for bug#42443
94 select * from t1 where b=6;
95 DELETE from t1 WHERE b = 6;
96 select * from t1 where b=6;
97 select * from t1 where a=6;
98 UPDATE t1 SET c=1001 WHERE a=6;
99 select * from t1 where a=6;
100 DELETE from t1 WHERE a = 6;
101 select * from t1 where a=6;
102
103 #
104 # Test that explicit partition info _is_ shown in show create table
105 # result _should_ contain (PARTITION x1 ... etc)
106 #
107 --disable_warnings
108 show create table t1;
109 --enable_warnings
110
111 drop table t1;
112
113 #
114 # Bug #17499, #17687
115 # Alter partitioned NDB table causes mysqld to core
116 #
117
118 CREATE TABLE t1
119 (id MEDIUMINT NOT NULL,
120 b1 BIT(8),
121 vc VARCHAR(255),
122 bc CHAR(255),
123 d DECIMAL(10,4) DEFAULT 0,
124 f FLOAT DEFAULT 0,
125 total BIGINT UNSIGNED,
126 y YEAR,
127 t DATE PRIMARY KEY) ENGINE=NDB
128 PARTITION BY RANGE (YEAR(t))
129 (PARTITION p0 VALUES LESS THAN (1901),
130 PARTITION p1 VALUES LESS THAN (1946),
131 PARTITION p2 VALUES LESS THAN (1966),
132 PARTITION p3 VALUES LESS THAN (1986),
133 PARTITION p4 VALUES LESS THAN (2005),
134 PARTITION p5 VALUES LESS THAN MAXVALUE);
135
136 INSERT INTO t1 VALUES (0,NULL,NULL,NULL,NULL,NULL,NULL,NULL, '2009-05-15');
137 SELECT * FROM t1;
138 ALTER TABLE t1 ENGINE=INNODB;
139 SELECT * FROM t1;
140 DROP TABLE t1;
141
142 CREATE LOGFILE GROUP lg1
143 ADD UNDOFILE 'undofile.dat'
144 INITIAL_SIZE 16M
145 UNDO_BUFFER_SIZE=1M
146 ENGINE=NDB;
147
148 CREATE TABLESPACE ts1
149 ADD DATAFILE 'datafile.dat'
150 USE LOGFILE GROUP lg1
151 INITIAL_SIZE 12M
152 EXTENT_SIZE 256k
153 ENGINE NDB;
154
155 CREATE TABLE test.t1 (
156 a1 INT,
157 a2 TEXT NOT NULL,
158 a3 BIT NOT NULL,
159 a4 DECIMAL(8,3),
160 a5 INT NOT NULL,
161 a6 INT,
162 PRIMARY KEY(a1))
163 TABLESPACE ts1 STORAGE DISK ENGINE=NDB
164 PARTITION BY LIST (a1)
165 (PARTITION p0 VALUES IN (1,2,3,4,5),
166 PARTITION p1 VALUES IN (6,7,8,9, 10),
167 PARTITION p2 VALUES IN (11, 12, 13, 14, 15));
168
169 # Alter table directly without any statements inbetween
170 ALTER TABLE test.t1 DROP COLUMN a6;
171 ALTER TABLE test.t1 ADD COLUMN a6 VARCHAR(255);
172
173 let $j= 15;
174 --disable_query_log
175 while ($j)
176 {
177 eval INSERT INTO test.t1 VALUES ($j, "Tested Remotely from Texas, USA",
178 b'1',$j.00,$j+1,"By NIK $j");
179 dec $j;
180 }
181 --enable_query_log
182 SELECT COUNT(*) FROM test.t1;
183
184 ALTER TABLE test.t1 DROP COLUMN a4;
185 SELECT COUNT(*) FROM test.t1;
186
187 DROP TABLE t1;
188
189 CREATE TABLE test.t1 (
190 a1 INT,
191 a2 TEXT NOT NULL,
192 a3 BIT NOT NULL,
193 a4 DECIMAL(8,3),
194 a5 INT NOT NULL,
195 a6 VARCHAR(255),
196 PRIMARY KEY(a1))
197 TABLESPACE ts1 STORAGE DISK ENGINE=NDB
198 PARTITION BY HASH(a1)
199 PARTITIONS 4;
200
201 let $j= 15;
202 --disable_query_log
203 while ($j)
204 {
205 eval INSERT INTO test.t1 VALUES ($j, "Tested Remotely from Texas, USA",
206 b'1',$j.00,$j+1,"By NIK $j");
207 dec $j;
208 }
209 --enable_query_log
210 SELECT COUNT(*) FROM test.t1;
211
212 ALTER TABLE test.t1 DROP COLUMN a4;
213 SELECT COUNT(*) FROM test.t1;
214
215 DROP TABLE t1;
216
217 ALTER TABLESPACE ts1
218 DROP DATAFILE 'datafile.dat';
219 DROP TABLESPACE ts1;
220 DROP LOGFILE GROUP lg1 ENGINE=NDB;
221
222
223 #
224 # Bug #17701 ALTER TABLE t1 ADD PARTITION for PARTITION BY LIST hangs test
225 #
226
227 CREATE TABLE t1
228 (id MEDIUMINT NOT NULL PRIMARY KEY,
229 b1 BIT(8),
230 vc VARCHAR(255),
231 bc CHAR(255),
232 d DECIMAL(10,4) DEFAULT 0,
233 f FLOAT DEFAULT 0,
234 total BIGINT UNSIGNED,
235 y YEAR,
236 t DATE) ENGINE=NDB
237 PARTITION BY LIST(id)
238 (PARTITION p0 VALUES IN (2, 4),
239 PARTITION p1 VALUES IN (42, 142));
240
241 INSERT INTO t1 VALUES (2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
242 SELECT * FROM t1;
243 ALTER TABLE t1 ADD PARTITION
244 (PARTITION p2 VALUES IN (412));
245 SELECT * FROM t1;
246 DROP TABLE t1;
247
248 #
249 # Bug #17806 Update on NDB table with list partition causes mysqld to core
250 # Bug #16385 Partitions: crash when updating a range partitioned NDB table
251 #
252 CREATE TABLE t1 (
253 a int not null PRIMARY KEY,
254 b int not null,
255 c int not null) engine=ndb
256 partition by list(a)
257 partitions 2
258 (partition x123 values in (1,5,6),
259 partition x234 values in (4,7,8));
260 INSERT into t1 VALUES (5,1,1);
261 select * from t1;
262 UPDATE t1 SET a=8 WHERE a=5 AND b=1;
263 select * from t1;
264 drop table t1;
265
266 CREATE TABLE t1 ( f1 INTEGER PRIMARY KEY, f2 char(20)) engine=ndb
267 PARTITION BY RANGE(f1)
268 ( PARTITION part1 VALUES LESS THAN (2),
269 PARTITION part2 VALUES LESS THAN (1000));
270 INSERT INTO t1 VALUES(1, '---1---');
271 INSERT INTO t1 VALUES(2, '---2---');
272 select * from t1 order by f1;
273 UPDATE t1 SET f1 = f1 + 4 WHERE f1 = 2;
274 select * from t1 order by f1;
275 UPDATE t1 SET f1 = f1 + 4 WHERE f1 = 1;
276 select * from t1 order by f1;
277 drop table t1;
278
279
280 # Check partitioning at NdbApi level for range partitioned table
281 #
282 create table t1 ( a int, b int, c int, primary key (a,b)) engine=ndb
283 partition by range (a)
284 (partition part0 values less than (3),
285 partition part1 values less than (6));
286
287 insert into t1 values (0, 0, 0);
288 insert into t1 values (0, 1, 1);
289 insert into t1 values (0, 2, 2);
290 insert into t1 values (1, 0, 3);
291 insert into t1 values (1, 1, 4);
292 insert into t1 values (1, 2, 5);
293 insert into t1 values (4, 0, 6);
294 insert into t1 values (4, 1, 7);
295 insert into t1 values (4, 2, 8);
296
297 --echo All partitions scan
298 --source suite/ndb/include/ndb_init_scan_counts.inc
299 select * from t1 order by c;
300 --source suite/ndb/include/ndb_scan_counts.inc
301
302 --echo Single partition scan
303 --source suite/ndb/include/ndb_init_scan_counts.inc
304 select * from t1 where a=0 order by c;
305 --source suite/ndb/include/ndb_scan_counts.inc
306
307 --echo Single partition scan
308 --source suite/ndb/include/ndb_init_scan_counts.inc
309 select * from t1 where a=4 order by c;
310 --source suite/ndb/include/ndb_scan_counts.inc
311
312 --echo Single partition scan
313 --source suite/ndb/include/ndb_init_scan_counts.inc
314 select * from t1 where a<3 order by c;
315 --source suite/ndb/include/ndb_scan_counts.inc
316
317 --echo MRR single partition scan
318 --source suite/ndb/include/ndb_init_scan_counts.inc
319 select * from t1 where a in (0, 2) order by c;
320 --source suite/ndb/include/ndb_scan_counts.inc
321
322 drop table t1;
323