diff options
| -rw-r--r-- | mysql-test/r/partition.result | 14 | ||||
| -rw-r--r-- | mysql-test/r/partition_datatype.result | 8 | ||||
| -rw-r--r-- | mysql-test/r/partition_error.result | 13 | ||||
| -rw-r--r-- | mysql-test/suite/parts/inc/partition_engine.inc | 78 | ||||
| -rw-r--r-- | mysql-test/suite/parts/r/partition_engine_innodb.result | 1378 | ||||
| -rw-r--r-- | mysql-test/suite/parts/r/partition_engine_myisam.result | 1420 | ||||
| -rw-r--r-- | mysql-test/t/partition.test | 14 | ||||
| -rw-r--r-- | mysql-test/t/partition_datatype.test | 3 | ||||
| -rw-r--r-- | mysql-test/t/partition_error.test | 18 | ||||
| -rw-r--r-- | sql/partition_info.cc | 99 | ||||
| -rw-r--r-- | sql/sql_partition.cc | 10 | ||||
| -rw-r--r-- | sql/sql_table.cc | 7 |
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))) |