Debian Codegraph @

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

raw
1 **** Test Setup Section ****
2 CREATE LOGFILE GROUP log_group1
3 ADD UNDOFILE './log_group1/undofile.dat'
4 INITIAL_SIZE 16M
5 UNDO_BUFFER_SIZE = 1M
6 ENGINE=NDB;
7 CREATE TABLESPACE table_space1
8 ADD DATAFILE './table_space1/datafile.dat'
9 USE LOGFILE GROUP log_group1
10 INITIAL_SIZE 28M
11 EXTENT_SIZE 256K
12 ENGINE NDB;
13 CREATE TABLE test.t1
14 (pk1 INT NOT NULL PRIMARY KEY, b INT NOT NULL, c INT NOT NULL)
15 TABLESPACE table_space1 STORAGE DISK
16 ENGINE=NDB;
17 CREATE TABLE test.t2
18 (pk2 INT NOT NULL PRIMARY KEY, b2 INT NOT NULL, c2 INT NOT NULL)
19 ENGINE=NDB;
20
21 **** Data load for first test ****
22 INSERT INTO test.t1 VALUES
23 (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
24 (6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),
25 (11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15),
26 (16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),
27 (21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),
28 (26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30),
29 (31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35),
30 (36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40),
31 (41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45),
32 (46,46,46),(47,47,47),(48,48,48),(49,49,49),(50,50,50),
33 (51,51,51),(52,52,52),(53,53,53),(54,54,54),(55,55,55),
34 (56,56,56),(57,57,57),(58,58,58),(59,59,59),(60,60,60),
35 (61,61,61),(62,62,62),(63,63,63),(64,64,64),(65,65,65),
36 (66,66,66),(67,67,67),(68,68,68),(69,69,69),(70,70,70),
37 (71,71,71),(72,72,72),(73,73,73),(74,74,74),(75,75,75);
38 INSERT INTO test.t2 VALUES
39 (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
40 (6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),
41 (11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15),
42 (16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),
43 (21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),
44 (26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30),
45 (31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35),
46 (36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40),
47 (41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45),
48 (46,46,46),(47,47,47),(48,48,48),(49,49,49),(50,50,50),
49 (51,51,51),(52,52,52),(53,53,53),(54,54,54),(55,55,55),
50 (56,56,56),(57,57,57),(58,58,58),(59,59,59),(60,60,60),
51 (61,61,61),(62,62,62),(63,63,63),(64,64,64),(65,65,65),
52 (66,66,66),(67,67,67),(68,68,68),(69,69,69),(70,70,70),
53 (71,71,71),(72,72,72),(73,73,73),(74,74,74),(75,75,75);
54
55 *** Test 1 Section Begins ***
56 SELECT COUNT(*) FROM test.t2 LEFT JOIN test.t1 ON pk2=pk1 WHERE b2 IN (4);
57 COUNT(*)
58 1
59 SELECT * FROM test.t2 LEFT JOIN test.t1 ON pk2=pk1 WHERE b2 IN (4);
60 pk2 b2 c2 pk1 b c
61 4 4 4 4 4 4
62 SELECT COUNT(*) FROM test.t1 LEFT JOIN test.t2 ON pk1=pk2 WHERE b IN (4);
63 COUNT(*)
64 1
65 SELECT COUNT(*) FROM test.t1 LEFT JOIN test.t2 ON b=b2 WHERE pk1 IN (75);
66 COUNT(*)
67 1
68 SELECT b, c FROM test.t1 LEFT JOIN test.t2 ON pk1=pk2 ORDER BY b;
69 b c
70 1 1
71 2 2
72 3 3
73 4 4
74 5 5
75 6 6
76 7 7
77 8 8
78 9 9
79 10 10
80 11 11
81 12 12
82 13 13
83 14 14
84 15 15
85 16 16
86 17 17
87 18 18
88 19 19
89 20 20
90 21 21
91 22 22
92 23 23
93 24 24
94 25 25
95 26 26
96 27 27
97 28 28
98 29 29
99 30 30
100 31 31
101 32 32
102 33 33
103 34 34
104 35 35
105 36 36
106 37 37
107 38 38
108 39 39
109 40 40
110 41 41
111 42 42
112 43 43
113 44 44
114 45 45
115 46 46
116 47 47
117 48 48
118 49 49
119 50 50
120 51 51
121 52 52
122 53 53
123 54 54
124 55 55
125 56 56
126 57 57
127 58 58
128 59 59
129 60 60
130 61 61
131 62 62
132 63 63
133 64 64
134 65 65
135 66 66
136 67 67
137 68 68
138 69 69
139 70 70
140 71 71
141 72 72
142 73 73
143 74 74
144 75 75
145
146 *** Setup for test 2 ****
147 DELETE FROM test.t1;
148 INSERT INTO test.t1 VALUES
149 (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
150 (6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),
151 (11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15),
152 (16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),
153 (21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),
154 (26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30),
155 (31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35),
156 (36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40),
157 (41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45);
158
159 **** Test Section 2 ****
160 SELECT b, c FROM test.t1 LEFT JOIN test.t2 ON pk1=pk2 ORDER BY b;
161 b c
162 1 1
163 2 2
164 3 3
165 4 4
166 5 5
167 6 6
168 7 7
169 8 8
170 9 9
171 10 10
172 11 11
173 12 12
174 13 13
175 14 14
176 15 15
177 16 16
178 17 17
179 18 18
180 19 19
181 20 20
182 21 21
183 22 22
184 23 23
185 24 24
186 25 25
187 26 26
188 27 27
189 28 28
190 29 29
191 30 30
192 31 31
193 32 32
194 33 33
195 34 34
196 35 35
197 36 36
198 37 37
199 38 38
200 39 39
201 40 40
202 41 41
203 42 42
204 43 43
205 44 44
206 45 45
207 SELECT COUNT(*) FROM test.t1 LEFT JOIN test.t2 ON b=b2;
208 COUNT(*)
209 45
210 SELECT COUNT(*) FROM test.t1 RIGHT JOIN test.t2 ON b=b2;
211 COUNT(*)
212 75
213 SHOW CREATE TABLE test.t2;
214 Table Create Table
215 t2 CREATE TABLE `t2` (
216 `pk2` int NOT NULL,
217 `b2` int NOT NULL,
218 `c2` int NOT NULL,
219 PRIMARY KEY (`pk2`)
220 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
221 SHOW CREATE TABLE test.t1;
222 Table Create Table
223 t1 CREATE TABLE `t1` (
224 `pk1` int NOT NULL,
225 `b` int NOT NULL,
226 `c` int NOT NULL,
227 PRIMARY KEY (`pk1`)
228 ) /*!50100 TABLESPACE `table_space1` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
229 ALTER TABLE test.t2 TABLESPACE table_space1 STORAGE DISK
230 ENGINE=NDB;
231 SHOW CREATE TABLE test.t2;
232 Table Create Table
233 t2 CREATE TABLE `t2` (
234 `pk2` int NOT NULL,
235 `b2` int NOT NULL,
236 `c2` int NOT NULL,
237 PRIMARY KEY (`pk2`)
238 ) /*!50100 TABLESPACE `table_space1` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
239 ALTER TABLE test.t1 STORAGE MEMORY ENGINE=NDBCLUSTER;
240 SHOW CREATE TABLE test.t1;
241 Table Create Table
242 t1 CREATE TABLE `t1` (
243 `pk1` int NOT NULL,
244 `b` int NOT NULL,
245 `c` int NOT NULL,
246 PRIMARY KEY (`pk1`)
247 ) /*!50100 TABLESPACE `table_space1` STORAGE MEMORY */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
248
249 DROP TABLE test.t1;
250 DROP TABLE test.t2;
251 *** Setup for Test Section 3 ***
252 CREATE TABLE test.t1 (
253 usr_id INT unsigned NOT NULL,
254 uniq_id INT unsigned NOT NULL AUTO_INCREMENT,
255 start_num INT unsigned NOT NULL DEFAULT 1,
256 increment INT unsigned NOT NULL DEFAULT 1,
257 PRIMARY KEY (uniq_id),
258 INDEX usr_uniq_idx (usr_id, uniq_id),
259 INDEX uniq_usr_idx (uniq_id, usr_id))
260 TABLESPACE table_space1 STORAGE DISK
261 ENGINE=NDB;
262 CREATE TABLE test.t2 (
263 id INT unsigned NOT NULL DEFAULT 0,
264 usr2_id INT unsigned NOT NULL DEFAULT 0,
265 max INT unsigned NOT NULL DEFAULT 0,
266 c_amount INT unsigned NOT NULL DEFAULT 0,
267 d_max INT unsigned NOT NULL DEFAULT 0,
268 d_num INT unsigned NOT NULL DEFAULT 0,
269 orig_time INT unsigned NOT NULL DEFAULT 0,
270 c_time INT unsigned NOT NULL DEFAULT 0,
271 active ENUM ("no","yes") NOT NULL,
272 PRIMARY KEY (id,usr2_id),
273 INDEX id_idx (id),
274 INDEX usr2_idx (usr2_id))
275 ENGINE=NDB;
276 INSERT INTO test.t1 VALUES (3,NULL,0,50),(3,NULL,0,200),(3,NULL,0,25),(3,NULL,0,84676),(3,NULL,0,235),(3,NULL,0,10),(3,NULL,0,3098),(3,NULL,0,2947),(3,NULL,0,8987),(3,NULL,0,8347654),(3,NULL,0,20398),(3,NULL,0,8976),(3,NULL,0,500),(3,NULL,0,198);
277
278 **** Test Section 3 ****
279 SELECT test.t1.usr_id,test.t1.uniq_id,test.t1.increment,
280 test.t2.usr2_id,test.t2.c_amount,test.t2.max
281 FROM test.t1
282 LEFT JOIN test.t2 ON test.t2.id = test.t1.uniq_id
283 WHERE test.t1.uniq_id = 4
284 ORDER BY test.t2.c_amount;
285 usr_id uniq_id increment usr2_id c_amount max
286 3 4 84676 NULL NULL NULL
287 INSERT INTO test.t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
288 INSERT INTO test.t2 VALUES (4,3,3000,6000,0,0,746584,837484,'yes');
289 INSERT INTO test.t2 VALUES (7,3,1000,2000,0,0,746294,937484,'yes');
290 SELECT test.t1.usr_id,test.t1.uniq_id,test.t1.increment,
291 test.t2.usr2_id,test.t2.c_amount,test.t2.max
292 FROM test.t1
293 LEFT JOIN test.t2 ON test.t2.id = test.t1.uniq_id
294 WHERE test.t1.uniq_id = 4
295 ORDER BY test.t2.c_amount;
296 usr_id uniq_id increment usr2_id c_amount max
297 3 4 84676 3 6000 3000
298
299 DROP TABLE test.t1;
300 DROP TABLE test.t2;
301 CREATE TABLE test.t1 (
302 a1 smallint NOT NULL,
303 a2 int NOT NULL,
304 a3 bigint NOT NULL,
305 a4 char(10),
306 a5 decimal(5,1),
307 a6 time,
308 a7 date,
309 a8 datetime,
310 a9 VARCHAR(255),
311 a10 blob,
312 PRIMARY KEY(a1)
313 ) TABLESPACE table_space1 STORAGE DISK ENGINE=NDB;
314 CREATE TABLE test.t2 (
315 b1 smallint NOT NULL,
316 b2 int NOT NULL,
317 b3 bigint NOT NULL,
318 b4 char(10),
319 b5 decimal(5,1),
320 b6 time,
321 b7 date,
322 b8 datetime,
323 b9 VARCHAR(255),
324 b10 blob,
325 PRIMARY KEY(b1)
326 ) ENGINE=NDB;
327 ALTER TABLE test.t1 ADD INDEX (a2), ADD INDEX (a3), ADD INDEX (a8);
328 ALTER TABLE test.t2 ADD INDEX (b2), ADD INDEX (b3), ADD INDEX (b8);
329 SHOW CREATE TABLE test.t1;
330 Table Create Table
331 t1 CREATE TABLE `t1` (
332 `a1` smallint NOT NULL,
333 `a2` int NOT NULL,
334 `a3` bigint NOT NULL,
335 `a4` char(10) DEFAULT NULL,
336 `a5` decimal(5,1) DEFAULT NULL,
337 `a6` time DEFAULT NULL,
338 `a7` date DEFAULT NULL,
339 `a8` datetime DEFAULT NULL,
340 `a9` varchar(255) DEFAULT NULL,
341 `a10` blob,
342 PRIMARY KEY (`a1`),
343 KEY `a2` (`a2`),
344 KEY `a3` (`a3`),
345 KEY `a8` (`a8`)
346 ) /*!50100 TABLESPACE `table_space1` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
347 SHOW CREATE TABLE test.t2;
348 Table Create Table
349 t2 CREATE TABLE `t2` (
350 `b1` smallint NOT NULL,
351 `b2` int NOT NULL,
352 `b3` bigint NOT NULL,
353 `b4` char(10) DEFAULT NULL,
354 `b5` decimal(5,1) DEFAULT NULL,
355 `b6` time DEFAULT NULL,
356 `b7` date DEFAULT NULL,
357 `b8` datetime DEFAULT NULL,
358 `b9` varchar(255) DEFAULT NULL,
359 `b10` blob,
360 PRIMARY KEY (`b1`),
361 KEY `b2` (`b2`),
362 KEY `b3` (`b3`),
363 KEY `b8` (`b8`)
364 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
365 SELECT * FROM test.t1 order by a1;
366 a1 a2 a3 a4 a5 a6 a7 a8 a9 a10
367 1 2 2000000001 aaa1 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb1 binary data
368 2 3 2000000002 aaa2 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb2 binary data
369 3 4 2000000003 aaa3 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb3 binary data
370 4 5 2000000004 aaa4 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb4 binary data
371 5 6 2000000005 aaa5 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb5 binary data
372 6 7 2000000006 aaa6 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb6 binary data
373 7 8 2000000007 aaa7 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb7 binary data
374 8 9 2000000008 aaa8 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb8 binary data
375 9 10 2000000009 aaa9 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb9 binary data
376 10 11 2000000010 aaa10 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb10 binary data
377 11 12 2000000011 aaa11 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb11 binary data
378 12 13 2000000012 aaa12 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb12 binary data
379 13 14 2000000013 aaa13 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb13 binary data
380 14 15 2000000014 aaa14 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb14 binary data
381 15 16 2000000015 aaa15 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb15 binary data
382 16 17 2000000016 aaa16 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb16 binary data
383 17 18 2000000017 aaa17 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb17 binary data
384 18 19 2000000018 aaa18 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb18 binary data
385 19 20 2000000019 aaa19 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb19 binary data
386 20 21 2000000020 aaa20 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb20 binary data
387 SELECT * FROM test.t2 order by b1;
388 b1 b2 b3 b4 b5 b6 b7 b8 b9 b10
389 3 4 3000000001 aaa1 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb1 binary data
390 4 5 3000000002 aaa2 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb2 binary data
391 5 6 3000000003 aaa3 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb3 binary data
392 6 7 3000000004 aaa4 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb4 binary data
393 7 8 3000000005 aaa5 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb5 binary data
394 8 9 3000000006 aaa6 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb6 binary data
395 9 10 3000000007 aaa7 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb7 binary data
396 10 11 3000000008 aaa8 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb8 binary data
397 11 12 3000000009 aaa9 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb9 binary data
398 12 13 3000000010 aaa10 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb10 binary data
399 13 14 3000000011 aaa11 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb11 binary data
400 14 15 3000000012 aaa12 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb12 binary data
401 15 16 3000000013 aaa13 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb13 binary data
402 16 17 3000000014 aaa14 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb14 binary data
403 17 18 3000000015 aaa15 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb15 binary data
404 18 19 3000000016 aaa16 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb16 binary data
405 19 20 3000000017 aaa17 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb17 binary data
406 20 21 3000000018 aaa18 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb18 binary data
407 21 22 3000000019 aaa19 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb19 binary data
408 22 23 3000000020 aaa20 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb20 binary data
409 SELECT COUNT(a1), a1, COUNT(a1)*a1 FROM test.t1 GROUP BY a1;
410 COUNT(a1) a1 COUNT(a1)*a1
411 1 1 1
412 1 2 2
413 1 3 3
414 1 4 4
415 1 5 5
416 1 6 6
417 1 7 7
418 1 8 8
419 1 9 9
420 1 10 10
421 1 11 11
422 1 12 12
423 1 13 13
424 1 14 14
425 1 15 15
426 1 16 16
427 1 17 17
428 1 18 18
429 1 19 19
430 1 20 20
431 SELECT COUNT(a2), (a2+1), COUNT(a2)*(a2+0) FROM test.t1 GROUP BY a2;
432 COUNT(a2) (a2+1) COUNT(a2)*(a2+0)
433 1 3 2
434 1 4 3
435 1 5 4
436 1 6 5
437 1 7 6
438 1 8 7
439 1 9 8
440 1 10 9
441 1 11 10
442 1 12 11
443 1 13 12
444 1 14 13
445 1 15 14
446 1 16 15
447 1 17 16
448 1 18 17
449 1 19 18
450 1 20 19
451 1 21 20
452 1 22 21
453 DROP TABLE test.t1;
454 DROP TABLE test.t2;
455 CREATE TABLE test.t1 (a INT NOT NULL,
456 b CHAR(5), c TEXT, d INT NOT NULL PRIMARY KEY) TABLESPACE
457 table_space1 STORAGE DISK ENGINE=NDB;
458 insert into test.t1 (a,b,c,d) values (1,'a','Jonas',1),(2,'b','Tomas',2),
459 (3,'c','Pekka',3),(4,'d','Martin',4),(5,'e','Stewart',5),(6,'f','Jeb',6),
460 (7,'g','Serge',7),(8,'h','Clavin',8);
461 select distinct a from test.t1 group by b,a having a > 4 order by a desc;
462 a
463 8
464 7
465 6
466 5
467 select distinct a,c from test.t1 group by b,c,a having a > 2 order by a desc;
468 a c
469 8 Clavin
470 7 Serge
471 6 Jeb
472 5 Stewart
473 4 Martin
474 3 Pekka
475 select distinct a from test.t1 group by b,a having a > 1 order by a asc;
476 a
477 2
478 3
479 4
480 5
481 6
482 7
483 8
484 select distinct a,c from test.t1 group by b,c,a having a > 3 order by a asc;
485 a c
486 4 Martin
487 5 Stewart
488 6 Jeb
489 7 Serge
490 8 Clavin
491 drop table test.t1;
492 create table test.t1 (a char(1), key(a)) TABLESPACE table_space1
493 STORAGE DISK ENGINE=NDB;
494 insert into test.t1 values('1'),('1'),('1'),('2'),('2'),('3'),('3');
495 select * from test.t1 where a >= '1' order by a;
496 a
497 1
498 1
499 1
500 2
501 2
502 3
503 3
504 select distinct a from test.t1 order by a desc;
505 a
506 3
507 2
508 1
509 select distinct a from test.t1 where a >= '1' order by a desc;
510 a
511 3
512 2
513 1
514 select distinct a from test.t1 where a >= '1' order by a asc;
515 a
516 1
517 2
518 3
519 drop table test.t1;
520 CREATE TABLE test.t1 (email varchar(50), infoID BIGINT, dateentered DATETIME)
521 TABLESPACE table_space1 STORAGE DISK ENGINE=NDB;
522 CREATE TABLE test.t2 (infoID BIGINT, shipcode varchar(10)) ENGINE=NDB;
523 INSERT INTO test.t1 (email, infoID, dateentered) VALUES
524 ('[email protected]', 1, '2002-07-30 22:56:38'),
525 ('[email protected]', 1, '2002-07-27 22:58:16'),
526 ('[email protected]', 1, '2002-06-19 15:22:19'),
527 ('[email protected]', 2, '2002-06-18 14:23:47'),
528 ('[email protected]', 1, '2002-05-19 22:17:32');
529 INSERT INTO test.t2(infoID, shipcode) VALUES
530 (1, 'Z001'),
531 (2, 'R002');
532 SELECT DISTINCTROW email, shipcode FROM test.t1, test.t2
533 WHERE t1.infoID=t2.infoID order by email, shipcode;
534 email shipcode
535 [email protected] Z001
536 [email protected] R002
537 [email protected] Z001
538 [email protected] Z001
539 SELECT DISTINCTROW email FROM test.t1 ORDER BY dateentered DESC;
540 email
541 [email protected]
542 [email protected]
543 [email protected]
544 SELECT DISTINCTROW email, shipcode FROM test.t1, test.t2
545 WHERE test.t1.infoID=test.t2.infoID ORDER BY dateentered DESC;
546 email shipcode
547 [email protected] Z001
548 [email protected] Z001
549 [email protected] R002
550 [email protected] Z001
551 DROP TABLE test.t1,test.t2;
552 #
553 # Bug#22195588 : SETTING NULL TO A DYNAMIC IN-MEMORY COLUMN CORRUPTS DISK COLUMN VALUES
554 #
555 # Setting up tables
556 create table t1(
557 a int unsigned auto_increment primary key,
558 b int,
559 c int default null column_format dynamic storage memory,
560 d varchar(100) default null column_format dynamic storage memory
561 )tablespace table_space1 storage disk engine=ndbcluster;
562 # Populate t1
563 # Setting all the dynamic columns to NULL and varying disk parts
564 insert into t1 () values();
565 insert into t1 (b) values (10);
566 # Populating values to single dynamic column and varying disk parts
567 insert into t1 (c) values (20);
568 insert into t1 (b, c) values (10, 20);
569 insert into t1 (d) values (repeat('0123456789', 3));
570 insert into t1 (d) values (repeat('0123456789', 10));
571 insert into t1 (b, d) values (10, repeat('0123456789',3));
572 insert into t1 (b, d) values (10, repeat('0123456789',10));
573 # Both dynamic columns have non NULL values and disk columns varied
574 insert into t1 (c, d) values (20, repeat('0123456789',3));
575 insert into t1 (c, d) values (20, repeat('0123456789',10));
576 insert into t1 (b, c, d) values (10, 20, repeat('0123456789',3));
577 insert into t1 (b, c, d) values (10, 20, repeat('0123456789',10));
578 # Verify that they are properly stored and retrieved
579 select * from t1 order by a;
580 a b c d
581 1 NULL NULL NULL
582 2 10 NULL NULL
583 3 NULL 20 NULL
584 4 10 20 NULL
585 5 NULL NULL 012345678901234567890123456789
586 6 NULL NULL 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
587 7 10 NULL 012345678901234567890123456789
588 8 10 NULL 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
589 9 NULL 20 012345678901234567890123456789
590 10 NULL 20 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
591 11 10 20 012345678901234567890123456789
592 12 10 20 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
593 # Update some rows to verify that the varpart is reallocating properly
594 # Updating NULL dynamic columns and assigning NON-NULL values
595 update t1 set c = 10 where a in (1,2);
596 select * from t1 where a in (1,2) order by a;
597 a b c d
598 1 NULL 10 NULL
599 2 10 10 NULL
600 # Updating and setting NULL to NON-NULL dynamic columns
601 update t1 set c = null where a in (1,2);
602 select * from t1 where a in (1,2) order by a;
603 a b c d
604 1 NULL NULL NULL
605 2 10 NULL NULL
606 # Updating NULL dynamic var columns and assigning NON-NULL values
607 update t1 set d = repeat('0123456789', 5) where a in (1,2);
608 select * from t1 where a in (1,2) order by a;
609 a b c d
610 1 NULL NULL 01234567890123456789012345678901234567890123456789
611 2 10 NULL 01234567890123456789012345678901234567890123456789
612 # Updating NULL to NON-NULL and vice versa in dynamic columns
613 update t1 set c = null, d = repeat('0123456789', 10) where a in (3,4);
614 select * from t1 where a in (3,4) order by a;
615 a b c d
616 3 NULL NULL 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
617 4 10 NULL 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
618 # Updating and setting NULL to NON-NULL dynamic var columns
619 update t1 set c = 20, d = null where a in (5,6,7,8);
620 select * from t1 where a in (5,6,7,8) order by a;
621 a b c d
622 5 NULL 20 NULL
623 6 NULL 20 NULL
624 7 10 20 NULL
625 8 10 20 NULL
626 # Update all dynamic columns to NULL
627 update t1 set c = null, d = null where a > 8;
628 select * from t1 where a > 8 order by a;
629 a b c d
630 9 NULL NULL NULL
631 10 NULL NULL NULL
632 11 10 NULL NULL
633 12 10 NULL NULL
634 # Update all dynamic columns to NON-NULL values
635 update t1 set c = 20, d = '0123456789' where a > 8;
636 select * from t1 where a > 8 order by a;
637 a b c d
638 9 NULL 20 0123456789
639 10 NULL 20 0123456789
640 11 10 20 0123456789
641 12 10 20 0123456789
642 select * from t1 order by a;
643 a b c d
644 1 NULL NULL 01234567890123456789012345678901234567890123456789
645 2 10 NULL 01234567890123456789012345678901234567890123456789
646 3 NULL NULL 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
647 4 10 NULL 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
648 5 NULL 20 NULL
649 6 NULL 20 NULL
650 7 10 20 NULL
651 8 10 20 NULL
652 9 NULL 20 0123456789
653 10 NULL 20 0123456789
654 11 10 20 0123456789
655 12 10 20 0123456789
656 # Now do some transactions with multiple operations
657 # Add more data to dynamic varchar and then set to NULL
658 # Meanwhile insert value into int column
659 # This should reduce the var memory usage
660 select * from t1 where a = 1;
661 a b c d
662 1 NULL NULL 01234567890123456789012345678901234567890123456789
663 begin;
664 update t1 set d = repeat('0123456789', 10) where a = 1;
665 update t1 set b = 20 where a = 1;
666 update t1 set d = NULL where a = 1;
667 update t1 set c = 30 where a = 1;
668 commit;
669 select * from t1 where a = 1;
670 a b c d
671 1 20 30 NULL
672 # Check if the var mem has decreased
673 Has var part memory usage decreased?
674 Yes
675 # Setting up next transaction - make dynamic columns NULL
676 update t1 set c = NULL where a = 1;
677 # Checking var memory and number of var parts before transaction
678 # Insert and later set null to dynamic columns in the same transaction
679 # This should not change memory usage
680 select * from t1 where a = 1;
681 a b c d
682 1 20 NULL NULL
683 begin;
684 update t1 set c = 30 where a = 1;
685 update t1 set b = 30 where a = 1;
686 update t1 set d = repeat('0123456789', 10) where a = 1;
687 update t1 set b = 45 where a = 1;
688 update t1 set d = repeat('0123456789', 5) where a = 1;
689 update t1 set c = NULL where a = 1;
690 update t1 set d = NULL where a = 1;
691 commit;
692 select * from t1 where a = 1;
693 a b c d
694 1 45 NULL NULL
695 # Checking if the var mem is the same
696 Is var part memory usage unchanged?
697 Yes
698 # Grow the dynamic varchar and then shrink it but still having more data than when it was at start.
699 # Insert alternatively the other dynamic column with NULL but back to a NON-NULL value at end
700 # This should be reflected correctly in the memory
701 select * from t1 where a = 12;
702 a b c d
703 12 10 20 0123456789
704 begin;
705 update t1 set c = 42 where a = 12;
706 update t1 set d = repeat('0123456789', 5) where a = 12;
707 update t1 set c = NULL where a = 12;
708 update t1 set d = repeat('0123456789', 10) where a = 12;
709 update t1 set c = 30 where a = 12;
710 update t1 set d = repeat('0123456789', 2) where a = 12;
711 commit;
712 select * from t1 where a = 12;
713 a b c d
714 12 10 30 01234567890123456789
715 # Check if the var mem has increased
716 Has var part memory usage increased?
717 Yes
718 # Cleanup
719 drop table t1;
720 ALTER TABLESPACE table_space1
721 DROP DATAFILE './table_space1/datafile.dat';
722 DROP TABLESPACE table_space1;
723 DROP LOGFILE GROUP log_group1
724 ENGINE =NDB;