MySQL Lists: commits: bk commit into 5.1 tree (mattiasj:1.2528) BUG#31931

List:	Commits	« Previous Message Next Message »
From:	mattiasj	Date:	February 25 2008 9:19pm
Subject:	bk commit into 5.1 tree (mattiasj:1.2528) BUG#31931
View as plain text
Below is the list of changes that have just been committed into a local
5.1 repository of mattiasj.  When mattiasj 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@stripped, 2008-02-25 21:18:50+01:00, mattiasj@witty. +12 -0
  Post push fix
  
  Fixed a missed case in the patch for Bug#31931.
  Also makes Bug#33722 a duplicate of Bug#31931.
  Added tests for better coverage.
  Replaced some legacy function calls.

  mysql-test/r/partition.result@stripped, 2008-02-25 21:18:40+01:00, mattiasj@witty. +14 -0
    Added tests for better coverage

  mysql-test/r/partition_datatype.result@stripped, 2008-02-25 21:18:41+01:00, mattiasj@witty.
+8 -0
    Added tests for better coverage

  mysql-test/r/partition_error.result@stripped, 2008-02-25 21:18:41+01:00, mattiasj@witty. +13
-0
    Added tests for better coverage

  mysql-test/suite/parts/inc/partition_engine.inc@stripped, 2008-02-25 21:18:41+01:00,
mattiasj@witty. +54 -24
    Bug#31931: Mix of handlers error message
    
    Bug#33722 is fixed within this patch too

  mysql-test/suite/parts/r/partition_engine_innodb.result@stripped, 2008-02-25 21:18:42+01:00,
mattiasj@witty. +1366 -12
    Bug#31931: Mix of handlers error message
    
    Bug#33722 is fixed within this patch too

  mysql-test/suite/parts/r/partition_engine_myisam.result@stripped, 2008-02-25 21:18:42+01:00,
mattiasj@witty. +1408 -12
    Bug#31931: Mix of handlers error message
    
    Bug#33722 is fixed within this patch too

  mysql-test/t/partition.test@stripped, 2008-02-25 21:18:43+01:00, mattiasj@witty. +12 -2
    Added tests for better coverage

  mysql-test/t/partition_datatype.test@stripped, 2008-02-25 21:18:43+01:00, mattiasj@witty. +3
-0
    Added tests for better coverage

  mysql-test/t/partition_error.test@stripped, 2008-02-25 21:18:44+01:00, mattiasj@witty. +18
-0
    Added tests for Bug#31931

  sql/partition_info.cc@stripped, 2008-02-25 21:18:44+01:00, mattiasj@witty. +63 -36
    Bug#31931: Mix of handlers error message
    
    Fixed case where given info->db_type not matched
    thd->lex->create_info.db_type
    
    And the check for inconsistent subpartition engines-clauses.

  sql/sql_partition.cc@stripped, 2008-02-25 21:18:45+01:00, mattiasj@witty. +5 -5
    Changed ha_legacy_type to ha_resolve_storage_engine_name

  sql/sql_table.cc@stripped, 2008-02-25 21:18:45+01:00, mattiasj@witty. +4 -3
    Changed ha_legacy_type to ha_resolve_storage_engine_name

diff -Nrup a/mysql-test/r/partition.result b/mysql-test/r/partition.result
--- a/mysql-test/r/partition.result	2008-02-24 17:25:43 +01:00
+++ b/mysql-test/r/partition.result	2008-02-25 21:18:40 +01:00
@@ -1052,6 +1052,12 @@ test.t1	repair	note	The storage engine f
 OPTIMIZE TABLE t1;
 Table	Op	Msg_type	Msg_text
 test.t1	optimize	note	The storage engine for the table doesn't support optimize
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	note	The storage engine for the table doesn't support check
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	note	The storage engine for the table doesn't support analyze
 drop table t1;
 drop procedure if exists mysqltest_1;
 create table t1 (a int)
@@ -1290,6 +1296,14 @@ ALTER TABLE t1 OPTIMIZE PARTITION p1 EXT
 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 'EXTENDED' at line 1
 ALTER TABLE t1 ANALYZE PARTITION p1 EXTENDED;
 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 'EXTENDED' at line 1
+ALTER TABLE t1 ANALYZE PARTITION p1;
+ERROR 42000: The storage engine for the table doesn't support analyze partition
+ALTER TABLE t1 CHECK PARTITION p1;
+ERROR 42000: The storage engine for the table doesn't support check partition
+ALTER TABLE t1 REPAIR PARTITION p1;
+ERROR 42000: The storage engine for the table doesn't support repair partition
+ALTER TABLE t1 OPTIMIZE PARTITION p1;
+ERROR 42000: The storage engine for the table doesn't support optimize partition
 DROP TABLE t1;
 CREATE TABLE t1 (s1 BIGINT UNSIGNED)
 PARTITION BY RANGE (s1) (
diff -Nrup a/mysql-test/r/partition_datatype.result
b/mysql-test/r/partition_datatype.result
--- a/mysql-test/r/partition_datatype.result	2008-02-07 15:54:53 +01:00
+++ b/mysql-test/r/partition_datatype.result	2008-02-25 21:18:41 +01:00
@@ -135,8 +135,16 @@ drop table t1;
 # test with null allowed
 create table t1 (a bit) partition by key (a);
 insert into t1 values (b'1');
+insert into t1 values (NULL);
 select hex(a) from t1 where a = b'1';
 hex(a)
+1
+select hex(a) from t1 where a is NULL;
+hex(a)
+NULL
+select hex(a) from t1 order by a;
+hex(a)
+NULL
 1
 drop table t1;
 create table t1 (a tinyint) partition by key (a);
diff -Nrup a/mysql-test/r/partition_error.result b/mysql-test/r/partition_error.result
--- a/mysql-test/r/partition_error.result	2007-11-10 13:09:16 +01:00
+++ b/mysql-test/r/partition_error.result	2008-02-25 21:18:41 +01:00
@@ -1,4 +1,17 @@
 drop table if exists t1;
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (a)
+( PARTITION p0 ENGINE=MyISAM,
+PARTITION p1);
+ERROR HY000: The mix of handlers in the partitions is not allowed in this version of
MySQL
+CREATE TABLE t1 (a INT)
+PARTITION BY LIST (a)
+SUBPARTITION BY HASH (a)
+( PARTITION p0 VALUES IN (0)
+( SUBPARTITION s0, SUBPARTITION s1 ENGINE=MyISAM, SUBPARTITION s2),
+PARTITION p1 VALUES IN (1)
+( SUBPARTITION s3 ENGINE=MyISAM, SUBPARTITION s4, SUBPARTITION s5 ENGINE=MyISAM));
+ERROR HY000: The mix of handlers in the partitions is not allowed in this version of
MySQL
 CREATE TABLE t1 (
 a int
 )
diff -Nrup a/mysql-test/suite/parts/inc/partition_engine.inc
b/mysql-test/suite/parts/inc/partition_engine.inc
--- a/mysql-test/suite/parts/inc/partition_engine.inc	2008-01-09 13:15:47 +01:00
+++ b/mysql-test/suite/parts/inc/partition_engine.inc	2008-02-25 21:18:41 +01:00
@@ -112,11 +112,23 @@ SUBPARTITION BY HASH(f_int1)
      (SUBPARTITION subpart21 STORAGE ENGINE = $engine,
       SUBPARTITION subpart22 STORAGE ENGINE = $engine)
 );
---echo # this should fail with ER_MIX_HANDLER_ERROR
---echo # after fixing Bug#33722
+--error ER_MIX_HANDLER_ERROR
+eval CREATE TABLE t1 (
+$column_list
+)
+PARTITION BY RANGE(f_int1)
+SUBPARTITION BY HASH(f_int1)
+( PARTITION part1 VALUES LESS THAN ($max_row_div2)
+     (SUBPARTITION subpart11 STORAGE ENGINE = $engine,
+      SUBPARTITION subpart12 STORAGE ENGINE = $engine),
+  PARTITION part2 VALUES LESS THAN $MAX_VALUE
+     (SUBPARTITION subpart21,
+      SUBPARTITION subpart22 )
+);
 eval CREATE TABLE t1 (
 $column_list
 )
+ENGINE = $engine
 PARTITION BY RANGE(f_int1)
 SUBPARTITION BY HASH(f_int1)
 ( PARTITION part1 VALUES LESS THAN ($max_row_div2)
@@ -135,14 +147,38 @@ DROP TABLE t1;
 --echo # 4 Storage engine assignment after partition name + after name of
 --echo #   subpartitions belonging to another partition
 --echo #------------------------------------------------------------------------
---echo # this should work
---echo # after fixing Bug#33722
 --error ER_MIX_HANDLER_ERROR
 eval CREATE TABLE t1 (
 $column_list
 )
 PARTITION BY RANGE(f_int1)
 SUBPARTITION BY HASH(f_int1)
+( PARTITION part1 VALUES LESS THAN ($max_row_div2)
+     (SUBPARTITION subpart11,
+      SUBPARTITION subpart12),
+  PARTITION part2 VALUES LESS THAN $MAX_VALUE
+     (SUBPARTITION subpart21 STORAGE ENGINE = $engine,
+      SUBPARTITION subpart22 STORAGE ENGINE = $engine)
+);
+eval CREATE TABLE t1 (
+$column_list
+)
+ENGINE = $engine
+PARTITION BY RANGE(f_int1)
+SUBPARTITION BY HASH(f_int1)
+( PARTITION part1 VALUES LESS THAN ($max_row_div2) ENGINE = $engine
+     (SUBPARTITION subpart11,
+      SUBPARTITION subpart12),
+  PARTITION part2 VALUES LESS THAN $MAX_VALUE
+     (SUBPARTITION subpart21,
+      SUBPARTITION subpart22 STORAGE ENGINE = $engine)
+);
+DROP TABLE t1;
+eval CREATE TABLE t1 (
+$column_list
+)
+PARTITION BY RANGE(f_int1)
+SUBPARTITION BY HASH(f_int1)
 ( PARTITION part1 VALUES LESS THAN ($max_row_div2) ENGINE = $engine
      (SUBPARTITION subpart11,
       SUBPARTITION subpart12),
@@ -150,13 +186,10 @@ SUBPARTITION BY HASH(f_int1)
      (SUBPARTITION subpart21 STORAGE ENGINE = $engine,
       SUBPARTITION subpart22 STORAGE ENGINE = $engine)
 );
-#INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
-#SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
-#--source suite/parts/inc/partition_check.inc
-#DROP TABLE t1;
---echo # this should work
---echo # after fixing Bug#33722
---error ER_MIX_HANDLER_ERROR
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
+--source suite/parts/inc/partition_check.inc
+DROP TABLE t1;
 eval CREATE TABLE t1 (
 $column_list
 )
@@ -166,13 +199,13 @@ SUBPARTITION BY HASH(f_int1)
      (SUBPARTITION subpart11 STORAGE ENGINE = $engine,
       SUBPARTITION subpart12 STORAGE ENGINE = $engine),
   PARTITION part2 VALUES LESS THAN $MAX_VALUE ENGINE = $engine
-     (SUBPARTITION subpart21,
+     (SUBPARTITION subpart21 ENGINE = $engine,
       SUBPARTITION subpart22)
 );
-#INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
-#SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
-#--source suite/parts/inc/partition_check.inc
-#DROP TABLE t1;
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
+--source suite/parts/inc/partition_check.inc
+DROP TABLE t1;
 #
 --echo #------------------------------------------------------------------------
 --echo # 5 Precedence of storage engine assignments (if there is any)
@@ -208,10 +241,7 @@ SELECT f_int1,f_int2,f_char1,f_char2,f_c
 DROP TABLE t1;
 --echo # 6.2 Storage engine assignment after partition name + after
 --echo #     subpartition name
-#     in partition part + in sub partition part
---echo # this should work
---echo # after fixing Bug#33722
---error ER_MIX_HANDLER_ERROR
+--echo #     in partition part + in sub partition part
 eval CREATE TABLE t1 (
 $column_list
 )
@@ -224,10 +254,10 @@ SUBPARTITION BY HASH(f_int1)
      (SUBPARTITION subpart21 STORAGE ENGINE = $engine,
       SUBPARTITION subpart22 STORAGE ENGINE = $engine)
 );
-#INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
-#SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
-#--source suite/parts/inc/partition_check.inc
-#DROP TABLE t1;
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
+--source suite/parts/inc/partition_check.inc
+DROP TABLE t1;
 
 --echo #------------------------------------------------------------------------
 --echo # 6 Session default engine differs from engine used within create table
diff -Nrup a/mysql-test/suite/parts/r/partition_engine_innodb.result
b/mysql-test/suite/parts/r/partition_engine_innodb.result
--- a/mysql-test/suite/parts/r/partition_engine_innodb.result	2008-02-24 19:13:16 +01:00
+++ b/mysql-test/suite/parts/r/partition_engine_innodb.result	2008-02-25 21:18:42 +01:00
@@ -1446,8 +1446,6 @@ PARTITION part2 VALUES LESS THAN (214748
 SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
 );
 ERROR HY000: The mix of handlers in the partitions is not allowed in this version of
MySQL
-# this should fail with ER_MIX_HANDLER_ERROR
-# after fixing Bug#33722
 CREATE TABLE t1 (
 f_int1 INTEGER,
 f_int2 INTEGER,
@@ -1464,6 +1462,24 @@ PARTITION part2 VALUES LESS THAN (214748
 (SUBPARTITION subpart21,
 SUBPARTITION subpart22 )
 );
+ERROR HY000: The mix of handlers in the partitions is not allowed in this version of
MySQL
+CREATE TABLE t1 (
+f_int1 INTEGER,
+f_int2 INTEGER,
+f_char1 CHAR(20),
+f_char2 CHAR(20),
+f_charbig VARCHAR(1000)
+)
+ENGINE = 'InnoDB'
+PARTITION BY RANGE(f_int1)
+SUBPARTITION BY HASH(f_int1)
+( PARTITION part1 VALUES LESS THAN (10)
+(SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
+SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
+PARTITION part2 VALUES LESS THAN (2147483646)
+(SUBPARTITION subpart21,
+SUBPARTITION subpart22 )
+);
 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
 # Start usability test (inc/partition_check.inc)
@@ -1905,8 +1921,6 @@ DROP TABLE t1;
 # 4 Storage engine assignment after partition name + after name of
 #   subpartitions belonging to another partition
 #------------------------------------------------------------------------
-# this should work
-# after fixing Bug#33722
 CREATE TABLE t1 (
 f_int1 INTEGER,
 f_int2 INTEGER,
@@ -1916,7 +1930,7 @@ f_charbig VARCHAR(1000)
 )
 PARTITION BY RANGE(f_int1)
 SUBPARTITION BY HASH(f_int1)
-( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'InnoDB'
+( PARTITION part1 VALUES LESS THAN (10)
 (SUBPARTITION subpart11,
 SUBPARTITION subpart12),
 PARTITION part2 VALUES LESS THAN (2147483646)
@@ -1924,8 +1938,477 @@ PARTITION part2 VALUES LESS THAN (214748
 SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
 );
 ERROR HY000: The mix of handlers in the partitions is not allowed in this version of
MySQL
-# this should work
-# after fixing Bug#33722
+CREATE TABLE t1 (
+f_int1 INTEGER,
+f_int2 INTEGER,
+f_char1 CHAR(20),
+f_char2 CHAR(20),
+f_charbig VARCHAR(1000)
+)
+ENGINE = 'InnoDB'
+PARTITION BY RANGE(f_int1)
+SUBPARTITION BY HASH(f_int1)
+( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'InnoDB'
+(SUBPARTITION subpart11,
+SUBPARTITION subpart12),
+PARTITION part2 VALUES LESS THAN (2147483646)
+(SUBPARTITION subpart21,
+SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
+);
+DROP TABLE t1;
+CREATE TABLE t1 (
+f_int1 INTEGER,
+f_int2 INTEGER,
+f_char1 CHAR(20),
+f_char2 CHAR(20),
+f_charbig VARCHAR(1000)
+)
+PARTITION BY RANGE(f_int1)
+SUBPARTITION BY HASH(f_int1)
+( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'InnoDB'
+(SUBPARTITION subpart11,
+SUBPARTITION subpart12),
+PARTITION part2 VALUES LESS THAN (2147483646)
+(SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB',
+SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
+);
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
+# Start usability test (inc/partition_check.inc)
+create_command
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f_int1` int(11) DEFAULT NULL,
+  `f_int2` int(11) DEFAULT NULL,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION
BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE =
InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB), PARTITION part2 VALUES LESS THAN
(2147483646) (SUBPARTITION subpart21 ENGINE = InnoDB, SUBPARTITION subpart22 ENGINE =
InnoDB)) */
+
+# check prerequisites-1 success:    1
+# check COUNT(*) success:    1
+# check MIN/MAX(f_int1) success:    1
+# check MIN/MAX(f_int2) success:    1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
+WHERE f_int1 IN (2,3);
+# check prerequisites-3 success:    1
+DELETE FROM t1 WHERE f_charbig = 'delete me';
+# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
+# check read via f_int1 success: 1
+# check read via f_int2 success: 1
+	
+# check multiple-1 success: 	1
+DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
+	
+# check multiple-2 success: 	1
+INSERT INTO t1 SELECT * FROM t0_template
+WHERE MOD(f_int1,3) = 0;
+	
+# check multiple-3 success: 	1
+UPDATE t1 SET f_int1 = f_int1 + @max_row
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
+AND @max_row_div2 + @max_row_div4;
+	
+# check multiple-4 success: 	1
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
+AND @max_row_div2 + @max_row_div4 + @max_row;
+	
+# check multiple-5 success: 	1
+SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+	
+# check single-1 success: 	1
+SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+	
+# check single-2 success: 	1
+SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
+SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value2
+WHERE  f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
+	
+# check single-3 success: 	1
+SET @cur_value1= -1;
+SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value1
+WHERE  f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
+	
+# check single-4 success: 	1
+SELECT MAX(f_int1) INTO @cur_value FROM t1;
+DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
+	
+# check single-5 success: 	1
+DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
+	
+# check single-6 success: 	1
+INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig =
'#2147483647##';
+ERROR HY000: Table has no partition for value 2147483647
+DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
+INSERT t1 SET f_int1 = 0 , f_int2 = 0,
+f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
+f_charbig = '#NULL#';
+INSERT INTO t1
+SET f_int1 = NULL , f_int2 = -@max_row,
+f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
+f_charbig = '#NULL#';
+# check null success:    1
+	
+# check null-1 success: 	1
+UPDATE t1 SET f_int1 = -@max_row
+WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-2 success: 	1
+UPDATE t1 SET f_int1 = NULL
+WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-3 success: 	1
+DELETE FROM t1
+WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-4 success: 	1
+DELETE FROM t1
+WHERE f_int1 = 0 AND f_int2 = 0
+AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
+AND f_charbig = '#NULL#';
+SET AUTOCOMMIT= 0;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+	
+# check transactions-1 success: 	1
+COMMIT WORK;
+	
+# check transactions-2 success: 	1
+ROLLBACK WORK;
+	
+# check transactions-3 success: 	1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+ROLLBACK WORK;
+	
+# check transactions-4 success: 	1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+	
+# check transactions-5 success: 	1
+ROLLBACK WORK;
+	
+# check transactions-6 success: 	1
+# INFO: Storage engine used for t1 seems to be transactional.
+COMMIT;
+	
+# check transactions-7 success: 	1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+SET @@session.sql_mode = 'traditional';
+SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
+'', '', 'was inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+ERROR 22012: Division by 0
+COMMIT;
+	
+# check transactions-8 success: 	1
+# INFO: Storage engine used for t1 seems to be able to revert
+#       changes made by the failing statement.
+SET @@session.sql_mode = '';
+SET AUTOCOMMIT= 1;
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+UPDATE t1 SET f_charbig = REPEAT('b', 1000);
+	
+# check special-1 success: 	1
+UPDATE t1 SET f_charbig = '';
+	
+# check special-2 success: 	1
+UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+	
+# check trigger-1 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+	
+# check trigger-2 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-3 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-4 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-5 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-6 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-7 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-8 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = old.f_int1 + @max_row,
+new.f_int2 = old.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+	
+# check trigger-9 success: 	1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = new.f_int1 + @max_row,
+new.f_int2 = new.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+	
+# check trigger-10 success: 	1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+	
+# check trigger-11 success: 	1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_char1, f_char2, f_charbig)
+SELECT CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+	
+# check trigger-12 success: 	1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+ANALYZE  TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	note	The storage engine for the table doesn't support analyze
+CHECK    TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	check	note	The storage engine for the table doesn't support check
+CHECKSUM TABLE t1 EXTENDED;
+Table	Checksum
+test.t1	<some_value>
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	The storage engine for the table doesn't support optimize
+# check layout success:    1
+REPAIR   TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	note	The storage engine for the table doesn't support repair
+# check layout success:    1
+TRUNCATE t1;
+	
+# check TRUNCATE success: 	1
+# check layout success:    1
+# End usability test (inc/partition_check.inc)
+DROP TABLE t1;
 CREATE TABLE t1 (
 f_int1 INTEGER,
 f_int2 INTEGER,
@@ -1939,10 +2422,446 @@ SUBPARTITION BY HASH(f_int1)
 (SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
 SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
 PARTITION part2 VALUES LESS THAN (2147483646) ENGINE = 'InnoDB'
-(SUBPARTITION subpart21,
+(SUBPARTITION subpart21 ENGINE = 'InnoDB',
 SUBPARTITION subpart22)
 );
-ERROR HY000: The mix of handlers in the partitions is not allowed in this version of
MySQL
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
+# Start usability test (inc/partition_check.inc)
+create_command
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f_int1` int(11) DEFAULT NULL,
+  `f_int2` int(11) DEFAULT NULL,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION
BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE =
InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB), PARTITION part2 VALUES LESS THAN
(2147483646) (SUBPARTITION subpart21 ENGINE = InnoDB, SUBPARTITION subpart22 ENGINE =
InnoDB)) */
+
+# check prerequisites-1 success:    1
+# check COUNT(*) success:    1
+# check MIN/MAX(f_int1) success:    1
+# check MIN/MAX(f_int2) success:    1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
+WHERE f_int1 IN (2,3);
+# check prerequisites-3 success:    1
+DELETE FROM t1 WHERE f_charbig = 'delete me';
+# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
+# check read via f_int1 success: 1
+# check read via f_int2 success: 1
+	
+# check multiple-1 success: 	1
+DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
+	
+# check multiple-2 success: 	1
+INSERT INTO t1 SELECT * FROM t0_template
+WHERE MOD(f_int1,3) = 0;
+	
+# check multiple-3 success: 	1
+UPDATE t1 SET f_int1 = f_int1 + @max_row
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
+AND @max_row_div2 + @max_row_div4;
+	
+# check multiple-4 success: 	1
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
+AND @max_row_div2 + @max_row_div4 + @max_row;
+	
+# check multiple-5 success: 	1
+SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+	
+# check single-1 success: 	1
+SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+	
+# check single-2 success: 	1
+SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
+SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value2
+WHERE  f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
+	
+# check single-3 success: 	1
+SET @cur_value1= -1;
+SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value1
+WHERE  f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
+	
+# check single-4 success: 	1
+SELECT MAX(f_int1) INTO @cur_value FROM t1;
+DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
+	
+# check single-5 success: 	1
+DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
+	
+# check single-6 success: 	1
+INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig =
'#2147483647##';
+ERROR HY000: Table has no partition for value 2147483647
+DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
+INSERT t1 SET f_int1 = 0 , f_int2 = 0,
+f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
+f_charbig = '#NULL#';
+INSERT INTO t1
+SET f_int1 = NULL , f_int2 = -@max_row,
+f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
+f_charbig = '#NULL#';
+# check null success:    1
+	
+# check null-1 success: 	1
+UPDATE t1 SET f_int1 = -@max_row
+WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-2 success: 	1
+UPDATE t1 SET f_int1 = NULL
+WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-3 success: 	1
+DELETE FROM t1
+WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-4 success: 	1
+DELETE FROM t1
+WHERE f_int1 = 0 AND f_int2 = 0
+AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
+AND f_charbig = '#NULL#';
+SET AUTOCOMMIT= 0;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+	
+# check transactions-1 success: 	1
+COMMIT WORK;
+	
+# check transactions-2 success: 	1
+ROLLBACK WORK;
+	
+# check transactions-3 success: 	1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+ROLLBACK WORK;
+	
+# check transactions-4 success: 	1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+	
+# check transactions-5 success: 	1
+ROLLBACK WORK;
+	
+# check transactions-6 success: 	1
+# INFO: Storage engine used for t1 seems to be transactional.
+COMMIT;
+	
+# check transactions-7 success: 	1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+SET @@session.sql_mode = 'traditional';
+SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
+'', '', 'was inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+ERROR 22012: Division by 0
+COMMIT;
+	
+# check transactions-8 success: 	1
+# INFO: Storage engine used for t1 seems to be able to revert
+#       changes made by the failing statement.
+SET @@session.sql_mode = '';
+SET AUTOCOMMIT= 1;
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+UPDATE t1 SET f_charbig = REPEAT('b', 1000);
+	
+# check special-1 success: 	1
+UPDATE t1 SET f_charbig = '';
+	
+# check special-2 success: 	1
+UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+	
+# check trigger-1 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+	
+# check trigger-2 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-3 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-4 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-5 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-6 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-7 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-8 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = old.f_int1 + @max_row,
+new.f_int2 = old.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+	
+# check trigger-9 success: 	1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = new.f_int1 + @max_row,
+new.f_int2 = new.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+	
+# check trigger-10 success: 	1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+	
+# check trigger-11 success: 	1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_char1, f_char2, f_charbig)
+SELECT CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+	
+# check trigger-12 success: 	1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+ANALYZE  TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	note	The storage engine for the table doesn't support analyze
+CHECK    TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	check	note	The storage engine for the table doesn't support check
+CHECKSUM TABLE t1 EXTENDED;
+Table	Checksum
+test.t1	<some_value>
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	The storage engine for the table doesn't support optimize
+# check layout success:    1
+REPAIR   TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	note	The storage engine for the table doesn't support repair
+# check layout success:    1
+TRUNCATE t1;
+	
+# check TRUNCATE success: 	1
+# check layout success:    1
+# End usability test (inc/partition_check.inc)
+DROP TABLE t1;
 #------------------------------------------------------------------------
 # 5 Precedence of storage engine assignments (if there is any)
 #------------------------------------------------------------------------
@@ -2853,8 +3772,7 @@ TRUNCATE t1;
 DROP TABLE t1;
 # 6.2 Storage engine assignment after partition name + after
 #     subpartition name
-# this should work
-# after fixing Bug#33722
+#     in partition part + in sub partition part
 CREATE TABLE t1 (
 f_int1 INTEGER,
 f_int2 INTEGER,
@@ -2871,7 +3789,443 @@ PARTITION part2 VALUES LESS THAN (214748
 (SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB',
 SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
 );
-ERROR HY000: The mix of handlers in the partitions is not allowed in this version of
MySQL
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
+# Start usability test (inc/partition_check.inc)
+create_command
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f_int1` int(11) DEFAULT NULL,
+  `f_int2` int(11) DEFAULT NULL,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION
BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE =
InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB), PARTITION part2 VALUES LESS THAN
(2147483646) (SUBPARTITION subpart21 ENGINE = InnoDB, SUBPARTITION subpart22 ENGINE =
InnoDB)) */
+
+# check prerequisites-1 success:    1
+# check COUNT(*) success:    1
+# check MIN/MAX(f_int1) success:    1
+# check MIN/MAX(f_int2) success:    1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
+WHERE f_int1 IN (2,3);
+# check prerequisites-3 success:    1
+DELETE FROM t1 WHERE f_charbig = 'delete me';
+# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
+# check read via f_int1 success: 1
+# check read via f_int2 success: 1
+	
+# check multiple-1 success: 	1
+DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
+	
+# check multiple-2 success: 	1
+INSERT INTO t1 SELECT * FROM t0_template
+WHERE MOD(f_int1,3) = 0;
+	
+# check multiple-3 success: 	1
+UPDATE t1 SET f_int1 = f_int1 + @max_row
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
+AND @max_row_div2 + @max_row_div4;
+	
+# check multiple-4 success: 	1
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
+AND @max_row_div2 + @max_row_div4 + @max_row;
+	
+# check multiple-5 success: 	1
+SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+	
+# check single-1 success: 	1
+SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+	
+# check single-2 success: 	1
+SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
+SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value2
+WHERE  f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
+	
+# check single-3 success: 	1
+SET @cur_value1= -1;
+SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value1
+WHERE  f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
+	
+# check single-4 success: 	1
+SELECT MAX(f_int1) INTO @cur_value FROM t1;
+DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
+	
+# check single-5 success: 	1
+DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
+	
+# check single-6 success: 	1
+INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig =
'#2147483647##';
+ERROR HY000: Table has no partition for value 2147483647
+DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
+INSERT t1 SET f_int1 = 0 , f_int2 = 0,
+f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
+f_charbig = '#NULL#';
+INSERT INTO t1
+SET f_int1 = NULL , f_int2 = -@max_row,
+f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
+f_charbig = '#NULL#';
+# check null success:    1
+	
+# check null-1 success: 	1
+UPDATE t1 SET f_int1 = -@max_row
+WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-2 success: 	1
+UPDATE t1 SET f_int1 = NULL
+WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-3 success: 	1
+DELETE FROM t1
+WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-4 success: 	1
+DELETE FROM t1
+WHERE f_int1 = 0 AND f_int2 = 0
+AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
+AND f_charbig = '#NULL#';
+SET AUTOCOMMIT= 0;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+	
+# check transactions-1 success: 	1
+COMMIT WORK;
+	
+# check transactions-2 success: 	1
+ROLLBACK WORK;
+	
+# check transactions-3 success: 	1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+ROLLBACK WORK;
+	
+# check transactions-4 success: 	1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+	
+# check transactions-5 success: 	1
+ROLLBACK WORK;
+	
+# check transactions-6 success: 	1
+# INFO: Storage engine used for t1 seems to be transactional.
+COMMIT;
+	
+# check transactions-7 success: 	1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+SET @@session.sql_mode = 'traditional';
+SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
+'', '', 'was inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+ERROR 22012: Division by 0
+COMMIT;
+	
+# check transactions-8 success: 	1
+# INFO: Storage engine used for t1 seems to be able to revert
+#       changes made by the failing statement.
+SET @@session.sql_mode = '';
+SET AUTOCOMMIT= 1;
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+UPDATE t1 SET f_charbig = REPEAT('b', 1000);
+	
+# check special-1 success: 	1
+UPDATE t1 SET f_charbig = '';
+	
+# check special-2 success: 	1
+UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+	
+# check trigger-1 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+	
+# check trigger-2 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-3 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-4 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-5 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-6 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-7 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-8 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = old.f_int1 + @max_row,
+new.f_int2 = old.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+	
+# check trigger-9 success: 	1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = new.f_int1 + @max_row,
+new.f_int2 = new.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+	
+# check trigger-10 success: 	1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+	
+# check trigger-11 success: 	1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_char1, f_char2, f_charbig)
+SELECT CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+	
+# check trigger-12 success: 	1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+ANALYZE  TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	note	The storage engine for the table doesn't support analyze
+CHECK    TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	check	note	The storage engine for the table doesn't support check
+CHECKSUM TABLE t1 EXTENDED;
+Table	Checksum
+test.t1	<some_value>
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	The storage engine for the table doesn't support optimize
+# check layout success:    1
+REPAIR   TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	note	The storage engine for the table doesn't support repair
+# check layout success:    1
+TRUNCATE t1;
+	
+# check TRUNCATE success: 	1
+# check layout success:    1
+# End usability test (inc/partition_check.inc)
+DROP TABLE t1;
 #------------------------------------------------------------------------
 # 6 Session default engine differs from engine used within create table
 #------------------------------------------------------------------------
diff -Nrup a/mysql-test/suite/parts/r/partition_engine_myisam.result
b/mysql-test/suite/parts/r/partition_engine_myisam.result
--- a/mysql-test/suite/parts/r/partition_engine_myisam.result	2008-02-24 19:13:17 +01:00
+++ b/mysql-test/suite/parts/r/partition_engine_myisam.result	2008-02-25 21:18:42 +01:00
@@ -1480,8 +1480,6 @@ PARTITION part2 VALUES LESS THAN (214748
 SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
 );
 ERROR HY000: The mix of handlers in the partitions is not allowed in this version of
MySQL
-# this should fail with ER_MIX_HANDLER_ERROR
-# after fixing Bug#33722
 CREATE TABLE t1 (
 f_int1 INTEGER,
 f_int2 INTEGER,
@@ -1498,6 +1496,24 @@ PARTITION part2 VALUES LESS THAN (214748
 (SUBPARTITION subpart21,
 SUBPARTITION subpart22 )
 );
+ERROR HY000: The mix of handlers in the partitions is not allowed in this version of
MySQL
+CREATE TABLE t1 (
+f_int1 INTEGER,
+f_int2 INTEGER,
+f_char1 CHAR(20),
+f_char2 CHAR(20),
+f_charbig VARCHAR(1000)
+)
+ENGINE = 'MyISAM'
+PARTITION BY RANGE(f_int1)
+SUBPARTITION BY HASH(f_int1)
+( PARTITION part1 VALUES LESS THAN (10)
+(SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
+SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
+PARTITION part2 VALUES LESS THAN (2147483646)
+(SUBPARTITION subpart21,
+SUBPARTITION subpart22 )
+);
 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
 # Start usability test (inc/partition_check.inc)
@@ -1953,8 +1969,6 @@ DROP TABLE t1;
 # 4 Storage engine assignment after partition name + after name of
 #   subpartitions belonging to another partition
 #------------------------------------------------------------------------
-# this should work
-# after fixing Bug#33722
 CREATE TABLE t1 (
 f_int1 INTEGER,
 f_int2 INTEGER,
@@ -1964,7 +1978,7 @@ f_charbig VARCHAR(1000)
 )
 PARTITION BY RANGE(f_int1)
 SUBPARTITION BY HASH(f_int1)
-( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'MyISAM'
+( PARTITION part1 VALUES LESS THAN (10)
 (SUBPARTITION subpart11,
 SUBPARTITION subpart12),
 PARTITION part2 VALUES LESS THAN (2147483646)
@@ -1972,8 +1986,491 @@ PARTITION part2 VALUES LESS THAN (214748
 SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
 );
 ERROR HY000: The mix of handlers in the partitions is not allowed in this version of
MySQL
-# this should work
-# after fixing Bug#33722
+CREATE TABLE t1 (
+f_int1 INTEGER,
+f_int2 INTEGER,
+f_char1 CHAR(20),
+f_char2 CHAR(20),
+f_charbig VARCHAR(1000)
+)
+ENGINE = 'MyISAM'
+PARTITION BY RANGE(f_int1)
+SUBPARTITION BY HASH(f_int1)
+( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'MyISAM'
+(SUBPARTITION subpart11,
+SUBPARTITION subpart12),
+PARTITION part2 VALUES LESS THAN (2147483646)
+(SUBPARTITION subpart21,
+SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
+);
+DROP TABLE t1;
+CREATE TABLE t1 (
+f_int1 INTEGER,
+f_int2 INTEGER,
+f_char1 CHAR(20),
+f_char2 CHAR(20),
+f_charbig VARCHAR(1000)
+)
+PARTITION BY RANGE(f_int1)
+SUBPARTITION BY HASH(f_int1)
+( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'MyISAM'
+(SUBPARTITION subpart11,
+SUBPARTITION subpart12),
+PARTITION part2 VALUES LESS THAN (2147483646)
+(SUBPARTITION subpart21 STORAGE ENGINE = 'MyISAM',
+SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
+);
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
+# Start usability test (inc/partition_check.inc)
+create_command
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f_int1` int(11) DEFAULT NULL,
+  `f_int2` int(11) DEFAULT NULL,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION
BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE =
MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN
(2147483646) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE =
MyISAM)) */
+
+unified filelist
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYD
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYI
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYD
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYI
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYD
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYI
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYD
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYI
+$MYSQLTEST_VARDIR/master-data/test/t1.frm
+$MYSQLTEST_VARDIR/master-data/test/t1.par
+
+# check prerequisites-1 success:    1
+# check COUNT(*) success:    1
+# check MIN/MAX(f_int1) success:    1
+# check MIN/MAX(f_int2) success:    1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
+WHERE f_int1 IN (2,3);
+# check prerequisites-3 success:    1
+DELETE FROM t1 WHERE f_charbig = 'delete me';
+# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
+# check read via f_int1 success: 1
+# check read via f_int2 success: 1
+	
+# check multiple-1 success: 	1
+DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
+	
+# check multiple-2 success: 	1
+INSERT INTO t1 SELECT * FROM t0_template
+WHERE MOD(f_int1,3) = 0;
+	
+# check multiple-3 success: 	1
+UPDATE t1 SET f_int1 = f_int1 + @max_row
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
+AND @max_row_div2 + @max_row_div4;
+	
+# check multiple-4 success: 	1
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
+AND @max_row_div2 + @max_row_div4 + @max_row;
+	
+# check multiple-5 success: 	1
+SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+	
+# check single-1 success: 	1
+SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+	
+# check single-2 success: 	1
+SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
+SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value2
+WHERE  f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
+	
+# check single-3 success: 	1
+SET @cur_value1= -1;
+SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value1
+WHERE  f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
+	
+# check single-4 success: 	1
+SELECT MAX(f_int1) INTO @cur_value FROM t1;
+DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
+	
+# check single-5 success: 	1
+DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
+	
+# check single-6 success: 	1
+INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig =
'#2147483647##';
+ERROR HY000: Table has no partition for value 2147483647
+DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
+INSERT t1 SET f_int1 = 0 , f_int2 = 0,
+f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
+f_charbig = '#NULL#';
+INSERT INTO t1
+SET f_int1 = NULL , f_int2 = -@max_row,
+f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
+f_charbig = '#NULL#';
+# check null success:    1
+	
+# check null-1 success: 	1
+UPDATE t1 SET f_int1 = -@max_row
+WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-2 success: 	1
+UPDATE t1 SET f_int1 = NULL
+WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-3 success: 	1
+DELETE FROM t1
+WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-4 success: 	1
+DELETE FROM t1
+WHERE f_int1 = 0 AND f_int2 = 0
+AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
+AND f_charbig = '#NULL#';
+SET AUTOCOMMIT= 0;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+	
+# check transactions-1 success: 	1
+COMMIT WORK;
+	
+# check transactions-2 success: 	1
+ROLLBACK WORK;
+	
+# check transactions-3 success: 	1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+ROLLBACK WORK;
+	
+# check transactions-4 success: 	1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+	
+# check transactions-5 success: 	1
+ROLLBACK WORK;
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+	
+# check transactions-6 success: 	1
+# INFO: Storage engine used for t1 seems to be not transactional.
+COMMIT;
+	
+# check transactions-7 success: 	1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+SET @@session.sql_mode = 'traditional';
+SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
+'', '', 'was inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+ERROR 22012: Division by 0
+COMMIT;
+	
+# check transactions-8 success: 	1
+# INFO: Storage engine used for t1 seems to be unable to revert
+#       changes made by the failing statement.
+SET @@session.sql_mode = '';
+SET AUTOCOMMIT= 1;
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+UPDATE t1 SET f_charbig = REPEAT('b', 1000);
+	
+# check special-1 success: 	1
+UPDATE t1 SET f_charbig = '';
+	
+# check special-2 success: 	1
+UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+	
+# check trigger-1 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+	
+# check trigger-2 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-3 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-4 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-5 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-6 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-7 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-8 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = old.f_int1 + @max_row,
+new.f_int2 = old.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+	
+# check trigger-9 success: 	1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = new.f_int1 + @max_row,
+new.f_int2 = new.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+	
+# check trigger-10 success: 	1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+	
+# check trigger-11 success: 	1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_char1, f_char2, f_charbig)
+SELECT CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+	
+# check trigger-12 success: 	1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+ANALYZE  TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	note	The storage engine for the table doesn't support analyze
+CHECK    TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	check	note	The storage engine for the table doesn't support check
+CHECKSUM TABLE t1 EXTENDED;
+Table	Checksum
+test.t1	<some_value>
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	The storage engine for the table doesn't support optimize
+# check layout success:    1
+REPAIR   TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	note	The storage engine for the table doesn't support repair
+# check layout success:    1
+TRUNCATE t1;
+	
+# check TRUNCATE success: 	1
+# check layout success:    1
+# End usability test (inc/partition_check.inc)
+DROP TABLE t1;
 CREATE TABLE t1 (
 f_int1 INTEGER,
 f_int2 INTEGER,
@@ -1987,10 +2484,460 @@ SUBPARTITION BY HASH(f_int1)
 (SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
 SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
 PARTITION part2 VALUES LESS THAN (2147483646) ENGINE = 'MyISAM'
-(SUBPARTITION subpart21,
+(SUBPARTITION subpart21 ENGINE = 'MyISAM',
 SUBPARTITION subpart22)
 );
-ERROR HY000: The mix of handlers in the partitions is not allowed in this version of
MySQL
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
+# Start usability test (inc/partition_check.inc)
+create_command
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f_int1` int(11) DEFAULT NULL,
+  `f_int2` int(11) DEFAULT NULL,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION
BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE =
MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN
(2147483646) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE =
MyISAM)) */
+
+unified filelist
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYD
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYI
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYD
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYI
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYD
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYI
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYD
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYI
+$MYSQLTEST_VARDIR/master-data/test/t1.frm
+$MYSQLTEST_VARDIR/master-data/test/t1.par
+
+# check prerequisites-1 success:    1
+# check COUNT(*) success:    1
+# check MIN/MAX(f_int1) success:    1
+# check MIN/MAX(f_int2) success:    1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
+WHERE f_int1 IN (2,3);
+# check prerequisites-3 success:    1
+DELETE FROM t1 WHERE f_charbig = 'delete me';
+# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
+# check read via f_int1 success: 1
+# check read via f_int2 success: 1
+	
+# check multiple-1 success: 	1
+DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
+	
+# check multiple-2 success: 	1
+INSERT INTO t1 SELECT * FROM t0_template
+WHERE MOD(f_int1,3) = 0;
+	
+# check multiple-3 success: 	1
+UPDATE t1 SET f_int1 = f_int1 + @max_row
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
+AND @max_row_div2 + @max_row_div4;
+	
+# check multiple-4 success: 	1
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
+AND @max_row_div2 + @max_row_div4 + @max_row;
+	
+# check multiple-5 success: 	1
+SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+	
+# check single-1 success: 	1
+SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+	
+# check single-2 success: 	1
+SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
+SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value2
+WHERE  f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
+	
+# check single-3 success: 	1
+SET @cur_value1= -1;
+SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value1
+WHERE  f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
+	
+# check single-4 success: 	1
+SELECT MAX(f_int1) INTO @cur_value FROM t1;
+DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
+	
+# check single-5 success: 	1
+DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
+	
+# check single-6 success: 	1
+INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig =
'#2147483647##';
+ERROR HY000: Table has no partition for value 2147483647
+DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
+INSERT t1 SET f_int1 = 0 , f_int2 = 0,
+f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
+f_charbig = '#NULL#';
+INSERT INTO t1
+SET f_int1 = NULL , f_int2 = -@max_row,
+f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
+f_charbig = '#NULL#';
+# check null success:    1
+	
+# check null-1 success: 	1
+UPDATE t1 SET f_int1 = -@max_row
+WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-2 success: 	1
+UPDATE t1 SET f_int1 = NULL
+WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-3 success: 	1
+DELETE FROM t1
+WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-4 success: 	1
+DELETE FROM t1
+WHERE f_int1 = 0 AND f_int2 = 0
+AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
+AND f_charbig = '#NULL#';
+SET AUTOCOMMIT= 0;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+	
+# check transactions-1 success: 	1
+COMMIT WORK;
+	
+# check transactions-2 success: 	1
+ROLLBACK WORK;
+	
+# check transactions-3 success: 	1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+ROLLBACK WORK;
+	
+# check transactions-4 success: 	1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+	
+# check transactions-5 success: 	1
+ROLLBACK WORK;
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+	
+# check transactions-6 success: 	1
+# INFO: Storage engine used for t1 seems to be not transactional.
+COMMIT;
+	
+# check transactions-7 success: 	1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+SET @@session.sql_mode = 'traditional';
+SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
+'', '', 'was inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+ERROR 22012: Division by 0
+COMMIT;
+	
+# check transactions-8 success: 	1
+# INFO: Storage engine used for t1 seems to be unable to revert
+#       changes made by the failing statement.
+SET @@session.sql_mode = '';
+SET AUTOCOMMIT= 1;
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+UPDATE t1 SET f_charbig = REPEAT('b', 1000);
+	
+# check special-1 success: 	1
+UPDATE t1 SET f_charbig = '';
+	
+# check special-2 success: 	1
+UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+	
+# check trigger-1 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+	
+# check trigger-2 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-3 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-4 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-5 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-6 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-7 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-8 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = old.f_int1 + @max_row,
+new.f_int2 = old.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+	
+# check trigger-9 success: 	1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = new.f_int1 + @max_row,
+new.f_int2 = new.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+	
+# check trigger-10 success: 	1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+	
+# check trigger-11 success: 	1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_char1, f_char2, f_charbig)
+SELECT CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+	
+# check trigger-12 success: 	1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+ANALYZE  TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	note	The storage engine for the table doesn't support analyze
+CHECK    TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	check	note	The storage engine for the table doesn't support check
+CHECKSUM TABLE t1 EXTENDED;
+Table	Checksum
+test.t1	<some_value>
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	The storage engine for the table doesn't support optimize
+# check layout success:    1
+REPAIR   TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	note	The storage engine for the table doesn't support repair
+# check layout success:    1
+TRUNCATE t1;
+	
+# check TRUNCATE success: 	1
+# check layout success:    1
+# End usability test (inc/partition_check.inc)
+DROP TABLE t1;
 #------------------------------------------------------------------------
 # 5 Precedence of storage engine assignments (if there is any)
 #------------------------------------------------------------------------
@@ -2925,8 +3872,7 @@ TRUNCATE t1;
 DROP TABLE t1;
 # 6.2 Storage engine assignment after partition name + after
 #     subpartition name
-# this should work
-# after fixing Bug#33722
+#     in partition part + in sub partition part
 CREATE TABLE t1 (
 f_int1 INTEGER,
 f_int2 INTEGER,
@@ -2943,7 +3889,457 @@ PARTITION part2 VALUES LESS THAN (214748
 (SUBPARTITION subpart21 STORAGE ENGINE = 'MyISAM',
 SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
 );
-ERROR HY000: The mix of handlers in the partitions is not allowed in this version of
MySQL
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
+# Start usability test (inc/partition_check.inc)
+create_command
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f_int1` int(11) DEFAULT NULL,
+  `f_int2` int(11) DEFAULT NULL,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION
BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE =
MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN
(2147483646) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE =
MyISAM)) */
+
+unified filelist
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYD
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYI
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYD
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYI
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYD
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYI
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYD
+$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYI
+$MYSQLTEST_VARDIR/master-data/test/t1.frm
+$MYSQLTEST_VARDIR/master-data/test/t1.par
+
+# check prerequisites-1 success:    1
+# check COUNT(*) success:    1
+# check MIN/MAX(f_int1) success:    1
+# check MIN/MAX(f_int2) success:    1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
+WHERE f_int1 IN (2,3);
+# check prerequisites-3 success:    1
+DELETE FROM t1 WHERE f_charbig = 'delete me';
+# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
+# check read via f_int1 success: 1
+# check read via f_int2 success: 1
+	
+# check multiple-1 success: 	1
+DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
+	
+# check multiple-2 success: 	1
+INSERT INTO t1 SELECT * FROM t0_template
+WHERE MOD(f_int1,3) = 0;
+	
+# check multiple-3 success: 	1
+UPDATE t1 SET f_int1 = f_int1 + @max_row
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
+AND @max_row_div2 + @max_row_div4;
+	
+# check multiple-4 success: 	1
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
+AND @max_row_div2 + @max_row_div4 + @max_row;
+	
+# check multiple-5 success: 	1
+SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+	
+# check single-1 success: 	1
+SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+	
+# check single-2 success: 	1
+SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
+SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value2
+WHERE  f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
+	
+# check single-3 success: 	1
+SET @cur_value1= -1;
+SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value1
+WHERE  f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
+	
+# check single-4 success: 	1
+SELECT MAX(f_int1) INTO @cur_value FROM t1;
+DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
+	
+# check single-5 success: 	1
+DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
+	
+# check single-6 success: 	1
+INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig =
'#2147483647##';
+ERROR HY000: Table has no partition for value 2147483647
+DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
+INSERT t1 SET f_int1 = 0 , f_int2 = 0,
+f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
+f_charbig = '#NULL#';
+INSERT INTO t1
+SET f_int1 = NULL , f_int2 = -@max_row,
+f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
+f_charbig = '#NULL#';
+# check null success:    1
+	
+# check null-1 success: 	1
+UPDATE t1 SET f_int1 = -@max_row
+WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-2 success: 	1
+UPDATE t1 SET f_int1 = NULL
+WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-3 success: 	1
+DELETE FROM t1
+WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+	
+# check null-4 success: 	1
+DELETE FROM t1
+WHERE f_int1 = 0 AND f_int2 = 0
+AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
+AND f_charbig = '#NULL#';
+SET AUTOCOMMIT= 0;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+	
+# check transactions-1 success: 	1
+COMMIT WORK;
+	
+# check transactions-2 success: 	1
+ROLLBACK WORK;
+	
+# check transactions-3 success: 	1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+ROLLBACK WORK;
+	
+# check transactions-4 success: 	1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+	
+# check transactions-5 success: 	1
+ROLLBACK WORK;
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+	
+# check transactions-6 success: 	1
+# INFO: Storage engine used for t1 seems to be not transactional.
+COMMIT;
+	
+# check transactions-7 success: 	1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+SET @@session.sql_mode = 'traditional';
+SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
+'', '', 'was inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+ERROR 22012: Division by 0
+COMMIT;
+	
+# check transactions-8 success: 	1
+# INFO: Storage engine used for t1 seems to be unable to revert
+#       changes made by the failing statement.
+SET @@session.sql_mode = '';
+SET AUTOCOMMIT= 1;
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+UPDATE t1 SET f_charbig = REPEAT('b', 1000);
+	
+# check special-1 success: 	1
+UPDATE t1 SET f_charbig = '';
+	
+# check special-2 success: 	1
+UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+	
+# check trigger-1 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+	
+# check trigger-2 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-3 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-4 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-5 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-6 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-7 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+      WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+	
+# check trigger-8 success: 	1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = old.f_int1 + @max_row,
+new.f_int2 = old.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+	
+# check trigger-9 success: 	1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = new.f_int1 + @max_row,
+new.f_int2 = new.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+	
+# check trigger-10 success: 	1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+	
+# check trigger-11 success: 	1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_char1, f_char2, f_charbig)
+SELECT CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+	
+# check trigger-12 success: 	1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+ANALYZE  TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	note	The storage engine for the table doesn't support analyze
+CHECK    TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	check	note	The storage engine for the table doesn't support check
+CHECKSUM TABLE t1 EXTENDED;
+Table	Checksum
+test.t1	<some_value>
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	The storage engine for the table doesn't support optimize
+# check layout success:    1
+REPAIR   TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	note	The storage engine for the table doesn't support repair
+# check layout success:    1
+TRUNCATE t1;
+	
+# check TRUNCATE success: 	1
+# check layout success:    1
+# End usability test (inc/partition_check.inc)
+DROP TABLE t1;
 #------------------------------------------------------------------------
 # 6 Session default engine differs from engine used within create table
 #------------------------------------------------------------------------
diff -Nrup a/mysql-test/t/partition.test b/mysql-test/t/partition.test
--- a/mysql-test/t/partition.test	2008-02-24 17:25:44 +01:00
+++ b/mysql-test/t/partition.test	2008-02-25 21:18:43 +01:00
@@ -1215,13 +1215,15 @@ DROP TABLE t1;
 #
 # Bug 17455 Partitions: Wrong message and error when using Repair/Optimize
 #                       table on partitioned table
-#
+# (added check/analyze for gcov of Bug#20129)
 create table t1 (a int)
 engine=MEMORY
 partition by key (a);
 
 REPAIR TABLE t1;
 OPTIMIZE TABLE t1;
+CHECK TABLE t1;
+ANALYZE TABLE t1;
 
 drop table t1;
 
@@ -1524,7 +1526,7 @@ select c1 from t1 group by (select c0 fr
 drop table t1;
 
 # Bug #30495: optimize table t1,t2,t3 extended errors
-#
+# (added more maintenace commands for Bug#20129
 CREATE TABLE t1(a int)
 PARTITION BY RANGE (a) (
   PARTITION p1 VALUES LESS THAN (10),
@@ -1534,6 +1536,14 @@ PARTITION BY RANGE (a) (
 ALTER TABLE t1 OPTIMIZE PARTITION p1 EXTENDED;
 --error ER_PARSE_ERROR
 ALTER TABLE t1 ANALYZE PARTITION p1 EXTENDED;
+--error ER_CHECK_NOT_IMPLEMENTED
+ALTER TABLE t1 ANALYZE PARTITION p1;
+--error ER_CHECK_NOT_IMPLEMENTED
+ALTER TABLE t1 CHECK PARTITION p1;
+--error ER_CHECK_NOT_IMPLEMENTED
+ALTER TABLE t1 REPAIR PARTITION p1;
+--error ER_CHECK_NOT_IMPLEMENTED
+ALTER TABLE t1 OPTIMIZE PARTITION p1;
 DROP TABLE t1;
 
 #
diff -Nrup a/mysql-test/t/partition_datatype.test b/mysql-test/t/partition_datatype.test
--- a/mysql-test/t/partition_datatype.test	2008-02-07 15:54:53 +01:00
+++ b/mysql-test/t/partition_datatype.test	2008-02-25 21:18:43 +01:00
@@ -103,7 +103,10 @@ drop table t1;
 -- echo # test with null allowed
 create table t1 (a bit) partition by key (a);
 insert into t1 values (b'1');
+insert into t1 values (NULL);
 select hex(a) from t1 where a = b'1';
+select hex(a) from t1 where a is NULL;
+select hex(a) from t1 order by a;
 drop table t1;
 create table t1 (a tinyint) partition by key (a);
 insert into t1 values (2);
diff -Nrup a/mysql-test/t/partition_error.test b/mysql-test/t/partition_error.test
--- a/mysql-test/t/partition_error.test	2007-11-10 13:09:16 +01:00
+++ b/mysql-test/t/partition_error.test	2008-02-25 21:18:44 +01:00
@@ -9,6 +9,24 @@ drop table if exists t1;
 --enable_warnings
 
 #
+# Bug#31931: Mix of handlers error message
+#
+--error ER_MIX_HANDLER_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (a)
+( PARTITION p0 ENGINE=MyISAM,
+  PARTITION p1);
+--error ER_MIX_HANDLER_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY LIST (a)
+SUBPARTITION BY HASH (a)
+( PARTITION p0 VALUES IN (0)
+( SUBPARTITION s0, SUBPARTITION s1 ENGINE=MyISAM, SUBPARTITION s2),
+  PARTITION p1 VALUES IN (1)
+( SUBPARTITION s3 ENGINE=MyISAM, SUBPARTITION s4, SUBPARTITION s5 ENGINE=MyISAM));
+
+
+#
 # Bug 29368:
 # Incorrect error, 1467, for syntax error when creating partition
 --error ER_PARTITION_REQUIRES_VALUES_ERROR
diff -Nrup a/sql/partition_info.cc b/sql/partition_info.cc
--- a/sql/partition_info.cc	2008-02-24 16:46:34 +01:00
+++ b/sql/partition_info.cc	2008-02-25 21:18:44 +01:00
@@ -464,15 +464,15 @@ static bool check_engine_condition(parti
 {
   DBUG_ENTER("check_engine_condition");
 
-  DBUG_PRINT("enter", ("p_eng %u t_eng %u t_eng_set %u first %u state %u",
-                       ha_legacy_type(p_elem->engine_type),
-                       ha_legacy_type(*engine_type),
+  DBUG_PRINT("enter", ("p_eng %s t_eng %s t_eng_set %u first %u state %u",
+                       ha_resolve_storage_engine_name(p_elem->engine_type),
+                       ha_resolve_storage_engine_name(*engine_type),
                        table_engine_set, *first, p_elem->part_state));
   if (*first && !table_engine_set)
   {
     *engine_type= p_elem->engine_type;
-    DBUG_PRINT("info", ("setting table_engine = %u",
-                         ha_legacy_type(*engine_type)));
+    DBUG_PRINT("info", ("setting table_engine = %s",
+                         ha_resolve_storage_engine_name(*engine_type)));
   }
   *first= FALSE;
   if ((table_engine_set &&
@@ -522,8 +522,8 @@ bool partition_info::check_engine_mix(ha
   bool first= TRUE;
   uint no_parts= partitions.elements;
   DBUG_ENTER("partition_info::check_engine_mix");
-  DBUG_PRINT("info", ("in: engine_type = %u, table_engine_set = %u",
-                       ha_legacy_type(engine_type),
+  DBUG_PRINT("info", ("in: engine_type = %s, table_engine_set = %u",
+                       ha_resolve_storage_engine_name(engine_type),
                        table_engine_set));
   if (no_parts)
   {
@@ -532,8 +532,8 @@ bool partition_info::check_engine_mix(ha
     do
     {
       partition_element *part_elem= part_it++;
-      DBUG_PRINT("info", ("part = %d engine = %d table_engine_set %u",
-                 i, ha_legacy_type(part_elem->engine_type),
+      DBUG_PRINT("info", ("part = %d engine = %s table_engine_set %u",
+                 i, ha_resolve_storage_engine_name(part_elem->engine_type),
                  table_engine_set));
       if (is_sub_partitioned() &&
           part_elem->subpartitions.elements)
@@ -544,8 +544,8 @@ bool partition_info::check_engine_mix(ha
         do
         {
           partition_element *sub_elem= sub_it++;
-          DBUG_PRINT("info", ("sub = %d engine = %u table_engie_set %u",
-                     j, ha_legacy_type(sub_elem->engine_type),
+          DBUG_PRINT("info", ("sub = %d engine = %s table_engie_set %u",
+                     j, ha_resolve_storage_engine_name(sub_elem->engine_type),
                      table_engine_set));
           if (check_engine_condition(sub_elem, table_engine_set,
                                      &engine_type, &first))
@@ -561,8 +561,8 @@ bool partition_info::check_engine_mix(ha
         goto error;
     } while (++i < no_parts);
   }
-  DBUG_PRINT("info", ("engine_type = %u",
-                       ha_legacy_type(engine_type)));
+  DBUG_PRINT("info", ("engine_type = %s",
+                       ha_resolve_storage_engine_name(engine_type)));
   if (!engine_type)
     engine_type= old_engine_type;
   if (engine_type->flags & HTON_NO_PARTITION)
@@ -570,8 +570,8 @@ bool partition_info::check_engine_mix(ha
     my_error(ER_PARTITION_MERGE_ERROR, MYF(0));
     DBUG_RETURN(TRUE);
   }
-  DBUG_PRINT("info", ("out: engine_type = %u",
-                       ha_legacy_type(engine_type)));
+  DBUG_PRINT("info", ("out: engine_type = %s",
+                       ha_resolve_storage_engine_name(engine_type)));
   DBUG_ASSERT(engine_type != partition_hton);
   DBUG_RETURN(FALSE);
 error:
@@ -859,6 +859,8 @@ bool partition_info::check_partition_inf
   DBUG_ENTER("partition_info::check_partition_info");
   DBUG_ASSERT(default_engine_type != partition_hton);
 
+  DBUG_PRINT("info", ("default table_engine = %s",
+                      ha_resolve_storage_engine_name(table_engine)));
   if (check_partition_function)
   {
     int err= 0;
@@ -913,10 +915,16 @@ bool partition_info::check_partition_inf
       the table and all partitions/subpartitions are set.
       So when ALTER it is already set on table level
   */
-  if (thd->lex->create_info.used_fields & HA_CREATE_USED_ENGINE)
+  if (info && info->used_fields & HA_CREATE_USED_ENGINE)
   {
     table_engine_set= TRUE;
-    table_engine= thd->lex->create_info.db_type;
+    table_engine= info->db_type;
+    /* if partition_hton, use thd->lex->create_info */
+    if (table_engine == partition_hton)
+      table_engine= thd->lex->create_info.db_type;
+    DBUG_ASSERT(table_engine != partition_hton);
+    DBUG_PRINT("info", ("Using table_engine = %s",
+                        ha_resolve_storage_engine_name(table_engine)));
   }
   else
   {
@@ -924,6 +932,8 @@ bool partition_info::check_partition_inf
     if (thd->lex->sql_command != SQLCOM_CREATE_TABLE)
     {
       table_engine_set= TRUE;
+      DBUG_PRINT("info", ("No create, table_engine = %s",
+                          ha_resolve_storage_engine_name(table_engine)));
       DBUG_ASSERT(table_engine && table_engine != partition_hton);
     }
   }
@@ -941,11 +951,6 @@ bool partition_info::check_partition_inf
     do
     {
       partition_element *part_elem= part_it++;
-      if (part_elem->engine_type == NULL)
-      {
-        no_parts_not_set++;
-        part_elem->engine_type= default_engine_type;
-      }
 #ifdef HAVE_READLINK
       if (!my_use_symdir || (thd->variables.sql_mode & MODE_NO_DIR_IN_CREATE))
 #endif
@@ -960,23 +965,29 @@ bool partition_info::check_partition_inf
       }
       if (!is_sub_partitioned())
       {
+        if (part_elem->engine_type == NULL)
+        {
+          no_parts_not_set++;
+          part_elem->engine_type= default_engine_type;
+        }
         if (check_table_name(part_elem->partition_name,
                              strlen(part_elem->partition_name)))
         {
           my_error(ER_WRONG_PARTITION_NAME, MYF(0));
           goto end;
         }
-        DBUG_PRINT("info", ("part = %d engine = %d",
-                   i, ha_legacy_type(part_elem->engine_type)));
+        DBUG_PRINT("info", ("part = %d engine = %s",
+                   i, ha_resolve_storage_engine_name(part_elem->engine_type)));
       }
       else
       {
         uint j= 0;
         uint no_subparts_not_set= 0;
         List_iterator<partition_element> sub_it(part_elem->subpartitions);
+        partition_element *sub_elem;
         do
         {
-          partition_element *sub_elem= sub_it++;
+          sub_elem= sub_it++;
           if (check_table_name(sub_elem->partition_name,
                                strlen(sub_elem->partition_name)))
           {
@@ -985,24 +996,41 @@ bool partition_info::check_partition_inf
           }
           if (sub_elem->engine_type == NULL)
           {
-            sub_elem->engine_type= default_engine_type;
-            no_subparts_not_set++;
+            if (part_elem->engine_type != NULL)
+              sub_elem->engine_type= part_elem->engine_type;
+            else
+            {
+              sub_elem->engine_type= default_engine_type;
+              no_subparts_not_set++;
+            }
           }
-          DBUG_PRINT("info", ("part = %d sub = %d engine = %u",
-                     i, j, ha_legacy_type(sub_elem->engine_type)));
+          DBUG_PRINT("info", ("part = %d sub = %d engine = %s", i, j,
+                     ha_resolve_storage_engine_name(sub_elem->engine_type)));
         } while (++j < no_subparts);
-        if (prev_no_subparts_not_set == (no_subparts + 1))
+
+        if (prev_no_subparts_not_set == (no_subparts + 1) &&
+            (no_subparts_not_set == 0 || no_subparts_not_set == no_subparts))
           prev_no_subparts_not_set= no_subparts_not_set;
+
         if (!table_engine_set &&
-            prev_no_subparts_not_set == no_subparts_not_set &&
-            no_subparts_not_set != 0 &&
-            no_subparts_not_set != no_subparts)
+            prev_no_subparts_not_set != no_subparts_not_set)
         {
           DBUG_PRINT("info", ("no_subparts_not_set = %u no_subparts = %u",
                      no_subparts_not_set, no_subparts));
           my_error(ER_MIX_HANDLER_ERROR, MYF(0));
           goto end;
         }
+
+        if (part_elem->engine_type == NULL)
+        {
+          if (no_subparts_not_set == 0)
+            part_elem->engine_type= sub_elem->engine_type;
+          else
+          {
+            no_parts_not_set++;
+            part_elem->engine_type= default_engine_type;
+          }
+        }
       }
     } while (++i < no_parts);
     if (!table_engine_set &&
@@ -1021,9 +1049,8 @@ bool partition_info::check_partition_inf
     goto end;
   }
 
-  if (table_engine == partition_hton)
-    DBUG_PRINT("info", ("Table engine set to partition_hton"));
-  DBUG_ASSERT(default_engine_type == table_engine);
+  DBUG_ASSERT(table_engine != partition_hton &&
+              default_engine_type == table_engine);
   if (eng_type)
     *eng_type= table_engine;
 
diff -Nrup a/sql/sql_partition.cc b/sql/sql_partition.cc
--- a/sql/sql_partition.cc	2008-02-24 17:25:44 +01:00
+++ b/sql/sql_partition.cc	2008-02-25 21:18:45 +01:00
@@ -3819,9 +3819,9 @@ bool mysql_unpack_partition(THD *thd,
 
   DBUG_PRINT("info", ("Successful parse"));
   part_info= lex.part_info;
-  DBUG_PRINT("info", ("default engine = %d, default_db_type = %d",
-             ha_legacy_type(part_info->default_engine_type),
-             ha_legacy_type(default_db_type)));
+  DBUG_PRINT("info", ("default engine = %s, default_db_type = %s",
+             ha_resolve_storage_engine_name(part_info->default_engine_type),
+             ha_resolve_storage_engine_name(default_db_type)));
   if (is_create_table_ind && old_lex->sql_command == SQLCOM_CREATE_TABLE)
   {
     if (old_lex->create_info.options & HA_LEX_CREATE_TABLE_LIKE)
@@ -4041,8 +4041,8 @@ static bool check_native_partitioned(HA_
       DBUG_ASSERT(engine_type && engine_type != partition_hton);
     }
   }
-  DBUG_PRINT("info", ("engine_type = %u, table_engine_set = %u",
-                       ha_legacy_type(engine_type),
+  DBUG_PRINT("info", ("engine_type = %s, table_engine_set = %u",
+                       ha_resolve_storage_engine_name(engine_type),
                        table_engine_set));
   if (part_info->check_engine_mix(engine_type, table_engine_set))
     goto error;
diff -Nrup a/sql/sql_table.cc b/sql/sql_table.cc
--- a/sql/sql_table.cc	2008-02-14 16:52:59 +01:00
+++ b/sql/sql_table.cc	2008-02-25 21:18:45 +01:00
@@ -3295,8 +3295,9 @@ bool mysql_create_table_no_lock(THD *thd
         }
       }
     }
-    DBUG_PRINT("info", ("db_type = %d",
-                         ha_legacy_type(part_info->default_engine_type)));
+    DBUG_PRINT("info", ("db_type = %s create_info->db_type = %s",
+             ha_resolve_storage_engine_name(part_info->default_engine_type),
+             ha_resolve_storage_engine_name(create_info->db_type)));
     if (part_info->check_partition_info(thd, &engine_type, file,
                                         create_info, TRUE))
       goto err;
@@ -3321,7 +3322,7 @@ bool mysql_create_table_no_lock(THD *thd
         Assign the partition handler as the handler of the table.
       */
       DBUG_PRINT("info", ("db_type: %d",
-                          ha_legacy_type(create_info->db_type)));
+                        ha_resolve_storage_engine_name(create_info->db_type)));
       delete file;
       create_info->db_type= partition_hton;
       if (!(file= get_ha_partition(part_info)))
Thread
• bk commit into 5.1 tree (mattiasj:1.2528) BUG#31931	mattiasj	25 Feb