From: Date: October 1 2008 2:01pm
Subject: bzr commit into mysql-6.0 branch (mattias.jonsson:2844) Bug#33479
 Bug#38804
List-Archive: http://lists.mysql.com/commits/54920
X-Bug: 33479,38804
Message-Id: <20081001120106.62EF613687D6@client-10-129-10-147.upp.off.mysql.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7BIT

#At file:///Users/mattiasj/clones/bzrroot/topush-60-5129rc/

 2844 Mattias Jonsson	2008-10-01 [merge]
      merge (basically a null merge, since it contains a backport of bug#33479 as a fix for Bug#38804)
removed:
  mysql-test/suite/parts/inc/partition_auto_increment.inc
  mysql-test/suite/parts/r/partition_auto_increment_innodb.result
  mysql-test/suite/parts/r/partition_auto_increment_myisam.result
  mysql-test/suite/parts/t/partition_auto_increment_innodb.test
  mysql-test/suite/parts/t/partition_auto_increment_myisam.test
added:
  mysql-test/suite/parts/inc/partition_auto_increment.inc
  mysql-test/suite/parts/r/partition_auto_increment_archive.result
  mysql-test/suite/parts/r/partition_auto_increment_blackhole.result
  mysql-test/suite/parts/r/partition_auto_increment_innodb.result
  mysql-test/suite/parts/r/partition_auto_increment_memory.result
  mysql-test/suite/parts/r/partition_auto_increment_myisam.result
  mysql-test/suite/parts/r/partition_auto_increment_ndb.result
  mysql-test/suite/parts/t/partition_auto_increment_archive.test
  mysql-test/suite/parts/t/partition_auto_increment_blackhole.test
  mysql-test/suite/parts/t/partition_auto_increment_innodb.test
  mysql-test/suite/parts/t/partition_auto_increment_memory.test
  mysql-test/suite/parts/t/partition_auto_increment_myisam.test
  mysql-test/suite/parts/t/partition_auto_increment_ndb.test
modified:
  mysql-test/suite/parts/r/partition_auto_increment_falcon.result
  mysql-test/suite/parts/t/disabled.def
  sql/ha_partition.cc
  sql/ha_partition.h

per-file messages:
  mysql-test/suite/parts/t/disabled.def
    Enabled partition_auto_increment_falcon, since it now works, disabled parttion_auto_increment_ndb since it have changed behavior which should be investigated
  sql/ha_partition.cc
    Bug#38804
    
    merge of fixes when backporting bug#33479 to 5.1
  sql/ha_partition.h
    Bug#38804
    
    merge of fixes when backporting bug#33479 to 5.1
=== added file 'mysql-test/suite/parts/inc/partition_auto_increment.inc'
--- a/mysql-test/suite/parts/inc/partition_auto_increment.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc	2008-09-08 13:30:01 +0000
@@ -0,0 +1,600 @@
+# inc/partition_auto_increment.inc
+#
+# auto_increment test
+# used variables: $engine
+#
+
+-- disable_warnings
+DROP TABLE IF EXISTS t1;
+-- enable_warnings
+
+-- echo # test without partitioning for reference
+eval CREATE TABLE t1 (
+  c1 INT NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (c1))
+ENGINE=$engine;
+SHOW CREATE TABLE t1;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+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';
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+INSERT INTO t1 VALUES (0);
+-- error 0, ER_DUP_KEY, ER_DUP_ENTRY
+INSERT INTO t1 VALUES (5), (16);
+if (!$mysql_errno)
+{
+  echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19), (NULL);
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+if ($mysql_errno)
+{
+  echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 30;
+INSERT INTO t1 VALUES (NULL);
+if (!$skip_update)
+{
+  UPDATE t1 SET c1 = 50 WHERE c1 = 17;
+  UPDATE t1 SET c1 = 51 WHERE c1 = 19;
+  -- error 0, ER_BAD_NULL_ERROR
+  UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+if (!$mysql_errno)
+{
+  echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+  INSERT INTO t1 VALUES (NULL);
+  INSERT INTO t1 VALUES (NULL);
+}
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+eval CREATE TABLE t1 (
+  c1 INT NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (c1))
+ENGINE=$engine;
+SHOW CREATE TABLE t1;
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+if (!$skip_delete)
+{
+DELETE FROM t1;
+}
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+if (!$skip_truncate)
+{
+TRUNCATE TABLE t1;
+}
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+-- echo # Simple test with NULL
+eval CREATE TABLE t1 (
+  c1 INT NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (c1))
+ENGINE=$engine
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+-- echo # Test with sql_mode and first insert as 0
+eval CREATE TABLE t1 (
+  c1 INT,
+  c2 INT NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (c2))
+ENGINE=$engine
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, NULL);
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (1, 1), (99, 99);
+if (!$mysql_errno)
+{
+  echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (1, NULL);
+let $old_sql_mode = `select @@session.sql_mode`;
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (1, 0);
+if ($mysql_errno)
+{
+  echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+SELECT * FROM t1 ORDER BY c1, c2;
+DROP TABLE t1;
+eval CREATE TABLE t1 (
+  c1 INT,
+  c2 INT NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (c2))
+ENGINE=$engine
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (1, 0);
+if ($mysql_errno)
+{
+  echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+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;
+eval SET @@session.sql_mode = '$old_sql_mode';
+DROP TABLE t1;
+
+
+-- echo # Simple test with NULL, 0 and explicit values both incr. and desc.
+eval CREATE TABLE t1 (
+  c1 INT NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (c1))
+ENGINE=$engine
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (2), (4), (NULL);
+INSERT INTO t1 VALUES (0);
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (5), (16);
+if (!$mysql_errno)
+{
+  echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (17), (19), (NULL);
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+if ($mysql_errno)
+{
+  echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (NULL), (9);
+if ($mysql_errno)
+{
+  echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (59), (55);
+if ($mysql_errno)
+{
+  echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+INSERT INTO t1 VALUES (NULL), (90);
+INSERT INTO t1 VALUES (NULL);
+if (!$skip_update)
+{
+  UPDATE t1 SET c1 = 150 WHERE c1 = 17;
+  UPDATE t1 SET c1 = 151 WHERE c1 = 19;
+  -- error 0, ER_BAD_NULL_ERROR
+  UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+if (!$mysql_errno)
+{
+  echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+  INSERT INTO t1 VALUES (NULL);
+  INSERT INTO t1 VALUES (NULL);
+}
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+-- echo # Test with auto_increment_increment and auto_increment_offset.
+eval CREATE TABLE t1 (
+  c1 INT NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (c1))
+ENGINE=$engine
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+let $old_increment = `SELECT @@session.auto_increment_increment`;
+let $old_offset = `SELECT @@session.auto_increment_offset`;
+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);
+let $new_val = `SELECT LAST_INSERT_ID()`;
+eval INSERT INTO t1 VALUES ($new_val + 1);
+INSERT INTO t1 VALUES (NULL);
+let $new_val = `SELECT LAST_INSERT_ID()`;
+eval INSERT INTO t1 VALUES ($new_val + 2);
+INSERT INTO t1 VALUES (NULL);
+let $new_val = `SELECT LAST_INSERT_ID()`;
+eval INSERT INTO t1 VALUES ($new_val + 3);
+INSERT INTO t1 VALUES (NULL);
+let $new_val = `SELECT LAST_INSERT_ID()`;
+eval INSERT INTO t1 VALUES ($new_val + 4);
+INSERT INTO t1 VALUES (NULL);
+let $new_val = `SELECT LAST_INSERT_ID()`;
+eval INSERT INTO t1 VALUES ($new_val + 5);
+INSERT INTO t1 VALUES (NULL);
+let $new_val = `SELECT LAST_INSERT_ID()`;
+eval INSERT INTO t1 VALUES ($new_val + 6);
+INSERT INTO t1 VALUES (NULL);
+eval SET @@session.auto_increment_increment = $old_increment;
+eval SET @@session.auto_increment_offset = $old_offset;
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+
+-- echo # Test reported auto_increment value
+eval CREATE TABLE t1 (
+  c1 INT NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (c1))
+ENGINE=$engine
+PARTITION BY HASH (c1)
+PARTITIONS 2;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+INSERT INTO t1 VALUES (2);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+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';
+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';
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (10);
+if ($mysql_errno)
+{
+  echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+SELECT * FROM t1 ORDER BY c1;
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+INSERT INTO t1 VALUES (NULL);
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (15);
+if ($mysql_errno)
+{
+  echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+INSERT INTO t1 VALUES (NULL);
+if (!$skip_delete)
+{
+DELETE FROM t1;
+}
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+if (!$skip_truncate)
+{
+TRUNCATE TABLE t1;
+}
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+-- echo # Test with two threads
+connection default;
+-- echo # con default
+eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+  ENGINE = $engine
+  PARTITION BY HASH(c1)
+  PARTITIONS 2;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+connect(con1, localhost, root,,);
+connection con1;
+-- echo # con1
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+connection default;
+-- echo # 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);
+-- echo # con1
+connection con1;
+INSERT INTO t1 (c1) VALUES (NULL);
+connection default;
+-- echo # con default
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 (c1) VALUES (16);
+if ($mysql_errno)
+{
+  echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+-- echo # con1
+connection con1;
+INSERT INTO t1 (c1) VALUES (NULL);
+disconnect con1;
+connection default;
+-- echo # con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+-- echo # Test with two threads + start transaction NO PARTITIONING
+connect(con1, localhost, root,,);
+connection default;
+-- echo # con default
+eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+  ENGINE = $engine;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+connection con1;
+-- echo # con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+connection default;
+-- echo # 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);
+-- echo # con1
+connection con1;
+INSERT INTO t1 (c1) VALUES (NULL);
+connection default;
+-- echo # con default
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 (c1) VALUES (16);
+if ($mysql_errno)
+{
+  echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+-- echo # con1
+connection con1;
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+disconnect con1;
+connection default;
+-- echo # con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+-- echo # Test with two threads + start transaction
+connect(con1, localhost, root,,);
+connection default;
+-- echo # con default
+eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+  ENGINE = $engine
+  PARTITION BY HASH(c1)
+  PARTITIONS 2;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+connection con1;
+-- echo # con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL), (10);
+connection default;
+-- echo # con default
+INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
+INSERT INTO t1 (c1) VALUES (21);
+-- echo # con1
+connection con1;
+INSERT INTO t1 (c1) VALUES (NULL);
+connection default;
+-- echo # con default
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 (c1) VALUES (16);
+if ($mysql_errno)
+{
+  echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+-- echo # con1
+connection con1;
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+disconnect con1;
+connection default;
+-- echo # con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+if (!$only_ai_pk)
+{
+-- echo # Test with another column after
+eval CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+c2 INT,
+PRIMARY KEY (c1,c2))
+ENGINE = $engine
+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;
+DROP TABLE t1;
+}
+
+-- echo # Test with another column before
+eval CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE = $engine
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+-- error 0, ER_DUP_KEY, ER_DUP_ENTRY
+INSERT INTO t1 VALUES (1, 1);
+if (!$mysql_errno)
+{
+  echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
+INSERT INTO t1 VALUES (2, NULL);
+-- error 0, ER_DUP_KEY, ER_DUP_ENTRY
+INSERT INTO t1 VALUES (2, 2);
+if (!$mysql_errno)
+{
+  echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (2, 22);
+INSERT INTO t1 VALUES (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+DROP TABLE t1;
+
+-- echo # Test with auto_increment on secondary column in multi-column-index
+-- disable_abort_on_error
+eval CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1,c2))
+ENGINE = $engine
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+-- enable_abort_on_error
+-- disable_query_log
+eval SET @my_errno= $mysql_errno ;
+let $run = `SELECT @my_errno = 0`;
+# ER_WRONG_AUTO_KEY is 1075
+let $ER_WRONG_AUTO_KEY= 1075;
+if (`SELECT @my_errno NOT IN (0,$ER_WRONG_AUTO_KEY)`)
+{
+  -- echo # Unknown error code, exits
+  exit;
+}
+-- enable_query_log
+if ($run)
+{
+INSERT INTO t1 VALUES (1, 0);
+-- error 0, ER_DUP_KEY, ER_DUP_ENTRY
+INSERT INTO t1 VALUES (1, 1);
+if (!$mysql_errno)
+{
+  echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+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);
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (2, 2);
+if (!$mysql_errno)
+{
+echo # ERROR (only OK if Blackhole/NDB) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (2, 22), (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+DROP TABLE t1;
+}
+
+-- echo # Test AUTO_INCREMENT in CREATE
+eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+  ENGINE = $engine
+  AUTO_INCREMENT = 15
+  PARTITION BY HASH(c1)
+  PARTITIONS 2;
+SHOW CREATE TABLE t1;
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 (c1) VALUES (4);
+if ($mysql_errno)
+{
+  echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+
+-- echo # Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
+let $old_sql_mode = `select @@session.sql_mode`;
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 (c1) VALUES (300);
+SHOW CREATE TABLE t1;
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 (c1) VALUES (0);
+if ($mysql_errno)
+{
+  echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+eval SET @@session.sql_mode = '$old_sql_mode';
+DROP TABLE t1;
+
+-- echo # Test SET INSERT_ID
+eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+  ENGINE = $engine
+  PARTITION BY HASH(c1)
+  PARTITIONS 2;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+SET INSERT_ID = 23;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+-- echo # Testing with FLUSH TABLE
+eval CREATE TABLE t1 (
+  c1 INT NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (c1))
+  ENGINE=$engine
+  PARTITION BY HASH(c1)
+  PARTITIONS 2;
+SHOW CREATE TABLE t1;
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+

=== removed file 'mysql-test/suite/parts/inc/partition_auto_increment.inc'
--- a/mysql-test/suite/parts/inc/partition_auto_increment.inc	2008-04-25 11:37:58 +0000
+++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc	1970-01-01 00:00:00 +0000
@@ -1,466 +0,0 @@
-# inc/partition_auto_increment.inc
-#
-# auto_increment test
-# used variables: $engine
-#
-
--- disable_warnings
-DROP TABLE IF EXISTS t1;
--- enable_warnings
-
--- echo # test without partitioning for reference
-eval CREATE TABLE t1 (
-  c1 INT NOT NULL AUTO_INCREMENT,
-  PRIMARY KEY (c1))
-ENGINE=$engine;
-SHOW CREATE TABLE t1;
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-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';
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-INSERT INTO t1 VALUES (0);
--- error ER_DUP_ENTRY
-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);
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-eval CREATE TABLE t1 (
-  c1 INT NOT NULL AUTO_INCREMENT,
-  PRIMARY KEY (c1))
-ENGINE=$engine;
-SHOW CREATE TABLE t1;
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (4);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (NULL);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-DELETE FROM t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-TRUNCATE TABLE t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
--- echo # Simple test with NULL
-eval CREATE TABLE t1 (
-  c1 INT NOT NULL AUTO_INCREMENT,
-  PRIMARY KEY (c1))
-ENGINE=$engine
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1;
-DROP TABLE t1;
-
--- echo # Test with sql_mode and first insert as 0
-eval CREATE TABLE t1 (
-  c1 INT,
-  c2 INT NOT NULL AUTO_INCREMENT,
-  PRIMARY KEY (c2))
-ENGINE=$engine
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, NULL);
--- error ER_DUP_KEY
-INSERT INTO t1 VALUES (1, 1), (99, 99);
-INSERT INTO t1 VALUES (1, NULL);
-let $old_sql_mode = `select @@session.sql_mode`;
-SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
-INSERT INTO t1 VALUES (1, 0);
-SELECT * FROM t1 ORDER BY c1, c2;
-DROP TABLE t1;
-eval CREATE TABLE t1 (
-  c1 INT,
-  c2 INT NOT NULL AUTO_INCREMENT,
-  PRIMARY KEY (c2))
-ENGINE=$engine
-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;
-eval SET @@session.sql_mode = '$old_sql_mode';
-DROP TABLE t1;
-
-
--- echo # Simple test with NULL, 0 and explicit values both incr. and desc.
-eval CREATE TABLE t1 (
-  c1 INT NOT NULL AUTO_INCREMENT,
-  PRIMARY KEY (c1))
-ENGINE=$engine
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (2), (4), (NULL);
-INSERT INTO t1 VALUES (0);
--- error ER_DUP_KEY
-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);
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
--- echo # Test with auto_increment_increment and auto_increment_offset.
-eval CREATE TABLE t1 (
-  c1 INT NOT NULL AUTO_INCREMENT,
-  PRIMARY KEY (c1))
-ENGINE=$engine
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-let $old_increment = `SELECT @@session.auto_increment_increment`;
-let $old_offset = `SELECT @@session.auto_increment_offset`;
-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);
-let $new_val = `SELECT LAST_INSERT_ID()`;
-eval INSERT INTO t1 VALUES ($new_val + 1);
-INSERT INTO t1 VALUES (NULL);
-let $new_val = `SELECT LAST_INSERT_ID()`;
-eval INSERT INTO t1 VALUES ($new_val + 2);
-INSERT INTO t1 VALUES (NULL);
-let $new_val = `SELECT LAST_INSERT_ID()`;
-eval INSERT INTO t1 VALUES ($new_val + 3);
-INSERT INTO t1 VALUES (NULL);
-let $new_val = `SELECT LAST_INSERT_ID()`;
-eval INSERT INTO t1 VALUES ($new_val + 4);
-INSERT INTO t1 VALUES (NULL);
-let $new_val = `SELECT LAST_INSERT_ID()`;
-eval INSERT INTO t1 VALUES ($new_val + 5);
-INSERT INTO t1 VALUES (NULL);
-let $new_val = `SELECT LAST_INSERT_ID()`;
-eval INSERT INTO t1 VALUES ($new_val + 6);
-INSERT INTO t1 VALUES (NULL);
-eval SET @@session.auto_increment_increment = $old_increment;
-eval SET @@session.auto_increment_offset = $old_offset;
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
-
--- echo # Test reported auto_increment value
-eval CREATE TABLE t1 (
-  c1 INT NOT NULL AUTO_INCREMENT,
-  PRIMARY KEY (c1))
-ENGINE=$engine
-PARTITION BY HASH (c1)
-PARTITIONS 2;
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-INSERT INTO t1 VALUES (2);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-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';
-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';
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-INSERT INTO t1 VALUES (10);
-SELECT * FROM t1 ORDER BY c1;
-INSERT INTO t1 VALUES (NULL);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (15);
-INSERT INTO t1 VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-INSERT INTO t1 VALUES (NULL);
-DELETE FROM t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-TRUNCATE TABLE t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
--- echo # Test with two threads
-connect(con1, localhost, root,,);
-connection default;
--- echo # con default
-eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-  ENGINE = $engine
-  PARTITION BY HASH(c1)
-  PARTITIONS 2;
-INSERT INTO t1 (c1) VALUES (2);
-INSERT INTO t1 (c1) VALUES (4);
-connection con1;
--- echo # con1
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (10);
-connection default;
--- echo # 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);
--- echo # con1
-connection con1;
-INSERT INTO t1 (c1) VALUES (NULL);
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (16);
--- echo # con1
-connection con1;
-INSERT INTO t1 (c1) VALUES (NULL);
-disconnect con1;
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
--- echo # Test with two threads + start transaction NO PARTITIONING
-connect(con1, localhost, root,,);
-connection default;
--- echo # con default
-eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-  ENGINE = $engine;
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (2);
-INSERT INTO t1 (c1) VALUES (4);
-connection con1;
--- echo # con1
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (10);
-connection default;
--- echo # 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);
--- echo # con1
-connection con1;
-INSERT INTO t1 (c1) VALUES (NULL);
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (16);
--- echo # con1
-connection con1;
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-disconnect con1;
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
--- echo # Test with two threads + start transaction
-connect(con1, localhost, root,,);
-connection default;
--- echo # con default
-eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-  ENGINE = $engine
-  PARTITION BY HASH(c1)
-  PARTITIONS 2;
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (2);
-INSERT INTO t1 (c1) VALUES (4);
-connection con1;
--- echo # con1
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (NULL), (10);
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
-INSERT INTO t1 (c1) VALUES (21);
--- echo # con1
-connection con1;
-INSERT INTO t1 (c1) VALUES (NULL);
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (16);
--- echo # con1
-connection con1;
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-disconnect con1;
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
--- echo # Test with another column after
-eval CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-c2 INT,
-PRIMARY KEY (c1,c2))
-ENGINE = $engine
-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;
-DROP TABLE t1;
-
--- echo # Test with another column before
-eval CREATE TABLE t1 (
-c1 INT,
-c2 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c2))
-ENGINE = $engine
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, 0);
--- error ER_DUP_KEY
-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);
--- error ER_DUP_KEY
-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;
-DROP TABLE t1;
-
--- echo # Test with auto_increment on secondary column in multi-column-index
--- disable_abort_on_error
-eval CREATE TABLE t1 (
-c1 INT,
-c2 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1,c2))
-ENGINE = $engine
-PARTITION BY HASH(c2)
-PARTITIONS 2;
--- enable_abort_on_error
--- disable_query_log
-eval SET @my_errno= $mysql_errno ;
-let $run = `SELECT @my_errno = 0`;
-# ER_WRONG_AUTO_KEY is 1075
-let $ER_WRONG_AUTO_KEY= 1075;
-if (`SELECT @my_errno NOT IN (0,$ER_WRONG_AUTO_KEY)`)
-{
-  -- echo # Unknown error code, exits
-  exit;
-}
--- enable_query_log
-if ($run)
-{
-INSERT INTO t1 VALUES (1, 0);
--- error ER_DUP_KEY
-INSERT INTO t1 VALUES (1, 1);
-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);
--- error ER_DUP_KEY
-INSERT INTO t1 VALUES (2, 2);
-INSERT INTO t1 VALUES (2, 22), (2, NULL);
-SELECT * FROM t1 ORDER BY c1,c2;
-DROP TABLE t1;
-}
-
--- echo # Test AUTO_INCREMENT in CREATE
-eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-  ENGINE = $engine
-  AUTO_INCREMENT = 15
-  PARTITION BY HASH(c1)
-  PARTITIONS 2;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (c1) VALUES (4);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (c1) VALUES (0);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-
--- echo # Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
-let $old_sql_mode = `select @@session.sql_mode`;
-SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
-INSERT INTO t1 (c1) VALUES (300);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (c1) VALUES (0);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-eval SET @@session.sql_mode = '$old_sql_mode';
-DROP TABLE t1;
-
--- echo # Test SET INSERT_ID
-eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-  ENGINE = $engine
-  PARTITION BY HASH(c1)
-  PARTITIONS 2;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1;
-SET INSERT_ID = 23;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
--- echo # Testing with FLUSH TABLE
-eval CREATE TABLE t1 (
-  c1 INT NOT NULL AUTO_INCREMENT,
-  PRIMARY KEY (c1))
-  ENGINE=$engine
-  PARTITION BY HASH(c1)
-  PARTITIONS 2;
-SHOW CREATE TABLE t1;
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (4);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (NULL);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-

=== added file 'mysql-test/suite/parts/r/partition_auto_increment_archive.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_archive.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_archive.result	2008-09-08 13:30:01 +0000
@@ -0,0 +1,747 @@
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive';
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE 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);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 30;
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+22
+30
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive';
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE 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=ARCHIVE 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=ARCHIVE 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=ARCHIVE AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
+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=ARCHIVE AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+6
+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=ARCHIVE AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+6
+7
+DROP TABLE t1;
+# Simple test with NULL
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive'
+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=ARCHIVE 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='Archive'
+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);
+# ERROR (only OK if Archive) mysql_errno: 1022
+SELECT * FROM t1 ORDER BY c1, c2;
+c1	c2
+1	1
+1	2
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='Archive'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+# ERROR (only OK if Archive) mysql_errno: 1022
+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	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='Archive'
+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);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (NULL), (9);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (59), (55);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (NULL), (90);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+22
+59
+60
+90
+91
+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='Archive'
+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='Archive'
+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);
+# ERROR (only OK if Archive) mysql_errno: 1022
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+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);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+22
+23
+24
+INSERT INTO t1 VALUES (NULL);
+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=ARCHIVE AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2  */
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+22
+23
+24
+25
+26
+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=ARCHIVE AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2  */
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+22
+23
+24
+25
+26
+27
+DROP TABLE t1;
+# Test with two threads
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Archive'
+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);
+# ERROR (only OK if Archive) mysql_errno: 1022
+# 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
+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 = 'Archive';
+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);
+# ERROR (only OK if Archive) mysql_errno: 1022
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+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
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+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 = 'Archive'
+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);
+# ERROR (only OK if Archive) mysql_errno: 1022
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+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
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with another column before
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE = 'Archive'
+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 = 'Archive'
+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 = 'Archive'
+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=ARCHIVE AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2  */
+INSERT INTO t1 (c1) VALUES (4);
+# ERROR (only OK if Archive) mysql_errno: 1022
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE 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=ARCHIVE 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=ARCHIVE AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2  */
+SELECT * FROM t1 ORDER BY c1;
+c1
+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=ARCHIVE AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2  */
+INSERT INTO t1 (c1) VALUES (0);
+# ERROR (only OK if Archive) mysql_errno: 1022
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE 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=ARCHIVE AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2  */
+SELECT * FROM t1 ORDER BY c1;
+c1
+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 = 'Archive'
+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=ARCHIVE 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=ARCHIVE 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=ARCHIVE 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=ARCHIVE 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='Archive'
+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=ARCHIVE 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=ARCHIVE 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=ARCHIVE 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=ARCHIVE 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;

=== added file 'mysql-test/suite/parts/r/partition_auto_increment_blackhole.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result	2008-09-08 13:30:01 +0000
@@ -0,0 +1,570 @@
+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
+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;
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+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
+DROP TABLE t1;
+# 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
+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
+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;
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+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
+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
+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
+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
+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
+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  */
+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;

=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_falcon.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_falcon.result	2008-05-08 13:41:16 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_falcon.result	2008-10-01 12:00:30 +0000
@@ -27,13 +27,17 @@ AUTO_INCREMENT
 6
 INSERT INTO t1 VALUES (0);
 INSERT INTO t1 VALUES (5), (16);
-ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
 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
@@ -41,13 +45,15 @@ c1
 5
 6
 10
-17
-19
 20
 21
 22
 23
 30
+50
+51
+52
+53
 DROP TABLE t1;
 CREATE TABLE t1 (
 c1 INT NOT NULL AUTO_INCREMENT,
@@ -133,7 +139,6 @@ PARTITION BY HASH(c2)
 PARTITIONS 2;
 INSERT INTO t1 VALUES (1, NULL);
 INSERT INTO t1 VALUES (1, 1), (99, 99);
-ERROR 23000: Can't write; duplicate key in table 't1'
 INSERT INTO t1 VALUES (1, NULL);
 SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
 INSERT INTO t1 VALUES (1, 0);
@@ -174,13 +179,17 @@ PARTITIONS 2;
 INSERT INTO t1 VALUES (2), (4), (NULL);
 INSERT INTO t1 VALUES (0);
 INSERT INTO t1 VALUES (5), (16);
-ERROR 23000: Can't write; duplicate key in table 't1'
 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
@@ -189,8 +198,6 @@ c1
 6
 9
 10
-17
-19
 20
 21
 22
@@ -200,6 +207,10 @@ c1
 60
 90
 91
+150
+151
+152
+153
 DROP TABLE t1;
 # Test with auto_increment_increment and auto_increment_offset.
 CREATE TABLE t1 (
@@ -547,12 +558,10 @@ PARTITION BY HASH(c2)
 PARTITIONS 2;
 INSERT INTO t1 VALUES (1, 0);
 INSERT INTO t1 VALUES (1, 1);
-ERROR 23000: Can't write; duplicate key in table 't1'
 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 23000: Can't write; duplicate key in table 't1'
 INSERT INTO t1 VALUES (2, 22);
 INSERT INTO t1 VALUES (2, NULL);
 SELECT * FROM t1 ORDER BY c1,c2;

=== added file 'mysql-test/suite/parts/r/partition_auto_increment_innodb.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_innodb.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result	2008-09-08 13:30:01 +0000
@@ -0,0 +1,747 @@
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB';
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB 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
+22
+23
+25
+30
+31
+32
+50
+51
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB';
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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='InnoDB'
+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=InnoDB 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='InnoDB'
+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='InnoDB'
+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='InnoDB'
+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='InnoDB'
+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='InnoDB'
+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=InnoDB 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=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2  */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+DROP TABLE t1;
+# Test with two threads
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'InnoDB'
+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 = 'InnoDB';
+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
+5
+10
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+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 = 'InnoDB'
+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
+5
+10
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+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 = 'InnoDB'
+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 = 'InnoDB'
+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 = 'InnoDB'
+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 = 'InnoDB'
+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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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 = 'InnoDB'
+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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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='InnoDB'
+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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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;

=== removed file 'mysql-test/suite/parts/r/partition_auto_increment_innodb.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_innodb.result	2008-04-25 11:37:58 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result	1970-01-01 00:00:00 +0000
@@ -1,738 +0,0 @@
-DROP TABLE IF EXISTS t1;
-# test without partitioning for reference
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='InnoDB';
-SHOW CREATE TABLE t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `c1` int(11) NOT NULL AUTO_INCREMENT,
-  PRIMARY KEY (`c1`)
-) ENGINE=InnoDB 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);
-ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
-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);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-6
-10
-17
-19
-20
-22
-23
-25
-30
-DROP TABLE t1;
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='InnoDB';
-SHOW CREATE TABLE t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `c1` int(11) NOT NULL AUTO_INCREMENT,
-  PRIMARY KEY (`c1`)
-) ENGINE=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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='InnoDB'
-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=InnoDB 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='InnoDB'
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, NULL);
-INSERT INTO t1 VALUES (1, 1), (99, 99);
-ERROR 23000: Can't write; duplicate key in table 't1'
-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='InnoDB'
-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='InnoDB'
-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 23000: Can't write; duplicate key in table 't1'
-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);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-6
-9
-10
-17
-19
-20
-21
-22
-23
-55
-59
-60
-90
-91
-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='InnoDB'
-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='InnoDB'
-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=InnoDB 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=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2  */
-SELECT * FROM t1 ORDER BY c1;
-c1
-1
-DROP TABLE t1;
-# Test with two threads
-# con default
-CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-ENGINE = 'InnoDB'
-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 = 'InnoDB';
-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
-5
-10
-22
-23
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-c1
-5
-10
-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 = 'InnoDB'
-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
-5
-10
-22
-23
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-c1
-5
-10
-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 = 'InnoDB'
-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 = 'InnoDB'
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, 0);
-INSERT INTO t1 VALUES (1, 1);
-ERROR 23000: Can't write; duplicate key in table 't1'
-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 23000: Can't write; duplicate key in table 't1'
-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 = 'InnoDB'
-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 = 'InnoDB'
-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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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 = 'InnoDB'
-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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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='InnoDB'
-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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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;

=== added file 'mysql-test/suite/parts/r/partition_auto_increment_memory.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_memory.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_memory.result	2008-09-08 13:30:01 +0000
@@ -0,0 +1,775 @@
+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;

=== added file 'mysql-test/suite/parts/r/partition_auto_increment_myisam.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_myisam.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result	2008-09-08 13:30:01 +0000
@@ -0,0 +1,794 @@
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='MyISAM';
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=MyISAM 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='MyISAM';
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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='MyISAM'
+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=MyISAM 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='MyISAM'
+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='MyISAM'
+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='MyISAM'
+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='MyISAM'
+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='MyISAM'
+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=MyISAM 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=MyISAM 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 = 'MyISAM'
+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 = 'MyISAM';
+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 = 'MyISAM'
+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 = 'MyISAM'
+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 = 'MyISAM'
+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 = 'MyISAM'
+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);
+INSERT INTO t1 VALUES (3, NULL);
+INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (2, 22), (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1	c2
+1	1
+1	2
+2	1
+2	2
+2	3
+2	22
+2	23
+3	1
+3	2
+DROP TABLE t1;
+# Test AUTO_INCREMENT in CREATE
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'MyISAM'
+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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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 = 'MyISAM'
+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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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='MyISAM'
+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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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;

=== removed file 'mysql-test/suite/parts/r/partition_auto_increment_myisam.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_myisam.result	2008-04-25 11:37:58 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result	1970-01-01 00:00:00 +0000
@@ -1,787 +0,0 @@
-DROP TABLE IF EXISTS t1;
-# test without partitioning for reference
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='MyISAM';
-SHOW CREATE TABLE t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `c1` int(11) NOT NULL AUTO_INCREMENT,
-  PRIMARY KEY (`c1`)
-) ENGINE=MyISAM 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);
-ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
-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);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-6
-10
-17
-19
-20
-21
-22
-23
-30
-DROP TABLE t1;
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='MyISAM';
-SHOW CREATE TABLE t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `c1` int(11) NOT NULL AUTO_INCREMENT,
-  PRIMARY KEY (`c1`)
-) ENGINE=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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='MyISAM'
-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=MyISAM 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='MyISAM'
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, NULL);
-INSERT INTO t1 VALUES (1, 1), (99, 99);
-ERROR 23000: Can't write; duplicate key in table 't1'
-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='MyISAM'
-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='MyISAM'
-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 23000: Can't write; duplicate key in table 't1'
-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);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-6
-9
-10
-17
-19
-20
-21
-22
-23
-55
-59
-60
-90
-91
-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='MyISAM'
-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='MyISAM'
-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=MyISAM 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=MyISAM 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 = 'MyISAM'
-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 = 'MyISAM';
-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 = 'MyISAM'
-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 = 'MyISAM'
-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 = 'MyISAM'
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, 0);
-INSERT INTO t1 VALUES (1, 1);
-ERROR 23000: Can't write; duplicate key in table 't1'
-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 23000: Can't write; duplicate key in table 't1'
-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 = 'MyISAM'
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, 0);
-INSERT INTO t1 VALUES (1, 1);
-ERROR 23000: Can't write; duplicate key in table 't1'
-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 23000: Can't write; duplicate key in table 't1'
-INSERT INTO t1 VALUES (2, 22), (2, NULL);
-SELECT * FROM t1 ORDER BY c1,c2;
-c1	c2
-1	1
-1	2
-2	1
-2	2
-2	3
-2	22
-2	23
-3	1
-3	2
-DROP TABLE t1;
-# Test AUTO_INCREMENT in CREATE
-CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-ENGINE = 'MyISAM'
-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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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 = 'MyISAM'
-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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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='MyISAM'
-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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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;

=== added file 'mysql-test/suite/parts/r/partition_auto_increment_ndb.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_ndb.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_ndb.result	2008-09-08 13:30:01 +0000
@@ -0,0 +1,769 @@
+SET new=on;
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='NDB';
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster 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='NDB';
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster 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=ndbcluster 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=ndbcluster 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=ndbcluster 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=ndbcluster 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=ndbcluster 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='NDB'
+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=ndbcluster 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='NDB'
+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	100
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='NDB'
+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='NDB'
+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='NDB'
+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='NDB'
+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=ndbcluster 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=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2  */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+DROP TABLE t1;
+# Test with two threads
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'NDB'
+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 = 'NDB';
+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
+5
+10
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+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 = 'NDB'
+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
+5
+10
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+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 = 'NDB'
+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 = 'NDB'
+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 = 'NDB'
+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);
+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
+INSERT INTO t1 VALUES (2, 22), (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1	c2
+1	1
+1	2
+2	2
+2	3
+2	6
+2	7
+2	22
+2	23
+3	4
+3	5
+DROP TABLE t1;
+# Test AUTO_INCREMENT in CREATE
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'NDB'
+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=ndbcluster 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=ndbcluster 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=ndbcluster 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=ndbcluster 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=ndbcluster 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=ndbcluster 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=ndbcluster 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 = 'NDB'
+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=ndbcluster 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=ndbcluster 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=ndbcluster 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=ndbcluster 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='NDB'
+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=ndbcluster 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=ndbcluster 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=ndbcluster 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=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2  */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+DROP TABLE t1;

=== modified file 'mysql-test/suite/parts/t/disabled.def'
--- a/mysql-test/suite/parts/t/disabled.def	2008-09-11 08:01:28 +0000
+++ b/mysql-test/suite/parts/t/disabled.def	2008-10-01 12:00:30 +0000
@@ -7,7 +7,6 @@ partition_syntax_ndb           : Bug#367
 partition_value_innodb         : Bug#30581 partition_value tests use disallowed CAST() function
 partition_value_myisam         : Bug#30581 partition_value tests use disallowed CAST() function
 partition_value_ndb            : Bug#30581 partition_value tests use disallowed CAST() function
-partition_auto_increment_falcon : Bug#36595 2008-05-08 test crashes
 ndb_dd_backuprestore           : Bug#32659 2008-07-14 alik  Disabled to make 6.0 greaner (the test fails too often)
 partition_mgm_lc0_archive      : Bug#38751 - add partition deletes all rows
 partition_mgm_lc1_archive      : Bug#38751 - add partition deletes all rows
@@ -15,3 +14,4 @@ partition_mgm_lc2_archive      : Bug#387
 partition_mgm_lc0_ndb          : Bug#38778 - master1 crashes
 partition_mgm_lc1_ndb          : Bug#38778 - master1 crashes
 partition_mgm_lc2_ndb          : Bug#38778 - master1 crashes
+partition_auto_increment_ndb   : Bug#39773 - auto_increment for NDB changed behavior

=== added file 'mysql-test/suite/parts/t/partition_auto_increment_archive.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_archive.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_archive.test	2008-09-08 13:30:01 +0000
@@ -0,0 +1,40 @@
+################################################################################
+# t/partition_auto_increment_archive.test                                      #
+#                                                                              #
+# Purpose:                                                                     #
+#  Tests around auto increment column                                          #
+#        Archive branch                                                        #
+#                                                                              #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ                                                    #
+# Original Date: 2008-09-02                                                    #
+# Change Author:                                                               #
+# Change Date:                                                                 #
+# Change:                                                                      #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+#       TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+#       THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+--source include/have_archive.inc
+# Archve does not support delete
+let $skip_delete= 1;
+let $skip_truncate= 1;
+let $skip_update= 1;
+let $only_ai_pk= 1;
+
+##### Storage engine to be tested
+let $engine= 'Archive';
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+

=== added file 'mysql-test/suite/parts/t/partition_auto_increment_blackhole.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test	2008-09-08 13:30:01 +0000
@@ -0,0 +1,35 @@
+################################################################################
+# t/partition_auto_increment_blackhole.test                                    #
+#                                                                              #
+# Purpose:                                                                     #
+#  Tests around auto increment column                                          #
+#        Blackhole branch                                                      #
+#                                                                              #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ                                                    #
+# Original Date: 2008-09-02                                                    #
+# Change Author:                                                               #
+# Change Date:                                                                 #
+# Change:                                                                      #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+#       TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+#       THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+--source include/have_blackhole.inc
+
+##### Storage engine to be tested
+let $engine= 'Blackhole';
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+

=== added file 'mysql-test/suite/parts/t/partition_auto_increment_innodb.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_innodb.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_innodb.test	2008-09-08 13:30:01 +0000
@@ -0,0 +1,35 @@
+################################################################################
+# t/partition_auto_increment_innodb.test                                       #
+#                                                                              #
+# Purpose:                                                                     #
+#  Tests around auto increment column                                          #
+#        InnoDB branch                                                         #
+#                                                                              #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ                                                    #
+# Original Date: 2008-02-12                                                    #
+# Change Author:                                                               #
+# Change Date:                                                                 #
+# Change:                                                                      #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+#       TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+#       THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+
+##### Storage engine to be tested
+let $engine= 'InnoDB';
+--source include/have_innodb.inc
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+

=== removed file 'mysql-test/suite/parts/t/partition_auto_increment_innodb.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_innodb.test	2008-04-25 11:37:58 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_innodb.test	1970-01-01 00:00:00 +0000
@@ -1,35 +0,0 @@
-################################################################################
-# t/partition_auto_increment_innodb.test                                       #
-#                                                                              #
-# Purpose:                                                                     #
-#  Tests around auto increment column                                          #
-#        InnoDB branch                                                         #
-#                                                                              #
-#------------------------------------------------------------------------------#
-# Original Author: MattiasJ                                                    #
-# Original Date: 2008-02-12                                                    #
-# Change Author:                                                               #
-# Change Date:                                                                 #
-# Change:                                                                      #
-################################################################################
-
-#
-# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
-#       TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
-#       THE SOURCED FILES ONLY.
-#
-
-# The server must support partitioning.
---source include/have_partition.inc
-
-#------------------------------------------------------------------------------#
-# Engine specific settings and requirements
-
-##### Storage engine to be tested
-let $engine= 'InnoDB';
---source include/have_innodb.inc
-
-#------------------------------------------------------------------------------#
-# Execute the tests to be applied to all storage engines
---source suite/parts/inc/partition_auto_increment.inc
-

=== added file 'mysql-test/suite/parts/t/partition_auto_increment_memory.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_memory.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_memory.test	2008-09-08 13:30:01 +0000
@@ -0,0 +1,34 @@
+################################################################################
+# t/partition_auto_increment_memory.test                                       #
+#                                                                              #
+# Purpose:                                                                     #
+#  Tests around auto increment column                                          #
+#        Memory branch                                                         #
+#                                                                              #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ                                                    #
+# Original Date: 2008-02-12                                                    #
+# Change Author:                                                               #
+# Change Date:                                                                 #
+# Change:                                                                      #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+#       TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+#       THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+
+##### Storage engine to be tested
+let $engine= 'Memory';
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+

=== added file 'mysql-test/suite/parts/t/partition_auto_increment_myisam.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_myisam.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_myisam.test	2008-09-08 13:30:01 +0000
@@ -0,0 +1,34 @@
+################################################################################
+# t/partition_auto_increment_myisam.test                                       #
+#                                                                              #
+# Purpose:                                                                     #
+#  Tests around auto increment column                                          #
+#        MyISAM branch                                                         #
+#                                                                              #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ                                                    #
+# Original Date: 2008-02-12                                                    #
+# Change Author:                                                               #
+# Change Date:                                                                 #
+# Change:                                                                      #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+#       TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+#       THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+
+##### Storage engine to be tested
+let $engine= 'MyISAM';
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+

=== removed file 'mysql-test/suite/parts/t/partition_auto_increment_myisam.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_myisam.test	2008-04-25 11:37:58 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_myisam.test	1970-01-01 00:00:00 +0000
@@ -1,34 +0,0 @@
-################################################################################
-# t/partition_auto_increment_myisam.test                                       #
-#                                                                              #
-# Purpose:                                                                     #
-#  Tests around auto increment column                                          #
-#        MyISAM branch                                                         #
-#                                                                              #
-#------------------------------------------------------------------------------#
-# Original Author: MattiasJ                                                    #
-# Original Date: 2008-02-12                                                    #
-# Change Author:                                                               #
-# Change Date:                                                                 #
-# Change:                                                                      #
-################################################################################
-
-#
-# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
-#       TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
-#       THE SOURCED FILES ONLY.
-#
-
-# The server must support partitioning.
---source include/have_partition.inc
-
-#------------------------------------------------------------------------------#
-# Engine specific settings and requirements
-
-##### Storage engine to be tested
-let $engine= 'MyISAM';
-
-#------------------------------------------------------------------------------#
-# Execute the tests to be applied to all storage engines
---source suite/parts/inc/partition_auto_increment.inc
-

=== added file 'mysql-test/suite/parts/t/partition_auto_increment_ndb.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_ndb.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_ndb.test	2008-09-08 13:30:01 +0000
@@ -0,0 +1,41 @@
+################################################################################
+# t/partition_auto_increment_ndb.test                                          #
+#                                                                              #
+# Purpose:                                                                     #
+#  Tests around auto increment column                                          #
+#        NDB branch                                                            #
+#                                                                              #
+# Note: NDB behavior for auto_increment on secondary column in                 #
+#       multi-column-index is NOT like MyISAM, instead it uses the same        #
+#       behavior as if it was the primary column.                              #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ                                                    #
+# Original Date: 2008-09-02                                                    #
+# Change Author:                                                               #
+# Change Date:                                                                 #
+# Change:                                                                      #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+#       TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+#       THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+--source include/have_ndb.inc
+
+##### Storage engine to be tested
+let $engine= 'NDB';
+connection default;
+#enable hash partitioning
+SET new=on;
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+

=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc	2008-09-20 07:22:37 +0000
+++ b/sql/ha_partition.cc	2008-10-01 12:00:30 +0000
@@ -2515,7 +2515,10 @@ int ha_partition::open(const char *name,
                                    alloc_root(&table_share->mem_root,
                                               sizeof(HA_DATA_PARTITION));
     if (!ha_data)
+    {
+      pthread_mutex_unlock(&table_share->LOCK_ha_data);
       goto err_handler;
+    }
     DBUG_PRINT("info", ("table_share->ha_data 0x%p", ha_data));
     bzero(ha_data, sizeof(HA_DATA_PARTITION));
   }
@@ -2933,7 +2936,7 @@ int ha_partition::write_row(uchar * buf)
   tmp_disable_binlog(thd); /* Do not replicate the low-level changes. */
   error= m_file[part_id]->ha_write_row(buf);
   if (have_auto_increment && !table->s->next_number_keypart)
-    set_auto_increment_if_higher();
+    set_auto_increment_if_higher(table->next_number_field->val_int());
   reenable_binlog(thd);
 exit:
   table->timestamp_field_type= orig_timestamp_type;
@@ -2960,13 +2963,6 @@ exit:
     Keep in mind that the server can do updates based on ordering if an
     ORDER BY clause was used. Consecutive ordering is not guarenteed.
 
-    Currently new_data will not have an updated auto_increament record, or
-    and updated timestamp field. You can do these for partition by doing these:
-    if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE)
-      table->timestamp_field->set_time();
-    if (table->next_number_field && record == table->record[0])
-      update_auto_increment();
-
     Called from sql_select.cc, sql_acl.cc, sql_update.cc, and sql_insert.cc.
     new_data is always record[0]
     old_data is normally record[1] but may be anything
@@ -3009,10 +3005,12 @@ int ha_partition::update_row(const uchar
       table_share->ha_data->next_auto_inc_val if needed.
       (not to be used if auto_increment on secondary field in a multi-
       column index)
+      mysql_update does not set table->next_number_field, so we use
+      table->found_next_number_field instead.
     */
-    if (table->next_number_field && new_data == table->record[0] &&
+    if (table->found_next_number_field && new_data == table->record[0] &&
         !table->s->next_number_keypart)
-      set_auto_increment_if_higher();
+      set_auto_increment_if_higher(table->found_next_number_field->val_int());
     reenable_binlog(thd);
     goto exit;
   }
@@ -3022,6 +3020,9 @@ int ha_partition::update_row(const uchar
 			old_part_id, new_part_id));
     tmp_disable_binlog(thd); /* Do not replicate the low-level changes. */
     error= m_file[new_part_id]->ha_write_row(new_data);
+    if (table->found_next_number_field && new_data == table->record[0] &&
+        !table->s->next_number_keypart)
+      set_auto_increment_if_higher(table->found_next_number_field->val_int());
     reenable_binlog(thd);
     if (error)
       goto exit;
@@ -5989,6 +5990,7 @@ void ha_partition::get_auto_increment(ul
       if (first_value_part == ~(ulonglong)(0)) // error in one partition
       {
         *first_value= first_value_part;
+        /* log that the error was between table/partition handler */
         sql_print_error("Partition failed to reserve auto_increment value");
         unlock_auto_increment();
         DBUG_VOID_RETURN;

=== modified file 'sql/ha_partition.h'
--- a/sql/ha_partition.h	2008-09-20 07:22:37 +0000
+++ b/sql/ha_partition.h	2008-10-01 12:00:30 +0000
@@ -881,9 +881,8 @@ private:
       auto_increment_lock= FALSE;
     }
   }
-  virtual void set_auto_increment_if_higher()
+  virtual void set_auto_increment_if_higher(const ulonglong nr)
   {
-    ulonglong nr= table->next_number_field->val_int();
     HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
     lock_auto_increment();
     /* must check when the mutex is taken */