Bug #69179 accessing information_schema.partitions causes plans to change
Submitted: 9 May 2013 4:22 Modified: 23 Sep 2013 15:08
Reporter: Justin Swanhart Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6.11, 5.5.31, 5.6.13 OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb, performance, statistics
Triage: Needs Triage: D2 (Serious)

[9 May 2013 4:22] Justin Swanhart
Description:
MySQL 5.6 supposed supports persistent statistics.

I enabled persistent statistics in my "my.cnf" and I also enabled them with the ALTER TABLE statement.  However, in my testing, a read-only workload quickly resets the statistics as evidenced by query plans changes which result in severe performance loss.

In my testing, the first few batches of test queries will execute in 20 seconds or less with the data in the buffer pool, but then a batch of queries will start taking 80 seconds or more. An examination of the EXPLAIN plan on one of the queries in the batch shows a query plan change.

How to repeat:
I use Shard-Query with the Star Schema Benchmark to execute queries.  I can give you details for how to set this up, but I think you can probably duplicate this without using Shard-Query.

I have a partitioned table with STATS_PERSISTENT=1, STATS_AUTO_RECALC=0:
CREATE TABLE `lineorder` (
  `LO_OrderKey` bigint(20) NOT NULL,
  `LO_LineNumber` tinyint(4) NOT NULL,
  `LO_CustKey` int(11) NOT NULL,
  `LO_PartKey` int(11) NOT NULL,
  `LO_SuppKey` int(11) NOT NULL,
  `LO_OrderDateKey` int(11) NOT NULL,
  `LO_OrderPriority` varchar(15) DEFAULT NULL,
  `LO_ShipPriority` char(1) DEFAULT NULL,
  `LO_Quantity` tinyint(4) DEFAULT NULL,
  `LO_ExtendedPrice` decimal(10,0) DEFAULT NULL,
  `LO_OrdTotalPrice` decimal(10,0) DEFAULT NULL,
  `LO_Discount` decimal(10,0) DEFAULT NULL,
  `LO_Revenue` decimal(10,0) DEFAULT NULL,
  `LO_SupplyCost` decimal(10,0) DEFAULT NULL,
  `LO_Tax` tinyint(4) DEFAULT NULL,
  `LO_CommitDateKey` int(11) NOT NULL,
  `LO_ShipMode` varchar(10) DEFAULT NULL,
  KEY `LO_OrderDateKey` (`LO_OrderDateKey`),
  KEY `LO_SuppKey` (`LO_SuppKey`),
  KEY `LO_PartKey` (`LO_PartKey`),
  KEY `LO_CustKey` (`LO_CustKey`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=1 STATS_AUTO_RECALC=0
/*!50100 PARTITION BY RANGE (LO_OrderDateKey)
(PARTITION p1992_01 VALUES LESS THAN (19920131) ENGINE = InnoDB,
 ...,
 PARTITION p1998_12 VALUES LESS THAN (19981231) ENGINE = InnoDB) */
1 row in set (0.00 sec)

I used ANALYZE TABLE to refresh the stats:
ANALYZE TABLE lineorder;

Here is the join before I run SELECT queries:
mysql> explain SELECT c_nation AS expr$0,s_nation AS expr$1,d_year AS expr$2,SUM(lo_revenue) AS expr_2336619454 FROM customer AS `customer` JOIN lineorder AS `lineorder` ON( lo_custkey = c_customerkey )  JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey )  JOIN dim_date AS `dim_date` ON( lo_orderdatekey = d_datekey )  WHERE  c_region = 'ASIA' and s_region = 'ASIA' and d_year >= 1992 and d_year <= 1997 AND LO_OrderDateKey >= (19920731) AND  LO_OrderDateKey <  (19920831) GROUP BY expr$0,expr$1,expr$2 ORDER BY NULL\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
         type: ref
possible_keys: PRIMARY,C_Region
          key: C_Region
      key_len: 15
          ref: const
         rows: 218196
        Extra: Using index condition; Using temporary
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineorder
         type: ref
possible_keys: LO_OrderDateKey,LO_SuppKey,LO_CustKey
          key: LO_CustKey
      key_len: 4
          ref: ssb.customer.C_CustomerKey
         rows: 3
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: dim_date
         type: eq_ref
possible_keys: PRIMARY,D_Year,D_Year_2
          key: PRIMARY
      key_len: 4
          ref: ssb.lineorder.LO_OrderDateKey
         rows: 1
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: supplier
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: ssb.lineorder.LO_SuppKey
         rows: 1
        Extra: Using where
4 rows in set (0.01 sec)

In another session, I run a bunch of select queries of the same type, but the WHERE clause contains different constants such that one partition is evaluated per query.

After some query executions the query statistics on lineorder change because the query plan changes (notice that it now starts from dim_date not customer!):

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dim_date
         type: range
possible_keys: PRIMARY,D_Year,D_Year_2
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 29
        Extra: Using where; Using temporary
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineorder
         type: ref
possible_keys: LO_OrderDateKey,LO_SuppKey,LO_CustKey
          key: LO_OrderDateKey
      key_len: 4
          ref: ssb.dim_date.D_DateKey
         rows: 1
        Extra: NULL
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: supplier
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: ssb.lineorder.LO_SuppKey
         rows: 1
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
         type: eq_ref
possible_keys: PRIMARY,C_Region
          key: PRIMARY
      key_len: 4
          ref: ssb.lineorder.LO_CustKey
         rows: 1
        Extra: Using where
4 rows in set (0.00 sec)

^-- notice that I never ran analyze between the plans changing.  Where did the new stats come from?

Note that not only do I have the flag for persistent stats turned on at the table level, I also have it turned on at the instance level:

mysql> show global variables like '%persist%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_stats_persistent              | ON    |
| innodb_stats_persistent_sample_pages | 20    |
+--------------------------------------+-------+
2 rows in set (0.00 sec)

I also tried turning innodb_stats_auto_recalc=off.

Suggested fix:
Make persistent statistics persistent.
[9 May 2013 4:52] Justin Swanhart
if you want to duplicate data:
get the ssb dbgen: https://github.com/electrum/ssb-dbgen
You will need to fix config.h  add:
#include <sys/types.h>

Generate the tables at scale factor 20 (12GB data):
./dbgen -s 20 -T l
./dbgen -s 20 -T c
./dbgen -s 20 -T s
./dbgen -s 20 -T p
./dbgen -s 20 -T d

Get the DDL here:
https://shard-query.googlecode.com/svn/trunk/tools/ssb/ssb_schema.sql

You will have to partition the table.  I used this PHP script to generate the partitioning clauses:
<?php
for($y=1992;$y<=1998;++$y) {
        for($m = 1; $m <= 12; ++$m) {
                if($m < 10) $m = "0$m";
                echo "partition p{$y}_{$m} values less than ({$y}{$m}31),\n";
        }
}
?>

to load the tables:
load data infile '/path/to/ssb/lineorder.tbl' into table lineorder 
fields terminated by '|';

To set up shard-query download it from SVN.

Use install_db.php to create the config database.
Also grant all on *.* to shard_query@'localhost' identified by 'shard_query';

use the following bootstrap.ini:
;BEGIN
[default]
host=127.0.0.1
user=shard_query
password=shard_query
db=ssb

[config]
db=shard_query
mapper="none"
column="none"
inlist="none"
between="none"
column_datatype=integer
schema_name=default
star_schema=false

[single_node]
db=ssb
;END

php setup.php --ini=bootstrap.ini

Download and compile gearmand from gearman.org
run gearmand (ignore warnings about logging):

gearmand -d -p 7001 -L 127.0.0.1

change to the ./bin directory inside of the shard-query source tree

start a shard-query worker to verify that gearman is working properly.  if it just sits there and does nothing (no errors) then everything is good:
./worker

After 30 seconds or so with no errors, hit ctrl-c and start 8 workers (assuming you have 8 cores):
./start_workers 8 

You now have 8 workers running in the background (ps -ef|grep worker) and if a worker dies, the run_worker script will restart it (run_worker functions like mysqld_safe).

Verify shard-query is working:
echo "select count(*) from lineorder;"| php run_query

You should get a response back after awhile.  Open another console and use SHOW PROCESSLIST to verify the queries are running.  You should get one query per worker, as each worker accesses one partition.
[22 May 2013 8:56] Justin Swanhart
Shard-Query uses:
I_S.PARTITIONS
I_S.TABLES
I_S.COLUMNS

I suspect that statistics are being recalculated after accessing these views.
[26 May 2013 4:31] Justin Swanhart
run this script and the plan will change

Attachment: test.sql (text/x-sql), 633.39 KiB.

[26 May 2013 4:41] Justin Swanhart
I've attached a test script that should cause the query plan for Q2.2 of the star schema benchmark to change.  

Generate SSB scale factor 10 and load it into the tables.

Collect statistics for all tables and restart the database.

Examine the query plan for Q2.2:
explain partitions
select sum(lo_revenue), d_year, p_brand
 from lineorder
 join dim_date
 on lo_orderdatekey = d_datekey
 join part
 on lo_partkey = p_partkey
 join supplier
 on lo_suppkey = s_suppkey
 where p_brand between 'MFGR#2221' and 'MFGR#2228'
 and s_region = 'ASIA'
 group by d_year, p_brand
 order by d_year, p_brand;

Execute the provided test script.

Examine the query plan again.  Before running the test script the query plan should start that the `part` table.  After running the test script it will start at `dim_date`.
[26 May 2013 5:27] Justin Swanhart
Sorry, this is the exact query that has a plan change.  It seems problem occurs when partition elimination is used:

explain partitions SELECT SUM(lo_revenue) AS expr_841233792,d_year AS expr$0,p_brand AS expr$1 FROM lineorder AS `lineorder` JOIN dim_date AS `dim_date` ON( lo_orderdatekey = d_datekey )  JOIN part AS `part` ON( lo_partkey = p_partkey )  JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey )  WHERE  p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' AND LO_OrderDateKey >= (19951131) AND  LO_OrderDateKey <  (19951231) GROUP BY expr$0,expr$1 ORDER BY NULL;

Here is the exact query that causes the plan change for the above query:
select
            partition_expression, 
            CONCAT(partition_name,IF(SUBPARTITION_NAME IS NULL,'', concat('_', subpartition_name))) partition_name,
                concat(
                  -- get the boundary expression for the previous partition (if any)
                  IFNULL(
                    
                    (select 
                     concat(p3.partition_expression, ' >= (', 
                       if(p3.partition_description = 'MAXVALUE', (select max(p2.partition_description) 
                                                                 from information_schema.partitions p2 
                                                                where p2.table_name = p3.table_name 
                                                                  and p2.table_schema = p3.table_schema
                                                                  and p2.partition_description != 'MAXVALUE'
                                                                  and table_name = 'lineorder' and table_schema = 'ssb_tokudb'
                                                              and (subpartition_ordinal_position is null or subpartition_ordinal_position = 1)
                                                                 ), p3.partition_description), ') AND ')
                from information_schema.partitions p3
                where p.partition_description != 'MAXVALUE'
                and p3.table_name = p.table_name and p.table_schema = p3.table_schema
                and table_name = 'lineorder' and table_schema = 'ssb_tokudb'
            and (subpartition_ordinal_position is null or subpartition_ordinal_position = 1)
                and p3.PARTITION_ORDINAL_POSITION=(p.PARTITION_ORDINAL_POSITION-1)),''), ' ',
                 -- get the boundary expression for the current partition     
                  p.partition_expression, ' ', IF(p.partition_description != 'MAXVALUE', '< ', '>= '), ' (', 
                       if(p.partition_description = 'MAXVALUE', (select max(p2.partition_description) 
                                                                 from information_schema.partitions p2 
                                                                where p2.table_name = p.table_name 
                                                                  and p2.table_schema = p.table_schema
                                                                  and p2.partition_description != 'MAXVALUE' 
                                                              and (subpartition_ordinal_position is null or subpartition_ordinal_position = 1)
                                                                  and table_name = 'lineorder' and table_schema = 'ssb_tokudb'
                                                                ), p.partition_description), ')'
                ) as where_expression
                from information_schema.partitions p
                where partition_method like 'RANGE%'
                  and partition_expression not like '%`,`%'
                  and table_name = 'lineorder'
                  and table_schema = 'ssb_tokudb'
              and (subpartition_ordinal_position is null or subpartition_ordinal_position = 1)
                UNION ALL
                select partition_expression,
            CONCAT(partition_name,IF(SUBPARTITION_NAME IS NULL,'', concat('_', subpartition_name))) partition_name,
                  concat(p.partition_expression, ' IN (',  
                       p.partition_description, ')'
                ) as expression
    
                from information_schema.partitions p
                where partition_method like 'LIST%'
                  and partition_expression not like '%`,`%' 
              and (subpartition_ordinal_position is null or subpartition_ordinal_position = 1)
                  and table_schema = 'ssb_tokudb'
                  and table_name = 'lineorder';

Note that innodb_stats_on_metadata=off (I also tested with on, it had no effect).  

I also duplicated this bug in TokuDB-5.5.30-mariadb-7.0.1.  Not sure if it affects Oracle MySQL 5.5 since MariaDB inherits portions of the 5.6 codebase.
[26 May 2013 6:06] Justin Swanhart
Tested on Oracle MySQL 5.5.31.  

mysql> explain partitions SELECT SUM(lo_revenue) AS expr_841233792,d_year AS expr$0,p_brand AS expr$1 FROM lineorder AS `lineorder` JOIN dim_date AS `dim_date` ON( lo_orderdatekey = d_datekey )  JOIN part AS `part` ON( lo_partkey = p_partkey )  JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey )  WHERE  p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' AND LO_OrderDateKey >= (19951131) AND  LO_OrderDateKey <  (19951231) GROUP BY expr$0,expr$1 ORDER BY NULL;
+----+-------------+-----------+------------+--------+---------------------------------------+------------+---------+-------------------------------+------+-------------------------------------------+
| id | select_type | table     | partitions | type   | possible_keys                         | key        | key_len | ref                           | rows | Extra                                     |
+----+-------------+-----------+------------+--------+---------------------------------------+------------+---------+-------------------------------+------+-------------------------------------------+
|  1 | SIMPLE      | part      | NULL       | range  | PRIMARY,P_Brand                       | P_Brand    | 18      | NULL                          | 1583 | Using where; Using index; Using temporary |
|  1 | SIMPLE      | lineorder | p1995_12   | ref    | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_PartKey | 4       | ssb.part.P_PartKey            |    1 | Using where                               |
|  1 | SIMPLE      | supplier  | NULL       | eq_ref | PRIMARY                               | PRIMARY    | 4       | ssb.lineorder.LO_SuppKey      |    1 | Using where                               |
|  1 | SIMPLE      | dim_date  | NULL       | eq_ref | PRIMARY                               | PRIMARY    | 4       | ssb.lineorder.LO_OrderDateKey |    1 |                                           |
+----+-------------+-----------+------------+--------+---------------------------------------+------------+---------+-------------------------------+------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql> select count(*) From information_Schema.partitions;+----------+
| count(*) |
+----------+
|      273 |
+----------+
1 row in set (0.03 sec)

mysql> explain partitions SELECT SUM(lo_revenue) AS expr_841233792,d_year AS expr$0,p_brand AS expr$1 FROM lineorder AS `lineorder` JOIN dim_date AS `dim_date` ON( lo_orderdatekey = d_datekey )  JOIN part AS `part` ON( lo_partkey = p_partkey )  JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey )  WHERE  p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' AND LO_OrderDateKey >= (19951131) AND  LO_OrderDateKey <  (19951231) GROUP BY expr$0,expr$1 ORDER BY NULL;
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------+------+------------------------------+
| id | select_type | table     | partitions | type   | possible_keys                         | key             | key_len | ref                      | rows | Extra                        |
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------+------+------------------------------+
|  1 | SIMPLE      | dim_date  | NULL       | range  | PRIMARY                               | PRIMARY         | 4       | NULL                     |   29 | Using where; Using temporary |
|  1 | SIMPLE      | lineorder | p1995_12   | ref    | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_OrderDateKey | 4       | ssb.dim_date.D_DateKey   |    1 |                              |
|  1 | SIMPLE      | supplier  | NULL       | eq_ref | PRIMARY                               | PRIMARY         | 4       | ssb.lineorder.LO_SuppKey |    1 | Using where                  |
|  1 | SIMPLE      | part      | NULL       | eq_ref | PRIMARY,P_Brand                       | PRIMARY         | 4       | ssb.lineorder.LO_PartKey |    1 | Using where                  |
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------+------+------------------------------+
4 rows in set (0.00 sec)

innodb_stats_on_metadata does not affect behavior but the I_S query is two orders of magnitude slower with it on, and it still generates bad plan.
[26 May 2013 7:52] Justin Swanhart
Update version info.  Fat fingers.
[27 May 2013 9:50] Justin Swanhart
Here is a fix (at least I think it is).

[justin@workstation sql]$ diff -u ha_partition.cc /tmp/ha_partition.cc 
--- ha_partition.cc	2013-04-05 05:27:18.000000000 -0700
+++ /tmp/ha_partition.cc	2013-05-27 02:45:01.680676228 -0700
@@ -6455,9 +6455,11 @@
 void ha_partition::get_dynamic_partition_info(PARTITION_STATS *stat_info,
                                               uint part_id)
 {
+
   handler *file= m_file[part_id];
   DBUG_ASSERT(bitmap_is_set(&(m_part_info->read_partitions), part_id));
-  file->info(HA_STATUS_CONST | HA_STATUS_TIME | HA_STATUS_VARIABLE |
+ 
+  info(HA_STATUS_CONST | HA_STATUS_TIME | HA_STATUS_VARIABLE |
              HA_STATUS_VARIABLE_EXTRA | HA_STATUS_NO_LOCK);
 
   stat_info->records=              file->stats.records;

You should have an SCA for me on file.  I'm pretty certain I signed one for my blackhole engine fix.  If not, let me know and I'll file an OCA.
[27 May 2013 17:55] Sinisa Milivojevic
Hi!

This bug looks quite serious, so we are eager to reproduce it. First of all, please confirm that you have verified it on our standard GPL 5.6.11 binary.

Second, would you be so kind to provide us with a test case not involving third party programs. If there is a need to run queries in parallel in order to reproduce the bug, just let us know which statements (or batches thereof) should be run in parallel and we shall use our standard tools for the verification.

Many, many thanks in advance !!!
[27 May 2013 19:19] Justin Swanhart
SSB schema with partitions

Attachment: ssb_schema_partitioned.sql (text/x-sql), 6.19 KiB.

[27 May 2013 19:28] Justin Swanhart
-- There is no third party program needed except the data generator, which 
-- produces cardinalities necessary to see the problem.

-- You will need to download and compile the data generator:
-- get the ssb dbgen: https://github.com/electrum/ssb-dbgen

-- You will need to fix config.h  add:
#include <sys/types.h>

-- Generate the tables at scale factor 10 (6GB data) (you can try -s 1 too):
./dbgen -s 10 -T l
./dbgen -s 10 -T c
./dbgen -s 10 -T s
./dbgen -s 10 -T p
./dbgen -s 10 -T d

-- Create the schema using the attached ssb_schema_partitioned.sql file.
\. /data/ssb/SF10/ssb_schema_partitioned.sql

-- Load the data into the tables:
LOAD DATA INFILE '/data/ssb/SF10/lineorder.tbl' INTO TABLE lineorder fields terminated by '|';
LOAD DATA INFILE '/data/ssb/SF10/customer.tbl' INTO TABLE customer fields terminated by '|';
LOAD DATA INFILE '/data/ssb/SF10/part.tbl' INTO TABLE part fields terminated by '|';
LOAD DATA INFILE '/data/ssb/SF10/supplier.tbl' INTO TABLE supplier fields terminated by '|';
LOAD DATA INFILE '/data/ssb/SF10/dim_date.tbl' INTO TABLE dim_date fields terminated by '|';

-- Add indexes to lineorder:
alter table lineorder
add key(lo_orderdatekey),
add key(lo_suppkey),
add key(lo_partkey),
add key(lo_custkey);

-- switch stats to permanent for all tables:
ALTER TABLE lineorder stats_persistent=1, stats_auto_recalc=0;
ALTER TABLE customer stats_persistent=1, stats_auto_recalc=0;
ALTER TABLE supplier stats_persistent=1, stats_auto_recalc=0;
ALTER TABLE part stats_persistent=1, stats_auto_recalc=0;
ALTER TABLE dim_date stats_persistent=1, stats_auto_recalc=0;

-- analyze tables
analyze table lineorder;
analyze table customer;
analyze table supplier;
analyze table part;
analyze table dim_date;

-- Examine the plan for a problem query.  It SHOULD START AT THE `part` TABLE
mysql> explain partitions SELECT SUM(lo_revenue) AS expr_841233792,d_year AS expr$0,p_brand AS expr$1 FROM lineorder AS `lineorder` JOIN dim_date AS `dim_date` ON( lo_orderdatekey = d_datekey )  JOIN part AS `part` ON( lo_partkey = p_partkey )  JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey )  WHERE  p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' AND LO_OrderDateKey >= (19951131) AND  LO_OrderDateKey <  (19951231) GROUP BY expr$0,expr$1 ORDER BY NULL;
+----+-------------+-----------+------------+--------+---------------------------------------+------------+---------+-------------------------------+------+-------------------------------------------+
| id | select_type | table     | partitions | type   | possible_keys                         | key        | key_len | ref                           | rows | Extra                                     |
+----+-------------+-----------+------------+--------+---------------------------------------+------------+---------+-------------------------------+------+-------------------------------------------+
|  1 | SIMPLE      | part      | NULL       | range  | PRIMARY,P_Brand                       | P_Brand    | 18      | NULL                          | 1583 | Using where; Using index; Using temporary |
|  1 | SIMPLE      | lineorder | p1995_12   | ref    | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_PartKey | 4       | ssb.part.P_PartKey            |    1 | Using where                               |
|  1 | SIMPLE      | supplier  | NULL       | eq_ref | PRIMARY                               | PRIMARY    | 4       | ssb.lineorder.LO_SuppKey      |    1 | Using where                               |
|  1 | SIMPLE      | dim_date  | NULL       | eq_ref | PRIMARY                               | PRIMARY    | 4       | ssb.lineorder.LO_OrderDateKey |    1 |                                           |
+----+-------------+-----------+------------+--------+---------------------------------------+------------+---------+-------------------------------+------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql> select count(*) From information_Schema.partitions;
+----------+
| count(*) |
+----------+
|      273 |
+----------+
1 row in set (0.03 sec)

mysql> explain partitions SELECT SUM(lo_revenue) AS expr_841233792,d_year AS expr$0,p_brand AS expr$1 FROM lineorder AS `lineorder` JOIN dim_date AS `dim_date` ON( lo_orderdatekey = d_datekey )  JOIN part AS `part` ON( lo_partkey = p_partkey )  JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey )  WHERE  p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' AND LO_OrderDateKey >= (19951131) AND  LO_OrderDateKey <  (19951231) GROUP BY expr$0,expr$1 ORDER BY NULL;
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------+------+------------------------------+
| id | select_type | table     | partitions | type   | possible_keys                         | key             | key_len | ref                      | rows | Extra                        |
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------+------+------------------------------+
|  1 | SIMPLE      | dim_date  | NULL       | range  | PRIMARY                               | PRIMARY         | 4       | NULL                     |   29 | Using where; Using temporary |
|  1 | SIMPLE      | lineorder | p1995_12   | ref    | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_OrderDateKey | 4       | ssb.dim_date.D_DateKey   |    1 |                              |
|  1 | SIMPLE      | supplier  | NULL       | eq_ref | PRIMARY                               | PRIMARY         | 4       | ssb.lineorder.LO_SuppKey |    1 | Using where                  |
|  1 | SIMPLE      | part      | NULL       | eq_ref | PRIMARY,P_Brand                       | PRIMARY         | 4       | ssb.lineorder.LO_PartKey |    1 | Using where                  |
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------+------+------------------------------+
4 rows in set (0.00 sec)

This duplicates with binaries and source compiled 5.6.11.  It exists in all variants of MySQL including MariaDB.  The problem appears to be in ::get_dynamic_partition_info which is in ha_partition.cc, which is storage engine agnostic.  Thus this affects InnoDB, MyISAM, TokuDB, etc.
[27 May 2013 22:33] Justin Swanhart
Uploaded FTP attachment: bug-data-69179.tgz

It contains data for one partition (the one in the example query), and a README file for reproduction.  This removes any requirement for a third party binary.
[28 May 2013 12:10] Joe Grasse
Could this be related to http://bugs.mysql.com/bug.php?id=60071?
[28 May 2013 17:21] Justin Swanhart
Yes, it appears to be a duplicate of 60071.  

A workaround may be running FLUSH TABLES after accessing I_S.PARTITIONS.  This should discard the statistics (mysql stats, not innodb stats) being collected by I_S.PARTITIONS.
[29 May 2013 8:25] Umesh Shastry
Hello Justin,

I can not repeat described behavior neither on reported version or later version with the provided test case. Could you please provide fully repeatable test case?

Thanks,
Umesh
[29 May 2013 8:26] Umesh Shastry
Test case with results...

Attachment: 69179.txt (text/plain), 20.56 KiB.

[29 May 2013 19:08] Justin Swanhart
Look at the README.

In the test data the files are TAB separated not '|'.. You have thousands of warnings per LOAD DATA.
[29 May 2013 19:31] Justin Swanhart
I didn't do that to confuse you.  As I mentioned in the bug, the original files come from dbgen but the files in the data I sent you came from SELECT ... INTO OUTFILE which produces tab delimited files by default.  It was you who wanted to ensure you had the exact same data as me.  If you had compiled dbgen your test would have worked :)
[30 May 2013 3:20] Justin Swanhart
Also, apologies if I sounded rude.  Thank you for looking into this.
[30 May 2013 5:30] Umesh Shastry
Thank you for the report.
Verified as described.
[30 May 2013 5:37] Umesh Shastry
Test case with results...

Attachment: 69179.txt (text/plain), 11.20 KiB.

[28 Aug 2013 12:14] Sergei Glushchenko
The bug is fixed in Percona Server

http://bazaar.launchpad.net/~percona-core/percona-server/5.5/revision/543
[24 Sep 2013 8:10] Olav Sandstå
Duplicate of Bug#60071.