Partition Defaults

It's been an interesting exercise to define the defaults when creating and
altering partitioned tables.
If one creates a table like this

CREATE TABLE t1 (a int)
PARTITION BY KEY(a)

1) How many partitions
2) What will be the names of the partitions
3) Which engine is used

In this case the answer will be
1) 1
2) p0
3) MyISAM, most likely, depends on which is the default engine in the server
(If NDB is default then the answer will differ)

The reasoning here is that 1 is a minimalistic approach and can still be desired
since it might be desirable to be able to grow the table using ADD PARTITION
and so forth.

For RANGE/LIST partitioned tables it is not possible to avoid defining the
partitions. The reason is that the VALUES LESS THAN/VALUES IN must be
specified, otherwise we don't know the ranges or the list values (these are
too hard to guess at for a default).

However subpartitions can again be default
CREATE TABLE t1 (a date)
PARTITION BY RANGE (year(a))
SUBPARTITION BY KEY(a)
(PARTITION p0 VALUES LESS THAN (10));

In this case we will again set default number of subpartitions to 1, which in this
case isn't very useful since we cannot change the subpartitioning with any
partition management commands in this version.

CREATE TABLE t1 (a date)
PARTITION BY RANGE (year(a))
SUBPARTITION BY KEY(a)
SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (10));

This is more useful and the name of the subpartitions will be sp0 and sp1.

Now to even more tricky parts. What if a table created with all defaults has
partitions added to it.

ALTER TABLE t1 ADD PARTITION 1;

Assume that the table already had all defaults this would mean that we had 1
partition and add another. So from now on we don't have default number of
partitions anymore so the table is the same as if created by

CREATE TABLE t1 (a int)
PARTITION BY KEY(a)
PARTITIONS 2;
Thus partitions and their names are still default but not the name.

If we do instead
ALTER TABLE t1 (a int) ADD PARTITION (PARTITION x0);

In this case we have specified one new partition and we have even specified its
name and thus we don't use defaults anymore. Thus the table is now the same
as if created with:

CREATE TABLE t1 (a int)
PARTITION BY KEY(a)
(PARTITION p0, PARTITION x0);

For partitioned tables using NDB as the storage engine (MySQL Cluster) the
default number of partitions will be equal to the number of nodes in the
cluster, except when MAX_ROWS has been set very high when it can be set
higher, to a multiple of the number of nodes in the cluster.