Below is the list of changes that have just been committed into a local
5.1 repository of pcrews. When pcrews does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2008-05-09 15:35:46-04:00, pcrews@stripped +28 -0
Bug#35744 - Test 'partition_alter1_innodb' times out on multiple platforms
Broke up the test into 3 smaller parts to reduce single-test runtime
Total runtime is approx. the same, but individual tests run faster
than a single large test.
Re-recorded .result files for all 3 engines, and re-enabled ndb variant of the test.
*Might* need to disable ndb version, but will need to see it's performance on
PushBuild. Runs ok on developer machines.
Reworked Changeset due to dev machine problem.
BitKeeper/deleted/.del-partition_alter1.inc@stripped, 2008-05-08 10:26:25-04:00,
pcrews@stripped +0 -0
Delete: mysql-test/suite/parts/inc/partition_alter1.inc
BitKeeper/deleted/.del-partition_alter1_innodb.result@stripped, 2008-05-08 10:25:57-04:00,
pcrews@stripped +0 -0
Delete: mysql-test/suite/parts/r/partition_alter1_innodb.result
BitKeeper/deleted/.del-partition_alter1_innodb.test@stripped, 2008-05-08 10:25:37-04:00,
pcrews@stripped +0 -0
Delete: mysql-test/suite/parts/t/partition_alter1_innodb.test
BitKeeper/deleted/.del-partition_alter1_myisam.result@stripped, 2008-05-08 10:25:57-04:00,
pcrews@stripped +0 -0
Delete: mysql-test/suite/parts/r/partition_alter1_myisam.result
BitKeeper/deleted/.del-partition_alter1_myisam.test@stripped, 2008-05-08 10:25:37-04:00,
pcrews@stripped +0 -0
Delete: mysql-test/suite/parts/t/partition_alter1_myisam.test
BitKeeper/deleted/.del-partition_alter1_ndb.test@stripped, 2008-05-08 10:25:38-04:00,
pcrews@stripped +0 -0
Delete: mysql-test/suite/parts/t/partition_alter1_ndb.test
mysql-test/suite/parts/inc/partition_alter1_1.inc@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +67 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/inc/partition_alter1_1.inc@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +0 -0
mysql-test/suite/parts/inc/partition_alter1_1_2.inc@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +54 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/inc/partition_alter1_1_2.inc@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +0 -0
mysql-test/suite/parts/inc/partition_alter1_2.inc@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +126 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/inc/partition_alter1_2.inc@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +0 -0
mysql-test/suite/parts/r/partition_alter1_1_2_innodb.result@stripped, 2008-05-09
15:35:42-04:00, pcrews@stripped +27950 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/r/partition_alter1_1_2_innodb.result@stripped, 2008-05-09
15:35:42-04:00, pcrews@stripped +0 -0
mysql-test/suite/parts/r/partition_alter1_1_2_myisam.result@stripped, 2008-05-09
15:35:42-04:00, pcrews@stripped +8511 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/r/partition_alter1_1_2_myisam.result@stripped, 2008-05-09
15:35:42-04:00, pcrews@stripped +0 -0
mysql-test/suite/parts/r/partition_alter1_1_2_ndb.result@stripped, 2008-05-09 15:35:42-04:00,
pcrews@stripped +27726 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/r/partition_alter1_1_2_ndb.result@stripped, 2008-05-09 15:35:42-04:00,
pcrews@stripped +0 -0
mysql-test/suite/parts/r/partition_alter1_1_innodb.result@stripped, 2008-05-09
15:35:42-04:00, pcrews@stripped +16416 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/r/partition_alter1_1_innodb.result@stripped, 2008-05-09
15:35:42-04:00, pcrews@stripped +0 -0
mysql-test/suite/parts/r/partition_alter1_1_myisam.result@stripped, 2008-05-09
15:35:42-04:00, pcrews@stripped +8674 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/r/partition_alter1_1_myisam.result@stripped, 2008-05-09
15:35:42-04:00, pcrews@stripped +0 -0
mysql-test/suite/parts/r/partition_alter1_1_ndb.result@stripped, 2008-05-09 15:35:42-04:00,
pcrews@stripped +24316 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/r/partition_alter1_1_ndb.result@stripped, 2008-05-09 15:35:42-04:00,
pcrews@stripped +0 -0
mysql-test/suite/parts/r/partition_alter1_2_innodb.result@stripped, 2008-05-09
15:35:42-04:00, pcrews@stripped +35866 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/r/partition_alter1_2_innodb.result@stripped, 2008-05-09
15:35:42-04:00, pcrews@stripped +0 -0
mysql-test/suite/parts/r/partition_alter1_2_myisam.result@stripped, 2008-05-09
15:35:42-04:00, pcrews@stripped +15309 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/r/partition_alter1_2_myisam.result@stripped, 2008-05-09
15:35:42-04:00, pcrews@stripped +0 -0
mysql-test/suite/parts/r/partition_alter1_2_ndb.result@stripped, 2008-05-09 15:35:42-04:00,
pcrews@stripped +35540 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/r/partition_alter1_2_ndb.result@stripped, 2008-05-09 15:35:42-04:00,
pcrews@stripped +0 -0
mysql-test/suite/parts/t/disabled.def@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +0 -1
Removed partition_alter1_ndb - test was split into 3 smaller
tests.
Didn't disable the new tests to see if their runtimes are acceptable
for PushBuild
mysql-test/suite/parts/t/partition_alter1_1_2_innodb.test@stripped, 2008-05-09
15:35:41-04:00, pcrews@stripped +83 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/t/partition_alter1_1_2_innodb.test@stripped, 2008-05-09
15:35:41-04:00, pcrews@stripped +0 -0
mysql-test/suite/parts/t/partition_alter1_1_2_myisam.test@stripped, 2008-05-09
15:35:41-04:00, pcrews@stripped +81 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/t/partition_alter1_1_2_myisam.test@stripped, 2008-05-09
15:35:41-04:00, pcrews@stripped +0 -0
mysql-test/suite/parts/t/partition_alter1_1_2_ndb.test@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +87 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/t/partition_alter1_1_2_ndb.test@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +0 -0
mysql-test/suite/parts/t/partition_alter1_1_innodb.test@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +82 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/t/partition_alter1_1_innodb.test@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +0 -0
mysql-test/suite/parts/t/partition_alter1_1_myisam.test@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +82 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/t/partition_alter1_1_myisam.test@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +0 -0
mysql-test/suite/parts/t/partition_alter1_1_ndb.test@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +87 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/t/partition_alter1_1_ndb.test@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +0 -0
mysql-test/suite/parts/t/partition_alter1_2_innodb.test@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +83 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/t/partition_alter1_2_innodb.test@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +0 -0
mysql-test/suite/parts/t/partition_alter1_2_myisam.test@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +82 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/t/partition_alter1_2_myisam.test@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +0 -0
mysql-test/suite/parts/t/partition_alter1_2_ndb.test@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +88 -0
Split partition_alter1_<eng> into 3 parts to reduce individual
test run times.
mysql-test/suite/parts/t/partition_alter1_2_ndb.test@stripped, 2008-05-09 15:35:41-04:00,
pcrews@stripped +0 -0
diff -Nrup a/mysql-test/suite/parts/inc/partition_alter1.inc
b/mysql-test/suite/parts/inc/partition_alter1.inc
--- a/mysql-test/suite/parts/inc/partition_alter1.inc 2007-11-20 10:04:02 -05:00
+++ /dev/null Wed Dec 31 16:00:00 196900
@@ -1,207 +0,0 @@
-################################################################################
-# inc/partition_alter1.inc #
-# #
-# Purpose: #
-# ADD/DROP PRIMARY KEYs and/or UNIQUE INDEXes tests on partitioned tables #
-# This routine is only useful for the partition_<feature>_<engine> tests.
#
-# #
-#------------------------------------------------------------------------------#
-# Original Author: mleich #
-# Original Date: 2006-03-05 #
-# Change Author: #
-# Change Date: #
-# Change: #
-################################################################################
-
---echo
---echo #========================================================================
---echo # 1. ALTER TABLE ADD PRIMARY KEY and/or UNIQUE INDEX
---echo #========================================================================
-# Rule: The table does not have a PRIMARY KEY or UNIQUE INDEX.
-# ---> $unique must be empty
-# ---> The PRIMARY KEY or UNIQUE INDEX to be created must contain
-# the columns used for partitioning.
---echo #------------------------------------------------------------------------
---echo # 1.1 ADD PRIMARY KEY or UNIQUE INDEX to table with one column (f_int1)
---echo # within the partitioning function
---echo #------------------------------------------------------------------------
-# Rule: Only f_int1 is used within the partitioning function
-# ---> inc/partition_alter_11.inc
-if ($do_pk_tests)
-{
- # The value of the following test is maybe covered by 1.1.3.
- if ($more_pk_ui_tests)
- {
- --echo # 1.1.1 PRIMARY KEY consisting of one column
- let $alter= ALTER TABLE t1 ADD PRIMARY KEY(f_int1);
- --source suite/parts/inc/partition_alter_11.inc
- }
- # This must fail, because PRIMARY KEY does not contain f_int1
- let $alter= ALTER TABLE t1 ADD PRIMARY KEY(f_int2);
- --source suite/parts/inc/partition_alter_11.inc
-}
-# The value of the following test is maybe covered by 1.1.4.
-if ($more_pk_ui_tests)
-{
- --echo # 1.1.2 UNIQUE INDEX consisting of one column
- let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int1);
- --source suite/parts/inc/partition_alter_11.inc
-}
-# This must fail, because UNIQUE INDEX does not contain f_int1
-let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int2);
---source suite/parts/inc/partition_alter_11.inc
-if ($do_pk_tests)
-{
- --echo # 1.1.3 PRIMARY KEY consisting of two columns
- let $alter= ALTER TABLE t1 ADD PRIMARY KEY(f_int1,f_int2);
- --source suite/parts/inc/partition_alter_11.inc
- let $alter= ALTER TABLE t1 ADD PRIMARY KEY(f_int2,f_int1);
- --source suite/parts/inc/partition_alter_11.inc
-}
---echo # 1.1.4 UNIQUE INDEX consisting of two columns
-let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int1,f_int2);
---source suite/parts/inc/partition_alter_11.inc
-let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int2,f_int1);
---source suite/parts/inc/partition_alter_11.inc
-#
---echo #------------------------------------------------------------------------
---echo # 1.2 ADD PRIMARY KEY or UNIQUE INDEX to table with two columns
---echo # (f_int1 and f_int2) within the partitioning function
---echo #------------------------------------------------------------------------
-# Rule: f_int1 and f_int2 is used within the partitioning function
-# ---> inc/partition_alter_13.inc
-if ($do_pk_tests)
-{
- --echo # 1.2.1 PRIMARY KEY consisting of two columns
- let $alter= ALTER TABLE t1 ADD PRIMARY KEY(f_int1,f_int2);
- --source suite/parts/inc/partition_alter_13.inc
- let $alter= ALTER TABLE t1 ADD PRIMARY KEY(f_int2,f_int1);
- --source suite/parts/inc/partition_alter_13.inc
-}
---echo # 1.2.2 UNIQUE INDEX consisting of two columns
-let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int1,f_int2);
---source suite/parts/inc/partition_alter_13.inc
-let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int2,f_int1);
---source suite/parts/inc/partition_alter_13.inc
-if ($do_pk_tests)
-{
- --echo # 1.2.3 PRIMARY KEY and UNIQUE INDEX consisting of two columns
- let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int1,f_int2), ADD PRIMARY
KEY(f_int2,f_int1);
- --source suite/parts/inc/partition_alter_13.inc
- let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int2,f_int1), ADD PRIMARY
KEY(f_int1,f_int2);
- --source suite/parts/inc/partition_alter_13.inc
- let $unique= ;
- --source suite/parts/inc/partition_alter_13.inc
-}
-#
-#
---echo
---echo #========================================================================
---echo # 2 DROP PRIMARY KEY or UNIQUE INDEX
---echo #========================================================================
-# Rule: The table must have a PRIMARY KEY or UNIQUE INDEX.
-# ---> $unique must not be empty
-# ---> The PRIMARY KEY or UNIQUE INDEX to be dropped must contain
-# the columns used for partitioning.
---echo #------------------------------------------------------------------------
---echo # 2.1 Partitioning function contains one column(f_int1)
---echo #------------------------------------------------------------------------
-# Rule: Only f_int1 is used within the partitioning function
-# ---> inc/partition_alter_11.inc
-# The value of the following test is maybe covered by 2.1.5.
-if ($more_pk_ui_tests)
-{
- if ($do_pk_tests)
- {
- --echo # 2.1.1 DROP PRIMARY KEY consisting of one column
- let $unique= , PRIMARY KEY(f_int1);
- let $alter= ALTER TABLE t1 DROP PRIMARY KEY;
- --source suite/parts/inc/partition_alter_11.inc
- }
- #
- --echo # 2.1.2 DROP UNIQUE INDEX consisting of one column
- let $unique= , UNIQUE INDEX uidx1 (f_int1);
- let $alter= ALTER TABLE t1 DROP INDEX uidx1;
- --source suite/parts/inc/partition_alter_11.inc
- #
- if ($do_pk_tests)
- {
- --echo # 2.1.3 DROP PRIMARY KEY consisting of two columns
- let $alter= ALTER TABLE t1 DROP PRIMARY KEY;
- let $unique= , PRIMARY KEY(f_int1,f_int2);
- --source suite/parts/inc/partition_alter_11.inc
- let $unique= , PRIMARY KEY(f_int2,f_int1);
- --source suite/parts/inc/partition_alter_11.inc
- }
- #
- --echo # 2.1.4 DROP UNIQUE INDEX consisting of two columns
- let $alter= ALTER TABLE t1 DROP INDEX uidx1;
- let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2);
- --source suite/parts/inc/partition_alter_11.inc
- let $unique= , UNIQUE INDEX uidx1 (f_int2,f_int1);
- --source suite/parts/inc/partition_alter_11.inc
- }
-#
-if ($do_pk_tests)
-{
- --echo # 2.1.5 DROP PRIMARY KEY + UNIQUE INDEX consisting of two columns
- let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2), PRIMARY KEY(f_int2,f_int1);
- let $alter= ALTER TABLE t1 DROP PRIMARY KEY, DROP INDEX uidx1;
- --source suite/parts/inc/partition_alter_11.inc
- let $unique= , UNIQUE INDEX uidx1 (f_int2,f_int1), PRIMARY KEY(f_int1,f_int2);
- let $alter= ALTER TABLE t1 DROP PRIMARY KEY, DROP INDEX uidx1;
- --source suite/parts/inc/partition_alter_11.inc
-}
-let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2), UNIQUE INDEX uidx2 (f_int2,f_int1);
-let $alter= ALTER TABLE t1 DROP INDEX uidx1, DROP INDEX uidx2;
---source suite/parts/inc/partition_alter_11.inc
-#
---echo #------------------------------------------------------------------------
---echo # 2.2 Partitioning function contains two columns (f_int1,f_int2)
---echo #------------------------------------------------------------------------
-# Rule: f_int1 and f_int2 is used within the partitioning function
-# ---> inc/partition_alter_13.inc
-if ($do_pk_tests)
-{
- --echo # 2.2.1 DROP PRIMARY KEY consisting of two columns
- let $alter= ALTER TABLE t1 DROP PRIMARY KEY;
- let $unique= , PRIMARY KEY(f_int1,f_int2);
- --source suite/parts/inc/partition_alter_13.inc
- let $unique= , PRIMARY KEY(f_int2,f_int1);
- --source suite/parts/inc/partition_alter_13.inc
-}
-#
---echo # 2.2.2 DROP UNIQUE INDEX consisting of two columns
-let $alter= ALTER TABLE t1 DROP INDEX uidx1;
-let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2);
---source suite/parts/inc/partition_alter_13.inc
-let $unique= , UNIQUE INDEX uidx1 (f_int2,f_int1);
---source suite/parts/inc/partition_alter_13.inc
-#
-if ($do_pk_tests)
-{
- --echo # 2.2.3 DROP PRIMARY KEY + UNIQUE INDEX consisting of two columns
- let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2), PRIMARY KEY(f_int2,f_int1);
- let $alter= ALTER TABLE t1 DROP PRIMARY KEY, DROP INDEX uidx1;
- --source suite/parts/inc/partition_alter_13.inc
- let $unique= , UNIQUE INDEX uidx1 (f_int2,f_int1), PRIMARY KEY(f_int1,f_int2);
- let $alter= ALTER TABLE t1 DROP PRIMARY KEY, DROP INDEX uidx1;
- --source suite/parts/inc/partition_alter_13.inc
-}
-let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2), UNIQUE INDEX uidx2 (f_int2,f_int1);
-let $alter= ALTER TABLE t1 DROP INDEX uidx1, DROP INDEX uidx2;
---source suite/parts/inc/partition_alter_13.inc
-
-if (0)
-{
---echo
---echo #========================================================================
---echo # 3. ALTER TABLE "ALTER" PRIMARY KEY
---echo # mleich: I think that an ALTER TABLE statement where a PRIMARY
---echo # KEY is dropped and recreated (with different layout) might
---echo # be of interest, if the tree containing the table data has
---echo # to be reorganized during this operation.
---echo # To be implemented
---echo #========================================================================
---echo
-}
diff -Nrup a/mysql-test/suite/parts/inc/partition_alter1_1.inc
b/mysql-test/suite/parts/inc/partition_alter1_1.inc
--- /dev/null Wed Dec 31 16:00:00 196900
+++ b/mysql-test/suite/parts/inc/partition_alter1_1.inc 2008-05-09 15:35:41 -04:00
@@ -0,0 +1,67 @@
+################################################################################
+# inc/partition_alter1_1.inc #
+# #
+# Purpose: #
+# ADD/DROP PRIMARY KEYs and/or UNIQUE INDEXes tests on partitioned tables #
+# This routine is only useful for the partition_<feature>_<engine> tests.
#
+# #
+#------------------------------------------------------------------------------#
+# Original Author: mleich #
+# Original Date: 2006-03-05 #
+# Change Author: #
+# Change Date: #
+# Change: #
+################################################################################
+
+--echo
+--echo #========================================================================
+--echo # 1. ALTER TABLE ADD PRIMARY KEY and/or UNIQUE INDEX
+--echo #========================================================================
+# Rule: The table does not have a PRIMARY KEY or UNIQUE INDEX.
+# ---> $unique must be empty
+# ---> The PRIMARY KEY or UNIQUE INDEX to be created must contain
+# the columns used for partitioning.
+--echo #------------------------------------------------------------------------
+--echo # 1.1 ADD PRIMARY KEY or UNIQUE INDEX to table with one column (f_int1)
+--echo # within the partitioning function
+--echo #------------------------------------------------------------------------
+# Rule: Only f_int1 is used within the partitioning function
+# ---> inc/partition_alter_11.inc
+if ($do_pk_tests)
+{
+ # The value of the following test is maybe covered by 1.1.3.
+ if ($more_pk_ui_tests)
+ {
+ --echo # 1.1.1 PRIMARY KEY consisting of one column
+ let $alter= ALTER TABLE t1 ADD PRIMARY KEY(f_int1);
+ --source suite/parts/inc/partition_alter_11.inc
+ }
+ # This must fail, because PRIMARY KEY does not contain f_int1
+ let $alter= ALTER TABLE t1 ADD PRIMARY KEY(f_int2);
+ --source suite/parts/inc/partition_alter_11.inc
+}
+# The value of the following test is maybe covered by 1.1.4.
+if ($more_pk_ui_tests)
+{
+ --echo # 1.1.2 UNIQUE INDEX consisting of one column
+ let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int1);
+ --source suite/parts/inc/partition_alter_11.inc
+}
+# This must fail, because UNIQUE INDEX does not contain f_int1
+let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int2);
+--source suite/parts/inc/partition_alter_11.inc
+if ($do_pk_tests)
+{
+ --echo # 1.1.3 PRIMARY KEY consisting of two columns
+ let $alter= ALTER TABLE t1 ADD PRIMARY KEY(f_int1,f_int2);
+ --source suite/parts/inc/partition_alter_11.inc
+ let $alter= ALTER TABLE t1 ADD PRIMARY KEY(f_int2,f_int1);
+ --source suite/parts/inc/partition_alter_11.inc
+}
+--echo # 1.1.4 UNIQUE INDEX consisting of two columns
+let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int1,f_int2);
+--source suite/parts/inc/partition_alter_11.inc
+let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int2,f_int1);
+--source suite/parts/inc/partition_alter_11.inc
+
+
diff -Nrup a/mysql-test/suite/parts/inc/partition_alter1_1_2.inc
b/mysql-test/suite/parts/inc/partition_alter1_1_2.inc
--- /dev/null Wed Dec 31 16:00:00 196900
+++ b/mysql-test/suite/parts/inc/partition_alter1_1_2.inc 2008-05-09 15:35:41 -04:00
@@ -0,0 +1,54 @@
+################################################################################
+# inc/partition_alter1_1_2.inc #
+# #
+# Purpose: #
+# ADD/DROP PRIMARY KEYs and/or UNIQUE INDEXes tests on partitioned tables #
+# This routine is only useful for the partition_<feature>_<engine> tests.
#
+# #
+#------------------------------------------------------------------------------#
+# Original Author: mleich #
+# Original Date: 2006-03-05 #
+# Change Author: #
+# Change Date: #
+# Change: #
+################################################################################
+
+--echo
+--echo #========================================================================
+--echo # 1. ALTER TABLE ADD PRIMARY KEY and/or UNIQUE INDEX
+--echo #========================================================================
+# Rule: The table does not have a PRIMARY KEY or UNIQUE INDEX.
+# ---> $unique must be empty
+# ---> The PRIMARY KEY or UNIQUE INDEX to be created must contain
+# the columns used for partitioning.
+#
+--echo #------------------------------------------------------------------------
+--echo # 1.2 ADD PRIMARY KEY or UNIQUE INDEX to table with two columns
+--echo # (f_int1 and f_int2) within the partitioning function
+--echo #------------------------------------------------------------------------
+# Rule: f_int1 and f_int2 is used within the partitioning function
+# ---> inc/partition_alter_13.inc
+if ($do_pk_tests)
+{
+ --echo # 1.2.1 PRIMARY KEY consisting of two columns
+ let $alter= ALTER TABLE t1 ADD PRIMARY KEY(f_int1,f_int2);
+ --source suite/parts/inc/partition_alter_13.inc
+ let $alter= ALTER TABLE t1 ADD PRIMARY KEY(f_int2,f_int1);
+ --source suite/parts/inc/partition_alter_13.inc
+}
+--echo # 1.2.2 UNIQUE INDEX consisting of two columns
+let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int1,f_int2);
+--source suite/parts/inc/partition_alter_13.inc
+let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int2,f_int1);
+--source suite/parts/inc/partition_alter_13.inc
+if ($do_pk_tests)
+{
+ --echo # 1.2.3 PRIMARY KEY and UNIQUE INDEX consisting of two columns
+ let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int1,f_int2), ADD PRIMARY
KEY(f_int2,f_int1);
+ --source suite/parts/inc/partition_alter_13.inc
+ let $alter= ALTER TABLE t1 ADD UNIQUE INDEX uidx1 (f_int2,f_int1), ADD PRIMARY
KEY(f_int1,f_int2);
+ --source suite/parts/inc/partition_alter_13.inc
+ let $unique= ;
+ --source suite/parts/inc/partition_alter_13.inc
+}
+
diff -Nrup a/mysql-test/suite/parts/inc/partition_alter1_2.inc
b/mysql-test/suite/parts/inc/partition_alter1_2.inc
--- /dev/null Wed Dec 31 16:00:00 196900
+++ b/mysql-test/suite/parts/inc/partition_alter1_2.inc 2008-05-09 15:35:41 -04:00
@@ -0,0 +1,126 @@
+################################################################################
+# inc/partition_alter1_2.inc #
+# #
+# Purpose: #
+# ADD/DROP PRIMARY KEYs and/or UNIQUE INDEXes tests on partitioned tables #
+# This routine is only useful for the partition_<feature>_<engine> tests.
#
+# #
+#------------------------------------------------------------------------------#
+# Original Author: mleich #
+# Original Date: 2006-03-05 #
+# Change Author: #
+# Change Date: #
+# Change: #
+################################################################################
+#
+--echo
+--echo #========================================================================
+--echo # 2 DROP PRIMARY KEY or UNIQUE INDEX
+--echo #========================================================================
+# Rule: The table must have a PRIMARY KEY or UNIQUE INDEX.
+# ---> $unique must not be empty
+# ---> The PRIMARY KEY or UNIQUE INDEX to be dropped must contain
+# the columns used for partitioning.
+--echo #------------------------------------------------------------------------
+--echo # 2.1 Partitioning function contains one column(f_int1)
+--echo #------------------------------------------------------------------------
+# Rule: Only f_int1 is used within the partitioning function
+# ---> inc/partition_alter_11.inc
+# The value of the following test is maybe covered by 2.1.5.
+if ($more_pk_ui_tests)
+{
+ if ($do_pk_tests)
+ {
+ --echo # 2.1.1 DROP PRIMARY KEY consisting of one column
+ let $unique= , PRIMARY KEY(f_int1);
+ let $alter= ALTER TABLE t1 DROP PRIMARY KEY;
+ --source suite/parts/inc/partition_alter_11.inc
+ }
+ #
+ --echo # 2.1.2 DROP UNIQUE INDEX consisting of one column
+ let $unique= , UNIQUE INDEX uidx1 (f_int1);
+ let $alter= ALTER TABLE t1 DROP INDEX uidx1;
+ --source suite/parts/inc/partition_alter_11.inc
+ #
+ if ($do_pk_tests)
+ {
+ --echo # 2.1.3 DROP PRIMARY KEY consisting of two columns
+ let $alter= ALTER TABLE t1 DROP PRIMARY KEY;
+ let $unique= , PRIMARY KEY(f_int1,f_int2);
+ --source suite/parts/inc/partition_alter_11.inc
+ let $unique= , PRIMARY KEY(f_int2,f_int1);
+ --source suite/parts/inc/partition_alter_11.inc
+ }
+ #
+ --echo # 2.1.4 DROP UNIQUE INDEX consisting of two columns
+ let $alter= ALTER TABLE t1 DROP INDEX uidx1;
+ let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2);
+ --source suite/parts/inc/partition_alter_11.inc
+ let $unique= , UNIQUE INDEX uidx1 (f_int2,f_int1);
+ --source suite/parts/inc/partition_alter_11.inc
+ }
+#
+if ($do_pk_tests)
+{
+ --echo # 2.1.5 DROP PRIMARY KEY + UNIQUE INDEX consisting of two columns
+ let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2), PRIMARY KEY(f_int2,f_int1);
+ let $alter= ALTER TABLE t1 DROP PRIMARY KEY, DROP INDEX uidx1;
+ --source suite/parts/inc/partition_alter_11.inc
+ let $unique= , UNIQUE INDEX uidx1 (f_int2,f_int1), PRIMARY KEY(f_int1,f_int2);
+ let $alter= ALTER TABLE t1 DROP PRIMARY KEY, DROP INDEX uidx1;
+ --source suite/parts/inc/partition_alter_11.inc
+}
+let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2), UNIQUE INDEX uidx2 (f_int2,f_int1);
+let $alter= ALTER TABLE t1 DROP INDEX uidx1, DROP INDEX uidx2;
+--source suite/parts/inc/partition_alter_11.inc
+#
+--echo #------------------------------------------------------------------------
+--echo # 2.2 Partitioning function contains two columns (f_int1,f_int2)
+--echo #------------------------------------------------------------------------
+# Rule: f_int1 and f_int2 is used within the partitioning function
+# ---> inc/partition_alter_13.inc
+if ($do_pk_tests)
+{
+ --echo # 2.2.1 DROP PRIMARY KEY consisting of two columns
+ let $alter= ALTER TABLE t1 DROP PRIMARY KEY;
+ let $unique= , PRIMARY KEY(f_int1,f_int2);
+ --source suite/parts/inc/partition_alter_13.inc
+ let $unique= , PRIMARY KEY(f_int2,f_int1);
+ --source suite/parts/inc/partition_alter_13.inc
+}
+#
+--echo # 2.2.2 DROP UNIQUE INDEX consisting of two columns
+let $alter= ALTER TABLE t1 DROP INDEX uidx1;
+let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2);
+--source suite/parts/inc/partition_alter_13.inc
+let $unique= , UNIQUE INDEX uidx1 (f_int2,f_int1);
+--source suite/parts/inc/partition_alter_13.inc
+#
+if ($do_pk_tests)
+{
+ --echo # 2.2.3 DROP PRIMARY KEY + UNIQUE INDEX consisting of two columns
+ let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2), PRIMARY KEY(f_int2,f_int1);
+ let $alter= ALTER TABLE t1 DROP PRIMARY KEY, DROP INDEX uidx1;
+ --source suite/parts/inc/partition_alter_13.inc
+ let $unique= , UNIQUE INDEX uidx1 (f_int2,f_int1), PRIMARY KEY(f_int1,f_int2);
+ let $alter= ALTER TABLE t1 DROP PRIMARY KEY, DROP INDEX uidx1;
+ --source suite/parts/inc/partition_alter_13.inc
+}
+let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2), UNIQUE INDEX uidx2 (f_int2,f_int1);
+let $alter= ALTER TABLE t1 DROP INDEX uidx1, DROP INDEX uidx2;
+--source suite/parts/inc/partition_alter_13.inc
+
+if (0)
+{
+--echo
+--echo #========================================================================
+--echo # 3. ALTER TABLE "ALTER" PRIMARY KEY
+--echo # mleich: I think that an ALTER TABLE statement where a PRIMARY
+--echo # KEY is dropped and recreated (with different layout) might
+--echo # be of interest, if the tree containing the table data has
+--echo # to be reorganized during this operation.
+--echo # To be implemented
+--echo #========================================================================
+--echo
+}
+
diff -Nrup a/mysql-test/suite/parts/r/partition_alter1_1_2_innodb.result
b/mysql-test/suite/parts/r/partition_alter1_1_2_innodb.result
--- /dev/null Wed Dec 31 16:00:00 196900
+++ b/mysql-test/suite/parts/r/partition_alter1_1_2_innodb.result 2008-05-09 15:35:42
-04:00
@@ -0,0 +1,27950 @@
+SET @max_row = 20;
+SET @@session.storage_engine = 'InnoDB';
+
+#------------------------------------------------------------------------
+# 0. Setting of auxiliary variables + Creation of an auxiliary tables
+# needed in many testcases
+#------------------------------------------------------------------------
+SELECT @max_row DIV 2 INTO @max_row_div2;
+SELECT @max_row DIV 3 INTO @max_row_div3;
+SELECT @max_row DIV 4 INTO @max_row_div4;
+SET @max_int_4 = 2147483647;
+DROP TABLE IF EXISTS t0_template;
+CREATE TABLE t0_template (
+f_int1 INTEGER,
+f_int2 INTEGER,
+f_char1 CHAR(20),
+f_char2 CHAR(20),
+f_charbig VARCHAR(1000) ,
+PRIMARY KEY(f_int1))
+ENGINE = MEMORY;
+# Logging of <max_row> INSERTs into t0_template suppressed
+DROP TABLE IF EXISTS t0_definition;
+CREATE TABLE t0_definition (
+state CHAR(3),
+create_command VARBINARY(5000),
+file_list VARBINARY(10000),
+PRIMARY KEY (state)
+) ENGINE = MEMORY;
+DROP TABLE IF EXISTS t0_aux;
+CREATE TABLE t0_aux ( f_int1 INTEGER,
+f_int2 INTEGER,
+f_char1 CHAR(20),
+f_char2 CHAR(20),
+f_charbig VARCHAR(1000) )
+ENGINE = MEMORY;
+SET AUTOCOMMIT= 1;
+SET @@session.sql_mode= '';
+# End of basic preparations needed for all tests
+#-----------------------------------------------
+
+#========================================================================
+# 1. ALTER TABLE ADD PRIMARY KEY and/or UNIQUE INDEX
+#========================================================================
+#------------------------------------------------------------------------
+# 1.2 ADD PRIMARY KEY or UNIQUE INDEX to table with two columns
+# (f_int1 and f_int2) within the partitioning function
+#------------------------------------------------------------------------
+# 1.2.1 PRIMARY KEY consisting of two columns
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (
+f_int1 INTEGER,
+f_int2 INTEGER,
+f_char1 CHAR(20),
+f_char2 CHAR(20),
+f_charbig VARCHAR(1000)
+
+)
+PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2;
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template
+WHERE f_int1 BETWEEN 1 AND @max_row_div2 - 1;
+ALTER TABLE t1 ADD PRIMARY KEY(f_int1,f_int2);
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 AND @max_row;
+# Start usability test (inc/partition_check.inc)
+create_command
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `f_int1` int(11) NOT NULL DEFAULT '0',
+ `f_int2` int(11) NOT NULL DEFAULT '0',
+ `f_char1` char(20) DEFAULT NULL,
+ `f_char2` char(20) DEFAULT NULL,
+ `f_charbig` varchar(1000) DEFAULT NULL,
+ PRIMARY KEY (`f_int1`,`f_int2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1 + f_int2)
PARTITIONS 2 */
+
+# check prerequisites-1 success: 1
+# check COUNT(*) success: 1
+# check MIN/MAX(f_int1) success: 1
+# check MIN/MAX(f_int2) success: 1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
+WHERE f_int1 IN (2,3);
+ERROR 23000: Can't write; duplicate key in table 't1'
+# check prerequisites-3 success: 1
+# INFO: f_int1 AND/OR f_int2 AND/OR (f_int1,f_int2) is UNIQUE
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, 2 * @max_row + f_int1, CAST((2 * @max_row + f_int1) AS CHAR),
+CAST((2 * @max_row + f_int1) AS CHAR), 'delete me' FROM t0_template
+WHERE f_int1 IN (2,3);
+DELETE FROM t1 WHERE f_charbig = 'delete me';
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT 2 * @max_row + f_int1, f_int1, CAST((2 * @max_row + f_int1) AS CHAR),
+CAST((2 * @max_row + f_int1) AS CHAR), 'delete me' FROM t0_template
+WHERE f_int1 IN (2,3);
+DELETE FROM t1 WHERE f_charbig = 'delete me';
+# check read via f_int1 success: 1
+# check read via f_int2 success: 1
+
+# check multiple-1 success: 1
+DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
+
+# check multiple-2 success: 1
+INSERT INTO t1 SELECT * FROM t0_template
+WHERE MOD(f_int1,3) = 0;
+
+# check multiple-3 success: 1
+UPDATE t1 SET f_int1 = f_int1 + @max_row
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
+AND @max_row_div2 + @max_row_div4;
+
+# check multiple-4 success: 1
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
+AND @max_row_div2 + @max_row_div4 + @max_row;
+
+# check multiple-5 success: 1
+SELECT COUNT(*) INTO @try_count FROM t0_template
+WHERE MOD(f_int1,3) = 0
+AND f_int1 BETWEEN @max_row_div2 AND @max_row;
+SELECT COUNT(*) INTO @clash_count
+FROM t1 INNER JOIN t0_template USING(f_int1)
+WHERE MOD(f_int1,3) = 0
+AND f_int1 BETWEEN @max_row_div2 AND @max_row;
+SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+
+# check single-1 success: 1
+SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+
+# check single-2 success: 1
+SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
+SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value2
+WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
+
+# check single-3 success: 1
+SET @cur_value1= -1;
+SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value1
+WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
+
+# check single-4 success: 1
+SELECT MAX(f_int1) INTO @cur_value FROM t1;
+DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
+
+# check single-5 success: 1
+DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
+
+# check single-6 success: 1
+INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig =
'#2147483647##';
+
+# check single-7 success: 1
+DELETE FROM t1 WHERE f_charbig = '#2147483647##';
+DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
+INSERT t1 SET f_int1 = 0 , f_int2 = 0,
+f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
+f_charbig = '#NULL#';
+INSERT INTO t1
+SET f_int1 = NULL , f_int2 = -@max_row,
+f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
+f_charbig = '#NULL#';
+ERROR 23000: Column 'f_int1' cannot be null
+# check null success: 1
+DELETE FROM t1
+WHERE f_int1 = 0 AND f_int2 = 0
+AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
+AND f_charbig = '#NULL#';
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+ FROM t0_template source_tab
+WHERE MOD(f_int1,3) = 0
+AND f_int1 BETWEEN @max_row_div2 AND @max_row
+ON DUPLICATE KEY
+UPDATE f_int1 = 2 * @max_row + source_tab.f_int1,
+f_int2 = 2 * @max_row + source_tab.f_int1,
+f_charbig = 'was updated';
+
+# check unique-1-a success: 1
+
+# check unique-1-b success: 1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===')
+WHERE f_charbig = 'was updated';
+REPLACE INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, - f_int1, '', '', 'was inserted or replaced'
+ FROM t0_template source_tab
+WHERE MOD(f_int1,3) = 0 AND f_int1 BETWEEN @max_row_div2 AND @max_row;
+
+# check replace success: 1
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 AND @max_row_div2 + @max_row_div4;
+DELETE FROM t1
+WHERE f_int1 = f_int2 AND MOD(f_int1,3) = 0 AND
+f_int1 BETWEEN @max_row_div2 + @max_row_div4 AND @max_row;
+UPDATE t1 SET f_int2 = f_int1,
+f_char1 = CAST(f_int1 AS CHAR),
+f_char2 = CAST(f_int1 AS CHAR),
+f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===')
+WHERE f_charbig = 'was inserted or replaced' AND f_int1 = - f_int2;
+SET AUTOCOMMIT= 0;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+
+# check transactions-1 success: 1
+COMMIT WORK;
+
+# check transactions-2 success: 1
+ROLLBACK WORK;
+
+# check transactions-3 success: 1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+ROLLBACK WORK;
+
+# check transactions-4 success: 1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+
+# check transactions-5 success: 1
+ROLLBACK WORK;
+
+# check transactions-6 success: 1
+# INFO: Storage engine used for t1 seems to be transactional.
+COMMIT;
+
+# check transactions-7 success: 1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+SET @@session.sql_mode = 'traditional';
+SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
+'', '', 'was inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+ERROR 22012: Division by 0
+COMMIT;
+
+# check transactions-8 success: 1
+# INFO: Storage engine used for t1 seems to be able to revert
+# changes made by the failing statement.
+SET @@session.sql_mode = '';
+SET AUTOCOMMIT= 1;
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+UPDATE t1 SET f_charbig = REPEAT('b', 1000);
+
+# check special-1 success: 1
+UPDATE t1 SET f_charbig = '';
+
+# check special-2 success: 1
+UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+
+# check trigger-1 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+
+# check trigger-2 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+
+# check trigger-3 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+
+# check trigger-4 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+
+# check trigger-5 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+
+# check trigger-6 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+
+# check trigger-7 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+
+# check trigger-8 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = old.f_int1 + @max_row,
+new.f_int2 = old.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+
+# check trigger-9 success: 1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = new.f_int1 + @max_row,
+new.f_int2 = new.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+
+# check trigger-10 success: 1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+
+# check trigger-11 success: 1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_char1, f_char2, f_charbig)
+SELECT CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+
+# check trigger-12 success: 1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze note The storage engine for the table doesn't support analyze
+CHECK TABLE t1 EXTENDED;
+Table Op Msg_type Msg_text
+test.t1 check note The storage engine for the table doesn't support check
+CHECKSUM TABLE t1 EXTENDED;
+Table Checksum
+test.t1 <some_value>
+OPTIMIZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 optimize note The storage engine for the table doesn't support optimize
+# check layout success: 1
+REPAIR TABLE t1 EXTENDED;
+Table Op Msg_type Msg_text
+test.t1 repair note The storage engine for the table doesn't support repair
+# check layout success: 1
+TRUNCATE t1;
+
+# check TRUNCATE success: 1
+# check layout success: 1
+# End usability test (inc/partition_check.inc)
+DROP TABLE t1;
+CREATE TABLE t1 (
+f_int1 INTEGER,
+f_int2 INTEGER,
+f_char1 CHAR(20),
+f_char2 CHAR(20),
+f_charbig VARCHAR(1000)
+
+)
+PARTITION BY KEY(f_int1,f_int2) PARTITIONS 5;
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template
+WHERE f_int1 BETWEEN 1 AND @max_row_div2 - 1;
+ALTER TABLE t1 ADD PRIMARY KEY(f_int1,f_int2);
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 AND @max_row;
+# Start usability test (inc/partition_check.inc)
+create_command
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `f_int1` int(11) NOT NULL DEFAULT '0',
+ `f_int2` int(11) NOT NULL DEFAULT '0',
+ `f_char1` char(20) DEFAULT NULL,
+ `f_char2` char(20) DEFAULT NULL,
+ `f_charbig` varchar(1000) DEFAULT NULL,
+ PRIMARY KEY (`f_int1`,`f_int2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1,f_int2)
PARTITIONS 5 */
+
+# check prerequisites-1 success: 1
+# check COUNT(*) success: 1
+# check MIN/MAX(f_int1) success: 1
+# check MIN/MAX(f_int2) success: 1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
+WHERE f_int1 IN (2,3);
+ERROR 23000: Can't write; duplicate key in table 't1'
+# check prerequisites-3 success: 1
+# INFO: f_int1 AND/OR f_int2 AND/OR (f_int1,f_int2) is UNIQUE
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, 2 * @max_row + f_int1, CAST((2 * @max_row + f_int1) AS CHAR),
+CAST((2 * @max_row + f_int1) AS CHAR), 'delete me' FROM t0_template
+WHERE f_int1 IN (2,3);
+DELETE FROM t1 WHERE f_charbig = 'delete me';
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT 2 * @max_row + f_int1, f_int1, CAST((2 * @max_row + f_int1) AS CHAR),
+CAST((2 * @max_row + f_int1) AS CHAR), 'delete me' FROM t0_template
+WHERE f_int1 IN (2,3);
+DELETE FROM t1 WHERE f_charbig = 'delete me';
+# check read via f_int1 success: 1
+# check read via f_int2 success: 1
+
+# check multiple-1 success: 1
+DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
+
+# check multiple-2 success: 1
+INSERT INTO t1 SELECT * FROM t0_template
+WHERE MOD(f_int1,3) = 0;
+
+# check multiple-3 success: 1
+UPDATE t1 SET f_int1 = f_int1 + @max_row
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
+AND @max_row_div2 + @max_row_div4;
+
+# check multiple-4 success: 1
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
+AND @max_row_div2 + @max_row_div4 + @max_row;
+
+# check multiple-5 success: 1
+SELECT COUNT(*) INTO @try_count FROM t0_template
+WHERE MOD(f_int1,3) = 0
+AND f_int1 BETWEEN @max_row_div2 AND @max_row;
+SELECT COUNT(*) INTO @clash_count
+FROM t1 INNER JOIN t0_template USING(f_int1)
+WHERE MOD(f_int1,3) = 0
+AND f_int1 BETWEEN @max_row_div2 AND @max_row;
+SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+
+# check single-1 success: 1
+SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+
+# check single-2 success: 1
+SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
+SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value2
+WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
+
+# check single-3 success: 1
+SET @cur_value1= -1;
+SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value1
+WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
+
+# check single-4 success: 1
+SELECT MAX(f_int1) INTO @cur_value FROM t1;
+DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
+
+# check single-5 success: 1
+DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
+
+# check single-6 success: 1
+INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig =
'#2147483647##';
+
+# check single-7 success: 1
+DELETE FROM t1 WHERE f_charbig = '#2147483647##';
+DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
+INSERT t1 SET f_int1 = 0 , f_int2 = 0,
+f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
+f_charbig = '#NULL#';
+INSERT INTO t1
+SET f_int1 = NULL , f_int2 = -@max_row,
+f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
+f_charbig = '#NULL#';
+ERROR 23000: Column 'f_int1' cannot be null
+# check null success: 1
+DELETE FROM t1
+WHERE f_int1 = 0 AND f_int2 = 0
+AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
+AND f_charbig = '#NULL#';
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+ FROM t0_template source_tab
+WHERE MOD(f_int1,3) = 0
+AND f_int1 BETWEEN @max_row_div2 AND @max_row
+ON DUPLICATE KEY
+UPDATE f_int1 = 2 * @max_row + source_tab.f_int1,
+f_int2 = 2 * @max_row + source_tab.f_int1,
+f_charbig = 'was updated';
+
+# check unique-1-a success: 1
+
+# check unique-1-b success: 1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===')
+WHERE f_charbig = 'was updated';
+REPLACE INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, - f_int1, '', '', 'was inserted or replaced'
+ FROM t0_template source_tab
+WHERE MOD(f_int1,3) = 0 AND f_int1 BETWEEN @max_row_div2 AND @max_row;
+
+# check replace success: 1
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 AND @max_row_div2 + @max_row_div4;
+DELETE FROM t1
+WHERE f_int1 = f_int2 AND MOD(f_int1,3) = 0 AND
+f_int1 BETWEEN @max_row_div2 + @max_row_div4 AND @max_row;
+UPDATE t1 SET f_int2 = f_int1,
+f_char1 = CAST(f_int1 AS CHAR),
+f_char2 = CAST(f_int1 AS CHAR),
+f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===')
+WHERE f_charbig = 'was inserted or replaced' AND f_int1 = - f_int2;
+SET AUTOCOMMIT= 0;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+
+# check transactions-1 success: 1
+COMMIT WORK;
+
+# check transactions-2 success: 1
+ROLLBACK WORK;
+
+# check transactions-3 success: 1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+ROLLBACK WORK;
+
+# check transactions-4 success: 1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+
+# check transactions-5 success: 1
+ROLLBACK WORK;
+
+# check transactions-6 success: 1
+# INFO: Storage engine used for t1 seems to be transactional.
+COMMIT;
+
+# check transactions-7 success: 1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+SET @@session.sql_mode = 'traditional';
+SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
+'', '', 'was inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+ERROR 22012: Division by 0
+COMMIT;
+
+# check transactions-8 success: 1
+# INFO: Storage engine used for t1 seems to be able to revert
+# changes made by the failing statement.
+SET @@session.sql_mode = '';
+SET AUTOCOMMIT= 1;
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+UPDATE t1 SET f_charbig = REPEAT('b', 1000);
+
+# check special-1 success: 1
+UPDATE t1 SET f_charbig = '';
+
+# check special-2 success: 1
+UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+
+# check trigger-1 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = new.f_int1;
+END|
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+
+# check trigger-2 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+
+# check trigger-3 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+
+# check trigger-4 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = new.f_int1;
+END|
+UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+
+# check trigger-5 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = - old.f_int1;
+END|
+UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+
+# check trigger-6 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+
+# check trigger-7 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
+'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
+BEGIN
+UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
+f_charbig = 'updated by trigger'
+ WHERE f_int1 = - old.f_int1;
+END|
+DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+
+# check trigger-8 success: 1
+DROP TRIGGER trg_1;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = 'just inserted'
+ WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
+DELETE FROM t0_aux
+WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = old.f_int1 + @max_row,
+new.f_int2 = old.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+
+# check trigger-9 success: 1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = new.f_int1 + @max_row,
+new.f_int2 = new.f_int2 - @max_row,
+new.f_charbig = '####updated per update trigger####';
+END|
+UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+
+# check trigger-10 success: 1
+DROP TRIGGER trg_2;
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===');
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+
+# check trigger-11 success: 1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+SET new.f_int1 = @my_max1 + @counter,
+new.f_int2 = @my_min2 - @counter,
+new.f_charbig = '####updated per insert trigger####';
+SET @counter = @counter + 1;
+END|
+SET @counter = 1;
+SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
+INSERT INTO t1 (f_char1, f_char2, f_charbig)
+SELECT CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
+ORDER BY f_int1;
+DROP TRIGGER trg_3;
+
+# check trigger-12 success: 1
+DELETE FROM t1
+WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
+AND f_int2 <> CAST(f_char1 AS SIGNED INT)
+AND f_charbig = '####updated per insert trigger####';
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze note The storage engine for the table doesn't support analyze
+CHECK TABLE t1 EXTENDED;
+Table Op Msg_type Msg_text
+test.t1 check note The storage engine for the table doesn't support check
+CHECKSUM TABLE t1 EXTENDED;
+Table Checksum
+test.t1 <some_value>
+OPTIMIZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 optimize note The storage engine for the table doesn't support optimize
+# check layout success: 1
+REPAIR TABLE t1 EXTENDED;
+Table Op Msg_type Msg_text
+test.t1 repair note The storage engine for the table doesn't support repair
+# check layout success: 1
+TRUNCATE t1;
+
+# check TRUNCATE success: 1
+# check layout success: 1
+# End usability test (inc/partition_check.inc)
+DROP TABLE t1;
+CREATE TABLE t1 (
+f_int1 INTEGER,
+f_int2 INTEGER,
+f_char1 CHAR(20),
+f_char2 CHAR(20),
+f_charbig VARCHAR(1000)
+
+)
+PARTITION BY LIST(MOD(f_int1 + f_int2,4))
+(PARTITION part_3 VALUES IN (-3),
+PARTITION part_2 VALUES IN (-2),
+PARTITION part_1 VALUES IN (-1),
+PARTITION part_N VALUES IN (NULL),
+PARTITION part0 VALUES IN (0),
+PARTITION part1 VALUES IN (1),
+PARTITION part2 VALUES IN (2),
+PARTITION part3 VALUES IN (3));
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template
+WHERE f_int1 BETWEEN 1 AND @max_row_div2 - 1;
+ALTER TABLE t1 ADD PRIMARY KEY(f_int1,f_int2);
+INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
+SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template
+WHERE f_int1 BETWEEN @max_row_div2 AND @max_row;
+# Start usability test (inc/partition_check.inc)
+create_command
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `f_int1` int(11) NOT NULL DEFAULT '0',
+ `f_int2` int(11) NOT NULL DEFAULT '0',
+ `f_char1` char(20) DEFAULT NULL,
+ `f_char2` char(20) DEFAULT NULL,
+ `f_charbig` varchar(1000) DEFAULT NULL,
+ PRIMARY KEY (`f_int1`,`f_int2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (MOD(f_int1 +
f_int2,4)) (PARTITION part_3 VALUES IN (-3) ENGINE = InnoDB, PARTITION part_2 VALUES IN
(-2) ENGINE = InnoDB, PARTITION part_1 VALUES IN (-1) ENGINE = InnoDB, PARTITION part_N
VALUES IN (NULL) ENGINE = InnoDB, PARTITION part0 VALUES IN (0) ENGINE = InnoDB,
PARTITION part1 VALUES IN (1) ENGINE = InnoDB, PARTITION part2 VALUES IN (2) ENGINE =
InnoDB, PARTITION part3 VALUES IN (3) ENGINE = InnoDB) */
+
+# check prerequisites-1 success: 1
+# check COUNT(*) success: 1
+# check MIN/MAX(f_int1) success: 1
+# check MIN/MAX(f_int2) success: 1
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
+CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
+WHERE f_int1 IN (2,3);
+ERROR 23000: Can't write; duplicate key in table 't1'
+# check prerequisites-3 success: 1
+# INFO: f_int1 AND/OR f_int2 AND/OR (f_int1,f_int2) is UNIQUE
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, 2 * @max_row + f_int1, CAST((2 * @max_row + f_int1) AS CHAR),
+CAST((2 * @max_row + f_int1) AS CHAR), 'delete me' FROM t0_template
+WHERE f_int1 IN (2,3);
+DELETE FROM t1 WHERE f_charbig = 'delete me';
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT 2 * @max_row + f_int1, f_int1, CAST((2 * @max_row + f_int1) AS CHAR),
+CAST((2 * @max_row + f_int1) AS CHAR), 'delete me' FROM t0_template
+WHERE f_int1 IN (2,3);
+DELETE FROM t1 WHERE f_charbig = 'delete me';
+# check read via f_int1 success: 1
+# check read via f_int2 success: 1
+
+# check multiple-1 success: 1
+DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
+
+# check multiple-2 success: 1
+INSERT INTO t1 SELECT * FROM t0_template
+WHERE MOD(f_int1,3) = 0;
+
+# check multiple-3 success: 1
+UPDATE t1 SET f_int1 = f_int1 + @max_row
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
+AND @max_row_div2 + @max_row_div4;
+
+# check multiple-4 success: 1
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
+AND @max_row_div2 + @max_row_div4 + @max_row;
+
+# check multiple-5 success: 1
+SELECT COUNT(*) INTO @try_count FROM t0_template
+WHERE MOD(f_int1,3) = 0
+AND f_int1 BETWEEN @max_row_div2 AND @max_row;
+SELECT COUNT(*) INTO @clash_count
+FROM t1 INNER JOIN t0_template USING(f_int1)
+WHERE MOD(f_int1,3) = 0
+AND f_int1 BETWEEN @max_row_div2 AND @max_row;
+SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+
+# check single-1 success: 1
+SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
+INSERT INTO t1
+SET f_int1 = @cur_value , f_int2 = @cur_value,
+f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
+f_charbig = '#SINGLE#';
+
+# check single-2 success: 1
+SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
+SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value2
+WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
+
+# check single-3 success: 1
+SET @cur_value1= -1;
+SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
+UPDATE t1 SET f_int1 = @cur_value1
+WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
+
+# check single-4 success: 1
+SELECT MAX(f_int1) INTO @cur_value FROM t1;
+DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
+
+# check single-5 success: 1
+DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
+
+# check single-6 success: 1
+INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig =
'#2147483647##';
+
+# check single-7 success: 1
+DELETE FROM t1 WHERE f_charbig = '#2147483647##';
+DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
+INSERT t1 SET f_int1 = 0 , f_int2 = 0,
+f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
+f_charbig = '#NULL#';
+INSERT INTO t1
+SET f_int1 = NULL , f_int2 = -@max_row,
+f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
+f_charbig = '#NULL#';
+ERROR 23000: Column 'f_int1' cannot be null
+# check null success: 1
+DELETE FROM t1
+WHERE f_int1 = 0 AND f_int2 = 0
+AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
+AND f_charbig = '#NULL#';
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+ FROM t0_template source_tab
+WHERE MOD(f_int1,3) = 0
+AND f_int1 BETWEEN @max_row_div2 AND @max_row
+ON DUPLICATE KEY
+UPDATE f_int1 = 2 * @max_row + source_tab.f_int1,
+f_int2 = 2 * @max_row + source_tab.f_int1,
+f_charbig = 'was updated';
+
+# check unique-1-a success: 1
+
+# check unique-1-b success: 1
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
+f_int2 = CAST(f_char1 AS SIGNED INT),
+f_charbig = CONCAT('===',f_char1,'===')
+WHERE f_charbig = 'was updated';
+REPLACE INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, - f_int1, '', '', 'was inserted or replaced'
+ FROM t0_template source_tab
+WHERE MOD(f_int1,3) = 0 AND f_int1 BETWEEN @max_row_div2 AND @max_row;
+
+# check replace success: 1
+DELETE FROM t1
+WHERE f_int1 BETWEEN @max_row_div2 AND @max_row_div2 + @max_row_div4;
+DELETE FROM t1
+WHERE f_int1 = f_int2 AND MOD(f_int1,3) = 0 AND
+f_int1 BETWEEN @max_row_div2 + @max_row_div4 AND @max_row;
+UPDATE t1 SET f_int2 = f_int1,
+f_char1 = CAST(f_int1 AS CHAR),
+f_char2 = CAST(f_int1 AS CHAR),
+f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===')
+WHERE f_charbig = 'was inserted or replaced' AND f_int1 = - f_int2;
+SET AUTOCOMMIT= 0;
+INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
+SELECT f_int1, f_int1, '', '', 'was inserted'
+FROM t0_template source_tab
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+
+# check transactions-1 success: 1
+COMMIT WORK;
+
+# check transactions-2 success: 1
+ROLLBACK WORK;
+
+# check 