# Copyright (c) 2008, 2013, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License, version 2.0, # as published by the Free Software Foundation. # # This program is also distributed with certain software (including # but not limited to OpenSSL) that is licensed under separate terms, # as designated in a particular file or component or in included license # documentation. The authors of MySQL hereby grant you an additional # permission to link the program and your derivative works with the # separately licensed software that they have included with MySQL. # # 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, version 2.0, 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 ################################################################################ # outer_join.yy # Purpose: Random Query Generator grammar for testing larger (6 - 10 tables) JOINs # Tuning: Please tweak the rule table_ref ratio of table:join for larger joins # NOTE: be aware that larger (15-20 tables) queries can take far too # long to run to be of much interest for fast, automated testing # # Notes: This grammar is designed to be used with gendata=conf/outer_join.zz # It can be altered, but one will likely need field names # Additionally, it is not recommended to use the standard RQG-produced # tables as they way we pick tables can result in the use of # several large tables that will bog down a generated query # # Please rely largely on the _portable variant of this grammar if # doing 3-way comparisons as it has altered code that will produce # more standards-compliant queries for use with other DBMS's # # We keep the grammar here as it is in order to also test certain # MySQL-specific syntax variants. ################################################################################ query: { @nonaggregates = (); @aggregates = (); $tables = 0; $ext = ""; $fields = 0; @outer_tables = (); $stack->push(); "" } query_expr order_by_clause { $stack->pop(undef) } ; ############################################### ## Ref: ISO 9075, Chap 6.3 ## ############################################### ################################################################################ # recommend 4 tables : 1 joins for smaller queries, 3:1 for larger ones ################################################################################ table_ref: table | table | table | table | joined_table #| derived_table # See note where 'derived_table:' is defined. ; ## # There are two problems with subqry as derived_tables which are the reason for not # allowing them in this grammar: # # 1. Name resolving of outer referrence columns seems to be broken. # 2. Create correct references to columns from the derived tables are complicated within RQG. # It might be doable with logic similar to 'lookahead_for_table_alias' in order, # but I want invest time in this right now, and probably never. # # Testcoverage of the different flavours of subqueries are assumed to be sufficient # throught scalar_subqry and IN/EXISTS ## derived_table_unused: { $stack->push(); undef } table_subquery { my $x = " AS table".++$tables.$ext; my @s=($x); $stack->pop(\@s); $x } ; ############################################## ## Ref: ISO 9075, Chap 6.x ## ############################################## value_expr: int_value_expr | char_value_expr ; int_value_expr: _digit | int_column #| int_aggregate | int_value_expr + int_value_expr | int_value_expr - int_value_expr | CHAR_LENGTH(char_value_expr) | (int_value_expr) #| int_scalar_subquery ; char_value_expr: char_column | {"'".$prng->string(4)."'"} #| {"'"}_letter{"'"} #| char_aggregate #| UPPER(char_value_expr) #| LOWER(char_value_expr) #| TRIM(char_value_expr) | (char_value_expr) #| char_scalar_subquery ; char_string_literal: {"'".$prng->string(4)."'"} ; int_column: table_alias.int_field_name ; char_column: table_alias.char_field_name ; ####################################################### ## Ref: ISO 9075, Chap 7.2
## ####################################################### table_value_constr: VALUES table_value_list ; table_value_list: (1, 2, 3) ; ################################################ ## Ref: ISO 9075, Chap 7.3
## ################################################ table_expr: from_clause where_clause group_by_clause having_clause ; ########################################### ## Ref: ISO 9075, Chap 7.4 ## ########################################### from_clause: FROM table_list { scalar(@tables) == $table_refs or die "Table lookahead predicted ".scalar(@tables)." tables, found: ".$tables."\n"; undef } ; #### #
has been inlined into
, and decomposed # in order to increase frequency of: # -
,
,
... # - single # - single
# Particularly we want to avoid to many , .... # being produced, they are allowed though. # Tweak the rule 'table_ref_list' to adjust this. #### table_list: table_ref_list #| table_only_list ; outer_join_pattern_unused: { $stack->push() } table_ref { $stack->set("left",$stack->get("result")); } outer_join_type JOIN table_ref join_spec { my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } ; table_only_list: table | table, table_only_list # { $stack->push() } # table, { $stack->set("left",$stack->get("result")); } # table_only_list ; table_ref_list: table | composite_table_ref | composite_table_ref | composite_table_ref | composite_table_ref | composite_table_ref #| composite_table_ref, table_list ; composite_table_ref: joined_table #| derived_table # See note where 'derived_table:' is defined. ; table: # We use the "AS table" bit here so we can have unique aliases if we use the same table many times { $stack->push(); my $x = $prng->arrayElement($executors->[0]->tables())." AS table".++$tables.$ext; my @s=($x); $stack->pop(\@s); $x } ; lookahead_for_table_alias: { @table_refs = (); my $tmp = join('', @sentence); my $lookahead = substr($tmp,index($tmp,$_)+length($_)); while ($lookahead =~ s/[(](?!SELECT)([^()]*)[)]/$1/g or $lookahead =~ s/[(]SELECT[^()]*[)]//g or $lookahead =~ s/(^[^(()]*)[)].*/$1/) {}; my @tab=(1..($lookahead =~ s/AS table//g)); foreach $id (@tab) { push (@table_refs,"table".$id.$ext) }; "" } ; # NOTE can't make use of 'existing_table_item' as has not been produced yet table_alias: # If there are 'outer tables': choose either inner or outer table_ref { $prng->arrayElement(\@table_refs) } | { scalar(@outer_tables) > 0 ? $prng->arrayElement(\@outer_tables) : $prng->arrayElement(\@table_refs) } ; outer_table_unused: { $prng->arrayElement(\@outer_tables) } ; ############################################ ## Ref: ISO 9075, Chap 7.5 ## ############################################ joined_table: qualified_join | qualified_join | qualified_join | qualified_join | qualified_join | qualified_join #| cross_join | (joined_table) ; qualified_join: inner_join | outer_join ; cross_join: table_ref CROSS JOIN table_ref # Stupid MySQL extension where missing 'cross' join is determined based on no join_condition | table_ref INNER JOIN table_ref | table_ref JOIN table_ref ; # MySQL has the (stupid) cross_join extension (above) where it actually is the presence of the join_cond which # determines if the join is a cross- or inner-join. This creates ambigous parser precedence rules which requires # us to enclose right side argument in paranthesis - Else the 'outer_join:' rule is more in accordance # with the ISO 9075 standards. inner_join: { $stack->push() } table_ref { $stack->set("left",$stack->get("result")); } inner_join_type JOIN (table_ref) join_spec { my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } ; outer_join: { $stack->push() } table_ref { $stack->set("left",$stack->get("result")); } outer_join_type JOIN table_ref join_spec { my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } ; join_spec: ON join_condition #| USING (pk) # Hard to handle, need to refer a avail common column from left / right ; inner_join_type: # Unspecified -> implies 'INNER' | INNER ; outer_join_type: LEFT outer | RIGHT outer ; outer: | OUTER ; # Prefer int_condition as char_condition will hardly ever match. join_condition: int_condition | int_condition | int_condition | int_condition | int_condition | int_condition | char_condition | other_condition ; int_condition: existing_left_table.int_field_name = existing_right_table.int_field_name # General rule | existing_left_table.int_indexed = existing_right_table.int_indexed # Want more joins on indexed field | int_multi_conditions ; # Most of these join conditions crafted to match specific unique indexes int_multi_conditions: # ix1(col_int,col_int_unique) existing_left_table.col_int = existing_right_table.col_int AND existing_left_table.col_int_unique = existing_right_table.col_int_unique | # ix2((col_int_key,col_int_unique)) existing_left_table.col_int_key = existing_right_table.col_int_key AND existing_left_table.col_int_unique = existing_right_table.col_int_unique | # Variant of ix2() above existing_left_table.col_int_key = existing_right_table.int_field_name AND existing_left_table.col_int_unique = existing_right_table.int_field_name | # ix3(col_int,col_int_key,col_int_unique) existing_left_table.col_int = existing_right_table.col_int AND existing_left_table.col_int_key = existing_right_table.col_int_key AND existing_left_table.col_int_unique = existing_right_table.col_int_unique #| # int_condition AND int_condition ; char_condition: existing_left_table.char_field_name = existing_right_table.char_field_name # General rule | existing_left_table.char_indexed = existing_right_table.char_indexed # Want more joins on indexed field | char_multi_conditions ; char_multi_conditions: # ix1(col_char_16,col_char_16_unique) existing_left_table.col_char_16 = existing_right_table.col_char_16 AND existing_left_table.col_char_16_unique = existing_right_table.col_char_16_unique | # ix2(col_varchar_256,col_char_16_unique) existing_left_table.col_varchar_256 = existing_right_table.col_varchar_256 AND existing_left_table.col_varchar_10_unique = existing_right_table.col_varchar_10_unique #| # char_condition AND char_condition ; other_condition: existing_left_table.col_int comparison_operator existing_right_table.col_int | existing_left_table.col_int IS not NULL | existing_right_table.col_int IS not NULL | existing_left_table.col_int not IN (number_list) | existing_right_table.col_int not IN (number_list) | join_condition and_or join_condition | not (join_condition) is_truth_value ; existing_left_table: { my $left = $stack->get("left"); my %s=map{$_=>1} @$left; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/, $table_string); $table_array[1] } ; existing_right_table: { my $right = $stack->get("result"); my %s=map{$_=>1} @$right; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/, $table_string); $table_array[1] } ; ############################################ ## Ref: ISO 9075, Chap 7.6 ## ############################################ where_clause: # is optional | WHERE search_condition ; ################################################################################ # 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 # ################################################################################ ############################################### ## Ref: ISO 9075, Chap 7.7 ## ############################################### group_by_clause: group_by_iff_aggregate | group_by_iff_aggregate | group_by_iff_aggregate | group_by_iff_aggregate | group_by_iff_aggregate | group_by_iff_aggregate | group_by ; ## Conditionaly specify 'GROUP BY' if table expression is a grouped table group_by_iff_aggregate: { scalar(@aggregates) > 0 and scalar(@nonaggregates) > 0 ? " GROUP BY ".join (', ' , @nonaggregates ) : "" } ; group_by: { scalar(@nonaggregates) > 0 ? " GROUP BY ".join (', ' , @nonaggregates ) : "" } ; ############################################# ## Ref: ISO 9075, Chap 7.8 ## ############################################# having_clause: HAVING having_list ||||| ; having_list: having_item | having_item | (having_list and_or having_item) ; having_item: existing_select_item comparison_operator _digit ; and_or: AND | AND | OR ; ################################################### ## Ref: ISO 9075, Chap 7.9 ## ################################################### query_spec: SELECT lookahead_for_table_alias distinct straight_join select_option select_list table_expr ; distinct: |||| DISTINCT ; select_option: | SQL_SMALL_RESULT #| SQL_BIG_RESULT # Need fix for Bug#53534 ||||||| ; straight_join: ||||||||||| STRAIGHT_JOIN ; # Tuned to prefer short