513 lines
9.9 KiB
Plaintext
513 lines
9.9 KiB
Plaintext
--source include/have_ndb.inc
|
|
|
|
##############################################
|
|
# Test suite intended to endianness related issues
|
|
|
|
|
|
|
|
##########################################
|
|
# Bug #29010641
|
|
# FAILS TO HANDLE DIFFERENT ENDIANNESS FORMATS IN PUSHED JOIN KEYS
|
|
#
|
|
# A big endian platform specific bug (SPARC)
|
|
#
|
|
# Test pushed joins where Field values from a previous
|
|
# InnoDb table is referred from the key of a child table
|
|
# within the pushed join.
|
|
# This introduce possible endianness problems, ...
|
|
#
|
|
# All test cases below use the same pattern:
|
|
# - A 3 table join, where the first table is an InnoDb table.
|
|
# - The 2 other tables are NDB cluster tables forming
|
|
# a pushed join.
|
|
# - The join key of the last (child-) table, refers a Field
|
|
# value from the InnoDb table.
|
|
#
|
|
# On big endian platforms, we may have different endianness
|
|
# on the InnoDb and NDB cluster tables. This has to be correctly
|
|
# handled when generating the NDB Cluster key from the InnoDb Field.
|
|
# All endian sensitive data types, pluss some more, are checked below.
|
|
#
|
|
# NOTE: A big test case as lots of data types had to be tested.
|
|
#########################################
|
|
|
|
create table t1_innodb (
|
|
t1 tinyint,
|
|
t1u tinyint unsigned,
|
|
s1 smallint,
|
|
s1u smallint unsigned,
|
|
m1 mediumint,
|
|
m1u mediumint unsigned,
|
|
i1 int,
|
|
i1u int unsigned,
|
|
b1 bigint,
|
|
b1u bigint unsigned,
|
|
f1 float,
|
|
d1 double,
|
|
dec1 decimal(10,4),
|
|
dec2 decimal(10,2)
|
|
) engine=innodb;
|
|
|
|
insert into t1_innodb values (1,1,1,1,1,1,1,1,1,1,
|
|
1234.5678, 9876.21,
|
|
1234.5678, 9876.21);
|
|
|
|
create table t1 (
|
|
t1 tinyint,
|
|
t1u tinyint unsigned,
|
|
s1 smallint,
|
|
s1u smallint unsigned,
|
|
m1 mediumint,
|
|
m1u mediumint unsigned,
|
|
i1 int,
|
|
i1u int unsigned,
|
|
b1 bigint,
|
|
b1u bigint unsigned,
|
|
f1 float,
|
|
d1 double,
|
|
dec1 decimal(10,4),
|
|
dec2 decimal(10,2)
|
|
) engine=ndbcluster;
|
|
|
|
insert into t1 values (1,1,1,1,1,1,1,1,1,1,
|
|
1234.5678, 9876.21,
|
|
1234.5678, 9876.21);
|
|
|
|
# Disable BNL to encourage usage of pushed join
|
|
set @@optimizer_switch='block_nested_loop=off';
|
|
|
|
|
|
#######
|
|
create index ix on t1(t1,t1u);
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t1u = t1.t1u;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.t1u = t2.t1u and t3.t1 = t1.t1;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
alter table t1 drop index ix;
|
|
|
|
#######
|
|
|
|
create index ix on t1(s1,s1u);
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.s1 = t2.s1 and t3.s1u = t1.s1u;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.s1u = t2.s1u and t3.s1 = t1.s1;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
alter table t1 drop index ix;
|
|
|
|
#######
|
|
|
|
create index ix on t1(m1,m1u);
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.m1 = t2.m1 and t3.m1u = t1.m1u;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.m1u = t2.m1u and t3.m1 = t1.m1;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
alter table t1 drop index ix;
|
|
|
|
#######
|
|
|
|
create index ix on t1(i1,i1u);
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.i1 = t2.i1 and t3.i1u = t1.i1u;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.i1u = t2.i1u and t3.i1 = t1.i1;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
alter table t1 drop index ix;
|
|
|
|
#######
|
|
|
|
create index ix on t1(b1,b1u);
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.b1 = t2.b1 and t3.b1u = t1.b1u;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.b1u = t2.b1u and t3.b1 = t1.b1;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
alter table t1 drop index ix;
|
|
|
|
#######
|
|
|
|
create index ix on t1(f1,d1);
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.f1 = t2.f1 and t3.d1 = t1.d1;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.d1 = t2.d1 and t3.f1 = t1.f1;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
alter table t1 drop index ix;
|
|
|
|
#######
|
|
|
|
create index ix on t1(dec1,dec2);
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.dec1 = t2.dec1 and t3.dec2 = t1.dec2;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.dec2 = t2.dec2 and t3.dec1 = t1.dec1;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
alter table t1 drop index ix;
|
|
#######
|
|
|
|
drop table t1;
|
|
drop table t1_innodb;
|
|
|
|
|
|
#############
|
|
# Test character types
|
|
#############
|
|
|
|
create table t1_innodb (
|
|
c1 char,
|
|
c2 char(16),
|
|
vc1 varchar(32),
|
|
vc2 varchar(512)
|
|
) engine=innodb;
|
|
|
|
insert into t1_innodb values ('a', 'abc....xyz', 'abcdefgd12434545...xyc',
|
|
'A long varchar, not that long really...');
|
|
|
|
|
|
create table t1 (
|
|
c1 char,
|
|
c2 char(16),
|
|
vc1 varchar(32),
|
|
vc2 varchar(512)
|
|
) engine=ndbcluster;
|
|
|
|
insert into t1 values ('a', 'abc....xyz', 'abcdefgd12434545...xyc',
|
|
'A long varchar, not that long really...');
|
|
|
|
#######
|
|
create index ix on t1(c1,c2);
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.c1 = t2.c1 and t3.c2 = t1.c2;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.c2 = t2.c2 and t3.c1 = t1.c1;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
alter table t1 drop index ix;
|
|
|
|
#######
|
|
|
|
create index ix on t1(vc1,vc2);
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.vc1 = t2.vc1 and t3.vc2 = t1.vc2;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.vc2 = t2.vc2 and t3.vc1 = t1.vc1;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
alter table t1 drop index ix;
|
|
#######
|
|
|
|
drop table t1;
|
|
drop table t1_innodb;
|
|
|
|
|
|
#############
|
|
# Test temporal types
|
|
#############
|
|
|
|
create table t1_innodb (
|
|
y1 year,
|
|
y2 year(4),
|
|
d1 date,
|
|
d2 date,
|
|
t1 time,
|
|
t2 time(0),
|
|
t3 time(6),
|
|
dt1 datetime,
|
|
dt2 datetime(0),
|
|
dt3 datetime(6),
|
|
ts1 timestamp,
|
|
ts2 timestamp(0),
|
|
ts3 timestamp(6)
|
|
) engine=innodb;
|
|
|
|
insert into t1_innodb values (99, 2018, '2018-12-04', '2005-01-26',
|
|
'12:00:00', '11:59:59', '11:59:59.999',
|
|
'2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123',
|
|
'2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123'
|
|
);
|
|
|
|
create table t1 (
|
|
y1 year,
|
|
y2 year(4),
|
|
d1 date,
|
|
d2 date,
|
|
t1 time,
|
|
t2 time(0),
|
|
t3 time(6),
|
|
dt1 datetime,
|
|
dt2 datetime(0),
|
|
dt3 datetime(6),
|
|
ts1 timestamp,
|
|
ts2 timestamp(0),
|
|
ts3 timestamp(6)
|
|
) engine=ndbcluster;
|
|
|
|
insert into t1 values (99, 2018, '2018-12-04', '2005-01-26',
|
|
'12:00:00', '11:59:59', '11:59:59.999',
|
|
'2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123',
|
|
'2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123'
|
|
);
|
|
|
|
|
|
#######
|
|
|
|
create index ix on t1(y1,y2);
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.y1 = t2.y1 and t3.y2 = t1.y2;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.y2 = t2.y2 and t3.y1 = t1.y1;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
alter table t1 drop index ix;
|
|
|
|
#######
|
|
|
|
create index ix on t1(d1,d2);
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.d1 = t2.d1 and t3.d2 = t1.d2;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
alter table t1 drop index ix;
|
|
|
|
#######
|
|
|
|
create index ix on t1(t1,t2,t3);
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t2 = t1.t2;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.t2 = t2.t2 and t3.t1 = t1.t1;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t2 = t2.t2 and t3.t3 = t1.t3;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
alter table t1 drop index ix;
|
|
|
|
#######
|
|
|
|
create index ix on t1(dt1,dt2,dt3);
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.dt1 = t2.dt1 and t3.dt2 = t1.dt2;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.dt2 = t2.dt2 and t3.dt1 = t1.dt1;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.dt1 = t2.dt1 and t3.dt2 = t2.dt2 and t3.dt3 = t1.dt3;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
alter table t1 drop index ix;
|
|
|
|
#######
|
|
|
|
create index ix on t1(ts1,ts2,ts3);
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.ts1 = t2.ts1 and t3.ts2 = t1.ts2;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.ts2 = t2.ts2 and t3.ts1 = t1.ts1;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
let $query =
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.ts1 = t2.ts1 and t3.ts2 = t2.ts2 and t3.ts3 = t1.ts3;
|
|
|
|
eval explain $query;
|
|
eval $query;
|
|
|
|
alter table t1 drop index ix;
|
|
#######
|
|
|
|
drop table t1;
|
|
drop table t1_innodb;
|
|
|
|
set @@optimizer_switch='block_nested_loop=default';
|
|
|
|
##
|
|
##
|
|
## End large testcase for bug#29010641
|
|
##
|
|
##############################
|