« back to all changes in this revision
Viewing changes to conf/optimizer_no_subquery.yy
- browse files at revision 0.67.9
- compare with another revision
- compare with revision 2426.3.1
- download diff
- download tarball
- view history from revision 0.67.9
- Committer: Philip Stoev
- Date: 2009-10-03 12:26:59 UTC
- mto: (0.79.2 randgen-trunk) (2312.4.1 drizzle-randgen)
- mto: This revision was merged to the branch mainline in revision 2435.
- Revision ID: pstoev@mysql.com-20091003122659-q2f7jsoli6ghxx3e
merge from internal tree
- files added:
- conf/backup_interop.yy
- lib/GenTest/Filter
- lib/GenTest/Translator
- files modified:
- combinations.pl
added
removed
conf/optimizer_no_subquery.yy
1
################################################################################
2
# optimizer_no_subquery.yy: Random Query Generator grammar for testing #
3
# non-subquery optimizations. This grammar #
4
# *should* hit the optimizations listed here: #
5
# https://inside.mysql.com/wiki/Optimizer_grammar_worksheet #
6
# see: WL#5006 Random Query Generator testing of Azalea Optimizer- subqueries #
7
# https://intranet.mysql.com/worklog/QA-Sprint/?tid=5006 #
8
# #
9
# recommendations: #
10
# queries: 10k+. We can see a lot with lower values, but over 10k is #
11
# best. The intersect optimization happens with low frequency #
12
# so larger values help us to hit it at least some of the time #
13
# engines: MyISAM *and* Innodb. Certain optimizations are only hit with #
14
# one engine or another and we should use both to ensure we #
15
# are getting maximum coverage #
16
# Validators: ResultsetComparatorSimplify #
17
# - used on server-server comparisons #
18
# Transformer - used on a single server #
19
# - creates equivalent versions of a single query #
20
# SelectStability - used on a single server #
21
# - ensures the same query produces stable result sets #
22
################################################################################
23
24
################################################################################
25
# The perl code in {} helps us with bookkeeping for writing more sensible #
26
# queries. We need to keep track of these items to ensure we get interesting #
27
# and stable queries that find bugs rather than wondering if our query is #
28
# dodgy. #
29
################################################################################
30
query:
31
{ @nonaggregates = () ; $tables = 0 ; $fields = 0 ; "" } query_type ;
32
33
34
query_type:
35
main_select | main_select | main_select | loose_scan ;
36
37
################################################################################
38
# The loose* rules listed below are to hit the 'Using index for group-by' #
39
# optimization. This optimization has some strict requirements, thus #
40
# we needed a separate query pattern to ensure we hit it. #
41
################################################################################
42
loose_scan:
43
SELECT distinct loose_select_clause
44
FROM new_table_item
45
WHERE generic_where_list
46
group_by_clause ;
47
48
loose_select_clause:
49
loose_select_list |
50
MIN( _field_indexed) AS { "field".++$fields } , loose_select_list |
51
MAX( _field_indexed) AS { "field".++$fields } , loose_select_list |
52
MIN( _field_indexed[invariant] ) AS { "field".++$fields }, MAX( _field_indexed[invariant] ) AS { "field".++$fields }, loose_select_list ;
53
54
loose_select_list:
55
loose_select_item |
56
loose_select_item , loose_select_list ;
57
58
loose_select_item:
59
_field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
60
61
################################################################################
62
# The bulk of interesting things happen with this main rule #
63
################################################################################
64
main_select:
65
simple_select | simple_select | mixed_select ;
66
67
mixed_select:
68
SELECT distinct select_option select_list
69
FROM join_list
70
where_clause
71
group_by_clause
72
having_clause
73
order_by_clause ;
74
75
simple_select:
76
SELECT distinct select_option simple_select_list
77
FROM join_list
78
where_clause
79
optional_group_by
80
having_clause
81
order_by_clause ;
82
83
aggregate_select:
84
SELECT distinct select_option aggregate_select_list
85
FROM join_list
86
where_clause
87
optional_group_by
88
having_clause
89
order_by_clause ;
90
91
distinct: DISTINCT | | | | ;
92
93
select_option: | | | | | | | | | SQL_SMALL_RESULT ;
94
95
select_list:
96
new_select_item |
97
new_select_item , select_list |
98
new_select_item , select_list ;
99
100
simple_select_list:
101
nonaggregate_select_item |
102
nonaggregate_select_item , simple_select_list |
103
nonaggregate_select_item , simple_select_list ;
104
105
aggregate_select_list:
106
aggregate_select_item | aggreagate_select_item |
107
aggregate_select_item, aggregate_select_list ;
108
109
join_list:
110
################################################################################
111
# this limits us to 2 and 3 table joins / can use it if we hit #
112
# too many mega-join conditions which take too long to run #
113
################################################################################
114
( new_table_item join_type new_table_item ON (join_condition_list ) ) |
115
( new_table_item join_type ( ( new_table_item join_type new_table_item ON (join_condition_list ) ) ) ON (join_condition_list ) ) ;
116
117
join_list_disabled:
118
################################################################################
119
# preventing deep join nesting for run time / table access methods are more #
120
# important here - join.yy can provide deeper join coverage #
121
# Enabling this / swapping out with join_list above can produce some #
122
# time-consuming queries. #
123
################################################################################
124
125
new_table_item |
126
( new_table_item join_type join_list ON (join_condition_list ) ) ;
127
128
join_type:
129
INNER JOIN | left_right outer JOIN | STRAIGHT_JOIN ;
130
131
join_condition_list:
132
join_condition_item |
133
( join_condition_item ) and_or ( join_condition_item ) |
134
( current_table_item .`pk` arithmetic_operator previous_table_item . _field ) AND (current_table_item .`pk` arithmetic_operator previous_table_item . _field ) ;
135
136
join_condition_item:
137
current_table_item . int_indexed = previous_table_item . int_field_name |
138
current_table_item . int_field_name = previous_table_item . int_indexed |
139
current_table_item . `varchar_key` = previous_table_item . char_field_name |
140
current_table_item . char_field_name = previous_table_item . `varchar_key` |
141
current_table_item . int_indexed arithmetic_operator previous_table_item . int_field_name |
142
current_table_item . int_field_name arithmetic_operator previous_table_item . int_indexed |
143
current_table_item . `varchar_key` arithmetic_operator previous_table_item . char_field_name |
144
current_table_item . char_field_name arithmetic_operator previous_table_item . `varchar_key`;
145
146
147
left_right:
148
LEFT | RIGHT ;
149
150
outer:
151
| OUTER ;
152
153
where_clause:
154
| WHERE where_list ;
155
156
157
where_list:
158
generic_where_list |
159
range_predicate1_list | range_predicate2_list |
160
range_predicate1_list and_or generic_where_list |
161
range_predicate2_list and_or generic_where_list ;
162
163
164
generic_where_list:
165
where_item |
166
( where_list and_or where_item ) ;
167
168
not:
169
| | | NOT;
170
171
################################################################################
172
# The IS not NULL values in where_item are to hit the ref_or_null and #
173
# the not_exists optimizations. The LIKE '%a%' rule is to try to hit the #
174
# rnd_pos optimization #
175
################################################################################
176
where_item:
177
table1 .`pk` arithmetic_operator existing_table_item . _field |
178
table1 .`pk` arithmetic_operator existing_table_item . _field |
179
existing_table_item . _field arithmetic_operator value |
180
existing_table_item . _field arithmetic_operator existing_table_item . _field |
181
existing_table_item . _field arithmetic_operator value |
182
existing_table_item . _field arithmetic_operator existing_table_item . _field |
183
table1 .`pk` IS not NULL |
184
table1 . _field IS not NULL |
185
table1 . _field_indexed arithmetic_operator value AND ( table1 . char_field_name LIKE '%a%' OR table1.char_field_name LIKE '%b%') ;
186
187
################################################################################
188
# The range_predicate_1* rules below are in place to ensure we hit the #
189
# index_merge/sort_union optimization. #
190
# NOTE: combinations of the predicate_1 and predicate_2 rules tend to hit the #
191
# index_merge/intersect optimization #
192
################################################################################
193
194
range_predicate1_list:
195
range_predicate1_item |
196
( range_predicate1_item OR range_predicate1_list ) ;
197
198
range_predicate1_item:
199
table1 . int_indexed not BETWEEN _tinyint_unsigned[invariant] AND ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) |
200
table1 . `varchar_key` arithmetic_operator _char[invariant] |
201
table1 . int_indexed not IN (number_list) |
202
table1 . `varchar_key` not IN (char_list) |
203
table1 . `pk` > _tinyint_unsigned[invariant] AND table1 . `pk` < ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) |
204
table1 . `int_key` > _tinyint_unsigned[invariant] AND table1 . `int_key` < ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) ;
205
206
################################################################################
207
# The range_predicate_2* rules below are in place to ensure we hit the #
208
# index_merge/union optimization. #
209
# NOTE: combinations of the predicate_1 and predicate_2 rules tend to hit the #
210
# index_merge/intersect optimization #
211
################################################################################
212
213
range_predicate2_list:
214
range_predicate2_item |
215
( range_predicate2_item and_or range_predicate2_list ) ;
216
217
range_predicate2_item:
218
table1 . `pk` = _tinyint_unsigned |
219
table1 . `int_key` = _tinyint_unsigned |
220
table1 . `varchar_key` = _char |
221
table1 . int_indexed = _tinyint_unsigned |
222
table1 . `varchar_key` = _char |
223
table1 . int_indexed = existing_table_item . int_indexed |
224
table1 . `varchar_key` = existing_table_item . `varchar_key` ;
225
226
################################################################################
227
# The number and char_list rules are for creating WHERE conditions that test #
228
# 'field' IN (list_of_items) #
229
################################################################################
230
number_list:
231
_tinyint_unsigned | number_list, _tinyint_unsigned ;
232
233
char_list:
234
_char | char_list, _char ;
235
236
################################################################################
237
# We ensure that a GROUP BY statement includes all nonaggregates. #
238
# This helps to ensure the query is more useful in detecting real errors / #
239
# that the query doesn't lend itself to variable result sets #
240
################################################################################
241
group_by_clause:
242
{ scalar(@nonaggregates) > 0 ? " GROUP BY ".join (', ' , @nonaggregates ) : "" } ;
243
244
optional_group_by:
245
| | group_by_clause ;
246
247
having_clause:
248
| HAVING having_list;
249
250
having_list:
251
having_item |
252
having_item |
253
(having_list and_or having_item) ;
254
255
having_item:
256
existing_select_item arithmetic_operator value ;
257
258
################################################################################
259
# We use the total_order_by rule when using the LIMIT operator to ensure that #
260
# we have a consistent result set - server1 and server2 should not differ #
261
################################################################################
262
263
order_by_clause:
264
|
265
ORDER BY total_order_by , table1 . _field_indexed desc limit |
266
ORDER BY order_by_list |
267
ORDER BY total_order_by, order_by_list limit ;
268
269
total_order_by:
270
{ join(', ', map { "field".$_ } (1..$fields) ) };
271
272
order_by_list:
273
order_by_item |
274
order_by_item , order_by_list ;
275
276
order_by_item:
277
table1 . _field_indexed , existing_table_item .`pk` desc |
278
table1 . _field_indexed desc |
279
existing_select_item desc ;
280
281
desc:
282
ASC | | DESC ;
283
284
################################################################################
285
# We mix digit and _digit here. We want to alter the possible values of LIMIT #
286
# To ensure we hit varying EXPLAIN plans, but the OFFSET can be smaller #
287
################################################################################
288
289
limit:
290
| | LIMIT limit_size | LIMIT limit_size OFFSET _digit;
291
292
new_select_item:
293
nonaggregate_select_item |
294
nonaggregate_select_item |
295
nonaggregate_select_item |
296
nonaggregate_select_item |
297
nonaggregate_select_item |
298
nonaggregate_select_item |
299
nonaggregate_select_item |
300
nonaggregate_select_item |
301
aggregate_select_item |
302
combo_select_item ;
303
304
################################################################################
305
# We have the perl code here to help us write more sensible queries #
306
# It allows us to use field1...fieldn in the WHERE, ORDER BY, and GROUP BY #
307
# clauses so that the queries will produce more stable and interesting results #
308
################################################################################
309
310
nonaggregate_select_item:
311
table_one_two . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
312
table_one_two . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
313
table_one_two . _field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
314
315
aggregate_select_item:
316
aggregate table_one_two . _field ) AS { "field".++$fields };
317
318
################################################################################
319
# The combo_select_items are for 'spice' - we actually found #
320
################################################################################
321
322
combo_select_item:
323
( ( table_one_two . int_field_name ) math_operator ( table_one_two . int_field_name ) ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
324
CONCAT ( table_one_two . char_field_name , table_one_two . char_field_name ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
325
326
table_one_two:
327
table1 | table2 ;
328
329
aggregate:
330
COUNT( | SUM( | MIN( | MAX( ;
331
332
################################################################################
333
# The following rules are for writing more sensible queries - that we don't #
334
# reference tables / fields that aren't present in the query and that we keep #
335
# track of what we have added. You shouldn't need to touch these ever #
336
################################################################################
337
new_table_item:
338
_table AS { "table".++$tables };
339
340
current_table_item:
341
{ "table".$tables };
342
343
previous_table_item:
344
{ "table".($tables - 1) };
345
346
existing_table_item:
347
{ "table".$prng->int(1,$tables) };
348
349
existing_select_item:
350
{ "field".$prng->int(1,$fields) };
351
################################################################################
352
# end of utility rules #
353
################################################################################
354
355
arithmetic_operator:
356
= | > | < | != | <> | <= | >= ;
357
358
################################################################################
359
# We are trying to skew the ON condition for JOINs to be largely based on #
360
# equalities, but to still allow other arithmetic operators #
361
################################################################################
362
join_condition_operator:
363
arithmetic_operator | = | = | = ;
364
365
################################################################################
366
# Used for creating combo_items - ie (field1 + field2) AS fieldX #
367
# We ignore division to prevent division by zero errors #
368
################################################################################
369
math_operator:
370
+ | - | * ;
371
372
################################################################################
373
# We stack AND to provide more interesting options for the optimizer #
374
# Alter these percentages at your own risk / look for coverage regressions #
375
# with --debug if you play with these. Those optimizations that require an #
376
# OR-only list in the WHERE clause are specifically stacked in another rule #
377
################################################################################
378
and_or:
379
AND | AND | OR ;
380
381
382
value:
383
_digit | _digit | _digit | _digit | _tinyint_unsigned|
384
_char(2) | _char(2) | _char(2) | _char(2) | _char(2) ;
385
386
_table:
387
A | B | C | BB | CC | B | C | BB | CC |
388
C | C | C | C | C | C | C | C | C |
389
CC | CC | CC | CC | CC | CC | CC | CC |
390
D ;
391
392
################################################################################
393
# Add a possibility for 'view' to occur at the end of the previous '_table' rule
394
# to allow a chance to use views (when running the RQG with --views)
395
################################################################################
396
397
view:
398
_A | _B | _C | _BB | _CC ;
399
400
_field:
401
int_field_name | char_field_name ;
402
403
_digit:
404
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | _tinyint_unsigned ;
405
406
407
int_field_name:
408
`pk` | `int_key` | `int_nokey` ;
409
410
int_indexed:
411
`pk` | `int_key` ;
412
413
414
char_field_name:
415
`varchar_key` | `varchar_nokey` ;
416
417
################################################################################
418
# We define LIMIT_rows in this fashion as LIMIT values can differ depending on #
419
# how large the LIMIT is - LIMIT 2 = LIMIT 9 != LIMIT 19 #
420
################################################################################
421
422
limit_size:
423
1 | 2 | 10 | 100 | 1000;
Loggerhead is a web-based interface for Breezy
Version: 2.0.1