Bug #66187 Wrong query plan for min/max over an entire partition

Description:
I have a HASH partitioned table.  There is an indexed column in the table.

I want to retrieve the min/max value for the indexed column for a specific partition.

For the query of the following form, I expect to read only one row (actually just one index entry) from one partition:
select MIN|MAX(indexed_column)
  from partitioned_table
 where partition_key = CONSTANT

In reality, a full table scan is made over the partition (example is below).

How to repeat:
 CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text CHARACTER SET utf8 NOT NULL,
  `imdb_index` varchar(12) CHARACTER SET utf8 DEFAULT NULL,
  `kind_id` int(11) NOT NULL,
  `production_year` int(11) DEFAULT NULL,
  `imdb_id` int(11) DEFAULT NULL,
  `phonetic_code` varchar(5) CHARACTER SET utf8 DEFAULT NULL,
  `episode_of_id` int(11) DEFAULT NULL,
  `season_nr` int(11) DEFAULT NULL,
  `episode_nr` int(11) DEFAULT NULL,
  `series_years` varchar(49) CHARACTER SET utf8 DEFAULT NULL,
  `title_crc32` int(10) unsigned DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1543721 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (kind_id)
PARTITIONS 7 */

explain select max(id) from t1 where kind_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 475122
        Extra: Using where
1 row in set (0.00 sec)
Notice the type: ALL

I would expect a plan equivalent to the following plan:

explain select id from t1 where kind_id = 1 order by id desc limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: id
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

Suggested fix:
Fix MIN/MAX over an entire partition, so that it works like it does when examining an entire table.  MIN/MAX over the entire partitioned table is fast, so it should be fast for entire partitions:

-- max(id) over whole table does not ALL scan partitions
explain select max(id) from t1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
1 row in set (0.00 sec)

I guess I can understand this for hash partitions, as it depends on data distribution.  In this case, there is a 1:1 mapping between partition_keys and the number of values in the partitioning.  This table could be partitioned with LIST or RANGE partitioning instead:

mysql> alter table t1 partition by list (kind_id) (partition p0 values in (1), partition p1 values in(2),  partition p2 values in(3), partition p3 values in (4), partition p4 values in(5), partition p5 values in(6), partition p6 values in(7));
Query OK, 1543719 rows affected (53.22 sec)
Records: 1543719  Duplicates: 0  Warnings: 0

-- there can only be kind_id=1 in p0 so we shouldn't need to do an FTS
mysql> explain partitions select max(id) from t1 where kind_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p0
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 478041
        Extra: Using where
1 row in set (0.00 sec)

mysql> alter table t1 add key (kind_id,id);
Query OK, 0 rows affected (18.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain partitions select max(id) from t1 where kind_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
1 row in set (0.00 sec)

Thank you for the problem report. Same problem confirmed with PARTITION BY LIST on current mysql-5.5.

The same is also true for range partitioning on columns for 5.5.25

This looks to have been solved on 5.5.30, on my table below t_id is actually unique copied from h_id which was PRIMARY KEY.

mysql [localhost] {msandbox} (test) > show create table history \G  
*************************** 1. row ***************************
       Table: history
Create Table: CREATE TABLE `history` (
  `h_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `h_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `t_id` bigint(20) unsigned NOT NULL,
  KEY `h_id` (`h_id`),
  KEY `t_id` (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=22896060 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (t_id)
PARTITIONS 8 */

mysql (test) > EXPLAIN PARTITIONS SELECT MAX(h_date) FROM history WHERE t_id = 10947385 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: history
   partitions: p1
         type: ref
possible_keys: t_id
          key: t_id
      key_len: 8
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

Scratch that last comment, I had a key on t_id which is not supposed to be there and the point of this bug report. If I remove the key on t_id, it does a full table scan instead:

mysql (test) > EXPLAIN PARTITIONS SELECT MAX(h_date) FROM history WHERE t_id = 10947385 \G                        
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: history
   partitions: p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1974747
        Extra: Using where
1 row in set (0.00 sec)