801 lines
36 KiB
Plaintext
801 lines
36 KiB
Plaintext
# Copyright (C) 2008-2010 Sun Microsystems, Inc. All rights reserved.
|
|
# Use is subject to license terms.
|
|
#
|
|
# This program is free software; you can redistribute it and/or modify
|
|
# it under the terms of the GNU General Public License as published by
|
|
# the Free Software Foundation; version 2 of the License.
|
|
#
|
|
# This program is distributed in the hope that it will be useful, but
|
|
# WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
|
|
# General Public License for more details.
|
|
#
|
|
# You should have received a copy of the GNU General Public License
|
|
# along with this program; if not, write to the Free Software
|
|
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301
|
|
# USA
|
|
|
|
# **NOTE** Joins for this grammar are currently not working as intended.
|
|
# For example, if we have tables 1, 2, and 3, we end up with ON conditions that
|
|
# only involve tables 2 and 3.
|
|
# This will be fixed, but initial attempts at altering this had a negative
|
|
# impact on the coverage the test was providing. To be fixed when scheduling
|
|
# permits. We are still seeing significant coverage with the grammar as-is.
|
|
|
|
################################################################################
|
|
# optimizer_subquery.yy: Random Query Generator grammar for testing subquery #
|
|
# optimizations. This grammar *should* hit the #
|
|
# optimizations listed here: #
|
|
# https://inside.mysql.com/wiki/Optimizer_grammar_worksheet #
|
|
# see: WL#5006 Random Query Generator testing of Azalea Optimizer- subqueries #
|
|
# https://intranet.mysql.com/worklog/QA-Sprint/?tid=5006 #
|
|
# #
|
|
# recommendations: #
|
|
# queries: 10k+. We can see a lot with lower values, but over 10k is #
|
|
# best. The intersect optimization happens with low frequency #
|
|
# so larger values help us to hit it at least some of the time #
|
|
# engines: MyISAM *and* Innodb. Certain optimizations are only hit with #
|
|
# one engine or another and we should use both to ensure we #
|
|
# are getting maximum coverage #
|
|
# Validators: ResultsetComparatorSimplify #
|
|
# - used on server-server comparisons #
|
|
# Transformer - used on a single server #
|
|
# - creates equivalent versions of a single query #
|
|
# SelectStability - used on a single server #
|
|
# - ensures the same query produces stable result sets #
|
|
################################################################################
|
|
|
|
################################################################################
|
|
# The perl code in {} helps us with bookkeeping for writing more sensible #
|
|
# queries. We need to keep track of these items to ensure we get interesting #
|
|
# and stable queries that find bugs rather than wondering if our query is #
|
|
# dodgy. #
|
|
################################################################################
|
|
query:
|
|
{ @nonaggregates = () ; $tables = 0 ; $fields = 0 ; $subquery_idx=0 ; $child_subquery_idx=0 ; "" } main_select ;
|
|
|
|
main_select:
|
|
simple_select | simple_select | simple_select | simple_select |
|
|
mixed_select | mixed_select | mixed_select | mixed_select |
|
|
aggregate_select ;
|
|
|
|
mixed_select:
|
|
explain_extended SELECT distinct straight_join select_option select_list
|
|
FROM join_list
|
|
where_clause
|
|
group_by_clause
|
|
having_clause
|
|
order_by_clause ;
|
|
|
|
simple_select:
|
|
explain_extended SELECT distinct straight_join select_option simple_select_list
|
|
FROM join_list
|
|
where_clause
|
|
optional_group_by
|
|
having_clause
|
|
order_by_clause ;
|
|
|
|
aggregate_select:
|
|
explain_extended SELECT distinct straight_join select_option aggregate_select_list
|
|
FROM join_list
|
|
where_clause
|
|
optional_group_by
|
|
having_clause
|
|
order_by_clause ;
|
|
|
|
explain_extended:
|
|
| | | | | | | | | explain_extended2 ;
|
|
|
|
explain_extended2: | | | | EXPLAIN | EXPLAIN EXTENDED ;
|
|
|
|
distinct: DISTINCT | | | | | | | | | ;
|
|
|
|
select_option: | | | | | | | | | | | SQL_SMALL_RESULT ;
|
|
|
|
straight_join: | | | | | | | | | | | STRAIGHT_JOIN ;
|
|
|
|
select_list:
|
|
new_select_item |
|
|
new_select_item , select_list |
|
|
new_select_item , select_list ;
|
|
|
|
simple_select_list:
|
|
nonaggregate_select_item |
|
|
nonaggregate_select_item , simple_select_list |
|
|
nonaggregate_select_item , simple_select_list ;
|
|
|
|
aggregate_select_list:
|
|
aggregate_select_item | aggregate_select_item |
|
|
aggregate_select_item, aggregate_select_list ;
|
|
|
|
join_list:
|
|
################################################################################
|
|
# this limits us to 2 and 3 table joins / can use it if we hit #
|
|
# too many mega-join conditions which take too long to run #
|
|
################################################################################
|
|
( new_table_item join_type new_table_item ON (join_condition_item ) ) |
|
|
( new_table_item join_type ( ( new_table_item join_type new_table_item ON (join_condition_item ) ) ) ON (join_condition_item ) ) ;
|
|
|
|
join_list_disabled:
|
|
################################################################################
|
|
# preventing deep join nesting for run time / table access methods are more #
|
|
# important here - join.yy can provide deeper join coverage #
|
|
# Enabling this / swapping out with join_list above can produce some #
|
|
# time-consuming queries. #
|
|
################################################################################
|
|
|
|
new_table_item |
|
|
( new_table_item join_type join_list ON (join_condition_item ) ) ;
|
|
|
|
join_type:
|
|
INNER JOIN | left_right outer JOIN | STRAIGHT_JOIN ;
|
|
|
|
join_condition_item:
|
|
current_table_item . int_indexed = previous_table_item . int_field_name on_subquery |
|
|
current_table_item . int_field_name = previous_table_item . int_indexed on_subquery |
|
|
current_table_item . `col_varchar_key` = previous_table_item . char_field_name on_subquery |
|
|
current_table_item . char_field_name = previous_table_item . `col_varchar_key` on_subquery ;
|
|
|
|
on_subquery:
|
|
|||||||||||||||||||| { $subquery_idx += 1 ; $subquery_tables=0 ; ""} and_or general_subquery ;
|
|
|
|
|
|
left_right:
|
|
LEFT | RIGHT ;
|
|
|
|
outer:
|
|
| OUTER ;
|
|
|
|
where_clause:
|
|
WHERE ( where_subquery ) and_or where_list ;
|
|
|
|
|
|
where_list:
|
|
generic_where_list |
|
|
range_predicate1_list | range_predicate2_list |
|
|
range_predicate1_list and_or generic_where_list |
|
|
range_predicate2_list and_or generic_where_list ;
|
|
|
|
|
|
generic_where_list:
|
|
where_item |
|
|
( where_item and_or where_item ) ;
|
|
|
|
not:
|
|
| | | NOT;
|
|
|
|
where_item:
|
|
where_subquery |
|
|
table1 . int_field_name arithmetic_operator existing_table_item . int_field_name |
|
|
existing_table_item . char_field_name arithmetic_operator _char |
|
|
existing_table_item . char_field_name arithmetic_operator existing_table_item . char_field_name |
|
|
table1 . _field IS not NULL |
|
|
table1 . int_field_name arithmetic_operator existing_table_item . int_field_name |
|
|
existing_table_item . char_field_name arithmetic_operator _char |
|
|
existing_table_item . char_field_name arithmetic_operator existing_table_item . char_field_name |
|
|
table1 . _field IS not NULL ;
|
|
|
|
################################################################################
|
|
# subquery rules
|
|
################################################################################
|
|
|
|
where_subquery:
|
|
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} subquery_type ;
|
|
|
|
subquery_type:
|
|
general_subquery | special_subquery ;
|
|
|
|
general_subquery:
|
|
existing_table_item . int_field_name arithmetic_operator int_single_value_subquery |
|
|
existing_table_item . char_field_name arithmetic_operator char_single_value_subquery |
|
|
existing_table_item . int_field_name membership_operator int_single_member_subquery |
|
|
( existing_table_item . int_field_name , existing_table_item . int_field_name ) not IN int_double_member_subquery |
|
|
existing_table_item . char_field_name membership_operator char_single_member_subquery |
|
|
( existing_table_item . char_field_name , existing_table_item . char_field_name ) not IN char_double_member_subquery |
|
|
( _digit, _digit ) not IN int_double_member_subquery |
|
|
( _char, _char ) not IN char_double_member_subquery |
|
|
existing_table_item . int_field_name membership_operator int_single_union_subquery |
|
|
existing_table_item . char_field_name membership_operator char_single_union_subquery ;
|
|
|
|
general_subquery_union_test_disabled:
|
|
existing_table_item . char_field_name arithmetic_operator all_any char_single_union_subquery_disabled |
|
|
existing_table_item . int_field_name arithmetic_operator all_any int_single_union_subquery_disabled ;
|
|
|
|
special_subquery:
|
|
not EXISTS ( int_single_member_subquery ) |
|
|
not EXISTS ( char_single_member_subquery ) |
|
|
not EXISTS int_correlated_subquery |
|
|
not EXISTS char_correlated_subquery |
|
|
existing_table_item . int_field_name membership_operator int_correlated_subquery |
|
|
existing_table_item . char_field_name membership_operator char_correlated_subquery |
|
|
int_single_value_subquery IS not NULL |
|
|
char_single_value_subquery IS not NULL ;
|
|
|
|
int_single_value_subquery:
|
|
( SELECT distinct select_option aggregate subquery_table_one_two . int_field_name ) AS { "SUBQUERY".$subquery_idx."_field1" }
|
|
subquery_body ) |
|
|
( SELECT distinct select_option aggregate subquery_table_one_two . int_field_name ) AS { "SUBQUERY".$subquery_idx."_field1" }
|
|
subquery_body ) |
|
|
( SELECT _digit FROM DUAL ) ;
|
|
|
|
char_single_value_subquery:
|
|
( SELECT distinct select_option aggregate subquery_table_one_two . char_field_name ) AS { "SUBQUERY".$subquery_idx."_field1" }
|
|
subquery_body ) |
|
|
( SELECT distinct select_option aggregate subquery_table_one_two . char_field_name ) AS { "SUBQUERY".$subquery_idx."_field1" }
|
|
subquery_body ) |
|
|
( SELECT _char FROM DUAL ) ;
|
|
|
|
int_single_member_subquery:
|
|
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SUBQUERY".$subquery_idx."_field1" }
|
|
subquery_body
|
|
single_subquery_group_by
|
|
subquery_having ) |
|
|
( SELECT _digit FROM DUAL ) ;
|
|
|
|
int_single_union_subquery:
|
|
( SELECT _digit UNION all_distinct SELECT _digit ) ;
|
|
|
|
int_single_union_subquery_disabled:
|
|
int_single_member_subquery UNION all_distinct int_single_member_subquery ;
|
|
|
|
int_double_member_subquery:
|
|
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SUBQUERY".$subquery_idx."_field1" } ,
|
|
subquery_table_one_two . int_field_name AS { SUBQUERY.$subquery_idx."_field2" }
|
|
subquery_body
|
|
double_subquery_group_by
|
|
subquery_having ) |
|
|
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SUBQUERY".$subquery_idx."_field1" } ,
|
|
aggregate subquery_table_one_two . int_field_name ) AS { SUBQUERY.$subquery_idx."_field2" }
|
|
subquery_body
|
|
single_subquery_group_by
|
|
subquery_having ) |
|
|
( SELECT _digit , _digit UNION all_distinct SELECT _digit, _digit ) ;
|
|
|
|
char_single_member_subquery:
|
|
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SUBQUERY".$subquery_idx."_field1" }
|
|
subquery_body
|
|
single_subquery_group_by
|
|
subquery_having) ;
|
|
|
|
char_single_union_subquery:
|
|
( SELECT _char UNION all_distinct SELECT _char ) ;
|
|
|
|
char_single_union_subquery_disabled:
|
|
char_single_member_subquery UNION all_distinct char_single_member_subquery ;
|
|
|
|
char_double_member_subquery:
|
|
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SUBQUERY".$subquery_idx."_field1" } ,
|
|
subquery_table_one_two . char_field_name AS { SUBQUERY.$subquery_idx."_field2" }
|
|
subquery_body
|
|
double_subquery_group_by
|
|
subquery_having ) |
|
|
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SUBQUERY".$subquery_idx."_field1" } ,
|
|
aggregate subquery_table_one_two . char_field_name ) AS { SUBQUERY.$subquery_idx."_field2" }
|
|
subquery_body
|
|
single_subquery_group_by
|
|
subquery_having ) |
|
|
( SELECT _char , _char UNION all_distinct SELECT _char , _char ) ;
|
|
|
|
int_correlated_subquery:
|
|
( SELECT distinct select_option subquery_table_one_two . int_field_name AS { "SUBQUERY".$subquery_idx."_field1" }
|
|
FROM subquery_join_list
|
|
correlated_subquery_where_clause ) ;
|
|
|
|
char_correlated_subquery:
|
|
( SELECT distinct select_option subquery_table_one_two . char_field_name AS { "SUBQUERY".$subquery_idx."_field1" }
|
|
FROM subquery_join_list
|
|
correlated_subquery_where_clause ) ;
|
|
|
|
int_scalar_correlated_subquery:
|
|
( SELECT distinct select_option aggregate subquery_table_one_two . int_field_name ) AS { "SUBQUERY".$subquery_idx."_field1" }
|
|
FROM subquery_join_list
|
|
correlated_subquery_where_clause ) ;
|
|
|
|
subquery_body:
|
|
FROM subquery_join_list
|
|
subquery_where_clause ;
|
|
|
|
subquery_where_clause:
|
|
| | WHERE subquery_where_list ;
|
|
|
|
correlated_subquery_where_clause:
|
|
WHERE correlated_subquery_where_list ;
|
|
|
|
correlated_subquery_where_list:
|
|
correlated_subquery_where_item |
|
|
correlated_subquery_where_item and_or correlated_subquery_where_item |
|
|
correlated_subquery_where_item and_or subquery_where_item ;
|
|
|
|
correlated_subquery_where_item:
|
|
existing_subquery_table_item . int_field_name arithmetic_operator existing_table_item . int_field_name |
|
|
existing_subquery_table_item . char_field_name arithmetic_operator existing_table_item . char_field_name ;
|
|
|
|
subquery_where_list:
|
|
subquery_where_item | subquery_where_item | subquery_where_item |
|
|
( subquery_where_item and_or subquery_where_item ) ;
|
|
|
|
subquery_where_item:
|
|
existing_subquery_table_item . int_field_name arithmetic_operator _digit |
|
|
existing_subquery_table_item . char_field_name arithmetic_operator _char |
|
|
existing_subquery_table_item . int_field_name arithmetic_operator existing_subquery_table_item . int_field_name |
|
|
existing_subquery_table_item . char_field_name arithmetic_operator existing_subquery_table_item . char_field_name |
|
|
child_subquery ;
|
|
|
|
subquery_join_list:
|
|
subquery_new_table_item | subquery_new_table_item |
|
|
( subquery_new_table_item join_type subquery_new_table_item ON (subquery_join_condition_item ) ) |
|
|
( subquery_new_table_item join_type subquery_new_table_item ON (subquery_join_condition_item ) ) |
|
|
( 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 ) ) ;
|
|
|
|
subquery_join_condition_item:
|
|
subquery_current_table_item . int_field_name = subquery_previous_table_item . int_indexed subquery_on_subquery |
|
|
subquery_current_table_item . int_indexed = subquery_previous_table_item . int_field_name subquery_on_subquery |
|
|
subquery_current_table_item . `col_varchar_key` = subquery_previous_table_item . char_field_name subquery_on_subquery |
|
|
subquery_current_table_item . char_field_name = subquery_previous_table_item . `col_varchar_key` subquery_on_subquery ;
|
|
|
|
subquery_on_subquery:
|
|
|||||||||||||||||||| { $child_subquery_idx += 1 ; $child_subquery_tables=0 ; ""} and_or general_child_subquery ;
|
|
|
|
single_subquery_group_by:
|
|
| | | | | | | | | GROUP BY { SUBQUERY.$subquery_idx."_field1" } ;
|
|
|
|
|
|
double_subquery_group_by:
|
|
| | | | | | | | | GROUP BY { SUBQUERY.$subquery_idx."_field1" } , { SUBQUERY.$subquery_idx."_field2" } ;
|
|
|
|
subquery_having:
|
|
| | | | | | | | | | HAVING subquery_having_list ;
|
|
|
|
subquery_having_list:
|
|
subquery_having_item |
|
|
subquery_having_item |
|
|
(subquery_having_list and_or subquery_having_item) ;
|
|
|
|
subquery_having_item:
|
|
existing_subquery_table_item . int_field_name arithmetic_operator _digit |
|
|
existing_subquery_table_item . int_field_name arithmetic_operator _char ;
|
|
|
|
|
|
################################################################################
|
|
# Child subquery rules
|
|
################################################################################
|
|
|
|
child_subquery:
|
|
{ $child_subquery_idx += 1 ; $child_subquery_tables=0 ; ""} child_subquery_type ;
|
|
|
|
child_subquery_type:
|
|
general_child_subquery | special_child_subquery ;
|
|
|
|
general_child_subquery:
|
|
existing_subquery_table_item . int_field_name arithmetic_operator int_single_value_child_subquery |
|
|
existing_subquery_table_item . char_field_name arithmetic_operator char_single_value_child_subquery |
|
|
existing_subquery_table_item . int_field_name membership_operator int_single_member_child_subquery |
|
|
( existing_subquery_table_item . int_field_name , existing_subquery_table_item . int_field_name ) not IN int_double_member_child_subquery |
|
|
existing_subquery_table_item . char_field_name membership_operator char_single_member_child_subquery |
|
|
( existing_subquery_table_item . char_field_name , existing_subquery_table_item . char_field_name ) not IN char_double_member_child_subquery |
|
|
( _digit, _digit ) not IN int_double_member_child_subquery |
|
|
( _char, _char ) not IN char_double_member_child_subquery |
|
|
existing_subquery_table_item . int_field_name membership_operator int_single_union_child_subquery |
|
|
existing_subquery_table_item . char_field_name membership_operator char_single_union_child_subquery ;
|
|
|
|
special_child_subquery:
|
|
not EXISTS ( int_single_member_child_subquery ) |
|
|
not EXISTS ( char_single_member_child_subquery ) |
|
|
not EXISTS int_correlated_child_subquery |
|
|
not EXISTS char_correlated_child_subquery |
|
|
existing_subquery_table_item . int_field_name membership_operator int_correlated_child_subquery |
|
|
existing_subquery_table_item . char_field_name membership_operator char_correlated_child_subquery ;
|
|
|
|
|
|
int_single_value_child_subquery:
|
|
( SELECT distinct select_option aggregate child_subquery_table_one_two . int_field_name ) AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" }
|
|
child_subquery_body ) ;
|
|
|
|
char_single_value_child_subquery:
|
|
( SELECT distinct select_option aggregate child_subquery_table_one_two . char_field_name ) AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" }
|
|
child_subquery_body ) ;
|
|
|
|
int_single_member_child_subquery:
|
|
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" }
|
|
child_subquery_body
|
|
single_child_subquery_group_by
|
|
child_subquery_having ) ;
|
|
|
|
int_single_union_child_subquery:
|
|
( SELECT _digit UNION all_distinct SELECT _digit ) ;
|
|
|
|
int_double_member_child_subquery:
|
|
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" } ,
|
|
child_subquery_table_one_two . int_field_name AS { child_subquery.$child_subquery_idx."_field2" }
|
|
child_subquery_body
|
|
double_child_subquery_group_by
|
|
child_subquery_having ) |
|
|
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" } ,
|
|
aggregate child_subquery_table_one_two . int_field_name ) AS { child_subquery.$child_subquery_idx."_field2" }
|
|
child_subquery_body
|
|
single_child_subquery_group_by
|
|
child_subquery_having );
|
|
|
|
char_single_member_child_subquery:
|
|
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" }
|
|
child_subquery_body
|
|
single_child_subquery_group_by
|
|
child_subquery_having) ;
|
|
|
|
char_single_union_child_subquery:
|
|
( SELECT _digit UNION all_distinct SELECT _digit ) ;
|
|
|
|
char_double_member_child_subquery:
|
|
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" } ,
|
|
child_subquery_table_one_two . char_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field2" }
|
|
child_subquery_body
|
|
double_child_subquery_group_by
|
|
child_subquery_having ) |
|
|
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" } ,
|
|
aggregate child_subquery_table_one_two . char_field_name ) AS { "CHILD_SUBQUERY".$child_subquery_idx."_field2" }
|
|
child_subquery_body
|
|
single_child_subquery_group_by
|
|
child_subquery_having );
|
|
|
|
int_correlated_child_subquery:
|
|
( SELECT distinct select_option child_subquery_table_one_two . int_field_name AS { "CHILD_SUBQUERY".$subquery_idx."_field1" }
|
|
FROM child_subquery_join_list
|
|
correlated_child_subquery_where_clause ) ;
|
|
|
|
char_correlated_child_subquery:
|
|
( SELECT distinct select_option child_subquery_table_one_two . char_field_name AS { "CHILD_SUBQUERY".$subquery_idx."_field1" }
|
|
FROM child_subquery_join_list
|
|
correlated_child_subquery_where_clause ) ;
|
|
|
|
child_subquery_body:
|
|
FROM child_subquery_join_list
|
|
child_subquery_where_clause ;
|
|
|
|
child_subquery_where_clause:
|
|
| WHERE child_subquery_where_list ;
|
|
|
|
correlated_child_subquery_where_clause:
|
|
WHERE correlated_child_subquery_where_list ;
|
|
|
|
correlated_child_subquery_where_list:
|
|
correlated_child_subquery_where_item | correlated_child_subquery_where_item | correlated_child_subquery_where_item |
|
|
correlated_child_subquery_where_item and_or correlated_child_subquery_where_item |
|
|
correlated_child_subquery_where_item and_or child_subquery_where_item ;
|
|
|
|
correlated_child_subquery_where_item:
|
|
existing_child_subquery_table_item . int_field_name arithmetic_operator existing_subquery_table_item . int_field_name |
|
|
existing_child_subquery_table_item . char_field_name arithmetic_operator existing_subquery_table_item . char_field_name ;
|
|
|
|
child_subquery_where_list:
|
|
child_subquery_where_item | child_subquery_where_item | child_subquery_where_item |
|
|
( child_subquery_where_item and_or child_subquery_where_item ) ;
|
|
|
|
child_subquery_where_item:
|
|
existing_child_subquery_table_item . int_field_name arithmetic_operator _digit |
|
|
existing_child_subquery_table_item . char_field_name arithmetic_operator _char |
|
|
existing_child_subquery_table_item . int_field_name arithmetic_operator existing_child_subquery_table_item . int_field_name |
|
|
existing_child_subquery_table_item . char_field_name arithmetic_operator existing_child_subquery_table_item . char_field_name |
|
|
child_child_subquery ;
|
|
|
|
child_subquery_join_list:
|
|
child_subquery_new_table_item | child_subquery_new_table_item |
|
|
( child_subquery_new_table_item join_type child_subquery_new_table_item ON (child_subquery_join_condition_item ) ) |
|
|
( child_subquery_new_table_item join_type child_subquery_new_table_item ON (child_subquery_join_condition_item ) ) |
|
|
( 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 ) ) ;
|
|
|
|
child_subquery_join_condition_item:
|
|
child_subquery_current_table_item . int_field_name = child_subquery_previous_table_item . int_indexed |
|
|
child_subquery_current_table_item . int_indexed = child_subquery_previous_table_item . int_field_name |
|
|
child_subquery_current_table_item . `col_varchar_key` = child_subquery_previous_table_item . char_field_name |
|
|
child_subquery_current_table_item . char_field_name = child_subquery_previous_table_item . `col_varchar_key` ;
|
|
|
|
single_child_subquery_group_by:
|
|
| | | | | | | | | GROUP BY { child_subquery.$child_subquery_idx."_field1" } ;
|
|
|
|
|
|
double_child_subquery_group_by:
|
|
| | | | | | | | | GROUP BY { child_subquery.$child_subquery_idx."_field1" } , { child_subquery.$child_subquery_idx."_field2" } ;
|
|
|
|
child_subquery_having:
|
|
| | | | | | | | | | HAVING child_subquery_having_list ;
|
|
|
|
child_subquery_having_list:
|
|
child_subquery_having_item |
|
|
child_subquery_having_item |
|
|
(child_subquery_having_list and_or child_subquery_having_item) ;
|
|
|
|
child_subquery_having_item:
|
|
existing_child_subquery_table_item . int_field_name arithmetic_operator _digit |
|
|
existing_child_subquery_table_item . int_field_name arithmetic_operator _char ;
|
|
|
|
|
|
################################################################################
|
|
# The range_predicate_1* rules below are in place to ensure we hit the #
|
|
# index_merge/sort_union optimization. #
|
|
# NOTE: combinations of the predicate_1 and predicate_2 rules tend to hit the #
|
|
# index_merge/intersect optimization #
|
|
################################################################################
|
|
|
|
range_predicate1_list:
|
|
range_predicate1_item |
|
|
( range_predicate1_item OR range_predicate1_item ) ;
|
|
|
|
range_predicate1_item:
|
|
table1 . int_indexed not BETWEEN _tinyint_unsigned[invariant] AND ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) |
|
|
table1 . `col_varchar_key` arithmetic_operator _char[invariant] |
|
|
table1 . int_indexed not IN (number_list) |
|
|
table1 . `col_varchar_key` not IN (char_list) |
|
|
table1 . `pk` > _tinyint_unsigned[invariant] AND table1 . `pk` < ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) |
|
|
table1 . `col_int_key` > _tinyint_unsigned[invariant] AND table1 . `col_int_key` < ( _tinyint_unsigned[invariant] + _tinyint_unsigned ) ;
|
|
|
|
################################################################################
|
|
# The range_predicate_2* rules below are in place to ensure we hit the #
|
|
# index_merge/union optimization. #
|
|
# NOTE: combinations of the predicate_1 and predicate_2 rules tend to hit the #
|
|
# index_merge/intersect optimization #
|
|
################################################################################
|
|
|
|
range_predicate2_list:
|
|
range_predicate2_item |
|
|
( range_predicate2_item and_or range_predicate2_item ) ;
|
|
|
|
range_predicate2_item:
|
|
table1 . `pk` = _tinyint_unsigned |
|
|
table1 . `col_int_key` = _tinyint_unsigned |
|
|
table1 . `col_varchar_key` = _char |
|
|
table1 . int_indexed = _tinyint_unsigned |
|
|
table1 . `col_varchar_key` = _char |
|
|
table1 . int_indexed = existing_table_item . int_indexed |
|
|
table1 . `col_varchar_key` = existing_table_item . `col_varchar_key` ;
|
|
|
|
################################################################################
|
|
# The number and char_list rules are for creating WHERE conditions that test #
|
|
# 'field' IN (list_of_items) #
|
|
################################################################################
|
|
number_list:
|
|
_tinyint_unsigned | number_list, _tinyint_unsigned ;
|
|
|
|
char_list:
|
|
_char | char_list, _char ;
|
|
|
|
################################################################################
|
|
# We ensure that a GROUP BY statement includes all nonaggregates. #
|
|
# This helps to ensure the query is more useful in detecting real errors / #
|
|
# that the query doesn't lend itself to variable result sets #
|
|
################################################################################
|
|
group_by_clause:
|
|
{ scalar(@nonaggregates) > 0 ? " GROUP BY ".join (', ' , @nonaggregates ) : "" } ;
|
|
|
|
optional_group_by:
|
|
| | group_by_clause ;
|
|
|
|
having_clause:
|
|
| HAVING having_list;
|
|
|
|
having_list:
|
|
having_item |
|
|
having_item |
|
|
(having_list and_or having_item) ;
|
|
|
|
having_item:
|
|
existing_select_item arithmetic_operator value |
|
|
existing_select_item arithmetic_operator value |
|
|
existing_select_item arithmetic_operator value |
|
|
existing_select_item arithmetic_operator value |
|
|
existing_select_item arithmetic_operator value |
|
|
existing_select_item arithmetic_operator value |
|
|
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} general_subquery;
|
|
|
|
################################################################################
|
|
# We use the total_order_by rule when using the LIMIT operator to ensure that #
|
|
# we have a consistent result set - server1 and server2 should not differ #
|
|
################################################################################
|
|
|
|
order_by_clause:
|
|
|
|
|
ORDER BY table1 . _field_indexed desc , total_order_by limit |
|
|
ORDER BY order_by_list |
|
|
ORDER BY order_by_list, total_order_by limit ;
|
|
|
|
total_order_by:
|
|
{ join(', ', map { "field".$_ } (1..$fields) ) };
|
|
|
|
order_by_list:
|
|
order_by_item |
|
|
order_by_item , order_by_list ;
|
|
|
|
order_by_item:
|
|
table1 . _field_indexed , existing_table_item .`pk` desc |
|
|
table1 . _field_indexed desc |
|
|
existing_select_item desc |
|
|
CONCAT ( existing_table_item . char_field_name, existing_table_item . char_field_name );
|
|
desc:
|
|
ASC | | DESC ;
|
|
|
|
|
|
limit:
|
|
| | LIMIT limit_size | LIMIT limit_size OFFSET _digit;
|
|
|
|
new_select_item:
|
|
nonaggregate_select_item |
|
|
nonaggregate_select_item |
|
|
aggregate_select_item |
|
|
combo_select_item |
|
|
nonaggregate_select_item |
|
|
nonaggregate_select_item |
|
|
aggregate_select_item |
|
|
select_subquery;
|
|
|
|
################################################################################
|
|
# We have the perl code here to help us write more sensible queries #
|
|
# It allows us to use field1...fieldn in the WHERE, ORDER BY, and GROUP BY #
|
|
# clauses so that the queries will produce more stable and interesting results #
|
|
################################################################################
|
|
|
|
nonaggregate_select_item:
|
|
table_one_two . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
|
|
table_one_two . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
|
|
table_one_two . _field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
|
|
|
|
aggregate_select_item:
|
|
aggregate table_one_two . _field ) AS { "field".++$fields };
|
|
|
|
select_subquery:
|
|
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} select_subquery_body;
|
|
|
|
select_subquery_body:
|
|
int_single_value_subquery AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
|
|
char_single_value_subquery AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
|
|
int_scalar_correlated_subquery AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
|
|
|
|
select_subquery_body_disabled:
|
|
( SELECT _digit UNION all_distinct ( SELECT _digit ) LIMIT 1 ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
|
|
( SELECT _char UNION all_distinct ( SELECT _char ) LIMIT 1 ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
|
|
|
|
################################################################################
|
|
# The combo_select_items are for 'spice'
|
|
################################################################################
|
|
|
|
combo_select_item:
|
|
( ( table_one_two . int_field_name ) math_operator ( table_one_two . int_field_name ) ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
|
|
CONCAT ( table_one_two . char_field_name , table_one_two . char_field_name ) AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
|
|
|
|
table_one_two:
|
|
table1 | table1 | table2 ;
|
|
|
|
subquery_table_one_two:
|
|
{ "SUBQUERY".$subquery_idx."_t1" ; } | { "SUBQUERY".$subquery_idx."_t1" ; } |
|
|
{ "SUBQUERY".$subquery_idx."_t1" ; } | { "SUBQUERY".$subquery_idx."_t2" ; } ;
|
|
|
|
child_subquery_table_one_two:
|
|
{ "CHILD_SUBQUERY".$child_subquery_idx."_t1" ; } | { "CHILD_SUBQUERY".$child_subquery_idx."_t1" ; } |
|
|
{ "CHILD_SUBQUERY".$child_subquery_idx."_t1" ; } | { "CHILD_SUBQUERY".$child_subquery_idx."_t2" ; } ;
|
|
|
|
aggregate:
|
|
COUNT( distinct | SUM( distinct | MIN( distinct | MAX( distinct ;
|
|
|
|
################################################################################
|
|
# The following rules are for writing more sensible queries - that we don't #
|
|
# reference tables / fields that aren't present in the query and that we keep #
|
|
# track of what we have added. You shouldn't need to touch these ever #
|
|
################################################################################
|
|
new_table_item:
|
|
_table AS { "table".++$tables } | _table AS { "table".++$tables } | _table AS { "table".++$tables } |
|
|
( from_subquery ) AS { "table".++$tables } ;
|
|
|
|
from_subquery:
|
|
{ $subquery_idx += 1 ; $subquery_tables=0 ; ""} SELECT distinct select_option subquery_table_one_two . * subquery_body ;
|
|
|
|
subquery_new_table_item:
|
|
_table AS { "SUBQUERY".$subquery_idx."_t".++$subquery_tables } ;
|
|
|
|
child_subquery_new_table_item:
|
|
_table AS { "CHILD_SUBQUERY".$child_subquery_idx."_t".++$child_subquery_tables } ;
|
|
|
|
current_table_item:
|
|
{ "table".$tables };
|
|
|
|
subquery_current_table_item:
|
|
{ "SUBQUERY".$subquery_idx."_t".$subquery_tables } ;
|
|
|
|
child_subquery_current_table_item:
|
|
{ "CHILD_SUBQUERY".$child_subquery_idx."_t".$child_subquery_tables } ;
|
|
|
|
previous_table_item:
|
|
{ "table".($tables - 1) };
|
|
|
|
subquery_previous_table_item:
|
|
{ "SUBQUERY".$subquery_idx."_t".($subquery_tables-1) } ;
|
|
|
|
child_subquery_previous_table_item:
|
|
{ "CHILD_SUBQUERY".$child_subquery_idx."_t".($child_subquery_tables-1) } ;
|
|
|
|
existing_table_item:
|
|
{ "table".$prng->int(1,$tables) };
|
|
|
|
existing_subquery_table_item:
|
|
{ "SUBQUERY".$subquery_idx."_t".$prng->int(1,$subquery_tables) } ;
|
|
|
|
existing_child_subquery_table_item:
|
|
{ "CHILD_SUBQUERY".$child_subquery_idx."_t".$prng->int(1,$child_subquery_tables) } ;
|
|
|
|
existing_select_item:
|
|
{ "field".$prng->int(1,$fields) };
|
|
|
|
################################################################################
|
|
# end of utility rules #
|
|
################################################################################
|
|
|
|
arithmetic_operator:
|
|
= | > | < | != | <> | <= | >= ;
|
|
|
|
|
|
membership_operator:
|
|
arithmetic_operator all_any |
|
|
not IN ;
|
|
|
|
all_any:
|
|
ALL | ANY | SOME ;
|
|
|
|
################################################################################
|
|
# Used for creating combo_items - ie (field1 + field2) AS fieldX #
|
|
# We ignore division to prevent division by zero errors #
|
|
################################################################################
|
|
math_operator:
|
|
+ | - | * ;
|
|
|
|
################################################################################
|
|
# We stack AND to provide more interesting options for the optimizer #
|
|
# Alter these percentages at your own risk / look for coverage regressions #
|
|
# with --debug if you play with these. Those optimizations that require an #
|
|
# OR-only list in the WHERE clause are specifically stacked in another rule #
|
|
################################################################################
|
|
and_or:
|
|
AND | AND | OR ;
|
|
|
|
all_distinct:
|
|
| | | | |
|
|
| | | ALL | DISTINCT ;
|
|
|
|
|
|
value:
|
|
_digit | _digit | _digit | _digit | _tinyint_unsigned|
|
|
_char(2) | _char(2) | _char(2) | _char(2) | _char(2) ;
|
|
|
|
_table:
|
|
A | B | C | BB | CC | B | C | BB | CC |
|
|
CC | CC | CC | CC | CC |
|
|
C | C | C | C | C | D | view ;
|
|
|
|
################################################################################
|
|
# Add a possibility for 'view' to occur at the end of the previous '_table' rule
|
|
# to allow a chance to use views (when running the RQG with --views)
|
|
################################################################################
|
|
|
|
view:
|
|
_A | _B | _C | _BB | _CC ;
|
|
|
|
_field:
|
|
int_field_name | char_field_name ;
|
|
|
|
_digit:
|
|
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | _tinyint_unsigned ;
|
|
|
|
int_field_name:
|
|
`pk` | `col_int_key` | `col_int_nokey` ;
|
|
|
|
int_indexed:
|
|
`pk` | `col_int_key` ;
|
|
|
|
|
|
char_field_name:
|
|
`col_varchar_key` | `col_varchar_nokey` ;
|
|
|
|
################################################################################
|
|
# We define LIMIT_rows in this fashion as LIMIT values can differ depending on #
|
|
# how large the LIMIT is - LIMIT 2 = LIMIT 9 != LIMIT 19 #
|
|
################################################################################
|
|
|
|
limit_size:
|
|
1 | 2 | 10 | 100 | 1000;
|