Bug #18423 partition by hash cuts the results

• Submitted: 22 Mar 2006 14:47
• Modified: 12 Apr 2006 1:02
• Reporter: Giuseppe Maxia
• Status: Closed
• Category: MySQL Server: Partitions
• Severity: S1 (Critical)
• Version: 5.1.7, 5.1.8
• OS: Linux (Linux)
• Assigned to: Mikael Ronström
• CPU Architecture: Any

[22 Mar 2006 14:47] Giuseppe Maxia

Description:
A partition by hash loses results when using a WHERE condition involving the hash expression.

Similar to what reported in bug #18329. It is actually the same bug, but more general.

Example:

+----------------+
| version()      |
+----------------+
| 5.1.8-beta-log |
+----------------+
1 row in set (0.00 sec)

select * from part_tab_hash;
+------+------+------------+
| c1   | c2   | c3         |
+------+------+------------+
| 136  | abc  | 2002-01-05 |
| 158  | abc  | 2002-06-01 |
| 142  | abc  | 2002-02-14 |
| 162  | abc  | 2002-06-28 |
| 182  | abc  | 2002-11-09 |
| 184  | abc  | 2002-11-22 |
+------+------+------------+
6 rows in set (0.00 sec)

select * from part_tab_hash where c3 between '2002-01-01' and '2002-12-31';
+------+------+------------+
| c1   | c2   | c3         |
+------+------+------------+
| 182  | abc  | 2002-11-09 |
+------+------+------------+
1 row in set (0.00 sec)

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

drop table if exists part_tab_hash;
CREATE TABLE part_tab_hash
(   c1 int default NULL,
    c2 varchar(30) default NULL,
    c3 date default NULL
) engine=myisam
PARTITION BY hash (to_days(c3))
partitions 12;

INSERT INTO `part_tab_hash` VALUES
(136,'abc','2002-01-05'),
(142,'abc','2002-02-14'),
(162,'abc','2002-06-28'),
(182,'abc','2002-11-09'),
(158,'abc','2002-06-01'),
(184,'abc','2002-11-22');

select version();
select * from part_tab_hash;
select * from part_tab_hash where c3 between '2002-01-01' and '2002-12-31';

[24 Mar 2006 7:01] Hartmut Holzgraefe

verified, test case attached

[24 Mar 2006 7:03] Hartmut Holzgraefe

simplified test case

Attachment: bug18423.tar.gz (application/x-gzip, text), 715 bytes.

[10 Apr 2006 9:03] Mikael Ronström

This bug has been fixed by previous bug patch in 5.1.10 or earlier

[12 Apr 2006 1:02] 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 in 5.1.10 changelog. Closed.