1
# Copyright (c) 2008,2010 Oracle and/or its affiliates. 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
package GenTest::Simplifier::Test;
27
use GenTest::Simplifier::Tables;
28
use GenTest::Comparator;
29
use GenTest::Constants;
31
# Check if SQL::Beautify module is present for pretty printing.
35
require SQL::Beautify;
36
$pretty_sql = SQL::Beautify->new;
39
use constant SIMPLIFIER_EXECUTORS => 0;
40
use constant SIMPLIFIER_QUERIES => 1;
41
use constant SIMPLIFIER_RESULTS => 2;
44
### Add options to this list to include them in the generated test
45
### cases. It does not matter whether they only applies to certain
46
### versions, since the non-existing options will be ignored for a
49
my @optimizer_variables = (
52
'optimizer_condition_pushdown',
55
'optimizer_join_cache_level',
64
my $simplifier = $class->SUPER::new({
65
executors => SIMPLIFIER_EXECUTORS,
66
results => SIMPLIFIER_RESULTS,
67
queries => SIMPLIFIER_QUERIES
74
my ($inLine,$useHash) = @_;
75
## Neat for MTR and readability of multiline diffs
76
my $splitLine = join("\n# ", split("\n", $inLine));
78
return "# " . $splitLine;
80
return "/* ". $splitLine . " */";
84
my ($simplifier,$show_index) = @_;
88
my $executors = $simplifier->executors();
90
my $results = $simplifier->results();
91
my $queries = $simplifier->queries();
92
my ($foo, $tcp_port) = $executors->[0]->dbh()->selectrow_array("SHOW VARIABLES LIKE 'port'");
94
## We use Hash-comments in an pure MySQL environment due to MTR
98
if (defined $executors->[$i]) {
99
$useHash = 0 if $executors->[$i]->type() != DB_MYSQL;
104
# If we have two Executors determine the differences in Optimizer settings and print them as test comments
105
# If there is only one executor, dump its settings directly into the test as test queries
107
foreach my $i (0,1) {
108
if (defined $executors->[$i]) {
109
my $version = $executors->[$i]->getName()." ".$executors->[$i]->version();
110
$test .= _comment("Server".$i.": $version",$useHash)."\n";
115
if (defined $executors->[1] and $executors->[0]->type() == DB_MYSQL and $executors->[1]->type() == DB_MYSQL) {
116
foreach my $optimizer_variable (@optimizer_variables) {
117
my @optimizer_values;
118
foreach my $i (0..1) {
119
my $optimizer_value = $executors->[$i]->dbh()->selectrow_array('SELECT @@'.$optimizer_variable);
121
$optimizer_value = 'ON' if $optimizer_value == 1 && $optimizer_variable eq 'engine_condition_pushdown';
122
$optimizer_values[$i] = $optimizer_value;
125
foreach my $i (0..1) {
126
if ($optimizer_values[$i] =~ m{^\d+$}) {
127
$test .= _comment("Server $i : SET SESSION $optimizer_variable = $optimizer_values[$i]",$useHash)."\n";
128
} elsif (defined $optimizer_values[$i]) {
129
$test .= _comment("Server $i : SET SESSION $optimizer_variable = '$optimizer_values[$i]'",$useHash)."\n";
134
} elsif (defined $executors->[0]) {
135
$test .= "--disable_abort_on_error\n";
136
foreach my $optimizer_variable (@optimizer_variables) {
137
my $optimizer_value = $executors->[0]->dbh->selectrow_array('SELECT @@'.$optimizer_variable);
138
$optimizer_value = 'ON' if $optimizer_value == 1 && $optimizer_variable eq 'engine_condition_pushdown';
140
if ($optimizer_value =~ m{^\d+$}) {
141
$test .= "SET SESSION $optimizer_variable = $optimizer_value;\n";
142
} elsif (defined $optimizer_value) {
143
$test .= "SET SESSION $optimizer_variable = '$optimizer_value';\n";
146
$test .= "--enable_abort_on_error\n";
150
my $query_count = defined $queries ? $#$queries : $#$results;
152
# Message to indicate pretty printing module is not present for use.
153
if (!defined $pretty_sql) {
154
say("INFO :: Could not find the module SQL::Beautify to pretty print the query.");
157
foreach my $query_id (0..$query_count) {
160
if (defined $queries) {
161
$original_query = $queries->[$query_id];
163
$original_query = $results->[$query_id]->[0]->query();
166
$test .= _comment("Begin test case for query $query_id",$useHash)."\n\n";
168
my $simplified_database = 'query'.$query_id.$$;
170
my $tables_simplifier = GenTest::Simplifier::Tables->new(
171
dsn => $executors->[0]->dsn(),
172
orig_database => 'test',
173
new_database => $simplified_database
176
my ($participating_tables, $rewritten_query) = $tables_simplifier->simplify($original_query);
178
if ($#$participating_tables > -1) {
179
$test .= "--disable_warnings\n";
180
foreach my $tab (@$participating_tables) {
181
$test .= "DROP TABLE /*! IF EXISTS */ $tab;\n";
183
$test .= "--enable_warnings\n\n"
186
my $mysqldump_cmd = "mysqldump -uroot --net_buffer_length=4096 --max_allowed_packet=4096 --no-set-names --compact --skip_extended_insert --force --protocol=tcp --port=$tcp_port $simplified_database ";
187
$mysqldump_cmd .= join(' ', @$participating_tables) if $#$participating_tables > -1;
188
open (MYSQLDUMP, "$mysqldump_cmd|") or say("Unable to run $mysqldump_cmd: $!");
189
while (<MYSQLDUMP>) {
191
$_ =~ s{\(\n}{(}sgio;
192
$_ =~ s{\)\n}{)}sgio;
193
$_ =~ s{`([a-zA-Z0-9_]+)`}{$1}sgio;
194
next if $_ =~ m{SET \@saved_cs_client}sio;
195
next if $_ =~ m{SET character_set_client}sio;
203
# If show_index variable is defined then SHOW INDEX statement is executed on the list of
204
# participating tables and the output is printed within comments.
205
# This was a request from optimizer team, for them to understand under which circumstances a
206
# query result difference or transformation has taken place.
207
if (defined $show_index) {
208
if ($#$participating_tables > -1) {
209
foreach my $tab (@$participating_tables) {
210
$test .= "# /* Output of `SHOW INDEX from $tab` for query $query_id:\n";
211
my $stmt = $executors->[0]->execute("SHOW INDEX from $simplified_database.$tab");
212
$test .= "# |".join("|",@{$stmt->columnNames()})."|\n";
213
foreach my $row (@{$stmt->data()}) {
214
$test .= "# |".join("|", @$row)."|\n";
222
# If pretty printing module is available then use it to format the query
223
# otherwise use the existing regex pattern to format the query.
224
if ( defined $pretty_sql) {
225
$rewritten_query =~ s{\s+}{ }sgio; # Remove extra spaces.
226
$rewritten_query =~ s{`}{}sgio; # Remove backquotes.
227
$pretty_sql->query($rewritten_query);
228
$test .= $pretty_sql->beautify;
229
$test .= $test.";\n\n"; # Include the query terminator.
231
$rewritten_query =~ s{\s+}{ }sgio;
232
$rewritten_query =~ s{`}{}sgio;
233
$rewritten_query =~ s{\s+\.}{.}sgio;
234
$rewritten_query =~ s{\.\s+}{.}sgio;
235
$rewritten_query =~ s{(SELECT|LEFT|RIGHT|FROM|WHERE|GROUP\s+BY|ORDER\s+BY|HAVING|LIMIT)}{\n$1}sgio;
236
$rewritten_query =~ s{\(}{\n(}sgio; # Put each set of parenthesis on its own line
237
$rewritten_query =~ s{\)}{)\n}sgio; #
238
$rewritten_query =~ s{[\r\n]+}{\n}sgio;
239
$test .= $rewritten_query.";\n\n";
244
if ($rewritten_query =~ m/^\s*SELECT/) {
245
foreach my $ex (0..1) {
246
if (defined $executors->[$ex]) {
248
# The original idea was to run EXPLAIN and provide the query plan for each test case dumped.
249
# However, for crashing queries, running EXPLAIN frequently crashes as well, so we disable it for the time being.
251
# $test .= "/* Query plan Server $ex:\n";
252
# my $plan = $executors->[$ex]->execute("EXPLAIN EXTENDED $query", 1);
254
# foreach my $row (@{$plan->data()}) {
255
# $test .= "# |".join("|", @$row)."|\n";
258
# $test .= "# Extended: \n# ".join("# \n", map { $_->[2] } @{$plan->warnings()})."\n";
259
# $test .= "# */\n\n";
265
(defined $results) &&
266
(defined $results->[$query_id])
268
$test .= _comment("Diff:",$useHash)."\n\n";
270
my $diff = GenTest::Comparator::dumpDiff(
271
$simplifier->results()->[$query_id]->[0],
272
$simplifier->results()->[$query_id]->[1]
275
$test .= _comment($diff,$useHash)."\n\n\n";
278
if ($#$participating_tables > -1) {
279
foreach my $tab (@$participating_tables) {
280
$test .= "DROP TABLE $tab;\n";
284
$test .= _comment("End of test case for query $query_id",$useHash)."\n\n";
291
return $_[0]->[SIMPLIFIER_EXECUTORS];
295
return $_[0]->[SIMPLIFIER_QUERIES];
299
return $_[0]->[SIMPLIFIER_RESULTS];