################################################################################
#
# 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).