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

[14 Mar 2006 17:26] Holger S.
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.
[10 May 2006 12:13] Bugs System
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
[10 May 2006 16:57] Bugs System
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
[24 May 2006 12:36] Sergey Vojtovich
Pushed into trees currently marked as 5.0.23, 5.1.12.
[24 May 2006 18:25] Paul DuBois
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.