Debian Codegraph @

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

raw
1 select @@session.time_zone;
2 @@session.time_zone
3 SYSTEM
4 set time_zone = '+00:00';
5 select @@session.time_zone;
6 @@session.time_zone
7 +00:00
8 # create table
9 create table t1 (
10 a int not null,
11 b year not null default '2000',
12 c date not null default '2000-01-02',
13 d time not null default '10:11:12',
14 e datetime not null default '2000-01-02 10:11:12',
15 f timestamp not null default '2000-01-02 10:11:12',
16 primary key using hash (a),
17 index bx (b),
18 index cx (c),
19 index dx (d),
20 index ex (e),
21 index fx (f)
22 ) engine=ndb;
23 show create table t1;
24 Table Create Table
25 t1 CREATE TABLE `t1` (
26 `a` int NOT NULL,
27 `b` year NOT NULL DEFAULT '2000',
28 `c` date NOT NULL DEFAULT '2000-01-02',
29 `d` time NOT NULL DEFAULT '10:11:12',
30 `e` datetime NOT NULL DEFAULT '2000-01-02 10:11:12',
31 `f` timestamp NOT NULL DEFAULT '2000-01-02 10:11:12',
32 PRIMARY KEY (`a`) USING HASH,
33 KEY `bx` (`b`),
34 KEY `cx` (`c`),
35 KEY `dx` (`d`),
36 KEY `ex` (`e`),
37 KEY `fx` (`f`)
38 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
39 # test defaults
40 insert into t1 (a) values (0);
41 select * from t1;
42 a b c d e f
43 0 2000 2000-01-02 10:11:12 2000-01-02 10:11:12 2000-01-02 10:11:12
44 delete from t1;
45 # load data
46 load data local infile 'suite/ndb/data/wl946_pre.dat'
47 into table t1 fields terminated by ',' lines terminated by '\n';
48 # check contents
49 select count(*), sum(crc32(concat(a,b,c,d,e,f))) from t1;
50 count(*) sum(crc32(concat(a,b,c,d,e,f)))
51 500 1105949732775
52 # run backup
53 # index queries
54 select * from t1 where b = '1969';
55 a b c d e f
56 233 1969 2017-03-21 17:50:30 2014-03-19 18:41:26 1999-05-23 10:45:42
57 select * from t1 where c = '2017-03-21';
58 a b c d e f
59 233 1969 2017-03-21 17:50:30 2014-03-19 18:41:26 1999-05-23 10:45:42
60 select * from t1 where d = '17:50:30';
61 a b c d e f
62 233 1969 2017-03-21 17:50:30 2014-03-19 18:41:26 1999-05-23 10:45:42
63 select * from t1 where e = '2014-03-19 18:41:26';
64 a b c d e f
65 233 1969 2017-03-21 17:50:30 2014-03-19 18:41:26 1999-05-23 10:45:42
66 select * from t1 where f = '1999-05-23 10:45:42';
67 a b c d e f
68 233 1969 2017-03-21 17:50:30 2014-03-19 18:41:26 1999-05-23 10:45:42
69 select count(*) from t1
70 where b between '2000' and 2010;
71 count(*)
72 15
73 select count(*) from t1
74 where c between '2000-01-01' and '2010-01-01';
75 count(*)
76 20
77 select count(*) from t1
78 where d between '-20:30:40' and '20:30:40';
79 count(*)
80 381
81 select count(*) from t1
82 where e between '2000-01-01 20:30:40' and '2010-01-01 20:30:40';
83 count(*)
84 23
85 select count(*) from t1
86 where f between '2000-01-01 20:30:40' and '2010-01-01 20:30:40';
87 count(*)
88 67
89 # check results via innodb
90 alter table t1 engine=innodb;
91 Warnings:
92 Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
93 show create table t1;
94 Table Create Table
95 t1 CREATE TABLE `t1` (
96 `a` int NOT NULL,
97 `b` year NOT NULL DEFAULT '2000',
98 `c` date NOT NULL DEFAULT '2000-01-02',
99 `d` time NOT NULL DEFAULT '10:11:12',
100 `e` datetime NOT NULL DEFAULT '2000-01-02 10:11:12',
101 `f` timestamp NOT NULL DEFAULT '2000-01-02 10:11:12',
102 PRIMARY KEY (`a`),
103 KEY `bx` (`b`),
104 KEY `cx` (`c`),
105 KEY `dx` (`d`),
106 KEY `ex` (`e`),
107 KEY `fx` (`f`)
108 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
109 select count(*), sum(crc32(concat(a,b,c,d,e,f))) from t1;
110 count(*) sum(crc32(concat(a,b,c,d,e,f)))
111 500 1105949732775
112 select * from t1 where b = '1969';
113 a b c d e f
114 233 1969 2017-03-21 17:50:30 2014-03-19 18:41:26 1999-05-23 10:45:42
115 select * from t1 where c = '2017-03-21';
116 a b c d e f
117 233 1969 2017-03-21 17:50:30 2014-03-19 18:41:26 1999-05-23 10:45:42
118 select * from t1 where d = '17:50:30';
119 a b c d e f
120 233 1969 2017-03-21 17:50:30 2014-03-19 18:41:26 1999-05-23 10:45:42
121 select * from t1 where e = '2014-03-19 18:41:26';
122 a b c d e f
123 233 1969 2017-03-21 17:50:30 2014-03-19 18:41:26 1999-05-23 10:45:42
124 select * from t1 where f = '1999-05-23 10:45:42';
125 a b c d e f
126 233 1969 2017-03-21 17:50:30 2014-03-19 18:41:26 1999-05-23 10:45:42
127 select count(*) from t1
128 where b between '2000' and 2010;
129 count(*)
130 15
131 select count(*) from t1
132 where c between '2000-01-01' and '2010-01-01';
133 count(*)
134 20
135 select count(*) from t1
136 where d between '-20:30:40' and '20:30:40';
137 count(*)
138 381
139 select count(*) from t1
140 where e between '2000-01-01 20:30:40' and '2010-01-01 20:30:40';
141 count(*)
142 23
143 select count(*) from t1
144 where f between '2000-01-01 20:30:40' and '2010-01-01 20:30:40';
145 count(*)
146 67
147 # drop table
148 drop table t1;
149 # restore meta from backup
150 show create table t1;
151 Table Create Table
152 t1 CREATE TABLE `t1` (
153 `a` int NOT NULL,
154 `b` year NOT NULL DEFAULT '2000',
155 `c` date NOT NULL DEFAULT '2000-01-02',
156 `d` time NOT NULL DEFAULT '10:11:12',
157 `e` datetime NOT NULL DEFAULT '2000-01-02 10:11:12',
158 `f` timestamp NOT NULL DEFAULT '2000-01-02 10:11:12',
159 PRIMARY KEY (`a`) USING HASH,
160 KEY `bx` (`b`),
161 KEY `cx` (`c`),
162 KEY `dx` (`d`),
163 KEY `ex` (`e`),
164 KEY `fx` (`f`)
165 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
166 # restore data from backup
167 # check contents
168 select count(*), sum(crc32(concat(a,b,c,d,e,f))) from t1;
169 count(*) sum(crc32(concat(a,b,c,d,e,f)))
170 500 1105949732775
171 # drop table
172 drop table t1;
173 # restore meta from wl946_pre
174 Attributes:
175 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
176 b Year NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000
177 c Date NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02
178 d Time NOT NULL AT=FIXED ST=MEMORY DEFAULT 10:11:12
179 e Datetime NOT NULL AT=FIXED ST=MEMORY DEFAULT 2000-01-02/10:11:12
180 f Timestamp NOT NULL AT=FIXED ST=MEMORY DEFAULT 946807872
181 Indexes:
182 bx(b) - OrderedIndex
183 cx(c) - OrderedIndex
184 dx(d) - OrderedIndex
185 ex(e) - OrderedIndex
186 fx(f) - OrderedIndex
187 # restore data from wl946_pre
188 # check contents
189 select count(*), sum(crc32(concat(a,b,c,d,e,f))) from t1;
190 ERROR 42S02: Table 'test.t1' doesn't exist
191 SHOW WARNINGS;
192 Level Code Message
193 Warning 10923 Table definition contains obsolete data types such as old temporal or decimal types
194 Warning 1296 Failed to discover table 't1' from NDB, could not upgrade table with extra metadata version 1
195 Error 1146 Table 'test.t1' doesn't exist
196 set time_zone = default;
197 select @@session.time_zone;
198 @@session.time_zone
199 SYSTEM