DROP TABLE IF EXISTS t1; # test without partitioning for reference CREATE TABLE t1 ( c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE='Memory'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=MEMORY 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 6 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; AUTO_INCREMENT 6 INSERT INTO t1 VALUES (0); INSERT INTO t1 VALUES (5), (16); 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); UPDATE t1 SET c1 = 50 WHERE c1 = 17; UPDATE t1 SET c1 = 51 WHERE c1 = 19; 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 2 4 5 6 10 20 21 22 23 30 50 51 52 53 DROP TABLE t1; CREATE TABLE t1 ( c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE='Memory'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=MEMORY 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=MEMORY 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=MEMORY AUTO_INCREMENT=5 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=MEMORY AUTO_INCREMENT=6 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=MEMORY AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 SELECT * FROM t1 ORDER BY c1; c1 6 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=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 SELECT * FROM t1 ORDER BY c1; c1 1 DROP TABLE t1; # Simple test with NULL CREATE TABLE t1 ( c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE='Memory' 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=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */ SELECT * FROM t1; c1 1 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='Memory' PARTITION BY HASH(c2) PARTITIONS 2; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (1, 1), (99, 99); 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 1 0 1 1 1 2 DROP TABLE t1; CREATE TABLE t1 ( c1 INT, c2 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c2)) ENGINE='Memory' 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 1 0 1 1 1 2 1 8 2 3 4 7 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='Memory' PARTITION BY HASH(c1) PARTITIONS 2; INSERT INTO t1 VALUES (2), (4), (NULL); INSERT INTO t1 VALUES (0); INSERT INTO t1 VALUES (5), (16); 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; 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 2 4 5 6 9 10 20 21 22 23 55 59 60 90 91 150 151 152 153 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='Memory' 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 1 5 15 25 33 34 38 40 43 46 48 52 53 58 63 69 73 DROP TABLE t1; # Test reported auto_increment value CREATE TABLE t1 ( c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE='Memory' 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 2 4 5 6 10 17 19 20 21 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 2 4 5 6 10 15 17 19 20 21 22 23 24 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=MEMORY AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */ SELECT * FROM t1 ORDER BY c1; c1 26 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=MEMORY AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */ SELECT * FROM t1 ORDER BY c1; c1 27 DROP TABLE t1; # Test with two threads # con default CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE = 'Memory' 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 2 4 5 10 11 12 16 19 21 22 23 24 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 = 'Memory'; 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 2 4 5 10 11 12 16 19 21 22 23 COMMIT; SELECT * FROM t1 ORDER BY c1; c1 2 4 5 10 11 12 16 19 21 22 23 # con default INSERT INTO t1 (c1) VALUES (NULL); SELECT * FROM t1 ORDER BY c1; c1 2 4 5 10 11 12 16 19 21 22 23 24 COMMIT; SELECT * FROM t1 ORDER BY c1; c1 2 4 5 10 11 12 16 19 21 22 23 24 DROP TABLE t1; # Test with two threads + start transaction # con default CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE = 'Memory' 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 2 4 5 10 11 12 16 19 21 22 23 COMMIT; SELECT * FROM t1 ORDER BY c1; c1 2 4 5 10 11 12 16 19 21 22 23 # con default INSERT INTO t1 (c1) VALUES (NULL); SELECT * FROM t1 ORDER BY c1; c1 2 4 5 10 11 12 16 19 21 22 23 24 COMMIT; SELECT * FROM t1 ORDER BY c1; c1 2 4 5 10 11 12 16 19 21 22 23 24 DROP TABLE t1; # Test with another column after CREATE TABLE t1 ( c1 INT NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1,c2)) ENGINE = 'Memory' 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 1 0 1 1 2 0 2 1 2 2 2 22 3 2 4 3 5 3 6 2 7 2 DROP TABLE t1; # Test with another column before CREATE TABLE t1 ( c1 INT, c2 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c2)) ENGINE = 'Memory' PARTITION BY HASH(c2) PARTITIONS 2; INSERT INTO t1 VALUES (1, 0); INSERT INTO t1 VALUES (1, 1); 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); INSERT INTO t1 VALUES (2, 22); INSERT INTO t1 VALUES (2, NULL); SELECT * FROM t1 ORDER BY c1,c2; c1 c2 1 1 1 2 2 3 2 13 2 14 2 22 2 23 3 11 3 12 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 = 'Memory' PARTITION BY HASH(c2) PARTITIONS 2; ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key # Test AUTO_INCREMENT in CREATE CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE = 'Memory' 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=MEMORY AUTO_INCREMENT=15 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=MEMORY AUTO_INCREMENT=15 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=MEMORY AUTO_INCREMENT=16 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=MEMORY AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */ SELECT * FROM t1 ORDER BY c1; c1 4 15 16 # 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=MEMORY 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=MEMORY 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=MEMORY AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */ SELECT * FROM t1 ORDER BY c1; c1 0 4 15 16 300 301 SET @@session.sql_mode = ''; DROP TABLE t1; # Test SET INSERT_ID CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE = 'Memory' 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=MEMORY 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=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */ SELECT * FROM t1; c1 1 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=MEMORY 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=MEMORY AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */ SELECT * FROM t1 ORDER BY c1; c1 1 23 DROP TABLE t1; # Testing with FLUSH TABLE CREATE TABLE t1 ( c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE='Memory' 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=MEMORY 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=MEMORY 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=MEMORY AUTO_INCREMENT=5 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=MEMORY AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */ SELECT * FROM t1 ORDER BY c1; c1 4 5 DROP TABLE t1;