1
# Copyright (C) 2008-2010 Sun Microsystems, Inc. All rights reserved.
2
# Use is subject to license terms.
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.
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.
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
18
# **NOTE** Joins for this grammar are currently not working as intended.
19
# For example, if we have tables 1, 2, and 3, we end up with ON conditions that
20
# only involve tables 2 and 3.
21
# This will be fixed, but initial attempts at altering this had a negative
22
# impact on the coverage the test was providing. To be fixed when scheduling
23
# permits. We are still seeing significant coverage with the grammar as-is.
25
################################################################################
26
# partition_pruning.yy
28
# Purpose: RQG grammar for testing partitioning by COLUMN_LIST
29
# , introduced in WL#3352
31
# NOTES: * Must be used with conf/partitioning/partition_pruning.zz as the gendata
34
# ** server-server comparisons, particularly
35
# comparing a version of MySQL without partitioning
36
# (like 5.0) to one that has partition_pruning
38
# ** Single-server stress / endurance testing
40
# * Will NOT work if the server has partitioning, but
41
# not partitioning by COLUMN_LIST and
42
# * Basically, the grammar was designed to ensure
43
# that no result-set differences occur via the
44
# server-server compare and to look for crashes
46
# * the gendata file, conf/partitioning/partition_pruning.zz
47
# contains the table setups / partitioning schemes
48
################################################################################
52
{ @nonaggregates = () ; $tables = 0 ; $fields = 0 ; "" } query_type ;
54
################################################################################
55
# We use a mix of SELECT, UPDATE, INSERT, and DELETE to ensure data consistency
56
################################################################################
59
select | select | select | select | select |
60
select | select | select | select | select |
61
select | select | select | select | select |
62
update | update | update | delete | delete |
65
distict: DISTINCT | | | | | | | | | ;
67
select_option: SQL_SMALL_RESULT | | | | | | | | | | | | | | | | | | | | ;
70
| | | | | | | | | explain_extended2 ;
72
explain_extended2: | | | | EXPLAIN | EXPLAIN EXTENDED ;
76
simple_select | mixed_select ;
79
explain_extended SELECT simple_select_list
88
explain_extended SELECT select_list
95
################################################################################
96
# For every UPDATE, INSERT and DELETE, we first do the action (UPDATE, etc)
97
# *then* we do a SELECT COUNT(*), looking for those rows that should have been
98
# touched by the previous query - most useful in server-server comparisons
99
################################################################################
102
int_update | char_update ;
105
int_update_query ; int_select_count ;
108
UPDATE _table[invariant] SET `col_int_signed` = _digit[invariant] WHERE special_where_list ;
111
SELECT COUNT(*) FROM _table[invariant] WHERE `col_int_signed` = _digit[invariant];
114
utf8_char_update | utf8_char_update2 | utf8_char_update3 ;
117
utf8_char_update_query ; utf8_char_select_count ;
119
utf8_char_update_query:
120
UPDATE _table[invariant] SET `col_varchar_256_utf8` = _char[invariant] WHERE special_where_list;
122
utf8_char_select_count:
123
SELECT COUNT(*) FROM _table[invariant] WHERE `col_varchar_256_utf8` = _char[invariant];
126
utf8_char_update_query2 ; utf8_char_select_count2 ;
128
utf8_char_update_query2:
129
UPDATE _table[invariant] SET `col_varchar_512_utf8` = _char[invariant] WHERE special_where_list;
131
utf8_char_select_count2:
132
SELECT COUNT(*) FROM _table[invariant] WHERE `col_varchar_512_utf8` = _char[invariant];
135
utf8_char_update_query3 ; utf8_char_select_count3 ;
137
utf8_char_update_query3:
138
UPDATE _table[invariant] SET `col_varchar_5_utf8` = _char[invariant] WHERE special_where_list;
140
utf8_char_select_count3:
141
SELECT COUNT(*) FROM _table[invariant] WHERE `col_varchar_5_utf8` = _char[invariant];
148
int_delete_query ; int_select_count ;
151
DELETE FROM _table[invariant] WHERE `col_int_signed` = _digit[invariant] AND special_where_list ;
154
not ( special_where_item ) | not ( special_where_item ) |
155
( special_where_list and_or special_where_item ) ;
158
_table[invariant] . partitioned_int_field comparison_operator _digit |
159
_table[invariant] . int_field comparison_operator _digit |
160
_table[invariant] . partitioned_int_field not BETWEEN _digit[invariant] AND ( _digit[invariant] + _digit ) |
161
_table[invariant] . int_field not BETWEEN _digit[invariant] AND ( _digit[invariant] + _digit ) |
162
_table[invariant] . partitioned_int_field not IN ( number_list ) |
163
_table[invariant] . int_field not in (number_list) |
164
_table[invariant] . partitioned_char_field comparison_operator _char |
165
_table[invariant] . partitioned_char_field not IN (char_list ) |
166
_table[invariant] . char_field not IN (char_list) ;
169
INSERT INTO _table SELECT _field_list FROM _table[invariant] WHERE special_where_list ORDER BY _field_list LIMIT _digit ;
171
#######################################################
173
#######################################################
176
new_select_item | new_select_item | new_select_item |
177
new_select_item, select_list | new_select_item, select_list ;
180
nonaggregate_select_item | nonaggregate_select_item | nonaggregate_select_item |
181
nonaggregate_select_item | nonaggregate_select_item, simple_select_list | nonaggregate_select_item, simple_select_list ;
184
( new_table_item join_type ( ( new_table_item join_type new_table_item ON ( join_condition ) ) ) ON ( join_condition ) ) ;
187
new_table_item join_type new_table_item ON ( join_condition ) |
188
new_table_item | new_table_item | new_table_item | new_table_item | new_table_item ;
191
INNER JOIN | left_right outer JOIN | STRAIGHT_JOIN ;
194
current_table_item . int_indexed = previous_table_item . int_indexed |
195
current_table_item . char_indexed = previous_table_item . char_indexed ;
198
#########################################################
199
# We use partition pruning friendly clauses here
200
#########################################################
203
not ( where_item ) | not ( where_item ) |
204
not ( where_list and_or where_item ) ;
207
table1 . partitioned_int_field comparison_operator existing_table_item . int_field |
208
table1 . partitioned_int_field comparison_operator _digit |
209
table1 . partitioned_int_field not BETWEEN _digit[invariant] AND ( _digit[invariant] + _digit ) |
210
table1 . partitioned_int_field not IN ( number_list ) |
211
table1 . partitioned_char_field comparison_operator _char |
212
table1 . partitioned_char_field not IN (char_list ) |
213
table1 . utf8_char_field comparison_operator existing_table_item . utf8_char_field |
214
table1 . latin1_char_field comparison_operator existing_table_item . latin1_char_field |
215
table1 . cp932_char_field comparison_operator existing_table_item . cp932_char_field |
216
table1 . `col_date` comparison_operator _date |
217
table1 . `col_datetime` comparison_operator _datetime |
218
table1 . date_field comparison_operator _date |
219
table1 . char_field comparison_operator _char |
220
table1 . int_field comparison_operator _digit ;
222
partitioned_int_field:
225
partitioned_char_field:
226
`col_varchar_5_utf8` | `col_varchar_5_cp932` | `col_varchar_5_latin1` |
227
`col_varchar_256_utf8` | `col_varchar_256_cp932` | `col_varchar_256_latin1` |
228
`col_varchar_512_utf8` | `col_varchar_512_cp932` | `col_varchar_512_latin1` ;
231
`col_int_signed` | `col_int_signed_key` ;
234
`col_varchar_5_utf8` | `col_varchar_5_utf8_key` | `col_varchar_256_utf8` | `col_varchar_256_utf8_key` | `col_varchar_512_utf8` | `col_varchar_512_utf8_key` ;
237
`col_varchar_5_latin1` | `col_varchar_5_latin1_key` | `col_varchar_256_latin1` | `col_varchar_256_latin1_key` | `col_varchar_512_latin1` | `col_varchar_512_latin1_key`;
240
`col_varchar_5_cp932` | `col_varchar_5_cp932_key` | `col_varchar_256_cp932` | `col_varchar_256_cp932_key` | `col_varchar_512_cp932` | `col_varchar_512_cp932_key` ;
243
utf8_char_field | latin1_char_field | cp932_char_field ;
246
`col_datetime` | `col_date_key` | `col_datetime_key` | `col_date` ;
249
char_field | date_field ;
252
_digit | number_list, _digit ;
255
char | char_list, char ;
258
_char | _char | _char | _char | _char |
259
_char | _char | _char | _char | big_char ;
262
_varchar(512) | _varchar(1024) ;
264
#########################################################
265
# GROUP BY / HAVING / ORDER BY rules
266
#########################################################
268
{ scalar(@nonaggregates) > 0 ? " GROUP BY ".join (', ' , @nonaggregates ) : "" } ;
271
| | | | | | | | | group_by_clause ;
274
| | | | | | | | | HAVING having_list;
277
not ( having_item ) |
278
not ( having_item ) |
279
(having_list and_or having_item) ;
282
existing_table_item . char_field comparison_operator _char |
283
existing_table_item . int_field comparison_operator _digit |
284
existing_table_item . date_field comparison_operator _date |
285
existing_table_item . char_field comparison_operator existing_table_item . char_field |
286
existing_table_item . int_field comparison_operator existing_table_item . int_field |
287
existing_table_item . date_field comparison_operator existing_table_tiem . date_field ;
290
| | | | ORDER BY total_order_by desc limit ;
293
{ join(', ', map { "field".$_ } (1..$fields) ) };
299
| | | | | | | | | | LIMIT limit_size | LIMIT limit_size OFFSET _digit;
302
1 | 2 | 10 | 100 | 1000;
304
#########################################################
305
# query component rules
306
#########################################################
309
nonaggregate_select_item | aggregate_select_item ;
311
nonaggregate_select_item:
312
table_one_two . _field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
314
aggregate_select_item:
315
aggregate table_one_two . non_int_field ) AS { "field".++$fields } |
316
int_aggregate table_one_two . int_field ) AS { "field".++$fields } ;
320
_table AS { "table".++$tables };
325
# disabled this to reduce number of bad queries
326
# can merge with rule table_one_two to add more
327
# variety to queries, but it is recommended that
328
# table1 is still favored disproportionately
329
# (there aren't a lot of joins)
330
table_one_two_disabled:
337
{ "table".($tables - 1) };
340
{ "table".$prng->int(1,$tables) };
348
existing_select_item:
349
{ "field".$prng->int(1,$fields) };
352
`col_int_signed_key` ;
355
`col_varchar_256_utf8_key` | `col_varchar_512_utf8_key` |
356
`col_varchar_256_latin1_key` | `col_varchar_512_latin1_key` |
357
`col_varchar_256_cp932_key` | `col_varchar_512_cp932_key` ;
360
= | > | < | != | <> | <= | >= ;
363
COUNT( distinct | MIN( distinct | MAX( distinct ;
369
AND | AND | AND | AND | OR | OR | XOR ;
376
_digit | _digit | _digit | _digit | _digit |
377
_char | _char | _char | _char | _char ;