Adding upstream version 1:10.5.12.upstream/1%10.5.12upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 1152 insertions, 0 deletions

diff --git a/mysql-test/suite/parts/inc/partition_check.inc b/mysql-test/suite/parts/inc/partition_check.inc
new file mode 100644
index 00000000..ed323dfa
--- /dev/null
+++ b/mysql-test/suite/parts/inc/partition_check.inc
@@ -0,0 +1,1152 @@
+################################################################################
+# inc/partition_check.inc                                                      #
+#                                                                              #
+# Purpose:                                                                     #
+#   Do some basic usability checks on table t1.                                #
+#   This routine is only useful for the partition_<feature>_<engine> tests.    #
+#                                                                              #
+# Some Notes:                                                                  #
+#   It is intended that in many testcases (statements) more than one partition #
+#   or subpartition is affected.                                               #
+#   Without analysis of the partitioning function used during CREATE TABLE     #
+#   we cannot be 100% sure that this goal is reached.                          #
+#   But statements affecting many rows give a good probability that this       #
+#   appears.                                                                   #
+#                                                                              #
+#   It is expected that the table to be checked contains at the beginning      #
+#   of this script records following the scheme                                #
+#       f_int1  f_int2  f_char1  f_char2  f_charbig                            #
+#          1        1     '1'      '1'    '###1###'                            #
+#          2        2     '2'      '1'    '###2###'                            #
+#         ...      ...    ...      ...       ...                               #
+#          x        x     'x'      'x'    '###x###'                            #
+#   x = @max_row                                                               #
+#                                                                              #
+#   The table content must be equal to the content of the table t0_template.   #
+#   Attention: Please be careful when modiying the data.                       #
+#              Records can be deleted or inserted, but the content of the      #
+#              records after a test/testsequence should follow this scheme.    #
+#                                                                              #
+#   All checks of preceding statements via Select are so written,              #
+#   that they deliver a                                                        #
+#       # check <n> success:        1                                          #
+#   when everything is like expected.                                          #
+# - f_charbig is typically used for showing if something was changed.          #
+#                      This is useful for cleanups.                            #
+#                                                                              #
+#------------------------------------------------------------------------------#
+# Original Author: mleich                                                      #
+# Original Date:   2006-03-05                                                  #
+# Change Author:   mleich                                                      #
+# Change Date:     2007-10-08                                                  #
+# Change:          Around fix for                                              #
+#                  Bug#31243 Test "partition_basic_myisam" truncates path names#
+#                  Adjustments of expected error codes:                        #
+#                  ER_NO_PARTITION_FOR_GIVEN_VALUE is now 1525                 #
+#                  ER_SAME_NAME_PARTITION is now 1516                          #
+################################################################################
+
+
+--echo # Start usability test (inc/partition_check.inc)
+# Print the CREATE TABLE STATEMENT and store the current layout of the table
+--source suite/parts/inc/partition_layout_check1.inc
+
+
+#-------------------------------------------------------------------------------
+## 1.   Check the prerequisites for the following tests
+#             (sideeffect some SELECT functionality is also tested)
+#       Determine if we have PRIMARY KEYs or UNIQUE INDEXes
+## 1.1  Check if the content of the records is like expected
+#       Sideeffect: mass SELECT, all records/partitions/subpartitions have to be
+#                   read, because at least f_charbig is not part of any
+#                   INDEX/PRIMARY KEY
+if ($no_debug)
+{
+--disable_query_log
+}
+let $my_stmt= SELECT COUNT(*) <> 0 INTO @aux FROM t1
+WHERE f_int1 <> f_int2 OR f_char1 <> CAST(f_int1 AS CHAR) OR f_char1 <> f_char2
+   OR f_charbig <> CONCAT('===',f_char1,'===')
+   OR f_int1 IS NULL OR f_int2 IS NULL OR f_char1 IS NULL OR f_char2 IS NULL
+   OR f_charbig IS NULL;
+eval $my_stmt;
+let $run= `SELECT @aux`;
+--enable_query_log
+if ($run)
+{
+   --echo #      Prerequisites for following tests not fullfilled.
+   --echo #      The content of the table t1 is unexpected
+   eval $my_stmt;
+   SELECT @aux;
+   --echo #      Sorry, have to abort.
+   exit;
+}
+# Give a success message like in the other following tests
+--echo # check prerequisites-1 success:    1
+#-------------------------------------------------------------------------------
+## 1.2  Check if the number of records and the maximum and minimum values are
+#       like expected
+#       Sideeffect: Check
+#            - COUNT(*)
+#            - MIN/MAX on all columns possibly used in part. function
+#              The optimizer might decide to run on INDEX only, if available.
+#
+## 1.2.1 Check COUNT(*)
+if ($no_debug)
+{
+--disable_query_log
+}
+let $my_stmt= SELECT COUNT(*) <> @max_row INTO @aux FROM t1;
+let $run= `SELECT @aux`;
+--enable_query_log
+if ($run)
+{
+   --echo #      Prerequisites for following tests not fullfilled.
+   --echo #      The content of the table t1 is unexpected
+   eval $my_stmt;
+   SELECT @aux;
+   --echo #      Sorry, have to abort.
+   exit;
+}
+# Give a success message like in the other following tests
+--echo # check COUNT(*) success:    1
+## 1.2.2 Check MAX(f_int1),MIN(f_int1)
+if ($no_debug)
+{
+--disable_query_log
+}
+let $my_stmt= SELECT MIN(f_int1) <> 1 AND MAX(f_int1) <> @max_row INTO @aux
+FROM t1;
+let $run= `SELECT @aux`;
+--enable_query_log
+if ($run)
+{
+   --echo #      Prerequisites for following tests not fullfilled.
+   --echo #      The content of the table t1 is unexpected
+   eval $my_stmt;
+   SELECT @aux;
+   --echo #      Sorry, have to abort.
+   exit;
+}
+# Give a success message like in the other following tests
+--echo # check MIN/MAX(f_int1) success:    1
+## 1.2.3 Check MAX(f_int2),MIN(f_int2)
+if ($no_debug)
+{
+--disable_query_log
+}
+let $my_stmt= SELECT MIN(f_int2) <> 1 AND MAX(f_int2) <> @max_row INTO @aux
+FROM t1;
+let $run= `SELECT @aux`;
+--enable_query_log
+if ($run)
+{
+   --echo #      Prerequisites for following tests not fullfilled.
+   --echo #      The content of the table t1 is unexpected
+   eval $my_stmt;
+   SELECT @aux;
+   --echo #      Sorry, have to abort.
+   exit;
+}
+# Give a success message like in the other following tests
+--echo # check MIN/MAX(f_int2) success:    1
+
+#-------------------------------------------------------------------------------
+## 1.3  Check, if f_int1 and/or f_char2 and/or (f_char1,f_char2) is UNIQUE
+#       by testing if any DUPLICATE KEY might appear
+#       Note: INFORMATION_SCHEMA SELECTs could be also used, but testing the
+#             table via INSERT and SELECT is better because is stresses the
+#             partitioning mechanism.
+#       Sideeffect: Attempt to INSERT one record
+#             DUPLICATE KEY will appear if we have UNIQUE columns
+#             ER_DUP_KEY, ER_DUP_ENTRY
+--disable_abort_on_error
+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);
+--enable_abort_on_error
+if ($no_debug)
+{
+--disable_query_log
+}
+eval SET @my_errno = $mysql_errno;
+let $run_delete= `SELECT @my_errno = 0`;
+let $any_unique= `SELECT @my_errno IN ($ER_DUP_KEY,$ER_DUP_ENTRY)`;
+# DEBUG eval SELECT $run_delete AS run_delete, $any_unique AS any_unique,
+#                   @my_errno AS sql_errno;
+if (`SELECT @my_errno NOT IN (0,$ER_DUP_KEY,$ER_DUP_ENTRY)`)
+{
+   --echo #      The last command got an unexpected error response.
+   --echo #      Expected/handled SQL codes are 0,$ER_DUP_KEY,$ER_DUP_ENTRY
+   SELECT '#      SQL code we got was: ' AS "", @my_errno AS "";
+   --echo #      Sorry, have to abort.
+   exit;
+   --echo
+}
+# Give a success message like in the other following tests
+--echo # check prerequisites-3 success:    1
+--enable_query_log
+# DEBUG eval SELECT $run_delete AS run_delete, $any_unique AS any_unique;
+if ($run_delete)
+{
+   # INSERT was successful -> DELETE this new record
+   DELETE FROM t1 WHERE f_charbig = 'delete me';
+   --echo # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
+}
+if ($any_unique)
+{
+   --echo # INFO: f_int1 AND/OR f_int2 AND/OR (f_int1,f_int2) is UNIQUE
+
+   ## 1.3.1  Check, if f_int1 is UNIQUE
+   #       Sideeffect: Attempt to INSERT one record
+   #             DUPLICATE KEY will appear if we have UNIQUE columns
+   #             ER_DUP_KEY, ER_DUP_ENTRY
+   --disable_abort_on_error
+   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);
+   --enable_abort_on_error
+   if ($no_debug)
+   {
+   --disable_query_log
+   }
+   eval SET @my_errno = $mysql_errno;
+   let $run_delete= `SELECT @my_errno = 0`;
+   let $f_int1_is_unique= `SELECT @my_errno IN ($ER_DUP_KEY,$ER_DUP_ENTRY)`;
+   # DEBUG eval SELECT $run_delete AS run_delete, $f_int1_is_unique AS any_unique,
+   #                   @my_errno AS sql_errno;
+   if (`SELECT @my_errno NOT IN (0,$ER_DUP_KEY,$ER_DUP_ENTRY)`)
+   {
+      --echo #      The last command got an unexpected error response.
+      --echo #      Expected/handled SQL codes are 0,$ER_DUP_KEY,$ER_DUP_ENTRY
+      SELECT '#      SQL code we got was: ' AS "", @my_errno AS "";
+      --echo #      Sorry, have to abort.
+      exit;
+      --echo
+   }
+   --enable_query_log
+   if ($f_int1_is_unique)
+   {
+      --echo # INFO: f_int1 is UNIQUE
+   }
+   if ($run_delete)
+   {
+      # INSERT was successful -> DELETE this new record
+      DELETE FROM t1 WHERE f_charbig = 'delete me';
+   }
+
+   ## 1.3.2  Check, if f_int2 is UNIQUE (get ER_DUP_KEY or ER_DUP_ENTRY
+   --disable_abort_on_error
+   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);
+   --enable_abort_on_error
+   if ($no_debug)
+   {
+   --disable_query_log
+   }
+   eval SET @my_errno = $mysql_errno;
+   let $run_delete= `SELECT @my_errno = 0`;
+   let $f_int1_is_unique= `SELECT @my_errno IN ($ER_DUP_KEY,$ER_DUP_ENTRY)`;
+   # DEBUG eval SELECT $run_delete AS run_delete, $f_int1_is_unique AS any_unique,
+   #                   @my_errno AS sql_errno;
+   if (`SELECT @my_errno NOT IN (0,$ER_DUP_KEY,$ER_DUP_ENTRY)`)
+   {
+      --echo #      The last command got an unexpected error response.
+      --echo #      Expected/handled SQL codes are 0,$ER_DUP_KEY,$ER_DUP_ENTRY
+      SELECT '#      SQL code we got was: ' AS "", @my_errno AS "";
+      --echo #      Sorry, have to abort.
+      exit;
+      --echo
+   }
+   if ($f_int2_is_unique)
+   {
+      --echo # INFO: f_int2 is UNIQUE
+   }
+   --enable_query_log
+   if ($run_delete)
+   {
+      # INSERT was successful -> DELETE this new record
+      DELETE FROM t1 WHERE f_charbig = 'delete me';
+   }
+}
+
+
+#-------------------------------------------------------------------------------
+## 2.   Read the table row by row
+#       Note: There were crashes in history when reading a partitioned table
+#             PRIMARY KEY AND/OR UNIQUE INDEXes
+## 2.1  Read all existing and some not existing records of table
+#       per f_int1 used in partitioning function
+let $col_to_check= f_int1;
+--source suite/parts/inc/partition_check_read.inc
+## 2.2  Read all existing and some not existing records of table
+#       per f_int2 used in partitioning function
+let $col_to_check= f_int2;
+--source suite/parts/inc/partition_check_read.inc
+
+
+#-------------------------------------------------------------------------------
+# 3    Some operations with multiple records
+# 3.1  Select on "full" table
+if ($no_debug)
+{
+--disable_query_log
+}
+SELECT '# check multiple-1 success: ' AS "",COUNT(*) = @max_row AS "" FROM t1;
+--enable_query_log
+#
+# 3.2  (mass) DELETE of @max_row_div3 records
+DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
+if ($no_debug)
+{
+--disable_query_log
+}
+SELECT '# check multiple-2 success: ' AS "",COUNT(*) = @max_row - @max_row_div3 AS "" FROM t1;
+--enable_query_log
+#
+# 3.3  (mass) Insert of @max_row_div3 records
+#             (Insert the records deleted in 3.2)
+INSERT INTO t1 SELECT * FROM t0_template
+WHERE MOD(f_int1,3) = 0;
+#      Check of preceding statement via Select
+if ($no_debug)
+{
+--disable_query_log
+}
+SELECT '# check multiple-3 success: ' AS "",
+(COUNT(*) = @max_row) AND (MIN(f_int1) = 1) AND (MAX(f_int1) = @max_row) AS ""
+FROM t1;
+--enable_query_log
+# DEBUG SELECT COUNT(*),MIN(f_int1),MAX(f_int1) FROM t1;
+#
+# 3.4  (mass) Update @max_row_div4 * 2 + 1 records
+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 of preceding statement via Select
+if ($no_debug)
+{
+--disable_query_log
+}
+SELECT '# check multiple-4 success: ' AS "",(COUNT(*) = @max_row) AND (MIN(f_int1) = 1) AND
+(MAX(f_int1) = @max_row_div2 + @max_row_div4 + @max_row ) AS "" FROM t1;
+--enable_query_log
+# DEBUG SELECT COUNT(*),MIN(f_int1),MAX(f_int1) FROM t1;
+#
+# 3.5  (mass) Delete @max_row_div4 * 2 + 1 records
+#             (Delete the records updated in 3.4)
+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 of preceding statement via Select
+if ($no_debug)
+{
+--disable_query_log
+}
+SELECT '# check multiple-5 success: ' AS "",
+(COUNT(*) = @max_row - @max_row_div4 - @max_row_div4 - 1)
+AND (MIN(f_int1) = 1) AND (MAX(f_int1) = @max_row) AS "" FROM t1;
+--enable_query_log
+# DEBUG SELECT COUNT(*),MIN(f_int1),MAX(f_int1) FROM t1;
+
+#-------------------------------------------------------------------------------
+# Attention: After this section all modification on the table have to be reverted !
+#            Current content of t1 follows the rule:
+#                    <value>,<value>,'<value>','<value>',===<value>===
+#            <value> contains all INTEGER values
+#                    between 1 and @max_row_div2 - @max_row_div4 - 1
+#                    and
+#                    between @max_row_div2 + @max_row_div4 + 1 and @max_row
+#            With other words the values between @max_row_div2 - @max_row_div4
+#            and @max_row_div2 + @max_row_div4 are "missing".
+#-------------------------------------------------------------------------------
+# The following is only needed for tests of UNIQUE CONSTRAINTs.
+if ($any_unique)
+{
+   # Calculate the number of records, where we will try INSERT ..... or REPLACE
+   SELECT COUNT(*) INTO @try_count FROM t0_template
+   WHERE MOD(f_int1,3) = 0
+     AND f_int1 BETWEEN @max_row_div2 AND @max_row;
+   #
+   # Calculate the number of records, where we will get DUPLICATE KEY
+   #           f_int1 is sufficient for calculating this, because 1.1
+   #           checks, that f_int1 = f_int2 is valid for all rows.
+   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;
+   if ($debug)
+   {
+      SELECT @try_count, @clash_count;
+   }
+}
+
+
+#-------------------------------------------------------------------------------
+# 4    Some operations with single records
+# 4.1  Insert one record with a value for f_int1 which is lower than in all
+#      existing records.
+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 of preceding statement via Select
+if ($no_debug)
+{
+--disable_query_log
+}
+SELECT '# check single-1 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
+WHERE f_int1 = @cur_value AND f_int2 = @cur_value
+  AND f_char1 = CAST(@cur_value AS CHAR)
+  AND f_char2 = CAST(@cur_value AS CHAR) AND f_charbig = '#SINGLE#';
+--enable_query_log
+#
+# 4.2  Insert one record with a value for f_int1 which is higher than in all
+#      existing records.
+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 of preceding statement via Select
+if ($no_debug)
+{
+--disable_query_log
+}
+SELECT '# check single-2 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
+WHERE f_int1 = @cur_value AND f_int2 = @cur_value
+  AND f_char1 = CAST(@cur_value AS CHAR)
+  AND f_char2 = CAST(@cur_value AS CHAR) AND f_charbig = '#SINGLE#';
+--enable_query_log
+#
+# 4.3  Update one record. The value of f_int1 is altered from the lowest to
+#      the highest value of all existing records.
+#      If f_int1 is used for the partitioning expression a movement of the
+#      record to another partition/subpartition might appear.
+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 of preceding statement via Select
+if ($no_debug)
+{
+--disable_query_log
+}
+SELECT '# check single-3 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
+WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
+--enable_query_log
+#
+# 4.4  Update one record. The value of f_int1 is altered from the highest value
+#      to a value lower than in all existing records.
+#      If f_int1 is used for the partitioning expression a movement of the
+#      record to another partition/subpartition might appear.
+#      f_int1 gets the delicate value '-1'.
+SET @cur_value1= -1;
+SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
+# Bug#15968: Partitions: crash when INSERT with f_int1 = -1 into PARTITION BY HASH(f_int1)
+UPDATE t1 SET f_int1 = @cur_value1
+WHERE  f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
+#      Check of preceding statement via Select
+if ($no_debug)
+{
+--disable_query_log
+}
+SELECT '# check single-4 success: ' AS "",COUNT(*) AS "" FROM t1
+WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
+--enable_query_log
+#
+# 4.5  Delete the record with the highest value of f_int1.
+SELECT MAX(f_int1) INTO @cur_value FROM t1;
+DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
+#      Check of preceding statements via Select
+if ($no_debug)
+{
+--disable_query_log
+}
+SELECT '# check single-5 success: ' AS "",COUNT(*) = 0 AS "" FROM t1
+WHERE f_charbig = '#SINGLE#' AND f_int1 = f_int1 = @cur_value;
+--enable_query_log
+#
+# 4.6  Delete the record with f_int1 = -1
+DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
+#      Check of preceding statements via Select
+if ($no_debug)
+{
+--disable_query_log
+}
+SELECT '# check single-6 success: ' AS "",COUNT(*) = 0 AS "" FROM t1
+WHERE f_charbig = '#SINGLE#' AND f_int1 IN (-1,@cur_value);
+--enable_query_log
+#
+# 4.7  Insert one record with such a big value for f_int1, so that in case
+#      - f_int1 is used within the partitioning algorithm
+#      - we use range partitioning
+#      we get error ER_NO_PARTITION_FOR_GIVEN_VALUE
+#         "Table has no partition for value ...."
+#      or ER_SAME_NAME_PARTITION
+--disable_abort_on_error
+eval INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#$max_int_4##';
+--enable_abort_on_error
+if ($no_debug)
+{
+--disable_query_log
+}
+eval SET @my_errno = $mysql_errno;
+if (`SELECT @my_errno NOT IN (0,$ER_SAME_NAME_PARTITION,$ER_NO_PARTITION_FOR_GIVEN_VALUE)`)
+{
+   --echo #      The last command got an unexpected error response.
+   --echo #      Expected/handled SQL codes are 0,$ER_SAME_NAME_PARTITION,$ER_NO_PARTITION_FOR_GIVEN_VALUE
+   SELECT '#      SQL code we got was: ' AS "", @my_errno AS "";
+   --echo #      Sorry, have to abort.
+   exit;
+   --echo
+}
+#      Check of preceding statement via Select, if the INSERT was successful
+let $run= `SELECT @my_errno = 0`;
+if ($run)
+{
+# Attention: There are some tests where the column type is changed from
+#            INTEGER to MEDIUMINT. MEDIUMINT has a smaller range and the
+#            inserted value is automatically adjusted to the maximum value
+#            of the data type.
+#            that's the reason why we cannot use WHERE <column> = @max_int_4 here.
+#
+eval SELECT '# check single-7 success: ' AS "",
+COUNT(*) = 1 AS "" FROM t1 WHERE f_charbig = '#$max_int_4##';
+# Revert this modification
+--enable_query_log
+eval DELETE FROM t1 WHERE f_charbig = '#$max_int_4##';
+}
+--enable_query_log
+
+
+#-------------------------------------------------------------------------------
+# 5    Experiments with NULL
+# If the result of the partitioning function IS NULL partitioning treats
+# this record as if the the result of the partitioning function is
+#      MySQL 5.1 <  March 2006 : zero
+#      MySQL 5.1 >= March 2006 : LONGLONG_MIN
+# Let's INSERT a record where the result of the partitioning function is
+# probably (depends on function currently used) zero and look if there are
+# any strange effects during the execution of the next statements.
+# Bug#18659: Partitions: wrong result on WHERE <col. used in part. function> IS NULL
+DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
+# Attention: Zero should be tested
+INSERT t1 SET f_int1 = 0 , f_int2 = 0,
+                   f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
+                   f_charbig = '#NULL#';
+# 5.1  Insert one record with f_int1 IS NULL.
+#      f1 "=" NULL is a delicate value which might stress the partitioning
+#      mechanism if the result of the expression in the partitioning algorithm
+#      becomes NULL.
+#      This INSERT will fail, if f_int1 is PRIMARY KEY or UNIQUE INDEX
+#      with ER_BAD_NULL_ERROR.
+--disable_abort_on_error
+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#';
+#      Some other NULL experiments if preceding INSERT was successfull
+--enable_abort_on_error
+if ($no_debug)
+{
+--disable_query_log
+}
+eval SET @my_errno = $mysql_errno;
+let $run= `SELECT @my_errno = 0`;
+if (`SELECT @my_errno NOT IN (0,$ER_BAD_NULL_ERROR)`)
+{
+   --echo #      The last command got an unexpected error response.
+   --echo #      Expected/handled SQL codes are 0,$ER_BAD_NULL_ERROR
+   SELECT '#      SQL code we got was: ' AS "", @my_errno AS "";
+   --echo #      Sorry, have to abort.
+   --echo #      Please check the error name to number mapping in inc/partition.pre.
+   exit;
+   --echo
+}
+--enable_query_log
+# Give a success message like in the other following tests
+--echo # check null success:    1
+# The following checks do not make sense if f_int1 cannot be NULL
+if ($run)
+{
+#      Check of preceding statement via Select
+if ($no_debug)
+{
+--disable_query_log
+}
+# Bug#17432: Partitions: wrong result, SELECT ... where <column> is null
+SELECT '# check null-1 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
+WHERE f_int1 IS NULL AND f_charbig = '#NULL#';
+--enable_query_log
+#
+# 5.2  Update of f_int1 from NULL to negative value
+# Bug#17432: Partitions: wrong result, SELECT ... where <column> is null
+UPDATE t1 SET f_int1 = -@max_row
+WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+  AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+if ($no_debug)
+{
+--disable_query_log
+}
+#      Check of preceding statement via Select
+SELECT '# check null-2 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
+WHERE f_int1 = -@max_row AND f_charbig = '#NULL#';
+--enable_query_log
+# 5.3  Update of f_int1 from negative value to NULL
+UPDATE t1 SET f_int1 = NULL
+WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+  AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+if ($no_debug)
+{
+--disable_query_log
+}
+#      Check of preceding statement via Select
+SELECT '# check null-3 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
+WHERE f_int1 IS NULL AND f_charbig = '#NULL#';
+--enable_query_log
+# 5.4  DELETE of the record with f_int1 IS NULL
+DELETE FROM t1
+WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
+  AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
+#      Check of preceding statement via Select
+if ($no_debug)
+{
+--disable_query_log
+}
+SELECT '# check null-4 success: ' AS "",COUNT(*) = 0 AS "" FROM t1
+WHERE f_int1 IS NULL;
+--enable_query_log
+}
+# Remove the "0" record
+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#';
+
+
+#-------------------------------------------------------------------------------
+## 6.   UPDATEs of columns used in the partitioning function and the PRIMARY KEY
+#       the UNIQUE INDEX without using straight forward UPDATE.
+#       INSERT .... ON DUPLICATE KEY UPDATE .... --> update existing record
+#       REPLACE --> delete existing record + insert new record
+#       Note:
+#         - This test is skipped for tables without any PRIMARY KEY or
+#           UNIQUE INDEX.
+#         - MOD(<column>,n) with n = prime number, n <> 2 is used to cause
+#           that many records and most probably more than one PARTITION/
+#           SUBPARTITION are affected.
+#         - Under certain circumstanditions a movement of one or more records
+#           to other PARTITIONs/SUBPARTITIONs might appear.
+#         - There are some storage engines, which are unable to revert changes
+#           of a failing statement. This has to be taken into account when
+#           checking if a DUPLICATE KEY might occur.
+#
+#       What to test ?
+#         UNIQUE columns
+#         f_int1         IU f_int1               IU f_int1,f_int2   R
+#         f_int2                     IU f_int2   IU f_int1,f_int2   R
+#         f_int1,f_int2                          IU f_int1,f_int2   R
+#
+#         IU column = INSERT .. ON DUPLICATE KEY UPDATE column
+#         R         = REPLACE ..
+#
+# Current state of the data
+#    1. f_int1 = f_int2, f_char1 = CAST(f_int1 AS CHAR), f_char2 = f_char1,
+#       f_charbig = CONCAT('===',f_char1,'===);
+#    2. f_int1 FROM 1 TO @max_row_div4
+#                AND @max_row_div2 + @max_row_div4 TO @max_row
+#
+# Do not apply the following tests to tables without UNIQUE columns.
+if ($any_unique)
+{
+   let $num= 1;
+   if ($f_int1_is_unique)
+   {
+      ## 6.1 f_int1 is UNIQUE, UPDATE f_int1 when DUPLICATE KEY
+      # Bug#15236 Partitions: crash, if Insert .. on duplicate key causes update of existing row
+      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_charbig = 'was updated';
+      --source suite/parts/inc/partition_20.inc
+   }
+
+   if ($f_int2_is_unique)
+   {
+      ## 6.2 f_int2 is UNIQUE, UPDATE f_int2 when DUPLICATE KEY
+      # Bug#15236 Partitions: crash, if Insert .. on duplicate key causes update of existing row
+      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_int2 = 2 * @max_row + source_tab.f_int1,
+             f_charbig = 'was updated';
+      --source suite/parts/inc/partition_20.inc
+   }
+
+   ## 6.3 f_int1, f_int2 is UNIQUE, UPDATE f_int1, f_int2 when DUPLICATE KEY
+   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';
+   --source suite/parts/inc/partition_20.inc
+
+   ## 6.4 REPLACE
+   # Bug#16782: Partitions: crash, REPLACE .. on table with PK, DUPLICATE KEY
+   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;
+   # DEBUG SELECT * FROM t1 ORDER BY f_int1, f_int2;
+   # Check of preceding statement via Select
+   if ($no_debug)
+   {
+      --disable_query_log
+   }
+   SELECT '# check replace success: ' AS "", COUNT(*) = @try_count AS ""
+   FROM t1 WHERE f_charbig = 'was inserted or replaced';
+   --enable_query_log
+   # Revert the modification
+   DELETE FROM t1
+   WHERE f_int1 BETWEEN @max_row_div2 AND @max_row_div2 + @max_row_div4;
+         # If there is only UNIQUE (f1,f2) we will have pairs f_int1,f_int2
+         #    <n>, <n> and <n>, <-n>
+         # where MOD(f_int1,3) = 0
+         # and f_int1 between @max_row_div2 + @max_row_div4 and @max_row.
+         # Delete the <n>, <n> records.
+   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;
+   # DEBUG SELECT * FROM t1 ORDER BY f_int1, f_int2;
+}
+
+
+#-------------------------------------------------------------------------------
+# 7    Transactions
+SET AUTOCOMMIT= 0;
+# DEBUG SELECT @max_row_div4 ,  @max_row_div2 + @max_row_div4;
+if ($no_debug)
+{
+   --disable_query_log
+}
+SELECT COUNT(f_int1) INTO @start_count FROM t1
+WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+--enable_query_log
+let $run= `SELECT @start_count <> 0`;
+if ($run)
+{
+   --echo #      Prerequisites for following tests not fullfilled.
+   --echo #      The content of the table t1 is unexpected
+   --echo #      There must be no rows BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+   SELECT COUNT(f_int1) FROM t1
+   WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+   --echo #      Sorry, have to abort.
+   exit;
+}
+# Number of records to be inserted
+if ($no_debug)
+{
+   --disable_query_log
+}
+SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
+--enable_query_log
+# 7.1  Successful INSERT + COMMIT
+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;
+# The inserted records must be visible (at least for our current session)
+if ($no_debug)
+{
+   --disable_query_log
+}
+SELECT '# check transactions-1 success: ' AS "",
+       COUNT(*) = @exp_inserted_rows AS ""
+FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+--enable_query_log
+# Make the changes persistent for all storage engines
+COMMIT WORK;
+# The inserted records must be visible (for all open and future sessions)
+if ($no_debug)
+{
+   --disable_query_log
+}
+SELECT '# check transactions-2 success: ' AS "",
+       COUNT(*) = @exp_inserted_rows AS ""
+FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+--enable_query_log
+# Let's assume we have a transactional engine + COMMIT is ill.
+# A correct working ROLLBACK might revert the INSERT.
+ROLLBACK WORK;
+if ($no_debug)
+{
+   --disable_query_log
+}
+SELECT '# check transactions-3 success: ' AS "",
+       COUNT(*) = @exp_inserted_rows AS ""
+FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+--enable_query_log
+# Revert the changes
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+ROLLBACK WORK;
+if ($no_debug)
+{
+   --disable_query_log
+}
+SELECT '# check transactions-4 success: ' AS "",
+       COUNT(*) = 0 AS ""
+FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+--enable_query_log
+#
+# 7.2  Successful INSERT + ROLLBACK
+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;
+if ($no_debug)
+{
+   --disable_query_log
+}
+SELECT '# check transactions-5 success: ' AS "",
+       COUNT(*) = @exp_inserted_rows AS ""
+FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+--enable_query_log
+ROLLBACK WORK;
+if ($no_debug)
+{
+   --disable_query_log
+}
+SELECT COUNT(*) INTO @my_count
+FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+SELECT '# check transactions-6 success: ' AS "",
+       @my_count IN (0,@exp_inserted_rows) AS "";
+let $run= `SELECT @my_count = 0`;
+if ($run)
+{
+      --echo # INFO: Storage engine used for t1 seems to be transactional.
+}
+let $run= `SELECT @my_count = @exp_inserted_rows`;
+if ($run)
+{
+      --echo # INFO: Storage engine used for t1 seems to be not transactional.
+}
+--enable_query_log
+# Let's assume we have a transactional engine + ROLLBACK is ill.
+# A correct working COMMIT might make the inserted records again visible.
+COMMIT;
+if ($no_debug)
+{
+   --disable_query_log
+}
+SELECT '# check transactions-7 success: ' AS "",
+       COUNT(*) IN (0,@exp_inserted_rows) AS ""
+FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+--enable_query_log
+# Revert the changes
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+#
+# 7.3  Failing INSERT (in mid of statement processing) + COMMIT
+SET @@session.sql_mode = 'traditional';
+# Number of records where a INSERT has to be tried
+SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
+#
+--disable_abort_on_error
+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;
+--enable_abort_on_error
+COMMIT;
+# How many new records should be now visible ?
+# 1. storage engine unable to revert changes made by the failing statement
+#         @max_row_div2 - 1 - @max_row_div4 + 1
+# 2. storage engine able to revert changes made by the failing statement
+#         0
+if ($no_debug)
+{
+   --disable_query_log
+}
+SELECT COUNT(*) INTO @my_count
+FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
+SELECT '# check transactions-8 success: ' AS "",
+       @my_count IN (@max_row_div2 - 1 - @max_row_div4 + 1,0) AS "";
+let $run= `SELECT @my_count = @max_row_div2 - 1 - @max_row_div4 + 1`;
+if ($run)
+{
+      --echo # INFO: Storage engine used for t1 seems to be unable to revert
+      --echo #       changes made by the failing statement.
+}
+let $run= `SELECT @my_count = 0`;
+if ($run)
+{
+      --echo # INFO: Storage engine used for t1 seems to be able to revert
+      --echo #       changes made by the failing statement.
+}
+--enable_query_log
+SET @@session.sql_mode = '';
+SET AUTOCOMMIT= 1;
+# Revert the changes
+DELETE FROM t1 WHERE f_charbig = 'was inserted';
+COMMIT WORK;
+
+if ($debug)
+{
+   SELECT * FROM t1 ORDER BY f_int1;
+}
+
+
+#-------------------------------------------------------------------------------
+# 8    Some special cases
+# 8.1  Dramatic increase of the record/partition/subpartition/table sizes
+UPDATE t1 SET f_charbig = REPEAT('b', 1000);
+#      partial check of preceding statement via Select
+if ($no_debug)
+{
+--disable_query_log
+}
+eval SELECT '# check special-1 success: ' AS "",1 AS "" FROM t1
+WHERE f_int1 = 1 AND f_charbig = REPEAT('b', 1000);
+--enable_query_log
+#
+# 8.2  Dramatic decrease of the record/partition/subpartition/table sizes
+UPDATE t1 SET f_charbig = '';
+#      partial check of preceding statement via Select
+if ($no_debug)
+{
+--disable_query_log
+}
+eval SELECT '# check special-2 success: ' AS "",1 AS "" FROM t1
+WHERE f_int1 = 1 AND f_charbig = '';
+--enable_query_log
+# Revert the changes
+UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
+
+if ($debug)
+{
+   SELECT * FROM t1 ORDER BY f_int1;
+}
+
+
+#-------------------------------------------------------------------------------
+# 9    TRIGGERs
+let $num= 1;
+# 9.1  BEFORE/AFTER INSERT/UPDATE/DELETE TRIGGER on table t0_aux causes that
+#      column values used in partitioning function of t1 are changed.
+let $tab_has_trigg= t0_aux;
+let $tab_in_trigg=  t1;
+
+#             Insert three records, which will be updated by the trigger
+eval INSERT INTO $tab_in_trigg(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;
+
+let $statement= 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;
+let $event= BEFORE INSERT;
+--source suite/parts/inc/partition_trigg1.inc
+let $event= AFTER INSERT;
+--source suite/parts/inc/partition_trigg1.inc
+
+let $statement= 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));
+let $event= BEFORE UPDATE;
+--source suite/parts/inc/partition_trigg1.inc
+let $event= AFTER UPDATE;
+--source suite/parts/inc/partition_trigg1.inc
+
+let $statement= DELETE FROM t0_aux
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+let $event= BEFORE DELETE;
+--source suite/parts/inc/partition_trigg1.inc
+let $event= AFTER DELETE;
+--source suite/parts/inc/partition_trigg1.inc
+
+# Cleanup
+eval DELETE FROM $tab_in_trigg
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+
+# Two currently (February 2006) impossible operations.
+# 1442: 'Can't update table 't1' in stored function/trigger because it is
+#        already used by statement which invoked this stored function/trigger.'
+# 1362: 'Updating of OLD row is not allowed in trigger'
+
+if ($debug)
+{
+   SELECT * FROM t1 ORDER BY f_int1;
+}
+
+if ($more_trigger_tests)
+{
+# 9.2  BEFORE/AFTER INSERT/UPDATE/DELETE TRIGGER on partitioned table t1 causes
+#      that column values in not partitioned table t0_aux are changed.
+let $tab_has_trigg= t1;
+let $tab_in_trigg=  t0_aux;
+
+#             Insert three records, which will be updated by the trigger
+eval INSERT INTO $tab_in_trigg(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;
+
+let $statement= INSERT INTO t1(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;
+let $event= BEFORE INSERT;
+--source suite/parts/inc/partition_trigg1.inc
+let $event= AFTER INSERT;
+--source suite/parts/inc/partition_trigg1.inc
+
+let $statement= UPDATE t1 SET f_int1 =  - f_int1, f_int2 = - f_int2
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+let $event= BEFORE UPDATE;
+--source suite/parts/inc/partition_trigg1.inc
+let $event= AFTER UPDATE;
+--source suite/parts/inc/partition_trigg1.inc
+
+let $statement= DELETE FROM t1
+WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
+let $event= BEFORE DELETE;
+--source suite/parts/inc/partition_trigg1.inc
+let $event= AFTER DELETE;
+--source suite/parts/inc/partition_trigg1.inc
+eval DELETE FROM $tab_in_trigg
+WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
+}
+
+if ($debug)
+{
+   SELECT * FROM t1 ORDER BY f_int1;
+}
+
+# 9.3   BEFORE/AFTER UPDATE TRIGGER on partitioned table causes that the value
+#       of columns in partitioning function is recalculated
+if ($more_trigger_tests)
+{
+# 9.3.1 The UPDATE itself changes a column which is not used in the partitioning
+#       function.
+#       "old" values are used as source within the trigger.
+let $statement= UPDATE t1
+SET f_charbig = '####updated per update statement itself####';
+let $source= old;
+let $event= BEFORE UPDATE;
+--source suite/parts/inc/partition_trigg2.inc
+# FIXME when AFTER TRIGGER can be used
+# Currently (2006-02-23) a AFTER TRIGGER is not allowed to modify a row, which
+# was just modified: 1362: Updating of NEW row is not allowed in after trigger
+}
+
+# 9.3.2   The UPDATE itself changes a column which is used in the partitioning
+#         function.
+let $statement= UPDATE t1
+SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
+f_charbig = '####updated per update statement itself####';
+
+# 9.3.2.1 "old" values are used as source within the trigger.
+let $source= old;
+let $event= BEFORE UPDATE;
+--source suite/parts/inc/partition_trigg2.inc
+# FIXME when AFTER TRIGGER can be used
+# Currently (2006-02-23) a AFTER TRIGGER is not allowed to modify a row, which
+# was just modified: 1362: Updating of NEW row is not allowed in after trigger
+# 9.3.2.2 "new" values are used as source within the trigger.
+let $source= new;
+let $event= BEFORE UPDATE;
+--source suite/parts/inc/partition_trigg2.inc
+# FIXME when AFTER TRIGGER can be used
+
+if ($debug)
+{
+   SELECT * FROM t1 ORDER BY f_int1;
+}
+
+# 9.4    BEFORE/AFTER INSERT TRIGGER on partitioned table causes that the value of
+#        columns in partitioning function is recalculated.
+# 9.4.1  INSERT assigns values to the recalculate columns
+let $statement= 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;
+let $event= BEFORE INSERT;
+let $source= new;
+--source suite/parts/inc/partition_trigg3.inc
+# FIXME when AFTER TRIGGER can be used
+
+# 9.4.2  INSERT assigns no values to the recalculate columns
+let $statement= 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;
+let $event= BEFORE INSERT;
+let $source= new;
+--source suite/parts/inc/partition_trigg3.inc
+# FIXME when AFTER TRIGGER can be used
+
+if ($debug)
+{
+   SELECT * FROM t1 ORDER BY f_int1;
+}
+
+
+#-------------------------------------------------------------------------------
+# 10   ANALYZE/CHECK/CHECKSUM
+ANALYZE  TABLE t1;
+CHECK    TABLE t1 EXTENDED;
+# Checksum depends on @max_row so we have to unify the value
+--replace_column 2 <some_value>
+CHECKSUM TABLE t1 EXTENDED;
+
+
+#-------------------------------------------------------------------------------
+# 11   Some special statements, which may lead to a rebuild of the trees
+#      depending on the storage engine and some particular conditions
+# 11.1 OPTIMIZE TABLE
+#      Manual about OPTIMIZE <InnoDB table>:
+#      ... , it is mapped to ALTER TABLE, which rebuilds the table.
+#      Rebuilding updates index statistics and frees unused space in the
+#      clustered index.
+OPTIMIZE TABLE t1;
+--source suite/parts/inc/partition_layout_check2.inc
+# 10.2 REPAIR TABLE
+REPAIR   TABLE t1 EXTENDED;
+--source suite/parts/inc/partition_layout_check2.inc
+#
+# 11.3 Truncate
+# Manual about TRUNCATE on tables ( != InnoDB table with FOREIGN KEY ):
+# Truncate operations drop and re-create the table ....
+TRUNCATE t1;
+#      Check of preceding statement via Select
+if ($no_debug)
+{
+--disable_query_log
+}
+SELECT '# check TRUNCATE success: ' AS "",COUNT(*) = 0 AS "" FROM t1;
+--enable_query_log
+--source suite/parts/inc/partition_layout_check2.inc
+--echo # End usability test (inc/partition_check.inc)
+