MySQL Bugs: #17410: "Obtaining Information About Partitions" section contains incorrect info

Bug #17410	"Obtaining Information About Partitions" section contains incorrect info
Submitted:	15 Feb 2006 2:55	Modified:	17 Feb 2006 13:44
Reporter:	Sergey Petrunya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
The page http://dev.mysql.com/doc/refman/5.1/en/partitioning-info.html has the following passages:

(1) EXPLAIN PARTITIONS provides meaningful results only when employed to examine queries against tables that are partitioned by RANGE or LIST. (For a table partitioned by KEY or HASH, all partitions in the table are listed in the partitions column of the output.)

(2) If EXPLAIN PARTITIONS is used to examine a query against a non-partitioned table, no error is produced, but the value of the partitions  column is always NULL.

(3) EXPLAIN PARTITIONS currently works correctly only with tables that are partitioned on a column of an integer datatype.

(1) and (3) are not true, EXPLAIN PARTITIONS results can be meaningful and non-confluent for all partitioning types (an example provided below), and for all datatypes. Here is an example:

create table t1 (a char(10)) partition by key(a) partitions 4;
insert into t1 values ('one'),('two'),('three'),('four');
mysql> explain partitions select * from t1 where a='one' or a='four'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p0,p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

(2) is listed either under "You should take note of the following restrictions and limitations" clause. In my opinion property (2) is not a restriction or limitation. Producing no error and setting partitions column to NULL for EXPLAINs of queries over non-partitioned tables is a logical choice, because:
 * It is uniform with what is done for other fields (e.g. possible_keys is NULL for tables that don't have any keys)
 * The case of a query that uses both partitioned and non-partitioned tables is covered in straightforward and natural way - for partitioned tables the partitions column is a list of partitions, for non-partitioned tables is NULL.
 * Producing no error for the case when all query tables are not partitioned is consisistent with the previous rule, and this behavior is easy to process for automatic tools.

The only limitation I see is that it is not possible to use both "EXTENDED" and "PARTITIONS". That would make sense but currently isn't supported.

How to repeat:
n/a

Suggested fix:
Rephrase the mentioned passages according to the comments.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

1. This was true when I tested it in the course of writing the Partitioning chapter (I know some people think I just make stuff up, but I really don't). Since no bug was ever filed and it wasn't covered in the WL, I had no way of knowing that this was not expected behaviour. I've cut this paragraph from the chapter.

2. My feeling was that if I didn't say something like this, then someone would complain that I'd not covered what happens when you try this statement against a non-partitioned table. ;)

3. Again, this was what I uncovered during tests with a pre-release build. (I can't test the behaviour of a release that's not yet been released.) Since you've provided a working counterexample (almost identical to one which I tried and which did NOT work when I wrote the chapter), I've cut the offending paragraph.

Thanks for bringing my attention to these issues!