--disable_abort_on_error

SET AUTOCOMMIT= 1;
set @@session.sql_mode= '';

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

--source include/have_partition.inc

--echo
--echo # InnoDB seems to be needed, MyISAM is harmless
SET SESSION storage_engine='InnoDB';

# harmless PARTITION BY RANGE(f_int1 DIV 2)
CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER,
 f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000))
PARTITION BY RANGE(f_int1 DIV 2) SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2 
(PARTITION parta VALUES LESS THAN (0),
 PARTITION partb VALUES LESS THAN (5),
 PARTITION partc VALUES LESS THAN (10),
 PARTITION partd VALUES LESS THAN (2147483646));

INSERT INTO t1 SET f_int1 = 8, f_int2 = 8, f_char1 = '8', f_char2 = '8', f_charbig = '===8===';
INSERT INTO t1 SET f_int1 = 7, f_int2 = 7, f_char1 = '7', f_char2 = '7', f_charbig = '===7===';
INSERT INTO t1 SET f_int1 = 3, f_int2 = 3, f_char1 = '3', f_char2 = '3', f_charbig = '===3===';
INSERT INTO t1 SET f_int1 = 20, f_int2 = 20, f_char1 = '20', f_char2 = '20', f_charbig = '===20===';

--echo
--echo # The following DELETE + INSERT seems to be needed
DELETE FROM t1 WHERE f_int1 = 3;
INSERT INTO t1 SET f_int1 = 3, f_int2 = 3, f_char1 = '3', f_char2 = '3', f_charbig = '===3===';

--echo
--echo # We get here 4 rows.
SELECT * FROM t1 ORDER BY f_int1;
--echo # This next update seems to cause the problem in combination with the DELETE + INSERT above. 
UPDATE t1 SET f_int1 = f_int1 + 20 WHERE f_int1 BETWEEN 5 AND 9;
--echo # Surprising fact
--echo #             f_int1 BETWEEN 5 AND 8
--echo #             f_int1 BETWEEN 6 AND 9
--echo # hit the same rows, but are harmless
--echo
--echo # Where is the row "27    7       7       7       ===7===" ?
SELECT * FROM t1 ORDER BY f_int1;

--echo
--echo # Let's make some physical reorganization of the table and try the SELECT again
ALTER TABLE t1 ADD PRIMARY KEY (f_int1);
--echo # Now the result set looks good.
SELECT * FROM t1 ORDER BY f_int1;