Debian Codegraph @

mysql-test/suite/engines/rr_trx/t/rr_sc_select-limit-nolimit_4.test

raw
1 ################################################################################
2 #
3 # Part of checking that REPEATABLE-READ transactions are indeed repeatable read.
4 #
5 # This test executes several queries in the same transaction which should return
6 # the exact same results. Some of the queries will use LIMIT, others will not.
7 #
8 # In order to trigger Falcon's LIMIT optimization we need to ORDER BY an indexed
9 # column, use a WHERE range predicate and include the LIMIT keyword.
10 #
11 ################################################################################
12
13 SET autocommit = 0;
14 START TRANSACTION;
15
16 --echo Comparing results from 2 queries (unless we deadlock or some such)...
17 # Without LIMIT
18 let $query= SELECT * FROM t1 WHERE `pk` > 1000 ORDER BY `int1_key`;
19 --echo *** Query 1: $query
20 --source suite/engines/rr_trx/include/record_query_all_columns.inc
21
22 --echo *** Filler:
23 SELECT SLEEP(1);
24
25 # With LIMIT. We assume that t1 will not have more than <limit> rows.
26 # With some large limit, just to exercise the optimization but return the same results:
27 let $query= SELECT * FROM t1 WHERE `pk` > 1000 ORDER BY `int1_key` LIMIT 987654321;
28 --echo *** Query 2: $query
29 # Disable warnings (due to warning about unsafe binlogging with LIMIT).
30 --disable_warnings
31 --source suite/engines/rr_trx/include/record_query_all_columns.inc
32 --enable_warnings
33
34 --echo *** Filler: Do something other than sleep while waiting for other transactions to do stuff...
35 CREATE TEMPORARY TABLE tmpSelectLimitNoLimit (a INT, b VARCHAR(255), c TIMESTAMP, KEY(a));
36 INSERT INTO tmpSelectLimitNoLimit VALUES
37 (-1, 'This is a filler', NOW()),
38 (0, 'More stuff', NOW()),
39 (999999999, 'Even more bogus data', NOW()),
40 (-98765, 'Even more bogus data', NOW());
41 SELECT * FROM tmpSelectLimitNoLimit WHERE a < -99999999 ORDER BY a;
42
43 # With LIMIT.
44 let $query= SELECT * FROM t1 WHERE `pk` > 1000 ORDER BY `int1_key` LIMIT 987654321;
45 --echo *** Query 3: $query
46 --disable_warnings
47 --source suite/engines/rr_trx/include/record_query_all_columns.inc
48 --enable_warnings
49
50 --echo *** Filler:
51 UPDATE tmpSelectLimitNoLimit SET a = 3;
52 SELECT SLEEP(1);
53
54 #Without LIMIT:
55 let $query= SELECT * FROM t1 WHERE `pk` > 1000 ORDER BY `int1_key`;
56 --echo *** Query 4: $query
57 --source suite/engines/rr_trx/include/record_query_all_columns.inc
58
59 # Compare queries
60 --source suite/engines/rr_trx/include/check_repeatable_read_all_columns.inc
61
62 COMMIT;