Merge mskold@bk-internal.mysql.com:/home/bk/mysql-4.1-ndb

into mysql.com:/usr/local/home/marty/MySQL/mysql-4.1-ndb

3 files changed, 130 insertions, 2 deletions

diff --git a/mysql-test/r/ndb_index_unique.result b/mysql-test/r/ndb_index_unique.result
index ed97e0b110a..7ec2ef3a2f1 100644
--- a/mysql-test/r/ndb_index_unique.result
+++ b/mysql-test/r/ndb_index_unique.result
@@ -21,6 +21,28 @@ insert into t1 values(7,8,3);
 select * from t1 where b = 4 order by a;
 a	b	c
 3	4	6
+insert into t1 values(8, 2, 3);
+ERROR 23000: Can't write, because of unique constraint, to table 't1'
+select * from t1 order by a;
+a	b	c
+1	2	3
+2	3	5
+3	4	6
+4	5	8
+5	6	2
+6	7	2
+7	8	3
+delete from t1 where a = 1;
+insert into t1 values(8, 2, 3);
+select * from t1 order by a;
+a	b	c
+2	3	5
+3	4	6
+4	5	8
+5	6	2
+6	7	2
+7	8	3
+8	2	3
 drop table t1;
 CREATE TABLE t2 (
 a int unsigned NOT NULL PRIMARY KEY,
@@ -42,6 +64,28 @@ insert into t2 values(7,8,3);
 select * from t2 where b = 4 order by a;
 a	b	c
 3	4	6
+insert into t2 values(8, 2, 3);
+ERROR 23000: Can't write, because of unique constraint, to table 't2'
+select * from t2 order by a;
+a	b	c
+1	2	3
+2	3	5
+3	4	6
+4	5	8
+5	6	2
+6	7	2
+7	8	3
+delete from t2 where a = 1;
+insert into t2 values(8, 2, 3);
+select * from t2 order by a;
+a	b	c
+2	3	5
+3	4	6
+4	5	8
+5	6	2
+6	7	2
+7	8	3
+8	2	3
 drop table t2;
 CREATE TABLE t3 (
 a int unsigned NOT NULL,
@@ -74,8 +118,10 @@ INSERT INTO t1 VALUES (8,'dummy');
 CREATE TABLE t2 (
 cid bigint(20) unsigned NOT NULL auto_increment,
 cap varchar(255) NOT NULL default '',
-PRIMARY KEY  (cid)
+PRIMARY KEY  (cid),
+UNIQUE KEY (cid, cap)
 ) engine=ndbcluster;
+INSERT INTO t2 VALUES (NULL,'another dummy');
 CREATE TABLE t3 (
 gid bigint(20) unsigned NOT NULL auto_increment,
 gn varchar(255) NOT NULL default '',
@@ -132,6 +178,9 @@ cid	cv
 8	dummy
 select * from t1 where cv = 'test';
 cid	cv
+select * from t2 where cap = 'another dummy';
+cid	cap
+0	another dummy
 select * from t4 where uid = 1 and gid=1 and rid=2 and cid=4;
 uid	gid	rid	cid
 1	1	2	4
diff --git a/mysql-test/t/ndb_index_ordered.test b/mysql-test/t/ndb_index_ordered.test
index 09c87a44084..3def52e865c 100644
--- a/mysql-test/t/ndb_index_ordered.test
+++ b/mysql-test/t/ndb_index_ordered.test
@@ -114,3 +114,23 @@ select * from t1 where b=4 and c<=5 order by a;
 select * from t1 where b<=4 and c<=5 order by a;
 select * from t1 where b<=5 and c=0 or b<=5 and c=2;
 drop table t1;
+
+#
+# Indexing NULL values
+#
+
+#CREATE TABLE t1 (
+#  a int unsigned NOT NULL PRIMARY KEY,
+#  b int unsigned,
+#  c int unsigned,
+#  KEY bc(b,c)
+#) engine = ndb;
+
+#insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
+#select * from t1 use index (bc);
+#select count(*) from t1 use index (bc);
+#select count(*) from t1 use index (PRIMARY) where b IS NULL;
+#select count(*) from t1 use index (bc) where b IS NULL;
+#select count(*) from t1 use index (bc) where b IS NULL and c = 2;
+#select count(*) from t1 use index (bc) where b IS NOT NULL;
+#drop table t1;
diff --git a/mysql-test/t/ndb_index_unique.test b/mysql-test/t/ndb_index_unique.test
index 7cfc9a77452..96abc842639 100644
--- a/mysql-test/t/ndb_index_unique.test
+++ b/mysql-test/t/ndb_index_unique.test
@@ -21,6 +21,13 @@ select * from t1 where b = 4 order by b;
 insert into t1 values(7,8,3);
 select * from t1 where b = 4 order by a;
 
+-- error 1169
+insert into t1 values(8, 2, 3);
+select * from t1 order by a;
+delete from t1 where a = 1;
+insert into t1 values(8, 2, 3);
+select * from t1 order by a;
+
 drop table t1;
 
 
@@ -42,6 +49,13 @@ select * from t2 where c = 6;
 insert into t2 values(7,8,3);
 select * from t2 where b = 4 order by a;
 
+-- error 1169
+insert into t2 values(8, 2, 3);
+select * from t2 order by a;
+delete from t2 where a = 1;
+insert into t2 values(8, 2, 3);
+select * from t2 order by a;
+
 drop table t2;
 
 #
@@ -65,6 +79,48 @@ select * from t3 where b = 4 order by a;
 drop table t3;
 
 #
+# Indexes on NULL-able columns 
+#
+
+#CREATE TABLE t1 (
+#  pk int NOT NULL PRIMARY KEY,
+#  a int unsigned,
+#  UNIQUE KEY (a)
+#) engine=ndbcluster;
+		 
+#insert into t1 values (-1,NULL), (0,0), (1,NULL),(2,2),(3,NULL),(4,4);
+
+#select * from t1 order by pk;
+
+#--error 1169
+#insert into t1 values (5,0);
+#select * from t1 order by pk;
+#delete from t1 where a = 0;
+#insert into t1 values (5,0);
+#select * from t1 order by pk;
+
+#CREATE TABLE t2 (
+#  pk int NOT NULL PRIMARY KEY,
+#  a int unsigned,
+#  b tinyint NOT NULL,
+#  c VARCHAR(10),
+#  UNIQUE KEY si(a, c)
+#) engine=ndbcluster;
+
+#insert into t2 values (-1,1,17,NULL),(0,NULL,18,NULL),(1,3,19,'abc');
+
+#select * from t2 order by pk;
+
+#--error 1169
+#insert into t2 values(2,3,19,'abc');
+#select * from t2 order by pk;
+#delete from t2 where c IS NOT NULL;
+#insert into t2 values(2,3,19,'abc');
+#select * from t2 order by pk;
+		 
+#drop table t1, t2;
+
+#
 # More complex tables
 #
 
@@ -78,8 +134,10 @@ INSERT INTO t1 VALUES (8,'dummy');
 CREATE TABLE t2 (
   cid bigint(20) unsigned NOT NULL auto_increment,
   cap varchar(255) NOT NULL default '',
-  PRIMARY KEY  (cid)
+  PRIMARY KEY  (cid),
+  UNIQUE KEY (cid, cap)
 ) engine=ndbcluster;
+INSERT INTO t2 VALUES (NULL,'another dummy');
 CREATE TABLE t3 (
   gid bigint(20) unsigned NOT NULL auto_increment,
   gn varchar(255) NOT NULL default '',
@@ -134,6 +192,7 @@ INSERT INTO t7 VALUES(10, 5, 1, 1, 10);
 
 select * from t1 where cv = 'dummy';
 select * from t1 where cv = 'test';
+select * from t2 where cap = 'another dummy';
 select * from t4 where uid = 1 and gid=1 and rid=2 and cid=4;
 select * from t4 where uid = 1 and gid=1 and rid=1 and cid=4;
 select * from t4 where uid = 1 order by cid;