Debian Codegraph @

mysql-test/suite/ndb/t/ndb_endian.test

raw
1 --source include/have_ndb.inc
2
3 ##############################################
4 # Test suite intended to endianness related issues
5
6
7
8 ##########################################
9 # Bug #29010641
10 # FAILS TO HANDLE DIFFERENT ENDIANNESS FORMATS IN PUSHED JOIN KEYS
11 #
12 # A big endian platform specific bug (SPARC)
13 #
14 # Test pushed joins where Field values from a previous
15 # InnoDb table is referred from the key of a child table
16 # within the pushed join.
17 # This introduce possible endianness problems, ...
18 #
19 # All test cases below use the same pattern:
20 # - A 3 table join, where the first table is an InnoDb table.
21 # - The 2 other tables are NDB cluster tables forming
22 # a pushed join.
23 # - The join key of the last (child-) table, refers a Field
24 # value from the InnoDb table.
25 #
26 # On big endian platforms, we may have different endianness
27 # on the InnoDb and NDB cluster tables. This has to be correctly
28 # handled when generating the NDB Cluster key from the InnoDb Field.
29 # All endian sensitive data types, pluss some more, are checked below.
30 #
31 # NOTE: A big test case as lots of data types had to be tested.
32 #########################################
33
34 create table t1_innodb (
35 t1 tinyint,
36 t1u tinyint unsigned,
37 s1 smallint,
38 s1u smallint unsigned,
39 m1 mediumint,
40 m1u mediumint unsigned,
41 i1 int,
42 i1u int unsigned,
43 b1 bigint,
44 b1u bigint unsigned,
45 f1 float,
46 d1 double,
47 dec1 decimal(10,4),
48 dec2 decimal(10,2)
49 ) engine=innodb;
50
51 insert into t1_innodb values (1,1,1,1,1,1,1,1,1,1,
52 1234.5678, 9876.21,
53 1234.5678, 9876.21);
54
55 create table t1 (
56 t1 tinyint,
57 t1u tinyint unsigned,
58 s1 smallint,
59 s1u smallint unsigned,
60 m1 mediumint,
61 m1u mediumint unsigned,
62 i1 int,
63 i1u int unsigned,
64 b1 bigint,
65 b1u bigint unsigned,
66 f1 float,
67 d1 double,
68 dec1 decimal(10,4),
69 dec2 decimal(10,2)
70 ) engine=ndbcluster;
71
72 insert into t1 values (1,1,1,1,1,1,1,1,1,1,
73 1234.5678, 9876.21,
74 1234.5678, 9876.21);
75
76 # Disable BNL to encourage usage of pushed join
77 set @@optimizer_switch='block_nested_loop=off';
78
79
80 #######
81 create index ix on t1(t1,t1u);
82 let $query =
83 select count(*)
84 from t1_innodb as t1
85 straight_join t1 as t2
86 straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t1u = t1.t1u;
87
88 eval explain $query;
89 eval $query;
90
91 let $query =
92 select count(*)
93 from t1_innodb as t1
94 straight_join t1 as t2
95 straight_join t1 as t3 on t3.t1u = t2.t1u and t3.t1 = t1.t1;
96
97 eval explain $query;
98 eval $query;
99
100 alter table t1 drop index ix;
101
102 #######
103
104 create index ix on t1(s1,s1u);
105 let $query =
106 select count(*)
107 from t1_innodb as t1
108 straight_join t1 as t2
109 straight_join t1 as t3 on t3.s1 = t2.s1 and t3.s1u = t1.s1u;
110
111 eval explain $query;
112 eval $query;
113
114 let $query =
115 select count(*)
116 from t1_innodb as t1
117 straight_join t1 as t2
118 straight_join t1 as t3 on t3.s1u = t2.s1u and t3.s1 = t1.s1;
119
120 eval explain $query;
121 eval $query;
122
123 alter table t1 drop index ix;
124
125 #######
126
127 create index ix on t1(m1,m1u);
128 let $query =
129 select count(*)
130 from t1_innodb as t1
131 straight_join t1 as t2
132 straight_join t1 as t3 on t3.m1 = t2.m1 and t3.m1u = t1.m1u;
133
134 eval explain $query;
135 eval $query;
136
137 let $query =
138 select count(*)
139 from t1_innodb as t1
140 straight_join t1 as t2
141 straight_join t1 as t3 on t3.m1u = t2.m1u and t3.m1 = t1.m1;
142
143 eval explain $query;
144 eval $query;
145
146 alter table t1 drop index ix;
147
148 #######
149
150 create index ix on t1(i1,i1u);
151 let $query =
152 select count(*)
153 from t1_innodb as t1
154 straight_join t1 as t2
155 straight_join t1 as t3 on t3.i1 = t2.i1 and t3.i1u = t1.i1u;
156
157 eval explain $query;
158 eval $query;
159
160 let $query =
161 select count(*)
162 from t1_innodb as t1
163 straight_join t1 as t2
164 straight_join t1 as t3 on t3.i1u = t2.i1u and t3.i1 = t1.i1;
165
166 eval explain $query;
167 eval $query;
168
169 alter table t1 drop index ix;
170
171 #######
172
173 create index ix on t1(b1,b1u);
174 let $query =
175 select count(*)
176 from t1_innodb as t1
177 straight_join t1 as t2
178 straight_join t1 as t3 on t3.b1 = t2.b1 and t3.b1u = t1.b1u;
179
180 eval explain $query;
181 eval $query;
182
183 let $query =
184 select count(*)
185 from t1_innodb as t1
186 straight_join t1 as t2
187 straight_join t1 as t3 on t3.b1u = t2.b1u and t3.b1 = t1.b1;
188
189 eval explain $query;
190 eval $query;
191
192 alter table t1 drop index ix;
193
194 #######
195
196 create index ix on t1(f1,d1);
197 let $query =
198 select count(*)
199 from t1_innodb as t1
200 straight_join t1 as t2
201 straight_join t1 as t3 on t3.f1 = t2.f1 and t3.d1 = t1.d1;
202
203 eval explain $query;
204 eval $query;
205
206 let $query =
207 select count(*)
208 from t1_innodb as t1
209 straight_join t1 as t2
210 straight_join t1 as t3 on t3.d1 = t2.d1 and t3.f1 = t1.f1;
211
212 eval explain $query;
213 eval $query;
214
215 alter table t1 drop index ix;
216
217 #######
218
219 create index ix on t1(dec1,dec2);
220 let $query =
221 select count(*)
222 from t1_innodb as t1
223 straight_join t1 as t2
224 straight_join t1 as t3 on t3.dec1 = t2.dec1 and t3.dec2 = t1.dec2;
225
226 eval explain $query;
227 eval $query;
228
229 let $query =
230 select count(*)
231 from t1_innodb as t1
232 straight_join t1 as t2
233 straight_join t1 as t3 on t3.dec2 = t2.dec2 and t3.dec1 = t1.dec1;
234
235 eval explain $query;
236 eval $query;
237
238 alter table t1 drop index ix;
239 #######
240
241 drop table t1;
242 drop table t1_innodb;
243
244
245 #############
246 # Test character types
247 #############
248
249 create table t1_innodb (
250 c1 char,
251 c2 char(16),
252 vc1 varchar(32),
253 vc2 varchar(512)
254 ) engine=innodb;
255
256 insert into t1_innodb values ('a', 'abc....xyz', 'abcdefgd12434545...xyc',
257 'A long varchar, not that long really...');
258
259
260 create table t1 (
261 c1 char,
262 c2 char(16),
263 vc1 varchar(32),
264 vc2 varchar(512)
265 ) engine=ndbcluster;
266
267 insert into t1 values ('a', 'abc....xyz', 'abcdefgd12434545...xyc',
268 'A long varchar, not that long really...');
269
270 #######
271 create index ix on t1(c1,c2);
272 let $query =
273 select count(*)
274 from t1_innodb as t1
275 straight_join t1 as t2
276 straight_join t1 as t3 on t3.c1 = t2.c1 and t3.c2 = t1.c2;
277
278 eval explain $query;
279 eval $query;
280
281 let $query =
282 select count(*)
283 from t1_innodb as t1
284 straight_join t1 as t2
285 straight_join t1 as t3 on t3.c2 = t2.c2 and t3.c1 = t1.c1;
286
287 eval explain $query;
288 eval $query;
289
290 alter table t1 drop index ix;
291
292 #######
293
294 create index ix on t1(vc1,vc2);
295 let $query =
296 select count(*)
297 from t1_innodb as t1
298 straight_join t1 as t2
299 straight_join t1 as t3 on t3.vc1 = t2.vc1 and t3.vc2 = t1.vc2;
300
301 eval explain $query;
302 eval $query;
303
304 let $query =
305 select count(*)
306 from t1_innodb as t1
307 straight_join t1 as t2
308 straight_join t1 as t3 on t3.vc2 = t2.vc2 and t3.vc1 = t1.vc1;
309
310 eval explain $query;
311 eval $query;
312
313 alter table t1 drop index ix;
314 #######
315
316 drop table t1;
317 drop table t1_innodb;
318
319
320 #############
321 # Test temporal types
322 #############
323
324 create table t1_innodb (
325 y1 year,
326 y2 year(4),
327 d1 date,
328 d2 date,
329 t1 time,
330 t2 time(0),
331 t3 time(6),
332 dt1 datetime,
333 dt2 datetime(0),
334 dt3 datetime(6),
335 ts1 timestamp,
336 ts2 timestamp(0),
337 ts3 timestamp(6)
338 ) engine=innodb;
339
340 insert into t1_innodb values (99, 2018, '2018-12-04', '2005-01-26',
341 '12:00:00', '11:59:59', '11:59:59.999',
342 '2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123',
343 '2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123'
344 );
345
346 create table t1 (
347 y1 year,
348 y2 year(4),
349 d1 date,
350 d2 date,
351 t1 time,
352 t2 time(0),
353 t3 time(6),
354 dt1 datetime,
355 dt2 datetime(0),
356 dt3 datetime(6),
357 ts1 timestamp,
358 ts2 timestamp(0),
359 ts3 timestamp(6)
360 ) engine=ndbcluster;
361
362 insert into t1 values (99, 2018, '2018-12-04', '2005-01-26',
363 '12:00:00', '11:59:59', '11:59:59.999',
364 '2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123',
365 '2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123'
366 );
367
368
369 #######
370
371 create index ix on t1(y1,y2);
372 let $query =
373 select count(*)
374 from t1_innodb as t1
375 straight_join t1 as t2
376 straight_join t1 as t3 on t3.y1 = t2.y1 and t3.y2 = t1.y2;
377
378 eval explain $query;
379 eval $query;
380
381 let $query =
382 select count(*)
383 from t1_innodb as t1
384 straight_join t1 as t2
385 straight_join t1 as t3 on t3.y2 = t2.y2 and t3.y1 = t1.y1;
386
387 eval explain $query;
388 eval $query;
389
390 alter table t1 drop index ix;
391
392 #######
393
394 create index ix on t1(d1,d2);
395 let $query =
396 select count(*)
397 from t1_innodb as t1
398 straight_join t1 as t2
399 straight_join t1 as t3 on t3.d1 = t2.d1 and t3.d2 = t1.d2;
400
401 eval explain $query;
402 eval $query;
403
404 alter table t1 drop index ix;
405
406 #######
407
408 create index ix on t1(t1,t2,t3);
409 let $query =
410 select count(*)
411 from t1_innodb as t1
412 straight_join t1 as t2
413 straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t2 = t1.t2;
414
415 eval explain $query;
416 eval $query;
417
418 let $query =
419 select count(*)
420 from t1_innodb as t1
421 straight_join t1 as t2
422 straight_join t1 as t3 on t3.t2 = t2.t2 and t3.t1 = t1.t1;
423
424 eval explain $query;
425 eval $query;
426
427 let $query =
428 select count(*)
429 from t1_innodb as t1
430 straight_join t1 as t2
431 straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t2 = t2.t2 and t3.t3 = t1.t3;
432
433 eval explain $query;
434 eval $query;
435
436 alter table t1 drop index ix;
437
438 #######
439
440 create index ix on t1(dt1,dt2,dt3);
441 let $query =
442 select count(*)
443 from t1_innodb as t1
444 straight_join t1 as t2
445 straight_join t1 as t3 on t3.dt1 = t2.dt1 and t3.dt2 = t1.dt2;
446
447 eval explain $query;
448 eval $query;
449
450 let $query =
451 select count(*)
452 from t1_innodb as t1
453 straight_join t1 as t2
454 straight_join t1 as t3 on t3.dt2 = t2.dt2 and t3.dt1 = t1.dt1;
455
456 eval explain $query;
457 eval $query;
458
459 let $query =
460 select count(*)
461 from t1_innodb as t1
462 straight_join t1 as t2
463 straight_join t1 as t3 on t3.dt1 = t2.dt1 and t3.dt2 = t2.dt2 and t3.dt3 = t1.dt3;
464
465 eval explain $query;
466 eval $query;
467
468 alter table t1 drop index ix;
469
470 #######
471
472 create index ix on t1(ts1,ts2,ts3);
473 let $query =
474 select count(*)
475 from t1_innodb as t1
476 straight_join t1 as t2
477 straight_join t1 as t3 on t3.ts1 = t2.ts1 and t3.ts2 = t1.ts2;
478
479 eval explain $query;
480 eval $query;
481
482 let $query =
483 select count(*)
484 from t1_innodb as t1
485 straight_join t1 as t2
486 straight_join t1 as t3 on t3.ts2 = t2.ts2 and t3.ts1 = t1.ts1;
487
488 eval explain $query;
489 eval $query;
490
491 let $query =
492 select count(*)
493 from t1_innodb as t1
494 straight_join t1 as t2
495 straight_join t1 as t3 on t3.ts1 = t2.ts1 and t3.ts2 = t2.ts2 and t3.ts3 = t1.ts3;
496
497 eval explain $query;
498 eval $query;
499
500 alter table t1 drop index ix;
501 #######
502
503 drop table t1;
504 drop table t1_innodb;
505
506 set @@optimizer_switch='block_nested_loop=default';
507
508 ##
509 ##
510 ## End large testcase for bug#29010641
511 ##
512 ##############################