MySQL Bugs: #53685: Partition pruning for dates does not work in 5.5.3 and 5.5.4

Bug #53685	Partition pruning for dates does not work in 5.5.3 and 5.5.4
Submitted:	16 May 2010 20:56	Modified:	23 Aug 2010 10:03
Reporter:	Giuseppe Maxia	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.5.3, 5.5.4	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	dates, partitioning, pruning, regression

Description:
in MySQL 5.5.3 and 5.5.4, partition pruning does not work when using dates.

SELECT VERSION()
VERSION()
5.5.4-m3
1 row in set

EXPLAIN PARTITIONS SELECT * from t1 where d = '2010-02-01'
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p1,p2,p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set

Using a simple integer instead of a date works as expected:

EXPLAIN PARTITIONS SELECT * from t3 where i = 50
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: p2
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: 
1 row in set

By contrast, in 5.5.2, all worked as usual:
SELECT VERSION()
VERSION()
5.5.2-m2
1 row in set

EXPLAIN PARTITIONS SELECT * from t1 where d = '2010-02-01'
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set

These tests give the same results in both Linux 64bit and Mac OSX 10.6.

How to repeat:
drop table if exists t1,t2,t3,t4;
CREATE TABLE t1 (
          d date
) 
PARTITION BY RANGE (to_days(d))
(PARTITION p1 VALUES LESS THAN (to_days('2010-02-01')) ,
 PARTITION p2 VALUES LESS THAN (to_days('2010-05-01')) ,
 PARTITION p3 VALUES LESS THAN MAXVALUE ) 
;
CREATE TABLE t2 (
          d date
) 
PARTITION BY RANGE COLUMNS (d)
(PARTITION p1 VALUES LESS THAN ('2010-02-01') ,
 PARTITION p2 VALUES LESS THAN ('2010-05-01') ,
 PARTITION p3 VALUES LESS THAN MAXVALUE ) 
;
CREATE TABLE t3 (
          i INT
) 
PARTITION BY RANGE (i)
(PARTITION p1 VALUES LESS THAN (10) ,
 PARTITION p2 VALUES LESS THAN (100) ,
 PARTITION p3 VALUES LESS THAN MAXVALUE ) 
;

CREATE TABLE t4 (
          i INT
) 
PARTITION BY RANGE COLUMNS (i)
(PARTITION p1 VALUES LESS THAN (10) ,
 PARTITION p2 VALUES LESS THAN (100) ,
 PARTITION p3 VALUES LESS THAN MAXVALUE ) 
;

insert into t1 values ('2010-01-01'), ('2010-02-01'),('2010-03-01');
insert into t2 values ('2010-01-01'), ('2010-02-01'),('2010-03-01');
insert into t3 values (1), (50), (200);
insert into t4 values (1), (50), (200);

SELECT VERSION();
EXPLAIN PARTITIONS SELECT * from t1 where d = '2010-02-01'\G
EXPLAIN PARTITIONS SELECT * from t2 where d = '2010-02-01'\G
EXPLAIN PARTITIONS SELECT * from t3 where i = 50\G
EXPLAIN PARTITIONS SELECT * from t4 where i = 50\G

May be related to bug #53432.

Verified just as described:

mysql> SELECT VERSION();
+--------------------+
| VERSION()          |
+--------------------+
| 5.5.3-m3-community |
+--------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT * from t1 where d = '2010-02-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p1,p2,p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.08 sec)

mysql> EXPLAIN PARTITIONS SELECT * from t2 where d = '2010-02-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: p1,p2,p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT * from t3 where i = 50\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT * from t4 where i = 50\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t4
   partitions: p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

The puzzling thing in this issue is that the test suite runs fine, and it does not catch the bug.
So I checked the difference between the test suite and my setup.
The relevant difference is that the test suite has these lines in the configuration file:

[mysqld]
character-set-server=latin1

[mysql]
default-character-set=latin1

If I add these lines to my configuration file, the bug is not repeatable. Therefore, there is something in the handling of the character set that masks the problem.

Tested against mysql-trunk (rev. 3143), and the bug is not repeatable. So it must have been fixed by some other patch.

While this solves the problem for now, it is still necessary to add a stronger test case in the suite, to catch a similar bug in the future. Therefore I am leaving the bug report open.

Test file with both reject and result files.

Attachment: b53685_2.test.tgz (application/x-gzip, text), 816 bytes.

When building 5.5.3-m3 out of source in directory b, and then reverting the source to 5.5.2 and running the same test in b/mysql-test with the same 5.5.3 binary it succeeds.

When building 5.5.2 (in source) the test passes. It also passes with the same binary but in the 5.5.3 source tree.

new test files which only looks at the pruning

Attachment: b53685_3.test.tgz (application/x-gzip, text), 822 bytes.

Closing as duplicate of bug#52849.

bug was fixed by the same cset (bar@mysql.com-20100505092837-aezef74dv88bd4li).