MySQL Bugs: #20279: Can't select data from a specific partition

Bug #20279	Can't select data from a specific partition
Submitted:	6 Jun 2006 5:23	Modified:	24 May 2012 11:35
Reporter:	carlos mafla	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:	5.1.9-beta	OS:	Linux (CentOS release 4.2 (Final))
Assigned to:		CPU Architecture:	Any

Description:
I created a hash partitioned table and i couldn't find a way to get data from a specific partition to know where the rows gone, not just to know what partitions a specific query access as the EXPLAIN PARTITION SELECT clause does.

How to repeat:
mysql> create table partition_hash
( col1 integer,
 col2 integer )
partition by hash(col1) partitions 4;

mysql> insert into partition_hash values (3,2),(5,4),(3,5);

mysql> select * from partition_hash;
+------+------+
| col1 | col2 |
+------+------+
| 5    | 4    |
| 3    | 2    |
| 3    | 5    |
+------+------+
3 rows in set (0.05 sec)

mysql> explain partitions select * from partition_hash;
+----+-------------+----------------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table          | partitions  | type | possible_keys | key | key_len | ref  | rows | Extra |
+----+-------------+----------------+-------------+------+---------------+------+---------+------+------+-------+
| 1  | SIMPLE      | partition_hash | p0,p1,p2,p3 | ALL  |               | |         |      | 3    |       |
+----+-------------+----------------+-------------+------+---------------+------+---------+------+------+-------+

mysql> select * from partition_hash partition(p1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(p1)' at line 1
mysql> select * from partition_hash p1;
+------+------+
| col1 | col2 |
+------+------+
| 5    | 4    |
| 3    | 2    |
| 3    | 5    |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from partition_hash p2;
+------+------+
| col1 | col2 |
+------+------+
| 5    | 4    |
| 3    | 2    |
| 3    | 5    |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from partition_hash p3;
+------+------+
| col1 | col2 |
+------+------+
| 5    | 4    |
| 3    | 2    |
| 3    | 5    |
+------+------+

Suggested fix:
Allow the select from a specific partition

Subpartitions are not tables and can not be accessed independently from main tables.
If you would like to query an individual partition, please use a different partitioning (not by hash) and specify the partitioning clause in the WHERE.

Hi all

An other customer is requesting what they know from other RDBMS:

SELECT * FROM table (PARTITION #12);

I have a similar requirement. Did you get the answer to check the data partition-wise?

If you want to know 'count(*)' from specific partition like this,

SELECT count(*) FROM partition_hash PARTITION(p3);

you can use this query as alternative.

mysql> SELECT table_rows as 'count(*)' FROM information_schema.partitions WHERE table_schema = schema() and table_name =
'partition_hash' and partition_name = 'p3';
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

This feature is being implemented as WL#5217.

Do not use the above workaround for InnoDB (I_S.PARTITIONS.table_rows), since it will not be correct, only an approximation. It will be exact for MyISAM though.

Added to MySQL 5.6:
http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html