Debian Codegraph @

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

raw
1 *****
2 **** Copy data from table in one table space to table
3 **** in different table space
4 *****
5 CREATE LOGFILE GROUP lg
6 ADD UNDOFILE './lg_group/undofile.dat'
7 INITIAL_SIZE 16M
8 UNDO_BUFFER_SIZE = 1M
9 ENGINE=NDB;
10 CREATE TABLESPACE ts1
11 ADD DATAFILE './table_space1/datafile.dat'
12 USE LOGFILE GROUP lg
13 INITIAL_SIZE 30M
14 EXTENT_SIZE 256K
15 ENGINE NDB;
16 CREATE TABLESPACE ts2
17 ADD DATAFILE './table_space2/datafile.dat'
18 USE LOGFILE GROUP lg
19 INITIAL_SIZE 20M
20 ENGINE NDB;
21 CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB)
22 TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
23 CREATE TABLE test.t2 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB)
24 TABLESPACE ts2 STORAGE DISK ENGINE=NDB;
25 SHOW CREATE TABLE test.t1;
26 Table Create Table
27 t1 CREATE TABLE `t1` (
28 `a1` int NOT NULL,
29 `a2` varchar(256) DEFAULT NULL,
30 `a3` blob,
31 PRIMARY KEY (`a1`)
32 ) /*!50100 TABLESPACE `ts1` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
33 SHOW CREATE TABLE test.t2;
34 Table Create Table
35 t2 CREATE TABLE `t2` (
36 `a1` int NOT NULL,
37 `a2` varchar(256) DEFAULT NULL,
38 `a3` blob,
39 PRIMARY KEY (`a1`)
40 ) /*!50100 TABLESPACE `ts2` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
41 INSERT INTO test.t1 VALUES (1,'111111','aaaaaaaa');
42 INSERT INTO test.t1 VALUES (2,'222222','bbbbbbbb');
43 SELECT * FROM test.t1 ORDER BY a1;
44 a1 a2 a3
45 1 111111 aaaaaaaa
46 2 222222 bbbbbbbb
47 INSERT INTO test.t2(a1,a2,a3) SELECT * FROM test.t1;
48 SELECT * FROM test.t2 ORDER BY a1;
49 a1 a2 a3
50 1 111111 aaaaaaaa
51 2 222222 bbbbbbbb
52 DROP TABLE test.t1, test.t2;
53 set @vc1 = repeat('a', 200);
54 set @vc2 = repeat('b', 500);
55 set @vc3 = repeat('c', 1000);
56 set @vc4 = repeat('d', 4000);
57 set @x0 = '01234567012345670123456701234567';
58 set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0);
59 set @b1 = 'b1';
60 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
61 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
62 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
63 set @b1 = concat(@b1,@x0);
64 set @d1 = 'dd1';
65 set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
66 set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
67 set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
68 set @b2 = 'b2';
69 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
70 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
71 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
72 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
73 set @d2 = 'dd2';
74 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
75 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
76 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
77 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
78 select length(@x0),length(@b1),length(@d1) from dual;
79 length(@x0) length(@b1) length(@d1)
80 256 2256 3000
81 select length(@x0),length(@b2),length(@d2) from dual;
82 length(@x0) length(@b2) length(@d2)
83 256 20000 30000
84 CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(5000), a3 BLOB)
85 TABLESPACE ts1 STORAGE DISK ENGINE=NDB CHARACTER SET latin1;
86 CREATE TABLE test.t2 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(5000), a3 BLOB)
87 TABLESPACE ts2 STORAGE DISK ENGINE=NDB CHARACTER SET latin1;
88 SHOW CREATE TABLE test.t1;
89 Table Create Table
90 t1 CREATE TABLE `t1` (
91 `a1` int NOT NULL,
92 `a2` varchar(5000) DEFAULT NULL,
93 `a3` blob,
94 PRIMARY KEY (`a1`)
95 ) /*!50100 TABLESPACE `ts1` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
96 SHOW CREATE TABLE test.t2;
97 Table Create Table
98 t2 CREATE TABLE `t2` (
99 `a1` int NOT NULL,
100 `a2` varchar(5000) DEFAULT NULL,
101 `a3` blob,
102 PRIMARY KEY (`a1`)
103 ) /*!50100 TABLESPACE `ts2` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
104 INSERT INTO test.t1 VALUES (1,@vc1,@d1);
105 INSERT INTO test.t1 VALUES (2,@vc2,@b1);
106 INSERT INTO test.t1 VALUES (3,@vc3,@d2);
107 INSERT INTO test.t1 VALUES (4,@vc4,@b2);
108 SELECT a1,length(a2),substr(a2,180,2),length(a3),substr(a3,1+3*900,3)
109 FROM test.t1 WHERE a1=1;
110 a1 length(a2) substr(a2,180,2) length(a3) substr(a3,1+3*900,3)
111 1 200 aa 3000 dd1
112 SELECT a1,length(a2),substr(a2,480,2),length(a3),substr(a3,1+2*900,3)
113 FROM test.t1 where a1=2;
114 a1 length(a2) substr(a2,480,2) length(a3) substr(a3,1+2*900,3)
115 2 500 bb 2256 b1b
116 INSERT INTO test.t2(a1,a2,a3) SELECT * FROM test.t1;
117 SELECT a1,length(a2),substr(a2,180,2),length(a3),substr(a3,1+3*900,3)
118 FROM test.t2 WHERE a1=1;
119 a1 length(a2) substr(a2,180,2) length(a3) substr(a3,1+3*900,3)
120 1 200 aa 3000 dd1
121 SELECT a1,length(a2),substr(a2,480,2),length(a3),substr(a3,1+2*900,3)
122 FROM test.t2 where a1=2;
123 a1 length(a2) substr(a2,480,2) length(a3) substr(a3,1+2*900,3)
124 2 500 bb 2256 b1b
125 DROP TABLE test.t1, test.t2;
126 *****
127 **** Insert, Update, Delete from NDB table with BLOB fields
128 *****
129 set @vc1 = repeat('a', 200);
130 set @vc2 = repeat('b', 500);
131 set @vc3 = repeat('c', 1000);
132 set @vc4 = repeat('d', 4000);
133 set @vc5 = repeat('d', 5000);
134 set @bb1 = repeat('1', 2000);
135 set @bb2 = repeat('2', 5000);
136 set @bb3 = repeat('3', 10000);
137 set @bb4 = repeat('4', 40000);
138 set @bb5 = repeat('5', 50000);
139 select length(@vc1),length(@vc2),length(@vc3),length(@vc4),length(@vc5)
140 from dual;
141 length(@vc1) length(@vc2) length(@vc3) length(@vc4) length(@vc5)
142 200 500 1000 4000 5000
143 select length(@bb1),length(@bb2),length(@bb3),length(@bb4),length(@bb5)
144 from dual;
145 length(@bb1) length(@bb2) length(@bb3) length(@bb4) length(@bb5)
146 2000 5000 10000 40000 50000
147 CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(5000), a3 BLOB)
148 TABLESPACE ts1 STORAGE DISK CHARACTER SET latin1 ENGINE=NDB;
149 INSERT INTO test.t1 VALUES (1,@vc1,@bb1);
150 INSERT INTO test.t1 VALUES (2,@vc2,@bb2);
151 INSERT INTO test.t1 VALUES (3,@vc3,@bb3);
152 INSERT INTO test.t1 VALUES (4,@vc4,@bb4);
153 INSERT INTO test.t1 VALUES (5,@vc5,@bb5);
154 UPDATE test.t1 SET a2=@vc5, a3=@bb5 WHERE a1=1;
155 SELECT a1,length(a2),substr(a2,4998,2),length(a3),substr(a3,49997,3)
156 FROM test.t1 WHERE a1=1;
157 a1 length(a2) substr(a2,4998,2) length(a3) substr(a3,49997,3)
158 1 5000 dd 50000 555
159 UPDATE test.t1 SET a2=@vc4, a3=@bb4 WHERE a1=2;
160 SELECT a1,length(a2),substr(a2,3998,2),length(a3),substr(a3,39997,3)
161 FROM test.t1 WHERE a1=2;
162 a1 length(a2) substr(a2,3998,2) length(a3) substr(a3,39997,3)
163 2 4000 dd 40000 444
164 UPDATE test.t1 SET a2=@vc2, a3=@bb2 WHERE a1=3;
165 SELECT a1,length(a2),substr(a2,498,2),length(a3),substr(a3,3997,3)
166 FROM test.t1 WHERE a1=3;
167 a1 length(a2) substr(a2,498,2) length(a3) substr(a3,3997,3)
168 3 500 bb 5000 222
169 UPDATE test.t1 SET a2=@vc3, a3=@bb3 WHERE a1=4;
170 SELECT a1,length(a2),substr(a2,998,2),length(a3),substr(a3,9997,3)
171 FROM test.t1 WHERE a1=4;
172 a1 length(a2) substr(a2,998,2) length(a3) substr(a3,9997,3)
173 4 1000 cc 10000 333
174 UPDATE test.t1 SET a2=@vc1, a3=@bb1 WHERE a1=5;
175 SELECT a1,length(a2),substr(a2,198,2),length(a3),substr(a3,1997,3)
176 FROM test.t1 WHERE a1=5;
177 a1 length(a2) substr(a2,198,2) length(a3) substr(a3,1997,3)
178 5 200 aa 2000 111
179 DELETE FROM test.t1 where a1=5;
180 SELECT count(*) from test.t1;
181 count(*)
182 4
183 DELETE FROM test.t1 where a1=4;
184 SELECT count(*) from test.t1;
185 count(*)
186 3
187 DELETE FROM test.t1 where a1=3;
188 SELECT count(*) from test.t1;
189 count(*)
190 2
191 DELETE FROM test.t1 where a1=2;
192 SELECT count(*) from test.t1;
193 count(*)
194 1
195 DELETE FROM test.t1 where a1=1;
196 SELECT count(*) from test.t1;
197 count(*)
198 0
199 DROP TABLE test.t1;
200 *****
201 **** Create Stored procedures that use disk based tables
202 *****
203 CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB)
204 TABLESPACE ts1 STORAGE DISK ENGINE=NDB//
205 CREATE PROCEDURE test.sp1()
206 BEGIN
207 INSERT INTO test.t1 values (1,'111111','aaaaaaaa');
208 END//
209 CALL test.sp1();
210 SELECT * FROM test.t1;
211 a1 a2 a3
212 1 111111 aaaaaaaa
213 CREATE PROCEDURE test.sp2(n INT, vc VARCHAR(256), blb BLOB)
214 BEGIN
215 UPDATE test.t1 SET a2=vc, a3=blb where a1=n;
216 END//
217 CALL test.sp2(1,'222222','bbbbbbbb');
218 SELECT * FROM test.t1;
219 a1 a2 a3
220 1 222222 bbbbbbbb
221 DELETE FROM test.t1;
222 DROP PROCEDURE test.sp1;
223 DROP PROCEDURE test.sp2;
224 DROP TABLE test.t1;
225 *****
226 ***** Create function that operate on disk based tables
227 *****
228 CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB)
229 TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
230 CREATE FUNCTION test.fn1(n INT) RETURNS INT
231 BEGIN
232 DECLARE v INT;
233 SELECT a1 INTO v FROM test.t1 WHERE a1=n;
234 RETURN v;
235 END//
236 CREATE FUNCTION test.fn2(n INT, blb BLOB) RETURNS BLOB
237 BEGIN
238 DECLARE vv BLOB;
239 UPDATE test.t1 SET a3=blb where a1=n;
240 SELECT a3 INTO vv FROM test.t1 WHERE a1=n;
241 RETURN vv;
242 END//
243 SELECT test.fn1(10) FROM DUAL;
244 test.fn1(10)
245 10
246 SELECT test.fn2(50, 'new BLOB content') FROM DUAL;
247 test.fn2(50, 'new BLOB content')
248 new BLOB content
249 DELETE FROM test.t1;
250 DROP FUNCTION test.fn1;
251 DROP FUNCTION test.fn2;
252 DROP TABLE test.t1;
253 *****
254 ***** Create triggers that operate on disk based tables
255 *****
256 CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB)
257 TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
258 CREATE TRIGGER test.trg1 BEFORE INSERT ON test.t1 FOR EACH ROW
259 BEGIN
260 if isnull(new.a2) then
261 set new.a2:= 'trg1 works on a2 field';
262 end if;
263 if isnull(new.a3) then
264 set new.a3:= 'trg1 works on a3 field';
265 end if;
266 end//
267 insert into test.t1 (a1) values (1)//
268 insert into test.t1 (a1,a2) values (2, 'ccccccc')//
269 select * from test.t1 order by a1//
270 a1 a2 a3
271 1 trg1 works on a2 field trg1 works on a3 field
272 2 ccccccc trg1 works on a3 field
273 DELETE FROM test.t1;
274 DROP TRIGGER test.trg1;
275 DROP TABLE test.t1;
276 *****
277 ***** Create, update views that operate on disk based tables
278 *****
279 CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB)
280 TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
281 CREATE VIEW test.v1 AS SELECT * FROM test.t1;
282 SELECT * FROM test.v1 order by a1;
283 a1 a2 a3
284 1 aaaaa1 bbbbb1
285 2 aaaaa2 bbbbb2
286 3 aaaaa3 bbbbb3
287 4 aaaaa4 bbbbb4
288 5 aaaaa5 bbbbb5
289 6 aaaaa6 bbbbb6
290 7 aaaaa7 bbbbb7
291 8 aaaaa8 bbbbb8
292 9 aaaaa9 bbbbb9
293 10 aaaaa10 bbbbb10
294 CHECK TABLE test.v1, test.t1;
295 Table Op Msg_type Msg_text
296 test.v1 check status OK
297 test.t1 check note The storage engine for the table doesn't support check
298 UPDATE test.v1 SET a2='zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz' WHERE a1=5;
299 SELECT * FROM test.v1 order by a1;
300 a1 a2 a3
301 1 aaaaa1 bbbbb1
302 2 aaaaa2 bbbbb2
303 3 aaaaa3 bbbbb3
304 4 aaaaa4 bbbbb4
305 5 zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz bbbbb5
306 6 aaaaa6 bbbbb6
307 7 aaaaa7 bbbbb7
308 8 aaaaa8 bbbbb8
309 9 aaaaa9 bbbbb9
310 10 aaaaa10 bbbbb10
311 DROP VIEW test.v1;
312 DELETE FROM test.t1;
313 DROP TABLE test.t1;
314 *****
315 ***** Create and use disk based table that use auto inc
316 *****
317 CREATE TABLE test.t1 (a1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
318 a2 VARCHAR(256), a3 BLOB)
319 TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
320 SELECT * FROM test.t1 ORDER BY a1;
321 a1 a2 a3
322 1 aaaaa10 bbbbb10
323 2 aaaaa9 bbbbb9
324 3 aaaaa8 bbbbb8
325 4 aaaaa7 bbbbb7
326 5 aaaaa6 bbbbb6
327 6 aaaaa5 bbbbb5
328 7 aaaaa4 bbbbb4
329 8 aaaaa3 bbbbb3
330 9 aaaaa2 bbbbb2
331 10 aaaaa1 bbbbb1
332 DELETE FROM test.t1;
333 DROP TABLE test.t1;
334 *****
335 ***** Create test that use transaction (commit, rollback)
336 *****
337 SET AUTOCOMMIT=0;
338 CREATE TABLE test.t1 (a1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
339 a2 VARCHAR(256), a3 BLOB)
340 TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
341 INSERT INTO test.t1 VALUES(NULL, "aaaaa1", "bbbbb1");
342 COMMIT;
343 SELECT * FROM test.t1 ORDER BY a1;
344 a1 a2 a3
345 1 aaaaa1 bbbbb1
346 INSERT INTO test.t1 VALUES(NULL, "aaaaa2", "bbbbb2");
347 ROLLBACK;
348 SELECT * FROM test.t1 ORDER BY a1;
349 a1 a2 a3
350 1 aaaaa1 bbbbb1
351 DELETE FROM test.t1;
352 DROP TABLE test.t1;
353 SET AUTOCOMMIT=1;
354 CREATE TABLE test.t1 (a1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
355 a2 VARCHAR(256), a3 BLOB)
356 TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
357 START TRANSACTION;
358 INSERT INTO test.t1 VALUES(NULL, "aaaaa1", "bbbbb1");
359 COMMIT;
360 SELECT * FROM test.t1 ORDER BY a1;
361 a1 a2 a3
362 1 aaaaa1 bbbbb1
363 START TRANSACTION;
364 INSERT INTO test.t1 VALUES(NULL, "aaaaa2", "bbbbb2");
365 ROLLBACK;
366 SELECT * FROM test.t1 ORDER BY a1;
367 a1 a2 a3
368 1 aaaaa1 bbbbb1
369 DELETE FROM test.t1;
370 DROP TABLE test.t1;
371 *****
372 ***** Create test that uses locks
373 *****
374 drop table if exists test.t1;
375 CREATE TABLE test.t1 (a1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
376 a2 VARCHAR(256), a3 BLOB)
377 TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
378 LOCK TABLES test.t1 write;
379 INSERT INTO test.t1 VALUES(NULL, "aaaaa1", "bbbbb1");
380 INSERT INTO test.t1 VALUES(NULL, "aaaaa2", "bbbbb2");
381 SELECT * FROM test.t1 ORDER BY a1;
382 a1 a2 a3
383 1 aaaaa1 bbbbb1
384 2 aaaaa2 bbbbb2
385 SELECT * FROM test.t1 ORDER BY a1;
386 a1 a2 a3
387 1 aaaaa1 bbbbb1
388 2 aaaaa2 bbbbb2
389 INSERT INTO test.t1 VALUES(NULL, "aaaaa3", "bbbbb3");
390 UNLOCK TABLES;
391 INSERT INTO test.t1 VALUES(NULL, "aaaaa3", "bbbbb3");
392 SELECT * FROM test.t1 ORDER BY a1;
393 a1 a2 a3
394 1 aaaaa1 bbbbb1
395 2 aaaaa2 bbbbb2
396 3 aaaaa3 bbbbb3
397 4 aaaaa3 bbbbb3
398 DELETE FROM test.t1;
399 DROP TABLE test.t1;
400 *****
401 ***** Create large disk base table, do random queries, check cache hits
402 *****
403 set @vc1 = repeat('a', 200);
404 SELECT @vc1 FROM DUAL;
405 @vc1
406 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
407 set @vc2 = repeat('b', 500);
408 set @vc3 = repeat('b', 998);
409 set @x0 = '01234567012345670123456701234567';
410 set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0);
411 set @b1 = 'b1';
412 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
413 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
414 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
415 set @b1 = concat(@b1,@x0);
416 set @d1 = 'dd1';
417 set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
418 set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
419 set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
420 set @b2 = 'b2';
421 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
422 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
423 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
424 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
425 set @d2 = 'dd2';
426 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
427 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
428 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
429 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
430 select length(@x0),length(@b1),length(@d1) from dual;
431 length(@x0) length(@b1) length(@d1)
432 256 2256 3000
433 select length(@x0),length(@b2),length(@d2) from dual;
434 length(@x0) length(@b2) length(@d2)
435 256 20000 30000
436 CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(1000), a3 BLOB)
437 TABLESPACE ts1 STORAGE DISK ENGINE=NDB
438 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
439 INSERT INTO test.t1 values(1,@vc1,@d1);
440 INSERT INTO test.t1 values(2,@vc2,@d2);
441 explain SELECT * from test.t1 WHERE a1 = 1;
442 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
443 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 const # # NULL
444 Warnings:
445 Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`a3` AS `a3` from `test`.`t1` where (`test`.`t1`.`a1` = 1)
446 SELECT a1,length(a2),substr(a2,1+2*900,2),length(a3),substr(a3,1+3*900,3)
447 FROM test.t1 WHERE a1=1 ORDER BY a1;
448 a1 length(a2) substr(a2,1+2*900,2) length(a3) substr(a3,1+3*900,3)
449 1 200 3000 dd1
450 SELECT a1,length(a2),substr(a2,1+2*9000,2),length(a3),substr(a3,1+3*9000,3)
451 FROM test.t1 where a1=2 ORDER BY a1;
452 a1 length(a2) substr(a2,1+2*9000,2) length(a3) substr(a3,1+3*9000,3)
453 2 500 30000 dd2
454 UPDATE test.t1 set a2=@vc2,a3=@d2 where a1=1;
455 UPDATE test.t1 set a2=@vc1,a3=@d1 where a1=2;
456 SELECT a1,length(a2),substr(a2,1+2*9000,2),length(a3),substr(a3,1+3*9000,3)
457 FROM test.t1 where a1=1;
458 a1 length(a2) substr(a2,1+2*9000,2) length(a3) substr(a3,1+3*9000,3)
459 1 500 30000 dd2
460 SELECT a1,length(a2),substr(a2,1+2*900,2),length(a3),substr(a3,1+3*900,3)
461 FROM test.t1 where a1=2;
462 a1 length(a2) substr(a2,1+2*900,2) length(a3) substr(a3,1+3*900,3)
463 2 200 3000 dd1
464 DELETE FROM test.t1;
465 DROP TABLE test.t1;
466 *****
467 ***** Create test that uses COUNT(), SUM(), MAX(), MIN(), NOW(),
468 ***** USER(), TRUNCATE
469 *****
470 CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256),
471 a3 BLOB, a4 DATE, a5 CHAR(250))
472 TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
473 SELECT COUNT(*) from test.t1;
474 COUNT(*)
475 100
476 SELECT SUM(a1) from test.t1;
477 SUM(a1)
478 5050
479 SELECT MIN(a1) from test.t1;
480 MIN(a1)
481 1
482 SELECT MAX(a1) from test.t1;
483 MAX(a1)
484 100
485 SELECT a5 from test.t1 where a1=50;
486 a5
487 root@localhost
488 SELECT * from test.t1 order by a1;
489 a1 a2 a3 a4 a5
490 1 aaaaaaaaaaaaaaaa1 bbbbbbbbbbbbbbbbbb1 2006-06-20 root@localhost
491 2 aaaaaaaaaaaaaaaa2 bbbbbbbbbbbbbbbbbb2 2006-06-20 root@localhost
492 3 aaaaaaaaaaaaaaaa3 bbbbbbbbbbbbbbbbbb3 2006-06-20 root@localhost
493 4 aaaaaaaaaaaaaaaa4 bbbbbbbbbbbbbbbbbb4 2006-06-20 root@localhost
494 5 aaaaaaaaaaaaaaaa5 bbbbbbbbbbbbbbbbbb5 2006-06-20 root@localhost
495 6 aaaaaaaaaaaaaaaa6 bbbbbbbbbbbbbbbbbb6 2006-06-20 root@localhost
496 7 aaaaaaaaaaaaaaaa7 bbbbbbbbbbbbbbbbbb7 2006-06-20 root@localhost
497 8 aaaaaaaaaaaaaaaa8 bbbbbbbbbbbbbbbbbb8 2006-06-20 root@localhost
498 9 aaaaaaaaaaaaaaaa9 bbbbbbbbbbbbbbbbbb9 2006-06-20 root@localhost
499 10 aaaaaaaaaaaaaaaa10 bbbbbbbbbbbbbbbbbb10 2006-06-20 root@localhost
500 11 aaaaaaaaaaaaaaaa11 bbbbbbbbbbbbbbbbbb11 2006-06-20 root@localhost
501 12 aaaaaaaaaaaaaaaa12 bbbbbbbbbbbbbbbbbb12 2006-06-20 root@localhost
502 13 aaaaaaaaaaaaaaaa13 bbbbbbbbbbbbbbbbbb13 2006-06-20 root@localhost
503 14 aaaaaaaaaaaaaaaa14 bbbbbbbbbbbbbbbbbb14 2006-06-20 root@localhost
504 15 aaaaaaaaaaaaaaaa15 bbbbbbbbbbbbbbbbbb15 2006-06-20 root@localhost
505 16 aaaaaaaaaaaaaaaa16 bbbbbbbbbbbbbbbbbb16 2006-06-20 root@localhost
506 17 aaaaaaaaaaaaaaaa17 bbbbbbbbbbbbbbbbbb17 2006-06-20 root@localhost
507 18 aaaaaaaaaaaaaaaa18 bbbbbbbbbbbbbbbbbb18 2006-06-20 root@localhost
508 19 aaaaaaaaaaaaaaaa19 bbbbbbbbbbbbbbbbbb19 2006-06-20 root@localhost
509 20 aaaaaaaaaaaaaaaa20 bbbbbbbbbbbbbbbbbb20 2006-06-20 root@localhost
510 21 aaaaaaaaaaaaaaaa21 bbbbbbbbbbbbbbbbbb21 2006-06-20 root@localhost
511 22 aaaaaaaaaaaaaaaa22 bbbbbbbbbbbbbbbbbb22 2006-06-20 root@localhost
512 23 aaaaaaaaaaaaaaaa23 bbbbbbbbbbbbbbbbbb23 2006-06-20 root@localhost
513 24 aaaaaaaaaaaaaaaa24 bbbbbbbbbbbbbbbbbb24 2006-06-20 root@localhost
514 25 aaaaaaaaaaaaaaaa25 bbbbbbbbbbbbbbbbbb25 2006-06-20 root@localhost
515 26 aaaaaaaaaaaaaaaa26 bbbbbbbbbbbbbbbbbb26 2006-06-20 root@localhost
516 27 aaaaaaaaaaaaaaaa27 bbbbbbbbbbbbbbbbbb27 2006-06-20 root@localhost
517 28 aaaaaaaaaaaaaaaa28 bbbbbbbbbbbbbbbbbb28 2006-06-20 root@localhost
518 29 aaaaaaaaaaaaaaaa29 bbbbbbbbbbbbbbbbbb29 2006-06-20 root@localhost
519 30 aaaaaaaaaaaaaaaa30 bbbbbbbbbbbbbbbbbb30 2006-06-20 root@localhost
520 31 aaaaaaaaaaaaaaaa31 bbbbbbbbbbbbbbbbbb31 2006-06-20 root@localhost
521 32 aaaaaaaaaaaaaaaa32 bbbbbbbbbbbbbbbbbb32 2006-06-20 root@localhost
522 33 aaaaaaaaaaaaaaaa33 bbbbbbbbbbbbbbbbbb33 2006-06-20 root@localhost
523 34 aaaaaaaaaaaaaaaa34 bbbbbbbbbbbbbbbbbb34 2006-06-20 root@localhost
524 35 aaaaaaaaaaaaaaaa35 bbbbbbbbbbbbbbbbbb35 2006-06-20 root@localhost
525 36 aaaaaaaaaaaaaaaa36 bbbbbbbbbbbbbbbbbb36 2006-06-20 root@localhost
526 37 aaaaaaaaaaaaaaaa37 bbbbbbbbbbbbbbbbbb37 2006-06-20 root@localhost
527 38 aaaaaaaaaaaaaaaa38 bbbbbbbbbbbbbbbbbb38 2006-06-20 root@localhost
528 39 aaaaaaaaaaaaaaaa39 bbbbbbbbbbbbbbbbbb39 2006-06-20 root@localhost
529 40 aaaaaaaaaaaaaaaa40 bbbbbbbbbbbbbbbbbb40 2006-06-20 root@localhost
530 41 aaaaaaaaaaaaaaaa41 bbbbbbbbbbbbbbbbbb41 2006-06-20 root@localhost
531 42 aaaaaaaaaaaaaaaa42 bbbbbbbbbbbbbbbbbb42 2006-06-20 root@localhost
532 43 aaaaaaaaaaaaaaaa43 bbbbbbbbbbbbbbbbbb43 2006-06-20 root@localhost
533 44 aaaaaaaaaaaaaaaa44 bbbbbbbbbbbbbbbbbb44 2006-06-20 root@localhost
534 45 aaaaaaaaaaaaaaaa45 bbbbbbbbbbbbbbbbbb45 2006-06-20 root@localhost
535 46 aaaaaaaaaaaaaaaa46 bbbbbbbbbbbbbbbbbb46 2006-06-20 root@localhost
536 47 aaaaaaaaaaaaaaaa47 bbbbbbbbbbbbbbbbbb47 2006-06-20 root@localhost
537 48 aaaaaaaaaaaaaaaa48 bbbbbbbbbbbbbbbbbb48 2006-06-20 root@localhost
538 49 aaaaaaaaaaaaaaaa49 bbbbbbbbbbbbbbbbbb49 2006-06-20 root@localhost
539 50 aaaaaaaaaaaaaaaa50 bbbbbbbbbbbbbbbbbb50 2006-06-20 root@localhost
540 51 aaaaaaaaaaaaaaaa51 bbbbbbbbbbbbbbbbbb51 2006-06-20 root@localhost
541 52 aaaaaaaaaaaaaaaa52 bbbbbbbbbbbbbbbbbb52 2006-06-20 root@localhost
542 53 aaaaaaaaaaaaaaaa53 bbbbbbbbbbbbbbbbbb53 2006-06-20 root@localhost
543 54 aaaaaaaaaaaaaaaa54 bbbbbbbbbbbbbbbbbb54 2006-06-20 root@localhost
544 55 aaaaaaaaaaaaaaaa55 bbbbbbbbbbbbbbbbbb55 2006-06-20 root@localhost
545 56 aaaaaaaaaaaaaaaa56 bbbbbbbbbbbbbbbbbb56 2006-06-20 root@localhost
546 57 aaaaaaaaaaaaaaaa57 bbbbbbbbbbbbbbbbbb57 2006-06-20 root@localhost
547 58 aaaaaaaaaaaaaaaa58 bbbbbbbbbbbbbbbbbb58 2006-06-20 root@localhost
548 59 aaaaaaaaaaaaaaaa59 bbbbbbbbbbbbbbbbbb59 2006-06-20 root@localhost
549 60 aaaaaaaaaaaaaaaa60 bbbbbbbbbbbbbbbbbb60 2006-06-20 root@localhost
550 61 aaaaaaaaaaaaaaaa61 bbbbbbbbbbbbbbbbbb61 2006-06-20 root@localhost
551 62 aaaaaaaaaaaaaaaa62 bbbbbbbbbbbbbbbbbb62 2006-06-20 root@localhost
552 63 aaaaaaaaaaaaaaaa63 bbbbbbbbbbbbbbbbbb63 2006-06-20 root@localhost
553 64 aaaaaaaaaaaaaaaa64 bbbbbbbbbbbbbbbbbb64 2006-06-20 root@localhost
554 65 aaaaaaaaaaaaaaaa65 bbbbbbbbbbbbbbbbbb65 2006-06-20 root@localhost
555 66 aaaaaaaaaaaaaaaa66 bbbbbbbbbbbbbbbbbb66 2006-06-20 root@localhost
556 67 aaaaaaaaaaaaaaaa67 bbbbbbbbbbbbbbbbbb67 2006-06-20 root@localhost
557 68 aaaaaaaaaaaaaaaa68 bbbbbbbbbbbbbbbbbb68 2006-06-20 root@localhost
558 69 aaaaaaaaaaaaaaaa69 bbbbbbbbbbbbbbbbbb69 2006-06-20 root@localhost
559 70 aaaaaaaaaaaaaaaa70 bbbbbbbbbbbbbbbbbb70 2006-06-20 root@localhost
560 71 aaaaaaaaaaaaaaaa71 bbbbbbbbbbbbbbbbbb71 2006-06-20 root@localhost
561 72 aaaaaaaaaaaaaaaa72 bbbbbbbbbbbbbbbbbb72 2006-06-20 root@localhost
562 73 aaaaaaaaaaaaaaaa73 bbbbbbbbbbbbbbbbbb73 2006-06-20 root@localhost
563 74 aaaaaaaaaaaaaaaa74 bbbbbbbbbbbbbbbbbb74 2006-06-20 root@localhost
564 75 aaaaaaaaaaaaaaaa75 bbbbbbbbbbbbbbbbbb75 2006-06-20 root@localhost
565 76 aaaaaaaaaaaaaaaa76 bbbbbbbbbbbbbbbbbb76 2006-06-20 root@localhost
566 77 aaaaaaaaaaaaaaaa77 bbbbbbbbbbbbbbbbbb77 2006-06-20 root@localhost
567 78 aaaaaaaaaaaaaaaa78 bbbbbbbbbbbbbbbbbb78 2006-06-20 root@localhost
568 79 aaaaaaaaaaaaaaaa79 bbbbbbbbbbbbbbbbbb79 2006-06-20 root@localhost
569 80 aaaaaaaaaaaaaaaa80 bbbbbbbbbbbbbbbbbb80 2006-06-20 root@localhost
570 81 aaaaaaaaaaaaaaaa81 bbbbbbbbbbbbbbbbbb81 2006-06-20 root@localhost
571 82 aaaaaaaaaaaaaaaa82 bbbbbbbbbbbbbbbbbb82 2006-06-20 root@localhost
572 83 aaaaaaaaaaaaaaaa83 bbbbbbbbbbbbbbbbbb83 2006-06-20 root@localhost
573 84 aaaaaaaaaaaaaaaa84 bbbbbbbbbbbbbbbbbb84 2006-06-20 root@localhost
574 85 aaaaaaaaaaaaaaaa85 bbbbbbbbbbbbbbbbbb85 2006-06-20 root@localhost
575 86 aaaaaaaaaaaaaaaa86 bbbbbbbbbbbbbbbbbb86 2006-06-20 root@localhost
576 87 aaaaaaaaaaaaaaaa87 bbbbbbbbbbbbbbbbbb87 2006-06-20 root@localhost
577 88 aaaaaaaaaaaaaaaa88 bbbbbbbbbbbbbbbbbb88 2006-06-20 root@localhost
578 89 aaaaaaaaaaaaaaaa89 bbbbbbbbbbbbbbbbbb89 2006-06-20 root@localhost
579 90 aaaaaaaaaaaaaaaa90 bbbbbbbbbbbbbbbbbb90 2006-06-20 root@localhost
580 91 aaaaaaaaaaaaaaaa91 bbbbbbbbbbbbbbbbbb91 2006-06-20 root@localhost
581 92 aaaaaaaaaaaaaaaa92 bbbbbbbbbbbbbbbbbb92 2006-06-20 root@localhost
582 93 aaaaaaaaaaaaaaaa93 bbbbbbbbbbbbbbbbbb93 2006-06-20 root@localhost
583 94 aaaaaaaaaaaaaaaa94 bbbbbbbbbbbbbbbbbb94 2006-06-20 root@localhost
584 95 aaaaaaaaaaaaaaaa95 bbbbbbbbbbbbbbbbbb95 2006-06-20 root@localhost
585 96 aaaaaaaaaaaaaaaa96 bbbbbbbbbbbbbbbbbb96 2006-06-20 root@localhost
586 97 aaaaaaaaaaaaaaaa97 bbbbbbbbbbbbbbbbbb97 2006-06-20 root@localhost
587 98 aaaaaaaaaaaaaaaa98 bbbbbbbbbbbbbbbbbb98 2006-06-20 root@localhost
588 99 aaaaaaaaaaaaaaaa99 bbbbbbbbbbbbbbbbbb99 2006-06-20 root@localhost
589 100 aaaaaaaaaaaaaaaa100 bbbbbbbbbbbbbbbbbb100 2006-06-20 root@localhost
590 DROP TABLE test.t1;
591 ALTER TABLESPACE ts1
592 DROP DATAFILE './table_space1/datafile.dat';
593 DROP TABLESPACE ts1;
594 ALTER TABLESPACE ts2
595 DROP DATAFILE './table_space2/datafile.dat';
596 DROP TABLESPACE ts2;
597 DROP LOGFILE GROUP lg
598 ENGINE=NDB;