Debian Codegraph @

mysql-test/suite/ndb/r/ndb_partition_range.result

raw
1 Warnings:
2 Warning 1287 '@@new' is deprecated and will be removed in a future release.
3 CREATE TABLE t1 (
4 a int not null,
5 b int not null,
6 c int not null,
7 primary key(a,b),
8 index (a))
9 engine = ndb
10 partition by range (a)
11 partitions 3
12 (partition x1 values less than (5),
13 partition x2 values less than (10),
14 partition x3 values less than (20));
15 INSERT into t1 values (1, 1, 1);
16 INSERT into t1 values (6, 1, 1);
17 INSERT into t1 values (10, 1, 1);
18 INSERT into t1 values (15, 1, 1);
19 select * from information_schema.partitions where table_name= 't1';
20 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME
21 def test t1 x1 NULL 1 NULL RANGE NULL `a` NULL 5 1 40 32768 # 0 0 # # NULL NULL default NULL
22 def test t1 x2 NULL 2 NULL RANGE NULL `a` NULL 10 1 40 32768 # 0 0 # # NULL NULL default NULL
23 def test t1 x3 NULL 3 NULL RANGE NULL `a` NULL 20 2 40 32768 # 0 0 # # NULL NULL default NULL
24 select * from t1 order by a;
25 a b c
26 1 1 1
27 6 1 1
28 10 1 1
29 15 1 1
30 select * from t1 where a=1 order by a;
31 a b c
32 1 1 1
33 select * from t1 where a=15 and b=1 order by a;
34 a b c
35 15 1 1
36 select * from t1 where a=21 and b=1 order by a;
37 a b c
38 select * from t1 where a=21 order by a;
39 a b c
40 select * from t1 where a in (1,6,10,21) order by a;
41 a b c
42 1 1 1
43 6 1 1
44 10 1 1
45 select * from t1 where b=1 and a in (1,6,10,21) order by a;
46 a b c
47 1 1 1
48 6 1 1
49 10 1 1
50 INSERT into t1 values (1, 2, 2);
51 select max(b) from t1 where a = 1;
52 max(b)
53 2
54 select b from t1 where a = 1 order by b desc;
55 b
56 2
57 1
58 drop table t1;
59 CREATE TABLE t1 (
60 a int not null,
61 b int not null,
62 c int not null,
63 primary key(b),
64 unique (a))
65 engine = ndb
66 partition by range (b)
67 partitions 3
68 (partition x1 values less than (5),
69 partition x2 values less than (10),
70 partition x3 values less than (20));
71 INSERT into t1 values (1, 1, 1);
72 INSERT into t1 values (2, 6, 1);
73 INSERT into t1 values (3, 10, 1);
74 INSERT into t1 values (4, 15, 1);
75 select * from t1 order by a;
76 a b c
77 1 1 1
78 2 6 1
79 3 10 1
80 4 15 1
81 UPDATE t1 set a = 5 WHERE b = 15;
82 select * from t1 order by a;
83 a b c
84 1 1 1
85 2 6 1
86 3 10 1
87 5 15 1
88 UPDATE t1 set a = 6 WHERE a = 5;
89 select * from t1 order by a;
90 a b c
91 1 1 1
92 2 6 1
93 3 10 1
94 6 15 1
95 select * from t1 where b=1 order by b;
96 a b c
97 1 1 1
98 select * from t1 where b=15 and a=1 order by b;
99 a b c
100 select * from t1 where b=21 and a=1 order by b;
101 a b c
102 select * from t1 where b=21 order by b;
103 a b c
104 select * from t1 where b in (1,6,10,21) order by b;
105 a b c
106 1 1 1
107 2 6 1
108 3 10 1
109 select * from t1 where a in (1,2,5,6) order by b;
110 a b c
111 1 1 1
112 2 6 1
113 6 15 1
114 select * from t1 where a=1 and b in (1,6,10,21) order by b;
115 a b c
116 1 1 1
117 select * from t1 where b=6;
118 a b c
119 2 6 1
120 DELETE from t1 WHERE b = 6;
121 select * from t1 where b=6;
122 a b c
123 select * from t1 where a=6;
124 a b c
125 6 15 1
126 UPDATE t1 SET c=1001 WHERE a=6;
127 select * from t1 where a=6;
128 a b c
129 6 15 1001
130 DELETE from t1 WHERE a = 6;
131 select * from t1 where a=6;
132 a b c
133 show create table t1;
134 Table Create Table
135 t1 CREATE TABLE `t1` (
136 `a` int NOT NULL,
137 `b` int NOT NULL,
138 `c` int NOT NULL,
139 PRIMARY KEY (`b`),
140 UNIQUE KEY `a` (`a`)
141 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
142 /*!50100 PARTITION BY RANGE (`b`)
143 (PARTITION x1 VALUES LESS THAN (5) ENGINE = ndbcluster,
144 PARTITION x2 VALUES LESS THAN (10) ENGINE = ndbcluster,
145 PARTITION x3 VALUES LESS THAN (20) ENGINE = ndbcluster) */
146 drop table t1;
147 CREATE TABLE t1
148 (id MEDIUMINT NOT NULL,
149 b1 BIT(8),
150 vc VARCHAR(255),
151 bc CHAR(255),
152 d DECIMAL(10,4) DEFAULT 0,
153 f FLOAT DEFAULT 0,
154 total BIGINT UNSIGNED,
155 y YEAR,
156 t DATE PRIMARY KEY) ENGINE=NDB
157 PARTITION BY RANGE (YEAR(t))
158 (PARTITION p0 VALUES LESS THAN (1901),
159 PARTITION p1 VALUES LESS THAN (1946),
160 PARTITION p2 VALUES LESS THAN (1966),
161 PARTITION p3 VALUES LESS THAN (1986),
162 PARTITION p4 VALUES LESS THAN (2005),
163 PARTITION p5 VALUES LESS THAN MAXVALUE);
164 INSERT INTO t1 VALUES (0,NULL,NULL,NULL,NULL,NULL,NULL,NULL, '2009-05-15');
165 SELECT * FROM t1;
166 id b1 vc bc d f total y t
167 0 NULL NULL NULL NULL NULL NULL NULL 2009-05-15
168 ALTER TABLE t1 ENGINE=INNODB;
169 SELECT * FROM t1;
170 id b1 vc bc d f total y t
171 0 NULL NULL NULL NULL NULL NULL NULL 2009-05-15
172 DROP TABLE t1;
173 CREATE LOGFILE GROUP lg1
174 ADD UNDOFILE 'undofile.dat'
175 INITIAL_SIZE 16M
176 UNDO_BUFFER_SIZE=1M
177 ENGINE=NDB;
178 CREATE TABLESPACE ts1
179 ADD DATAFILE 'datafile.dat'
180 USE LOGFILE GROUP lg1
181 INITIAL_SIZE 12M
182 EXTENT_SIZE 256k
183 ENGINE NDB;
184 CREATE TABLE test.t1 (
185 a1 INT,
186 a2 TEXT NOT NULL,
187 a3 BIT NOT NULL,
188 a4 DECIMAL(8,3),
189 a5 INT NOT NULL,
190 a6 INT,
191 PRIMARY KEY(a1))
192 TABLESPACE ts1 STORAGE DISK ENGINE=NDB
193 PARTITION BY LIST (a1)
194 (PARTITION p0 VALUES IN (1,2,3,4,5),
195 PARTITION p1 VALUES IN (6,7,8,9, 10),
196 PARTITION p2 VALUES IN (11, 12, 13, 14, 15));
197 ALTER TABLE test.t1 DROP COLUMN a6;
198 ALTER TABLE test.t1 ADD COLUMN a6 VARCHAR(255);
199 SELECT COUNT(*) FROM test.t1;
200 COUNT(*)
201 15
202 ALTER TABLE test.t1 DROP COLUMN a4;
203 SELECT COUNT(*) FROM test.t1;
204 COUNT(*)
205 15
206 DROP TABLE t1;
207 CREATE TABLE test.t1 (
208 a1 INT,
209 a2 TEXT NOT NULL,
210 a3 BIT NOT NULL,
211 a4 DECIMAL(8,3),
212 a5 INT NOT NULL,
213 a6 VARCHAR(255),
214 PRIMARY KEY(a1))
215 TABLESPACE ts1 STORAGE DISK ENGINE=NDB
216 PARTITION BY HASH(a1)
217 PARTITIONS 4;
218 SELECT COUNT(*) FROM test.t1;
219 COUNT(*)
220 15
221 ALTER TABLE test.t1 DROP COLUMN a4;
222 SELECT COUNT(*) FROM test.t1;
223 COUNT(*)
224 15
225 DROP TABLE t1;
226 ALTER TABLESPACE ts1
227 DROP DATAFILE 'datafile.dat';
228 DROP TABLESPACE ts1;
229 DROP LOGFILE GROUP lg1 ENGINE=NDB;
230 CREATE TABLE t1
231 (id MEDIUMINT NOT NULL PRIMARY KEY,
232 b1 BIT(8),
233 vc VARCHAR(255),
234 bc CHAR(255),
235 d DECIMAL(10,4) DEFAULT 0,
236 f FLOAT DEFAULT 0,
237 total BIGINT UNSIGNED,
238 y YEAR,
239 t DATE) ENGINE=NDB
240 PARTITION BY LIST(id)
241 (PARTITION p0 VALUES IN (2, 4),
242 PARTITION p1 VALUES IN (42, 142));
243 INSERT INTO t1 VALUES (2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
244 SELECT * FROM t1;
245 id b1 vc bc d f total y t
246 2 NULL NULL NULL NULL NULL NULL NULL NULL
247 ALTER TABLE t1 ADD PARTITION
248 (PARTITION p2 VALUES IN (412));
249 SELECT * FROM t1;
250 id b1 vc bc d f total y t
251 2 NULL NULL NULL NULL NULL NULL NULL NULL
252 DROP TABLE t1;
253 CREATE TABLE t1 (
254 a int not null PRIMARY KEY,
255 b int not null,
256 c int not null) engine=ndb
257 partition by list(a)
258 partitions 2
259 (partition x123 values in (1,5,6),
260 partition x234 values in (4,7,8));
261 INSERT into t1 VALUES (5,1,1);
262 select * from t1;
263 a b c
264 5 1 1
265 UPDATE t1 SET a=8 WHERE a=5 AND b=1;
266 select * from t1;
267 a b c
268 8 1 1
269 drop table t1;
270 CREATE TABLE t1 ( f1 INTEGER PRIMARY KEY, f2 char(20)) engine=ndb
271 PARTITION BY RANGE(f1)
272 ( PARTITION part1 VALUES LESS THAN (2),
273 PARTITION part2 VALUES LESS THAN (1000));
274 INSERT INTO t1 VALUES(1, '---1---');
275 INSERT INTO t1 VALUES(2, '---2---');
276 select * from t1 order by f1;
277 f1 f2
278 1 ---1---
279 2 ---2---
280 UPDATE t1 SET f1 = f1 + 4 WHERE f1 = 2;
281 select * from t1 order by f1;
282 f1 f2
283 1 ---1---
284 6 ---2---
285 UPDATE t1 SET f1 = f1 + 4 WHERE f1 = 1;
286 select * from t1 order by f1;
287 f1 f2
288 5 ---1---
289 6 ---2---
290 drop table t1;
291 create table t1 ( a int, b int, c int, primary key (a,b)) engine=ndb
292 partition by range (a)
293 (partition part0 values less than (3),
294 partition part1 values less than (6));
295 insert into t1 values (0, 0, 0);
296 insert into t1 values (0, 1, 1);
297 insert into t1 values (0, 2, 2);
298 insert into t1 values (1, 0, 3);
299 insert into t1 values (1, 1, 4);
300 insert into t1 values (1, 2, 5);
301 insert into t1 values (4, 0, 6);
302 insert into t1 values (4, 1, 7);
303 insert into t1 values (4, 2, 8);
304 All partitions scan
305 select * from t1 order by c;
306 a b c
307 0 0 0
308 0 1 1
309 0 2 2
310 1 0 3
311 1 1 4
312 1 2 5
313 4 0 6
314 4 1 7
315 4 2 8
316 ndb_scan_count
317 1
318 ndb_pruned_scan_count
319 0
320 Single partition scan
321 select * from t1 where a=0 order by c;
322 a b c
323 0 0 0
324 0 1 1
325 0 2 2
326 ndb_scan_count
327 1
328 ndb_pruned_scan_count
329 1
330 Single partition scan
331 select * from t1 where a=4 order by c;
332 a b c
333 4 0 6
334 4 1 7
335 4 2 8
336 ndb_scan_count
337 1
338 ndb_pruned_scan_count
339 1
340 Single partition scan
341 select * from t1 where a<3 order by c;
342 a b c
343 0 0 0
344 0 1 1
345 0 2 2
346 1 0 3
347 1 1 4
348 1 2 5
349 ndb_scan_count
350 1
351 ndb_pruned_scan_count
352 1
353 MRR single partition scan
354 select * from t1 where a in (0, 2) order by c;
355 a b c
356 0 0 0
357 0 1 1
358 0 2 2
359 ndb_scan_count
360 1
361 ndb_pruned_scan_count
362 1
363 drop table t1;