Some points... 1. My response to the style 6 failure
I am also puzzled why 6 failed. The non-null case worked, but the NULL
case did not: no rows with null values were selected. After
"substitution" in:
WHERE mycol = ? OR (mycol IS NULL AND ? = 1)
Effectively, you get this for the NULL case:
WHERE mycol = NULL OR (mycol IS NULL AND 1 = 1)
In this case, the LHS of the OR operator usually fails for most DB
engines, but the RHS should succeed. A couple of possible explanations
(but not good ones):
A. Placeholder is not supported on LHS of = operator. But I'd expect a
driver error during prepare or execute, and there apparently is none.
And a parameter on the LHS of the IS operator works because "? IS NULL"
in style 4 worked.
B. Even though the SQL literal and placeholder parameter are apparently
both int, there is some type of float vs. integer comparison issue.
Seems unlikely though.
2. Not the latest script?
This appears to be a slightly older version of the script. A current
version is at:
http://svn.perl.org/modules/dbi/trunk/ex/perl_dbi_nulls_test.pl
as referenced in the DBI perl doc, in the "Placeholders and Bind Values"
section.
3. How about running a better script?
But that current version has a couple of bugs. Attached is a revision I
came up with, that fixes the bugs and improves the output format just a
bit. Also attached is a diff listing. Tim, are YOU maintaining files
at the link location above? Can you apply the attached revision? BTW,
I feel somewhat qualified to submit a revision, because I was the
primary contributor to the original.
Brian Campbell
Alcatel-Lucent
[EMAIL PROTECTED]
-----Original Message-----
From: Tim Bunce [mailto:[EMAIL PROTECTED]
Sent: Monday, April 30, 2007 5:08 AM
To: Alex Teslik
Cc: dbi-users@perl.org; [EMAIL PROTECTED]
Subject: Re: SQLite 3.3.16 nulls test results
On Fri, Apr 27, 2007 at 08:52:21AM -0700, Alex Teslik wrote:
> as requested by the DBI man page:
>
>
> [root]/home/alex# perl perl_dbi_nulls_test.pl Using connect arguments,
> db version: 3.3.16 => Drop table 'dbi__null_test_tmp', if it already
> exists...
> DBD::SQLite::db do failed: no such table: dbi__null_test_tmp(1) at
> dbdimp.c line 271 at perl_dbi_nulls_test.pl line 92.
> => Create table 'dbi__null_test_tmp'...
> => Insert 4 rows into the table...
> Values 1 Homer
> Values 2
> Values 3 Marge
> Values 4
> => Testing clause style 6: WHERE mycol = ? OR (mycol IS NULL AND ? =
> 1) => WHERE clause style 6 returned incorrect results.
> Non-Null test rows returned: 3
> Null test rows returned:
It's unfortunate and surprising that style 6 isn't supported as that's
the only style that every other database supports.
Could you look into that some more. Perhaps there's a bug somewhere.
> closing dbh with active statement handles at perl_dbi_nulls_test.pl
line 167.
Looks like a bug in DBD::SQLite. Executing a prepared NON-select
statement (like a CREATE TABLE or INSERT) should not leave
$sth->{Active} true.
> 1 styles are supported
> 4: WHERE mycol = ? OR (mycol IS NULL AND ? IS NULL)
I've updated the docs. Thanks.
Tim.
perl_dbi_nulls_test.pl
Description: perl_dbi_nulls_test.pl
*** perl_dbi_nulls_test.pl.orig Tue May 1 13:27:11 2007
--- perl_dbi_nulls_test.pl Tue May 1 13:59:51 2007
***************
*** 102,108 ****
for my $i (0..$#char_column_values)
{
my $val = $char_column_values[$i];
! printf "Inserting values (%d, %s)\n", $i+1, $dbh->quote($val);
$sth->execute($i+1, $val);
}
print "(Driver bug: statement handle should not be Active after an INSERT.)\n"
--- 102,108 ----
for my $i (0..$#char_column_values)
{
my $val = $char_column_values[$i];
! printf " Inserting values (%d, %s)\n", $i+1, $dbh->quote($val);
$sth->execute($i+1, $val);
}
print "(Driver bug: statement handle should not be Active after an INSERT.)\n"
***************
*** 113,123 ****
for my $i (0..$#select_clauses)
{
my $sel = $select_clauses[$i];
! print "\n=> Testing clause style $i: ".$sel->{clause}." to match
$marge\n";
$sth = $dbh->prepare("SELECT myid,mycol FROM $tablename ".$sel->{clause})
or next;
$sth->execute(@{$sel->{nonnull}})
or next;
my $r1 = $sth->fetchall_arrayref();
--- 113,124 ----
for my $i (0..$#select_clauses)
{
my $sel = $select_clauses[$i];
! print "\n=> Testing clause style $i: ".$sel->{clause}."...\n";
$sth = $dbh->prepare("SELECT myid,mycol FROM $tablename ".$sel->{clause})
or next;
+ print " Selecting row with $marge\n";
$sth->execute(@{$sel->{nonnull}})
or next;
my $r1 = $sth->fetchall_arrayref();
***************
*** 124,129 ****
--- 125,131 ----
my $n1_rows = $sth->rows;
my $n1 = @$r1;
+ print " Selecting rows with NULL\n";
$sth->execute(@{$sel->{null}})
or next;
my $r2 = $sth->fetchall_arrayref();
***************
*** 152,172 ****
print "=> WHERE clause style $i returned incorrect results.\n";
if ($n1 > 0 || $n2 > 0)
{
! print " Non-NULL test rows returned these row ids: ".
join(", ", map { $r1->[$_][0] } (0..$#{$r1}))."\n";
! print " The NULL test rows returned these row ids: ".
! join(", ", map { $r2->[$_][0] } (0..$#{$r1}))."\n";
}
}
}
$dbh->disconnect();
!
! printf "\n%d styles are supported $tag:\n", scalar @ok;
print "$_\n" for @ok;
print "\n";
print "If these results don't match what's in the 'Placeholders and Bind
Values'\n";
! print "section of the DBI documentation, or are for a database that not
already listed,\n";
! print "please email the results to [EMAIL PROTECTED] Thank you.\n";
exit 0;
--- 154,176 ----
print "=> WHERE clause style $i returned incorrect results.\n";
if ($n1 > 0 || $n2 > 0)
{
! print " Non-NULL test rows returned these row ids: ".
join(", ", map { $r1->[$_][0] } (0..$#{$r1}))."\n";
! print " The NULL test rows returned these row ids: ".
! join(", ", map { $r2->[$_][0] } (0..$#{$r2}))."\n";
}
}
}
$dbh->disconnect();
! print "\n";
! print "-" x 72, "\n";
! printf "%d styles are supported:\n", scalar @ok;
print "$_\n" for @ok;
+ print "-" x 72, "\n";
print "\n";
print "If these results don't match what's in the 'Placeholders and Bind
Values'\n";
! print "section of the DBI documentation, or are for a database that not
already\n";
! print "listed, please email the results to [EMAIL PROTECTED] Thank you.\n";
exit 0;
