1.1.4
by Tobias Frost
Import upstream version 7.1.32-rc |
1 |
# Copyright (C) 2008-2009 Sun Microsystems, Inc. All rights reserved. |
2 |
# Use is subject to license terms. |
|
3 |
#
|
|
4 |
# This program is free software; you can redistribute it and/or modify |
|
5 |
# it under the terms of the GNU General Public License as published by |
|
6 |
# the Free Software Foundation; version 2 of the License. |
|
7 |
#
|
|
8 |
# This program is distributed in the hope that it will be useful, but |
|
9 |
# WITHOUT ANY WARRANTY; without even the implied warranty of |
|
10 |
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
|
11 |
# General Public License for more details. |
|
12 |
#
|
|
13 |
# You should have received a copy of the GNU General Public License |
|
14 |
# along with this program; if not, write to the Free Software |
|
15 |
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 |
|
16 |
# USA |
|
17 |
||
18 |
#
|
|
19 |
# This test performs zero-sum queries, that is, queries after which the average value of all integers in the table remains the same. |
|
20 |
# Some queries move values within a single row, others between rows and some insert new values or delete existing ones. |
|
21 |
#
|
|
22 |
# 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 |
|
23 |
# insertions and deletions. |
|
24 |
#
|
|
25 |
# Rows beyond the 10th are just inserted and delted randomly because each row in that part of the table is self-contained |
|
26 |
#
|
|
27 |
||
28 |
query_init: |
|
29 |
SET AUTOCOMMIT=OFF ; START TRANSACTION ; |
|
30 |
||
31 |
query: |
|
32 |
update_all1 | update_multi1 | update_one1 | update_between1 | insert_one1 | insert_multi1 | insert_select1 | replace1 | delete_one1 | delete_multi1 ; |
|
33 |
||
34 |
update_all1: |
|
35 |
START TRANSACTION ; update_all ; commit_rollback ; |
|
36 |
||
37 |
update_multi1: |
|
38 |
START TRANSACTION ; update_multi ; commit_rollback ; |
|
39 |
||
40 |
update_one1: |
|
41 |
START TRANSACTION ; update_one ; commit_rollback ; |
|
42 |
||
43 |
update_between1: |
|
44 |
START TRANSACTION ; update_between ; commit_rollback ; |
|
45 |
# START TRANSACTION ; update_two ; commit_rollback | # Not fully consistent |
|
46 |
# START TRANSACTION ; update_limit ; commit_rollback | # Broken in Falcon |
|
47 |
||
48 |
update_in1: |
|
49 |
START TRANSACTION ; update_in ; commit_rollback ; |
|
50 |
||
51 |
insert_one1: |
|
52 |
START TRANSACTION ; insert_one ; commit_rollback ; |
|
53 |
||
54 |
insert_multi1: |
|
55 |
START TRANSACTION ; insert_multi ; commit_rollback ; |
|
56 |
||
57 |
insert_select1: |
|
58 |
START TRANSACTION ; insert_select ; commit_rollback ; |
|
59 |
||
60 |
insert_delete1: |
|
61 |
START TRANSACTION ; insert_delete ; commit_rollback ; |
|
62 |
||
63 |
# START TRANSACTION ; insert_update ; commit_rollback | # Not fully consistent |
|
64 |
||
65 |
replace1: |
|
66 |
START TRANSACTION ; replace ; commit_rollback ; |
|
67 |
||
68 |
delete_one1: |
|
69 |
START TRANSACTION ; delete_one ; commit_rollback ; |
|
70 |
||
71 |
delete_multi1: |
|
72 |
START TRANSACTION ; delete_multi ; commit_rollback ; |
|
73 |
||
74 |
commit_rollback: |
|
75 |
COMMIT | |
|
76 |
SAVEPOINT A | |
|
77 |
ROLLBACK TO SAVEPOINT A | |
|
78 |
ROLLBACK
|
|
79 |
;
|
|
80 |
||
81 |
update_all: |
|
82 |
UPDATE _table SET update_both ; |
|
83 |
||
84 |
update_multi: |
|
85 |
UPDATE _table SET update_both WHERE key_nokey_pk > _digit ; |
|
86 |
||
87 |
update_one: |
|
88 |
UPDATE _table SET update_both WHERE `pk` = value ; |
|
89 |
||
90 |
update_between: |
|
91 |
SET @var = half_digit ; UPDATE _table SET update_both WHERE `pk` >= @var AND `pk` <= @var + 1 | |
|
92 |
SET @var = half_digit ; UPDATE _table SET update_both WHERE `pk` BETWEEN @var AND @var + 1 ; |
|
93 |
||
94 |
update_two: |
|
95 |
UPDATE _table SET `col_int_key` = `col_int_key` - 10 WHERE `pk` = small ; UPDATE _table SET `col_int_key` = `col_int_key` + 10 WHERE `pk` = big ; |
|
96 |
||
97 |
update_limit: |
|
98 |
UPDATE _table SET update_one_half + IF(`pk` % 2 = 1 , 20, -20) WHERE `pk` >= half_digit ORDER BY `pk` ASC LIMIT 2 ; |
|
99 |
||
100 |
update_in: |
|
101 |
UPDATE _table SET update_one_half + IF(`pk` % 2 = 1 , 30, -30) WHERE `pk` IN ( even_odd ) ; |
|
102 |
||
103 |
insert_one: |
|
104 |
INSERT INTO _table ( `pk` , `col_int_key` , `col_int`) VALUES ( NULL , 100 , 100 ) | |
|
105 |
INSERT INTO _table ( `pk` ) VALUES ( NULL ) ; ROLLBACK ; |
|
106 |
||
107 |
insert_multi: |
|
108 |
INSERT INTO _table ( `pk` , `col_int_key` , `col_int`) VALUES ( NULL , 100 , 100 ) , ( NULL , 100 , 100 ) | |
|
109 |
INSERT INTO _table ( `pk` ) VALUES ( NULL ) , ( NULL ) , ( NULL ) ; ROLLBACK ; |
|
110 |
||
111 |
insert_select: |
|
112 |
INSERT INTO _table ( `col_int_key` , `col_int` ) SELECT `col_int` , `col_int_key` FROM _table WHERE `pk` > 10 LIMIT _digit ; |
|
113 |
||
114 |
insert_delete: |
|
115 |
INSERT INTO _table ( `pk` , `col_int_key` , `col_int` ) VALUES ( NULL , 50 , 60 ) ; DELETE FROM _table WHERE `pk` = @@LAST_INSERT_ID ; |
|
116 |
||
117 |
insert_update: |
|
118 |
INSERT INTO _table ( `pk` , `col_int_key` , `col_int` ) VALUES ( NULL, 170 , 180 ) ; UPDATE _table SET `col_int_key` = `col_int_key` - 80 , `col_int` = `col_int` - 70 WHERE `pk` = _digit ; |
|
119 |
||
120 |
replace: |
|
121 |
REPLACE INTO _table ( `pk` , `col_int_key` , `col_int` ) VALUES ( NULL, 100 , 100 ) | |
|
122 |
REPLACE INTO _table ( `pk` ) VALUES ( _digit ) ; ROLLBACK ; |
|
123 |
||
124 |
delete_one: |
|
125 |
DELETE FROM _table WHERE `pk` = _tinyint_unsigned AND `pk` > 10; |
|
126 |
||
127 |
delete_multi: |
|
128 |
DELETE FROM _table WHERE `pk` > _tinyint_unsigned AND `pk` > 10 LIMIT _digit ; |
|
129 |
||
130 |
update_both: |
|
131 |
`col_int_key` = `col_int_key` - 20, `col_int` = `col_int` + 20 | |
|
132 |
`col_int` = `col_int` + 30, `col_int_key` = `col_int_key` - 30 ; |
|
133 |
||
134 |
update_one_half: |
|
135 |
`col_int_key` = `col_int_key` | |
|
136 |
`col_int` = `col_int` ; |
|
137 |
||
138 |
key_nokey_pk: |
|
139 |
`col_int_key` | `col_int` | `pk` ; |
|
140 |
||
141 |
value: |
|
142 |
_digit; |
|
143 |
||
144 |
half_digit: |
|
145 |
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 ; |
|
146 |
||
147 |
even_odd: |
|
148 |
odd , even | even , odd ; |
|
149 |
||
150 |
odd: |
|
151 |
1 | 3 | 5 | 7 | 9 ; |
|
152 |
||
153 |
even: |
|
154 |
2 | 4 | 6 | 8 ; |
|
155 |
||
156 |
small: |
|
157 |
1 | 2 | 3 | 4 ; |
|
158 |
||
159 |
big: |
|
160 |
5 | 6 | 7 | 8 | 9 ; |
|
161 |
||
162 |
_digit: |
|
163 |
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 ; |