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
# optimizer_access.yy - RQG grammar to reliably mix a variety of table access
20
# methods (range, union, sort_union, etc)
22
# The design goal is to have a minimum of 3 tables per query
23
# This needs to be implemented as the current JOIN construction rule
24
# still produces a number of queries with < 3 tables (and a number of invalid
25
# ( and a number of invalid queries as a result - SELECT table3.whatever FROM
26
# table1, table2 WHERE...)
27
# However, the grammar will produce a varied amount of EXPLAIN output (shown via
28
# --debug) and is useful
30
# NOTE: This must be used with a gendata file, I have been using
31
# range_access.zz with success for now
32
# If you want to try an alternate, ensure that the rules for
33
# things like int_field, char_field, etc are correct for the gendata file
34
################################################################################
37
{ @nonaggregates = () ; $tables = 0 ; $fields = 0 ; "" } main_select ;
40
simple_select | mixed_select | mixed_select ;
43
SELECT distinct straight_join select_option simple_select_list
51
SELECT distinct straight_join select_option select_list
58
distinct: DISTINCT | | | | ;
60
select_option: | | | | | | | | | SQL_SMALL_RESULT ;
62
straight_join: | | | | | | | | | | | STRAIGHT_JOIN ;
65
table1_simple_select_list , table2_simple_select_list , table3_simple_select_list |
66
table3_simple_select_list , table1_simple_select_list , table2_simple_select_list |
70
nonaggregate_select_item |
71
simple_wild_list , nonaggregate_select_item |
72
simple_wild_list , nonaggregate_select_item ;
76
new_select_item , select_list |
77
new_select_item , select_list ;
79
table1_simple_select_list:
80
{ $cur_table='table1'; ""} special_simple_select_list ;
82
table2_simple_select_list:
83
{ $cur_table='table2'; ""} special_simple_select_list ;
85
table3_simple_select_list:
86
{ $cur_table='table3'; ""} special_simple_select_list ;
88
special_simple_select_list:
89
# NOTE: need to determine non-indexed fields so we
90
# can have a non-indexed list here
91
indexed_spec_list | mixed_spec_list ;
94
{ $cur_table } . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
95
{ $cur_table } . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } , indexed_spec_list ;
98
{ $cur_table } . _field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
99
{ $cur_table } . _field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } , mixed_spec_list ;
102
nonaggregate_select_item |
103
nonaggregate_select_item |
104
nonaggregate_select_item |
105
aggregate_select_item ;
107
nonaggregate_select_item:
108
table_123 . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
109
table_123 . _field_indexed AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } |
110
table_123 . _field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
112
aggregate_select_item:
113
aggregate table_123 . aggregate_field ) AS { "field".++$fields } ;
119
{ $stack->set("left",$stack->get("result")); }
120
left_right outer JOIN table_or_join
125
int_condition | char_condition ;
128
{ 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 join_condition_operator
129
{ 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
130
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
131
{ 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 join_condition_operator
132
{ 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
133
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } ;
136
{ 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 join_condition_operator
137
{ 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
138
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
139
{ 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 join_condition_operator
140
{ 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
141
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } ;
144
table | table | table | table | table | table |
148
# We use the "AS table" bit here so we can have unique aliases if we use the same table many times
149
{ $stack->push(); my $x = $prng->arrayElement($executors->[0]->tables())." AS table".++$tables; my @s=($x); $stack->pop(\@s); $x } ;
152
table1 | table1 | table2 | table2 | table3 ;
154
join_condition_operator:
155
= | = | = | = | = | = | = | < | <= | > | >= ;
158
WHERE t1_where_list and_or t2_where_list and_or t3_where_list |
159
WHERE t1_where_list and_or t2_where_list and_or t3_where_list |
160
WHERE t1_where_list and_or t2_where_list ;
164
{ $cur_table='table1'; "" } targeted_where_list ;
167
{ $cur_table='table1'; "" } targeted_where_list ;
170
{ $cur_table='table1'; "" } targeted_where_list ;
173
range_where_list | sort_union_where_list | union_where_list ;
176
range_where_item and_or range_where_item |
177
range_where_list and_or range_where_item |
181
{ $cur_table } . int_field_name comparison_operator int_value |
182
{ $cur_table } . int_field_name comparison_operator existing_table_item . int_field_name |
183
{ $cur_table } . int_field_name IS not NULL |
184
{ $cur_table } . int_field_name not IN ( number_list ) |
185
{ $cur_table } . int_field_name not BETWEEN _digit[invariant] AND ( _digit[invariant] + increment ) |
186
{ $cur_table } . char_field_name comparison_operator char_value |
187
{ $cur_table } . char_field_name comparison_operator existing_table_item . char_field_name |
188
{ $cur_table } . char_field_name not IN ( char_list ) |
189
{ $cur_table } . char_field_name not BETWEEN _char[invariant] AND CHAR(ASCII( _char[invariant] ) + increment ) |
190
{ $cur_table } . char_field_name IS not NULL ;
192
sort_union_where_list:
193
int_idx_where_clause | char_idx_where_clause |
194
sort_union_where_list OR int_idx_where_clause |
195
sort_union_where_list OR char_idx_where_clause ;
197
int_idx_where_clause:
198
{ my @int_idx_fields = ("`pk`" , "`col_int_key`") ; $int_idx_field = $cur_table." . ".$prng->arrayElement(\@int_idx_fields) ; "" } single_int_idx_where_list ;
200
single_int_idx_where_list:
201
single_int_idx_where_list and_or single_int_idx_where_item |
202
single_int_idx_where_item | single_int_idx_where_item ;
204
single_int_idx_where_item:
205
{ $int_idx_field } greater_than _digit[invariant] AND { $int_idx_field } less_than ( _digit[invariant] + increment ) |
206
{ $int_idx_field } greater_than _digit[invariant] AND { $int_idx_field } less_than ( _digit[invariant] + increment ) |
207
{ $int_idx_field } comparison_operator existing_table_item . int_field_name |
208
{ $int_idx_field } greater_than _digit[invariant] AND { $int_idx_field } less_than ( _digit + int_value ) |
209
{ $int_idx_field } greater_than _digit AND { $int_idx_field } less_than ( _digit + increment ) |
210
{ $int_idx_field } comparison_operator int_value |
211
{ $int_idx_field } not_equal int_value |
212
{ $int_idx_field } not IN ( number_list ) |
213
{ $int_idx_field } not BETWEEN _digit[invariant] AND ( _digit[invariant] + int_value ) |
214
{ $int_idx_field } IS not NULL ;
216
char_idx_where_clause:
217
{ my @char_idx_fields = ("`col_varchar_10_latin1_key`", "`col_varchar_10_utf8_key`", "`col_varchar_1024_latin1_key`", "`col_varchar_1024_utf8_key`") ; $char_idx_field = $cur_table." . ".$prng->arrayElement(\@char_idx_fields) ; "" } single_char_idx_where_list ;
219
single_char_idx_where_list:
220
single_char_idx_where_list and_or single_char_idx_where_item |
221
single_char_idx_where_item | single_char_idx_where_item ;
223
single_char_idx_where_item:
224
{ $char_idx_field } greater_than _char AND { $char_idx_field } less_than 'z' |
225
{ $char_idx_field } greater_than _char AND { $char_idx_field } less_than 'z' |
226
{ $char_idx_field } comparison_operator existing_table_item . char_field_name |
227
{ $char_idx_field } greater_than char_value AND { $char_idx_field } less_than char_value |
228
{ $char_idx_field } greater_than char_value AND { $char_idx_field } less_than 'zzzz' |
229
{ $char_idx_field } IS not NULL |
230
{ $char_idx_field } not IN (char_list) |
231
{ $char_idx_field } not LIKE ( char_pattern ) |
232
{ $char_idx_field } not BETWEEN _char AND 'z' ;
235
int_idx_where_clause | char_idx_where_clause |
236
union_where_list OR spec_int_idx_where_clause |
237
union_where_list OR spec_char_idx_where_clause ;
239
spec_int_idx_where_clause:
240
{ my @int_idx_fields = ("`pk`" , "`col_int_key`") ; $int_idx_field = $cur_table." . ".$prng->arrayElement(\@int_idx_fields) ; "" } spec_single_int_idx_where_list ;
242
spec_single_int_idx_where_list:
243
spec_single_int_idx_where_list and_or spec_single_int_idx_where_item |
244
spec_single_int_idx_where_item | spec_single_int_idx_where_item ;
246
spec_single_int_idx_where_item:
247
{ $int_idx_field } >= _digit[invariant] AND { $int_idx_field } <= ( _digit[invariant] + increment ) |
248
{ $int_idx_field } >= _digit[invariant] AND { $int_idx_field } <= ( _digit[invariant] + increment ) |
249
{ $int_idx_field } >= _digit AND { $int_idx_field } <= ( _digit[invariant] + int_value ) |
250
{ $int_idx_field } = int_value |
251
{ $int_idx_field } not IN ( number_list ) |
252
{ $int_idx_field } not BETWEEN _digit[invariant] AND ( _digit[invariant] + int_value ) |
253
{ $int_idx_field } IS not NULL ;
255
spec_char_idx_where_clause:
256
{ my @char_idx_fields = ("`col_varchar_10_latin1_key`", "`col_varchar_10_utf8_key`", "`col_varchar_1024_latin1_key`", "`col_varchar_1024_utf8_key`") ; $char_idx_field = $cur_table." . ".$prng->arrayElement(\@char_idx_fields) ; "" } spec_single_char_idx_where_list ;
258
spec_single_char_idx_where_list:
259
spec_single_char_idx_where_list and_or spec_single_char_idx_where_item |
260
spec_single_char_idx_where_item | spec_single_char_idx_where_item ;
262
spec_single_char_idx_where_item:
263
{ $char_idx_field } >= low_char AND { $char_idx_field } <= high_char |
264
{ $char_idx_field } >= low_char AND { $char_idx_field } <= high_char |
265
{ $char_idx_field } >= _char AND { $char_idx_field } <= high_char |
266
{ $char_idx_field } >= char_value AND { $char_idx_field } <= char_value |
267
{ $char_idx_field } >= char_value AND { $char_idx_field } <= high_char |
268
{ $char_idx_field } IS not NULL |
269
{ $char_idx_field } not IN (char_list) |
270
{ $char_idx_field } not LIKE ( char_pattern ) |
271
{ $char_idx_field } not BETWEEN _char AND 'z' ;
274
################################################################################
275
# We ensure that a GROUP BY statement includes all nonaggregates. #
276
# This helps to ensure the query is more useful in detecting real errors / #
277
# that the query doesn't lend itself to variable result sets #
278
################################################################################
280
{ scalar(@nonaggregates) > 0 ? " GROUP BY ".join (', ' , @nonaggregates ) : "" } ;
283
| | group_by_clause ;
286
| | | | HAVING having_list;
291
(having_list and_or having_item) ;
294
existing_table_item . int_field_name comparison_operator int_value |
295
existing_table_item . char_field_name comparison_operator char_value ;
297
################################################################################
298
# We use the total_order_by rule when using the LIMIT operator to ensure that #
299
# we have a consistent result set - server1 and server2 should not differ #
300
################################################################################
304
ORDER BY total_order_by desc limit ;
307
{ join(', ', map { "field".$_ } (1..$fields) ) };
312
################################################################################
313
# We define LIMIT_rows in this fashion as LIMIT values can differ depending on #
314
# how large the LIMIT is - LIMIT 2 = LIMIT 9 != LIMIT 19 #
315
################################################################################
318
| | | | | LIMIT limit_value ;
321
1 | 2 | 10 | 100 | 1000 ;
324
COUNT( | SUM( | MIN( | MAX( ;
327
int_value | number_list, int_value ;
330
char_value | char_list, char_value ;
333
LEFT | LEFT | LEFT | RIGHT ;
348
= | > | < | != | <> | <= | >= ;
360
1 | 1 | 2 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 20 | 25 | 100 ;
363
_digit | increment | limit_value ;
366
_char | _quid | _english ;
369
'a' | 'b' | 'c' | 'd' |
370
'a' | 'b' | 'c' | 'd' |
374
'w' | 'x' | 'y' | 'z' |
375
'w' | 'x' | 'y' | 'z' |
380
char_value | char_value | CONCAT( _char, '%') | 'a%' | _quid | '_' | '_%' ;
383
{ "table".$prng->int(1,$tables) } ;
386
`pk` | `col_int_key` ;
389
`pk` | `col_int_key` | `col_int` ;
392
`col_varchar_10_latin1_key` | `col_varchar_10_utf8_key` |
393
`col_varchar_1024_latin1_key` | `col_varchar_1024_utf8_key`;
396
`col_varchar_10_latin1_key` | `col_varchar_10_utf8_key` |
397
`col_varchar_1024_latin1_key` | `col_varchar_1024_utf8_key` |
398
`col_varchar_10_latin1` | `col_varchar_10_utf8` |
399
`col_varchar_1024_latin1` | `col_varchar_1024_utf8` ;
402
`col_date_key` | `col_date` ;
405
int_field_name | char_field_name | int_field_name | char_field_name |
406
int_field_name | char_field_name | int_field_name | char_field_name |
407
int_field_name | char_field_name | int_field_name | char_field_name |