MySQL Bugs: #33722: Possible to mix specifications for subpartitions when not specified for table

Bug #33722	Possible to mix specifications for subpartitions when not specified for table
Submitted:	7 Jan 2008 14:40	Modified:	7 Mar 2008 10:30
Reporter:	Mattias Jonsson	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
when fixing bug#31931 a related bug appeared, which is not fixed in that bug, it it possible to mix specified and not specified subpartition engines.

The rule should be:
    - All subpartitions must use the same engine
      AND it must be the same as the partition.
    - All partitions must use the same engine
      AND it must be the same as the table.
    - if one does NOT specify an engine on the table level
      then one must either NOT specify any engine on any partition/subpartition
      OR for ALL partitions/subpartitions

Also note that after CREATE, the table have now specified engines for both table  and all partitions. So the rules also applies for ALTER, but then the engine is always specified on table level (even if one does not specify it).

Currently it is only correct for subpartitions withing every partition, not between partitions. 

How to repeat:
-- error ER_MIX_HANDLER_ERROR
create table t1 (c1 int, c2 int)
 partition by list (c1)
 subpartition by hash (c2)
(
  partition p0 values in (0)
  (subpartition s0 engine=myisam,subpartition s1 engine=myisam)
,
  partition p1 values in (1)
  (subpartition s2, subpartition s3)
)
;

after the patch for Bug#31931 it does fail with this test too which should be OK (from mysql-test/suite/parts/inc/partition_engine.inc):
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1)
SUBPARTITION BY HASH(f_int1)
( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'InnoDB'
(SUBPARTITION subpart11,
SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2147483646)
(SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB',
SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
);

This was fixed in Bug#31931 in its pushed patch.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha