DROP TABLE IF EXISTS t1, t2, r1;
create table t1 (
a int primary key,
b int not null,
c int not null,
index(b), unique index using hash(c)
) engine = ndb;
insert into t1 values
(1,2,1),(2,3,2),(3,4,3),(4,5,4),
(5,2,12),(6,3,11),(7,4,10),(8,5,9),
(9,2,8),(10,3,7),(11,4,6),(12,5,5);
create table r1 as select * from t1 where a in (2,8,12);
select * from r1 order by a;
a	b	c
2	3	2
8	5	9
12	5	5
drop table r1;
create table r1 as select * from t1 where b in (1,2,5);
select * from r1 order by a;
a	b	c
1	2	1
4	5	4
5	2	12
8	5	9
9	2	8
12	5	5
drop table r1;
create table r1 as select * from t1 where c in (2,8,12);
select * from r1 order by a;
a	b	c
2	3	2
5	2	12
9	2	8
drop table r1;
create table r1 as select * from t1 where a in (2,8) or (a > 11) or (a <= 1);
select * from r1 order by a;
a	b	c
1	2	1
2	3	2
8	5	9
12	5	5
drop table r1;
create table r1 as select * from t1 where a in (33,8,12);
select * from r1 order by a;
a	b	c
8	5	9
12	5	5
drop table r1;
create table r1 as select * from t1 where a in (2,33,8,12,34);
select * from r1 order by a;
a	b	c
2	3	2
8	5	9
12	5	5
drop table r1;
create table r1 as select * from t1 where b in (1,33,5);
select * from r1 order by a;
a	b	c
4	5	4
8	5	9
12	5	5
drop table r1;
select * from t1 where b in (1,33,5) order by a;
a	b	c
4	5	4
8	5	9
12	5	5
create table r1 as select * from t1 where b in (45,1,33,5,44);
select * from r1 order by a;
a	b	c
4	5	4
8	5	9
12	5	5
drop table r1;
select * from t1 where b in (45,22) order by a;
a	b	c
create table r1 as select * from t1 where c in (2,8,33);
select * from r1 order by a;
a	b	c
2	3	2
9	2	8
drop table r1;
create table r1 as select * from t1 where c in (13,2,8,33,12);
select * from r1 order by a;
a	b	c
2	3	2
5	2	12
9	2	8
drop table r1;
select * from t1 where a in (33,8,12) order by a;
a	b	c
8	5	9
12	5	5
select * from t1 where a in (33,34,35) order by a;
a	b	c
select * from t1 where a in (2,8) or (a > 11) or (a <= 1) order by a;
a	b	c
1	2	1
2	3	2
8	5	9
12	5	5
select * from t1 where b in (6,7) or (b <= 5) or (b >= 10) order by b,a;
a	b	c
1	2	1
5	2	12
9	2	8
2	3	2
6	3	11
10	3	7
3	4	3
7	4	10
11	4	6
4	5	4
8	5	9
12	5	5
select * from t1 where c in (13,2,8,33,12) order by c,a;
a	b	c
2	3	2
9	2	8
5	2	12
drop table t1;
create table t1 (
a int not null,
b int not null,
c int not null,
d int not null,
e int not null,
primary key (a,b,c,d), index (d)
) engine = ndb;
insert into t1 values
(1,2,1,1,1),(2,3,2,3,1),(3,4,3,1,1),(4,5,4,7,1),
(5,2,12,12,1),(6,3,11,1,1),(7,4,10,3,1),(8,5,9,5,1),
(9,2,8,6,1),(10,3,7,5,1),(11,4,6,3,1),(12,5,5,2,1),
(1,2,1,2,1),
(1,2,1,3,1),
(1,2,1,4,1),
(1,2,1,5,1);
create table r1 as select * from t1
where a=1 and b=2 and c=1 and d in (1,4,3,2);
select * from r1 order by a,b,c,d;
a	b	c	d	e
1	2	1	1	1
1	2	1	2	1
1	2	1	3	1
1	2	1	4	1
drop table r1;
update t1 set e = 100
where d in (12,6,7);
select * from t1 where d in (12,6,7) order by a,b,c,d;
a	b	c	d	e
4	5	4	7	100
5	2	12	12	100
9	2	8	6	100
select * from t1 where d not in (12,6,7) and e = 100;
a	b	c	d	e
update t1 
set e = 101
where a=1 and 
b=2 and 
c=1 and 
d in (1,4,3,2);
select * 
from t1
where a=1 and b=2 and c=1 and d in (1,4,3,2)
order by a,b,c,d;
a	b	c	d	e
1	2	1	1	101
1	2	1	2	101
1	2	1	3	101
1	2	1	4	101
select * 
from t1 
where not (a=1 and b=2 and c=1 and d in (1,4,3,2))
and e=101;
a	b	c	d	e
update t1 
set e = 
(case d
when 12 then 112
when 6  then 106
when 7  then 107
end)
where d in (12,6,7);
select * from t1 where d in (12,6,7) order by a,b,c,d;
a	b	c	d	e
4	5	4	7	107
5	2	12	12	112
9	2	8	6	106
update t1 
set e = 
(case d
when 1 then 111
when 4 then 444
when 3 then 333
when 2 then 222
end)
where a=1 and 
b=2 and 
c=1 and 
d in (1,4,3,2);
select * 
from t1
where a=1 and b=2 and c=1 and d in (1,4,3,2)
order by a,b,c,d;
a	b	c	d	e
1	2	1	1	111
1	2	1	2	222
1	2	1	3	333
1	2	1	4	444
delete from t1 where d in (12,6,7);
select * from t1 where d in (12,6,7);
a	b	c	d	e
drop table t1;
create table t1 (
a int not null primary key,
b int,
c int,
d int,
unique index (b),
index(c)
) engine = ndb;
insert into t1 values
(1,null,1,1),
(2,2,2,2),
(3,null,null,3),
(4,4,null,4),
(5,null,5,null),
(6,6,6,null),
(7,null,null,null),
(8,8,null,null),
(9,null,9,9),
(10,10,10,10),
(11,null,null,11),
(12,12,null,12),
(13,null,13,null),
(14,14,14,null),
(15,null,null,null),
(16,16,null,null);
create table t2 as select * from t1 where a in (5,6,7,8,9,10);
select * from t2 order by a;
a	b	c	d
5	NULL	5	NULL
6	6	6	NULL
7	NULL	NULL	NULL
8	8	NULL	NULL
9	NULL	9	9
10	10	10	10
drop table t2;
create table t2 as select * from t1 where b in (5,6,7,8,9,10);
select * from t2 order by a;
a	b	c	d
6	6	6	NULL
8	8	NULL	NULL
10	10	10	10
drop table t2;
create table t2 as select * from t1 where c in (5,6,7,8,9,10);
select * from t2 order by a;
a	b	c	d
5	NULL	5	NULL
6	6	6	NULL
9	NULL	9	9
10	10	10	10
drop table t2;
drop table t1;
