WL#5344: Testing binlogging of operations on temporary tables

Affects: Server-5.5   —   Status: Complete   —   Priority: Medium

CONTEXT
=======

In this test case, we check how changes to temporary tables are written
to the binary log.


RELATED BUGS
============

BUG#53259.
Changing Temporary Tables
=========================

Temporary tables that are changed, i.e.,

 (1) created by a CREATE TEMPORARY, CREATE TEMPORARY SELECT or CREATE LIKE;
 (2) dropped by a DROP;
 (3) dropped by a DROP TEMPORARY;
 (4) have a row inserted, deleted or updated;

are subjected to the following rules regarding what is written to the
binary log.

RULES
=====

If there is an on-going transaction, changes to temporary tables are written to
the binary log according to the type of the engine in use, until something is
written to the transactional cache. After that, any change is kept in the
context of the on-going transaction. To easy the presentation, in what follows,
we assume that something was already written to the transactional cache.

(1) If the format is ROW:

  (1.1) DROP TEMPORARY TABLE is written to the binary log with an IF
  EXISTS appended to the statement if a temporary table is dropped.

  (1.2) DROP TEMPORARY TABLE returns an error, i.e. table does not
  exist, if we try to drop a regular table.

  (1.3) Any DROP that affects a temporary table has the temporary table
  logged as part of a DROP TEMPORARY TABLE IF EXISTS.

  (1.4) Other tables, if there is any, are written to the binary log
  as part of the DROP TABLE.

  (1.5) DROP is written to the binary log as a single statement because
  it commits any on-going transaction.

  (1.6) Other changes to temporary tables are not written to the binary log.

(2) If the format is STATEMENT, changes to temporary tables are written to
the binary log as follows:

  (2.1) INSERTS, UPDATES and DELETES are kept in the context of any on-going
  transaction and wrapped by a COMMIT/ROLLBACK.

  (2.2) CREATE TEMPORARY, CREATE TEMPORARY SELECT, CREATE LIKE and DROP
  TEMPORARY TABLE are kept in the context of any on-going transaction if
  there is one.

  (2.3) Otherwise, such commands are written as single statements.

  (2.4) There is no special procedure for DROP TEMPORARY TABLE [IF EXISTS].

  (2.5) DROP TEMPORARY TABLE returns an error, i.e. table does not exist,
  if we try to drop a regular table.

  (2.6) DROP is written to the binary log as a single statement because
  it commits any on-going transaction.

(3) If the format is MIXED and the execution switched from statements to
rows due to any reason, the system behaves as in the ROW format.

(4) If the format is MIXED and the execution did not switch from statements to
rows, the system behaves as in the STATEMENT format.

REMARKS:
========

. In (1.1), a statement written to the binary log with an appended IF EXISTS,
because a temporary table "could" be created and replicated when a different
format was in use.

. In (1.3), any DROP that affects a temporary table has the drop re-written as
DROP TEMPORARY IF EXISTS and DROP because the IF EXISTS needed to be appended to
the DROP TEMPORARY.

. DROP TEMPORARY TABLE, when the table is regular, raises an error as the table
does not exist.

. DROP TEMPORARY TABLE, when the table is temporary, works fine.

EXPECTED RESULTS:
=================

So we verify if DROP TEMPORARY works correctly by dropping different types of
tables and checking if the statement is written to the binary log as expected:

DROP TEMPORARY TABLE:
---------------------
                            (STATEMENT)
               Temp T-Table   Temp N-Table   Xe

Temp T-Table   k              k              k with error

Temp N-Table   k              k              k with error

Xe             k with error   k with error   -

T-table        k with error   k with error   -

N-table        k with error   k with error   -

                               (ROW)
                Temp T-Table     Temp N-Table   Xe

Temp T-Table    k with IF        k with IF      k with IF and
                                                Temp T-Table

Temp N-Table    k with IF        k with IF      k with IF and
                                                Temp N-Table

Xe              k with IF and    k with IF and  -
                Temp T-Table     Temp N-Table

T-Table         k with IF and    k with IF and  -
                Temp T-Table     Temp N-Table

DROP TEMPORARY TABLE IF EXISTS:
-------------------------------
                                 (STATEMENT)
                Temp T-table   Temp N-Table   Xe

Temp T-Table         k              k         k

Temp N-Table         k              k         k

Xe                   k              k         k

                                  (ROW)
                Temp T-Table     Temp N-Table   Xe

Temp T-Table    k with IF        k with IF      k with IF and
                                                Temp T-Table

Temp N-Table    k with IF        k with IF      k with IF and
                                                Temp N-Table

Xe              k with IF and    k with IF and  -
                Temp T-Table     Temp N-Table

T-Table         k with IF and    k with IF and  -
                Temp T-Table     Temp N-Table


DROP TABLE:
-----------
                             (STATEMENT)
               Temp T-Table   Temp N-Table   Xe

Temp T-Table   y              y              y with error

Temp N-Table   y              y              y with error

Xe             y with error   y with error   -

T-Table        y              y              y with error

N-Table        y              y              y with error


                                (ROW)
               Temp T-Table      Temp N-Table      Xe

Temp T-Table   k with IF         k with IF         . k with IF and
                                                   Temp T-Table
                                                   . y with error
                                                   and Xe

Temp N-Table   k with IF         k with IF         . k with IF and
                                                   Temp N-Table
                                                   . y with error
                                                   and Xe

Xe             . k with IF and   . k with IF and   -
               Temp T-Table      Temp N-Table
               . y with error    . y with error
               and Xe            and Xe

T-Table        . k with IF and   . k with IF and   y with error
               Temp T-Table      Temp N-Table
               . y and T-Table   . y and T-Table

N-Table        . k with IF and   . k with IF and   y with error
               Temp T-Table      Temp N-Table
               . y and N-Table   . y and N-Table


DROP TABLE IF EXISTS:
---------------------
                         (STATEMENT)
               Temp T-Table   Temp N-Table   Xe

Temp T-Table   y              y              y

Temp N-Table   y              y              y

Xe             y              y              -

T-Table        y              y              y

N-Table        y              y              y


                               (ROW)
               Temp T-Table      Temp N-Table      Xe

Temp T-Table   k with IF         k with IF         . k with IF and
                                                   Temp T-Table
                                                   . y and Xe

Temp N-Table   k with IF         k with IF         . k with IF and
                                                   Temp N-Table
                                                   . y and Xe

Xe             . k with IF and   . k with IF and   y
               Temp T-Table      Temp N-Table
               . y and Xe        . y and Xe

T-Table        . k with IF and   . k with IF and   y
               Temp T-Table      Temp N-Table
               . y and T-Table   . y and T-Table

N-Table        . k with IF and   . k with IF and   y
               Temp T-Table      Temp N-Table
               . y and N-Table   . y and N-Table

LEGEND:
-------

. k - DROP TEMPORARY [IF EXISTS]
. y - DROP [IF EXISTS]
TEST CASES:
===========

We should add the following files:

. an engine to processes commands involving temporary tables in order to ease
the creation of different permutations.
   mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.inc

. definition of the permutations:
  mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.test

. test cases that call the rpl_drop_create_temp_table.test for the different
logging formats:
  mysql-test/suite/rpl/t/rpl_mixed_drop_create_temp_table.test
  mysql-test/suite/rpl/t/rpl_row_drop_create_temp_table.test
  mysql-test/suite/rpl/t/rpl_stm_drop_create_temp_table.test

. their result files:
  mysql-test/suite/rpl/r/rpl_mixed_drop_create_temp_table.result
  mysql-test/suite/rpl/r/rpl_row_drop_create_temp_table.result
  mysql-test/suite/rpl/r/rpl_stm_drop_create_temp_table.result

We should remove the following tests that were temporarily introduced while this
worklog was not ready:

mysql-test/suite/rpl/t/rpl_temp_temporary.test
mysql-test/suite/rpl/t/rpl_temp_temporary-master.opt
mysql-test/suite/rpl/r/rpl_temp_temporary.result


The engine allows the following commands:

 B - Begin
 C - Commit
 R - Rollback


 T - Inserts a row into a T-table
 N-Temp - Inserts a row into a temporary N-table.


 T-SELECT-N-Temp - Selects from a temporary N-table and inserts
                   into a T-table.
 N-SELECT-N-Temp - Selects from a temporary N-table and inserts
                   into a N-table.
 T-SELECT-T-Temp - Selects from a temporary T-table and inserts
                   into a T-table.
 N-SELECT-T-Temp - Selects from a temporary T-table and inserts
                   into a N-table.


 Create-N-Temp - Creates a temporary N-table if a temporary N-table
                 was dropped before
 Create-T-Temp - Creates a temporary T-table if a temporary T-table
                 was dropped before


 Drop-Temp-T-Temp - Drops a temporary T-table if there is any
 Drop-Temp-N-Temp - Drops a temporary N-table if there is any
 Drop-Temp-TN-Temp - Drops both a temporary T-table and N-table if there
                     is any
 Drop-Temp-TT-Temp - Drops two temporary T-tables if there is any
 Drop-Temp-NN-Temp - Drops two temporary N-tables if there is any
 Drop-Temp-Xe-Temp - Tries to drop a temporary table that does not exist
 Drop-Temp-NXe-Temp - Drops a temporary N-table if there is any and
                      a temporary table that does not exist
 Drop-Temp-TXe-Temp - Drops a temporary T-table if there is any and
                      a temporary table that does not exist


 Drop-Temp-If-Xe-Temp - Tries to drop a temporary table that does not exist
 Drop-Temp-If-TXe-Temp - Drops a temporary T-table if there is any and
                         a temporary table that does not exist


 Drop-T - Drops a T-table if there is any
 Drop-N - Drops a  N-table if there is any
 Drop-Xe - Tries to drop a table that does not exist
 Drop-TXe - Drops a T-table if there is any and a table that does
            not exist
 Drop-NXe - Drops a N-table if there is any and a table that does
            not exist
 Drop-TN - Drops both a T-table and N-table if there is any
 Drop-TT - Drops two T-tables if there is any
 Drop-NN - Drops two N-tables if there is any
 Drop-N-TN-Temp - Drops a N-table and both a temporary T-table and
                  N-table if there is any


 Drop-If-Xe - Tries to drop a table that does not exist
 Drop-If-TXe - Drops a T-table if there is any and a table that does
               not exist
 Drop-If-NXe - Drops a N-table if there is any and a table that does
               not exist