1 files changed, 340 insertions, 0 deletions
diff --git a/mysql-test/suite/ndb/t/ndb_read_multi_range.test b/mysql-test/suite/ndb/t/ndb_read_multi_range.test
new file mode 100644
index 00000000000..1965874940b
--- /dev/null
+++ b/mysql-test/suite/ndb/t/ndb_read_multi_range.test
@@ -0,0 +1,340 @@
+-- source include/have_ndb.inc
+-- source include/not_embedded.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2, r1;
+--enable_warnings
+
+#
+# Basic test to see that batching is working
+#
+
+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);
+
+# batch on primary key
+create table r1 as select * from t1 where a in (2,8,12);
+select * from r1 order by a;
+drop table r1;
+
+# batch on ordered index
+create table r1 as select * from t1 where b in (1,2,5);
+select * from r1 order by a;
+drop table r1;
+
+# batch on unique hash index
+create table r1 as select * from t1 where c in (2,8,12);
+select * from r1 order by a;
+drop table r1;
+
+# batch mixed
+create table r1 as select * from t1 where a in (2,8) or (a > 11) or (a <= 1);
+select * from r1 order by a;
+drop table r1;
+
+# batch on primary key, missing values
+create table r1 as select * from t1 where a in (33,8,12);
+select * from r1 order by a;
+drop table r1;
+create table r1 as select * from t1 where a in (2,33,8,12,34);
+select * from r1 order by a;
+drop table r1;
+
+# batch on ordered index, missing values
+create table r1 as select * from t1 where b in (1,33,5);
+select * from r1 order by a;
+drop table r1;
+select * from t1 where b in (1,33,5) order by a;
+create table r1 as select * from t1 where b in (45,1,33,5,44);
+select * from r1 order by a;
+drop table r1;
+select * from t1 where b in (45,22) order by a;
+
+# batch on unique hash index, missing values
+create table r1 as select * from t1 where c in (2,8,33);
+select * from r1 order by a;
+drop table r1;
+create table r1 as select * from t1 where c in (13,2,8,33,12);
+select * from r1 order by a;
+drop table r1;
+
+select * from t1 where a in (33,8,12) order by a;
+select * from t1 where a in (33,34,35) order by a;
+select * from t1 where a in (2,8) or (a > 11) or (a <= 1) order by a;
+select * from t1 where b in (6,7) or (b <= 5) or (b >= 10) order by b,a;
+select * from t1 where c in (13,2,8,33,12) order by c,a;
+drop table t1;
+
+#
+# Somewhat more complicated
+#
+
+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);
+
+# batch on primary key
+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;
+drop table r1;
+
+# batched update ordered index, one value for all
+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;
+select * from t1 where d not in (12,6,7) and e = 100;
+
+# batched update primary key, one value for all
+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;
+select * 
+  from t1 
+  where not (a=1 and b=2 and c=1 and d in (1,4,3,2))
+        and e=101;
+
+
+# batched update ordered index, different values
+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;
+
+# batched update primary key, different values
+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;
+
+# batched delete
+delete from t1 where d in (12,6,7);
+select * from t1 where d in (12,6,7);
+
+drop table t1;
+
+# null handling
+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;
+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;
+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;
+drop table t2;
+
+drop table t1;
+
+# bug17729
+
+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;
+select * from t12 order by 1,2,3;
+select * from t21 order by 1,2,3;
+select * from t22 order by 1,2,3;
+
+# join tests
+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;
+
+update t22 set c = '2005-12-08 15:58:27' where a = 255;
+select * from t22 order by 1,2,3;
+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;
+
+delete from t22 where a > 245651;
+update t22 set b = a + 1;
+select * from t22 order by 1,2,3;
+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;
+
+DROP TABLE t1, t11, t12, t21, t22;
+
+# bug#19956
+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');
+SELECT id, tag, doc, type FROM t1 WHERE id IN ('flipper','sakila');
+
+DROP TABLE t1;
+
+#bug#25522
+CREATE TABLE t1 (
+       var1 int(2) NOT NULL,
+       var2 int(2) NOT NULL,
+       PRIMARY KEY  (var1)
+     ) ENGINE=ndbcluster DEFAULT CHARSET=ascii CHECKSUM=1;
+
+
+CREATE TABLE t2 (
+       var1 int(2) NOT NULL,
+       var2 int(2) NOT NULL,
+       PRIMARY KEY  (var1)
+     ) ENGINE=ndbcluster DEFAULT CHARSET=ascii CHECKSUM=1;
+
+
+DELIMITER |;
+CREATE TRIGGER testtrigger
+     AFTER UPDATE ON t1 FOR EACH ROW BEGIN
+     REPLACE INTO t2 SELECT * FROM t1 WHERE t1.var1 = NEW.var1;END|
+DELIMITER ;|
+
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
+
+UPDATE t1 SET var2 = 9 WHERE var1 IN(1,2,3);
+
+DROP TRIGGER testtrigger;
+
+DROP TABLE t1, t2;
+
+#bug#25821
+create table t1 (a int, b int, primary key (a), key ab (a,b)) engine=ndbcluster;
+
+insert into t1 values (1,1), (10,10);
+
+select * from t1 use index (ab) where a in(1,10) order by a;
+
+create table t2 (a int, b int, primary key (a,b)) engine=ndbcluster
+partition by key(a);
+
+insert into t2 values (1,1), (10,10);
+
+select * from t2 where a in (1,10) order by a;
+drop table t1, t2;
+
+#bug#30337
+
+create table t1 (id int primary key) engine ndb;
+insert into t1 values (1), (2), (3);
+
+create table t2 (id int primary key) engine ndb;
+insert into t2 select id from t1;
+
+delimiter |;
+create trigger kaboom after delete on t1
+for each row begin
+  delete from t2 where id=old.id;
+end|
+delimiter ;|
+
+select * from t1 order by id;
+delete from t1 where id in (1,2);
+select * from t2 order by id;
+
+drop trigger kaboom;
+drop table t1;