Description:
Queries of the following form
SELECT table3 .`date_key` field1 FROM B table1
LEFT JOIN B table3
JOIN (
C table6
JOIN A table7
ON table6 .`varchar_nokey`
) ON table6 .`int_nokey`
ON table6 .`date_key`
WHERE NOT ( table1 .`varchar_key` AND table7 .`pk` )
GROUP BY field1
(or very likely simpler ones) produce inconsistent results when run multiple times on a server that is executing other concurrent queries.
See also bug #45266 in Azalea.
How to repeat:
To reproduce:
1. Use the Random Query Generator (in the mysql-test/gentest directory of the mysql-test-extra-6.0 tree) to run the following
$ perl runall.pl --basedir1=/build/bzr/mysql-5.1/ --grammar=conf/join.yy --threads=1 --queries=100000
Ignore any output from this script, it is just there to provide the background query load that is required for the bug to show up.
2. In a separate console, use the following script to run the offending query numerous times:
use strict;
use DBI;
$| = 1;
my $dsn = 'dbi:mysql:host=127.0.0.1:port=19306:user=root:database=test';
my $query = "
SELECT table3 .`date_key` field1 FROM B table1
LEFT JOIN B table3
JOIN (
C table6
JOIN A table7
ON table6 .`varchar_nokey`
) ON table6 .`int_nokey`
ON table6 .`date_key`
WHERE NOT ( table1 .`varchar_key` AND table7 .`pk` )
GROUP BY field1
";
my $dbh = DBI->connect($dsn);
foreach my $trial (1..150000) {
my $sth = $dbh->prepare($query);
$sth->execute();
print localtime()." [$$] trial: $trial; rows ".$sth->rows()."\n";
exit if $sth->rows() != 1;
}
After a few thousand queries, the query will return zero rows.
Description: Queries of the following form SELECT table3 .`date_key` field1 FROM B table1 LEFT JOIN B table3 JOIN ( C table6 JOIN A table7 ON table6 .`varchar_nokey` ) ON table6 .`int_nokey` ON table6 .`date_key` WHERE NOT ( table1 .`varchar_key` AND table7 .`pk` ) GROUP BY field1 (or very likely simpler ones) produce inconsistent results when run multiple times on a server that is executing other concurrent queries. See also bug #45266 in Azalea. How to repeat: To reproduce: 1. Use the Random Query Generator (in the mysql-test/gentest directory of the mysql-test-extra-6.0 tree) to run the following $ perl runall.pl --basedir1=/build/bzr/mysql-5.1/ --grammar=conf/join.yy --threads=1 --queries=100000 Ignore any output from this script, it is just there to provide the background query load that is required for the bug to show up. 2. In a separate console, use the following script to run the offending query numerous times: use strict; use DBI; $| = 1; my $dsn = 'dbi:mysql:host=127.0.0.1:port=19306:user=root:database=test'; my $query = " SELECT table3 .`date_key` field1 FROM B table1 LEFT JOIN B table3 JOIN ( C table6 JOIN A table7 ON table6 .`varchar_nokey` ) ON table6 .`int_nokey` ON table6 .`date_key` WHERE NOT ( table1 .`varchar_key` AND table7 .`pk` ) GROUP BY field1 "; my $dbh = DBI->connect($dsn); foreach my $trial (1..150000) { my $sth = $dbh->prepare($query); $sth->execute(); print localtime()." [$$] trial: $trial; rows ".$sth->rows()."\n"; exit if $sth->rows() != 1; } After a few thousand queries, the query will return zero rows.