MySQL Bugs: #65935: SELECT query returns not all rows that are expected

Bug #65935	SELECT query returns not all rows that are expected
Submitted:	18 Jul 2012 9:54	Modified:	30 Aug 2012 13:54
Reporter:	Carsten Klotz von Hoff	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.5.24	OS:	Linux (Debian Squeeze)
Assigned to:		CPU Architecture:	Any
Tags:	select incorrect

Description:
We have some SELECT queries that are returning not all the data rows it should return. We copied the table without the partitioning and everything works fine. Adding the partitions again repeated the problem.

How to repeat:
We have the following table (SQL dump follows):

CREATE TABLE `page_impression_member_viewer` (
  `vID` int(11) NOT NULL DEFAULT '0',
  `pID` int(11) NOT NULL DEFAULT '0',
  `max_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `pID_2` (`pID`,`vID`),
  KEY `vID` (`vID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
ALTER TABLE page_impression_member_viewer PARTITION BY KEY () PARTITIONS 20;

The following statement returns in our case 23 rows. Every row matches the condition vID != 6843.

SELECT *
FROM page_impression_member_viewer
WHERE pID = 6843;

The next statement returns only one row, we expect all 23 like above.

SELECT *
FROM page_impression_member_viewer
WHERE pID = 6843 and vID != 6843;

complete test case with data on MySQL 5.5:

SELECT VERSION(); -- 5.5.23 

CREATE TABLE `page_impression_member_viewer` (
  `vID` INT(11) NOT NULL DEFAULT '0',
  `pID` INT(11) NOT NULL DEFAULT '0',
  `max_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `pID_2` (`pID`,`vID`),
  KEY `vID` (`vID`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

INSERT INTO `test`.`page_impression_member_viewer` (`vID`, `pID`) VALUES ('1', '6843'); 
INSERT INTO `test`.`page_impression_member_viewer` (`vID`, `pID`) VALUES ('2', '6843');

SELECT *
FROM page_impression_member_viewer
WHERE pID = 6843;
-- 2 rows returned

SELECT *
FROM page_impression_member_viewer
WHERE pID = 6843 AND vID != 6843;
-- 2 rows returned

ALTER TABLE page_impression_member_viewer PARTITION BY KEY () PARTITIONS 20;

SELECT *
FROM page_impression_member_viewer
WHERE pID = 6843;
-- 2 rows returned

SELECT *
FROM page_impression_member_viewer
WHERE pID = 6843 AND vID != 6843;
-- empty set

SELECT *
FROM page_impression_member_viewer
WHERE pID = 6843 AND (vID = 1 OR vID = 2);
-- 2 rows returned

It works as expected on 5.1.63

Storage engine does not matter.  Same issue with MyISAM on 5.5.23

The following is a workaround:

SELECT *
FROM page_impression_member_viewer
WHERE pID = 6843
HAVING vID != 6843;

This bug is verified in 5.5.25 and 5.6.6labs. Thanks for the bug report.

I set the bug from severity S3 to S2 because we now cannot trust our business data.

affects all versions of 5.5.x  (testing from 5.5.0 thru 5.5.27).

drop table if exists `t1`;
create table `t1` (`a` int,`b` int,primary key(`b`,`a`))engine=innodb;
insert into `t1` (`a`, `b`) values (1,2);
select * from `t1` where `b`=2 and `a`<>2;          #1 result
alter table `t1` partition by key () partitions 2;
select * from `t1` where `b`=2 and `a`<>2;          #0 result

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Fixed in 5.5.28, 5.6.7, and trunk.

Closed.