MySQL Bugs: #18233: Memory tables INDEX USING HASH (a,b) returns 1 row on SELECT WHERE a= AND b=

Bug #18233	Memory tables INDEX USING HASH (a,b) returns 1 row on SELECT WHERE a= AND b=
Submitted:	14 Mar 2006 17:26	Modified:	24 May 2006 18:25
Reporter:	Holger S.	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Linux (Debian Linux)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
Having a table with three attributes, a=varchar(32), b=numeric,c=timestamp and an index using hash over a and be, mysql returns only one single row when I do a select like
select * from table where a='whatever' and b=5;
even if multiple matching rows exist in the table.

Same  problem with aggregative functions:
select count(*) from table where a='whatever' and b=5;
returns always "1" if one or more matching rows exist.

How to repeat:
Hello,

This is my Table Structure for test:

CREATE TABLE `bugtest` (
  `a` varchar(32) NOT NULL,
  `b` tinyint(4) NOT NULL,
  `c` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  KEY `a` (`a`,`b`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 COMMENT='testing bug';

-- and data:

INSERT INTO `bugtest` (`a`, `b`, `c`) VALUES ('d89986d2116a8d0c1afbc636c1ab9ca8', 2, '2006-03-14 17:23:28'),
('d89986d2116a8d0c1afbc636c1ab9ca8', 1, '2006-03-14 17:23:58'),
('d89986d2116a8d0c1afbc636c1ab9ca8', 1, '2006-03-14 17:23:58'),
('d89986d2116a8d0c1afbc636c1ab9ca8', 1, '2006-03-14 17:24:03'),
('d89986d2116a8d0c1afbc636c1ab9ca8', 1, '2006-03-14 17:24:03');

-- single row returned by:
SELECT * FROM `bugtest` where a='d89986d2116a8d0c1afbc636c1ab9ca8' and b=1;
-- although 4 rows should be returned!

-- "1" returned by:
SELECT COUNT(*) FROM `bugtest` where a='d89986d2116a8d0c1afbc636c1ab9ca8' and b=1;
-- although "4" should be returned!

-- correct result returned by:
SELECT * FROM `bugtest` where a like 'd89986d2116a8d0c1afbc636c1ab9ca8' and b=1;

ALTER TABLE `bugtest` DROP INDEX `a`;

-- now returning correct result
SELECT * FROM `bugtest` where a='d89986d2116a8d0c1afbc636c1ab9ca8' and b=1;

ALTER TABLE bugtest ADD INDEX USING btree(a,b);

-- now returning correct result
SELECT * FROM `bugtest` where a='d89986d2116a8d0c1afbc636c1ab9ca8' and b=1;

Suggested fix:
Warkaround for users: Dropping the Index or replacing it by BTREE-Index makes mysql return correct results.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6189

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6206

Pushed into trees currently marked as 5.0.23, 5.1.12.

Noted in 5.0.23, 5.1.12 changelogs.

Selecting data from a <literal>MEMORY</literal> table with a
<literal>VARCHAR</literal> column and a
<literal>HASH</literal> index over it returned only the first
row matched.