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
# optimizer_no_subquery.yy: Random Query Generator grammar for testing #
27
# non-subquery optimizations. This grammar #
28
# *should* hit the optimizations listed here: #
29
# https://inside.mysql.com/wiki/Optimizer_grammar_worksheet #
30
# see: WL#5006 Random Query Generator testing of Azalea Optimizer- subqueries #
31
# https://intranet.mysql.com/worklog/QA-Sprint/?tid=5006 #
34
# queries: 10k+. We can see a lot with lower values, but over 10k is #
35
# best. The intersect optimization happens with low frequency #
36
# so larger values help us to hit it at least some of the time #
37
# engines: MyISAM, Innodb, Memory. Certain optimizations are only hit with #
38
# one engine or another and we should use both to ensure we #
39
# are getting maximum coverage #
40
# Validators: ResultsetComparatorSimplify #
41
# - used on server-server comparisons #
42
# Transformer - used on a single server #
43
# - creates equivalent versions of a single query #
44
# SelectStability - used on a single server #
45
# - ensures the same query produces stable result sets #
46
################################################################################
48
################################################################################
49
# The perl code in {} helps us with bookkeeping for writing more sensible #
50
# queries. We need to keep track of these items to ensure we get interesting #
51
# and stable queries that find bugs rather than wondering if our query is #
53
################################################################################
55
{ @nonaggregates = () ; $tables = 0 ; $fields = 0 ; "" } query_type ;
59
main_select | main_select | main_select | loose_scan ;
61
################################################################################
62
# The loose* rules listed below are to hit the 'Using index for group-by' #
63
# optimization. This optimization has some strict requirements, thus #
64
# we needed a separate query pattern to ensure we hit it. #
65
################################################################################
67
SELECT distinct loose_select_clause
69
WHERE generic_where_list
74
MIN( _field_indexed) AS { "field".++$fields } , loose_select_list |
75
MAX( _field_indexed) AS { "field".++$fields } , loose_select_list |
76
MIN( _field_indexed[invariant] ) AS { "field".++$fields }, MAX( _field_indexed[invariant] ) AS { "field".++$fields }, loose_select_list ;
80
loose_select_item , loose_select_list ;
83
_field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
85
################################################################################
86
# The bulk of interesting things happen with this main rule #
87
################################################################################
89
simple_select | simple_select | aggregate_select |
90
mixed_select | mixed_select | mixed_select ;
93
SELECT distinct straight_join select_option select_list
101
SELECT distinct straight_join select_option simple_select_list
109
SELECT distinct straight_join select_option aggregate_select_list
116
distinct: DISTINCT | | | | ;
118
select_option: | | | | | | | | | SQL_SMALL_RESULT ;
120
straight_join: | | | | | | | | | | | | | | | | | | | | | STRAIGHT_JOIN ;
124
new_select_item , select_list |
125
new_select_item , select_list ;
128
nonaggregate_select_item |
129
nonaggregate_select_item , simple_select_list |
130
nonaggregate_select_item , simple_select_list ;
132
aggregate_select_list:
133
aggregate_select_item | aggregate_select_item |
134
aggregate_select_item, aggregate_select_list ;
137
################################################################################
138
# this limits us to 2 and 3 table joins / can use it if we hit #
139
# too many mega-join conditions which take too long to run #
140
################################################################################
141
( new_table_item join_type new_table_item ON (join_condition_list ) ) |
142
( new_table_item join_type ( ( new_table_item join_type new_table_item ON (join_condition_list ) ) ) ON (join_condition_list ) ) ;
145
################################################################################
146
# preventing deep join nesting for run time / table access methods are more #
147
# important here - join.yy can provide deeper join coverage #
148
# Enabling this / swapping out with join_list above can produce some #
149
# time-consuming queries. #
150
################################################################################
153
( new_table_item join_type join_list ON (join_condition_list ) ) ;
156
INNER JOIN | left_right outer JOIN | STRAIGHT_JOIN ;
159
join_condition_item |
160
( join_condition_item ) and_or ( join_condition_item ) |
161
( current_table_item .`pk` arithmetic_operator previous_table_item . _field ) AND (current_table_item .`pk` arithmetic_operator previous_table_item . _field ) ;
164
current_table_item . int_indexed = previous_table_item . int_field_name |
165
current_table_item . int_field_name = previous_table_item . int_indexed |
166
current_table_item . `col_varchar_key` = previous_table_item . char_field_name |
167
current_table_item . char_field_name = previous_table_item . `col_varchar_key` |
168
current_table_item . int_indexed arithmetic_operator previous_table_item . int_field_name |
169
current_table_item . int_field_name arithmetic_operator previous_table_item . int_indexed |
170
current_table_item . `col_varchar_key` arithmetic_operator previous_table_item . char_field_name |
171
current_table_item . char_field_name arithmetic_operator previous_table_item . `col_varchar_key`;
186
range_predicate1_list | range_predicate2_list |
187
range_predicate1_list and_or generic_where_list |
188
range_predicate2_list and_or generic_where_list ;
193
( where_list and_or where_item ) ;
198
################################################################################
199
# The IS not NULL values in where_item are to hit the ref_or_null and #
200
# the not_exists optimizations. The LIKE '%a%' rule is to try to hit the #
201
# rnd_pos optimization #
202
################################################################################
204
alias1 .`pk` arithmetic_operator existing_table_item . _field |
205
alias1 .`pk` arithmetic_operator existing_table_item . _field |
206
existing_table_item . _field arithmetic_operator value |
207
existing_table_item . _field arithmetic_operator existing_table_item . _field |
208
existing_table_item . _field arithmetic_operator value |
209
existing_table_item . _field arithmetic_operator existing_table_item . _field |
210
alias1 .`pk` IS not NULL |
211
alias1 . _field IS not NULL |
212
alias1 . _field_indexed arithmetic_operator value AND ( alias1 . char_field_name LIKE '%a%' OR alias1.char_field_name LIKE '%b%') ;
214
################################################################################
215
# The range_predicate_1* rules below are in place to ensure we hit the #
216
# index_merge/sort_union optimization. #
217
# NOTE: combinations of the predicate_1 and predicate_2 rules tend to hit the #
218
# index_merge/intersect optimization #
219
################################################################################
221
range_predicate1_list:
222
range_predicate1_item |
223
( range_predicate1_item OR range_predicate1_list ) ;
225
range_predicate1_item:
226
alias1 . int_indexed not BETWEEN _tinyint_unsigned[invariant] AND ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) |
227
alias1 . `col_varchar_key` arithmetic_operator _char[invariant] |
228
alias1 . int_indexed not IN (number_list) |
229
alias1 . `col_varchar_key` not IN (char_list) |
230
alias1 . `pk` > _tinyint_unsigned[invariant] AND alias1 . `pk` < ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) |
231
alias1 . `col_int_key` > _tinyint_unsigned[invariant] AND alias1 . `col_int_key` < ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) ;
233
################################################################################
234
# The range_predicate_2* rules below are in place to ensure we hit the #
235
# index_merge/union optimization. #
236
# NOTE: combinations of the predicate_1 and predicate_2 rules tend to hit the #
237
# index_merge/intersect optimization #
238
################################################################################
240
range_predicate2_list:
241
range_predicate2_item |
242
( range_predicate2_item and_or range_predicate2_list ) ;
244
range_predicate2_item:
245
alias1 . `pk` = _tinyint_unsigned |
246
alias1 . `col_int_key` = _tinyint_unsigned |
247
alias1 . `col_varchar_key` = _char |
248
alias1 . int_indexed = _tinyint_unsigned |
249
alias1 . `col_varchar_key` = _char |
250
alias1 . int_indexed = existing_table_item . int_indexed |
251
alias1 . `col_varchar_key` = existing_table_item . `col_varchar_key` ;
253
################################################################################
254
# The number and char_list rules are for creating WHERE conditions that test #
255
# 'field' IN (list_of_items) #
256
################################################################################
258
_tinyint_unsigned | number_list, _tinyint_unsigned ;
261
_char | char_list, _char ;
263
################################################################################
264
# We ensure that a GROUP BY statement includes all nonaggregates. #
265
# This helps to ensure the query is more useful in detecting real errors / #
266
# that the query doesn't lend itself to variable result sets #
267
################################################################################
269
{ scalar(@nonaggregates) > 0 ? " GROUP BY ".join (', ' , @nonaggregates ) : "" } ;
272
| | group_by_clause ;
275
| HAVING having_list;
280
(having_list and_or having_item) ;
283
existing_select_item arithmetic_operator value ;
285
################################################################################
286
# We use the total_order_by rule when using the LIMIT operator to ensure that #
287
# we have a consistent result set - server1 and server2 should not differ #
288
################################################################################
292
ORDER BY total_order_by , alias1 . _field_indexed desc limit |
293
ORDER BY order_by_list |
294
ORDER BY total_order_by, order_by_list limit ;
297
{ join(', ', map { "field".$_ } (1..$fields) ) };
301
order_by_item , order_by_list ;
304
alias1 . _field_indexed , existing_table_item .`pk` desc |
305
alias1 . _field_indexed desc |
306
existing_select_item desc ;
311
################################################################################
312
# We mix digit and _digit here. We want to alter the possible values of LIMIT #
313
# To ensure we hit varying EXPLAIN plans, but the OFFSET can be smaller #
314
################################################################################
317
| | LIMIT limit_size | LIMIT limit_size OFFSET _digit;
320
nonaggregate_select_item |
321
nonaggregate_select_item |
322
nonaggregate_select_item |
323
nonaggregate_select_item |
324
nonaggregate_select_item |
325
nonaggregate_select_item |
326
nonaggregate_select_item |
327
nonaggregate_select_item |
328
aggregate_select_item |
331
################################################################################
332
# We have the perl code here to help us write more sensible queries #
333
# It allows us to use field1...fieldn in the WHERE, ORDER BY, and GROUP BY #
334
# clauses so that the queries will produce more stable and interesting results #
335
################################################################################
337
nonaggregate_select_item:
338
table_one_two . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
339
table_one_two . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
340
table_one_two . _field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
342
aggregate_select_item:
343
aggregate table_one_two . _field ) AS { "field".++$fields };
345
################################################################################
346
# The combo_select_items are for 'spice' - we actually found #
347
################################################################################
350
( ( table_one_two . int_field_name ) math_operator ( table_one_two . int_field_name ) ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
351
CONCAT ( table_one_two . char_field_name , table_one_two . char_field_name ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
357
COUNT( distinct | SUM( distinct | MIN( distinct | MAX( distinct ;
359
################################################################################
360
# The following rules are for writing more sensible queries - that we don't #
361
# reference tables / fields that aren't present in the query and that we keep #
362
# track of what we have added. You shouldn't need to touch these ever #
363
################################################################################
365
_table AS { "alias".++$tables };
371
{ "alias".($tables - 1) };
374
{ "alias".$prng->int(1,$tables) };
376
existing_select_item:
377
{ "field".$prng->int(1,$fields) };
378
################################################################################
379
# end of utility rules #
380
################################################################################
383
= | > | < | != | <> | <= | >= ;
385
################################################################################
386
# We are trying to skew the ON condition for JOINs to be largely based on #
387
# equalities, but to still allow other arithmetic operators #
388
################################################################################
389
join_condition_operator:
390
arithmetic_operator | = | = | = ;
392
################################################################################
393
# Used for creating combo_items - ie (field1 + field2) AS fieldX #
394
# We ignore division to prevent division by zero errors #
395
################################################################################
399
################################################################################
400
# We stack AND to provide more interesting options for the optimizer #
401
# Alter these percentages at your own risk / look for coverage regressions #
402
# with --debug if you play with these. Those optimizations that require an #
403
# OR-only list in the WHERE clause are specifically stacked in another rule #
404
################################################################################
410
_digit | _digit | _digit | _digit | _tinyint_unsigned|
411
_char(2) | _char(2) | _char(2) | _char(2) | _char(2) ;
414
A | B | C | BB | CC | B | C | BB | CC |
415
C | C | C | C | C | C | C | C | C |
416
CC | CC | CC | CC | CC | CC | CC | CC |
419
################################################################################
420
# Add a possibility for 'view' to occur at the end of the previous '_table' rule
421
# to allow a chance to use views (when running the RQG with --views)
422
################################################################################
425
view_A | view_B | view_C | view_BB | view_CC ;
428
int_field_name | char_field_name ;
431
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | _tinyint_unsigned ;
435
`pk` | `col_int_key` | `col_int_nokey` ;
438
`pk` | `col_int_key` ;
442
`col_varchar_key` | `col_varchar_nokey` ;
444
################################################################################
445
# We define LIMIT_rows in this fashion as LIMIT values can differ depending on #
446
# how large the LIMIT is - LIMIT 2 = LIMIT 9 != LIMIT 19 #
447
################################################################################
450
1 | 2 | 10 | 100 | 1000;