List:Commits« Previous MessageNext Message »
From:mleich Date:January 11 2006 5:07pm
Subject:bk commit into 5.1 tree (mleich:1.2030)
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 repository of matthias. When matthias does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2030 06/01/11 18:07:07 mleich@stripped +7 -0
  WL#2808 - mysql-test cases for Partitioning
  - improved numbering and comments
  - borders for range partitioning (VALUE LESS THAN...) and the corresponding INSERTS are now
    dynamic (depend on the configurable number of rows within the template table)

  mysql-test/t/disabled.def
    1.33 06/01/11 18:06:57 mleich@stripped +1 -0
    Disabling of the NDB realted tests partition_03ndb because of crashing bug

  mysql-test/r/partition_02myisam.result
    1.4 06/01/11 18:06:57 mleich@stripped +440 -291
    Updated result

  mysql-test/include/partition_1.inc
    1.4 06/01/11 18:06:57 mleich@stripped +221 -118
    - improved numbering and comments
    - borders for range partitioning (VALUE LESS THAN...) and the corresponding INSERTS are now
      dynamic (depend on the configurable number of rows within the template table)

  mysql-test/include/partition_layout.inc
    1.1 06/01/11 18:06:57 mleich@stripped +13 -0
    New auxiliary script

  mysql-test/include/partition_layout.inc
    1.0 06/01/11 18:06:57 mleich@stripped +0 -0
    BitKeeper file /home/matthias/Arbeit/mysql-5.1/mikael1/mysql-test/include/partition_layout.inc

  mysql-test/include/partition_12.inc
    1.3 06/01/11 18:06:57 mleich@stripped +56 -34
    Borders for range partitioning (VALUE LESS THAN...) and the corresponding INSERTS are now
    dynamic (depend on the configurable number of rows within the template table)

  mysql-test/include/partition_11.inc
    1.2 06/01/11 18:06:57 mleich@stripped +1 -1
    Borders for range partitioning (VALUE LESS THAN...) and the corresponding INSERTS are now
    dynamic (depend on the configurable number of rows within the template table)

  mysql-test/include/partition_10.inc
    1.3 06/01/11 18:06:57 mleich@stripped +5 -9
    Borders for range partitioning (VALUE LESS THAN...) and the corresponding INSERTS are now
    dynamic (depend on the configurable number of rows within the template table)

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	mleich
# Host:	three.local.lan
# Root:	/home/matthias/Arbeit/mysql-5.1/mikael1

--- 1.32/mysql-test/t/disabled.def	2006-01-11 21:27:58 +01:00
+++ 1.33/mysql-test/t/disabled.def	2006-01-11 18:06:57 +01:00
@@ -24,3 +24,4 @@
 ps_7ndb         : Bug#15923 Core dump in RBR mode when executing test suite
 rpl_ddl         : Bug#15963 SBR does not show "Definer" correctly
 mysqlslap       : Bug#16167
+partition_03ndb : Crash during UPDATE, working on bug report

--- 1.3/mysql-test/include/partition_1.inc	2006-01-11 21:27:57 +01:00
+++ 1.4/mysql-test/include/partition_1.inc	2006-01-11 18:06:57 +01:00
@@ -10,6 +10,11 @@
 SET AUTOCOMMIT= 1;
 
 ##### Disabled testcases, because of open bugs #####
+--echo
+--echo #------------------------------------------------------------------------
+--echo # There are several testcases disabled because ouf the open bugs 
+--echo # #15407 , #15408 , #15890 , #15961 , #13447 , #15966 , #15968, #16370
+--echo #------------------------------------------------------------------------
 # Bug#15407 Partitions: crash if subpartition
 let $fixed_bug15407= 0;
 # Bug#15408 Partitions: subpartition names are not unique
@@ -24,12 +29,15 @@
 let $fixed_bug15966= 0;
 # Bug#15968 Partitions: crash when INSERT with f1 = -1 into PARTITION BY HASH(f1)
 let $fixed_bug15968= 0;
+# Bug #16370  	Partitions: subpartitions names not mentioned in SHOW CREATE TABLE output
+let $fixed_bug16370= 0;
 
 ##### Option, for displaying files #####
 #
 # Attention: Displaying the directory content via "ls var/master-data/test/t*"
 #            is probably not portable.
-let $ls= 1;
+#            let $ls= 0; disables the execution of "ls ....."
+let $ls= 0;
 
 ################################################################################
 #                     Partitioning syntax
@@ -65,22 +73,24 @@
 # ]
 ################################################################################
 
+--echo
 --echo #------------------------------------------------------------------------
---echo #  0. Creation of an auxiliary table needed in all testcases
+--echo #  0. Setting of auxiliary variables + Creation of an auxiliary table
+--echo #     needed in all testcases
 --echo #------------------------------------------------------------------------
+let $max_row= `SELECT @max_row`;
+let $max_row_div2= `SELECT @max_row DIV 2`;
+let $max_row_div3= `SELECT @max_row DIV 3`;
+let $max_row_div4= `SELECT @max_row DIV 4`;
+let $max_int_4= 2147483647;
 --disable_warnings
 DROP TABLE IF EXISTS t0_template;
 --enable_warnings
 CREATE TABLE t0_template ( f1 INTEGER, f2 char(20), PRIMARY KEY(f1))
 ENGINE = MEMORY;
---echo #     Logging of 200 INSERTs into t0_template suppressed
+--echo #     Logging of <max_row> INSERTs into t0_template suppressed
 --disable_query_log
-let $max_row= `SELECT @max_row`;
-let $max_row_div2= `SELECT @max_row DIV 2`;
-let $max_row_div3= `SELECT @max_row DIV 3`;
-let $max_row_div4= `SELECT @max_row DIV 4`;
 let $num= $max_row;
-# let $num= 200;
 while ($num)
 {
   eval INSERT INTO t0_template SET f1 = $num, f2 = '---$num---';
@@ -89,6 +99,7 @@
 }
 --enable_query_log
 
+--echo
 --echo #------------------------------------------------------------------------
 --echo #  1. Some syntax checks
 --echo #------------------------------------------------------------------------
@@ -104,27 +115,37 @@
 PARTITION BY RANGE(f1)
 ( PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11));
 }
-
---echo #------------------------------------------------------------------------
---echo #  2. Checks where the engine is set on all supported CREATE TABLE
---echo #     statement positions + basic operations on the tables
+--echo #  FIXME Implement testcases, where it is checked that all create and
+--echo #  alter table statements
+--echo #  - with missing mandatory parameters are rejected
+--echo #  - with optional parameters are accepted
+--echo #  - with wrong combinations of optional parameters are rejected
+--echo #  - ............
+
+--echo
+--echo #------------------------------------------------------------------------
+--echo #  2. Checks where the engine is assigned on all supported (CREATE TABLE
+--echo #     statement) positions + basic operations on the tables
+--echo #        Storage engine mixups are currently (2005-12-23) not supported
 --echo #------------------------------------------------------------------------
 --disable_warnings
 DROP TABLE IF EXISTS t1;
 --enable_warnings
 
---echo # 2.1 table (non partitioned) for comparison
+--echo # 2.1 non partitioned table (for comparison)
 eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = $engine;
+# MLML Full size (as check of check routine)
 --source include/partition_10.inc
 DROP TABLE t1;
 #
---echo # 2.2 table with engine setting just after column list
+--echo # 2.2 Assignment of storage engine just after column list only
 eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = $engine
      PARTITION BY HASH(f1) PARTITIONS 2;
 --source include/partition_10.inc
 DROP TABLE t1;
 #
---echo # 2.3 table with engine setting in the named partition part
+--echo # 2.3 Assignment of storage engine just after partition or subpartition
+--echo #     name only
 eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
      PARTITION BY HASH(f1)
      ( PARTITION part1 STORAGE ENGINE = $engine,
@@ -132,20 +153,21 @@
      );
 --source include/partition_10.inc
 DROP TABLE t1;
---echo # 2.4 table with engine setting in the named subpartition part
 eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
      PARTITION BY RANGE(f1)
      SUBPARTITION BY HASH(f1)
-     ( PARTITION part1 VALUES LESS THAN (1000)
+     ( PARTITION part1 VALUES LESS THAN ($max_row_div2)
           (SUBPARTITION subpart11 STORAGE ENGINE = $engine,
            SUBPARTITION subpart12 STORAGE ENGINE = $engine),
-       PARTITION part2 VALUES LESS THAN (2000)
+       PARTITION part2 VALUES LESS THAN ($max_int_4)
           (SUBPARTITION subpart21 STORAGE ENGINE = $engine,
            SUBPARTITION subpart22 STORAGE ENGINE = $engine)
      );
 --source include/partition_10.inc
 DROP TABLE t1;
---echo # 2.5 Ugly "incomplete" storage engine assignments
+#
+--echo # 2.4 Some but not all named partitions or subpartitions get a storage
+--echo #     engine assigned
 eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
      PARTITION BY HASH(f1)
      ( PARTITION part1 STORAGE ENGINE = $engine,
@@ -163,10 +185,10 @@
 eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
      PARTITION BY RANGE(f1)
      SUBPARTITION BY HASH(f1)
-     ( PARTITION part1 VALUES LESS THAN (1000)
+     ( PARTITION part1 VALUES LESS THAN ($max_row_div2)
           (SUBPARTITION subpart11,
            SUBPARTITION subpart12 STORAGE ENGINE = $engine),
-       PARTITION part2 VALUES LESS THAN (2000)
+       PARTITION part2 VALUES LESS THAN ($max_int_4)
           (SUBPARTITION subpart21 STORAGE ENGINE = $engine,
            SUBPARTITION subpart22 STORAGE ENGINE = $engine)
      );
@@ -175,76 +197,82 @@
 eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
      PARTITION BY RANGE(f1)
      SUBPARTITION BY HASH(f1)
-     ( PARTITION part1 VALUES LESS THAN (1000)
+     ( PARTITION part1 VALUES LESS THAN ($max_row_div2)
           (SUBPARTITION subpart11 STORAGE ENGINE = $engine,
            SUBPARTITION subpart12 STORAGE ENGINE = $engine),
-       PARTITION part2 VALUES LESS THAN (2000)
-          (SUBPARTITION subpart21                         ,
-           SUBPARTITION subpart22                         )
-     );
---source include/partition_10.inc
-DROP TABLE t1;
---echo # 2.6 Ugly "over determined" storage engine assignments
-#      after column list + in partition part
-eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = $engine
-     PARTITION BY HASH(f1)
-     ( PARTITION part1 STORAGE ENGINE = $engine,
-       PARTITION part2 STORAGE ENGINE = $engine
+       PARTITION part2 VALUES LESS THAN ($max_int_4)
+          (SUBPARTITION subpart21,
+           SUBPARTITION subpart22 )
      );
 --source include/partition_10.inc
 DROP TABLE t1;
-#      after column list + in sub partition part
-eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = $engine
+#
+--echo # 2.5 Storage engine assignment after partition name + after name of
+--echo #     subpartitions belonging to another partition
+eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
      PARTITION BY RANGE(f1)
      SUBPARTITION BY HASH(f1)
-     ( PARTITION part1 VALUES LESS THAN (1000)
-          (SUBPARTITION subpart11 STORAGE ENGINE = $engine,
-           SUBPARTITION subpart12 STORAGE ENGINE = $engine),
-       PARTITION part2 VALUES LESS THAN (2000)
+     ( PARTITION part1 VALUES LESS THAN ($max_row_div2) ENGINE = $engine
+          (SUBPARTITION subpart11,
+           SUBPARTITION subpart12),
+       PARTITION part2 VALUES LESS THAN ($max_int_4)
           (SUBPARTITION subpart21 STORAGE ENGINE = $engine,
            SUBPARTITION subpart22 STORAGE ENGINE = $engine)
      );
 --source include/partition_10.inc
 DROP TABLE t1;
-#     in partition part + in sub partition part
 eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
      PARTITION BY RANGE(f1)
      SUBPARTITION BY HASH(f1)
-     ( PARTITION part1 VALUES LESS THAN (1000) STORAGE ENGINE = $engine
+     ( PARTITION part1 VALUES LESS THAN ($max_row_div2)
           (SUBPARTITION subpart11 STORAGE ENGINE = $engine,
            SUBPARTITION subpart12 STORAGE ENGINE = $engine),
-       PARTITION part2 VALUES LESS THAN (2000)
-          (SUBPARTITION subpart21 STORAGE ENGINE = $engine,
-           SUBPARTITION subpart22 STORAGE ENGINE = $engine)
+       PARTITION part2 VALUES LESS THAN ($max_int_4) ENGINE = $engine
+          (SUBPARTITION subpart21,
+           SUBPARTITION subpart22)
      );
 --source include/partition_10.inc
 DROP TABLE t1;
---echo # 2.7 Ugly storage engine assignments mixups
-eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
+#
+--echo # 2.6 Precedence of storage engine assignments
+--echo # 2.6.1 Storage engine assignment after column list + after partition
+--echo #       or subpartition name
+eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = $engine
+     PARTITION BY HASH(f1)
+     ( PARTITION part1 STORAGE ENGINE = $engine,
+       PARTITION part2 STORAGE ENGINE = $engine
+     );
+--source include/partition_10.inc
+DROP TABLE t1;
+eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = $engine
      PARTITION BY RANGE(f1)
      SUBPARTITION BY HASH(f1)
-     ( PARTITION part1 VALUES LESS THAN (1000) ENGINE = $engine
-          (SUBPARTITION subpart11                          ,
-           SUBPARTITION subpart12                         ),
-       PARTITION part2 VALUES LESS THAN (2000)
+     ( PARTITION part1 VALUES LESS THAN ($max_row_div2)
+          (SUBPARTITION subpart11 STORAGE ENGINE = $engine,
+           SUBPARTITION subpart12 STORAGE ENGINE = $engine),
+       PARTITION part2 VALUES LESS THAN ($max_int_4)
           (SUBPARTITION subpart21 STORAGE ENGINE = $engine,
            SUBPARTITION subpart22 STORAGE ENGINE = $engine)
      );
 --source include/partition_10.inc
 DROP TABLE t1;
+--echo # 2.6.2 Storage engine assignment after partition name + after 
+--echo #       subpartition name
+#     in partition part + in sub partition part
 eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
      PARTITION BY RANGE(f1)
      SUBPARTITION BY HASH(f1)
-     ( PARTITION part1 VALUES LESS THAN (1000)
+     ( PARTITION part1 VALUES LESS THAN ($max_row_div2) STORAGE ENGINE = $engine
           (SUBPARTITION subpart11 STORAGE ENGINE = $engine,
            SUBPARTITION subpart12 STORAGE ENGINE = $engine),
-       PARTITION part2 VALUES LESS THAN (2000) ENGINE = $engine
-          (SUBPARTITION subpart21                         ,
-           SUBPARTITION subpart22                         )
+       PARTITION part2 VALUES LESS THAN ($max_int_4)
+          (SUBPARTITION subpart21 STORAGE ENGINE = $engine,
+           SUBPARTITION subpart22 STORAGE ENGINE = $engine)
      );
 --source include/partition_10.inc
 DROP TABLE t1;
---echo # 2.8 Session default engine differs from engine used within create table
+
+--echo # 2.7 Session default engine differs from engine used within create table
 eval SET SESSION storage_engine=$engine_other;
 if ($fixed_bug15966)
 {
@@ -265,28 +293,28 @@
 }
 eval SET SESSION storage_engine=$engine;
 
-# FIXME Test storage engine mixups (currently 2005-12-23 not supported)
 
+--echo
 --echo #------------------------------------------------------------------------
---echo #  3. Check number of partitions and subpartitions
+--echo #  3. Check assigning the number of partitions and subpartitions
+--echo #     with and without named partitions/subpartitions
 --echo #------------------------------------------------------------------------
-
 --disable_warnings
 DROP TABLE IF EXISTS t1;
 --enable_warnings
 --echo # 3.1 (positive) without partition/subpartition number assignment
---echo # 3.1.1     no partition number, no named partitions, no subpartitions mentioned
+--echo # 3.1.1 no partition number, no named partitions
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1);
+PARTITION BY HASH(f1);
 --source include/partition_10.inc
 DROP TABLE t1;
---echo # 3.1.2     no partition number, named partitions, no subpartitions mentioned
+--echo # 3.1.2 no partition number, named partitions
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1) (PARTITION part1, PARTITION part2);
+PARTITION BY HASH(f1) (PARTITION part1, PARTITION part2);
 --source include/partition_10.inc
 DROP TABLE t1;
 # Attention: Several combinations are impossible
-#           If subpartioning exist
+#           If subpartitioning exists
 #           - partitioning algorithm must be RANGE or LIST
 #             This implies the assignment of named partitions.
 #           - subpartitioning algorithm must be HASH or KEY
@@ -309,27 +337,32 @@
 #             Y     N     Y
 #             Y     Y     N
 #             Y     Y     Y
+--disable_query_log
 let $part0= CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1);
+PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1);
 #
-let $part1_N= (PARTITION part1 VALUES LESS THAN (1000),;
-let $part1_Y= (PARTITION part1 VALUES LESS THAN (1000)
-(SUBPARTITION subpart11 , SUBPARTITION subpart12 ),;
-#
-let $part2_N= PARTITION part2 VALUES LESS THAN (2000),;
-let $part2_Y= PARTITION part2 VALUES LESS THAN (2000)
-(SUBPARTITION subpart21 , SUBPARTITION subpart22 ),;
-#
-let $part3_N= PARTITION part3 VALUES LESS THAN (2147483647));
-let $part3_Y= PARTITION part3 VALUES LESS THAN (2147483647)
-(SUBPARTITION subpart21 , SUBPARTITION subpart22 ));
+eval SET @aux = '(PARTITION part1 VALUES LESS THAN ($max_row_div2),';
+let $part1_N= `SELECT @AUX`;
+eval SET @aux = '(PARTITION part1 VALUES LESS THAN ($max_row_div2)
+(SUBPARTITION subpart11 , SUBPARTITION subpart12 ),';
+let $part1_Y= `SELECT @AUX`;
+#
+eval SET @aux = 'PARTITION part2 VALUES LESS THAN ($max_row),';
+let $part2_N= `SELECT @AUX`;
+eval SET @aux = 'PARTITION part2 VALUES LESS THAN ($max_row)
+(SUBPARTITION subpart21 , SUBPARTITION subpart22 ),';
+let $part2_Y= `SELECT @AUX`;
+#
+eval SET @aux = 'PARTITION part3 VALUES LESS THAN ($max_int_4))';
+let $part3_N= `SELECT @AUX`;
+eval SET @aux = 'PARTITION part3 VALUES LESS THAN ($max_int_4)
+(SUBPARTITION subpart31 , SUBPARTITION subpart32 ))';
+let $part3_Y= `SELECT @AUX`;
+--enable_query_log
 
 eval $part0 $part1_N $part2_N $part3_N ;
 DROP TABLE t1;
---echo #  FIXME several subtestcases of 3.1.3 disabled because of server crashes
---echo # Bug#15407 Partitions: crash if subpartition
 # Bug#15407 Partitions: crash if subpartition
-# All variants enclosed with the "if ($fixed_bug15407)" earn a crash
 if ($fixed_bug15407)
 {
 eval $part0 $part1_N $part2_N $part3_Y ;
@@ -353,6 +386,7 @@
 }
 eval $part0 $part1_Y $part2_Y $part3_Y ;
 --source include/partition_10.inc
+--exec ls var/master-data/test/t1*
 DROP TABLE t1;
 
 --echo # 3.2 partition/subpartition numbers good and bad values and notations
@@ -476,99 +510,154 @@
 let $part_number= "GARBAGE";
 --source include/partition_11.inc
 
---echo # 3.3 Mixups of number and names of partition/subpartition assigned
---echo # 3.3.1 (positive) number of partition/subpartition = number of named partition/subpartition
+--echo # 3.3 Mixups of assigned partition/subpartition numbers and names
+--echo # 3.3.1 (positive) number of partition/subpartition
+--echo #                         = number of named partition/subpartition
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1) PARTITIONS    2 ( PARTITION    part1,    PARTITION    part2 ) ;
+PARTITION BY HASH(f1) PARTITIONS 2 ( PARTITION part1, PARTITION part2 ) ;
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY RANGE(f1) PARTITIONS    2
+PARTITION BY RANGE(f1) PARTITIONS 2
 SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
 ( PARTITION part1 VALUES LESS THAN (1000)
     (SUBPARTITION subpart11, SUBPARTITION subpart12),
   PARTITION part2 VALUES LESS THAN (2147483647)
     (SUBPARTITION subpart21, SUBPARTITION subpart22)
 );
-SHOW CREATE TABLE t1;
+--source include/partition_layout.inc
 DROP TABLE t1;
---echo # 3.3.2 (positive) number of partition/subpartition , 0 (= no) named partition/subpartition
+--echo # 3.3.2 (positive) number of partition/subpartition ,
+--echo #                  0 (= no) named partition/subpartition
 --echo #                  already checked above
---echo # 3.3.3 (negative) number of partitions > number of named partitions
+--echo # 3.3.3 (negative) number of partitions/subpartitions
+--echo #                         > number of named partitions/subpartitions
 --error 1064
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1) PARTITIONS    2 ( PARTITION    part1 ) ;
+PARTITION BY HASH(f1) PARTITIONS 2 ( PARTITION part1 ) ;
 # Wrong number of named subpartitions in first partition
 --error 1064
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY RANGE(f1)
+PARTITION BY RANGE(f1)
 SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
 ( PARTITION part1 VALUES LESS THAN (1000)
-    (SUBPARTITION subpart11                        ),
+    (SUBPARTITION subpart11 ),
   PARTITION part2 VALUES LESS THAN (2147483647)
     (SUBPARTITION subpart21, SUBPARTITION subpart22)
 );
 # Wrong number of named subpartitions in non first/non last partition
 --error 1064
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY RANGE(f1)
+PARTITION BY RANGE(f1)
 SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
 ( PARTITION part1 VALUES LESS THAN (1000)
     (SUBPARTITION subpart11, SUBPARTITION subpart12),
   PARTITION part2 VALUES LESS THAN (2000)
-    (SUBPARTITION subpart21                        ),
+    (SUBPARTITION subpart21 ),
   PARTITION part3 VALUES LESS THAN (2147483647)
     (SUBPARTITION subpart31, SUBPARTITION subpart32)
 );
 # Wrong number of named subpartitions in last partition
 --error 1064
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY RANGE(f1) PARTITIONS    2
+PARTITION BY RANGE(f1) PARTITIONS 2
 SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
 ( PARTITION part1 VALUES LESS THAN (1000)
     (SUBPARTITION subpart11, SUBPARTITION subpart12),
   PARTITION part2 VALUES LESS THAN (2147483647)
     (SUBPARTITION subpart21                        )
 );
+--echo # 3.3.4 (negative) number of partitions < number of named partitions
+--error 1064
+CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
+PARTITION BY HASH(f1) PARTITIONS 1 ( PARTITION part1, PARTITION part2 ) ;
+# Wrong number of named subpartitions in first partition
+--error 1064
+CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
+PARTITION BY RANGE(f1)
+SUBPARTITION BY HASH(f1) SUBPARTITIONS 1
+( PARTITION part1 VALUES LESS THAN (1000)
+    (SUBPARTITION subpart11, SUBPARTITION subpart12),
+  PARTITION part2 VALUES LESS THAN (2147483647)
+    (SUBPARTITION subpart21, SUBPARTITION subpart22)
+);
+# Wrong number of named subpartitions in non first/non last partition
+--error 1064
+CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
+PARTITION BY RANGE(f1)
+SUBPARTITION BY HASH(f1) SUBPARTITIONS 1
+( PARTITION part1 VALUES LESS THAN (1000)
+    (SUBPARTITION subpart11, SUBPARTITION subpart12),
+  PARTITION part2 VALUES LESS THAN (2000)
+    (SUBPARTITION subpart21                        ),
+  PARTITION part3 VALUES LESS THAN (2147483647)
+    (SUBPARTITION subpart31, SUBPARTITION subpart32)
+);
+# Wrong number of named subpartitions in last partition
+--error 1064
+CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
+PARTITION BY RANGE(f1)
+SUBPARTITION BY HASH(f1) SUBPARTITIONS 1
+( PARTITION part1 VALUES LESS THAN (1000)
+    (SUBPARTITION subpart11, SUBPARTITION subpart12),
+  PARTITION part2 VALUES LESS THAN (2147483647)
+    (SUBPARTITION subpart21, SUBPARTITION subpart22)
+);
+
 
+--echo
 --echo #------------------------------------------------------------------------
 --echo #  4. Checks of logical partition/subpartition name
 --echo #     file name clashes during CREATE TABLE
 --echo #------------------------------------------------------------------------
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
 
-# ZZZZ (negative) one name used more than once
+--echo #  4.1 (negative) A partition name used more than once
 --error ER_SAME_NAME_PARTITION
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1) (PARTITION part1, PARTITION part1);
-if (fixed_bug15408)
+PARTITION BY HASH(f1) (PARTITION part1, PARTITION part1);
+#
+if ($fixed_bug15408)
 {
+# Bug#15408 Partitions: subpartition names are not unique
 --error ER_SAME_NAME_PARTITION
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY RANGE(f1)
+PARTITION BY RANGE(f1)
 SUBPARTITION BY HASH(f1)
 ( PARTITION part1 VALUES LESS THAN (1000)
     (SUBPARTITION subpart11, SUBPARTITION subpart11)
 );
 }
-# FIXME Implement testcases with filename problems
-#       existing file of other table --- partition/subpartition file name
-#       partition/subpartition file name  --- file of the same table
+--echo # FIXME Implement testcases with filename problems
+--echo #       existing file of other table --- partition/subpartition file name
+--echo #       partition/subpartition file name  --- file of the same table
 
+--echo
 --echo #------------------------------------------------------------------------
 --echo #  5. Alter table experiments
 --echo #------------------------------------------------------------------------
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
 --echo # 5.1 alter table add partition
 --echo # 5.1.1 (negative) add partition to non partitioned table
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20));
---echo # FIXME Why does the error numbers of MyISAM(1482) and NDB(1005) differ ?
+--source include/partition_layout.inc
+# MyISAM gets ER_PARTITION_MGMT_ON_NONPARTITIONED and NDB 1005
+# The error code of NDB differs, because all NDB tables are partitioned even
+# if the CREATE TABLE does not contain a partitioning clause.
 --error ER_PARTITION_MGMT_ON_NONPARTITIONED,1005
 ALTER TABLE t1 ADD PARTITION (PARTITION part1);
+--source include/partition_layout.inc
 DROP TABLE t1;
 
 --echo # 5.1.2 Add one partition to a table with one partition
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1);
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100;
+PARTITION BY HASH(f1);
+--source include/partition_layout.inc
+eval INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND $max_row_div2 - 1;
 --disable_query_log
 eval SELECT $engine = 'NDB' INTO @aux;
 let $my_exit= `SELECT @aux`;
@@ -583,31 +672,33 @@
 
 --echo # 5.1.3 Several times add one partition to a table with some partitions
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1) (PARTITION part1, PARTITION part3);
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100;
+PARTITION BY HASH(f1) (PARTITION part1, PARTITION part3);
+--source include/partition_layout.inc
+eval INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND $max_row_div2 - 1;
 # Partition name before first existing partition name
 ALTER TABLE t1 ADD PARTITION (PARTITION part0);
 --source include/partition_12.inc
 DELETE FROM t1;
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100;
+eval INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND $max_row_div2 - 1;
 # Partition name between existing partition names
 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
 --source include/partition_12.inc
 DELETE FROM t1;
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100;
+eval INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND $max_row_div2 - 1;
 if ($fixed_bug13447)
 {
-# Partition name after all existing partition name
+# Partition name after all existing partition names
 # Bug#13447 Partitions: crash with alter table
 ALTER TABLE t1 ADD PARTITION (PARTITION part4);
 }
 --source include/partition_12.inc
 DROP TABLE t1;
 
---echo # 5.1.4 Add several partition to a table with some partitions
+--echo # 5.1.4 Add several partitions to a table with some partitions
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1) (PARTITION part1, PARTITION part3);
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100;
+PARTITION BY HASH(f1) (PARTITION part1, PARTITION part3);
+--source include/partition_layout.inc
+eval INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND $max_row_div2 - 1;
 if ($fixed_bug13447)
 {
 # Bug#13447 Partitions: crash with alter table
@@ -619,14 +710,14 @@
 --echo # 5.1.5 (negative) Add partitions to a table with some partitions
 --echo #       clash on new and already existing partition names
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1) (PARTITION part1, PARTITION part2, PARTITION part3);
+PARTITION BY HASH(f1) (PARTITION part1, PARTITION part2, PARTITION part3);
 # Clash on first/non last partition name
 --error ER_SAME_NAME_PARTITION
 ALTER TABLE t1 ADD PARTITION (PARTITION part1);
 # Clash on non first/non last partition name
 --error ER_SAME_NAME_PARTITION
 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
-# Clash on non first/ last partition name
+# Clash on non first/last partition name
 --error ER_SAME_NAME_PARTITION
 ALTER TABLE t1 ADD PARTITION (PARTITION part3);
 # Clash on all partition names
@@ -634,12 +725,24 @@
 ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part2, PARTITION part3);
 DROP TABLE t1;
 
-# FIXME Is there any way to add a subpartition without adding a new partition
+# FIXME Is there any way to add a subpartition to an already existing partition
 
 --echo # 5.2 alter table add subpartition
---echo # 5.2.1 Add one subpartition to a table with subpartitioning rule
-CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY RANGE(f1)
+--echo # 5.2.1 Add one subpartition to a table with subpartitioning rule and
+--echo #       no explicit defined subpartitions
+eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
+PARTITION BY RANGE(f1)
 SUBPARTITION BY HASH(f1)
-(PARTITION part1 VALUES LESS THAN (1000));
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100;
+(PARTITION part1 VALUES LESS THAN ($max_row_div2));
+if ($fixed_bug16370)
+{
+--source include/partition_layout.inc
+}
+eval INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND $max_row_div2 - 1;
+eval ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN ($max_int_4)
+    (SUBPARTITION subpart21));
+if ($fixed_bug16370)
+{
+--source include/partition_12.inc
+}
+DROP TABLE t1;

--- 1.2/mysql-test/include/partition_10.inc	2005-12-23 22:49:49 +01:00
+++ 1.3/mysql-test/include/partition_10.inc	2006-01-11 18:06:57 +01:00
@@ -10,12 +10,8 @@
 # is like expected.
 #
 
-#      Show CREATE for this table
-eval SHOW CREATE TABLE t1;
-if ($ls)
-{
-#   --exec ls var/master-data/test/t1*
-}
+--source include/partition_layout.inc
+
 ####### Variations with multiple records
 #      Select on empty table
 SELECT COUNT(*) = 0 AS my_value FROM t1;
@@ -25,14 +21,14 @@
 eval SELECT (COUNT(*) = $max_row) AND (MIN(f1) = 1) AND (MAX(f1) = $max_row)
             AS my_value FROM t1;
 # DEBUG SELECT COUNT(*),MIN(f1),MAX(f1) FROM t1;
-#      (mass) Update $max_row_div4 * 2 records
+#      (mass) Update $max_row_div4 * 2 + 1 records
 eval UPDATE t1 SET f1 = f1 + $max_row
 WHERE f1 BETWEEN $max_row_div2 - $max_row_div4 AND $max_row_div2 + $max_row_div4;
 #      Select
-# DEBUG SELECT COUNT(*),MIN(f1),MAX(f1) FROM t1;
 eval SELECT (COUNT(*) = $max_row) AND (MIN(f1) = 1) AND (MAX(f1) = $max_row_div2 + $max_row_div4 + $max_row )
             AS my_value FROM t1;
-#      (mass) Delete of records
+# DEBUG SELECT COUNT(*),MIN(f1),MAX(f1) FROM t1;
+#      (mass) Delete $max_row_div4 * 2 + 1 records
 eval DELETE FROM t1
 WHERE f1 BETWEEN $max_row_div2 - $max_row_div4 + $max_row AND $max_row_div2 + $max_row_div4 + $max_row;
 #      Select

--- 1.1/mysql-test/include/partition_11.inc	2005-12-21 14:41:42 +01:00
+++ 1.2/mysql-test/include/partition_11.inc	2006-01-11 18:06:57 +01:00
@@ -20,7 +20,7 @@
 eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS $part_number
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN ($max_row_div2), PARTITION part2 VALUES LESS THAN ($max_int_4));
 --disable_query_log
 eval SET @my_errno= $mysql_errno ;
 let $run= `SELECT @my_errno = 0`;

--- 1.2/mysql-test/include/partition_12.inc	2005-12-23 22:49:49 +01:00
+++ 1.3/mysql-test/include/partition_12.inc	2006-01-11 18:06:57 +01:00
@@ -4,40 +4,62 @@
 # sourcing this file was successful.
 #
 
---disable_query_log
-# eval SET @my_errno= $mysql_errno;
-# let $run= `SELECT @my_errno = 0`;
---enable_query_log
-# if ($run)
-# {
-   #      Show CREATE for this table
-     eval SHOW CREATE TABLE t1;
-   if ($ls)
-   {
-      --exec ls var/master-data/test/t1*
-   }
-   #      (mass) Insert 100 records
-   INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 101 AND 200;
+--source include/partition_layout.inc
+
+####### Variations with multiple records
+   #      (mass) Insert max_row_div2 + 1 records
+   eval INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN $max_row_div2 AND $max_row;
    #      Select
-   SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-   #      (mass) Update 100 records
-   UPDATE t1 SET f1 = f1 + 200 WHERE f1 BETWEEN 50 AND 150;
+   eval SELECT (COUNT(*) = $max_row) AND (MIN(f1) = 1) AND (MAX(f1) = $max_row)
+            AS my_value FROM t1;
+   # DEBUG SELECT COUNT(*),MIN(f1),MAX(f1) FROM t1;
+   #      (mass) Update $max_row_div4 * 2 + 1 records
+   eval UPDATE t1 SET f1 = f1 + $max_row
+   WHERE f1 BETWEEN $max_row_div2 - $max_row_div4 AND $max_row_div2 + $max_row_div4;
    #      Select
-   SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-   #      (mass) Delete 100 records
-   DELETE FROM t1 WHERE f1 BETWEEN 250 AND 350;
+   eval SELECT (COUNT(*) = $max_row) AND (MIN(f1) = 1) AND (MAX(f1) = $max_row_div2 + $max_row_div4 + $max_row )
+            AS my_value FROM t1;
+   # DEBUG SELECT COUNT(*),MIN(f1),MAX(f1) FROM t1;
+   #      (mass) Delete $max_row_div4 * 2 + 1 records
+   eval DELETE FROM t1
+   WHERE f1 BETWEEN $max_row_div2 - $max_row_div4 + $max_row AND $max_row_div2 + $max_row_div4 + $max_row;
    #      Select
-   SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-   #      Insert one record
-   INSERT INTO t1 SET f1 = 0, f2 = '---0---';
-   #      Select
-   SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-   #      Update one record
-   UPDATE t1 SET f1 = 999, f2 = '---999---' WHERE f1 = 0 AND f2 = '---0---';
-   #      Select
-   SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-   #      Truncate
-   TRUNCATE t1;
-   #      Select on empty table
-   SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-# }
+   eval SELECT (COUNT(*) = $max_row - $max_row_div4 - $max_row_div4 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = $max_row)
+            AS my_value FROM t1;
+   # DEBUG SELECT COUNT(*),MIN(f1),MAX(f1) FROM t1;
+
+####### Variations with single records
+#      Insert one record at beginning
+INSERT INTO t1 SET f1 = 0 , f2 = '#######';
+#      Select this record
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
+#      Insert one record at end
+eval INSERT INTO t1 SET f1 = $max_row + 1, f2 = '#######';
+#      Select this record
+eval SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = $max_row + 1 AND f2 = '#######';
+#      Update one record
+eval UPDATE t1 SET f1 = $max_row + 2, f2 = 'ZZZZZZZ'
+     WHERE  f1 = 0 AND f2 = '#######';
+#      Select
+eval SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = $max_row + 2 AND f2 = 'ZZZZZZZ';
+if ($fixed_bug15968)
+{
+# Bug #15968: Partitions: crash when INSERT with f1 = -1 into PARTITION BY HASH(f1)
+eval UPDATE t1 SET f1 = 0 - 1, f2 = 'ZZZZZZZ'
+     WHERE  f1 = $max_row + 1 AND f2 = '#######';
+#      Select
+SELECT COUNT(*) AS my_value FROM t1 WHERE f1 = 0 - 1 AND f2 = 'ZZZZZZZ';
+}
+#      Delete
+eval DELETE FROM t1 WHERE f1 = $max_row + 2 AND f2 = 'ZZZZZZZ';
+if ($fixed_bug15968)
+{
+DELETE FROM t1 WHERE f1 = 0 - 1 AND f2 = 'ZZZZZZZ';
+}
+#      Select
+SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
+
+#      Truncate
+TRUNCATE t1;
+#      Select on empty table
+SELECT COUNT(*) = 0 AS my_value FROM t1;
--- New file ---
+++ mysql-test/include/partition_layout.inc	06/01/11 18:06:57
# include/partition_layout.inc
#
# Print partitioning related informations about the table t1
#

eval SHOW CREATE TABLE t1;

# Optional (most probably issues with separators and case sensitivity)
# listing of files belonging to the table t1
if ($ls)
{
   --exec ls var/master-data/test/t1*
}


--- 1.3/mysql-test/r/partition_02myisam.result	2006-01-11 21:27:57 +01:00
+++ 1.4/mysql-test/r/partition_02myisam.result	2006-01-11 18:06:57 +01:00
@@ -1,24 +1,40 @@
 SET SESSION storage_engine='MYISAM';
 SET @max_row = 200;
 SET AUTOCOMMIT= 1;
+
 #------------------------------------------------------------------------
-#  0. Creation of an auxiliary table needed in all testcases
+# There are several testcases disabled because ouf the open bugs 
+# #15407 , #15408 , #15890 , #15961 , #13447 , #15966 , #15968, #16370
+#------------------------------------------------------------------------
+
+#------------------------------------------------------------------------
+#  0. Setting of auxiliary variables + Creation of an auxiliary table
+#     needed in all testcases
 #------------------------------------------------------------------------
 DROP TABLE IF EXISTS t0_template;
 CREATE TABLE t0_template ( f1 INTEGER, f2 char(20), PRIMARY KEY(f1))
 ENGINE = MEMORY;
-#     Logging of 200 INSERTs into t0_template suppressed
+#     Logging of <max_row> INSERTs into t0_template suppressed
+
 #------------------------------------------------------------------------
 #  1. Some syntax checks
 #------------------------------------------------------------------------
 #  1.1 Subpartioned table without subpartitioning rule must be rejected
 DROP TABLE IF EXISTS t1;
+#  FIXME Implement testcases, where it is checked that all create and
+#  alter table statements
+#  - with missing mandatory parameters are rejected
+#  - with optional parameters are accepted
+#  - with wrong combinations of optional parameters are rejected
+#  - ............
+
 #------------------------------------------------------------------------
-#  2. Checks where the engine is set on all supported CREATE TABLE
-#     statement positions + basic operations on the tables
+#  2. Checks where the engine is assigned on all supported (CREATE TABLE
+#     statement) positions + basic operations on the tables
+#        Storage engine mixups are currently (2005-12-23) not supported
 #------------------------------------------------------------------------
 DROP TABLE IF EXISTS t1;
-# 2.1 table (non partitioned) for comparison
+# 2.1 non partitioned table (for comparison)
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM';
 SHOW CREATE TABLE t1;
 Table	Create Table
@@ -68,7 +84,7 @@
 my_value
 1
 DROP TABLE t1;
-# 2.2 table with engine setting just after column list
+# 2.2 Assignment of storage engine just after column list only
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM'
 PARTITION BY HASH(f1) PARTITIONS 2;
 SHOW CREATE TABLE t1;
@@ -119,7 +135,8 @@
 my_value
 1
 DROP TABLE t1;
-# 2.3 table with engine setting in the named partition part
+# 2.3 Assignment of storage engine just after partition or subpartition
+#     name only
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1)
 ( PARTITION part1 STORAGE ENGINE = 'MYISAM',
@@ -173,14 +190,13 @@
 my_value
 1
 DROP TABLE t1;
-# 2.4 table with engine setting in the named subpartition part
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY RANGE(f1)
 SUBPARTITION BY HASH(f1)
-( PARTITION part1 VALUES LESS THAN (1000)
+( PARTITION part1 VALUES LESS THAN (100)
 (SUBPARTITION subpart11 STORAGE ENGINE = 'MYISAM',
 SUBPARTITION subpart12 STORAGE ENGINE = 'MYISAM'),
-PARTITION part2 VALUES LESS THAN (2000)
+PARTITION part2 VALUES LESS THAN (2147483647)
 (SUBPARTITION subpart21 STORAGE ENGINE = 'MYISAM',
 SUBPARTITION subpart22 STORAGE ENGINE = 'MYISAM')
 );
@@ -189,7 +205,7 @@
 t1	CREATE TABLE `t1` (
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2000)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
 SELECT COUNT(*) = 0 AS my_value FROM t1;
 my_value
 1
@@ -232,7 +248,8 @@
 my_value
 1
 DROP TABLE t1;
-# 2.5 Ugly "incomplete" storage engine assignments
+# 2.4 Some but not all named partitions or subpartitions get a storage
+#     engine assigned
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1)
 ( PARTITION part1 STORAGE ENGINE = 'MYISAM',
@@ -342,10 +359,10 @@
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY RANGE(f1)
 SUBPARTITION BY HASH(f1)
-( PARTITION part1 VALUES LESS THAN (1000)
+( PARTITION part1 VALUES LESS THAN (100)
 (SUBPARTITION subpart11,
 SUBPARTITION subpart12 STORAGE ENGINE = 'MYISAM'),
-PARTITION part2 VALUES LESS THAN (2000)
+PARTITION part2 VALUES LESS THAN (2147483647)
 (SUBPARTITION subpart21 STORAGE ENGINE = 'MYISAM',
 SUBPARTITION subpart22 STORAGE ENGINE = 'MYISAM')
 );
@@ -354,7 +371,7 @@
 t1	CREATE TABLE `t1` (
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2000)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
 SELECT COUNT(*) = 0 AS my_value FROM t1;
 my_value
 1
@@ -400,19 +417,19 @@
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY RANGE(f1)
 SUBPARTITION BY HASH(f1)
-( PARTITION part1 VALUES LESS THAN (1000)
+( PARTITION part1 VALUES LESS THAN (100)
 (SUBPARTITION subpart11 STORAGE ENGINE = 'MYISAM',
 SUBPARTITION subpart12 STORAGE ENGINE = 'MYISAM'),
-PARTITION part2 VALUES LESS THAN (2000)
-(SUBPARTITION subpart21                         ,
-SUBPARTITION subpart22                         )
+PARTITION part2 VALUES LESS THAN (2147483647)
+(SUBPARTITION subpart21,
+SUBPARTITION subpart22 )
 );
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2000)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
 SELECT COUNT(*) = 0 AS my_value FROM t1;
 my_value
 1
@@ -455,18 +472,24 @@
 my_value
 1
 DROP TABLE t1;
-# 2.6 Ugly "over determined" storage engine assignments
-CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM'
-PARTITION BY HASH(f1)
-( PARTITION part1 STORAGE ENGINE = 'MYISAM',
-PARTITION part2 STORAGE ENGINE = 'MYISAM'
+# 2.5 Storage engine assignment after partition name + after name of
+#     subpartitions belonging to another partition
+CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
+PARTITION BY RANGE(f1)
+SUBPARTITION BY HASH(f1)
+( PARTITION part1 VALUES LESS THAN (100) ENGINE = 'MYISAM'
+(SUBPARTITION subpart11,
+SUBPARTITION subpart12),
+PARTITION part2 VALUES LESS THAN (2147483647)
+(SUBPARTITION subpart21 STORAGE ENGINE = 'MYISAM',
+SUBPARTITION subpart22 STORAGE ENGINE = 'MYISAM')
 );
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part2  ENGINE = MyISAM)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
 SELECT COUNT(*) = 0 AS my_value FROM t1;
 my_value
 1
@@ -509,22 +532,22 @@
 my_value
 1
 DROP TABLE t1;
-CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM'
+CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY RANGE(f1)
 SUBPARTITION BY HASH(f1)
-( PARTITION part1 VALUES LESS THAN (1000)
+( PARTITION part1 VALUES LESS THAN (100)
 (SUBPARTITION subpart11 STORAGE ENGINE = 'MYISAM',
 SUBPARTITION subpart12 STORAGE ENGINE = 'MYISAM'),
-PARTITION part2 VALUES LESS THAN (2000)
-(SUBPARTITION subpart21 STORAGE ENGINE = 'MYISAM',
-SUBPARTITION subpart22 STORAGE ENGINE = 'MYISAM')
+PARTITION part2 VALUES LESS THAN (2147483647) ENGINE = 'MYISAM'
+(SUBPARTITION subpart21,
+SUBPARTITION subpart22)
 );
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2000)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
 SELECT COUNT(*) = 0 AS my_value FROM t1;
 my_value
 1
@@ -567,22 +590,20 @@
 my_value
 1
 DROP TABLE t1;
-CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION BY RANGE(f1)
-SUBPARTITION BY HASH(f1)
-( PARTITION part1 VALUES LESS THAN (1000) STORAGE ENGINE = 'MYISAM'
-(SUBPARTITION subpart11 STORAGE ENGINE = 'MYISAM',
-SUBPARTITION subpart12 STORAGE ENGINE = 'MYISAM'),
-PARTITION part2 VALUES LESS THAN (2000)
-(SUBPARTITION subpart21 STORAGE ENGINE = 'MYISAM',
-SUBPARTITION subpart22 STORAGE ENGINE = 'MYISAM')
+# 2.6 Precedence of storage engine assignments
+# 2.6.1 Storage engine assignment after column list + after partition
+#       or subpartition name
+CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM'
+PARTITION BY HASH(f1)
+( PARTITION part1 STORAGE ENGINE = 'MYISAM',
+PARTITION part2 STORAGE ENGINE = 'MYISAM'
 );
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2000)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part2  ENGINE = MyISAM)
 SELECT COUNT(*) = 0 AS my_value FROM t1;
 my_value
 1
@@ -625,14 +646,13 @@
 my_value
 1
 DROP TABLE t1;
-# 2.7 Ugly storage engine assignments mixups
-CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
+CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM'
 PARTITION BY RANGE(f1)
 SUBPARTITION BY HASH(f1)
-( PARTITION part1 VALUES LESS THAN (1000) ENGINE = 'MYISAM'
-(SUBPARTITION subpart11                          ,
-SUBPARTITION subpart12                         ),
-PARTITION part2 VALUES LESS THAN (2000)
+( PARTITION part1 VALUES LESS THAN (100)
+(SUBPARTITION subpart11 STORAGE ENGINE = 'MYISAM',
+SUBPARTITION subpart12 STORAGE ENGINE = 'MYISAM'),
+PARTITION part2 VALUES LESS THAN (2147483647)
 (SUBPARTITION subpart21 STORAGE ENGINE = 'MYISAM',
 SUBPARTITION subpart22 STORAGE ENGINE = 'MYISAM')
 );
@@ -641,7 +661,7 @@
 t1	CREATE TABLE `t1` (
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2000)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
 SELECT COUNT(*) = 0 AS my_value FROM t1;
 my_value
 1
@@ -684,22 +704,24 @@
 my_value
 1
 DROP TABLE t1;
+# 2.6.2 Storage engine assignment after partition name + after 
+#       subpartition name
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY RANGE(f1)
 SUBPARTITION BY HASH(f1)
-( PARTITION part1 VALUES LESS THAN (1000)
+( PARTITION part1 VALUES LESS THAN (100) STORAGE ENGINE = 'MYISAM'
 (SUBPARTITION subpart11 STORAGE ENGINE = 'MYISAM',
 SUBPARTITION subpart12 STORAGE ENGINE = 'MYISAM'),
-PARTITION part2 VALUES LESS THAN (2000) ENGINE = 'MYISAM'
-(SUBPARTITION subpart21                         ,
-SUBPARTITION subpart22                         )
+PARTITION part2 VALUES LESS THAN (2147483647)
+(SUBPARTITION subpart21 STORAGE ENGINE = 'MYISAM',
+SUBPARTITION subpart22 STORAGE ENGINE = 'MYISAM')
 );
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2000)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
 SELECT COUNT(*) = 0 AS my_value FROM t1;
 my_value
 1
@@ -742,17 +764,19 @@
 my_value
 1
 DROP TABLE t1;
-# 2.8 Session default engine differs from engine used within create table
+# 2.7 Session default engine differs from engine used within create table
 SET SESSION storage_engine='MEMORY';
 SET SESSION storage_engine='MYISAM';
+
 #------------------------------------------------------------------------
-#  3. Check number of partitions and subpartitions
+#  3. Check assigning the number of partitions and subpartitions
+#     with and without named partitions/subpartitions
 #------------------------------------------------------------------------
 DROP TABLE IF EXISTS t1;
 # 3.1 (positive) without partition/subpartition number assignment
-# 3.1.1     no partition number, no named partitions, no subpartitions mentioned
+# 3.1.1 no partition number, no named partitions
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1);
+PARTITION BY HASH(f1);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -801,9 +825,9 @@
 my_value
 1
 DROP TABLE t1;
-# 3.1.2     no partition number, named partitions, no subpartitions mentioned
+# 3.1.2 no partition number, named partitions
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1) (PARTITION part1, PARTITION part2);
+PARTITION BY HASH(f1) (PARTITION part1, PARTITION part2);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -855,21 +879,19 @@
 # 3.1.3 variations on no partition/subpartition number, named partitions,
 #       different subpartitions are/are not named
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1) (PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2000), PARTITION part3 VALUES LESS THAN (2147483647)) ;
+PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) (PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (200), PARTITION part3 VALUES LESS THAN (2147483647)) ;
 DROP TABLE t1;
-#  FIXME several subtestcases of 3.1.3 disabled because of server crashes
-# Bug#15407 Partitions: crash if subpartition
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1) (PARTITION part1 VALUES LESS THAN (1000)
-(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (2000)
+PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) (PARTITION part1 VALUES LESS THAN (100)
+(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (200)
 (SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483647)
-(SUBPARTITION subpart21 , SUBPARTITION subpart22 )) ;
+(SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2000)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM), PARTITION part3 VALUES LESS THAN (2147483647)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (200)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM), PARTITION part3 VALUES LESS THAN (2147483647)  (SUBPARTITION subpart31 ENGINE = MyISAM, SUBPARTITION subpart32 ENGINE = MyISAM))
 SELECT COUNT(*) = 0 AS my_value FROM t1;
 my_value
 1
@@ -911,6 +933,20 @@
 SELECT COUNT(*) = 0 AS my_value FROM t1;
 my_value
 1
+var/master-data/test/t1#P#part1#SP#subpart11.MYD
+var/master-data/test/t1#P#part1#SP#subpart11.MYI
+var/master-data/test/t1#P#part1#SP#subpart12.MYD
+var/master-data/test/t1#P#part1#SP#subpart12.MYI
+var/master-data/test/t1#P#part2#SP#subpart21.MYD
+var/master-data/test/t1#P#part2#SP#subpart21.MYI
+var/master-data/test/t1#P#part2#SP#subpart22.MYD
+var/master-data/test/t1#P#part2#SP#subpart22.MYI
+var/master-data/test/t1#P#part3#SP#subpart31.MYD
+var/master-data/test/t1#P#part3#SP#subpart31.MYI
+var/master-data/test/t1#P#part3#SP#subpart32.MYD
+var/master-data/test/t1#P#part3#SP#subpart32.MYI
+var/master-data/test/t1.frm
+var/master-data/test/t1.par
 DROP TABLE t1;
 # 3.2 partition/subpartition numbers good and bad values and notations
 DROP TABLE IF EXISTS t1;
@@ -968,13 +1004,13 @@
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS 2
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) SUBPARTITIONS 2 (PARTITION part1 VALUES LESS THAN (1000) , PARTITION part2 VALUES LESS THAN (2147483647) )
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) SUBPARTITIONS 2 (PARTITION part1 VALUES LESS THAN (100) , PARTITION part2 VALUES LESS THAN (2147483647) )
 SELECT COUNT(*) = 0 AS my_value FROM t1;
 my_value
 1
@@ -1070,13 +1106,13 @@
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS 1
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) SUBPARTITIONS 1 (PARTITION part1 VALUES LESS THAN (1000) , PARTITION part2 VALUES LESS THAN (2147483647) )
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) SUBPARTITIONS 1 (PARTITION part1 VALUES LESS THAN (100) , PARTITION part2 VALUES LESS THAN (2147483647) )
 SELECT COUNT(*) = 0 AS my_value FROM t1;
 my_value
 1
@@ -1125,7 +1161,7 @@
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS 0
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR HY000: Number of subpartitions = 0 is not an allowed value
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1) PARTITIONS -1;
@@ -1133,16 +1169,16 @@
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS -1
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2' at line 3
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1) PARTITIONS 1000000;
 ERROR HY000: Too many partitions were defined
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS 1000000
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR HY000: Too many partitions were defined
 # 3.2.4 partition/subpartition numbers STRING notation
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
@@ -1151,63 +1187,63 @@
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS '2'
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (' at line 3
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2' at line 3
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1) PARTITIONS '2.0';
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0'' at line 2
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS '2.0'
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0'
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN' at line 3
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN ' at line 3
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1) PARTITIONS '0.2E+1';
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1'' at line 2
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS '0.2E+1'
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1'
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS T' at line 3
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS TH' at line 3
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1) PARTITIONS '2A';
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A'' at line 2
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS '2A'
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A'
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN ' at line 3
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1) PARTITIONS 'A2';
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2'' at line 2
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS 'A2'
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2'
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN ' at line 3
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1) PARTITIONS '';
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''' at line 2
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS ''
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2' at line 3
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1) PARTITIONS 'GARBAGE';
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE'' at line 2
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS 'GARBAGE'
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE'
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS ' at line 3
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS T' at line 3
 # 3.2.5 partition/subpartition numbers other notations
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1) PARTITIONS 2A;
@@ -1215,67 +1251,68 @@
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS 2A
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2' at line 3
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1) PARTITIONS A2;
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2' at line 2
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS A2
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2' at line 3
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1) PARTITIONS GARBAGE;
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE' at line 2
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS GARBAGE
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS TH' at line 3
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THA' at line 3
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1) PARTITIONS "2";
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2"' at line 2
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS "2"
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2"
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (' at line 3
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2' at line 3
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1) PARTITIONS "2A";
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A"' at line 2
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS "2A"
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A"
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN ' at line 3
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1) PARTITIONS "A2";
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2"' at line 2
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS "A2"
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2"
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN ' at line 3
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION BY HASH(f1) PARTITIONS "GARBAGE";
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE"' at line 2
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
 PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
 SUBPARTITIONS "GARBAGE"
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE"
-(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS ' at line 3
-# 3.3 Mixups of number and names of partition/subpartition assigned
-# 3.3.1 (positive) number of partition/subpartition = number of named partition/subpartition
+(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS T' at line 3
+# 3.3 Mixups of assigned partition/subpartition numbers and names
+# 3.3.1 (positive) number of partition/subpartition
+#                         = number of named partition/subpartition
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1) PARTITIONS    2 ( PARTITION    part1,    PARTITION    part2 ) ;
+PARTITION BY HASH(f1) PARTITIONS 2 ( PARTITION part1, PARTITION part2 ) ;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -1284,7 +1321,7 @@
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part2  ENGINE = MyISAM)
 DROP TABLE t1;
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY RANGE(f1) PARTITIONS    2
+PARTITION BY RANGE(f1) PARTITIONS 2
 SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
 ( PARTITION part1 VALUES LESS THAN (1000)
 (SUBPARTITION subpart11, SUBPARTITION subpart12),
@@ -1298,17 +1335,19 @@
   `f2` char(20) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
 DROP TABLE t1;
-# 3.3.2 (positive) number of partition/subpartition , 0 (= no) named partition/subpartition
+# 3.3.2 (positive) number of partition/subpartition ,
+#                  0 (= no) named partition/subpartition
 #                  already checked above
-# 3.3.3 (negative) number of partitions > number of named partitions
+# 3.3.3 (negative) number of partitions/subpartitions
+#                         > number of named partitions/subpartitions
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1) PARTITIONS    2 ( PARTITION    part1 ) ;
+PARTITION BY HASH(f1) PARTITIONS 2 ( PARTITION part1 ) ;
 ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 2
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY RANGE(f1)
+PARTITION BY RANGE(f1)
 SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
 ( PARTITION part1 VALUES LESS THAN (1000)
-(SUBPARTITION subpart11                        ),
+(SUBPARTITION subpart11 ),
 PARTITION part2 VALUES LESS THAN (2147483647)
 (SUBPARTITION subpart21, SUBPARTITION subpart22)
 );
@@ -1316,12 +1355,12 @@
 PARTITION part2 VALUES LESS THAN (2147483647)
 (SUBPARTITION subpart21, SUBPAR' at line 5
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY RANGE(f1)
+PARTITION BY RANGE(f1)
 SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
 ( PARTITION part1 VALUES LESS THAN (1000)
 (SUBPARTITION subpart11, SUBPARTITION subpart12),
 PARTITION part2 VALUES LESS THAN (2000)
-(SUBPARTITION subpart21                        ),
+(SUBPARTITION subpart21 ),
 PARTITION part3 VALUES LESS THAN (2147483647)
 (SUBPARTITION subpart31, SUBPARTITION subpart32)
 );
@@ -1329,7 +1368,7 @@
 PARTITION part3 VALUES LESS THAN (2147483647)
 (SUBPARTITION subpart31, SUBPAR' at line 7
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY RANGE(f1) PARTITIONS    2
+PARTITION BY RANGE(f1) PARTITIONS 2
 SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
 ( PARTITION part1 VALUES LESS THAN (1000)
 (SUBPARTITION subpart11, SUBPARTITION subpart12),
@@ -1338,27 +1377,91 @@
 );
 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')
 )' at line 7
+# 3.3.4 (negative) number of partitions < number of named partitions
+CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
+PARTITION BY HASH(f1) PARTITIONS 1 ( PARTITION part1, PARTITION part2 ) ;
+ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 2
+CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
+PARTITION BY RANGE(f1)
+SUBPARTITION BY HASH(f1) SUBPARTITIONS 1
+( PARTITION part1 VALUES LESS THAN (1000)
+(SUBPARTITION subpart11, SUBPARTITION subpart12),
+PARTITION part2 VALUES LESS THAN (2147483647)
+(SUBPARTITION subpart21, SUBPARTITION subpart22)
+);
+ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
+PARTITION part2 VALUES LESS THAN (2147483647)
+(SUBPARTITION subpart21, SUBPAR' at line 5
+CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
+PARTITION BY RANGE(f1)
+SUBPARTITION BY HASH(f1) SUBPARTITIONS 1
+( PARTITION part1 VALUES LESS THAN (1000)
+(SUBPARTITION subpart11, SUBPARTITION subpart12),
+PARTITION part2 VALUES LESS THAN (2000)
+(SUBPARTITION subpart21                        ),
+PARTITION part3 VALUES LESS THAN (2147483647)
+(SUBPARTITION subpart31, SUBPARTITION subpart32)
+);
+ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
+PARTITION part2 VALUES LESS THAN (2000)
+(SUBPARTITION subpart21              ' at line 5
+CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
+PARTITION BY RANGE(f1)
+SUBPARTITION BY HASH(f1) SUBPARTITIONS 1
+( PARTITION part1 VALUES LESS THAN (1000)
+(SUBPARTITION subpart11, SUBPARTITION subpart12),
+PARTITION part2 VALUES LESS THAN (2147483647)
+(SUBPARTITION subpart21, SUBPARTITION subpart22)
+);
+ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
+PARTITION part2 VALUES LESS THAN (2147483647)
+(SUBPARTITION subpart21, SUBPAR' at line 5
+
 #------------------------------------------------------------------------
 #  4. Checks of logical partition/subpartition name
 #     file name clashes during CREATE TABLE
 #------------------------------------------------------------------------
+DROP TABLE IF EXISTS t1;
+#  4.1 (negative) A partition name used more than once
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1) (PARTITION part1, PARTITION part1);
+PARTITION BY HASH(f1) (PARTITION part1, PARTITION part1);
 ERROR HY000: Duplicate partition name part1
+# FIXME Implement testcases with filename problems
+#       existing file of other table --- partition/subpartition file name
+#       partition/subpartition file name  --- file of the same table
+
 #------------------------------------------------------------------------
 #  5. Alter table experiments
 #------------------------------------------------------------------------
+DROP TABLE IF EXISTS t1;
 # 5.1 alter table add partition
 # 5.1.1 (negative) add partition to non partitioned table
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20));
-# FIXME Why does the error numbers of MyISAM(1482) and NDB(1005) differ ?
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) default NULL,
+  `f2` char(20) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
 ALTER TABLE t1 ADD PARTITION (PARTITION part1);
 Got one of the listed errors
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) default NULL,
+  `f2` char(20) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1;
 # 5.1.2 Add one partition to a table with one partition
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1);
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100;
+PARTITION BY HASH(f1);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) default NULL,
+  `f2` char(20) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) 
+INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
 ALTER TABLE t1 ADD PARTITION (PARTITION part1);
 SHOW CREATE TABLE t1;
 Table	Create Table
@@ -1366,41 +1469,55 @@
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION p0  ENGINE = MyISAM, PARTITION part1  ENGINE = MyISAM)
-var/master-data/test/t1#P#p0.MYD
-var/master-data/test/t1#P#p0.MYI
-var/master-data/test/t1#P#part1.MYD
-var/master-data/test/t1#P#part1.MYI
-var/master-data/test/t1.frm
-var/master-data/test/t1.par
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 101 AND 200;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-200	1	200	---1---	---99---
-UPDATE t1 SET f1 = f1 + 200 WHERE f1 BETWEEN 50 AND 150;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-200	1	350	---1---	---99---
-DELETE FROM t1 WHERE f1 BETWEEN 250 AND 350;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-99	1	200	---1---	---9---
-INSERT INTO t1 SET f1 = 0, f2 = '---0---';
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-100	0	200	---0---	---9---
-UPDATE t1 SET f1 = 999, f2 = '---999---' WHERE f1 = 0 AND f2 = '---0---';
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-100	1	999	---1---	---999---
-TRUNCATE t1;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-0	NULL	NULL	NULL	NULL
+INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 AND 200;
+SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
+AS my_value FROM t1;
+my_value
+1
+UPDATE t1 SET f1 = f1 + 200
+WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
+SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
+AS my_value FROM t1;
+my_value
+1
+DELETE FROM t1
+WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
+SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
+AS my_value FROM t1;
+my_value
+1
+INSERT INTO t1 SET f1 = 0 , f2 = '#######';
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
+my_value
+1
+INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
+my_value
+1
+UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
+     WHERE  f1 = 0 AND f2 = '#######';
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
+my_value
+1
+DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
+SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
+my_value
+1
+TRUNCATE t1;
+SELECT COUNT(*) = 0 AS my_value FROM t1;
+my_value
+1
 DROP TABLE t1;
 # 5.1.3 Several times add one partition to a table with some partitions
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1) (PARTITION part1, PARTITION part3);
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100;
+PARTITION BY HASH(f1) (PARTITION part1, PARTITION part3);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) default NULL,
+  `f2` char(20) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part3  ENGINE = MyISAM)
+INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
 ALTER TABLE t1 ADD PARTITION (PARTITION part0);
 SHOW CREATE TABLE t1;
 Table	Create Table
@@ -1408,40 +1525,46 @@
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part3  ENGINE = MyISAM, PARTITION part0  ENGINE = MyISAM)
-var/master-data/test/t1#P#part0.MYD
-var/master-data/test/t1#P#part0.MYI
-var/master-data/test/t1#P#part1.MYD
-var/master-data/test/t1#P#part1.MYI
-var/master-data/test/t1#P#part3.MYD
-var/master-data/test/t1#P#part3.MYI
-var/master-data/test/t1.frm
-var/master-data/test/t1.par
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 101 AND 200;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-200	1	200	---1---	---99---
-UPDATE t1 SET f1 = f1 + 200 WHERE f1 BETWEEN 50 AND 150;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-200	1	350	---1---	---99---
-DELETE FROM t1 WHERE f1 BETWEEN 250 AND 350;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-99	1	200	---1---	---9---
-INSERT INTO t1 SET f1 = 0, f2 = '---0---';
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-100	0	200	---0---	---9---
-UPDATE t1 SET f1 = 999, f2 = '---999---' WHERE f1 = 0 AND f2 = '---0---';
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-100	1	999	---1---	---999---
-TRUNCATE t1;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-0	NULL	NULL	NULL	NULL
+INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 AND 200;
+SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
+AS my_value FROM t1;
+my_value
+1
+UPDATE t1 SET f1 = f1 + 200
+WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
+SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
+AS my_value FROM t1;
+my_value
+1
+DELETE FROM t1
+WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
+SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
+AS my_value FROM t1;
+my_value
+1
+INSERT INTO t1 SET f1 = 0 , f2 = '#######';
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
+my_value
+1
+INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
+my_value
+1
+UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
+     WHERE  f1 = 0 AND f2 = '#######';
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
+my_value
+1
+DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
+SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
+my_value
+1
+TRUNCATE t1;
+SELECT COUNT(*) = 0 AS my_value FROM t1;
+my_value
+1
 DELETE FROM t1;
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100;
+INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
 SHOW CREATE TABLE t1;
 Table	Create Table
@@ -1449,128 +1572,150 @@
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part3  ENGINE = MyISAM, PARTITION part0  ENGINE = MyISAM, PARTITION part2  ENGINE = MyISAM)
-var/master-data/test/t1#P#part0.MYD
-var/master-data/test/t1#P#part0.MYI
-var/master-data/test/t1#P#part1.MYD
-var/master-data/test/t1#P#part1.MYI
-var/master-data/test/t1#P#part2.MYD
-var/master-data/test/t1#P#part2.MYI
-var/master-data/test/t1#P#part3.MYD
-var/master-data/test/t1#P#part3.MYI
-var/master-data/test/t1.frm
-var/master-data/test/t1.par
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 101 AND 200;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-200	1	200	---1---	---99---
-UPDATE t1 SET f1 = f1 + 200 WHERE f1 BETWEEN 50 AND 150;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-200	1	350	---1---	---99---
-DELETE FROM t1 WHERE f1 BETWEEN 250 AND 350;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-99	1	200	---1---	---9---
-INSERT INTO t1 SET f1 = 0, f2 = '---0---';
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-100	0	200	---0---	---9---
-UPDATE t1 SET f1 = 999, f2 = '---999---' WHERE f1 = 0 AND f2 = '---0---';
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-100	1	999	---1---	---999---
-TRUNCATE t1;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-0	NULL	NULL	NULL	NULL
+INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 AND 200;
+SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
+AS my_value FROM t1;
+my_value
+1
+UPDATE t1 SET f1 = f1 + 200
+WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
+SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
+AS my_value FROM t1;
+my_value
+1
+DELETE FROM t1
+WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
+SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
+AS my_value FROM t1;
+my_value
+1
+INSERT INTO t1 SET f1 = 0 , f2 = '#######';
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
+my_value
+1
+INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
+my_value
+1
+UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
+     WHERE  f1 = 0 AND f2 = '#######';
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
+my_value
+1
+DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
+SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
+my_value
+1
+TRUNCATE t1;
+SELECT COUNT(*) = 0 AS my_value FROM t1;
+my_value
+1
 DELETE FROM t1;
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100;
+INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part3  ENGINE = MyISAM, PARTITION part0  ENGINE = MyISAM, PARTITION part2  ENGINE = MyISAM)
-var/master-data/test/t1#P#part0.MYD
-var/master-data/test/t1#P#part0.MYI
-var/master-data/test/t1#P#part1.MYD
-var/master-data/test/t1#P#part1.MYI
-var/master-data/test/t1#P#part2.MYD
-var/master-data/test/t1#P#part2.MYI
-var/master-data/test/t1#P#part3.MYD
-var/master-data/test/t1#P#part3.MYI
-var/master-data/test/t1.frm
-var/master-data/test/t1.par
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 101 AND 200;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-200	1	200	---1---	---99---
-UPDATE t1 SET f1 = f1 + 200 WHERE f1 BETWEEN 50 AND 150;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-200	1	350	---1---	---99---
-DELETE FROM t1 WHERE f1 BETWEEN 250 AND 350;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-99	1	200	---1---	---9---
-INSERT INTO t1 SET f1 = 0, f2 = '---0---';
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-100	0	200	---0---	---9---
-UPDATE t1 SET f1 = 999, f2 = '---999---' WHERE f1 = 0 AND f2 = '---0---';
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-100	1	999	---1---	---999---
-TRUNCATE t1;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-0	NULL	NULL	NULL	NULL
+INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 AND 200;
+SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
+AS my_value FROM t1;
+my_value
+1
+UPDATE t1 SET f1 = f1 + 200
+WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
+SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
+AS my_value FROM t1;
+my_value
+1
+DELETE FROM t1
+WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
+SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
+AS my_value FROM t1;
+my_value
+1
+INSERT INTO t1 SET f1 = 0 , f2 = '#######';
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
+my_value
+1
+INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
+my_value
+1
+UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
+     WHERE  f1 = 0 AND f2 = '#######';
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
+my_value
+1
+DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
+SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
+my_value
+1
+TRUNCATE t1;
+SELECT COUNT(*) = 0 AS my_value FROM t1;
+my_value
+1
 DROP TABLE t1;
-# 5.1.4 Add several partition to a table with some partitions
+# 5.1.4 Add several partitions to a table with some partitions
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1) (PARTITION part1, PARTITION part3);
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100;
+PARTITION BY HASH(f1) (PARTITION part1, PARTITION part3);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `f1` int(11) default NULL,
   `f2` char(20) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part3  ENGINE = MyISAM)
-var/master-data/test/t1#P#part1.MYD
-var/master-data/test/t1#P#part1.MYI
-var/master-data/test/t1#P#part3.MYD
-var/master-data/test/t1#P#part3.MYI
-var/master-data/test/t1.frm
-var/master-data/test/t1.par
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 101 AND 200;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-200	1	200	---1---	---99---
-UPDATE t1 SET f1 = f1 + 200 WHERE f1 BETWEEN 50 AND 150;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-200	1	350	---1---	---99---
-DELETE FROM t1 WHERE f1 BETWEEN 250 AND 350;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-99	1	200	---1---	---9---
-INSERT INTO t1 SET f1 = 0, f2 = '---0---';
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-100	0	200	---0---	---9---
-UPDATE t1 SET f1 = 999, f2 = '---999---' WHERE f1 = 0 AND f2 = '---0---';
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-100	1	999	---1---	---999---
-TRUNCATE t1;
-SELECT COUNT(*),MIN(f1),MAX(f1),MIN(f2),MAX(f2) FROM t1;
-COUNT(*)	MIN(f1)	MAX(f1)	MIN(f2)	MAX(f2)
-0	NULL	NULL	NULL	NULL
+INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) default NULL,
+  `f2` char(20) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part3  ENGINE = MyISAM)
+INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 AND 200;
+SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
+AS my_value FROM t1;
+my_value
+1
+UPDATE t1 SET f1 = f1 + 200
+WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
+SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
+AS my_value FROM t1;
+my_value
+1
+DELETE FROM t1
+WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
+SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
+AS my_value FROM t1;
+my_value
+1
+INSERT INTO t1 SET f1 = 0 , f2 = '#######';
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
+my_value
+1
+INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
+my_value
+1
+UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
+     WHERE  f1 = 0 AND f2 = '#######';
+SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
+my_value
+1
+DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
+SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
+my_value
+1
+TRUNCATE t1;
+SELECT COUNT(*) = 0 AS my_value FROM t1;
+my_value
+1
 DROP TABLE t1;
 # 5.1.5 (negative) Add partitions to a table with some partitions
 #       clash on new and already existing partition names
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY HASH(f1) (PARTITION part1, PARTITION part2, PARTITION part3);
+PARTITION BY HASH(f1) (PARTITION part1, PARTITION part2, PARTITION part3);
 ALTER TABLE t1 ADD PARTITION (PARTITION part1);
 ERROR HY000: Duplicate partition name part1
 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
@@ -1581,9 +1726,13 @@
 ERROR HY000: Duplicate partition name part1
 DROP TABLE t1;
 # 5.2 alter table add subpartition
-# 5.2.1 Add one subpartition to a table with subpartitioning rule
+# 5.2.1 Add one subpartition to a table with subpartitioning rule and
+#       no explicit defined subpartitions
 CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
-PARTITION    BY RANGE(f1)
+PARTITION BY RANGE(f1)
 SUBPARTITION BY HASH(f1)
-(PARTITION part1 VALUES LESS THAN (1000));
-INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100;
+(PARTITION part1 VALUES LESS THAN (100));
+INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
+ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (2147483647)
+(SUBPARTITION subpart21));
+DROP TABLE t1;
Thread
bk commit into 5.1 tree (mleich:1.2030)mleich11 Jan