call mtr.add_suppression(".*NDB_SHARE.*Moving away for safety, but possible memleak");
create table parent(a int primary key, b int, key(b)) engine=ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
create table child(a int, b int, primary key(a,b), key(b)) engine=ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
insert into parent values (1,1), (2,2), (3,3), (4,4);
insert into parent select a+4, b+4 from parent;
insert into parent select a+8, b+8 from parent;
insert into parent select a+16, b+16 from parent;
insert into parent select a+32, b+32 from parent;
insert into parent select a+64, b+64 from parent;
insert into parent select a+128, b+128 from parent;
insert into parent select a+256, b+256 from parent;
insert into parent select a+512, b+512 from parent;
insert into parent select a+1024, b+1024 from parent;
insert into parent select a+2048, b+2048 from parent;
insert into parent select a+4096, b+4096 from parent;
insert into child select * from parent;
analyze table parent, child;
Table	Op	Msg_type	Msg_text
test.parent	analyze	status	OK
test.child	analyze	status	OK
set ndb_join_pushdown = off;
explain
select straight_join count(*)
from parent join child
on child.a = parent.a and child.b = parent.b
where parent.b > 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	parent	p0,p1,p2,p3,p4,p5,p6,p7	range	PRIMARY,b	b	5	NULL	###	###	Using pushed condition ((`test`.`parent`.`b` > 5) and (`test`.`parent`.`b` is not null)); Using MRR
1	SIMPLE	child	p0,p1,p2,p3,p4,p5,p6,p7	eq_ref	PRIMARY,b	PRIMARY	8	test.parent.a,test.parent.b	###	###	NULL
Warnings:
Note	1003	/* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`b`) and (`test`.`child`.`a` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
explain
select straight_join count(*)
from parent join child
on child.a = parent.a 
where parent.b > 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	parent	p0,p1,p2,p3,p4,p5,p6,p7	range	PRIMARY,b	b	5	NULL	###	###	Using pushed condition (`test`.`parent`.`b` > 5); Using MRR
1	SIMPLE	child	p0,p1,p2,p3,p4,p5,p6,p7	ref	PRIMARY	PRIMARY	4	test.parent.a	###	###	NULL
Warnings:
Note	1003	/* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`a` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
explain
select straight_join count(*)
from parent join child
on child.b = parent.b 
where parent.b > 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	parent	p0,p1,p2,p3,p4,p5,p6,p7	range	b	b	5	NULL	###	###	Using pushed condition ((`test`.`parent`.`b` > 5) and (`test`.`parent`.`b` is not null)); Using MRR
1	SIMPLE	child	p0,p1,p2,p3,p4,p5,p6,p7	ref	b	b	4	test.parent.b	###	###	NULL
Warnings:
Note	1003	/* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`b`) and (`test`.`parent`.`b` > 5))
show variables like 'server_id';
Variable_name	Value
server_id	1
create procedure query_eq_ref_load (seconds int)
begin
set @x=time_to_sec(current_time()) + seconds;
repeat
set ndb_join_pushdown = off;
select straight_join count(*)
from parent join child
on child.a = parent.a and child.b = parent.b
where parent.b > 5;
until @x <= time_to_sec(current_time())
end repeat;
end%
create procedure query_ref_load (seconds int)
begin
set @x=time_to_sec(current_time()) + seconds;
repeat
set ndb_join_pushdown = off;
select straight_join count(*)
from parent join child
on child.a = parent.a and child.b = parent.b
where parent.b > 5;
until @x <= time_to_sec(current_time())
end repeat;
end%
create procedure query_ref_b_load (seconds int)
begin
set @x=time_to_sec(current_time()) + seconds;
repeat
set ndb_join_pushdown = off;
select straight_join count(*)
from parent join child
on child.a = parent.a and child.b = parent.b
where parent.b > 5;
until @x <= time_to_sec(current_time())
end repeat;
end%
Starting background DML load
call query_eq_ref_load(10);
call query_ref_load(10);
call query_ref_b_load(10);
show variables like 'server_id';
Variable_name	Value
server_id	2
Starting concurrent DDL operations
alter table child add column c int;
Warnings:
Warning	1478	Converted FIXED field 'c' to DYNAMIC to enable online ADD COLUMN
alter table child drop column c;
alter table child drop primary key;
alter table child add primary key(b);
alter table child drop primary key;
alter table child add primary key(a,b) using hash;
alter table child drop primary key;
alter table child add primary key(a,b);
Completed DDL operations, rejoins with DML load.
====================================================
Force a 'trailing share' to be created in order to
check correct cleanup in ndbcluster_real_free_share()
=====================================================
set ndb_join_pushdown = off;
explain select straight_join * 
from parent join child
on child.a = parent.a and child.b = parent.b
where parent.b > 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	parent	p0,p1,p2,p3,p4,p5,p6,p7	range	PRIMARY,b	b	5	NULL	###	###	Using pushed condition ((`test`.`parent`.`b` > 5) and (`test`.`parent`.`b` is not null)); Using MRR
1	SIMPLE	child	p0,p1,p2,p3,p4,p5,p6,p7	eq_ref	PRIMARY,b	PRIMARY	8	test.parent.a,test.parent.b	###	###	NULL
Warnings:
Note	1003	/* select#1 */ select straight_join `test`.`parent`.`a` AS `a`,`test`.`parent`.`b` AS `b`,`test`.`child`.`a` AS `a`,`test`.`child`.`b` AS `b` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`b`) and (`test`.`child`.`a` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
alter table child algorithm=copy, drop primary key;
commit;
drop table child;
drop table parent;
drop procedure query_eq_ref_load;
drop procedure query_ref_load;
drop procedure query_ref_b_load;