60 lines
2.6 KiB
Perl
60 lines
2.6 KiB
Perl
# Copyright (C) 2008-2009 Sun Microsystems, Inc. All rights reserved.
|
|
# Use is subject to license terms.
|
|
#
|
|
# This program is free software; you can redistribute it and/or modify
|
|
# it under the terms of the GNU General Public License as published by
|
|
# the Free Software Foundation; version 2 of the License.
|
|
#
|
|
# This program is distributed in the hope that it will be useful, but
|
|
# WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
|
|
# General Public License for more details.
|
|
#
|
|
# You should have received a copy of the GNU General Public License
|
|
# along with this program; if not, write to the Free Software
|
|
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301
|
|
# USA
|
|
|
|
use strict;
|
|
use DBI;
|
|
use lib 'lib';
|
|
use lib '../lib';
|
|
|
|
$| = 1;
|
|
require GenTest::Simplifier::SQL;
|
|
|
|
#
|
|
# This script demonstrates the simplification of queries that fail sporadically. In order to account for the sporadicity,
|
|
# we define an oracle() that runs every query 5000 times, and reports that the problem is gone only if all 5000 instances
|
|
# returned the same number of rows. Otherwise, it reports that the problem remains, and the Simplifier will use this information
|
|
# to further quide the simplification process. More information is available at:
|
|
#
|
|
# http://forge.mysql.com/wiki/RandomQueryGeneratorSimplification
|
|
#
|
|
|
|
my $query = " SELECT table3 .`date_key` field1 FROM B table1 LEFT JOIN B JOIN ( B table3 JOIN ( AA table4 JOIN ( C table6 JOIN A table7 ON table6 .`varchar_nokey` ) ON table6 .`int_key` ) ON table6 .`int_nokey` ) ON table6 .`varchar_nokey` ON table6 .`date_key` WHERE NOT ( ( NOT ( NOT ( NOT ( NOT table7 .`int_key` >= table1 .`varchar_key` OR table3 .`time_nokey` <> table1 .`pk` ) AND table4 .`date_key` >= table1 .`date_key` ) OR table3 .`time_key` > '2005-07-24 11:06:03' ) AND table1 .`datetime_key` <= 8 ) AND table7 .`pk` < 6 ) GROUP BY field1 ORDER BY field1 , field1 , field1 , field1 LIMIT 7 ";
|
|
my $trials = 1000;
|
|
|
|
my $dsn = 'dbi:mysql:host=127.0.0.1:port=19306:user=root:database=test';
|
|
my $dbh = DBI->connect($dsn);
|
|
|
|
my $simplifier = GenTest::Simplifier::SQL->new(
|
|
oracle => sub {
|
|
my $query = shift;
|
|
print "testing $query\n";
|
|
my %outcomes;
|
|
foreach my $trial (1..$trials) {
|
|
my $sth = $dbh->prepare($query);
|
|
$sth->execute();
|
|
return 0 if $sth->err() > 0;
|
|
$outcomes{$sth->rows()}++;
|
|
print "*";
|
|
return 1 if scalar(keys %outcomes) > 1;
|
|
}
|
|
return 0;
|
|
}
|
|
);
|
|
my $simplified = $simplifier->simplify($query);
|
|
|
|
print "Simplified query:\n$simplified;\n";
|