Bug #33722 Possible to mix specifications for subpartitions when not specified for table
Submitted: 7 Jan 2008 15:40 Modified: 7 Mar 2008 11:30
Reporter: Mattias Jonsson
Status: Duplicate
Category:Server: Partition Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Mattias Jonsson Target Version:
Triage: D3 (Medium) / R2 (Low) / E2 (Low)

[7 Jan 2008 15: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 16: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 11:30] Mattias Jonsson 
This was fixed in Bug#31931 in its pushed patch.
[27 Mar 2008 12:22] Bugs System 
Pushed into 5.1.24-rc
[27 Mar 2008 18:54] Bugs System 
Pushed into 6.0.5-alpha