MySQL Bugs: #65008: PARTITION RANGE COLUMNS() on DATE columns not pruning

Bug #65008	PARTITION RANGE COLUMNS() on DATE columns not pruning
Submitted:	17 Apr 2012 15:31	Modified:	18 Apr 2012 18:37
Reporter:	Jeremy Rumpf	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.5.23	OS:	Linux (2.6.32-220.7.1.el6.centos.plus.x86_64 #1 SMP Wed Mar 7 11:06:23 GMT 2012 x86_64)
Assigned to:		CPU Architecture:	Any

Description:
It does not appear partition pruning is being performed on DATE columns as described in this developer blog.

http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html

Per the above article:

-------------------------------------------------

/*With MySQL 5.5*/
CREATE TABLE t2
(
  dt DATE
)
PARTITION BY RANGE COLUMNS (dt)
(
  PARTITION p01 VALUES LESS THAN ('2007-01-01'),
  PARTITION p02 VALUES LESS THAN ('2008-01-01'),
  PARTITION p03 VALUES LESS THAN ('2009-01-01'),
  PARTITION p04 VALUES LESS THAN (MAXVALUE));

SHOW CREATE TABLE t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `dt` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(dt)
(PARTITION p01 VALUES LESS THAN ('2007-01-01') ENGINE = MyISAM,
 PARTITION p02 VALUES LESS THAN ('2008-01-01') ENGINE = MyISAM,
 PARTITION p03 VALUES LESS THAN ('2009-01-01') ENGINE = MyISAM,
 PARTITION p04 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */

The partition pruning will kick as in the previous case. There is no confusion between defining by function and querying by column because the definition is by column; the values we insert in the definition are preserved, making the DBA job much easier.

-------------------------------------------------

The issue at hand is that I cannot reproduce that behavior:

mysql> CREATE TABLE  test (dt DATE, foo INTEGER);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO  test (dt, foo) VALUES ('1900-01-01', 1),('1900-01-02',2),('1903-01-01', 3),('1903-01-02', 4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test PARTITION  BY RANGE COLUMNS (dt) (PARTITION  p0 VALUES LESS THAN ('1901-01-01'), PARTITION p1 VALUES LESS THAN (MAXVALUE));
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test;
CREATE TABLE `test` (
  `dt` date DEFAULT NULL,
  `foo` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY RANGE  COLUMNS(dt)
(PARTITION p0 VALUES LESS THAN ('1901-01-01') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

mysql> SELECT   partition_name part, partition_expression expr,   partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='test';
+------+------+--------------+------------+
| part | expr | descr        | table_rows |
+------+------+--------------+------------+
| p0   | `dt` | '1901-01-01' |          2 |
| p1   | `dt` | MAXVALUE     |          2 |
+------+------+--------------+------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM test WHERE dt = '1900-01-02';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | p0,p1      | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+

Should not this simple SELECT be pruned to partition p0?

How to repeat:
mysql> CREATE TABLE  test (dt DATE, foo INTEGER);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO  test (dt, foo) VALUES ('1900-01-01', 1),('1900-01-02',2),('1903-01-01', 3),('1903-01-02', 4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test PARTITION  BY RANGE COLUMNS (dt) (PARTITION  p0 VALUES LESS THAN ('1901-01-01'), PARTITION p1 VALUES LESS THAN (MAXVALUE));
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> EXPLAIN PARTITIONS SELECT * FROM test WHERE dt = '1900-01-02';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | p0,p1      | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+

No pruning on DATE columns.

With 5.5.22 on Mac everything works as expected:

macbook-pro:mysql-5.5.22-osx10.5-x86_64 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE  test (dt DATE, foo INTEGER);
Query OK, 0 rows affected (0.18 sec)

mysql> ALTER TABLE test PARTITION  BY RANGE COLUMNS (dt) (PARTITION  p0 VALUES LESS THAN
    -> ('1901-01-01'), PARTITION p1 VALUES LESS THAN (MAXVALUE));
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `dt` date DEFAULT NULL,
  `foo` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(dt)
(PARTITION p0 VALUES LESS THAN ('1901-01-01') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
1 row in set (0.04 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM test WHERE dt = '1900-01-02';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | p0         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.04 sec)

mysql> show variables like 'char%';
+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | utf8                                                   |
| character_set_connection | utf8                                                   |
| character_set_database   | latin1                                                 |
| character_set_filesystem | binary                                                 |
| character_set_results    | utf8                                                   |
| character_set_server     | latin1                                                 |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.5.22-osx10.5-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+
8 rows in set (0.00 sec)

Please, send the output of:

show variables like 'char%';

in your case.

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Thank you for the feedback.

I can not repeat described behavior too. Please also specify accurate version of MySQL package you use (filename you downloaded).

Dist:   CentOS 6.2
Kernel: 2.6.32-220.7.1.el6.centos.plus.x86_64

Packages:

MySQL-shared-5.5.23-1.el6.x86_64
MySQL-devel-5.5.23-1.el6.x86_64
MySQL-server-5.5.23-1.el6.x86_64
MySQL-client-5.5.23-1.el6.x86_64

As downloaded from:

http://dev.mysql.com/downloads/mysql/5.5.html

It should be noted, that I reverted to MySQL v5.5.22 as in your test. 

Download URL:

http://dev.mysql.com/get/Downloads/MySQL-5.5/MySQL-server-5.5.22-1.el6.x86_64.rpm/from/htt...

Package: 

MySQL-server-5.5.22-1.el6.x86_64.rpm

Version:

mysqld --version
mysqld  Ver 5.5.22 for Linux on x86_64 (MySQL Community Server (GPL))

And the problem still appears to persist:

mysql>  CREATE TABLE  test (dt DATE, foo INTEGER);
ERROR 1050 (42S01): Table 'test' already exists
mysql> drop table test;
Query OK, 0 rows affected (0.03 sec)

mysql>  CREATE TABLE  test (dt DATE, foo INTEGER);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO  test (dt, foo) VALUES ('1900-01-01',
    -> 1),('1900-01-02',2),('1903-01-01', 3),('1903-01-02', 4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test PARTITION  BY RANGE COLUMNS (dt) (PARTITION  p0 VALUES LESS THAN
    -> ('1901-01-01'), PARTITION p1 VALUES LESS THAN (MAXVALUE));
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> EXPLAIN PARTITIONS SELECT * FROM test WHERE dt = '1900-01-02';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | p0,p1      | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

No problems with 5.5.23 on Windows and utf8:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.23-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> EXPLAIN PARTITIONS SELECT * FROM test WHERE dt = '1900-01-02';
+----+-------------+-------+------------+------+---------------+------+---------
+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len
| ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------
+------+------+-------------+
|  1 | SIMPLE      | test  | p0         | ALL  | NULL          | NULL | NULL
| NULL |    2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------
+------+------+-------------+
1 row in set (0.52 sec)

mysql> show variables like 'char%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name            | Value
     |
+--------------------------+----------------------------------------------------
-----+
| character_set_client     | utf8
     |
| character_set_connection | utf8
     |
| character_set_database   | utf8
     |
| character_set_filesystem | binary
     |
| character_set_results    | utf8
     |
| character_set_server     | utf8
     |
| character_set_system     | utf8
     |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.5\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.01 sec)

Probably something Linux-specific...

yes, Jeremy uses MyISAM and everybody else uses INNODB tables :)

Not sure about MyISAM... at least in one case I see InnoDB in his comment on what he tried to implement.

I've narrowed the source of the issue. In the my.cnf file:

skip_character_set_client_handshake

Is preventing the pruning from working properly. This was added years back to the my.cnf for a utf8 conversion in the application to prevent older clients from changing the charset away from utf8. Removing it restores the desired pruning.

Without:

mysql> EXPLAIN PARTITIONS SELECT * FROM test WHERE dt = '1900-01-02';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | p0         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+

With:

+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | p0,p1      | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+

None of the data in this test is exotic or outside of the base ASCII range. So one would think that the connection charset wouldn't have any impact. I'm not sure of the exact interaction, but I would imagine it is relatively benign in regards to MySQL.

Please feel free to mark this as 'Not a Bug' if you feel so.

Thanks for the help.

Thank you for the feedback.

I can not repeat described behavior with packages, option provided and both MyISAM and InnoDB storage engine. Having nobody in our organization can not repeat this and removing skip-character-set-client-handshake is workaround for you, so closing the report as "Can't repeat".