Description:
Select without distinct returns multiple, duplicate rows. Select DISTINCT returns zero rows.
It fails on specific where clause values and works on others.
raptor:/usr/local/mysql/bin # ./mysql --version
./mysql Ver 14.12 Distrib 5.0.16, for unknown-linux-gnu (x86_64) using readline 5.0
raptor:/usr/local/mysql/bin # ll mysql
-rwxr-xr-x 1 root mysql 1613416 Nov 15 12:55 mysql
How to repeat:
Run the Perl script below (with the connect parameters set to your data)
=============================================================
#!/usr/local/bin/perl -w
use DBI;
$dbh = &connect();
$sql = "SELECT distinct subProjectId, subProjectDesc
FROM fgpPdSpidTwdid
where subProjectId = 7512";
&exec($sql); # Show results with DISTINCT.
$sql =~ s/distinct//; # Remove __DISTINCT__ from select statement.
&exec($sql); # Show results WITHOUT___DISTINCT__
sub exec() {
my $sql = $_[0];
my $rows = 0;
print("\nExec: SQL = \"$sql\".\n");
$ssh = $dbh->prepare($sql); # Source Statement Handle.
$ssh->execute;
while(@ara = $ssh->fetchrow_array()) {
printf("Fetch[$rows] has %d elements, \"%s\"\n",
scalar @ara, join(", ", @ara));
$rows++;
}
print("SQL returned $rows rows.\n");
}
sub connect() {
$dhost = "raptor"; # Destination Hostname of database server.
$ddb = "XXXXX"; # Destination DataBase.
$duid = "XXXXX"; # Destination User ID.
$dpw = "XXXXX"; # Destination PassWord.
$dbh = DBI->connect("dbi:mysql:database=$ddb;host=$dhost",
$duid, $dpw, {'RaiseError' => 1})
or die "Couldn't connect $DBI::errstr";
return $dbh;
}
===========================================================
Results:
brianp@raptor:/usr/local/apache2/cgi-bin> mysql.bug.pl
Exec: SQL = "SELECT distinct subProjectId, subProjectDesc
FROM fgpPdSpidTwdid
where subProjectId = 7512".
SQL returned 0 rows.
Exec: SQL = "SELECT subProjectId, subProjectDesc
FROM fgpPdSpidTwdid
where subProjectId = 7512".
Fetch[0] has 2 elements, "7512, ddr2 write"
Fetch[1] has 2 elements, "7512, ddr2 write"
SQL returned 2 rows.
brianp@raptor:/usr/local/apache2/cgi-bin>
Description: Select without distinct returns multiple, duplicate rows. Select DISTINCT returns zero rows. It fails on specific where clause values and works on others. raptor:/usr/local/mysql/bin # ./mysql --version ./mysql Ver 14.12 Distrib 5.0.16, for unknown-linux-gnu (x86_64) using readline 5.0 raptor:/usr/local/mysql/bin # ll mysql -rwxr-xr-x 1 root mysql 1613416 Nov 15 12:55 mysql How to repeat: Run the Perl script below (with the connect parameters set to your data) ============================================================= #!/usr/local/bin/perl -w use DBI; $dbh = &connect(); $sql = "SELECT distinct subProjectId, subProjectDesc FROM fgpPdSpidTwdid where subProjectId = 7512"; &exec($sql); # Show results with DISTINCT. $sql =~ s/distinct//; # Remove __DISTINCT__ from select statement. &exec($sql); # Show results WITHOUT___DISTINCT__ sub exec() { my $sql = $_[0]; my $rows = 0; print("\nExec: SQL = \"$sql\".\n"); $ssh = $dbh->prepare($sql); # Source Statement Handle. $ssh->execute; while(@ara = $ssh->fetchrow_array()) { printf("Fetch[$rows] has %d elements, \"%s\"\n", scalar @ara, join(", ", @ara)); $rows++; } print("SQL returned $rows rows.\n"); } sub connect() { $dhost = "raptor"; # Destination Hostname of database server. $ddb = "XXXXX"; # Destination DataBase. $duid = "XXXXX"; # Destination User ID. $dpw = "XXXXX"; # Destination PassWord. $dbh = DBI->connect("dbi:mysql:database=$ddb;host=$dhost", $duid, $dpw, {'RaiseError' => 1}) or die "Couldn't connect $DBI::errstr"; return $dbh; } =========================================================== Results: brianp@raptor:/usr/local/apache2/cgi-bin> mysql.bug.pl Exec: SQL = "SELECT distinct subProjectId, subProjectDesc FROM fgpPdSpidTwdid where subProjectId = 7512". SQL returned 0 rows. Exec: SQL = "SELECT subProjectId, subProjectDesc FROM fgpPdSpidTwdid where subProjectId = 7512". Fetch[0] has 2 elements, "7512, ddr2 write" Fetch[1] has 2 elements, "7512, ddr2 write" SQL returned 2 rows. brianp@raptor:/usr/local/apache2/cgi-bin>