################################################################################
#
# Part of checking that REPEATABLE-READ transactions are indeed repeatable read.
#
# This test executes several queries in the same transaction which should return
# the exact same results. Some of the queries will use LIMIT, others will not.
#
# In order to trigger Falcon's LIMIT optimization we need to ORDER BY an indexed
# column, use a WHERE range predicate and include the LIMIT keyword.
#
################################################################################

SET autocommit = 0;
START TRANSACTION;

--echo Comparing results from 2 queries (unless we deadlock or some such)...
# Without LIMIT
let $query= SELECT * FROM t1 WHERE `pk` > 1000 ORDER BY `int1_key`;
--echo *** Query 1: $query
--source suite/engines/rr_trx/include/record_query_all_columns.inc

--echo *** Filler: 
SELECT SLEEP(1);

# With LIMIT. We assume that t1 will not have more than <limit> rows.
# With some large limit, just to exercise the optimization but return the same results:
let $query= SELECT * FROM t1 WHERE `pk` > 1000 ORDER BY `int1_key` LIMIT 987654321;
--echo *** Query 2: $query
# Disable warnings (due to warning about unsafe binlogging with LIMIT).
--disable_warnings
--source suite/engines/rr_trx/include/record_query_all_columns.inc
--enable_warnings

--echo *** Filler: Do something other than sleep while waiting for other transactions to do stuff...
CREATE TEMPORARY TABLE tmpSelectLimitNoLimit (a INT, b VARCHAR(255), c TIMESTAMP, KEY(a));
INSERT INTO tmpSelectLimitNoLimit VALUES
    (-1, 'This is a filler', NOW()),
    (0, 'More stuff', NOW()),
    (999999999, 'Even more bogus data', NOW()),
    (-98765, 'Even more bogus data', NOW());
SELECT * FROM tmpSelectLimitNoLimit WHERE a < -99999999 ORDER BY a;

# With LIMIT.
let $query= SELECT * FROM t1 WHERE `pk` > 1000 ORDER BY `int1_key` LIMIT 987654321;
--echo *** Query 3: $query
--disable_warnings
--source suite/engines/rr_trx/include/record_query_all_columns.inc
--enable_warnings

--echo *** Filler:
UPDATE tmpSelectLimitNoLimit SET a = 3;
SELECT SLEEP(1);

#Without LIMIT:
let $query= SELECT * FROM t1 WHERE `pk` > 1000 ORDER BY `int1_key`;
--echo *** Query 4: $query
--source suite/engines/rr_trx/include/record_query_all_columns.inc

# Compare queries
--source suite/engines/rr_trx/include/check_repeatable_read_all_columns.inc

COMMIT;