Debian Codegraph @

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

raw
1 drop table if exists t1, t1sum;
2 select @@session.time_zone;
3 @@session.time_zone
4 SYSTEM
5 set time_zone = '+00:00';
6 select @@session.time_zone;
7 @@session.time_zone
8 +00:00
9 # create query summary table
10 create table t1sum (
11 pk int primary key,
12 cnt int unsigned not null,
13 crc bigint unsigned
14 ) engine=myisam;
15 # PART 1: data load and queries
16 # create table prec 3
17 create table t1 (
18 a int not null,
19 b year not null default '2000',
20 c date not null default '2000-01-02',
21 d time(3) not null default '10:11:12.123',
22 e datetime(3) not null default '2000-01-02 10:11:12.123',
23 f timestamp(3) not null default '2000-01-02 10:11:12.123',
24 primary key using hash (a),
25 index bx (b),
26 index cx (c),
27 index dx (d),
28 index ex (e),
29 index fx (f)
30 ) engine=ndb;
31 show create table t1;
32 Table Create Table
33 t1 CREATE TABLE `t1` (
34 `a` int NOT NULL,
35 `b` year NOT NULL DEFAULT '2000',
36 `c` date NOT NULL DEFAULT '2000-01-02',
37 `d` time(3) NOT NULL DEFAULT '10:11:12.123',
38 `e` datetime(3) NOT NULL DEFAULT '2000-01-02 10:11:12.123',
39 `f` timestamp(3) NOT NULL DEFAULT '2000-01-02 10:11:12.123',
40 PRIMARY KEY (`a`) USING HASH,
41 KEY `bx` (`b`),
42 KEY `cx` (`c`),
43 KEY `dx` (`d`),
44 KEY `ex` (`e`),
45 KEY `fx` (`f`)
46 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
47 Attributes:
48 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
49 b Year NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000
50 c Date NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02
51 d Time2(3) NOT NULL AT=FIXED ST=MEMORY DEFAULT 10:11:12.123
52 e Datetime2(3) NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02/10:11:12.123
53 f Timestamp2(3) NOT NULL AT=FIXED ST=MEMORY DEFAULT 946807872.123
54 Indexes:
55 bx(b) - OrderedIndex
56 cx(c) - OrderedIndex
57 dx(d) - OrderedIndex
58 ex(e) - OrderedIndex
59 fx(f) - OrderedIndex
60 # test defaults
61 insert into t1(a) values (0);
62 select * from t1;
63 a b c d e f
64 0 2000 2000-01-02 10:11:12.123 2000-01-02 10:11:12.123 2000-01-02 10:11:12.123
65 delete from t1;
66 # load data
67 load data local infile 'suite/ndb/data/wl946_main3.dat'
68 into table t1 fields terminated by ',' lines terminated by '\n';
69 # check contents
70 select count(*), sum(crc32(concat(a,b,c,d,e,f))) from t1;
71 count(*) sum(crc32(concat(a,b,c,d,e,f)))
72 500 1099869477618
73 # verify ndb_select_all
74 create temporary table t1tmp1 (
75 x char(255)
76 ) engine=myisam;
77 load data infile 'DUMP_FILE1' into table t1tmp1;
78 delete from t1tmp1 where x not regexp '^[0-9]*,';
79 select x from t1tmp1 into outfile 'DUMP_FILE2';
80 create temporary table t1tmp2 (
81 a int primary key,
82 b char(40),
83 c char(40),
84 d char(40),
85 e char(40),
86 f char(40)
87 ) engine=myisam;
88 load data infile 'DUMP_FILE2' into table t1tmp2
89 fields terminated by ',' lines terminated by '\n';
90 update t1tmp2 set f = replace(f, '\r', '');
91 select count(*), sum(crc32(concat(a,b,c,d,e,unix_timestamp(f)))) from t1;
92 count(*) sum(crc32(concat(a,b,c,d,e,unix_timestamp(f))))
93 500 1042802316591
94 select count(*), sum(crc32(concat(a,b,c,d,replace(e,'/',' '),f))) from t1tmp2;
95 count(*) sum(crc32(concat(a,b,c,d,replace(e,'/',' '),f)))
96 500 1042802316591
97 drop table t1tmp1;
98 drop table t1tmp2;
99 # index queries
100 select * from t1 where b = '2011';
101 a b c d e f
102 379 2011 2007-12-01 -13:38:50.785 8321-12-27 21:08:47.878 1979-12-28 20:02:06.999
103 select * from t1 where c = '2007-12-01';
104 a b c d e f
105 379 2011 2007-12-01 -13:38:50.785 8321-12-27 21:08:47.878 1979-12-28 20:02:06.999
106 select * from t1 where d = '-13:38:50.785';
107 a b c d e f
108 379 2011 2007-12-01 -13:38:50.785 8321-12-27 21:08:47.878 1979-12-28 20:02:06.999
109 select * from t1 where e = '8321-12-27 21:08:47.878';
110 a b c d e f
111 379 2011 2007-12-01 -13:38:50.785 8321-12-27 21:08:47.878 1979-12-28 20:02:06.999
112 select * from t1 where f = '1979-12-28 20:02:06.999';
113 a b c d e f
114 379 2011 2007-12-01 -13:38:50.785 8321-12-27 21:08:47.878 1979-12-28 20:02:06.999
115 select sum(cnt), sum(crc32(concat(cnt,crc))) from t1sum;
116 sum(cnt) sum(crc32(concat(cnt,crc)))
117 69971 828138701178
118 # check results via myisam
119 alter table t1 engine=myisam;
120 Warnings:
121 Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
122 show create table t1;
123 Table Create Table
124 t1 CREATE TABLE `t1` (
125 `a` int NOT NULL,
126 `b` year NOT NULL DEFAULT '2000',
127 `c` date NOT NULL DEFAULT '2000-01-02',
128 `d` time(3) NOT NULL DEFAULT '10:11:12.123',
129 `e` datetime(3) NOT NULL DEFAULT '2000-01-02 10:11:12.123',
130 `f` timestamp(3) NOT NULL DEFAULT '2000-01-02 10:11:12.123',
131 PRIMARY KEY (`a`),
132 KEY `bx` (`b`),
133 KEY `cx` (`c`),
134 KEY `dx` (`d`),
135 KEY `ex` (`e`),
136 KEY `fx` (`f`)
137 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
138 select count(*), sum(crc32(concat(a,b,c,d,e,f))) from t1;
139 count(*) sum(crc32(concat(a,b,c,d,e,f)))
140 500 1099869477618
141 select * from t1 where b = '2011';
142 a b c d e f
143 379 2011 2007-12-01 -13:38:50.785 8321-12-27 21:08:47.878 1979-12-28 20:02:06.999
144 select * from t1 where c = '2007-12-01';
145 a b c d e f
146 379 2011 2007-12-01 -13:38:50.785 8321-12-27 21:08:47.878 1979-12-28 20:02:06.999
147 select * from t1 where d = '-13:38:50.785';
148 a b c d e f
149 379 2011 2007-12-01 -13:38:50.785 8321-12-27 21:08:47.878 1979-12-28 20:02:06.999
150 select * from t1 where e = '8321-12-27 21:08:47.878';
151 a b c d e f
152 379 2011 2007-12-01 -13:38:50.785 8321-12-27 21:08:47.878 1979-12-28 20:02:06.999
153 select * from t1 where f = '1979-12-28 20:02:06.999';
154 a b c d e f
155 379 2011 2007-12-01 -13:38:50.785 8321-12-27 21:08:47.878 1979-12-28 20:02:06.999
156 select sum(cnt), sum(crc32(concat(cnt,crc))) from t1sum;
157 sum(cnt) sum(crc32(concat(cnt,crc)))
158 69971 828138701178
159 # drop table prec 3
160 drop table t1;
161 # create table prec 6
162 create table t1 (
163 a int not null,
164 b year not null default '2000',
165 c date not null default '2000-01-02',
166 d time(6) not null default '10:11:12.123456',
167 e datetime(6) not null default '2000-01-02 10:11:12.123456',
168 f timestamp(6) not null default '2000-01-02 10:11:12.123456',
169 primary key using hash (a),
170 index bx (b),
171 index cx (c),
172 index dx (d),
173 index ex (e),
174 index fx (f)
175 ) engine=ndb;
176 show create table t1;
177 Table Create Table
178 t1 CREATE TABLE `t1` (
179 `a` int NOT NULL,
180 `b` year NOT NULL DEFAULT '2000',
181 `c` date NOT NULL DEFAULT '2000-01-02',
182 `d` time(6) NOT NULL DEFAULT '10:11:12.123456',
183 `e` datetime(6) NOT NULL DEFAULT '2000-01-02 10:11:12.123456',
184 `f` timestamp(6) NOT NULL DEFAULT '2000-01-02 10:11:12.123456',
185 PRIMARY KEY (`a`) USING HASH,
186 KEY `bx` (`b`),
187 KEY `cx` (`c`),
188 KEY `dx` (`d`),
189 KEY `ex` (`e`),
190 KEY `fx` (`f`)
191 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
192 Attributes:
193 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
194 b Year NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000
195 c Date NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02
196 d Time2(6) NOT NULL AT=FIXED ST=MEMORY DEFAULT 10:11:12.123456
197 e Datetime2(6) NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02/10:11:12.123456
198 f Timestamp2(6) NOT NULL AT=FIXED ST=MEMORY DEFAULT 946807872.123456
199 Indexes:
200 bx(b) - OrderedIndex
201 cx(c) - OrderedIndex
202 dx(d) - OrderedIndex
203 ex(e) - OrderedIndex
204 fx(f) - OrderedIndex
205 # test defaults
206 insert into t1(a) values (0);
207 select * from t1;
208 a b c d e f
209 0 2000 2000-01-02 10:11:12.123456 2000-01-02 10:11:12.123456 2000-01-02 10:11:12.123456
210 delete from t1;
211 # load data
212 load data local infile 'suite/ndb/data/wl946_main6.dat'
213 into table t1 fields terminated by ',' lines terminated by '\n';
214 # check contents
215 select count(*), sum(crc32(concat(a,b,c,d,e,f))) from t1;
216 count(*) sum(crc32(concat(a,b,c,d,e,f)))
217 500 1091780159996
218 # verify ndb_select_all
219 create temporary table t1tmp1 (
220 x char(255)
221 ) engine=myisam;
222 load data infile 'DUMP_FILE1' into table t1tmp1;
223 delete from t1tmp1 where x not regexp '^[0-9]*,';
224 select x from t1tmp1 into outfile 'DUMP_FILE2';
225 create temporary table t1tmp2 (
226 a int primary key,
227 b char(40),
228 c char(40),
229 d char(40),
230 e char(40),
231 f char(40)
232 ) engine=myisam;
233 load data infile 'DUMP_FILE2' into table t1tmp2
234 fields terminated by ',' lines terminated by '\n';
235 update t1tmp2 set f = replace(f, '\r', '');
236 select count(*), sum(crc32(concat(a,b,c,d,e,unix_timestamp(f)))) from t1;
237 count(*) sum(crc32(concat(a,b,c,d,e,unix_timestamp(f))))
238 500 1102908582969
239 select count(*), sum(crc32(concat(a,b,c,d,replace(e,'/',' '),f))) from t1tmp2;
240 count(*) sum(crc32(concat(a,b,c,d,replace(e,'/',' '),f)))
241 500 1102908582969
242 drop table t1tmp1;
243 drop table t1tmp2;
244 # index queries
245 select * from t1 where b = '1984';
246 a b c d e f
247 95 1984 2099-12-21 15:11:51.840792 1917-01-19 09:13:16.715589 2032-03-16 20:53:59.869710
248 select * from t1 where c = '2099-12-21';
249 a b c d e f
250 95 1984 2099-12-21 15:11:51.840792 1917-01-19 09:13:16.715589 2032-03-16 20:53:59.869710
251 select * from t1 where d = '15:11:51.840792';
252 a b c d e f
253 95 1984 2099-12-21 15:11:51.840792 1917-01-19 09:13:16.715589 2032-03-16 20:53:59.869710
254 select * from t1 where e = '1917-01-19 09:13:16.715589';
255 a b c d e f
256 95 1984 2099-12-21 15:11:51.840792 1917-01-19 09:13:16.715589 2032-03-16 20:53:59.869710
257 select * from t1 where f = '2032-03-16 20:53:59.869710';
258 a b c d e f
259 95 1984 2099-12-21 15:11:51.840792 1917-01-19 09:13:16.715589 2032-03-16 20:53:59.869710
260 select sum(cnt), sum(crc32(concat(cnt,crc))) from t1sum;
261 sum(cnt) sum(crc32(concat(cnt,crc)))
262 69575 874482681592
263 # check results via myisam
264 alter table t1 engine=myisam;
265 Warnings:
266 Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
267 show create table t1;
268 Table Create Table
269 t1 CREATE TABLE `t1` (
270 `a` int NOT NULL,
271 `b` year NOT NULL DEFAULT '2000',
272 `c` date NOT NULL DEFAULT '2000-01-02',
273 `d` time(6) NOT NULL DEFAULT '10:11:12.123456',
274 `e` datetime(6) NOT NULL DEFAULT '2000-01-02 10:11:12.123456',
275 `f` timestamp(6) NOT NULL DEFAULT '2000-01-02 10:11:12.123456',
276 PRIMARY KEY (`a`),
277 KEY `bx` (`b`),
278 KEY `cx` (`c`),
279 KEY `dx` (`d`),
280 KEY `ex` (`e`),
281 KEY `fx` (`f`)
282 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
283 select count(*), sum(crc32(concat(a,b,c,d,e,f))) from t1;
284 count(*) sum(crc32(concat(a,b,c,d,e,f)))
285 500 1091780159996
286 select * from t1 where b = '1984';
287 a b c d e f
288 95 1984 2099-12-21 15:11:51.840792 1917-01-19 09:13:16.715589 2032-03-16 20:53:59.869710
289 select * from t1 where c = '2099-12-21';
290 a b c d e f
291 95 1984 2099-12-21 15:11:51.840792 1917-01-19 09:13:16.715589 2032-03-16 20:53:59.869710
292 select * from t1 where d = '15:11:51.840792';
293 a b c d e f
294 95 1984 2099-12-21 15:11:51.840792 1917-01-19 09:13:16.715589 2032-03-16 20:53:59.869710
295 select * from t1 where e = '1917-01-19 09:13:16.715589';
296 a b c d e f
297 95 1984 2099-12-21 15:11:51.840792 1917-01-19 09:13:16.715589 2032-03-16 20:53:59.869710
298 select * from t1 where f = '2032-03-16 20:53:59.869710';
299 a b c d e f
300 95 1984 2099-12-21 15:11:51.840792 1917-01-19 09:13:16.715589 2032-03-16 20:53:59.869710
301 select sum(cnt), sum(crc32(concat(cnt,crc))) from t1sum;
302 sum(cnt) sum(crc32(concat(cnt,crc)))
303 69575 874482681592
304 # drop table prec 6
305 drop table t1;
306 # PART 2: backup and restore
307 # create table prec 3
308 create table t1 (
309 a int not null,
310 b year not null default '2000',
311 c date not null default '2000-01-02',
312 d time(3) not null default '10:11:12.123',
313 e datetime(3) not null default '2000-01-02 10:11:12.123',
314 f timestamp(3) not null default '2000-01-02 10:11:12.123',
315 primary key using hash (a),
316 index bx (b),
317 index cx (c),
318 index dx (d),
319 index ex (e),
320 index fx (f)
321 ) engine=ndb;
322 show create table t1;
323 Table Create Table
324 t1 CREATE TABLE `t1` (
325 `a` int NOT NULL,
326 `b` year NOT NULL DEFAULT '2000',
327 `c` date NOT NULL DEFAULT '2000-01-02',
328 `d` time(3) NOT NULL DEFAULT '10:11:12.123',
329 `e` datetime(3) NOT NULL DEFAULT '2000-01-02 10:11:12.123',
330 `f` timestamp(3) NOT NULL DEFAULT '2000-01-02 10:11:12.123',
331 PRIMARY KEY (`a`) USING HASH,
332 KEY `bx` (`b`),
333 KEY `cx` (`c`),
334 KEY `dx` (`d`),
335 KEY `ex` (`e`),
336 KEY `fx` (`f`)
337 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
338 Attributes:
339 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
340 b Year NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000
341 c Date NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02
342 d Time2(3) NOT NULL AT=FIXED ST=MEMORY DEFAULT 10:11:12.123
343 e Datetime2(3) NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02/10:11:12.123
344 f Timestamp2(3) NOT NULL AT=FIXED ST=MEMORY DEFAULT 946807872.123
345 Indexes:
346 bx(b) - OrderedIndex
347 cx(c) - OrderedIndex
348 dx(d) - OrderedIndex
349 ex(e) - OrderedIndex
350 fx(f) - OrderedIndex
351 # load data
352 load data local infile 'suite/ndb/data/wl946_main3.dat'
353 into table t1 fields terminated by ',' lines terminated by '\n';
354 # run backup
355 # drop table prec 3
356 drop table t1;
357 # restore meta from backup
358 show create table t1;
359 Table Create Table
360 t1 CREATE TABLE `t1` (
361 `a` int NOT NULL,
362 `b` year NOT NULL DEFAULT '2000',
363 `c` date NOT NULL DEFAULT '2000-01-02',
364 `d` time(3) NOT NULL DEFAULT '10:11:12.123',
365 `e` datetime(3) NOT NULL DEFAULT '2000-01-02 10:11:12.123',
366 `f` timestamp(3) NOT NULL DEFAULT '2000-01-02 10:11:12.123',
367 PRIMARY KEY (`a`) USING HASH,
368 KEY `bx` (`b`),
369 KEY `cx` (`c`),
370 KEY `dx` (`d`),
371 KEY `ex` (`e`),
372 KEY `fx` (`f`)
373 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
374 Attributes:
375 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
376 b Year NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000
377 c Date NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02
378 d Time2(3) NOT NULL AT=FIXED ST=MEMORY DEFAULT 10:11:12.123
379 e Datetime2(3) NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02/10:11:12.123
380 f Timestamp2(3) NOT NULL AT=FIXED ST=MEMORY DEFAULT 946807872.123
381 Indexes:
382 bx(b) - OrderedIndex
383 cx(c) - OrderedIndex
384 dx(d) - OrderedIndex
385 ex(e) - OrderedIndex
386 fx(f) - OrderedIndex
387 # restore data from backup
388 # check contents
389 select count(*), sum(crc32(concat(a,b,c,d,e,f))) from t1;
390 count(*) sum(crc32(concat(a,b,c,d,e,f)))
391 500 1099869477618
392 # drop table prec 3
393 drop table t1;
394 # create table prec 6
395 create table t1 (
396 a int not null,
397 b year not null default '2000',
398 c date not null default '2000-01-02',
399 d time(6) not null default '10:11:12.123456',
400 e datetime(6) not null default '2000-01-02 10:11:12.123456',
401 f timestamp(6) not null default '2000-01-02 10:11:12.123456',
402 primary key using hash (a),
403 index bx (b),
404 index cx (c),
405 index dx (d),
406 index ex (e),
407 index fx (f)
408 ) engine=ndb;
409 show create table t1;
410 Table Create Table
411 t1 CREATE TABLE `t1` (
412 `a` int NOT NULL,
413 `b` year NOT NULL DEFAULT '2000',
414 `c` date NOT NULL DEFAULT '2000-01-02',
415 `d` time(6) NOT NULL DEFAULT '10:11:12.123456',
416 `e` datetime(6) NOT NULL DEFAULT '2000-01-02 10:11:12.123456',
417 `f` timestamp(6) NOT NULL DEFAULT '2000-01-02 10:11:12.123456',
418 PRIMARY KEY (`a`) USING HASH,
419 KEY `bx` (`b`),
420 KEY `cx` (`c`),
421 KEY `dx` (`d`),
422 KEY `ex` (`e`),
423 KEY `fx` (`f`)
424 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
425 Attributes:
426 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
427 b Year NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000
428 c Date NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02
429 d Time2(6) NOT NULL AT=FIXED ST=MEMORY DEFAULT 10:11:12.123456
430 e Datetime2(6) NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02/10:11:12.123456
431 f Timestamp2(6) NOT NULL AT=FIXED ST=MEMORY DEFAULT 946807872.123456
432 Indexes:
433 bx(b) - OrderedIndex
434 cx(c) - OrderedIndex
435 dx(d) - OrderedIndex
436 ex(e) - OrderedIndex
437 fx(f) - OrderedIndex
438 # load data
439 load data local infile 'suite/ndb/data/wl946_main6.dat'
440 into table t1 fields terminated by ',' lines terminated by '\n';
441 # run backup
442 # drop table prec 6
443 drop table t1;
444 # restore meta from backup
445 show create table t1;
446 Table Create Table
447 t1 CREATE TABLE `t1` (
448 `a` int NOT NULL,
449 `b` year NOT NULL DEFAULT '2000',
450 `c` date NOT NULL DEFAULT '2000-01-02',
451 `d` time(6) NOT NULL DEFAULT '10:11:12.123456',
452 `e` datetime(6) NOT NULL DEFAULT '2000-01-02 10:11:12.123456',
453 `f` timestamp(6) NOT NULL DEFAULT '2000-01-02 10:11:12.123456',
454 PRIMARY KEY (`a`) USING HASH,
455 KEY `bx` (`b`),
456 KEY `cx` (`c`),
457 KEY `dx` (`d`),
458 KEY `ex` (`e`),
459 KEY `fx` (`f`)
460 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
461 Attributes:
462 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
463 b Year NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000
464 c Date NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02
465 d Time2(6) NOT NULL AT=FIXED ST=MEMORY DEFAULT 10:11:12.123456
466 e Datetime2(6) NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02/10:11:12.123456
467 f Timestamp2(6) NOT NULL AT=FIXED ST=MEMORY DEFAULT 946807872.123456
468 Indexes:
469 bx(b) - OrderedIndex
470 cx(c) - OrderedIndex
471 dx(d) - OrderedIndex
472 ex(e) - OrderedIndex
473 fx(f) - OrderedIndex
474 # restore data from backup
475 # check contents
476 select count(*), sum(crc32(concat(a,b,c,d,e,f))) from t1;
477 count(*) sum(crc32(concat(a,b,c,d,e,f)))
478 500 1091780159996
479 # check contents truncated to prec 3
480 drop table if exists t1tmp;
481 create temporary table t1tmp (
482 x char(255)
483 ) engine=myisam;
484 insert into t1tmp select concat(a,b,c,left(d,length(d)-3),left(e,length(e)-3),left(f,length(f)-3)) from t1;
485 select count(*), sum(crc32(x)) from t1tmp;
486 count(*) sum(crc32(x))
487 500 992449343770
488 drop table t1tmp;
489 # drop table prec 6
490 drop table t1;
491 # PART 3: restore with data promote and demote
492 # create table prec 6
493 create table t1 (
494 a int not null,
495 b year not null default '2000',
496 c date not null default '2000-01-02',
497 d time(6) not null default '10:11:12.123456',
498 e datetime(6) not null default '2000-01-02 10:11:12.123456',
499 f timestamp(6) not null default '2000-01-02 10:11:12.123456',
500 primary key using hash (a),
501 index bx (b),
502 index cx (c),
503 index dx (d),
504 index ex (e),
505 index fx (f)
506 ) engine=ndb;
507 show create table t1;
508 Table Create Table
509 t1 CREATE TABLE `t1` (
510 `a` int NOT NULL,
511 `b` year NOT NULL DEFAULT '2000',
512 `c` date NOT NULL DEFAULT '2000-01-02',
513 `d` time(6) NOT NULL DEFAULT '10:11:12.123456',
514 `e` datetime(6) NOT NULL DEFAULT '2000-01-02 10:11:12.123456',
515 `f` timestamp(6) NOT NULL DEFAULT '2000-01-02 10:11:12.123456',
516 PRIMARY KEY (`a`) USING HASH,
517 KEY `bx` (`b`),
518 KEY `cx` (`c`),
519 KEY `dx` (`d`),
520 KEY `ex` (`e`),
521 KEY `fx` (`f`)
522 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
523 Attributes:
524 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
525 b Year NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000
526 c Date NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02
527 d Time2(6) NOT NULL AT=FIXED ST=MEMORY DEFAULT 10:11:12.123456
528 e Datetime2(6) NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02/10:11:12.123456
529 f Timestamp2(6) NOT NULL AT=FIXED ST=MEMORY DEFAULT 946807872.123456
530 Indexes:
531 bx(b) - OrderedIndex
532 cx(c) - OrderedIndex
533 dx(d) - OrderedIndex
534 ex(e) - OrderedIndex
535 fx(f) - OrderedIndex
536 # restore data from backup with promote
537 # check contents
538 select count(*), sum(crc32(replace(concat(a,b,c,d,e,f),".000","."))) from t1;
539 count(*) sum(crc32(replace(concat(a,b,c,d,e,f),".000",".")))
540 500 1099869477618
541 # drop table prec 6
542 drop table t1;
543 # create table prec 3
544 create table t1 (
545 a int not null,
546 b year not null default '2000',
547 c date not null default '2000-01-02',
548 d time(3) not null default '10:11:12.123',
549 e datetime(3) not null default '2000-01-02 10:11:12.123',
550 f timestamp(3) not null default '2000-01-02 10:11:12.123',
551 primary key using hash (a),
552 index bx (b),
553 index cx (c),
554 index dx (d),
555 index ex (e),
556 index fx (f)
557 ) engine=ndb;
558 show create table t1;
559 Table Create Table
560 t1 CREATE TABLE `t1` (
561 `a` int NOT NULL,
562 `b` year NOT NULL DEFAULT '2000',
563 `c` date NOT NULL DEFAULT '2000-01-02',
564 `d` time(3) NOT NULL DEFAULT '10:11:12.123',
565 `e` datetime(3) NOT NULL DEFAULT '2000-01-02 10:11:12.123',
566 `f` timestamp(3) NOT NULL DEFAULT '2000-01-02 10:11:12.123',
567 PRIMARY KEY (`a`) USING HASH,
568 KEY `bx` (`b`),
569 KEY `cx` (`c`),
570 KEY `dx` (`d`),
571 KEY `ex` (`e`),
572 KEY `fx` (`f`)
573 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
574 Attributes:
575 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
576 b Year NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000
577 c Date NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02
578 d Time2(3) NOT NULL AT=FIXED ST=MEMORY DEFAULT 10:11:12.123
579 e Datetime2(3) NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02/10:11:12.123
580 f Timestamp2(3) NOT NULL AT=FIXED ST=MEMORY DEFAULT 946807872.123
581 Indexes:
582 bx(b) - OrderedIndex
583 cx(c) - OrderedIndex
584 dx(d) - OrderedIndex
585 ex(e) - OrderedIndex
586 fx(f) - OrderedIndex
587 # restore data from backup with demote
588 # check contents truncated to prec 3
589 select count(*), sum(crc32(concat(a,b,c,d,e,f))) from t1;
590 count(*) sum(crc32(concat(a,b,c,d,e,f)))
591 500 992449343770
592 set time_zone = default;
593 select @@session.time_zone;
594 @@session.time_zone
595 SYSTEM
596 drop table t1, t1sum;