1
# Copyright (C) 2009-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
################################################################################
20
# Purpose: For testing the range optimization for MySQL
21
# gendata: Use with conf/optimizer/range_access.zz as a gendata file
25
# no need for an engine argument - we mix storage engines via the gendata file
27
# valgrind - use MarkErrorLog Validator + --valgrind
28
# comparison between MySQL versions / configurations
29
# 3way compares to javadb and postgres
31
# NOTES: This grammar will run against either a single or multi-part index
32
# For multi-part indexes, the index will be created, a set of queries
33
# will be run, then the index will be dropped
35
# TODO: It would be nice to dynamically create indexes or to create
36
# more complex indexes on multiple tables at a time
37
# but this is more than a little tricky
38
################################################################################
42
{ $idx_table = '' ; @idx_fields = () ; "" } query_type ;
45
single_idx_query_set | dual_int_idx_query_set | dual_char_idx_query_set | tri_int_idx_query_set ;
48
single_idx_query ; single_idx_query ; single_idx_query ; single_idx_query ; single_idx_query ;
50
dual_int_idx_query_set:
51
new_dual_int_index ; multi_int_idx_query_set ;
53
dual_char_idx_query_set:
54
new_dual_char_index ; multi_char_idx_query_set ;
56
tri_int_idx_query_set:
57
new_tri_int_index ; multi_int_idx_query_set ;
59
tri_char_idx_query_set:
60
new_tri_char_index ; multi_char_idx_query_set ;
63
single_idx_query | multi_int_idx_query | multi_char_idx_query ;
65
multi_int_idx_query_set:
66
multi_int_idx_query ; multi_int_idx_query ; multi_int_idx_query ; multi_int_idx_query ; multi_int_idx_query ; wild_query ; drop_index ;
68
multi_char_idx_query_set:
69
multi_char_idx_query ; multi_char_idx_query ; multi_char_idx_query ; multi_char_idx_query ; multi_char_idx_query ; wild_query ; drop_index ;
71
################################################################################
72
# index-specific rules
73
################################################################################
76
DROP INDEX `test_idx` ON { $idx_table } ;
79
ALTER TABLE index_table ADD INDEX `test_idx` USING index_type ;
82
index_pre (dual_int_idx_field_list) ;
85
index_pre (dual_char_idx_field_list) ;
88
index_pre (tri_int_idx_field_list) ;
91
index_pre (tri_char_idx_field_list) ;
93
dual_int_idx_field_list:
94
`pk`, `col_int_key` { @idx_fields =("`pk`", "`col_int_key`") ; "" } |
95
`col_int_key` , `pk` { @idx_fields =("`col_int_key`", "`pk`") ; "" } |
96
`col_int_key` , `col_int` { @idx_fields =("`col_int_key`", "`col_int`") ; "" } ;
98
dual_char_idx_field_list:
99
`col_varchar_10_utf8`( small_length ) , `col_varchar_1024_utf8`( large_length ) {@idx_fields = ("`col_varchar_10_utf8`", "`col_varchar_1024_utf8`") ; "" } |
100
`col_varchar_1024_latin1`( large_length ) , `col_varchar_1024_utf8`( large_length ) {@idx_fields = ("`col_varchar_1024_latin1`", "`col_varchar_1024_utf8`") ; "" } ;
102
tri_int_idx_field_list:
103
`pk`, `col_int_key`, `col_int` { @idx_fields =("`pk`", "`col_int_key`", "`col_int`") ; "" } |
104
`col_int_key` , `pk` , `col_int` { @idx_fields =("`col_int_key`", "`pk`", "`col_int`") ; "" } |
105
`col_int_key` , `col_int`, `pk` { @idx_fields =("`col_int_key`", "`col_int`", "`pk`") ; "" } ;
107
tri_char_idx_field_list:
108
`col_varchar_10_utf8`( small_length ) , `col_varchar_1024_utf8`( large_length ) , `col_varchar_1024_latin1`( random_length ) {@idx_fields = ("`col_varchar_10_utf8`", "`col_varchar_1024_utf8`", "`col_varchar_1024_latin1`") ; "" } |
109
`col_varchar_1024_latin1`( large_length ) , `col_varchar_1024_utf8`( large_length ), `col_varchar_10_utf8`( small_length ) {@idx_fields = ("`col_varchar_1024_latin1`", "`col_varchar_1024_utf8`", "`col_varchar_10_utf8`") ; "" } ;
111
################################################################################
113
################################################################################
115
single_idx_where_list:
116
single_int_idx_where_clause | single_char_idx_where_clause |
117
single_idx_where_list and_or single_int_idx_where_clause |
118
single_idx_where_list and_or single_char_idx_where_clause ;
121
single_int_idx_where_clause:
122
{ my @int_idx_fields = ("`pk`" , "`col_int_key`") ; $int_idx_field = ("alias".$prng->int(1,$tables))." . ".$prng->arrayElement(\@int_idx_fields) ; "" } single_int_idx_where_list ;
125
single_int_idx_where_list:
126
single_int_idx_where_list or_and single_int_idx_where_item |
127
single_int_idx_where_item | single_int_idx_where_item ;
129
single_int_idx_where_item:
130
{ $int_idx_field } greater_than _digit[invariant] AND { $int_idx_field } less_than ( _digit[invariant] + increment ) |
131
{ $int_idx_field } greater_than _digit[invariant] AND { $int_idx_field } less_than ( _digit[invariant] + increment ) |
132
{ $int_idx_field } greater_than _digit AND { $int_idx_field } less_than ( _digit[invariant] + int_value ) |
133
{ $int_idx_field } greater_than _digit[invariant] AND { $int_idx_field } less_than ( _digit + int_value ) |
134
{ $int_idx_field } greater_than _digit AND { $int_idx_field } less_than ( _digit + increment ) |
135
{ $int_idx_field } comparison_operator int_value |
136
{ $int_idx_field } not_equal int_value |
137
{ $int_idx_field } not IN (number_list) |
138
{ $int_idx_field } not BETWEEN _digit[invariant] AND (_digit[invariant] + int_value ) |
139
{ $int_idx_field } IS not NULL ;
142
single_char_idx_where_clause:
143
{ 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 = ("alias".$prng->int(1,$tables))." . ".$prng->arrayElement(\@char_idx_fields) ; "" } single_char_idx_where_list ;
145
single_char_idx_where_list:
146
single_char_idx_where_list and_or single_char_idx_where_item |
147
single_char_idx_where_item | single_char_idx_where_item ;
149
single_char_idx_where_item:
150
{ $char_idx_field } greater_than _char AND { $char_idx_field } less_than 'z' |
151
{ $char_idx_field } greater_than _char AND { $char_idx_field } less_than 'z' |
152
{ $char_idx_field } greater_than _char AND { $char_idx_field } less_than 'z' |
153
{ $char_idx_field } greater_than char_value AND { $char_idx_field } less_than char_value |
154
{ $char_idx_field } greater_than char_value AND { $char_idx_field } less_than 'zzzz' |
155
{ $char_idx_field } IS not NULL |
156
{ $char_idx_field } not IN (char_list) |
157
{ $char_idx_field } not LIKE ( char_pattern ) |
158
{ $char_idx_field } not BETWEEN _char AND 'z' ;
160
################################################################################
161
# multi-part index rules
162
################################################################################
164
multi_int_idx_where_list:
165
multi_int_idx_where_clause |
166
multi_int_idx_where_list and_or multi_int_idx_where_clause | multi_int_idx_where_list and_or multi_int_idx_where_clause ;
169
multi_int_idx_where_clause:
170
{ $int_idx_field = ("alias".$prng->int(1,$tables))." . ".$prng->arrayElement(\@idx_fields) ; "" } single_int_idx_where_list ;
173
multi_char_idx_where_list:
174
multi_char_idx_where_clause |
175
multi_char_idx_where_list and_or multi_char_idx_where_clause | multi_char_idx_where_list and_or multi_char_idx_where_clause ;
177
multi_char_idx_where_clause:
178
{ $char_idx_field = ("alias".$prng->int(1,$tables))." . ".$prng->arrayElement(\@idx_fields) ; "" } single_char_idx_where_list ;
182
################################################################################
183
# general-purpose query rules
184
################################################################################
187
{ $tables=0 ; $fields = 0 ; "" } SELECT select_list FROM join WHERE single_idx_where_list opt_where_list order_by_clause ;
190
{ $tables=0 ; $fields = 0 ; "" } SELECT select_list FROM idx_join WHERE multi_int_idx_where_list opt_where_list order_by_clause ;
192
multi_char_idx_query:
193
{ $tables=0 ; $fields = 0 ; "" } SELECT select_list FROM idx_join WHERE multi_char_idx_where_list opt_where_list order_by_clause ;
196
select_item | select_item , select_list ;
199
table_one_two . _field AS { my $f = "field".++$fields ; $f } ;
204
{ $stack->set("left",$stack->get("result")); }
205
left_right outer JOIN table_or_join
212
{ $stack->set("left",$stack->get("result")); }
213
left_right outer JOIN table_or_join
218
int_condition | char_condition ;
221
{ 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 =
222
{ 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
223
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
224
{ 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 =
225
{ 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
226
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
227
{ 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 =
228
{ 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/,
229
$table_string); $table_array[1] } . int_indexed
230
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
231
{ 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 =
232
{ 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/,
233
$table_string); $table_array[1] } . int_field_name
234
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } ;
237
{ 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 =
238
{ 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
239
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
240
{ 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 =
241
{ 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
242
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
243
{ 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 =
244
{ 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
245
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } ;
248
table | table | table | table | table | table |
249
table | table | join | join ;
252
# We use the "AS alias" bit here so we can have unique aliases if we use the same table many times
253
{ $stack->push(); my $x = $prng->arrayElement($executors->[0]->tables())." AS alias".++$tables; my @s=($x); $stack->pop(\@s); $x } ;
256
{ $stack->push() ; my $x = $idx_table." AS alias".++$tables; my @s=($x); $stack->pop(\@s); $x } ;
259
INNER JOIN | left_right outer JOIN | STRAIGHT_JOIN ;
262
LEFT | LEFT | LEFT | RIGHT ;
271
{ my $idx_table_candidate = $prng->arrayElement($executors->[0]->tables()) ; $idx_table = $idx_table_candidate ; $idx_table } ;
274
| | | | and_or where_list ;
277
where_item | where_item | where_item | ( where_list and_or where_item ) ;
280
existing_table_item . int_field_name comparison_operator int_value |
281
existing_table_item . char_field_name comparison_operator _char |
282
existing_table_item . int_field_name comparison_operator int_value |
283
existing_table_item . int_field_name comparison_operator existing_table_item . int_field_name |
284
existing_table_item . char_field_name comparison_operator _char |
285
existing_table_item . char_field_name comparison_operator existing_table_item . char_field_name |
286
existing_table_item . _field IS not NULL |
287
existing_table_item . `pk` IS not NULL |
288
single_idx_where_list ;
292
ORDER BY total_order_by desc limit |
293
ORDER BY order_by_list ;
296
{ join(', ', map { "field".$_ } (1..$fields) ) };
300
order_by_item , order_by_list ;
303
existing_select_item desc ;
309
| | LIMIT limit_size | LIMIT limit_size OFFSET int_value;
312
1 | 2 | 10 | 100 | 1000;
314
################################################################################
315
# utility / helper rules
316
################################################################################
319
_table AS { "alias".++$tables };
322
{ "alias".$prng->int(1,$tables) };
324
existing_select_item:
325
{ "field".$prng->int(1,$fields) };
328
= | > | < | != | <> | <= | >= ;
340
_digit | _digit | _digit | _digit | _digit | digit | other_int ;
343
_tinyint_unsigned | 20 | 25 | 30 | 35 | 50 | 65 | 75 | 100 ;
346
_char | _char | _char | _quid | _english ;
349
char_value | char_value | CONCAT( _char, '%') | 'a%'| _quid | '_' | '_%' ;
352
1 | 1 | 2 | 2 | 5 | 5 | 6 | 10 ;
355
200 | 200 | 200 | 200 | 200 | 100 | 200 | 250 | 37 | 50 | 175 | small_length ;
358
1 | 2 | 5 | 7 | 8 | 9 | 10 | 10 | 10 | 10 ;
361
large_length | large_length | small_length ;
364
`pk` | `col_int_key` ;
367
`pk` | `col_int_key` | `col_int` ;
370
`col_varchar_10_latin1_key` | `col_varchar_10_utf8_key` |
371
`col_varchar_1024_latin1_key` | `col_varchar_1024_utf8_key`;
374
`col_varchar_10_latin1_key` | `col_varchar_10_utf8_key` |
375
`col_varchar_1024_latin1_key` | `col_varchar_1024_utf8_key` |
376
`col_varchar_10_latin1` | `col_varchar_10_utf8` |
377
`col_varchar_1024_latin1` | `col_varchar_1024_utf8` ;
380
int_value | number_list, int_value ;
383
_char | char_list, _char ;
386
alias1 | alias1 | alias1 | alias2 | alias2 ;