Bug #24186 nested query across partitions returns fewer records
Submitted: 10 Nov 2006 13:08 Modified: 1 Feb 2007 0:04
Reporter: Giuseppe Maxia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:5.1.11,5.1.13 OS:Linux (Linux)
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: nested query, partitions, Q1, subquery

[10 Nov 2006 13:08] Giuseppe Maxia
Description:
A nested query on a partitioned table returns fewer records than a corresponding normal table, when the subquery affects more than one partition.

For example, a query that returns three rows in a normal MyISAM or InnoDB table, will return only one with the partitioned table:

+-----------------+
| version()       |
+-----------------+
| 5.1.11-beta-log |
+-----------------+
1 row in set (0.01 sec)

# normal MyISAM table
+-----+------+--------+
| Pri | Stop | Number |
+-----+------+--------+
|   1 |    0 |      1 |
|   1 |    1 |      1 |
|   2 |    0 |      1 |
+-----+------+--------+
3 rows in set (0.00 sec)

#partitioned MyISAM Table
+-----+------+--------+
| Pri | Stop | Number |
+-----+------+--------+
|   1 |    1 |      1 |
+-----+------+--------+
1 row in set (0.00 sec)

Notice that:

- When executing the subquery on its own, it returns the expected records.
  This behavior happens only when the request is a nested query across
  partitions;

- all tables (partitioned and non-partitioned) have exactly the same data,
  coming from a single source, as the test below shows.

- The same error happens with MyISAM and InnoDB tables alike.

How to repeat:
create schema if not exists test;
use test;
select version();

DROP TABLE IF EXISTS snap_myisam;
CREATE TABLE snap_myisam (
  taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  id int(11) NOT NULL DEFAULT '0',
  status varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (id,taken),
  KEY taken (taken),
  KEY status_taken (status,taken)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO snap_myisam VALUES
('2006-09-27 21:50:01',16421,'Open'),
('2006-10-02 21:50:01',16421,'Verified'),
('2006-09-27 21:50:01',19092,'Closed'),
('2006-09-28 21:50:01',19092,'Verified'),
('2006-09-29 21:50:01',19092,'Verified'),
('2006-09-30 21:50:01',19092,'Verified'),
('2006-10-01 21:50:01',19092,'Verified'),
('2006-10-02 21:50:01',19092,'In progress'),
('2006-09-27 21:50:01',22589,'Open'),
('2006-09-29 21:50:01',22589,'Patch pending');

DROP TABLE IF EXISTS snap_bugs;
CREATE TABLE snap_bugs (
  id int(8) NOT NULL,
  severity tinyint(4) NOT NULL DEFAULT '0',
  priority tinyint(4) NOT NULL DEFAULT '0',
  status varchar(20) DEFAULT NULL,
  alien tinyint(4) NOT NULL,
  PRIMARY KEY (id),
  KEY severity (severity),
  KEY status (status),
  KEY priority (priority)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO snap_bugs VALUES
(16421,1,2,'Verified',0),
(19092,1,1,'Closed',1),
(22589,1,1,'Need Feedback',0);

DROP TABLE IF EXISTS snap_innodb;
CREATE TABLE snap_innodb LIKE snap_myisam;
ALTER TABLE snap_innodb ENGINE=InnoDB;

DROP TABLE IF EXISTS snap_myisam_part_r;
CREATE TABLE snap_myisam_part_r (
  taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  id int(11) NOT NULL DEFAULT '0',
  status varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (id,taken),
  KEY taken (taken),
  KEY status_taken (status,taken)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (to_days(taken))
(
PARTITION p01 VALUES LESS THAN (732524) ,
PARTITION p02 VALUES LESS THAN (732555) ,
PARTITION p03 VALUES LESS THAN (732585) ,
PARTITION p04 VALUES LESS THAN (732616) ,
PARTITION p05 VALUES LESS THAN (732646) ,
PARTITION p06 VALUES LESS THAN (732677) ,
PARTITION p07 VALUES LESS THAN (732708) ,
PARTITION p08 VALUES LESS THAN (732736) ,
PARTITION p09 VALUES LESS THAN (732767) ,
PARTITION p10 VALUES LESS THAN (732797) ,
PARTITION p11 VALUES LESS THAN (732828) ,
PARTITION p12 VALUES LESS THAN (732858) ,
PARTITION p13 VALUES LESS THAN (732889) ,
PARTITION p14 VALUES LESS THAN (732920) ,
PARTITION p15 VALUES LESS THAN (732950) ,
PARTITION p16 VALUES LESS THAN MAXVALUE ) ;

DROP TABLE IF EXISTS snap_innodb_part_r;
CREATE TABLE snap_innodb_part_r like snap_myisam_part_r;
ALTER TABLE snap_innodb_part_r ENGINE=InnoDB;

INSERT INTO snap_myisam_part_r select * from snap_myisam;
INSERT INTO snap_innodb select * from snap_myisam;
INSERT INTO snap_innodb_part_r select * from snap_myisam;

set @f_date='2006-09-28';
set @t_date='2006-10-02';

SELECT t1.priority AS Pri, t1.alien as Stop, count(distinct
t1.id) AS Number
FROM snap_bugs as t1
WHERE t1.severity = 1
      AND t1.id IN (
                     SELECT distinct id
                     FROM snap_myisam
                     WHERE status IN ('In progress', 'In review', 'Verified',
                                      'Patch pending', 'Patch approved',
                                      'Patch queued' )
                       AND taken BETWEEN @f_date
                                 AND date_add(@t_date, INTERVAL '1' DAY)
      )
      AND t1.id IN (
                    SELECT distinct id
                    FROM snap_myisam
                    WHERE status NOT IN ( 'In progress', 'In review',
                                          'Verified', 'Patch pending',
                                          'Patch approved', 'Patch queued' )
                      AND taken BETWEEN date_sub(@f_date, INTERVAL '1' DAY)
                                AND @f_date
      )
GROUP BY t1.priority, t1.alien;

SELECT t1.priority AS Pri, t1.alien as Stop, count(distinct
t1.id) AS Number
FROM snap_bugs as t1
WHERE t1.severity = 1
      AND t1.id IN (
                     SELECT distinct id
                     FROM snap_myisam_part_r
                     WHERE status IN ('In progress', 'In review', 'Verified',
                                      'Patch pending', 'Patch approved',
                                      'Patch queued' )
                       AND taken BETWEEN @f_date
                                 AND date_add(@t_date, INTERVAL '1' DAY)
      )
      AND t1.id IN (
                    SELECT distinct id
                    FROM snap_myisam_part_r
                    WHERE status NOT IN ( 'In progress', 'In review',
                                          'Verified', 'Patch pending',
                                          'Patch approved', 'Patch queued' )
                      AND taken BETWEEN date_sub(@f_date, INTERVAL '1' DAY)
                                AND @f_date
      )
GROUP BY t1.priority, t1.alien;

SELECT t1.priority AS Pri, t1.alien as Stop, count(distinct
t1.id) AS Number
FROM snap_bugs as t1
WHERE t1.severity = 1
      AND t1.id IN (
                     SELECT distinct id
                     FROM snap_innodb
                     WHERE status IN ('In progress', 'In review', 'Verified',
                                      'Patch pending', 'Patch approved',
                                      'Patch queued' )
                       AND taken BETWEEN @f_date
                                 AND date_add(@t_date, INTERVAL '1' DAY)
      )
      AND t1.id IN (
                    SELECT distinct id
                    FROM snap_innodb
                    WHERE status NOT IN ( 'In progress', 'In review',
                                          'Verified', 'Patch pending',
                                          'Patch approved', 'Patch queued' )
                      AND taken BETWEEN date_sub(@f_date, INTERVAL '1' DAY)
                                AND @f_date
      )
GROUP BY t1.priority, t1.alien;

SELECT t1.priority AS Pri, t1.alien as Stop, count(distinct
t1.id) AS Number
FROM snap_bugs as t1
WHERE t1.severity = 1
      AND t1.id IN (
                     SELECT distinct id
                     FROM snap_innodb_part_r
                     WHERE status IN ('In progress', 'In review', 'Verified',
                                      'Patch pending', 'Patch approved',
                                      'Patch queued' )
                       AND taken BETWEEN @f_date
                                 AND date_add(@t_date, INTERVAL '1' DAY)
      )
      AND t1.id IN (
                    SELECT distinct id
                    FROM snap_innodb_part_r
                    WHERE status NOT IN ( 'In progress', 'In review',
                                          'Verified', 'Patch pending',
                                          'Patch approved', 'Patch queued' )
                      AND taken BETWEEN date_sub(@f_date, INTERVAL '1' DAY)
                                AND @f_date
      )
GROUP BY t1.priority, t1.alien;
[10 Nov 2006 14:47] Giuseppe Maxia
Test script and expected result for bug 24186

Attachment: bug_24186_test.zip (application/zip, text), 1.58 KiB.

[10 Nov 2006 14:49] Giuseppe Maxia
Added a smaller test case, with less attributes and a simpler query. (see attached file http://bugs.mysql.com/file.php?id=4857 )
[11 Nov 2006 15:58] Giuseppe Maxia
Wider test case (checks partitions by key, list, range, hash) for three engines (MyISAM, InnoDB, Archive)

Attachment: bug_24186.pl (text/x-perl), 5.57 KiB.

[11 Nov 2006 16:09] Giuseppe Maxia
New batch of 138 tests added (http://bugs.mysql.com/file.php?id=4874).
It's a wider set of test, probing partitions by list, range, key, and hash for three different engines (MyISAM, InnoDB, Archive). 
For each partition type, there are two tests, one by date and one by number.
The bug kicks only with partitions by date, where a key is involved. 
The Archive engine is not affected. Changing the 'has_key' attribute to 0 for InnoDB and MyISAM will result in all tests being successful. This means that the bug affects only partitions depending on a key, with a date column.

Running this test requires Perl + DBI + DBD::mysql
[14 Nov 2006 1:20] Timothy Smith
Assigned to Timour (for Mikael) on behalf of Elliot
[18 Dec 2006 6:16] 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/17098

ChangeSet@1.2359, 2006-12-18 10:50:35+04:00, holyfoot@mysql.com +3 -0
  bug #24186 (nested query across partitions returns fewer records)
  
  Subselect's engine checks table->status field to determine if the
  record was properly found when we use keyread on the table.
  Partition's engine tries to read all it's partitions looking for given
  key before return. So if matching record was found in first partition, and
  no matching records was in second, we have table->status=NOT_FOUND after
  partition searching returns. It makes subselects to skip matching data
  in result.
  Here i just added table->status=0 if we found something matching before.
[20 Dec 2006 17:15] Giuseppe Maxia
updated test for bug #24186

Attachment: bug_24186test.tar.gz (application/gzip, text), 887 bytes.

[12 Jan 2007 11:10] 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/18018

ChangeSet@1.2384, 2007-01-12 15:46:20+04:00, holyfoot@mysql.com +3 -0
  bug #24186 (nested query across partitions returns fewer records)
  
  Subselect's engine checks table->status field to determine if the
  record was properly found when we use keyread upon the table.
  Partition engine checks all the partitions for given key
  before return. So if matching record was found in the first
  partition and no matching records were found in the second, 
  we have table->status == NOT_FOUND after the function, what
  makes subselects to skip matching records.
  The patch adds table->status= 0 if we actually found something.
[13 Jan 2007 9:26] Giuseppe Maxia
QA_TEST_PASS
The proposed patch passes all the simple and extended tests created for this bug. 
There were no side effects.
[28 Jan 2007 2:31] Igor Babaev
The fix has been pushed into 5.1.16-beta main tree.
[1 Feb 2007 0:04] 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

Documented bugfix in 5.1.16 changelog.