Bug #18329 subpartitioning by HASH cuts the results

• Submitted: 18 Mar 2006 14:17
• Modified: 24 Mar 2006 7:01
• Reporter: Giuseppe Maxia
• Status: Duplicate
• Category: MySQL Server: Partitions
• Severity: S1 (Critical)
• Version: 5.1.7,5.1.8
• OS: Linux (Linux)
• CPU Architecture: Any

[18 Mar 2006 14:17] Giuseppe Maxia

Description:
Create a table with a main partition by range and subpartition by HASH, both of them using an expression involving a DATE column.
Queries on a specific date range will miss about half of the records.
For example:
+----------------+
| version()      |
+----------------+
| 5.1.8-beta-log |
+----------------+
1 row in set (0.00 sec)

+------+------+------------+
| c1   | c2   | c3         |
+------+------+------------+
| 136  | abc  | 2002-01-05 |
| 142  | abc  | 2002-02-14 |
| 162  | abc  | 2002-06-28 |
| 174  | abc  | 2002-09-16 |
| 176  | abc  | 2002-09-30 |
| 182  | abc  | 2002-11-09 |
| 188  | abc  | 2002-12-19 |
| 140  | abc  | 2002-02-01 |
| 152  | abc  | 2002-04-22 |
| 158  | abc  | 2002-06-01 |
| 184  | abc  | 2002-11-22 |
+------+------+------------+
11 rows in set (0.00 sec)

select count(*) from sub_part_tab where c3 between '2002-01-01' and '2002-12-31';
+----------+
| count(*) |
+----------+
| 7        |
+----------+
1 row in set (0.00 sec)

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

DROP TABLE IF EXISTS `sub_part_tab`;
CREATE TABLE `sub_part_tab` (
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(30) DEFAULT NULL,
  `c3` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 PARTITION BY RANGE (year(c3))
 SUBPARTITION BY HASH (to_days(c3))
 SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN (1995) ,
PARTITION p1 VALUES LESS THAN (2002) ,
PARTITION p2 VALUES LESS THAN MAXVALUE );

INSERT INTO `sub_part_tab` VALUES
(136,'abc','2002-01-05'),
(142,'abc','2002-02-14'),
(162,'abc','2002-06-28'),
(174,'abc','2002-09-16'),
(176,'abc','2002-09-30'),
(182,'abc','2002-11-09'),
(188,'abc','2002-12-19'),
(140,'abc','2002-02-01'),
(152,'abc','2002-04-22'),
(158,'abc','2002-06-01'),
(184,'abc','2002-11-22');
select version();
select * from sub_part_tab;
select count(*) from sub_part_tab where c3 between '2002-01-01' and '2002-12-31';

Suggested fix:
None at the moment.

[20 Mar 2006 8:16] Giuseppe Maxia

It seems that this sort of misbehavior happens only with dates.
I could not reproduce the same bug using a numeric column.

[20 Mar 2006 17:47] Hartmut Holzgraefe

test case

Attachment: bug18329.test (application/octet-stream, text), 851 bytes.

[20 Mar 2006 17:47] Hartmut Holzgraefe

expected test result

Attachment: bug18329.result (application/octet-stream, text), 822 bytes.

[24 Mar 2006 7:01] Hartmut Holzgraefe

duplicate of bug #18423 which provides a more general case of this