1
# Copyright (c) 2008, 2011 Oracle and/or its affiliates. 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 ;
60
SELECT distinct straight_join select_option select_list
68
| | | | | | | | | explain_extended2 ;
70
explain_extended2: | | | | EXPLAIN | EXPLAIN EXTENDED ;
72
distinct: DISTINCT | | | | | | | | | ;
74
select_option: | | | | | | | | | | | SQL_SMALL_RESULT ;
76
straight_join: | | | | | | | | | | | STRAIGHT_JOIN ;
80
new_select_item , select_list |
81
new_select_item , select_list ;
84
################################################################################
85
# this limits us to 2 and 3 table joins / can use it if we hit #
86
# too many mega-join conditions which take too long to run #
87
################################################################################
88
( new_table_item join_type new_table_item ON (join_condition_item ) ) |
89
( new_table_item join_type ( ( new_table_item join_type new_table_item ON (join_condition_item ) ) ) ON (join_condition_item ) ) ;
92
################################################################################
93
# preventing deep join nesting for run time / table access methods are more #
94
# important here - join.yy can provide deeper join coverage #
95
# Enabling this / swapping out with join_list above can produce some #
96
# time-consuming queries. #
97
################################################################################
100
( new_table_item join_type join_list ON (join_condition_item ) ) ;
103
INNER JOIN | left_right outer JOIN | STRAIGHT_JOIN ;
106
current_table_item . int_indexed = previous_table_item . int_field_name on_subquery |
107
current_table_item . int_field_name = previous_table_item . int_indexed on_subquery |
108
current_table_item . `col_varchar_key` = previous_table_item . char_field_name on_subquery |
109
current_table_item . char_field_name = previous_table_item . `col_varchar_key` on_subquery ;
112
|||||||||||||||||||| { $subquery_idx += 1 ; $subquery_tables=0 ; ""} and_or general_subquery ;
122
WHERE ( where_subquery ) and_or where_list ;
127
range_predicate1_list | range_predicate2_list |
128
range_predicate1_list and_or generic_where_list |
129
range_predicate2_list and_or generic_where_list ;
134
( where_item and_or where_item ) ;
141
table1 . int_field_name arithmetic_operator existing_table_item . int_field_name |
142
existing_table_item . char_field_name arithmetic_operator _char |
143
existing_table_item . char_field_name arithmetic_operator existing_table_item . char_field_name |
144
table1 . _field IS not NULL |
145
table1 . int_field_name arithmetic_operator existing_table_item . int_field_name |
146
existing_table_item . char_field_name arithmetic_operator _char |
147
existing_table_item . char_field_name arithmetic_operator existing_table_item . char_field_name |
148
table1 . _field IS not NULL ;
150
################################################################################
152
################################################################################
155
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} subquery_type ;
158
general_subquery | special_subquery ;
161
existing_table_item . int_field_name arithmetic_operator int_single_value_subquery |
162
existing_table_item . char_field_name arithmetic_operator char_single_value_subquery |
163
existing_table_item . int_field_name membership_operator int_single_member_subquery |
164
# ( existing_table_item . int_field_name , existing_table_item . int_field_name ) not IN int_double_member_subquery |
165
existing_table_item . char_field_name membership_operator char_single_member_subquery |
166
# ( existing_table_item . char_field_name , existing_table_item . char_field_name ) not IN char_double_member_subquery |
167
# ( _digit, _digit ) not IN int_double_member_subquery |
168
# ( _char, _char ) not IN char_double_member_subquery |
169
existing_table_item . int_field_name membership_operator int_single_union_subquery |
170
existing_table_item . char_field_name membership_operator char_single_union_subquery ;
172
general_subquery_union_test_disabled:
173
existing_table_item . char_field_name arithmetic_operator all_any char_single_union_subquery_disabled |
174
existing_table_item . int_field_name arithmetic_operator all_any int_single_union_subquery_disabled ;
177
not EXISTS ( int_single_member_subquery ) |
178
not EXISTS ( char_single_member_subquery ) |
179
not EXISTS int_correlated_subquery |
180
not EXISTS char_correlated_subquery |
181
existing_table_item . int_field_name membership_operator int_correlated_subquery |
182
existing_table_item . char_field_name membership_operator char_correlated_subquery |
183
int_single_value_subquery IS not NULL |
184
char_single_value_subquery IS not NULL ;
186
int_single_value_subquery:
187
( SELECT distinct select_option aggregate subquery_table_one_two . int_field_name ) AS { "SUBQUERY".$subquery_idx."_field1" }
189
( SELECT distinct select_option aggregate subquery_table_one_two . int_field_name ) AS { "SUBQUERY".$subquery_idx."_field1" }
191
( SELECT _digit FROM DUMMY ) ;
193
char_single_value_subquery:
194
( SELECT distinct select_option aggregate subquery_table_one_two . char_field_name ) AS { "SUBQUERY".$subquery_idx."_field1" }
196
( SELECT distinct select_option aggregate subquery_table_one_two . char_field_name ) AS { "SUBQUERY".$subquery_idx."_field1" }
198
( SELECT _char FROM DUMMY ) ;
200
int_single_member_subquery:
201
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SUBQUERY".$subquery_idx."_field1" }
203
single_subquery_group_by
205
( SELECT _digit FROM DUMMY ) ;
207
int_single_union_subquery:
208
( SELECT _digit FROM DUMMY UNION all_distinct SELECT _digit FROM DUMMY ) ;
210
int_single_union_subquery_disabled:
211
int_single_member_subquery UNION all_distinct int_single_member_subquery ;
213
int_double_member_subquery:
214
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SUBQUERY".$subquery_idx."_field1" } ,
215
subquery_table_one_two . int_field_name AS { SUBQUERY.$subquery_idx."_field2" }
217
double_subquery_group_by
219
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SUBQUERY".$subquery_idx."_field1" } ,
220
aggregate subquery_table_one_two . int_field_name ) AS { SUBQUERY.$subquery_idx."_field2" }
222
single_subquery_group_by
224
( SELECT _digit , _digit FROM DUMMY UNION all_distinct SELECT _digit, _digit FROM DUMMY ) ;
226
char_single_member_subquery:
227
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SUBQUERY".$subquery_idx."_field1" }
229
single_subquery_group_by
232
char_single_union_subquery:
233
( SELECT _char FROM DUMMY UNION all_distinct SELECT _char FROM DUMMY ) ;
235
char_single_union_subquery_disabled:
236
char_single_member_subquery UNION all_distinct char_single_member_subquery ;
238
char_double_member_subquery:
239
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SUBQUERY".$subquery_idx."_field1" } ,
240
subquery_table_one_two . char_field_name AS { SUBQUERY.$subquery_idx."_field2" }
242
double_subquery_group_by
244
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SUBQUERY".$subquery_idx."_field1" } ,
245
aggregate subquery_table_one_two . char_field_name ) AS { SUBQUERY.$subquery_idx."_field2" }
247
single_subquery_group_by
249
( SELECT _char , _char FROM DUMMY UNION all_distinct SELECT _char , _char FROM DUMMY ) ;
251
int_correlated_subquery:
252
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SUBQUERY".$subquery_idx."_field1" }
253
FROM subquery_join_list
254
correlated_subquery_where_clause ) ;
256
char_correlated_subquery:
257
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SUBQUERY".$subquery_idx."_field1" }
258
FROM subquery_join_list
259
correlated_subquery_where_clause ) ;
261
int_scalar_correlated_subquery:
262
( SELECT distinct select_option aggregate subquery_table_one_two . int_field_name ) AS { "SUBQUERY".$subquery_idx."_field1" }
263
FROM subquery_join_list
264
correlated_subquery_where_clause ) ;
267
FROM subquery_join_list
268
subquery_where_clause ;
270
subquery_where_clause:
271
| | WHERE subquery_where_list ;
273
correlated_subquery_where_clause:
274
WHERE correlated_subquery_where_list ;
276
correlated_subquery_where_list:
277
correlated_subquery_where_item |
278
correlated_subquery_where_item and_or correlated_subquery_where_item |
279
correlated_subquery_where_item and_or subquery_where_item ;
281
correlated_subquery_where_item:
282
existing_subquery_table_item . int_field_name arithmetic_operator existing_table_item . int_field_name |
283
existing_subquery_table_item . char_field_name arithmetic_operator existing_table_item . char_field_name ;
286
subquery_where_item | subquery_where_item | subquery_where_item |
287
( subquery_where_item and_or subquery_where_item ) ;
290
existing_subquery_table_item . int_field_name arithmetic_operator _digit |
291
existing_subquery_table_item . char_field_name arithmetic_operator _char |
292
existing_subquery_table_item . int_field_name arithmetic_operator existing_subquery_table_item . int_field_name |
293
existing_subquery_table_item . char_field_name arithmetic_operator existing_subquery_table_item . char_field_name |
297
subquery_new_table_item | subquery_new_table_item |
298
( subquery_new_table_item join_type subquery_new_table_item ON (subquery_join_condition_item ) ) |
299
( subquery_new_table_item join_type subquery_new_table_item ON (subquery_join_condition_item ) ) |
300
( 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 ) ) ;
302
subquery_join_condition_item:
303
subquery_current_table_item . int_field_name = subquery_previous_table_item . int_indexed subquery_on_subquery |
304
subquery_current_table_item . int_indexed = subquery_previous_table_item . int_field_name subquery_on_subquery |
305
subquery_current_table_item . `col_varchar_key` = subquery_previous_table_item . char_field_name subquery_on_subquery |
306
subquery_current_table_item . char_field_name = subquery_previous_table_item . `col_varchar_key` subquery_on_subquery ;
308
subquery_on_subquery:
309
|||||||||||||||||||| { $child_subquery_idx += 1 ; $child_subquery_tables=0 ; ""} and_or general_child_subquery ;
311
single_subquery_group_by:
312
| | | | | | | | | GROUP BY { SUBQUERY.$subquery_idx."_field1" } ;
315
double_subquery_group_by:
316
| | | | | | | | | GROUP BY { SUBQUERY.$subquery_idx."_field1" } , { SUBQUERY.$subquery_idx."_field2" } ;
319
| | | | | | | | | | HAVING subquery_having_list ;
321
subquery_having_list:
322
subquery_having_item |
323
subquery_having_item |
324
(subquery_having_list and_or subquery_having_item) ;
326
subquery_having_item:
327
existing_subquery_table_item . int_field_name arithmetic_operator _digit |
328
existing_subquery_table_item . int_field_name arithmetic_operator _char ;
331
################################################################################
332
# Child subquery rules
333
################################################################################
336
{ $child_subquery_idx += 1 ; $child_subquery_tables=0 ; ""} child_subquery_type ;
339
general_child_subquery | special_child_subquery ;
341
general_child_subquery:
342
existing_subquery_table_item . int_field_name arithmetic_operator int_single_value_child_subquery |
343
existing_subquery_table_item . char_field_name arithmetic_operator char_single_value_child_subquery |
344
existing_subquery_table_item . int_field_name membership_operator int_single_member_child_subquery |
345
# ( existing_subquery_table_item . int_field_name , existing_subquery_table_item . int_field_name ) not IN int_double_member_child_subquery |
346
existing_subquery_table_item . char_field_name membership_operator char_single_member_child_subquery |
347
# ( existing_subquery_table_item . char_field_name , existing_subquery_table_item . char_field_name ) not IN char_double_member_child_subquery |
348
# ( _digit, _digit ) not IN int_double_member_child_subquery |
349
# ( _char, _char ) not IN char_double_member_child_subquery |
350
existing_subquery_table_item . int_field_name membership_operator int_single_union_child_subquery |
351
existing_subquery_table_item . char_field_name membership_operator char_single_union_child_subquery ;
353
special_child_subquery:
354
not EXISTS ( int_single_member_child_subquery ) |
355
not EXISTS ( char_single_member_child_subquery ) |
356
not EXISTS int_correlated_child_subquery |
357
not EXISTS char_correlated_child_subquery |
358
existing_subquery_table_item . int_field_name membership_operator int_correlated_child_subquery |
359
existing_subquery_table_item . char_field_name membership_operator char_correlated_child_subquery ;
362
int_single_value_child_subquery:
363
( SELECT distinct select_option aggregate child_subquery_table_one_two . int_field_name ) AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" }
364
child_subquery_body ) ;
366
char_single_value_child_subquery:
367
( SELECT distinct select_option aggregate child_subquery_table_one_two . char_field_name ) AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" }
368
child_subquery_body ) ;
370
int_single_member_child_subquery:
371
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" }
373
single_child_subquery_group_by
374
child_subquery_having ) ;
376
int_single_union_child_subquery:
377
( SELECT _digit FROM DUMMY UNION all_distinct SELECT _digit FROM DUMMY ) ;
379
int_double_member_child_subquery:
380
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" } ,
381
child_subquery_table_one_two . int_field_name AS { child_subquery.$child_subquery_idx."_field2" }
383
double_child_subquery_group_by
384
child_subquery_having ) |
385
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" } ,
386
aggregate child_subquery_table_one_two . int_field_name ) AS { child_subquery.$child_subquery_idx."_field2" }
388
single_child_subquery_group_by
389
child_subquery_having );
391
char_single_member_child_subquery:
392
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" }
394
single_child_subquery_group_by
395
child_subquery_having) ;
397
char_single_union_child_subquery:
398
( SELECT _digit FROM DUMMY UNION all_distinct SELECT _digit FROM DUMMY ) ;
400
char_double_member_child_subquery:
401
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" } ,
402
child_subquery_table_one_two . char_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field2" }
404
double_child_subquery_group_by
405
child_subquery_having ) |
406
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" } ,
407
aggregate child_subquery_table_one_two . char_field_name ) AS { "CHILD_SUBQUERY".$child_subquery_idx."_field2" }
409
single_child_subquery_group_by
410
child_subquery_having );
412
int_correlated_child_subquery:
413
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "CHILD_SUBQUERY".$subquery_idx."_field1" }
414
FROM child_subquery_join_list
415
correlated_child_subquery_where_clause ) ;
417
char_correlated_child_subquery:
418
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "CHILD_SUBQUERY".$subquery_idx."_field1" }
419
FROM child_subquery_join_list
420
correlated_child_subquery_where_clause ) ;
423
FROM child_subquery_join_list
424
child_subquery_where_clause ;
426
child_subquery_where_clause:
427
| WHERE child_subquery_where_list ;
429
correlated_child_subquery_where_clause:
430
WHERE correlated_child_subquery_where_list ;
432
correlated_child_subquery_where_list:
433
correlated_child_subquery_where_item | correlated_child_subquery_where_item | correlated_child_subquery_where_item |
434
correlated_child_subquery_where_item and_or correlated_child_subquery_where_item |
435
correlated_child_subquery_where_item and_or child_subquery_where_item ;
437
correlated_child_subquery_where_item:
438
existing_child_subquery_table_item . int_field_name arithmetic_operator existing_subquery_table_item . int_field_name |
439
existing_child_subquery_table_item . char_field_name arithmetic_operator existing_subquery_table_item . char_field_name ;
441
child_subquery_where_list:
442
child_subquery_where_item | child_subquery_where_item | child_subquery_where_item |
443
( child_subquery_where_item and_or child_subquery_where_item ) ;
445
child_subquery_where_item:
446
existing_child_subquery_table_item . int_field_name arithmetic_operator _digit |
447
existing_child_subquery_table_item . char_field_name arithmetic_operator _char |
448
existing_child_subquery_table_item . int_field_name arithmetic_operator existing_child_subquery_table_item . int_field_name |
449
existing_child_subquery_table_item . char_field_name arithmetic_operator existing_child_subquery_table_item . char_field_name |
450
child_child_subquery ;
452
child_subquery_join_list:
453
child_subquery_new_table_item | child_subquery_new_table_item |
454
( child_subquery_new_table_item join_type child_subquery_new_table_item ON (child_subquery_join_condition_item ) ) |
455
( child_subquery_new_table_item join_type child_subquery_new_table_item ON (child_subquery_join_condition_item ) ) |
456
( 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 ) ) ;
458
child_subquery_join_condition_item:
459
child_subquery_current_table_item . int_field_name = child_subquery_previous_table_item . int_indexed |
460
child_subquery_current_table_item . int_indexed = child_subquery_previous_table_item . int_field_name |
461
child_subquery_current_table_item . `col_varchar_key` = child_subquery_previous_table_item . char_field_name |
462
child_subquery_current_table_item . char_field_name = child_subquery_previous_table_item . `col_varchar_key` ;
464
single_child_subquery_group_by:
465
| | | | | | | | | GROUP BY { child_subquery.$child_subquery_idx."_field1" } ;
468
double_child_subquery_group_by:
469
| | | | | | | | | GROUP BY { child_subquery.$child_subquery_idx."_field1" } , { child_subquery.$child_subquery_idx."_field2" } ;
471
child_subquery_having:
472
| | | | | | | | | | HAVING child_subquery_having_list ;
474
child_subquery_having_list:
475
child_subquery_having_item |
476
child_subquery_having_item |
477
(child_subquery_having_list and_or child_subquery_having_item) ;
479
child_subquery_having_item:
480
existing_child_subquery_table_item . int_field_name arithmetic_operator _digit |
481
existing_child_subquery_table_item . int_field_name arithmetic_operator _char ;
484
################################################################################
485
# The range_predicate_1* rules below are in place to ensure we hit the #
486
# index_merge/sort_union optimization. #
487
# NOTE: combinations of the predicate_1 and predicate_2 rules tend to hit the #
488
# index_merge/intersect optimization #
489
################################################################################
491
range_predicate1_list:
492
range_predicate1_item |
493
( range_predicate1_item OR range_predicate1_item ) ;
495
range_predicate1_item:
496
table1 . int_indexed not BETWEEN _tinyint_unsigned[invariant] AND ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) |
497
table1 . `col_varchar_key` arithmetic_operator _char[invariant] |
498
table1 . int_indexed not IN (number_list) |
499
table1 . `col_varchar_key` not IN (char_list) |
500
table1 . `pk` > _tinyint_unsigned[invariant] AND table1 . `pk` < ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) |
501
table1 . `col_int_key` > _tinyint_unsigned[invariant] AND table1 . `col_int_key` < ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) ;
503
################################################################################
504
# The range_predicate_2* rules below are in place to ensure we hit the #
505
# index_merge/union optimization. #
506
# NOTE: combinations of the predicate_1 and predicate_2 rules tend to hit the #
507
# index_merge/intersect optimization #
508
################################################################################
510
range_predicate2_list:
511
range_predicate2_item |
512
( range_predicate2_item and_or range_predicate2_item ) ;
514
range_predicate2_item:
515
table1 . `pk` = _tinyint_unsigned |
516
table1 . `col_int_key` = _tinyint_unsigned |
517
table1 . `col_varchar_key` = _char |
518
table1 . int_indexed = _tinyint_unsigned |
519
table1 . `col_varchar_key` = _char |
520
table1 . int_indexed = existing_table_item . int_indexed |
521
table1 . `col_varchar_key` = existing_table_item . `col_varchar_key` ;
523
################################################################################
524
# The number and char_list rules are for creating WHERE conditions that test #
525
# 'field' IN (list_of_items) #
526
################################################################################
528
_tinyint_unsigned | number_list, _tinyint_unsigned ;
531
_char | char_list, _char ;
533
################################################################################
534
# We ensure that a GROUP BY statement includes all nonaggregates. #
535
# This helps to ensure the query is more useful in detecting real errors / #
536
# that the query doesn't lend itself to variable result sets #
537
################################################################################
540
{ scalar(@nonaggregates) > 0 ? " GROUP BY ".join (', ' , @nonaggregates ) : "" } ;
543
| HAVING having_list;
548
(having_list and_or having_item) ;
551
existing_select_item arithmetic_operator value |
552
existing_select_item arithmetic_operator value |
553
existing_select_item arithmetic_operator value |
554
existing_select_item arithmetic_operator value |
555
existing_select_item arithmetic_operator value |
556
existing_select_item arithmetic_operator value |
557
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} general_subquery;
559
################################################################################
560
# We use the total_order_by rule when using the LIMIT operator to ensure that #
561
# we have a consistent result set - server1 and server2 should not differ #
562
################################################################################
566
ORDER BY table1 . _field_indexed desc , total_order_by limit |
567
ORDER BY order_by_list |
568
ORDER BY order_by_list, total_order_by limit ;
571
{ join(', ', map { "field".$_." /*+JavaDB:Postgres: NULLS FIRST */" } (1..$fields) ) };
575
order_by_item , order_by_list ;
578
table1 . _field_indexed /*+JavaDB:Postgres: NULLS FIRST*/ , existing_table_item .`pk` desc |
579
table1 . _field_indexed desc |
580
existing_select_item desc |
581
CONCAT( existing_table_item . char_field_name, existing_table_item . char_field_name ) /*+JavaDB:Postgres: NULLS FIRST*/ ;
583
ASC /*+JavaDB:Postgres: NULLS FIRST */| /*+JavaDB:Postgres: NULLS FIRST */ | DESC /*+JavaDB:Postgres: NULLS LAST */ ;
587
| | LIMIT limit_size | LIMIT limit_size OFFSET _digit;
590
nonaggregate_select_item |
591
nonaggregate_select_item |
592
# aggregate_select_item |
594
nonaggregate_select_item |
595
nonaggregate_select_item |
596
# aggregate_select_item |
599
################################################################################
600
# We have the perl code here to help us write more sensible queries #
601
# It allows us to use field1...fieldn in the WHERE, ORDER BY, and GROUP BY #
602
# clauses so that the queries will produce more stable and interesting results #
603
################################################################################
605
nonaggregate_select_item:
606
table_one_two . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
607
table_one_two . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
608
table_one_two . _field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
610
aggregate_select_item:
611
aggregate table_one_two . _field ) AS { "field".++$fields };
614
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} select_subquery_body;
616
select_subquery_body:
617
int_single_value_subquery AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
618
char_single_value_subquery AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
619
int_scalar_correlated_subquery AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
621
select_subquery_body_disabled:
622
( SELECT _digit UNION all_distinct ( SELECT _digit ) LIMIT 1 ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
623
( SELECT _char UNION all_distinct ( SELECT _char ) LIMIT 1 ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
625
################################################################################
626
# The combo_select_items are for 'spice'
627
################################################################################
630
( ( table_one_two . int_field_name ) math_operator ( table_one_two . int_field_name ) ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
631
CONCAT( table_one_two . char_field_name , table_one_two . char_field_name ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
634
table1 | table1 | table2 ;
636
subquery_table_one_two:
637
{ "SUBQUERY".$subquery_idx."_t1" ; } | { "SUBQUERY".$subquery_idx."_t1" ; } |
638
{ "SUBQUERY".$subquery_idx."_t1" ; } | { "SUBQUERY".$subquery_idx."_t2" ; } ;
640
child_subquery_table_one_two:
641
{ "CHILD_SUBQUERY".$child_subquery_idx."_t1" ; } | { "CHILD_SUBQUERY".$child_subquery_idx."_t1" ; } |
642
{ "CHILD_SUBQUERY".$child_subquery_idx."_t1" ; } | { "CHILD_SUBQUERY".$child_subquery_idx."_t2" ; } ;
645
COUNT( distinct | SUM( distinct | MIN( distinct | MAX( distinct ;
647
################################################################################
648
# The following rules are for writing more sensible queries - that we don't #
649
# reference tables / fields that aren't present in the query and that we keep #
650
# track of what we have added. You shouldn't need to touch these ever #
651
################################################################################
653
_table AS { "table".++$tables } | _table AS { "table".++$tables } | _table AS { "table".++$tables } |
654
( from_subquery ) AS { "table".++$tables } ;
657
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} SELECT distinct select_option subquery_table_one_two . * subquery_body ;
659
subquery_new_table_item:
660
_table AS { "SUBQUERY".$subquery_idx."_t".++$subquery_tables } ;
662
child_subquery_new_table_item:
663
_table AS { "CHILD_SUBQUERY".$child_subquery_idx."_t".++$child_subquery_tables } ;
668
subquery_current_table_item:
669
{ "SUBQUERY".$subquery_idx."_t".$subquery_tables } ;
671
child_subquery_current_table_item:
672
{ "CHILD_SUBQUERY".$child_subquery_idx."_t".$child_subquery_tables } ;
675
{ "table".($tables - 1) };
677
subquery_previous_table_item:
678
{ "SUBQUERY".$subquery_idx."_t".($subquery_tables-1) } ;
680
child_subquery_previous_table_item:
681
{ "CHILD_SUBQUERY".$child_subquery_idx."_t".($child_subquery_tables-1) } ;
684
{ "table".$prng->int(1,$tables) };
686
existing_subquery_table_item:
687
{ "SUBQUERY".$subquery_idx."_t".$prng->int(1,$subquery_tables) } ;
689
existing_child_subquery_table_item:
690
{ "CHILD_SUBQUERY".$child_subquery_idx."_t".$prng->int(1,$child_subquery_tables) } ;
692
existing_select_item:
693
{ "field".$prng->int(1,$fields) };
695
################################################################################
696
# end of utility rules #
697
################################################################################
700
= | > | < | != | <> | <= | >= ;
704
arithmetic_operator all_any |
710
################################################################################
711
# Used for creating combo_items - ie (field1 + field2) AS fieldX #
712
# We ignore division to prevent division by zero errors #
713
################################################################################
717
################################################################################
718
# We stack AND to provide more interesting options for the optimizer #
719
# Alter these percentages at your own risk / look for coverage regressions #
720
# with --debug if you play with these. Those optimizations that require an #
721
# OR-only list in the WHERE clause are specifically stacked in another rule #
722
################################################################################
728
| | | ALL | DISTINCT ;
732
_digit | _digit | _digit | _digit | _tinyint_unsigned|
733
_char(2) | _char(2) | _char(2) | _char(2) | _char(2) ;
736
A | B | C | BB | CC | B | C | BB | CC |
737
CC | CC | CC | CC | CC |
738
C | C | C | C | C | D ;
740
################################################################################
741
# Add a possibility for 'view' to occur at the end of the previous '_table' rule
742
# to allow a chance to use views (when running the RQG with --views)
743
################################################################################
746
view_A | view_B | view_C | view_BB | view_CC ;
749
int_field_name | char_field_name ;
752
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | _tinyint_unsigned ;
755
`pk` | `col_int_key` | `col_int_nokey` ;
758
`pk` | `col_int_key` ;
762
`col_varchar_key` | `col_varchar_nokey` ;
764
################################################################################
765
# We define LIMIT_rows in this fashion as LIMIT values can differ depending on #
766
# how large the LIMIT is - LIMIT 2 = LIMIT 9 != LIMIT 19 #
767
################################################################################
770
1 | 2 | 10 | 100 | 1000;
added
removed
tests/test_tools/randgen/conf/optimizer/optimizer_subquery_portable.yy
