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. 5.1 re-implementation

1 files changed, 34 insertions, 1 deletions

diff --git a/mysql-test/r/ndb_index_unique.result b/mysql-test/r/ndb_index_unique.result
index 563024a8800..fbc3a2bb915 100644
--- a/mysql-test/r/ndb_index_unique.result
+++ b/mysql-test/r/ndb_index_unique.result
@@ -144,7 +144,40 @@ b int unsigned not null,
 c int unsigned,
 UNIQUE (b, c) USING HASH	
 ) engine=ndbcluster;
-ERROR 42000: Table handler doesn't support NULL in given index. Please change column 'c' to be NOT NULL or use another handler
+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,