Re: SQLite 3.3.16 nulls test results
- From: Tim.Bunce@xxxxxxxxx (Tim Bunce)
- Date: Thu, 3 May 2007 00:57:13 +0100
On Tue, May 01, 2007 at 05:09:50PM -0500, CAMPBELL, BRIAN D (BRIAN) wrote:
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.
There aren't any changes to what's tested - the results would be the same.
I just modified the test to make it easier to see what was going on.
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.
Patch applied and checked in (r9490). Thanks Brian.
Alex, please run Brian's script and post the results. Thanks.
Tim.
-----Original Message-----
From: Tim Bunce [mailto:Tim.Bunce@xxxxxxxxx]
Sent: Monday, April 30, 2007 5:08 AM
To: Alex Teslik
Cc: dbi-users@xxxxxxxx; msergeant@xxxxxxxx
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.plline 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.
Content-Description: diff_out.txt
*** 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 dbi-users\@perl.org. 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 dbi-users\@perl.org. Thank you.\n";
exit 0;
.
- Follow-Ups:
- Re: SQLite 3.3.16 nulls test results
- From: Alex Teslik
- Re: SQLite 3.3.16 nulls test results
- References:
- RE: SQLite 3.3.16 nulls test results
- From: Brian D Campbell
- RE: SQLite 3.3.16 nulls test results
- Prev by Date: Re: ANNOUNCE: DBI 1.55 RC3
- Next by Date: Re: SQLite 3.3.16 nulls test results
- Previous by thread: RE: SQLite 3.3.16 nulls test results
- Next by thread: Re: SQLite 3.3.16 nulls test results
- Index(es):
Relevant Pages
|
|
