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 ;
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 ) ) ;
120
################################################################################
121
# preventing deep join nesting for run time / table access methods are more #
122
# important here - join.yy can provide deeper join coverage #
123
# Enabling this / swapping out with join_list above can produce some #
124
# time-consuming queries. #
125
################################################################################
128
( new_table_item join_type join_list ON (join_condition_item ) ) ;
131
INNER JOIN | left_right outer JOIN | STRAIGHT_JOIN ;
134
current_table_item . int_indexed = previous_table_item . int_field_name on_subquery |
135
current_table_item . int_field_name = previous_table_item . int_indexed on_subquery |
136
current_table_item . char_indexed = previous_table_item . char_field_name on_subquery |
137
current_table_item . char_field_name = previous_table_item . char_indexed on_subquery ;
140
|||||||||||||||||||| { $subquery_idx += 1 ; $subquery_tables=0 ; ""} and_or general_subquery ;
150
WHERE ( where_subquery ) and_or where_list ;
155
range_predicate1_list | range_predicate2_list |
156
range_predicate1_list and_or generic_where_list |
157
range_predicate2_list and_or generic_where_list ;
162
( where_item and_or where_item ) ;
169
table1 . int_field_name arithmetic_operator existing_table_item . int_field_name |
170
existing_table_item . char_field_name arithmetic_operator _char |
171
existing_table_item . char_field_name arithmetic_operator existing_table_item . char_field_name |
172
table1 . _field IS not NULL |
173
table1 . int_field_name arithmetic_operator existing_table_item . int_field_name |
174
existing_table_item . char_field_name arithmetic_operator _char |
175
existing_table_item . char_field_name arithmetic_operator existing_table_item . char_field_name |
176
table1 . _field IS not NULL ;
178
################################################################################
180
################################################################################
183
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} subquery_type ;
186
general_subquery | special_subquery ;
189
existing_table_item . int_field_name arithmetic_operator int_single_value_subquery |
190
existing_table_item . char_field_name arithmetic_operator char_single_value_subquery |
191
existing_table_item . int_field_name membership_operator int_single_member_subquery |
192
( existing_table_item . int_field_name , existing_table_item . int_field_name ) not IN int_double_member_subquery |
193
existing_table_item . char_field_name membership_operator char_single_member_subquery |
194
( existing_table_item . char_field_name , existing_table_item . char_field_name ) not IN char_double_member_subquery |
195
( _digit, _digit ) not IN int_double_member_subquery |
196
( _char, _char ) not IN char_double_member_subquery |
197
existing_table_item . int_field_name membership_operator int_single_union_subquery |
198
existing_table_item . char_field_name membership_operator char_single_union_subquery ;
200
general_subquery_union_test_disabled:
201
existing_table_item . char_field_name arithmetic_operator all_any char_single_union_subquery_disabled |
202
existing_table_item . int_field_name arithmetic_operator all_any int_single_union_subquery_disabled ;
205
not EXISTS ( int_single_member_subquery ) |
206
not EXISTS ( char_single_member_subquery ) |
207
not EXISTS int_correlated_subquery |
208
not EXISTS char_correlated_subquery |
209
existing_table_item . int_field_name membership_operator int_correlated_subquery |
210
existing_table_item . char_field_name membership_operator char_correlated_subquery |
211
int_single_value_subquery IS not NULL |
212
char_single_value_subquery IS not NULL ;
214
int_single_value_subquery:
215
( SELECT distinct select_option aggregate subquery_table_one_two . int_field_name ) AS { "SUBQUERY".$subquery_idx."_field1" }
217
( SELECT distinct select_option aggregate subquery_table_one_two . int_field_name ) AS { "SUBQUERY".$subquery_idx."_field1" }
220
char_single_value_subquery:
221
( SELECT distinct select_option aggregate subquery_table_one_two . char_field_name ) AS { "SUBQUERY".$subquery_idx."_field1" }
223
( SELECT distinct select_option aggregate subquery_table_one_two . char_field_name ) AS { "SUBQUERY".$subquery_idx."_field1" }
226
int_single_member_subquery:
227
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SUBQUERY".$subquery_idx."_field1" }
229
single_subquery_group_by
232
int_single_union_subquery:
233
( SELECT _digit UNION all_distinct SELECT _digit ) ;
235
int_single_union_subquery_disabled:
236
int_single_member_subquery UNION all_distinct int_single_member_subquery ;
238
int_double_member_subquery:
239
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SUBQUERY".$subquery_idx."_field1" } ,
240
subquery_table_one_two . int_field_name AS { SUBQUERY.$subquery_idx."_field2" }
242
double_subquery_group_by
244
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SUBQUERY".$subquery_idx."_field1" } ,
245
aggregate subquery_table_one_two . int_field_name ) AS { SUBQUERY.$subquery_idx."_field2" }
247
single_subquery_group_by
249
( SELECT _digit , _digit UNION all_distinct SELECT _digit, _digit ) ;
251
char_single_member_subquery:
252
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SUBQUERY".$subquery_idx."_field1" }
254
single_subquery_group_by
257
char_single_union_subquery:
258
( SELECT _char UNION all_distinct SELECT _char ) ;
260
char_single_union_subquery_disabled:
261
char_single_member_subquery UNION all_distinct char_single_member_subquery ;
263
char_double_member_subquery:
264
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SUBQUERY".$subquery_idx."_field1" } ,
265
subquery_table_one_two . char_field_name AS { SUBQUERY.$subquery_idx."_field2" }
267
double_subquery_group_by
269
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SUBQUERY".$subquery_idx."_field1" } ,
270
aggregate subquery_table_one_two . char_field_name ) AS { SUBQUERY.$subquery_idx."_field2" }
272
single_subquery_group_by
274
( SELECT _char , _char UNION all_distinct SELECT _char , _char ) ;
276
int_correlated_subquery:
277
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SUBQUERY".$subquery_idx."_field1" }
278
FROM subquery_join_list
279
correlated_subquery_where_clause ) ;
281
char_correlated_subquery:
282
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SUBQUERY".$subquery_idx."_field1" }
283
FROM subquery_join_list
284
correlated_subquery_where_clause ) ;
286
int_scalar_correlated_subquery:
287
( SELECT distinct select_option aggregate subquery_table_one_two . int_field_name ) AS { "SUBQUERY".$subquery_idx."_field1" }
288
FROM subquery_join_list
289
correlated_subquery_where_clause ) ;
292
FROM subquery_join_list
293
subquery_where_clause ;
295
subquery_where_clause:
296
| | WHERE subquery_where_list ;
298
correlated_subquery_where_clause:
299
WHERE correlated_subquery_where_list ;
301
correlated_subquery_where_list:
302
correlated_subquery_where_item |
303
correlated_subquery_where_item and_or correlated_subquery_where_item |
304
correlated_subquery_where_item and_or subquery_where_item ;
306
correlated_subquery_where_item:
307
existing_subquery_table_item . int_field_name arithmetic_operator existing_table_item . int_field_name |
308
existing_subquery_table_item . char_field_name arithmetic_operator existing_table_item . char_field_name ;
311
subquery_where_item | subquery_where_item | subquery_where_item |
312
( subquery_where_item and_or subquery_where_item ) ;
315
existing_subquery_table_item . int_field_name arithmetic_operator _digit |
316
existing_subquery_table_item . char_field_name arithmetic_operator _char |
317
existing_subquery_table_item . int_field_name arithmetic_operator existing_subquery_table_item . int_field_name |
318
existing_subquery_table_item . char_field_name arithmetic_operator existing_subquery_table_item . char_field_name |
322
subquery_new_table_item | subquery_new_table_item |
323
( subquery_new_table_item join_type subquery_new_table_item ON (subquery_join_condition_item ) ) |
324
( subquery_new_table_item join_type subquery_new_table_item ON (subquery_join_condition_item ) ) |
325
( 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 ) ) ;
327
subquery_join_condition_item:
328
subquery_current_table_item . int_field_name = subquery_previous_table_item . int_indexed subquery_on_subquery |
329
subquery_current_table_item . int_indexed = subquery_previous_table_item . int_field_name subquery_on_subquery |
330
subquery_current_table_item . char_indexed = subquery_previous_table_item . char_field_name subquery_on_subquery |
331
subquery_current_table_item . char_field_name = subquery_previous_table_item . char_indexed subquery_on_subquery ;
333
subquery_on_subquery:
334
|||||||||||||||||||| { $child_subquery_idx += 1 ; $child_subquery_tables=0 ; ""} and_or general_child_subquery ;
336
single_subquery_group_by:
337
| | | | | | | | | GROUP BY { SUBQUERY.$subquery_idx."_field1" } ;
340
double_subquery_group_by:
341
| | | | | | | | | GROUP BY { SUBQUERY.$subquery_idx."_field1" } , { SUBQUERY.$subquery_idx."_field2" } ;
344
| | | | | | | | | | HAVING subquery_having_list ;
346
subquery_having_list:
347
subquery_having_item |
348
subquery_having_item |
349
(subquery_having_list and_or subquery_having_item) ;
351
subquery_having_item:
352
existing_subquery_table_item . int_field_name arithmetic_operator _digit |
353
existing_subquery_table_item . int_field_name arithmetic_operator _char ;
356
################################################################################
357
# Child subquery rules
358
################################################################################
361
{ $child_subquery_idx += 1 ; $child_subquery_tables=0 ; ""} child_subquery_type ;
364
general_child_subquery | special_child_subquery ;
366
general_child_subquery:
367
existing_subquery_table_item . int_field_name arithmetic_operator int_single_value_child_subquery |
368
existing_subquery_table_item . char_field_name arithmetic_operator char_single_value_child_subquery |
369
existing_subquery_table_item . int_field_name membership_operator int_single_member_child_subquery |
370
( existing_subquery_table_item . int_field_name , existing_subquery_table_item . int_field_name ) not IN int_double_member_child_subquery |
371
existing_subquery_table_item . char_field_name membership_operator char_single_member_child_subquery |
372
( existing_subquery_table_item . char_field_name , existing_subquery_table_item . char_field_name ) not IN char_double_member_child_subquery |
373
( _digit, _digit ) not IN int_double_member_child_subquery |
374
( _char, _char ) not IN char_double_member_child_subquery |
375
existing_subquery_table_item . int_field_name membership_operator int_single_union_child_subquery |
376
existing_subquery_table_item . char_field_name membership_operator char_single_union_child_subquery ;
378
special_child_subquery:
379
not EXISTS ( int_single_member_child_subquery ) |
380
not EXISTS ( char_single_member_child_subquery ) |
381
not EXISTS int_correlated_child_subquery |
382
not EXISTS char_correlated_child_subquery |
383
existing_subquery_table_item . int_field_name membership_operator int_correlated_child_subquery |
384
existing_subquery_table_item . char_field_name membership_operator char_correlated_child_subquery ;
387
int_single_value_child_subquery:
388
( SELECT distinct select_option aggregate child_subquery_table_one_two . int_field_name ) AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" }
389
child_subquery_body ) ;
391
char_single_value_child_subquery:
392
( SELECT distinct select_option aggregate child_subquery_table_one_two . char_field_name ) AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" }
393
child_subquery_body ) ;
395
int_single_member_child_subquery:
396
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" }
398
single_child_subquery_group_by
399
child_subquery_having ) ;
401
int_single_union_child_subquery:
402
( SELECT _digit UNION all_distinct SELECT _digit ) ;
404
int_double_member_child_subquery:
405
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" } ,
406
child_subquery_table_one_two . int_field_name AS { child_subquery.$child_subquery_idx."_field2" }
408
double_child_subquery_group_by
409
child_subquery_having ) |
410
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" } ,
411
aggregate child_subquery_table_one_two . int_field_name ) AS { child_subquery.$child_subquery_idx."_field2" }
413
single_child_subquery_group_by
414
child_subquery_having );
416
char_single_member_child_subquery:
417
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" }
419
single_child_subquery_group_by
420
child_subquery_having) ;
422
char_single_union_child_subquery:
423
( SELECT _digit UNION all_distinct SELECT _digit ) ;
425
char_double_member_child_subquery:
426
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" } ,
427
child_subquery_table_one_two . char_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field2" }
429
double_child_subquery_group_by
430
child_subquery_having ) |
431
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" } ,
432
aggregate child_subquery_table_one_two . char_field_name ) AS { "CHILD_SUBQUERY".$child_subquery_idx."_field2" }
434
single_child_subquery_group_by
435
child_subquery_having );
437
int_correlated_child_subquery:
438
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "CHILD_SUBQUERY".$subquery_idx."_field1" }
439
FROM child_subquery_join_list
440
correlated_child_subquery_where_clause ) ;
442
char_correlated_child_subquery:
443
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "CHILD_SUBQUERY".$subquery_idx."_field1" }
444
FROM child_subquery_join_list
445
correlated_child_subquery_where_clause ) ;
448
FROM child_subquery_join_list
449
child_subquery_where_clause ;
451
child_subquery_where_clause:
452
| WHERE child_subquery_where_list ;
454
correlated_child_subquery_where_clause:
455
WHERE correlated_child_subquery_where_list ;
457
correlated_child_subquery_where_list:
458
correlated_child_subquery_where_item | correlated_child_subquery_where_item | correlated_child_subquery_where_item |
459
correlated_child_subquery_where_item and_or correlated_child_subquery_where_item |
460
correlated_child_subquery_where_item and_or child_subquery_where_item ;
462
correlated_child_subquery_where_item:
463
existing_child_subquery_table_item . int_field_name arithmetic_operator existing_subquery_table_item . int_field_name |
464
existing_child_subquery_table_item . char_field_name arithmetic_operator existing_subquery_table_item . char_field_name ;
466
child_subquery_where_list:
467
child_subquery_where_item | child_subquery_where_item | child_subquery_where_item |
468
( child_subquery_where_item and_or child_subquery_where_item ) ;
470
child_subquery_where_item:
471
existing_child_subquery_table_item . int_field_name arithmetic_operator _digit |
472
existing_child_subquery_table_item . char_field_name arithmetic_operator _char |
473
existing_child_subquery_table_item . int_field_name arithmetic_operator existing_child_subquery_table_item . int_field_name |
474
existing_child_subquery_table_item . char_field_name arithmetic_operator existing_child_subquery_table_item . char_field_name |
475
child_child_subquery ;
477
child_subquery_join_list:
478
child_subquery_new_table_item | child_subquery_new_table_item |
479
( child_subquery_new_table_item join_type child_subquery_new_table_item ON (child_subquery_join_condition_item ) ) |
480
( child_subquery_new_table_item join_type child_subquery_new_table_item ON (child_subquery_join_condition_item ) ) |
481
( 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 ) ) ;
483
child_subquery_join_condition_item:
484
child_subquery_current_table_item . int_field_name = child_subquery_previous_table_item . int_indexed |
485
child_subquery_current_table_item . int_indexed = child_subquery_previous_table_item . int_field_name |
486
child_subquery_current_table_item . char_indexed = child_subquery_previous_table_item . char_field_name |
487
child_subquery_current_table_item . char_field_name = child_subquery_previous_table_item . char_indexed ;
489
single_child_subquery_group_by:
490
| | | | | | | | | GROUP BY { child_subquery.$child_subquery_idx."_field1" } ;
493
double_child_subquery_group_by:
494
| | | | | | | | | GROUP BY { child_subquery.$child_subquery_idx."_field1" } , { child_subquery.$child_subquery_idx."_field2" } ;
496
child_subquery_having:
497
| | | | | | | | | | HAVING child_subquery_having_list ;
499
child_subquery_having_list:
500
child_subquery_having_item |
501
child_subquery_having_item |
502
(child_subquery_having_list and_or child_subquery_having_item) ;
504
child_subquery_having_item:
505
existing_child_subquery_table_item . int_field_name arithmetic_operator _digit |
506
existing_child_subquery_table_item . int_field_name arithmetic_operator _char ;
509
################################################################################
510
# The range_predicate_1* rules below are in place to ensure we hit the #
511
# index_merge/sort_union optimization. #
512
# NOTE: combinations of the predicate_1 and predicate_2 rules tend to hit the #
513
# index_merge/intersect optimization #
514
################################################################################
516
range_predicate1_list:
517
range_predicate1_item |
518
( range_predicate1_item OR range_predicate1_item ) ;
520
range_predicate1_item:
521
table1 . int_indexed not BETWEEN _tinyint_unsigned[invariant] AND ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) |
522
table1 . char_indexed arithmetic_operator _char[invariant] |
523
table1 . int_indexed not IN (number_list) |
524
table1 . char_indexed not IN (char_list) |
525
table1 . `pk` > _tinyint_unsigned[invariant] AND table1 . `pk` < ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) |
526
table1 . `col_int_key` > _tinyint_unsigned[invariant] AND table1 . `col_int_key` < ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) ;
528
################################################################################
529
# The range_predicate_2* rules below are in place to ensure we hit the #
530
# index_merge/union optimization. #
531
# NOTE: combinations of the predicate_1 and predicate_2 rules tend to hit the #
532
# index_merge/intersect optimization #
533
################################################################################
535
range_predicate2_list:
536
range_predicate2_item |
537
( range_predicate2_item and_or range_predicate2_item ) ;
539
range_predicate2_item:
540
table1 . `pk` = _tinyint_unsigned |
541
table1 . `col_int_key` = _tinyint_unsigned |
542
table1 . char_indexed = _char |
543
table1 . int_indexed = _tinyint_unsigned |
544
table1 . char_indexed = _char |
545
table1 . int_indexed = existing_table_item . int_indexed |
546
table1 . char_indexed = existing_table_item . char_indexed ;
548
################################################################################
549
# The number and char_list rules are for creating WHERE conditions that test #
550
# 'field' IN (list_of_items) #
551
################################################################################
553
_tinyint_unsigned | number_list, _tinyint_unsigned ;
556
_char | char_list, _char ;
558
################################################################################
559
# We ensure that a GROUP BY statement includes all nonaggregates. #
560
# This helps to ensure the query is more useful in detecting real errors / #
561
# that the query doesn't lend itself to variable result sets #
562
################################################################################
564
{ scalar(@nonaggregates) > 0 ? " GROUP BY ".join (', ' , @nonaggregates ) : "" } ;
567
| | group_by_clause ;
570
| HAVING having_list;
575
(having_list and_or having_item) ;
578
existing_select_item arithmetic_operator value |
579
existing_select_item arithmetic_operator value |
580
existing_select_item arithmetic_operator value |
581
existing_select_item arithmetic_operator value |
582
existing_select_item arithmetic_operator value |
583
existing_select_item arithmetic_operator value |
584
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} general_subquery;
586
################################################################################
587
# We use the total_order_by rule when using the LIMIT operator to ensure that #
588
# we have a consistent result set - server1 and server2 should not differ #
589
################################################################################
593
ORDER BY table1 . _field_indexed desc , total_order_by limit |
594
ORDER BY order_by_list |
595
ORDER BY order_by_list, total_order_by limit ;
598
{ join(', ', map { "field".$_ } (1..$fields) ) };
602
order_by_item , order_by_list ;
605
table1 . _field_indexed , existing_table_item .`pk` desc |
606
table1 . _field_indexed desc |
607
existing_select_item desc |
608
CONCAT ( existing_table_item . char_field_name, existing_table_item . char_field_name );
614
| | LIMIT limit_size | LIMIT limit_size OFFSET _digit;
617
nonaggregate_select_item |
618
nonaggregate_select_item |
619
aggregate_select_item |
621
nonaggregate_select_item |
622
nonaggregate_select_item |
623
aggregate_select_item |
626
################################################################################
627
# We have the perl code here to help us write more sensible queries #
628
# It allows us to use field1...fieldn in the WHERE, ORDER BY, and GROUP BY #
629
# clauses so that the queries will produce more stable and interesting results #
630
################################################################################
632
nonaggregate_select_item:
633
table_one_two . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
634
table_one_two . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
635
table_one_two . _field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
637
aggregate_select_item:
638
aggregate table_one_two . _field ) AS { "field".++$fields };
641
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} select_subquery_body;
643
select_subquery_body:
644
int_single_value_subquery AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
645
char_single_value_subquery AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
646
int_scalar_correlated_subquery AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
648
select_subquery_body_disabled:
649
( SELECT _digit UNION all_distinct ( SELECT _digit ) LIMIT 1 ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
650
( SELECT _char UNION all_distinct ( SELECT _char ) LIMIT 1 ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
652
################################################################################
653
# The combo_select_items are for 'spice'
654
################################################################################
657
( ( table_one_two . int_field_name ) math_operator ( table_one_two . int_field_name ) ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
658
CONCAT ( table_one_two . char_field_name , table_one_two . char_field_name ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
661
table1 | table1 | table2 ;
663
subquery_table_one_two:
664
{ "SUBQUERY".$subquery_idx."_t1" ; } | { "SUBQUERY".$subquery_idx."_t1" ; } |
665
{ "SUBQUERY".$subquery_idx."_t1" ; } | { "SUBQUERY".$subquery_idx."_t2" ; } ;
667
child_subquery_table_one_two:
668
{ "CHILD_SUBQUERY".$child_subquery_idx."_t1" ; } | { "CHILD_SUBQUERY".$child_subquery_idx."_t1" ; } |
669
{ "CHILD_SUBQUERY".$child_subquery_idx."_t1" ; } | { "CHILD_SUBQUERY".$child_subquery_idx."_t2" ; } ;
672
COUNT( distinct | SUM( distinct | MIN( distinct | MAX( distinct ;
674
################################################################################
675
# The following rules are for writing more sensible queries - that we don't #
676
# reference tables / fields that aren't present in the query and that we keep #
677
# track of what we have added. You shouldn't need to touch these ever #
678
################################################################################
680
_table AS { "table".++$tables } | _table AS { "table".++$tables } | _table AS { "table".++$tables } |
681
( from_subquery ) AS { "table".++$tables } ;
684
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} SELECT distinct select_option subquery_table_one_two . * subquery_body ;
686
subquery_new_table_item:
687
_table AS { "SUBQUERY".$subquery_idx."_t".++$subquery_tables } ;
689
child_subquery_new_table_item:
690
_table AS { "CHILD_SUBQUERY".$child_subquery_idx."_t".++$child_subquery_tables } ;
695
subquery_current_table_item:
696
{ "SUBQUERY".$subquery_idx."_t".$subquery_tables } ;
698
child_subquery_current_table_item:
699
{ "CHILD_SUBQUERY".$child_subquery_idx."_t".$child_subquery_tables } ;
702
{ "table".($tables - 1) };
704
subquery_previous_table_item:
705
{ "SUBQUERY".$subquery_idx."_t".($subquery_tables-1) } ;
707
child_subquery_previous_table_item:
708
{ "CHILD_SUBQUERY".$child_subquery_idx."_t".($child_subquery_tables-1) } ;
711
{ "table".$prng->int(1,$tables) };
713
existing_subquery_table_item:
714
{ "SUBQUERY".$subquery_idx."_t".$prng->int(1,$subquery_tables) } ;
716
existing_child_subquery_table_item:
717
{ "CHILD_SUBQUERY".$child_subquery_idx."_t".$prng->int(1,$child_subquery_tables) } ;
719
existing_select_item:
720
{ "field".$prng->int(1,$fields) };
722
################################################################################
723
# end of utility rules #
724
################################################################################
727
= | > | < | != | <> | <= | >= ;
731
arithmetic_operator all_any |
737
################################################################################
738
# Used for creating combo_items - ie (field1 + field2) AS fieldX #
739
# We ignore division to prevent division by zero errors #
740
################################################################################
744
################################################################################
745
# We stack AND to provide more interesting options for the optimizer #
746
# Alter these percentages at your own risk / look for coverage regressions #
747
# with --debug if you play with these. Those optimizations that require an #
748
# OR-only list in the WHERE clause are specifically stacked in another rule #
749
################################################################################
755
| | | ALL | DISTINCT ;
759
_digit | _digit | _digit | _digit | _tinyint_unsigned|
760
_char(2) | _char(2) | _char(2) | _char(2) | _char(2) ;
763
AA | AA | AA | BB | BB | BB |
764
CC | CC | DD | small_table ;
767
A | B | C | C | C | D | D | D ;
770
int_field_name | char_field_name ;
773
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | _tinyint_unsigned ;
776
`pk` | `col_int_key` | `col_int` |
777
`col_bigint` | `col_bigint_key` |
778
`col_int_not_null` | `col_int_not_null_key` ;
781
`col_char_10` | `col_char_10_key` | `col_text_not_null` | `col_text_not_null_key` |
782
`col_text_key` | `col_text` | `col_char_10_not_null_key` | `col_char_10_not_null` |
783
`col_char_1024` | `col_char_1024_key` | `col_char_1024_not_null` | `col_char_1024_not_null_key` ;
785
char_field_name_disabled:
786
# need to explore enum more before enabling this
787
`col_enum` | `col_enum_key` | `col_enum_not_null` | `col_enum_not_null_key` ;
790
`pk` | `col_int_key` | `col_bigint_key` | `col_int_not_null_key` ;
793
`col_char_1024_key` | `col_char_1024_not_null_key` |
794
`col_char_10_key` | `col_char_10_not_null_key` ;
796
################################################################################
797
# We define LIMIT_rows in this fashion as LIMIT values can differ depending on #
798
# how large the LIMIT is - LIMIT 2 = LIMIT 9 != LIMIT 19 #
799
################################################################################
802
1 | 2 | 10 | 100 | 1000;
added
removed
tests/randgen/conf/drizzle/optimizer_subquery_drizzle.yy
