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
Triage: D3 (Medium) / R2 (Low) / E2 (Low)

[7 Jan 2008 14:40] Mattias Jonsson
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)
)
;
[8 Jan 2008 15:21] Mattias Jonsson
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')
);
[7 Mar 2008 10:30] Mattias Jonsson
This was fixed in Bug#31931 in its pushed patch.
[27 Mar 2008 11:22] Bugs System
Pushed into 5.1.24-rc
[27 Mar 2008 17:54] Bugs System
Pushed into 6.0.5-alpha