MySQL Bugs: #24186: nested query across partitions returns fewer records

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

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;

Test script and expected result for bug 24186

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

Added a smaller test case, with less attributes and a simpler query. (see attached file http://bugs.mysql.com/file.php?id=4857 )

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.

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

Assigned to Timour (for Mikael) on behalf of Elliot

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.

updated test for bug #24186

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

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.

QA_TEST_PASS
The proposed patch passes all the simple and extended tests created for this bug. 
There were no side effects.

The fix has been pushed into 5.1.16-beta main tree.

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.