in mysql-test/suite/ndb/t/ndb_join_pushdown.inc [2701:4394]
select count(*)
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
join t1 as t3 on t3.a = t2.c and t3.b = t2.d;
select count(*)
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
join t1 as t3 on t3.a = t2.c and t3.b = t2.d;
explain
select count(*)
from t1
join t1 as t2 on t2.a = t1.c
join t1 as t3 on t3.a = t2.c and t3.b = t2.d;
select count(*)
from t1
join t1 as t2 on t2.a = t1.c
join t1 as t3 on t3.a = t2.c and t3.b = t2.d;
explain
select count(*)
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
join t1 as t3 on t3.a = t2.c;
select count(*)
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
join t1 as t3 on t3.a = t2.c;
connection ddl;
alter table t1 partition by key(a);
connection spj;
explain
select count(*)
from t1
join t1 as t2 on t2.a = t1.c
join t1 as t3 on t3.a = t2.c and t3.b = t2.d;
select count(*)
from t1
join t1 as t2 on t2.a = t1.c
join t1 as t3 on t3.a = t2.c and t3.b = t2.d;
connection ddl;
drop table t1;
--error 0,1193
set global debug=@save_debug;
# Pushed join accessing disk data.
connection ddl;
create logfile group lg1
add undofile 'undofile.dat'
initial_size 1m
undo_buffer_size = 1m
engine=ndb;
create tablespace ts1
add datafile 'datafile.dat'
use logfile group lg1
initial_size 6m
engine ndb;
create table t1 (a int not null,
b int not null storage disk,
c int not null storage memory,
primary key(a))
tablespace ts1 storage disk engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t1 values (10, 11, 11);
insert into t1 values (11, 12, 12);
insert into t1 values (12, 13, 13);
connection ddl;
create table t2 (a int not null,
b int not null, primary key(a)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t2 values (10, 11);
insert into t2 values (11, 12);
insert into t2 values (12, 13);
# Disk data in projection of first op.
explain select * from t1, t2 where t1.c = t2.a;
--sorted_result
select * from t1, t2 where t1.c = t2.a;
explain select * from t1, t2 where t1.a=11 and t1.c = t2.a;
select * from t1, t2 where t1.a=11 and t1.c = t2.a;
# Disk data in projection of second op.
explain select * from t2, t1 where t2.b = t1.a;
--sorted_result
select * from t2, t1 where t2.b = t1.a;
explain select * from t2, t1 where t2.a=11 and t2.b = t1.a;
select * from t2, t1 where t2.a=11 and t2.b = t1.a;
# Disk data in predicate but not in projection
explain select t1.a, t1.c, t2.a, t2.b from t1, t2 where t1.b = t2.a;
--sorted_result
select t1.a, t1.c, t2.a, t2.b from t1, t2 where t1.b = t2.a;
explain select t1.a, t1.c, t2.a, t2.b from t1, t2 where t1.a=11 and t1.b = t2.a;
select t1.a, t1.c, t2.a, t2.b from t1, t2 where t1.a=11 and t1.b = t2.a;
connection ddl;
drop table t1;
drop table t2;
alter tablespace ts1
drop datafile 'datafile.dat'
engine ndb;
drop tablespace ts1
engine ndb;
drop logfile group lg1
engine ndb;
connection spj;
# Store old counter values.
connection ddl;
create temporary table old_count
select counter_name, sum(val) as val
from ndbinfo.counters
where block_name='DBSPJ'
group by counter_name;
connection ddl;
# Specify number of partition to be independent of configured #LDM's
create table t1 (a int not null,
b int not null,
c int not null,
primary key(a))
engine = ndb
partition by key() partitions 8;
connection spj;
# We use key values that have the same representation in little and big endian.
# Otherwise, the numbers for local and remote reads may depend on endian-ness,
# since hashing is endian dependent.
insert into t1 values (1, 2, 2);
insert into t1 values (2, 3, 3);
insert into t1 values (3, 4, 4);
# Run some queries that should increment the counters.
select * from t1 t1, t1 t2 where t1.a = 2 and t2.a = t1.b;
select count(*) from t1 t1, t1 t2 where t2.a = t1.b;
select count(*) from t1 t1, t1 t2 where t1.a >= 2 and t2.a = t1.b;
# Get new counter values.
connection ddl;
create temporary table new_count
select counter_name, sum(val) as val
from ndbinfo.counters
where block_name='DBSPJ'
group by counter_name;
# Compute the difference.
--sorted_result
select new_count.counter_name, new_count.val - old_count.val
from new_count, old_count
where new_count.counter_name = old_count.counter_name
and new_count.counter_name <> 'LOCAL_READS_SENT'
and new_count.counter_name <> 'REMOTE_READS_SENT';
select 'READS_SENT', sum(new_count.val - old_count.val)
from new_count, old_count
where new_count.counter_name = old_count.counter_name
and (new_count.counter_name = 'LOCAL_READS_SENT'
or new_count.counter_name = 'REMOTE_READS_SENT');
connection ddl;
drop table old_count;
drop table new_count;
drop table t1;
### Test that scan filters are used for pushed operations.
connection ddl;
create table t1 (
a int primary key,
b int,
c int) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t1 values (1, 2, 3);
insert into t1 values (2, 3, 4);
insert into t1 values (3, 4, 5);
# Find the total number of lookups issued by the SPJ blocks.
let $spj_lookups = query_get_value(select sum(val) as Value from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT'), Value, 1);
# Root scan should give only one tuple if scan filter is pushed.
# Therefore only one lookup on 'y'.
--replace_column 10 # 11 #
explain select * from t1 x, t1 y where x.b=y.a and x.c=4;
select * from t1 x, t1 y where x.b=y.a and x.c=4;
--disable_query_log
--eval select sum(val) - $spj_lookups as lookups from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT')
--enable_query_log
# Lookup on y should only give one result tuple if filter is pushed.
# This should give 3 lookups on 'y' and 1 on 'z', 4 in all.
--replace_column 10 # 11 #
explain select * from t1 x, t1 y, t1 z where x.b=y.a and y.c=4 and y.b=z.a;
select * from t1 x, t1 y, t1 z where x.b=y.a and y.c=4 and y.b=z.a;
--disable_query_log
--eval select sum(val) - $spj_lookups as lookups from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT')
--enable_query_log
connection ddl;
drop table t1;
# Test and server status variables (i.e. mysqld counters)
connection ddl;
create table t1(
a int not null,
b int not null,
c int not null,
primary key(a,b))
engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"
partition by key (a);
connection spj;
insert into t1 values (10, 10, 11);
insert into t1 values (11, 11, 12);
insert into t1 values (12, 12, 13);
# First query against a new table causes an extra scan (of a dictionaty table??)
# so adding an extra scan here to make results from the following part easier
# to interpret.
select * from t1 t1, t1 t2
where t1.a = 10 and t1.b = 10 and
t2.a = t1.c and t2.b = t1.c;
# Save old counter values.
# Save old mysqld counter values.
--disable_warnings ONCE
create temporary table server_counts
select * from performance_schema.global_status
where variable_name in
('Ndb_scan_count',
'Ndb_pruned_scan_count',
'Ndb_sorted_scan_count',
'Ndb_pushed_queries_defined',
'Ndb_pushed_queries_dropped',
'Ndb_pushed_reads');
# Run some queries that should increment the counters.
# This query should push a single read.
select * from t1 t1, t1 t2
where t1.a = 11 and t1.b = 11 and
t2.a = t1.c and t2.b = t1.c;
# This query should push a sorted scan (and three reads).
select * from t1 t1, t1 t2
where t2.a = t1.c and t2.b = t1.c
order by t1.a;
# This query should push a pruned scan (but pruning must be fixed for
# pushed scans.)
select count(*) from t1 t1, t1 t2
where t1.a = 11 and
t2.a = t1.c and t2.b = t1.c;
# Calculate the change in mysqld counters.
--disable_warnings ONCE
select new.variable_name, new.variable_value - old.variable_value
from server_counts as old,
performance_schema.global_status as new
where new.variable_name = old.variable_name
order by new.variable_name;
drop table server_counts;
connection ddl;
drop table t1;
# Test scan pruning
connection ddl;
create table t1(
d int not null,
c int not null,
a int not null,
b int not null,
primary key using hash (a,b))
engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"
partition by key (a);
connection spj;
insert into t1(a,b,c,d) values (10, 10, 11, 11);
insert into t1(a,b,c,d) values (11, 11, 12, 12);
insert into t1(a,b,c,d) values (12, 12, 13, 13);
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
# Should give pruned scan.
connection ddl;
create index i1 on t1(c,a);
connection spj;
select count(*) from t1 t1, t1 t2 where t1.c = 12 and t1.a = 11 and t2.a = t1.d and t2.b = t1.d;
connection ddl;
drop index i1 on t1;
connection spj;
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--enable_query_log
# Should give pruned scan. There is a one sided limit for t1.b, but this is
# after the partition key prefix.
connection ddl;
create index i2 on t1(a,b);
connection spj;
select count(*) from t1 t1, t1 t2 where t1.a = 11 and t1.b<13 and t2.a = t1.c and t2.b = t1.c;
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--enable_query_log
# Should give pruned scan. Upper and lower bounds for t1.a are the sane.
select count(*) from t1 t1, t1 t2 where t1.a >= 12 and t1.a<=12 and t2.a = t1.c and t2.b = t1.c;
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--enable_query_log
# Should not give pruned scan. Upper and lower bounds for t1.a are different.
select count(*) from t1 t1, t1 t2 where t1.a >= 11 and t1.a<=12 and t2.a = t1.c and t2.b = t1.c;
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--enable_query_log
# Should not give pruned scan. There will be two sets of bounds that have
# different distribution keys (t1.a=10 and t1.a=12).
select count(*) from t1 t1, t1 t2 where (t1.a = 10 or t1.a=12) and t1.b<13 and t2.a = t1.c and t2.b = t1.c;
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--enable_query_log
# Should give pruned scan. There will be two sets of bounds, but they have the
# same distribution key.
select count(*) from t1 t1, t1 t2 where t1.a = 10 and (t1.b<11 or t1.b>11) and t2.a = t1.c and t2.b = t1.c;
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--enable_query_log
connection ddl;
drop table t1;
create table t2(
d int not null,
e int not null,
f int not null,
a int not null,
b int not null,
c int not null,
primary key using hash (a,b,c))
engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"
partition by key (b,a);
connection spj;
insert into t2(a,b,c,d,e,f) values (1, 2, 3, 1, 2, 3);
insert into t2(a,b,c,d,e,f) values (1, 2, 4, 1, 2, 3);
insert into t2(a,b,c,d,e,f) values (2, 3, 4, 1, 2, 3);
insert into t2(a,b,c,d,e,f) values (3, 4, 5, 1, 2, 3);
insert into t2(a,b,c,d,e,f) values (4, 5, 6, 1, 2, 3);
insert into t2(a,b,c,d,e,f) values (5, 6, 7, 1, 2, 3);
insert into t2(a,b,c,d,e,f) values (6, 7, 8, 1, 2, 3);
insert into t2(a,b,c,d,e,f) values (7, 8, 9, 1, 2, 3);
connection ddl;
create index i2_1 on t2(d, a, b, e);
connection spj;
# Should give pruned scan. The index prefix containing the distribution key
# has a single possible value.
select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3;
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--enable_query_log
connection ddl;
drop index i2_1 on t2;
create index i2_3 on t2(a, d, b, e);
connection spj;
# Should give pruned scan. The index prefix containing the distribution key
# has a single possible value.
# Turn off condition_fanout_filter to keep "old" QEP
set optimizer_switch='condition_fanout_filter=off';
select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3;
# Restore condition_fanout_filter to default value
set optimizer_switch='condition_fanout_filter=default';
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
--enable_query_log
connection ddl;
drop table t2;
connection ddl;
create table t1 (a binary(10) primary key, b binary(10) not null) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t1 values ('\0123456789', '1234567890');
insert into t1 values ('1234567890', '\0123456789');
explain
select count(*)
from t1 join t1 as t2 on t2.a = t1.b
where t1.a = '\0123456789';
select count(*)
from t1 join t1 as t2 on t2.a = t1.b
where t1.a = '\0123456789';
connection ddl;
drop table t1;
# Tests for some bugfixes which have been cherry picked from 5.1 main
# Not necessarily test of SPJ functionality, but may test optimizer
# behaviour which we depend on when doing RQG testing
# We can remove these testcases when fixes - and propper MTR testcases -
# Have been merged from 5.1 main branch.
connection ddl;
create table t1 (pk int primary key, a int unique key) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t1 values (1,10), (2,20), (3,30);
set ndb_join_pushdown = false;
# Bug#53334:
# Join should be optimized as 'Impossible On condition'
# ... *not* 'Impossible WHERE'
explain
select * from t1 as x right join t1 as y
on x.pk = y.pk
and x.pk = y.a
and x.a = y.pk
where y.pk = 2;
select * from t1 as x right join t1 as y
on x.pk = y.pk
and x.pk = y.a
and x.a = y.pk
where y.pk = 2;
set ndb_join_pushdown = true;
explain
select * from t1 as x right join t1 as y
on x.pk = y.pk
and x.pk = y.a
and x.a = y.pk
where y.pk = 2;
select * from t1 as x right join t1 as y
on x.pk = y.pk
and x.pk = y.a
and x.a = y.pk
where y.pk = 2;
connection ddl;
drop table t1;
#########################################
# Test section for scan-child operations
#########################################
# Test scan-lookup-scan query (see http://lists.mysql.com/commits/115164)
connection ddl;
create table t1 (pk int primary key, u int not null, a int, b int) engine=ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
create index ix1 on t1(b,a);
connection spj;
insert into t1 values (0,1,10,20);
insert into t1 values (1,2,20,30);
insert into t1 values (2,3,30,40);
--replace_column 10 # 11 #
explain select * from t1 as x join t1 as y join t1 as z on x.u=y.pk and y.a=z.b;
--sorted_result
select * from t1 as x join t1 as y join t1 as z on x.u=y.pk and y.a=z.b;
connection ddl;
drop table t1;
# Test sorted scan where inner join eliminates all rows (known regression).
connection ddl;
create table t1 (pk int primary key, u int not null) engine=ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t1 values (0,-1), (1,-1), (2,-1), (3,-1), (4,-1), (5,-1), (6,-1),
(7,-1), (8,-1), (9,-1), (10,-1), (11,-1), (12,-1), (13,-1), (14,-1), (15,-1),
(16,-1), (17,-1), (18,-1), (19,-1), (20,-1), (21,-1), (22,-1), (23,-1),
(24,-1), (25,-1), (26,-1), (27,-1), (28,-1), (29,-1), (30,-1), (31,-1),
(32,-1), (33,-1), (34,-1), (35,-1), (36,-1), (37,-1), (38,-1), (39,-1),
(40,-1), (41,-1), (42,-1), (43,-1), (44,-1), (45,-1), (46,-1), (47,-1),
(48,-1), (49,-1), (50,-1), (51,-1), (52,-1), (53,-1), (54,-1), (55,-1),
(56,-1), (57,-1), (58,-1), (59,-1), (60,-1), (61,-1), (62,-1), (63,-1),
(64,-1), (65,-1), (66,-1), (67,-1), (68,-1), (69,-1), (70,-1), (71,-1),
(72,-1), (73,-1), (74,-1), (75,-1), (76,-1), (77,-1), (78,-1), (79,-1),
(80,-1), (81,-1), (82,-1), (83,-1), (84,-1), (85,-1), (86,-1), (87,-1),
(88,-1), (89,-1), (90,-1), (91,-1), (92,-1), (93,-1), (94,-1), (95,-1),
(96,-1), (97,-1), (98,-1), (99,-1), (100,-1), (101,-1), (102,-1), (103,-1),
(104,-1), (105,-1), (106,-1), (107,-1), (108,-1), (109,-1), (110,-1),
(111,-1), (112,-1), (113,-1), (114,-1), (115,-1), (116,-1), (117,-1),
(118,-1), (119,-1), (120,-1), (121,-1), (122,-1), (123,-1), (124,-1),
(125,-1), (126,-1), (127,-1), (128,-1), (129,-1), (130,-1), (131,-1),
(132,-1), (133,-1), (134,-1), (135,-1), (136,-1), (137,-1), (138,-1), (139,-1);
--error 0,1193
set global debug='+d,max_64rows_in_spj_batches';
explain select * from t1 as x join t1 as y on x.u=y.pk order by(x.pk);
select * from t1 as x join t1 as y on x.u=y.pk order by(x.pk);
--error 0,1193
set global debug=@save_debug;
connection ddl;
drop table t1;
# Test query using "scan -> unique index lookup -> index scan".
connection ddl;
create table t1 (pk int primary key, u int not null, a int, b int) engine=ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
create index ix1 on t1(b,a);
create unique index ix2 on t1(u);
connection spj;
insert into t1 values (0,0,10,10);
insert into t1 values (1,1,10,10);
insert into t1 values (2,2,10,10);
insert into t1 values (3,3,10,10);
insert into t1 values (4,4,10,10);
insert into t1 values (5,5,10,10);
insert into t1 values (6,6,10,10);
insert into t1 values (7,7,10,10);
insert into t1 values (8,8,10,10);
insert into t1 values (9,9,10,10);
insert into t1 values (10,10,10,10);
insert into t1 values (11,11,10,10);
explain select count(*) from t1 as x1 join t1 as x2 join t1 as x3
on x1.a=x2.u and x2.a = x3.b;
select count(*) from t1 as x1 join t1 as x2 join t1 as x3
on x1.a=x2.u and x2.a = x3.b;
explain select count(*) from t1 as x1, t1 as x2, t1 as x3
where x1.u=x2.pk and x1.a=x3.b;
select count(*) from t1 as x1, t1 as x2, t1 as x3
where x1.u=x2.pk and x1.a=x3.b;
# Regression test for commit http://lists.mysql.com/commits/116372
# (missing rows in left join query with multiple result batches).
insert into t1 values (12,12,20,10);
explain select count(*) from t1 as x1 left join t1 as x2 on x1.a=x2.b;
select count(*) from t1 as x1 left join t1 as x2 on x1.a=x2.b;
set ndb_join_pushdown=off;
select count(*) from t1 as x1 left join t1 as x2 on x1.a=x2.b;
set ndb_join_pushdown=on;
# Test left join with mix of scan and lookup.
explain select count(*) from t1 as x1
left join t1 as x2 on x1.u=x2.pk
left join t1 as x3 on x2.a=x3.b;
select count(*) from t1 as x1
left join t1 as x2 on x1.u=x2.pk
left join t1 as x3 on x2.a=x3.b;
set ndb_join_pushdown=off;
select count(*) from t1 as x1
left join t1 as x2 on x1.u=x2.pk
left join t1 as x3 on x2.a=x3.b;
set ndb_join_pushdown=on;
explain select count(*) from t1 as x1
left join t1 as x2 on x1.u=x2.pk
left join t1 as x3 on x2.a=x3.b
left join t1 as x4 on x3.u=x4.pk
left join t1 as x5 on x4.a=x5.b;
select count(*) from t1 as x1
left join t1 as x2 on x1.u=x2.pk
left join t1 as x3 on x2.a=x3.b
left join t1 as x4 on x3.u=x4.pk
left join t1 as x5 on x4.a=x5.b;
set ndb_join_pushdown=off;
select count(*) from t1 as x1
left join t1 as x2 on x1.u=x2.pk
left join t1 as x3 on x2.a=x3.b
left join t1 as x4 on x3.u=x4.pk
left join t1 as x5 on x4.a=x5.b;
set ndb_join_pushdown=on;
############################
# Testcase for 'Got error 20002 'Unknown error code' from NDBCLUSTER'
# Caused by failure to identify AT_MULTI_PRIMARY_KEY as a lookup operation.
# This in turn caused a pushed 'lookup-scan' query to be produced - which we don't support
# Should not be pushed (lookup-scan query)
explain select count(*) from t1 as x1
join t1 as x2 on x1.a=x2.b
where x1.pk = 1 or x1.u=1;
select count(*) from t1 as x1
join t1 as x2 on x1.a=x2.b
where x1.pk = 1 or x1.u=1;
############################
# Testcase which forced us to ditch using the 'global cursor'
# on the NdbQuery result set from mysqld.
#
# As the global cursor will fool mysqld into handling the resultset
# as a result from a scan - n*lookup query, incorrect cardinality on the
# parent operation was perceived. Which caused extra null-joined outer rows
# to be emitted in this testcase.
#
# Refactored handler interface and SPJ API use subcursor on each operation
# which correctly preserves the dependency between the parent subscans
# and its child(s).
--error 0,1193
set global debug='+d,max_4rows_in_spj_batches';
set ndb_join_pushdown=on;
explain
select straight_join * from t1 as table1
left join
(t1 as table2 join t1 as table3 on table2.pk = table3.b)
on table1.pk = table2.b;
--sorted_result
select straight_join * from t1 as table1
left join
(t1 as table2 join t1 as table3 on table2.pk = table3.b)
on table1.pk = table2.b;
--error 0,1193
set global debug=@save_debug;
#############
# Testcase for 'sledgehammer' fix for scan -> outer join scan:
# Pushing of outer joined has to be dissabled as incomplete child batches
# may cause the parent row to be returned multiple times:
# Push scan-scan when inner joined
explain select straight_join * from t1 as x1
inner join t1 as x2 on x2.b = x1.a;
# Outer joined scans are not pushed.
explain select straight_join * from t1 as x1
left join t1 as x2 on x2.b = x1.a;
explain select straight_join * from t1 as x1
right join t1 as x2 on x2.b = x1.a;
# If there is a lookup operation(s) inbetween the scans
# pushing is disabled if any of these are outer joined
# inner joined lookups, push allowed
explain select straight_join * from
t1 as x1 inner join
(t1 as x2 inner join t1 as x3 on x3.b = x2.a)
on x2.pk = x1.a;
# Even if x3 is inner joined with x2 (lookup)
# push is dissabled as x2 is outer joined with embedding scan operation
# which makes join relation between the scans on x1 & x3 an 'indirect' outer join
explain select straight_join * from
t1 as x1 left join
(t1 as x2 inner join t1 as x3 on x3.b = x2.a)
on x2.pk = x1.a;
#############
# Test bushy-scans:
# These should be allowed to be executed in 'parallel', depending on
# only the root operation
#
explain select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.b;
set ndb_join_pushdown=off;
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.b;
set ndb_join_pushdown=on;
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.b;
# A really bushy scan - would take almost forever to execute if
# we had to force the child scan to be non-bushy (serialized by
# adding artificial parent dependencies)
#
--replace_column 10 # 11 #
explain select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.a
join t1 as x4 on x4.b = x1.a
join t1 as x5 on x5.b = x1.a
join t1 as x6 on x6.b = x1.a
join t1 as x7 on x7.b = x1.a
where x3.a < x2.pk and x4.a < x3.pk;
# set '64rows' in order to avoid to small batches which will
# cause all subscans to be repeated... and repeated... and
--error 0,1193
set global debug='+d,max_64rows_in_spj_batches';
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.a
join t1 as x4 on x4.b = x1.a
join t1 as x5 on x5.b = x1.a
join t1 as x6 on x6.b = x1.a
join t1 as x7 on x7.b = x1.a
where x3.a < x2.pk and x4.a < x3.pk;
--error 0,1193
set global debug=@save_debug;
#############
# If we have an outer join, we can't create an artificial dep. 'through' the outer join.
# In this case the child scan can't be part of the pushed query.
#
explain select straight_join count(*) from t1 as x1
left join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.b;
set ndb_join_pushdown=off;
select straight_join count(*) from t1 as x1
left join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.b;
set ndb_join_pushdown=on;
select straight_join count(*) from t1 as x1
left join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.b;
############
# However, When the scanchild itself is an outer join, we *can* push that scan operation
#
explain select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
left join t1 as x3 on x3.b = x1.b;
set ndb_join_pushdown=off;
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
left join t1 as x3 on x3.b = x1.b;
set ndb_join_pushdown=on;
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
left join t1 as x3 on x3.b = x1.b;
##############
# If we have a bushy lookup, with scandescendants depending on these lookups,
# the query is 'scan-bushy' through these lookups.
#
# Bushy execution is expected for these scans (x2 & x4) wrt. root (x1)
#
explain
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
set ndb_join_pushdown=off;
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
set ndb_join_pushdown=on;
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
#############
# Test bushy lookups + 1scan,
# (Regression test for previous commit: http://lists.mysql.com/commits/117571)
# Repeatable child rangescan with same parent should be allowed to be in
# 'm_iterState != Iter_finished' if the child row didn't exist (outer join):
explain select straight_join count(*) from t1 as x1
left join t1 as x3 on x3.b = x1.a
join t1 as x2 on x2.pk = x1.a;
select straight_join count(*) from t1 as x1
left join t1 as x3 on x3.b = x1.a
join t1 as x2 on x2.pk = x1.a;
# Modify rows to force null rows from outer join
update t1 set b=b+10;
select straight_join count(*) from t1 as x1
left join t1 as x3 on x3.b = x1.a
join t1 as x2 on x2.pk = x1.a;
#Undo update
update t1 set b=b-10;
##############
# Testcase for: http://lists.mysql.com/commits/118917
# There used to be a bug in SPJ API resulthandling of incomplete
# child batches where we tested for incomplete fetch for any
# childs (in a bushy scan) instead if this particular child batch
# being incomplete.
#
# In this testcase (x inner join y) will have incomplete
# childbatches, while (x left join z) will be complete.
# Modify rows to force null rows from lookup(z) below
update t1 set u=u+100;
set ndb_join_pushdown=on;
--error 0,1193
set global debug='+d,max_64rows_in_spj_batches';
explain select straight_join count(*) from
(t1 as x join t1 as y on y.b = x.a)
left outer join t1 as z on z.u = x.a;
select straight_join count(*) from
(t1 as x join t1 as y on y.b = x.a)
left outer join t1 as z on z.u = x.a;
--error 0,1193
set global debug=@save_debug;
#Undo update
update t1 set u=u-100;
##############
connection ddl;
drop index ix2 on t1;
create unique index ix2 on t1(a,u);
connection spj;
set ndb_join_pushdown=on;
explain
select straight_join * from
t1 as table1 join
(t1 as table2 join t1 as table3 on table3.a = table2.a)
on table3.u = table1.u
where table2.pk = 3;
--sorted_result
select straight_join * from
t1 as table1 join
(t1 as table2 join t1 as table3 on table3.a = table2.a)
on table3.u = table1.u
where table2.pk = 3;
##############
connection ddl;
drop table t1;
##############
# Test that branches of a bushy scan are correctly reset.
connection ddl;
CREATE TABLE t1 (
a int NOT NULL,
b int NOT NULL,
c int NOT NULL,
d int NOT NULL,
PRIMARY KEY (`a`,`b`)
) ENGINE=ndbcluster
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t1 values (1,1,1,1), (1,2,1,1), (1,3,1,1), (1,4,1,2);
connection ddl;
CREATE TABLE t2 (
a int NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=ndbcluster
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
CREATE TABLE t3 (
a int NOT NULL,
b int NOT NULL,
PRIMARY KEY (`a`,`b`)
) ENGINE=ndbcluster
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection ddl;
insert into t2 values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
connection spj;
# Make t3 so big that it takes multiple batches to scan it.
insert into t3 select 1, x1.a * 10+x2.a from t2 as x1 cross join t2 as x2;
--error 0,1193
set global debug='+d,max_64rows_in_spj_batches';
explain select straight_join count(*) from t1 as x0
join t3 as x1 on x1.a=x0.c
join t1 as x2 on x2.a=x0.d
join t3 as x3 on x3.a=x2.c
join t1 as x4 on x4.a=x0.d and x4.b=x3.b;
select straight_join count(*) from t1 as x0
join t3 as x1 on x1.a=x0.c
join t1 as x2 on x2.a=x0.d
join t3 as x3 on x3.a=x2.c
join t1 as x4 on x4.a=x0.d and x4.b=x3.b;
# If the first batch of an index scan has low parallelism and returns few rows,
# there is a mechanism that will try to query the remaining fragments within
# the same batch. This is done in order to avoid repeating other branches of
# a bushy scan whenever possible. This is a test of that mechanism. Scan
# of x2 should return only one row. Therefore we should be able to fetch
# x2 in one batch and scan x3 only once.
let $scan_rows = query_get_value(select sum(val) as Value from ndbinfo.counters where block_name='DBSPJ' and counter_name='LOCAL_RANGE_SCANS_SENT', Value, 1);
--replace_column 10 # 11 #
explain select straight_join count(*) from t1 as x1
join t1 as x2 on x1.c=x2.a and x2.d=2
join t3 as x3 on x1.d=x3.a;
select straight_join count(*) from t1 as x1
join t1 as x2 on x1.c=x2.a and x2.d=2
join t3 as x3 on x1.d=x3.a;
#####
# Bug #18175080
# INCORRECT 'LOCAL_RANGE_SCAN' REPORTED FROM NDB_JOIN_PUSHDOWN_*.TEST
#
# Reported local_range_scan was not deterministic, so had
# to disable this subtest.
#
#--disable_query_log
#--eval select sum(val) - $scan_rows as Local_range_scans from ndbinfo.counters where block_name='DBSPJ' and counter_name='LOCAL_RANGE_SCANS_SENT';
#--enable_query_log
--error 0,1193
set global debug=@save_debug;
connection ddl;
drop table t1;
drop table t2;
drop table t3;
#############################################
# Test pruned index scan:
connection ddl;
create table t1(
d int not null,
e int null,
f int null,
a int not null,
b int not null,
c int not null,
primary key (a,b,c))
engine = ndb partition by key (b) partitions 8;
connection spj;
insert into t1(a,b,c,d,e,f) values
(1, 2, 3, 1, 2, 3),
(1, 2, 4, 1, 2, 3),
(2, 3, 4, 1, 2, 3),
(3, 4, 5, 1, 2, 3),
(4, 5, 6, 1, 2, 3),
(5, 6, 7, 1, 2, 3),
(6, 7, 8, 1, 2, 3),
(7, 8, 9, 1, 2, 3);
# Find the total number of pruned range scans so far
let $pruned_range = query_get_value(select sum(val) as Value from ndbinfo.counters where block_name='DBSPJ' and counter_name='PRUNED_RANGE_SCANS_RECEIVED', Value, 1);
let $const_pruned_range = query_get_value(select sum(val) as Value from ndbinfo.counters where block_name='DBSPJ' and counter_name='CONST_PRUNED_RANGE_SCANS_RECEIVED', Value, 1);
set ndb_join_pushdown=on;
--replace_column 10 # 11 #
explain
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
connection ddl;
alter table t1 partition by key (a) partitions 8;
connection spj;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
connection ddl;
alter table t1 partition by key (a,b) partitions 8;
connection spj;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
connection ddl;
alter table t1 partition by key (b,a) partitions 8;
connection spj;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
#########
# const pruned testcase
#########
connection ddl;
alter table t1 partition by key (b) partitions 8;
connection spj;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=2;
connection ddl;
alter table t1 partition by key (a) partitions 8;
connection spj;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=1 and y.b=x.e;
select straight_join * from t1 x, t1 y where y.a=0 and y.b=x.e;
# Non-const pruned as both partition keys are not const
connection ddl;
alter table t1 partition by key (a,b) partitions 8;
connection spj;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=1 and y.b=x.e;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=2;
##########
# Test pruned scan using an index:
# Declaring PK as 'using hash' will prevent that PK is used as index
# Declare PK / ix1 with mismatching column order will test correct
# usage of NdbRecord::distkey_indexes[]
##########
connection ddl;
alter table t1 drop primary key, add primary key using hash (d,b,a,c);
alter table t1 partition by key (b) partitions 8;
create index ix1 on t1(b,d,a);
connection spj;
--replace_column 10 # 11 #
explain
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
###########
# Partition keys may evaluate to null-values:
###########
insert into t1(a,b,c,d,e,f) values
(8, 9, 0, 1, null, 3),
(9, 9, 0, 1, 2, null);
connection ddl;
alter table t1 partition by key (b) partitions 8;
connection spj;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
# Verify pruned execution by comparing the NDB$INFO counters
--disable_query_log
--eval select sum(val) - $pruned_range as pruned from ndbinfo.counters where block_name='DBSPJ' and counter_name='PRUNED_RANGE_SCANS_RECEIVED'
--eval select sum(val) - $const_pruned_range as const_pruned from ndbinfo.counters where block_name='DBSPJ' and counter_name='CONST_PRUNED_RANGE_SCANS_RECEIVED'
--enable_query_log
connection ddl;
drop table t1;
###
# Sorted scan with sub scan used to not being pushable.
# However since v7.2.6(?) we support this by enforcing
# batchRowSize=1 for the parent table in the scan-scan.
# (At the cost of more roundrtrips)
###
connection ddl;
create table t1 (pk int primary key, a int, b int) engine=ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
create index ix1 on t1(b,a);
connection spj;
insert into t1 values (0,10,10);
insert into t1 values (1,10,20);
insert into t1 values (2,20,20);
insert into t1 values (3,10,10);
insert into t1 values (4,10,20);
insert into t1 values (5,10,20);
insert into t1 values (6,10,10);
insert into t1 values (7,10,10);
insert into t1 values (8,10,20);
insert into t1 values (9,10,10);
# This sorted scan-scan is pushed since V7.2.6
explain select x1.pk,x1.a,x1.b from t1 as x1
join t1 as x2 on x1.a=x2.b
join t1 as x3 on x2.a=x3.b
order by x1.pk limit 70;
select x1.pk,x1.a,x1.b from t1 as x1
join t1 as x2 on x1.a=x2.b
join t1 as x3 on x2.a=x3.b
order by x1.pk limit 70;
# This query should not be pushed, since mysqld requires sorted
# results for the root scan.
explain select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3;
select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3;
connection ddl;
drop table t1;
########
# Test correct cleanup of MRR accesses being executed multiple times.
# This used to be bug#57481, and this is a SPJ specific testcase in addition to
# the specific testcase commited together with patch for this bug.
#######
connection ddl;
create table t (pk int primary key, a int) engine=ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
insert into t values
(1,1), (2,1),
(4,3), (6,3),
(7,4), (8,4);
connection spj;
--replace_column 10 # 11 #
explain
select distinct straight_join table1.pk FROM
t as table1 join
(t as table2 join
(t as table3 join t as table4 on table3.pk = table4.a)
on table2.pk = table3.pk )
on table1.a = table4.pk
where table2.pk != 6;
--sorted_result
select distinct straight_join table1.pk FROM
t as table1 join
(t as table2 join
(t as table3 join t as table4 on table3.pk = table4.a)
on table2.pk = table3.pk )
on table1.a = table4.pk
where table2.pk != 6;
connection ddl;
drop table t;
########
# SPJ variant of bug#57396
# Test correct format of an 'open bound'
########
connection ddl;
create table t (b int, a int, primary key (a,b)) engine=ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t values(0,0);
--replace_column 10 # 11 #
explain
select * from t as t1 join t as t2 on t2.a=t1.a where t1.a < 8 or t1.a >= 8;
select * from t as t1 join t as t2 on t2.a=t1.a where t1.a < 8 or t1.a >= 8;
connection ddl;
drop table t;
#######
# Testcase for bug introduced by initial fix for
# bug#57601 'Optimizer is overly eager to request ordered access.'
# When we turned of 'sorted' for 'descending', we broke QUICK_SELECT_DESC
# which required result to be read as an ordered index access
# Note: Before V7.2.6, and as a result of WL5558, such 'turn of sorting'
# is not expected to happen any more.
#######
connection ddl;
create table t (pk1 int, pk2 int, primary key(pk1,pk2)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t values (1,3), (3,6), (6,9), (9,1);
--replace_column 10 # 11 #
explain
select * from t as t1 join t as t2
on t1.pk2 = t2.pk1
where t1.pk1 != 6
order by t1.pk1 DESC;
select * from t as t1 join t as t2
on t1.pk2 = t2.pk1
where t1.pk1 != 6
order by t1.pk1 DESC;
connection ddl;
drop table t;
#######
# Testcase using 'REF_OR_NULL'
# 'ref_or_null' contains elements of left outer join wo/ being identical.
#
connection ddl;
create table t (k int, uq int, unique key ix1 (uq)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t values (1,3), (3,NULL), (6,9), (9,1);
# Currently we do not handle 'ref_or_null' correctly.
# It is therefore disabled as pushable
explain
select straight_join * from t as a join t as b
on a.uq=b.uq or b.uq is null;
--sorted_result
select straight_join * from t as a join t as b
on a.uq=b.uq or b.uq is null;
connection ddl;
drop table t;
########
# JT_SYSTEM testcase, 'a.k is null' is known 'false' ->
# Join condition will always fail, and all 'left joins' can be NULL complemented wo/
# even requiring to access left table (b) which becomes 'system' -> No pushed joins !
########
connection ddl;
create table t (k int primary key, uq int) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t values (1,3), (3,NULL), (6,9), (9,1);
explain
select * from t as a left join t as b
on a.k is null and a.uq=b.uq;
--sorted_result
select * from t as a left join t as b
on a.k is null and a.uq=b.uq;
connection ddl;
drop table t;
#######
# Test of varchar query parameteres.
#######
connection ddl;
create table tc(
a varchar(10) not null,
b varchar(10),
c varchar(10),
primary key (a),
unique key uk1 (b, c)
) engine=ndbcluster
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into tc values ('aa','bb', 'x'), ('bb','cc', 'x'), ('cc', 'dd', 'x');
explain select * from tc as x1
right outer join tc as x2 on x1.b=x2.a
left outer join tc as x3 on x2.b = x3.b and x1.c=x3.c;
--sorted_result
select * from tc as x1
right outer join tc as x2 on x1.b=x2.a
left outer join tc as x3 on x2.b = x3.b and x1.c=x3.c;
####
# Test that 'select ... for update' is not pushed, since this requires locking.
####
explain select * from tc as x1, tc as x2 where x1.b=x2.a for update;
explain select * from tc as x1, tc as x2 where x1.b=x2.a;
connection ddl;
drop table tc;
###
# prune with xfrm set incorrect keylen
#
connection ddl;
create table t1 (
a varchar(16) not null,
b int not null,
c varchar(16) not null,
d int not null,
primary key (a,b)
) engine ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"
partition by key (a);
connection spj;
insert into t1 values ('aaa', 1, 'aaa', 1);
select * from t1 as q1, t1 as q2 where q1.a = 'aaa' and q1.c=q2.a;
connection ddl;
drop table t1;
########################
# Bug#17845161 'CORRUPT KEY IN TC, UNABLE TO XFRM'
#
# Created an incorrect lookup key when a varchar()
# in the key was NULL
connection ddl;
CREATE TABLE t1 (
id int NOT NULL AUTO_INCREMENT,
t2_id int,
PRIMARY KEY (id)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
CREATE TABLE t2 (
id int NOT NULL AUTO_INCREMENT,
t3_id varchar(20) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
CREATE TABLE t3 (
id varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
CREATE TABLE t4 (
pk int NOT NULL,
id varchar(20) NOT NULL,
PRIMARY KEY (pk),
UNIQUE KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
INSERT INTO t1 VALUES (20, NULL);
INSERT INTO t1 VALUES (23, 24);
INSERT INTO t2 VALUES (24, NULL);
EXPLAIN
SELECT *
FROM t1
INNER JOIN t2 ON t2.id = t1.t2_id
LEFT OUTER JOIN t3 ON t3.id = t2.t3_id
WHERE t1.id = 20;
SELECT *
FROM t1
INNER JOIN t2 ON t2.id = t1.t2_id
LEFT OUTER JOIN t3 ON t3.id = t2.t3_id
WHERE t1.id = 20;
EXPLAIN
SELECT *
FROM t1
INNER JOIN t2 ON t2.id = t1.t2_id
LEFT OUTER JOIN t3 ON t3.id = t2.t3_id
WHERE t1.id = 23;
SELECT *
FROM t1
INNER JOIN t2 ON t2.id = t1.t2_id
LEFT OUTER JOIN t3 ON t3.id = t2.t3_id
WHERE t1.id = 23;
EXPLAIN
SELECT *
FROM t1
INNER JOIN t2 ON t2.id = t1.t2_id
LEFT OUTER JOIN t4 ON t4.id = t2.t3_id
WHERE t1.id = 23;
SELECT *
FROM t1
INNER JOIN t2 ON t2.id = t1.t2_id
LEFT OUTER JOIN t4 ON t4.id = t2.t3_id
WHERE t1.id = 23;
EXPLAIN
SELECT *
FROM t1
LEFT OUTER JOIN t2 ON t2.id = t1.t2_id
LEFT OUTER JOIN t2 as t3 ON t3.id = t1.t2_id;
--sorted_result
SELECT *
FROM t1
LEFT OUTER JOIN t2 ON t2.id = t1.t2_id
LEFT OUTER JOIN t2 as t3 ON t3.id = t1.t2_id;
connection ddl;
DROP TABLE t1,t2,t3,t4;
#######################################
# Some tests for nested left joins.
connection ddl;
CREATE TABLE t1 (
a int NOT NULL,
b int NOT NULL,
c int NOT NULL,
d int,
PRIMARY KEY (`a`,`b`),
unique key(c)
) ENGINE=ndbcluster
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t1 values
(1,1,1,1),
(1,2,2,1),
(1,3,3,1),
(1,4,4,1),
(1,5,5,2),
(1,6,6,2),
(1,7,7,2),
(1,8,8,2);
explain select count(*) from t1 as x1
join (t1 as x2
left join (t1 as x3
cross join t1 as x4)
on x2.d=x3.a)
on x2.c is null or x1.a=x4.d;
select count(*) from t1 as x1
join (t1 as x2
left join (t1 as x3
cross join t1 as x4)
on x2.d=x3.a)
on x2.c is null or x1.a=x4.d;
explain select count(*) from t1 as x1
left join (t1 as x2
cross join t1 as x3)
on x1.d=x2.a;
select count(*) from t1 as x1
left join (t1 as x2
cross join t1 as x3)
on x1.d=x2.a;
explain select count(*) from t1 as x0
left join (t1 as x1
join (t1 as x2
left join (t1 as x3
join t1 as x4 on x3.d=x4.a)
on x2.d=x3.a)
on x2.c is null or x1.a=x4.d)
on x0.d=x1.a;
select count(*) from t1 as x0
left join (t1 as x1
join (t1 as x2
left join (t1 as x3
join t1 as x4 on x3.d=x4.a)
on x2.d=x3.a)
on x2.c is null or x1.a=x4.d)
on x0.d=x1.a;
connection ddl;
drop table t1;
## Test scan sorted on string field.
connection ddl;
create table t1 (pk char(10) primary key, u int not null) engine=ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
create table t2 (pk int primary key, u int not null) engine=ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t1 values ('wh',1);
insert into t1 values ('ik',2);
insert into t1 values ('cu',3);
insert into t1 values ('pw',4);
insert into t1 values ('cq',4);
insert into t2 values (1,2), (2,3), (3,4), (4,5);
explain select * from t1 join t2 on t1.u = t2.pk order by t1.pk;
select * from t1 join t2 on t1.u = t2.pk order by t1.pk;
connection ddl;
drop table t1;
drop table t2;
########################################
# Test query with very long records.
connection ddl;
create table t1 (
a char(10) primary key,
b char(10) not null,
c char(10) not null,
l00 char(255) not null,
l01 char(255) not null,
l02 char(255) not null,
l03 char(255) not null,
l04 char(255) not null,
l05 char(255) not null,
l06 char(255) not null,
l07 char(255) not null,
l08 char(255) not null,
l09 char(255) not null,
l10 char(255) not null,
l11 char(255) not null,
l12 char(255) not null,
l13 char(255) not null,
l14 char(255) not null,
l15 char(255) not null,
l16 char(255) not null,
l17 char(255) not null,
l18 char(255) not null,
l19 char(255) not null,
l20 char(255) not null,
l21 char(255) not null,
l22 char(255) not null,
l23 char(255) not null,
l24 char(255) not null,
l25 char(255) not null,
l26 char(255) not null,
l27 char(255) not null,
l28 char(255) not null,
l29 char(255) not null,
l30 char(255) not null,
l31 char(255) not null,
index(c, b)
) engine=ndb character set latin1 partition by key(a) partitions 8;
connection spj;
insert into t1 values ('a','a','a','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x');
insert into t1 values ('b','b','b','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x');
insert into t1 values ('c','c','c','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x');
--replace_column 10 # 11 #
explain select count(*) from t1 as x1 join t1 as x2 on x1.b = x2.c;
select count(*) from t1 as x1 join t1 as x2 on x1.b = x2.c;
connection ddl;
drop table t1;
####################
# Test pruned child scans using parameter values (known regression).
####################
create table t1
(a int not null,
b int not null,
c int not null,
d int not null,
primary key(a,b,c,d)) engine=ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"
partition by key (b,c);
connection spj;
insert into t1 values (0x4f, 0x4f, 0x4f, 0x4f);
# Prune key depends on parent row.
--replace_column 10 # 11 #
explain select * from t1 as x1
join t1 as x2 on x1.c=0x4f and x2.a=0+x1.b and x2.b=x1.b
join t1 as x3 on x3.a=x2.d and x3.b=x1.d and x3.c=x2.c;
select * from t1 as x1
join t1 as x2 on x1.c=0x4f and x2.a=0+x1.b and x2.b=x1.b
join t1 as x3 on x3.a=x2.c and x3.b=x1.d and x3.c=x2.c;
# Prune key is fixed.
--replace_column 10 # 11 #
explain select * from t1 as x1
join t1 as x2 on x1.c=0x4f and x2.a=0+x1.b and x2.b=x1.b
join t1 as x3 on x3.a=x2.d and x3.b=x1.d and x3.c=0x4f;
select * from t1 as x1
join t1 as x2 on x1.c=0x4f and x2.a=0+x1.b and x2.b=x1.b
join t1 as x3 on x3.a=x2.c and x3.b=x1.d and x3.c=0x4f;
connection ddl;
drop table t1;
############################################################
# Bug#13990924 / Bug#64865 Large WHERE IN with SPJ leads to
# cluster shutdown
#
# Caused by incorrect error handling in SPJ block when
# there was a buffer overflow (>32K)
# (Pushed condition became to large)
#
# API also failed to prevent this from happen as it checked
# for size <= 64K, while actuall buffer size was 32K
# (Has now been increased)
############################################################
create table t1 (
k1 int primary key,
i int,
name varchar(32),
key (name)
)
default charset = utf8
engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
insert into t1 values (1, 1, 'Ole');
insert into t1 values (2, 2, 'Dole');
insert into t1 values (3, 3, 'Doffen');
insert into t1 values (4, 4, 'row# 999');
# These queries with large IN clauses exceeds the default memory limit of the
# range optimizer on 64 bit but not on 32 bit, thus cauing different result.
# Fix by setting a fairly low value in order to get consistent behaviour
set @save_range_opt_max = @@session.range_optimizer_max_mem_size;
set range_optimizer_max_mem_size = 512*512;
# Build SPJ query with 2000 IN values
# This used to result in buffer overflow, but will now
# pass as buffer size is incresed from 32K -> 64K
#
let $query = select * from t1 x, t1 y where y.k1=x.i and x.name in ('foo';
let $values = 0;
while ($values < 2000)
{
let $query = $query , 'row# $values';
inc $values;
}