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; CREATE TABLE t1 ( a int(11) NOT NULL, b int(11) NOT NULL, c datetime default NULL, PRIMARY KEY (a), KEY idx_bc (b,c) ) ENGINE=ndbcluster; INSERT INTO t1 VALUES (406989,67,'2006-02-23 17:08:46'), (150078,67,'2005-10-26 11:17:45'), (406993,67,'2006-02-27 11:20:57'), (245655,67,'2005-12-08 15:59:08'), (406994,67,'2006-02-27 11:26:46'), (256,67,NULL), (398341,67,'2006-02-20 04:48:44'), (254,67,NULL),(1120,67,NULL), (406988,67,'2006-02-23 17:07:22'), (255,67,NULL), (398340,67,'2006-02-20 04:38:53'),(406631,67,'2006-02-23 10:49:42'), (245653,67,'2005-12-08 15:59:07'),(406992,67,'2006-02-24 16:47:18'), (245654,67,'2005-12-08 15:59:08'),(406995,67,'2006-02-28 11:55:00'), (127261,67,'2005-10-13 12:17:58'),(406991,67,'2006-02-24 16:42:32'), (245652,67,'2005-12-08 15:58:27'),(398545,67,'2006-02-20 04:53:13'), (154504,67,'2005-10-28 11:53:01'),(9199,67,NULL),(1,67,'2006-02-23 15:01:35'), (223456,67,NULL),(4101,67,NULL),(1133,67,NULL), (406990,67,'2006-02-23 18:01:45'),(148815,67,'2005-10-25 15:34:17'), (148812,67,'2005-10-25 15:30:01'),(245651,67,'2005-12-08 15:58:27'), (154503,67,'2005-10-28 11:52:38'); create table t11 engine = ndbcluster select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 asc; create table t12 engine = ndbcluster select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 desc; create table t21 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 asc; create table t22 engine = ndbcluster select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 desc; select * from t11 order by 1,2,3; a b c 254 67 NULL 255 67 NULL 256 67 NULL 1120 67 NULL 1133 67 NULL 4101 67 NULL 9199 67 NULL 223456 67 NULL select * from t12 order by 1,2,3; a b c 254 67 NULL 255 67 NULL 256 67 NULL 1120 67 NULL 1133 67 NULL 4101 67 NULL 9199 67 NULL 223456 67 NULL select * from t21 order by 1,2,3; a b c 1 67 2006-02-23 15:01:35 254 67 NULL 255 67 NULL 256 67 NULL 1120 67 NULL 1133 67 NULL 4101 67 NULL 9199 67 NULL 223456 67 NULL 245651 67 2005-12-08 15:58:27 245652 67 2005-12-08 15:58:27 245653 67 2005-12-08 15:59:07 245654 67 2005-12-08 15:59:08 245655 67 2005-12-08 15:59:08 398340 67 2006-02-20 04:38:53 398341 67 2006-02-20 04:48:44 398545 67 2006-02-20 04:53:13 406631 67 2006-02-23 10:49:42 406988 67 2006-02-23 17:07:22 406989 67 2006-02-23 17:08:46 406990 67 2006-02-23 18:01:45 406991 67 2006-02-24 16:42:32 406992 67 2006-02-24 16:47:18 406993 67 2006-02-27 11:20:57 406994 67 2006-02-27 11:26:46 406995 67 2006-02-28 11:55:00 select * from t22 order by 1,2,3; a b c 1 67 2006-02-23 15:01:35 254 67 NULL 255 67 NULL 256 67 NULL 1120 67 NULL 1133 67 NULL 4101 67 NULL 9199 67 NULL 223456 67 NULL 245651 67 2005-12-08 15:58:27 245652 67 2005-12-08 15:58:27 245653 67 2005-12-08 15:59:07 245654 67 2005-12-08 15:59:08 245655 67 2005-12-08 15:59:08 398340 67 2006-02-20 04:38:53 398341 67 2006-02-20 04:48:44 398545 67 2006-02-20 04:53:13 406631 67 2006-02-23 10:49:42 406988 67 2006-02-23 17:07:22 406989 67 2006-02-23 17:08:46 406990 67 2006-02-23 18:01:45 406991 67 2006-02-24 16:42:32 406992 67 2006-02-24 16:47:18 406993 67 2006-02-27 11:20:57 406994 67 2006-02-27 11:26:46 406995 67 2006-02-28 11:55:00 select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null order by t12.a; a 255 256 update t22 set c = '2005-12-08 15:58:27' where a = 255; select * from t22 order by 1,2,3; a b c 1 67 2006-02-23 15:01:35 254 67 NULL 255 67 2005-12-08 15:58:27 256 67 NULL 1120 67 NULL 1133 67 NULL 4101 67 NULL 9199 67 NULL 223456 67 NULL 245651 67 2005-12-08 15:58:27 245652 67 2005-12-08 15:58:27 245653 67 2005-12-08 15:59:07 245654 67 2005-12-08 15:59:08 245655 67 2005-12-08 15:59:08 398340 67 2006-02-20 04:38:53 398341 67 2006-02-20 04:48:44 398545 67 2006-02-20 04:53:13 406631 67 2006-02-23 10:49:42 406988 67 2006-02-23 17:07:22 406989 67 2006-02-23 17:08:46 406990 67 2006-02-23 18:01:45 406991 67 2006-02-24 16:42:32 406992 67 2006-02-24 16:47:18 406993 67 2006-02-27 11:20:57 406994 67 2006-02-27 11:26:46 406995 67 2006-02-28 11:55:00 select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; a b c 256 67 NULL delete from t22 where a > 245651; update t22 set b = a + 1; select * from t22 order by 1,2,3; a b c 1 2 2006-02-23 15:01:35 254 255 NULL 255 256 2005-12-08 15:58:27 256 257 NULL 1120 1121 NULL 1133 1134 NULL 4101 4102 NULL 9199 9200 NULL 223456 223457 NULL 245651 245652 2005-12-08 15:58:27 select t21.c, count(*) from t21 inner join t22 using (a) where t22.b in (2,256,257,1121,1134,4102,9200,223457,245652) group by t21.c order by t21.c; c count(*) NULL 7 2005-12-08 15:58:27 1 2006-02-23 15:01:35 1 DROP TABLE t1, t11, t12, t21, t22; CREATE TABLE t1 (id varchar(255) NOT NULL, tag int(11) NOT NULL, doc text NOT NULL, type varchar(150) NOT NULL, modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE=ndbcluster; INSERT INTO t1 VALUES ('sakila',1,'Some text goes here','text',CURRENT_TIMESTAMP); SELECT id, tag, doc, type FROM t1 WHERE id IN ('flipper','orka'); id tag doc type SELECT id, tag, doc, type FROM t1 WHERE id IN ('flipper','sakila'); id tag doc type sakila 1 Some text goes here text DROP TABLE t1;