--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

# The server must support partitioning.
--source include/have_partition.inc

# Storage engine to be tested
let $engine= 'InnoDB';
##### Switch to MyISAM -> the bug disappears
  let $engine= 'MyISAM';
--source include/have_innodb.inc
eval SET SESSION storage_engine=$engine;

# Prevent that a change of defaults breaks the tests.
SET AUTOCOMMIT= 1;
set @@session.sql_mode= '';

# PARTITION parte VALUES LESS THAN (20),
let $partition= PARTITION BY RANGE(f_int1)
(
PARTITION parta VALUES LESS THAN (0),
PARTITION partc VALUES LESS THAN (10),
PARTITION partf VALUES LESS THAN (2147483647));
##### Removing partitioning -> the bug disappears
# let $partition= ;

eval CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(10),
f_charbig VARCHAR(1000)
) $partition;
INSERT INTO t1 SET f_int1 = 20,f_int2 = 20,f_char1 = '20',f_charbig = '===20===';
INSERT INTO t1 SET f_int1 = 16,f_int2 = 16,f_char1 = '16',f_charbig = '===16===';
INSERT INTO t1 SET f_int1 = 12,f_int2 = 12,f_char1 = '12',f_charbig = '===12===';
INSERT INTO t1 SET f_int1 = 8, f_int2 = 8, f_char1 = '8', f_charbig = '===8===';
INSERT INTO t1 SET f_int1 = 4, f_int2 = 4, f_char1 = '4', f_charbig = '===4===';

SELECT * FROM t1 ORDER BY f_int1;

--echo # The BEFORE UPDATE trigger recalculates the column used in the
--echo # partitioning function.
delimiter |;
eval CREATE TRIGGER trg BEFORE UPDATE ON t1 FOR EACH ROW
BEGIN
   SET new.f_int1 = old.f_int1 + 20,
       new.f_int2 = old.f_int2 - 20,
       new.f_charbig = '####updated per update trigger####';
END|
delimiter ;|
SELECT * FROM t1 ORDER BY f_int1;
--echo # This UPDATE activates the trigger.
UPDATE t1
SET f_charbig = '####updated per update statement itself####';
SELECT * FROM t1 ORDER BY f_int1;
DROP TRIGGER trg;

# Revert the changes
eval UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
              f_int2 = CAST(f_char1 AS SIGNED INT),
              f_charbig = CONCAT('===',f_char1,'===');

--echo # Here are some records missing.
SELECT * FROM t1 ORDER BY f_int1;

--echo # Assume the PRIMARY KEY (implicit created in case of InnoDB) is rotten.
--echo # Just create a new one.
ALTER TABLE t1 ADD PRIMARY KEY (f_int1,f_int2);

--echo # Now the result set is OK.
SELECT * FROM t1 ORDER BY f_int1;
exit;