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

[18 Jul 2012 9:54] Carsten Klotz von Hoff
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;
[18 Jul 2012 11:13] Peter Laursen
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
[18 Jul 2012 11:16] Peter Laursen
It works as expected on 5.1.63
[18 Jul 2012 11:20] Peter Laursen
Storage engine does not matter.  Same issue with MyISAM on 5.5.23
[18 Jul 2012 11:48] Carsten Klotz von Hoff
The following is a workaround:

SELECT *
FROM page_impression_member_viewer
WHERE pID = 6843
HAVING vID != 6843;
[18 Jul 2012 13:26] Arnaud Adant
This bug is verified in 5.5.25 and 5.6.6labs. Thanks for the bug report.
[19 Jul 2012 6:24] Carsten Klotz von Hoff
I set the bug from severity S3 to S2 because we now cannot trust our business data.
[19 Jul 2012 6:36] Shane Bester
affects all versions of 5.5.x  (testing from 5.5.0 thru 5.5.27).
[19 Jul 2012 6:40] Shane Bester
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
[30 Aug 2012 13:54] Jon Stephens
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
[30 Aug 2012 13:55] Jon Stephens
Fixed in 5.5.28, 5.6.7, and trunk.

Closed.