Updated ndb_basic test

2 files changed, 420 insertions, 2 deletions

diff --git a/mysql-test/r/ndb_basic.result b/mysql-test/r/ndb_basic.result
index 4e26ac8e0b5..baa51cd018e 100644
--- a/mysql-test/r/ndb_basic.result
+++ b/mysql-test/r/ndb_basic.result
@@ -1,4 +1,4 @@
-DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7;
 CREATE TABLE t1 (
 pk1 INT NOT NULL PRIMARY KEY,
 attr1 INT NOT NULL
@@ -128,3 +128,213 @@ attr1 INT NOT NULL
 ) ENGINE=NDB;
 INSERT INTO t1 values(1, 9999);
 DROP TABLE t1;
+CREATE TABLE t2 (
+a bigint unsigned NOT NULL PRIMARY KEY,
+b int unsigned not null,
+c int unsigned
+) engine=ndbcluster;
+CREATE TABLE t3 (
+a bigint unsigned NOT NULL,
+b bigint unsigned not null,
+c bigint unsigned,
+PRIMARY KEY(a)
+) engine=ndbcluster;
+CREATE TABLE t4 (
+a bigint unsigned NOT NULL,
+b bigint unsigned not null,
+c bigint unsigned NOT NULL,
+d int unsigned,
+PRIMARY KEY(a, b, c)
+) engine=ndbcluster;
+select * from t2 where a = 7 order by b;
+a	b	c
+7	16	5
+select * from t2 where a = 7 order by a;
+a	b	c
+7	16	5
+select * from t2 where a = 7 order by 2;
+a	b	c
+7	16	5
+select * from t2 where a = 7 order by c;
+a	b	c
+7	16	5
+select * from t2 where a = 7 and b = 16 order by b;
+a	b	c
+7	16	5
+select * from t2 where a = 7 and b = 16 order by a;
+a	b	c
+7	16	5
+select * from t2 where a = 7 and b = 17 order by a;
+a	b	c
+select * from t2 where a = 7 and b != 16 order by b;
+a	b	c
+select * from t2 where a = 7 and b = 16 and c = 5 order by b;
+a	b	c
+7	16	5
+select * from t2 where a = 7 and b = 16 and c = 5 order by a;
+a	b	c
+7	16	5
+select * from t2 where a = 7 and b = 16 and c = 6 order by a;
+a	b	c
+select * from t2 where a = 7 and b != 16 and c = 5 order by b;
+a	b	c
+select * from t3 where a = 7 order by b;
+a	b	c
+7	16	5
+select * from t3 where a = 7 order by a;
+a	b	c
+7	16	5
+select * from t3 where a = 7 order by 2;
+a	b	c
+7	16	5
+select * from t3 where a = 7 order by c;
+a	b	c
+7	16	5
+select * from t3 where a = 7 and b = 16 order by b;
+a	b	c
+7	16	5
+select * from t3 where a = 7 and b = 16 order by a;
+a	b	c
+7	16	5
+select * from t3 where a = 7 and b = 17 order by a;
+a	b	c
+select * from t3 where a = 7 and b != 16 order by b;
+a	b	c
+select * from t4 where a = 7 order by b;
+a	b	c	d
+7	16	5	26007
+select * from t4 where a = 7 order by a;
+a	b	c	d
+7	16	5	26007
+select * from t4 where a = 7 order by 2;
+a	b	c	d
+7	16	5	26007
+select * from t4 where a = 7 order by c;
+a	b	c	d
+7	16	5	26007
+select * from t4 where a = 7 and b = 16 order by b;
+a	b	c	d
+7	16	5	26007
+select * from t4 where a = 7 and b = 16 order by a;
+a	b	c	d
+7	16	5	26007
+select * from t4 where a = 7 and b = 17 order by a;
+a	b	c	d
+select * from t4 where a = 7 and b != 16 order by b;
+a	b	c	d
+delete from t2;
+delete from t3;
+delete from t4;
+drop table t2;
+drop table t3;
+drop table t4;
+CREATE TABLE t5 (
+a bigint unsigned NOT NULL,
+b bigint unsigned not null,
+c bigint unsigned NOT NULL,
+d int unsigned,
+PRIMARY KEY(a, b, c)
+) engine=ndbcluster;
+insert into t5 values(10, 19, 5, 26010);
+delete from t5 where a=10 and b=19 and c=5;
+select * from t5;
+a	b	c	d
+insert into t5 values(10, 19, 5, 26010);
+update t5 set d=21997 where a=10 and b=19 and c=5;
+select * from t5;
+a	b	c	d
+10	19	5	21997
+delete from t5;
+drop table t5;
+CREATE TABLE t6 (
+adress char(255),
+a int NOT NULL PRIMARY KEY,
+b int
+) engine = NDB;
+insert into t6 values
+("Nice road 3456", 1, 23),
+("Street Road 78", 3, 92),
+("Road street 89C", 5, 71),
+(NULL, 7, NULL);
+select * from t6 order by a;
+adress	a	b
+Nice road 3456	1	23
+Street Road 78	3	92
+Road street 89C	5	71
+NULL	7	NULL
+select a, b from t6 order by a;
+a	b
+1	23
+3	92
+5	71
+7	NULL
+update t6 set adress="End of road 09" where a=3;
+update t6 set b=181, adress="Street 76" where a=7;
+select * from t6 order by a;
+adress	a	b
+Nice road 3456	1	23
+End of road 09	3	92
+Road street 89C	5	71
+Street 76	7	181
+select * from t6 where a=1;
+adress	a	b
+Nice road 3456	1	23
+delete from t6 where a=1;
+select * from t6 order by a;
+adress	a	b
+End of road 09	3	92
+Road street 89C	5	71
+Street 76	7	181
+delete from t6 where b=71;
+select * from t6 order by a;
+adress	a	b
+End of road 09	3	92
+Street 76	7	181
+drop table t6;
+CREATE TABLE t7 (
+adress char(255),
+a int NOT NULL,
+b int,
+c int NOT NULL,
+PRIMARY KEY(a, c)	
+) engine = NDB;
+insert into t7 values
+("Highway 3456", 1, 23, 2),
+("Street Road 78", 3, 92, 3),
+("Main street 89C", 5, 71, 4),
+(NULL, 8, NULL, 12);
+select * from t7;
+adress	a	b	c
+Street Road 78	3	92	3
+Highway 3456	1	23	2
+NULL	8	NULL	12
+Main street 89C	5	71	4
+select a, b from t7;
+a	b
+3	92
+1	23
+8	NULL
+5	71
+update t7 set adress="End of road 09" where a=3;
+update t7 set adress="Gatuvägen 90C" where a=5 and c=4;
+update t7 set adress="No adress" where adress is NULL;
+select * from t7;
+adress	a	b	c
+Gatuvägen 90C	5	71	4
+End of road 09	3	92	3
+Highway 3456	1	23	2
+No adress	8	NULL	12
+select * from t7 where a=1 and c=2;
+adress	a	b	c
+Highway 3456	1	23	2
+delete from t7 where a=1;
+delete from t7 where a=3 and c=3;
+delete from t7 where a=5 and c=4;
+select * from t7;
+adress	a	b	c
+No adress	8	NULL	12
+delete from t7 where b=23;
+select * from t7;
+adress	a	b	c
+No adress	8	NULL	12
+drop table t7;
diff --git a/mysql-test/t/ndb_basic.test b/mysql-test/t/ndb_basic.test
index 42dd9f0b8ed..05fb4e1103e 100644
--- a/mysql-test/t/ndb_basic.test
+++ b/mysql-test/t/ndb_basic.test
@@ -1,7 +1,7 @@
 -- source include/have_ndb.inc
 
 --disable_warnings
-DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7;
 --enable_warnings
 
 #
@@ -107,3 +107,211 @@ CREATE TABLE t1 (
 INSERT INTO t1 values(1, 9999);
 
 DROP TABLE t1;
+
+
+#
+# A more extensive test with a lot more records
+#
+
+CREATE TABLE t2 (
+  a bigint unsigned NOT NULL PRIMARY KEY,
+  b int unsigned not null,
+  c int unsigned
+) engine=ndbcluster;
+
+CREATE TABLE t3 (
+  a bigint unsigned NOT NULL,
+  b bigint unsigned not null,
+  c bigint unsigned,
+  PRIMARY KEY(a)
+) engine=ndbcluster;
+
+CREATE TABLE t4 (
+  a bigint unsigned NOT NULL,
+  b bigint unsigned not null,
+  c bigint unsigned NOT NULL,
+  d int unsigned,
+  PRIMARY KEY(a, b, c)
+) engine=ndbcluster;
+
+
+#
+# insert more records into tables
+#
+let $1=1000;
+disable_query_log;
+while ($1)
+{
+ eval insert into t2 values($1, $1+9, 5);
+ eval insert into t3 values($1, $1+9, 5);
+ eval insert into t4 values($1, $1+9, 5, $1+26000);
+ dec $1;
+}
+enable_query_log;
+
+
+#
+# delete every other record in the tables
+#
+let $1=1000;
+disable_query_log;
+while ($1)
+{
+ eval delete from t2 where a=$1;
+ eval delete from t3 where a=$1;
+ eval delete from t4 where a=$1 and b=$1+9 and c=5;
+ dec $1;
+ dec $1;
+}
+enable_query_log;
+
+
+select * from t2 where a = 7 order by b;
+select * from t2 where a = 7 order by a;
+select * from t2 where a = 7 order by 2;
+select * from t2 where a = 7 order by c;
+
+select * from t2 where a = 7 and b = 16 order by b;
+select * from t2 where a = 7 and b = 16 order by a;
+select * from t2 where a = 7 and b = 17 order by a;
+select * from t2 where a = 7 and b != 16 order by b;
+
+select * from t2 where a = 7 and b = 16 and c = 5 order by b;
+select * from t2 where a = 7 and b = 16 and c = 5 order by a;
+select * from t2 where a = 7 and b = 16 and c = 6 order by a;
+select * from t2 where a = 7 and b != 16 and c = 5 order by b;
+
+select * from t3 where a = 7 order by b;
+select * from t3 where a = 7 order by a;
+select * from t3 where a = 7 order by 2;
+select * from t3 where a = 7 order by c;
+
+select * from t3 where a = 7 and b = 16 order by b;
+select * from t3 where a = 7 and b = 16 order by a;
+select * from t3 where a = 7 and b = 17 order by a;
+select * from t3 where a = 7 and b != 16 order by b;
+
+select * from t4 where a = 7 order by b;
+select * from t4 where a = 7 order by a;
+select * from t4 where a = 7 order by 2;
+select * from t4 where a = 7 order by c;
+
+select * from t4 where a = 7 and b = 16 order by b;
+select * from t4 where a = 7 and b = 16 order by a;
+select * from t4 where a = 7 and b = 17 order by a;
+select * from t4 where a = 7 and b != 16 order by b;
+
+#
+# update records
+#
+let $1=1000;
+disable_query_log;
+while ($1)
+{
+ eval update t2 set c=$1 where a=$1;
+ eval update t3 set c=7 where a=$1 and b=$1+9 and c=5;
+ eval update t4 set d=$1+21987 where a=$1 and b=$1+9 and c=5;
+ dec $1;
+ dec $1;
+}
+enable_query_log;
+
+delete from t2;
+delete from t3;
+delete from t4;
+
+drop table t2;
+drop table t3;
+drop table t4;
+
+#
+# Test delete and update from table with 3 keys
+#
+
+CREATE TABLE t5 (
+  a bigint unsigned NOT NULL,
+  b bigint unsigned not null,
+  c bigint unsigned NOT NULL,
+  d int unsigned,
+  PRIMARY KEY(a, b, c)
+) engine=ndbcluster;
+
+insert into t5 values(10, 19, 5, 26010);
+
+delete from t5 where a=10 and b=19 and c=5;
+
+select * from t5;
+
+insert into t5 values(10, 19, 5, 26010);
+
+update t5 set d=21997 where a=10 and b=19 and c=5;
+
+select * from t5;
+
+delete from t5;
+
+drop table t5;
+
+#
+# Test using table with a char(255) column first in table
+#
+
+CREATE TABLE t6 (
+  adress char(255),
+  a int NOT NULL PRIMARY KEY,
+  b int
+) engine = NDB;
+
+insert into t6 values
+ ("Nice road 3456", 1, 23),
+ ("Street Road 78", 3, 92),
+ ("Road street 89C", 5, 71),
+ (NULL, 7, NULL);
+select * from t6 order by a;
+select a, b from t6 order by a;
+
+update t6 set adress="End of road 09" where a=3;
+update t6 set b=181, adress="Street 76" where a=7;
+select * from t6 order by a;
+select * from t6 where a=1;
+delete from t6 where a=1;
+select * from t6 order by a;
+delete from t6 where b=71;
+select * from t6 order by a;
+
+drop table t6;
+
+#
+# Test using table with a char(255) column first in table and a 
+# primary key consisting of two columns
+#
+
+CREATE TABLE t7 (
+  adress char(255),
+  a int NOT NULL,
+  b int,
+  c int NOT NULL,
+  PRIMARY KEY(a, c)	
+) engine = NDB;
+
+insert into t7 values
+ ("Highway 3456", 1, 23, 2),
+ ("Street Road 78", 3, 92, 3),
+ ("Main street 89C", 5, 71, 4),
+ (NULL, 8, NULL, 12);
+select * from t7;
+select a, b from t7;
+
+update t7 set adress="End of road 09" where a=3;
+update t7 set adress="Gatuvägen 90C" where a=5 and c=4;
+update t7 set adress="No adress" where adress is NULL;
+select * from t7;
+select * from t7 where a=1 and c=2;
+delete from t7 where a=1;
+delete from t7 where a=3 and c=3;
+delete from t7 where a=5 and c=4;
+select * from t7;
+delete from t7 where b=23;
+select * from t7;
+
+drop table t7;