summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--mysql-test/r/partition.result14
-rw-r--r--mysql-test/r/partition_datatype.result8
-rw-r--r--mysql-test/r/partition_error.result13
-rw-r--r--mysql-test/suite/parts/inc/partition_engine.inc78
-rw-r--r--mysql-test/suite/parts/r/partition_engine_innodb.result1378
-rw-r--r--mysql-test/suite/parts/r/partition_engine_myisam.result1420
-rw-r--r--mysql-test/t/partition.test14
-rw-r--r--mysql-test/t/partition_datatype.test3
-rw-r--r--mysql-test/t/partition_error.test18
-rw-r--r--sql/partition_info.cc99
-rw-r--r--sql/sql_partition.cc10
-rw-r--r--sql/sql_table.cc7
12 files changed, 2968 insertions, 94 deletions
diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index 1cbe0d577b1..b13010f8471 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -1052,6 +1052,12 @@ test.t1 repair note The storage engine for the table doesn't support repair
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 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 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 --git a/mysql-test/r/partition_datatype.result b/mysql-test/r/partition_datatype.result
index 43c65f23e61..43d9aac13b7 100644
--- a/mysql-test/r/partition_datatype.result
+++ b/mysql-test/r/partition_datatype.result
@@ -135,9 +135,17 @@ 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);
insert into t1 values (2);
diff --git a/mysql-test/r/partition_error.result b/mysql-test/r/partition_error.result
index 46532cb32ab..585447ff333 100644
--- a/mysql-test/r/partition_error.result
+++ b/mysql-test/r/partition_error.result
@@ -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 --git a/mysql-test/suite/parts/inc/partition_engine.inc b/mysql-test/suite/parts/inc/partition_engine.inc
index d2f3277cf6d..b2fab2ff27e 100644
--- a/mysql-test/suite/parts/inc/partition_engine.inc
+++ b/mysql-test/suite/parts/inc/partition_engine.inc
@@ -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_charbig FROM t0_template;
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 --git a/mysql-test/suite/parts/r/partition_engine_innodb.result b/mysql-test/suite/parts/r/partition_engine_innodb.result
index fef4186d5b9..4aad4c3c36a 100644
--- a/mysql-test/suite/parts/r/partition_engine_innodb.result
+++ b/mysql-test/suite/parts/r/partition_engine_innodb.result
@@ -1446,8 +1446,6 @@ PARTITION part2 VALUES LESS THAN (2147483646)
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 (2147483646)
(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 (2147483646)
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 (2147483646)
(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 --git a/mysql-test/suite/parts/r/partition_engine_myisam.result b/mysql-test/suite/parts/r/partition_engine_myisam.result
index 6c228b05113..2dfea58995e 100644
--- a/mysql-test/suite/parts/r/partition_engine_myisam.result
+++ b/mysql-test/suite/parts/r/partition_engine_myisam.result
@@ -1480,8 +1480,6 @@ PARTITION part2 VALUES LESS THAN (2147483646)
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 (2147483646)
(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 (2147483646)
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 (2147483646)
(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 --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test
index 5adf21d53e8..c4e447988db 100644
--- a/mysql-test/t/partition.test
+++ b/mysql-test/t/partition.test
@@ -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 from t1 limit 1);
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 --git a/mysql-test/t/partition_datatype.test b/mysql-test/t/partition_datatype.test
index c21fb40f735..7440a9bf3a3 100644
--- a/mysql-test/t/partition_datatype.test
+++ b/mysql-test/t/partition_datatype.test
@@ -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 --git a/mysql-test/t/partition_error.test b/mysql-test/t/partition_error.test
index c9b95fc1664..d470fb67b49 100644
--- a/mysql-test/t/partition_error.test
+++ b/mysql-test/t/partition_error.test
@@ -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 --git a/sql/partition_info.cc b/sql/partition_info.cc
index 6816fce7818..1d21a10beaa 100644
--- a/sql/partition_info.cc
+++ b/sql/partition_info.cc
@@ -464,15 +464,15 @@ static bool check_engine_condition(partition_element *p_elem,
{
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(handlerton *engine_type,
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(handlerton *engine_type,
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(handlerton *engine_type,
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(handlerton *engine_type,
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(handlerton *engine_type,
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_info(THD *thd, handlerton **eng_type,
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_info(THD *thd, handlerton **eng_type,
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_info(THD *thd, handlerton **eng_type,
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_info(THD *thd, handlerton **eng_type,
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_info(THD *thd, handlerton **eng_type,
}
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_info(THD *thd, handlerton **eng_type,
}
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_info(THD *thd, handlerton **eng_type,
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 --git a/sql/sql_partition.cc b/sql/sql_partition.cc
index a072b7fa8e4..7eab04b073a 100644
--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -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_CREATE_INFO *create_info,bool *ret_val,
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 --git a/sql/sql_table.cc b/sql/sql_table.cc
index 553ce9b3f1a..3114bbc77b3 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -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)))