DROP TABLE IF EXISTS t1;
# test without partitioning for reference
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1))
ENGINE='Blackhole';
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
AUTO_INCREMENT
1
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (NULL);
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
AUTO_INCREMENT
1
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
AUTO_INCREMENT
1
INSERT INTO t1 VALUES (0);
INSERT INTO t1 VALUES (5), (16);
# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
# mysql_errno: 0
INSERT INTO t1 VALUES (17);
INSERT INTO t1 VALUES (19), (NULL);
INSERT INTO t1 VALUES (NULL), (10), (NULL);
INSERT INTO t1 VALUES (NULL);
SET INSERT_ID = 30;
INSERT INTO t1 VALUES (NULL);
SET INSERT_ID = 29;
INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
# mysql_errno: 0
INSERT INTO t1 VALUES (NULL);
UPDATE t1 SET c1 = 50 WHERE c1 = 17;
UPDATE t1 SET c1 = 51 WHERE c1 = 19;
FLUSH TABLES;
UPDATE t1 SET c1 = 40 WHERE c1 = 50;
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
  AND TABLE_NAME='t1';
AUTO_INCREMENT
1
UPDATE t1 SET c1 = NULL WHERE c1 = 4;
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
DROP TABLE t1;
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1))
ENGINE='Blackhole';
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
FLUSH TABLE;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES (4);
FLUSH TABLE;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES (NULL);
FLUSH TABLE;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
DELETE FROM t1;
INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
SELECT * FROM t1 ORDER BY c1;
c1
TRUNCATE TABLE t1;
INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
SELECT * FROM t1 ORDER BY c1;
c1
INSERT INTO t1 VALUES (100);
INSERT INTO t1 VALUES (NULL);
DELETE FROM t1 WHERE c1 >= 100;
OPTIMIZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	note	The storage engine for the table doesn't support optimize
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE TABLE t1
(a INT NULL AUTO_INCREMENT,
UNIQUE KEY (a))
ENGINE='Blackhole';
SET LAST_INSERT_ID = 999;
SET INSERT_ID = 0;
INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
999
SELECT * FROM t1;
a
INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
999
SELECT * FROM t1;
a
UPDATE t1 SET a = 1 WHERE a IS NULL;
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
999
SELECT * FROM t1;
a
UPDATE t1 SET a = NULL WHERE a = 1;
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
999
SELECT * FROM t1;
a
DROP TABLE t1;
SET INSERT_ID = 1;
# Simple test with NULL
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1))
ENGINE='Blackhole'
PARTITION BY HASH(c1)
PARTITIONS 2;
INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
SELECT * FROM t1;
c1
DROP TABLE t1;
# Test with sql_mode and first insert as 0
CREATE TABLE t1 (
c1 INT,
c2 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c2))
ENGINE='Blackhole'
PARTITION BY HASH(c2)
PARTITIONS 2;
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t1 VALUES (1, 1), (99, 99);
# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
# mysql_errno: 0
INSERT INTO t1 VALUES (1, NULL);
SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
INSERT INTO t1 VALUES (1, 0);
SELECT * FROM t1 ORDER BY c1, c2;
c1	c2
DROP TABLE t1;
CREATE TABLE t1 (
c1 INT,
c2 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c2))
ENGINE='Blackhole'
PARTITION BY HASH(c2)
PARTITIONS 2;
INSERT INTO t1 VALUES (1, 0);
INSERT INTO t1 VALUES (1, 1), (1, NULL);
INSERT INTO t1 VALUES (2, NULL), (4, 7);
INSERT INTO t1 VALUES (1, NULL);
SELECT * FROM t1 ORDER BY c1, c2;
c1	c2
SET @@session.sql_mode = '';
DROP TABLE t1;
# Simple test with NULL, 0 and explicit values both incr. and desc.
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1))
ENGINE='Blackhole'
PARTITION BY HASH(c1)
PARTITIONS 2;
INSERT INTO t1 VALUES (2), (4), (NULL);
INSERT INTO t1 VALUES (0);
INSERT INTO t1 VALUES (5), (16);
# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
# mysql_errno: 0
INSERT INTO t1 VALUES (17), (19), (NULL);
INSERT INTO t1 VALUES (NULL), (10), (NULL);
INSERT INTO t1 VALUES (NULL), (9);
INSERT INTO t1 VALUES (59), (55);
INSERT INTO t1 VALUES (NULL), (90);
INSERT INTO t1 VALUES (NULL);
UPDATE t1 SET c1 = 150 WHERE c1 = 17;
UPDATE t1 SET c1 = 151 WHERE c1 = 19;
FLUSH TABLES;
UPDATE t1 SET c1 = 140 WHERE c1 = 150;
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
  AND TABLE_NAME='t1';
AUTO_INCREMENT
1
UPDATE t1 SET c1 = NULL WHERE c1 = 4;
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
DROP TABLE t1;
# Test with auto_increment_increment and auto_increment_offset.
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1))
ENGINE='Blackhole'
PARTITION BY HASH(c1)
PARTITIONS 2;
SET @@session.auto_increment_increment = 10;
SET @@session.auto_increment_offset = 5;
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
SET @@session.auto_increment_increment = 5;
SET @@session.auto_increment_offset = 3;
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (33 + 1);
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (38 + 2);
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (43 + 3);
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (48 + 4);
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (53 + 5);
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (63 + 6);
INSERT INTO t1 VALUES (NULL);
SET @@session.auto_increment_increment = 1;
SET @@session.auto_increment_offset = 1;
SELECT * FROM t1 ORDER BY c1;
c1
DROP TABLE t1;
# Test reported auto_increment value
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1))
ENGINE='Blackhole'
PARTITION BY HASH (c1)
PARTITIONS 2;
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
AUTO_INCREMENT
1
INSERT INTO t1 VALUES (2);
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
AUTO_INCREMENT
3
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (NULL);
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
AUTO_INCREMENT
6
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (17);
INSERT INTO t1 VALUES (19);
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (NULL);
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
AUTO_INCREMENT
22
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
AUTO_INCREMENT
22
INSERT INTO t1 VALUES (10);
SELECT * FROM t1 ORDER BY c1;
c1
INSERT INTO t1 VALUES (NULL);
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
AUTO_INCREMENT
23
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (15);
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
INSERT INTO t1 VALUES (NULL);
DELETE FROM t1;
INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE AUTO_INCREMENT=27 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
SELECT * FROM t1 ORDER BY c1;
c1
TRUNCATE TABLE t1;
INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
SELECT * FROM t1 ORDER BY c1;
c1
INSERT INTO t1 VALUES (100);
INSERT INTO t1 VALUES (NULL);
DELETE FROM t1 WHERE c1 >= 100;
OPTIMIZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	note	The storage engine for the table doesn't support optimize
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
DROP TABLE t1;
# Test with two threads
# con default
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
ENGINE = 'Blackhole'
PARTITION BY HASH(c1)
PARTITIONS 2;
INSERT INTO t1 (c1) VALUES (2);
INSERT INTO t1 (c1) VALUES (4);
# con1
INSERT INTO t1 (c1) VALUES (NULL);
INSERT INTO t1 (c1) VALUES (10);
# con default
INSERT INTO t1 (c1) VALUES (NULL);
INSERT INTO t1 (c1) VALUES (NULL);
INSERT INTO t1 (c1) VALUES (19);
INSERT INTO t1 (c1) VALUES (21);
# con1
INSERT INTO t1 (c1) VALUES (NULL);
# con default
INSERT INTO t1 (c1) VALUES (16);
# con1
INSERT INTO t1 (c1) VALUES (NULL);
# con default
INSERT INTO t1 (c1) VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
DROP TABLE t1;
# Test with two threads + start transaction NO PARTITIONING
# con default
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
ENGINE = 'Blackhole';
START TRANSACTION;
INSERT INTO t1 (c1) VALUES (2);
INSERT INTO t1 (c1) VALUES (4);
# con1
START TRANSACTION;
INSERT INTO t1 (c1) VALUES (NULL);
INSERT INTO t1 (c1) VALUES (10);
# con default
INSERT INTO t1 (c1) VALUES (NULL);
INSERT INTO t1 (c1) VALUES (NULL);
INSERT INTO t1 (c1) VALUES (19);
INSERT INTO t1 (c1) VALUES (21);
# con1
INSERT INTO t1 (c1) VALUES (NULL);
# con default
INSERT INTO t1 (c1) VALUES (16);
# con1
INSERT INTO t1 (c1) VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
COMMIT;
SELECT * FROM t1 ORDER BY c1;
c1
# con default
INSERT INTO t1 (c1) VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
COMMIT;
SELECT * FROM t1 ORDER BY c1;
c1
DROP TABLE t1;
# Test with two threads + start transaction
# con default
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
ENGINE = 'Blackhole'
PARTITION BY HASH(c1)
PARTITIONS 2;
START TRANSACTION;
INSERT INTO t1 (c1) VALUES (2);
INSERT INTO t1 (c1) VALUES (4);
# con1
START TRANSACTION;
INSERT INTO t1 (c1) VALUES (NULL), (10);
# con default
INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
INSERT INTO t1 (c1) VALUES (21);
# con1
INSERT INTO t1 (c1) VALUES (NULL);
# con default
INSERT INTO t1 (c1) VALUES (16);
# con1
INSERT INTO t1 (c1) VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
COMMIT;
SELECT * FROM t1 ORDER BY c1;
c1
# con default
INSERT INTO t1 (c1) VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
COMMIT;
SELECT * FROM t1 ORDER BY c1;
c1
DROP TABLE t1;
# Test with another column after
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
c2 INT,
PRIMARY KEY (c1,c2))
ENGINE = 'Blackhole'
PARTITION BY HASH(c2)
PARTITIONS 2;
INSERT INTO t1 VALUES (1, 0);
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
INSERT INTO t1 VALUES (NULL, 3);
INSERT INTO t1 VALUES (2, 0), (NULL, 2);
INSERT INTO t1 VALUES (2, 2);
INSERT INTO t1 VALUES (2, 22);
INSERT INTO t1 VALUES (NULL, 2);
SELECT * FROM t1 ORDER BY c1,c2;
c1	c2
DROP TABLE t1;
# Test with another column before
CREATE TABLE t1 (
c1 INT,
c2 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c2))
ENGINE = 'Blackhole'
PARTITION BY HASH(c2)
PARTITIONS 2;
INSERT INTO t1 VALUES (1, 0);
INSERT INTO t1 VALUES (1, 1);
# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
# mysql_errno: 0
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
INSERT INTO t1 VALUES (2, NULL);
INSERT INTO t1 VALUES (2, 2);
# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
# mysql_errno: 0
INSERT INTO t1 VALUES (2, 22);
INSERT INTO t1 VALUES (2, NULL);
SELECT * FROM t1 ORDER BY c1,c2;
c1	c2
DROP TABLE t1;
# Test with auto_increment on secondary column in multi-column-index
CREATE TABLE t1 (
c1 INT,
c2 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1,c2))
ENGINE = 'Blackhole'
PARTITION BY HASH(c2)
PARTITIONS 2;
INSERT INTO t1 VALUES (1, 0);
INSERT INTO t1 VALUES (1, 1);
# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
# mysql_errno: 0
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t1 VALUES (2, NULL);
INSERT INTO t1 VALUES (3, NULL);
INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL);
INSERT INTO t1 VALUES (2, 2);
# ERROR (only OK if Blackhole/NDB) should give ER_DUP_KEY or ER_DUP_ENTRY
# mysql_errno: 0
INSERT INTO t1 VALUES (2, 22), (2, NULL);
SELECT * FROM t1 ORDER BY c1,c2;
c1	c2
DROP TABLE t1;
# Test AUTO_INCREMENT in CREATE
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
ENGINE = 'Blackhole'
AUTO_INCREMENT = 15
PARTITION BY HASH(c1)
PARTITIONS 2;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
INSERT INTO t1 (c1) VALUES (4);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
INSERT INTO t1 (c1) VALUES (0);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
INSERT INTO t1 (c1) VALUES (NULL);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
SELECT * FROM t1 ORDER BY c1;
c1
# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
INSERT INTO t1 (c1) VALUES (300);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE AUTO_INCREMENT=301 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
INSERT INTO t1 (c1) VALUES (0);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE AUTO_INCREMENT=301 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
INSERT INTO t1 (c1) VALUES (NULL);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE AUTO_INCREMENT=302 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
SELECT * FROM t1 ORDER BY c1;
c1
SET @@session.sql_mode = '';
DROP TABLE t1;
# Test SET INSERT_ID
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
ENGINE = 'Blackhole'
PARTITION BY HASH(c1)
PARTITIONS 2;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
INSERT INTO t1 (c1) VALUES (NULL);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
SELECT * FROM t1;
c1
SET INSERT_ID = 23;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
INSERT INTO t1 (c1) VALUES (NULL);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE AUTO_INCREMENT=24 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
SET INSERT_ID = 22;
INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
# mysql_errno: 0
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
DROP TABLE t1;
# Testing with FLUSH TABLE
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1))
ENGINE='Blackhole'
PARTITION BY HASH(c1)
PARTITIONS 2;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
FLUSH TABLE;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
INSERT INTO t1 VALUES (4);
FLUSH TABLE;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
INSERT INTO t1 VALUES (NULL);
FLUSH TABLE;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
SELECT * FROM t1 ORDER BY c1;
c1
DROP TABLE t1;