1
# Copyright (C) 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/drizzle/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`") ; "" } ;
101
tri_int_idx_field_list:
102
`pk`, `col_int_key`, `col_int` { @idx_fields =("`pk`", "`col_int_key`", "`col_int`") ; "" } |
103
`col_int_key` , `pk` , `col_int` { @idx_fields =("`col_int_key`", "`pk`", "`col_int`") ; "" } |
104
`col_int_key` , `col_int`, `pk` { @idx_fields =("`col_int_key`", "`col_int`", "`pk`") ; "" } ;
106
tri_char_idx_field_list:
107
`col_varchar_10_utf8`( small_length ) , `col_varchar_1024_utf8`( large_length ) {@idx_fields = ("`col_varchar_10_utf8`", "`col_varchar_1024_utf8`") ; "" } ;
109
################################################################################
111
################################################################################
113
single_idx_where_list:
114
single_int_idx_where_clause | single_char_idx_where_clause |
115
single_idx_where_list and_or single_int_idx_where_clause |
116
single_idx_where_list and_or single_char_idx_where_clause ;
119
single_int_idx_where_clause:
120
{ my @int_idx_fields = ("`pk`" , "`col_int_key`") ; $int_idx_field = ("table".$prng->int(1,$tables))." . ".$prng->arrayElement(\@int_idx_fields) ; "" } single_int_idx_where_list ;
123
single_int_idx_where_list:
124
single_int_idx_where_list or_and single_int_idx_where_item |
125
single_int_idx_where_item | single_int_idx_where_item ;
127
single_int_idx_where_item:
128
{ $int_idx_field } greater_than _digit[invariant] AND { $int_idx_field } less_than ( _digit[invariant] + increment ) |
129
{ $int_idx_field } greater_than _digit[invariant] AND { $int_idx_field } less_than ( _digit[invariant] + increment ) |
130
{ $int_idx_field } greater_than _digit AND { $int_idx_field } less_than ( _digit[invariant] + int_value ) |
131
{ $int_idx_field } greater_than _digit[invariant] AND { $int_idx_field } less_than ( _digit + int_value ) |
132
{ $int_idx_field } greater_than _digit AND { $int_idx_field } less_than ( _digit + increment ) |
133
{ $int_idx_field } comparison_operator int_value |
134
{ $int_idx_field } not_equal int_value |
135
{ $int_idx_field } not IN (number_list) |
136
{ $int_idx_field } not BETWEEN _digit[invariant] AND (_digit[invariant] + int_value ) |
137
{ $int_idx_field } IS not NULL ;
140
single_char_idx_where_clause:
141
{ my @char_idx_fields = ("`col_varchar_10_utf8_key`", "`col_varchar_1024_utf8_key`") ; $char_idx_field = ("table".$prng->int(1,$tables))." . ".$prng->arrayElement(\@char_idx_fields) ; "" } single_char_idx_where_list ;
143
single_char_idx_where_list:
144
single_char_idx_where_list and_or single_char_idx_where_item |
145
single_char_idx_where_item | single_char_idx_where_item ;
147
single_char_idx_where_item:
148
{ $char_idx_field } greater_than _char AND { $char_idx_field } less_than 'z' |
149
{ $char_idx_field } greater_than _char AND { $char_idx_field } less_than 'z' |
150
{ $char_idx_field } greater_than _char AND { $char_idx_field } less_than 'z' |
151
{ $char_idx_field } greater_than char_value AND { $char_idx_field } less_than char_value |
152
{ $char_idx_field } greater_than char_value AND { $char_idx_field } less_than 'zzzz' |
153
{ $char_idx_field } IS not NULL |
154
{ $char_idx_field } not IN (char_list) |
155
{ $char_idx_field } not LIKE ( char_pattern ) |
156
{ $char_idx_field } not BETWEEN _char AND 'z' ;
158
################################################################################
159
# multi-part index rules
160
################################################################################
162
multi_int_idx_where_list:
163
multi_int_idx_where_clause |
164
multi_int_idx_where_list and_or multi_int_idx_where_clause | multi_int_idx_where_list and_or multi_int_idx_where_clause ;
167
multi_int_idx_where_clause:
168
{ $int_idx_field = ("table".$prng->int(1,$tables))." . ".$prng->arrayElement(\@idx_fields) ; "" } single_int_idx_where_list ;
171
multi_char_idx_where_list:
172
multi_char_idx_where_clause |
173
multi_char_idx_where_list and_or multi_char_idx_where_clause | multi_char_idx_where_list and_or multi_char_idx_where_clause ;
175
multi_char_idx_where_clause:
176
{ $char_idx_field = ("table".$prng->int(1,$tables))." . ".$prng->arrayElement(\@idx_fields) ; "" } single_char_idx_where_list ;
180
################################################################################
181
# general-purpose query rules
182
################################################################################
185
{ $tables=0 ; $fields = 0 ; "" } SELECT select_list FROM join WHERE single_idx_where_list opt_where_list order_by_clause ;
188
{ $tables=0 ; $fields = 0 ; "" } SELECT select_list FROM idx_join WHERE multi_int_idx_where_list opt_where_list order_by_clause ;
190
multi_char_idx_query:
191
{ $tables=0 ; $fields = 0 ; "" } SELECT select_list FROM idx_join WHERE multi_char_idx_where_list opt_where_list order_by_clause ;
194
select_item | select_item , select_list ;
197
table_one_two . _field AS { my $f = "field".++$fields ; $f } ;
202
{ $stack->set("left",$stack->get("result")); }
203
left_right outer JOIN table_or_join
210
{ $stack->set("left",$stack->get("result")); }
211
left_right outer JOIN table_or_join
216
int_condition | char_condition ;
219
{ 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 =
220
{ 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
221
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
222
{ 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 =
223
{ 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
224
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
225
{ 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 =
226
{ 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/,
227
$table_string); $table_array[1] } . int_indexed
228
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
229
{ 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 =
230
{ 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/,
231
$table_string); $table_array[1] } . int_field_name
232
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } ;
235
{ 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 =
236
{ 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
237
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
238
{ 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 =
239
{ 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
240
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
241
{ 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 =
242
{ 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
243
{ my $left = $stack->get("left"); my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } ;
246
table | table | table | table | table | table |
247
table | table | join | join ;
250
# We use the "AS table" bit here so we can have unique aliases if we use the same table many times
251
{ $stack->push(); my $x = $prng->arrayElement($executors->[0]->tables())." AS table".++$tables; my @s=($x); $stack->pop(\@s); $x } ;
254
{ $stack->push() ; my $x = $idx_table." AS table".++$tables; my @s=($x); $stack->pop(\@s); $x } ;
257
INNER JOIN | left_right outer JOIN | STRAIGHT_JOIN ;
260
LEFT | LEFT | LEFT | RIGHT ;
269
{ my $idx_table_candidate = $prng->arrayElement($executors->[0]->tables()) ; $idx_table = $idx_table_candidate ; $idx_table } ;
272
| | | | and_or where_list ;
275
where_item | where_item | where_item | ( where_list and_or where_item ) ;
278
existing_table_item . int_field_name comparison_operator int_value |
279
existing_table_item . char_field_name comparison_operator _char |
280
existing_table_item . int_field_name comparison_operator int_value |
281
existing_table_item . int_field_name comparison_operator existing_table_item . int_field_name |
282
existing_table_item . char_field_name comparison_operator _char |
283
existing_table_item . char_field_name comparison_operator existing_table_item . char_field_name |
284
existing_table_item . _field IS not NULL |
285
existing_table_item . `pk` IS not NULL |
286
single_idx_where_list ;
290
ORDER BY total_order_by desc limit |
291
ORDER BY order_by_list ;
294
{ join(', ', map { "field".$_ } (1..$fields) ) };
298
order_by_item , order_by_list ;
301
existing_select_item desc ;
307
| | LIMIT limit_size | LIMIT limit_size OFFSET int_value;
310
1 | 2 | 10 | 100 | 1000;
312
################################################################################
313
# utility / helper rules
314
################################################################################
317
_table AS { "table".++$tables };
320
{ "table".$prng->int(1,$tables) };
322
existing_select_item:
323
{ "field".$prng->int(1,$fields) };
326
= | > | < | != | <> | <= | >= ;
338
_digit | _digit | _digit | _digit | _digit | digit | other_int ;
341
_tinyint_unsigned | 20 | 25 | 30 | 35 | 50 | 65 | 75 | 100 ;
344
_char | _char | _char | _quid | _english ;
347
char_value | char_value | CONCAT( _char, '%') | 'a%'| _quid | '_' | '_%' ;
350
1 | 1 | 2 | 2 | 5 | 5 | 6 | 10 ;
353
200 | 200 | 200 | 200 | 200 | 100 | 200 | 250 | 37 | 50 | 175 | small_length ;
356
1 | 2 | 5 | 7 | 8 | 9 | 10 | 10 | 10 | 10 ;
359
large_length | large_length | small_length ;
362
`pk` | `col_int_key` ;
365
`pk` | `col_int_key` | `col_int` ;
368
`col_varchar_10_utf8_key` | `col_varchar_1024_utf8_key`;
371
`col_varchar_10_utf8_key` | `col_varchar_1024_utf8_key` | `col_varchar_10_utf8` | `col_varchar_1024_utf8` ;
374
int_value | number_list, int_value ;
377
_char | char_list, _char ;
380
table1 | table1 | table1 | table2 | table2 ;