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
################################################################################
19
# outer_join_portable.yy
20
# Purpose: Random Query Generator grammar for testing larger (6 - 10 tables) JOINs
21
# Tuning: Please tweak the rule table_or_joins ratio of table:join for larger joins
22
# NOTE: be aware that larger (15-20 tables) queries can take far too
23
# long to run to be of much interest for fast, automated testing
25
# Notes: This grammar is designed to be used with gendata=conf/optimizer/outer_join.zz
26
# It can be altered, but one will likely need field names
27
# Additionally, it is not recommended to use the standard RQG-produced
28
# tables as they way we pick tables can result in the use of
29
# several large tables that will bog down a generated query
31
# Please rely this variant of this grammar if
32
# doing 3-way comparisons as it has altered code that will produce
33
# more standards-compliant queries for use with other DBMS's
35
# We keep the outer_join grammar as it is in order to also test
36
# certain MySQL-specific syntax variants.
37
################################################################################
39
################################################################################
40
# we have the perl code here as these variables are helpers for generating
42
# nonaggregates - holds all nonaggregate fields used, stored as the alias used
43
# such as field1, field2...
44
# tables - counter used to generate accurate table aliases - table1, table2..
45
# fields - same as tables
47
# NOTE: refer to rule nonaggregate_select_item to see the next two items
50
# table_alias_set - we store aliases for creating standards-compliant
51
# field references in GROUP BY and HAVING clauses
52
# int_field_set - we only use integer fields in this grammar and we
53
# create this helper array for the same purposes as
55
################################################################################
58
{ @table_alias_set = ("table1", "table1", "table1", "table1", "table2", "table2", "table2", "table3", "table4", "table5", "table1", "table1", "table2") ; "" }
59
{ @int_field_set = ("pk", "col_int", "col_int_key") ; "" }
60
{ @nonaggregates = () ; $tables = 0 ; $fields = 0 ; "" } query_type ;
62
################################################################################
63
# We have various query_type's so that we can ensure more syntactically correct
64
# queries are generated. Certain mixes have different requirements
65
# mixed - regular fields + aggregates
66
# simple - regular fields only
67
# aggregate - aggregates only
68
################################################################################
71
simple_select | simple_select | mixed_select | mixed_select | mixed_select | aggregate_select ;
74
{ $stack->push() } SELECT distinct straight_join select_option select_list FROM join WHERE where_list group_by_clause having_clause order_by_clause { $stack->pop(undef) } ;
77
{ $stack->push() } SELECT distinct straight_join select_option simple_select_list FROM join WHERE where_list optional_group_by having_clause order_by_clause { $stack->pop(undef) } ;
80
{ $stack->push() } SELECT distinct straight_join select_option aggregate_select_list FROM join WHERE where_list optional_group_by having_clause order_by_clause { $stack->pop(undef) } ;
82
distinct: DISTINCT | | | | ;
84
select_option: | | | | | | | | | SQL_SMALL_RESULT ;
86
straight_join: | | | | | | | | | | | STRAIGHT_JOIN ;
90
new_select_item , select_list |
91
new_select_item , select_list ;
94
nonaggregate_select_item |
95
nonaggregate_select_item , simple_select_list |
96
nonaggregate_select_item , simple_select_list ;
98
aggregate_select_list:
99
aggregate_select_item | aggregate_select_item |
100
aggregate_select_item, aggregate_select_list ;
103
nonaggregate_select_item |
104
nonaggregate_select_item |
105
nonaggregate_select_item |
106
nonaggregate_select_item |
107
nonaggregate_select_item |
108
aggregate_select_item ;
110
################################################################################
111
# We differ from the main variant of the grammar here
112
# We pop from the previously populated helper arrays table_alias_set and
113
# int_field_set so that we can generate and store fields in the form:
114
# <table_alias> . <field_name> AS field<number>
115
# <table_alias> . <field_name> is stored in the @nonaggregates array and
116
# used in the GROUP BY statements - this is standards-compliant and won't
117
# throw javadb or postgres for a loop
118
################################################################################
120
nonaggregate_select_item:
121
{ my $x = $prng->arrayElement(\@table_alias_set)." . ".$prng->arrayElement(\@int_field_set); push @nonaggregates , $x ; $x } AS {my $f = "field".++$fields ; $f };
123
aggregate_select_item:
124
aggregate table_alias . int_field_name ) AS {"field".++$fields } ;
126
################################################################################
127
# We make use of the new RQG stack in order to generate more interesting
128
# queries. Please refer to the RQG documentation for a more in-depth discussion
129
# of how the stack functions
130
################################################################################
135
{ $stack->set("left",$stack->get("result")); }
136
left_right outer JOIN table_or_join
141
int_condition | char_condition ;
144
{ 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] } . int_indexed =
145
{ 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] } . int_indexed
146
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
147
{ 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] } . int_indexed =
148
{ 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] } . int_field_name
149
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
150
{ 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] } . int_field_name =
151
{ 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/,
152
$table_string); $table_array[1] } . int_indexed
153
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
154
{ 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] } . int_field_name =
155
{ 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/,
156
$table_string); $table_array[1] } . int_field_name
157
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } ;
160
{ 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] } . char_field_name =
161
{ 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] } . char_field_name
162
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
163
{ 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] } . char_indexed =
164
{ 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] } . char_field_name
165
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
166
{ 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] } . char_field_name =
167
{ 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] } . char_indexed
168
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } ;
171
where_item | where_item |
172
( where_list and_or where_item ) ;
175
existing_table_item . `pk` comparison_operator _digit |
176
existing_table_item . `pk` comparison_operator existing_table_item . int_field_name |
177
existing_table_item . int_field_name comparison_operator _digit |
178
existing_table_item . int_field_name comparison_operator existing_table_item . int_field_name |
179
existing_table_item . int_field_name IS not NULL |
180
existing_table_item . int_field_name not IN (number_list) |
181
existing_table_item . int_field_name not BETWEEN _digit[invariant] AND ( _digit[invariant] + _digit );
184
_digit | number_list, _digit ;
186
################################################################################
187
# We ensure that a GROUP BY statement includes all nonaggregates. #
188
# This helps to ensure the query is more useful in detecting real errors / #
189
# that the query doesn't lend itself to variable result sets #
190
################################################################################
192
{ scalar(@nonaggregates) > 0 ? " GROUP BY ".join (', ' , @nonaggregates ) : "" } ;
195
| | | | | | | | group_by_clause ;
200
having_clause_disabled:
201
| | | | HAVING having_list;
206
(having_list and_or having_item) ;
208
################################################################################
209
# NOTE: It would be nice if we also had aggregates in the pool for HAVING
210
# clause items, but the code overhead isn't necessarily worth it in the
211
# portable grammar - we do test this more thoroughly in the regular
212
# version of the grammar
213
################################################################################
216
{ my $y = $prng->arrayElement(\@nonaggregates) ; $y } comparison_operator _digit ;
218
################################################################################
219
# We use the total_order_by rule when using the LIMIT operator to ensure that #
220
# we have a consistent result set - server1 and server2 should not differ #
221
################################################################################
225
ORDER BY total_order_by desc /*+javadb:postgres: NULLS FIRST*/ limit |
226
ORDER BY order_by_list /*+javadb:postgres: NULLS FIRST*/ ;
229
{ join(', ', map { "field".$_ } (1..$fields) ) };
233
order_by_item , order_by_list ;
236
existing_select_item desc ;
241
################################################################################
242
# We mix digit and _digit here. We want to alter the possible values of LIMIT #
243
# To ensure we hit varying EXPLAIN plans, but the OFFSET can be smaller #
244
################################################################################
247
| | LIMIT limit_size | LIMIT limit_size OFFSET _digit;
249
################################################################################
250
# Recommend 8 table : 2 join for smaller queries, 6 : 2 for larger ones
251
################################################################################
254
table | table | table | table | table |
255
table | table | table | join | join ;
257
################################################################################
258
# We stack the probabilities regarding table size via how we create tables
259
# with the gendata config file (conf/optimizer/outer_join.zz)
260
# If for some reason, you ever decide to change this, it is also possible to
261
# stack probabilities by creating a @table_set array and simply listing
262
# certain tables more often. It is less elegant and adaptable, but we document
263
# it here just in case.
264
# EX: @table_set = ("A","A","A","A","B","C")
265
# replace the $executors->[0]->tables() in the rule below with
266
# \@table_set as well
268
# see the nonaggregate_select_item rule
269
# plus the initial query rule for examples
270
################################################################################
274
# We use the "AS table" bit here so we can have unique aliases if we use the same table many times
275
{ $stack->push(); my $x = $prng->arrayElement($executors->[0]->tables())." AS table".++$tables; my @s=($x); $stack->pop(\@s); $x } ;
278
`pk` | `col_int_key` | `col_int` ;
281
`pk` | `col_int_key` ;
284
`col_varchar_10_utf8` |
285
`col_varchar_10_latin1`|
286
`col_varchar_1024_utf8_key` |
287
`col_varchar_1024_utf8` |
288
`col_varchar_1024_latin1_key` |
289
`col_varchar_10_utf8_key` |
290
`col_varchar_1024_latin1` |
291
`col_varchar_10_latin1_key` ;
294
`col_varchar_10_latin1_key` | `col_varchar_10_utf8_key` |
295
`col_varchar_1024_latin1_key` |`col_varchar_1024_utf8_key` ;
298
table1 | table1 | table1 | table1 | table1 | table1 | table1 | table1 | table1 | table1 |
299
table2 | table2 | table2 | table2 | table2 | table2 | table2 | table2 | table2 | other_table ;
302
table3 | table3 | table3 | table3 | table3 | table4 | table4 | table5 ;
305
{ "table".$prng->int(1,$tables) };
307
existing_select_item:
308
{ "field".$prng->int(1,$fields) };
311
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
312
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | _tinyint_unsigned ;
319
= | > | < | != | <> | <= | >= ;
322
COUNT( distinct | SUM( distinct | MIN( distinct | MAX( distinct ;
328
LEFT | LEFT | LEFT | RIGHT ;
333
################################################################################
334
# We define LIMIT_rows in this fashion as LIMIT values can differ depending on #
335
# how large the LIMIT is - LIMIT 2 = LIMIT 9 != LIMIT 19 #
336
################################################################################
339
1 | 2 | 10 | 100 | 1000;