check_repeatable_read_all_columns.inc

################################################################################
#
# Verifies that queries in a REPEATABLE READ transaction is indeed repeatable.
# Wrong results are shown as a result set based on one or more specially crafted
# queries. Normally these result sets should be empty.
#
# We want to verify that reads (SELECTs) are indeed repeatable during a 
# REPEATABLE READ transaction.
#
# Generally, queries which should yield the same results at one moment in time 
# should also yield the same results later in the same transaction. In some 
# configurations, however, phantom reads are allowed (may e.g. depend on 
# settings such as falcon_consistent_read).
#
# The check will fail if rows are changed or missing when comparing later
# queries to earlier ones.
# TODO: Phantom reads.
#       Note: There is a separate test looking for `is_uncommitted` = 1.
#
# Assumptions:
#   - we are in a REPEATABLE READ transaction with autocommit OFF.
#   - queries include all columns of table (t1) (we SELECT columns by name)
#
# Requires/using the following variables:
#   $query_count - the number of queries to compare.
#                  Will also be used to deduce the name of the temp table in
#                  which the query results should be stored (see 
#                  record_query_all_columns.inc).
#
################################################################################

# Show results of next queries. Empty results is OK. Non-empty means failure.
--enable_result_log

# The mysqltest language is unfortunaltely not very flexible, but we try our
# best to compare query results this way:
#   - For each query, compare with previous query
#     - this requires that at least 2 queries have been stored
#   - Number of queries should be stored as $query_count
#   - Results should be stored in temp tables with names ending with the query
#     number, and with prefix "tmp". 
#   - E.g. compare "tmp2" with "tmp1", "tmp3" with "tmp2", "tmp4" with "tmp3" etc.
#   - Fail the test once we detect changed or missing or invalid extra rows in
#     latter query.
#   ?????
#   - Problem is that if one of the queries deadlocked or timed out, we may not
#     have enough result sets to compare, so output will vary depending on this.
#     Still we need the output from these checks to see which rows are missing or
#     changed.
#     So, if we don't have enough queries we fake "correct output" to make mysqltest
#     happy.
#
# Unfortunately, we need to utilize SQL and spend client-server roundtrips
# in order to do some computations that the mysqltest language does not handle.
# We try to use mysqltest variables instead where possible, as this should be 
# less expensive in terms of CPU usage and time spenditure.

#
# First, check that we have at least two query results stored.
# We need at least 2 to be able to compare.
# Some results may not have been stored due to locking errors (see record_query_all_columns.inc), so
# we cannot assume that we always have at least 2 query results stored.
# If less than 2 query results are stored, return to calling test/script.
#
if (`SELECT IF($query_count > 1, 1, 0)`)
{
    
    --echo ***************************************************************************
    --echo * Checking REPEATABLE READ by comparing result sets from same transaction
    --echo ***************************************************************************

    --echo *** Query log disabled. See include files used by test for query details.
    --disable_query_log

    let $queryA= 1;
    let $queryB= 2;
    let $more_queries= $query_count;

    # We start out by comparing the first 2 queries, so the while loop should run
    # $query_count - 1 times. (If we have 3 queries, compare 1 with 2, 2 and 3).
    --dec $more_queries

    while ($more_queries)
    {
        # We still have one or more queries that have not been compared to the
        # previous query.
        # Compare queryB ("current query") with queryA ("previous query")

        #--source suite/stress_tx_rr/include/compare_queries_with_pk.inc

        let $tableA= tmp$queryA;
        let $tableB= tmp$queryB;

        --echo *** Comparing query $queryA (A) with query $queryB (B):

        #
        # In the following queries, 'SELECT * ...' could have been used instead of
        # 'SELECT tmp1.pk AS ...' etc., but the latter makes it easier to compare the first
        # result set to the second in test/diff output.



        ###########################
        # Detect extra rows:
        # Allow phantoms in some configurations:
        #   - InnoDB default settings
        #   - Falcon's falcon_consistent_read=0 (non-default setting)
        #   (TODO: What about PBXT?)
        #
        ###########################
        # TODO: Execute a query against tmp1 and tmp2 which selects new rows (rows
        #       present in tmp2 that are not present in tmp1) that are of the uncommitted
        #       variety (field `is_uncommitted` = 1).
        # E.g. something like:
        #  SELECT ...
        #   FROM tmp2 LEFT JOIN tmp1
        #   ON tmp1.`pk` = tmp2.`pk`
        #   WHERE tmp1.`int1` IS NULL
        #   OR tmp1.`int1_key` IS NULL
        #   OR tmp1.`int1_unique` IS NULL
        #   OR tmp1.`int2` IS NULL
        #   OR tmp1.`int2_key` IS NULL
        #   OR tmp1.`int2_unique` IS NULL
        #   AND tmp2.`is_uncommitted` = 1;


        --echo ###########################
        --echo # Detect missing rows:
        --echo ###########################


        eval SELECT    $tableA.pk AS 'A.pk',
                      $tableB.pk AS 'B.pk',
                      $tableA.id AS 'A.id',
                      $tableB.id AS 'B.id',
                      $tableA.`int1` AS 'A.int1',
                      $tableB.`int1` AS 'B.int1',
                      $tableA.`int1_key` AS 'A.int1_key',
                      $tableB.`int1_key` AS 'B.int1_key',
                      $tableA.`int1_unique` AS 'A.int1_unique',
                      $tableB.`int1_unique` AS 'B.int1_unique',
                      $tableA.`int2` AS 'A.int2',
                      $tableB.`int2` AS 'B.int2',
                      $tableA.`int2_key` AS 'A.int2_key',
                      $tableB.`int2_key` AS 'B.int2_key',
                      $tableA.`int2_unique` AS 'A.int2_unique',
                      $tableB.`int2_unique` AS 'B.int2_unique',
                      $tableA.`for_update` AS 'A.for_update',
                      $tableB.`for_update` AS 'B.for_update',
                      $tableA.timestamp AS 'A.timestamp',
                      $tableB.timestamp AS 'B.timestamp',
                      $tableA.`connection_id` AS 'A.connection_id',
                      $tableB.`connection_id` AS 'B.connection_id',
                      $tableA.`thread_id` AS 'A.thread_id',
                      $tableB.`thread_id` AS 'B.thread_id',
                      $tableA.`is_uncommitted` AS 'A.is_uncommitted',
                      $tableB.`is_uncommitted` AS 'B.is_uncommitted',
                      $tableA.`is_consistent` AS 'A.is_consistent',
                      $tableB.`is_consistent` AS 'B.is_consistent'
                FROM $tableA LEFT JOIN $tableB
                  ON $tableA.`pk` = $tableB.`pk`
                WHERE $tableB.`pk` IS NULL;

    #
    #               OR $tableB.`int1_key` IS NULL
    #               OR $tableB.`int1_unique` IS NULL
    #               OR $tableB.`int2` IS NULL
    #               OR $tableB.`int2_key` IS NULL
    #               OR $tableB.`int2_unique` IS NULL;

        --echo
        --echo ###########################
        --echo # Detect changed rows:
        --echo ###########################

        eval SELECT  $tableA.pk AS 'A.pk',
                      $tableB.pk AS 'B.pk',
                      $tableA.id AS 'A.id',
                      $tableB.id AS 'B.id',
                      $tableA.`int1` AS 'A.int1',
                      $tableB.`int1` AS 'B.int1',
                      $tableA.`int1_key` AS 'A.int1_key',
                      $tableB.`int1_key` AS 'B.int1_key',
                      $tableA.`int1_unique` AS 'A.int1_unique',
                      $tableB.`int1_unique` AS 'B.int1_unique',
                      $tableA.`int2` AS 'A.int2',
                      $tableB.`int2` AS 'B.int2',
                      $tableA.`int2_key` AS 'A.int2_key',
                      $tableB.`int2_key` AS 'B.int2_key',
                      $tableA.`int2_unique` AS 'A.int2_unique',
                      $tableB.`int2_unique` AS 'B.int2_unique',
                      $tableA.`for_update` AS 'A.for_update',
                      $tableB.`for_update` AS 'B.for_update',
                      $tableA.timestamp AS 'A.timestamp',
                      $tableB.timestamp AS 'B.timestamp',
                      $tableA.`connection_id` AS 'A.connection_id',
                      $tableB.`connection_id` AS 'B.connection_id',
                      $tableA.`thread_id` AS 'A.thread_id',
                      $tableB.`thread_id` AS 'B.thread_id',
                      $tableA.`is_uncommitted` AS 'A.is_uncommitted',
                      $tableB.`is_uncommitted` AS 'B.is_uncommitted',
                      $tableA.`is_consistent` AS 'A.is_consistent',
                      $tableB.`is_consistent` AS 'B.is_consistent'
                FROM $tableB INNER JOIN $tableA
                  ON $tableB.`pk` = $tableA.`pk`
                WHERE $tableB.`int1` <> $tableA.`int1`
                  OR $tableB.`int1_key` <> $tableA.`int1_key`
                  OR $tableB.`int1_unique` <> $tableA.`int1_unique`
                  OR $tableB.`int2` <> $tableA.`int2`
                  OR $tableB.`int2_key` <> $tableA.`int2_key`
                  OR $tableB.`int2_unique` <> $tableA.`int2_unique`;

        --dec $more_queries
        --inc $queryA
        --inc $queryB

    }
    --enable_query_log
}


## Cleanup is skipped because temporary tables and prepared statements will
## be cleaned up automatically by the server when this session ends, and we
## want to have as few client-server roundtrips as possible (thus avoid 
## unnecessary SQL statement executions).