#At file:///home/nirbhay/Project/mysql/repo/wl/mysql-next-mr-bugfixing-44771/ based on revid:dao-gang.qu@stripped
3326 Nirbhay Choubey 2010-10-19
Bug#44771 : Unique Hash index in memory engine will give wrong query result for
NULL value.
In a memory table when there are more than one NULL values for a unique key
column using HASH index, a search for NULL on that column returns just one
row.
In memory engine, while reading a hash key which is NULL, it doesn't get copied
to info->lastkey, which inturn is used to search next records.
Fixed by adding the condition to check for NULL key, which lets copying of key
to info->lastkey.
@ mysql-test/r/heap_hash.result
Bug#44771 : Unique Hash index in memory engine will give wrong query result for
NULL value.
Added a test case for bug#44771.
@ mysql-test/t/heap_hash.test
Bug#44771 : Unique Hash index in memory engine will give wrong query result for
NULL value.
Added a test case for bug#44771.
@ storage/heap/hp_rkey.c
Bug#44771 : Unique Hash index in memory engine will give wrong query result for
NULL value.
Modified the condition to check for NULL key which is checked before copying key
to info->lastkey in heap_rkey function. info->lastkey is further used by
heap_rnext to search for the records with the same key.
modified:
mysql-test/r/heap_hash.result
mysql-test/t/heap_hash.test
storage/heap/hp_rkey.c
=== modified file 'mysql-test/r/heap_hash.result'
--- a/mysql-test/r/heap_hash.result 2010-07-28 21:02:43 +0000
+++ b/mysql-test/r/heap_hash.result 2010-10-19 12:19:16 +0000
@@ -392,4 +392,28 @@ INDEX(col_int_key) USING HASH) ENGINE =
INSERT INTO t1 (col_int_nokey, col_int_key) VALUES (3, 0), (4, 0), (3, 1);
DELETE FROM t1 WHERE col_int_nokey = 5 ORDER BY col_int_key LIMIT 2;
DROP TABLE t1;
+#
+# Bug #44771: Unique Hash index in memory engine will give wrong
+# query result for NULL value.
+#
+CREATE TABLE t1
+(
+pk INT PRIMARY KEY,
+val INT,
+UNIQUE KEY USING HASH(val)
+) ENGINE=MEMORY;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, NULL);
+INSERT INTO t1 VALUES (3, NULL);
+INSERT INTO t1 VALUES (4, NULL);
+SELECT * FROM t1 WHERE val IS NULL;
+pk val
+4 NULL
+3 NULL
+2 NULL
+1 NULL
+EXPLAIN SELECT * FROM t1 WHERE val IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref val val 5 const 1 Using where
+DROP TABLE t1;
End of 5.5 tests
=== modified file 'mysql-test/t/heap_hash.test'
--- a/mysql-test/t/heap_hash.test 2010-07-28 21:02:43 +0000
+++ b/mysql-test/t/heap_hash.test 2010-10-19 12:19:16 +0000
@@ -299,5 +299,26 @@ DELETE FROM t1 WHERE col_int_nokey = 5 O
DROP TABLE t1;
+--echo #
+--echo # Bug #44771: Unique Hash index in memory engine will give wrong
+--echo # query result for NULL value.
+--echo #
+
+CREATE TABLE t1
+(
+ pk INT PRIMARY KEY,
+ val INT,
+ UNIQUE KEY USING HASH(val)
+) ENGINE=MEMORY;
+
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, NULL);
+INSERT INTO t1 VALUES (3, NULL);
+INSERT INTO t1 VALUES (4, NULL);
+
+SELECT * FROM t1 WHERE val IS NULL;
+EXPLAIN SELECT * FROM t1 WHERE val IS NULL;
+DROP TABLE t1;
+
--echo End of 5.5 tests
=== modified file 'storage/heap/hp_rkey.c'
--- a/storage/heap/hp_rkey.c 2009-12-22 09:35:56 +0000
+++ b/storage/heap/hp_rkey.c 2010-10-19 12:19:16 +0000
@@ -63,7 +63,12 @@ int heap_rkey(HP_INFO *info, uchar *reco
info->update= 0;
DBUG_RETURN(my_errno);
}
- if (!(keyinfo->flag & HA_NOSAME))
+ /*
+ If key is unique and can accept NULL values, we still
+ need to copy it to info->lastkey, which in turn is used
+ to search subsequent records.
+ */
+ if (!(keyinfo->flag & HA_NOSAME) || (keyinfo->flag & HA_NULL_PART_KEY))
memcpy(info->lastkey, key, (size_t) keyinfo->length);
}
memcpy(record, pos, (size_t) share->reclength);
Attachment: [text/bzr-bundle]
