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_subquery.yy: Random Query Generator grammar for testing subquery #
27
# optimizations. This grammar *should* hit the #
28
# 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 *and* Innodb. 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 ; $subquery_idx=0 ; $child_subquery_idx=0 ; "" } main_select /* _thread_id */ ;
58
simple_select | simple_select | simple_select | simple_select |
59
mixed_select | mixed_select | mixed_select | mixed_select |
63
explain_extended SELECT distinct straight_join select_option select_list
71
explain_extended SELECT distinct straight_join select_option simple_select_list
79
explain_extended SELECT distinct straight_join select_option aggregate_select_list
87
| | | | | | | | | explain_extended2 ;
89
explain_extended2: | | | | EXPLAIN | EXPLAIN EXTENDED ;
91
distinct: DISTINCT | | | | | | | | | ;
93
select_option: | | | | | | | | | | | SQL_SMALL_RESULT ;
95
straight_join: | | | | | | | | | | | | | | | | | | | | | STRAIGHT_JOIN ;
99
new_select_item , select_list |
100
new_select_item , select_list ;
103
nonaggregate_select_item |
104
nonaggregate_select_item , simple_select_list |
105
nonaggregate_select_item , simple_select_list ;
107
aggregate_select_list:
108
aggregate_select_item | aggregate_select_item |
109
aggregate_select_item, aggregate_select_list ;
112
################################################################################
113
# this limits us to 2 and 3 table joins / can use it if we hit #
114
# too many mega-join conditions which take too long to run #
115
################################################################################
116
( new_table_item join_type new_table_item ON (join_condition_item ) ) |
117
( new_table_item join_type ( ( new_table_item join_type new_table_item ON (join_condition_item ) ) ) ON (join_condition_item ) ) |
118
( new_table_item , new_table_item ) |
119
( new_table_item , new_table_item , new_table_item ) ;
123
################################################################################
124
# preventing deep join nesting for run time / table access methods are more #
125
# important here - join.yy can provide deeper join coverage #
126
# Enabling this / swapping out with join_list above can produce some #
127
# time-consuming queries. #
128
################################################################################
131
( new_table_item join_type join_list ON (join_condition_item ) ) ;
134
INNER JOIN | INNER JOIN | INNER JOIN | INNER JOIN | STRAIGHT_JOIN |
135
left_right outer JOIN | STRAIGHT_JOIN ;
138
current_table_item . int_indexed = previous_table_item . int_field_name on_subquery |
139
current_table_item . int_field_name = previous_table_item . int_indexed on_subquery |
140
current_table_item . `col_varchar_key` = previous_table_item . char_field_name on_subquery |
141
current_table_item . char_field_name = previous_table_item . `col_varchar_key` on_subquery ;
144
|||||||||||||||||||| { $subquery_idx += 1 ; $subquery_tables=0 ; ""} and_or general_subquery ;
154
WHERE where_subquery |
155
WHERE ( where_subquery ) and_or where_list |
156
WHERE ( where_subquery ) and_or where_list ;
161
range_predicate1_list | range_predicate2_list |
162
range_predicate1_list and_or generic_where_list |
163
range_predicate2_list and_or generic_where_list ;
168
( where_item and_or where_item ) ;
175
alias1 . int_field_name arithmetic_operator existing_table_item . int_field_name |
176
existing_table_item . char_field_name arithmetic_operator _char |
177
existing_table_item . char_field_name arithmetic_operator existing_table_item . char_field_name |
178
alias1 . _field IS not NULL |
179
alias1 . int_field_name arithmetic_operator existing_table_item . int_field_name |
180
existing_table_item . char_field_name arithmetic_operator _char |
181
existing_table_item . char_field_name arithmetic_operator existing_table_item . char_field_name |
182
alias1 . _field IS not NULL ;
184
################################################################################
186
################################################################################
189
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} subquery_type ;
192
general_subquery | special_subquery ;
195
existing_table_item . int_field_name arithmetic_operator int_single_value_subquery |
196
existing_table_item . char_field_name arithmetic_operator char_single_value_subquery |
197
existing_table_item . int_field_name membership_operator int_single_member_subquery |
198
( existing_table_item . int_field_name , existing_table_item . int_field_name ) not IN int_double_member_subquery |
199
( existing_table_item . int_field_name , existing_table_item . int_field_name ) not IN int_double_member_subquery |
200
( existing_table_item . int_field_name , existing_table_item . int_field_name ) not IN int_double_member_subquery |
201
( existing_table_item . int_field_name , existing_table_item . int_field_name ) not IN int_double_member_subquery |
202
( existing_table_item . int_field_name , existing_table_item . int_field_name ) not IN int_double_member_subquery |
203
existing_table_item . char_field_name membership_operator char_single_member_subquery |
204
( existing_table_item . char_field_name , existing_table_item . char_field_name ) not IN char_double_member_subquery |
205
( existing_table_item . char_field_name , existing_table_item . char_field_name ) not IN char_double_member_subquery |
206
( existing_table_item . char_field_name , existing_table_item . char_field_name ) not IN char_double_member_subquery |
207
( existing_table_item . char_field_name , existing_table_item . char_field_name ) not IN char_double_member_subquery |
208
( _digit, _digit ) not IN int_double_member_subquery |
209
( _char, _char ) not IN char_double_member_subquery |
210
( _digit, _digit ) not IN int_double_member_subquery |
211
( _char, _char ) not IN char_double_member_subquery |
212
( _digit, _digit ) not IN int_double_member_subquery |
213
( _char, _char ) not IN char_double_member_subquery |
214
( _digit, _digit ) not IN int_double_member_subquery |
215
( _char, _char ) not IN char_double_member_subquery |
216
( _digit, _digit ) not IN int_double_member_subquery |
217
( _char, _char ) not IN char_double_member_subquery |
218
existing_table_item . int_field_name membership_operator int_single_union_subquery |
219
existing_table_item . char_field_name membership_operator char_single_union_subquery ;
221
general_subquery_union_test_disabled:
222
existing_table_item . char_field_name arithmetic_operator all_any char_single_union_subquery_disabled |
223
existing_table_item . int_field_name arithmetic_operator all_any int_single_union_subquery_disabled ;
226
not EXISTS ( int_single_member_subquery ) |
227
not EXISTS ( char_single_member_subquery ) |
228
not EXISTS int_correlated_subquery |
229
not EXISTS char_correlated_subquery |
230
existing_table_item . int_field_name membership_operator int_correlated_subquery |
231
existing_table_item . char_field_name membership_operator char_correlated_subquery |
232
int_single_value_subquery IS not NULL |
233
char_single_value_subquery IS not NULL ;
235
int_single_value_subquery:
236
( SELECT distinct select_option aggregate subquery_table_one_two . int_field_name ) AS { "SQ".$subquery_idx."_field1" }
238
( SELECT distinct select_option aggregate subquery_table_one_two . int_field_name ) AS { "SQ".$subquery_idx."_field1" }
240
( SELECT _digit FROM DUAL ) ;
242
char_single_value_subquery:
243
( SELECT distinct select_option aggregate subquery_table_one_two . char_field_name ) AS { "SQ".$subquery_idx."_field1" }
245
( SELECT distinct select_option aggregate subquery_table_one_two . char_field_name ) AS { "SQ".$subquery_idx."_field1" }
247
( SELECT _char FROM DUAL ) ;
249
int_single_member_subquery:
250
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SQ".$subquery_idx."_field1" }
252
single_subquery_group_by
254
( SELECT _digit FROM DUAL ) ;
256
int_single_union_subquery:
257
( SELECT _digit UNION all_distinct SELECT _digit ) ;
259
int_single_union_subquery_disabled:
260
int_single_member_subquery UNION all_distinct int_single_member_subquery ;
262
int_double_member_subquery:
263
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SQ".$subquery_idx."_field1" } ,
264
subquery_table_one_two . int_field_name AS { "SQ".$subquery_idx."_field2" }
266
double_subquery_group_by
268
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SQ".$subquery_idx."_field1" } ,
269
subquery_table_one_two . int_field_name AS { "SQ".$subquery_idx."_field2" }
271
double_subquery_group_by
273
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SQ".$subquery_idx."_field1" } ,
274
subquery_table_one_two . int_field_name AS { "SQ".$subquery_idx."_field2" }
276
double_subquery_group_by
278
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SQ".$subquery_idx."_field1" } ,
279
aggregate subquery_table_one_two . int_field_name ) AS { "SQ".$subquery_idx."_field2" }
281
single_subquery_group_by
283
( SELECT _digit , _digit UNION all_distinct SELECT _digit, _digit ) ;
285
char_single_member_subquery:
286
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SQ".$subquery_idx."_field1" }
288
single_subquery_group_by
291
char_single_union_subquery:
292
( SELECT _char UNION all_distinct SELECT _char ) ;
294
char_single_union_subquery_disabled:
295
char_single_member_subquery UNION all_distinct char_single_member_subquery ;
297
char_double_member_subquery:
298
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SQ".$subquery_idx."_field1" } ,
299
subquery_table_one_two . char_field_name AS { "SQ".$subquery_idx."_field2" }
301
double_subquery_group_by
303
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SQ".$subquery_idx."_field1" } ,
304
subquery_table_one_two . char_field_name AS { "SQ".$subquery_idx."_field2" }
306
double_subquery_group_by
308
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SQ".$subquery_idx."_field1" } ,
309
subquery_table_one_two . char_field_name AS { "SQ".$subquery_idx."_field2" }
311
double_subquery_group_by
313
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SQ".$subquery_idx."_field1" } ,
314
subquery_table_one_two . char_field_name AS { "SQ".$subquery_idx."_field2" }
316
double_subquery_group_by
318
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SQ".$subquery_idx."_field1" } ,
319
aggregate subquery_table_one_two . char_field_name ) AS { "SQ".$subquery_idx."_field2" }
321
single_subquery_group_by
323
( SELECT _char , _char UNION all_distinct SELECT _char , _char ) ;
325
int_correlated_subquery:
326
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SQ".$subquery_idx."_field1" }
327
FROM subquery_join_list
328
correlated_subquery_where_clause ) ;
330
char_correlated_subquery:
331
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SQ".$subquery_idx."_field1" }
332
FROM subquery_join_list
333
correlated_subquery_where_clause ) ;
335
int_scalar_correlated_subquery:
336
( SELECT distinct select_option aggregate subquery_table_one_two . int_field_name ) AS { "SQ".$subquery_idx."_field1" }
337
FROM subquery_join_list
338
correlated_subquery_where_clause ) ;
341
FROM subquery_join_list
342
subquery_where_clause ;
344
subquery_where_clause:
345
| | WHERE subquery_where_list ;
347
correlated_subquery_where_clause:
348
WHERE correlated_subquery_where_list ;
350
correlated_subquery_where_list:
351
correlated_subquery_where_item |
352
correlated_subquery_where_item and_or correlated_subquery_where_item |
353
correlated_subquery_where_item and_or subquery_where_item ;
355
correlated_subquery_where_item:
356
existing_subquery_table_item . int_field_name arithmetic_operator existing_table_item . int_field_name |
357
existing_subquery_table_item . char_field_name arithmetic_operator existing_table_item . char_field_name ;
360
subquery_where_item | subquery_where_item | subquery_where_item |
361
( subquery_where_item and_or subquery_where_item ) ;
364
existing_subquery_table_item . int_field_name arithmetic_operator _digit |
365
existing_subquery_table_item . char_field_name arithmetic_operator _char |
366
existing_subquery_table_item . int_field_name arithmetic_operator existing_subquery_table_item . int_field_name |
367
existing_subquery_table_item . char_field_name arithmetic_operator existing_subquery_table_item . char_field_name |
371
subquery_new_table_item | subquery_new_table_item |
372
( subquery_new_table_item , subquery_new_table_item ) |
373
( subquery_new_table_item join_type subquery_new_table_item ON (subquery_join_condition_item ) ) |
374
( subquery_new_table_item join_type subquery_new_table_item ON (subquery_join_condition_item ) ) |
375
( subquery_new_table_item join_type ( subquery_new_table_item join_type subquery_new_table_item ON (subquery_join_condition_item ) ) ON (subquery_join_condition_item ) ) ;
377
subquery_join_condition_item:
378
subquery_current_table_item . int_field_name = subquery_previous_table_item . int_indexed subquery_on_subquery |
379
subquery_current_table_item . int_indexed = subquery_previous_table_item . int_field_name subquery_on_subquery |
380
subquery_current_table_item . `col_varchar_key` = subquery_previous_table_item . char_field_name subquery_on_subquery |
381
subquery_current_table_item . char_field_name = subquery_previous_table_item . `col_varchar_key` subquery_on_subquery ;
383
subquery_on_subquery:
384
|||||||||||||||||||| { $child_subquery_idx += 1 ; $child_subquery_tables=0 ; ""} and_or general_child_subquery ;
386
single_subquery_group_by:
387
| | | | | | | | | GROUP BY { "SQ".$subquery_idx."_field1" } ;
390
double_subquery_group_by:
391
| | | | | | | | | GROUP BY { "SQ".$subquery_idx."_field1" } , { "SQ".$subquery_idx."_field2" } ;
394
| | | | | | | | | | HAVING subquery_having_list ;
396
subquery_having_list:
397
subquery_having_item |
398
subquery_having_item |
399
(subquery_having_list and_or subquery_having_item) ;
401
subquery_having_item:
402
existing_subquery_table_item . int_field_name arithmetic_operator _digit |
403
existing_subquery_table_item . int_field_name arithmetic_operator _char ;
406
################################################################################
407
# Child subquery rules
408
################################################################################
411
{ $child_subquery_idx += 1 ; $child_subquery_tables=0 ; ""} child_subquery_type ;
414
general_child_subquery | special_child_subquery ;
416
general_child_subquery:
417
existing_subquery_table_item . int_field_name arithmetic_operator int_single_value_child_subquery |
418
existing_subquery_table_item . char_field_name arithmetic_operator char_single_value_child_subquery |
419
existing_subquery_table_item . int_field_name membership_operator int_single_member_child_subquery |
420
( existing_subquery_table_item . int_field_name , existing_subquery_table_item . int_field_name ) not IN int_double_member_child_subquery |
421
existing_subquery_table_item . char_field_name membership_operator char_single_member_child_subquery |
422
( existing_subquery_table_item . char_field_name , existing_subquery_table_item . char_field_name ) not IN char_double_member_child_subquery |
423
( _digit, _digit ) not IN int_double_member_child_subquery |
424
( _char, _char ) not IN char_double_member_child_subquery |
425
existing_subquery_table_item . int_field_name membership_operator int_single_union_child_subquery |
426
existing_subquery_table_item . char_field_name membership_operator char_single_union_child_subquery ;
428
special_child_subquery:
429
not EXISTS ( int_single_member_child_subquery ) |
430
not EXISTS ( char_single_member_child_subquery ) |
431
not EXISTS int_correlated_child_subquery |
432
not EXISTS char_correlated_child_subquery |
433
existing_subquery_table_item . int_field_name membership_operator int_correlated_child_subquery |
434
existing_subquery_table_item . char_field_name membership_operator char_correlated_child_subquery ;
437
int_single_value_child_subquery:
438
( SELECT distinct select_option aggregate child_subquery_table_one_two . int_field_name ) AS { "C_SQ".$child_subquery_idx."_field1" }
439
child_subquery_body ) ;
441
char_single_value_child_subquery:
442
( SELECT distinct select_option aggregate child_subquery_table_one_two . char_field_name ) AS { "C_SQ".$child_subquery_idx."_field1" }
443
child_subquery_body ) ;
445
int_single_member_child_subquery:
446
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "C_SQ".$child_subquery_idx."_field1" }
448
single_child_subquery_group_by
449
child_subquery_having ) ;
451
int_single_union_child_subquery:
452
( SELECT _digit UNION all_distinct SELECT _digit ) ;
454
int_double_member_child_subquery:
455
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "C_SQ".$child_subquery_idx."_field1" } ,
456
child_subquery_table_one_two . int_field_name AS { "C_SQ".$child_subquery_idx."_field2" }
458
double_child_subquery_group_by
459
child_subquery_having ) |
460
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "C_SQ".$child_subquery_idx."_field1" } ,
461
aggregate child_subquery_table_one_two . int_field_name ) AS { "C_SQ".$child_subquery_idx."_field2" }
463
single_child_subquery_group_by
464
child_subquery_having );
466
char_single_member_child_subquery:
467
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "C_SQ".$child_subquery_idx."_field1" }
469
single_child_subquery_group_by
470
child_subquery_having) ;
472
char_single_union_child_subquery:
473
( SELECT _digit UNION all_distinct SELECT _digit ) ;
475
char_double_member_child_subquery:
476
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "C_SQ".$child_subquery_idx."_field1" } ,
477
child_subquery_table_one_two . char_field_name AS { "C_SQ".$child_subquery_idx."_field2" }
479
double_child_subquery_group_by
480
child_subquery_having ) |
481
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "C_SQ".$child_subquery_idx."_field1" } ,
482
aggregate child_subquery_table_one_two . char_field_name ) AS { "C_SQ".$child_subquery_idx."_field2" }
484
single_child_subquery_group_by
485
child_subquery_having );
487
int_correlated_child_subquery:
488
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "C_SQ".$subquery_idx."_field1" }
489
FROM child_subquery_join_list
490
correlated_child_subquery_where_clause ) ;
492
char_correlated_child_subquery:
493
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "C_SQ".$subquery_idx."_field1" }
494
FROM child_subquery_join_list
495
correlated_child_subquery_where_clause ) ;
498
FROM child_subquery_join_list
499
child_subquery_where_clause ;
501
child_subquery_where_clause:
502
| WHERE child_subquery_where_list ;
504
correlated_child_subquery_where_clause:
505
WHERE correlated_child_subquery_where_list ;
507
correlated_child_subquery_where_list:
508
correlated_child_subquery_where_item | correlated_child_subquery_where_item | correlated_child_subquery_where_item |
509
correlated_child_subquery_where_item and_or correlated_child_subquery_where_item |
510
correlated_child_subquery_where_item and_or child_subquery_where_item ;
512
correlated_child_subquery_where_item:
513
existing_child_subquery_table_item . int_field_name arithmetic_operator existing_subquery_table_item . int_field_name |
514
existing_child_subquery_table_item . char_field_name arithmetic_operator existing_subquery_table_item . char_field_name ;
516
child_subquery_where_list:
517
child_subquery_where_item | child_subquery_where_item | child_subquery_where_item |
518
( child_subquery_where_item and_or child_subquery_where_item ) ;
520
child_subquery_where_item:
521
existing_child_subquery_table_item . int_field_name arithmetic_operator _digit |
522
existing_child_subquery_table_item . char_field_name arithmetic_operator _char |
523
existing_child_subquery_table_item . int_field_name arithmetic_operator existing_child_subquery_table_item . int_field_name |
524
existing_child_subquery_table_item . char_field_name arithmetic_operator existing_child_subquery_table_item . char_field_name ;
526
# child_child_subquery ;
528
child_subquery_join_list:
529
child_subquery_new_table_item | child_subquery_new_table_item |
530
( child_subquery_new_table_item join_type child_subquery_new_table_item ON (child_subquery_join_condition_item ) ) |
531
( child_subquery_new_table_item join_type child_subquery_new_table_item ON (child_subquery_join_condition_item ) ) |
532
( child_subquery_new_table_item join_type ( ( child_subquery_new_table_item join_type child_subquery_new_table_item ON (child_subquery_join_condition_item ) ) ) ON (child_subquery_join_condition_item ) ) ;
534
child_subquery_join_condition_item:
535
child_subquery_current_table_item . int_field_name = child_subquery_previous_table_item . int_indexed |
536
child_subquery_current_table_item . int_indexed = child_subquery_previous_table_item . int_field_name |
537
child_subquery_current_table_item . `col_varchar_key` = child_subquery_previous_table_item . char_field_name |
538
child_subquery_current_table_item . char_field_name = child_subquery_previous_table_item . `col_varchar_key` ;
540
single_child_subquery_group_by:
541
| | | | | | | | | GROUP BY { "C_SQ".$child_subquery_idx."_field1" } ;
544
double_child_subquery_group_by:
545
| | | | | | | | | GROUP BY { "C_SQ".$child_subquery_idx."_field1" } , { "C_SQ".$child_subquery_idx."_field2" } ;
547
child_subquery_having:
548
| | | | | | | | | | HAVING child_subquery_having_list ;
550
child_subquery_having_list:
551
child_subquery_having_item |
552
child_subquery_having_item |
553
(child_subquery_having_list and_or child_subquery_having_item) ;
555
child_subquery_having_item:
556
existing_child_subquery_table_item . int_field_name arithmetic_operator _digit |
557
existing_child_subquery_table_item . int_field_name arithmetic_operator _char ;
560
################################################################################
561
# The range_predicate_1* rules below are in place to ensure we hit the #
562
# index_merge/sort_union optimization. #
563
# NOTE: combinations of the predicate_1 and predicate_2 rules tend to hit the #
564
# index_merge/intersect optimization #
565
################################################################################
567
range_predicate1_list:
568
range_predicate1_item |
569
( range_predicate1_item OR range_predicate1_item ) ;
571
range_predicate1_item:
572
alias1 . int_indexed not BETWEEN _tinyint_unsigned[invariant] AND ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) |
573
alias1 . `col_varchar_key` arithmetic_operator _char[invariant] |
574
alias1 . int_indexed not IN (number_list) |
575
alias1 . `col_varchar_key` not IN (char_list) |
576
alias1 . `pk` > _tinyint_unsigned[invariant] AND alias1 . `pk` < ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) |
577
alias1 . `col_int_key` > _tinyint_unsigned[invariant] AND alias1 . `col_int_key` < ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) ;
579
################################################################################
580
# The range_predicate_2* rules below are in place to ensure we hit the #
581
# index_merge/union optimization. #
582
# NOTE: combinations of the predicate_1 and predicate_2 rules tend to hit the #
583
# index_merge/intersect optimization #
584
################################################################################
586
range_predicate2_list:
587
range_predicate2_item |
588
( range_predicate2_item and_or range_predicate2_item ) ;
590
range_predicate2_item:
591
alias1 . `pk` = _tinyint_unsigned |
592
alias1 . `col_int_key` = _tinyint_unsigned |
593
alias1 . `col_varchar_key` = _char |
594
alias1 . int_indexed = _tinyint_unsigned |
595
alias1 . `col_varchar_key` LIKE CONCAT( _char , '%') |
596
alias1 . int_indexed = existing_table_item . int_indexed |
597
alias1 . `col_varchar_key` = existing_table_item . `col_varchar_key` ;
599
################################################################################
600
# The number and char_list rules are for creating WHERE conditions that test #
601
# 'field' IN (list_of_items) #
602
################################################################################
604
_tinyint_unsigned | number_list, _tinyint_unsigned ;
607
_char | 'USA' | char_list , _char | char_list , 'USA' ;
609
################################################################################
610
# We ensure that a GROUP BY statement includes all nonaggregates. #
611
# This helps to ensure the query is more useful in detecting real errors / #
612
# that the query doesn't lend itself to variable result sets #
613
################################################################################
615
{ scalar(@nonaggregates) > 0 ? " GROUP BY ".join (', ' , @nonaggregates ) : "" } ;
618
| | group_by_clause ;
621
| HAVING having_list;
626
(having_list and_or having_item) ;
629
existing_select_item arithmetic_operator value |
630
existing_select_item arithmetic_operator value |
631
existing_select_item arithmetic_operator value |
632
existing_select_item arithmetic_operator value |
633
existing_select_item arithmetic_operator value |
634
existing_select_item arithmetic_operator value |
635
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} general_subquery;
637
################################################################################
638
# We use the total_order_by rule when using the LIMIT operator to ensure that #
639
# we have a consistent result set - server1 and server2 should not differ #
640
################################################################################
644
ORDER BY alias1 . _field_indexed desc , total_order_by limit |
645
ORDER BY order_by_list |
646
ORDER BY order_by_list, total_order_by limit ;
649
{ join(', ', map { "field".$_ } (1..$fields) ) };
653
order_by_item , order_by_list ;
656
alias1 . _field_indexed , existing_table_item .`pk` desc |
657
alias1 . _field_indexed desc |
658
existing_select_item desc |
659
CONCAT ( existing_table_item . char_field_name, existing_table_item . char_field_name );
665
| | LIMIT limit_size | LIMIT limit_size OFFSET _digit;
668
nonaggregate_select_item |
669
nonaggregate_select_item |
670
aggregate_select_item |
672
nonaggregate_select_item |
673
nonaggregate_select_item |
674
aggregate_select_item |
677
################################################################################
678
# We have the perl code here to help us write more sensible queries #
679
# It allows us to use field1...fieldn in the WHERE, ORDER BY, and GROUP BY #
680
# clauses so that the queries will produce more stable and interesting results #
681
################################################################################
683
nonaggregate_select_item:
684
table_one_two . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
685
table_one_two . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
686
table_one_two . _field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
688
aggregate_select_item:
689
aggregate table_one_two . _field ) AS { "field".++$fields };
692
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} select_subquery_body;
694
select_subquery_body:
695
int_single_value_subquery AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
696
char_single_value_subquery AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
697
int_scalar_correlated_subquery AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
699
select_subquery_body_disabled:
700
( SELECT _digit UNION all_distinct ( SELECT _digit ) LIMIT 1 ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
701
( SELECT _char UNION all_distinct ( SELECT _char ) LIMIT 1 ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
703
################################################################################
704
# The combo_select_items are for 'spice'
705
################################################################################
708
( ( table_one_two . int_field_name ) math_operator ( table_one_two . int_field_name ) ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
709
CONCAT ( table_one_two . char_field_name , table_one_two . char_field_name ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
712
alias1 | alias1 | alias2 ;
714
subquery_table_one_two:
715
{ "SQ".$subquery_idx."_alias1" ; } | { "SQ".$subquery_idx."_alias1" ; } |
716
{ "SQ".$subquery_idx."_alias1" ; } | { "SQ".$subquery_idx."_alias2" ; } ;
718
child_subquery_table_one_two:
719
{ "C_SQ".$child_subquery_idx."_alias1" ; } | { "C_SQ".$child_subquery_idx."_alias1" ; } |
720
{ "C_SQ".$child_subquery_idx."_alias1" ; } | { "C_SQ".$child_subquery_idx."_alias2" ; } ;
723
COUNT( distinct | SUM( distinct | MIN( distinct | MAX( distinct ;
725
################################################################################
726
# The following rules are for writing more sensible queries - that we don't #
727
# reference tables / fields that aren't present in the query and that we keep #
728
# track of what we have added. You shouldn't need to touch these ever #
729
################################################################################
731
_table AS { "alias".++$tables } | _table AS { "alias".++$tables } | _table AS { "alias".++$tables } ;
733
# ( from_subquery ) AS { "alias".++$tables } ;
736
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} SELECT distinct select_option subquery_table_one_two . * subquery_body ;
738
subquery_new_table_item:
739
_table AS { "SQ".$subquery_idx."_alias".++$subquery_tables } ;
741
child_subquery_new_table_item:
742
_table AS { "C_SQ".$child_subquery_idx."_alias".++$child_subquery_tables } ;
747
subquery_current_table_item:
748
{ "SQ".$subquery_idx."_alias".$subquery_tables } ;
750
child_subquery_current_table_item:
751
{ "C_SQ".$child_subquery_idx."_alias".$child_subquery_tables } ;
754
{ "alias".($tables - 1) };
756
subquery_previous_table_item:
757
{ "SQ".$subquery_idx."_alias".($subquery_tables-1) } ;
759
child_subquery_previous_table_item:
760
{ "C_SQ".$child_subquery_idx."_alias".($child_subquery_tables-1) } ;
763
{ "alias".$prng->int(1,$tables) };
765
existing_subquery_table_item:
766
{ "SQ".$subquery_idx."_alias".$prng->int(1,$subquery_tables) } ;
768
existing_child_subquery_table_item:
769
{ "C_SQ".$child_subquery_idx."_alias".$prng->int(1,$child_subquery_tables) } ;
771
existing_select_item:
772
{ "field".$prng->int(1,$fields) };
774
################################################################################
775
# end of utility rules #
776
################################################################################
779
= | > | < | != | <> | <= | >= ;
783
arithmetic_operator all_any |
792
################################################################################
793
# Used for creating combo_items - ie (field1 + field2) AS fieldX #
794
# We ignore division to prevent division by zero errors #
795
################################################################################
799
################################################################################
800
# We stack AND to provide more interesting options for the optimizer #
801
# Alter these percentages at your own risk / look for coverage regressions #
802
# with --debug if you play with these. Those optimizations that require an #
803
# OR-only list in the WHERE clause are specifically stacked in another rule #
804
################################################################################
806
AND | AND | AND | AND | AND | OR ;
810
| | | ALL | DISTINCT ;
814
_digit | _digit | _digit | _digit | _tinyint_unsigned|
815
_char(1) | _char(1) | _char(1) | _char(2) | _char(2) | 'USA' ;
818
A | B | C | BB | CC | B | C | BB | CC |
819
CC | CC | CC | CC | CC |
820
C | C | C | C | C | D | view ;
822
################################################################################
823
# Add a possibility for 'view' to occur at the end of the previous '_table' rule
824
# to allow a chance to use views (when running the RQG with --views)
825
################################################################################
828
view_A | view_AA | view_B | view_BB | view_C | view_CC | view_C | view_CC | view_D ;
831
int_field_name | char_field_name ;
834
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | _tinyint_unsigned ;
837
`pk` | `col_int_key` | `col_int_nokey` ;
840
`pk` | `col_int_key` ;
844
`col_varchar_key` | `col_varchar_nokey` ;
846
################################################################################
847
# We define LIMIT_rows in this fashion as LIMIT values can differ depending on #
848
# how large the LIMIT is - LIMIT 2 = LIMIT 9 != LIMIT 19 #
849
################################################################################
852
1 | 2 | 10 | 100 | 1000;