Bug #70823 Extended keys doesn't work for partitioned tables
Submitted: 5 Nov 2013 17:50 Modified: 5 Nov 2013 20:00
Reporter: Sergey Petrunya Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.6.14, 5.7.2 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[5 Nov 2013 17:50] Sergey Petrunya
Description:
InnoDB has extended keys feature. However, it doesn't work for partitioned tables.

How to repeat:
## Fill the tables
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t2 (a int);
insert into t2 select A.a + 10 *B.a + 100*C.a from t1 A, t1 B, t1 C;

create table t3 (pk1 int, pk2 int, a int, b int, key(a), primary key(pk1, pk2)) engine=innodb;

insert into t3 select a,a,a,a from t2;
create table t4 (pk1 int, pk2 int, a int, b int, key(a), primary key(pk1, pk2)) partition by key(pk1, pk2) partitions 10;
insert into t4 select * from t3;

## And then run
MySQL [test]> explain select * from t3 where a between 10 and 20 ;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t3    | range | a             | a    | 5       | NULL |   11 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

MySQL [test]> explain select * from t3 where a between 10 and 20 and pk1 >3 ;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t3    | range | PRIMARY,a     | a    | 9       | NULL |   11 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

Note that key_len=9 for the second query. This is extended keys feature at work.

However, for the partitioned table, we get:

MySQL [test]> explain select * from t4 where a between 10 and 20 ;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t4    | range | a             | a    | 5       | NULL |   12 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

MySQL [test]> explain select * from t4 where a between 10 and 20 and pk1 >3 ;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t4    | range | PRIMARY,a     | a    | 5       | NULL |   12 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Suggested fix:
Add extended keys support to ha_partition.
[5 Nov 2013 20:00] Sveta Smirnova
Thank you for the report.

Verified as described.