From 1050d648199d963e19bde905cdbe778d7361b693 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 7 Nov 2006 16:38:37 +0100 Subject: bug#21507 I can't create a unique hash index in NDB: Added possibillity to create hash only indexes with NULL valued attributes, but any NULL valued access will become full table scan with pushed condition on index attribute values --- mysql-test/r/ndb_index_unique.result | 35 ++++++++++++++++++++++++++++++++++- 1 file changed, 34 insertions(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/ndb_index_unique.result b/mysql-test/r/ndb_index_unique.result index ce4759ad553..12ffc8437db 100644 --- a/mysql-test/r/ndb_index_unique.result +++ b/mysql-test/r/ndb_index_unique.result @@ -140,7 +140,40 @@ b int unsigned not null, c int unsigned, UNIQUE USING HASH (b, c) ) engine=ndbcluster; -ERROR 42000: Column 'c' is used with UNIQUE or INDEX but is not defined as NOT NULL +Warnings: +Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan +insert t2 values(1,1,NULL),(2,2,2),(3,3,NULL),(4,4,4),(5,5,NULL),(6,6,6),(7,7,NULL),(8,3,NULL),(9,3,NULL); +select * from t2 where c IS NULL order by a; +a b c +1 1 NULL +3 3 NULL +5 5 NULL +7 7 NULL +8 3 NULL +9 3 NULL +select * from t2 where b = 3 AND c IS NULL order by a; +a b c +3 3 NULL +8 3 NULL +9 3 NULL +select * from t2 where (b = 3 OR b = 5) AND c IS NULL order by a; +a b c +3 3 NULL +5 5 NULL +8 3 NULL +9 3 NULL +set @old_ecpd = @@session.engine_condition_pushdown; +set engine_condition_pushdown = true; +explain select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY,b PRIMARY 4 NULL 1 Using where with pushed condition +select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a; +a b c +3 3 NULL +5 5 NULL +8 3 NULL +set engine_condition_pushdown = @old_ecpd; +drop table t2; CREATE TABLE t3 ( a int unsigned NOT NULL, b int unsigned not null, -- cgit v1.2.1 From 3d629d9f43095387b3b1bb07a36e1a73c4a96230 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 29 Nov 2006 11:53:43 +0100 Subject: bug#24303 Wrong result for UPDATE IGNORE for NDB table: Added test case --- mysql-test/r/ndb_update.result | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/ndb_update.result b/mysql-test/r/ndb_update.result index 164d1bd700c..52236bb0c3d 100644 --- a/mysql-test/r/ndb_update.result +++ b/mysql-test/r/ndb_update.result @@ -17,6 +17,14 @@ pk1 b c 0 0 0 2 2 2 4 1 1 +UPDATE t1 set pk1 = 4 where pk1 = 2; +ERROR 23000: Duplicate entry '4' for key 1 +UPDATE IGNORE t1 set pk1 = 4 where pk1 = 2; +select * from t1 order by pk1; +pk1 b c +0 0 0 +2 2 2 +4 1 1 UPDATE t1 set pk1 = 1, c = 2 where pk1 = 4; ERROR 23000: Duplicate entry '' for key 0 select * from t1 order by pk1; -- cgit v1.2.1 From d4efa758f2a5e543492d4ba0a985a91e3229e41f Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 30 Nov 2006 15:02:33 +0100 Subject: #18487 UPDATE IGNORE not supported for unique constraint violation of non-primary key: call peek_index_rows --- mysql-test/r/ndb_update.result | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/ndb_update.result b/mysql-test/r/ndb_update.result index 52236bb0c3d..d75f82172ae 100644 --- a/mysql-test/r/ndb_update.result +++ b/mysql-test/r/ndb_update.result @@ -27,6 +27,7 @@ pk1 b c 4 1 1 UPDATE t1 set pk1 = 1, c = 2 where pk1 = 4; ERROR 23000: Duplicate entry '' for key 0 +UPDATE IGNORE t1 set pk1 = 1, c = 2 where pk1 = 4; select * from t1 order by pk1; pk1 b c 0 0 0 -- cgit v1.2.1