~drizzle-trunk/drizzle/development

SET @max_row = 20;

SET @@session.storage_engine = 'InnoDB';

#------------------------------------------------------------------------

#  0. Setting of auxiliary variables + Creation of an auxiliary tables

#     needed in many testcases

#------------------------------------------------------------------------

SELECT @max_row DIV 2 INTO @max_row_div2;

SELECT @max_row DIV 3 INTO @max_row_div3;

SELECT @max_row DIV 4 INTO @max_row_div4;

SET @max_int_4 = 2147483647;

DROP TABLE IF EXISTS t0_template;

CREATE TABLE t0_template (

f_int1 INTEGER,

f_int2 INTEGER,

f_char1 CHAR(20),

f_char2 CHAR(20),

f_charbig VARCHAR(1000) ,

PRIMARY KEY(f_int1))

ENGINE = MEMORY;

#     Logging of <max_row> INSERTs into t0_template suppressed

DROP TABLE IF EXISTS t0_definition;

CREATE TABLE t0_definition (

state CHAR(3),

create_command VARBINARY(5000),

file_list      VARBINARY(10000),

PRIMARY KEY (state)

) ENGINE = MEMORY;

DROP TABLE IF EXISTS t0_aux;

CREATE TABLE t0_aux ( f_int1 INTEGER,

f_int2 INTEGER,

f_char1 CHAR(20),

f_char2 CHAR(20),

f_charbig VARCHAR(1000) )

ENGINE = MEMORY;

SET AUTOCOMMIT= 1;

SET @@session.sql_mode= '';

# End of basic preparations needed for all tests

#-----------------------------------------------

#========================================================================

#  Check partitioning methods on just created tables

#  The tables should be defined without/with PRIMARY KEY and

#  UNIQUE INDEXes.

#  Every test round has to check

#        PARTITION BY HASH/KEY/LIST/RANGE

#        PARTITION BY RANGE/LIST ... SUBPARTITION BY HASH/KEY ...

#========================================================================

#------------------------------------------------------------------------

#  1   Tables without PRIMARY KEY or UNIQUE INDEXes

#------------------------------------------------------------------------

#  1.1 The partitioning function contains one column.

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (

f_int1 INTEGER,

f_int2 INTEGER,

f_char1 CHAR(20),

f_char2 CHAR(20),

f_charbig VARCHAR(1000)

)

PARTITION BY HASH(f_int1) PARTITIONS 2;

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 HASH (f_int1) PARTITIONS 2  */

unified filelist

$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##';

# check single-7 success: 	1

DELETE FROM t1 WHERE f_charbig = '#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,

f_char1 CHAR(20),

f_char2 CHAR(20),

f_charbig VARCHAR(1000)

)

PARTITION BY KEY(f_int1) PARTITIONS 5;

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 KEY (f_int1) PARTITIONS 5  */

unified filelist

$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##';

# check single-7 success: 	1

DELETE FROM t1 WHERE f_charbig = '#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,

f_char1 CHAR(20),

f_char2 CHAR(20),

f_charbig VARCHAR(1000)

)

PARTITION BY LIST(MOD(f_int1,4))

(PARTITION part_3 VALUES IN (-3),

PARTITION part_2 VALUES IN (-2),

PARTITION part_1 VALUES IN (-1),

PARTITION part_N VALUES IN (NULL),

PARTITION part0 VALUES IN (0),

PARTITION part1 VALUES IN (1),

PARTITION part2 VALUES IN (2),

PARTITION part3 VALUES IN (3));

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 LIST (MOD(f_int1,4)) (PARTITION part_3 VALUES IN (-3) ENGINE = InnoDB, PARTITION part_2 VALUES IN (-2) ENGINE = InnoDB, PARTITION part_1 VALUES IN (-1) ENGINE = InnoDB, PARTITION part_N VALUES IN (NULL) ENGINE = InnoDB, PARTITION part0 VALUES IN (0) ENGINE = InnoDB, PARTITION part1 VALUES IN (1) ENGINE = InnoDB, PARTITION part2 VALUES IN (2) ENGINE = InnoDB, PARTITION part3 VALUES IN (3) ENGINE = InnoDB) */

unified filelist

$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##';

# check single-7 success: 	1

DELETE FROM t1 WHERE f_charbig = '#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,