drop table if exists t1;
drop database if exists test2;
create table t1 (
a int not null primary key,
b tinytext
) engine=ndbcluster;
insert into t1 values(1, 'x');
update t1 set b = 'y';
select * from t1;
a	b
1	y
delete from t1;
drop table t1;
create table t1 (
a int not null primary key,
b text not null
) engine=ndbcluster;
insert into t1 values(1, '');
select * from t1;
a	b
1	
drop table t1;
set autocommit=0;
create table t1 (
a int not null primary key,
b text not null,
c int not null,
d longblob,
key (c)
) engine=ndbcluster;
set @x0 = '01234567012345670123456701234567';
set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0);
set @b1 = 'b1';
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
set @b1 = concat(@b1,@x0);
set @d1 = 'dd1';
set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
set @b2 = 'b2';
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
set @d2 = 'dd2';
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
select length(@x0),length(@b1),length(@d1) from dual;
length(@x0)	length(@b1)	length(@d1)
256	2256	3000
select length(@x0),length(@b2),length(@d2) from dual;
length(@x0)	length(@b2)	length(@d2)
256	20000	30000
insert into t1 values(1,@b1,111,@d1);
insert into t1 values(2,@b2,222,@d2);
commit;
explain select * from t1 where a = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
from t1 where a=1;
a	length(b)	substr(b,1+2*900,2)	length(d)	substr(d,1+3*900,3)
1	2256	b1	3000	dd1
select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
from t1 where a=2;
a	length(b)	substr(b,1+2*9000,2)	length(d)	substr(d,1+3*9000,3)
2	20000	b2	30000	dd2
update t1 set b=@b2,d=@d2 where a=1;
update t1 set b=@b1,d=@d1 where a=2;
commit;
select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
from t1 where a=1;
a	length(b)	substr(b,1+2*9000,2)	length(d)	substr(d,1+3*9000,3)
1	20000	b2	30000	dd2
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
from t1 where a=2;
a	length(b)	substr(b,1+2*900,2)	length(d)	substr(d,1+3*900,3)
2	2256	b1	3000	dd1
update t1 set b=concat(b,b),d=concat(d,d) where a=1;
update t1 set b=concat(b,b),d=concat(d,d) where a=2;
commit;
select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3)
from t1 where a=1;
a	length(b)	substr(b,1+4*9000,2)	length(d)	substr(d,1+6*9000,3)
1	40000	b2	60000	dd2
select a,length(b),substr(b,1+4*900,2),length(d),substr(d,1+6*900,3)
from t1 where a=2;
a	length(b)	substr(b,1+4*900,2)	length(d)	substr(d,1+6*900,3)
2	4512	b1	6000	dd1
update t1 set d=null where a=1;
commit;
delete from t1 where a=1;
delete from t1 where a=2;
commit;
select count(*) from t1;
count(*)
0
insert into t1 values(1,@b1,111,@d1);
insert into t1 values(2,@b2,222,@d2);
commit;
explain select * from t1 where c = 111;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	c	c	4	const	10	Using where
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
from t1 where c=111;
a	length(b)	substr(b,1+2*900,2)	length(d)	substr(d,1+3*900,3)
1	2256	b1	3000	dd1
select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
from t1 where c=222;
a	length(b)	substr(b,1+2*9000,2)	length(d)	substr(d,1+3*9000,3)
2	20000	b2	30000	dd2
update t1 set b=@b2,d=@d2 where c=111;
update t1 set b=@b1,d=@d1 where c=222;
commit;
select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
from t1 where c=111;
a	length(b)	substr(b,1+2*9000,2)	length(d)	substr(d,1+3*9000,3)
1	20000	b2	30000	dd2
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
from t1 where c=222;
a	length(b)	substr(b,1+2*900,2)	length(d)	substr(d,1+3*900,3)
2	2256	b1	3000	dd1
update t1 set d=null where c=111;
commit;
select a from t1 where d is null;
a
1
delete from t1 where c=111;
delete from t1 where c=222;
commit;
select count(*) from t1;
count(*)
0
insert into t1 values(1,'b1',111,'dd1');
insert into t1 values(2,'b2',222,'dd2');
insert into t1 values(3,'b3',333,'dd3');
insert into t1 values(4,'b4',444,'dd4');
insert into t1 values(5,'b5',555,'dd5');
insert into t1 values(6,'b6',666,'dd6');
insert into t1 values(7,'b7',777,'dd7');
insert into t1 values(8,'b8',888,'dd8');
insert into t1 values(9,'b9',999,'dd9');
commit;
explain select * from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	
select * from t1 order by a;
a	b	c	d
1	b1	111	dd1
2	b2	222	dd2
3	b3	333	dd3
4	b4	444	dd4
5	b5	555	dd5
6	b6	666	dd6
7	b7	777	dd7
8	b8	888	dd8
9	b9	999	dd9
update t1 set b=concat(a,'x',b),d=concat(a,'x',d);
commit;
select * from t1 order by a;
a	b	c	d
1	1xb1	111	1xdd1
2	2xb2	222	2xdd2
3	3xb3	333	3xdd3
4	4xb4	444	4xdd4
5	5xb5	555	5xdd5
6	6xb6	666	6xdd6
7	7xb7	777	7xdd7
8	8xb8	888	8xdd8
9	9xb9	999	9xdd9
delete from t1;
commit;
select count(*) from t1;
count(*)
0
insert into t1 values(1,@b1,111,@d1);
insert into t1 values(2,@b2,222,@d2);
commit;
explain select * from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
from t1 order by a;
a	length(b)	substr(b,1+2*900,2)	length(d)	substr(d,1+3*900,3)
1	2256	b1	3000	dd1
2	20000	b2	30000	dd2
update t1 set b=concat(b,b),d=concat(d,d);
commit;
select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3)
from t1 order by a;
a	length(b)	substr(b,1+4*9000,2)	length(d)	substr(d,1+6*9000,3)
1	4512		6000	
2	40000	b2	60000	dd2
delete from t1;
commit;
select count(*) from t1;
count(*)
0
insert into t1 values(1,'b1',111,'dd1');
insert into t1 values(2,'b2',222,'dd2');
insert into t1 values(3,'b3',333,'dd3');
insert into t1 values(4,'b4',444,'dd4');
insert into t1 values(5,'b5',555,'dd5');
insert into t1 values(6,'b6',666,'dd6');
insert into t1 values(7,'b7',777,'dd7');
insert into t1 values(8,'b8',888,'dd8');
insert into t1 values(9,'b9',999,'dd9');
commit;
explain select * from t1 where c >= 100 order by a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	c	c	4	NULL	10	Using where; Using filesort
select * from t1 where c >= 100 order by a;
a	b	c	d
1	b1	111	dd1
2	b2	222	dd2
3	b3	333	dd3
4	b4	444	dd4
5	b5	555	dd5
6	b6	666	dd6
7	b7	777	dd7
8	b8	888	dd8
9	b9	999	dd9
update t1 set b=concat(a,'x',b),d=concat(a,'x',d)
where c >= 100;
commit;
select * from t1 where c >= 100 order by a;
a	b	c	d
1	1xb1	111	1xdd1
2	2xb2	222	2xdd2
3	3xb3	333	3xdd3
4	4xb4	444	4xdd4
5	5xb5	555	5xdd5
6	6xb6	666	6xdd6
7	7xb7	777	7xdd7
8	8xb8	888	8xdd8
9	9xb9	999	9xdd9
select * from t1 order by a;
a	b	c	d
1	1xb1	111	1xdd1
2	2xb2	222	2xdd2
3	3xb3	333	3xdd3
4	4xb4	444	4xdd4
5	5xb5	555	5xdd5
6	6xb6	666	6xdd6
7	7xb7	777	7xdd7
8	8xb8	888	8xdd8
9	9xb9	999	9xdd9
alter table t1 add x int;
select * from t1 order by a;
a	b	c	d	x
1	1xb1	111	1xdd1	NULL
2	2xb2	222	2xdd2	NULL
3	3xb3	333	3xdd3	NULL
4	4xb4	444	4xdd4	NULL
5	5xb5	555	5xdd5	NULL
6	6xb6	666	6xdd6	NULL
7	7xb7	777	7xdd7	NULL
8	8xb8	888	8xdd8	NULL
9	9xb9	999	9xdd9	NULL
alter table t1 drop x;
select * from t1 order by a;
a	b	c	d
1	1xb1	111	1xdd1
2	2xb2	222	2xdd2
3	3xb3	333	3xdd3
4	4xb4	444	4xdd4
5	5xb5	555	5xdd5
6	6xb6	666	6xdd6
7	7xb7	777	7xdd7
8	8xb8	888	8xdd8
9	9xb9	999	9xdd9
create database test2;
use test2;
CREATE TABLE t2 (
a bigint unsigned NOT NULL PRIMARY KEY,
b int unsigned not null,
c int unsigned
) engine=ndbcluster;
insert into t2 values (1,1,1),(2,2,2);
select * from test.t1,t2 where test.t1.a = t2.a order by test.t1.a;
a	b	c	d	a	b	c
1	1xb1	111	1xdd1	1	1	1
2	2xb2	222	2xdd2	2	2	2
drop table t2;
use test;
select * from t1 order by a;
a	b	c	d
1	1xb1	111	1xdd1
2	2xb2	222	2xdd2
3	3xb3	333	3xdd3
4	4xb4	444	4xdd4
5	5xb5	555	5xdd5
6	6xb6	666	6xdd6
7	7xb7	777	7xdd7
8	8xb8	888	8xdd8
9	9xb9	999	9xdd9
alter table t1 add x int;
select * from t1 order by a;
a	b	c	d	x
1	1xb1	111	1xdd1	NULL
2	2xb2	222	2xdd2	NULL
3	3xb3	333	3xdd3	NULL
4	4xb4	444	4xdd4	NULL
5	5xb5	555	5xdd5	NULL
6	6xb6	666	6xdd6	NULL
7	7xb7	777	7xdd7	NULL
8	8xb8	888	8xdd8	NULL
9	9xb9	999	9xdd9	NULL
alter table t1 drop x;
select * from t1 order by a;
a	b	c	d
1	1xb1	111	1xdd1
2	2xb2	222	2xdd2
3	3xb3	333	3xdd3
4	4xb4	444	4xdd4
5	5xb5	555	5xdd5
6	6xb6	666	6xdd6
7	7xb7	777	7xdd7
8	8xb8	888	8xdd8
9	9xb9	999	9xdd9
delete from t1 where c >= 100;
commit;
select count(*) from t1;
count(*)
0
insert into t1 values(1,@b1,111,@d1);
insert into t1 values(2,@b2,222,@d2);
commit;
explain select * from t1 where c >= 100 order by a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	c	c	4	NULL	10	Using where; Using filesort
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
from t1 where c >= 100 order by a;
a	length(b)	substr(b,1+2*900,2)	length(d)	substr(d,1+3*900,3)
1	2256	b1	3000	dd1
2	20000	b2	30000	dd2
update t1 set b=concat(b,b),d=concat(d,d);
commit;
select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3)
from t1 where c >= 100 order by a;
a	length(b)	substr(b,1+4*9000,2)	length(d)	substr(d,1+6*9000,3)
1	4512		6000	
2	40000	b2	60000	dd2
delete from t1 where c >= 100;
commit;
select count(*) from t1;
count(*)
0
insert into t1 values(1,@b1,111,@d1);
insert into t1 values(2,@b2,222,@d2);
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
from t1 where a = 0;
a	length(b)	substr(b,1+2*900,2)	length(d)	substr(d,1+3*900,3)
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
from t1 where a = 1;
a	length(b)	substr(b,1+2*900,2)	length(d)	substr(d,1+3*900,3)
1	2256	b1	3000	dd1
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
from t1 where a = 2;
a	length(b)	substr(b,1+2*900,2)	length(d)	substr(d,1+3*900,3)
2	20000	b2	30000	dd2
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
from t1 order by a;
a	length(b)	substr(b,1+2*900,2)	length(d)	substr(d,1+3*900,3)
1	2256	b1	3000	dd1
2	20000	b2	30000	dd2
rollback;
select count(*) from t1;
count(*)
0