Partition pruning not working with utf8 / skip-character-set-client-handshake options in 5.5

[Nicolas Brousse]

Partition pruning doesn't seem to work properly when using RANGE partition and to_days();

With MySQL 5.1:

mysql> explain partitions SELECT sum(s) FROM t WHERE time_id >= '2012-10-09 00:00:00' AND time_id < '2012-10-16 00:00:00' AND cid = 'TpqiCvy6S9af5KbEL5fQ';
+----+-------------+--------------------+-----------------------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table              | partitions                  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------------------+-----------------------------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | t | PM_2010_01_31,PM_2012_10_31 | ref  | PRIMARY       | PRIMARY | 20      | const |    2 | Using where |
+----+-------------+--------------------+-----------------------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.01 sec)

With MySQL Percona Server 5.5:

mysql> explain partitions SELECT sum(s) FROM t WHERE time_id >= '2012-10-09 00:00:00' AND time_id < '2012-10-16 00:00:00' AND cid = 'TpqiCvy6S9af5KbEL5fQ';
+----+-------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | t | PM_2010_01_31,PM_2010_02_28,PM_2010_03_31,PM_2010_04_30,PM_2010_05_31,PM_2010_06_30,PM_2010_07_31,PM_2010_08_31,PM_2010_09_30,PM_2010_10_31,PM_2010_11_30,PM_2010_12_31,PM_2011_01_31,PM_2011_02_28,PM_2011_03_31,PM_2011_04_30,PM_2011_05_31,PM_2011_06_30,PM_2011_07_31,PM_2011_08_31,PM_2011_09_30,PM_2011_10_31,PM_2011_11_30,PM_2011_12_31,PM_2012_01_31,PM_2012_02_29,PM_2012_03_31,PM_2012_04_30,PM_2012_05_31,PM_2012_06_30,PM_2012_07_31,PM_2012_08_31,PM_2012_09_30,PM_2012_10_31,PM_2012_11_30,PM_2012_12_31 | ref | PRIMARY | PRIMARY | 20 | const | 1 | Using where |
+----+-------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

[Eamon Bisson-Danahue (3-eamon)]

+1, This is impacting me as well.

[Tianshi Wang (java-jsps)]

This is very important for me since our large tables are heavily partitioned and many of them using to_days() function.

[Nicolas Brousse (orieg)]

It's actually a MySQL bug with utf8 and the skip-character-set-client-handshake in 5.5. It just got confirmed by MySQL: http://bugs.mysql.com/bug.php?id=67241

[Valerii Kravchuk (valerii-kravchuk)]

Verified with recent Percona server 5.5.28 also:

...
mysql> explain partitions SELECT * FROM test_nico WHERE time_id >= '2012-10-09 00:00:00' AND time_id < '2012-10-16 00:00:00' AND cid = 'TpqiCvy6S9af5KbEL5fQ'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_nico
   partitions: PM_2010_01_31,PM_2010_02_28,PM_2010_03_31,PM_2010_04_30,PM_2010_05_31,PM_2010_06_30,PM_2010_07_31,PM_2010_08_31,PM_2010_09_30,PM_2010_10_31,PM_2010_11_30,PM_2010_12_31,PM_2011_01_31,PM_2011_02_28,PM_2011_03_31,PM_2011_04_30,PM_2011_05_31,PM_2011_06_30,PM_2011_07_31,PM_2011_08_31,PM_2011_09_30,PM_2011_10_31,PM_2011_11_30,PM_2011_12_31,PM_2012_01_31,PM_2012_02_29,PM_2012_03_31,PM_2012_04_30,PM_2012_05_31,PM_2012_06_30,PM_2012_07_31,PM_2012_08_31,PM_2012_09_30,PM_2012_10_31,PM_2012_11_30,PM_2012_12_31
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 20
          ref: const
         rows: 36
        Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> show variables like '%ver%';
+----------------------------------+------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------+
| character_set_server | utf8 |
| collation_server | utf8_unicode_ci |
| innodb_force_recovery | 0 |
| innodb_recovery_stats | OFF |
| innodb_recovery_update_relay_log | OFF |
| innodb_show_verbose_locks | 0 |
| innodb_version | 1.1.8-29.0 |
| log_slow_verbosity | |
| myisam_recover_options | OFF |
| protocol_version | 10 |
| relay_log_recovery | OFF |
| rpl_recovery_rank | 0 |
| server_id | 0 |
| slave_type_conversions | |
| version | 5.5.28 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+----------------------------------+------------------------------+
18 rows in set (0.00 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 |
| char...

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1944