MySQL Bugs: #2688: Wrong index_merge query results for BDB table with variable length primary key

Bug #2688	Wrong index_merge query results for BDB table with variable length primary key
Submitted:	9 Feb 2004 8:34	Modified:	24 Jun 2004 14:40
Reporter:	Sergey Petrunya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0	OS:
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
Running index_merge queries for bdb tables with variable length primary key one can get some rows returned twice in the result.

How to repeat:
Run the following:
CREATE TABLE pk_varchar (
  pk1 varchar(8) NOT NULL default '',
  pk2 varchar(4) NOT NULL default '',
  key1 int(11) default NULL,
  key2 int(11) default NULL,
  PRIMARY KEY  (pk1,pk2),
  KEY key1 (key1),
  KEY key2 (key2)
) ENGINE=BDB;

INSERT INTO pk_varchar VALUES ('','empt',2,2),
  ('a','a--a',2,2),
  ('bb','b--b',2,2),
  ('ccc','c--c',2,2),
  ('dddd','d--d',2,2);

Then run:
select * from pk_varchar force index(key1, key2) where key1 < 3 or key2 < 3;

And get 9 rows in the result (while there are 5 rows in the table)

Suggested fix:
The problem is that ha_berkeley::position() doesn't touch end of rowid buffer if packed PK members occupy less then ref_length bytes. Since index_merge code uses memcmp(x, y, ref_length) call to compare rowids, it will erroneously produce duplicates in the output.

The suggest fix is to to bzero() not filled rowid elements.

This also may get the same row deleted twice from BDB table on multitable delete.
In my expirements it causes no problems, but I am not sure if it is always ok to delete the same row twice from a BDB table.

I verified this on our build machine under bk 5.0

I followed Sergeys suggestion and it fixed the problem.
The changeset notification is:
bk commit - 5.0 tree (ingo:1.1668)
of 29 Mar 2004 15:43 CEST

The after-review changes made it into a new patch:
bk commit - 5.0 tree (ingo:1.1690) BUG#2688
of Mon, 14 Jun 2004 15:59:07 +0200

Fix should be done in the 4.0 tree

Bugfix for 4.0 is now pending:
bk commit - 4.0 tree (ingo:1.1888) BUG#2688

Pushed bugfix.