1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
|
#
# This test performs zero-sum queries, that is, queries after which the average value of all integers in the table remains the same.
# Some queries move values within a single row, others between rows and some insert new values or delete existing ones.
#
# The values in the first 10 rows are updated so that values from one row may move into another row. This makes those rows unsuitable for random
# insertions and deletions.
#
# Rows beyond the 10th are just inserted and delted randomly because each row in that part of the table is self-contained
#
query_init:
SET AUTOCOMMIT=OFF ; START TRANSACTION ;
query:
START TRANSACTION ; body ; commit_rollback ;
body:
update_all |
update_multi |
update_one |
update_between |
# update_two | # Not fully consistent
update_limit | # Broken in Falcon
update_in |
insert_one | # Broken with Falcon
insert_multi | # Broken with Falcon
insert_select | # Broken with Falcon
insert_delete | # Broken with Falcon
# insert_update | # Not fully consistent
replace | # Broken in Falcon
delete_one |
delete_multi
;
commit_rollback:
COMMIT |
SAVEPOINT A |
ROLLBACK TO SAVEPOINT A |
ROLLBACK
;
update_all:
UPDATE _table SET update_both ;
update_multi:
UPDATE _table SET update_both WHERE key_nokey_pk > _digit ;
update_one:
UPDATE _table SET update_both WHERE `pk` = value ;
update_between:
SET @var = half_digit ; UPDATE _table SET update_both WHERE `pk` >= @var AND `pk` <= @var + 1 |
SET @var = half_digit ; UPDATE _table SET update_both WHERE `pk` BETWEEN @var AND @var + 1 ;
update_two:
UPDATE _table SET `int_key` = `int_key` - 10 WHERE `pk` = small ; UPDATE _table SET `int_key` = `int_key` + 10 WHERE `pk` = big ;
update_limit:
UPDATE _table SET update_one_half + IF(`pk` % 2 = 1 , 20, -20) WHERE `pk` >= half_digit ORDER BY `pk` ASC LIMIT 2 ;
update_in:
UPDATE _table SET update_one_half + IF(`pk` % 2 = 1 , 30, -30) WHERE `pk` IN ( even_odd ) ;
insert_one:
INSERT INTO _table ( `pk` , `int_key` , `int`) VALUES ( NULL , 100 , 100 ) |
INSERT INTO _table ( `pk` ) VALUES ( NULL ) ; ROLLBACK ;
insert_multi:
INSERT INTO _table ( `pk` , `int_key` , `int`) VALUES ( NULL , 100 , 100 ) , ( NULL , 100 , 100 ) |
INSERT INTO _table ( `pk` ) VALUES ( NULL ) , ( NULL ) , ( NULL ) ; ROLLBACK ;
insert_select:
INSERT INTO _table ( `int_key` , `int` ) SELECT `int` , `int_key` FROM _table WHERE `pk` > 10 LIMIT _digit ;
insert_delete:
INSERT INTO _table ( `pk` , `int_key` , `int` ) VALUES ( NULL , 50 , 60 ) ; DELETE FROM _table WHERE `pk` = @@LAST_INSERT_ID ;
insert_update:
INSERT INTO _table ( `pk` , `int_key` , `int` ) VALUES ( NULL, 170 , 180 ) ; UPDATE _table SET `int_key` = `int_key` - 80 , `int` = `int` - 70 WHERE `pk` = _digit ;
replace:
REPLACE INTO _table ( `pk` , `int_key` , `int` ) VALUES ( NULL, 100 , 100 ) |
REPLACE INTO _table ( `pk` ) VALUES ( _digit ) ; ROLLBACK ;
delete_one:
DELETE FROM _table WHERE `pk` = _tinyint_unsigned AND `pk` > 10;
delete_multi:
DELETE FROM _table WHERE `pk` > _tinyint_unsigned AND `pk` > 10 LIMIT _digit ;
update_both:
`int_key` = `int_key` - 20, `int` = `int` + 20 |
`int` = `int` + 30, `int_key` = `int_key` - 30 ;
update_one_half:
`int_key` = `int_key` |
`int` = `int` ;
key_nokey_pk:
`int_key` | `int` | `pk` ;
value:
_digit;
half_digit:
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 ;
even_odd:
odd , even | even , odd ;
odd:
1 | 3 | 5 | 7 | 9 ;
even:
2 | 4 | 6 | 8 ;
small:
1 | 2 | 3 | 4 ;
big:
5 | 6 | 7 | 8 | 9 ;
_digit:
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 ;
|