Bug #17894 Comparison with "less than" operator fails with Range partition
Submitted: 3 Mar 2006 12:00 Modified: 15 Mar 2006 12:52
Reporter: Giuseppe Maxia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:5.1.7 OS:Linux (Linux, Windows)
Assigned to: Reggie Burnett CPU Architecture:Any

[3 Mar 2006 12:00] Giuseppe Maxia
Description:
Using a range partition like the one described in a recent article ( http://dev.mysql.com/tech-resources/articles/performance-partitioning.html ), queries on specific ranges (e.g. date_column between x and y) succeed while queries asking for "less than" x fail.
e.g. 
select * from test_partition;
+------+--------------------+------------+
| c1   | c2                 | c3         |
+------+--------------------+------------+
|    1 | testing partitions | 1995-07-17 |
|    3 | testing partitions | 1995-07-31 |
|    5 | testing partitions | 1995-08-13 |
|    7 | testing partitions | 1995-08-26 |
|    9 | testing partitions | 1995-09-09 |
|    0 | testing partitions | 2000-07-10 |
|    2 | testing partitions | 2000-07-23 |
|    4 | testing partitions | 2000-08-05 |
|    6 | testing partitions | 2000-08-19 |
|    8 | testing partitions | 2000-09-01 |
+------+--------------------+------------+

select count(*) from test_partition where c3 between  '1996-12-31' and '2000-12-31';
+----------+
| count(*) |
+----------+
|        5 |
+----------+

 select count(*) from test_partition where c3 < '2000-12-31';
+----------+
| count(*) |
+----------+
|        0 |
+----------+

# test_myisam has the same records as test_partition (see below)
 select count(*) from test_myisam where c3 < '2000-12-31';
+----------+
| count(*) |
+----------+
|       10 |
+----------+

The same behavior is noted using different engines.

How to repeat:
create database if not exists test;
use test ;

DROP TABLE IF EXISTS `test_myisam`;
CREATE TABLE `test_myisam` (
  `c1` int(11) default NULL,
  `c2` varchar(30) default NULL,
  `c3` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `test_partition`;
CREATE TABLE `test_partition` (
  `c1` int(11) default NULL,
  `c2` varchar(30) default NULL,
  `c3` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (year(c3))
    (PARTITION p0 VALUES LESS THAN (1995),
     PARTITION p1 VALUES LESS THAN (1996),
     PARTITION p2 VALUES LESS THAN (1997),
     PARTITION p3 VALUES LESS THAN (1998),
     PARTITION p4 VALUES LESS THAN (1999),
     PARTITION p5 VALUES LESS THAN (2000),
     PARTITION p6 VALUES LESS THAN (2001),
     PARTITION p7 VALUES LESS THAN (2002),
     PARTITION p8 VALUES LESS THAN (2003),
     PARTITION p9 VALUES LESS THAN (2004),
     PARTITION p10 VALUES LESS THAN (2010),
     PARTITION p11 VALUES LESS THAN MAXVALUE
     );

INSERT INTO `test_partition` VALUES
    (0,'testing partitions','2000-07-10'),
    (1,'testing partitions','1995-07-17'),
    (2,'testing partitions','2000-07-23'),
    (3,'testing partitions','1995-07-31'),
    (4,'testing partitions','2000-08-05'),
    (5,'testing partitions','1995-08-13'),
    (6,'testing partitions','2000-08-19'),
    (7,'testing partitions','1995-08-26'),
    (8,'testing partitions','2000-09-01'),
    (9,'testing partitions','1995-09-09');
INSERT INTO test_myisam select * from test_partition;

select count(*) from test_myisam where c3 > '1994-01-01' and c3 <  '2000-12-31';
select count(*) from test_myisam where c3 <  '2000-12-31';
select count(*) from test_partition where c3 > '1994-01-01' and c3 <  '2000-12-31';
select count(*) from test_partition where c3 <  '2000-12-31';
[3 Mar 2006 12:21] Hartmut Holzgraefe
verified using yesterdays 5.1bk source
[6 Mar 2006 15:51] Peter Gulutzan
Looks similar to bug#17173 "Partitions: less-than search fails"
[6 Mar 2006 20:17] Miguel Solorzano
See also bug: http://bugs.mysql.com/bug.php?id=17980
[13 Mar 2006 13:57] Reggie Burnett
Fixed in 5.1.8.  check cset comment for review of what was wrong and fix.
[15 Mar 2006 12:52] 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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.1.8 changelog. Closed.